Re: [PERFORM] Data Selection Slow From VB 6.0

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(dot)shinde(at)codecindia(dot)com <mailto:mahesh(dot)shinde(at)codecindia(dot)com>