SQL-Server-views, tabellen uit verschillende databases

[KlaasJanBolhuis]

Wat is de beste manier om in view tabellen te 'Joinen' uit 2 verschillende databases?

Ik gebruik MS-Server 2019 express, met een database met Snelstart (gekocht boekhoud programma) en een database met ons opdracht-administratie-systeem. (Zelfbouw in Access, en nu bezig om de tabellen van Acces backend over te zetten naar SQL-Server). De relatie-tabel uit Snelstart houden we bij in Snelstart en gebruiken we zowel in Snelstart als in ons zelfbouw systeem.

Als ik de snelstart-relatie-tabel rechtstreeks join in een view in de opdrachten-database, zakt de performance hard onderuit. (SELECT allerlei velden FROM Snelstart.dbo.tblRelatie INNER JOIN dbo.tbl_pakbonkop ON Snelstart.dbo.tblRelatie.fldRelatieID = dbo.tbl_pakbonkop.Klant).
Maak ik eerst een view met de relatie's, met alleen de benodigde velden en filter voor alleen relevante records, die ik later weer gebruik voor andere views, wordt het niet beter. De 'join' velden zijn geïndexeerd.

Bijkomend probleem is dat ik graag werk met bacpac's voor mijn versie beheer en bij het gebruik zoals bovenstaand, wil er geen bacpac meer gemaakt worden vanwege verwijzingen buiten de database (logisch).

Wat is de meest juiste manier om de live-koppeling te maken.

[Muuh87]

Heb je toevallig al gekeken naar je queryplan en statistics om te zien waar de traagheid in zit?

[sig69]

KlaasJanBolhuis schreef op maandag 21 maart 2022 @ 13:05:

Als ik de snelstart-relatie-tabel rechtstreeks join in een view in de opdrachten-database, zakt de performance hard onderuit. (SELECT allerlei velden FROM Snelstart.dbo.tblRelatie INNER JOIN dbo.tbl_pakbonkop ON Snelstart.dbo.tblRelatie.fldRelatieID = dbo.tbl_pakbonkop.Klant).
Maak ik eerst een view met de relatie's, met alleen de benodigde velden en filter voor alleen relevante records, die ik later weer gebruik voor andere views, wordt het niet beter. De 'join' velden zijn geïndexeerd.

Ik zie geen where clause? Dan trek je feite de hele tabellen leeg. Weet niet om hoeveel records het gaat?

Bijkomend probleem is dat ik graag werk met bacpac's voor mijn versie beheer en bij het gebruik zoals bovenstaand, wil er geen bacpac meer gemaakt worden vanwege verwijzingen buiten de database (logisch).

Wat is de meest juiste manier om de live-koppeling te maken.

Als je een relatie view maakt in je eigen database die verwijst naar de relatie tabel in de snelstart database, werkt het dan wel misschien?

[dixet]

Een simpele join over twee databases heen kan prima performen, dus het probleem zou ik in de inrichting van je tabellen zoeken. Hoe groot zijn ze? Zijn alleen de join-velden geïndexeerd of ook je selectiecriteria?
Zo te zien hebben de tabellen een 1:N relatie met een join op de pk van tblRelatie. Is de index op die sleutel als uniek gedefinieerd?

De beste manier om er achter te komen waar de pijn zit is zoals hierboven al gezegd kijken naar je queryplan en statistics.

sig69 schreef op maandag 21 maart 2022 @ 19:48:
Ik zie geen where clause? Dan trek je feite de hele tabellen leeg. Weet niet om hoeveel records het gaat?

Dat een view geen where-clause heeft is niet zo gek. Die where-clause voeg je dan wel toe wanneer je de view gebruikt, wat voor de performance geen verschil zou moeten maken. De optimizer maakt een queryplan voor je hele query, niet alleen voor de view.
Als je een select * from view doet zonder where clause en je tabellen zijn giga is het niet gek dat het niet performt.

[KlaasJanBolhuis]

Bedankt.
Ik heb naar het queryplan gekeken. De statistics lukten niet vanwege 'Schemabinding' en als ik die zou toevoegen lijkt het mij dat mijn flexibiliteit gedurende de ontwikkeling voor een deel wegvalt. Erg leerzaam en kom er steeds meer achter dat SQL-server veel meer kan regelen dan dat ik voorheen in de backend in access had.
Het queryplan en de opmerkingen over indexen en unieke sleutels heeft mij geleid naar de oplossing. Een tijd terug heb ik de relatie-tabel uit snelstart gekoppeld gehad aan de backend in Access. Om de koppeling tot stand te kunnen brengen moest ik de naam veranderen van de primary key van de relatie tabel (Access accepteerde geen 'PK' in de naam tijdens het koppelen). Ik verwacht dat omdat ik nu deze naam weer terug veranderd heb de performance opeens top is. Met de zwaarste query's met de beschikbare tabellen kom in 0,86 sec en dat is wat mij betreft top.
De 'where' clausule had ik niet in mijn bericht meegenomen om de vraag niet te vertroebelen.
Ik meende dat de optimizer niet werkte bij de SQL-Server-Express versie, daarom zal ik in mijn ontwikkel-omgeving eens kijken of ik de developer versie geïnstalleerd krijg.
Wat ik nog graag zou willen weten is:
: Wat is in jullie ogen de beste manier om 'cross-database' query's/views op te bouwen waar de 'Export Data Tier Application' niet over struikelt bij het maken van Bacpac?. Of moet ik dat gewoon accepteren en opzoek naar een andere methode om versie beheer te doen.

[Stefke]

Let op dat Access alle data van een view lokaal haalt en dan pas zijn eigen bewerkingen doet, dus enige voorselectie op SQL server is meestal wel wenselijk (vooral als je netwerksnelheid niet al te best is of er veel gebruikersactiviteiten zijn).

Met vaste WHERE waarden is dat geen punt, maar dat kan dan wat lastiger wanneer je parameters wil/moet gebruiken (omdat je die niet zonder meer vanuit Access naar SQL kunt "passen"), dan zul je of met stored procedures moeten werken, of eventueel met dynamische SQL via een passthrough query of ADODB recordset