Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How would you get rid of this cursor?! Expand / Collapse
Author
Message
Posted Wednesday, April 15, 2009 10:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 269, Visits: 782
Hello,

I've inherited a cursor that is taking a long time. This is run once an hour. It usually finds about 500 records to insert. The problem is that it is querying very big tables. Can anyone give some advice on changing this query from a cursor?


Thank you.




alter PROCEDURE ce_sp_RefreshTables
as

Declare @dbid int, @CompanyName nvarchar(255), @CurrentTable char(7), @sql varchar(5000), @q nvarchar(4), @GPModDate AS DATETIME
Set @q = ''''
Set @CurrentTable = 'GL20000'
Set @GPModDate = GETDATE()

Declare CompanyCursor cursor for
select interid from DYNAMICS..SY01500 where UDCOSTR2 = 'Active' and interid <> 'Audit' order by interid


open CompanyCursor
fetch next from CompanyCursor into @CompanyName

While @@fetch_status = 0

Begin

InsertLoop:

Set @sql = 'USE CE_CUSTOM;
Insert into GL_Transactions
(
SOURCEINTERID,
SOURCETBL,
DEX_ROW_ID,
SOURCDOC,
OPENYEAR,
OPENMTH,
OPENDAY,
ACTINDX,
ACCTTYPE,
ACTNUMBR_1,
ACTNUMBR_2,
ACTNUMBR_3,
ACTNUMBR_4,
ACTNUMBR_5)

Select ' + @q + rtrim(@CompanyName) + @q + ',' +
@q + @CurrentTable + @q + ',' +
'A.DEX_ROW_ID,
A.SOURCDOC,
DATEPART(yyyy, A.TRXDATE),
DATEPART(mm, A.TRXDATE),
DATEPART(dd, A.TRXDATE),
B.ACTINDX,
B.ACCTTYPE,
B.ACTNUMBR_1,
B.ACTNUMBR_2,
B.ACTNUMBR_3,
B.ACTNUMBR_4,
B.ACTNUMBR_5' +

'From ' + rtrim(@CompanyName) + '..' + @CurrentTable + ' A ' +
'Join ' + rtrim(@CompanyName) + '..GL00100 B ON A.ACTINDX = B.ACTINDX ' +
'Where A.TRXDATE > = ' + @q + 'Oct 1, 2004' + @q + ' and ' +
'NOT EXISTS (Select * from CE_CUSTOM..GL_Transactions C Where C.DEX_ROW_ID = A.DEX_ROW_ID and C.SOURCEINTERID = ' +
@q + rtrim(@CompanyName) + @q + ' and C.SOURCETBL = ' + @q + @CurrentTable + @q + ') ' +

'Group By
A.DEX_ROW_ID,
B.ACTINDX,
A.SOURCDOC,
A.TRXDATE,
B.ACTNUMBR_1,
B.ACTNUMBR_2,
B.ACTNUMBR_3,
B.ACTNUMBR_4,
B.ACTNUMBR_5'

Exec (@sql)

If @CurrentTable = 'GL20000'
Begin
Set @CurrentTable = 'GL30000'
GoTo InsertLoop
End
Else
Begin
Set @CurrentTable = 'GL20000'
End

Fetch next from CompanyCursor into @CompanyName

End

Deallocate CompanyCursor

GO





Post #697734
Posted Wednesday, April 15, 2009 11:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
To make sure I'm understanding this, it looks like you have a tables in a number of different databases that you're querying, and inserting some data into a single master table. The purpose of the cursor is to step through each database. Is that correct?

Beyond that question, it would be very helpful if you could provide create scripts for the tables involved, and some sample data that can be inserted into them.

Multi-database dynamic queries are one of the places where cursors often do work reasonably well, but the query can probably be improved nonetheless.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #697747
Posted Wednesday, April 15, 2009 11:16 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 269, Visits: 782
Yes, that is correct.

1) First it creates a result set of companies which could be 500 rows.
2) Then it looks at each company's database
3) It finds either the GL20000
4) If it finds rows, the inserts those rows
5) Then it loops back and finds the records using the GL30000 table
6) Then it goes to the next company row


The create statements for the tables are quite big. I have to see if I can post things like the create scripts and then create some sample data. I know I can't post actual data.

Thanks for helping. It may be that we actually need a cursor. I was always taught cursors were a bad thing.


Tony



Post #697758
Posted Wednesday, April 15, 2009 11:24 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
One thing you might try is changing the cursor to either "Static Forward_Only", or "Fast_Forward". You're not using it to update the base table, so that will often speed things up a bit.

But I'd be willing to bet the part that's taking all the time is the actual inserts.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #697764
Posted Wednesday, April 15, 2009 11:54 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 269, Visits: 782
It may be, but I think the problem is scrolling throgh the resultset one at a time.

We get a company from the initial resultset, query the GL20000 table and insert rows, then query the GL30000 table and insert rows and then go to the next company.


This may be the right way, but it just seems that we shouldn't have to go one row at a time.

Tony



Post #697786
Posted Wednesday, April 15, 2009 12:10 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
One thing you could try is building a single dynamic string, like this:
create table #T (
ID int identity primary key,
Name varchar(100));

insert into #T (Name)
select 'Ink Inc' union all
select 'ACME Co';

declare @SQL varchar(max);

select @SQL = coalesce(
@SQL + ';' + 'select * from #T where id = ' + cast(id as varchar(10)),
'select * from #T where id = ' + cast(id as varchar(10)))
from #T;

print @SQL;

Result:
select * from #T where id = 1;select * from #T where id = 2


You can build complex queries that way from data in tables, and then instead of "print", used "exec()". That's going to be faster than a cursor on that part.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #697803
Posted Wednesday, April 15, 2009 12:19 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 269, Visits: 782
I've never heard of that before. Creating a table with dynamic strings? Let me try to create a table and see if I understand what you are saying.


Thanks again.



Post #697809
Posted Wednesday, April 15, 2009 12:26 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
You don't create the table with dynamic strings. You build the string with data from a table.

I just created the temp table and put some data in it for a simple proof-of-concept demo.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #697811
Posted Wednesday, April 15, 2009 12:36 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Your dynamic concat would look more like this:
create table #Tables (
TName varchar(100) primary key);

insert into #Tables (TName)
select 'GL20000' union all
select 'GL30000';

declare @SQL varchar(max), @Q char(4);

select @Q = '''';

select @SQL = coalesce(
@SQL +
'begin transaction;
Insert into CE_CUSTOM..GL_Transactions
(
SOURCEINTERID,
SOURCETBL,
DEX_ROW_ID,
SOURCDOC,
OPENYEAR,
OPENMTH,
OPENDAY,
ACTINDX,
ACCTTYPE,
ACTNUMBR_1,
ACTNUMBR_2,
ACTNUMBR_3,
ACTNUMBR_4,
ACTNUMBR_5)

Select ' + @q + rtrim(interid) + @q + ',' +
@q + TName + @q + ',' +
'A.DEX_ROW_ID,
A.SOURCDOC,
DATEPART(yyyy, A.TRXDATE),
DATEPART(mm, A.TRXDATE),
DATEPART(dd, A.TRXDATE),
B.ACTINDX,
B.ACCTTYPE,
B.ACTNUMBR_1,
B.ACTNUMBR_2,
B.ACTNUMBR_3,
B.ACTNUMBR_4,
B.ACTNUMBR_5' +

'From ' + rtrim(interid) + '..' + TName + ' A ' +
'Join ' + rtrim(interid) + '..GL00100 B ON A.ACTINDX = B.ACTINDX ' +
'Where A.TRXDATE > = ' + @q + 'Oct 1, 2004' + @q + ' and ' +
'NOT EXISTS (Select * from CE_CUSTOM..GL_Transactions C Where C.DEX_ROW_ID = A.DEX_ROW_ID and C.SOURCEINTERID = ' +
@q + rtrim(interid) + @q + ' and C.SOURCETBL = ' + @q + TName + @q + ') ' +

'Group By
A.DEX_ROW_ID,
B.ACTINDX,
A.SOURCDOC,
A.TRXDATE,
B.ACTNUMBR_1,
B.ACTNUMBR_2,
B.ACTNUMBR_3,
B.ACTNUMBR_4,
B.ACTNUMBR_5; while @@trancount> 0 commit;',
--
'begin transaction;
Insert into CE_CUSTOM..GL_Transactions
(
SOURCEINTERID,
SOURCETBL,
DEX_ROW_ID,
SOURCDOC,
OPENYEAR,
OPENMTH,
OPENDAY,
ACTINDX,
ACCTTYPE,
ACTNUMBR_1,
ACTNUMBR_2,
ACTNUMBR_3,
ACTNUMBR_4,
ACTNUMBR_5)

Select ' + @q + rtrim(interid) + @q + ',' +
@q + TName + @q + ',' +
'A.DEX_ROW_ID,
A.SOURCDOC,
DATEPART(yyyy, A.TRXDATE),
DATEPART(mm, A.TRXDATE),
DATEPART(dd, A.TRXDATE),
B.ACTINDX,
B.ACCTTYPE,
B.ACTNUMBR_1,
B.ACTNUMBR_2,
B.ACTNUMBR_3,
B.ACTNUMBR_4,
B.ACTNUMBR_5' +

'From ' + rtrim(interid) + '..' + TName + ' A ' +
'Join ' + rtrim(interid) + '..GL00100 B ON A.ACTINDX = B.ACTINDX ' +
'Where A.TRXDATE > = ' + @q + 'Oct 1, 2004' + @q + ' and ' +
'NOT EXISTS (Select * from CE_CUSTOM..GL_Transactions C Where C.DEX_ROW_ID = A.DEX_ROW_ID and C.SOURCEINTERID = ' +
@q + rtrim(interid) + @q + ' and C.SOURCETBL = ' + @q + TName + @q + ') ' +

'Group By
A.DEX_ROW_ID,
B.ACTINDX,
A.SOURCDOC,
A.TRXDATE,
B.ACTNUMBR_1,
B.ACTNUMBR_2,
B.ACTNUMBR_3,
B.ACTNUMBR_4,
B.ACTNUMBR_5; while @@trancount> 0 commit;')
from DYNAMICS..SY01500
cross join #Tables
where UDCOSTR2 = 'Active' and interid <> 'Audit';

print @SQL;
--exec (@SQL);

Test it with the print command, then run it on a test server with the exec command.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #697816
Posted Wednesday, April 15, 2009 1:10 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 269, Visits: 782
Wow!!

GSquared, this is whole new territory for me. I am going to do some tests and try this example.

Thanks so much for your help, ideas and getting me going in the right direction.



Tony



Post #697838
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse