Due to nature of the existing SQL Server 2000 database in my company, I have to use around four or more tables in my query to get the required results. Consider two tables for now: contracts and dealers. I have to join these two tables in mostly all reports. Indexes have been defined on fields dealer_no (String) and tpa_code (String), which are used in INNER JOIN. Tpa_code has fewer distinct values, and for me its value is same in all reports, say 'UWC'.
I am using stored procedures for all queries and have the following doubt regarding the query performance. Which one will be better under load? Both tables have large number of rows.
------------------------- Declare @tpa_code = 'UWC' 1)select col1, col2, col3, col4, col5 from (Select col1, col2, col3 from contracts c where tpa_code=@tpa_code) INNER JOIN (Select col4, col5 from dealers d where tpa_code=@tpa_code) ON c.dealer_no and d.dealer_no 2) Select col1, col2, col3, col4, col5 from contracts c INNER JOIN dealers d ON c.dealer_no and d.dealer_no and c.tpa_code = d.tpa_code and c.tpa_code=@tpa_codeI am at present using the first of these queries written. I am working as a junior developer and had some serious talk about performance of these two queries with my seniors who disagreed with me, arguing on a maintenance problem of the sub-select which tends to increase the stored procedure text. I have been reading that the least the number of rows you have to join and less the join condition, the better. So I prefer the first one. Will using the sub-select in place of table name with condition degrade performance? Please guide me in this.
Note: There will be at least four tables in most of the queries which will be INNER joined or LEFT joined and majority of them will involve tpa_code as the second join condition besides the primary key for the table. How do I find the most suitable way to write such queries? I have been using Query Analyzer to view execution plans and at present selecting the one which gives least cost.
In general I recommend using the INNER JOIN syntax, but with the information available I have a hard time giving you a definitive answer. Another general best practice I recommend is splitting the OLTP system from the reporting system so that report generation does not negatively impact typical business transactions. If you do not have the luxury of two systems, then try to schedule and execute the reports during low usage periods.
Nevertheless, performance tuning is a major initiative for any production system relying on SQL Server. As such, I would recommend analyzing the SQL Server performance on a regular basis to ensure you organization is achieving a high return on investment based on the hardware and development investment. I have published a recent white paper focused on performance tuning that may be of benefit to you entitled Reach the SQL Server performance tuning pinnacle that may be of benefit to you and your team in the short and long run. Happy tuning!
This was first published in March 2005