SQL Server Performance Forum – Threads Archive

Query optimization..

Hi,
I have this query: SELECTtop 1
i_chronicle_id,
pfn_lead_alert,
pfn_left_site_modules,
pfn_site_modules,
pfn_fileselect_files,
a_effective_date,
a_expiration_date,
r_creation_date,
pfn_allfiles_date_range,
pfn_allfiles_display_order,
pfn_fileselect_display_opt,
pfn_allfiles_display_opt,
pfn_fileselect_title,
pfn_allfiles_title,
pfn_assign_to_all,
a.a_webc_url,
pfn_primary_loc
FROM pfieldnet_property_table_s a join pfieldnet_property_table_r b
ON a.a_webc_url = b.a_webc_url
WHERE a.pfn_primary_loc=’9′
AND b.a_effective_date IS not null AND b.a_effective_date <=GETDATE()
AND a.i_full_format = ‘smhtml’
AND (b.a_expiration_date IS NULL OR b.a_expiration_date >= getdate())
ORDER BY b.a_effective_date DESC,a.r_creation_date DESC
The statistics IO look like:
Table ‘pfieldnet_property_table_r’. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0.
Table ‘pfieldnet_property_table_s’. Scan count 1, logical reads 6921, physical reads 0, read-ahead reads 0.
table_r has about 135k rows
and table_s has about 20k rows Indexes on table_s:
1)clustered index on a_webc_url
2) index on i_full_format
Indexes on table_r:
1) non clustered index on a_webc_url Is there a way to better the query so that the reads can be minimized.. Thanx

---

Creating an index on pfn_primary_loc made it look much better:
Table ‘pfieldnet_property_table_r’. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0.
Table ‘pfieldnet_property_table_s’. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0. Any more inputs about the way the query itself can be re-written..

---

Run ITW for this query and check for any further suggestions.

---

]]>