Hi,

    I use ODBC drivers to connect PHP and Pervasive sql. The connection
is DSN less. I have created DDFs using the DDF builder in Psql9.
    Now quite a few of these files are very large. Around 24,000
records and 40-50 odd fields. I can access other files that are not
this big. But my connection just times out for queries on the large
tables. Even If I choose only one field from a table with a "Where
coupon_no =1" clause. It still times out. Is there any solution to this
problem?

TIA,
MD

I had  similiar issues with version 7 of pervasive and usually got a
performance boost when i did the following:
Make sure that there is an index built upon any fields that you want to
filter on.
Create a stored procedure with the select statement in it.
Call the stored proceedure from your ODBC connection.
Don't know if the OBBC interface is improved in version 9 or not, but I
stay well away from it and try to use it for very little if at all,
thus the compiled stored proceedure.

-Henry

meghdutt@gmail.com wrote:
> Hi,
> 
>     I use ODBC drivers to connect PHP and Pervasive sql. The connection
> is DSN less. I have created DDFs using the DDF builder in Psql9.
>     Now quite a few of these files are very large. Around 24,000
> records and 40-50 odd fields. I can access other files that are not
> this big. But my connection just times out for queries on the large
> tables. Even If I choose only one field from a table with a "Where
> coupon_no =1" clause. It still times out. Is there any solution to this
> problem?

As mehoss says, what have you got in the way of indicies? With out an
index (eg on coupon_no) when you ask the database for record(s) where
coupon_no = 1 the database is going to have to examine every record
to check the value even if there is only one such record. Adding an
index on coupon_on would make a big difference.

You should spend some time analysing your data and how you are going to
access it to make sure you have the necessary indicies.

We have a couple of tables which are reasonably large with 5million
records in one and 10 in the other. We have set-up indicies on them
and can access any record in a fraction of a second if there SQL
where clause specifies fields which are indexed.

Guy
-- --------------------------------------------------------------------
Guy Dawson                    I.T. Manager              Crossflight Ltd
gnues@crossflight.co.uk

24,000 records is not that many.
I would even expect a table scan to finish in under the timeout time
except on a very slow or busy server.

If it is just a simple select it really should return in the blink of
an eye no matter how large the file.
My guess is that there is no Key in the data file that the SQL engine
can use as an Index.  This can happen if the fields are not defined
properly and does not match up with the actual keys or the key is a
Btrieve NULL key (which is different then a SQL NULL index.

Double check the definition is correct and there is a usable key.



On 31 Jan 2006 16:43:37 -0800, meghdutt@gmail.com wrote:

>Hi,
>
>    I use ODBC drivers to connect PHP and Pervasive sql. The connection
>is DSN less. I have created DDFs using the DDF builder in Psql9.
>    Now quite a few of these files are very large. Around 24,000
>records and 40-50 odd fields. I can access other files that are not
>this big. But my connection just times out for queries on the large
>tables. Even If I choose only one field from a table with a "Where
>coupon_no =1" clause. It still times out. Is there any solution to this
>problem?
>
>TIA,
>MD

Hi There,

Thanks a lot for your answers. I already have an Index on Coupon_No.
(BTW the table as 240,000 records as opposed to my earlier figure of
24,000).

The thing that I noticed is that even in the PCC, it takes really long
to execute the query. Also, the index is a non-duplicate one. So when i
queried for Coupon_No =1 it brought back 3 rows (after a
million-gazillion years i.e.). I havent changed the indices as I am not
allowed to do that from the DDF builder.

The server processor is a little slow but not this slow. Could there be
any other reason why?

MD

If you really have an index on Coupon_No, this should return in a few
milliseconds.  Can you verify a few more things?

1) First, run the Check Database Wizard on the table to be sure that is
passes.  This will ensure that the Btrieve Keys properly match the SQL
indices.  If the indices don't match (or are not there), the Btrieve
key cannot be used.

2) Second, check (with BUTIL -STAT) the Coupon_No key to see if the key
is "Nullable".  This will be a non-blank field (like 0x00) in the NULL
column of the STAT report.  If a key is Nullable in Btrieve, then the
SQL engine may have to skip it when searching for keys due to
possibly-incorrect results showing up.

Saving either of those, post your file definition at the Btrieve
(BUTIL -STAT) and SQL (paste in the Edit Table Design screens) and
we'll see what else might be wrong...
	Goldstar Software Inc.
	Building on Btrieve(R) for the Future(SM)
	Bill Bach 
	BillBach@goldstarsoftware.com
	http://www.goldstarsoftware.com
	*** Sydney: Pervasive.SQL Service & Support Class - 02/27/06 ***
	*** Chicago: Pervasive.SQL Service & Support Class - 03/27/06 ***

meghdutt@gmail.com wrote:

> Hi There,
> 
> Thanks a lot for your answers. I already have an Index on Coupon_No.
> (BTW the table as 240,000 records as opposed to my earlier figure of
> 24,000).
> 
> The thing that I noticed is that even in the PCC, it takes really long
> to execute the query. Also, the index is a non-duplicate one. So when
> i queried for Coupon_No =1 it brought back 3 rows (after a
> million-gazillion years i.e.). I havent changed the indices as I am
> not allowed to do that from the DDF builder.
> 
> The server processor is a little slow but not this slow. Could there
> be any other reason why?
> 
> MD