[MSSQL] View Dynamisch zoekvoorwaarden meegeven

[Plopeye]

Helaas nMe heeft de vorige wat snel gesloten zodat antwoorden cq aanpassen topicstart niet meer mogelijk was... hij vond de vraag wat basic.

Ik zal dan ook bij deze mijn best doen om mischien iets duidelijker aan te geven wat het probleem is... want het probleem is verre van basic volgens mij... (foutje in de aanmaak van een view resulteerde in 89 miljoen regels die betekenen dat er een virtuele tabel van 1,5 gb in je werkgeheugen van de sql server gepompt worden, wat voor mij een vastloper van de machine betekende...)

Deze grote hoevelheid is ook te wijten aan de postcode tabellen waar we het over hebben an ja je leest het goed er zijn er 2... we hebben het over transport en het is dus de bedoeling dat elke postcode in europa kunnen bedienen vanuit elke postcode in europa...
om de tabel met prijzen kleiner te maken zijn de postcode's in zone's ingedeeld... maar omdat we met meerdere expediteurs werken is deze zone indeling expediteur afhankelijk...
resultaat:

[view1] combineert "laadpostcode, laadzone, vervoerder" met "lospostcode, loszone, vervoerder"
de gelijkstelling zit hem in de vervoerder(expediteur)
bij het omzetten van postcode naar zone+vervoerder krijg je dus meerdere laadlos codes en meerdere expediteurs...
[/view1]

[view2]
nu wil ik op basis van de lijst uit view1 op basis van de laadlos codes + vervoerder en de te vervoeren hoeveelheid voorzien van de daarbijbehoorende prijzen nu is het zo dat de te vervoeren hoeveelheid een gebruikersinvoer is die dus niet elders uit een tabel of view te halen is...

wat ik wil bereiken:
zoals joopst aangaf:

select from view where col1 = 'waarde' ? in een view en waar waarde word gevoed met een @waarde uit de ASP.NET applicatie

met andere woorden query op query... (is dit basic????)
heb google helemaal het hemd van het lijf gevraagd maar vind niet wat ik zoek...

mijn vraag is dus: kan dit? en zo ja hoe ?

[joopst]

een view is volgens mij een stukje query die achter je andere query wordt geplakt en neemt 'standaard' geen ruimte in op de server volgens mij (als in: hij kopieert niet alle gegevens nog eens ff extra). Dus het eerste probleem wat jij schetst lijkt me een andere oorzaak te hebben.

stel dat wat ik zeg waar is, dan is je vraag eigenlijk al beantwoord. Je kan dus een query doen (bijv. een view) en daar weer een query op doen.

je kan het ook met de hand doen:

code:

SELECT mijnTabel.naam FROM ( SELECT bedrijfsnaam AS naam FROM bedrijven WHERE id=1 ) mijnTabel

p.s.
ik vermoed dat een query die jij nodig hebt ook gemaakt kan worden met een paar joins in 1 query.

[P_de_B]

Geef eens een overzicht van je tabelstructuur en de gewenste output, ik heb het idee dat je nodeloos in gewikkeld aan het doen bent.

[Plopeye]

P_de_B schreef op vrijdag 09 december 2005 @ 13:52:
Geef eens een overzicht van je tabelstructuur en de gewenste output, ik heb het idee dat je nodeloos in gewikkeld aan het doen bent.

structuur van de tabellen:

tbl_laadcode:
kolommen:

laadcode_id
laadpostcode
laadcode
vervoerder

tbl_loscode:
kolommen:

loscode_id
lospostcode
loscode
vervoerder

tbl_prijzen:
kolommen:

prijs_id
laadmeter
laadcode
loscode
prijs
vervoerder

zoals eerder vermeld kunnen laadcode en loscode niet los van de vervoerder gezien worden...

de gebruiker geeft hetzij indirect op:

laadpostcode
lospostcode
die samen met de iso landcode een uniek waarde opleveren.
en de laadmeters worden berekend uit de gebruikersinvoer en zijn dus als string ook bekend...

gewenste output: de goedkoopste prijs en welke vervoerder dit is...

[ Voor 30% gewijzigd door Plopeye op 09-12-2005 14:14 ]

[P_de_B]

En wat is nu precies je gewenste output? Dat maak ik niet duidelijk genoeg op uit je topicstart.

[Plopeye]

P_de_B schreef op vrijdag 09 december 2005 @ 14:13:
En wat is nu precies je gewenste output? Dat maak ik niet duidelijk genoeg op uit je topicstart.

En schaam je niet als je hier niet 123 een antwoord op weet want wij bijten ons hier al meer dan een week op stuk...

[P_de_B]

Het ligt waarschijnlijk aan mij, maar ik snap het nog niet helemaal. In de tabel laadcodes, wat staat daar in? Alle mogelijke postcodes waar een expediteur kan laden? En in prijzen? Wat het vervoer van de laadpostcode naar de lospostcode kost?

Kun je eens een paar records voorbeeld per tabel geven, met daarbij wat mensen invoeren en wat eruit moet komen?

[joopst]

en als je je database zo inricht:


dan kan je
- per vervoerder
- per laad-los-combinatie
- per laadcode
je 'prijs per eenheid' bijhouden en opvragen..

alle vervoerders zitten dan 1x in de db
alle postcodes zitten dan 1x in de db (in jouw ontwerp 2x)
en alle laadcodes zitten in de db

je performance-truuk met postcode-zones heb ik niet erin gemaakt omdat dat volgens mij (voor de performance iig) niet hoeft

[Plopeye]

in de tabel laadcode staat het volgende:

code:

tbl_laadcode:

laadcode_id     laadpostcode      laadcode        vervoerder
idtje voor row     NL96                 NL_1              expediteur a
"            "         NL97                 NL_1              expediteur a

tbl_loscode:

loscode_id     lospostcode         loscode             vervoerder
idtje voor row     DE47                 DE_8             expediteur a
"            "         DE48                DE_6              expediteur a

tbl_prijzen:

prijs_id   laadmeters      laadcode       loscode    prijs    vervoerder
idtje              0,1            NL_1              DE_8     100     expediteur a

hoe vind ik prijs:

kijk in postcodetabel in welke loscode de postcode valt en welke vervoerder daarbij hoort
dit kunnen meerdere vervoerders zijn en dus ook meerdere laad cq loscodes zijn

kijk voor al deze vervoerders in combinatie met gevonden laad/los code en ingevoerde hoeveelheid kost en selecteer de goedkoopste.
geef mij daarvan de prijs en de vervoerder

[ Voor 9% gewijzigd door Plopeye op 09-12-2005 15:37 ]

[joopst]

ik zie net dat je ook nog losCodes hebt ..

is er per postcode 1 laadcode en 1 loscode ?
dan zouden die nog in de postcodetabel erbij kunnen .. ipv in een eigense tabel

[P_de_B]

SQL:

SELECT TOP 1 prijs.vervoerder, prijs.prijs 
FROM tbl_prijzen prijs
INNER JOIN tbl_loscode los on prijs.loscode = los.loscode
INNER JOIN tbl_laadcode laad on prijs.laadcode = laad.laadcode
WHERE los.lospostcode = 'xxx' AND laad.laadpostcode = 'xxx'
ORDER BY prijs

Volgens mij krijg je met bovenstaande query de juiste gegevens.

[joopst]

iets als:

SQL:

select top 1 prijs, tbl_prijzen.vervoerder from tbl_prijzen 
join tbl_loscode 
on tbl_prijzen.vervoerder = tbl_loscode.vervoerder
and lostpostcode = 'abba'
join tbl_laadcode
on tbl_prijzen.vervoerder = tbl_laadcode.vervoerder
and laadpostcode = 'bccb'
order by prijs asc

[ Voor 6% gewijzigd door joopst op 09-12-2005 14:46 ]

[joopst]

wrom krijg jij mooie kleurtjes in jouw code ?

[Plopeye]

joopst schreef op vrijdag 09 december 2005 @ 14:35:
ik zie net dat je ook nog losCodes hebt ..

is er per postcode 1 laadcode en 1 loscode ?
dan zouden die nog in de postcodetabel erbij kunnen .. ipv in een eigense tabel

laad/los code(ook wel zone te noemen) is meerdere postcode gebieden

bijvoorbeeld:

in duitsland alle postcode gebieden die beginnen met 01, 02 ~ 09 is zone DE_1

ga jij maar eens uitrekenen hoeveel rijen je krijgt als je alle postcode gebieden op basis van de eerste 2 cijfers of letters van europa en alle mogelijke combinaties hiervan.
dan nog iets een trailer bestaat uit 13,6 laadmeter en de prijs is per 0,1 laadmeter dus 136 mogelijkheden.

hoeveel prijzen worden dat ?

wij kwamen voor alleen duitsland al uit op: 1.332.936 waardes lees prijzen

[P_de_B]

joopst schreef op vrijdag 09 december 2005 @ 14:45:
wrom krijg jij mooie kleurtjes in jouw code ?

kleurtjes?

je moet [code=sql] gebruiken

[joopst]

hoeveel prijzen worden dat ?

wij kwamen voor alleen duitsland al uit op: 1.332.936 waardes lees prijzen

op zich is dat geen probleem omdat je id's geindexeerd en klein zijn, maar als ze allemaal hetzelfde zijn .. dan kan je het best doen :-)

[Plopeye]

P_de_B schreef op vrijdag 09 december 2005 @ 14:42:

