By TheNixon


2014-09-11 10:13:22 8 Comments

A view executed Select columns from view where col= "date range" (view contains calculation in it Col1+Col2*col3 AS Col4). It took 7 seconds for 500K rows to return, logical reads some 144k.

Added covering index and now after execution it says: logical reads = 8365 (some 17x times less) but the time of execution remained the same, some 7 seconds for 500k to return.

I would expect that reduction of logical reads would lower execution time too, or I am doing something wrong? Any advice?

Thanks.

@Craig, I tired what you told me and execution plan shows me that it was ignoring hints in both cases. It was index seek every time (index I added to help time execution). When I tried it without doing freeing cache it executed in almost the same time for all three of them and with no physical reads for the first execution. No need since all data was in the memory already. Here is the output of what you told me to try:

Warning: Index hints supplied for view 'Name of the view' will be ignored. Warning: Index hints supplied for view 'Name of the view' will be ignored.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

do not use any indexes

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (100000 row(s) affected) Table 'Name'. Scan count 2, logical reads 2137, physical reads 7, read-ahead reads 6510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 156 ms, elapsed time = 939 ms.

let mssql choose what index to use

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (100000 row(s) affected) Table 'Name'. Scan count 2, logical reads 2137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 94 ms, elapsed time = 799 ms.

force index name_of_index

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (100000 row(s) affected) Table 'Name'. Scan count 2, logical reads 2137, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 47 ms, elapsed time = 769 ms.

2 comments

@Remus Rusanu 2014-11-27 08:44:22

some 7 seconds for 500k to return, and also a lot of time to render the grid you're likely displaying the results in.

You are waiting 7 seconds because that's how much it takes for SQL to push 500k rows to your client. Look at client statistics in your SSMS, see Database Engine Query Editor:

Include Client Statistics: Includes a Client Statistics window that contains statistics about the query and about the network packets, and the elapsed time of the query.

SQL Server Execution Times: CPU time = 47 ms, elapsed time = 769 ms.

Your actual query executes in 47ms. Elapsed time is much longer (still under 1 second) because of network waits. You can confirm this using wait stats analysis, read How to analyse SQL Server performance for details, including how to capture the query wait stats.

Ultimately, the problem is returning 500k rows to the client. There cannot be any reason for such operation, no human user can comprehend half a million rows. Process data on the back end.

Half a million rows is used since it is easier to notice speed changes with more than less data

Well, in this case you have sent yourself on a snipe hunt. There is no problem, other that one of your own doing in marshaling and rendering 500k rows. Is a completely bogus scenario, no app should retrieve 500k rows. And processing of 500k rows(eg. aggregates) should tests... the processing, including the aggregates.

@XCom69 2014-11-27 07:16:03

You would also need to clear the query cache or use RECOMPILE to ensure SQL Server is creating a new query plan when testing (Do No use RECOMPILE in production without proper testing). You would also need to see if parallelism is playing it's part in the execution time(Default SQL Server settings(Cost Threshold=5, recommend to increase this to some where around 45 for a start) would split this query for parallelism).

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] Check existence with EXISTS outperform COUNT! ... Not?

1 Answered Questions

1 Answered Questions

[SOLVED] High processor utilization when running a stored procedure

1 Answered Questions

[SOLVED] Aggregation in Outer Apply vs Left Join vs Derived table

1 Answered Questions

[SOLVED] Multi-statement TVF vs Inline TVF Performance

4 Answered Questions

[SOLVED] Which of these queries is best for performance?

Sponsored Content