Re: SQL Taking too long .

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 02/22/05 

Date: Mon, 21 Feb 2005 20:41:18 -0500

Where is @Budget_Amount3 declared?

To speed this up, simply get rid of the cursor... here's the same logic,
cursorless:

update OT_ACTUAL_BUDGET_BALANCES
set [Budget_Amount3]=@Budget_Amount3
WHERE EXISTS
    (SELECT *
    FROM BUDGET_BALANCES B
    WHERE B.account_code = OT_ACTUAL_BUDGET_BALANCES.account_code
        AND B.district_code='OTML')

INSERT OT_ACTUAL_BUDGET_BALANCES
SELECT B.account_code
FROM BUDGET_BALANCES B
WHERE NOT EXISTS
    (SELECT *
    FROM OT_ACTUAL_BUDGET_BALANCES
    WHERE B.account_code = OT_ACTUAL_BUDGET_BALANCES.account_code
        AND B.district_code='OTML') 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"SQL Taking to LONG" <SQL Taking to LONG@discussions.microsoft.com> wrote in
message news:A428243E-C987-47F4-87C4-16D195A6C49F@microsoft.com...
> Can someone please help me out. My sql is taking alsmost 16 hours to
complete
> and is not exceptable. Am creating a cursor from a table A which has about
> 120,000 record. For each record if this record exists in table B then
update
> the columns otherwise insert a new record in this table B.
>
> I was able to reduce the time to 8 hours by creating an index on table B
> however I still want to reduce this further. Attach is my sql. This is
urgent
> and if someone can help it will be greatly appreciated..
>
> Thanks
>
> --*******************************
> /**************************budget_balance row
> olders********************************/
> set nocount on
> declare @district_code [nvarchar] (4)
> declare @account_code [nvarchar] (24)
>
> /*other variables*/
> DECLARE @TranBudget VARCHAR(20)
> SELECT @TranBudget = 'Budgets'
>
> declare @rowcount int
> declare @cnt int
> declare @budgetTotalRows int
> declare @msg nvarchar(200)
> set @rowcount = 1
> set @cnt=0
> set @budgetTotalRows=(select count(*) from budget_balances)
> /*budget cursor*/
> print 'Starting Actual Budget Balances...'
>
> DECLARE budget_cursor CURSOR FOR
> SELECT account_code
> FROM BUDGET_BALANCES
> WHERE district_code='OTML'
>
> OPEN budget_cursor
>
> FETCH NEXT FROM budget_cursor
> INTO @account_code
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> BEGIN TRANSACTION @TranBudget
> set @msg=N'RowNumber: ' + rtrim(cast(@rowcount as char)) + N'/' +
> rtrim(cast(@budgetTotalRows as char)) --+ N' -->Processing Account No:' +
> cast(@account_code as char)
> set @cnt = @cnt + 1
> print @msg
>
> /*Check if this budget has a record existing in OT_ACTUAL_BUDGET_BALANCES
> table*/
> if exists (select account_code from OT_ACTUAL_BUDGET_BALANCES
>            where account_code=@account_code
>            )
>
> begin
> /*Only update OT_ACTUAL_BUDGET_BALANCES table*/
>       update OT_ACTUAL_BUDGET_BALANCES set
[Budget_Amount3]=@Budget_Amount3
>
>       where account_code=@account_code
>
>
> set @rowcount = @rowcount + 1
>
> end
>
> else
> begin
> /*Insert this record*/
>        insert into OT_ACTUAL_BUDGET_BALANCES (account_code)
>        values(@account_code)
> set @rowcount = @rowcount + 1
> end
>
> if @rowcount=1000
> --print 'Commiting the A 1000 records-->::' + @msg
> begin
> COMMIT TRANSACTION Budgets
> set @rowcount=1
> end
> /*next fetch*/
> FETCH NEXT FROM budget_cursor
> INTO @account_code
>
> END
> CLOSE budget_cursor
> DEALLOCATE budget_cursor
> GO
> --*********************************
>
>
>
>

Relevant Pages

  • Re: Declare cursor problem in ISeries
    ... The cursor declaration is as follows: ... SQL statement is not allowed for one of the following reasons: ... DECLARE PROCEDURE, DESCRIBE, GET DIAGNOSTICS, SIGNAL, RESIGNAL, and ... The version information of Iseries is: ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL Server steht auf der Bremse
    ... DECLARE targetID_cursor CURSOR LOCAL FORWARD_ONLY FOR ... INSERT INTO .... ... skaliert der SQL Server über alle 4 Prozessoren. ...
    (microsoft.public.de.sqlserver)
  • Re: Declare cursor problem in ISeries
    ... When I try to declare the cursor in Iseries Run SQL scripts tool I get ...
    (comp.sys.ibm.as400.misc)
  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: Optimize function that uses cursors
    ... > The function can be made recursive as there are no much recursions (we ... > groups and ancestor groups (i.e. the parents of the parents and so on) for ... > a recursive call in the select of the cursor), and in the cursor look it ... > declare @more bit ...
    (microsoft.public.sqlserver.programming)