Thread: bad performance for Access, ODBC and Postgres
Hi, I have very poor access whith a front end Access 2003, ODBC and Postgres 7.4.3. Much time to find a record in a table ff 13000 records with indexs. Sniffing the network, I can see a lot of traffic and the CPU of postmaster is about 30% (P4, 2.8 GHz). It seems that all the data's are backed on the client side and then, analyzed. Does it exist an option to improve the performances ? thanks, -- Jean-Max Reymond CKR Solutions http://www.ckr-solutions.com
Jean-Max Reymond a écrit : >Hi, >I have very poor access whith a front end Access 2003, ODBC and Postgres 7.4.3. >Much time to find a record in a table ff 13000 records with indexs. >Sniffing the network, I can see a lot of traffic and the CPU of >postmaster is about 30% (P4, 2.8 GHz). It seems that all the data's >are backed on the client side and then, analyzed. >Does it exist an option to improve the performances ? >thanks, > > > Maybe you can use the EXPLAIN command to see what query plan the system creates for your query (you can see the where condition, which index is used, ...) Eric.
On Thu, 29 Jul 2004 14:57:25 +0200, Eric HAGENBACH <eric.hagenbach@vif.tm.fr> wrote: > Jean-Max Reymond a écrit : > > >Hi, > >I have very poor access whith a front end Access 2003, ODBC and Postgres 7.4.3. > >Much time to find a record in a table ff 13000 records with indexs. > >Sniffing the network, I can see a lot of traffic and the CPU of > >postmaster is about 30% (P4, 2.8 GHz). It seems that all the data's > >are backed on the client side and then, analyzed. > >Does it exist an option to improve the performances ? > >thanks, > > > > > > > Maybe you can use the EXPLAIN command to see what query plan the system > creates for your query (you can see the where condition, which index is > used, ...) > > Eric. > EXPLAIN is for identified requests and I don't know which request Access sends. It seems that Access open a cursor on the database and reads each record one by one and tries to match with the condition. This is the only way I can explain such behaviour and it is of course very silly. -- Jean-Max Reymond CKR Solutions http://www.ckr-solutions.com
Hello, Can you give us some more informations regarding the way you link Access and Postgresql? Linked table? Pass-through query? How do you query the table? With an Access filter? In order to get the best performance, I suggest you put all the logic on the database, and use a pass-through query to querythe postgresql view or function. -----Message d'origine----- De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Jean-Max Reymond Envoyé : jeudi, 29. juillet 2004 14:34 À : pgsql-odbc@postgresql.org Objet : [ODBC] bad performance for Access, ODBC and Postgres Hi, I have very poor access whith a front end Access 2003, ODBC and Postgres 7.4.3. Much time to find a record in a table ff 13000 records with indexs. Sniffing the network, I can see a lot of traffic and the CPU of postmaster is about 30% (P4, 2.8 GHz). It seems that allthe data's are backed on the client side and then, analyzed. Does it exist an option to improve the performances ? thanks, -- Jean-Max Reymond CKR Solutions http://www.ckr-solutions.com ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings