Fetch Next Cursor is to Slow 2

[bmann]

How can I speed up this Fetch next cursor? Below is my code. Is their another way to perform this task more efficently. I am running this task against 3 million records where I convert varchar(10) to a datetime. I would appreciate your help

/*Declare Variables*/
DECLARE @PERIMAGE_DATE varchar(10), @FCN varchar(13), @PERIMAGE_ID varchar(13),
@DOB_MO smallint, @DOB_DAY smallint, @DOB_YEAR smallint, @BIRTH char(8),
@MONTH char(2), @DAY char(2), @YEAR char(4), @BIRTH_DATE datetime

/*Create a forward only cursor */
DECLARE update_dob CURSOR FOR
SELECT FCN, PERIMAGE_DATE, PERIMAGE_ID
FROM Los_Angeles_CALPHOTO.dbo.FCNPERIMAGETemp WHERE PERIMAGE_DATE is not null

/*Begin moving dob records.*/
OPEN update_dob
FETCH update_dob INTO @FCN, @PERIMAGE_DATE, @PERIMAGE_ID
WHILE (@@fetch_status = 0)
BEGIN
--print 'here'
IF (@PERIMAGE_DATE is Not Null)
Begin

/* Create a Birthdate*/
IF (@PERIMAGE_DATE is not Null)
SET @MONTH = substring(@PERIMAGE_DATE, 5, 2)

/*Thirty days hath September*/
IF (@PERIMAGE_DATE is not Null)
SET @DAY = substring(@PERIMAGE_DATE, 7, 2)

IF (@PERIMAGE_DATE is not Null)
SET @YEAR = substring(@PERIMAGE_DATE, 1, 4)


/*Update the record*/
UPDATE CALPHOTO.dbo.Perimage
SET PERIMAGE_DATE = Cast((@YEAR + @MONTH + @DAY) as datetime)
WHERE CALPHOTO.dbo.Perimage.CP_FCN like 'LASD%' and CALPHOTO.dbo.Perimage.FCN = @FCN and CALPHOTO.dbo.Perimage.PERIMAGE_ID = @PERIMAGE_ID and CALPHOTO.dbo.Perimage.PERIMAGE_DATE is null
End

Delete LOS_ANGELES_CALPHOTO.dbo.FCNPerimagetemp
Where FCN = @FCN and PERIMAGE_ID = @PERIMAGE_ID

FETCH next from update_dob INTO @FCN, @PERIMAGE_DATE, @PERIMAGE_ID

END
CLOSE update_dob
DEALLOCATE update_dob
GO

 

[redgate]

Dont know how much this will help but maybe start by declaring the cusror 'READ_ONLY' and then deleting the records in bulk at the end of the cursor.

 

[foxdev]

Unless I'm missing something, you don't need a cursor for this. You should be able to do the whole shebang with a single Update statement. --------------

http://www.lowcostprints.com

 

[MeanGreen]

Is Los_Angeles_CALPHOTO.dbo.FCNPERIMAGETemp the same table as CALPHOTO.dbo.Perimage? I think you could do this update in one pass, as opposed to a cursor. Would take me about an hour to create the update query, if I understood the relationship between your temp table and the update table. Would be glad to help.

Thank you.

 

[cdukes]

You should be able to do this with a single update statement. This is just a quick mockup, but it should be along the following lines:

UPDATE CALPHOTO.dbo.Perimage
SET Cast((substring(LA.PERIMAGE_DATE, 5, 2)
+ substring(LA.PERIMAGE_DATE, 1, 4)
+ substring(LA.PERIMAGE_DATE, 7, 2)
) as datetime)

FROM Los_Angeles_CALPHOTO.dbo.FCNPERIMAGETemp LA

INNER JOIN CALPHOTO.dbo.Perimage CAL

On CAL.CP_FCN like 'LASD%' and
CAL.FCN = LA.FCN and
CAL.PERIMAGE_ID = LA.PERIMAGE_ID and
CAL.PERIMAGE_DATE is null

WHERE LA.PERIMAGE_DATE is not null

Hope this helps,

Chris Dukes

 

[bmann]

Hello MeanGreen. Los_Angeles_CALPHOTO.dbo.FCNPERIMAGETemp and CALPHOTO.dbo.Perimage are different tables. I update CALPHOTO.dbo.Perimage from Los_Angeles_Calphoto.dbo.FCNPERIMAGETemp. The data looks like this.

Los_Angeles_Calphoto.dbo.FCNPERIMAGETemp

FCN perimage_date
65080 20021010

I convert this varchar(10) to datetime and then update record in CALPHOTO.dbo.Perimage based on FCN number. If you need more info let me know.

Thanks Meangreen



Thank you.

 

[MeanGreen]

The code Cdukes provided would probably work with one change.
UPDATE CALPHOTO.dbo.Perimage
SET PERIMAGE_DATE = Cast((substring(LA.PERIMAGE_DATE, 5, 2)
+ substring(LA.PERIMAGE_DATE, 1, 4)
+ substring(LA.PERIMAGE_DATE, 7, 2)
) as datetime)

FROM Los_Angeles_CALPHOTO.dbo.FCNPERIMAGETemp LA

INNER JOIN CALPHOTO.dbo.Perimage CAL

On CAL.CP_FCN like 'LASD%' and
CAL.FCN = LA.FCN and
CAL.PERIMAGE_ID = LA.PERIMAGE_ID and
CAL.PERIMAGE_DATE is null

WHERE LA.PERIMAGE_DATE is not null

Try using a select and see if it is returning you the proper values before you apply the update. Example:

Select la.perimage_date, cal.perimage_date,
Cast((substring(LA.PERIMAGE_DATE, 5, 2)
+ substring(LA.PERIMAGE_DATE, 1, 4)
+ substring(LA.PERIMAGE_DATE, 7, 2)
) as datetime) newperimage_date

FROM Los_Angeles_CALPHOTO.dbo.FCNPERIMAGETemp LA

INNER JOIN CALPHOTO.dbo.Perimage CAL

On CAL.CP_FCN like 'LASD%' and
CAL.FCN = LA.FCN and
CAL.PERIMAGE_ID = LA.PERIMAGE_ID and
CAL.PERIMAGE_DATE is null

WHERE LA.PERIMAGE_DATE is not null


Hope this helps.

 

[cdukes]

OOPS,

Yes, I forgot the set PERIMAGE_DATE =. Its always a problem trying to do several things at the same time!!

Chris

 

[MeanGreen]

Sorry Chris,
I wasn't critiquing your work. It was an excellent job at rewriting the cursor. I hate cursors and have only found a few occasions where they were necessary. You might call it a pet peeve of mine. (probably misspelled it). Cursors are just so slow!

Good luck!

 

[cdukes]

MeanGreen,

Cursors do have their place (occasionally!!), but when using a language like SQL that is designed for these kind of bulk transactions then you might as well take advantage of it.

Otherwise you might as well write it in access using 23 global opened recordsets!!

Exactly the application that I am re-writting at the very moment, hence 'the trying to do several things at the same time'

Chris Dukes

 

[bmann]

Thanks Meangreen and cdukes. That code works great. I hope both of you have a great day.

 

[MeanGreen]

I would be interested to know how long the query method takes to update versus your CURSOR routine. If you have the time that is.

Thank you.

 

[bmann]

Hello Meangreen. The cursor routine was processing 20,000 records per hour. I have approximately 3 million records. This is why I needed help. It would take probably two weeks to complete. Your routine updated the records in about 5 to 10 minutes. Thanks very much for your help.

I have one other problem. I am getting an error because of some bad dates. The bad date is 1950-00-00. How do I skip over those records with bad dates. I know I need some type of substring but confused on syntax. This code works very well. I would appreciate your help. Here is the code.

UPDATE CALPHOTO.dbo.DOB
Set
DOB_MO = Cast((substring(LA.Birth_DATE, 6, 2)) as smallint),
DOB_DAY = Cast((substring(LA.Birth_DATE, 9, 2)) as smallint),
DOB_YEAR = Cast((substring(LA.Birth_DATE, 1, 4)) as smallint),
BIRTH_DATE = Cast(LA.Birth_DATE as datetime)

FROM Los_Angeles_CALPHOTO.dbo.FCNDOBTemp LA

INNER JOIN CALPHOTO.dbo.DOB CAL

On CAL.CP_FCN like 'LASD%' and
CAL.FCN = LA.FCN and
CAL.Birth_DATE is null

WHERE LA.Birth_DATE is not null and Len(LA.BIRTH_DATE) = '10' and LA.BIRTH_DATE > '1900'


GO

 

[MeanGreen]

You can use the ISDATE function I think. Try this:

UPDATE CALPHOTO.dbo.DOB
Set
DOB_MO = Cast((substring(LA.Birth_DATE, 6, 2)) as smallint),
DOB_DAY = Cast((substring(LA.Birth_DATE, 9, 2)) as smallint),
DOB_YEAR = Cast((substring(LA.Birth_DATE, 1, 4)) as smallint),
BIRTH_DATE = Cast(LA.Birth_DATE as datetime)

FROM Los_Angeles_CALPHOTO.dbo.FCNDOBTemp LA

INNER JOIN CALPHOTO.dbo.DOB CAL

On CAL.CP_FCN like 'LASD%' and
CAL.FCN = LA.FCN and
CAL.Birth_DATE is null

WHERE LA.Birth_DATE is not null and Len(LA.BIRTH_DATE) = '10' and LA.BIRTH_DATE > '1900' and (isdate(LA.BIRTH_DATE) = 1)

Hope this helps.


GO

 

[cdukes]

bMann,

Glad you have a more acceptable update query,

as for the additional problem, Just check for the bad dates:

WHERE LA.Birth_DATE is not null and Len(LA.BIRTH_DATE) = '10' and LA.BIRTH_DATE > '1900'
and LS.BIRTH_DATE <> '00-00-1950'

alternatively, you could check and only convert valid dates:

WHERE LA.Birth_DATE is not null and Len(LA.BIRTH_DATE) = '10' and LA.BIRTH_DATE > '1900'
and isdate(LA.BIRTH_DATE) = 1

Chris Dukes

 

[bmann]

Thanks Meangreen and cdukes. You guys are sharp. Are you guys consultants for your own company? I have never seen a forum respond so quickly to a question. I hope you guys get rewarded somehow. Let me throw another question at you guys. How do I speed up this Linked server query against a Oracle server? Here is the code.

/* DOB */
/* Insert new records */
PRINT 'Inserting new DOBs.'
INSERT CALPHOTO.dbo.DOB
(
FCN,
ORI_OWNER,
OCA,
TIMESTAMP,
CP_FCN,
CP_TIME
)
SELECT
left(FCN,13),
left(ORI_OWNER,9),
left(OCA,20),
CREATED_TIMESTAMP,
'LASD' + left(FCN,13),
getdate()
FROM openquery(LOS_ANGELES, 'SELECT /*+ RULE */ fcn, ORI_OWNER, OCA, CREATED_TIMESTAMP from IWS.CAL_DOB')

 

[MeanGreen]

Sorry,
I don't know anything about Oracle. You might try posting this in the Oracle forum and see if they could help you. Glad to hear the other query worked.

Good Luck.

 

[cdukes]

Bmann,

Just for your information, I am a contract PC developer and have worked with SQLServer, VB etc for the last 10 - 12 years in many large industries and companies.

Unfortunatly, your next question is slightly more tricky as it not only involves a linked server, but also Oracle. There are several things that might speed it up.

1) restrict the query on oracle to as small a recordset as possible. Whatever happens, all the records selected, must come over the network.

2) does CALPHOTO.dbo.DOB have any indexes that are required to be rebuilt.

3) Is the insert in a transaction.

4) It might be quicker to transfer all the data to a temp table in SQLServer (with no indexes) and then process the temp table into the main table. This will be especially true if it is trying to maintain a transaction across a linked server and rebuild indexes.


Hope this helps,

Chris Dukes

 

[bmann]

Thanks cdukes. I do not have to rebuild indexes. I am also transferring data into a temp table to perform other stored procedures. But even tranferring all the data into temp tables takes a while. I appreciate your help. I am just trying to speed up the transactions on the linked server. I hope you have a good day.

Thanks

Brian