update query performance is diffent in two differnt database of the same server in sql server2008

Question

update query performance is diffent in two differnt database of the same server in sql server2008

dsh_roy

Old Hand

Points: 335
More actions
June 29, 2012 at 11:57 am

#277137

Hi Friends,
I am running the following piece of code in on DatabaseA and also on DatabaseB.
DECLARE @MaxLoadKey int,
@dttime DATETIME,
@id VARCHAR(100) = 'xyz'
SET @dttime = GETDATE()
SELECT @MaxLoadKey = MAX(Loadkey) FROM Load
UPDATE TableA
SET mkey = B.Mkey
,LAST_UPDATE_DATE = @dttime
,LAST_UPDATE_BY = @id
FROM TableA A
JOIN TableB B ON b.xid = A.xid
WHERE a.Loadkey = @MaxLoadKey
The issue here is, when I run this query on the DatabaseA it took about 10-12 sec.
but same piece of code when I run on the databaseB, it is taking more than an hour.
DatabaseA and Database B on the same server.
could anybody tell me what is going wrong?
Thanks alot
disha

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

dsh_roy (6/29/2012)
Hi Friends,
I am running the following piece of code in on DatabaseA and also on DatabaseB.
DECLARE @MaxLoadKey int,
@dttime DATETIME,
@id VARCHAR(100) = 'xyz'
SET @dttime = GETDATE()
SELECT @MaxLoadKey = MAX(Loadkey) FROM Load
UPDATE TableA
SET mkey = B.Mkey
,LAST_UPDATE_DATE = @dttime
,LAST_UPDATE_BY = @id
FROM TableA A
JOIN TableB B ON b.xid = A.xid
WHERE a.Loadkey = @MaxLoadKey
The issue here is, when I run this query on the DatabaseA it took about 10-12 sec.
but same piece of code when I run on the databaseB, it is taking more than an hour.
DatabaseA and Database B on the same server.
could anybody tell me what is going wrong?
Thanks alot
disha

Please post the DDL for the tables including indexes and the actual execution plans as .sqlplan files.

Gullimeel Hall of Fame Points: 3871 More actions · Answer 2

Are the tables same in size, number of columns,indexes etc in both databases? Are statistics upto date in both databases for both of these tables?

As mentioned, by Lynn, post the DDL, and both plans and if possible post the following as well.

select * from sys.dm_db_index_physical_stats(db_id('databasea'),object_id('tablea'),null,null,'DETAILED')

select * from sys.dm_db_index_physical_stats(db_id('databasea'),object_id('tableb'),null,null,'DETAILED')

select * from sys.dm_db_index_physical_stats(db_id('databaseb'),object_id('tablea'),null,null,'DETAILED')

select * from sys.dm_db_index_physical_stats(db_id('databaseba'),object_id('tableb'),null,null,'DETAILED')

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

dsh_roy Old Hand Points: 335 More actions · Answer 3

Hi,

I have created non_clusted indexes on the columns that I am using in the update query.

now is fine. Now running in 30 sec.

But in the testdb(databasea) does not indexes,but still performance of the query is 30 sec.

I am trying to understand the execution plan of the both, i will send you.

Thanks alot for you hints.

Disha