Access query taking too long

Question

I am attempting to work with an .mdb file of about 15k+ entries. I'm using ado in vbscript and asp query the database. The query I am making looks something like this. x in this case is just a variable that holds the current date in mm/dd/yyyy form.

rs.Open "SELECT [Item Number], [Qty], [Delivery Point] 
    FROM [ItemBoard] 
    WHERE format([Submitted Date/Time], 'mm/dd/yyyy') = '"&x&"' 
      AND [OrderUrgency] = 'Rush' 
      AND ([Delivered] is NOT NULL OR [Delivered Short] is NOT NULL)", conn

My issue is that this query takes upwards of one minute to complete, which is way too high. What's more, if I change the Where clause to only have one of the 3 statments rather than all 3 of them, the time to completion of the query only goes down to about 50-55 seconds. Other than the structure of the database itself, are there any likely candidates for causes to this issue?

With `WHERE format([Submitted Date/Time], 'mm/dd/yyyy') =`, the db engine can not use indexed retrieval for `[Submitted Date/Time]`. If that field is Date/Time datatype, do a date comparison instead of a string comparison and make sure the field is indexed. If that is not sufficient, show us what `x` is. — HansUp, Aug 02 '16 at 15:03
@HansUp I can add clarification to x in my post and will certainly make this improvement, but as I said above even if I leave this out of the where clause I still get 50-55 second completion time. — Naiya55, Aug 02 '16 at 15:09
Aha. When you said "only have one of the 3 statments", you didn't say which one. Then use `[Submitted Date/Time]=Date()` and make sure both `[Submitted Date/Time]` and `[OrderUrgency]` are indexed. If that's not sufficient, I would switch from ADO to DAO. — HansUp, Aug 02 '16 at 15:16
@Andre good question. Does this slowness happen when the accdb file is on your (OP) local computer? — Brad, Aug 02 '16 at 15:51
@Brad The file is on a network. I tried a running it on a local copy of the file and got the time down to 30-35 seconds which is definitely better. Unfortunately, this file is updated several times a minute on the server and it isn't much use to me if it isn't up to date. At the moment, I don't really see a way of getting around this. — Naiya55, Aug 02 '16 at 16:30
How long does `SELECT [Item Number], [Qty], [Delivery Point] FROM [ItemBoard] ` take? How about `SELECT top 1 [Item Number], [Qty], [Delivery Point] FROM [ItemBoard] ` — Brad, Aug 02 '16 at 16:34
@Brad `Select top 1` is more is less instant. `Select` takes about 20. These times are for the local copy. — Naiya55, Aug 02 '16 at 16:48
@Brad The local copy has indexes I put in while messing around with it that the actual db doesn't. This shouldn't effect the times because I'm taking the whole datebase and then only the first entry. Indexing only really helps when you are looking for specific rows rather than the whole table, correct? — Naiya55, Aug 02 '16 at 16:54
Or disable index, try to exclude other "Or" , be sure that fields used in where condition are indexed and test. — M.Hassan, Aug 02 '16 at 17:31
@M.Hassan Unfortunately short of doing a second query immediately after the first, I don't think there is a way to get rid of this particular OR — Naiya55, Aug 02 '16 at 17:38
@HansUp will comparing a datetime to a date work? I had thought the time part of the datetime would throw the comparison off. — Naiya55, Aug 02 '16 at 17:43
we remove Or just for test. Is Or the root performance problem. — M.Hassan, Aug 02 '16 at 17:44
Create query for this select statement with parameters. it's better to use query to avoid sql interpreting. — M.Hassan, Aug 02 '16 at 17:50
So with the date change and the OR change, my query times are in the seconds, if not instant — Naiya55, Aug 02 '16 at 17:53
Since `[Submitted Date/Time]` includes a time of day other than midnight, and you want all of those from today's date regardless of time of day, you can use this condition: `[Submitted Date/Time] >= Date() AND [Submitted Date/Time] < DateAdd("d", 1, Date())` With the field indexed, Access can quickly determine which rows satisfy that condition. — HansUp, Aug 02 '16 at 18:29
Personally I'd be interested in seeing the difference in execution time if you converted your SQL to a parameter query using @Hansup condition and then converted your recordsets to DAO. You're dealing with an mdb file not SQL Server or Oracle, and I've always found DAO to be faster than ADO when working with JET databases. — dbmitch, Aug 02 '16 at 19:20
*"So with the date change and the OR change, my query times are in the seconds, if not instant"* -- so the issue is solved? What have you changed in the end? -- This question has become somewhat confusing... — Andre, Aug 03 '16 at 09:32

score 0 · Accepted Answer · answered Aug 03 '16 at 20:02

While all suggestions improved my runtime, this is what gave me the best time without changing what I was looking for.

rs.Open "SELECT [Item Number], [Qty], [Delivery Point] 
FROM [ItemBoard] 
WHERE [Submitted Date/Time] >= Date() AND [Submitted Date/Time] < DateAdd('d', 1, Date()) 
  AND [OrderUrgency] = 'Rush' 
  AND ([Delivered] is NOT NULL OR [Delivered Short] is NOT NULL)"

I had been hoping it was the network which was slowing me down, but that doesn't seem to be the case. Or at least it wasn't the primary cause of the high query time I was getting. Guess I'll have to go back to asking for permission to clean up the actual database and add indexes.

Access query taking too long

1 Answers1