SQL view: voor- en nadelen

[Verwijderd]

Ik ontwikkel een programma met een een bijbehorende SQL server database in C#. Ik wil de gebruiker een bepaalde range laten selecteren (bijvoorbeeld een afgebakende tijdsperiode). Voor deze afgebakende tijdsperiode moet mijn programma vervolgens een 10/20 tal queries uitvoeren.

Ik kan dit oplossen door voor elke query de range in de where clausule op te nemen. Ik kan het echter ook oplossen door een view te creeren voor deze range en alle queries niet de database table zelf, maar de view te laten raadplegen. Ik heb echter gelezen dat een view wordt aangemaakt in het geheugen van het programma. De hoeveelheid data in de view kan aardig oplopen, dat staat mij een beetje tegen. Zijn er andere voor- of nadelen met betrekking tot het gebruik van views?

Wat heeft jullie voorkeur in dit geval en zijn er nog andere alternatieven?

[MrBucket]

Verwijderd schreef op zondag 19 augustus 2007 @ 17:32:
Ik heb echter gelezen dat een view wordt aangemaakt in het geheugen van het programma.

Voor zover ik weet klopt dit niet. Een view is niets anders dan 'syntactic sugar', onder water zal SQL Server gewoon de query gebruiken waarmee de view is aangemaakt.

Anders wordt het als je zgn. indexed views gebruikt. Dit zijn views die zijn gemaakt met "with schemabinding" in de definitie, en waarop vervolgens een index is gelegd die alle relevante velden van je view bevat. Als je zo'n view gebruikt kan SQL server alle benodigde data uit deze index halen en hoeft het niet de onderliggende query meer uit te voeren. Zeker voor complexe queries die aggregaten berekenen (sums, counts, group by, etc.) kan dit de performance verbeteren, maar het nadeel is dan wel dat bij elke insert, update of delete op de onderliggende tabel deze index bijgewerkt moet worden. Alleen gebruiken dus als je heel veel selects gebruikt en weinig wijzigingen uitvoert op de onderliggende tabel.

Wat heeft jullie voorkeur in dit geval en zijn er nog andere alternatieven?

Als je veel range queries gaat uitvoeren op hetzelfde veld (e.g. "where mydatum between '20060101' and '20070101'", of "where startdatum <= '20061231' and einddatum >= '20061231'"), zorg er dan voor dat je clustered index dan ligt over de kolom(men) waarop de range bepaald wordt (bij bovengenoemde voorbeelden resp. mydatum en startdatum, einddatum).
Dit zorgt ervoor dat alle records fysiek op deze kolommen gesorteerd worden, zodat er niet voor elke rij een index lookup gedaan hoeft te worden; je query wordt hier dus sneller van.

Wat betreft die 10 a 20 queries: misschien kun je deze proberen te vatten in een enkele (of een aantal) stored procedures? Dit verminderd het aantal roundtrips naar de database, wat ook positief is voor je performance.

edit: lol@whoami: dan zijn we het in ieder geval eens

[whoami]

SQL Server kent ook materialized views, misschien dat je met een dergelijke view wat betere performance behaalt, maar met een 'gewone' view zal je imho niet echt performance winst halen.
(hmm, de indexed view dus waar MrBucket het al over had)

Aangezien ik ook lees: selectie op een range, doet dit bij mij het belletje clustered index rinkelen. Als die kolum (datum type) waarop je die range filtert, vrijwel nooit wijzigt, en die datum in een nieuw record altijd hoger (of lager) is dan het voorgaande record, dan is dat een goede kandidaat om een clustered index op te lggen.

edit:
fck, ik moet echt eerst eens ook de reacties lezen alvorens ik post.

[ Voor 11% gewijzigd door whoami op 19-08-2007 21:03 ]

[Verwijderd]

Als een view niet in het geheugen wordt geladen klinkt dat prima voor mijn doel. Nu ben ik de documentatie aan het nalezen, maar behalve allemaal mooie SQL queries zie ik niet hoe ik dit in C# moet implementeren.

Ik heb nu het volgende:

C#:

            SqlCommand cmdView = new SqlCommand("CREATE VIEW currentView AS SELECT * FROM Data " +
                "Where AccountID = @p_accountID AND TicketDate > @p_start AND TicketDate < @p_end", con);

            cmdView.Parameters.Add("@p_accountID", SqlDbType.Int).Value = accountID;
            cmdView.Parameters.Add("@p_start", SqlDbType.DateTime).Value = start;
            cmdView.Parameters.Add("@p_end", SqlDbType.DateTime).Value = end;
            
            con.Open();

            cmdView.ExecuteNonQuery();

            con.Close();

Bij het uitvoeren krijg ik de volgende foutmelding.

System.Data.SqlClient.SqlException was unhandled
Message="Incorrect syntax near the keyword 'VIEW'."

Moet ik een view wel aanmaken met een ExecuteNonQuery method call?

[whoami]

Waarom wil je die View via je C# programma laten maken ?

Volgens mij ben je nu 2 dingen door elkaar aan het slaan:
- het creeëren van de view
- het uitvoeren van de view.

Het select statement binnen een view kan ook geen (dynamische) parameters bevatten afaik.
Je view zou er bv zo kunnen uit zien:

code:

CREATE VIEW myView 
AS
SELECT * FROM Data
INNER JOIN melp ON Data.Id = melp.foreign_id

Dan kan je een select doen op die View:

code:

select * from myView where accountId = @Id

[Verwijderd]

Wat ik wil bereiken is een view die kan veranderen tijdens runtime. De user kan bijvoorbeeld start en eind data selecteren via de GUI waarna de view geupdate moet worden. Als ik de view wil laten updaten tijdens runtime moet dit gebeuren door middel van code.

[whoami]

Tja, dan maak je gewoon die View één keer, en laat 'm in je DB zitten. Die View is een 'object' in je DB zoals een tabel.
Je kan er dan gewoon queries op uitvoeren met parameters. Die View is dan eigenlijk niets meer dan een abstractie over je tabellen. Een soort shortcut.

[Verwijderd]

Let erop dat indexed views alleen in de dure enterprise editie werden. Zie hiervoor: [SQL SERVER 2005] Indexed view levert query op base tables

[RobIII]

whoami schreef op dinsdag 18 september 2007 @ 22:26:
Die View is dan eigenlijk niets meer dan een abstractie over je tabellen. Een soort shortcut.

Of een "virtuele" tabel; zo leg ik het althans meestal uit.

[Robtimus]

Is een stored procedure in dit geval niet misschien een goed idee?
Daarmee hard-code je alles in de database server zelf, op de datum parameters na. Deze geef je dan mee als je de SP aanroept.

[whoami]

Als je voor een SP kiest, dan zit je wel vast aan die ene bepaalde query (ook al heb je params).
Hangt er vanaf wat je precies wil...
Weet ook dat je een SP niet in een SELECT kunt zetten als column.

[Gomez12]

Verwijderd schreef op dinsdag 18 september 2007 @ 22:09:
Wat ik wil bereiken is een view die kan veranderen tijdens runtime. De user kan bijvoorbeeld start en eind data selecteren via de GUI waarna de view geupdate moet worden. Als ik de view wil laten updaten tijdens runtime moet dit gebeuren door middel van code.

Virtuele tabel vind ik wel een mooie definitie van een view.

Dus een view gebruik je voor select querys net zoals een tabel, je kan er dus gewoon een query op los laten.
Het updaten van views kan imho niet. Je update de basistabellen waardoor je virtuele tabel gelijk meeverandert... Views zijn vziw read-only.
Daarom moet je ook zo opletten met indexed views, je loopt het risico dat als je dan een basistabel aanpast dat dan de indexen van je basistabel bijgewerkt moeten worden plus de indexen van je view. Bij verkeerd gebruik kan dit een behoorlijke performance slowdown zijn.
Bij juist gebruik kan het een behoorlijke performance winst opleveren.

Imho gebruik je indexed views juist bij basistabellen die weinig veranderen ( klanten / leveranciers / bedrijfsgegevens / de basis-gegevens van een artikel ) zodat de indexen weinig bijgewerkt hoeven te worden. Voor voorraden / regels / omzetten nooit indexed views gebruiken omdat dit altijd veranderd...

[whoami]

Views zijn vziw read-only.

Niet helemaal juist.
Je kan (in SQL Server) een instead of trigger op die view maken waarin je dan de juiste statements specifieert die moeten uitgevoerd worden.

[Gomez12]

whoami schreef op woensdag 19 september 2007 @ 08:58:
[...]
Niet helemaal juist.
Je kan (in SQL Server) een instead of trigger op die view maken waarin je dan de juiste statements specifieert die moeten uitgevoerd worden.

Even ter mijner info. Dus als ik een view maak met daarin username en aantal posts, dan kan ik weer een trigger maken op username zodat ik deze wel kan veranderen en deze dan automatisch een update query uitvoert op de tabel users, dan is mijn veld posts nog wel read-only???

Hmmm lijkt me een beetje verwarrend worden, dan krijg ik gewone query's, gewone triggers, SP's en instead of triggers die alleen op views werken... En alles moet bijgehouden worden en rechten aan toegekend worden...
Lijkt me met een middelgrote dbase eerder een recipe for disaster, bij grote dbases met echte dba's zie ik het nog wel kunnen werken. Maar bij middelgrote lijkt het me al snel te ingewikkeld worden... Te veel kans dat iemand ergens iets in verwerkt wat ergens anders een leuke security leak genereert.

[whoami]

Een instead of trigger werkt niet enkel op een view.
Een instead of trigger kan je ook op een tabel definieren. Als je een INSERT doet op een 'object' (view of tabel) met een INSTEAD OF trigger, dan gaat de instead of trigger je insert statement afhandelen.

Voer dit bv eens uit, en kijk dan eens wat er in die tabel melp zit:

code:

CREATE TABLE melp
(
   [id]     integer,  
   naam     varchar(50)
)

CREATE TRIGGER melp_i ON melp INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO melp
    ([id], naam)
    SELECT [id], 'from trigger' FROM inserted
END

INSERT INTO melp
([id], naam)
values
(1, 'whoami')

[Verwijderd]

Gomez12 schreef op woensdag 19 september 2007 @ 01:17:
[...]

Het updaten van views kan imho niet. Je update de basistabellen waardoor je virtuele tabel gelijk meeverandert... Views zijn vziw read-only.

Dat is wel mijn bedoeling, de SQL van de view moet tijdens runtime kunnen veranderen. Ik heb inmiddels een view 'object' via de database explorer gecreeerd. Dit werkt ok, maar nu nog de mogelijkheid om de sql van de view tijdens runtime te veranderen.

Volgens MSDN moet ik hiervoor ALTER VIEW gebruiken. Ik zit echter met hetzelfde probleem als hierboven, de execute non query geeft een exception op de sql. {"Incorrect syntax near the keyword 'VIEW'."}. Mijn code is hetzelfde als voorheen al eens gepost, maar dan ALTER ipv CREATE in de SQL line. Heeft iemand een idee waar dit aan kan liggen?

[Verwijderd]

Waarom zou je runtime de metadata van een view willen veranderen?

[Gomez12]

Verwijderd schreef op vrijdag 21 september 2007 @ 20:54:
[...]


Dat is wel mijn bedoeling, de SQL van de view moet tijdens runtime kunnen veranderen. Ik heb inmiddels een view 'object' via de database explorer gecreeerd. Dit werkt ok, maar nu nog de mogelijkheid om de sql van de view tijdens runtime te veranderen.

Volgens MSDN moet ik hiervoor ALTER VIEW gebruiken. Ik zit echter met hetzelfde probleem als hierboven, de execute non query geeft een exception op de sql. {"Incorrect syntax near the keyword 'VIEW'."}. Mijn code is hetzelfde als voorheen al eens gepost, maar dan ALTER ipv CREATE in de SQL line. Heeft iemand een idee waar dit aan kan liggen?

En toch houdt ik het idee dat jij niet helemaal begrijpt wat een view is.
Want op een view kan je dezelfde select-querys loslaten als op een tabel. Dus je kan ook op een select op je view gewoon een where-component meegeven.

Maar wat is je reden dat je een view at runtime wilt kunnen veranderen??? Ipv dat je gewoon 2/3/4 views aanmaakt?

Want ik denk dat als jij te vaak een view gaat veranderen at runtime dat dan je dbase het opeens een stuk zwaarder gaat krijgen door allerlei indexen die aangemaakt moeten worden etc.

[RobIII]

Verwijderd schreef op vrijdag 21 september 2007 @ 22:05:
Waarom zou je runtime de metadata van een view willen veranderen?

Inderdaard. Dat je "eens" ("een keer in de zoveel tijd") eens een alter-view gebruikt, allee. Al is het maar bij een update van je software (om maar wat te noemen). Om dit op 'regelmatige basis' te gaan doen is andere koek.

Ik heb daarom ook het idee dat JMfx compleet in de verkeerde richting aan 't zoeken is. Ik vermoed dat een dynamische query zoals zoveel OR-mappers die genereren beter van toepassing is op het probleem, en anders wel stored procedures (qua parameters etc.).
Mijn vermoeden is echter dat de view door HMfx hier anders opgevat wordt; je kunt immers prima een where-clause op een view los laten en daarmee je resultset 'verfijnen' (en eigenlijk is een view ook niet veel anders dan een tussenliggende laag waarin je al een aantal joins en andere stunts uithaalt om de daaropvolgende queries te versimpelen omdat je een 'virtuele tabel' queried).
Is dat niet het probleem (en voel ik het dus verkeerd aan) dan vermoed ik dat JMfx bepaalde kolommen e.d. tijdens runtime niet wil weergeven omdat die teveel in de view zouden zitten en als je je resultset regelrecht aan een DBGrid hangt (bijv) dan zie je meer dan je aan de gebruiker wil weergeven. In dat geval is het het makkelijkst om gewoon die kolommen te verbergen in het datasourcechanged event ofzo.

Hoe dan ook denk ik dat JMfx het in de verkeerde richting zoekt en het lijkt me handig als hij dan ook even verduidelijkt wat nu precies het probleem is en wat hij wil bereiken.

Gomez12 schreef op vrijdag 21 september 2007 @ 22:13:
[...]
Want ik denk dat als jij te vaak een view gaat veranderen at runtime dat dan je dbase het opeens een stuk zwaarder gaat krijgen door allerlei indexen die aangemaakt moeten worden etc.

Het is wat kort door de bocht, maar doorgaans hebben views geen eigen indexes

[ Voor 17% gewijzigd door RobIII op 21-09-2007 23:36 ]

[Verwijderd]

Het is inderdaad een beetje onduidelijk merk ik. Ik zal het proberen te verduidelijken.

Mijn programma bevat code om statistieken te generen. Om deze statistieken te genereren gebruik ik ongeveer 10 SQL queries die data uit de database halen. Met de gegevens van deze queries kan ik alle statistieken door middel van code generen.

Elk van deze queries is uniek, ze halen allemaal verschillende gegevens op. Er is echter één gemeenschappelijk deel voor alle queries, het limiteren van de query voor een bepaald accountID en een bepaalde tijdsrange. In andere woorden, mijn programma kan statistieken generen voor data in de database, maar ik wil de gebruiker de mogelijkheid geven over welk deel van de data in de database de statistieken gegenereerd moeten worden. In nog andere woorden bevat elke query dus een uniek deel dat nodig is om bepaalde gegevens voor het genereren van statistieken te selecteren en een generiek dynamisch deel dat nodig is de gebruiker de mogelijkheid te geven het account ID en de tijdsrange te selecteren (limiteren).

Zover ik kan zien zijn er verschillende oplossingen. Eén oplossing zou zijn om aan elke query de where clause uit te breiden als volgt:
AND accountID = @p_accountID AND TicketDate > @p_start AND TicketDate < @p_end. Vervolgens kan ik voor elke query parameter code toevoegen die voor elke parameter verwijst naar een van de drie globale variabelen. Deze drie variabelen (accountID, start en einde) kan de gebruiker dan via de gui toekennen. Elke keer als de gebruiker dan aangeeft dat de statistieken opnieuw gegenereerd moeten worden, worden de globale variabelen gebruikt die die de gebruiker kan modificeren.

Een andere oplossing zou het gebruik van een view zijn. In dat geval verander ik de tien queries zodat ze niet uit de database tabel maar uit de view selecteren. Het dynamische gedeelte (accountID, start en einde) kan ik dan vatten in de view. In dat geval moet ik de gebruiker de mogelijkheid geven om de view query aan te passen.

De view oplossing leek mij het beste aangezien je het gemeenschappelijke aspect van alle queries niet dubbel hoeft uit te voeren, maar in één centrale plaats kan vatten (de view). Wat betreft stored procedures, dit is voor mij nogal ver van mijn bed. Ik weet dat een aantal van jullie professionele programmeurs zijn, maar voor mij is dit een hobby project. Een simpele oplossing heeft voor mij meerwaarde. Stored procedures komen op mij over als bedrijfstoepassingen waarbij een query vele keren moet worden uitgevoerd. Misschien moet ik er ook bij vermelden dat het om een locale SQL server database gaat, op elk moment is er maar één gebruiker bezig met de database. De queries worden ook niet zo vaak uitgevoerd, enkel bij het starten van het programma en in het geval dat de gebruiker er voor kiest een andere account en/of tijdsrange te selecteren. Als jullie een goede suggestie hebben hoor ik het graag.

[whoami]

Maak een stored procedure die de nodige parameters neemt (accountid en tijdsrange) en die de hele zut voor je doet (uitvoeren van de nodige queries etc..).
Ik heb zo het gevoel dat je je laat afschrikken door 'stored procedures'. Dat zijn echter helemaal geen ingewikkelde of 'heavy' dingen ofzo; kort gezegd is het gewoon een routine / functie die je in je DB bewaard. En een stored procedure is vooral handig als er een hele rits bewerkingen moeten gebeuren op data - niveau, en er een hoop data moet verwerkt worden (bv statistieken).
Tuurlijk kan je deze functie ook in C# ofzo implementeren, echter, door het in een SP te steken bespaar je een paar trips naar de server.

Een view is hier niet de juiste oplossing als ik het zo lees.

[Verwijderd]

Een view is hier idd niet de juiste oplossing.
Een stored proc is hier een stuk handiger, maar dat moet dan ook wel in je ontwerp passen: wil je business intelligence in je database of niet?

[Niemand_Anders]

whoami schreef op woensdag 19 september 2007 @ 08:58:
[...]
Niet helemaal juist.
Je kan (in SQL Server) een instead of trigger op die view maken waarin je dan de juiste statements specifieert die moeten uitgevoerd worden.

In MSSQL2008 kun je een view op dezelfde wijze benaderen als een tabel. Een voorwaarde is dat de view is geschreven zonder wildcards of ambigious veldnamen. Dus geen select * from, etc.

Als je je daar aan houd, dan kun je de volgende query gebruiken:

SQL:

update vwUser set email='[e-mail]' where ID=12

Er zit wel een addertje onder het gras. SQL2008 is pas bij BETA 2 en naar verwachting zal de final in februari samen met Visual Studio en Windows 2008 gereleased worden. Wij hebben inmiddels al een productie website welke gebruik maakt van SQL2008. Wij hadden namelijk xpath indexes (index op XML column) nodig samen met computed xml columns.

Wel wil ik je erop wijzen dat het niet verstandig is om update queries op views te schrijven. Een cascade update kan dezelfde resultaten opleveren plus dat het ook al in SQL2000 en SQL2005 werkt.

[whoami]

Niemand_Anders schreef op maandag 24 september 2007 @ 09:12:
[...]


In MSSQL2008 kun je een view op dezelfde wijze benaderen als een tabel. Een voorwaarde is dat de view is geschreven zonder wildcards of ambigious veldnamen. Dus geen select * from, etc.

plus dat het ook al in SQL2000 en SQL2005 werkt.

Idd, dit werkt ook al op SQL 2000 & 2005; echter, vziw mag je view geen gegevens uit meerdere tabellen halen; indien dit wel het geval is, moet je met instead of triggers a/d slag

[Verwijderd]

Ik heb nu alles uitgevoerd volgens de eerste methode die hierboven staat, dat werkt op zich prima. Maar het vatten van meerdere SQL statements in één stored procedure en niet meer SQL in C# schrijven klinkt wel ok, dus ik ben me wat gaan verdiepen in Stored Procedures.

