August 23, 2012 at 10:46 pm
I think this is another way not yet suggested.
SELECT TranID,
(
SELECT OriginCode + ''
FROM #Test t1
WHERE t1.TranID = t2.TranID
ORDER BY ID
FOR XML PATH('')) +
(
SELECT TOP 1 DestinationCode
FROM #Test t1
WHERE t1.TranID = t2.TranID
ORDER BY ID DESC)
FROM #Test t2
GROUP BY TranID
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 23, 2012 at 11:11 pm
Let's try a small test harness, shall we:
CREATE TABLE #Test
(ID int NOT NULL IDENTITY(1,1) Primary key,
TranID int NOT NULL,
OriginCode varchar(10) NOT NULL,
DestinationCode varchar(10) NOT NULL
)
INSERT INTO #Test
SELECT 1 ,'AMD','DOH'
UNION ALL
SELECT 1 ,'DOH','NBO'
UNION ALL
SELECT 1 ,'NBO','ADD'
UNION ALL
SELECT 1 ,'ADD','DEL'
UNION ALL
SELECT 1 ,'DEL','AMD'
UNION ALL
SELECT 2 ,'IXJ','RMI'
;WITH Tally (n) AS (
SELECT TOP 999 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)
INSERT INTO #Test
SELECT TranID * 10000 + n
,OriginCode + CAST(n AS VARCHAR(10))
,DestinationCode + CAST(n AS VARCHAR(10))
FROM #Test
CROSS APPLY Tally
DECLARE @Holder1 INT, @Holder2 VARCHAR(4000)
PRINT '---- Dwain''s query'
SET STATISTICS TIME ON
SELECT @Holder1=TranID, @Holder2=
(
SELECT OriginCode + ''
FROM #Test t1
WHERE t1.TranID = t2.TranID
ORDER BY ID
FOR XML PATH('')) +
(
SELECT TOP 1 DestinationCode
FROM #Test t1
WHERE t1.TranID = t2.TranID
ORDER BY ID DESC)
FROM #Test t2
GROUP BY TranID
SET STATISTICS TIME OFF
PRINT '---- J Livingston''s query'
SET STATISTICS TIME ON
;with cte as
(
SELECT ID, TranID, OriginCode AS scode, 1 as col
FROM #Test
union all
SELECT ID, tranID, DestinationCode AS scode, 2 as col
FROM #Test
),
cte2 as
(
select DISTINCT DENSE_RANK() over (partition by tranid order by (Id+col)) dr, tranid, scode from cte
)
SELECT
@Holder1= tranid, @Holder2=
(
SELECT sCode + ''
FROM cte2 p2
WHERE p1.tranid = p2.tranid
FOR XML PATH('')
)
FROM cte2 p1
GROUP BY tranid
SET STATISTICS TIME OFF
PRINT '---- Cadavre''s improvement'
SET STATISTICS TIME ON
;WITH CTE AS (SELECT DISTINCT TranID,
CASE WHEN a.N = 1 THEN OriginCode ELSE DestinationCode END AS code,
DENSE_RANK() OVER (PARTITION BY TranID ORDER BY ID+a.N) AS pos
FROM #Test
CROSS JOIN (SELECT 1 UNION ALL SELECT 2)a(N)
)
SELECT @Holder1=TranID,@Holder2=(SELECT code + ''
FROM CTE b
WHERE a.TranID = b.TranID
ORDER BY b.pos
FOR XML PATH('')) --AS overallCode
FROM CTE a
GROUP BY TranID;
SET STATISTICS TIME OFF
DROP TABLE #Test
Timing results:
---- Dwain's query
SQL Server Execution Times:
CPU time = 920 ms, elapsed time = 937 ms.
---- J Livingston's query
SQL Server Execution Times:
CPU time = 24867 ms, elapsed time = 25251 ms.
---- Cadavre's improvement
SQL Server Execution Times:
CPU time = 30841 ms, elapsed time = 31235 ms.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 24, 2012 at 4:03 am
There is a faster way though.
SELECT TranID, RouteInfo=
(
SELECT OriginCode + ''
FROM (
SELECT OriginCode
FROM (
SELECT TOP 1 OriginCode
FROM #Test t1
WHERE t1.TranID = t2.TranID
ORDER BY ID) a
UNION ALL
SELECT DestinationCode
FROM (
SELECT TOP 100 PERCENT DestinationCode
FROM #Test t1
WHERE t1.TranID = t2.TranID
ORDER BY ID) b) c
FOR XML PATH(''))
FROM #Test t2
GROUP BY TranID
---- Dwain's query
SQL Server Execution Times:
CPU time = 983 ms, elapsed time = 985 ms.
---- Dwain's improved query
SQL Server Execution Times:
CPU time = 358 ms, elapsed time = 369 ms.
---- J Livingston's query
SQL Server Execution Times:
CPU time = 24679 ms, elapsed time = 25003 ms.
---- Cadavre's improvement
SQL Server Execution Times:
CPU time = 30358 ms, elapsed time = 30649 ms.
The code is not quite as elegant though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 24, 2012 at 4:30 am
whar results are supposed to be returned for this data set?
CREATE TABLE #Test
(ID int NOT NULL IDENTITY(1,1) Primary key,
TranID int NOT NULL,
OriginCode varchar(5) NOT NULL,
DestinationCode varchar(5) NOT NULL
)
GO
INSERT INTO #Test
SELECT 201205 ,'AMD','DOH'
UNION ALL
SELECT 201205 ,'xxx','NBO' -- note change
UNION ALL
SELECT 201205 ,'NBO','ADD'
UNION ALL
SELECT 201205 ,'ADD','DEL'
UNION ALL
SELECT 201205 ,'DEL','AMD'
UNION ALL
SELECT 201206 ,'IXJ','RMI'
GO
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 24, 2012 at 4:32 am
J Livingston SQL (8/24/2012)
whar results are supposed to be returned for this data set?
CREATE TABLE #Test
(ID int NOT NULL IDENTITY(1,1) Primary key,
TranID int NOT NULL,
OriginCode varchar(5) NOT NULL,
DestinationCode varchar(5) NOT NULL
)
GO
INSERT INTO #Test
SELECT 201205 ,'AMD','DOH'
UNION ALL
SELECT 201205 ,'xxx','NBO' -- note change
UNION ALL
SELECT 201205 ,'NBO','ADD'
UNION ALL
SELECT 201205 ,'ADD','DEL'
UNION ALL
SELECT 201205 ,'DEL','AMD'
UNION ALL
SELECT 201206 ,'IXJ','RMI'
GO
I don't think that change makes sense because these appear to be flight segments between airports with TranID 201205 being a round trip.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 26, 2012 at 11:49 pm
No +1 for my performance improvements? :crying:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 31, 2012 at 4:48 am
dwain.c (8/26/2012)
No +1 for my performance improvements? :crying:
Sorry dwain, but that smacks way too much of the stackoverflow method for answering questions. Since I hate the atmosphere over there, I flat out refuse to participate in anything that smells remotely similar when posting in sqlservercentral. If I didn't say so in the PM (I think I did, but I clear out my folders too regularly to be able to check), then I meant to say that it was much better than the other offerings. I'm still surprised that my method comes out slower than J Livingston's.
By the way, did you test my ugly recursive CTE ?
August 31, 2012 at 5:41 am
Cadavre (8/31/2012)
dwain.c (8/26/2012)
No +1 for my performance improvements? :crying:Sorry dwain, but that smacks way too much of the stackoverflow method for answering questions. Since I hate the atmosphere over there, I flat out refuse to participate in anything that smells remotely similar when posting in sqlservercentral. If I didn't say so in the PM (I think I did, but I clear out my folders too regularly to be able to check), then I meant to say that it was much better than the other offerings. I'm still surprised that my method comes out slower than J Livingston's.
By the way, did you test my ugly recursive CTE ?
Actually, no I didn't test the rCTE, mainly because I think you said you didn't think it would perform too well. So I took your word, making the assumption that you thought the improved version J L's would be better.
Sorry if I overflowed your stack!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 31, 2012 at 5:57 am
dwain.c (8/31/2012)
Actually, no I didn't test the rCTE, mainly because I think you said you didn't think it would perform too well. So I took your word, making the assumption that you thought the improved version J L's would be better.Sorry if I overflowed your stack!
Yeah, I would say the recursive CTE should be the worst of the bunch. But since I seem to be incorrect about about my suggested improvement to J Livingston's query, who can possibly take my word for it? :hehe:
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply