Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Network:
Windows 2008 Server
Hi David
>Can someone help me figure out why the following update statement is not fully optimized? Thanks!
>
>*** sys(3054) tells me that it is using the curaccount.cchanged index, and the curaccount. xline index,
>**but it doesn't say it is using the account clineid index, and it says the optimization for table account is none.
>*-- as a result I had to abandon this code and replace it with the seek/looping code below it
>*** I have numerous updates statements of the exact same form, they all run slow, haven't tested them all yet.
>
>
>
>
>*-- there is an index on account.clineid called clineid
>*-- there is an index on curaccount.clineid called xline
>*-- there is an index on curaccount.cchanged called xchange
>
>SET ENGINEBEHAVIOR 80 && tried with and without this
>PUBLIC cmemvar
>=SYS(3054,11,"cmemvar")
>UPDATE account SET cacc = curaccount.cacc, cid = curaccount.cid from account join curaccount on account.clineid = curaccount.clineid where curaccount.cchanged='Y'
>?cmemvar
Is the index xchange built like this: INDEX ON UPPER(cchanged) TAG xchange. That is my general practice and I would advise you to do that in this case unless cchanged = 'y' means something different than cchanged = 'Y' :). If it is also your practice then you need to change the above where clause to where upper(curaccount.cchanged)='Y'
You should have an FLOCK on the account table before the update command and an UNLOCK afterwards.
BTW PUBLIC declarations should be your last choice for a variable. Local first, private second.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only