[gelöst] Zeitintensiver SQL Befehl beim Öffnen einiger Forms
8. März 2007 08:14
Guten Morgen zusammen.
Der Titel hätte auch "Monster SELECT" heißen können
Beim Öffnen diverser Forms (hier Kontakt) wird folgender SQL Befehl abgesetzt (technisch NAV SP3, SQL Server 2005 SP1 + Rollups):
Wobei -CP-TMPL-CONS-P unser erster Kontakt (ein internes Template) ist und der Server erstmal über 20 Sekunden rödelt (bei 1.000.000 Kontakten) bis sich die Form überhaupt initial öffnet. Ein Deaktivieren und Aktivieren der Form führt zu dem gleichen Phänomen. Nochmaliges Öffnen benutzt dann zwischengespeicherte Daten und geht dementsprechend schneller. Nach längerer (Arbeits-)Pause tritt es in der gleichen NAV Instanz wieder auf.
Mit ist klar, dass das direkt mit dem Merkmal SourceTablePlacement=Saved der Form zu tun hat und dass eine Änderung auf "First" das Problem umgeht. Verwirrend dabei ist, dass dieses Verhalten auf unserem Entwicklungsserver (SQL 2005 SP2) nicht zu beobachten ist, trotz gleicher Form-Merkmale.
Meine Fragen dazu also:
Läßt sich dieses extrem lästige, weil telefonanruffördernde, Verhalten auch anderweitig steuern?
Hängt es mit dem Patch-Stand des SQL Servers zusammen?
Was machen andere ggf. mit Forms/Tabellen und solchen Datenvolumen um solche Probleme zu umgehen?
Der Titel hätte auch "Monster SELECT" heißen können
Beim Öffnen diverser Forms (hier Kontakt) wird folgender SQL Befehl abgesetzt (technisch NAV SP3, SQL Server 2005 SP1 + Rollups):
- Code:
SELECT *,DATALENGTH("Picture") FROM "Company$Contact" WHERE "No_">='-CP-TMPL-CONS-P' ORDER BY "No_" OPTION (FAST 10)
Wobei -CP-TMPL-CONS-P unser erster Kontakt (ein internes Template) ist und der Server erstmal über 20 Sekunden rödelt (bei 1.000.000 Kontakten) bis sich die Form überhaupt initial öffnet. Ein Deaktivieren und Aktivieren der Form führt zu dem gleichen Phänomen. Nochmaliges Öffnen benutzt dann zwischengespeicherte Daten und geht dementsprechend schneller. Nach längerer (Arbeits-)Pause tritt es in der gleichen NAV Instanz wieder auf.
Mit ist klar, dass das direkt mit dem Merkmal SourceTablePlacement=Saved der Form zu tun hat und dass eine Änderung auf "First" das Problem umgeht. Verwirrend dabei ist, dass dieses Verhalten auf unserem Entwicklungsserver (SQL 2005 SP2) nicht zu beobachten ist, trotz gleicher Form-Merkmale.
Meine Fragen dazu also:
Läßt sich dieses extrem lästige, weil telefonanruffördernde, Verhalten auch anderweitig steuern?
Hängt es mit dem Patch-Stand des SQL Servers zusammen?
Was machen andere ggf. mit Forms/Tabellen und solchen Datenvolumen um solche Probleme zu umgehen?
Zuletzt geändert von SilverX am 9. März 2007 07:22, insgesamt 1-mal geändert.
8. März 2007 08:34
Wie sehen denn die Ausführungspläne aus (Live und Entwicklung)?
8. März 2007 09:22
Die sind identisch:
Compute Scalar[2,1];Clustered Index Seek(Company$Contact$0)[3,2]
Cost: Select 0%, Compute 0%, Seek 100%
Compute Scalar[2,1];Clustered Index Seek(Company$Contact$0)[3,2]
Cost: Select 0%, Compute 0%, Seek 100%
8. März 2007 12:49
Nun, am Ausführungsplan liegt's nicht, der "Clustered Index Seek" ist in dem Fall ideal.
Ich fürchte, daß das Problem darin besteht, daß NAV hier einen "cursor" mit 1 Mio. Datensätzen aufbaut - im SQL Profiler zu verifizieren - und der entprechende User-Client soz. speichermäßig damit überfordert ist.
Der Unterschied im Verhalten von Dev zu Live liegt wahrscheinlich darin, daß die Entwickler-Rechner besser ausgestattet sind.
Das Ändern der "SourceTablePlacement" Eigenschaft würde eben anstatt einer "cursor" Transaktion ein simples SELECT TOP 1 ausführen ...
Ich fürchte, daß das Problem darin besteht, daß NAV hier einen "cursor" mit 1 Mio. Datensätzen aufbaut - im SQL Profiler zu verifizieren - und der entprechende User-Client soz. speichermäßig damit überfordert ist.
Der Unterschied im Verhalten von Dev zu Live liegt wahrscheinlich darin, daß die Entwickler-Rechner besser ausgestattet sind.
Das Ändern der "SourceTablePlacement" Eigenschaft würde eben anstatt einer "cursor" Transaktion ein simples SELECT TOP 1 ausführen ...
8. März 2007 15:21
Beschleunigen kann dann nur noch ein Trick:
eine Form ohne Tabelle erstellen, darauf ein paar Felder platzieren, nach denen gesucht werden soll, Datenquelle eine Variable des entsprechenden Typs und dann mit Klick auf einen Button "suchen" die Filter auf die Contact Tabelle anwenden, einen findfirst machen und die Form genau mit diesem Datensatz öffnen.
eine Form ohne Tabelle erstellen, darauf ein paar Felder platzieren, nach denen gesucht werden soll, Datenquelle eine Variable des entsprechenden Typs und dann mit Klick auf einen Button "suchen" die Filter auf die Contact Tabelle anwenden, einen findfirst machen und die Form genau mit diesem Datensatz öffnen.
8. März 2007 15:30
Dieser FINDFIRST bewirkt exact das gleiche wir ein SourceTablePlacement = First !
8. März 2007 17:13
Ja, Jörg, das ist richtig, aber der Trick ist, vor dem Öffnen der Form schon den Filter auf den gesuchten Record zu setzen, wenn schnell ein bestimmter Kontakt wg eines Anrufs geöffnet werden soll.
Wenn aber jemand z.B. die Bearbeitung eines Kontaktes weitermachen will, also genau den Kontakt wieder öffnen will, der zuletzt angezeigt wurde, macht ihm das Sourcetableplacement einen Strich durch die Rechnung. das wird mit dem Findfirst auf der Hilfsform vor dem öffnen der Kontaktkarte umgangen. Diese Hilfsform ist dann auch so klein, dass sie irgendwo im Eck liegen kann ohne zu stören und bei Bedarf sofort greifbar ist.
Wenn aber jemand z.B. die Bearbeitung eines Kontaktes weitermachen will, also genau den Kontakt wieder öffnen will, der zuletzt angezeigt wurde, macht ihm das Sourcetableplacement einen Strich durch die Rechnung. das wird mit dem Findfirst auf der Hilfsform vor dem öffnen der Kontaktkarte umgangen. Diese Hilfsform ist dann auch so klein, dass sie irgendwo im Eck liegen kann ohne zu stören und bei Bedarf sofort greifbar ist.
8. März 2007 17:55
Ah so! Jetzt hab' ich's verstanden! 
8. März 2007 18:53
viel wichtiger ist allerdings, ob auch SilverX das verstanden hat und es ihm weiterhilft 
8. März 2007 22:25
Nein ich bin nicht verschollen, war nur den ganzen Tag mit anderen Dingen beschäftigt.
@stryk: Der Client Rechner war in beiden Fällen der gleiche. Nur die physischen Server und die verwendete SQL Server version unterscheiden sich ("SQL 2005 SP1 + Rollup = Produktiv, definitiv physisch besser ausgestattet als der "SQL 2005 SP2 = Entwicklung"). Also vermute ich eher einen Unterschied beim Server. Datenmengen unterscheiden sich um 10-15%.
Weiterhin ist es so, dass dieses Phänomen ja nur ungefiltert auftritt. Mit Filtern wird recht schnell der richtige Datensatz gefunden. Zwei Auszüge aus dem Profiler direkt nach Öffnen der Datenbanken und der Kontakt Karte ohne Filter:
@Michael: So etwas in der Art haben wir bereits. Halt Adress- und E-Mail Felder zum schnellen Filtern ohne Finger verbiegen :) Ist zwar grundsätzlich mal eine Umgehung des Problems, aber irgendwie nicht das Gelbe vom Ei.
Ich behaupte einfach mal das ist ein Fehler. Obiger Auszug und meine Tests lassen aber hoffen, dass es sich hierbei tatsächlich um ein Problem des SQL Servers handelt der mit dem SP2 beseitigt ist. Würde auch erklären, warum derzeit viele Mitarbeiter allgemein über Performance Probleme klagen. Wenn nur 3 Leute gleichzeitig ne Form öffnen...
Sobald der Server gepatched ist werde ich das Resultat hier kund tun. Mal abwarten.
@stryk: Der Client Rechner war in beiden Fällen der gleiche. Nur die physischen Server und die verwendete SQL Server version unterscheiden sich ("SQL 2005 SP1 + Rollup = Produktiv, definitiv physisch besser ausgestattet als der "SQL 2005 SP2 = Entwicklung"). Also vermute ich eher einen Unterschied beim Server. Datenmengen unterscheiden sich um 10-15%.
Weiterhin ist es so, dass dieses Phänomen ja nur ungefiltert auftritt. Mit Filtern wird recht schnell der richtige Datensatz gefunden. Zwei Auszüge aus dem Profiler direkt nach Öffnen der Datenbanken und der Kontakt Karte ohne Filter:
- Code:
Produktiv:
declare @p1 int
set @p1=180150023
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=10
exec sp_cursoropen @p1 output,N'SELECT *,DATALENGTH("Picture") FROM "NAVPROD"."dbo"."Company$Contact" WHERE "No_">=@P1 ORDER BY "No_" OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20)','-CP-TMPL-CONS-P'
select @p1, @p3, @p4, @p5
Reads: 3742098, Writes: 52010, Duration: 20851
- Code:
Entwicklung:
declare @p1 int
set @p1=180150019
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=10
exec sp_cursoropen @p1 output,N'SELECT *,DATALENGTH("Picture") FROM "NAVDEV"."dbo"."Company$Contact" WHERE "No_">=@P1 ORDER BY "No_" OPTION (FAST 10)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20)','-CP-TMPL-CONS-P'
select @p1, @p3, @p4, @p5
Reads: 23, Writes: 0, Duration: 0
@Michael: So etwas in der Art haben wir bereits. Halt Adress- und E-Mail Felder zum schnellen Filtern ohne Finger verbiegen :) Ist zwar grundsätzlich mal eine Umgehung des Problems, aber irgendwie nicht das Gelbe vom Ei.
Ich behaupte einfach mal das ist ein Fehler. Obiger Auszug und meine Tests lassen aber hoffen, dass es sich hierbei tatsächlich um ein Problem des SQL Servers handelt der mit dem SP2 beseitigt ist. Würde auch erklären, warum derzeit viele Mitarbeiter allgemein über Performance Probleme klagen. Wenn nur 3 Leute gleichzeitig ne Form öffnen...
Sobald der Server gepatched ist werde ich das Resultat hier kund tun. Mal abwarten.
9. März 2007 01:47
Ja, ich habe hier im Partnerstammtisch etwas gelesen, dass es da einen Hotfix nach SP2 geben soll, der eine ReCompile option ermöglicht, was im Zusammenhang mit großen Tabellen (>1Mio. Datensätze) deutliche performance bringen soll. Dieses Hotfix zu KB 930775 ist über das Microsoft Support-Team zu beziehen. Auf Eurem Entwicklungsserver scheint es schon angewendet zu sein, wenn Ihr da keine Probleme habt.
9. März 2007 07:21
Ich habe nun, da im Entwicklungssystem keine Probleme auftraten, eher im Gegensatz, das SQL 2005 SP2 heute Morgen auch im Produktivsystem eingespielt. Das Problem ist weg. Die Karte wird innerhalb von Millisekunden geöffnet. Mit und ohne Filter.
Danke für die Info Michael. Ich werde die Performance nun noch etwas genauer beobachten. Gerade die Postentabellen die schon einige Mio. Datensätze beinhalten. Ich erhoffe mir aber insgesamt nochmal eine große Leistungssteigerung durch das Datenbankupdate Update von 3.70 auf 4.0 SP3 welches nächsten Monat stattfindet. Da sind ja doch einige Schlüssel umgestellt. Danach macht es dann auch Sinn nochmal alles genauer unter die Lupe zu nehmen und an den Schrauben zu drehen.
Wichtig ist, dass dieses Problem nun erstmal aus der Welt ist!
Danke für die Info Michael. Ich werde die Performance nun noch etwas genauer beobachten. Gerade die Postentabellen die schon einige Mio. Datensätze beinhalten. Ich erhoffe mir aber insgesamt nochmal eine große Leistungssteigerung durch das Datenbankupdate Update von 3.70 auf 4.0 SP3 welches nächsten Monat stattfindet. Da sind ja doch einige Schlüssel umgestellt. Danach macht es dann auch Sinn nochmal alles genauer unter die Lupe zu nehmen und an den Schrauben zu drehen.
Wichtig ist, dass dieses Problem nun erstmal aus der Welt ist!