June 30, 2009 at 6:35 am
We are migrating a server from SQL 2000 to SQL 2005. There is a SP runs fine in sql 2000 but does not run in 2005 (not getting any results or errors). Its keep running without results
There server is 2005 Std SP3, database compatable level is 9. All indexes were rebuilt. All the statistics were updated.
Sorry for the crappy code. its part of a SP. Its very old system and i cant change it. All i can do is, changing the joins or minor modifications. I tried to re-write the subquery area and no results 🙁 Its works great in sql 2000.
DECLARE @STARTDATE AS CHAR(8)
SET @STARTDATE = '20090624'
SELECT SUBSTRING(A.TERM_ID, 1, 3) + 'Y' AS TERMID, MAX(B.DAT_TIM) AS LASTTRX
FROM dbo.TBL1 AS A JOIN
dbo.TBL2 AS B ON A.TERM_ID = SUBSTRING(B.TERM_ID, 2, 3)
WHERE CONVERT(char(8), B.DAT_TIM, 112) = CONVERT(DATETIME, @STARTDATE) - 1 AND B.REGN_ID <> 'USAD'
AND A.TERM_ID NOT IN ('106', '111', '113', '119', '108', '118')
AND A.TERM_ID NOT IN (SELECT SUBSTRING(TERM_ID, 2, 3)-- AS Expr1
FROM dbo.TBL2 WHERE CONVERT(char(8), DAT_TIM, 112) = @STARTDATE AND (TERM_FIID = 'KMET' OR TERM_FIID = 'BUYN'))
GROUP BY A.TERM_ID
June 30, 2009 at 7:39 am
All those functions on the columns in the JOINS are going to kill performance. They should have in 2000 as well. Can you post the execution plan?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 30, 2009 at 10:07 am
You could start by making B.DAT_TIM SARGable, something like:
DECLARE @STARTDATE AS datetime
SET @STARTDATE = '20090624'
SELECT SUBSTRING(A.TERM_ID, 1, 3) + 'Y' AS TERMID
,MAX(B.DAT_TIM) AS LASTTRX
FROM dbo.TBL1 AS A
JOIN dbo.TBL2 AS B
ON A.TERM_ID = SUBSTRING(B.TERM_ID, 2, 3)
WHERE B.DAT_TIM >= @STARTDATE - 1
AND B.DAT_TIM < @STARTDATE
AND B.REGN_ID 'USAD'
AND A.TERM_ID NOT IN ('106', '111', '113', '119', '108', '118')
AND A.TERM_ID NOT IN (
SELECT SUBSTRING(B1.TERM_ID, 2, 3)
FROM dbo.TBL2 B1
WHERE B1.DAT_TIM >= @STARTDATE
AND B1.DAT_TIM < @STARTDATE + 1
AND B1.TERM_FIID IN ('KMET', 'BUYN')
)
GROUP BY A.TERM_ID
You could then look at
either making SUBSTRING(B.TERM_ID, 2, 3) a calculated column on TBL2 and indexing it
or using a VIEW of TBL2 and having SUBSTRING(B.TERM_ID, 2, 3) as an additional indexed column.
This would speed up the JOIN.
June 30, 2009 at 12:07 pm
Thank you Ken and Grant. Right now, i dont have access to real data. i will check it as soon as possible and post the results.
The original query plan
|--Compute Scalar(DEFINE:([Expr1009]=substring([ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID],(1),(3))+'Y'))
|--Stream Aggregate(GROUP BY:([A].[TERM_ID]) DEFINE:([Expr1008]=MAX([ONLINE].[dbo].[TBL2].[DAT_TIM] as .[DAT_TIM])))
|--Nested Loops(Inner Join, WHERE:([ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]=[Expr1011]))
|--Nested Loops(Left Anti Semi Join, WHERE:([Expr1012] IS NULL OR [ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]=[Expr1012]))
| |--Clustered Index Scan(OBJECT:([ONLINE].[dbo].[TBL1].[PK_TBL1_1] AS [A]), WHERE:([ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]'106' AND [ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]'108' AND [ONLINE].[dbo].[TBL1]
| |--Compute Scalar(DEFINE:([Expr1012]=substring([ONLINE].[dbo].[TBL2].[TERM_ID],(2),(3))))
| |--Clustered Index Scan(OBJECT:([ONLINE].[dbo].[TBL2].[PK_TBL2]), WHERE:(CONVERT_IMPLICIT(datetime,CONVERT(char(8),[ONLINE].[dbo].[TBL2].[DAT_TIM],112),0)=[@STARTDATE] AND ([ONLINE].[dbo].[TBL2].[TERM_FIID]='BBYN' OR
|--Compute Scalar(DEFINE:([Expr1011]=substring([ONLINE].[dbo].[TBL2].[TERM_ID] as .[TERM_ID],(2),(3))))
|--Clustered Index Scan(OBJECT:([ONLINE].[dbo].[TBL2].[PK_TBL2] AS ), WHERE:([ONLINE].[dbo].[TBL2].[REGN_ID] as .[REGN_ID]'USAD' AND CONVERT_IMPLICIT(datetime,CONVERT(char(8),[ONLINE].[dbo].[TBL2].[DAT_TIM] as .
and the modified query by Ken, generated plan
|--Compute Scalar(DEFINE:([Expr1008]=substring([ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID],(1),(3))+'Y'))
|--Stream Aggregate(GROUP BY:([A].[TERM_ID]) DEFINE:([Expr1007]=MAX([ONLINE].[dbo].[TBL2].[DAT_TIM] as .[DAT_TIM])))
|--Nested Loops(Inner Join, WHERE:([ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]=[Expr1011]))
|--Nested Loops(Left Anti Semi Join, WHERE:([Expr1012] IS NULL OR [ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]=[Expr1012]))
| |--Clustered Index Scan(OBJECT:([ONLINE].[dbo].[TBL1].[PK_TBL1_1] AS [A]), WHERE:([ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]'106' AND [ONLINE].[dbo].[TBL1].[TERM_ID] as [A].[TERM_ID]'108' AND [ONLINE].[dbo].[TBL1]
| |--Compute Scalar(DEFINE:([Expr1012]=substring([ONLINE].[dbo].[TBL2].[TERM_ID] as [B1].[TERM_ID],(2),(3))))
| |--Clustered Index Seek(OBJECT:([ONLINE].[dbo].[TBL2].[PK_TBL2] AS [B1]), SEEK:([B1].[DAT_TIM] >= [@STARTDATE] AND [B1].[DAT_TIM] = [@STARTDATE]-'1900-01-02 00:00:00.000' AND .[DAT_TIM] < [@STARTDATE]), WHERE:([ONLINE].[dbo].[TBL2].[REGN_ID] as .[REGN_ID
June 30, 2009 at 12:13 pm
Well done Ken!
All those scans converted to nice seeks. Looks good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2009 at 12:33 am
Thanks Ken. The query plan looks great but query is generating arithmetic overflow error after the. let me fix it and give a feedback
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply