Recordset Open taking a long time

[mahmed1]

Hi Lovely People

i have a database which is around 100kb in size

Within that i have table called Tbl_All_Data

If i run a simple query like this in Access - its quick
SELECT date_, dept, sum(total)
FROM tbl_Incoming
WHERE date_ >= #01/04/2020 AND date_ <= #30/04/2020
GROUP BY date_, dept

takes around 4-5 seconds

now if i run this same query in Excel using open recordset it takes around 4-5 minutes to run

rs.Open sqlString is the part it takes ages

the SQL string is exactly the same as I posted but put into the variable sqlString

so nothing has changed and my excel file is small and all other SQL statements etc run supee quuick - its just this from excel that takes long

is there anything you can advise i add to speed it up?

 

[mahmed1]

Hi Xenou

couple of questions that you may advise if this is best approach

1) If I change the cursor location to Client would that be faster?

2) When I add an index on table where there are no duplicates but duplicates in data table - if I apply Index (No duplicates) on a field in this lookup table (will that ignore duplicates in data table or is that not the case

Is Index (No Duplicates) faster than Index (Yes Duplicates)?

3) Is it quicker to load results into an array and then paste to sheet or should it be quick enough with copy from
Recordset?

 

[xenou]

1. I don't think so, but you could try it. I usually use the default whatever it is without setting it explicitly but in caes where I see it being set I usually see people using client so maybe its better!
2. You should use no duplicates on an index only when you want no duplicates allowed in that column (however, you can have nulls). Shouldn't affect performance unless you have a lot of duplicate values in the column, but in that case you don't have a choice since you obviously couldn't use no duplicates in that case. So use whatever index is appropriate and see if it helps or not.
3. Copy from recordset should be equivalent to copying from an array, as far as I know.

Note that your data table is large enough (given the many columns) to be noticeably slow based on just the amount of data. You will have to do some trial and error - see if smaller queries run more quickly, and determine if the amount of data alone is the key factor or not. Try to rule out network speed (run queries on local databases with local data) and try to rule out data fragmentation by compacting your database after any significant data changes (especially if there has been a large number of deletes on data in the tables).