SQL Laufzeit bei einfachem WHERE Filter sehr (sehr) schlecht

[Squicky]

Hallo

Ich habe eine SQL-Laufzeit-Frage.
Mein Problem versuche ich an einer (erfundenen) einfachen Tabelle zu erklären:

Es gibt eine SQL Produkt-Tabelle:

SQL-Abfrage 1:

SELECT
	Nummer
	,Warenart
	,Produktart
FROM 
	Tab

	
Nummer  |  Warenart  |  Produktart
------------------------------------
1       | Spielzeug  |  Hammer
2       | Spielzeug  |  Puppe
3       | Spielzeug  |  Hammer
4       | Werkzeug   |  Hammer

Nun ist noch folgende Information gebraucht: Anzahl eindeutiger Warenart und Produktart Kombinationen.

Folgende Abfrage liefert richtiges und gesuchtes Ergbnis:

SQL-Abfrage 2:

SELECT
	Tab.Nummer
	,Tab.Warenart
	,Tab.Produktart
	,AnzahlTab.Anzahl_eindeutige_Warenart_Produktart
FROM 
	Tab
INNER JOIN (

	SELECT
		Warenart
		,Produktart
		,COUNT(Nummer) AS Anzahl_eindeutige_Warenart_Produktart
	FROM 
		Tab
	GROUP BY
		Warenart
		,Produktart
		
) AS AnzahlTab
	ON Tab.Warenart = AnzahlTab.Warenart
	AND Tab.Produktart = AnzahlTab.Produktart


Nummer  |  Warenart  |  Produktart | Anzahl_eindeutige_Warenart_Produktart
-------------------------------------------------------------------------
1       | Spielzeug  |  Hammer     | 2
2       | Spielzeug  |  Puppe      | 1
3       | Spielzeug  |  Hammer     | 2
4       | Werkzeug   |  Hammer     | 1

Abfrage 1 und Abfrage 2 brauchen ca. 2 Sekunden. Diese Zeit ist auch OK.

Wenn man aber an Abfrage 2 folgendenen WHERE Filter anhängt, dann braucht die Abfrage ca. 3 Minuten:

WHERE AnzahlTab.Anzahl_eindeutige_Warenart_Produktart = 1

Es sieht so aus: Wenn man nach AnzahlTab.Anzahl_eindeutige_Warenart_Produktart filtert, dann wird für jede Zeile die "Unterabfrage / Untertabelle" (AnzahlTab) neu berechnet.
Aber dies ist nicht nötig. Kann man dies deaktivieren?

Wie kann auf die vier benötigte Spalten aus der Tabelle (Abfrage 2) mit einem einfachen Selectbefehl zugreifen werden?
Die Laufzeit sollte aber bei ca. 2 bis 3 Sekunden liegen. Keine 3 Minuten.

Benutzt wird MS SQL 2012

Danke

 

[Metzlor]

Schonmal mit Having AnzahlTab.Anzahl_eindeutige_Warenart_Produktart = 1 probiert.

Bei Where versucht er es aus der Datenbank zu ziehen, bei having nimmt er das vorhandene Ergebniss und filtert dies.

 

[knubbel74]

Hallo

versuch es doch mal so:

SELECT DISTINCT
Tab.Nummer
,Tab.Warenart
,Tab.Produktart
,Count(*) as Anzahl
FROM
Tab
GROUP BY
Tab.Nummer
,Tab.Warenart
,Tab.Produktart
HAVING
COUNT(*) = 1

 

[Squicky]

@knubbel74
Wegen "Tab.Nummer" in "GROUP BY" ist die "Anzahl" immer 1.
Denn Nummer ist für jede Zeile eindeutig.

@Metzlor
Das würde gehen.

Aber Abfrage 2 soll später eine View werden.
Und auf diese View soll dann mit Where
Anzahl_eindeutige_Warenart_Produktart = 1
oder
Anzahl_eindeutige_Warenart_Produktart = 2
oder
Anzahl_eindeutige_Warenart_Produktart = @Variable

gefiltert werden "können".

 

[Metzlor]

Wenn das eine View werden soll, dann nehme deine Abfrage und mach eine WHERE oder HAVING abfrage auf die View.

kannst auch knubbel74 abfrage nehmen und group by warenart, produktart ohne nummer nehmen oder wozu wird die Nummer gebraucht?

 

[sdwaroc]

Je nach Größe der Tabelle kannst du enorm von vertikaler Partitionierung profitieren. Außerdem sollten alle Spalten, die in einer WHERE-Klausel auftauchen, indiziert sein.

 

[Metzlor]

SELECT
Warenart
,Produktart
,COUNT(Nummer) AS Anzahl_eindeutige_Warenart_Produktart
FROM
Tab
GROUP BY
Warenart
,Produktart

als VIEW erstellen, und dann im Produktivsystem

Select Nummer, warenart, produktart, Anzahl_eindeutige_warenart_produktart from tab inner join VIEWNAME
on tab.Produktart = VIEWNAME.Produktart and tab.Warenart = VIEWNAME .PRoduktart where
VIEWNAME.Anzahl_eindeutige_Warenart_Produktart = 1

 

[Piktogramm]

Wenn ich das Problem richtig verstehe wäre es sinnvoll Tab.Nummer wegzulassen und auf das Problem mit "SELECT DISTINCT" loszugehen. Siehe:

https://stackoverflow.com/questions/6870397/selecting-distinct-combinations#6870416

Ansonsten, deine Tabelle scheint nicht normalisiert zu sein. Waren- und Produktart als Integer abzubilden kann eine solche Abfrage (je nach DBS) deutlich beschleunigen. Wenn da also erstmal deine große Tabelle mit Waren- und Produktart als Integer durchgewühlt wird und erst im Nachhinein die IDs der *-arten über einen weiteren Join aufgelöst werden, kann schneller sein als eine einzelne Tabelle zu durchwühlen, die die Daten nach denen selektiert wird als String zu parsen sind.

Was deinen Join angeht, je nach DBS kannst du dir das Verfassen des Joins sparen und mit simplen WHERE, GROUP BY und SELECT DISTINCT das Ganze schneller abwickeln, da die Abfrageplanung des DBS mit diesen Funktionen intern besser optimieren als deinen Join.



@sdwareoc
Ob und wann welcher Typ an Indizes sinnvoll ist lässt sich so pauschal nicht sagen. Wenn die Selektivität der WHERE-clause gering ist bringt ein Indizes mitunter nichts. So pauschal würde ich deinen Tip also nicht geben wollen.

 

[Squicky]

(Meine) Lösung:

Abfrage 2 ist in einer "Multi-statment Table-valued Funktion".
Und diese Funktion ist in einer View.

Somit kann man auf die Daten (inkl. Anzahl) normal per View zugreifen und bei Bedarf per where filtern.
Und egal wie und ob gefiltert wird, die Laufzeit ist immer ca. 2 Sekunden.

:-)