2005 query plan problem: embedded values vs. variables

Last Post 13 May 2008 01:14 PM by SQLUSA. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
AndyMoore
New Member
New Member

--
12 May 2008 08:08 AM
Hi all,

I'm having trouble with with a query in 2005 that is picking a vastly different query plan from 2000. I can get it to use a sensible plan in 2005 either by hinting OPTION (RECOMPILE) or by hard-coding values into the script rather than using variables, but with variables it's horrible - can also get a vaguely decent plan hinting indexes, but not as good as RECOMPILE.

Unfortunately the SQL in question is generated by a third party app., so hints are out of the question as a fix. When I've come across this kind of thing in the past it's usually been a datatype mismatch or stats related, but I've tried rebuilding both indexes & stats to no avail and I can't see any datatype mismatches. Also tried on a box running CU7 with no joy. The box was originally set PARAMETERIZATION: FORCED, but has since been set back to SIMPLE.

The 'good' plan ends up using clustered indexes & takes 5 seconds when cached, the 'bad' plan uses unclustered indexes that cause thousands of scans & takes 5 minutes when cached.

Can anyone tell me what incredibly stupid detail I'm overlooking (hopefully)?

thanks all,


Andy


Script follows (can you tell it's machine generated? ;-)), unfortunately tuning the SQL isn't much of a goer due to this - I'm hoping for a magic bullet.


DECLARE @FromAccount CHAR(10)
DECLARE @ToAccount CHAR(10)
DECLARE @FromDate PSDATE
DECLARE @ToDate PSDATE

set @FromAccount = '40000'
set @ToAccount = '49999'
set @FromDate = '2007-05-01'
set @ToDate = '2008-04-30'

SELECT A.BUSINESS_UNIT,
C.ACCOUNT,
B.DESCR,
C.OPERATING_UNIT,
C.PRODUCT,
C.PROJECT_ID,
C.ACTIVITY_ID,
B.SETID,
B.ACCOUNT,
(CONVERT(CHAR(10),B.EFFDT,121))
FROM PS_JRNL_HEADER A,
PS_JRNL_LN C,
PS_GL_ACCOUNT_TBL B
WHERE
A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.JOURNAL_ID = C.JOURNAL_ID
AND A.JOURNAL_DATE = C.JOURNAL_DATE
AND A.UNPOST_SEQ = C.UNPOST_SEQ
AND A.BUSINESS_UNIT = 'GL140'
AND (C.ACCOUNT >= @FromAccount
AND C.ACCOUNT <= @ToAccount)
AND C.OPERATING_UNIT LIKE '%'
AND C.PROJECT_ID LIKE 'KEN%'
AND C.ACTIVITY_ID LIKE '%'
AND (C.LEDGER = 'ACTUALS'
AND ((C.ACCOUNT NOT BETWEEN '30000' AND '59999')
OR (C.JOURNAL_DATE >=@FromDate
AND C.ACCOUNT BETWEEN '30000' AND '59999'))
OR (C.LEDGER = 'BUDGETS'
AND C.JOURNAL_DATE >= @FromDate
AND C.SCENARIO LIKE '%'))
AND C.JOURNAL_DATE <= @ToDate
AND B.ACCOUNT = C.ACCOUNT
AND B.EFFDT = (SELECT MAX(C.EFFDT)
FROM PS_GL_ACCOUNT_TBL C,
PS_CAL_DETP_TBL D
WHERE C.EFFDT = (SELECT MAX(C_ED.EFFDT)
FROM PS_GL_ACCOUNT_TBL C_ED
WHERE C.SETID = C_ED.SETID
AND C.ACCOUNT = C_ED.ACCOUNT
AND C_ED.EFFDT <= @ToDate)
AND C.SETID = D.SETID
AND B.ACCOUNT = C.ACCOUNT
AND D.CALENDAR_ID = 'OX'
AND D.END_DT <= @ToDate)

GROUP BY A.BUSINESS_UNIT,C.ACCOUNT,B.DESCR,C.OPERATING_UNIT,
C.PRODUCT,C.PROJECT_ID,C.ACTIVITY_ID,B.SETID,
B.ACCOUNT,(CONVERT(CHAR(10),B.EFFDT,121))
ORDER BY 1,
2,
4,
5,
6,
7
SQLUSA
New Member
New Member

--
12 May 2008 08:29 AM
Andy,

Some general performance tuning guidelines:

1. Make sure your indexes are A1 OK
2. Check for missing indexes:

select object_name(mid.object_id) as TableName,

migs.user_seeks as Seeks,

mid.equality_columns as Equality
from sys.dm_db_missing_index_details mid

join sys.dm_db_missing_index_groups mig

on mid.index_handle=mig.index_handle

join sys.dm_db_missing_index_group_stats migs

on migs.group_handle=mig.index_group_handle

where database_id=db_id()

3. If you can change the query, use CTE-s to restructure/reengineer it. By doing so, you can lead the database engine to the "fast path".

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
12 May 2008 09:15 AM
<EDITED>
Incorrect/irrelavent technical content removed




AndyMoore
New Member
New Member

--
13 May 2008 01:37 AM
Many thanks all. Unfortunately this is adhoc code generated by a third-party app, so any additions to the code (including hints) are impossible, but plan guides sound interesting, assuming the generator will create the same code when different criteria are passed in.

After having no joy getting the optimizer to use the existing 'good' indexes I've moved on to creating new covered indexes for the query which bring it down to sub second, but because of the system constraints I need to confirm whether I'm able to take these live.

Thanks again,


Andy
SQLUSA
New Member
New Member

--
13 May 2008 02:47 AM
Andy,

How did you create the "covered indexes"?

SQL Server 2005 has a special facility for it: INCLUDE non-key columns in indexes. It works real well.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Performance Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
AndyMoore
New Member
New Member

--
13 May 2008 08:24 AM
Kalman - Thought INCLUDE was useful to extend indexes beyond the usual size limits & to allow datatypes that you couldn't usually cover, do they have more general performance benefits?

GunneyK - I've come into the process part way through to a certain extent, but Forced appeared to be causing reuse of poor plans and was dropped for that reason. I've tried this query against both Simple & Forced, doesn't appear to make any difference.

Major difficulty with the system is that it's a horrible generic model (55,000+ tables, 30,000 views, no procs) that executes all its SQL via server cursors (sp_cursorprep & sp_cursorexec). This makes it difficult to trace against because the cursorexecs are the query, but the prep has the actual SQL statement, so you can't do usual monitoring based on duration etc. Basically getting actual SQL at all is a pain so every time someone finds something that performs poorly everyone jumps on it in isolation, without being aware of how the changes may affect other queries.

Some performance testing before upgrading would have been useful in this instance...

Still hoping for a dumb environmental problem that will magic bullet all the problems if anyone can think of one!

cheers,


Andy
SQLUSA
New Member
New Member

--
13 May 2008 10:07 AM
Andy,

The advantage of new SQL Server 2005 "covering index" design (INCLUDE non-key columns) is that the index is very efficient at the top levels (key column(s) only!). The non-key columns included only at the leaf level.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Performance Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/highperformance
SQLUSA
New Member
New Member

--
13 May 2008 01:14 PM
Russell,

In SQL Server 2000 when you include extra (non-key) columns in an index to satisfy a query without table read, they are all made into composite index columns, included in all levels of the B-Tree. Making the index less efficient, bigger also.

In SQL Server 2005, the non-key columns included only at the leaf-level. That means there is no "performance punishment" for including them. The index tree above the leaf-level is very effecient.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
SwePeso
New Member
New Member

--
13 May 2008 01:17 PM
This query is quite normal. One thing strikes me though.

SELECT A.BUSINESS_UNIT,
C.ACCOUNT,
B.DESCR,
C.OPERATING_UNIT,
C.PRODUCT,
C.PROJECT_ID,
C.ACTIVITY_ID,
B.SETID,
B.ACCOUNT,
CONVERT(CHAR(10), B.EFFDT, 121)
FROM PS_JRNL_HEADER A
inner join PS_JRNL_LN C on C.BUSINESS_UNIT = A.BUSINESS_UNIT
and C.JOURNAL_ID = A.JOURNAL_ID
and C.JOURNAL_DATE = A.JOURNAL_DATE
and C.UNPOST_SEQ = A.UNPOST_SEQ
and C.ACCOUNT between @FromAccount AND @ToAccount
AND C.OPERATING_UNIT LIKE '%'
AND C.PROJECT_ID LIKE 'KEN%'
AND C.ACTIVITY_ID LIKE '%'
AND C.JOURNAL_DATE <= @ToDate
inner join PS_GL_ACCOUNT_TBL B on B.ACCOUNT = C.ACCOUNT
WHERE A.BUSINESS_UNIT = 'GL140'
AND (
C.LEDGER = 'ACTUALS'
AND (C.ACCOUNT NOT BETWEEN '30000' AND '59999' OR C.JOURNAL_DATE >= @FromDate AND C.ACCOUNT BETWEEN '30000' AND '59999')
OR
C.LEDGER = 'BUDGETS'
AND C.JOURNAL_DATE >= @FromDate
AND C.SCENARIO LIKE '%'
)
AND B.EFFDT = ( SELECT MAX(C.EFFDT)
FROM PS_GL_ACCOUNT_TBL C
inner join PS_CAL_DETP_TBL D on D.SETID = C.SETID
WHERE C.EFFDT = ( SELECT MAX(C_ED.EFFDT)
FROM PS_GL_ACCOUNT_TBL C_ED
WHERE C.SETID = C_ED.SETID
AND C.ACCOUNT = C_ED.ACCOUNT
AND C_ED.EFFDT <= @ToDate
)
AND D.CALENDAR_ID = 'OX'
AND D.END_DT <= @ToDate
)
GROUP BY A.BUSINESS_UNIT,
C.ACCOUNT,
B.DESCR,
C.OPERATING_UNIT,
C.PRODUCT,
C.PROJECT_ID,
C.ACTIVITY_ID,
B.SETID,
B.ACCOUNT,
CONVERT(CHAR(10),B.EFFDT,121)
ORDER BY 1,
2,
4,
5,
6,
7

And that part is the nested correlated subquery!
What on earth was going on when that part was written?


AND B.EFFDT = ( SELECT MAX(C.EFFDT)
FROM PS_GL_ACCOUNT_TBL C
inner join PS_CAL_DETP_TBL D on D.SETID = C.SETID
WHERE C.EFFDT = ( SELECT MAX(C_ED.EFFDT)
FROM PS_GL_ACCOUNT_TBL C_ED
WHERE C.SETID = C_ED.SETID
AND C.ACCOUNT = C_ED.ACCOUNT
AND C_ED.EFFDT <= @ToDate
)
AND D.CALENDAR_ID = 'OX'
AND D.END_DT <= @ToDate
)

Replace this part and you will get the speed you need!
SwePeso
New Member
New Member

--
13 May 2008 01:23 PM
Try this one.


SELECT A.BUSINESS_UNIT,
C.ACCOUNT,
B.DESCR,
C.OPERATING_UNIT,
C.PRODUCT,
C.PROJECT_ID,
C.ACTIVITY_ID,
B.SETID,
B.ACCOUNT,
CONVERT(CHAR(10), B.EFFDT, 121)
FROM PS_JRNL_HEADER A
inner join PS_JRNL_LN C on C.BUSINESS_UNIT = A.BUSINESS_UNIT
and C.JOURNAL_ID = A.JOURNAL_ID
and C.JOURNAL_DATE = A.JOURNAL_DATE
and C.UNPOST_SEQ = A.UNPOST_SEQ
and C.ACCOUNT between @FromAccount AND @ToAccount
AND C.OPERATING_UNIT LIKE '%'
AND C.PROJECT_ID LIKE 'KEN%'
AND C.ACTIVITY_ID LIKE '%'
AND C.JOURNAL_DATE <= @ToDate
AND C.EFFDT <= @ToDate
inner join PS_GL_ACCOUNT_TBL B on B.ACCOUNT = C.ACCOUNT
inner join (
SELECT SETID,
MAX(C.EFFDT) AS EFFDT
FROM PS_CAL_DETP_TBL
WHERE CALENDAR_ID = 'OX'
AND END_DT <= @ToDate
group by SETID
) AS d ON D.SETID = C.SETID
WHERE A.BUSINESS_UNIT = 'GL140'
AND (
C.LEDGER = 'ACTUALS'
AND (C.ACCOUNT NOT BETWEEN '30000' AND '59999' OR C.JOURNAL_DATE >= @FromDate AND C.ACCOUNT BETWEEN '30000' AND '59999')
OR
C.LEDGER = 'BUDGETS'
AND C.JOURNAL_DATE >= @FromDate
AND C.SCENARIO LIKE '%'
)
AND B.EFFDT = d.EFFDT
GROUP BY A.BUSINESS_UNIT,
C.ACCOUNT,
B.DESCR,
C.OPERATING_UNIT,
C.PRODUCT,
C.PROJECT_ID,
C.ACTIVITY_ID,
B.SETID,
B.ACCOUNT,
CONVERT(CHAR(10),B.EFFDT,121)
ORDER BY 1,
2,
4,
5,
6,
7
SQLUSA
New Member
New Member

--
13 May 2008 02:07 PM
Peter,

The query is coming from an sql generator as Russell mentioned it. Cannot be change.

Only server properties, indexes, and similar can be changed.

Andy said covering index helped with this query.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/order2005highperformance
You are not authorized to post a reply.

Acceptable Use Policy