Suggestion to avoid Cursor

Question

Suggestion to avoid Cursor

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

He put "Select 1" to avoid the overhead of getting meta data for all the columns in the table that is being selected. This has been the standarp practice to check if a record exists in a table.

Hope this helps.

luissantos SSCertifiable Points: 7391 More actions · Answer 2

Hello Eugene

I will test your script for only for one REF(Article) = '2019023'. this article have only one invoice with one line.

Your script :

UPDATE fi

SET cor = 'Y', tam = 'Z'

WHERE EXISTS (SELECT 1 FROM arttamcor a

WHERE a.referencia COLLATE SQL_Latin1_General_CP1_CI_AS = fi.ref

OR (fi.ref = '' AND a.referencia collate Latin1_General_CI_AS = fi.oref) AND a.referencia = '2019023')

The script take very lot of time , because for this REF i have only 1 invoice line.

I think is that because he scan all the records on table ArtTamCor without regarding the conditions on WHERE clause.

To test it and see what´s happen , i make this change:

(SELECT 1 FROM arttamcor a inner join fi

on Rtrim(a.referencia) COLLATE SQL_Latin1_General_CP1_CI_AS = Rtrim(fi.ref)

OR (Rtrim(fi.ref) = '' AND Rtrim(a.referencia) collate Latin1_General_CI_AS = rtrim(fi.oref))

AND RTRIM(a.Referencia) = '2019023')

After +/- 8m:38s , the numbers of rows return are 41513 and the query analiser still continue running, it´s impossible.

Could you explain to me why, because i don´t understand

Best regards,

Luis Santos

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Hello comnunity

Any explication or advice regarding my last post.

Many thanks

Luis Santos

luissantos SSCertifiable Points: 7391 More actions · Answer 4

Hello again

After some test, the best performance script is the script send by Cadavre, with some adjustements:

UPDATE fi

SET fi.cor = 'Y', fi.tam = 'Z'

FROM (SELECT RTRIM(referencia) referencia

FROM arttamcor

GROUP BY referencia) a

RIGHT JOIN fi ON -- RIGHT join improve incompare top performance

(RTRIM(fi.ref) = '' AND RTRIM(fi.oref) = RTRIM(a.referencia)collate Latin1_General_CI_AS)

OR RTRIM(fi.ref) = RTRIM(a.referencia)collate Latin1_General_CI_AS

where RTRIM(fi.ref) = '2121150';

Now , this script take 00:00:02 to update 79 line on my table FI and don´t hint my CPU and Memory.

The Script send by Eugene hint CPU (100%) and Memory (5,8 GB) and take an uncalculate time to execute.

In fact i don´t know why, but for the future i hope someone explain why this happen...!!!

Many thanks for all your replies.

Best regards,

Luis Santos

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

luissantos (10/26/2012)
Hello Eugene
I will test your script for only for one REF(Article) = '2019023'. this article have only one invoice with one line.
Your script :
UPDATE fi
SET cor = 'Y', tam = 'Z'
WHERE EXISTS (SELECT 1 FROM arttamcor a
WHERE a.referencia COLLATE SQL_Latin1_General_CP1_CI_AS = fi.ref
OR (fi.ref = '' AND a.referencia collate Latin1_General_CI_AS = fi.oref) AND a.referencia = '2019023')
The script take very lot of time , because for this REF i have only 1 invoice line.
I think is that because he scan all the records on table ArtTamCor without regarding the conditions on WHERE clause.
To test it and see what´s happen , i make this change:
(SELECT 1 FROM arttamcor a inner join fi
on Rtrim(a.referencia) COLLATE SQL_Latin1_General_CP1_CI_AS = Rtrim(fi.ref)
OR (Rtrim(fi.ref) = '' AND Rtrim(a.referencia) collate Latin1_General_CI_AS = rtrim(fi.oref))
AND RTRIM(a.Referencia) = '2019023')
After +/- 8m:38s , the numbers of rows return are 41513 and the query analiser still continue running, it´s impossible.
Could you explain to me why, because i don´t understand
Best regards,
Luis Santos

The reason why is because it's actually an illegal form of UPDATE in SQL Server. If you have any kind of JOINed UPDATE (and an EXISTS or IN is a form of join) in the FROM or the WHERE clause, then you have to have reference to the table that is the target of the UPDATE in a FROM clause for the UPDATE. Sometimes SQL Server will let you get away without such a thing but it will eventually lose its mind and slip into a "Cartesian Join" mode.

This shouldn't be confused with using a correlated sub-query in the SET clause of an UPDATE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BriPan SSCrazy Points: 2061 More actions · Answer 6

Yes my code is not at all optimize but yes it can be optimized

i was just giving one of solution using while loop that's i asked question first "do you mean this"

there can be different solution and optimized

will sure get back to you will good solution.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

-- if this returns a few thousands of rows or more;

SELECT RTRIM(referencia) referencia FROM arttamcor GROUP BY referencia

-- then consider setting it up as a temp table:

SELECT RTRIM(referencia) referencia

INTO #arttamcor

FROM arttamcor

GROUP BY referencia

CREATE UNIQUE CLUSTERED INDEX UCX_referencia ON #arttamcor (referencia)

-- and use it as follows

UPDATE fi

SET cor = 'Y', tam = 'Z'

FROM fi AS f

INNER JOIN #arttamcor AS a

ON (f.ref = '' AND f.oref = a.referencia) OR f.ref = a.referencia;

-- or

UPDATE fi

SET cor = 'Y', tam = 'Z'

FROM fi

WHERE EXISTS (SELECT 1 FROM #arttamcor a

WHERE a.referencia = fi.ref

OR (fi.ref = '' AND a.referencia = fi.oref))

-- for either of these solutions to perform well, you must have supporting indexes on fi.ref and fi.oref

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

Jeff Moden (10/27/2012)
luissantos (10/26/2012)
Hello Eugene
I will test your script for only for one REF(Article) = '2019023'. this article have only one invoice with one line.
Your script :
UPDATE fi
SET cor = 'Y', tam = 'Z'
WHERE EXISTS (SELECT 1 FROM arttamcor a
WHERE a.referencia COLLATE SQL_Latin1_General_CP1_CI_AS = fi.ref
OR (fi.ref = '' AND a.referencia collate Latin1_General_CI_AS = fi.oref) AND a.referencia = '2019023')
The script take very lot of time , because for this REF i have only 1 invoice line.
I think is that because he scan all the records on table ArtTamCor without regarding the conditions on WHERE clause.
To test it and see what´s happen , i make this change:
(SELECT 1 FROM arttamcor a inner join fi
on Rtrim(a.referencia) COLLATE SQL_Latin1_General_CP1_CI_AS = Rtrim(fi.ref)
OR (Rtrim(fi.ref) = '' AND Rtrim(a.referencia) collate Latin1_General_CI_AS = rtrim(fi.oref))
AND RTRIM(a.Referencia) = '2019023')
After +/- 8m:38s , the numbers of rows return are 41513 and the query analiser still continue running, it´s impossible.
Could you explain to me why, because i don´t understand
Best regards,
Luis Santos
The reason why is because it's actually an illegal form of UPDATE in SQL Server. If you have any kind of JOINed UPDATE (and an EXISTS or IN is a form of join) in the FROM or the WHERE clause, then you have to have reference to the table that is the target of the UPDATE in a FROM clause for the UPDATE. Sometimes SQL Server will let you get away without such a thing but it will eventually lose its mind and slip into a "Cartesian Join" mode.
This shouldn't be confused with using a correlated sub-query in the SET clause of an UPDATE.

I don't think "Cartesian Join" is relevant here. I can see how it can be formed with use of JOIN in update but why how it can happen with simple EXISTS check?

The real reason is: You are comparing two absolutely different queries! The way OP changed my query made it very different to the query which used JOIN:

UPDATE fi

SET cor = 'Y', tam = 'Z'

EXISTS (SELECT 1 FROM arttamcor a

WHERE a.referencia COLLATE SQL_Latin1_General_CP1_CI_AS = fi.ref

OR (fi.ref = '' AND a.referencia collate Latin1_General_CI_AS = fi.oref)

AND a.referencia = '2019023')

Is totally different to:

UPDATE fi

SET fi.cor = 'Y', fi.tam = 'Z'

FROM (SELECT RTRIM(referencia) referencia

FROM arttamcor

` GROUP BY referencia) a

RIGHT JOIN fi ON -- RIGHT join improve incompare top performance

(RTRIM(fi.ref) = '' AND RTRIM(fi.oref) = RTRIM(a.referencia)collate Latin1_General_CI_AS)

OR RTRIM(fi.ref) = RTRIM(a.referencia)collate Latin1_General_CI_AS

where RTRIM(fi.ref) = '2121150';

The second one limits the number of UPDATED rows by applying WHERE filter on fi.ref

and the one with EXISTS, checks the value of arttamcor.referencia (but even that is done with totally wrong logic!)

Try proper analogue of your "JOIN" based "noodles":

UPDATE fi

SET cor = 'Y', tam = 'Z'

WHERE RTRIM(fi.ref) = '2121150';

AND EXISTS (SELECT 1 FROM arttamcor a

WHERE RTRIM(fi.ref) = RTRIM(a.referencia) COLLATE Latin1_General_CI_AS

OR ( RTRIM(fi.ref) = ''

AND RTRIM(fi.oref) = RTRIM(a.referencia) COLLATE Latin1_General_CI_AS)

)

But again, as I've mentioned before, if you only want to update records with RTRIM(fi.ref) = '2121150', you don't need to check condition of RTRIM(fi.ref) = '' as it is irrelevant anyway, so your update doesn't even need to check EXISTS in the update query, it can be done just once:

IF EXISTS(SELECT 1 FROM arttamcor WHERE a.referencia = '2121150')

BEGIN

UPDATE fi

SET cor = 'Y', tam = 'Z'

WHERE fi.ref = '2121150';

END

If you need, you can replace your hard-coded value with variable/parameter.

And the last thing: Why are you using RTRIM here? SQL Server will ignore spaces at the end anyway, try this:

DECLARE @C VARCHAR(10)

SET @C = '1 '

IF @C = '1' print 'ok'

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

How to post your question to get the best and quick help[/url]