SQL Server hoe kan ik de output van top besturen

[Vroem79]

Mijn vraag

Ik wil eigenlijk met een subselect een waarde teruggeven net naar gelang ik output krijg uit een where statement of niet.

Alleen SQL reageert anders net naar gelang ik een tabel erbij betrek of niet.

Mijn 1e poging is :

SQL:

select top 1 * from (
    select 'a' as test
    where 1=1
    UNION ALL
    select 'b' as test
    ) as c

En die gaat gewoon goed, ik krijg daar net naar gelang ik ga spelen met 1=1 of 1=0 a of b uit precies zoals ik het hebben wil.

Ok, gaan we naar poging 2 :

SQL:

--drop table #tmp
select 'c' as test into #tmp

select top 1 * from (
    select 'a' as test
    from #tmp
    where 1=1
    UNION ALL
    select 'b' as test
    ) as c

Ik maak dus nu eerst even een tmp-tabel aan, dit is alleen gedaan omdat het ook optreed met fysieke tabellen, alleen dan is het zo moeilijk pseudo-code te geven.

Alleen het resultaat hiervan is dat ik enkel nog maar b terugkrijg, ik kan op geen enkele wijze nog a terugkrijgen...
Als ik gewoon de inner query uitvoer krijg ik gewoon 2 resultaten met a bovenaan, dus exact wat ik zou verwachten, als ik zeg 1=0 dan krijg ik uit de inner query 1 resultaat met enkel b, de inner query lijkt gewoon te kloppen, alleen door het erbij betrekken van een tabel heb ik opeens een ander resultaat uit mijn outer query.

Rara politiepet?

Ook als ik het expliciet sorteer met de volgende trucage krijg ik niet het goede antwoord :

SQL:

--drop table #tmp
select 'c' as test into #tmp
select top 1 * from (
    select TOP 100 PERCENT * from (
        select 'a' as test
        from #tmp
        where 1=1
        UNION ALL
        select 'b' as test
        ) as c
    order by test asc
    ) as d 

Of ik mijn tussenresultaat nou ascending of descending sorteer hij blijft b teruggeven. En praktisch gezien is dit ook geen oplossing omdat ik voor mijn toepassing niet weet of a groter of kleiner dan b is.

Technisch gezien zou ik het op kunnen lossen met een functie, maar dan verlies ik mijn parallelliteit voor zover ik weet en dit ding moet verschillende waardes doen binnen een query met 20+ miljoen resultaten, dus die parallelliteit geef ik liever niet op.

Het uiteindelijke doel is om een soort tri-state te hebben :
- Als var1 en var2 gelijk zijn, dan return var3
- Als var1 en var2 ongelijk zijn, doe dan een query naar een tabel en retourneer het resultaat
- Anders (als de query geen resultaat teruggeeft) return null

Ik dacht met deze constructie 2 en 1 te vangen, maar dit werkt al niet en ik zie niet in waarom het niet werkt of hoe het ik het buiten een functie om geregeld zou krijgen.

Relevante software en hardware die ik gebruik
SQL Server 2014

[ Voor 0% gewijzigd door RobIII op 22-04-2021 20:59 . Reden: Code tags gefixed ]

[Lethalis]

Vroem79 schreef op donderdag 22 april 2021 @ 20:54:
Technisch gezien zou ik het op kunnen lossen met een functie, maar dan verlies ik mijn parallelliteit voor zover ik weet en dit ding moet verschillende waardes doen binnen een query met 20+ miljoen resultaten, dus die parallelliteit geef ik liever niet op.

Ik heb het nare gevoel dat er iets mis is met het database ontwerp, of dat er geen data warehousing strategie is.

Als ik in het verleden queries moest uitvoeren op zulke grote tabellen dan was het meestal voor statistische gegevens en laat dat nou net ideaal zijn voor een archiveringsproces waarbij je gegevens precies zo verzamelt als je ze wil hebben (kun je eventueel met SSIS packages doen).

En dan doe je de query daar op. Wij verzamelen bijvoorbeeld omzetgegevens per dag met daarin een stukje planned redundancy. Onze dashboards e.d. halen de gegevens daar uit en zijn daardoor een stuk sneller.

Wat jouw tri-state betreft, zou ik waarschijnlijk de data er eerst altijd bij joinen en daarna gewoon een case statement gebruiken en eens kijken hoe de performance is? Eventueel een goede index maken / uitbreiden als dat nodig is.

Zou misschien helpen voor ons / mijn begrip als er meer context zou zijn dan a, b en c

[Kalentum]

Mijn eerste gok uit voorbeeld 2:

Die TOP 1 returned de eerste rij uit de result set. Omdat je geen ORDER BY gebruikt mag SQL Server zelf de volgorde bepalen. Dat is blijkbaar altijd 'b' . Ik ken SQL Server verder niet maar misschien moet je eerst proberen om de UNION te ordenen

code:

select 'c' as test into #tmp

select top 1 * from (
   (
      select 'a' as test
      from #tmp
      where 1=1
      UNION ALL
      select 'b' as test
  ) ORDER BY test
) as c

[Vroem79]

Lethalis schreef op vrijdag 23 april 2021 @ 07:48:
[...]
Ik heb het nare gevoel dat er iets mis is met het database ontwerp, of dat er geen data warehousing strategie is.

Als ik in het verleden queries moest uitvoeren op zulke grote tabellen dan was het meestal voor statistische gegevens

Het nadeel is dat het geen data warehouse is, het is ook geen echt grote tabel. Het is slechts 1 van vele exports, daarom zal er weinig aan het db-ontwerp aangepast kunnen worden (of het moet echt substantieel versnellen). Momenteel lopen er grofweg 20 query's op deze tabellen / aantallen voor 20 externe systemen.
En als er volgende week een klant betaald dan kunnen dat er 21 worden.
Oftewel het heeft de voorkeur om het in de query zelf op te lossen.

Wat jouw tri-state betreft, zou ik waarschijnlijk de data er eerst altijd bij joinen en daarna gewoon een case statement gebruiken en eens kijken hoe de performance is? Eventueel een goede index maken / uitbreiden als dat nodig is.

Mijn collega had nog een idee en we hebben nu de volledige tri-state opgelost als volgt :

SQL:

(SELECT CASE 
                WHEN verkoopEenheid is null THEN brutoPrijs
                WHEN prijsEenheid=verkoopEenheid THEN brutoPrijs
                WHEN EXISTS(
                        SELECT NULL
                        FROM #lookuptable
                        WHERE prijsEenheid=#lookuptable. prijsEenheid and verkoopEenheid=#lookuptable.verkoopEenheid)
                    THEN (SELECT (brutoPrijs * #lookuptable.Multiplier)
                        FROM #lookuptable
                        WHERE prijsEenheid=#lookuptable.prijsEenheid and verkoopEenheid=#lookuptable.verkoopEenheid)
                ELSE NULL
            END) as verkoopEenheidPrijs

let op dat het voorbeeld alsnog verzonnen is, maar dit is volgens mij wel een redelijk voorbeeld dan.

Het komt er op neer dat partij a een prijseenheid en een brutoprijs kan aanleveren terwijl hij wel of geen verkoopeenheid kan hebben met wel of geen losse prijs. Terwijl partij B alleen om kan gaan met een verkoopeenheid met bijbehorende prijs.

En als partij a dan bijv wel een prijseenheid in grammen aanlevert, een verkoopeenheid in kilogrammen dan kunnen wij met een vaste lookuptabel de prijsper dus omrekenen voor klant b die dit nodig heeft.
Maar met een prijseenheid in grammen en een verkoopeenheid in meters, tja daar kunnen wij geen omrekening voor geven.

En een gewone join gaat veel trager zijn dan een lookup in de select (ongeveer 90% hoeft de lookup niet te doen).

Maar ik zou gewoon verwachten dat mijn poging 2 of tenminste mijn poging 3 zou werken, ik vind het heel erg vreemd dat top mijn in poging 3 opgegeven order by negeert.
Als iemand daar nog een verklaring voor heeft dan ben ik daar erg benieuwd naar, want mij lijkt het ongewenst gedrag wat zich hier voordoet.

Kalentum schreef op vrijdag 23 april 2021 @ 08:11:
Mijn eerste gok uit voorbeeld 2:

Die TOP 1 returned de eerste rij uit de result set. Omdat je geen ORDER BY gebruikt mag SQL Server zelf de volgorde bepalen. Dat is blijkbaar altijd 'b' . Ik ken SQL Server verder niet maar misschien moet je eerst proberen om de UNION te ordenen

Dat is dus mijn trucage / poging 3. SQL Server pakt niet blind een order by op een subquery, dan moet je een top oid meegeven, wat ik hier dus truc door op te geven dat ik 100 % wil hebben.
En dit werkt altijd als ik met meerdere tabellen werk, alleen blijkbaar niet als je met vaste waardes werkt...

[Lethalis]

@Vroem79
Jouw voorbeeldje doet idd wel grappige dingen, heb hem even uitgevoerd met wat aanpassingen:

SQL:

select * 
from (
    select TOP 100 PERCENT * 
    from (
        select 'a' as test
        UNION ALL
        select 'b' as test
    ) as c
    order by test desc
) as d

Dit negeert inderdaad de ORDER BY.

Zo heb je de tijdelijke tabel niet nodig om het fenomeen te zien.

b, a wordt a, b

Nu heb ik ook altijd geleerd geen ORDER BY te gebruiken in subqueries. Als je de TOP 100 PERCENT weghaalt, krijg je ook een toepasselijke foutmelding:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

In principe wil je altijd met verzamelingen werken, niet met de weergave ervan (ORDER BY wordt pas op het einde uitgevoerd). Hij doet dus eerst de FROM, dan de SELECT en pas op het einde ORDER BY, maar database servers zijn vrij om de ORDER BY te negeren in een subquery. De ontwikkelaars van MariaDB schrijven bijvoorbeeld op hun site:

https://mariadb.com/kb/en...-a-from-subquery-ignored/

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).

You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.

Vroem79 schreef op vrijdag 23 april 2021 @ 10:14:
[...]
Mijn collega had nog een idee en we hebben nu de volledige tri-state opgelost als volgt

Mooi dat het gelukt is Een CASE statement is ook veel leesbaarder.

Wel vraag ik mij af of het verstandig is om complexere logica in SQL onder te brengen. Dat maakt eventuele fouten lastig om op te sporen / debuggen / testen enzovoorts.

En een gewone join gaat veel trager zijn dan een lookup in de select (ongeveer 90% hoeft de lookup niet te doen).

Heb je het echter ook getest?

Mijn ervaring is dat dit (veel) sneller kan zijn dan je verwacht. In feite doe jij nu een soort "premature optimization" door voor SQL Server te gaan denken. Met een conditionele subquery in een CASE statement doet SQL Server waarschijnlijk veel meer index scans / seeks (hopelijk geen table lookups) dan met een enkele join.

Heb je al het query execution plan bekeken?

[ Voor 15% gewijzigd door Lethalis op 23-04-2021 17:44 ]

[Vroem79]

Lethalis schreef op vrijdag 23 april 2021 @ 17:15:
@Vroem79
In principe wil je altijd met verzamelingen werken, niet met de weergave ervan

Ik houd me ook helemaal niet bezig met de weergave met order by in subquery's, normaliter gebruik je die daar omdat je subquery een kleiner resultaat oplevert en je die hiermee je subquery richt naar een clustered index van je outer query.
Mits juist gebruikt kan dit ervoor zorgen dat je geen sort over je outer query krijgt.

(ORDER BY wordt pas op het einde uitgevoerd). Hij doet dus eerst de FROM, dan de SELECT en pas op het einde ORDER BY, maar database servers zijn vrij om de ORDER BY te negeren in een subquery.

Gelukkig werkt het niet zo simpel, dit is een soort basisschool uitleg, als je query-optimizer dit hanteert dan vraag ik me af of je wel van een database kan praten.

De ontwikkelaars van MariaDB schrijven bijvoorbeeld op hun site:

https://mariadb.com/kb/en...-a-from-subquery-ignored/

A "table" (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).

You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT.

Tja, er zijn redenen dat ik al decennialang wegblijf van mysql en de forks daarvan... Dit is een mooi voorbeeld ervan. Wat er feitelijk staat is :
- de sql standaard zegt dat het niet mag
- we doen het wel omdat er toch behoefte aan is
- maar je kan er niet vanuit gaan dat het ook gebeurt

Als je moet teruggrijpen naar de sql standaard om uit te leggen waarom iets niet goed werkt dan vraag ik me altijd af waarom je je dan niet gewoon 100% aan de sql standaard houdt maar er van alles bij en op en aan gaat zitten bouwen. Oh wacht, dat is omdat je erkent dat de sql standaard niet overal voldoende voor is...

[...]
Mooi dat het gelukt is Een CASE statement is ook veel leesbaarder.

Tja, meningen verschillen. Ik heb nu een case statement wat over 50 regels heengaat omdat ik grotendeels de inner query in de exists moet herhalen.

Wel vraag ik mij af of het verstandig is om complexere logica in SQL onder te brengen. Dat maakt eventuele fouten lastig om op te sporen / debuggen / testen enzovoorts.

Het maakt het niet lastiger, het vereist alleen een meer dan basiskennis van SQL.
En tja, complexe vragen vereisen nu eenmaal ook complexe oplossingen wil je het een beetje performant houden is mijn ervaring.

[...]
Heb je het echter ook getest?
...
Met een conditionele subquery in een CASE statement doet SQL Server waarschijnlijk veel meer index scans (hopelijk geen table lookups) dan met een enkele join.

Als je het goed doet dan doet SQL Server slechts 1 index scan (mits je die index clustered weet aan te spreken, waarvoor je bijv order by in een subquery kan gebruiken).
Maar hier doet hij enkel 1 index seek wanneer het nodig is, terwijl hij anders een hash-table ertussen gaat gooien per gebruik van de lookup tabel. Wat desastreus is als je die lookup op 10 plekken gebruikt.
Als je idd tegen meerdere index scans gaat aanlopen dan is al snel een join sneller.

Maar heck, daarom is het ook een lookup-tabel, daar moet je gewoon genoeg hebben aan 1 index-seek op een index.

Heb je al het query execution plan bekeken?

Dat is juist het mooie van lookup-subquery's in de select, je weet al van te voren waar die in je query execution plan komen... Het is 100% voorspelbaar gedrag.
Maar wel even gecontroleerd uiteraard want ooit gaat die 100% naar 99% en dan klapt het systeem in elkaar

[Lethalis]

@Vroem79

https://dba.stackexchange...easonable-feature-request

An inline table-valued T-SQL function is expanded like a view, so has no direct effect

Misschien een optie?

[Lethalis]

Vroem79 schreef op vrijdag 23 april 2021 @ 18:56:
[...]
Het maakt het niet lastiger, het vereist alleen een meer dan basiskennis van SQL.
En tja, complexe vragen vereisen nu eenmaal ook complexe oplossingen wil je het een beetje performant houden is mijn ervaring.

En hoe controleer jij dan dat "een case statement wat over 50 regels heengaat" correct is?