Query is not fast

  • Thread starter Thread starter Beto1967 via AccessMonster.com
  • Start date Start date
B

Beto1967 via AccessMonster.com

Hi
I've a table that has one column with 2.5 million entries in the column.
When I create a query to group and get a count of the entries Access locks
up system advises "not responding". What can I do to speed it up? I've the
column in the table "Index yes dup" that hasn't speed up the process I even
removed the index with same results. The data looks like this in the table
(I'm using 2003 NT ) and at home XP 2002
COLUMN
00005838xx0-Funds Transfer
00006674xxx-Address Change
000100000042xxx-Account Maintenance
0001000000889xx-Funds Transfer
000100000121xxx-Address Change
000100000126xxx-Account Maintenanc
000100000132xxx-Address Change
0001-000005xxx3-1-Check Order
0001-000005xxx6-8-Check Order
There are accounts where it only shows up once or it shows up 10x what I
need is to get a count of how many times the account appears Thanks for your
assistant
 
It's not quite clear how you doing your grouping.

are you saying that you actually have one column, and an entry in the column
looks something like

00005838xx0-Funds Transfer
00006674xxx-Address Change
000100000042xxx-Account Maintenance
0001000000889xx-Funds Transfer

etc.?

I assume your goal is to get a count of say how many "funds transfer"
accounts do I have?

How could you possibly do be doing a grouping and sorting on that data as
above? There is absolutely no way you can to get any kind of reasonable
sorting and counts on the above data. there going to have to work today to a
little bit before you attempt to do a grouping and sorting query.

If the data is as above like one column, to get any kind of decent
performance in terms of grouping and sorting, I would suggest that you do
some type of pre processing on the data, before you run the queries. what we
wanted do is pull out that text party information, into a separate column,
then we can use high speed indexing (remember, you can't use sorting and
grouping on part of a text string in of the single column, you have to have
individual text values that can be grouped by.

Hencem I would create a new column called "accounttype."

I would then run a process to split out the above information into two
columns, eg:

CheckInfo AccountType
00005838xx0 Funds Transfer
00006674xxx Address Change
000100000042xxx Account Maintenance
0001000000889xx Funds Transfer

Thus we want to have the above two columns, then you can do a query to Group
by each account type, and we'll be able to use high speed indexing for this
purpose.

So, our first goal than is to take that original column, and split it into
two columns. in fact, just looking at the data, it seems it's also possible
to pull out the cheque number also. How ever since we don't need that other
check number, and just be the account type, then I think we should just
split this into two data collums....

To create the account type column, we just going to the table design mode
and create the AccountType field (make it a standard text field). make sure
an indexes on this this collum (and of course, the index will have to allow
duplicates as your above shows there will be many of each type).

The next step would then be to run an update query to move the account type
from our original column or into this account that column.

we could use:

update nameOfTable set AccountType = mid([checkinfo], instr([checkinfo],"-")
+ 1))

After we run the above update query (I'm assuming we're going to do this on
a backup copy, because those update queries can be pretty nasty, and if you
do this wrong you can mess up the whole table of data).

Once you've achieved the above, we have a 2nd collum here. (you can take a
look at the table after the query...and see if it looks ok). once you have
the second column the way you want, then it's very easy to use a query to
give you a breakdown in summary count of how many acount types you have of
each.

SELECT Sum(1) AS AccountCount FROM nameofTable
GROUP BY AccountType.

The above query should run quite fast, ...I would guess WELL under one
minute to do this....
 
Does your query deal only with the whole field, or are you trying to
group records by part of the field (e.g. by account number or
transaction type)?

if the latter, indexing won't help and it will be slow. You can speed
things up by splitting the single field into two or three (e.g. one
for AccountNum, one for TransactionType) and indexing both or all of
them.
 
John said:
Does your query deal only with the whole field, or are you trying to
group records by part of the field (e.g. by account number or
transaction type)?

if the latter, indexing won't help and it will be slow. You can speed
things up by splitting the single field into two or three (e.g. one
for AccountNum, one for TransactionType) and indexing both or all of
them.
Hi
I've a table that has one column with 2.5 million entries in the column.
Click to expand...
[quoted text clipped - 16 lines]
need is to get a count of how many times the account appears Thanks for your
assistant
Click to expand...
Click to expand...
Thank you so much for assisting me in matter. I followd your advise and have
split the data into 2 cols I ran a append query it runs faster...One more
question what do think about turning the user transaction to no in a action
query ? (I'm working with 2.5 million rows) and even after the split its
still takes a while to run thanks for your assitance
 
Back
Top