S
SMA007
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));
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));