I am combining several columns and tables and I am wondering if there is a way to improve performance, for instance can I write an implicit join version of the following code, rather than having these explicit joins?
objCmd = new OleDbCommand ("SELECT MAmunicipalities.*, MAcitytown.*, countiesalias1.countyname, countiesalias1.countylinktitle AS clink1, countiesalias2.countyname, countiesalias2.countylinktitle AS clink2, countiesalias3.countyname, countiesalias3.countylinktitle AS clink3 FROM (MAmunicipalities LEFT OUTER JOIN MAcitytown ON MAmunicipalities.citytown = MAcitytown.citytown) LEFT OUTER JOIN MAcounties AS countiesalias1 ON MAcitytown.county1 = countiesalias1.countyname LEFT OUTER JOIN MAcounties AS countiesalias2 ON MAcitytown.county2 = countiesalias2.countyname LEFT OUTER JOIN MAcounties AS countiesalias3 ON MAcitytown.county3 = countiesalias3.countyname WHERE MAmunicipalities.municipality='plymouth'", objConn);
Sadly, I have even more joins left to add as well as other columns to add to the SELECT statement and more aliases to add as well. Any thoughts would be greatly appreciated.
So far, this appears to be a five table JOIN. That in itself 'shouldn't be a preformance issue.
Judicious usage of TABLE aliases would make the code a bit more readible (and maintainable).
And it is widely considered a 'best practice' to specifically identify columns instead of using [ SELECT * ].
For Example:
Code Snippet
SELECTm.*,
c.*,
c1.CountyName,
c1.CountyLinkTitle AS cLink1,
c2.CountyName,
c2.CountyLinkTitle AS cLink2,
c3.CountyName,
c3.CountyLinkTitle AS cLink3
FROM MaMunicipalities m
LEFT JOIN MaCityTown c
ON m.CityTown = c.CityTown
LEFT JOIN MaCounties c1
ON c.County1 = c1.CountyName
LEFT JOIN MaCounties c2
ON c.County2 = c2.CountyName
LEFT JOIN MaCounties c3
ON c.County3 = c3.CountyName
WHERE m.Municipality = 'plymouth'
Are there additional tables to JOIN, or is it additional JOINs to the same tables (like with MaCounties above)?
|||I have additional tables to join. Right now it is taking between 10 and 15 seconds for a page to load using the code that is posted. I have indexed columns within those tables which has helped somewhat but it is still taking way too long. I suspect that having varchars being joined instead of ints is also contributing to the problem. Would a stored procedure help?|||A stored procedure may help some -but its doubtful that it would provide the kind of improvement you really need.
As you suspect, the major issue is most likely the varchar() fields used for the JOINs.
One of the most significant things that help with speed on JOINs is the 'width' of an index. An integer field is 4 bytes. A varchar() field is as many bytes as characters (nvarchar() is double that.) The 'wider' the index, the fewer entries on an index page, therefore the more pages that have to be 'crawled' through and read to find the data.
Ideally, your CityTown and CountyName values would be integer values (CityTownID and CountyNameID).
I suggest that you explore using the Database Tuning Advisor to get assistance in fine tuning the indexes.
Refer to Books Online, Topic: 'Database Engine Tuning Advisor'
