Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

19 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

19 juli 2024, 03:30:55

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: WHERE NOT IN... subquery doet raar?  (gelezen 29591 keer)

0 leden en 1 gast bekijken dit topic.

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.856
  • Geslacht: Man
    • NL Computer Forum
WHERE NOT IN... subquery doet raar?
« Gepost op: 29 november 2009, 21:49:06 »
Hallo allemaal,

Of het aan SQL Server 2008 kan liggen, weet ik niet, maar ik liep laatst tegen iets raars aan. Althans, iets werkte niet zoals ik het zou verwachten. Hier is in het kort de situatie:

Tabel A: bevat een kolom 'ID' die is ingesteld als int. Deze kolom bevat (dus) alleen gehele getallen.
Tabel B: bevat een kolom 'project' die is ingesteld als nvarchar(20). Deze kolom bevat zowel cijfers als letters, maar is nooit NULL (hooguit staat er '')

Om te kijken welke projectnummers van tabel A ik óók in tabel B kan vinden, gebruikte ik deze query:

select ID from A where ID IN (select cast(project as int) from B where IsNumeric(project) = 1)

Als ik alleen de subquery uitvoer, krijg ik inderdaad alleen de projecten die puur alleen een nummer hebben als aanduiding, dus dat werkt.
Maar als ik de volledige query uitvoer, krijg ik foutmeldingen die erop duiden dat de niet-numerieke projectnummers uit de B-tabel tóch worden meegenomen!

Ik heb er uiteindelijk maar omheen gewerkt, door de numerieke projectnummers in een tijdelijk tabelletje te stoppen, en die tijdelijke tabel in de subquery gebruikt.

Maar hoe kan dit nou?  :o

Groeten,

Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence

Offline TdJ

  • Sysop
  • *****
  • Berichten: 218
  • Geslacht: Man
    • Mijn bedrijf
Re: WHERE NOT IN... subquery doet raar?
« Reactie #1 Gepost op: 29 november 2009, 22:24:23 »
Ronald,

Zomaar een wilde opmerking: als je convert ipv cast gebruikt, gaat het dan ook fout?

TdJ

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: WHERE NOT IN... subquery doet raar?
« Reactie #2 Gepost op: 29 november 2009, 22:27:46 »
Hoi Ronald,

De SQL Server optimizer bepaalt uiteindelijk hoe een query wordt uitgevoerd. En dat betekent dat de volgorde waarin dingen worden geevalueerd kan afwijken van de volgorde waarin het in de query staat en/of waarin jij denkt dat de query zou moeten worden uitgevoerd.

In dit geval lijkt het erop dat de optimizer de IN subqery heeft vertaald in een join, en dat de WHERE clause op het resultaat van die join wordt uitgevoerd - maar helaas moet voor de join een conversie naar integer worden uitgevoerd.

Een andere manier om dit op te lossen zou zijn geweest een CASE expressie te gebruiken, want daarbij mag de optimizer de volgorde niet veranderen:
... IN (SELECT CASE(WHEN ISNUMERIC(project) = 1 THEN CAST(project AS int) ELSE 0 END) FROM ...
(en in plaats van 0 een andere waarde als de waarde 0 gebruikt kan worden)

Maar de allerbeste oplossing is een beter ontwerp, want een kolom waarin zowel numerieke als niet numerieke gegevens kunnen voorkomen maar die wel met een numerieke kolom vergeleken moet worden is in mijn ogen zoiets als een grote oranje knipperlamp met het opschrift "SLECHT OTNWERP" (en een luide sirene erbij, voor de zekerheid).

Groetjes, Hugo

PS: Tony - met convert zou het inderdaad ook fout zijn gegaan.
--
Hugo Kornelis, SQL Server MVP

Offline TdJ

  • Sysop
  • *****
  • Berichten: 218
  • Geslacht: Man
    • Mijn bedrijf
Re: WHERE NOT IN... subquery doet raar?
« Reactie #3 Gepost op: 29 november 2009, 22:32:12 »
Hugo / Ronald,

Re: convert : Daar was ik al een beetje bang voor...

Hugo, is dat soort optimizing ook in het executieplan te zien? Nav een van jouw presentaties bij de SDN over indexen kijk ik daar namelijk vaak om te zien of bijvoorbeeld de index wel (goed) wordt gebruikt.

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: WHERE NOT IN... subquery doet raar?
« Reactie #4 Gepost op: 29 november 2009, 22:41:18 »
Hoi Tony,

Het is te zien, als je een execution plan kan lezen. Dat vergt wel veel oefening. En helaas staat in het grafische execution plan net even minder informatie dan in een tekstuele, die echter nog iets lastiger te lezen is.

In het geval van Ronald's query verwacht ik dat je in het execution plan helemaal rechts een scan op een van de tabellen ziet en een scan of seek op de andere, die dan met een join (vermoedelijk inner loop, maar merge join zou ook kunnen) bijeen worden gebracht. En links van die join staat dan een filter, waar je dus het WHERE criterium in terug kan vinden (om het precieze criterium te zien moet je volgens mij inderdaad naar het tekstuele plan - of, als je een echte die-hard bent, naar de XML versie)

Ik ben overigens blij te horen dat je echt iets mee hebt kunnen nemen van mijn presentatie!

Groetjes, Hugo
--
Hugo Kornelis, SQL Server MVP

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.856
  • Geslacht: Man
    • NL Computer Forum
Re: WHERE NOT IN... subquery doet raar?
« Reactie #5 Gepost op: 29 november 2009, 22:53:29 »
Hoi Hugo,

Hartelijk dank voor je tips! :)

De SQL Server optimizer bepaalt uiteindelijk hoe een query wordt uitgevoerd. En dat betekent dat de volgorde waarin dingen worden geevalueerd kan afwijken van de volgorde waarin het in de query staat en/of waarin jij denkt dat de query zou moeten worden uitgevoerd.

Phew, gelukkig! Ik was al even bang dat ik gek werd, maar feitelijk kwam het dus door een soort van Mijnheer Van Dalen Wacht Op Antwoord regel, maar dan op z'n SQL's, begrijp ik. ;)

Zou zo'n query onder SQL 2005 of 2000 toch nog werken zoals ik het had verwacht? Mijn algemene indruk van SQL Server 2008 is dat het wat kieskeuriger is. Of misschien moet ik zeggen: per ongeluk een stukje code gebruiken die niet helemaal optimaal is, wordt snel afgestraft met foutmeldingen. <g>

Maar de allerbeste oplossing is een beter ontwerp, want een kolom waarin zowel numerieke als niet numerieke gegevens kunnen voorkomen maar die wel met een numerieke kolom vergeleken moet worden is in mijn ogen zoiets als een grote oranje knipperlamp met het opschrift "SLECHT OTNWERP" (en een luide sirene erbij, voor de zekerheid).

Helemaal mee eens, en ik had er bij moeten vermelden dat deze tabellen uit 2 verschillende toepassingen kwamen. Aan mij de eer om die 2 aan elkaar te knopen. ;)

By the way, moet ik nog even snel een avatar van het oude Forum voor je plukken, of...? :D

Groeten,

Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: WHERE NOT IN... subquery doet raar?
« Reactie #6 Gepost op: 29 november 2009, 23:08:39 »
Zou zo'n query onder SQL 2005 of 2000 toch nog werken zoals ik het had verwacht? Mijn algemene indruk van SQL Server 2008 is dat het wat kieskeuriger is. Of misschien moet ik zeggen: per ongeluk een stukje code gebruiken die niet helemaal optimaal is, wordt snel afgestraft met foutmeldingen. <g>

Het zou kunnen werken onder oudere versies en het zou kunnen werken bij SQL 2008. Hangt helemaal af van de keuzes van de optimizer, en die hangen weer af van gegevens in de tabellen, statistics daarover, indexen die er wel of niet zijn, resources die de machine tot zijn beschikking heeft, etc. En uiteraard is de optimizer bij uitstek een onderdeel waar van versie tot versie aan gesleuteld wordt.

Dus wellicht werkt het vandaag op een oudere versie, maar dat is evengoed geen garantie voor morgen. De enige veilige regel is dat je nooit code moet hebben die afhangt van de volgorde waarin een query wordt uitgevoerd.

By the way, moet ik nog even snel een avatar van het oude Forum voor je plukken, of...? :D

Had ik daar een avatar, dan?  :-\
Doe geen moeite, ik upload zo wel effe mijn foto, dan zie je weer tegen wie je praat.
--
Hugo Kornelis, SQL Server MVP

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.856
  • Geslacht: Man
    • NL Computer Forum
Re: WHERE NOT IN... subquery doet raar?
« Reactie #7 Gepost op: 3 december 2009, 22:08:16 »
Hoi Hugo,

Ok, dank je wel weer! Ik ben wel benieuwd hoe ik kan herkennen wanneer code "afhangt van de volgorde waarin een query wordt uitgevoerd". In die zin is het een kleine shock dat wat ik heb geleerd (gooi eerst wat je nodig hebt in de subquery, en dan de hoofdquery lekker dáármee aan de slag), dus niet altijd zo blijkt te werken.  ???

Hee, bekende foto! Ik zie dat je -net als ik- geen dag ouder wordt. <bg>

Groeten,

Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: WHERE NOT IN... subquery doet raar?
« Reactie #8 Gepost op: 3 december 2009, 23:57:26 »
Ik ben wel benieuwd hoe ik kan herkennen wanneer code "afhangt van de volgorde waarin een query wordt uitgevoerd".

De enige situatie die ik kan bedenken is het voorbeeld in deze discussie (of heet het hier nog thread?  ;)). In algemene termen dus de situatie waarin je in een query een constructie hebt die tot runtime fouten kan leiden als bepaalde waarden niet worden uitgesloten. Conversies zijn daar een goed voorbeeld van; deling (en andere formules waarin 0 of negatieve waarden tot runtime fouten leiden) zijn een ander voorbeeld. Dus ook een query waarin je deelt door een expressie die 0 kan zijn is riskant, zelfs als je die 0-waarden in de WHERE uitsluit. De beste manier om daar omheen te werken is NULLIF gebruiken. Onderstaande voorbeeld is dus wél veilig.

SELECT Kolom1 / NULLIF(Kolom2, 0) AS ResultaatVanDeling
FROM   VoorbeeldTabel
WHERE  Kolom2 <> 0;

Zonder de NULLIF kan je ondanks de WHERE nog altijd een foutmelding wegens delen door 0 krijgen. Met de NULLIF is dat niet meer mogelijk.

Groetjes, Hugo
--
Hugo Kornelis, SQL Server MVP

Offline Ronald

  • Forum Manager
  • *****
  • Berichten: 1.856
  • Geslacht: Man
    • NL Computer Forum
Re: WHERE NOT IN... subquery doet raar?
« Reactie #9 Gepost op: 1 januari 2010, 23:08:05 »
Hoi Hugo,

De enige situatie die ik kan bedenken is het voorbeeld in deze discussie (of heet het hier nog thread?  ;) ).

Volgens mij heet het hier 'topic', maar je mag het van mij alles noemen. <g>

Dank je voor de tip van de NullIf!

Groeten,

Ronald
Forum Manager NL Computer Forum
Microsoft Certified Solutions Expert (MCSE) - Business Intelligence