Avatar billede Peter Jørgensen Juniormester
19. juni 2018 - 10:10 Der 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å?
Avatar billede 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
Avatar billede 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.
Avatar billede Peter Jørgensen Juniormester
19. juni 2018 - 14:53 #3
@plaidDK: Prøv lige at læse mine spørgsmål igen :-)
Avatar billede 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.
Avatar billede Peter Jørgensen Juniormester
19. juni 2018 - 16:49 #5
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 :-)
Avatar billede 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.
Avatar billede Peter Jørgensen Juniormester
20. juni 2018 - 12:05 #7
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]
)
Avatar billede 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 ;)
Avatar billede Peter Jørgensen Juniormester
22. juni 2018 - 08:00 #9
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??
22. juni 2018 - 08:29 #10
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189294(v=sql.105)

https://dba.stackexchange.com/questions/22459/performance-of-inline-tvf-vs-views

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.
22. juni 2018 - 08:55 #11
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.
Avatar billede Peter Jørgensen Juniormester
22. juni 2018 - 10:29 #12
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.
22. juni 2018 - 10:56 #13
Kan du ikke bare lave en subselect?

select itemno,max(entry no) as maxentryno from  (
Select itemno,entryno from dbo.warehouse entry where entry no > 70
)x
group by itemno

Du vil jo alligevel kun have alt større end 70?
22. juni 2018 - 11:09 #14
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?
Avatar billede Peter Jørgensen Juniormester
22. juni 2018 - 12:34 #15
Hvordan vil du lave det som et view, hvor kalderen bestemmer fra hvilket løbenr der skal vises poster?
22. juni 2018 - 12:38 #16
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
Avatar billede Peter Jørgensen Juniormester
22. juni 2018 - 13:17 #17
Ja, eller en inline table-valued function, som jeg endte med at bruge.
Hvad er fordelen med din sub select, fremfor at gøre det direkte?
22. juni 2018 - 13:38 #18
Tror ikke der er nogle fordele som sådan :) Udover du har flere fordele med en SP.
Avatar billede Peter Jørgensen Juniormester
25. juni 2018 - 09:18 #19
Kan vi i det mindste ikke bare blive enige om at der er fordele og ulemper ved både stored procedures og functions? :-)
Avatar billede Ny bruger Nybegynder

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.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester