Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 1.5 million records.It takes 24 seconds for one table scan.It's too long.

share

migration rejected from stackoverflow.com Jun 30 at 14:37

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by usr, Colin 't Hart, Michael Green, Mark Sinkinson, RolandoMySQLDBA Jun 30 at 14:37

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – usr, Colin 't Hart, Michael Green, Mark Sinkinson
If this question can be reworded to fit the rules in the help center, please edit the question.

12  
We need more information - please post the query and explain table structure, unless you want answers like buy a fast disk or delete half of the records. ;) –  Marek Grzenkowicz May 20 '11 at 6:24
3  
Please attach the output of DBCC SHOWCONTIG(yourtablename) WITH TABLERESULTS –  Will A May 20 '11 at 6:27
5  
1.5 million rows doesn't define how big a table is. Does each row have a single tinyint in it, or are they using 8000 bytes of in-row data? –  Damien_The_Unbeliever May 20 '11 at 6:42
    
This is my dbcc result USCC_SV_ChannelProdRel 1305771709 0 0 8221 1545257 41 41 41 0 1029 1028 13.521 ‌​99.8329503335804 99.9028182701652 1028 1029 0 0.194363459669582 –  Robin May 21 '11 at 4:54

2 Answers 2

My normal response to this is:

1) make sure that your table normalization is in good order first. When you intend to store more than 50,000 records in a table, you need to start really considering proper table design. If you are not going to be storing a book worth of text, avoid varchar(max), if you know its a zip code, set it to char(5), just the basic stuff like that.

2) Wrap that query up into a store procedure, this will allow sql to create an optimized execution plan for that query.

3) Check out your actual execution plan. See if you can diagnose where the bottleneck on that query is. This will give you an indication of what kind of non clustered indexing you need to setup on that table.

4) If your trying to scan text and it's on a varchar over 900 bytes, you will not be able to drop an index on it. You should consider using full text indexing

5) MAKE SURE YOU HAVE A PRIMARY KEY ON THE TABLE!

6) If you are using SQL Express, you might consider upgrading to SQL Standard, as it will allow you to use more RAM to better serve your indexes.

I have tables with 500,000,000 records that will return from their queries in milliseconds with the right balance of design, query optimization and indexing

Another tip to consider is that if you are using table variables in your TSQL, go ahead and use a PRIMARY KEY if you can, the speed boost you will get from doing so in joins is worth the 11 characters of typing it takes you

Best of luck.

share

You can also run your query in the SQL database engine tuning advisor. This will give you suggestions on how to improve your query. Click here to see how to use the DTA.

share

Not the answer you're looking for? Browse other questions tagged or ask your own question.