Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SARG an UPDATE STATEMENT USING TEXT DATATYPE

Author  Topic 

miranwar
Posting Yak Master

125 Posts
Posted - 2006-11-07 : 12:49:22
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,'') <> '')

Kristen
Test

22859 Posts
Posted - 2006-11-07 : 12:57:20
Why are you casting tDescription as varchar?

"Furthermore the tDescription column is a text field."

Edit: Missed that bit, sorry!

Would this be faster:

WHERE    tDescription IS NULL
      OR DATALENGTH(tDescription) = 0)
      OR vcImage IS NULL
      OR vcImage = ''
WHERE (
              tDescription IS NOT NULL
          AND DATALENGTH(tDescription) <> 0
      )
      OR (
              vcImage IS NOT NULL
          AND vcImage <> ''
      )

although I'm surprised its taking 80 seconds to UPDATE a #TEMP table of 2,000 rows.

Maybe there is contention for TEMPDB?

Are you doing any:

SELECT *
INTO #TEMP
FROM MyTable

type stuff? (This locks certain actions on TEMPDB for prolonged periods)

Do you have an index/PrimaryKey on #TEST (I presume it would be on K_EL_ID, K_EV_ID in order to match the JOIN). Ditto on TH (on iExhibitorID, iEventID)

I have had poor performance on #TempTables when there was no PK.

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-11-07 : 12:59:31
I would check which indexes are in use here
UPDATE		TR 
SET		TR.Filled = 1 
FROM		#Test TR 
INNER JOIN	TH ON TH.iExhibitorID = TR.K_EL_ID AND TH.iEventID = TR.K_EV_ID
WHERE		(tDescription IS NOT NULL OR vcImage IS NOT NULL OR vcImage <> '')
		AND TR.Filled <> 1


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-07 : 13:11:37
You can do that check like this

WHERE 
(tDescription NOT LIKE '' AND tDescription IS NOT NULL)


But note that because the tDescription column is a text column it cannot be indexed and so can never be used for an index seek anyway. So if you are going to use that column in the WHERE clause you're going to get a scan. You could try creating a computed column that records the datalength of the text and then using that column in the WHERE clause.


CREATE TABLE TH
(tDescription text,
tDescLength AS datalength(tDescription))

SELECT ...
WHERE tDescLength > 0
Go to Top of Page

miranwar
Posting Yak Master

125 Posts
Posted - 2006-11-07 : 13:28:19
Thanks people

Thanks SnSql would a Computed Column Slow down on performance on Inserts.
Go to Top of Page

miranwar
Posting Yak Master

125 Posts
Posted - 2006-11-07 : 13:31:24
How do I create a computed column on an existing table...Would I have to drop the table ?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-11-07 : 13:33:46
I don't think there is any performance gain with a COMPUTED column for this job, and lots of downsides.

It just should not be taking 80 seconds to update 2,000 rows.

How big is the max/average text in tDescription ??

Kristen
Go to Top of Page

miranwar
Posting Yak Master

125 Posts
Posted - 2006-11-07 : 13:44:55
Kirsten,

the Length of the text field is 16. however, I just tried the original query and it took 24 Seconds..I am going MAD!!!...All morning the server was getting hit with that Update and each time it was taking more than a minute. I am confused now Should i look ate using NO LOCK on the TH table..that table contains around 18000 rows
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-11-07 : 13:48:21
Do you really need the JOIN? Which columns are from TH table in your query?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

miranwar
Posting Yak Master

125 Posts
Posted - 2006-11-07 : 13:53:39
HERE IS THE ORIGINAL QUERY

UPDATE TR
SET TR.Filled = 1
FROM tblHighlights 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,'') <> '')


HI KIRSTEN, I noticed that one DB had a Autogrow operation in the morning, but surely this should not have impacted the server all day..It looks like things have gone quiet. All the updates are now taking 22 seconds. Any ideas on what I should look at now?


Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-07 : 13:55:56
quote:
How do I create a computed column on an existing table...Would I have to drop the table ?


ALTER TABLE TH
ADD tDescLength AS datalength(tDescription)


The computed column wouldn't affect insert performance, just the index update which is the same as for any index.

I would agree with Kristen though that updating 18000 rows from the temp table with 2000 rows should not be taking that long anyway. Just try using the LIKE and ISNULL in your WHERE clause instead of the CAST and it should improve.

Note that the length of your text is not 16, that's the length of the text pointer, if you use the datalength function you'll get the true length of the actual text.
Go to Top of Page

miranwar
Posting Yak Master

125 Posts
Posted - 2006-11-07 : 14:07:18
How about Kirstens's suggestion:

UPDATE TR
SET TR.Filled = 1
FROM tblHighlights TH
INNER JOIN #Test TR ON TR.K_EL_ID = TH.iExhibitorID AND TR.K_EV_ID = TH.iEventID
WHERE tDescription IS NULL
OR DATALENGTH(tDescription) = 0
OR vcImage IS NULL
OR vcImage = ''
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-11-07 : 14:11:58
Is that not the opposite of what you want?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-07 : 14:15:05
quote:
Originally posted by miranwar

How about Kirstens's suggestion:

UPDATE TR
SET TR.Filled = 1
FROM tblHighlights TH
INNER JOIN #Test TR ON TR.K_EL_ID = TH.iExhibitorID AND TR.K_EV_ID = TH.iEventID
WHERE tDescription IS NULL
OR DATALENGTH(tDescription) = 0
OR vcImage IS NULL
OR vcImage = ''

That should be good, another possibility
UPDATE #Test              
SET TR.Filled = 1              
WHERE EXISTS (SELECT * FROM tblHighlights TH              
WHERE (#Test.K_EL_ID = TH.iExhibitorID AND #Test.K_EV_ID = TH.iEventID) 
      AND tDescription IS NULL
      OR DATALENGTH(tDescription) = 0
      OR vcImage IS NULL
      OR vcImage = '')
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-07 : 14:21:01
quote:
Originally posted by Peso

Is that not the opposite of what you want?


Peter Larsson
Helsingborg, Sweden

Peter's right, so my query would become
UPDATE #Test              
SET TR.Filled = 1              
WHERE EXISTS (SELECT * FROM tblHighlights TH              
WHERE (#Test.K_EL_ID = TH.iExhibitorID AND #Test.K_EV_ID = TH.iEventID) 
      AND ((tDescription IS NOT NULL
      AND DATALENGTH(tDescription) > 0)
      OR (vcImage IS NOT NULL
      AND DATALENGTH(vcImage) > 0)))
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-11-07 : 14:26:56
What happens if tDescription becomes NULL and vcImage too?
When will TR.Filled be 0 again?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-11-07 : 14:45:24
"but surely this should not have impacted the server all day"

yes, it might well have done - see other thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74581

Basically if you increase the size of your database by 2GB 9as I calculated it for your database) AND there is reasonable user activity on the database AND you have an "average" server - rather than a HumDinger - then IME it will take A Long Time to extend the database. As per other thread change the 20% AutoGrow to, say, 50MB-100MB.

"Is that not the opposite of what you want?"

Sorry about that, didn't read the question ...

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-11-07 : 14:52:47
snSQL:

AND DATALENGTH(vcImage) > 0

might be better (assuming that its VARCHAR and not TEXT) as:

AND vcImage <> ''

to take advantage of any index on vcImage

"What happens if tDescription becomes NULL and vcImage too?
When will TR.Filled be 0 again?"

Peso: I was going to propose a Trigger earlier when there was talk of Computed Columns ... but I thought better of it! mainly because TR.Filled is on #TEST which I presume is only for a short-duration purpose.

Kristen
Go to Top of Page

miranwar
Posting Yak Master

125 Posts
Posted - 2006-11-07 : 14:55:40
Thanks I'll try these out should i use DBCC DROPCLEANBUFFERS after each test?
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts
Posted - 2006-11-07 : 14:59:22
quote:
AND DATALENGTH(vcImage) > 0

might be better (assuming that its VARCHAR and not TEXT) as:

AND vcImage <> ''

Absolutely, sorry, I got fixated on the text column. Although given that it's a not equal, an index probably won't be used anyway.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-11-07 : 15:07:54
not withstanding that it probably isn't indexed anyway!
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site