February 10, 2012 at 5:31 am
Kiran, as someone else mentioned these forums are "staffed" by volunteers. Thus the forums are appropriate for small, short, simple-to-moderately complex topics. That query really does not look like any of those things. Depending on the functions and views involved I could easily see spending several hours to perhaps a few days optimizing that one query. I have 15 years of experience doing this type of work so I do have some basis for making my assertion. If you want this query tuned you REALLY should hire a performance tuning professional to assist you.
Oh, and when people give you a link and ask you go give them more information based on that link so they can help you you should read the link and do as they ask. Especially when it is someone with almost 3 orders of magnitude more points on this forum than you have. Actually multiple people pointed you to references to properly give necessary information about your problem. Ask yourself if you think you could refactor a query without any knowledge of the underlying objects...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 11, 2012 at 3:44 am
kiran.rajenimbalkar (2/8/2012)
Hi Professional,I need your help to solve this issue.
I need to tune this my SQL mentioned below,
SELECT ind.ind_cst_key,ind_first_name_noaccent_ext,
ind_last_name_noaccent_ext,
ind_membership_id_ext,
DESCRIPTION,
ixo_org_cst_key,
q21_district_key,
q21_district_name
FROM ( SELECT ixo_ind_cst_key AS ind_cst_key,
ristartdt,
rienddt,
DESCRIPTION
FROM co_individual_x_organization (NOLOCK)
FULL OUTER JOIN fn_client_ri_all_rotary_years()
ON ((DATEADD(YYYY,1, dbo.av_begin_of_day(GETDATE())) = calyear) OR (dbo.av_begin_of_day(GETDATE()) = calyear))
WHERE ixo_rlt_code = 'Member'
AND ixo_delete_flag = 0
AND ((ixo_start_date BETWEEN ristartdt AND rienddt) OR ixo_start_date < ristartdt)
AND (ixo_end_date IS NULL OR ixo_end_date >= rienddt OR (ixo_end_date BETWEEN ristartdt AND rienddt))
EXCEPT
SELECT ixo_ind_cst_key AS ind_cst_key,
ristartdt,
rienddt,
DESCRIPTION
FROM co_individual_x_organization(NOLOCK)
RIGHT JOIN fn_client_ri_all_rotary_years()
ON ((DATEADD(YYYY,1, dbo.av_begin_of_day(GETDATE())) = calyear) OR (dbo.av_begin_of_day(GETDATE()) = calyear))
WHERE ixo_rlt_code = 'Assistant Governor'
AND ixo_delete_flag = 0
AND ((ixo_start_date BETWEEN ristartdt AND rienddt) OR ixo_start_date < ristartdt)
AND (ixo_end_date IS NULL OR ixo_end_date >= rienddt OR (ixo_end_date BETWEEN ristartdt AND rienddt))) unassigned_members
JOIN co_individual_x_organization (NOLOCK) ON unassigned_members.ind_cst_key = ixo_ind_cst_key
AND ixo_rlt_code = 'Member'
AND ixo_delete_flag = 0
AND ((ixo_start_date BETWEEN unassigned_members.ristartdt AND unassigned_members.rienddt) OR ixo_start_date < unassigned_members.ristartdt)
AND (ixo_end_date IS NULL OR ixo_end_date >= unassigned_members.rienddt OR (ixo_end_date BETWEEN unassigned_members.ristartdt AND unassigned_members.rienddt))
JOIN co_individual (NOLOCK) ind ON ixo_ind_cst_key = ind.ind_cst_key
AND ind_deceased_flag = 0
AND ind_delete_flag = 0
JOIN co_individual_ext (NOLOCK) ON ind_cst_key_ext = ind.ind_cst_key
JOIN vw_client_ri_club_flex_structure (NOLOCK) ON ixo_org_cst_key = q21_club_key
AND (q21_end_date IS NULL OR q21_end_date > GETDATE())
Its taking very much time up to 13 minutes.
So request you to please can you help me out from this issue.
Request you to please do any modification if you need.
Regards,
Kiran
Without the information previously requested, it's nearly impossible to know where to help on this. My only "hint" would be to look at the execution plan yourself. Step 1 might be to look at the rowcounts for the arrows between the symbols. If you find one where the number of rows greatly exceeds the number of rows in the table or pair of tables associated with the arrow, then you may have an "accidental cross join" (a many-to-many join) caused by incorrect criteria.
It could also be the table valued function you're joining to especially if it's a multi-line table valued function instead of an inline table valued function.
But, as everyone has stated, without you providing the requested information, we're just guessing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply