Query A Large Table In Sections

Question

Query A Large Table In Sections

possibilities 41

Hello! I have read over this post but cannot get the code to work

https://learn.microsoft.com/en-us/answers/questions/565584/best-approach-to-load-data-from-table-with-billion.html

I have a table with almost 7 billion records in it which I am trying to query. The approach I am attempting involves querying 'smaller' (I realize it's still a lot of data) subsets of 100m records at a time, get the results of the 100m query and put those results in a temp table, move on to the next 100m, query those records, put the results in the result temp table and so on.

Here is my code:

Declare @minID as bigint, @maxID as bigint, @batchsize as bigint = 100000  
  
WHILE @minID IS NOT NULL  
BEGIN  
    SELECT @maxID = MAX(AppDetailID)  
    FROM (SELECT TOP (@batchsize) AppDetailID FROM dbo.ApplicationDetail  
	       WHERE AppDetailID >= @minID and ClaimID <> 0 and ReceiptID <> 0 and AppType = ('CA'))  
  
	INSERT INTO #result (AppDetailID, ReceiptID, ClaimID, AdjustAmt, SessionNbr)  
		SELECT AppDetailID, ReceiptID, ClaimID, AdjustAmt, SessionNbr  
		FROM dbo.ApplicationDetail   
		WHERE AppDetailID between @minID and @maxID)  
  
END

I am getting an error at the section SELECT @maxID = MAX(AppDetailID)

I really appreciate your help understanding what I am doing wrong

0 comments

3 answers

Your answer

Answer 1

Isabellaz-1451 3,616

Hi @possibilities

I think it takes a lot of time for you to write so much data into temporary tables, and there are no indexes in these temporary tables, the query may be even slower.

Maybe you can tell us what is your table structure, what is your final query, can we optimize this query speed by rebuilding proper indexes.

Or you can also split this table into multiple tables, rebuild the index, and then query these tables in turn.

Best Regards，
Isabella

possibilities 41 Reputation points

2022-08-10T10:04:40.31+00:00

My table has 18 columns, one column (AppDetailID) is a primary key, bigint and there is another column used as a foreign key, char(5). There is an existing clustered index on the AppDetailID column. It's my understanding that SSMS will automatically use this index so I do not specify anything index related in my existing query.

Answer 2

Tom Phillips 17,786

There is really no reason to do a loop that way. The loop is just slowing down the process of inserting all the records into the temp table.

What is the problem you are trying to resolve?

possibilities 41 Reputation points

2022-08-09T16:37:10.16+00:00

I am trying to query the 7 billion row table as quickly as possible. My current query takes a very (very) long time to run and I think it's because of the size of the table. I was hoping that by querying the 7 billion table in smaller sections it might run faster.
Tom Phillips 17,786 Reputation points

2022-08-09T20:20:57.937+00:00
You are still querying 7 billion records, slower in a loop because it has to find the min/max values then do the inserts.

A single command will run faster than a loop of X records.

INSERT INTO #result (AppDetailID, ReceiptID, ClaimID, AdjustAmt, SessionNbr) SELECT AppDetailID, ReceiptID, ClaimID, AdjustAmt, SessionNbr FROM dbo.ApplicationDetail
possibilities 41 Reputation points

2022-08-09T20:23:19.16+00:00

Ok thank you Tom. Simply put, it seems querying billions of rows is going to take a long time regardless I guess
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-08-09T21:29:35.543+00:00

I am not so sure. possibilities may very well be on the right track. Particularly, if the temp table is needed anyway. True, the temp table will be as big as it will be, batching or not, but the log usage in tempdb will be reduced if we do in batches.

As for the SELECT operation, that's a little hard to say if batching helps here. To know for sure, one would need to run a test - which of course is going to take a long time. Then again, if the desire is to be friendly with the system, we could read in small batch sizes and pause in between to avoid the big table driving everything else out of the cache at once. But with a small batch size, it will absolutely be slower.
possibilities 41 Reputation points

2022-08-10T10:06:59.863+00:00

Thank you. What would you suggest is a good batch size? I was wanting to query 100 million rows at a time which of course is still a lot of data but nothing compared to 7 billion
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-08-10T21:09:41.717+00:00

As they say: it depends.

My standard batch size is 5 million rows, but that's mainly for write operations. There are so many things involved, for instance: how wide is the table? You said 18 columns, but it's more about the bytes. If one column is a large LOB column that changes the equation. So does the available amount of memory, the current log and tempdb size etc.

But I think it is good that you've taken a large batch size. Some people only take a few thousand at a time, and there is certainly a penalty for that. I recall a test I did with a loop to copy a table to a new version of the schema. I still gained performance with some 10-15% when I went from 500000 rows to five million.

Answer 3

Tom Cooper 8,501

You have to give the subquery a name. In this case it makes no difference what name you give it. So you could do

     SELECT @maxID = MAX(AppDetailID)  
     FROM (SELECT TOP (@batchsize) AppDetailID FROM dbo.ApplicationDetail  
            WHERE AppDetailID >= @minID and ClaimID <> 0 and ReceiptID <> 0 and AppType = ('CA')) GetAppIDs

Tom

P.S. When you want us to help with an error, it is best if you give us the error message and error number. Don't just say "I am getting an error."

possibilities 41 Reputation points

2022-08-09T15:19:33.63+00:00

Wow that's so frustrating! I swear I tried that already and it still wasn't working. Thanks so much. Before I close this thread I want to see if I can get the rest of the code to work.

Share via

Query A Large Table In Sections

3 answers

Your answer