November 19, 2002 at 7:56 am
Hello All,
I have to transfer data between two tables, this sounds quite simple, but I happen to be hit by performance issues. Here is my situation, I have to check for the record in the destination table and do an insert or update the record accordingly. Right now I am using ADO recordset objects, for this task. I cann't use update statement as I have to build the set clause, in some cases there may be more than 52 columns.
Can any one suggest me a better way of doing this? Right now it takes me more than 4 hrs to update say 20000 records, with 52 columns. This is way too long, I am running this on pIII with 256mb ram.
Is there any way I can achieve the same task from SQL queries?
Any kind of help is greatly appreciated.
Thank you,
murali.k.maddali
November 19, 2002 at 8:06 am
I don't suppose you can do a delete and insert, instead of an update? Are you expanding the records sizewise, with all the set statements? What is your setting for FILL FACTOR on your clustered index? If you are expanding the records greatly, then it might be slow due to page splits. If you adjust your FILL FACTOR to take this into account, then possibly you might see a performance gain. Do you have a lot of indexes on the table? If so, possible dropping the indexes, updating the data, and then adding them back might help.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 19, 2002 at 8:43 am
Hello Gregory,
I have no idea about Fill Factor. But I cann't do an delete and insert as there may be some records that I would not be updating from the source to destination. I am not using any indexing how ever I have a rowguid field on the destination table, which is indexed. Here is the sample code I was trying, if this could help, it is pretty much straight forward.
___________________________
Do Until srcRs.EOF
With dstRs
If ((UBound(sIDFieldNameArr) - LBound(sIDFieldNameArr)) > 0) Then
.Filter = sFindCriteria
Else
.Find sFindCriteria, 0, adSearchForward, adBookmarkFirst
End If
End With
If dstRs.EOF Then
If dstRs.Supports(adAddNew) Then
dstRs.AddNew
For iCounter = 0 To (iColCount - 1)
With srcRs.Fields(iCounter)
dstRs(.Name).Value = Trim(.Value)
iLoop2 = iLoop2 + 1
End With
Next iCounter
Else
' update the record here
For iCounter = 0 To (iColCount - 1)
With srcRs.Fields(iCounter)
sFieldName = Trim(.Name)
sSrcFldValue = Trim(.Value) & ""
End With
sDstFldValue = Trim(dstRs.Fields(sFieldName).Value) & ""
If dstRs.Supports(adUpdate) Then
If LenB(sDstFldValue) <= 0 And LenB(sSrcFldValue) > 0 Then
dstRs(sFieldName).Value = sSrcFldValue
iLoop = iLoop + 1
ElseIf sDstFldValue <> sSrcFldValue Then
dstRs(sFieldName).Value = sSrcFldValue
iLoop = iLoop + 1
End If
End If
Next iCounter
End If
With dstRs
If .EditMode <> adEditNone Then
iLoop3 = iLoop3 + 1
If iLoop3 Mod 10 = 0 Then
.UpdateBatch
End If
End If
End With
srcRs.MoveNext
If iLoopCtr Mod 100 = 0 Then
DoEvents
End If
Loop
dstRs.UpdateBatch
___________________________
Thank you,
November 19, 2002 at 9:00 am
I don't mean delete every record, but only the ones you are planning on updating. If the records are a true replacement, then you should be able to delete and then insert.
If you don't know what the fill factor setting was then most likely you took the default when creating the index. Which means you probably took a fill factor of 0. This means if you are expanding the size of the records with the update, then you are causing page splits to occur. I would suggest you consider recreating your indexes with a fill factor setting of say 25, and see what happens. Especially if you are expanding the records with each update.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 19, 2002 at 9:10 am
Hello Greg,
Forgot to mention this, as I told you before the data transfer can be between any two tables, so there are quite good number of tables in my database, that do not have indexing, so what would I be doing in these cases, the sample I am trying donot have indexing on the table.
Thank you,
with regards,
murali.k.maddali
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply