Re: Any way of speeding up a very large query?
- From: James D. <JamesD@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 28 Jul 2006 11:01:02 -0700
Any Ideas why I would be getting the following Error:
"Cannot open any more databases. (Error 3048)"
You have reached the limit on the number of databases that can be opened at
one time. Close one or more databases and try the operation again.
This only occurs when I run the report, go into design view, run the report,
go into design view, then run the report again I get the message.
I noticed That this may be due to the amount of fields in the query that the
report is based on. When the query only has 80 fields it works fine, but
when I get close to around 90-100 it starts to occur.
Any info would be really great.
Thx.
James.
"Allen Browne" wrote:
It's a little difficult to know where to start here, and also difficult to.
know why you are doing what you are doing, but here's a start.
1. Function call in WHERE clause
The Year() function call is slowing the main query. Presumably you have the
FormattedRptDate field indexed, but the VBA function call means JET can't
use the index. Replace:
Year([FormattedRptdDate])=2004
with:
FormattedRptDate Between #1/1/2004# And #12/31/2004#
2. Function call on parameter
This will make almost no difference to the speed, but declare parameter
(Parameters on Query menu) as an integer, and you can drop the CInt() too.
3. Structure
Not sure I followed this, but field names like [t10-section] and [t10-Test]
suggest that you have repeating field names (perhaps a t-11-section and so
on.) If so, these tables need to be broken up into related tables with many
records instead of having a table with lots of fields. This is a fundamental
change, but it will probably be the thing you need to sort out all the
issues.
4. Crosstab
Once you have a normalized structure, you will be probably be able to
generate a crosstab query to give you the results you need, instead of
having to write 60 subqueries to get all the various columns.
If I have not understood what you are doing, please feel free to ignore this
reply.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"James D." <JamesD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2C84D732-EBD1-4A80-8F0C-73C460AC9E81@xxxxxxxxxxxxxxxx
I am not sure if there is a way of speeding this up, or if I am just trying
to do too much.
Problem: The problem is that it runs pretty slow. As well I can run the
report as many times as I like, but as soon as I run the report then go
into
the rpt design view, then try and run the report again it gives me the
error
"cannot open any more databases", at which point I have to close access
and
re open it.
Query Description: Basically if the svc departments section is 1 and the
diagnostic test run is one of the codes in the test codes table who is in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the
sum.
The NZ function is there to ensure that there is a 0 put in the field if
the
sum turn out to be 0. This one is for a specific week.
**Please note the test codes table is all the test that can be run for a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic
etc...
** Please note that there is no specific car description in qry01_Base, or
anywhere for that matter.
Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;
Now the reason that this is slow is that this query is slow is that it
runs
the following code 60 times(Each in a different column in query design
view)
in one query for each different car.
"nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004"
In addition to that I have three more queries that do the exact same thing
but for a different year. Then I use one more query who references all the
other queries to group them together so that I can bind them to the
report.
So I really have 240 fields.
Is there a better way to do this? Is there anyway to speed this up?
Any assistance would be great.
- Follow-Ups:
- Re: Any way of speeding up a very large query?
- From: Allen Browne
- Re: Any way of speeding up a very large query?
- References:
- Re: Any way of speeding up a very large query?
- From: Allen Browne
- Re: Any way of speeding up a very large query?
- Prev by Date: Re: working with dates
- Next by Date: Re: Pulling data from multiple tables of same structure
- Previous by thread: Re: Any way of speeding up a very large query?
- Next by thread: Re: Any way of speeding up a very large query?
- Index(es):
Relevant Pages
|
