Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

About RowLock

How i have to lock the row while updation of the row while that row is
accessed by various clients and also how i have to release the lock
Hello -
I would like to provide you couple of reference, hope that helps you.
1. Using Hints in Query
-- This Statement will not produce any locks to the Author Table in Pubs
Database.
Select * from pubs.dbo.authors with (NOLOCK)
Similarly you can use with (ROWLOCK),with (TABLOCK),with(PAGLOCK) in Insert
or Update Statements.
Please check different Isolation Level Mechanism incorporated in SQL Server
Using SP_INDEXOPTION
You can override how SQL Server performs locking on a table by using the
SP_INDEXOPTION command. Below is an example of code you can run to tell SQL
Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
Check SP_INDEXOPTION for details in Books On Line in SQL Server.
Thanks
Surajit
"balakarthik" wrote:

> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock
|||Actually I think the first answer is that you do not have to do anything...
When you update a row, SQL will automatically acquire the locks to protect
the row.
If you are changing a row in a user defined transaction ( Begin tran -
commit) SQL will hold the lock for the duration of the transaction... If you
wish to control locking yourself for some reason, the hints etc is the way..
most of the time I do not use hints - use them only if the standard locking
scheme does not give you the protection you need.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:458E7B1F-D387-4771-B844-02B4B8EDC4E0@.microsoft.com...
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock

About RowLock

How i have to lock the row while updation of the row while that row is
accessed by various clients and also how i have to release the lockHello -
I would like to provide you couple of reference, hope that helps you.
1. Using Hints in Query
-- This Statement will not produce any locks to the Author Table in Pubs
Database.
Select * from pubs.dbo.authors with (NOLOCK)
Similarly you can use with (ROWLOCK),with (TABLOCK),with(PAGLOCK) in Insert
or Update Statements.
Please check different Isolation Level Mechanism incorporated in SQL Server
Using SP_INDEXOPTION
You can override how SQL Server performs locking on a table by using the
SP_INDEXOPTION command. Below is an example of code you can run to tell SQL
Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
Check SP_INDEXOPTION for details in Books On Line in SQL Server.
Thanks
Surajit
"balakarthik" wrote:
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock|||Actually I think the first answer is that you do not have to do anything...
When you update a row, SQL will automatically acquire the locks to protect
the row.
If you are changing a row in a user defined transaction ( Begin tran -
commit) SQL will hold the lock for the duration of the transaction... If you
wish to control locking yourself for some reason, the hints etc is the way..
most of the time I do not use hints - use them only if the standard locking
scheme does not give you the protection you need.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:458E7B1F-D387-4771-B844-02B4B8EDC4E0@.microsoft.com...
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock

About RowLock

How i have to lock the row while updation of the row while that row is
accessed by various clients and also how i have to release the lockHello -
I would like to provide you couple of reference, hope that helps you.
1. Using Hints in Query
-- This Statement will not produce any locks to the Author Table in Pubs
Database.
Select * from pubs.dbo.authors with (NOLOCK)
Similarly you can use with (ROWLOCK),with (TABLOCK),with(PAGLOCK) in Insert
or Update Statements.
Please check different Isolation Level Mechanism incorporated in SQL Server
Using SP_INDEXOPTION
You can override how SQL Server performs locking on a table by using the
SP_INDEXOPTION command. Below is an example of code you can run to tell SQL
Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
GO
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
GO
Check SP_INDEXOPTION for details in Books On Line in SQL Server.
Thanks
Surajit
"balakarthik" wrote:

> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock|||Actually I think the first answer is that you do not have to do anything...
When you update a row, SQL will automatically acquire the locks to protect
the row.
If you are changing a row in a user defined transaction ( Begin tran -
commit) SQL will hold the lock for the duration of the transaction... If you
wish to control locking yourself for some reason, the hints etc is the way..
most of the time I do not use hints - use them only if the standard locking
scheme does not give you the protection you need.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"balakarthik" <balakarthik@.discussions.microsoft.com> wrote in message
news:458E7B1F-D387-4771-B844-02B4B8EDC4E0@.microsoft.com...
> How i have to lock the row while updation of the row while that row is
> accessed by various clients and also how i have to release the lock

about processXMLdata sample,how to insert a return in end of each row data of the result text fi

I am newbie, having a job to handle some XML files into sql server 2005. In the procXMLdata sample,,pls tell me how to insert a return in end of each row data of the result text file?

I am not familiar with this sample, but my guess is that you need to use Ragged Right Flat File format and choose {CR}{LF} as the row delimiter.

Give us more of the problem's context, if this is not what you are looking for.

Thanks.

|||

hi,

Thanks for your reply.

The processXMLdata sample was put in the sample directory of ms sql server,which just converted a xml file into a txt file ,using two xml task. The first was to preform XPATH method,output a variable, and the second xml task was to do XSLT method ,input the variable above, output a txt.

I know XML little , i guess the problem may be on propery of the second XML task.

About PL\SQL Trigger...

Hi all, I just wondering, when we should use Trigger statement and Trigger row? Because I'm not really understand in this part.
emm, where I can get complete free tutorial about this SQL Trigger?
Thanks in advance.the difference is when you insert more row in a time (tipically with a insert into ... select..)
Trigger statement is tipically faster, because is called one time, trigger row are tipically more easy to write because you have to manage only a row a time.
Trigger statement can be usefull also for checking a complex check that involve a set of row as a whole (for example chaecking the sum of a column must to be zero...)
pay attention that not every rdbm has both: for example, MS SQL has only the trigger statement, sqlite "for each row " only...|||You mention PL/SQL, so you are talking about Oracle triggers. See the free online documentation here:

Application Developer's Guide (http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg.htm#376)

Concepts (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c18trigs.htm#12312)|||Regarding your first question:

Statement-level triggers fire once only per statement, and cannot refer to specific row values using :OLD and :NEW
Row-level triggers fire once per row affected by the statement, and can refer to specific row values using :OLD and :NEW