code:

ALTER PROCEDURE dbo.Chart
    (
    @accountID int,
    @start DateTime,
    @end DateTime
    )
AS
    /*First Query */
    SELECT TicketDate, Balance 
    FROM Data
    WHERE (Ticket IN (bla + parameters enz))
   
    /*Second Query*/
    SELECT TicketDate, Balance 
    FROM Data
    WHERE (Ticket IN (bla + parameters enz))

    /*Third Query*/         
    SELECT TicketDate, Balance 
    FROM Data
    WHERE (Ticket IN (bla + parameters enz))

    RETURN

In C#

C#:

            SqlCommand commandChart = new SqlCommand("Chart", con);
            commandChart.CommandType = CommandType.StoredProcedure;

            commandChart.Parameters.Add("@accountID", SqlDbType.Int).Value = accountID;
            commandChart.Parameters.Add("@start", SqlDbType.DateTime).Value = startRange;
            commandChart.Parameters.Add("@end", SqlDbType.DateTime).Value = endRange;

            SqlDataReader readerChart = commandChart.ExecuteReader();

            while (readerChart.Read())
            {
                   Dingen specifiek voor de eerste query..
            }

            while (readerChart.Read())
            {
                   Dingen specifiek voor de tweede query..
            }

            while (readerChart.Read())
            {
                   Dingen specifiek voor de derde query..
            }

Nou gaat het opzicht redelijk goed, maar de eerste while loop eet alle query data op. Hoe kan ik dit voorkomen? Ik heb nog geprobeerd een RETURN na elke query te zetten, maar in dat geval wordt enkel de eerste query uitgevoerd.

[MrBucket]

Je hebt waarschijnlijk SqlDataReader.NextResult() nodig.

Zie ook http://msdn2.microsoft.co...eader_members(VS.71).aspx

[Verwijderd]

Bedankt, dat werkt prima. Had ik natuurlijk zelf moeten kunnen vinden

[RobIII]

Ik zie het voordeel niet om 3 result-sets uit een SP te returnen; waarom zou je er niet 3 SP's van maken? En, als ze zoveel op elkaar lijken, waarom niet 1 waarbij je een extra parameter meegeeft?

[whoami]

^^^ Met RobIII

Ik zie hier ook niet het voordeel om alles in één SP te gooien, temeer omdat je niets doet met die 3 resultsets samen.
Wat doe je trouwens in die 3 lussen in je c# applicatie ? Ga je daar enkel de gegevens gaan tonen, of doe je er ook iets anders mee ?

[Verwijderd]

De drie queries zijn datum en balans reeksen die worden toegevoegd aan (3x2) verschillende arraylists (welke later worden gebruikt als input voor een chart control). Elke loop doet dit voor respectievelijk dag, week en maand. Daarnaast bevat elke loop wat code om de datetime aan te passen naar het begin van die periode (begin van de dag, begin van de week en begin van de maand).

De queries staan hieronder beschreven, ik denk niet dat je dit zo makkelijk in een parameter kan vatten. De reden dat ik de drie queries gegroepeerd heb is dat ze allemaal op de grafiek van toepassing zijn. Wanneer zou ik volgens jullie meerdere queries in één stored procedure moeten vatten, als ik de resultaten van de queries ook kan verwerken in één en dezelfde while loop?

[SQL Server] select query help

[Niemand_Anders]

Eigenlijk is het antwoord nooit. Wil je via 1 SP toch drie resultaten terug krijgen dan zou je eventueel zelfs een vierde SP kunnen schrijven welke de drie SP's achter elkaar aanroept.

Daarnaast zou het in de toekomst eenvoudiger kunnen zijn om 'data sources' toe te voegen of juist weg te laten bij een resultaat (mergen van charts). Daarnaast als het erg lastig is om de verschillen in een SP te plaatsen, dan moet je hiervoor misschien geen stored procedures gebruiken. Gebruikt de juiste (eenvoudigste) tool voor de job.

Een enkele (simpele) query schrijven in C# is geen probleem, maar op het moment dat die query meer op een script gaat lijken (gebruik van cursors, case, if (exist)) dan kun je al bijna niet om een stored procedure heen.