SEARCH QUERIES VERY SLOW - HOW TO IMPROVE SPEED?

[BlueWolverine]

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.

 

[Michel Walsh]

The whole query seems to return a list of id, from t_CCCMap_Unique ,
appearing at least once, and eventually duplicated as many time as it appear
in t_CCCMap_Unique ... which, by its name, it seems it would not appear more
than once, right? if so, the whole query can be replaced by:

SELECT id FROM t_CCCMap_Unique

Could it be simpler?


Nothing is lost even if one of my assumption is wrong. Indeed, since the
WHERE clause occurs only on the unpreserved side of the outer join, I would
try to make a query with:

SELECT LEFT(t_CCCMap_Unique.CCC,3) AS x
FROM t_CCCMap_Unique
WHERE CCC=FORMS!f_SearchPanel!CCCSearch
AND VRT= FORMS!f_SearchPanel!VRTSearch
AND VFG=FORMS!f_SearchPanel!VFGSearch.

saved as, say, q1, then use

SELECT id
FROM t_CCC_Listing LEFT JOIN q1
ON LEFT(t_CCC_Listing.CCC,3) = q1.x


It is useless to index t_CCC_Listing.CCC, since you use a computed
expression on the field, not the field itself.



It is not clear why queries should be cascaded just because you add one more
condition in a WHERE clause: queries over queries likely lose the
possibilities to use the initial index defined on TABLES. On the other hand,
here up, forcing the WHERE clause to occur before the JOIN can be really
beneficial (and it eliminates the ugly OR, hard to optimize)


Vanderghast, Access MVP

 

[Michel Walsh]

The first sentence should have started by:

"The whole query seems to return a list of id, from t_CCC_Listing ... "

 

[BlueWolverine]

That improved performance on that query substantially. I will attempt to
propagate that method through the rest of my queries. Thank you!
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

The whole query seems to return a list of id, from t_CCCMap_Unique ,
appearing at least once, and eventually duplicated as many time as it appear
in t_CCCMap_Unique ... which, by its name, it seems it would not appear more
than once, right? if so, the whole query can be replaced by:

SELECT id FROM t_CCCMap_Unique

Could it be simpler?


Nothing is lost even if one of my assumption is wrong. Indeed, since the
WHERE clause occurs only on the unpreserved side of the outer join, I would
try to make a query with:

SELECT LEFT(t_CCCMap_Unique.CCC,3) AS x
FROM t_CCCMap_Unique
WHERE CCC=FORMS!f_SearchPanel!CCCSearch
AND VRT= FORMS!f_SearchPanel!VRTSearch
AND VFG=FORMS!f_SearchPanel!VFGSearch.

saved as, say, q1, then use

SELECT id
FROM t_CCC_Listing LEFT JOIN q1
ON LEFT(t_CCC_Listing.CCC,3) = q1.x


It is useless to index t_CCC_Listing.CCC, since you use a computed
expression on the field, not the field itself.



It is not clear why queries should be cascaded just because you add one more
condition in a WHERE clause: queries over queries likely lose the
possibilities to use the initial index defined on TABLES. On the other hand,
here up, forcing the WHERE clause to occur before the JOIN can be really
beneficial (and it eliminates the ugly OR, hard to optimize)


Vanderghast, Access MVP

 

[BlueWolverine]

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?

 

[Michel Walsh]

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]

Working out the left(,3) probably cut the process time by 50%.

I added a field to t_CCC_Listing called BaseCode and used an update query to
back fill it. The update runs whenever it needs to, and takes like .1
seconds.

I will still try to improve it but all in all, 3-5 seconds is not so bad.

Thank you so much for your help!!!

 

[John W. Vinson]

I added a field to t_CCC_Listing called BaseCode and used an update query to
back fill it. The update runs whenever it needs to, and takes like .1
seconds.

Rather than storing the BaseCode redundantly in both fields, why not just keep
it separate, and concatenate the two when needed for display purposes?