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

From Mahesh Shinde
Subject Data Selection Slow From VB 6.0
Date
Msg-id 009501c5a311$3706ef20$dd0aa8c0@codecindia.com
Whole thread Raw
Responses Re: Data Selection Slow From VB 6.0
Re: [PERFORM] Data Selection Slow From VB 6.0
List pgsql-jdbc
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

pgsql-jdbc by date:

Previous
From: Csaba Nagy
Date:
Subject: Re: Performance problem using V3 protocol in jdbc driver
Next
From: Oliver Jowett
Date:
Subject: Re: Data Selection Slow From VB 6.0