Query based on 3 querys

G

Guest

Hello,
I have a form that allows the user select from one check box of criteria and
then enter up to 4 "OR" criteria of data on 3 additional fields from 3
tables. The only way that I could complete this complicated task was to
create a total of 4 querys that return different data based on parameters
from the same form. These querys do work, however, the first 1 and 2 run
very fast, 3 is somewhat slower and the 4th is very slow (35 secs). I am
using access front end and back end databases and have just imported these
tables into SQL server to see if there was any speed difference, not much
difference. I thought that as the queries progress, the data set gets
smaller, so querys 3 and 4 should be less burdened. This doesn't seem to be
the case. It also seems that when running the last query I can see the form
come up with the correct number of records 10 secs before the query is done
running.

Can anyone give me a idea as to how I could speed up this search?

My 4 sql statements are listed below.

Thanks for any suggestions in advance.

Brian

Repair4b = 36,000 records (search is on boolean field, 0 or -1)
Disciplinelinker = 80,000 records. (search is on numeric field 1-56)
Aircraftexperience = 170,000 records (search is for "text string")

SELECT DISTINCTROW repair4b.*
FROM repair4b
WHERE (((repair4b.REMOVEFROM)=[forms]![Mainsearch].[searchablebox])) OR
((([forms]![Mainsearch].[searchablebox]) Is Null));

SELECT DISTINCTROW QueryMainsearchSearchable1.*
FROM QueryMainsearchSearchable1 LEFT JOIN DisciplineLinker ON
QueryMainsearchSearchable1.SS_ = DisciplineLinker.Repair4bID
WHERE (((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox] And
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox2] And
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox3] And
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox4])) OR
((([forms]![mainsearch].[disciplinebox4]) Is Null) AND
(([forms]![mainsearch].[disciplinebox]) Is Null) AND
(([forms]![mainsearch].[disciplinebox2]) Is Null) AND
(([forms]![mainsearch].[disciplinebox3]) Is Null)) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox]) AND
(([forms]![mainsearch].[disciplinebox4]) Is Null) AND
(([forms]![mainsearch].[disciplinebox2]) Is Null) AND
(([forms]![mainsearch].[disciplinebox3]) Is Null)) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox] Or
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox2]) AND
(([forms]![mainsearch].[disciplinebox4]) Is Null) AND
(([forms]![mainsearch].[disciplinebox3]) Is Null)) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox] Or
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox2])) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox3]) AND
(([forms]![mainsearch].[disciplinebox4]) Is Null));

SELECT DISTINCTROW QueryMainSearchdiscipline2.*
FROM QueryMainSearchdiscipline2 LEFT JOIN AircraftExperience ON
QueryMainSearchdiscipline2.SS_ = AircraftExperience.repair4bID
WHERE (((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox2] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox3] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox4])) OR
((([forms]![mainsearch].[sectorbox]) Is Null) AND
(([forms]![mainsearch].[sectorbox2]) Is Null) AND
(([forms]![mainsearch].[sectorbox3]) Is Null) AND
(([forms]![mainsearch].[sectorbox4]) Is Null)) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox]) AND
(([forms]![mainsearch].[sectorbox2]) Is Null) AND
(([forms]![mainsearch].[sectorbox3]) Is Null) AND
(([forms]![mainsearch].[sectorbox4]) Is Null)) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox2]) AND
(([forms]![mainsearch].[sectorbox3]) Is Null) AND
(([forms]![mainsearch].[sectorbox4]) Is Null)) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox2])) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox3]) AND
(([forms]![mainsearch].[sectorbox4]) Is Null));

SELECT DISTINCTROW QueryMainSearchsector3.*
FROM QueryMainSearchsector3 LEFT JOIN AircraftExperience ON
QueryMainSearchsector3.SS_ = AircraftExperience.repair4bID
WHERE (((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox2] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox3] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox4])) OR
((([forms]![mainsearch].[manufbox]) Is Null) AND
(([forms]![mainsearch].[manufbox2]) Is Null) AND
(([forms]![mainsearch].[manufbox3]) Is Null) AND
(([forms]![mainsearch].[manufbox4]) Is Null)) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox]) AND
(([forms]![mainsearch].[manufbox2]) Is Null) AND
(([forms]![mainsearch].[manufbox3]) Is Null) AND
(([forms]![mainsearch].[manufbox4]) Is Null)) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox2]) AND
(([forms]![mainsearch].[manufbox3]) Is Null) AND
(([forms]![mainsearch].[manufbox4]) Is Null)) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox2])) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox3]) AND
(([forms]![mainsearch].[manufbox4]) Is Null));
 
G

Guest

I use to work on DC-9s, Sabreliners, and F-111s in a past career.

Make sure that all your seach fields are indexed. That could really help.

I used something like below to put more than one text box entery into the
criteria of a where clause:

In ([Forms]![frmParameterMulti]![txtParameter1],
[Forms]![frmParameterMulti]![txtParameter2])

In the frmParameterMulti from I made the default values of the txtParameter1
and txtParameter2 unbound text boxes "x". I know that my data doesn't have
any x's so it won't return a record nor do I have to worry about null values.
One In clause should be much faster than doing 4 and statements plus the
permutations of Nulls.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

SMA007 said:
Hello,
I have a form that allows the user select from one check box of criteria and
then enter up to 4 "OR" criteria of data on 3 additional fields from 3
tables. The only way that I could complete this complicated task was to
create a total of 4 querys that return different data based on parameters
from the same form. These querys do work, however, the first 1 and 2 run
very fast, 3 is somewhat slower and the 4th is very slow (35 secs). I am
using access front end and back end databases and have just imported these
tables into SQL server to see if there was any speed difference, not much
difference. I thought that as the queries progress, the data set gets
smaller, so querys 3 and 4 should be less burdened. This doesn't seem to be
the case. It also seems that when running the last query I can see the form
come up with the correct number of records 10 secs before the query is done
running.

Can anyone give me a idea as to how I could speed up this search?

My 4 sql statements are listed below.

Thanks for any suggestions in advance.

Brian

Repair4b = 36,000 records (search is on boolean field, 0 or -1)
Disciplinelinker = 80,000 records. (search is on numeric field 1-56)
Aircraftexperience = 170,000 records (search is for "text string")

SELECT DISTINCTROW repair4b.*
FROM repair4b
WHERE (((repair4b.REMOVEFROM)=[forms]![Mainsearch].[searchablebox])) OR
((([forms]![Mainsearch].[searchablebox]) Is Null));

SELECT DISTINCTROW QueryMainsearchSearchable1.*
FROM QueryMainsearchSearchable1 LEFT JOIN DisciplineLinker ON
QueryMainsearchSearchable1.SS_ = DisciplineLinker.Repair4bID
WHERE (((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox] And
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox2] And
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox3] And
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox4])) OR
((([forms]![mainsearch].[disciplinebox4]) Is Null) AND
(([forms]![mainsearch].[disciplinebox]) Is Null) AND
(([forms]![mainsearch].[disciplinebox2]) Is Null) AND
(([forms]![mainsearch].[disciplinebox3]) Is Null)) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox]) AND
(([forms]![mainsearch].[disciplinebox4]) Is Null) AND
(([forms]![mainsearch].[disciplinebox2]) Is Null) AND
(([forms]![mainsearch].[disciplinebox3]) Is Null)) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox] Or
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox2]) AND
(([forms]![mainsearch].[disciplinebox4]) Is Null) AND
(([forms]![mainsearch].[disciplinebox3]) Is Null)) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox] Or
(DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox2])) OR
(((DisciplineLinker.ID)=[forms]![mainsearch].[disciplinebox3]) AND
(([forms]![mainsearch].[disciplinebox4]) Is Null));

SELECT DISTINCTROW QueryMainSearchdiscipline2.*
FROM QueryMainSearchdiscipline2 LEFT JOIN AircraftExperience ON
QueryMainSearchdiscipline2.SS_ = AircraftExperience.repair4bID
WHERE (((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox2] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox3] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox4])) OR
((([forms]![mainsearch].[sectorbox]) Is Null) AND
(([forms]![mainsearch].[sectorbox2]) Is Null) AND
(([forms]![mainsearch].[sectorbox3]) Is Null) AND
(([forms]![mainsearch].[sectorbox4]) Is Null)) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox]) AND
(([forms]![mainsearch].[sectorbox2]) Is Null) AND
(([forms]![mainsearch].[sectorbox3]) Is Null) AND
(([forms]![mainsearch].[sectorbox4]) Is Null)) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox2]) AND
(([forms]![mainsearch].[sectorbox3]) Is Null) AND
(([forms]![mainsearch].[sectorbox4]) Is Null)) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox] Or
(AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox2])) OR
(((AircraftExperience.Sector)=[forms]![mainsearch].[sectorbox3]) AND
(([forms]![mainsearch].[sectorbox4]) Is Null));

SELECT DISTINCTROW QueryMainSearchsector3.*
FROM QueryMainSearchsector3 LEFT JOIN AircraftExperience ON
QueryMainSearchsector3.SS_ = AircraftExperience.repair4bID
WHERE (((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox2] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox3] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox4])) OR
((([forms]![mainsearch].[manufbox]) Is Null) AND
(([forms]![mainsearch].[manufbox2]) Is Null) AND
(([forms]![mainsearch].[manufbox3]) Is Null) AND
(([forms]![mainsearch].[manufbox4]) Is Null)) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox]) AND
(([forms]![mainsearch].[manufbox2]) Is Null) AND
(([forms]![mainsearch].[manufbox3]) Is Null) AND
(([forms]![mainsearch].[manufbox4]) Is Null)) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox2]) AND
(([forms]![mainsearch].[manufbox3]) Is Null) AND
(([forms]![mainsearch].[manufbox4]) Is Null)) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox] Or
(AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox2])) OR
(((AircraftExperience.Manuf)=[forms]![mainsearch].[manufbox3]) AND
(([forms]![mainsearch].[manufbox4]) Is Null));
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query based on 3 tables with parameters from form 4

Top