Horton
Menu

Thread

  1. Slow Searches using MSAccess and ODBC to a PostGreSQL database

    Valerio Santinelli <tanis@mediacom.it> — 1998-12-18T15:11:09Z

    I've just setted up my PostGreSQL database on a Linux machine and it
seems to work fine with the radius daemon i'm currently working on.

Now I need to interface it with MSAccess to access data about our
customers and other stuff stored in the postgres database. It seems to
work correctly as to insertion, update and so on, but when I'm doing a
search on every field on a substring it gets something like 2 minutes
and a half to look in a 1500 records database and you all know that'd
way too much.
The search is on 8 fields on a single table.. what could I do to speed
it up ? (When i was running this database just on Access it took at most
2 seconds for the whole search)

I gave a look to the log and it seems that for every search on a single
record it involves seven transactions on the same record.. that sounds
strange to me.. anybody who can help me out ?

Thanks.

Valerio Santinelli
tanis@mediacom.it

  2. FWD: Erlang/Mnesia Open source

    Clark C . Evans <clark.evans@manhattanproject.com> — 1998-12-19T01:48:59Z

    FYI,



===============================

Subject:      Erlang/Mnesia Open source
Date:         15 Dec 1998 17:17:15 +0100
From:         Claes Wikstrom <klacke@erix.ericsson.se>
Organization: Ericsson Telecom, Stockholm, Sweden
  Newsgroups: comp.databases, comp.databases.theory

Hello all,

I'm posting in these database groups to announce that
Ericsson Telecommunications has just recently released the 
Erlang programming language in the public domain.

The reason I post in these database groups is that with this
open source release of Erlang comes a Distributed DBMS called Mnesia
which is then also in the public domain. 

Erlang is a mostly functional programming language and Mnesia
is a DDBMS which is tightly integrated in Erlang.

If you are interested, check out http://www.erlang.org for
more information about Mnesia.

/klacke

+--------------------------------------------------+
| Claes Wikstrom , tel: +46 8 719 81 08            |
| email: klacke@erix.ericsson.se                   |
| WWW:   http://www.ericsson.se/cslab/~klacke      |
+--------------------------------------------------+

  3. Re: [GENERAL] Slow Searches using MSAccess and ODBC to a PostGreSQL database

    Dustin Sallings <dustin@spy.net> — 1998-12-19T03:14:54Z

    On Fri, 18 Dec 1998, Valerio Santinelli wrote:

	What does the query look like, and what does the table look like?

// I've just setted up my PostGreSQL database on a Linux machine and it
// seems to work fine with the radius daemon i'm currently working on.
// 
// Now I need to interface it with MSAccess to access data about our
// customers and other stuff stored in the postgres database. It seems to
// work correctly as to insertion, update and so on, but when I'm doing a
// search on every field on a substring it gets something like 2 minutes
// and a half to look in a 1500 records database and you all know that'd
// way too much.
// The search is on 8 fields on a single table.. what could I do to speed
// it up ? (When i was running this database just on Access it took at most
// 2 seconds for the whole search)
// 
// I gave a look to the log and it seems that for every search on a single
// record it involves seven transactions on the same record.. that sounds
// strange to me.. anybody who can help me out ?
// 
// Thanks.
// 
// Valerio Santinelli
// tanis@mediacom.it
// 
// 
// 

--
Principle Member Technical Staff, beyond.com    The world is watching America,
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE 
L______________________________________________ and America is watching TV. __

  4. Re: [GENERAL] Slow Searches using MSAccess and ODBC to aPostGreSQL database

    Valerio Santinelli <tanis@mediacom.it> — 1998-12-20T19:28:26Z

    The table is made up of 8 fields: one of them is an ID number, the others are all
text fields except one that's of "memo" type.

I'm not generating the query.. instead it's MSAccess itself that's generating lots
of queries with a SELECT of everything based on the ID number .. something like:

SELECT * from clienti where ID = 1

and it's repeating this stuff 7 times for the same ID and then for every ID..

dustin sallings wrote:

> On Fri, 18 Dec 1998, Valerio Santinelli wrote:
>
>         What does the query look like, and what does the table look like?
>
> // I've just setted up my PostGreSQL database on a Linux machine and it
> // seems to work fine with the radius daemon i'm currently working on.
> //
> // Now I need to interface it with MSAccess to access data about our
> // customers and other stuff stored in the postgres database. It seems to
> // work correctly as to insertion, update and so on, but when I'm doing a
> // search on every field on a substring it gets something like 2 minutes
> // and a half to look in a 1500 records database and you all know that'd
> // way too much.
> // The search is on 8 fields on a single table.. what could I do to speed
> // it up ? (When i was running this database just on Access it took at most
> // 2 seconds for the whole search)
> //
> // I gave a look to the log and it seems that for every search on a single
> // record it involves seven transactions on the same record.. that sounds
> // strange to me.. anybody who can help me out ?
> //
> // Thanks.
> //
> // Valerio Santinelli
> // tanis@mediacom.it
> //
> //
> //
>
> --
> Principle Member Technical Staff, beyond.com    The world is watching America,
> pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
> |    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
> L______________________________________________ and America is watching TV. __

--
C'ya!

 Valerio Santinelli a.k.a. TANiS
 [tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]

  5. Re: [GENERAL] Slow Searches using MSAccess and ODBC to aPostGreSQL database

    David Hartwig <daveh@insightdist.com> — 1998-12-21T15:17:10Z

    
dustin sallings wrote:

> On Fri, 18 Dec 1998, Valerio Santinelli wrote:
>
>         What does the query look like, and what does the table look like?
>
> // I've just setted up my PostGreSQL database on a Linux machine and it
> // seems to work fine with the radius daemon i'm currently working on.
> //
> // Now I need to interface it with MSAccess to access data about our
> // customers and other stuff stored in the postgres database. It seems to
> // work correctly as to insertion, update and so on, but when I'm doing a
> // search on every field on a substring it gets something like 2 minutes
> // and a half to look in a 1500 records database and you all know that'd
> // way too much.
> // The search is on 8 fields on a single table.. what could I do to speed
> // it up ? (When i was running this database just on Access it took at most
> // 2 seconds for the whole search)
> //
> // I gave a look to the log and it seems that for every search on a single
> // record it involves seven transactions on the same record.. that sounds
> // strange to me.. anybody who can help me out ?

Welcome to the world of remote data access via the MS Jet.

Are you using the search/filter mechanism while browsing a table, or a query in
the query designer?

Which version of backend and driver?

Is there a primary key of which MS Access is aware?

Is there an actual primary key on the server side?


A CommLog file would be helpful.    You can send that direct to me.