Ausgabe

Ich habe eine Haupttabelle mit 5000 Zeilen, eine Managertabelle mit 51 Zeilen und eine Phasentabelle mit 16 Zeilen. Ich habe eine Abfrage mit einem LEFT JOIN von der Haupttabelle zu jeder der beiden anderen erstellt:

SELECT [tblTrue-UpMain].Contract_Number, [tblTrue-UpMain].ProjectName, [tblTrue-UpMain].AFGroupName, [tblTrue-UpMain].AFAccountMgr, [tblTrue-UpMain].AFSubStationName, [tblTrue-UpMain].AFAssociatedContract, [tblTrue-UpMain].AFPreviousFile, [tblTrue-UpMain].AFFinancing, [tblTrue-UpMain].AFReplaceCoverage, [tblTrue-UpMain].AFBillingRate, [tblTrue-UpMain].AFProjectType, [tblTrue-UpMain].AFPreviousClients, [tblTrue-UpMain].ID, [tblTrue-UpMain].AF_VS_File_Number, [tblTrue-UpMain].Customer_Number, [tblTrue-UpMain].Contract_Execution_Date, [tblTrue-UpMain].Service_Account, [tblTrue-UpMain].Contract_Account, [tblTrue-UpMain].Contract_No_IF_Or_AF, [tblTrue-UpMain].CM, [tblTrue-UpMain].FinanceReplacementCPUC, [tblTrue-UpMain].Project_Type, [tblTrue-UpMain].Contract_Status, [tblTrue-UpMain].Contract_Phase, Local_ContractManagers.Contract_Manager, Local_Contract_Phase.PhaseName
FROM ([tblTrue-UpMain] 
LEFT JOIN Local_ContractManagers ON [tblTrue-UpMain].CM = Local_ContractManagers.CMID) 
LEFT JOIN Local_Contract_Phase ON [tblTrue-UpMain].Contract_Phase = Local_Contract_Phase.PhaseID
WHERE ((([tblTrue-UpMain].Contract_Number) Like "AF*"))
ORDER BY [tblTrue-UpMain].Contract_Number;

[tblTrue-UpMain].CM und [tblTrue-UpMain].Contract_Phase sind beides Indizes. [Das Entfernen dieser Indizes hatte keinen Einfluss auf die Zeit.] Local_ContractManagers.CMID und Local_Contract_Phase.PhaseID sind beides Primärschlüssel. [INNER JOINS statt LEFT JOINS liefen in beiden Fällen etwa 1 Sekunde lang.

Wenn ich die Abfrage mit den Manager- und Phase-Tabellen als Access-Tabellen ausführe, dauert es 1 Sekunde oder weniger. Wenn ich es mit den Manager- und Phasentabellen auf SQL Server ausführe, dauert es über 40 Sekunden. Die Haupttabelle befindet sich für beide Abfragen auf SQL Server.]

Das Ausführen der Abfrage auf SSMS (mit allen Tabellen auf SQL Server) läuft ebenfalls schnell.

Irgendwelche Ideen, warum es so lange laufen könnte?

Lösung

Solange die Tabellen alle SQL-Server-basiert sind?

Dann sollte die Abfrage nicht so lange dauern. Wenn Sie jedoch eine lokale Tabelle und eine SQL-Server-Tabelle mischen, wird es langsam wie eine Schildkröte.

Aber eine Möglichkeit, das Problem zu beheben?

Starten Sie den SQL-Manager und erstellen Sie dieselbe Abfrage im SQL-Server.

Wählen Sie tatsächlich neue Ansicht erstellen. Eine SQL-“Ansicht” ist wirklich wie eine in Access gespeicherte Abfrage, und Sie können den vertrauten Abfragegenerator verwenden (der dem von Access ähnelt). Sie werden feststellen, dass das Sortieren im Allgemeinen nicht zulässig ist, also tun Sie dies im Bericht oder erstellen Sie sogar eine clientseitige Abfrage für diese Ansicht.

Speichern Sie nun diese Ansicht und erstellen Sie dann einen Link zu dieser Zugriffsseite.

Ich mache das so oft, dass ich eine kleine Hilfs-VBA-Routine habe, um diesen einen Link hinzuzufügen, und das ist viel weniger als die Verwendung des ODBC-Managers aus dem Menüband.

Versuchen Sie nun, anstelle der lokalen Abfrage, die Sie haben, die SQL-“Ansicht” zu verwenden (sie wird als verknüpfte Tabelle angezeigt).

Das Ergebnis sollte eine SEHR hohe Leistung und so gut sein, als wenn Sie die Abfrage in SQL Studio ausprobieren.

Ansichten sind in der Regel eine weitaus bessere Wahl als beispielsweise die Verwendung einer gespeicherten Prozedur, da sie in Access und sogar in VBA-Code viel einfacher zu verwenden ist.

Tatsächlich empfehle ich, der verknüpften Ansicht den GLEICHEN Namen zu geben, den Sie für die lokale SQL-Abfrage hatten. Auf diese Weise müssen vorhandene Formulare, Codes oder Berichte nicht geändert werden, und Sie erhalten eine fantastische Leistung.

