UPDATE query taking more time ...

Question

Post reply

UPDATE query taking more time ...

Oracle_91

SSChampion

Points: 14548
More actions
January 28, 2010 at 5:15 am

#218602

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 1

Are you missing indexes on the PROD server? Try updating the statistics on the PROD server.

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 2

Is the indexes in production server same as dev server?

use rowlevel with update and check.

Oracle_91 SSChampion Points: 14548 More actions · Answer 3

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

Also, how can i confirm whether there my update statistics is updated or not.

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

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

OTF SSCertifiable Points: 6104 More actions · Answer 5

Any number of things could cause this including missing indexes/statistics. You can probably eliminate most of the guesswork by posting the query plans from both queries and also the rowcounts being updated on both servers.

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 6

it may also be that there are no indexes in prod or dev. just that dev has tons less data than prod.

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 7

Do you get the results back if you run this simple query?

select count(*) from sysindexes

Oracle_91 SSChampion Points: 14548 More actions · Answer 8

Oracle_91

SSChampion

Points: 14548

January 29, 2010 at 7:16 am

#1110707

No.

Oracle_91 SSChampion Points: 14548 More actions · Answer 9

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.

I have attached the actual UPDATE query and execution plans with and without LTRIM(RTRIM()) functions.

Any comments or suggestions about the behavior.

Lowell SSC Guru Points: 323518 More actions · Answer 10

i think one problem is you are joining the same table 6 times, when it is not necessary:

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

isn't this exactly the same but without 6 joins?

SELECT a.ID, RTRIM(LTRIM(a.ID)) as fID,

a.demID,

b.mid AS gp,

b.mid AS op,

b.mid AS oom,

b.mid AS omid3,

b.mid AS oppHrs,

b.mid AS uid

FROM dba.table_3 a

LEFT JOIN table_4 b ON a.ID = b.ID

WHERE (a.gp = 1 OR a.op= 1)

and b.ID = 'AA5699' ) AS D

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Oracle_91 SSChampion Points: 14548 More actions · Answer 11

I have attached the original UPDATE query and EXECUTION PLAN in my previous post.

I think it the one we need to figure out.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Lowell (1/29/2010)
i think one problem is you are joining the same table 6 times, when it is not necessary:
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
isn't this exactly the same but without 6 joins?
SELECT a.ID, RTRIM(LTRIM(a.ID)) as fID,
a.demID,
b.mid AS gp,
b.mid AS op,
b.mid AS oom,
b.mid AS omid3,
b.mid AS oppHrs,
b.mid AS uid
FROM dba.table_3 a
LEFT JOIN table_4 b ON a.ID = b.ID
WHERE (a.gp = 1 OR a.op= 1)
and b.ID = 'AA5699' ) AS D

I may be wrong, but doesn't the b.ID = 'AA5699' actually make the LEFT INNER JOIN effectively an INNER JOIN?