I have a table with 1.5 million records.It takes 24 seconds for one table scan.It's too long.
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:
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.
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.