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|improve this question

migrated from stackoverflow.com May 20 '11 at 10:57

This question came from our site for professional and enthusiast programmers.

10  
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

5 Answers 5

Depends on what you mean by table scan. If you mean table scan operator as shown in an execution plan, there is nothing you can do, to speed up this operator.

On the other hand, in some cases, you can optimize your query and get rid of the table scan in your execution plan.

You might want to show the query you are trying to tune, the schemas of related tables, indexes, etc. This way community can suggest ways to go forward.

Usual answer to "how to avoid table scan" is "use indexes".

share|improve this answer
    
Thanks for you answer,my mean is table scan operator.when i execution sql like select * from uscc_sv_channel,it's need 24 second to complete.i think is to slow,i want to speed up it –  Robin May 21 '11 at 6:09

If your table is to big you can use

  1. indexing
  2. Partitioning http://msdn.microsoft.com/en-us/library/ms190787.aspx

to speed up. How to indexing and partitioning is dependent on your data and the quires you are executing.

share|improve this answer

First you need to observe proper indexing. Next when you query, always do pagination. This will be a very big help to minimize the stress of your database.

share|improve this answer

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|improve this answer

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|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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