Re: Re: [ODBC] linked tables in MS Access

Tue, Jan 23, 2007 at 11:21:58AM +0100, Jiří Nouza napsal(a):
> Of course I had commlog and mylog turned on. But I've never thought
> that writing some kb (ok, few megabytes) of text could have such big
> affect :(

mylog is very detailed log (all SQL statement all getted values, ...)
and it's called very often in the code.
psqlodbc.log should be ok (there are few informations).

> It seems that turning off helps very well. Access work by now commonly fast.
>
> I will try to change "use declare/fetch" and "cache size" but it is
> hard to debug via mylog because it is unbelievable slow.

You don't need it if it's ok now for you. Use declare/fetch convert
SELECT statements to cursor so the result is fetched in "cache size"
blocks. Sometime it helps sometime it kills the performance.

Regards,

Luf

> > ------------ Původní zpráva ------------
> > Od: Ludek Finstrle <luf(at)pzkagis(dot)cz>
> > Předmět: Re: [ODBC] linked tables in MS Access
> > Datum: 23.1.2007 10:26:52
> > ----------------------------------------
> > > But I have MS Access front end containing linked tables to Postgres
> > > via ODBC. And I'm struggling with slow performance. When I want to
> > > open a table which contains about 8000 records it takes more then
> > > 20 sec. When I want to move cursor at the last record it takes more
> > > than 60 extra seconds.
> > >
> > > I've already checked Postgres server log and MS Access queries are
> > > executed quickly (<500 ms).
> > >
> > > Is this normal behavior? Does MS Access cooperates with MS SQL such
> > > better than with other DBMS via odbc?
> >
> > I see no description of your configuration.
> > What's your ODBC settings? What ODBC driver do you use? What OS?
> > I hope you haven't enabled the mylog output.
> > How long takes it the psql to show 8000 records?
> >
> > BTW let's try play with "use declare/fetch" and "cache size" at first.
> > And mylog output could help you to see where's the problem.
> >
> > Regards,
> >
> > Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq