April 11, 2012 at 10:24 am
I wonder if any of you helpful folks could suggest any alternatives to cursor for the following issue :-
Consider a flight that is held as a transaction in 1 table, with 1 to many flight sectors in a sector table :-
flight_tran_table
flight_tran_id , (Other Tran Data)
1234, (Other Tran Data)
flight_sectors_table
tran_id sector_id dept arrive
1234 1 MAN LHR
1234 2 LHR JFK
1234 3 JFK LHR
1234 4 LHR MAN
I want a query that would show 1234, (Other Tran Data), MAN-LHR-JFK-LHR-MAN
My approach was a function fnGetFlightRoute that you passed in the flight_tran_id and passed out the return value of the routing, implementing a cursor within, that looped round that flight tran building up a concatenated string that started with the first dept of MAN, and within the loop added a '-' and every destination until the end of the cursor.
select flight_tran_table.flight_tran_id, (Other Tran Data), fnGetFlightRoute(flight_tran_table.flight_tran_id)
from flight_tran_table
My typical use of this would be to select a days worth of flight_trans from the table with say 2500 transactions in.
Assuming I have ruled out storing the flight routing in the transaction table at the point the transaction is created, leaves the Question :-
Q. If I do this on the fly what alternatives are there to my approach, and specifically any positive performance impacts.
Thanks for your time and thoughts.
April 11, 2012 at 10:47 am
Something like this?
IF OBJECT_ID('TEMPDB..#CONCAT_COLUMN_VALUES') IS NOT NULL
DROP TABLE #CONCAT_COLUMN_VALUES
CREATE TABLE #CONCAT_COLUMN_VALUES
(
GROUP_ID INT,
COL_VAL VARCHAR(5)
)
INSERT INTO #CONCAT_COLUMN_VALUES
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 1, 'C' UNION ALL
SELECT 2, 'D' UNION ALL
SELECT 2, 'E' UNION ALL
SELECT 3, 'F' UNION ALL
SELECT 4, 'G'
SELECT * FROM #CONCAT_COLUMN_VALUES
SELECT p1.GROUP_ID,
STUFF ( ( SELECT ','+COL_VAL
FROM #CONCAT_COLUMN_VALUES p2
WHERE p2.GROUP_ID = p1.GROUP_ID
ORDER BY COL_VAL
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)') , 1,1,SPACE(0)) AS Concat_Values
FROM #CONCAT_COLUMN_VALUES p1
GROUP BY p1.GROUP_ID ;
April 11, 2012 at 10:49 am
my example is close by using FORXML, but i don't get the "ending/last" airport.
flight_tran_idSkills
1234MAN-LHR-JFK-LHR
and my code example:
;WITH flight_tran_table (flight_tran_id , [(Other Tran Data)])
AS
(
SELECT 1234, '(Other Tran Data)'
),
flight_sectors_table(tran_id,sector_id,dept,arrive)
AS
(
SELECT 1234,1,'MAN','LHR' UNION ALL
SELECT 1234,2,'LHR','JFK' UNION ALL
SELECT 1234,3,'JFK','LHR' UNION ALL
SELECT 1234,4,'LHR','MAN'
)
SELECT flight_tran_id,
stuff(( SELECT '-'
+ dept
--+ CASE
-- WHEN dept = arrive
-- THEN ''
-- ELSE '-' + arrive
-- END
FROM flight_sectors_table s2
WHERE s2.tran_id= s1.flight_tran_id --- must match GROUP BY below
ORDER BY sector_id
FOR XML PATH('')
),1,1,'') as [Skills]
FROM flight_tran_table s1
GROUP BY s1.flight_tran_id --- without GROUP BY multiple rows are returned
ORDER BY s1.flight_tran_id
Lowell
April 11, 2012 at 11:01 am
Mine is incredibly close to yours Lowell. Instead of stuffing with dept I stuff arrive and just concatenated dept to the derived column.
;WITH flight_tran_table (flight_tran_id , OtherData)
AS
(
SELECT 1234, '(Other Tran Data)'
),
flight_sectors_table(tran_id,sector_id,dept,arrive)
AS
(
SELECT 1234,1,'MAN','LHR' UNION ALL
SELECT 1234,2,'LHR','JFK' UNION ALL
SELECT 1234,3,'JFK','LHR' UNION ALL
SELECT 1234,4,'LHR','MAN'
)
select flight_tran_id, OtherData, MyResult
from
(
select ft.flight_tran_id, ft.OtherData
, fs.dept + '-' + STUFF((select '-' + arrive
from flight_sectors_table fs2
where fs2.tran_id = fs.tran_id
for xml path('')), 1, 1, '') as MyResult
, ROW_NUMBER() over (partition by ft.flight_tran_id order by fs.sector_id) as RowNum
from flight_tran_table ft
join flight_sectors_table fs on ft.flight_tran_id = fs.tran_id
) x
where x.RowNum = 1
order by flight_tran_id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 11:06 am
Sean Lange (4/11/2012)
Mine is incredibly close to yours Lowell. Instead of stuffing with dept I stuff arrive and just concatenated dept to the derived column.{snip}
sheesh Sean; now that YOU did it, the complete, accurate solution is more than obvious.
Thanks!
saved your example to my snippets; hopefully i'll remember that in the future.
Lowell
April 12, 2012 at 2:20 am
Thanks for the suggestions.
I have run the code posted by Sean on my test dataset and it returns 2572 rows in 55 seconds.
My cursor function is returning the same dataset and results in 1m 4 seconds so 9 seconds quicker.
Sorry for not posting the test data creation queries.
April 12, 2012 at 2:56 am
Here's an alternate to Sean's solution that appears to have a better query plan.
;WITH flight_tran_table (flight_tran_id , OtherData)
AS
(
SELECT 1234, '(Other Tran Data)'
),
flight_sectors_table(tran_id,sector_id,dept,arrive)
AS
(
SELECT 1234,1,'MAN','LHR' UNION ALL
SELECT 1234,2,'LHR','JFK' UNION ALL
SELECT 1234,3,'JFK','LHR' UNION ALL
SELECT 1234,4,'LHR','MAN'
)
,new_sectors (tran_id, sector_id, airport) AS (
SELECT tran_id, 0, dept AS airport FROM flight_sectors_table WHERE sector_id = 1
UNION ALL SELECT tran_id, sector_id, arrive FROM flight_sectors_table
)
SELECT flight_tran_id, OtherData
, STUFF((select '-' + airport
from new_sectors fs2
where fs2.tran_id = fs.flight_tran_id
ORDER BY sector_id
for xml path('')), 1, 1, '')
FROM flight_tran_table fs
It would be interesting to see if it actually runs faster on your test server, as we know that query plans don't tell the whole truth and nothing but the truth.
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
April 12, 2012 at 5:22 am
dwain.c (4/12/2012)
It would be interesting to see if it actually runs faster on your test server, as we know that query plans don't tell the whole truth and nothing but the truth.
Dwain - I'd try this example but I dont understand which bit I need to modify, I cant work out how to get rid of the hardcoded test values and replace with the real values from my dataset?
I could do it with Seans query as I just took the main select and changed the object names to reflect my live database tables.
April 12, 2012 at 5:32 am
To use my solution you just need to remove the top 2 CTEs (that I took from Sean) and that you say you removed to use his solutions. You'll need to keep the new_sectors CTE (put the WITH in front of it).
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
April 12, 2012 at 5:45 am
dwain.c (4/12/2012)
To use my solution you just need to remove the top 2 CTEs (that I took from Sean) and that you say you removed to use his solutions. You'll need to keep the new_sectors CTE (put the WITH in front of it).
Got it, thanks.
On the same live dataset it returns all rows within 1 second - pretty amazing improvement.
April 12, 2012 at 5:55 am
sbuchan - You're most welcome.
Gotta give credit where credit is due though. My first attempt at a solution didn't look as good as Sean's so it was his solution that challenged me to find an alternative. Without it, I probably would have posted my initial attempt.
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
April 12, 2012 at 7:37 am
dwain.c (4/12/2012)
sbuchan - You're most welcome.Gotta give credit where credit is due though. My first attempt at a solution didn't look as good as Sean's so it was his solution that challenged me to find an alternative. Without it, I probably would have posted my initial attempt.
Dwain, thanks for the kudos but no need to be humble. That is good stuff.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply