pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
odbc/ado problems
5+ messages / 4 participants
[nested] [flat]

* odbc/ado problems
@ 2004-08-31 17:26  Kroh István <istvan.kroh@pomme.hu>
  0 siblings, 2 replies; 5+ messages in thread

From: Kroh István @ 2004-08-31 17:26 UTC (permalink / raw)
  To: pgsql-performance; pgsql-odbc@postgresql.org

Hi all!

I'm new here, so hello to everybody!

I'm in a deep truble using postgesSQL 7.2.0 on a low-end pc with SUSE 8. I'm
using some databases from that pc through odbc (7.3.200). Until now i had no
problems with this solution, everithing worked fine. But today i wrote a
small app, that converts/copies some data from a database to an other
database.

During this work i wrote a simple query as follows:
select pers_driving_license from person where pers_id=23456

This should return a single varchar(20) field. Running this query over
ADO/ODBC from a Delphi app tooks 50-100 secs. If i run this from pgAdmin II.
it takes some msecs.

The output of explain is:
Index Scan using person_id_index on person (cost=0.00..3.14 rows=1 width=4)

Any idea?

Thanks in advance: steve




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: odbc/ado problems
@ 2004-08-31 17:33  Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Merlin Moncure @ 2004-08-31 17:33 UTC (permalink / raw)
  To: Kroh István <istvan.kroh@pomme.hu>; +Cc: pgsql-performance

> I'm new here, so hello to everybody!
> 
> I'm in a deep truble using postgesSQL 7.2.0 on a low-end pc with SUSE 8.
> I'm
> using some databases from that pc through odbc (7.3.200). Until now i had
> no
> problems with this solution, everithing worked fine. But today i wrote a
> small app, that converts/copies some data from a database to an other
> database.
> 
> During this work i wrote a simple query as follows:
> select pers_driving_license from person where pers_id=23456
> 
> This should return a single varchar(20) field. Running this query over
> ADO/ODBC from a Delphi app tooks 50-100 secs. If i run this from pgAdmin
> II.
> it takes some msecs.

Question: what is your Delphi database driver?  If you are using the BDE, that might be your problem.  Try installing and using the ZeosLib toolkit.

http://www.zeoslib.org

Merlin



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: odbc/ado problems
@ 2004-08-31 19:00  Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Merlin Moncure @ 2004-08-31 19:00 UTC (permalink / raw)
  To: Pomme Bt. <info@pomme.hu>; +Cc: pgsql-performance

> thanks for the quick answer! My db driver is the native MS ADO, and
from
> Delphi i use the AODExpress components which are wrapper classes to
reach
> the ActiveX components from delhpi. The strange behaviour of that
query
> is,
> that all other queries executed in this environment are running fast,
> except
> this one. First there was any index on that table (appr. 40.000
records),
> and i thought that maybe this is my problem. Then i made this index,
but
> this hasn't solved my problem.
> Now i think that maybe the odbc driver makes something with my query?
Can
> this happen?

Possible.  I would turn on statement logging on the server and make sure
the query is the way you wrote it in the app.  The driver might be doing
something like pulling all the data and attempting a client side filter.
Otherwise you may be looking at a casting problem of some sort.

To turn on logging, set statement log to 'all' in your postgresql.conf
file.  You may need to start the server manually so you can determine
where the log goes (logging to terminal is often the easiest to work
with).

If you are writing Delphi applications, you really should check out
Zeos.  It utilizes native drivers to connect to the database...it's
really, really fast and supports all the Delphi controls (and free!).

Merlin



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: odbc/ado problems
@ 2004-09-01 08:19  Markus Donath <md-postgres-donath@netapps.de>
  parent: Kroh István <istvan.kroh@pomme.hu>
  1 sibling, 0 replies; 5+ messages in thread

From: Markus Donath @ 2004-09-01 08:19 UTC (permalink / raw)
  To: pgsql-odbc@postgresql.org

hello,

did you use a read only / forward only cursor (adOpenForwardOnly) ?
this is the fastest way to retreive data, other cursor types are much slower
with postgres odbc.

bye,
Markus Donath.


"Kroh István" <istvan.kroh@pomme.hu> schrieb im Newsbeitrag
news:005a01c48f7f$9ff8ae70$0102a8c0@pomme001...
> Hi all!
>
> I'm new here, so hello to everybody!
>
> I'm in a deep truble using postgesSQL 7.2.0 on a low-end pc with SUSE 8.
I'm
> using some databases from that pc through odbc (7.3.200). Until now i had
no
> problems with this solution, everithing worked fine. But today i wrote a
> small app, that converts/copies some data from a database to an other
> database.
>
> During this work i wrote a simple query as follows:
> select pers_driving_license from person where pers_id=23456
>
> This should return a single varchar(20) field. Running this query over
> ADO/ODBC from a Delphi app tooks 50-100 secs. If i run this from pgAdmin
II.
> it takes some msecs.
>
> The output of explain is:
> Index Scan using person_id_index on person (cost=0.00..3.14 rows=1
width=4)
>
> Any idea?
>
> Thanks in advance: steve
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: odbc/ado problems
@ 2004-09-02 10:46  Raymond O'Donnell <rod@iol.ie>
  parent: Kroh István <istvan.kroh@pomme.hu>
  1 sibling, 0 replies; 5+ messages in thread

From: Raymond O'Donnell @ 2004-09-02 10:46 UTC (permalink / raw)
  To: Kroh Istv n <istvan.kroh@pomme.hu>; pgsql-odbc@postgresql.org

On 31 Aug 2004 at 19:26, Kroh Istv n wrote:

> This should return a single varchar(20) field. Running this query over
> ADO/ODBC from a Delphi app tooks 50-100 secs. If i run this from
> pgAdmin II. it takes some msecs.

I have found instantiating and using the ADO objects directly to be
much faster than using the TADOConnection and TADOQuery components
that come with Delphi. - This is in web apps where I'd construct the
SQL and execute it with the Connection object.

--Ray.

-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@iol.ie                          Galway Cathedral Recitals
-------------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2004-09-02 10:46 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-31 17:26 odbc/ado problems Kroh István <istvan.kroh@pomme.hu>
2004-09-01 08:19 ` Markus Donath <md-postgres-donath@netapps.de>
2004-09-02 10:46 ` Raymond O'Donnell <rod@iol.ie>
2004-08-31 17:33 Re: odbc/ado problems Merlin Moncure <merlin.moncure@rcsonline.com>
2004-08-31 19:00 Re: odbc/ado problems Merlin Moncure <merlin.moncure@rcsonline.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox