Sql server 2014 - Select * query optimization

Question

Hello,
We have Sql server 2014 server and currently running Select * from table taking more then 2 minutes and it has almost 2M rows, is it we can improve it so it will take less time?

I know this one not having any Where condition to use the index but from execution plan perspective, it's using Cluster Index Scan so cost is 100%.

Thank you!

Answer 1

There is not much you can update with such a query. You can do ALTER INDEX REBUILD on the clustered index. This can help if the table is heavily fragmented, particularly in a way so that pages are only half-full.

Else you will need to look at that * and ask yourself, do I really need all those columns? Particularly, if there are LOB columns, can you leave these out?

It also matters where you do the SELECT. If you are running this in SSMS, this is just the wrong place to test. Most time of those two minutes may be spent in SSMS rendering the grids.

Network is of course also an important factor. If the database is on a remote server and you only have a 10 Mbit connection, it is not going to be fast...

Answer 2

I have also turned in SET STATISTICS IO ON
It shows mostly logical reads
Scan count 1, logical reads 147518, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Answer 3

Ima testing in SSMS because this query is part of our Reporting tool so instead of testing there i ma checking on my SSMS as don't want to run in Reporting server.

If you don't want to run it where you are going to run it, you cannot test it. It is that simple.

For this query there is not that much you can to in SQL Server. Well, you can compress the table (if on Enterprise) or even slap on a columnstore index. But if the bottleneck is in the client, which is very much likely the case, it will have zero effect.

As for the waitstats, well you can clear sys.dm_os_wait_stats with

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

just before you run the query, and then inspect the DMV directly after the query.

Answer 4

Hi @Pds ,

Here are a few tips about optimizing queries (some of them are also mentioned by Erland):
Indexing: Ensure proper indexing for quick access to the database.
Select query: Specify the columns in SELECT query instead of SELECT* to avoid extra fetching load on the database.
Running queries: Loops in query structure slows the sequence. Thus, avoid them.
Matching records: Use EXITS() for matching if the record exists.
Subqueries: Avoid correlated sub queries as it searches row by row, impacting the speed of SQL query processing.
Wildcards: Use wildcards (e.g. %xx%) wisely as they search the entire database for matching results.
Operators: Avoid using function at RHS of the operator.
Fetching data: Always fetch limited data.
Loading: Use a temporary table to handle bulk data.
Selecting Rows: Use the clause WHERE instead of HAVING for primary filters.

For more information, please see: https://www.mantralabsglobal.com/blog/sql-query-optimization-tips/

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Seeya,
Thank you very much for sending some tips regarding query optimization.
I have implemented some of above tips and it was always benefited applying it.
In some cases, when we run through Reporting tool, need to use Select * as including column list not viable option because of sometimes we are adding columns into base table/views.

Agreed, Elrand suggested some useful tips and always he had responded and provided useful tips.