|
JackBlack
Starting Member
1 Post |
 Posted - 2008-06-30 : 09:07:49
|
| This SQL is running very slow(7 - 21 sec), all the joins are on indexes.In execution plan nothing is taking more then 7%.I am not able to find any thing specific that can be improved.Is there is any things that can be improved?thanks. SELECT s.* FROM street s LEFT OUTER JOIN address a ON a.address2street = s.Objid LEFT OUTER JOIN property2address_mtm m ON m.address_objid = a.objid INNER JOIN property p ON p.objid = m.property_objid and m.type = 1 LEFT OUTER JOIN charge_basis cb ON cb.charge_basis2property = p.objid and charge_basis_oc = 1 INNER JOIN property2account_mtm m2 ON m2.property_objid = p.objid LEFT OUTER JOIN account acc ON acc.objid = m2.account_objid LEFT OUTER JOIN account2customer_mtm m4 ON m4.account_objid = acc.objid LEFT OUTER JOIN customer c ON c.objid = m4.customer_objid WHERE (m4.end_date is null or (m4.customer_objid > 0 or p.status = 'D')) and acc.bulk_account_indicator <>1 and s.objid = @p_street_reference and (m2.end_date is null or (m2.end_date = acc.end_date)) UNION SELECT s.* FROM street s INNER JOIN address a ON a.address2street = s.Objid INNER JOIN property2address_mtm m ON m.address_objid = a.objid INNER JOIN property p ON p.objid = m.property_objid INNER JOIN charge_basis cb ON cb.charge_basis2property = p.objid INNER JOIN meter_group mg on mg.objid = p.property2meter_group INNER JOIN property p2 on p2.objid = mg.meter_group2nominated_property INNER JOIN property2account_mtm m3 on m3.property_objid = p2.objid INNER JOIN account acc ON acc.objid = m3.account_objid LEFT OUTER JOIN account2customer_mtm m4 ON m4.account_objid = acc.objid LEFT OUTER JOIN customer c ON c.objid = m4.customer_objid WHERE mg.objid <> -1 and (m4.end_date is null or (m4.customer_objid > 0 or p.status = 'D')) and acc.bulk_account_indicator <>1 and acc.objid in ( select account_objid from account2indicator_description_mtm where account_objid=acc.objid and indicator_description_objid=@IndicatorDescriptiontObjId and end_date is null ) and charge_basis_oc = 1 and m3.end_date is null and m.type = 1 and s.objid = @p_street_reference and (m3.end_date is null or (m3.end_date = acc.end_date)) ORDER BY s.address_line_1, p.street_sequence General stats when ran after clearing buffers (takes some where b/w 7-24 secs)Table 'customer'. Scan count 511, logical reads 2530, physical reads 1, read-ahead reads 355.Table 'account2customer_mtm'. Scan count 522, logical reads 3799, physical reads 161, read-ahead reads 60.Table 'account2indicator_description_mtm'. Scan count 70, logical reads 516, physical reads 1, read-ahead reads 48.Table 'account'. Scan count 522, logical reads 2600, physical reads 11, read-ahead reads 197.Table 'property'. Scan count 546, logical reads 2271, physical reads 7, read-ahead reads 165.Table 'property2account_mtm'. Scan count 306, logical reads 1480, physical reads 0, read-ahead reads 161.Table 'meter_group'. Scan count 237, logical reads 955, physical reads 9, read-ahead reads 22.Table 'charge_basis'. Scan count 690, logical reads 2910, physical reads 34, read-ahead reads 119.Table 'property2address_mtm'. Scan count 476, logical reads 3780, physical reads 1, read-ahead reads 185.Table 'address'. Scan count 2, logical reads 2082, physical reads 2, read-ahead reads 226.Table 'street'. Scan count 2, logical reads 6, physical reads 3, read-ahead reads 0.Table 'customer'. Scan count 830, logical reads 2506, physical reads 350, read-ahead reads 0.Table 'account2customer_mtm'. Scan count 838, logical reads 5277, physical reads 501, read-ahead reads 0.Table 'account2indicator_description_mtm'. Scan count 141, logical reads 423, physical reads 131, read-ahead reads 0.Table 'account'. Scan count 842, logical reads 2584, physical reads 268, read-ahead reads 0.Table 'property'. Scan count 931, logical reads 2817, physical reads 212, read-ahead reads 0.Table 'property2account_mtm'. Scan count 530, logical reads 1595, physical reads 172, read-ahead reads 0.Table 'meter_group'. Scan count 395, logical reads 791, physical reads 37, read-ahead reads 0.Table 'charge_basis'. Scan count 1096, logical reads 3306, physical reads 170, read-ahead reads 0.Table 'property2address_mtm'. Scan count 790, logical reads 5042, physical reads 305, read-ahead reads 0.Table 'address'. Scan count 2, logical reads 2376, physical reads 164, read-ahead reads 2.Table 'street'. Scan count 2, logical reads 6, physical reads 3, read-ahead reads 0.General stats (NOT clearing buffers ;diff parms ) takes bit less time Table 'customer'. Scan count 479, logical reads 1445, physical reads 206, read-ahead reads 0.Table 'account2customer_mtm'. Scan count 486, logical reads 3040, physical reads 214, read-ahead reads 0.Table 'account2indicator_description_mtm'. Scan count 70, logical reads 210, physical reads 52, read-ahead reads 0.Table 'account'. Scan count 486, logical reads 1485, physical reads 114, read-ahead reads 0.Table 'property'. Scan count 544, logical reads 1660, physical reads 108, read-ahead reads 0.Table 'property2account_mtm'. Scan count 305, logical reads 916, physical reads 62, read-ahead reads 0.Table 'meter_group'. Scan count 237, logical reads 475, physical reads 10, read-ahead reads 0.Table 'charge_basis'. Scan count 653, logical reads 1964, physical reads 56, read-ahead reads 0.Table 'property2address_mtm'. Scan count 474, logical reads 2970, physical reads 112, read-ahead reads 0.Table 'address'. Scan count 2, logical reads 1426, physical reads 73, read-ahead reads 0.Table 'street'. Scan count 2, logical reads 6, physical reads 1, read-ahead reads 0.pls let me know if any other data is required.thanks.Jack Black |
|
Very Slow SQL