Updating Date-time Field takes long

Question

Post reply

Updating Date-time Field takes long

CrazyMan

SSChampion

Points: 12560
More actions
November 27, 2009 at 5:56 am

#213042

Hi All
There are 2 tables which i try to update from my application, one table 1 has got 10 million records and table 2 has got 5 million records, when i disable table 2 update the process takes 0.2 second to process, when i enable table 2 update, the the whole process takes 11 seconds to update,
Update statement on table 2 is very simple, it just updates 3 datetime column with getdate() with ID as primary key, ID is clustred index and update statement is simple
Update table2 set date1=getdate(),date2=getdate(),date3=getdate where id=@id
Has any one faced any problems like this before
Thanks in advance for all your help
🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'd assume that the columns you're updating in table 2 are part of one or more index.

If so, your update requires an index update as well.

Would it be possible to provide the execution plan for the secon update and/or give any more detailed information regarding the table/index structure?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 2

I second Lutz ideas of posting the Execution Plan?

What do you mean by disabling table2?

You mentioned about size of table1 but there is nothing in your update statement which does action on table1.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

CrazyMan SSChampion Points: 12560 More actions · Answer 3

Thanks Guys, this is been updated from a Java application, i dont know how i can get an exection plan from the application,

i have made the same SQL on SQLMS and attached the execution plan.

sorry how do i attach a file ,

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 4

CrazyMan (11/27/2009)
sorry how do i attach a file ,

When posting a reply, you need to look at the portion below and will find edit attachments.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

CrazyMan SSChampion Points: 12560 More actions · Answer 5

Please find the Execution plan, its not working on Chrome 🙂

LutzM SSC Guru Points: 107049 More actions · Answer 6

Your execution plan does not really support your statement in terms of duration.

It looks like it's done within 13 milliseconds and not 11 seconds a stated in your original post.

The update itself looks pretty simple. So I'm guessing it has to do with the way you assign a value to the parameter @id.

What would you get as execution time if you would run the statement you used to get the execution plan again but this time wrapped in

SET STATISTICS TIME ON

Update PhonePremsTopup Set LastTopupdate=getdate(),LastOutOfCrdate=getdate(),LastFailureDate=getdate() where Phone='447900000000'

SET STATISTICS TIME OFF

?

I'm almost certain you won't see the 11 seconds you mentioned.

So let's get back to the beginning:

Please show us the execution plan of the code you're getting the 11 seconds from.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

CrazyMan SSChampion Points: 12560 More actions · Answer 7

Thanks Lutz, Sorry for the late reply, I have included a primarykey on the table for ID, this has been clustered index already, this has reduced the time to almost half, now its processgin at 6 seconds,

i cant take execution plan for this statement, since this are been updated by Java using Hibernate, Hibernate generates statements on the fly and sends it to SQL server to update, Is there any way i can get this from SQL, if yes please let me know.

Currently the table has a Unique clustered Index on ID and a primary key on the same Column as well.

I have a duubt, Unique cluetered index is the same as primary Key ( am i worng?), why is the process faster if iam using primary key ??

any theories why this is happening.

Thanks once again for your time and patience 🙂

Cheers