Diese Leistung umfasst sogar das Öffnen eines Berichts mit einer „Where“-Klausel. Sie können also weiterhin clientseitige Filter verwenden oder sogar einen in VBA erstellten Filter, der den Bericht mit der „Where“-Klausel startet. Der Zugriff ruft NUR die Datensätze ab, die die Kriterien erfüllen (und daher ist dieser Ansatz ein netzwerk-/bandbreitenfreundlicher Ansatz).

Wann immer Sie auf eine langsame schildkrötenartige Leistung stoßen? Probieren Sie den View-Ansatz aus, er hat alle Leistungsprobleme behoben, die ich gesehen habe.

Für eine einfache Abfrage basierend auf einer Tabelle? Dann hilft ein Blick nicht. Sobald Sie jedoch mit der Einführung von Verknüpfungen und zusätzlichen Tabellen beginnen, kann der Zugriff keine ordnungsgemäße Verknüpfung durchführen, ohne viele Daten abzurufen. Eine Ansicht ist also wirklich hilfreich.

[tblTrue-UpMain].CM und [tblTrue-UpMain].Contract_Phase sind beides Indizes.

Wir gehen davon aus, dass Sie über serverseitige SQL-Indizes sprechen. Versuchen Sie NICHT, die Clientseite des Index zu erstellen – sie tun nichts und können nicht verwendet werden.

Ich schlage auch STRONG vor, dass Sie NICHT versuchen, serverseitig Indizes zu erstellen, wenn Sie eine Ansicht verwenden. Erstellen Sie die Indizes IMMER auf der Serverseite der Basis-SQL-Servertabellen – nicht mehr und nicht weniger.

Bearbeiten: Hinzufügen eines Links mit vba anstelle von odbc manager.=

Da man also, wie bereits erwähnt, oft eine neue Tabelle oder eine neue Ansicht (als Link vom SQL-Server) hinzufügen muss, habe ich eine VBA-Routine geschrieben, um Zeit zu sparen.

Nichts Besonderes an der Routine. Ich habe eine Schiffsladung solcher Routinen. Ich meine, sobald man eine geteilte Datenbank anwendet, “basteln” wir alle zur Erleichterung der Bereitstellung im Laufe der Zeit einen Code zum erneuten Verknüpfen zusammen. Dies gilt nur für ein Zugriffs-Backend oder ein SQL-Server-Backend.

Also, meine kleine VBA-Hilfsroutine, um einen Link hinzuzufügen?

Angenommen, ich habe gerade eine neue SQL-Serverseite erstellt. Sagen Sie ViewCustomers.

Jetzt brauche ich eine verknüpfte Tabelle (oder Ansicht) auf der Clientseite. Zu viel Zeit und Mühe, den odbc-Manager zu starten und all diese Schritte zu durchlaufen, um nur eine Tabelle zu verknüpfen.

Ich habe also diese Routine und führe sie aus dem Debug-Fenster aus. Also drücke ich Strg-G und tippe im Debug-Fenster Folgendes ein:

MyCreateOneLink "server table", "Local name for link"

Das obige ist also viel schneller.

Also, wie sieht die obige Routine aus?

In meinem Standardcodemodul habe ich Folgendes:

Sub MyCreateOneLink(strServerTable As String, strLocalTable As String)
  
  Dim tdfcurrent          As DAO.TableDef
  
  Set tdfcurrent = CurrentDb.CreateTableDef(strLocalTable)
  
  tdfcurrent.connect = dbCon("MYSERVER\SQLEXPRESS", "Customers", "MyUserName", "MyPassword", cControlPassword, "MyApp")
  
  tdfcurrent.SourceTableName = strServerTable
  CurrentDb.TableDefs.Append tdfcurrent


End Sub

Und oben ruft eine Routine “dbcon()” auf. Diese Routine erstellt aus den übergebenen Werten eine gültige SQL-Verbindungszeichenfolge.

Dies:

Public Function dbCon(ServerName As String, _
                     DataBaseName As String, _
                     Optional UserID As String, _
                     Optional USERpw As String, _
                     Optional APP As String = "Office 2010", _
                     Optional WSID As String = "Axis") As String

   ' returns a SQL server conneciton string
   
  dbCon = "ODBC;DRIVER=SQL Server;" & _
          "SERVER=" & ServerName & ";" & _
          "DATABASE=" & DataBaseName & ";"
          If UserID <> "" Then
             dbCon = dbCon & "UID=" & UserID & ";" & "PWD=" & USERpw & ";"
          End If
          dbCon = dbCon & _
          "APP=" & APP & ";" & _
          "WSID=" & WSID & ";" & _
          "Network=DBMSSOCN"

End Function

oben muss für einen “nativen” Treiber geändert werden, aber die einfache Idee hier ist, einen schnellen Code zu haben, und so kann ich von der Zugriffseingabeaufforderung (Debug-Fenster) aus einen schnellen einfachen Befehl eingeben, um einen Tabellenlink zu erstellen.

Daher wird man während der Entwicklung oft das (direkte) Zugriffsbefehlszeilenfenster verwenden, und wir können so kleine VBA-Hilfsroutinen aufrufen, z. B. eine, um eine Tabellenverknüpfung für uns zu erstellen.

Beantwortet von – Albert D. Kallal

Antwort geprüft von – Marilyn (FixError Volunteer)