anwarmir
asked on
SARG Update Statement
Hi I have run profiler filtering on all queries taking longer than 10 seconds. The Update statement below the update takes around 80 seconds. Is there anyway I can Sarg the update statement and make it run quicker. Currently it's doing and Index Scan on the temptable which contains 2000 rows and doing an index seek on the
tblHighlights table. Optimiser is joining the two tables together by doing a nested loop.
As I understand to make the sql optimizer use the indexes efficiently you should not use functions on columns in the WHERE clause. How can I get around this if i need to check the existense of empty string and NULL. Furthermore the tDescription column is a text field.
Help would be appreciated.
Thanks
UPDATE TR
SET TR.Filled = 1
FROM TH
INNER JOIN #Test TR ON TR.K_EL_ID = TH.iExhibitorID AND TR.K_EV_ID = TH.iEventID
WHERE
(ISNULL(CAST(tDescription AS VarChar),'') <> ''
OR ISNULL(vcImage,'') <> '')
tblHighlights table. Optimiser is joining the two tables together by doing a nested loop.
As I understand to make the sql optimizer use the indexes efficiently you should not use functions on columns in the WHERE clause. How can I get around this if i need to check the existense of empty string and NULL. Furthermore the tDescription column is a text field.
Help would be appreciated.
Thanks
UPDATE TR
SET TR.Filled = 1
FROM TH
INNER JOIN #Test TR ON TR.K_EL_ID = TH.iExhibitorID AND TR.K_EV_ID = TH.iEventID
WHERE
(ISNULL(CAST(tDescription AS VarChar),'') <> ''
OR ISNULL(vcImage,'') <> '')
Do you have index on TR.K_EL_ID ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also if possible try to create index on this #test table
Create index IX_test on #Test (iExhibitorID ,iEventID )
Create index IX_test on #Test (iExhibitorID ,iEventID )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I'll try these tips out.
>>make sure you need the cast it does not look needed to me
I need the cast to convert to varchar to check for empty string as this not allowd on a text field
>>make sure you need the cast it does not look needed to me
I need the cast to convert to varchar to check for empty string as this not allowd on a text field
anwarmir,
> need the cast to convert to varchar to check for empty string as this
> not allowd on a text field
consider enabling full text indexing this may help performance when using this field
does the field hold more than 8000 chars ? if nor consider changing it to varchar(AnticipatedMaxLeng
if you are on 2005 consider changing the field to varchar(max)
> need the cast to convert to varchar to check for empty string as this
> not allowd on a text field
consider enabling full text indexing this may help performance when using this field
does the field hold more than 8000 chars ? if nor consider changing it to varchar(AnticipatedMaxLeng
if you are on 2005 consider changing the field to varchar(max)