3

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.

Improve this question
8
  • 3
    You're returning half a million rows to, what, an SSMS grid? Over what type of network connection? I think you need to look at this from a higher angle - reducing reads on the database side doesn't change the time it takes to transfer that data over the network or the work Management Studio has to do to render it. Never mind the more basic question of why are you returning half a million rows to anyone anyway...
    – Aaron Bertrand
    Sep 11, 2014 at 11:15
  • 1
    Try throwing the data into a temp table to compare performance rather than returning to client, eg something like: Select columns into #tmp1 from view where col= "date range" go create index yourIndex ... go Select columns into #tmp2 from view where col= "date range"
    – wBob
    Sep 11, 2014 at 12:05
  • Or is parallelism kicking in on the first query and not the second? Have a look at the execution plans, post them up if possible.
    – wBob
    Sep 11, 2014 at 12:36
  • @AaronBertrand Yes, SSMS grid but just for testing since users were asking can we improve the execution. Half a million rows is used since it is easier to notice speed changes with more than less data. I am doing it on the SQL Server box itself, so there is no data transfer between far locations.
    – TheNixon
    Sep 11, 2014 at 12:47
  • 1
    lol well you could try clearing the buffer pool (not in production!) in between each run to get a true cold cache comparison, but I wouldn't worry about it too much, I think you've got a result there : )
    – wBob
    Sep 11, 2014 at 14:46

2 Answers 2

Reset to default
3

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.

Improve this answer
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.