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 the presence of alter procedure?

Is it a good practice to leave the alter procedure statement in a stored
procedure? Does it affect the execution in any way? Is it common to comment
the statements out until needed again?
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/I'm a bit . Where would you remove it from? Once the procedure is
altered, the "ALTER" is not really there anymore.
To illustrate this, try the following on your end:
CREATE PROCEDURE proc_x
AS
RETURN
GO
ALTER PROCEDURE proc_x
AS
RETURN 1
GO
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'proc_x'
GO
DROP PROCEDURE proc_x
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:utDcT6WnGHA.1592@.TK2MSFTNGP04.phx.gbl...
> Is it a good practice to leave the alter procedure statement in a stored
> procedure? Does it affect the execution in any way? Is it common to
> comment the statements out until needed again?
> --
> <%= Clinton Gallagher
> NET csgallagher AT metromilwaukee.com
> URL http://www.metromilwaukee.com/clintongallagher/
>

About the Complex SQL Statement

Belows are the data of table T1

field1 field2 field3 Value
F1 F2 F3 A
F1 F2 F3 B
F1 F2 F3 C
... ... ....
F1 F2 F3 Z

Can any bright person help to script the SQL to extract above data set and present as below ?

field1 field2 field3 Value
F1 F2 F3 ABC.....Z

Thanks for helpThought this woiuld be a good exercise...WHY you would want to do this, I have no idea...

Sorry, had to use a cursor....any non cursor solutions out there?

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(2), Col2 char(2), Col3 char(2), Col4 char(1))
GO

INSERT INTO myTable99(Col1, Col2, Col3, Col4)
SELECT 'F1', 'F2', 'F3', 'A' UNION ALL
SELECT 'F1', 'F2', 'F3', 'B' UNION ALL
SELECT 'F1', 'F2', 'F3', 'C' UNION ALL
SELECT 'F1', 'F2', 'F3', 'Z' UNION ALL
SELECT 'F1', 'F2', 'F5', 'A' UNION ALL
SELECT 'F1', 'F2', 'F6', 'B' UNION ALL
SELECT 'F1', 'F2', 'F7', 'C' UNION ALL
SELECT 'F1', 'F2', 'F8', 'Z'
GO

SET NOCOUNT ON

DECLARE myCursor99 CURSOR
FOR
SELECT DISTINCT Col1, Col2, Col3
FROM myTable99

DECLARE @.Col1 char(2), @.Col2 char(2), @.Col3 char(2), @.Col4 varchar(7000)

DECLARE @.x TABLE(Col1 char(2), Col2 char(2), Col3 char(2), Col4 varchar(7000))

OPEN myCursor99

FETCH NEXT
FROM myCursor99
INTO @.Col1
, @.Col2
, @.Col3

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.Col4 = COALESCE(@.Col4+ ', ', '') + CAST(Col4 AS varchar(6))
FROM myTable99
WHERE Col1 = @.Col1
AND Col2 = @.Col2
AND Col3 = @.Col3

INSERT INTO @.x(Col1, Col2, Col3, Col4)
SELECT DISTINCT @.Col1, @.Col2, @.Col3, @.Col4
FROM myTable99
WHERE Col1 = @.Col1
AND Col2 = @.Col2
AND Col3 = @.Col3

FETCH NEXT
FROM myCursor99
INTO @.Col1
, @.Col2
, @.Col3

SELECT @.Col4 = null
END

CLOSE myCursor99
DEALLOCATE myCursor99

SET NOCOUNT OFF

SELECT * FROM @.x

SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO|||CREATE FUNCTION dbo.f_ConcatVals(@.f1 char(2), @.f2 char(2), @.f3 char(3)) RETURNS varchar(8000) AS BEGIN
DECLARE @.s varchar(8000)
SET @.s = ''
SELECT @.s = @.s + Value
FROM T1
WHERE Field1 = @.f1 AND Field2 = @.f2 AND Field3 = @.f3
ORDER BY Value
RETURN @.s END

SELECT DISTINCT Field1, Field2, Field3, dbo.f_ConcatVals(Field1,Field2,Field3) Value
FROM t1

About Sql Statement

I am writing Procedure in SQL Server 2000, and i am giving three inputparameters ie: Account number and from date and to date.but in will give input to procedure as account number or from date and todate.

So in select command how can i write, ie i will give input any one ie accno or ftomdate and to date. i will write sql query which i write but it is giving error

Select
* From Mf_Tran_Reg
Where
mft_fundcd='RMF' and mft_purred='P'
if @.Folio = ''
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
end

Hi Majid,

CREATE PROCEDURE SomeProcedure
(
@.mft_accno INT = NULL,
@.Fdate DATETIME = NULL,
@.tdate DATETIME = NULL
)
AS
Select
* From Mf_Tran_Reg
Where
mft_fundcd='RMF' and mft_purred='P'
and
( mft_procdate between @.Fdate and @.tdate
OR mft_accno= @.Folio
)

As from your description both will not be passed, onyl either the Date or the number. If AccountNumber or dates can be NULL you will have to add the AND columnn is NULL to not give back the Nulled values.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

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