Less time consuming Query (A toughie) (1 Viewer)

[RainX]

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

[U]ID[/U]	[U]mmmyy[/U]	[U]Description[/U]	[U]Primary[/U]	[U]Pattern Mask[/U]
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)=[I]forms![SOS Report Generator]!Combo1[/I])); "

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

[U]type[/U]	[U]description[/U]
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]![I]Combo3[/I])); "

The table for grpoffic looks like this

[U]PRECINCT[/U]	[U]G[/U]	[U]CODE[/U]	[U]TYPE[/U]	[U]OFFICE[/U]
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]![[U][B]Combo2[/B][/U]]) AND ((grpoffic.OFFICE)=[forms]![SOS Report Generator]![[I][B]Combo4[/B][/I]]))
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

 

[Jon K]

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.
.

 

[RainX]

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

 

[Jon K]

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.
.

 

[RainX]

Re: Optimization

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

 

[RainX]

bump.. anyone?