November 10, 2010 at 9:34 pm
USE tempdb
--===== Create the test table with
CREATE TABLE [dbo].[TNG](
[DOCLINKID] [varchar](50) NULL,
[DELAYDATE] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TNG]
([DOCLINKID]
,[DELAYDATE])
SELECT '64N7EVMLKY492004L', '2003-10-06 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2003-10-16 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-01-19 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-02-10 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-03-15 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-05-23 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-07-04 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-08-05 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-12-12 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2004-12-27 00:00:00.000' UNION ALL
SELECT '64N7EVMLKY492004L', '2005-02-20 00:00:00.000'
GO
CREATE TABLE [dbo].[CSE](
[DOCLINKID] [varchar](50) NULL,
[DELAY1] [datetime] NULL,
[DELAY2] [datetime] NULL,
[DELAY3] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[CSE] ([DOCLINKID])
SELECT '64N7EVMLKY492004L'
What I need in here, is to update CSE table, take first date from TNG table to DELAY1 column, and second date from TNG to DELAY2 column and third to DELAY3.
In TNG table sometimes DOCLINKID not exist, sometimes with one or two days only. In that case, DELAY3 have to remain null
Thanks beforehand for your help, to solve this struggling since yest ;p
November 10, 2010 at 9:52 pm
Not the cleanest in optimization because of the multiple correlated subqueries, but this will get you on your way I believe. I used a second cte to trim down the massive resultsets you'd probably end up with from the first.
For better optimization if this runs too slow, make cte2 a temptable and then index it. Then run it against your primary query. Will depend on the size of your real dataset.
;WITH cte AS
(SELECT
doclinkid, delaydate, row_number() OVER( PARTITION BY doclinkID ORDER BY DelayDate) AS RowNum
FROM
TNG
),
cte2 AS
(SELECT
*
FROM
cte
WHERE
RowNum <=3
)
UPDATECSE
SET
Delay1 = (SELECT DelayDate FROM cte2 WHERE cte2.doclinkid = cse.doclinkID AND RowNum = 1),
Delay2 = (SELECT DelayDate FROM cte2 WHERE cte2.doclinkid = cse.doclinkID AND RowNum = 2),
Delay3 = (SELECT DelayDate FROM cte2 WHERE cte2.doclinkid = cse.doclinkID AND RowNum = 3)
select * from cse
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 11:57 pm
Thanks Craig Farrell for that, it's running.
But when I run it on real data getting a massage: 'timeout expired'. Then I run it on 10 data only on CSE while TNG have 122k, it tooks 28 sec, can't imagine how long would it take for all data.
I have this original script in Oracle, which looks more simple, no idea how long it runs, but the script is like this:
SELECT ALL TNG.DOCLINKID,
TNG.DelayDate, DelayCategory
FROM TNG
WHERE ROWNUM <= 3
ORDER BY TNG.DelayDate
Is there anyway to make it faster as it going to be used in DW ETL.
And what if I needed last three days, is that possible as well?
November 11, 2010 at 12:31 am
The reason is there is no rownumber as oracle would describe it, and even using a standard identity column wouldn't help in the case of multiple doclinks.
This is what I meant by using a temp table in the middle. It should provide much higher performance:
IF OBJECT_ID( 'tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
;WITH cte AS
(SELECT
doclinkid, delaydate, row_number() OVER( PARTITION BY doclinkID ORDER BY DelayDate) AS RowNum
FROM
TNG
)
SELECT * INTO #tmp FROM cte WHERE RowNum < 3
CREATE CLUSTERED INDEX idx_temp ON #tmp ( doclinkID, RowNum)
UPDATE CSE
SET
Delay1 = (SELECT DelayDate FROM #tmp WHERE #tmp.doclinkid = cse.doclinkID AND RowNum = 1),
Delay2 = (SELECT DelayDate FROM #tmp WHERE #tmp.doclinkid = cse.doclinkID AND RowNum = 2),
Delay3 = (SELECT DelayDate FROM #tmp WHERE #tmp.doclinkid = cse.doclinkID AND RowNum = 3)
select * from cse
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 1:00 am
Thank you very much bro, it's working pretty well, just little problem is my supervisor asked me not to use temp table, as there gonna be huge DB, in this case I think he will agree with me ;-).
Thank you again, Craig Farrell, and have a nice day.
November 11, 2010 at 1:06 am
Dehqon D. (11/11/2010)
Thank you very much bro, it's working pretty well, just little problem is my supervisor asked me not to use temp table, as there gonna be huge DB, in this case I think he will agree with me ;-).
In that case, if you truly need to, you can use a tablevariable instead and just CONSTRAINT PRIMARY KEY ( DockLinkID, RowNum). At 200k+ rows though, I'd stay with the temp table if you can.
Thank you again, Craig Farrell, and have a nice day.
My pleasure, and thank you. Now I will. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply