[LØST] Spørring fra flere databaser der den ene inneholder databasenavn til de andre

[hightow]

Jeg har en hoveddatabase (MasterX) og flere klientdatabaser.

MasterX-basen inneholder en tabell med bl.a databasenavn til klientdatabasene

 

Det jeg har problemer med er å kombinere spørring fra MasterX og alle klientdatabasene.

Jeg vil kjøre en der jeg lister ut alle navnene på klientdatabasene fra MasterX, og en Count(0) fra en tabell fra klientdatabasene. Noe ala dette.

 

SELECT DBM.CL_dbname, (SELECT Count(0) FROM DBM.CL_dbname..tTable)

FROM MasterX..Clients AS DBM

 

Her får jeg syntaks error på det som er uthevet.

 

Noen som kan fortelle meg hva som er riktig syntaks her (hvis det finnes)? Eller må jeg evt bruke cursors for å løse dette?

 

(Bruker SQLServer hvis det skulle ha noe å si...)

Endret 25. april 2008 av hightow

[roac]

Jeg er stygt redd du må bygge opp dynamisk SQL som du kjører med f eks sp_executesql. Siden du skal traversere over et sett med databasenavn så betyr dette at du også må bruke en løkkestruktur eller cursor for å få til dette.

[hightow]

Det var det jeg var redd for ja. Takk for hjelpen

[Kul drittunge]

Hum.

 

Jeg er enig i at dette blir dynamisk sql.

Og at det blir sp_executesql.

 

Meget interessant problemstilling, forresten.

Kunne du kommet med noen flere detaljer? Gjerne med tabelldefinisjoner.

 

Det virker i utgangspunktet klart for meg at dette kan håndteres best av en sp, men jeg skulle gjerne likt å vite hvordan MSSQL håndterer forskjellige kompileringsplaner i forhold til hvordan man spesifiserer parametrene.

[kaffenils]

Det virker i utgangspunktet klart for meg at dette kan håndteres best av en sp, men jeg skulle gjerne likt å vite hvordan MSSQL håndterer forskjellige kompileringsplaner i forhold til hvordan man spesifiserer parametrene.

 

Er det i forbindelse med procedure cache for stored procedures du mener?

[Kul drittunge]

Det virker i utgangspunktet klart for meg at dette kan håndteres best av en sp, men jeg skulle gjerne likt å vite hvordan MSSQL håndterer forskjellige kompileringsplaner i forhold til hvordan man spesifiserer parametrene.

 

Er det i forbindelse med procedure cache for stored procedures du mener?

 

Er noe tvilsomt om man kan cache en fremgangsplan for en utspørring hvor man ikke kjenner områdene man utspørr, på forhånd.

 

Så jeg vil være enig i at en dynamisk SQL and damn the torpedoes er greit.

Trenger litt mer info før jeg kan vurdere det, da.

[kaffenils]

Er noe tvilsomt om man kan cache en fremgangsplan for en utspørring hvor man ikke kjenner områdene man utspørr, på forhånd.

 

Du kan jo sette opp parameteriserte execution plans manuelt ved å bruke sp_get_query_template og sp_create_plan_guide, men du kan fort gå på en "smell" da det ikke er sikkert at planen er optimal for forskjellige parameterverdier. Jeg har aldri brukt det både fordi det er for komplsert og fordi jeg selv skriver alle spørringer og kan derfor guide query optimizeren hvis jeg synes den bruker en ekstremt dårlig plan, noe den veldig sjelden gjør.

Områder hvor dette kan komme til nytte f.eks. OR-M produkter der du ikke selv skriver SQL statementene, eller ferdige systemer som SAP hvor du heller ikke styrer hvordan SQL statementene ser ut.

 

Her er forresten en artig liten detalj jeg lærte på et Expert Level kurs hos MS rett før jul angående parameterisering og execution plan gjenbruk fra procedure cache. Hvis du har lest i BOL om simple parameterization så står det "Under the default behavior of simple parameterization, SQL Server parameterizes a relatively small class of queries". Du får altså ikke vite hva som er kriteriet for at SQL Server skal cache en parameterisert versjon av statementet, bare at relativt få typer spørringer blir parameterisert, og kan gjenbrukes. Det er egentlig ganske snodig at BOL ikke forteller hva som er kriteriet da svaret er såre enkelt.

Og det er som følger: "Hvis PARAMETERIZATION SIMPLE (default) er satt for databasen så vil den parameteriseter versjonen av en spørring caches hvis alle kolonnene i WHERE-delen har en unique index, unique constraint eller er definert som primary key og alle operatorene er =. Verre er det ikke.

 

Og til slutt en liten avdvarsel. Ikke sett PARAMETERIZATION FORCED for databasen bare for å få cachet parameteriserte versjoner av spørringer for bedre gjenbruk. Da vil du fort få problemer.

Hvis du vil tvinge enkelte typer spørringer til å bruke en parameterisert versjon fra procedure cache så bruker du heller sp_get_query_template og sp_create_plan_guide og setter @hints = N'OPTION(PARAMETERIZATION FORCED)' for sp_create_plan_guide.

Endret 25. april 2008 av kaffenils

[hightow]

Jeg har løst problemet mitt (jeg skjønte forsåvidt ikke halveis bæret av hva de siste innleggene gikk ut på, men det for bare være...)

 

Det ble jeg opprettet en cursor med databasenavnet til klientene. For hver klient kjørte jeg en spørring der resultatet ble lagt inn i en temporær tabell, og til slutt kjørte spørring mot den temporære tabellen.

[Kul drittunge]

Jeg har løst problemet mitt (jeg skjønte forsåvidt ikke halveis bæret av hva de siste innleggene gikk ut på, men det for bare være...)

 

Det ble jeg opprettet en cursor med databasenavnet til klientene. For hver klient kjørte jeg en spørring der resultatet ble lagt inn i en temporær tabell, og til slutt kjørte spørring mot den temporære tabellen.

 

Poenget er at hvis du har et system hvor du baserer deg på å lage dynamiske spørringer, og det er TABELLENE som du kobler mot som er dynamiske, da sliter kompilatoren ganske så godt med å i det hele tatt cache spørringsplanen.

 

Dette medfører at ettersom systemet vokser, så yter det dårligere og dårligere.

 

Det har vært folk som har hatt nettopp denslags lure tanker som du her er inne på, og det har "ikke vært så bra", om du er med. Vil ikke si for mye der.

[hightow]

Ok.

Alle klientdatabasene har tabeller som har samme faste struktur, og det er samme spørringen som skal foretas mot hver database. Det eneste som er "dynamisk" er antall klientdatabaser.

 

Denne spørringen er bare et engangstilfelle - normalt er det spørringer mot den enkelte klientdatabase.

[kaffenils]

Poenget er at hvis du har et system hvor du baserer deg på å lage dynamiske spørringer, og det er TABELLENE som du kobler mot som er dynamiske, da sliter kompilatoren ganske så godt med å i det hele tatt cache spørringsplanen.

 

Dette medfører at ettersom systemet vokser, så yter det dårligere og dårligere.

 

Hmm. Det vil vel nesten bli omvendt. For mye caches. Når du bruker sp_executesql så caches execution plan for kombinasjonen av @stmt og @params, og gjenbrukes når samme kombinasjon av parametrene for sp_executesql kjøres igjen. Dette kan fort gi performance problemer, f.eks. hvis du i @stmt har WHERE Dato between @date1 and @date2. Godt mulig at index seek er optimalt for verdiene for @date1 og @date2 første gang du kjører sp_executesql. Neste gang derimot kan verdiene tilsie av en index scan vil være optimal, men siden planen er cachet så vil denne gjenbrukes og en index seek vil bli brukt i stedet. Denne problemstillingen gjelder generelt for stored procedures.

 

Du kan selvfølgelig bruke OPTION(RECOMPILE) for å tvinge recompile av alle statements, men det har jo sine åpenbare svakheter også.

 

Damned If You Do... Damned If You Don't

Endret 25. april 2008 av kaffenils

[Kul drittunge]

Hmm. Det vil vel nesten bli omvendt. For mye caches. Når du bruker sp_executesql så caches execution plan for kombinasjonen av @stmt og @params, og gjenbrukes når samme kombinasjon av parametrene for sp_executesql kjøres igjen.

 

Mja.

Nå beveger vi oss jo litt over i løs spekulering rundt heller perifere ting, men det jeg snakker om, er at navnet på tabellen(e) ikke inngår i @params. Du må concatenere dem inn i @stmt.

 

I den forbindelse noterer jeg meg at sub-kompileringsplaner for statements i utgangspunktet blir liggende i cachen til rengjøringsdama trekker ut kontakten for å få strøm til støvsugeren, og jeg har da en liten mistanke om at optimalisereren ikke er så veldig interessert i å dytte inn tusenvis av sub-kompileringsplaner for hver sub-rutine i en enkelt prosedyre.

 

Hva som da skjer, har jeg ikke brydd meg med å undersøke selv, jeg noterer meg bare at siden man må gjøre kromspring for å lage dynamisk sql mot forskjellige tabeller, er det grunner til det, og i utgangspunktet en dårlig ide å gjøre dette annet enn unntaksvis.

 

Det er strengt tatt ikke så veldig vanskelig å undersøke dette, så du er velkommen til å oppklare det.

[kaffenils]

Det er strengt tatt ikke så veldig vanskelig å undersøke dette, så du er velkommen til å oppklare det.

 

Jeg testet faktisk før jeg postet forrige post, riktignok kun med et statement i @stmt. Hver eneste kombinasjon av @stmt og @params caches og gjenbrukes når samme kombinasjon eksekveres igjen. Du ser det lett ved å logge Cache:Hit og Cache:Miss eventer i Profiler. Skal se om jeg får tid til å teste med flere sub-statements for å se hvordan dette caches.