Database problems

Im using DAO to access a database file which contains 92,000 records.  Problem is thats it seems very slow performance wise.
The below line causes a 2-5 second pause which is fairly bad.  Is there any way to improve things?  Would using more tables instead oh having 92,000 records help?
Or would ADO be better?

    Set myrs = db.OpenRecordset(strsql, dbOpenDynaset) ' Causes BIG Pause
Who is Participating?
JackOfPHConnect With a Mentor Commented:
Try to change the query to this...
strsql = "select * from table1 where table1." & sfield & " = '" & sval & "' ORDER BY filename ASC"
Guy Hengel [angelIII / a3]Billing EngineerCommented:
are you trying to get all 92K records using this?
what does the query look like?
does the table have appropriate indexes?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ps: what database?
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

gerrymcdAuthor Commented:
No maybe just 100-300 at a time

typical query

        strsql = "select * from table1 where table1." & sfield & " = '" & sval & "'" '"ORDER BY filename ASC"

i made an index on the filename and path fields it seems to have speeded up the results to some degree.
gerrymcdAuthor Commented:
Typical sql:

select * from table1 where table1.path like '*images\holidays*' AND collectionname = 'holidayphotos2006'  ORDER BY filename ASC

i basically have a collections of images which are contain in a folder and subffolders, theres are image collections (say holiday photos,birthdays,art)

ive put all them in the one table. should i put each collection of images in its own table?

It is better not to save the image in your database... instead just save the image path in the database...

gerrymcdAuthor Commented:
the image is not being saved in the database, sry if that was unclear.
First of all,
What DB is this? If it's a DB Server, and not Access, it might help to put the SQL in a stored procedure.

Secondly, Is the database running on the same machine as your app? If not; switch to ADO.

gerrymcdAuthor Commented:
ive made each "group of files a collection of images" be made in a new table instead of 1 big tanle with 90,000+ entries it seems to have speeded up things but it makes things a bit akward.  any other tips or ideas?
All Courses

From novice to tech pro — start learning today.