We help IT Professionals succeed at work.

SARG Update Statement

anwarmir
anwarmir asked
on
Medium Priority
381 Views
Last Modified: 2012-06-27
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,'') <> '')              
Comment
Watch Question

Top Expert 2006

Commented:
Do you have index on TR.K_EL_ID  ?

Top Expert 2006
Commented:
Create this index and they try


Create index IX_TR on TR (K_EL_ID,K_EV_ID)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2006

Commented:
also if possible try to create index on this #test table

Create index IX_test on #Test   (iExhibitorID ,iEventID  )
CERTIFIED EXPERT
Commented:
Hi anwarmir,
make sure you need the cast it does not look needed to me

try this

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          
(tDescription is null and tDescription <> '')
or
(vcImage is null and vcImage <> '')

or these 2 may run faster than the one




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          

(vcImage is null and vcImage <> '')

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          
(tDescription is null and tDescription <> '')



HTH

R.

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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(AnticipatedMaxLength)

if you are on 2005 consider changing the field to varchar(max)


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.