Very slow MS SQL Server Inserts.... 1

[OrthoDocSoft]

Folks,

From time to time, instances of my software do this, which is basically firing up 3 recordsets and inserting data into three different tables. All the "str..." data is PASSED IN, so it is available:

Dim adoRSFrozens As ADODB.Recordset
Dim adoRSAccessionDesk As ADODB.Recordset
Dim adoRSPathDesk As ADODB.Recordset

Set adoRSFrozens = New ADODB.Recordset
Set adoRSAccessionDesk = New ADODB.Recordset
Set adoRSPathDesk = New ADODB.Recordset

Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String

Dim datDate As Date
Dim datNowTime As Date

adoRSFrozens.Open "frozens", adoConnection, _
adOpenDynamic, adLockOptimistic

adoRSAccessionDesk.Open "accessiondesk", adoConnection, _
adOpenDynamic, adLockOptimistic

adoRSPathDesk.Open "pathdesk", adoConnection, _
adOpenDynamic, adLockOptimistic

On Error GoTo DBErrorHandler

datNowTime = Now
datDate = Date

intValSpecimenIDNumber = Val(strSpecimenIDNumber)

strSQL = "INSERT INTO frozens (FirstName, MI, LastName, DOB," _
& " SessionNumber, History, SpecimenID, SpecimenIDNumber," _
& " MedicalRecordNumber," _
& " PatientEncounterNumber, UniqueNumber, Specimen," _
& " Description, SpecimenType, ORStatus, RecentStatusChange," _
& " Room, Surgeon, Nurse, TimeSent, DateSent) VALUES ('" _
& strFirstName & "', '" & strMI & "', '" & strLastName _
& "', '" & strDOB & "', '" & strSessionNumber & "', '" _
& strHistory & "', '" & strID & "', " _
& intValSpecimenIDNumber & ", '" & strMedicalRecordNumber & "', '" _
& strPatientEncounterNumber & "', '" & strUniqueNumber & "', '" _
& strSpecimen & "', '" _
& strDescr & "', '" & strSpecimenType & "', 'ORSent', 'Yes', '" _
& strRoom & "', '" & strSurgeon & "', '" & strNurse & "', '" _
& datNowTime & "', '" & datDate & "')"

adoConnection.Execute strSQL

strSQL2 = "INSERT INTO accessiondesk (UniqueNumber, Room, ADStatus," _
& " ExcludedFromTAT) VALUES ('" & strUniqueNumber & "', '" & strRoom _
& "', '', '')"

adoConnection.Execute strSQL2

strSQL3 = "INSERT INTO pathdesk (UniqueNumber, Room, Gross," _
& " FrozenDx, DxType, Pathologist, TimeResulted, PathStatus)" _
& " VALUES ('" & strUniqueNumber & "', '" & strRoom _
& "', '', '', '', '', '', '')"

adoConnection.Execute strSQL3

adoRSFrozens.Close
adoRSAccessionDesk.Close
adoRSPathDesk.Close

Set adoRSFrozens = Nothing
Set adoRSAccessionDesk = Nothing
Set adoRSPathDesk = Nothing

I have placed code (not shown) right after the "execute" bits that change "lights" (ie, colored shapes) on my form from yellow to red (like a progress bar), and found that the code above can take up to 3-5 seconds to execute. It used to be lightening-fast, but now is that slow. But I haven't changed this code in months, and the problem just started.

Any ideas why this is taking so long and what could be the new problem? I have added new tables to the DB, but that shouldn't affect it I wouldn't think.

Thanks,

Ortho

"you cain't fix 'stupid'...

 

[gmmastros]

Normally, inserts are very fast, but there are things that can slow them down.

Do you have any triggers on the tables?

Sp_helptrigger 'TableName'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vb5prgrmr]

When is the last time you trundicated the mdf/log? Lots of indexes? I'm betting that it is more of a network issue vs a sql issue...

BTW, visual ques will also slow things down...



Good Luck

 

[gmmastros]

Oh man. You should not truncate your log files.

http://blogs.lessthandot.com/index....SQLServerAdmin/do-not-truncate-your-ldf-files

Instead, regularly back up your database and log. This will keep the log file to a reasonable size.

Besides, large log files would not account for slow inserts. Lots of indexes might, but I doubt there could be enough indexes to cause 3 inserts to take 3 seconds.

Taking another look at your code...

Why do you open the tables in to a recordset? Specifically, 3 three lines?

[tt]adoRSFrozens.Open "frozens", adoConnection, _
adOpenDynamic, adLockOptimistic

adoRSAccessionDesk.Open "accessiondesk", adoConnection, _
adOpenDynamic, adLockOptimistic

adoRSPathDesk.Open "pathdesk", adoConnection, _
adOpenDynamic, adLockOptimistic[/tt]

This code effectively pulls all the data from those 3 tables in to a recordset. What's worse... it doesn't look like you are using those recordsets for anything.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

Here are your answers so far:

Triggers: There are none.

Truncated logs: Don't know how to, and have not, but the DB is my "development" DB, which I basically keep "empty" of data anyway....

The visual cues are just "shape1(X).fillcolor = vbGreen" (where it was vbYellow), which should be lightning-fast, not to mention that the problem is there just as strong without the cues.

Where I placed the visual cues, tho' tells me that it is the "execute" statements where the slowness is, and it may just be that my machine is struggling, especially since I haven't done anything with this code in months, and it used to work just fine.


And, George, when I copied that code to show you all, I wondered whether I needed to "Open" the recordsets before I just "Executed" the insert. That structure is a hold-over from the very first way I ever used recordsets. I'm sensing you're suggesting that I can just omit those lines you referenced above, yes? I believe it was you who taught me how to use stored procedures, which I now do extensively elsewhere. Should I use one here?

And I don't "backup" this DB at all, so the log files may have been "growing." Could someone tell me how to backup the DB? In my alpha site, I have a DBA who does this for me!

If anyone has additional suggestions, please do, and thanks,

Ortho

"you cain't fix 'stupid'...

 

[gmmastros]

Truncated logs: Don't know how to, and have not, but the DB is my "development" DB, which I basically keep "empty" of data anyway....

Since this is a development db, the data is probably not too critical. Table structures, indexes, stored procedures, etc.... are important. As such, I would recommend that you set the recovery model to simple (if it's not already), and then schedule a daily backup of the database.

Read these 2 articles before changing anything.

http://msdn.microsoft.com/en-us/library/ms189275.aspx

http://msdn.microsoft.com/en-us/library/ms189272.aspx

I'm sensing you're suggesting that I can just omit those lines you referenced above, yes?

Yes.

And I don't "backup" this DB at all, so the log files may have been "growing." Could someone tell me how to backup the DB? In my alpha site, I have a DBA who does this for me!

Shame on you! Like I said earlier, you should be making backups of your database, even if it is just a development database. Are you making backups of your source code? I bet you are because you probably realize that losing the source code would be disastrous. The same can usually be said for a database because it contains the structure of your tables, indexes, procedures, functions, etc....

This seems like a pretty good article about backups.

http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/

Pay particular attention to the part about "Working with simple recovery".

If you are using SQL Server Express (the free version), then you do not have a SQL Agent. The SQL Agent allows you to schedule things. If you have standard, enterprise, or developer edition, you can use SQL Server to schedule daily full backups. If you are using SQL Express, then I recommend you use SQLCMD (a command line tool) to run your backups, and then schedule this to run using windows task scheduler.

Back to the original problem.

Try removing those lines and running the code again. Verify that the code still works the way you expect it to. Is it still slow? Please let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

use stored procedures, which I now do extensively elsewhere. Should I use one here?

Yes.
Just pass the parameters to a stored procdure and let the DB do all the work.

Have fun.

---- Andy

 

[OrthoDocSoft]

George Maestro! That is my new name for you!

I removed the "open" bits (and the "close" bits at the end) and that SOLVED the problem! Thank you for helping me get to that.

I PROMISE to learn how to backup my DB and since I have developer's edition, I will set up automatic back-ups, if for no other reason than to lean how. And I aaaam soooooo ashamed.....

Thanks you amazing people, especially George.

Ortho

"you cain't fix 'stupid'...

 

[tedsmith]

Maybe your database has grown much bigger than it should be or have many unwanted records?
Inserting an indexed entry will take longer the larger the database.

Is the database capable of being compacted?

 

[JoeAtWork]

Maybe your database has grown much bigger than it should be or have many unwanted records?
Inserting an indexed entry will take longer the larger the database.

Is the database capable of being compacted?

I think the OP's last post was pretty clear that the problem was that he was opening 3 different recordsets, fetching ALL fields for ALL records. Once he got rid of that needless code the problem went away - this was never an INSERT issue.

 

[tedsmith]

Strange but the last post by OrthoDocSoft was not visible to me when I posted my suggestion! It is now.
Somehow I must have received a previous version or it was in a cache somewhere in webland.