X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6490552956; Wed, 17 Aug 2005 06:48:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24053-07; Wed, 17 Aug 2005 09:48:30 +0000 (GMT) Received: from smtp.bizmail.net4india.com (smtp.bizmail.net4india.com [202.71.129.102]) by svr1.postgresql.org (Postfix) with ESMTP id 43A6C528F0; Wed, 17 Aug 2005 06:48:26 -0300 (ADT) Received: from [61.11.17.20] (helo=devp20) by smtp.bizmail.net4india.com with smtp (Exim 3.36 #2) id 1E5KbJ-0000Y3-00; Wed, 17 Aug 2005 15:22:50 +0530 Message-ID: <009501c5a311$3706ef20$dd0aa8c0@codecindia.com> From: "Mahesh Shinde" To: Cc: Subject: Data Selection Slow From VB 6.0 Date: Wed, 17 Aug 2005 02:50:45 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0092_01C5A2D6.771DF440" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.048 required=5 tests=[AWL=0.020, HTML_60_70=0.027, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200508/112 X-Sequence-Number: 13681 This is a multi-part message in MIME format. ------=_NextPart_000_0092_01C5A2D6.771DF440 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 toll. To connect To the PostgreSQL I am using *PostgreSQL Win32 ODBC and = OLEDB client drivers 1.0.0.2*=20 The files included are=20 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) =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 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.=20 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=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 = debtor 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 ------=_NextPart_000_0092_01C5A2D6.771DF440 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi
    I am using Postgres = version=20
       =20 *PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc = (GCC) 3.2.2=20 20030222 (Red Hat Linux 3.2.2-5).*
        = for an multy=20 user desktop application using VB 6.0 as a = front end=20 toll.
 
    To connect To the PostgreSQL I am using = *PostgreSQL Win32=20 ODBC and OLEDB client drivers 1.0.0.2*
 
    The files included = are=20
    =    =20 Version 1.0 of the PGW32CLI Installer will install the following file = versions.=20 Files are installed in a separate PGW32CLI directory so should not = conflict with=20 existing            =20 applications.
    =    =20     libpq.dll 8.0.2.5098 = (PostgreSQL=20 library)
            = libintl-2.dll=20 0.11.5.1189 (GNU Text Utils)
            = libiconv-2.dll=20 1.8.1134.7927 (GNU Text Utils)
            = psqlodbc.dll=20 8.0.0.4 (PG ODBC)
            = pgoledb.dll=20 1.0.0.19 (PgOleDB)
            = libeay32.dll=20 0.9.7.f (OpenSSL)
            = ssleay32.dll=20 0.9.7.f (OpenSSL)
   =20
I have server configuration as =
    =    =20     P4 3 GHz HT = Tech
   =20         2 GB DDR RAM,
   =20         Intel Original 875 Chipset=20 Motherboard,
   =20         73 GB  10 K RPM SCSI HDD = x =20 2 Nos.
   =20         Adp SCSI Controller, (You can do = software=20 RAID on it)
   =20         Server Class = Cabinet
   =20
    = Since in the=20 database I have one Major table that Debtor table which is master table = and=20 having around 55 lac records. I have set debtorId as a primary key = having index=20 on it.I am developing a search screen to search a specific debtor=20 info using this table.
 
When I fire a query to = search a=20 debtor id,  it took around 5 seconds to return an answer for a = query=20 whether entered debtor id is present in the database or not using ODBC. = Where as=20 when Explian  the query on the database
 Index Scan using=20 tbmstban_debtorid on tbmstbandetails  (cost=3D0.00..6.01 rows=3D2=20 width=3D143)
   Index Cond: ((debtorid)::text =3D=20 '234'::text)

Query for the=20 search criteria is
 select * from = tbmstdebtordetails=20 where debtorid =3D'234'
 
 Where as when I = am using a like=20 query to search a record starting with debtor id having a characters = then it=20 took around 10-15 sec to return a record set having = records.
query is 
select * from = tbmstdebtordetails where=20 debtorid like '234%'
 
Explain output on the = database
 Index Scan=20 using tbmstban_debtorid on tbmstbandetails  (cost=3D0.00..6.01 = rows=3D2=20 width=3D143)
   Index Cond: ((debtorid)::text =3D=20 '234%'::text)
Thanks & regards,
Mahesh=20 Shinde
---------------------------------------------------------------= ---
Codec=20 Communications (I) Pvt. Ltd.
PUNE (INDIA)
T # = 91-20-24221460/70(Ext=20 43)
Desk No. 25143
Email =96 mahesh.shinde@codecindia.com=
------=_NextPart_000_0092_01C5A2D6.771DF440--