April 11, 2012 at 5:09 am
Hi,
SELECT
a.dnr_id,
a.dnr_type_cd,
a.dnr_type_desc,
a.dnr_name,
a.dnr_cst_guid,
a.dnr_recog_rlt_guid,
a.dnr_default_credit_org_guid,
a.dnr_default_credit_org_level_name,
a.dnr_default_credit_org_id,
CAST(a.dnr_anonymous_ind AS BIT) dnr_anonymous_ind,
b.dnr_pri_mailing_label_html,
b.dnr_pri_addr_type,
b.dnr_pri_addr_line_1,
b.dnr_pri_addr_line_2,
b.dnr_pri_addr_line_3,
b.dnr_pri_addr_city_name,
b.dnr_pri_state_cd,
b.dnr_pri_state_name,
b.dnr_pri_intl_province,
b.dnr_pri_zip_cd,
b.dnr_pri_country,
b.dnr_on_hold_flag,
a.dnr_pri_phone,
a.dnr_pri_phone_type,
a.dnr_pri_phone_country_cd,
a.dnr_pri_fax,
a.dnr_pri_fax_type,
a.dnr_pri_fax_country_cd,
a.dnr_pri_email,
a.dnr_moves_mgmt_ind,
a.dnr_name_native,
case when a.dnr_last_changed_date<b.dnr_last_changed_date then b.dnr_last_changed_date else a.dnr_last_changed_date end dnr_last_changed_date,
a.dnr_rec_active_ind,
a.dnr_primary_donor_flag
FROM tmp_donor a with(nolock)
LEFT JOIN tmp_donor_address b with(nolock)
ON a.dnr_cst_key=b.dnr_cst_key
OPTION (FAST 1000)
This is my sql which taking long time for fetching the records .
The total numbers of rows are 4919028 getting my this sql.
So requrst you to please provide me how to tune the mentioned SQL.Is there any hint to improve my sql ?
Regards,
Kiran R
April 11, 2012 at 5:34 am
Please post/attach the query execution plan.
April 11, 2012 at 5:37 am
Are the dnr_cst_key columns indexed in both tables?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 11, 2012 at 6:01 am
Yes both columns having index.
So please suggest me what should be hints can I use to get result fast.
April 11, 2012 at 6:07 am
Hints are not what's needed. In fact, I'd drop the FAST(1000) hint to start with. That's going to generate a skewed execution plan if you're returning more than 1000 rows. Without the execution plan, it's hard to know what's up.
Also, you do know that the NOLOCK hints can cause you return extra rows or even miss rows? If you're hitting locking contention I'd suggest using READ_COMMITTED_SNAPSHOT to reduce the locks, but still get the appropriate data returned. Using NOLOCK is a hidden danger.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 11, 2012 at 6:45 am
since there is no WHERE statement, there's not really much to tune, right? that'll be a full table scan regardless of the indexing, I'm thinking.
returning 4.9 million rows will take time, and that's going to lean more towards memory, disk I/O, network backbone speed, available memory to store the query on the client, etc.
Lowell
April 11, 2012 at 6:55 am
Thanks all for helping me out of this issue.
April 11, 2012 at 7:04 am
Lowell (4/11/2012)
since there is no WHERE statement, there's not really much to tune, right? that'll be a full table scan regardless of the indexing, I'm thinking.returning 4.9 million rows will take time, and that's going to lean more towards memory, disk I/O, network backbone speed, available memory to store the query on the client, etc.
I didn't even note the missing WHERE clause. You're 100% on. Plus, adding in hints is just going to make it worse becuase you may see it trying to use a seek when it should use a scan because of the FAST hint.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply