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å?
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
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.
19. juni 2018 - 14:53 #3
@plaidDK: Prøv lige at læse mine spørgsmål igen :-)
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.
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 :-)
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.
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]
)
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 ;)
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.
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?
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
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.
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? :-)
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

Opret Preview

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





Premium
Coloplasts it-direktør: GDPR har givet de it-kriminelle et nyt værktøj til at angribe os
Interview: Det er ikke kun på plads og kapacitet, at sundhedssektoren lige nu er udfordret. De sikkerhedsansvarlige har også fået en del mere at se til gennem et kaotisk 2020. "Cyberkriminelle har opdaget, at der er en hel masse værdi i denne her datatype. Enten som afpresningsmulighed eller som viden, de kan udnytte til noget," siger CIO i Coloplast, René Rasmussen.


Læs første artikel i serien om perspektiver fra sektoren, der er de it-kriminelles nye guldkalv
Computerworld
Intel taber retssag og skal betale to milliarder dollar: "Det er skandaløst"
Chipgiganten Intel har tabt en retsag i USA, og er blevet idømt en bøde på hele 2,1 milliarder dollar. Det er en af de største bøder for en patentsag i USA's historie.
CIO
Har du rost din mellemleder i dag? Snart er de uddøde - og det er et tab
Computerworld mener: Mellemledere lever livet farligt: Topledelsen får konstant ideer med skiftende hold i virkeligheden, og moden går mod flade agile organisationer. Men mellemlederen er en overset hverdagens helt med et kæmpe ansvar. Her er min hyldest til den ofte latterliggjorte mellemleder.
Job & Karriere
IBM Danmark trækker tilbud om frivillige fratrædelser tilbage for stort antal ansatte: "IBM har taget røven på sine ansatte"
Som led i IBM Danmarks store fyringsrunde fik 130 ansatte grønt lys til at forlade selskabet på en frivillig fratrædelsesordning. Men nu har IBM Danmark trukket det oprindelige tilbud tilbage for størstedelen af de ansatte.
White paper
Ryk SAP-workloads i skyen – og tag alle de kendte fordele med
SAP-kunder verden over har gennem de seneste 15 år draget fordel af løsninger baseret på NetApps ONTAP-data management-platform. Herunder en mere sikker og stabil drift samt forenklet administration, hvilket er afgørende for at gennemføre projekter effektivt og for at reducere deres risikoprofil. Gennem de seneste år er det i høj grad også blevet muligt at rykke SAP-workloads i skyen, og det ønsker mange virksomheder rimeligvis at drage fordel af – men uden at sige farvel til mulighederne med NetApp ONTAP. I dette whitepaper stiller vi skarpt på mulighederne for at indarbejde NetApp Cloud Volumes ONTAP and Azure NetApp Files som del af en SAP-strategi baseret på Microsoft Azure.