Re: SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Thu, 11 Dec 2008 13:42:56 -0500
It is not really that WHERE clause are faster (or slower) than JOINs, but
that the initial where clause was having a lot of OR and the final
formulation was having none. Also, you should consider that the table design
you are using is less than perfect: in theory, we should have the
information available without having to "compute" it,without having to
"extract" it, as we do now with Left( fieldName, 3). Sounds like the
field should have been TWO fields, in the table, on for the first three
characters and the other for whatever is left: With such ATOMIC disposition
of the data, already available, index COULD have been used, without having
to compute the expression Left( fieldName, 3) over and over.
Vanderghast, Access MVP
"BlueWolverine" <BlueWolverine@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3EF40EB4-187B-4254-8550-6FBFA1C6AA33@xxxxxxxxxxxxxxxx
So having taken Michael Walsh's Advice above, I have found that WHERE
takes
WAY less TIME than JOIN.
Does that sound right? Because the WHERE queries are crazy fast but the
associated JOINS take forever.
Additionally, if this is the case, why would I break out the data and ADD
joins to my chain?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!
"BlueWolverine" wrote:
Hello,
MS ACCESS 2003 on XP PRO.
I wrote search panel for my database. I used to have it setup where it
would pull all the data together into one query and then run the criteria
searches on it. that took 2 seconds.
A buddy of mine with database training recommended writing steps of
queries,
such that Query1 cuts out some data, Query2 cuts some data out of Query1,
and
so on. Thus, i'd only ever be passing smaller and smaller subsets of ID
numbers, and then I'd pull up all the data needed for those IDS. Make
sense
to me, but it takes for EVER. 10 seconds to run the whole search with
one
step taking around 5 seconds.
My buddy told me to avoid the use of "Like" comparisons, which I have to
use, but I tried to use them as late as possible so as to minimize the
data
being inefficiently compared.
I think I have things appropriately indexed but every time I run the
Performance Analyzer, it wants me to index another field in my table. Is
the
point of indexing lost if you index every field? Or can I do that ?
I will include the code for the most time consuming query. The others
are
slow, but not more than 1/2 second each.
SELECT t_CCC_Listing.ID
FROM t_CCC_Listing LEFT JOIN t_CCCMap_Unique ON
left(t_CCC_Listing.CCC,3)=left(t_CCCMap_Unique.CCC,3)
WHERE ((((t_CCCMap_Unique.CCC)=Forms!f_SearchPanel!CCCSearch) Or
isnull(Forms!f_SearchPanel!CCCSearch)) And
(((t_CCCMap_Unique.VFG)=Forms!f_SearchPanel!VFGSearch) Or
isnull(Forms!f_SearchPanel!VFGSearch)) And
(((t_CCCMap_Unique.VRT)=Forms!f_SearchPanel!VRTSearch) Or
isnull(Forms!f_SearchPanel!VRTSearch)))
GROUP BY t_CCC_Listing.ID
ORDER BY t_CCC_Listing.ID;
ANY ideas are appreciated.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!
.
- Follow-Ups:
- Re: SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- From: BlueWolverine
- Re: SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- References:
- SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- From: BlueWolverine
- RE: SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- From: BlueWolverine
- SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- Prev by Date: query for attendance
- Next by Date: Defining Text field sizes in destination Make-Table Query
- Previous by thread: RE: SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- Next by thread: Re: SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?
- Index(es):
Relevant Pages
|
