ich sitze gerade an einer eigentlich leichten SQL-Abfrage in der ich in der WHERE-Klausel ein OR benutze.
Die Abfrage läuft über 7min (dann habe ich sie abgebrochen)
Abfrage sieht so aus:
[highlight=sql]
select * from Staffel
where
( CAST(CAST(WARENGR.Firma AS nvarchar(8)) +'.'
+ CAST(WARENGR.Mandant AS nvarchar(8)) +'.'
+ CAST(ST_1 AS nvarchar(8)) +'.'
+ CAST(ST_2 AS nvarchar(8)) +'.'
+ CAST(ST_3 AS nvarchar(8)) +'.'
+ CAST(ST_4 AS nvarchar(8)) +'.'
+ CAST(ST_5 AS nvarchar(8)) +'.'
+ CAST(ST_6 AS nvarchar(8)) +'.'
+ CAST(ST_7 AS nvarchar(8)) +'.'
+ CAST(ST_8 AS nvarchar(8)) +'.'
+ CAST(ST_9 AS nvarchar(8)) +'.'
+ CAST(ST_10 AS nvarchar(8)) AS nvarchar(150))
IN (
select CAST(CAST(KUNDART.FIRMA AS nvarchar(8)) +'.'
+ CAST(KUNDART.MANDANT AS nvarchar(8)) +'.'
+ CAST(VK_AB_1 AS nvarchar(8)) +'.'
+ CAST(VK_AB_2 AS nvarchar(8)) +'.'
+ CAST(VK_AB_3 AS nvarchar(8)) +'.'
+ CAST(VK_AB_4 AS nvarchar(8)) +'.'
+ CAST(VK_AB_5 AS nvarchar(8)) +'.'
+ CAST(VK_AB_6 AS nvarchar(8)) +'.'
+ CAST(VK_AB_7 AS nvarchar(8)) +'.'
+ CAST(VK_AB_8 AS nvarchar(8)) +'.'
+ CAST(VK_AB_9 AS nvarchar(8)) +'.'
+ CAST(VK_AB_10 AS nvarchar(8)) AS nvarchar(150))
from KundenVerknuepfung)
OR
CAST(CAST(WARENGR.Firma AS nvarchar(8)) +'.'
+ CAST(WARENGR.Mandant AS nvarchar(8)) +'.'
+ CAST(ST_1 AS nvarchar(8)) +'.'
+ CAST(ST_2 AS nvarchar(8)) +'.'
+ CAST(ST_3 AS nvarchar(8)) +'.'
+ CAST(ST_4 AS nvarchar(8)) +'.'
+ CAST(ST_5 AS nvarchar(8)) +'.'
+ CAST(ST_6 AS nvarchar(8)) +'.'
+ CAST(ST_7 AS nvarchar(8)) +'.'
+ CAST(ST_8 AS nvarchar(8)) +'.'
+ CAST(ST_9 AS nvarchar(8)) +'.'
+ CAST(ST_10 AS nvarchar(8)) AS nvarchar(150))
IN (
select CAST(CAST(ARTLASER.FIRMA AS nvarchar(8)) +'.'
+ CAST(ARTLASER.MANDANT AS nvarchar(8)) +'.'
+ CAST(ME_1 AS nvarchar(8)) +'.'
+ CAST(ME_2 AS nvarchar(8)) +'.'
+ CAST(ME_3 AS nvarchar(8)) +'.'
+ CAST(ME_4 AS nvarchar(8)) +'.'
+ CAST(ME_5 AS nvarchar(8)) +'.'
+ CAST(ME_6 AS nvarchar(8)) +'.'
+ CAST(ME_7 AS nvarchar(8)) +'.'
+ CAST(ME_8 AS nvarchar(8)) +'.'
+ CAST(ME_9 AS nvarchar(8)) +'.'
+ CAST(ME_10 AS nvarchar(8)) AS nvarchar(150))
from LagerVerknuepfung)
) [/highlight]
Ich weiß dass es viel Performance kostet zusammengesetzte Strings zu vergleichen aber wenn ich die Abfragen OHNE OR separat voneinander ausführe, dauern sie 2 Sekunden.
-
Kennt jemand einen alternative um dieses Problem zu lösen?
Die Abfrage läuft über 7min (dann habe ich sie abgebrochen)
Abfrage sieht so aus:
[highlight=sql]
select * from Staffel
where
( CAST(CAST(WARENGR.Firma AS nvarchar(8)) +'.'
+ CAST(WARENGR.Mandant AS nvarchar(8)) +'.'
+ CAST(ST_1 AS nvarchar(8)) +'.'
+ CAST(ST_2 AS nvarchar(8)) +'.'
+ CAST(ST_3 AS nvarchar(8)) +'.'
+ CAST(ST_4 AS nvarchar(8)) +'.'
+ CAST(ST_5 AS nvarchar(8)) +'.'
+ CAST(ST_6 AS nvarchar(8)) +'.'
+ CAST(ST_7 AS nvarchar(8)) +'.'
+ CAST(ST_8 AS nvarchar(8)) +'.'
+ CAST(ST_9 AS nvarchar(8)) +'.'
+ CAST(ST_10 AS nvarchar(8)) AS nvarchar(150))
IN (
select CAST(CAST(KUNDART.FIRMA AS nvarchar(8)) +'.'
+ CAST(KUNDART.MANDANT AS nvarchar(8)) +'.'
+ CAST(VK_AB_1 AS nvarchar(8)) +'.'
+ CAST(VK_AB_2 AS nvarchar(8)) +'.'
+ CAST(VK_AB_3 AS nvarchar(8)) +'.'
+ CAST(VK_AB_4 AS nvarchar(8)) +'.'
+ CAST(VK_AB_5 AS nvarchar(8)) +'.'
+ CAST(VK_AB_6 AS nvarchar(8)) +'.'
+ CAST(VK_AB_7 AS nvarchar(8)) +'.'
+ CAST(VK_AB_8 AS nvarchar(8)) +'.'
+ CAST(VK_AB_9 AS nvarchar(8)) +'.'
+ CAST(VK_AB_10 AS nvarchar(8)) AS nvarchar(150))
from KundenVerknuepfung)
OR
CAST(CAST(WARENGR.Firma AS nvarchar(8)) +'.'
+ CAST(WARENGR.Mandant AS nvarchar(8)) +'.'
+ CAST(ST_1 AS nvarchar(8)) +'.'
+ CAST(ST_2 AS nvarchar(8)) +'.'
+ CAST(ST_3 AS nvarchar(8)) +'.'
+ CAST(ST_4 AS nvarchar(8)) +'.'
+ CAST(ST_5 AS nvarchar(8)) +'.'
+ CAST(ST_6 AS nvarchar(8)) +'.'
+ CAST(ST_7 AS nvarchar(8)) +'.'
+ CAST(ST_8 AS nvarchar(8)) +'.'
+ CAST(ST_9 AS nvarchar(8)) +'.'
+ CAST(ST_10 AS nvarchar(8)) AS nvarchar(150))
IN (
select CAST(CAST(ARTLASER.FIRMA AS nvarchar(8)) +'.'
+ CAST(ARTLASER.MANDANT AS nvarchar(8)) +'.'
+ CAST(ME_1 AS nvarchar(8)) +'.'
+ CAST(ME_2 AS nvarchar(8)) +'.'
+ CAST(ME_3 AS nvarchar(8)) +'.'
+ CAST(ME_4 AS nvarchar(8)) +'.'
+ CAST(ME_5 AS nvarchar(8)) +'.'
+ CAST(ME_6 AS nvarchar(8)) +'.'
+ CAST(ME_7 AS nvarchar(8)) +'.'
+ CAST(ME_8 AS nvarchar(8)) +'.'
+ CAST(ME_9 AS nvarchar(8)) +'.'
+ CAST(ME_10 AS nvarchar(8)) AS nvarchar(150))
from LagerVerknuepfung)
) [/highlight]
Ich weiß dass es viel Performance kostet zusammengesetzte Strings zu vergleichen aber wenn ich die Abfragen OHNE OR separat voneinander ausführe, dauern sie 2 Sekunden.
-
Kennt jemand einen alternative um dieses Problem zu lösen?
Comment