SQL query - performance issue
-
Initial Production server:
4-processor 1gig box
With 2 gig RAM
New Server (higher spec):
4 processor
4 gig Ram
SQL 2000 standard SP3
Win 2000 SP3
Collation Binary
They have two databases, the first holds the db and the second holds views of the database – one view for each db.
The new, more powerful, server shows a performance increase of 2 or 3 times over the old production server. But, the issue is with Views – as this now runs slower to access on the new box. Having done some digging and realized that the Query optimiser is now running differently – which could well the source of the problem.
The test query we are using, I have highlighted with "************" the problem clause. In essence on our old server if this clause is included then performance is increased (note the duplicate filter against the department and holding tables ... this was implemented specifically to improve performance)
Removing this clause on the new server improves performance from 10703ms down to 1453ms
Removing this clause from the old server degrades performance from 4500ms up to 4984ms
Hope you can help
regards
Tim
SELECT property.property_name,
holding.holding_id, holding.pace_holding_no, holding.holding_name, holding.dept_property_ref, holding.dept_holding_ref, CONVERT(int,holding.floor_area) 'holding_floor_area', isnull(code_tenure_type.tenure_type_desc,'-') 'tenure_type_desc', holding.dept_code, holding.property_centre_code, department.dept_name, CASE isnull(property.building_name,'--') WHEN '--' THEN isnull(property.property_name,'-') ELSE property.building_name END As 'building_name' ,property.town, holding.record_state_code, property.pace_property_no, CONVERT(int,property.os_grid_ref_east) 'os_grid_ref_east' , CONVERT(int,property.os_grid_ref_north) 'os_grid_ref_north' , property.os_grid_ref_accuracy , (select forename + ' ' + surname from pace_officer (NOLOCK) where update_no = 0 and property.pace_officer_id = pace_officer.pace_officer_id) 'pace_officer_name' , (select dept_desc from code_dept where code_dept.dept_code = holding.dept_code) 'actual_dept_name' , (select distinct property_image_library.pace_property_no from property_image_library where property_image_library.image_active = 1 and holding.pace_property_no = property_image_library.pace_property_no) 'picture_pace_property_no' , property.neighbourhood , property.county , property.post_code_area + property.post_code_street 'postcode' ,(select code_go_regional_boundaries.description from code_go_regional_boundaries where code_go_regional_boundaries.go_region = property.go_region) 'go_region_description'
FROM property (NOLOCK)
INNER JOIN ((holding (NOLOCK)
LEFT JOIN code_tenure_type (NOLOCK) ON holding.tenure_type_code = code_tenure_type.tenure_type_code)
LEFT LOOP JOIN department (NOLOCK) ON (holding.dept_code = department.dept_code) AND (holding.property_centre_code = department.property_centre_code))
ON property.pace_property_no = holding.pace_property_no
WHERE property.record_state_code IN ('C') AND (property.update_no = 0)
AND property.dummy_property <> 'Y' AND (property.sensitive_property <> 1)
AND (holding.sensitive_holding <> 1) AND (holding.update_no = 0)
AND holding.record_state_code IN ('C')
AND (exists (SELECT DISTINCT hld.holding_id
from holding hld (NOLOCK)
INNER JOIN department (NOLOCK)
ON (hld.property_centre_code = department.property_centre_code) AND (hld.dept_code = department.dept_code)
Where hld.update_no = 0
AND department.property_centre_code IN ('NPDEAS','NPDHOS','NPDNOR','NPDWES')
************ AND hld.property_centre_code IN ('NPDEAS','NPDHOS','NPDNOR','NPDWES')
AND hld.record_state_code IN ('C') AND department.historic = 'N'
AND hld.holding_id = holding.holding_id))
ORDER BY actual_dept_name, property.property_name
-
We've seen this happen a couple times on joins to derived tables, but never seen it happen from two permanent tables with an inner join. This code looks like it was transplanted from MS Access. Is that correct? If so, sometimes there can be errors in the transfer from Jet to T-SQL. It may be worth rewriting the code in a slightly more readable JOIN syntax. You don't need all the NOLOCK statements, or the LEFT LOOP JOIN (which looks suspicious already because you are INNER JOINING the same 2 tables in the EXISTS...). If possible, could you post either a SHOWPLAN or a the table schemas (or both
) so we can take a deeper look at this? Thanks, Jay. -
Excellent, thanks for the feedback..
) so we can take a deeper look at this? Thanks, Jay.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply