Cursor question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor question

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 12:21:49

Hello all. I have a script running that imports about 100,000 rows from one database to another.

The script runs very quickly until the end. All of my records have been pulled over, but the script continues to run. I am wondering if the Close CursorName and Deallocate CursorName is what is hanging up the script from finishing up.

I would be happy to post code but it is quite lengthy! And besides, the code is not the problem. The data is going exactly where I want it to go.

Thanks all!

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 12:25:26

The cursor is causing it to be slow. 100,000 rows is a lot for a cursor to handle. I'm surprised that you see any performance at all with this many rows. For better performance, you'll need to remove the cursor and come up with a set-based solution. A lot of people here can help you on this.

Tara

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-16 : 12:25:52

Are you running this from a scheduled job? There are knowledge base articles that describe hanging jobs and fixes for them.

Jonathan
Gaming will never be the same

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 12:58:04

This is being run by an application that my company developed. Basically, I put my scripts into a folder, tell it which database to run it against, and it handles it. So, in this case, I have 9 scripts that I need to run. I just put them in order and click "Run".

Regarding the cursor handling 100,000 rows, right after this particular script get done running, it fires off the next file which is another cursor that has to move 500,000 rows. I just checked it and it has already moved over 250,000 of them since yesterday night.

This is why I suspect that the cursor itself is not the problem, but rather the Close and Deallocate commands.

I will do some searching on the Set-based methods.

Thanks all!

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 13:01:47

250,000 since yesterday night is rather slow, IMO. How long though are we talking about? What does your cursor do? You don't need to post the code but rather explain what is being done.

Tara

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 13:26:05

In a nutshell, these nine scripts are being used to move data out of a clients database and into our core system. (they just bought our software and now we are migrating their data).

The scripts have to do lot's of things. And each of the cursors has a nested cursor somewhere in it. (their system was completely unnormalized, our's is highly normalized.).

Each of the 9 scripts has about 2500 lines of code in it. Mostly INSERT INTO statements with column names listed on each line. I think it looks cleaner that way.

I am always open to new suggestions.

Aj

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-16 : 13:51:21

Sounds like you've cobbled together a poor man's DTS package.

Jonathan
Gaming will never be the same

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-16 : 14:23:38

quote:
Originally posted by ajthepoolman

I am always open to new suggestions.

Aj

Yo poolman...where you bean?

First off, where does the other data live (come one you can tell us...it's Access right?)

Anyway.

I would dump ALL of the data to separate files...sounds like you did this already.

I would then create a staging database on the same box where the data will eventually end up.

Than take your logic and place it in stored procedures and move the data using set processing (it'll incur logging).

It ~~would~~ might be better to

1. build temporary tables that mirror where the data is going to go(using SELECT * INTO) Getting the data from stage
2. bcp it out to a delimited file
3. then bcp it in to the final table location.

That way you don't have to worry about all the logging...

Oh, and lose ALL of the cursors...

I would guess that this entire process should take, MAX 30 minutes...

MOO

Brett

8-)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 14:29:14

Hey Brett, just haven't needed any help lately!

These tables came from Access. I created databases in SQL and did an import. My scripts pull from the SQL tables.

Am I understanding you correctly that you think I should write stored procs to move that data instead of a cursor to move them?

Aj

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-16 : 15:13:01

What does the CURSOR do?

Very few instances I've seen where you need one *never mind nesting them...ughhh)

And aren't your cursors in stored procedures? Are you running them like a script?

And yes, I would put the steps I listed in to a sproc...

Brett

8-)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 15:22:46

The cursor inserts demographic information (names, address, stuff like that).

For instance, I insert data into tblPerson which is LastName, FirstName, DateOfBirth, SSN. I then take their address and insert it into tblAddress. Then I take the @@Identity values from tblPerson and tblAddress and insert them into tblPerson_Addresses to link the person to the address.

It is contained in a .sql file.

I have only been writing SQL for a couple of years now. I had figured that the best way to work with variables that you will need to use multiple times, was a cursor. So, with the example above I need to keep the Person ID and the Address ID for a later insert.

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 16:03:32

Just put them in a variable without a cursor:

DECLARE @PersonID INT

INSERT INTO Table1 (Name)
SELECT 'Tara'

SELECT @PersonID = @@IDENTITY

You don't need a cursor for what you described.

Tara

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 16:09:53

Yes Tara, except that I have to use @PersonID in 30 different locations. Then the script has to go get the next person (of 46,000) and dump their person id into those same 30 locations.

Where can I do some good reading on the set-based procedure?

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 16:13:15

What you are doing is processing one at a time. SQL Server is not efficient when you do it this way. If you could give us a simple example with your tables, we'll be able to show how to do it set-based. You don't have to do it one person at a time.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-16 : 16:28:01

Besides the (ugh) IDENTITY PK, is there a unique index on the person table?

Brett

8-)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 16:41:22

Sounds good Tara. Here ya go. Obviously, somethings are removed and there may be a syntax error here and there since I ommitted a large part of this.

DECLARE curProviders INSENSITIVE CURSOR FOR

SELECT ProviderID,
BusinessName,
FirstName,
LastName,
Address,
City,
State,
ZipCode,
ZipPlus4,
MailAddress,
MailCity,
MailState,
MailZipCode,
MailZipPlus4,
AreaCode1,
Phone1,
AreaCode2,
Phone2,
AreaCodeFax,
Fax,
Email

FROM CSP2DATA.dbo.tProvider

OPEN curProviders

FETCH NEXT FROM curProviders INTO @ProviderID,
@BusinessName,
@FirstName,
@LastName,
@Address,
@City,
@State,
@ZipCode,
@ZipPlus4,
@MailAddress,
@MailCity,
@MailState,
@MailZipCode,
@MailZipPlus4,
@AreaCode1,
@Phone1,
@AreaCode2,
@Phone2,
@AreaCodeFax,
@Fax,
@Email

WHILE (@@FETCH_STATUS <> -1)
BEGIN

-- Create the Provider Facility record
INSERT INTO tblProviderFacility
VALUES (

@intProviderID, --intProviderID,
@intFacilityTypeID, --intFacilityTypeID,
'MWDG-' + CAST(@ProviderID as VARCHAR (10)), --txtFacilityCode,
RTRIM(ISNULL(@BusinessName, @LastName + ', '+ @FirstName)), --txtFacilityName,
1, --intHomeOffice,
'Created on initial migration', --txtNote,
0, --intPlacementFacility,
1, --intAcceptReferral,
1, --intDayCare,
@TaxID, --txtTaxID,
NULL --intTaxIDTypeID
)

SET @intFacilityID = @@IDENTITY

INSERT INTO tblProviderFacility_Effective
VALUES (

@intFacilityID, --intFacilityID,
GETDATE(), --dtiStart,
NULL, --dtiEnd,
'Created on initial migration', --txtNote,
NULL--intOnHoldReasonID

)

IF @Address IS NOT NULL
BEGIN

--Create the facilities addresses
INSERT INTO tblAddress
VALUES (
0,
@Address,
NULL,
NULL,
NULL,
@City,
@intFacilityStateID,
NULL,
ISNULL(@ZipPlus4, @ZipCode),
@intCountyID
)

SET @intProviderPhysicalAddressID = @@IDENTITY

INSERT INTO tblProviderFacility_Addresses
VALUES (

@intFacilityID, --intFacilityID,
@intProviderPhysicalAddressID, --intAddressID,
@intPrimaryAddressTypeID, --intAddressTypeID,
0, --intSort,
0, --intObsolete,
'Created on initial migration', --txtNote,
1, --intPrimary,
GETDATE(), --dtiStart,
NULL --dtiEnd
)

END

--Check the mailing address to make sure it does not exist already
IF @MailAddress IS NOT NULL
BEGIN

--Create the facilities addresses
INSERT INTO tblAddress
VALUES (
0,
@MailAddress,
NULL,
NULL,
NULL,
@MailCity,
@intFacilityStateID,
NULL,
ISNULL(@MailZipPlus4, @MailZipCode),
@intCountyID
)

SET @intProviderMailAddressID = @@IDENTITY

INSERT INTO tblProviderFacility_Addresses
VALUES (

@intFacilityID, --intFacilityID,
@intProviderMailAddressID, --intAddressID,
@intMailingAddressTypeID, --intAddressTypeID,
0, --intSort,
0, --intObsolete,
'Created on initial migration', --txtNote,
1, --intPrimary,
GETDATE(), --dtiStart,
NULL --dtiEnd
)

END

--Create the Phone records

IF @Phone1 IS NOT NULL AND @Phone1 <> '000-0000'
BEGIN

--create the phone record in tblPhone
INSERT INTO tblPhone
VALUES (

@AreaCode1,
@Phone1
)
SET @intPrimaryPhoneID = @@IDENTITY
END

IF @Phone2 IS NOT NULL AND @Phone2 <> '000-0000'
BEGIN

--create the phone record in tblPhone
INSERT INTO tblPhone
VALUES (

@AreaCode2,
@Phone2
)
SET @intSecondaryPhoneID = @@IDENTITY
END

IF @Fax IS NOT NULL AND @Fax <> '000-0000'
BEGIN

--create the phone record in tblPhone
INSERT INTO tblPhone
VALUES (

@AreaCodeFax,
@Fax
)
SET @intFaxPhoneID = @@IDENTITY
END

IF @intPrimaryPhoneID IS NOT NULL
BEGIN

INSERT INTO tblProviderFacility_Phones
VALUES (

@intFacilityID, --intFacilityID,
@intPrimaryPhoneID, --intPhoneID,
@intPrimaryPhoneTypeID, --intPhoneNumTypeID,
0, --intSort,
0, --intObsolete,
'Created on initial migration', --txtNote,
1, --intPrimary,
GETDATE(), --dtiStart,
NULL --dtiEnd
)
END

IF @intSecondaryPhoneID IS NOT NULL
BEGIN

INSERT INTO tblProviderFacility_Phones
VALUES (

@intFacilityID, --intFacilityID,
@intSecondaryPhoneID, --intPhoneID,
@intSecondaryPhoneTypeID, --intPhoneNumTypeID,
0, --intSort,
0, --intObsolete,
'Created on initial migration', --txtNote,
1, --intPrimary,
GETDATE(), --dtiStart,
NULL --dtiEnd
)

END

IF @intFaxPhoneID IS NOT NULL
BEGIN

INSERT INTO tblProviderFacility_Phones
VALUES (

@intFacilityID, --intFacilityID,
@intFaxPhoneID, --intPhoneID,
@intFaxPhoneTypeID, --intPhoneNumTypeID,
0, --intSort,
0, --intObsolete,
'Created on initial migration', --txtNote,
1, --intPrimary,
GETDATE(), --dtiStart,
NULL --dtiEnd
)

END

--Email

--Add an email address and link it to the provider
INSERT INTO tblEmail
VALUES (
@Email
)
SET @intProviderEmail = @@IDENTITY

INSERT INTO tblProviderFacility_Emails
VALUES (

@intFacilityID, --intFacilityID,
@intProviderEmail, --intEmailID,
0, --intSort,
0, --intObsolete,
'Created on initial migration', --txtNote,
1, --intPrimary,
GETDATE(), --dtiStart,
NULL --dtiEnd
)
END

FETCH NEXT FROM curProviders INTO @ProviderID,
@BusinessName,
@FirstName,
@LastName,
@Address,
@City,
@State,
@ZipCode,
@ZipPlus4,
@MailAddress,
@MailCity,
@MailState,
@MailZipCode,
@MailZipPlus4,
@AreaCode1,
@Phone1,
@AreaCode2,
@Phone2,
@AreaCodeFax,
@Fax,
@Email

CLOSE curProviders
DEALLOCATE curProviders

Now, this chunk of code is about 2200 lines short of the actual script that I have, but hopefully you can get an idea of what I need to do.

I also left out the nested cursors since they are doing basic inserts as well.

In the example above, @intFacilityID is the one ID that is being used over and over in lot's of different places. And of course, once the cursor hits the end, it will grab the next facility and store it's id in @intFacilityID.

Thanks all!

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 16:52:08

Here's some code to get you started on how to remove the cursor:



INSERT INTO tblProviderFacility 
SELECT ColumnList --the ones that you need
FROM CSP2DATA.dbo.tProvider

Then figure out what the identity range was

SELECT @MIN = MIN(IdentityColumnName), @MAX = MAX(IdentityColumnName)
FROM 
WHERE SomeColumn = 'Created on initial migration' -- or whatever makes the newly inserted rows distinguishable between the rest

--Use a tally table to do the next insert based on the MIN and MAX from above

INSERT INTO tblProviderFacility_Effective
SELECT NumberValue
FROM Numbers -- table that has one column with numbers 1 - n where n is the maximum number that you'll need
WHERE NumberValue BETWEEN @MIN AND @MAX


--Create the facilities addresses
INSERT INTO tblAddress
SELECT ColumnList
FROM CSP2DATA.dbo.tProvider
WHERE Address IS NOT NULL

...

Tara

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 17:09:19

I think I am starting to understand.

Let me ask this though. The table that I am importing from has address information:
Street
City
State
Zip

If the State column contains values like "KS" or "CA". Using your method, how can I get them into my tblAddress which uses a foreign key to xtblStates? In other words, tblAddress does not store "KS" or "CA", it stores 23 or 10 since that is the foreign key value of KS and CA. Is that clear enough?

Aj

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 17:10:41

I just noticed that I am a Yak Posting Veteran now! Woo Hoo!

Aj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-16 : 17:48:28

INSERT INTO tblAddress
SELECT p.ColumnList..., s.StateCD
FROM CSP2DATA.dbo.tProvider p
INNER JOIN StateTable s ON p.StateAbbrev = s.StateAbbrev
WHERE Address IS NOT NULL

Tara

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-01-16 : 17:58:23

Gotcha! The reason I did not think of that is because you are wanting me to break one of the rules I was brought up on! Don't join on text values!

I guess that a new way of doing things requires a new way of looking at things!

Thanks!

Aj

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources