looping through rows against list in other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

looping through rows against list in other table

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2010-12-13 : 10:25:42

Hi

My question is how to optimize a query by looping through a list of ID's.

I have a huge SQL server 2008 table, something like 500 million records. I have a storeID field as a primary key which has been indexed. There are about 500 storeid's (not incremental) and about 1 million records per storeid. If I want to even run a simple select query on all these records it takes forever.

My question is how to run a simple query, for example through all the records without it taking so long and slowing the whole system down to a standstill. I thought the best option would be to run separate queries which loop through all the 500 storeID's against a list in another table. Not sure where to start with this though. one method I see is to use temp tables.

The simple query I would like to run is below, obviously changing the storeID to the next on the list each time it is run,


SELECT storeid, location
 FROM stores 
 WHERE storeid = '10' and location = 'Manchester'

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-13 : 12:36:14

can you post the Table DDL with it's Indexes?

And understand, that the query you posted is returns 100,000 of rows to (where exactly) you're gonna kill your resources and buffers

What is it that you exactly want to produce?


SELECT storeid, location, COUNT(*) AS ROW_COUNT FROM stores 
GROUP BY storeid, location
ORDER BY 3 DESC

Post the top 20 rows

What does that give you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

spinningtop
Starting Member

29 Posts

Posted - 2010-12-13 : 16:12:15

Hi

Sorry, I'll try to be clearer.

I've posted the first 2 columns of the table below. Lets say I want to count all the records in the table but as you say a query which runs through the whole table at once would take too much resources with such a large table as 500 million records, each distinct storeID has about a million records. So I want to run separate count queries on each storeID like the ones below but loop the queries so they run consecutively one after the other using a list to get the next storeID. At the end the results of each count query are added so I get a grand total for the table.

I'm mainly just interested in how to iterate through a table like this .



SELECT count(*)
FROM stores
WHERE storeID = '10'


SELECT count(*)
FROM stores
WHERE storeID = '14'


SELECT count(*)
FROM stores
WHERE storeID = '50'

storeID location
10 Manchester
10 Manchester
10 Manchester
14 London
14 London
14 London
50 Birmingham
50 Birmingham

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources