August 4th, 2012, 07:38 AM
Database (dbase IV) Query too slow, anything I can do to speed it up?
I have a database I'm querying using a DSN-less ADODB connection (have tried DSN, pretty much same result, DSN-less seems a tad bit faster).
Anyhow, I am querying a dbase IV database with several NDX files. I have them setup correctly in an inf file (I know it's done correctly because the NDX files are now updated correctly when doing an insert).
The problem is that the page takes too long to load. It takes about 7 seconds to perform the query and during that time the server's CPU is at 100%, making other requests wait.
My query looks like:
and I'm using odbc_exec on that query (also, doing this in PHP)
$attach_query="SELECT ATCHDATE, FILENAME, CATEGORY, DESC FROM attach WHERE REC='$code'";
The strange thing is, whether I have the inf file with the ndx files in it - it has no effect on the speed of the query...However, the inserts I have do not work fully unless the inf is there (I am connecting to the databases of an application we use at work).
There are about 1.6 million records in the database, when i count(*) the rows as 425750 so thats another reason i know the ndx files are working (thats what opening the dbf in access says when the inf is present) - is there anything else I can do to speed up this query?
Thanks you in advance for any advice!
Last edited by jdgower; August 4th, 2012 at 07:58 AM.