Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Best way to select a large amount of data? Expand / Collapse
Author
Message
Posted Thursday, August 19, 2010 1:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:33 PM
Points: 140, Visits: 553
Hello All,
I have this situation:
For several reports we need to extract about 5 millions records from a table of 124 million ( 173 gb)
I am able to use the clustered index ( with a "like" statement) and the only other parameter is a column with a state so it is not helping much, the thing is that the resultant table is really big ( 5 gb ) and is taking forever to load and it is also consuming lots of resources from the server ( we usually get the error there is not enough system memory to run this query... ) That said, I need to find a way to obtain the same data in a faster way or at least not consuming that much resources, maybe in chunks but I am not sure how to do it...
We have a server with 16 processors and 42 gb of ram, sql server 2006 sp3

Thanks a los
Post #972148
Posted Thursday, August 19, 2010 2:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
ricardo_chicas (8/19/2010)

For several reports we need to extract about 5 millions records from a table of 124 million ( 173 gb)
I am able to use the clustered index ( with a "like" statement) and the only other parameter is a column with a state so it is not helping much, the thing is that the resultant table is really big ( 5 gb ) and is taking forever to load and it is also consuming lots of resources from the server ( we usually get the error there is not enough system memory to run this query... ) That said, I need to find a way to obtain the same data in a faster way or at least not consuming that much resources, maybe in chunks but I am not sure how to do it...
We have a server with 16 processors and 42 gb of ram, sql server 2006 sp3

Do you have any indexes to actually serve this query?
Retrieving 5M out of 124M rows is a case where a very selective index would certainly help.
On the other hand, a "like" predicate pretty much forces optimizer to do a full table scan - have you traced the query to see what is doing?


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #972153
Posted Thursday, August 19, 2010 2:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:33 PM
Points: 140, Visits: 553
Hi,
I forgot to say that the "like" is this way: column like '123456%'
so it is using the index and doing a clustered index seek
Post #972161
Posted Thursday, August 19, 2010 2:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
ricardo_chicas (8/19/2010)
I forgot to say that the "like" is this way: column like '123456%'
so it is using the index and doing a clustered index seek

Are you positive this is what query is doing? did you check execution plan?


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #972178
Posted Thursday, August 19, 2010 2:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:33 PM
Points: 140, Visits: 553
Yes, I am absolutely sure
Post #972179
Posted Thursday, August 19, 2010 4:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:05 PM
Points: 31,284, Visits: 15,750
Are you perhaps selecting columns you don't need? Or pulling back BLOB type data?

5mm rows doesn't seem like a crazy number. Are you sure the server is properly sized?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #972222
Posted Thursday, August 19, 2010 4:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:33 PM
Points: 140, Visits: 553
I am only getting 5 columns, all of then are small, what i am looking for is a way to select chunks of that data in a optimal way....
Post #972228
Posted Friday, August 20, 2010 1:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
ricardo_chicas (8/19/2010)
Hello All,
I have this situation:
For several reports we need to extract about 5 millions records from a table of 124 million ( 173 gb)
I am able to use the clustered index ( with a "like" statement) and the only other parameter is a column with a state so it is not helping much, the thing is that the resultant table is really big ( 5 gb ) and is taking forever to load and it is also consuming lots of resources from the server ( we usually get the error there is not enough system memory to run this query... )


Exactly how are you generating the report?

SSRS ? Custom program ? T-SQL ?

Is the report generator running on the same machine as the SQL server ?

Do you really need to return all 5 million rows to the report generator?

I suppose the report generator does not print out a report with 5 million rows but instead does some aggregations of the data to print out a more reasonable number of rows. If this is the case could you perhaps do this aggregation in the source query instead of doing it in the report ?

Another possible thing to look for is if you have an ORDER BY in the source query - that might force SQL server to sort the result set which is fairly expensive for 5GB data


/SG
Post #972331
Posted Friday, August 20, 2010 8:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:05 PM
Points: 31,284, Visits: 15,750
5mm rows for a report is a little silly. There is no way anyone goes through this much data, even for an audit. And I can't see it being run very often.

I would look to clean this up from a business point of view.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #972543
Posted Friday, August 20, 2010 9:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:33 PM
Points: 140, Visits: 553
Hello,
It is a T-SQL program, the problem is that we really need that much info, I can't change the logic, And that info changes really often so the report must be updated every day, again, there is a way to retrieve that much info in chunk, but in an optimal way?
Post #972627
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse