January 21, 2011 at 5:42 am
Hi,
Below SQL is taking around 3 minutes to fetch 87 thousand records. Is there any way I can optimize it. I tried creating indexes here and there but didn't help.
SELECTcxa.cxa_cst_key,
adr_meeting_name_ext,
REPLACE(cxa_mailing_label_html,'
',' ') mailing_label_html,
adr_country,
ISNULL(adr_intl_province,adr_state) state_province,
ROW_NUMBER () OVER (PARTITION BY cxa.cxa_cst_key
ORDER BY ISNULL (cxa_change_date, cxa_add_date) DESC) row_num
FROMco_customer_x_address cxaWITH(NOLOCK)
JOINco_address_type adtWITH(NOLOCK) ON cxa_adt_key = adt_key
ANDadt_code = 'Meeting Location'
ANDcxa_delete_flag = 0
JOINco_address adrWITH(NOLOCK) ON cxa_adr_key = adr_key
ANDadr_delete_flag = 0
JOINco_address_ext adrxWITH(NOLOCK) ON cxa_adr_key = adr_key_ext
Attached is the actual exe plan for this SQL.
Other details you may need:
1. SQL Server Version 2008
2. Number of records per table excluding co_address_type are 5 million.
Please let me know if you need more details.
Thanks!
Bhushan
January 21, 2011 at 7:18 am
Well you don't have any WHERE criteria, so you are going to be getting every record except where the JOIN criteria don't match. Your two clustered index scans (essentially table scans) are reading 5 million and 6 million rows regardless of the number of rows you are returning. My guess is the indexes you tried to create did not cover the query. Look at your NC indexes to see if every field from the table is avaialble in the index. Ensure the delete flags and join fields are in the general fields. The remaining fields needed for the query can be put in the included columns.
I also suggest you move the hard-coded "JOIN" criteria to a WHERE clause for readability. SQL Server will use it appropriately, but it needs the indexes i described above.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply