Thread: slow odbc access....

slow odbc access....

From
Sanjay Arora
Date:
I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
Application accessing the DB through postgrSQL ODBC driver v. 6.50 on a 10
mbps ethernet network using four eight port cascaded hubs. PG is recompiled
with BLCKSZ 32 kb.

Thanks to all of you I seem to be making some progress in porting my app to
pg backend. I am facing a peculiar problem.

SQL queries through the odbc are extremely slow. Its so noticable that you
do not even need to bechmark the query execution time. I used simple select
query using where....no joins etc. Same query on psql through telnet was
noticably faster. At first I thought it was a Network problem and tested
the query at night with only the server & workstation switched on at night.
Problem was not resolved so I ruled out NW issues.

Another thing that is peculiar is that while the query is being executed
that the HDD light on the WinNT client machine goes bonkers. It seems that
the ODBC driver is caching something on the local hdd. I do have the odbc
log on but the hdd activity seems to be too intensive for that. Does ODBC
cache recordsets to local hdds or something like that or is poor suspicious
me being unfair to poor odbc logs? ;-))

Are there any other ODBC drivers on Win32 for PG that I can try? Any other
issues that I should study?

BTW, when would the PG 7.1 be coming?

Once again, I would like to extend my thanks to all who helped me.

With regards.
Sanjay.



Re: slow odbc access....

From
"Bryan Bateman"
Date:
VB6 uses the "jet" interface.  I had some similar problems not long ago with
Oracle.  They almost scrapped the project until we learned how to tune the
jet engine for the application.

If you use Access and link the table, they run the query from there are you
getting the same problems?

There are some registry hacks for jet that may help.  What MS OS are you on?
Is the ODBC manager up to date?  If you are on NT 4.0 what service pack
level are you on?

How large is the result from the query?

http://www.microsoft.com/data/

has a lot of resources to troubleshoot ODBC problems.

Start the postmaster up with with tracing and also turn tracing on in the
ODBC manager.  Compare and contrast these files after the query to see if
anything funky shows up.  Trace logs can be your good friends when it comes
to ODBC.

For the record I am running ODBC Manager 3.520.5303.2 and the Insight
Distribution Systems driver 6.50 for Postgresql on a Win2k machine.

----- Original Message -----
From: "Sanjay Arora" <sk@pobox.com>
To: <pgsql-interfaces@postgresql.org>
Sent: Tuesday, November 28, 2000 11:33 PM
Subject: [INTERFACES] slow odbc access....


> I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
> Application accessing the DB through postgrSQL ODBC driver v. 6.50 on a 10
> mbps ethernet network using four eight port cascaded hubs. PG is
recompiled
> with BLCKSZ 32 kb.
>
> Thanks to all of you I seem to be making some progress in porting my app
to
> pg backend. I am facing a peculiar problem.
>
> SQL queries through the odbc are extremely slow. Its so noticable that you
> do not even need to bechmark the query execution time. I used simple
select
> query using where....no joins etc. Same query on psql through telnet was
> noticably faster. At first I thought it was a Network problem and tested
> the query at night with only the server & workstation switched on at
night.
> Problem was not resolved so I ruled out NW issues.
>
> Another thing that is peculiar is that while the query is being executed
> that the HDD light on the WinNT client machine goes bonkers. It seems that
> the ODBC driver is caching something on the local hdd. I do have the odbc
> log on but the hdd activity seems to be too intensive for that. Does ODBC
> cache recordsets to local hdds or something like that or is poor
suspicious
> me being unfair to poor odbc logs? ;-))
>
> Are there any other ODBC drivers on Win32 for PG that I can try? Any other
> issues that I should study?
>
> BTW, when would the PG 7.1 be coming?
>
> Once again, I would like to extend my thanks to all who helped me.
>
> With regards.
> Sanjay.
>



Re: slow odbc access....

From
"Byron Nikolaidis"
Date:
I have seen really really slow access AND the hard drive light on constantly
when the odbc trace log is on.   This has always been the case.   I think MS
goes out of there way to make sure everything gets logged so it goes right
to disk for each odbc call, with no buffering.

Byron

----- Original Message -----
From: Sanjay Arora <sk@pobox.com>
To: <pgsql-interfaces@postgresql.org>
Sent: Tuesday, November 28, 2000 6:33 PM
Subject: [INTERFACES] slow odbc access....


> I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
> Application accessing the DB through postgrSQL ODBC driver v. 6.50 on a 10
> mbps ethernet network using four eight port cascaded hubs. PG is
recompiled
> with BLCKSZ 32 kb.
>
> Thanks to all of you I seem to be making some progress in porting my app
to
> pg backend. I am facing a peculiar problem.
>
> SQL queries through the odbc are extremely slow. Its so noticable that you
> do not even need to bechmark the query execution time. I used simple
select
> query using where....no joins etc. Same query on psql through telnet was
> noticably faster. At first I thought it was a Network problem and tested
> the query at night with only the server & workstation switched on at
night.
> Problem was not resolved so I ruled out NW issues.
>
> Another thing that is peculiar is that while the query is being executed
> that the HDD light on the WinNT client machine goes bonkers. It seems that
> the ODBC driver is caching something on the local hdd. I do have the odbc
> log on but the hdd activity seems to be too intensive for that. Does ODBC
> cache recordsets to local hdds or something like that or is poor
suspicious
> me being unfair to poor odbc logs? ;-))
>
> Are there any other ODBC drivers on Win32 for PG that I can try? Any other
> issues that I should study?
>
> BTW, when would the PG 7.1 be coming?
>
> Once again, I would like to extend my thanks to all who helped me.
>
> With regards.
> Sanjay.
>



RE: slow odbc access....

From
Dave Page
Date:

> -----Original Message-----
> From: Sanjay Arora [mailto:sk@pobox.com]
> Sent: 28 November 2000 23:34
> To: pgsql-interfaces@postgresql.org
> Subject: [INTERFACES] slow odbc access....
> 
> 
> I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
> Application accessing the DB through postgrSQL ODBC driver v. 
> 6.50 on a 10
> mbps ethernet network using four eight port cascaded hubs. PG 
> is recompiled
> with BLCKSZ 32 kb.

Try to use ADO to access the database rather than DAO or RDO as it's much
quicker. Make sure you use ForwardOnly recordsets wherever possible and only
use Dynamic if you absolutely have to.

Another thing to remember - Jet is evil don't let it anywhere near your
project if you can help it!!

HTH, regards,
Dave.


Re: slow odbc access....

From
Tibor Laszlo
Date:
> Another thing that is peculiar is that while the query is being executed
> that the HDD light on the WinNT client machine goes bonkers. It seems that
> the ODBC driver is caching something on the local hdd. I do have the odbc
> log on but the hdd activity seems to be too intensive for that. Does ODBC
> cache recordsets to local hdds or something like that or is poor suspicious
> me being unfair to poor odbc logs? ;-))

May the MS ODBC trace is the problem. I had the same problem. Turn it off, and
the HDD led won't light. The ODBC driver's own log is fast, but I turn it on
only when I really need it.

Regards, Tibor

-- 
Tibor Laszlo
ltibor@mail.tiszanet.hu


RE: slow odbc access....

From
"Francis Solomon"
Date:
Hi,

> VB6 uses the "jet" interface.  I had some similar problems
> not long ago with
> Oracle.  They almost scrapped the project until we learned
> how to tune the
> jet engine for the application.

It doesn't have to.
I write apps in VB6 (in common, I'm sure, with other people on this
list) which go VB6->ADO->ODBC->Postgres.
This avoids the JET layer completely and the performance is considerably
better. Yes, you lose some of the built-in JET functions this way, but
... frankly, I've not missed them. I've even forgotten which functions
I've lost. PostgreSQL itself has a rich set of built-in functions that
I'm sure will replace most of these - and for the rest, smart use of
queries and maybe a few extra lines of code here or there in the client
app will make a pretty good substitute.

