Newsgroup: it.comp.appl.access
Titolo: Per esperti Oracle-Access performance

Ciao a tutti,
sono un novellino di Access, alle prese con problemi di performance.
 
Uso un file Access 2010 con tabelle collegate ad un DB Oracle di
fatturazione (che vedo in sola lettura).
 
Tramite maschera e controlli, l'utente crea delle insert che prelevano
dati da queste tabelle collegate per inserirle in tabelle locali.
 
Il problema è che le relative SELECT fatte girare su Toad o SQL
Developer impiegano 2-10s per essere eseguite, mentre su ACCESS, con
le tabelle collegate il tempo è di 20-30 minuti.
Da quel che ho capito Access sembra che si scarichi in locale le
tabelle per poi fare la query in locale... per tabelle piccole
andrebbe bene, ma io ho in join una tabella da svariate decine di
milioni di record con se stessa.
 
C'è qualche trucchetto che posso sfruttare?
In rete ho trovato un bel po' di materiale, ma non una soluzione.
Un'idea, venuta ad altri a quanto ho letto, sarebbe quella di
collegarmi via ADO e inserire tutto in un recordset, da qui poi
copiare il recordset in una tabella attraverso un ciclo. Peccato che
proprio con questo ciclo di "insert" perderei in performance.
Non trovo invece un comando per dire ad access di inserire tutto il
recordset in tabella direttamente, senza ciclare.
 
Ad oggi l'unico escamotage che ho trovato è quello di far creare su un
altro DB oracle (su cui un collega ha l'utenza in scrittura) una vista
tramite DB link al DB di fatturazione,  io collego questa vista ad
access e faccio le query su questa tabella... guadagno già molto in
velocità, ma aziendalmente sono da evitare "giri" di questo genere fra
DB diversi.
 
Se potete indirizzarmi verso qualcosa, ve ne sarei grato.


Access nn si scarica la tabella in locale e poi esegue la query SE E
SOLO SE non usi funzioni di access per eseguirla...Dipende dal driver
(penso che userai ODBC) ma se può, Jet passa la palla al server per
l'esecuzione della query e si becca solo il recordset risultante. Per
essere sicuro che ciò succeda, sempre usando DAO, utilizza l'insieme
QueryDef per fare una query pass-through. Quella viene lanciata verso
il motore senza che jet ci metta becco. Se usi SQL standard, nessun
dlookup o nz o cose così la deve eseguire. Le performance di solito
sono appena inferiori alle query sul service


La query che faccio è abbastanza semplice, ho tolto le funzioni IIF,
CSTR, ad ora restano la funzione Now() e una data scritta con #mm/dd/
yyyy#.
Togliendo Now() e modificando la data in 'dd-mmm-yyyy' riesco
tranquillamente a far girare la query su SQL Developer, solo ad una
velocità di oltre 100 volte maggiore. :(
Sono piuttosto sicuro che si scarichi in locale la tabella, perché il
traffico lan passa da 0 al 20% e ci resta finché non ha completato la
query.
 
Io oggi compongo la query e la salvo in una variabile stringa, la
eseguo poi con il comando
strSQL=" INSERT INTO " & tblLocale & " SELECT Campo1,Campo2.. FROM " &
tblCOLLEGATA
DoCmd.RunSQL strSQL
 
Chiedo scusa per l'ignoranza, ma non so cosa sia l'insieme QueryDef
"per fare una query pass-through".
Se hai voglia/tempo puoi dirmi in due parole come procedere?
Su google mi sembra di capire che consista nel creare un recordset e
poi ciclare su questo per inserire i suoi dati in una tabella.
 
Grazie per la pazienza


non usare docmd.runsql ma il metodo execute dell'oggetto database. Il
primo viene eseguito da Access, il secondo da Jet (sembra una
sottigliezza ma è
MOOOLTO diverso!!!!).
Le query pass-through sono query alle quali si è detto di venire
eseguite PER FORZA sul server, senza nessun magheggio da parte del
client. Questro vuol dire che devono essere per forza scritte nel
'dialetto' del server e non del client (per esempio, su SQL server, se
facessi una query PT usando Nz mi darebbe errore. Dovrei usare la
funziono Coalesce, propria del transact SQL). Nn conosco il driver
access-oracle, ma sicuramente permette meno 'magie' di quello access-
SQL server, che si preoccupa di 'tradurre' i vari costrutti quasi in
automatico. Se traffico di rete aumenta, qualcosa succede. Se usi
Now() sicuramente jet esegue robe (nn credo che ci sia dentro Oracle).
Se usi DoCmd sicuramente Access lavora....Prova a step successivi. Fai
una select invece di una insert, conta i record (movelast ->
recordcount) con il tuo SQL. Poi riprova e togli OGNI funzione nativa
di Access (usa delle variabili per applicarle, poi usale nel costrutto
SQL, eventualmente). Vedi subito le eventuali differenze. Tieni conto
che, a seconda del driver, jet tende a delegare il server per le parti
che lui ritiene possa svolgere. Il resto lo fa in locale. Solo se
l'SQL è troppo 'dialettale' o per qualche ragione Jet ritiene che il
server non possa svolgere nulla richiede tutta la tabella. Ma se
lavori bene sono eventualità che non si verificano quasi mai.
Bisognerebbe eliminare in questi casi le funzioni native per quelle
standard (se non ho capito male, tu usi un Format(....). Ecco quella
Oracle non la conosce davvero, quindi non sa cosa farsene. Quindi
lavora Jet. Evitale come la peste!!!!! Butti la data in una variabile
TuaData e usi quella nell'SQL)


Per le date mi sono spiegato male, già creo una variabile su cui metto
il valore...
in aggiunta uso Now() come su Oracle userei SYSDATE per tracciare il
momento in cui è stata fatta l'elaborazione.
 
Ho trovato queste query di cui mi parlavi.
 
In pratica devo usare questo codice (magari serve ad altri)
 
        Dim qdfPassThrough As DAO.QueryDef
        Set qdfPassThrough = CreateQueryDef("qrySQLPass")
        qdfPassThrough.Connect =
"ODBC;DSN=ABCD;UID=UTENTE;PWD=PASSWORD"
        qdfPassThrough.SQL = strQuery
        qdfPassThrough.ReturnsRecords = True
        qdfPassThrough.Close
 
con questo creo la pass through, poi faccio una insert da questa alla
mia tabella locale con
        DoCmd.RunSQL strSQL_Insert
 
Se ho capito bene, è questo che mi consigliavi di fare.
Devo ancora verificare se funziona... lo sto scrivendo in diretta.
 
Quanto all'uso del metodo execute al posto di docmd.runsql, non
conosco la differenza, vedo di informarmi.
 
Per ora ti ringrazio, come vedi Access viene usato molto anche da chi
non ha tutte le conoscenze per fare un buon lavoro, ma è talmente
flessibile che conviene lo stesso fare così con prove ed errori!

Innanzitutto grazie!
 
ancora non ho guardato il DoCmd.RunSQL vs Execute, ma l'altra parte
del problema si è risolta più che bene con il pass through.
dai 30 minuti si è passati a pochi secondi, tempo del tutto
accettabile!
 
^___^


Quindi è evidente che il lavoro sporco lo faceva jet!!!
Togliti il dubbio: via ogni funzione di access/vba (anche Now()) e poi
 
Dim Dbs as database
Dim StrSql as string
 
set dbs=currentdb
 
StrSql="tuaStringaSQL"
 
dbs.execute StrSql, dbfailonerror+dbseechanges
 
e confronta i tempi!!!