Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Suggestion to avoid Cursor Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 4:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:04 PM
Points: 246, Visits: 488
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



Post #1377464
Posted Friday, October 26, 2012 4:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, Visits: 296
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




Post #1377467
Posted Friday, October 26, 2012 4:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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 : -
Missing rows with nolock
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



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1377470
Posted Friday, October 26, 2012 5:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874

...
        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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1377508
Posted Friday, October 26, 2012 7:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:04 PM
Points: 246, Visits: 488
Thanks for all your replies

I will test the code that will run faster.

Best regards,
Luis Santos



Post #1377565
Posted Friday, October 26, 2012 8:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
My money is on Cadavre's solution.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1377617
Posted Friday, October 26, 2012 8:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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))




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1377628
Posted Friday, October 26, 2012 8:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 2,763, Visits: 5,910
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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1377630
Posted Friday, October 26, 2012 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874


...
EDIT: Now I'm betting on Eugene's EXISTS solution
...


Are you a gambler?



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1377639
Posted Friday, October 26, 2012 9:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:04 PM
Points: 246, Visits: 488
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



Post #1377699
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse