August 12, 2012 at 2:59 am
Dear All,
thanks for help me from my previous problem. Now i have the second problem. I've search in this forum, but i can't fine the similiar one to my problem.
So this is my data.
-- Structure for test_table1
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#test_table1','U') IS NOT NULL
DROP TABLE #test_table1
--===== Create the test table with
CREATE TABLE #test_table1
(
TradeID VARCHAR(20),
AskClient VARCHAR(30),
BidClient VARCHAR(30)
)
INSERT INTO #test_table1
( TradeID, AskClient, BidClient )
SELECT '387901389','166601','290473' union all
SELECT '163519266','688547','742276' union all
SELECT '786144810','100025','100407' union all
SELECT '277345264','100025','105712' union all
SELECT '047234293','100173','107204' union all
SELECT '641655752','100173','107204' union all
SELECT '800001703','100173','110543' union all
SELECT '213485557','100173','115897' union all
SELECT '782051572','100552','116591' union all
SELECT '456731113','101406','119201' union all
SELECT '441128316','101406','124940' union all
SELECT '983599155','103053','127686' union all
SELECT '996506468','104527','127686' union all
SELECT '510968702','107204','127686' union all
SELECT '223655527','108032','127686' union all
SELECT '224733609','108603','127686' union all
SELECT '380800281','110327','133915' union all
SELECT '525044232','111805','136265' union all
SELECT '449918312','113866','139964' union all
SELECT '153859720','559339','164744' union all
SELECT '153859729','559339','290473' union all
SELECT '153859730','559339','275135' union all
SELECT '153859734','559339','742450' union all
SELECT '153859764','559339','573299' union all
SELECT '153859784','559339','113970' union all
SELECT '153859813','559339','448920' union all
SELECT '153859846','559339','741223' union all
SELECT '153859875','559339','345715' union all
SELECT '153859915','728545','290473' union all
SELECT '153860001','559339','138776' union all
SELECT '153860119','559339','133344' union all
SELECT '153860276','466377','290473' union all
SELECT '153860277','466377','656127' union all
SELECT '153860278','466377','170824' union all
SELECT '153860279','466377','130445' union all
SELECT '153860280','466377','663988' union all
SELECT '153860281','466377','329541' union all
SELECT '153860304','466377','274161' union all
SELECT '153860305','108032','274161' union all
SELECT '153860391','108032','735931' union all
SELECT '153860467','108032','126421' union all
SELECT '153860486','760174','433787' union all
SELECT '153860496','108032','201922' union all
SELECT '153860497','108032','745649' union all
SELECT '153860685','108032','584092' union all
SELECT '153860932','341570','433787' union all
SELECT '153861028','262733','253680' union all
SELECT '153861029','262733','665241' union all
SELECT '153861030','262733','130494' union all
SELECT '153861031','262733','112221' union all
SELECT '153861032','262733','744833' union all
SELECT '153861033','262733','153742' union all
SELECT '153861034','262733','570707' union all
SELECT '153861035','262733','354094' union all
SELECT '153861117','262733','135251' union all
SELECT '153861260','108032','124783' union all
SELECT '153861255','262733','124783' union all
SELECT '153861256','200548','124783' union all
SELECT '153861257','369679','124783' union all
SELECT '153861258','760174','124783' union all
SELECT '153861259','549215','124783' union all
SELECT '153861300','326552','201922' union all
SELECT '153861391','326552','407483' union all
SELECT '153861406','326552','466377' union all
SELECT '153861554','759606','466377' union all
SELECT '153861782','125511','466377' union all
SELECT '153862363','118718','466377' union all
SELECT '153862416','308706','466377' union all
SELECT '153862507','224970','466377' union all
SELECT '153862536','124783','466377' union all
SELECT '153862602','723167','466377' union all
SELECT '153862974','129692','466377' union all
SELECT '153862983','124783','466377' union all
SELECT '153863030','150090','466377' union all
SELECT '153863126','433787','466377' union all
SELECT '153863192','108032','466377' union all
SELECT '153863193','108032','200932' union all
SELECT '153863194','108032','402472' union all
SELECT '153863195','108032','407483' union all
SELECT '153863196','108032','130494' union all
SELECT '153863435','108032','184190' union all
SELECT '153863582','108032','211900' union all
SELECT '153863853','108032','205505' union all
SELECT '153863902','107204','735589' union all
SELECT '153863921','465270','169331' union all
SELECT '153864000','107204','205505' union all
SELECT '153864001','433787','113111' union all
SELECT '153864002','433787','124494' union all
SELECT '153864003','433787','206854' union all
SELECT '153864004','433787','564757' union all
SELECT '153864005','433787','354094' union all
SELECT '153864103','334919','354094' union all
SELECT '153864104','334919','200932' union all
SELECT '153864105','334919','759844' union all
SELECT '153864106','334919','742201' union all
SELECT '153864107','334919','570707' union all
SELECT '153864108','334919','660587' union all
SELECT '153864109','334919','408236' union all
SELECT '153864110','334919','395462' union all
SELECT '153864111','334919','775646'
From above structure and data i want to split AskClient and BidClient on same TradeID into two rows. I've create query with union like this
SELECT
TradeID,
'Ask' AS Part,
AskClient
FROM #test_table1
UNION
SELECT
TradeID,
'Bid' AS Part,
BidClient
FROM #test_table1
When i create this query for the first time, i'm just doing it with small amount of data so there is no problem. But when i move from development to production with 500.000 records, it took 5 minutes to finish the script. I think about using CTE to enhance my query performance, but i can't imagine how to implement CTE in my requirement.
My question is, is it normal to take 5 minutes for around 500.000 records on my query. Could anyone here teach me, how to do it with CTE??
Notes :
- On my real table, there is 25 fields, i'm just show 3 fields that relevant to my requirement to simplified it.
- i'm using SQL Server 2008 on Windows Server 2008 with Intel Core i5 2600k and 8Gb Rams
August 12, 2012 at 3:30 am
Assuming that the TradeID column is unique (primary key?), why not use a UNION ALL instead of UNION. This avoids a (expensive) distinct sort to filter out nonexisting duplicates. Still, using UNION (ALL) will cause the table to be scanned twice, once for each part. An easier solution which only requires a single table scan is:
SELECT
T.TradeID, CA.Part, CA.Client
FROM
#test_table1 T
CROSS APPLY
(
VALUES ('Ask', AskClient), ('Bid', BidClient)
) CA(Part, Client)
No need for a CTE.
August 12, 2012 at 9:11 pm
Peter Brinkhaus (8/12/2012)
Assuming that the TradeID column is unique (primary key?), why not use a UNION ALL instead of UNION. This avoids a (expensive) distinct sort to filter out nonexisting duplicates. Still, using UNION (ALL) will cause the table to be scanned twice, once for each part. An easier solution which only requires a single table scan is:
SELECT
T.TradeID, CA.Part, CA.Client
FROM
#test_table1 T
CROSS APPLY
(
VALUES ('Ask', AskClient), ('Bid', BidClient)
) CA(Part, Client)
No need for a CTE.
Thanks for your suggestion, your query has improve my query performance. I'm still new to sql server development, so i don't know much about query performance booster.
Maybe you could explain in general when i have to use APPLY and when i have to use normal UNION (ALL).
Thanks a lot for your help.
August 13, 2012 at 2:43 pm
martin.david (8/12/2012)
Peter Brinkhaus (8/12/2012)
Assuming that the TradeID column is unique (primary key?), why not use a UNION ALL instead of UNION. This avoids a (expensive) distinct sort to filter out nonexisting duplicates. Still, using UNION (ALL) will cause the table to be scanned twice, once for each part. An easier solution which only requires a single table scan is:
SELECT
T.TradeID, CA.Part, CA.Client
FROM
#test_table1 T
CROSS APPLY
(
VALUES ('Ask', AskClient), ('Bid', BidClient)
) CA(Part, Client)
No need for a CTE.
Thanks for your suggestion, your query has improve my query performance. I'm still new to sql server development, so i don't know much about query performance booster.
Maybe you could explain in general when i have to use APPLY and when i have to use normal UNION (ALL).
Thanks a lot for your help.
The function specified in CROSS APPLY is applied to every row in the resultset (i.e. every row coming from #test_table) and can add one to many rows to that resultset. In this case a table-valued constructor is applied which means #test_table is scanned once and all columns in #test_table are combined with all rows in CA (2 rows) thereby adding two rows to the final resultset for every one row in #test_table.
In the case of your UNION you're scanning #test_table twice, once each to build the AskClient and BidClient rows, i.e. it does roughly twice as much I/O as the query that uses CROSS APPLY.
In addition to the two table scans, because you use UNION instead of UNION ALL SQL Server also attempts to de-duplicate the resultset which requires an expensive sort. If you use UNION ALL instead of UNION your query will perform much better, although it still requires two passes over the data whereas the CROSS APPLY only requires one. UNION ALL in this case would have been an improvement but still not nearly as good as CROSS APPLY which is clear when dealing with large amounts of data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 13, 2012 at 7:53 pm
Peter Brinkhaus (8/12/2012)
Assuming that the TradeID column is unique (primary key?), why not use a UNION ALL instead of UNION. This avoids a (expensive) distinct sort to filter out nonexisting duplicates. Still, using UNION (ALL) will cause the table to be scanned twice, once for each part. An easier solution which only requires a single table scan is:
SELECT
T.TradeID, CA.Part, CA.Client
FROM
#test_table1 T
CROSS APPLY
(
VALUES ('Ask', AskClient), ('Bid', BidClient)
) CA(Part, Client)
No need for a CTE.
As Peter has so aptly demonstrated, this is just an UNPIVOT in disguise: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
The discussion thread of that article shows some performance considerations for using the CROSS APPLY VALUES approach to UNPIVOT on record sets of this size: http://www.sqlservercentral.com/Forums/Topic1338934-3122-2.aspx (continued on p. 3 of the discussion thread). Trying the suggestions on p. 3 you may be able to get a better mix of CPU and Elapsed time utilization.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply