Thread: Data Selection Slow From VB 6.0

Data Selection Slow From VB 6.0

From
"Mahesh Shinde"
Date:
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

Re: Data Selection Slow From VB 6.0

From
Chris Mair
Date:

> 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.


>


Re: Data Selection Slow From VB 6.0

From
John A Meinel
Date:
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>



Attachment

Re: [JDBC] Data Selection Slow From VB 6.0

From
Oliver Jowett
Date:
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