Is this query sub-optimal?

[NXMold]

The main screen of my database is bound to this query, it's always open and is the primary interaction with data.

I am trying to optimize the speed of things (I have been through all the standard FAQ's) and want to scrutinize this particular query.

There are about 4,000 job #'s, but less than 100 are active at any time. Since this query joins three tables but only shows active jobs, I think there might be a better way.

SELECT CMEJobList.*, Shop.*, Engineering.*
FROM (CMEJobList LEFT JOIN Engineering ON CMEJobList.CMEJobNumber = Engineering.CMEJobNumber) LEFT JOIN Shop ON CMEJobList.CMEJobNumber = Shop.CMEJobNumber
WHERE (((CMEJobList.Active)=True))
ORDER BY CMEJobList.CMEJobNumber;

I tried making a sub query Query_Active_JobList and using that instead of the table CMEJobList, BUT I reference the field "CMEJobList.CMEJobNumber" in dozens of places and cannot create that field name with an alias. I now know that it should have been an alias from the beginning.

I have read about placing the 'sub-query' in the WHERE clause, but don't understand exactly.

 

[PHV]

What about replacing the 2 LEFT JOIN by INNER JOIN ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

make sure you also have an index on the JobList table on JobNumber...that should help the query run faster....

Leslie

Come join me at New Mexico Linux Fest!

 

[NXMold]

Shop and Engineering tables may not have a matching record but I still need to display all records from CMEJobList.

Using this query, when a value is typed into a control bound to those fields a record is added to the shop or engineering table. These tables have 15-20 fields each.

 

[PHV]

So, be sure that CMEJobNumber is indexed in the 3 tables.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Also index CMEJobList.Active in additon to the job number.

Anything you join on should be indexed as PHV pointed out. Anything that is used often for criteria is a candidtate for indexing to increase performance.

You could also increase performance by specifying fewer fields instead of selecting from all of them. This may not be an option but worth considering.

All that considered, your query looks good. I would not use a sub-query (Query_Active_JobList) as that will at best have no effect if not slow things down. Also using a sub-query directly may slow things down as mentioned by using a sub-query in the where clause. Sub-queries have many useful purposes but are only performancing enhanceing to the point that it keeps you from using an even slower VBA solution.

 

[NXMold]

I have indexes on all those fields already. Thanks for the feedback lameid, I wasn't sure if that would improve it.

I have heard that specifying each field (instead of .*) saves time because its one less request access has to make, so maybe I'll expieriment with that.