RE: what happens internally when you execute same query in multiple times

SSC Guru

Points: 1003863

January 18, 2017 at 5:54 pm

#1923586

Grant Fritchey - Wednesday, January 18, 2017 10:47 AM
Eric M Russell - Wednesday, January 18, 2017 9:23 AM
Grant Fritchey - Wednesday, January 18, 2017 7:56 AM
Eric M Russell - Wednesday, January 18, 2017 7:11 AM
Assuming each query has the same parameters and the data was static between executions, I would expect the number of page reads and the execution plan to be the same. CPU time and duration will vary. One reason why the first execution may take significantly longer is physical reads (read from disk) versus logical reads (read from page buffer cache in memory).
And possibly additional time for compiling the query if there's not already a plan in cache.
Yeah, I always hate sitting there waiting an extra 100ms for my query to compile the first time through. 🙂
Yours take that long? Ha!

I'm fighting a problem right now where some lovely ORM generated code adds something like 30 different criteria to the WHERE clause. Because of its complexity and the constantly changing data it's provided, it ALWAYS recompiles and it takes 2 to 15 SECONDS to do so. It get's called several thousand times every hour. We're replacing it with a stored procedure. The real key is that one of the items that it passes to the WHERE clause is the PK for the table. One of the reasons why it takes so long to recompile is because the idiotic stats maintenance code that my predecessors used built stats on every single column of every single table and this particular table has 139 columns. Yeah... working on that lovely issue, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)