October 26, 2012 at 4:38 am
Hello comunity
I need to replace a Cursor with an example using a WHILE and IF condition, for testing if the Update statment i need to run coulb be much faster, i talk only for this table +/- 1.050.818 for REF(Myarticle) about 113.280, here is my cursor:
DECLARE cur1 CURSOR LOCAL FORWARD_ONLY FOR
SELECT
referencia
FROM arttamcor
GROUP BY referencia
-- Cursor Variables
DECLARE @ref VARCHAR(18)
DECLARE @descCor VARCHAR(25)
DECLARE @sgcstamp VARCHAR(25)
-- Cursor Variables (END) --
DECLARE @refActual VARCHAR(18)
SET @refActual = ''
OPEN cur1
FETCH NEXT FROM cur1 INTO @ref
WHILE @@FETCH_STATUS = 0
BEGIN
set @refactual = @ref
PRINT @ref
UPDATE fi SET fi.cor = 'Y', fi.tam = 'Z'
FROM fi (NOLOCK)
WHERE (case when fi.ref = '' THEN fi.oref ELSE fi.ref end) = RTRIM(@ref)
FETCH NEXT FROM cur1 INTO @ref
END
CLOSE cur1
DEALLOCATE cur1
Many thanks
Luis Santos
October 26, 2012 at 4:49 am
do you mean
you need to do this thing just with while loop not with cursor?
if yes then
declare @temptable table(i int identity(1,1) primary key,referencia varchar(50))
insert into @temptable
SELECT
referencia
FROM arttamcor
GROUP BY referencia
declare @i int set @i=1
declare @count int set @count=(select COUNT(*) from @temptable)
while(@i<=@count)
begin
declare @ref varchar(50) set @ref=(select referencia from @temptable where i=@i)
UPDATE fi SET fi.cor = 'Y', fi.tam = 'Z'
FROM fi (NOLOCK)
WHERE (case when fi.ref = '' THEN fi.oref ELSE fi.ref end) = RTRIM(@ref)
set @i=@i+1
end
October 26, 2012 at 4:58 am
Could you not just write it like this: -
UPDATE fi
SET fi.cor = 'Y', fi.tam = 'Z'
FROM (SELECT referencia
FROM arttamcor
GROUP BY referencia) a
WHERE (fi.ref = '' AND fi.oref = RTRIM(a.referencia))
OR fi.ref = RTRIM(a.referencia);
??
Also, I don't know what the circumstances are for your choice of the NOLOCK hint. This may not be applicable to you and without more information there is no way for me to know.
However, in case you're not aware (and for future google searchers), please note that NOLOCK is not a good idea. I have some links for you to read through if you're interested : -
Allocation order scans with nolock
Consistency issues with nolock
Transient Corruption Errors in SQL Server error log caused by nolock
Dirty reads, read errors, reading rows twice and missing rows with nolock
October 26, 2012 at 5:25 am
...
UPDATE fi SET fi.cor = 'Y', fi.tam = 'Z'
FROM fi (NOLOCK)
WHERE (case when fi.ref = '' THEN fi.oref ELSE fi.ref end) = RTRIM(@ref)
...
It may not cause an error (as you're using the hint at the "FROM" part), but NOLOCK renders itself useless when applied to target of data modification. SQL Server will apply required lock anyway.
October 26, 2012 at 7:13 am
Thanks for all your replies
I will test the code that will run faster.
Best regards,
Luis Santos
October 26, 2012 at 8:20 am
My money is on Cadavre's solution.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 26, 2012 at 8:30 am
The Dixie Flatline (10/26/2012)
My money is on Cadavre's solution.
The only thing I would suggest is to include updatable table under FROM clause, to avoid possibility of SQL Server building Cartesian product before applying WHERE filters:
UPDATE fi
SET cor = 'Y', tam = 'Z'
FROM fi AS f
JOIN (SELECT RTRIM(referencia) referencia FROM arttamcor GROUP BY referencia) AS a
ON (f.ref = '' AND f.oref = a.referencia) OR f.ref = a.referencia;
Or, use EXISTS, which can be even faster here:
UPDATE fi
SET cor = 'Y', tam = 'Z'
WHERE EXISTS (SELECT 1 FROM arttamcor a
WHERE a.referencia = fi.ref
OR (fi.ref = '' AND a.referencia = fi.oref))
October 26, 2012 at 8:33 am
Cadavre, is there a reason on why you didn't leave the condition as it was?
Should we test the difference between yours and this?
UPDATE fi
SET fi.cor = 'Y', fi.tam = 'Z'
FROM (SELECT DISTINCT referencia
FROM arttamcor) a
WHERE (case when fi.ref = '' THEN fi.oref ELSE fi.ref end) = RTRIM(a.referencia);
EDIT: Now I'm betting on Eugene's EXISTS solution.
October 26, 2012 at 8:43 am
...
EDIT: Now I'm betting on Eugene's EXISTS solution
...
Are you a gambler?
:hehe:
October 26, 2012 at 9:43 am
Hello again
I test the statment with the WHILE from yeshupandit_2002 and i have adding a condition for ref like '2%', i talk about 8323 REF (article) with 315610 records on table FI.
The time for execution stayed 1h32:31
Also, i have a question to Eugene last post script:
UPDATE fi
SET cor = 'Y', tam = 'Z'
WHERE EXISTS (SELECT 1 FROM arttamcor a
WHERE a.referencia = fi.ref
OR (fi.ref = '' AND a.referencia = fi.oref))
You put "1" to return each record, that´s correct ?
Due to the time consuming on my first test with the code supply by yeshupandit_2002, do you think that your code could reduce this time ?
Best regards
Luis Santos
October 26, 2012 at 10:06 am
luissantos (10/26/2012)
Hello againI test the statment with the WHILE from yeshupandit_2002 and i have adding a condition for ref like '2%', i talk about 8323 REF (article) with 315610 records on table FI.
The time for execution stayed 1h32:31
Also, i have a question to Eugene last post script:
UPDATE fi
SET cor = 'Y', tam = 'Z'
WHERE EXISTS (SELECT 1 FROM arttamcor a
WHERE a.referencia = fi.ref
OR (fi.ref = '' AND a.referencia = fi.oref))
You put "1" to return each record, that´s correct ?
Due to the time consuming on my first test with the code supply by yeshupandit_2002, do you think that your code could reduce this time ?
Best regards
Luis Santos
Give it a try...I am guessing you will find a time reduction of somewhere around 1h 30 minutes. 😛
_______________________________________________________________
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/
October 26, 2012 at 10:17 am
Hello Sean
Your reply don´t tell me anything useful about my doughts and also don´t suggest any kind of improvements.
But OK, if i could save 2 minutes :-Dthat´s fine for me.
Best regards
Luis Santos
October 26, 2012 at 10:18 am
luissantos (10/26/2012)
Hello againI test the statment with the WHILE from yeshupandit_2002 and i have adding a condition for ref like '2%', i talk about 8323 REF (article) with 315610 records on table FI.
The time for execution stayed 1h32:31
Also, i have a question to Eugene last post script:
UPDATE fi
SET cor = 'Y', tam = 'Z'
WHERE EXISTS (SELECT 1 FROM arttamcor a
WHERE a.referencia = fi.ref
OR (fi.ref = '' AND a.referencia = fi.oref))
You put "1" to return each record, that´s correct ?
Due to the time consuming on my first test with the code supply by yeshupandit_2002, do you think that your code could reduce this time ?
Best regards
Luis Santos
1. Changing CURSOR to WHILE loop would very unlikely give any performance improvement. More than that, very often properly written cursor will outperform "WHILE loop" implementation.
But in your case, use of CURSOR or WHILE loop is absolutely inappropriate.
2. EXISTS just checks if at least one record exists for the given condition, so you can put anything you like instead of 1: Null, 'Hello World" or whatever - it doesn't matter at all!
3. And about reducing the time: Try it and I'm sure you will be surprised.
BTW, I'm not 100 percent sure about your "ref like '2%'". Do you want to update only such "fi" rows which have ref begin with '2'? If so, there is no needs for "OR (fi.ref = '' AND a.referencia = fi.oref)" in the WHERE statement.
October 26, 2012 at 10:21 am
luissantos (10/26/2012)
Hello SeanYour reply don´t tell me anything useful about my doughts and also don´t suggest any kind of improvements.
But OK, if i could save 2 minutes :-Dthat´s fine for me.
Best regards
Luis Santos
I merely suggested that you try the suggestion that Eugene posted. You asked if you thought his approach would save any time. I said I am guessing your 1h 32m process will be reduced to 2 minutes or less...hence the time reduction of 1h 30 minutes.
_______________________________________________________________
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/
October 26, 2012 at 10:40 am
Hello Sean
Thanks for your reply, now you have give me a really good help about understanding my doughts, for this, many thanks.
I will go to try the second script that Eugene post like you suggest.
When you say :
"BTW, I'm not 100 percent sure about your "ref like '2%'". Do you want to update only such "fi" rows which have ref begin with '2'? If so, there is no needs for "OR (fi.ref = '' AND a.referencia = fi.oref)" in the WHERE statement."
That´s correct is exactly what i want.
Good weekend,
Luis Santos
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply