SLOW response Query

  • Thread starter Thread starter ac
  • Start date Start date
A

ac

Dear all,

I have a database table. I created a SQL query that JOIN 2 tables and perfom
calculations on the fields (ave, sum, total) and presented the
results to the users. My records are very big, around close to 1 Gb, so it
runs very slowly each time users run the query, it takes almost 1 hour
to create 1 query result.

how can i improve the speed? by scheduling the query with calculations into
another table using append query and let the users query
with new the table without triggering all the calculations since it's done
by the scheduled append uery?
or use a backend database example MySQL? I belive the speed will still be
the same if i use the backend SQL database.

Hope someone can shed some light here.

thanks,
ac.
 
Hard to say if the query could be improved without seeing the query and knowing
the structure of your database or at least the tables involved.

Going to a backend database won't improve the speed much (if at all) unless you
have the query run on the server.

Normally, I would say an hour to run a query would be much too long. I would
look at indexes and the query itself. With that many records you might be
running into memory problems and Access may be using virtual memory to handle
the size of the data.
 
Thanks for your interest and time looking into this. here some more info:

Job_Table
JobID DateStart DateEnd Duration(Min) UserName
Caller_Num JobType

1 30-Jul-2003 30-Jul-2003 20 1234
886932819590 1
12:00:00 PM 12:20:00 PM

2 31-Jul-2003 31-Jul-2003 19 3452
65932819590 2
12:00:00 PM 12:19:00 PM

Rate_Table
CustomerID RegisteredTelNum CustomerRate
1234 886932819590 1.5
3452 65932819590 1.2

My Query (with a lot of caculations on the fields of Job table):

SELECT Job.DateStart, Ave(job.duration), sum(job.ID), sum(....)
.............. FROM job LEFT JOIN Rate ON
Job.UserName Like Rate.CustomerID WHERE (((Job.DateStart)>=#1/1/2001#) And
((Job.DateStart)<=#1/1/9999#)) Group By Job.UserName


I have hundreds of thousands of records. created index on those fields that
i need to caculate and joined
but does not improve the speed. So if i use BE and it's still slow because
of all the calculations made to the
query as above, should i select them into a seperate database at fixed
interval, then let the user have a simple
query to get the results filter by date and userName?

Also, i want to present the query so that it provides statistic in hourly
over 1 day.

Like:

Date Hour JobType TotalJob
30-Jul-2003 0 1 4
30-Jul-2003 1 2 3
30-Jul-2003 2 1 3
30-Jul-2003 3 1 3
..........................................

How can I tackle virtual memory problem? appreciate your kind advice.

thanks a million.
ac
 
SELECT Job.DateStart, Ave(job.duration), sum(job.ID), sum(....)
............. FROM job LEFT JOIN Rate ON
Job.UserName Like Rate.CustomerID WHERE (((Job.DateStart)>=#1/1/2001#) And
((Job.DateStart)<=#1/1/9999#)) Group By Job.UserName
Click to expand...

For starters, since you are not using any wildcards, replace the LIKE
operator with an = sign: you want those records where the UserName and
CustomerID are identical, if I understand correctly. Obviously
UserName and CustomerID will already be indexed if you have
established a relationship between the tables (if you haven't DO
SO!!). The 'And ((Job.DateStart)<=#1/1/9999#))' criterion can be
removed unless you specifically want to exclude dates in 9999 AD. Most
critically, there should be a unique Index on DateStart.

Multiple calculations... just take time. It may indeed be necessary to
create a temporary backend .mdb file, run a MakeTable query daily (or
on whatever schedule meets your needs) to store the calculated values,
and use this table for your reports. Don't do it in your main
database, or it will cause rapid bloat and risk corruption - instead
use the CreateDatabase() method to actually construct a new .mdb file
just for the temp table, and delete the database when you're done with
it.
 
Thanks John, That really hit on the bull eyes! I will plan to do it on a
seperate database.

BTW, Is there a way to present my raw data which is capctured from a flat
file into Job_table
and presented the statistics as fixed interval in the second table,
(Results_table):

Job_Table
JobID DateStart DateEnd Dur(Min) User CallerNum JobType

1 30-Jul-2003 30-Jul-2003 20 1234 886932819590
1
12:00:00 PM 12:20:00 PM

2 31-Jul-2003 31-Jul-2003 19 3452 65932819590
2
12:00:00 PM 12:19:00 PM

Results_Table
Date Hour JobType TotalJob
30-Jul-2003 0 1 4
30-Jul-2003 1 2 3
30-Jul-2003 2 1 3
30-Jul-2003 3 1 3
....................................................................
30-Jul-2003 23 1 3
31-Jul-2003 0 . ........................
....................................................................

Millions thanks!,
ac.
 
Thanks John, That really hit on the bull eyes! I will plan to do it on a
seperate database.
Click to expand...

You're welcome. Did the indexing improve the speed of the query?
BTW, Is there a way to present my raw data which is capctured from a flat
file into Job_table
and presented the statistics as fixed interval in the second table,
(Results_table):
Click to expand...

I'm sorry, between word wrap and your assumption that I know what your
fieldnames mean, all the meaning got drained out of your question!
(Maybe I need another cup of coffee too). Could you explicate?
 
Hi John, the index does not help much. what i asked was achieved as follows
to show the statistic in 24 hours:

TRANSFORM Count(JobID)
SELECT .... (the fields i want to present as Row)
WHERE (((Job.DateStart)>=#7/31/2001#) And ((Job..DateStart)<=#1/1/9999#))
GROUP BY Hour(FormatDateTime([DateStart],4)), Job.User
PIVOT Hour(FormatDateTime([DateStart],4)) In
("0","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16",
"17","18","19","20","21","22","23");

Still slow tho'. but much thanks for the advice.

cheers
ac.
 
Back
Top