Incidentally, I've also done VB6->RDO->ODBC->Postgres in the past, and
that works just fine too.

> > Another thing that is peculiar is that while the query is
> being executed
> > that the HDD light on the WinNT client machine goes
> bonkers. It seems that
> > the ODBC driver is caching something on the local hdd. I do
> have the odbc
> > log on but the hdd activity seems to be too intensive for
> that. Does ODBC
> > cache recordsets to local hdds or something like that or is poor
> suspicious
> > me being unfair to poor odbc logs? ;-))

There are two different logs. There's the psqlodbc log (quite
lightweight unless you're running HUGE numbers of queries) which logs to
c:\ and shouldn't have anything but a negligible impact on your disk.
The ODBC Driver Manager in control panel also has a logging option,
which logs a LOT more information and may be responsible for thrashing
your disk. I seem to remember that it also logs to c:\ by default, so
have a look in here for stray log files. IIRC the file is called
SQL.log.

> > BTW, when would the PG 7.1 be coming?

Someone mentioned something about Feb 2001 very recently ...

Hope this helps.

Francis Solomon



Re: slow odbc access....

From
"Neil P Davis"
Date:
Hello,
It has been my experience (and this is documented in MSDN) that ODBC Tracing
will KILL your performance! Turn that off and try it again. It is for
debugging only...

L8,
Neil
----- Original Message -----
From: "Byron Nikolaidis" <byron.nikolaidis@home.com>
To: <pgsql-interfaces@postgresql.org>; "Sanjay Arora" <sk@pobox.com>
Sent: Tuesday, November 28, 2000 7:36 PM
Subject: Re: [INTERFACES] slow odbc access....


> I have seen really really slow access AND the hard drive light on
constantly
> when the odbc trace log is on.   This has always been the case.   I think
MS
> goes out of there way to make sure everything gets logged so it goes right
> to disk for each odbc call, with no buffering.
>
> Byron
>
> ----- Original Message -----
> From: Sanjay Arora <sk@pobox.com>
> To: <pgsql-interfaces@postgresql.org>
> Sent: Tuesday, November 28, 2000 6:33 PM
> Subject: [INTERFACES] slow odbc access....
>
>
> > I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
> > Application accessing the DB through postgrSQL ODBC driver v. 6.50 on a
10
> > mbps ethernet network using four eight port cascaded hubs. PG is
> recompiled
> > with BLCKSZ 32 kb.
> >
> > Thanks to all of you I seem to be making some progress in porting my app
> to
> > pg backend. I am facing a peculiar problem.
> >
> > SQL queries through the odbc are extremely slow. Its so noticable that
you
> > do not even need to bechmark the query execution time. I used simple
> select
> > query using where....no joins etc. Same query on psql through telnet was
> > noticably faster. At first I thought it was a Network problem and tested
> > the query at night with only the server & workstation switched on at
> night.
> > Problem was not resolved so I ruled out NW issues.
> >
> > Another thing that is peculiar is that while the query is being executed
> > that the HDD light on the WinNT client machine goes bonkers. It seems
that
> > the ODBC driver is caching something on the local hdd. I do have the
odbc
> > log on but the hdd activity seems to be too intensive for that. Does
ODBC
> > cache recordsets to local hdds or something like that or is poor
> suspicious
> > me being unfair to poor odbc logs? ;-))
> >
> > Are there any other ODBC drivers on Win32 for PG that I can try? Any
other
> > issues that I should study?
> >
> > BTW, when would the PG 7.1 be coming?
> >
> > Once again, I would like to extend my thanks to all who helped me.
> >
> > With regards.
> > Sanjay.
> >
>
>