SQL:

SELECT TOP 1 prijs.vervoerder, prijs.prijs 
FROM tbl_prijzen prijs
INNER JOIN tbl_loscode los on prijs.loscode = los.loscode
INNER JOIN tbl_laadcode laad on prijs.laadcode = laad.laadcode
WHERE los.lospostcode = 'xxx' AND laad.laadpostcode = 'xxx'
ORDER BY prijs

Volgens mij krijg je met bovenstaande query de juiste gegevens.

ik heb even gekeken maar helaas ik zag dat je de laad/los code los van de vervoerder opvraagt dit kan niet want alleen de laad/los code is niet uniek.

daarbij ga je er vanuit dat er maar 1 vervoerder is en kan je geen prijs vergelijken...
enig nuancering: ik zie dat je de goedkoopste bovenaan zet en selecteerd...
daarbij mis ik ook de hoeveelheid.

maar zo ben ik ook begonnen en al een week aan het klooien...

[ Voor 28% gewijzigd door Plopeye op 09-12-2005 15:00 ]

[joopst]

ik heb even gekeken maar helaas ik zag dat je de laad/los code los van de vervoerder opvraagt dit kan niet want alleen de laad/los code is niet uniek.

In mijn query is ie gejoined op vervoerder. ..
en die van p_de_b op code ..

waarschijnlijk hebben we allebei een deel van de puzzel

[ Voor 18% gewijzigd door joopst op 09-12-2005 15:08 ]

[P_de_B]

Plopeye schreef op vrijdag 09 december 2005 @ 14:55:
[...]


ik heb even gekeken maar helaas ik zag dat je de laad/los code los van de vervoerder opvraagt dit kan niet want alleen de laad/los code is niet uniek.

daarbij ga je er vanuit dat er maar 1 vervoerder is en kan je geen prijs vergelijken...
enig nuancering: ik zie dat je de goedkoopste bovenaan zet en selecteerd...
daarbij mis ik ook de hoeveelheid.

maar zo ben ik ook begonnen en al een week aan het klooien...

dan kun je dus achter de join nog de vervoerder opnemen.

SQL:

SELECT TOP 1 prijs.vervoerder, prijs.prijs 
FROM tbl_prijzen prijs
INNER JOIN tbl_loscode los on prijs.loscode = los.loscode and prijs.vervoerder = los.vervoerder
INNER JOIN tbl_laadcode laad on prijs.laadcode = laad.laadcode and prijs.vervoerder  = laad.vervoerder
WHERE los.lospostcode = 'xxx' AND laad.laadpostcode = 'xxx'
ORDER BY prijs

dan heb je het nog over hoeveelheid? Ik zie nergens iets over hoeveelheid

[Plopeye]

P_de_B schreef op vrijdag 09 december 2005 @ 15:09:
[...]


dan kun je dus achter de join nog de vervoerder opnemen.

SQL:

SELECT TOP 1 prijs.vervoerder, prijs.prijs 
FROM tbl_prijzen prijs
INNER JOIN tbl_loscode los on prijs.loscode = los.loscode and prijs.vervoerder = los.vervoerder
INNER JOIN tbl_laadcode laad on prijs.laadcode = laad.laadcode and prijs.vervoerder  = laad.vervoerder
WHERE los.lospostcode = 'xxx' AND laad.laadpostcode = 'xxx'
ORDER BY prijs

dan heb je het nog over hoeveelheid? Ik zie nergens iets over hoeveelheid

laadmeters sorry heb em aangepast en de toevoeging AND tbl.prijzen.laadmeters = 'xxx' zou het dan moeten doen volgens mij...

uitkomst van de test:

Query OK, 0 rows affected (0,33 sec)

Return Code: 0

[ Voor 26% gewijzigd door Plopeye op 09-12-2005 15:55 ]

[P_de_B]

Nou, dan moet je gaan zien waar het fout gaat. Wat krijg je als je alle WHERE statements weglaat? Krijg je dan wel een result?

[Plopeye]

P_de_B schreef op vrijdag 09 december 2005 @ 16:40:
Nou, dan moet je gaan zien waar het fout gaat. Wat krijg je als je alle WHERE statements weglaat? Krijg je dan wel een result?

ook als ik de where clausule weglaat krijg ik niets...

ik heb hem iets verbouwd dat hij een view aanspreekt die de laadpostcode laadcode lospostcode en loscode juist joined aan de hand van vervoerder...

ziet er nu zo uit:

SQL:

SELECT TOP 1
  prijs.vervoerder,
  prijs.prijs
FROM
  tbl_prijzen prijs
  INNER JOIN vvlaadlospctolc ON (prijs.laadcode = vvlaadlospctolc.laadcode)
  AND (prijs.loscode = vvlaadlospctolc.loscode)
  AND (prijs.laadcode = vvlaadlospctolc.laadcode)
  AND (prijs.vervoerder = vvlaadlospctolc.vervoerder)
  WHERE vvlaadlospctolc.lospostcode = @lospostcode
  AND vvlaadlospctolc.laadpostcode =@laadpostcode
  AND prijs.laadmeter =@laadmeters

ORDER BY
  prijs

hmm na een executiontime van 5 min op een p4 1,7 heb ik hem maar afgebroken heb niet het idee dat hier iets uitkomt

[ Voor 6% gewijzigd door Plopeye op 09-12-2005 17:03 ]

[Robbemans]

Je kunt 5 minuten lang vinden, maar als er geen goede indexen op staan, dan valt het nog wel mee.
Je zou de volgende stappen kunnen ondernemen om tot een resultaats te komen:

- Maak de juiste indexen aan. Indexen kun je ook op een view aanmaken. Probeer (indien je ruimte genoeg hebt) een covering index aan te maken, zodat je alleen index seeks hebt.
- Zorg voor een snapshot van de data. Je hebt nu de macht van de grote getallen: traag en log. Met testdata kun je er voor zorgen dat je in ieder geval op een goede manier de juiste data boven tafel krijgt.

Mocht dit allemaal niet mogelijk zijn, dan zit er niets anders op dan wachten. Laat hem een weekend stampen en wie weet heb je maandag resultaat. Zet ook even je Query Execution Plan aan, zodat je weet waar de tijd in gaat zitten.

Ter illustratie:

We hebben hier een exotische query gehad op 2 tabellen met respectievelijk 1,5 en 8 miljoen records. Met verschillende joins, e.d. duurde de query in 1e instatie 7 uur op een 4-proc XEON systeem. Nu duurt dezelfde query - na optimalisatie - nog maar 2 tot 5 seconden.

[ Voor 16% gewijzigd door Robbemans op 09-12-2005 19:37 ]

[P_de_B]

Ik zie zo niet waarom je die extra view nodig hebt. Met de voorbeeld data die je hebt gegeven moet je volgens mij wel resultaten krijgen met mijn laatste voorbeeld. Probeer anders een LEFT OUTER JOIN te gebruiken om te zien waar het mis gaat.

Verder heeft Robbemans natuurlijk gelijk, gebruik Query analyzer om te zien waar je query zo lang over doet. Heb je alle indexen goed gezet?

[Plopeye]

De oplossing is gevonden:

ik heb EMS SQL Query 2005 30 dagen trail gedownload en die bood de volgende werkende oplossing:

SQL:

CREATE PROCEDURE dbo.RequestPrijs 

@laadpostcode varchar(10),
@lospostcode varchar(10),
@laadmeters varchar(4)



AS

SELECT top 1
  dbo.[tbl_prijzen].laadmeter,
  dbo.[tbl_prijzen].prijs,
  dbo.[tbl_prijzen].vervoerder
FROM
  dbo.[vvlaadlospctolc]
  INNER JOIN dbo.[tbl_prijzen] ON (dbo.[vvlaadlospctolc].vervoerder = dbo.[tbl_prijzen].vervoerder)
  AND (dbo.[vvlaadlospctolc].loscode = dbo.[tbl_prijzen].loscode)
  AND (dbo.[vvlaadlospctolc].laadcode = dbo.[tbl_prijzen].laadcode)
WHERE
  dbo.[vvlaadlospctolc].laadpostcode = @laadpostcode AND
  dbo.[vvlaadlospctolc].lospostcode = @lospostcode AND
  dbo.[tbl_prijzen].laadmeter = @laadmeters
ORDER BY
  dbo.tbl_prijzen.prijs

Nu alleen nog nieuwe postcodes inlezen want we werken nu nog met 4 cijferig postcode wat oplevert dat je per land 10.000 postcodes hebt dus vertrekland (10.000)x aankomstland(10.000) = 100.000.000 mogelijkheden per gevonden vervoerder
verschillende mogelijkheden als je deze postcodes gaat koppellen...
duurde een beetje lang voor dat ie een prijs had (9,5 minuten voor 1 prijs)

we gaan nu naar 2 cijferige postcodes zodat je vertrekland (100)x aankomstland(100) = 10.000 mogelijkheden per gevonden vervoerder
ik denk dat ie dan aardig sneller kan...

nog 1 vraag: geeft de ORDER BY nog een probleem als er maar 1 vervoerder gevonden word ?

[ Voor 4% gewijzigd door Plopeye op 09-12-2005 20:14 ]

[P_de_B]

Nee, die order by geeft geen probleem als er 1 vervoerder gevonden wordt.

Ik snap trouwens niet waarom SQL2k5 ineens een werkende oplossing bood. Die query moet het ook nog wel in versie 5 doen.