19. juni 2018 - 10:10Der er
18 kommentarer og 1 løsning
Forskel på hvad en Where kan mod en tabel og et view? (aggregate)
Jeg har brug for at få en oversigt over varenumre der har nye lagerposter efter et bestemt lagerpost løbenr. Forsøgte først dette: SELECT [Item No], MAX([Entry No]) AS MaxEntryNo FROM dbo.[Warehouse Entry] WHERE MAX([Entry No]) > 70 GROUP BY [Item No]
Men det gav denne fejl: Msg 147, Level 15, State 1, Line 4 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Så prøvede jeg at lave min WHERE clause direkte på [Entry No], og så virkede det efter hensigten.
Så oprettede jeg et View med forespørgslen (uden WHERE), og blev derfor overrasket over at jeg godt kunne gøre det her: SELECT [Item No],[MaxEntryNo] FROM ChangedWarehouseEntryItems WHERE [MaxEntryNo] > 70
Der forventes snart flere millioner WarehouseEntries, og ca. 25.000 forskellige ItemNos. Forespørgslen vil typisk blive bedt om at vise hvilke ItemNos der indgår i de sidste 100-2000 poster. Det er derfor vigtigt at SQL først og fremmest kun kikker på de sidste 100-2000 poster, og derudfra laver sin gruppering. Så vidt jeg kan se, vil en HAVING altid vurdere hele datasettet inkl. gruppering, og først efterfølgende udvælge hvilke af posterne der skal returneres. Det vil næppe performe optimalt :-)
Hvorfor kan man godt lave WHERE på MaxEntryNo via Viewet, men ikke via en simpel Select? Bliver det reelt tolket som en HAVING-clause istedet, så jeg skal forvente performance problemer den dag der ligger flere millioner Warehouse Entry poster? Bør jeg istedet lave det som en StoredProcedure, så jeg kan sikre at min where-clause sker direkte på [Entry No] og ikke på [MaxEntryNo]? Andet jeg skal være opmærksom på?
Bavarian Nordics vækst blev starten på et DevOps-samarbejde med Novataris for hurtigt at kunne tilpasse IT-organisation til forretningen.
7. december 2023
Slettet bruger
19. juni 2018 - 12:41#1
Du skal bruge having når det er på din aggr kolonne
SELECT [Item No], MAX([Entry No]) AS MaxEntryNo FROM dbo.[Warehouse Entry]
GROUP BY [Item No]
HAVING MAX([Entry No]) > 70
Synes godt om
Slettet bruger
19. juni 2018 - 12:44#2
OG ret beset kan du vel bare filter fra hvor Entry No > 70 . Hvorfor skal du bruge en max? Du kan tage max bagefte rthvis du kun ønsker 1 række, men så ville jeg nok bruge andre metoder.
@plaidDK: Prøv lige at læse mine spørgsmål igen :-)
Synes godt om
Slettet bruger
19. juni 2018 - 15:54#4
Selvfølgelig kan du lave et where filter på dit view. Dit viewer jo bare en subselect af dit data. Heri er data allerede aggrereret ogderfor kand du lave din where clause. Det samme med når du direkte aggrere så skal du have en having. Men dit SQL giver ingen mening bortset fra det.
Data er vel ikke aggregeret før jeg forespørger viewet? Har altid troet at en forespørgsel på et View altid bare blive omdannet til en forespørgsel på den/de underliggende tabeller på forespørgselstidspunktet. (medmindre det er et indexeret view)
Har lige fundet en DB med 20 mio poster, hvor jeg kunne teste det. Performance på at lave WHERE på mit view er lige så dårligt, som at bruge en HAVING direkte. At bruge WHERE på EntryNo direkte, er derimod langt hurtigere.
Det kan jeg bare ikke rigtig opnå via et view, dvs. enten skal jeg leve med den dårlige performance, eller også oprette en function til det, hvilket giver nogle andre problemer. Hov, det ser ud til at jeg kan opnå det med en inline table-valued function, uden at kalderen behøver bekymre sig om at det ikke er et view. Det må jeg arbejde videre med imorgen :-)
PS: Det er muligt mit SQL ikke giver mening, men det løser mit behov :-)
Synes godt om
Slettet bruger
19. juni 2018 - 17:32#6
Det her er din select fra dit view: SELECT [Item No],[MaxEntryNo] FROM ChangedWarehouseEntryItems WHERE [MaxEntryNo] > 70
Her har du lavet MaxEntryNO - ERgo så er data aggreret inde bagved, men det sker runtime
Her aggrrer du data og skal bruge en having SELECT [Item No], MAX([Entry No]) AS MaxEntryNo FROM dbo.[Warehouse Entry] WHERE MAX([Entry No]) > 70 GROUP BY [Item No]
Hvis du skal have performance indexerer du tabellen. Du skal slet ikke begynde at bruge functioner.
Problemet blev løst med en inline table-valued function:
CREATE FUNCTION WarehouseEntryChangelog (@LastKnownEntryNo INTEGER) RETURNS TABLE AS RETURN ( SELECT [Item No], MAX([Entry No]) AS MaxEntryNo FROM dbo.[Warehouse Entry] WHERE MAX([Entry No]) > @LastKnownEntryNo GROUP BY [Item No] )
Synes godt om
1 synes godt om dette
Slettet bruger
21. juni 2018 - 18:07#8
Du er godt klar over det er samme forspørgsel ikke? Det hjælper ikke du putter den ind i en function :D :D :D Jeg tror du mangler noget basal viden om hvordan SQL servren forespørger på dine selects. Men godt du fik det til at virke ;)
Nej, der er omkring en faktor 100 i forskel på svartiden i mine tests, så selvom resultatet er det samme, så finder SQL resultatetsættet meget forskelligt.
Tror bare vi skal være enige om at være uenige om hvem der ved lidt om SQL :-)
Eller har du (plaidDK) nu også forstået forskellen, og derfor slettet din bruger??
En Inlined table function er det samme som et view. Du vil højest sandsynligt have samme performance og samme execution plan. Dine logical reads vil formentlig også være ens. Dertil skal det nævnes du kan ikke have en WHERE MAX(EntryNo) da det er en aggregering, så den skal smides i en having.
Til brugeren der blev slettet du har delvis ret og ikke:
En TVF er det samme som et view og execution plan vil formentligt være den samme. Jeg har dog lige selv kørt et par test, og når man når som spørgsmålstiller har flere mio. rækker, så svarer den rent faktisk hurtigere:
Her er den øverste et view og den nedereste en function testet på 1mio rækker:
SQL Server Execution Times: CPU time = 2171 ms, elapsed time = 6969 ms.
(1100097 rows affected)
SQL Server Execution Times: CPU time = 1986 ms, elapsed time = 6063 ms.
Hvis man tester på fx 100.000 rækker, så er viewet hurtigere, i hvert fald i mine tests. Dog er logical reads stadig det samme, som på view og function.
Så i har begge ret på en måde. Svartiden er hurtigere på view ved mindre rækker, svartiden er hurtigere på function på mio af rækker. Mens logical reads forbliver det samme.
Jeg siger ikke at der generelt er forskel på performance for views og functions. Jeg siger derimod at der er voldsom performanceforskel på disse to simple kald i en tabel med fx 1.000.000 poster:
SELECT [Item No], MAX([Entry No]) AS MaxEntryNo FROM dbo.[Warehouse Entry] WHERE [Entry No] > 999900 GROUP BY [Item No]
SELECT [Item No], MAX([Entry No]) AS MaxEntryNo FROM dbo.[Warehouse Entry] GROUP BY [Item No] HAVING MAX([Entry No]) > 999900
I det første kald løber den kun 100 poster igennem, og sørger dernæst for at gruppere dem. I det andet kald løber den 1000000 poster igen, gruppere dem alle, men returnere kun de samme som fra første kald. Dvs. to måder at få samme resultat, men forskellige måder at gøre det på.
Det første kald kan jeg bare ikke gøre let-tilgængelig som et view, men fint som en function.
Jeg tror ikke jeg forstår behovet for din Max(entryno) > 70 ? Hvis du blot skriver where EntryNo > 70 så får du jo fitleret det samme fra. Og hvis det performer hurtigere da den tager mindre rækker, så er problemet vel løst?
Ahh ikke lige med at kalderen skulle bestemme løbenr, men du kan jo evt smide det i en stored proc og parametizer den.
create proc dbo.test @entryno int as begin select itemno,max(entry no) as maxentryno from ( Select itemno,entryno from dbo.warehouse entry where entry no > @entryno )x group by itemno End
Kan vi i det mindste ikke bare blive enige om at der er fordele og ulemper ved både stored procedures og functions? :-)
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
