Tag Info

New answers tagged

6

Kris, •Do we really think that we will see a measurable performance gain here? If so, how can we quantify it aside from looking at the space used in the query above? That depends, but my gut instinct with the data you've given is - no. Sure, you'll potentially save some of that space as a plan stub will still take memory just not all that much ...


3

First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything. Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached. When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc ...


5

Most likely, either: The state of the database (e.g. statistics) changed since the cached plan was first compiled, causing a recompilation; or Your SSMS connection uses different settings (e.g. ANSI_NULLS) from those used by the connection that cached the plan (so it could not be reused); or It was an ad-hoc batch and the text of your query did not exactly ...


-2

For completness see my solution. To replace the simple command: SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (...) there is fast and ugly one: DECLARE @IDZapisovac int = 1 DECLARE @MinCas DateTime2(2) = NULL DECLARE @IDUkazatel smallint DECLARE @Cas DateTime2(2) = NULL DECLARE helper CURSOR FOR SELECT IDUkazatel FROM ... OPEN helper FETCH NEXT ...


7

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel. In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning ...



Top 50 recent answers are included