Performance problem (1 Viewer)

N

neoklis

Registered User.
Local time
Today, 18:37
Joined
Mar 12, 2007
Messages
80
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
 
Last edited:
B

Banana

split with a cherry atop.
Local time
Today, 08:37
Joined
Sep 1, 2005
Messages
6,318
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., and when you have a specific question, do feel free to post back.
 
N

neoklis

Registered User.
Local time
Today, 18:37
Joined
Mar 12, 2007
Messages
80
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..
 
B

Banana

split with a cherry atop.
Local time
Today, 08:37
Joined
Sep 1, 2005
Messages
6,318
Thanks for the clarification.

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

neoklis

Registered User.
Local time
Today, 18:37
Joined
Mar 12, 2007
Messages
80
ok banana..
i'll do it tomorrow when i get back to my office..
thanks again for your help..
 
N

neoklis

Registered User.
Local time
Today, 18:37
Joined
Mar 12, 2007
Messages
80
I post the function in case you notice something...

Code: 
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
 
    [COLOR=red]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;"[/COLOR]
    x = SaveSQL(xText, "UpdateSAPConfirmedQuants", "UpdateSAPConfirmedQuants_SQL", UpdateSAPConfirmedQuants_SQL, GetAddSQL())
    Set M = CurrentDb.OpenRecordset(UpdateSAPConfirmedQuants_SQL, 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
 
SQL_Hell

SQL_Hell

SQL Server DBA
Local time
Today, 16:37
Joined
Dec 4, 2003
Messages
1,360
More readable SQL statement here:

Code: 
"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?
 
B

Banana

split with a cherry atop.
Local time
Today, 08:37
Joined
Sep 1, 2005
Messages
6,318
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:
Code: 
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.
 
N

neoklis

Registered User.
Local time
Today, 18:37
Joined
Mar 12, 2007
Messages
80
SQL_Hell said:
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?
Click to expand...


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

neoklis

Registered User.
Local time
Today, 18:37
Joined
Mar 12, 2007
Messages
80
Banana, i will study your suggestions and i 'll be back..
 
B

Banana

split with a cherry atop.
Local time
Today, 08:37
Joined
Sep 1, 2005
Messages
6,318
One more thing... what do you do with the 100000 rows that's returned?
 
You must log in or register to reply here.

Similar threads

N
Implementing Parameterized Queries in DAO for Enhanced Security in Access- SQL Server Application
Replies
12
Views
725
mdlueck
M
J
Help with using ADOX to Set TableDef connect property in foreign DB w/out actually connecting
Replies
4
Views
233
Pat Hartman
P
I
ODBC Connection Fails when I remove a Field from SQL Server Table
Replies
19
Views
979
Pat Hartman
P
A
Migrating to SQL server - converting DAO to ADO
Replies
19
Views
1,481
Pat Hartman
P
S
Unable to connect to Remote (LAN) SQL Server Instance where both SQL Express and SQL Developer Edition are installed
Replies
7
Views
279
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom