MS SQL Server CE - PerformanceProblem

[Scheck Andreas]

Hallo zusammen,

folgende Problemstellung ist vorhanden:

Auf einem mobilen Gerät sollen Artikelpositionen erfasst werden. Nachdem ein Zahlen-Buchstabencode in das entsprechende Eingabefeld eingegeben wurde, wird der Artikel in der DB gesucht. Bei der Eingabe kann es sich um die Artikelnummer oder einen EAN-Code handeln.

Da für einen Artikel mehrere verschiedene Barcodes möglich sind (auch wenn man es nicht glauben will:-)), wurden die Barcodes in eine eigene Tabelle ausgelagert und über die ArtikelID miteinander verknüpft.

Folgendes SQL-Skript erstellt die beiden Tabellen:

CREATE TABLE ArtBarCode (ID int PRIMARY KEY, ArtID int NOT NULL, EANNummer nchar(50) NOT NULL, Kolli bit NOT NULL)

CREATE TABLE Artikel (ArtID int PRIMARY KEY, ArtNr nchar(50) NOT NULL UNIQUE, Name nchar(100) NULL, Name2 nchar(100) NULL, Status tinyint NULL, EANNummer nchar(24) NULL, KZSteuer tinyint NULL, VK1oS money NULL, VK2oS money NULL, VK3oS money NULL, VK4oS money NULL, VK5oS money NULL, VK6oS money NULL, VK1mS money NULL, Verkaufseinheit int NULL, Note1 nchar(150) NULL, Pfand_id int NULL, IstPfand bit NULL, GewStk nchar(1) NULL, Einheit nchar(50) NULL)

CREATE INDEX IX_EANNummer ON ArtBarCode (EANNummer)
CREATE INDEX IX_ArtID ON ArtBarCode (ArtID)
CREATE INDEX IX_ArtNr ON Artikel (ArtNr)
CREATE INDEX IX_ArtName ON Artikel (Name)

Mit der folgenden einfachen Abfrage der beiden Tabellen benötigt
SQL-Server CE schätzungsweise gut 10 Sekunden, obwohl in der Artikeldatenbank lediglich 341 und in der ArtBarCode-Tabelle nur 351 Datensätze vorhanden sind:

Beispielabfrage:

SELECT
a.ArtID, a.ArtNr, a.name, a.Name2, a.Vk1oS, a.Vk1mS, a.Note1, a.kzSteuer, a.VK2oS, a.VK3oS, a.VK4oS, a.VK5oS, a.VK6oS, a.Verkaufseinheit, a.Pfand_id, a.IstPfand, a.GewStk, a.Einheit

FROM
Artikel a, ArtBarcode b

WHERE
((a.ArtNr = '512') AND (a.ArtID > -1)) OR ((a.ArtID = b.ArtID) AND (a.ArtID > -1) AND (b.EANNummer = '512'))

GROUP BY
a.Name, a.Name2, a.ArtID, a.ArtNr, a.Vk1oS, a.Vk2oS, a.Vk3oS, a.Vk4oS, a.Vk5oS, a.VK6oS, a.Vk1mS, a.Note1, a.KZSteuer, a.Verkaufseinheit, a.Pfand_id, a.IstPfand, a.GewStk, a.Einheit

ORDER BY a.Name

Daraufhin habe ich noch zusätzliche Indexe angelegt, für jedes Feld, das in der Group-By-Klausel verwendet wird.

CREATE INDEX IX_ArtName2 ON Artikel (Name2)
CREATE INDEX IX_Vk1oS ON Artikel (Vk1oS)
CREATE INDEX IX_Vk2oS ON Artikel (Vk2oS)
CREATE INDEX IX_Vk3oS ON Artikel (Vk3oS)
CREATE INDEX IX_Vk4oS ON Artikel (Vk4oS)
CREATE INDEX IX_Vk5oS ON Artikel (Vk5oS)
CREATE INDEX IX_Vk6oS ON Artikel (Vk6oS)
CREATE INDEX IX_Vk1mS ON Artikel (Vk1mS)
CREATE INDEX IX_Note1 ON Artikel (Note1)
CREATE INDEX IX_KzSteuer ON Artikel (KzSteuer)
CREATE INDEX IX_Verkaufseinheit ON Artikel (Verkaufseinheit)
CREATE INDEX IX_Pfand_Id ON Artikel (Pfand_Id)
CREATE INDEX IX_IstPfand ON Artikel (IstPfand)
CREATE INDEX IX_GewStk ON Artikel (GewStk)
CREATE INDEX IX_Einheit ON Artikel (Einheit)

Ausserdem habe ich alle Textfelder auch mit nvarchars getestet sowie mit nchars.

Eine Verbesserung hat sich nicht ergeben. Am Ende habe ich mitgestoppt und diese einfache Abfrage benötigt ziemlich genau 13 Sekunden.

Diese Zeitspanne ist einfach inakzeptabel.

Ich bitte um Hilfe und bedanke mich an dieser Stelle schon mal im Voraus.

mfg
Andreas

[hwoess]

Das GROUP BY gibt für mich keinen Sinn, was passiert denn, wenn man es weglässt (oder habe ich was übersehen - der Tag ist schon lange)?
Mein Vorschlag:
SELECT ArtID, ArtNr, ......
FROM Artikel WHERE ArtID > -1 and ((ArtNr = '512') OR exists(select * from ArtBarcode where EANNummer = '512' and ArtBarcode.ArtID = Artikel.ArtID))
ORDER BY Name
Falls es da zu doppelten Sätzen kommt, würde ich die eher mittels
SELECT DISTINCT ArtID, ArtNr, ...... eleminieren.<br>
bye, Helmu

[Scheck Andreas]

Hallo Helmut,

Deine Antwort hat mir sehr geholfen.

Vielen Dank.

Gruß
Andrea

[hwoess]

Wenn das die Lösung war, dann solltest du jetzt auch noch die nicht benutzten Indexe eleminieren, ist ja sonst nur zusätzlicher Verwaltungsaufwand.<br>
bye, Helmut<br>
PS: würde mich aber interessieren, um wieviel schneller es jetzt geworden ist :-

[Scheck Andreas]

Hallo Helmut,

mit dem SQL-Statement: SELECT DISTINCT a.ArtNr,....etc... FROM Artikel a, ARtBarCode b WHERE ((a.ArtNr = '512) AND (a.ArtID > -1)) OR ((b.EANNummer = '512') AND (a.ArtID = b.ArtID) AND (a.ArtID > -1)) ORDER BY a.ArtNr konnte ich den Suchvorgang von 13 Sekunden auf 9 Sekunden auf dem mobilen Gerät reduzieren.

Auf dem richtigen (grossen) SQL-Server läuft das Statement innerhalb ein paar Millisekunden ab.

Offensichtlich ist die SQL-Server-CE-Variante bei weitem nicht so performant und ist deshalb immer noch unbefriedigend (9 Sekunden sind einfach zu lange).

Deshalb mache ich jetzt programmtechnisch das, was die Datenbank eigentlich machen sollte.

So bin ich nun dabei, zwei Statements draus zu machen.
Im ersten frage ich lediglich die Artikeltabelle ab (SELECT ArtID.....etc....FROM Artikel WHERE (ArtNr = '512') AND (ArtID > -1) ORDER BY ArtNr und im nächsten frage ich dann SELECT DISTINCT a.ArtID....etc....FROM Artikel a, ArtBarCode b WHERE (b.EANNummer = '512) AND NOT (a.ArtNr = '512') AND (a.ArtID = b.ArtID) AND (aArtID > -1)

Wenn ich diese Abfrage über das SQL-Query auf dem mobilen Geräte ausführe, benötigt es unterhalb einer Sekunde.

Gut wären natürlich Stored Procedures aber meines Wissens nach geht das bei der CE-Variante vom MS SQL-Server nicht (Korrigiere mich bitte, falls ich mich irren sollte).

Also nochmals vielen Dank für die Hilfe.

Andrea

[hwoess]

Hmm, von 13 auf 9 Sekunden ist nicht befriedigend. Hatte bisher noch nichts mit der CE Version zu tun. Aber das ist schon erschreckend. Nur, wenn die Abfrage in zwei Teile zerlegt dann in einer Sekunde geht und das gleiche Ergebnis bringt, müsste sich doch noch was machen lassen. Allerdings nicht mit stored procs, die gibt es, wie du schon sagtest, beim CE leider nicht.<br>
bye, Helmu

[Scheck Andreas]

Hallo Helmut,

denke auch, dass man da noch was machen könnte. Vielleicht spielen auch noch andere Faktoren eine Rolle.

Vielleicht ist das Gerät selbst nicht das schnellste oder es ist zuwenig Arbeitsspeicher vorhanden, damit das Statement schneller ausgeführt werden kann.

Jedenfalls habe ich es ja nun wie oben beschrieben anders gelöst.

Danke nochmals.

Andrea