Performance tuning an update statement in SQL SERVER -


i have 2 update statements. when executed both taking more 12hrs ssis. indexes on tables disabled before executing update statements. need improve performance. need suggestions:

1) first update statement

update db1.table1 set db1.table1.col1 = db2.table2.col1 db1.table1, db2.table2 db1.table1.col2 = db2.table2.col2

2) second update statement

update table1 set table1.col3 = 0 table1 table1.col3 null

can update in batches improving performance of 1st update statement? see having default on col3 sufficient instead of running second update. not sure if effects insert queries. table having lot of data. not sure of altering table include default value on column table having lot of data.

please provide suggestions performance tune above statements. please note not having proper permissions on db verify execution plan.

first, know update taking longer. but, indexes not enemy when doing updates.

the first update:

update t1     set t1.col1 = t2.col2     db1.table1 t1 join          db2.table2 t2          on t1.col2 = t2.col2;

this update needs index on db2.table2(col2). otherwise, need nested loop join.

the second update:

update table1 set table1.col3 = 0 table1 table1.col3 null

is bit trickier. updating column in where clause. sense if relatively few of values null -- few percent -- index on table1(col3) help. if many of columns null, index less useful. without index, requires full table scan , should not absurdly slow.

you may find batching updates on table performance.


Comments

Post a Comment

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

i have following sample code used pre-decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(--c); cout << c << endl; return; } } this function give output input 4 : dp3 dp2 dp1 dp0 0 1 2 3 but when use post decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(c--); cout << c << endl; return; } } output goes in infinite loop dp4 can explain me in detail why happens ? this happens because function(c--) called same value of c , when finishes c decremented. recursively called, hence called same value no chance of return , hit stack overflow error. assume this, int x = 1, y = 0; y = x++ result y == 1 , x == 2. if y = ++x , bot x , y 2.
Read more

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

i added new project. made mistake in projectname.conf file in sites-available apache2, led me reinstalling apache2. after reinstalling , re-enabling projects on every page of every project .. <body> run(); </body> .. i've run composer update, restarted service, restarted pc, checked config files, tried disabling new project. there nothing in error log, , access.log: 127.0.0.1 - - [15/may/2015:01:40:47 +0200] "get / http/1.1" 304 181 "-" "mozilla/5.0 (x11; ubuntu; linux x86_64; rv:38.0) gecko/20100101 firefox/38.0" maybe i'm forgetting apache2 reinstall? don't think did different last time installed it. looks haven't setup php correctly. take @ this great guide , maybe you're forgetting link php config apache2.
Read more

php - How can I echo out this array? -

i don't know how go grabbing data in variable , put foreach loop. class tree{ private $info = array( array('name'=>'jim','state'=>'nu','company'=>'nu','phone'=>array('cell'=>'5615111111','office'=>'5611111111'),'email'=>array('primary'=>'exs@example.com','ex@there.com')), array('name'=>'joe smith','city'=>'phoenix','phone'=>'4805551111','email'=>'jsmith@some_email.com'), array('name'=>'john doe','city'=>'chandler','company'=>'doe co.','email'=>array('jdoe@gmail.com','personal'=>'email@email.com'),'phone'=>'6025550002') ); } if you're inside of class, can access private variable using $this->variablename . example, i...
Read more