Hi all,

I have imported my back-end Ms Access database to SQL Server. I noticed that when executing some actions through DAO-ODBC Connection, in some cases, is much slower than when was executing through DAO-JET Connection. Is this really can be happening or am I missing something..? At this time I don’t want to convert to ADO. All I want to do is to leave my front-enddatabase as is (solve if possible, the performace problem) and on next step make the ADO conversion.

Thank you

Hi,

This can be due to several causes, but I bet the cause for this has to do with how your SQL statements are formed and not necessarily related to the manners of how they are connecting to the SQL Server. (I'm guessing by DAO-ODBC connection/DAO-JET connection, you're referring to a passthrough query compared to a linked table?)

See if this provides some hints & insights. (http://www.access-programmers.co.uk/forums/showthread.php?t=172243), and when you have a specific question, do feel free to post back.

thank's for your reply,

when i say

a) DAO-JET connection i am referring to a backend ms access database
b) DAO-ODBC connection to a backend ms sql 2005 database

now, in my front end database there is a command button that executes a ms-access function. In this function i make a select statement to 3 tables and due to some complex conditions i make some changes line to line in the select statement. When i execute the function with the a way it takes about 40 seconds to complete the changes. When i execute it with the b way it takes about 30 minutes..!!.. and i can't find what can be wrong..

Thanks for the clarification.

Paste the SQL, please. I bet it has to do with how your SQL is written.

ok banana..
i'll do it tomorrow when i get back to my office..
thanks again for your help..

I post the function in case you notice something...

Public Function UpdateSAPConfirmedQuants(xTable)
Static M As Recordset, UpdateSAPConfirmedQuants_SQL
Static Ret As Variant, z As Long, TotalRecs, xText
Static curKG, curST, curWCtxt, preKG, preST, preWCtxt
Static x, i, itxt, isPrev, LastPostingDate, pi, xPrevWCstr As vPrevWCstring

xText = "Update SAP confirmations to " & xTable

UpdateSAPConfirmedQuants_SQL = "SELECT " & xTable & ".ID, " & xTable & ".Component_Flag, " & xTable & ".MRP_Controller, " & xTable & ".Operation_No, " & xTable & ".Work_Center, " & xTable & ".Work_CenterConfirm, " & xTable & ".Work_CenterPrevConfirm, " & xTable & ".SAPWCConfirmText, " & xTable & ".SAPConfirmQuant_kg, " & xTable & ".SAPConfirmQuant_Pieces, " & xTable & ".LastSAP_PostingDate AS LastSAP_PostDate, " & xTable & ".WCConfirmText, " & xTable & ".ConfirmQuant_kg, " & xTable & ".ConfirmQuant_Pieces, " & xTable & ".SAPPrevWCConfirmText, " & xTable & ".SAPPrevConfirmQuant_kg, " & xTable & ".SAPPrevConfirmQuant_Pieces, " & xTable & ".PrevWCConfirmText, " & xTable & ".PrevConfirmQuant_kg, " & xTable & ".PrevConfirmQuant_Pieces, " & xTable & ".PrevCompleted, ProductionMatrixSAP.* FROM " & xTable & " INNER JOIN ProductionMatrixSAP ON " & xTable & ".SD_key = ProductionMatrixSAP.SD_key ORDER BY " & xTable & ".ID;"
x = SaveSQL(xText, "UpdateSAPConfirmedQuants", "UpdateSAPConfirmedQuants_SQL", UpdateSAPConfirmedQuants_SQL, GetAddSQL())
Set M = CurrentDb.OpenRecordset(UpdateSAPConfirmedQuants_S QL, dbOpenDynaset)

Debug.Print UpdateSAPConfirmedQuants_SQL

If M.RecordCount > 0 Then
M.MoveLast
TotalRecs = M.RecordCount
Ret = SysCmd(SYSCMD_INITMETER, xText, TotalRecs)
z = 1

M.MoveFirst
Do Until M.EOF
Ret = SysCmd(SYSCMD_UPDATEMETER, z)
curKG = 0
curST = 0
curWCtxt = Null
preKG = 0
preST = 0
preWCtxt = Null
LastPostingDate = CDate("01/01/1999")

xPrevWCstr = GetPrevWorkCenter_Var(M("Work_Center"), M("MRP_Controller"))
For i = M("maxOP") To 1 Step -1
itxt = Format(i, "00")
'check to find proper prv_string
If xPrevWCstr.IsComplex And (IsNull(xPrevWCstr.SinglePrevWCstr) Or IsEmpty(xPrevWCstr.SinglePrevWCstr)) Then
pi = 1
Do While (IsNull(xPrevWCstr.SinglePrevWCstr) Or IsEmpty(xPrevWCstr.SinglePrevWCstr)) And _
pi <= xPrevWCstr.ComplexPrevWCstrNo
If InStr(1, xPrevWCstr.ComplexPrevWCstr(pi), M("PG" + itxt)) > 0 And _
M("OP" + itxt) < M("Operation_No") Then
xPrevWCstr.SinglePrevWCstr = xPrevWCstr.ComplexPrevWCstr(pi)
End If
pi = pi + 1
Loop
End If
Next i

