Data Selection Slow From VB 6.0 - Mailing list pgsql-bugs

From Mahesh Shinde
Subject Data Selection Slow From VB 6.0
Date
Msg-id 003701c59dd0$5c0a6270$dd0aa8c0@codecindia.com
Whole thread Raw
Responses Re: Data Selection Slow From VB 6.0
List pgsql-bugs
Hi
    I am using Postgres version=20
        *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).*=20
        for an multy user desktop application using VB 6.0 as a front end t=
oll.

    To connect To the PostgreSQL I am using *PostgreSQL Win32 ODBC and OLED=
B client drivers 1.0.0.2*=20

    The files included are=20
        Version 1.0 of the PGW32CLI Installer will install the following fi=
le 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)
=20=20=20=20
I have server configuration as=20
            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
=20=20=20=20
    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 pr=
imary key having index on it.I am developing a search screen to search a sp=
ecific debtor info using this table.=20

When I fire a query to search a debtor id,  it took around 5 seconds to ret=
urn an answer for a query whether entered debtor id is present in the datab=
ase or not using ODBC. Where as when Explian  the query on the database=20
 Index Scan using tbmstban_debtorid on tbmstbandetails  (cost=3D0.00..6.01 =
rows=3D2 width=3D143)
   Index Cond: ((debtorid)::text =3D '234'::text)

Query for the search criteria is=20
 select * from tbmstdebtordetails where debtorid =3D'234'=20

 Where as when I am using a like query to search a record starting with deb=
tor id having a characters then it took around 10-15 sec to return a record=
 set having records.
query is=20
select * from tbmstdebtordetails where debtorid like '234%'=20

Explain output on the database
 Index Scan using tbmstban_debtorid on tbmstbandetails  (cost=3D0.00..6.01 =
rows=3D2 width=3D143)
   Index Cond: ((debtorid)::text =3D '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=

pgsql-bugs by date:

Previous
From: "Magnus Hagander"
Date:
Subject: Re: BUG #1818: ODBC: Wrong encoding when reading string coumns
Next
From: Oliver Jowett
Date:
Subject: Re: BUG #1800: "unexpected chunk number" during pg_dump