| Author |
Topic |
|
psfaro
Starting Member
49 Posts |
 Posted - 2010-11-28 : 08:55:41
|
| Hi,I've a SP that sometimes returns TimeoutIt contains a simple Update on a table with about 1 714 726 recordsUPDATE CCTPOCset vdb0=0,vdb1=0,vdb2=0,vdb3=0,vdb4=0,vdb5=0,vdb6=0,vdb7=0,vdb8=0,vdb9=0,vdb10=0,vdb11=0,vdb12=0,vdb13=0,vdb14=0,vdb15=0,vcr0=0,vcr1=0,vcr2=0,vcr3=0,vcr4=0,vcr5=0,vcr6=0,vcr7=0,vcr8=0,vcr9=0,vcr10=0,vcr11=0,vcr12=0,vcr13=0,vcr14=0,vcr15=0where ano=@anoThis only initialites some numeric's fields with 0.The indexes have 0,0 fragmentations and does'nt contain any fields value.Any help ?Regards Pedro Faro |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-28 : 08:58:57
|
Wouldn't it be easier to simply apply defaults to each column setting the default value to 0...would not need to update the table then..ALTER TABLE CCTPOC ADD CONSTRAINT [DF_vdb0] DEFAULT ((0)) FOR [vdb0]
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
psfaro
Starting Member
49 Posts |
Posted - 2010-11-28 : 10:27:38
|
| Hi,I need to call the SP everytime i need to clean the values that are in that fields. or is another way ?Regards Pedro |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-28 : 10:48:20
|
Of course there are other ways...It wasn't clear that you were "cleaning" the data because you said you were initiating it, but perhaps the data should be cleaned on the way into the table...how does the bad data get into the table? and it is getting there, why does it need to be zero'd out?How often do you have to "clean" the fields?Are any of the updated columns included in a clustered index?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
psfaro
Starting Member
49 Posts |
Posted - 2010-11-28 : 11:06:40
|
| This table acumulates values from other tables .Sometimes i have to clear the values (with this SP) and read the other tables to update this fields.This fields only for store numeric data .It's a simple table to save a values for 15 months( 2 fiels for month)vdb0 vcr0, vdb1 vcr1 ...No , there aren't in any index. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-28 : 11:34:52
|
Have you run a trace during the update to see what is going on? What is your timeout setting? How much time passes before the query times out? are the open reads on the table blocking the update?If you are updating the values from another table after resetting/cleaning them out, you can just combine the two queries....and reset the fields to zero and/or update the value from it's sourceside note: table stucture/concept seems flawed as it is aggregating data from other tables (perhaps for presentational reasons?)...Probably better to store the data in 5 or 6 columns, with period dates identifying the months..that way, you wouldn't have to clear them out and update...presumably, once a month has passed, the values don't need to be updated...and based on your description, it appears you would also have to update to shift the data over when month 1 becomes month 0 during month 16..
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
psfaro
Starting Member
49 Posts |
Posted - 2010-11-28 : 12:37:00
|
| This is an accounting application.The table holds values of others tables.I must clearing the values before acumulating them.I'will try to trace the update .When this SP is run there's is no users in the database.Timeout is the defaultMSSQL version : Enterprise Edition R2 10.50.1600This SP runs automatically at 0.00 Sometimes it runs , others time returns TimeOut. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2010-11-28 : 18:42:21
|
what type of index is on CCTPOC(ano)? And have you rebuilt it lately?
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
psfaro
Starting Member
49 Posts |
Posted - 2010-11-28 : 19:48:58
|
| Index is NonClusteredThe keys are ano, cod_conta,tp_conta,cod_ctc,The only condition on SP , is where ano='xxxx'Yes, it's rebuilt frequently in a maintenance plan. |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2010-12-02 : 15:25:30
|
| Is that all the stored procedure does, execute the update statement? Or in other words are you sure that it is the update that is timing out or something else in that stored procedure? Also of interest would be the query timeout settings on that server? |
|
|
|
|
Stored procedure with timeout