SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How would you get rid of this cursor?!


How would you get rid of this cursor?!

Author
Message
WebTechie
WebTechie
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 993
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
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24083 Visits: 9730
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
WebTechie
WebTechie
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 993
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
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24083 Visits: 9730
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
WebTechie
WebTechie
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 993
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
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24083 Visits: 9730
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
WebTechie
WebTechie
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 993
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.
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24083 Visits: 9730
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
GSquared
GSquared
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24083 Visits: 9730
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
WebTechie
WebTechie
Right there with Babe
Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)Right there with Babe (797 reputation)

Group: General Forum Members
Points: 797 Visits: 993
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search