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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy