October 21, 2013 at 5:45 pm
I am having serious performance issues with a large set of Key/value data. The table looks like this:
CREATE TABLE dbo.Table1 (
TicketId int NOT NULL,
RecordID int NOT NULL,
Name nvarchar(128) NOT NULL,
Value Float NOT NULL,
CONSTRAINT [pk_table1] PRIMARY KEY CLUSTERED
(
TicketID ASC,
RecordID ASC,
Name ASC
)
Now the goal is to pivot/flatten this data before joining to a client table. We do not need every row. This is what the query looks like:
SELECT
TicketId,
RecordID,
Name1,
Name2,
Name3,
etc
FROM
(
SELECT Ticketid, RecordId, Name, Value FROM Table1 WHERE TicketID In (@ticketId, @priorTicketId, @anotherTicketID)
AND Name In ('Name1', 'Name2', 'Name3',etc) as P
Pivot ( Max(Value) for Name IN ('Name1', 'Name2', 'Name3',etc) ) as piv
Table1 has 2 billion rows and the query runs for 3+ hours. Is this the best that can be done on a dataset this large?
First time posting so please excuse if I forgot to include any information you may need, or if I just posted int he wrong location.
October 21, 2013 at 5:55 pm
Have a look here, http://www.sqlservercentral.com/articles/SQLServerCentral/66909/, and then the experts will all come out to play and you will get a very nice solution to your problem.
October 21, 2013 at 6:54 pm
dogramone (10/21/2013)
Have a look here, http://www.sqlservercentral.com/articles/SQLServerCentral/66909/, and then the experts will all come out to play and you will get a very nice solution to your problem.
I agree so I'll help making that link clickable.
You may also want to reformulate your query as a cross tab instead of a pivot. Here's a couple of articles that might help:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns [/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply