Circular query slow to run

[JBRTaylor]

Hi

I bet slow queries are quite a common problem but i am not too sure what is causing mine to be so slow. It takes at least 55 seconds to run at the moment.

I have narrowed the problem down to the from clause by simplifying the select part of the sql statement. Although the example pasted below has a very simple select part, all of the tables are needed for the query to run properly.

<code>
SELECT tblCollection.HireID
FROM ((tblTariff INNER JOIN (tblProductions INNER JOIN (tblEquipment INNER JOIN (tblCollection INNER JOIN tblCollectionItemized ON tblCollection.HireID = tblCollectionItemized.HireID) ON tblEquipment.KitKey = tblCollectionItemized.KitKey) ON tblProductions.ProductionIDKey = tblCollection.ProductionName) ON tblTariff.TariffKey = tblCollection.Tarriff) INNER JOIN tblRateCard ON tblTariff.TariffKey = tblRateCard.Tariff) INNER JOIN tblKitSubCat ON (tblKitSubCat.CategoryID = tblEquipment.KitSubCategory) AND (tblRateCard.KitKey = tblKitSubCat.CategoryID)
WHERE HireID=1234
</code>

and I have attached a graphical form of the query.

Thanks in advance for any help you can offer.
Jon

 

[plog]

Tables shouldn't be related like that. There should only be one path between 2 tables. Like you've noticed, you made a circuit.

I don't know what your data is for or how it is related (other than your screenshot), but you need to rework the relationships so that tblEquipment is only related in one way to tblCollection. I don't know which way to go, but I do know it should only be one way.

 

[plog]

Taking a second look, its worse than that. A spiderweb would be a better analogy. I see that tblEquipment is also directly related to tblRateCard (even though its not used in this query).

It looks like you don't know where tblEquipment goes. Again, I don't know your data, so can you explain what real-world process this database models? No database jargon, just tell me what your data is about.

 

[JBRTaylor]

tblCollection - this holds information relating to a production who are collecting equipment. In this table you need to select the tarriff that the production will be charged.

tblCollectionItemized - These records are a reference to the equipment table, so each record is an item of kit that is booked out to the production detailed in the tblCollection. Booking active field says if the kit is still booked out or returned.

tblEquipment is a list of the kit available to hire

tblKitSubCat - this is putting the kit into categorys so it is easily searchable, eg cameras, laptops, phones.

tblRateCard - For each record in tblKitSubCat there is a tariff and a costeg
1 cameras rate1 £10
2 Cameras Rate2 £50
3 Laptops rate1 £20
4 Laptops Rate2 £79
5 Phones Rate1 .......

nb KitKey is mislabelled and has no reference to kitkey in the tblEquipment table.

tblTariff - are the names of the rates available, eg rate1

I am a total beginner with no propper training and have done well to get this far but i am aware i have made some big mistakes which i am trying to correct now and understand why and where i have gone wrong.

I APPRECIATE YOUR HELP.

 

[CJ_London]

regardless of final design I would also check your indexing to ensure your family keys are indexed

 

[plog]

Still not really understanding your data relationships. I don't think tblKitSubCat should be linked to tblEquipment.

Also, tblTarriff isn't accomplishing anything, you should just directly link tblCollection to tblRateCard.

 

[JBRTaylor]

I have now removed the link between tblRateCard and tblkitSubCat and directly joined tblRateCard to tblEquipment which has had quite an improvement and we are now down to about 20 seconds which is a huge improvement from about 55 we were at before.

I have also checked all the foreign keys are indexed but this has had little improvement on speed.

While this is good enough if there are any other suggestions for improvements i'd love to hear them.

Thanks
Jonathan

 

[CJ_London]

not sure if this is just the way you have posted it, but your original sql says

'WHERE HireID=1234'

But you have two HireID's - 'ON tblCollection.HireID=tblCollectionItemized.HireID'

How is your query differentiating between the two?