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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
ps: what database?
ASKER
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.
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.
ASKER
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?
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...
ASKER
the image is not being saved in the database, sry if that was unclear.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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?
what does the query look like?
does the table have appropriate indexes?