How to optimise query by using integers and not text comparisons?

[munkee]

Hi all,

I have installed a simple progress meter on one of my forms which contains a lot of inline sql queries to bring up around 40 different statistics.

One group however brings up the number of injuries based on body location, department occurred in and the date to be viewed. I have noticed the progress bar takes longest during the execution of this part of the code.

The sql is as follows:

SELECT Count(*) AS CountOfInjuryID1
FROM ((tbldept INNER JOIN (tblLocations INNER JOIN tblhselog ON tblLocations.LocationID = tblhselog.Incident_location) ON tbldept.DeptID = tblLocations.OwningDept) INNER JOIN tblInjuredPerson ON tblhselog.HSEID = tblInjuredPerson.HSEIDLINK) INNER JOIN (tblBodypart INNER JOIN tblInjuryInvestigation ON tblBodypart.PartID = tblInjuryInvestigation.InjuryLocation) ON tblInjuredPerson.InjuredPersonID = tblInjuryInvestigation.InjuredPersonLinkID
WHERE (((tblhselog.Incident_date) Between fiscalStartDate('February',2011) And fiscalEndDate('February',2011)) AND ((tblBodypart.BodyPart)='Head/Face/Eye') AND ((tbldept.Department)='HS&E'));

There are about 6 different tables joined here to get from a department of where an injury occurred through to the location on the body that was injured.

I can not shorten the amount of steps needed to be taken to do this but I was wondering, at present I use text values to match records. Would my query be quicker if I used the integer values of the records instead?

E.g.:

Instead of searching for "Head/Face/Eye" which in table tblBodyParts is all within one row (it doesnt need to be normalised further as we use Head/Face/Eye as a single grouping) as my second column (text) where my first column is autonumber. Would it be better to search based on the Primary Key? (integer value).

I don't know whether access queries search better based on numbers rather than text

[jimatqsi]

Yes of course because not only is the primary key an integer value but it is the primary index. But I am wondering if the most benefit might come from changing count(*) to count(someuniquecolumnname); I've never used count(*) but I presume that selects and counts everything. I'd be interested in knowing if that simple change makes all the difference in the world.

Jim

[munkee]

I have actually read that using * is better

[Rabbit]

Using * is quicker.

Comparing against an indexed integer would be quicker as well. Although if you index the text fields you're comparing on, there's not going to be a huge difference.

What's probably taking the most time is all the inner joins. You can drop tblDept and tblBodyPart from the joins and filter on the foreign key column, assuming they're indexed.

[munkee]

I think I will go through all my queries and drop the joins where I can use the foreign key. I coded with the joins as it helped me check inputs and outputs using actual names instead of a bunch of integer keys which I could easily confuse when running the sql inside functions

[Patrick M]

You should set up your database tables using a foreign key relationship. It'll make things easier in the long run. Also, use stored procedures if you're not already using them.