Question :
I have a SQL SELECT statement that has joins on 27 of our warehouse tables.
Original statement was taking more than an hour everyday so I decided to optimize it.
I created some indexes but still it was taking some time. To find out the main culprit, I changed my select statement to SELECT COUNT(*), commented all the joins and started uncommenting the joins one by one.
With some NOLOCKS and more indexes, I managed to bring down the execution time from 65 mins to 6 mins but as soon as I added my columns back in SELECT statement instead of COUNT(*), it started taking more than 70 mins.
So can some one please explain why this is happening and how I can resolve it.
P.S.: I don’t have any case statement or any other calculations going on in my select statement. Its purely selecting columns. (around 150 columns)
Answer :
I’m guessing you don’t have any covering indexes for the select columns. Because your Count(*) runs faster, you probably have the columns in the joins or where clause indexed. As soon as you add your select columns back, the query engine now has to scan the whole table again. If possible, add the select columns as included columns to the indexes (not sure if this is ms sql server).