Same SQL different Execution Plan!!

Question

Post reply

Same SQL different Execution Plan!!

Andrew Barton

SSC-Addicted

Points: 490
More actions
November 2, 2004 at 8:09 am

#61358

I have the following basic sql statements that I was running for testing purposes. One version uses parameters and the other doesn't. I would expect them to generate the same execution plan but they didn't. The statement using the parameters took on average three times as long to run as it created two hash Match/inner join steps. I then rewrote the parameter version into a stored procedure and it then created the same execution plan as the non parameter statement and took around the same time to run. Why did the the version with parameters generate a different plan when I ran it in QA?
Andrew Barton

DECLARE @application varchar(20),
  @datefrom datetime,
  @dateto datetime
SET @application = 'App1'
SET @datefrom = '2004-03-01'
SET @dateto = '2004-04-01'
SELECT [Application],
  cast(convert(varchar(11),[Interaction_Start], 106) as smalldatetime) as 'Interaction_start',
  Interaction_type,
  Count(*) as 'No.'
FROM dbo.[Interactions] i
WHERE Application = @application
AND (Interaction_Start >= @datefrom and Interaction_Start < @dateto)
Group by Application, cast(convert(varchar(11),[Interaction_Start], 106) as smalldatetime), Interaction_type
order by Application, cast(convert(varchar(11),[Interaction_Start], 106) as smalldatetime), Interaction_Type DESC
SELECT [Application],
  cast(convert(varchar(11),[Interaction_Start], 106) as smalldatetime) as 'Interaction_start',
  Interaction_type,
  Count(*) as 'No.'
FROM dbo.[Interactions]
WHERE Application = 'App1'
and (Interaction_Start >= '2004-03-01' and Interaction_Start < '2004-04-01')
Group by Application, cast(convert(varchar(11),[Interaction_Start], 106) as smalldatetime), Interaction_type
order by Application, cast(convert(varchar(11),[Interaction_Start], 106) as smalldatetime), Interaction_Type DESC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

What were the hash hash Match/inner join steps?

A couple of things to investigate are:

You have aliased the table in the first sql script as i:

FROM dbo.[Interactions] i

Also, you are storing the variable in a varchar(20) but you are only using 4 chars. Try using nvarchar(20) and see if it makes a difference, although I very much doubt it.

I have a feeling that it is something to do with the dates, but I couldn't tell you more without looking at the exact execution plan.

------------------------------
The Users are always right - when I'm not wrong!

Charles Otten Default port Points: 1475 More actions · Answer 2

I have to agree with Jonathan in that it is very difficult to suggest anything without seeing execution plan (which is obviously not an option here).

I vaguely remember an article about index usage and parameters in SQL Server Magazine (I live on that magazine). I will research to try and remember, but as a quick suggestion since u are casting many things into a smalldatetime i suggest defining your parameters as such.

If the phone doesn't ring...It's me.

Andrew Barton SSC-Addicted Points: 490 More actions · Answer 3

I already looked at the dates and tried them as datetime also tried them as varchar. Added times to the dates but it always gives the same execution plan. I run DBCC DROPCLEANBUFFERS before each run to make sure it gives a new execution plan. This is the plan that was generated for the SQL code with parameters

|--Sort(ORDER BY([Expr1001] ASC, .[Interaction_Type] DESC))

|--Compute Scalar(DEFINE([Expr1002]=Convert([Expr1007])))

|--Hash Match(Aggregate, HASH([Expr1001], .[Interaction_Type]), RESIDUAL([Expr1001]=[Expr1001] AND .[Interaction_Type]=.[Interaction_Type]) DEFINE([Expr1007]=COUNT(*), .[Application]=ANY(.[Application])))

|--Compute Scalar(DEFINE[Expr1001]=Convert(Convert(.[Interaction_Start]))))

|--Hash Match(Inner Join, HASH([Bmk1000])=([Bmk1000]), RESIDUAL[Bmk1000]=[Bmk1000]))

|--Hash Match(Inner Join, HASH([Bmk1000])=([Bmk1000]), RESIDUAL([Bmk1000]=[Bmk1000]))

| |--Index Seek(OBJECT([ReportTesting].[dbo].[Interactions].[IX_Interactions_Interaction_Start] AS ), SEEK(.[Interaction_Start] >= [@datefrom] AND .[Interaction_Start] < [@dateto]) ORDERED FORWARD)

| |--Index Seek(OBJECT([ReportTesting].[dbo].[Interactions].[IX_Interactions_Application] AS ), SEEK(.[Application]=[@application]) ORDERED FORWARD)

|--Index Scan(OBJECT([ReportTesting].[dbo].[Interactions].[IX_Interactions_Interaction_Type] AS ))

and this is the plan for the sql without parameters

|--Sort(ORDER BY([Expr1001] ASC, .[Interaction_Type] DESC))

|--Compute Scalar(DEFINE([Expr1002]=Convert([Expr1007])))

|--Hash Match(Aggregate, HASH([Expr1001], .[Interaction_Type]), RESIDUAL([Expr1001]=[Expr1001] AND .[Interaction_Type]=.[Interaction_Type]) DEFINE([Expr1007]=COUNT(*), .[Application]=ANY(.[Application])))

|--Compute Scalar(DEFINE[Expr1001]=Convert(Convert(.[Interaction_Start]))))

|--Clustered Index Scan(OBJECT([ReportTesting].[dbo].[Interactions].[IX_Interactions_Billing_Ref] AS ), WHERE(.[Interaction_Start]>='Mar 1 2004 12:00AM' AND .[Interaction_Start]<'Apr 1 2004 12:00AM') AND .[Application]='App1'))

The second plan is also what is generated when you convert the first sql statement to a stored procedure and pass in exactly the same variables.

regards

Andrew Barton