pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedData Selection Slow From VB 6.0
4+ messages / 4 participants
[nested] [flat]
* Data Selection Slow From VB 6.0
@ 2005-08-17 09:50 Mahesh Shinde <mahesh.shinde@codecindia.com>
0 siblings, 3 replies; 4+ messages in thread
From: Mahesh Shinde @ 2005-08-17 09:50 UTC (permalink / raw)
To: pgsql-performance; +Cc: pgsql-jdbc@postgresql.org
Hi
I am using Postgres version
*PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5).*
for an multy user desktop application using VB 6.0 as a front end toll.
To connect To the PostgreSQL I am using *PostgreSQL Win32 ODBC and OLEDB client drivers 1.0.0.2*
The files included are
Version 1.0 of the PGW32CLI Installer will install the following file versions. Files are installed in a separate PGW32CLI directory so should not conflict with existing applications.
libpq.dll 8.0.2.5098 (PostgreSQL library)
libintl-2.dll 0.11.5.1189 (GNU Text Utils)
libiconv-2.dll 1.8.1134.7927 (GNU Text Utils)
psqlodbc.dll 8.0.0.4 (PG ODBC)
pgoledb.dll 1.0.0.19 (PgOleDB)
libeay32.dll 0.9.7.f (OpenSSL)
ssleay32.dll 0.9.7.f (OpenSSL)
I have server configuration as
P4 3 GHz HT Tech
2 GB DDR RAM,
Intel Original 875 Chipset Motherboard,
73 GB 10 K RPM SCSI HDD x 2 Nos.
Adp SCSI Controller, (You can do software RAID on it)
Server Class Cabinet
Since in the database I have one Major table that Debtor table which is master table and having around 55 lac records. I have set debtorId as a primary key having index on it.I am developing a search screen to search a specific debtor info using this table.
When I fire a query to search a debtor id, it took around 5 seconds to return an answer for a query whether entered debtor id is present in the database or not using ODBC. Where as when Explian the query on the database
Index Scan using tbmstban_debtorid on tbmstbandetails (cost=0.00..6.01 rows=2 width=143)
Index Cond: ((debtorid)::text = '234'::text)
Query for the search criteria is
select * from tbmstdebtordetails where debtorid ='234'
Where as when I am using a like query to search a record starting with debtor id having a characters then it took around 10-15 sec to return a record set having records.
query is
select * from tbmstdebtordetails where debtorid like '234%'
Explain output on the database
Index Scan using tbmstban_debtorid on tbmstbandetails (cost=0.00..6.01 rows=2 width=143)
Index Cond: ((debtorid)::text = '234%'::text)
Thanks & regards,
Mahesh Shinde
------------------------------------------------------------------
Codec Communications (I) Pvt. Ltd.
PUNE (INDIA)
T # 91-20-24221460/70(Ext 43)
Desk No. 25143
Email - mahesh.shinde@codecindia.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Data Selection Slow From VB 6.0
@ 2005-08-17 10:28 Chris Mair <list@1006.org>
parent: Mahesh Shinde <mahesh.shinde@codecindia.com>
2 siblings, 0 replies; 4+ messages in thread
From: Chris Mair @ 2005-08-17 10:28 UTC (permalink / raw)
To: Mahesh Shinde <mahesh.shinde@codecindia.com>; +Cc: pgsql-performance
> When I fire a query to search a debtor id, it took around 5 seconds
> to return an answer for a query [...]
Are you sure that time is actually spent in the database engine?
Maybe there are DNS resolving issues or something...
Did you try to execute the queries directly on the server from
the psql shell?
Bye, Chris.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Data Selection Slow From VB 6.0
@ 2005-08-17 12:29 Oliver Jowett <oliver@opencloud.com>
parent: Mahesh Shinde <mahesh.shinde@codecindia.com>
2 siblings, 0 replies; 4+ messages in thread
From: Oliver Jowett @ 2005-08-17 12:29 UTC (permalink / raw)
To: Mahesh Shinde <mahesh.shinde@codecindia.com>; +Cc: pgsql-performance; pgsql-jdbc@postgresql.org
Mahesh Shinde wrote:
> Hi
> I am using Postgres version
> **PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc
> (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5).* *
> for an multy user desktop application using VB 6.0 as a front
> end toll.
>
> To connect To the PostgreSQL I am using **PostgreSQL Win32 ODBC and
> OLEDB client drivers 1.0.0.2**
pgsql-jdbc isn't relevant, then -- the JDBC driver is not involved.
-O
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] Data Selection Slow From VB 6.0
@ 2005-08-17 15:18 John A Meinel <john@arbash-meinel.com>
parent: Mahesh Shinde <mahesh.shinde@codecindia.com>
2 siblings, 0 replies; 4+ messages in thread
From: John A Meinel @ 2005-08-17 15:18 UTC (permalink / raw)
To: Mahesh Shinde <mahesh.shinde@codecindia.com>; +Cc: pgsql-performance; pgsql-jdbc@postgresql.org
Mahesh Shinde wrote:
> Hi
...
> To connect To the PostgreSQL I am using **PostgreSQL Win32 ODBC and
> OLEDB client drivers 1.0.0.2**
>
...
> Since in the database I have one Major table that Debtor table which
> is master table and having around 55 lac records. I have set debtorId as
> a primary key having index on it.I am developing a search screen to
> search a specific debtor info using this table.
>
> When I fire a query to search a debtor id, it took around 5 seconds to
> return an answer for a query whether entered debtor id is present in the
> database or not using ODBC. Where as when Explian the query on the
> database
> Index Scan using tbmstban_debtorid on tbmstbandetails (cost=0.00..6.01
> rows=2 width=143)
> Index Cond: ((debtorid)::text = '234'::text)
Are you checking this from the VB App? Or just going onto the server and
running psql? (I'm guessing there is some way to run a flat query using
VB. In which case you can just have the query run EXPLAIN ANALYZE, the
return value is just the text, one line after another.)
What I'm thinking is that it might be a locale/encoding issue.
What is the encoding of your database? And what is the default locale
and the locale that you are connecting as?
Can you give us the "EXPLAIN ANALYZE" output so that we can see if the
planner is doing what it thinks it is?
It certainly sounds like either it is always doing a sequential scan, or
something else is going on. 5 sec is a really long time for the type of
query you are doing.
Oh, and can you run the win32 psql client to see if it might be ODBC
which is causing the problem?
John
=:->
>
> Query for the search criteria is
> *select * from tbmstdebtordetails where debtorid ='234'*
>
> Where as when I am using a like query to search a record starting with
> debtor id having a characters then it took around 10-15 sec to return a
> record set having records.
> query is
> *select * from tbmstdebtordetails where debtorid like '234%'*
>
> Explain output on the database
> Index Scan using tbmstban_debtorid on tbmstbandetails (cost=0.00..6.01
> rows=2 width=143)
> Index Cond: ((debtorid)::text = '234%'::text)
> Thanks & regards,
> Mahesh Shinde
> ------------------------------------------------------------------
> Codec Communications (I) Pvt. Ltd.
> PUNE (INDIA)
> T # 91-20-24221460/70(Ext 43)
> Desk No. 25143
> Email – mahesh.shinde@codecindia.com <mailto:mahesh.shinde@codecindia.com>
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-08-17 15:18 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-17 09:50 Data Selection Slow From VB 6.0 Mahesh Shinde <mahesh.shinde@codecindia.com>
2005-08-17 10:28 ` Chris Mair <list@1006.org>
2005-08-17 12:29 ` Oliver Jowett <oliver@opencloud.com>
2005-08-17 15:18 ` John A Meinel <john@arbash-meinel.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