CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2query-performance
kubal5003
kubal5003

Reputation: 7254

SQL Server query plan generation optimization

I've got a quite complex view that has about 100 subqueries in it's definition.

Simple statement like:

SELECT * FROM MyView

take 2 seconds to generate the plan and execute query.Subsequent selects when the plan is cached take less than 1ms to execute.

This situation would be alright if I had only a couple of queries - performance hit only once is acceptable. The problem is that our ORM generates paging queries with parameters using CTE. Changing parameter values(pages) causes query plan recalculation - in this case this unfortunately takes about 4 seconds!

Let's add filtering, sorting and you get the idea of what happens..

What can I do to cut down the query plan creation times or make less of them or optimize this in any other manner?

@MartinSmith "SQL Server doesn't generate a plan for each parameter value except if the text has somehow changed"

I have a query like this (I've put stars here instead of more than 120 fields list):

DECLARE @low int = 20;
DECLARE @high int = 300;

WITH __actualSet
AS (
SELECT *
    ,ROW_NUMBER() OVER (
        ORDER BY CURRENT_TIMESTAMP
        ) AS __rowcnt
FROM (
    SELECT TOP 5000 *
    FROM [dbo].[Project] [LPA_L1]
    ORDER BY [LPA_L1].[CreatedOn] ASC
    ) AS _tmpSet
)
SELECT *
FROM __actualSet
WHERE [__rowcnt] > @low
AND [__rowcnt] <= @high
ORDER BY [__rowcnt] ASC

The first time I run this query ~4s. The second time ~1ms. When I change the param values - once again 4s. Maybe I'm misinterpreting something here?

Upvotes: 1

Views: 686

Answers (1)

Martin Smith
Martin Smith

Reputation: 453047

To test in SSMS and reuse the same plan for different values you need to parameterise the query and execute it with sp_executesql

DECLARE @low int = 20;
DECLARE @high int = 300;

EXEC sp_executesql N'
WITH __actualSet
AS (
SELECT *
    ,ROW_NUMBER() OVER (
        ORDER BY CURRENT_TIMESTAMP
        ) AS __rowcnt
FROM (
    SELECT TOP 5000 *
    FROM [dbo].[Project] [LPA_L1]
    ORDER BY [LPA_L1].[CreatedOn] ASC
    ) AS _tmpSet
)
SELECT *
FROM __actualSet
WHERE [__rowcnt] > @low
AND [__rowcnt] <= @high
ORDER BY [__rowcnt] ASC

', N'@low INT, @high INT', @low = @low, @high = @high

Upvotes: 1

Related Questions