Queries on large database run very slowly

[PaulCooper]

I have a largish (3.2M record) database with a couple of 255 characture fields (some full) and some other data. I do have some indexing switched on.

Running queries is VERY slow and sometimes I get "running out of virtual memory" error messages.

Looking at the system monitor only 11% of the processor power is being used and only 400Mb of the 1,250Mb available RAM is being used.

If either were full I could understand the lack of speed. Where is the bottleneck and/or how do I make Access (2000 Win NT SP3) use the resources avaiolable to it?

 

[PHV]

For me the virtual memory is the Page file on disk ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jsteph]

Is the .mdb on the network? How complex are the queries? Are the WHERE clauses using the indexes you created?
Post a sample sql with some index info and we can see if it's an indexing issue, which I think it is.

I've had .mdbs with multi-million record counts on a network that returned query results very quickly, as long as the tables were indexed properly and the queries were designed correctly.
--Jim

 

[PaulCooper]

Thanks PHV.

jsteph; .mdb is on the D drive ~ a local drive with about 15Gb spare space.

SELECT DISTINCT tblFilesMaster.FileName, tblFilesMaster.Filesize, tblFilesMaster.LastAccessed, tblFilesMaster.path, tblFilesMaster.LastWritten, tblFilesMaster.Extension
FROM tblFilesMaster
WHERE (((tblFilesMaster.Filesize)>1000000) AND ((tblFilesMaster.LastAccessed)<#1/1/2005#))
ORDER BY tblFilesMaster.FileName, tblFilesMaster.Filesize DESC;

FileSize and LastAccessed are indexed.

Ideally I only want to see results where there are duplicate filenames but that is a step too far for now

 

[PHV]

Remove the DISTINCT predicates.
Create a composite index on (FileName ASC, Filesize DESC)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886