Problemer med ODBC-link til AS400

Når du laver en forespørgsel, som trækker på meget store tabeller på din AS/400 server, kan Access ikke altid optimere korrekt. Derved skal der potentielt overføres meget store datamængder til Access' joins.
Det vil især være tilfældet, hvor det trækkes på flere tabeller og i endnu mere udpræget grad, hvor der trækkes på både linkede og lokale tabeller.
Har du tjekket, at der er index på de felter, der bruges til selektion og join? Det kan måske hjælpe.

Hvis der er mulighed for det, så kan det i høj grad betale sig at lave en forespørgsel som en fremsendelsesforespørgsel. Derved bliver hele forespørgslen sendt til afvikling på AS/400. SQL syntaxen skal i så fald naturligvis være DB2 syntax.

Til rapporter og visninger, hvor man benytter den samme forespørgsel hver gang (ikke parameterstyret) er det kanon. Ellers har man evt. mulighed for at ændre direkte i forspørgslens .SQL property inden udførelsen. Ikke så elegant, men muligt.

Det kan naturligvis ikke bruges til ændring i tabellerne (en fremsendelsesforespørgsel er per definition read-only), men ofte har man mulighed for at bruge en kombination af alm. forespørgsler og fremsendelsesforespørgsler. Igen, ikke så elegant som en "ren" linket forespørgsel, men hvor hastighed og performance er kritisk er det alligevel at foretrække.

Du kender det sikkert, men bare for en sikkerheds skyld. Når jeg taler om fremsendelsesforespørgsler, så mener jeg det, at du ændrer en Access forepørgsel via:

Når du står i en forespørgsels designmode, placerer cursoren på den blå bjælke foroven i vinduet og højreklikker her, kan du vælge "SQL specifikt". Vælg videregivelse. (Der er sikkert også andre måder at nå til samme resultat).

Hej Kjulius

Jeg tror der er tale om en kombination af det hele ... mit AS400 SQL opslag består i første omgang af at hente data / joine 3 AS400 PF-filer ( een stor og to mindre ). Den store fil består af 16 mio. rækker fordelt på 42 fields ...

I access joiner jeg endvidere med 2 lokale access tabeller som benyttes til at konvertede AS400 datoformater til noget letgenkendeligt ( du ved sikkert hvad jeg mener ) ...

I min SQL-forespørgsel benytter jeg mig af nogle få kriterier, som alle kører på index'erede felter.

Dette går udemærket/uden problemer i 1. hug, men jeg skal benytte denne forespørgsel som udgangspunkt for en række efterfølgende forespørgsler, og her benytter jeg kriterier på ikke-index'erede felter ... og så sker der ikke mere ...

Jeg er blevet enig med mig selv og virksomheden jeg arbejder i, at det nok er smartere at eksportere de PF-filer jeg skal benytte til en SQL-server, og benytte denne til mine dataudtræk ( forberedelse til DataWareHouse ) ... er det ikke en brugbar metode ?

Og hvis det er det ... hvordan er performance, hvis jeg ikke ændre tilfæjer flere index ... ( sorry det er første gang jeg arbejder med SQL-server løsninger )

Hmmm... Ja, der kommer jeg også til kort. :-)

Jeg har ikke erfaring med SQL server, så måske var det en idé at flytte spørgsmålet til MS MSQL Server afdelingen?

Umiddelbart ville jeg dog tro, at det ikke skulle være noget problem for SQL server at håndtere den datamængde (ligesom det heller ikke ville være det for AS/400's DB2, såfremt du kunne sende dine forespørgsler igennem til den).

Jeg ved, at det er meget brugt i Access at stacke forespørgsler. Mange finder det lettere end at lave en samlet forespørgsel (og Access understøtter da heller ikke mere end to niveauer, så det er også begrænset hvad muligheder man har). Den begrænsning har hverken MS SQL server eller AS/400' DB2, så her er man mere frit stillet til at lave mere komplekse forespørgsler. Man kan dog også her lave statiske views (svarende til en forespørgsel i Access). Den første forespørgsel du dannede i Access, og som dannede grundlag for dine efterfølgende forespørgsler, kunne du jo også lave i både MS SQL server og på AS/400 via en CREATE VIEW SQL-kommando. For at tilgå den fra Access er du dog nødt til at lave din forespørgsel som en fremsendelsesforespørgsel, da du ikke kan linke til et view.

Så hvis du stadig har mod på at arbejde på DB2/Access, kunne du jo prøve at oprette din første forespørgsel som et view på AS/400 og så ændre de andre forespørgsler til fremsendelsesforespørgsler til AS/400, baseret på dette view. Det kan fremme hastigheden mange gange.

Men du kan også gå SQL server vejen. Den er sikkert mindst lige så god. Hvis det er for at fiske data til en kube, er det sikkert endda bedre. Men jeg har som sagt ikke nogen erfaring med SQL server.

Ok .. tak for din hjælp so far :-)

Læs http://www.eksperten.dk/spm/807231

miped > De nævnte forslag har ingen effekt på performance

Nej, derfor skrev jeg også "Men så bliver der også lidt arbejde med VBA bagefter, hvis du løbende skal køre forespørgsler med relationer mellem en tabel i din Access database og AS/400. Men så vil det også uanset hvad gå meget hurtigere..."

Det er rigtig dårlig ide, at linke tabler i Access direkte med AS/400.. Du risikerer at ligge serveren ned. Derfor er det bestemt en fordel, at bruge VBA til at definere en SQL Pass-Trough Query på baggrund af data i en Access tabel.

Kan du give et eksempel på, hvad du gerne vil lave med de data i Access tablen og på AS/400'en?

- Fik du svar nok vedr. http://www.eksperten.dk/spm/807231 ?

Eksempelvis vil jeg gerne have skabt et dynamisk datasæt (som p.t består af 13 manuelt vedligeholdte qry i AS400) hvorfra slutbruger ved hjælp af kan egne (on-the-fly)periodevalg og/eller andre kriterier kan benytte den samme  rapportskabelon med grundag dataudtræk ... dynamisk

Ok, det lyder umiddelbart at være lige til. Hvis brugerne blot skal angive dato range, kontonr. el. lign. er det klart bedst, at define en SQL Pass-Trough Query i VBA med de oplysninger brugerne har angivet i en formular. På den måde bliver forespørgslen kun udført af AS/400, i stedet med både AS/400 og Access hvis dårlige netværksegenskaber du allerede har stiftet bekendskab med...

Har du nogle konkrete eksempler på, hvad der skal ske med hvilke data, fra at brugerne har angivet hvilke kriterier, til at datagrundlaget er som det skal være til rapporten?

Lukker ...
Læg venligst svar

..

Hej Jensen

Jeg ved det er lidt sent, da spørgsmålet er lukket, men jeg kiggede lige på konversationen, og faldt over din kommentar:

"I access joiner jeg endvidere med 2 lokale access tabeller som benyttes til at konvertede AS400 datoformater til noget letgenkendeligt ( du ved sikkert hvad jeg mener ) ..."

Jeg må indrømme, at jeg ikke ofte har haft problemer med datoer (måske fordi jeg bruger 01.01.1900 som "dummydato"). DB2's "dummydato" 01.01.0001 er Access ikke glad for, det er rigtigt. Men det er da så vidt jeg ved også det eneste der har været galt. Normale datoer i DB2 datoformat bliver korrekt konverteret til Access' datoformat af ODBC driveren.

Men ved at bruge en fremsendelsesforespørgsel, kan man få DB2 til at konvertere 01.01.0001 til f.eks. null eller en anden "dummydato" som ligger inden for Access' datoområde:

SELECT CAST(CASE WHEN REGDATO = '01.01.0001' THEN NULL ELSE REGDATO END AS DATE) AS REGDATO

Så også på det område bliver det lidt lettere med en fremsendelsesforespørgsel (aka Pass Through Query).

Bare en lille kommentar herfra efter lukketid... :-)

Hej kjulius

Jeg kigger på dit forslag ved lejlighed.  Takker