Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #923

Re: Break Up Large Table Query Into Results of N Rows

Show all headers | View raw

pbd22 (dushkin@gmail.com) writes:
> The specifics is that we are doing email deployments but google is 
> moving all of the email sent to gmail users to their spam boxes. As a
> result, we have to "chunk" the gmail users out of the total amount and
> send in manageable batches. We have figured that 80,000 per batch out of
> the total gmail users in the table is possible. 
>... 
> Bob, the table we are querying against is pretty simple. Essentially, 
> it has one one column - "email_address" which is a varchar. Its data is
> about 1 million email addresses (but that number changes often). The
> result set table(s) should only have two columns, the count (INT) and
> the email_address (varchar). Please see below. 

The simple-minded solution would be:

   WITH numbered AS (
      SELECT email, row_number() OVER(ORDER BY email) AS rowno
      FROM   addresses
      WHERE  email LIKE '%@gmail.com'
   )
   SELECT email 
   FROM   numbered
   WHERE  rowno > (@batchno - 1) * @batchsize 
     AND  rowno <= (@batchno - 1) * @batchsize 

But it would be far more efficient to do:

   CREATE TABLE gmail_addresses (rowno int NOT NULL,
                                 gmail nvarchar(255) NOT NULL,
      CONSTRAINT pk_gmail PRIMARY KEY CLUSTERED (rowno),
      CONSTRAINT pk_unique UNIQUE NONCLUSTERED(gmail))

   INSERT gmail_addresses(rowno, gmail)
     SELECT row_number() OVER(ORDER BY (SELECT 1)), gmail
     FROM   (SELECT DISTINCT email
             FROM   addresses
             WHERE  email like  '%@gmail.com') AS x

Then you have materialised the row number once for all, and a selection 
of 80000 accounts will be quick.




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Break Up Large Table Query Into Results of N Rows pbd22 <dushkin@gmail.com> - 2012-01-31 06:57 -0800
  Re: Break Up Large Table Query Into Results of N Rows pbd22 <dushkin@gmail.com> - 2012-01-31 12:50 -0800
    Re: Break Up Large Table Query Into Results of N Rows "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-01-31 16:49 -0500
    Re: Break Up Large Table Query Into Results of N Rows Erland Sommarskog <esquel@sommarskog.se> - 2012-01-31 23:03 +0100
      Re: Break Up Large Table Query Into Results of N Rows pbd22 <dushkin@gmail.com> - 2012-02-01 07:34 -0800
        Re: Break Up Large Table Query Into Results of N Rows "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-02-01 15:36 -0500
        Re: Break Up Large Table Query Into Results of N Rows Erland Sommarskog <esquel@sommarskog.se> - 2012-02-01 23:54 +0100

csiph-web