SQL Server replace cursor that iterates through X Accounting DBs

Question

SQL Server replace cursor that iterates through X Accounting DBs

JReed36

Valued Member

Points: 62
More actions
December 15, 2014 at 4:26 pm

#313083

Hi and thank you for any help.
I have been tasked with updating our Stored Procedures that loop through accounting DBs on another server or the same SQL Server, to pull account and vendor info for each company. What the person did is create a cursor that places the DBName in a variable and adds it to dynamic sql with an insert. However in some instances these inserts take 5 mins or more. As such I need to improve performance so I am looking into different methods of getting the data.
There is a CompanyCode table with the DBName and Systems table that has the server name which the cursor picks up and iterates through for each database. Is there a more efficient way to do this?
For example here is part of the dynamic sql the cursor is iterating through:
SET @SQLQuery = 'INSERT INTO#CompanyVendorsList SELECT
RTRIM(V.[VENDORID]) VendorCode
,RTRIM(V.[VENDNAME]) VendorName
,RTRIM(V.[ADDRESS1]) Address1
,RTRIM(V.[ADDRESS2]) Address2
,RTRIM(V.[ADDRESS3]) Address3
,RTRIM(V.[CITY]) City
,RTRIM(V.[STATE]) State
,RTRIM(V.[ZIPCODE]) ZipCode
,RTRIM(V.[COUNTRY]) Country
,RTRIM(V.[CURNCYID]) DefaultCurrencyCode
,RTRIM(A.[ACTNUMST]) DefaultExpenseAccount
,RTRIM(A.[ACTNUMST]) DefaultExpenseAccountNonFormatted
,RTRIM(Z.[ACTDESCR])DefaultExpenseAccountName
FROM' + @sServerName + '.' +
@DB_Name + '.dbo.PM00200 V ' + 'LEFT OUTER JOIN ' + @sServerName + '.' +
@DB_Name + '.dbo.GL00105 A ' + 'ON A.ACTINDX = V.PMPRCHIX ' + 'LEFT OUTER JOIN ' + @sServerName + '.' +
@DB_Name + '.dbo.GL00100 Z ' + 'ON Z.ACTINDX = A.ACTINDX ' + 'WHERE V.VENDSTTS <> 2 order by V.VENDORID'
EXEC (@SQLQuery)
Is there a way to do this without using a cursor or a loop? There can be anywhere from 1 to 200 company databases that need the Vendor records need to be selected from and insert into a Vendors table in our database. This varies depending on which customer is using the software.
Thank you
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

There's a different method but with up to 200 databases the validations can become complex and the improvement is unnoticeable. This is one of those occasions where a cursor is the way to go. You could change the default options to make it static and read only

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 2

Luis Cazares (12/15/2014)
There's a different method but with up to 200 databases the validations can become complex and the improvement is unnoticeable. This is one of those occasions where a cursor is the way to go. You could change the default options to make it static and read only

+1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JReed36 Valued Member Points: 62 More actions · Answer 3

JReed36

Valued Member

Points: 62

December 23, 2014 at 11:31 am

#1766247

Thank you for the replies. Much appreciated.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

In addition to the previous advice, I would recommend that you grab a copy of those queries and performance test them.

I can see that you are hitting linked servers, and the potential for non-optimal plans is quite high - especially if any of those databases/servers/tables have collations that do not match your local server/database/tempdb collations.

If the query is basically the same on each server/database pair, then I would also consider using the form:

insert #table(explicit column list)

exec(@SQLQuery) at Servername

I am suggesting this because the SELECT will then be forced as a remote query, with no danger of SQL deciding to bring an entire table or two (or three) back to perform the joins.

This may not improve speed, but it will prevent a change in plan causing massive data transfers.

Also, be sure to make your SELECT collation match with your temp table, even to the extent of explicitly using the COLLATE clause in the select and explicitly choosing the best collation for your specific use case, as collation conflicts can cause huge performance problems.

The worst case of this is when joining a local table to a remote one with different collations, when using COLLATE on the join can cause the remote table to be pulled back in its entirety before performing the join, while making sure your local data matches the remote collation can allow remote index seeks. (I sometimes create a local table with the remote collation before performing a remote join from that).

I'm not claiming any of this will help, but it would be best to think about it.

Good luck 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);