[SQL Server 2000] Create View binnen Stored Procedure

[Verwijderd]

Ik wil een view genereren waarin ik met "union all" een vooraf ongekend aantal tabellen met periodieke resultaten fusioneer. Ik heb een stored procedure die voor elke tabel een lijn aan deze view toevoegt. Als ik de samengestelde query uitvoer in query analyzer werkt het perfect.

Als ik hem uitvoer via exec(@sql) dan gaat het mis:
fout:
Server: Msg 203, Level 16, State 2, Procedure pr_wh_GenerateView_1, Line 53
The name 'CREATE VIEW [dbo].[vw_Summary] AS
SELECT blaat ...

Nu lees ik zopas in de help van sql server:
"The CREATE PROCEDURE definition itself can include any number and type of SQL statements except for the following CREATE statements, which cannot be used anywhere within a stored procedure: CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE "

Vraag 1: Is dit het probleem?
Vraag 2: Kan hier omheen gefietst worden en zo ja hoe?

Tnx,

[P_de_B]

Antwoord 1: Ja
Antwoord 2: Kun je iets meer info geven over je tabellen en wat je wilt bereiken?

[whoami]

Eh, de help zegt het toch zelf al dat dit idd het probleem is ?
en afaik kan er niet omheen gefietst worden nee. IMHO is het ook onzinnig om mbhv een SP een view te gaan maken.

[Jaspertje]

Waarom zou je idd elke keer als je een SP uitvoert een View willen maken?

[curry684]

Ik snap de vraag ook niet helemaal: " Ik heb een stored procedure die voor elke tabel een lijn aan deze view toevoegt."

Hebben ze voor regels 'toevoegen' nou niet net het concept 'tables' uitgevonden?

[Verwijderd]

Even verduidelijken:

Ik heb per periode een resultatentabel.
Ik wil deze periodes samenvoegen in 1 view.
Iedere periode komt er dus een tabel bij en ik wil niet iedere keer manueel mijn view gaan aanpassen zodanig dat de resultaten van de laatste periode erbij komen te staan.

Daarom dacht ik aan een stored procedure die de view elke keer dropt en regenereert met alle periodes erin.

Die sp zoekt dus alle resultatentabellen op en voegt per tabel een select statement toe aan de view met union all.
Dus krijg je zoiets als

Create view as MijnView
Select * from periode1 union all
Select * from periode2 union all
....

[curry684]

Verwijderd schreef op dinsdag 06 september 2005 @ 10:44:
Even verduidelijken:

Ik heb per periode een resultatentabel.

Daar gaat het al fout

Al eens nagedacht over waarom je daarmee ieder beginsel van normaliseren overtreedt?

[Verwijderd]

curry684 schreef op dinsdag 06 september 2005 @ 10:45:
[...]

Daar gaat het al fout

Al eens nagedacht over waarom je daarmee ieder beginsel van normaliseren overtreedt?

Om performantie redenen. Als je miljoenen records toevoegt aan een tabel waar er miljoenen records reeds instaan, met een hele hoop indexen daarop, dan wacht je ofwel uren omdat hij die indexen aan het updaten is ofwel omdat hij die indexen dropt en heropbouwt. Maar dat doet er nu eigenlijk niet toe.

Kan je een view op een of andere manier dynamisch samenstellen met een stored procedure... of iets anders?

[whoami]

Verwijderd schreef op dinsdag 06 september 2005 @ 11:10:
[...]


Om performantie redenen. Als je miljoenen records toevoegt aan een tabel waar er miljoenen records reeds instaan, met een hele hoop indexen daarop, dan wacht je ofwel uren omdat hij die indexen aan het updaten is ofwel omdat hij die indexen dropt en heropbouwt. Maar dat doet er nu eigenlijk niet toe.

Heb je dat al eens geprobeerd ?
(Heb je uberhaupt al eens een tabel gezien met miljoenen records in ? )

[Verwijderd]

whoami schreef op dinsdag 06 september 2005 @ 11:16:
[...]

Heb je dat al eens geprobeerd ?
(Heb je uberhaupt al eens een tabel gezien met miljoenen records in ? )

Ja, waarom?

[P_de_B]

Je huidige methode is gewoon echt niet goed. Heb je echt al geprobeerd wat de performance is, ook kun je je indexn eens bekijken. Een geclusterde index is bijvoorbeeld erg slecht als je veel inserts moet doen. Als je veel records tegelijk moet inserten kun je BULK INSERT gebruiken etc. etc.

De redenen die je aandraagt zijn in mijn ogen niet een goede reden voor een slecht design.

[whoami]

Omdat ik het eens ben met P_de_B.
Miljoenen records zouden zo geen probleem mogen zijn, tenzij je idd met een clustered index zit op een column die je vaak veranderd, of waarvan de waarde niet 'sequentieel' toeneemt.

[curry684]

whoami schreef op dinsdag 06 september 2005 @ 11:16:
[...]

Heb je dat al eens geprobeerd ?
(Heb je uberhaupt al eens een tabel gezien met miljoenen records in ? )

Is het feit dat SQL Server dat probleemloos aankan niet juist het gedeelte wat de enterprise RDBMS'en onderscheidt van de hobby-DB's?

Maar idd, als je performance fubar is heb je je clustered index verneukt cq. moet je een keer kijken naar distributed partitioned views. Het feit dat een enkele oplossing niet perfect functioneert is geen excuus voor een brak design als dit.

