Ich möchte prüfen, ob es ein bevorzugtes Entwurfsmuster für die Implementierung der Suchfunktionalität mit mehreren optionalen Parametern gegen die Datenbanktabelle gibt, wo der Zugriff auf die Datenbank nur über gespeicherte Prozeduren erfolgen sollte.
Die Zielplattform ist .Net mit SQL 2005, 2008 Backend, aber ich denke, das ist ein ziemlich allgemeines Problem.
Wir haben beispielsweise eine Kundentabelle und möchten der Benutzeroberfläche verschiedene Parameter wie Kundentyp, Kundenstatus, Kundenzip usw. zur Verfügung stellen. Alle sind optional und können in beliebigen Kombinationen ausgewählt werden . Mit anderen Worten, der Benutzer kann nur nach customerType oder nach customerType, customerZIp oder anderen möglichen Kombinationen suchen. Es gibt mehrere verfügbare Entwurfsansätze, aber alle haben einige Nachteile und ich möchte fragen, ob es einen bevorzugten Entwurf unter ihnen gibt oder ob es einen anderen Ansatz gibt.
Generieren Sie die sql-Anweisung wo where clause dynamisch in der Geschäftsschicht basierend auf der Suchanforderung von der Benutzeroberfläche und übergeben Sie sie als Parameter an eine gespeicherte Prozedur. Etwas wie @Where = 'wo CustomerZip = 111111' Erstellen Sie in der gespeicherten Prozedur eine dynamische SQL-Anweisung und führen Sie sie mit sp_executesql aus. Nachteil: dynamische SQL, SQL-Injektion
Implementieren Sie eine gespeicherte Prozedur mit mehreren Eingabeparametern, die die Suchfelder auf der Benutzeroberfläche darstellen, und verwenden Sie die folgende Konstruktion, um die Datensätze nur für die angeforderten Felder in der where-Anweisung auszuwählen.
WO %Vor%
Nachteil: Mögliches Leistungsproblem für die SQL.
3. Implementieren Sie eine separate gespeicherte Prozedur für jede Suchparameterkombination. Nachteil: Die Anzahl der gespeicherten Prozeduren erhöht sich schnell mit der Erhöhung der Suchparameter, wiederholter Code.
Dies ist der beste Artikel, der die subtilen Auswirkungen auf die Leistung in SQL beschreibt: Dynamische Suchbedingungen in T-SQL von Erland Sommarskog . Es deckt jede Methode ab und gibt PROs und Nachteile jeder Methode sehr detailliert.
Methode 1: dynamisches SQL kann Parameter übernehmen, es ist ziemlich trivial und eliminiert das Risiko einer SQL-Injektion. Das beste Argument gegen dynamisches SQL ist, dass nicht-triviale Anweisungen eine komplexe Logik generieren können, obwohl dies auch kein Problem ist, wenn Sie ein ordentliches ORM verwenden.
NHiberante und LinqToSql erstellen dynamisches SQL hinter den Kulissen und sind nicht mit Sicherheitslücken durchsetzt. Meiner Meinung nach denkt man am besten über eine dieser beiden Technologien nach, bevor man seine eigene DAL aufrollt.
Methode 2: Ich habe persönlich die zweite Methode in der Vergangenheit ohne Probleme angewendet. Sie haben das "mögliche Leistungsproblem für die SQL" kommentiert, aber haben Sie ein Profil erstellt? Vergleichte Ausführungspläne? Nach meiner eigenen Erfahrung wurde ihre Leistung mit dem @param is null OR col = @param -Ansatz kaum oder gar nicht erreicht. Denken Sie daran, wenn Sie 10 Stunden Entwicklerzeit benötigen, um den Code zu optimieren, um 10 Mikrosekunden pro Jahr Ausführungszeit zu sparen, beträgt Ihre Nettoeinsparung immer noch fast -10 Stunden.
Methode 3: Kombinatorische Explosion. Um jeden Preis vermeiden.
Ich habe dies als Kommentar gepostet, aber mir wurde klar, dass es wahrscheinlich eine Antwort sein sollte.
Es ist nicht gut, Prädikate als WHERE @Param IS NULL OR Column = @Param zu schreiben, da das Optimierungsprogramm es normalerweise als nicht sargierbar ansieht.
Probieren Sie dieses Experiment aus: Nehmen Sie Ihre am häufigsten gefüllte Tabelle und versuchen Sie, nur für das Primärschlüsselfeld abzufragen, das Ihr gruppierter Index sein sollte:
%Vor% Diese beiden SELECT -Anweisungen werden identische Ergebnisse liefern, unter der Annahme, dass die PK-Spalte ein nicht negatives int ist. Aber ziehen Sie den Ausführungsplan dafür hoch und Sie werden einen großen Unterschied in den Kosten sehen. Das erste SELECT führt einen vollständigen Index-Scan durch und benötigt normalerweise etwa 90% der Abfragekosten.
Wenn Sie optionale Suchbedingungen in SQL haben möchten und Sie kein dynamisches SQL verwenden können, ist es am besten für die Performance, wenn Sie es stattdessen in eine Bereichsabfrage umwandeln können, indem Sie ISNULL verwenden. Auch wenn der Bereich riesig ist (im wahrsten Sinne des Wortes der halbe Bereich von int ), wird das Optimierungsprogramm es immer noch herausfinden, wenn der optionale Parameter verwendet wird.
Tags und Links sql .net design-patterns ado.net