How to improve sql server 2008 table scan speed?

Question

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

Answer 1

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.

Answer 2

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".

Answer 3

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.

Answer 4

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.

Answer 5

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.