[whoami]

curry684 schreef op dinsdag 06 september 2005 @ 11:30:
[...]

Is het feit dat SQL Server dat probleemloos aankan niet juist het gedeelte wat de enterprise RDBMS'en onderscheidt van de hobby-DB's?

Vandaar m'n vraag.

Maar idd, als je performance fubar is heb je je clustered index verneukt cq. moet je een keer kijken naar distributed partitioned views.

Mja, dan ga je al je DB over verschillende servers gaan zetten, en ga je met je view enkel die data ophalen die de mensen die op die server zitten interesseren.
IMHO is dat enkel interessant als je een corporate DB hebt van een bedrijf met meerdere sites.

[curry684]

whoami schreef op dinsdag 06 september 2005 @ 11:34:
[...]

Mja, dan ga je al je DB over verschillende servers gaan zetten, en ga je met je view enkel die data ophalen die de mensen die op die server zitten interesseren.
IMHO is dat enkel interessant als je een corporate DB hebt van een bedrijf met meerdere sites.

Eerste vraag is natuurlijk of de clustered index brak is. Als dat niet zo is en het blijft een performance probleem is een distributed partitioned view met de laatste periode op server A en de rest van de view op server B (die op dezelfde server mogen zitten!) natuurlijk een perfecte oplossing voor dit performance probleem omdat de tabel waarop geinsert wordt net zo performant is als z'n huidige oplossing, maar dan wel functioneel en designtechnisch correct.

[Annie]

Wat wil je uiteindelijk met de view doen? Moet deze on-the-fly gegenereerd worden. Bijv. in een applicatie wordt een periode geselecteerd en op dat moment moeten de gegevens worden getoond (en moet de view dus worden gegenereerd). Of wordt deze view gewoon periodiek gewijzigd (als onderhoudstaak).
In het eerste geval zou ik de applicatie de view laten definieren. In het tweede geval zou ik òf een sql script bijhouden en telkens uitbreiden, òf een simpel scriptje (vbs ofzo) maken die de sql genereerd (even afhankelijk van de exacte wensen).

btw. Hoeveel unions wil je eigenlijk kunnen opnemen in je view? Er is namelijk een maximum (255 dacht ik).

Overigens lijkt je probleem te zijn dat je de data in de database gebruikt voor twee doelen (opslag en analyse) die elkaars tegenpolen zijn als het gaat om eisen die ze stellen aan de opzet van de database. Er nog andere mogelijkheden om je performance te waarborgen; heb je daar al eens naar gekeken? Bijv. OLAP, log-shipping om een schaduwdatabase bij te houden (de indices kan je periodiek, op rustige tijden, laten bijwerken), via DTS data overpompen naar een tweede database, enz.

[whoami]

Ik zou het dan nog zo niet oplossen.
Ik zou dan gewoon met een history tabel werken: de laatste periode(s) zit(ten) in de live-tabel, en alle andere -historische- periodes, zet je in een history tabel.

[curry684]

Die je dus met een partitioned view samen permanent tot 1 grote tabel samenvoegt ipv iedere keer een nieuwe view te construeren, dat zeg ik dus

Maar dan nog: alleen als de performance daar echt toe noopt. 1 tabel met alle data is de beste oplossing, en op een beetje server kan SQL2k dat probleemloos met "miljoenen rows". Zodra je tientallen of honderden miljoenen rows hebt praten we verder.

[Verwijderd]

Tnx voor de interessante reply's.

De database wordt idd gebruikt voor opslag en analyse = vergelijking van verschillende periodes en dergelijke maar niet tot in het oneindige, dus met 255 unions zal ik wel toekomen.

Ik heb nu zo'n 98 miljoen records van in totaal drie periodes. Er staan 12 indexen op. Ik denk als ik in één tabel van 100 miljoen records nog eens 30 mil ga bij pompen, dan ben ik gezien. Tenzij ik die indexen eraf gooi en na de insert terug opbouw maar stel dat ik op de duur 8 of 12 periodes heb zitten dan gooi ik indexen weg voor 330 miljoen rijen wat eigenlijk niet hoeft. Vandaar mijn redenering om die grote pak records met rust te laten.

[curry684]

30 miljoen rows per maand? Historytable dan, met huidige maand in een aparte tabel die je middels scheduled DTS op de eerste van de maand in de historytable pompt en desnoods records ouder dan 6 of 12 maanden weggooit. Nette view aanmaken die beiden joint waar gewenst. Clustered index op de historytable op de periode en datum, niet op andere intelligente dingen daar heb je nonclustereds voor

[Annie]

whoami schreef op dinsdag 06 september 2005 @ 11:40:
Ik zou het dan nog zo niet oplossen.
Ik zou dan gewoon met een history tabel werken: de laatste periode(s) zit(ten) in de live-tabel, en alle andere -historische- periodes, zet je in een history tabel.

Dat zeg ik toch ook
Na ja, ik had het over een andere database. Maar daarvoor zou je ook tabel/schijf/server kunnen lezen. Een en ander afhankelijk van wat je performance issue is (en welke impact deze heeft).

Ik zou het ook nooit oplossen met losse tabellen per periode, maar mocht de TS daar aan vast zitten (je hebt niet altijd de keuze om het op je eigen manier te doen), dan is hij natuurlijk nog steeds op zoek naar een oplossing. Vandaar mijn opmerkingen om het 'buiten de database' op te lossen.