Performance Issue

[Sash]

I'm executing the following SQL statements on the close of a form. the first
statement seems to take far to long to run. I do have indexes on both
tables. Any other suggestions on making this run faster.

Dim strSQLCPT As String
strSQLCPT = "UPDATE dbo_Anesth INNER JOIN dbo_CPT ON dbo_Anesth.fldProcCPT =
dbo_CPT.[CPT #] SET dbo_Anesth.fldAnesCPT = dbo_CPT.[CPT];"
DoCmd.RunSQL strSQLCPT

Dim strSQLResName As String
strSQLResName = "UPDATE dbo_Anesth INNER JOIN dbo_Resident ON
dbo_Anesth.ip_rescrnanum = dbo_Resident.fldResNum SET dbo_Anesth.ip_rescrna =
dbo_Resident.fldResName;"
DoCmd.RunSQL strSQLResName

Dim strSQLPostop As String
strSQLPostop = "UPDATE dbo_Anesth INNER JOIN dbo_PostOpCPT ON
dbo_Anesth.fldPostOpNum = dbo_PostOpCPT.auto SET dbo_Anesth.fldPostOp =
dbo_PostOpCPT.[PO_CPT];"
DoCmd.RunSQL strSQLPostop

Dim strSQLPostop2 As String
strSQLPostop2 = "UPDATE dbo_Anesth INNER JOIN dbo_PostOpCPT ON
dbo_Anesth.fldPostOpNum2 = dbo_PostOpCPT.auto SET dbo_Anesth.fldPostOp2 =
dbo_PostOpCPT.[PO_CPT];"
DoCmd.RunSQL strSQLPostop2

Dim qryUpdate As String
qryUpdate = "UpdateAdmit"
DoCmd.OpenQuery qryUpdate

Dim qryUpdateRes As String
qryUpdateRes = "Update_ResCRNA"
DoCmd.OpenQuery qryUpdateRes

 

[Dirk Goldgar]

I'm executing the following SQL statements on the close of a form. the
first
statement seems to take far to long to run. I do have indexes on both
tables. Any other suggestions on making this run faster.

Dim strSQLCPT As String
strSQLCPT = "UPDATE dbo_Anesth INNER JOIN dbo_CPT ON dbo_Anesth.fldProcCPT
=
dbo_CPT.[CPT #] SET dbo_Anesth.fldAnesCPT = dbo_CPT.[CPT];"
DoCmd.RunSQL strSQLCPT

Dim strSQLResName As String
strSQLResName = "UPDATE dbo_Anesth INNER JOIN dbo_Resident ON
dbo_Anesth.ip_rescrnanum = dbo_Resident.fldResNum SET
dbo_Anesth.ip_rescrna =
dbo_Resident.fldResName;"
DoCmd.RunSQL strSQLResName

Dim strSQLPostop As String
strSQLPostop = "UPDATE dbo_Anesth INNER JOIN dbo_PostOpCPT ON
dbo_Anesth.fldPostOpNum = dbo_PostOpCPT.auto SET dbo_Anesth.fldPostOp =
dbo_PostOpCPT.[PO_CPT];"
DoCmd.RunSQL strSQLPostop

Dim strSQLPostop2 As String
strSQLPostop2 = "UPDATE dbo_Anesth INNER JOIN dbo_PostOpCPT ON
dbo_Anesth.fldPostOpNum2 = dbo_PostOpCPT.auto SET dbo_Anesth.fldPostOp2 =
dbo_PostOpCPT.[PO_CPT];"
DoCmd.RunSQL strSQLPostop2

Dim qryUpdate As String
qryUpdate = "UpdateAdmit"
DoCmd.OpenQuery qryUpdate

Dim qryUpdateRes As String
qryUpdateRes = "Update_ResCRNA"
DoCmd.OpenQuery qryUpdateRes

Am I right in concluding from the "dbo_" prefixes that these are SQL Server
tables? Are these queries running in an MDB file or in an ADP?

You say you have indexes on both tables. Does that mean that, in addition
to whatever their primary keys may be, the fields dbo_Anesth.fldProcCPT and
dbo_CPT.[CPT #] are both indexed? If not, you can expect indexing them to
improve performance greatly.

Assuming that the tables are in SQL Server, does the query have the same
performance problem when run directly in SQL Server, rather than through
Access?

 

[david]

I think that using DoCmd like that is probably forcing the
query into something like a synchronous transaction.

Using a DAO update query would probably let them
run asynchronously, and using a DAO pass-through query
would probably be faster still

(david)

 

[Robert Morley]

In addition to what others have mentioned, try running the query as a SELECT
query and make sure that you're not getting redundant iterations from an
improper join that's returning overlapping results.


Rob