For i = 1 To M("maxOP")
itxt = Format(i, "00")
If M("OP" + itxt) < M("Operation_No") Then
isPrev = IIf(InStr(1, xPrevWCstr.SinglePrevWCstr, M("PG" + itxt)) > 0, True, False)
If isPrev Then
preKG = preKG + M("KG" + itxt)
preST = preST + M("ST" + itxt)
preWCtxt = AddToList(preWCtxt, Mid(M("WC" + itxt), IIf(Left(M("WC" + itxt), 2) = "S_", 3, 1), 8))
End If
End If
If M("OP" + itxt) = M("Operation_No") Then
If M("WG" + itxt) = M("Work_CenterConfirm") Then
curKG = curKG + M("KG" + itxt)
curST = curST + M("ST" + itxt)
LastPostingDate = IIf(LastPostingDate < M("LD" + itxt), M("LD" + itxt), LastPostingDate)
curWCtxt = AddToList(curWCtxt, Mid(M("WC" + itxt), IIf(Left(M("WC" + itxt), 2) = "S_", 3, 1), 8))
End If
End If
Next i

M.Edit
M("LastSAP_PostDate") = LastPostingDate
'Current Machine Confirmed Quant in SAP
'======================================
M("SAPWCConfirmText") = curWCtxt 'string with all WC produced the quantity in SAP
M("SAPConfirmQuant_kg") = curKG
M("SAPConfirmQuant_Pieces") = curST
'Current Machine Confirmed Quant in SAP + Deltia (Dif)
'================================================= ====
M("WCConfirmText") = curWCtxt 'string with all WC produced the quantity in SAP + Deltia
M("ConfirmQuant_kg") = curKG
M("ConfirmQuant_Pieces") = curST
'Previous Machine Confirmed Quant in SAP
'=======================================
M("SAPPrevWCConfirmText") = preWCtxt 'string with all previous WC produced the quantity in SAP
M("SAPPrevConfirmQuant_kg") = preKG
M("SAPPrevConfirmQuant_Pieces") = preST
'Previous Machine Confirmed Quant in SAP + Deltia (Dif)
'================================================= =====
M("PrevWCConfirmText") = preWCtxt 'string with all previous WC produced the quantity in SAP + Deltia
M("PrevConfirmQuant_kg") = preKG
M("PrevConfirmQuant_Pieces") = preST
M.Update

M.MoveNext
z = z + 1
Loop
Ret = SysCmd(SYSCMD_REMOVEMETER)
End If
M.Close
End Function

More readable SQL statement here:


"SELECT
" & xTable & ".ID,
" & xTable & ".Component_Flag,
" & xTable & ".MRP_Controller,
" & xTable & ".Operation_No,
" & xTable & ".Work_Center,
" & xTable & ".Work_CenterConfirm,
" & xTable & ".Work_CenterPrevConfirm,
" & xTable & ".SAPWCConfirmText,
" & xTable & ".SAPConfirmQuant_kg,
" & xTable & ".SAPConfirmQuant_Pieces,
" & xTable & ".LastSAP_PostingDate AS LastSAP_PostDate,
" & xTable & ".WCConfirmText,
" & xTable & ".ConfirmQuant_kg,
" & xTable & ".ConfirmQuant_Pieces,
" & xTable & ".SAPPrevWCConfirmText,
" & xTable & ".SAPPrevConfirmQuant_kg,
" & xTable & ".SAPPrevConfirmQuant_Pieces,
" & xTable & ".PrevWCConfirmText,
" & xTable & ".PrevConfirmQuant_kg,
" & xTable & ".PrevConfirmQuant_Pieces,
" & xTable & ".PrevCompleted,
ProductionMatrixSAP.*
FROM " & xTable & "
INNER JOIN ProductionMatrixSAP ON " & xTable & ".SD_key = ProductionMatrixSAP.SD_key
ORDER BY " & xTable & ".ID;"


Have you tried running the sql statement, with hardcoded table names in SQL server management studio? to see how fast it runs there?

How many rows are returned by this select?

1) Are all three tables linked table or a mixture of local and linked table? If you're joining a linked table with a local table, Jet would have to do heavy reading (maybe even a full table scan), whereas if it's all linked table, Jet can pass the operations off to SQL Server and let it do the join locally and only send back the result.

2) You're joining on SD_Key. Did you put an index on that column for all tables?

3) You're requesting a order by ID. Is ID a primary key? If so, is it clustered? If so, then I would expect ORDER BY to be unneeded. Even more to point why even need a ORDER BY to order by over an arbitrary key?

The link I gave you in my first post should explains several of whys and hows to deal with linked table efficiently.

On more general note, I have to wonder why you feel necessary to have a dynamic SQL to swap in a table. Usually there wouldn't be need to dynamically swap in a table. Can you explain that?

Furthermore, you are more likely to get better performance if you use a stored procedure so you can work out the logic completely inside the server's box without needing so much network traffic, which is likely what is killing you.

Finally a minor note:
Static M As Recordset, UpdateSAPConfirmedQuants_SQL

Unless you really want to persist variables between every calls, you really don't need the Static keyword. Use Dim. Furthermore, the UpDateSAPConfirmedQuants_SQL (and several other variables) are not typed so they're being declared as Variant, which can slow you down a bit. Best to declare single variable per line.

HTH.

Have you tried running the sql statement, with hardcoded table names in SQL server management studio? to see how fast it runs there?

How many rows are returned by this select?


Yes I have and it returns about 100000 rows really fast..

Banana, i will study your suggestions and i 'll be back..

One more thing... what do you do with the 100000 rows that's returned?