September 3, 2009 at 1:28 am
Am using cursor to accomplish the below result. It takes huge time(around 5 million records )
to complete.(5hrs to 6hrs)
Eg
Table1:
c1 c2 Date Date_Difference(RESULT)
001 a1 2009-01-01 NULL
001 b1 2009-01-01 NULL
001 a1 2009-01-28 27
Condtion TO STORE RESULT
IF C1=C1 AND C2=C2 THEN DATEDIFFERENCE
-- 0001=001 AND A1=A1 THEN DATEDIFF(DD,'2009-01-01','2009-01-28')
have used cursor to fetch each row and perform the above condtion.
Pls help/guide is there any alternate way to perform the above action without using cursor and also to mininmize the processing time.
Thanks...Gugan
September 3, 2009 at 1:56 am
Hi , please post the full code, im not to clear on your requirements.
DDL and example data would also be very helpful.
September 3, 2009 at 2:18 am
DECLARE
@c1 VARCHAR(5),
@c2 VARCHAR(5),
@date datetime,
@c1_i VARCHAR(5),
@c2_i VARCHAR(5),
@date_i datetime,
@RESULT INT
@c2 CURSOR
SET @c2 = CURSOR FOR
SELECT c1,c2,date FROM table1
OPEN @C1
FETCH NEXT FROM @C1 INTO @c1,@c2,@date
WHILE @@FETCH_STATUS=0
BEGIN
SET @c1_i=@c1
SET @c2_i=@c2
SET @date_i=@date
FETCH NEXT FROM @C1 INTO @c1,@c2,@date
IF @c1=@c1_i AND @c2=@c2_i ---001=001 & A1=A1
BEGIN
UPDATE table1 SET RESULT=Datediff(dd,@date_i,@date) WHERE
c1=@c1 AND c2=@c2 and date=@date --datediff(dd,'2009-01-01','2009-01-28')
END
END
CLOSE @c2
DEALLOCATE @c2
C1C2 DATE RESULT
001A12009-01-01 NULL
001B12009-01-01 NULL
001A12009-01-28 27
September 3, 2009 at 2:33 am
will this do ?
update table1 set Result = Datediff(dd,date,(Select top 1 date from table1 NextDate
where NextDate.c1 = table1.c1
and NextDate.c2 = table1.c2
and NextDate.Date > table1.Date order by date))
May still be slow if proper indexing has not been used, but will be better than a cursor.
BTW in your original code there was no 'ORDER BY' clause. If you need data a particular order , as you did, then you have to specify ORDER BY. Do not assume that as you have been given the data by SQLServer in the expected order once then that will hold true for ever.
September 3, 2009 at 5:06 am
Hi,
Thanks..
forgot to mention... have used clustered index in my table1 still it is taking huge time.
i.e.,
Clustered index (c1,date,c2)
Also pls clarify wat is nextdate(table) in your query.
September 3, 2009 at 5:18 am
For this query the index seems wrong , should be c1,c2,date.
NextDate is just an alias for table1 within the subquery.
The still could be a whole host of reasons why its running slow, have you defined what an acceptable time frame is ?
Please post DDL and queryplan as per this link http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 3, 2009 at 6:11 am
Hi,
have checked the query..need help how to modify it. ie.,
given query updates the result in minimum date row..
C1 C2 DATE RESULT
001 A1 2009-01-01 27
001 B1 2009-01-01 NULL
001 A1 2009-01-28 NULL
need the update the result in below row ie., in maximum date row.
C1 C2 DATE RESULT
001 A1 2009-01-01 NULL
001 B1 2009-01-01 NULL
001 A1 2009-01-28 27
September 3, 2009 at 6:18 am
I could give you the solution , but i would urge your to play with the code and attempt to find it for yourself. Its quite simple. After all , when your boss is screaming at you that that is not what he meant and now the company has lost thousands , i wont be there to hold your hand ( or take the wrap) .
September 3, 2009 at 8:44 am
Hi,
Pls provide any hint...would be great.
Thanks in advance..
September 3, 2009 at 9:52 am
I'm being gracious today. Here is some code for you to check out. The clustered index on the temporary table IS REQUIRED as is.
Code has to be attached, problems here at work.
September 3, 2009 at 9:54 am
HINT: you are updating the MIN record in a group, but you want to update the MAX instead. So look at changes like: "MAX" vs "MIN", "" and ORDER BY "DESC" vs "ASC".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2009 at 9:54 am
Are you just trying to find the difference between a date and the date in the next row?
If so, try this:
;with Dates (Row, Date, Days) as
(select row_number() over (partition by c1, c2 order by date),
date,
date_difference
from dbo.Table1)
update Dates1
set Days = datediff(day, Dates1.Date, Dates2.Date)
from Dates Dates1
inner join Dates Dates2
on Dates1.Row = Dates2.Row - 1;
What I did was assign each row a temporary ID based on the c1 and c2 values, and the date sequence. Then join that to itself, and update the one with the earlier date.
This method is usually pretty fast, generally much faster than a cursor or a triangular join.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2009 at 12:13 pm
Hi,
Thanks a lot...query works fine. Its saves huge processing time and an alternate to cursor.
September 4, 2009 at 12:39 pm
gugan_ta (9/4/2009)
Hi,Thanks a lot...query works fine. Its saves huge processing time and an alternate to cursor.
Nice to know, but as you were given several alternatives, what is your final solution?
September 9, 2009 at 9:02 am
Lynn Pettis (9/4/2009)
gugan_ta (9/4/2009)
Hi,Thanks a lot...query works fine. Its saves huge processing time and an alternate to cursor.
Nice to know, but as you were given several alternatives, what is your final solution?
I guess we'll never know. Oh well.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply