July 14, 2011 at 2:21 am
Hello All,
I need Help Needed for to Improve SQL Query Performance, the code is as bellow,
Create Table #DataHistory
(
HistoryId INT,
ID INT,
LetterNo INT,
Letter VArchar(2)
)
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(1,1,1,'C')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(2,1,1,'C')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(3,1,2,'B')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(4,1,3,'A')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(5,1,2,'B')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(6,1,2,'B')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(7,1,1,'C')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(8,1,6,'W')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(9,1,6,'W')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(10,2,2,'B')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(11,2,2,'B')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(12,2,2,'B')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(13,2,6,'W')
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter) VALUES(14,2,6,'W')
SELECTDISTINCT OM.ID,Sequence
FROM #DataHistory OM
OUTER APPLY
(
SELECT
STUFF((
SELECT ',' + LTRIM(RTRIM(CONVERT(VARCHAR(1000),O.Letter )))
FROM #DataHistory O
WHERE O.ID = OM.ID
GROUP BY O.HistoryId,O.Letter
FOR XML PATH(''))
,1,1,'') AS Sequence
) S
DROP TABLE #DataHistory
I am trying to improve performance for the code block
SELECTDISTINCT OM.ID,Sequence
FROM #DataHistory OM
OUTER APPLY
(
SELECT
STUFF((
SELECT ',' + LTRIM(RTRIM(CONVERT(VARCHAR(1000),O.Letter )))
FROM #DataHistory O
WHERE O.ID = OM.ID
GROUP BY O.HistoryId,O.Letter
FOR XML PATH(''))
,1,1,'') AS Sequence
) S
Thank you
Yatish
July 14, 2011 at 10:14 am
I just removed the GROUP BY as well as the CONVERT() function. The result is still identical:
SELECTDISTINCT OM.ID,Sequence
FROM #DataHistory OM
OUTER APPLY
(
SELECT
STUFF((
SELECT ',' + LTRIM(RTRIM(O.Letter ))
FROM #DataHistory O
WHERE O.ID = OM.ID
--GROUP BY O.HistoryId,O.Letter
FOR XML PATH(''))
,1,1,'') AS Sequence
) S
July 15, 2011 at 1:32 am
Hello LutzM,
I have tried your suggestion and it has made impact on the performance when I looked at the execution plan. Thank you for your Help.
I have also another code block for which I am working on to improve performance,
I request everyone to suggest me the problem for bad performance and solution to fix the issue. the code block is as below,
Create Table #Data
(
ID INT,
LetterNo INT,
CDate DateTime
)
Create Table #DataHistory
(
HistoryId INT,
ID INT,
LetterNo INT,
Letter VArchar(2),
CDate DateTime,
NDate DateTime Null
)
INSERT INTO #Data(ID,LetterNo,CDate) VALUES(1,1,GetDate())
INSERT INTO #Data(ID,LetterNo,CDate) VALUES(2,3,GetDate())
INSERT INTO #Data(ID,LetterNo,CDate) VALUES(3,2,GetDate())
INSERT INTO #Data(ID,LetterNo,CDate) VALUES(4,1,GetDate())
INSERT INTO #Data(ID,LetterNo,CDate) VALUES(5,5,GetDate())
INSERT INTO #Data(ID,LetterNo,CDate) VALUES(6,4,GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(1,1,1,'C',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(2,1,1,'C',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(3,1,2,'B',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(4,3,3,'A',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(5,3,2,'B',GetDate(),null)
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(6,4,2,'B',GetDate(),null)
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(7,5,1,'C',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(8,5,6,'W',GetDate(),null)
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(9,1,6,'W',GetDate(),null)
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(10,2,2,'B',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(11,2,2,'B',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(12,6,2,'B',GetDate(),null)
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(13,2,6,'W',GetDate(),GetDate())
INSERT INTO #DataHistory(Historyid,ID,LetterNo,letter,CDate,NDate) VALUES(14,2,6,'W',GetDate(),null)
;WITH History AS
(
SELECTOH.Historyid,
OH.ID,
OH.LetterNo,
OH.NDate,
OH.CDate,
OH.letter,
rn1 = ROW_NUMBER() OVER(PARTITION BY OH.ID ORDER BY OH.Historyid),
rn2 = ROW_NUMBER() OVER(PARTITION BY OH.ID,OH.letter ORDER BY OH.Historyid)
FROM #DataHistory OH
JOIN #Data O ON O.ID = OH.ID
)
SELECTOH.Historyid,
OH.ID,
OH.NDate,
OH.CDate,
OH.letter,
SequenceNumber = DENSE_RANK() OVER(PARTITION BY ID ORDER BY (rn2-rn1) desc, LetterNo desc)
INTO #History
FROM History OH
SELECT * FROM #History
DROP TABLE #Data
DROP TABLE #DataHistory
DROP TABLE #History
I am looking at the performance for the block
;WITH History AS
(
SELECTOH.Historyid,
OH.ID,
OH.LetterNo,
OH.NDate,
OH.CDate,
OH.letter,
rn1 = ROW_NUMBER() OVER(PARTITION BY OH.ID ORDER BY OH.Historyid),
rn2 = ROW_NUMBER() OVER(PARTITION BY OH.ID,OH.letter ORDER BY OH.Historyid)
FROM #DataHistory OH
JOIN #Data O ON O.ID = OH.ID
)
SELECTOH.Historyid,
OH.ID,
OH.NDate,
OH.CDate,
OH.letter,
SequenceNumber = DENSE_RANK() OVER(PARTITION BY ID ORDER BY (rn2-rn1) desc, LetterNo desc)
INTO #History
FROM History OH
Thank you
Yatish
July 15, 2011 at 9:20 am
Please post the index definitions you might have on your source tables.
I recommend to add an index on #DataHistory(ID,historyid) and #Data(ID).
Other than that, all I can think of to speed it up is the "quirky update" approach.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply