update taking more time to execute....

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 2005 Forums

Transact-SQL (2005)

update taking more time to execute....

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-01-28 : 07:14:17

Hi,

There is an UPDATE query being run in our application.

The UPDATE is based on join.

I run the query on the dev server it is taking less time i.e 1 min 16 secs.

But when we try to execute the same in the PROD it is taking more than an hour and is

being executed executed and so on....

Can anyone tell when UPDATE can hang????

I used sp_who only 2 connections are there other than mine.

I killed those and ran once again. But no progress.

How to fix this???

I also tried to run the below query to get the table counts. it is runing .....

-- getting the rowcounts in each table
SELECT o.name, i.rowcnt
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE indid < 2 and (OBJECTPROPERTY(object_id(o.name), N'IsTable')) = 1
order by 2 desc

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 07:49:40

Sounds like your indexes are fragmented, so you Rebuild Indexes and Update Statistics (WITH FULLSCAN) regularly?

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-01-28 : 08:00:36

Here is the table count and UPDATE look like this

select count(*) from table_1
select count(*) from table_2
select count(*) from table_3
select count(*) from table_4

/*
39621
39340
18975
40335
*/

UPDATE dba.table_1
SET gp = c.gp,
opp= c.opp,
Scale= c.Scale
FROM dba.table_1 a
INNER JOIN
dba.table_2 b ON a.ID = b.ID
INNER JOIN
dba.table_3 c ON b.fID = c.fID
INNER JOIN
(SELECT a.ID, RTRIM(LTRIM(a.ID)) as fID,
a.demID,
b.mid AS gp,
c.mid AS op,
d.mid AS oom,
e.mid AS omid3,
f.mid AS oppHrs,
g.mid AS uid
FROM dba.table_3 a
LEFT JOIN table_4 b ON a.ID = b.ID and b.ID = 'AA5699'
LEFT JOIN table_4 c ON a.ID = c.ID and c.ID = 'AA5699'
LEFT JOIN table_4 d ON a.ID = d.ID and d.ID = 'AA5699'
LEFT JOIN table_4 e ON a.ID = e.ID and e.ID = 'AA5699'
LEFT JOIN table_4 f ON a.ID = f.ID and f.ID = 'AA5699'
LEFT JOIN table_4 g ON a.ID = g.ID and g.ID = 'AA5699'
WHERE (a.gp = 1 OR a.op= 1 OR a.op = 1)) AS D
ON c.ID = D.ID AND c.ID = D.ID AND c.dID = D.dID

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-01-28 : 08:04:58

How can i say whether my indexes are fragmented???

Any query which says "if it is this,.. then the index is defragmented" and we have to go for index re-builds.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-01-28 : 08:10:54

How can i confirm whether my update stats are done correctly or not?

Because i have a job which runs the update stats midnite. it shows it is completed successfully.

But when i run the below query it shows nothing , it is executing executing ...... I think some problem is there
but dono what is going on.

-- getting the rowcounts in each table
SELECT o.name, i.rowcnt
FROM sysindexes i join sysobjects o on i.id = o.id
WHERE indid < 2 and (OBJECTPROPERTY(object_id(o.name), N'IsTable')) = 1
order by 2 desc

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 08:11:50

DBCC SHOWCONTIG

I use the options:

WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

I think this is still OK under SQL 2005, there is some System Table stuff you can query, but I think that is only in SQL 2008.

Sorry Frank, didn't spot that it was you posting when I replied. If this is a DEV machine just rebuild the indexes and update stats - then you know you have perfect tables / indexes, and the Query Plan will be optimal

If its production hopefully it has Housekeeping for all this sort of stuff, but if not be careful running the housekeeping as it may block the server (best done during "quiet time"!)

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 08:13:26

"Because i have a job which runs the update stats midnite. it shows it is completed successfully."

Maybe it is only using a Sample? Might not be enough for accurate creation of best Query Plan.

UPDATE STATISTICS ... WITH FULLSCAN

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-28 : 08:28:09

I can't ge the query to parse "D.dID isn't recognized".

N 56°04'39.26"
E 12°55'05.63"

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-01-29 : 09:11:54

I removed the ltrim and rtrim fuctions in the subquery and executed the update then it is getting executed very fast with 2 mins.

As per my knowledge, the indexes will not be used if we use functions on the column on which index is defined.

One more thing is, we shud Avoid enclosing Indexed Columns in a Function in the WHERE clause.

But how it is affecting in my query! Am just SELECTing the column with ltrim(rtrim(columnname)) not using in the WHERE clause.

I tried to execute the query again with LTRIM(RTRIM(Column)), it is executing ............... more than 5 hrours . Dono what is happening inside sqlserver.

One more important point to mention, once i execute the query without the trim functions and again immediately if i execute it with TRIM functions this time, it is executing with mins.

What could be reason for that??

One more thing...
I rebuilded the indexes with FILLFACTOR = 70 and retained the LTRIM and RTRIM functions and executed but same old story. no use. :(

Again, i removed the FILLFACTOR AND most importantly i removed the TRIM functions and ran it again. It executed within seconds.

Any comments or suggestions about the behavior.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-29 : 09:20:25

Using function on indexed columns, makes the index useless and forces a scan instead of a seek.

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 12:12:38

"But how it is affecting in my query! Am just SELECTing the column with ltrim(rtrim(columnname)) not using in the WHERE clause."

Maybe query (i.e. including SELECT) is "covered" by the Index. When you add Function() in the SELECT it is no longer covered.

Have a look at the Query Plan in both cases?

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-01-31 : 10:25:02

Do we have, the function based indexes concept in sql server like we have it in ORACLE 8i onwards?

To resolve this problem, i suggested them to prior running the query, run an update saying

UPDATE tname
set col = ltrim(rtrim(col));

Alternately, while inserting from front - end only, make sure you trim the column value and insert the data.

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 06:45:39

"Do we have, the function based indexes concept in sql server like we have it in ORACLE 8i onwards?"

I don't think so, but I haven't checked SQL 2008.

Given the amount of work that MS have done to allow "rubbish" SQL to be processed intelligently (e.g. parametrising dynamic SQL when it is not posted with parameters so that the query plan is reused) I'm surprised they haven't tackled simple Function wrapping of indexes columns ... but maybe its harder that I think - I guess for RTrim(LTrim(MyIndexedColumn)) something is going to have to walk the entire index ... but for RTrim(MyIndexedColumn) and Index-Range would be OK (similar to MyColumn LIKE 'FOO%')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources