Hi all,

Let me first setup how i accomplished this particular query, and then you guys can help me with improving it.


I currently have a search form which has 4 combo_boxes each based on a query

----

* Combo1 = "SELECT [EMY].mmmyy, [EMY].Description, [EMY].Primary, [EMY].[Pattern Mask] FROM [EMY]; "

The table EMY looks like this


ID mmmyy Description Primary Pattern Mask
15 feb03 February 03 Primary Yes 033
16 apr03 April 03 Election No 03??4
17 mar04 March 04 Primary Yes 041
18 nov04 November 04 Election No 04??2


The Primary field has check boxes as Yes or No

-------

* Combo2 = "SELECT [VC].mmmyy, [VC].[Voter Code], [Voter Code].[Voter Code Description], [VC].[Voter Code Pattern Mask]
FROM [VC]
WHERE ((([VC].mmmyy)=forms![SOS Report Generator]!Combo1)); "

The table for VC looks like this

mmmyy Voter Code Voter Code Description Voter Code Pattern Mask
nov02 * All *02??2*
nov02 N Did not vote *02??2N*
nov02 V Voted *02??2V*
mar02 * All *021*
mar02 D Voted Democrat *021D*
mar02 L Voted Libertarian *021L*
mar02 N Did not vote *021N*
mar02 P Voted Non-Partisan *021P*
mar02 R Voted Republican *021R*
mar02 V Voted *021V*
apr03 * All *03??4*
apr03 N Did not vote *03??4N*
apr03 V Voted *03??4V*
feb03 * All *033*
feb03 D Voted Democrat *033D*
feb03 N Did not vote *033N*
feb03 P Voted Non-Partisan *033P*
feb03 R Voted Republican *033R*
nov04 * All *04??2*
nov04 N Did not vote *04??2N*
nov04 V Voted *04??2V*
mar04 * All *041*
mar04 D Voted Democrat *041D*
mar04 L Voted Libertarian *041L*
mar04 N Did not vote *041N*
mar04 P Voted Non-Partisan *041P*
mar04 R Voted Republican *041R*
mar04 V Voted *041V*


---
* Combo3 = "SELECT [Office Type].type, [Office Type].description
FROM [Office Type]
ORDER BY [Office Type].description;"

the Table for Office Type Looks like this

type description
05 Township
10 Municipality
15 Elementary School
20 High School
25 College


----

* Combo4 = "SELECT grpoffic.TYPE, grpoffic.OFFICE
FROM grpoffic
GROUP BY grpoffic.TYPE, grpoffic.OFFICE
HAVING (((grpoffic.TYPE)=forms![SOS Report Generator]!Combo3)); "

The table for grpoffic looks like this


PRECINCT G CODE TYPE OFFICE
7000001 0 02-0010-100 05 B Township
7000001 0 03-0030-100 10 Village of B

What i do next is i use i try to search through two tables using these combo box fields. So the query for my search is

SELECT Sosx.Certnum, Sosx.Township, Sosx.Ward, Sosx.Precinct, Sosx.[Reg Date], Sosx.[Name], Sosx.Feb07, Sosx.Apr07, Sosx.Mar06, Sosx.Nov06, Sosx.Feb05, Sosx.Apr05, Sosx.Mar04, Sosx.Nov04, Sosx.Feb03, Sosx.Apr03, Sosx.[Voting History Combined]
FROM Sosx INNER JOIN grpoffic ON (Sosx.[Precinct Combined] = grpoffic.PRECINCT) AND (Sosx.Group = grpoffic.G)
WHERE (((Sosx.[Voting History Combined]) Like [forms]![SOS Report Generator]![Combo2]) AND ((grpoffic.OFFICE)=[forms]![SOS Report Generator]![Combo4]))
ORDER BY Sosx.[Precinct Combined];

Sosx table looks like this


Certnum Township City Ward Precinct Name Feb07 Apr07 Mar06 Nov06 Feb05 Apr05 Mar0 Nov04 Feb0 Apr03 Voting History Combined Precinct Combined
00000001 96 00 01 John Doe F 4 N N N N N V N N 073N4N061N2N053N4NN041N2V033N4N 9600013

I think the part thats taking a long time is when it does a pattern search. Is there anyway to optimize this so it doesnt take too much time?
Keep in mind that Sosx table has around 1.4 million entries


I know this is kind of a long problem, but i will be really grateful if someone can help me out.

Thanks in advance

SELECT Sosx.Certnum, Sosx.Township, Sosx.Ward, Sosx.Precinct, Sosx.[Reg Date], Sosx.[Name], Sosx.Feb07, Sosx.Apr07, Sosx.Mar06, Sosx.Nov06, Sosx.Feb05, Sosx.Apr05, Sosx.Mar04, Sosx.Nov04, Sosx.Feb03, Sosx.Apr03, Sosx.[Voting History Combined]
FROM Sosx INNER JOIN grpoffic ON (Sosx.[Precinct Combined] = grpoffic.PRECINCT) AND (Sosx.Group = grpoffic.G)
WHERE (((Sosx.[Voting History Combined]) Like [forms]![SOS Report Generator]![Combo2]) AND ((grpoffic.OFFICE)=[forms]![SOS Report Generator]![Combo4]))
ORDER BY Sosx.[Precinct Combined];

I can see only two combo boxes in the SQL statement.

Try indexing the fields [Voting History Combined] and [Precinct Combined] in table Sosx, the field [OFFICE] in table grpoffic, and all the fields used in the JOIN

and changing the Like operator to = as Like cannot take advantage of the index.

Hope that helps.
.

Thanks for the tip but changing the like to equal doesn't make the query return anything because

(Sosx.[Voting History Combined]) looks like this
073N4N061N2N053N4NN041N2V033N4N
and
[forms]![SOS Report Generator]![Combo2] looks like the voter code pattern mask in the VC table i.e. *021P*

Any other way around this?

Tc & thanks again

Oh, I overlooked the sample data!

Index the other fields and try splitting the SQL statement into two queries and combining them in a third one. Run the third query.

Sometimes splitting a query into smaller ones helps.
.

Thanks i've indexed those fields and it sped it up a little, but its still pretty slow.
I tried splitting it into two queries as well but that ended up taking alot longer.
I dont understand why though. If i run the queries by opening up the form and choosing the selections from the combo box and then going to the queries part and running the Query outside of the form it loads alot faster.. Is there a better way to load queries in a form? (Currently i'm using subform/subreport to do this).

Take care

bump.. anyone?