# Visual Basic > Database Development >  Sum() slow in Access SQL, suggestions?

## wengang

Hi all.
I have a data table with one field (PERCENT) that goes out to about 10 decimal places (1.444566433244, 0.0003343434, etc.)
Depending on the query there can be as few as 1 or as many as 2500 records matching the query.

When I ask for just Sum(Percent) as the result, it takes a really long time for Access to give me the result (like 5 seconds for a short list of PERCENTs and longer for a long list.

In VBA, I changed the query to just give me all of the PERCENTS, and then I looped through the records and added them in VBA, and the result is almost instantaneous no matter how long the list.

Is SUM() an inefficient way to add in SQL for Access?

My query is below for reference:
SELECT Sum(Percent) as TotalPercent
FROM tblItemList
WHERE Item in (select Item from tblStats where Instr('{semicolon delimited list of  Item numbers, could be as short as 2 or 3 items or as long as 2500 items}',';' & ID & ';') > 0);

I know there is debate about LIKE and INSTR, but in this case, when VBA does the adding, the result is instantaneous, so I assume this is neglible.
Maybe I'm overlooking something obvious, or a better way to add.
Thanks.

----------


## wqweto

> I know there is debate about LIKE and INSTR, but in this case, when VBA does the adding, the result is instantaneous


The SUM-ing in Access is instantenous too. It's fetching the filtered resultset to work upon which is needlessly convoluted and is probably making the DB engine sweat a bit.

cheers,
</wqw>

----------


## wengang

That's odd.  I ran it over and over.  As written, there is a delay before the result comes back either in VBA or in an Access query.  When I change Sum(Percent) to just Percent, I get the records back instantly.  To me, it's obvious that SUM() is slowing it down.

----------


## wqweto

You can first insert into a temp table and SUM its content to confirm (or not) SUM-ing is slow in MS Access in general.

----------

