February 8, 2012 at 7:17 am
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
February 8, 2012 at 7:20 am
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 7:51 am
Hi,
Thanks for the reply....
In this SQL below objects have been used
--Tables
co_individual_x_organization
co_individual
co_individual_ext
--View
vw_client_ri_club_flex_structure
--Function
fn_client_ri_all_rotary_years
For the table there are so many columns.
So can you able just look to where i need to change something in the SQL?
February 8, 2012 at 7:56 am
kiran.rajenimbalkar (2/8/2012)
So can you able just look to where i need to change something in the SQL?
No. There is too much guess work involved, which is why she asked you to read this article and post the items that it suggests[/url].
At best, your post is like telling a car mechanic the make and model of your car, telling him it's broken, but without telling him in what way it is broken or letting him look at it.
February 8, 2012 at 8:04 am
kiran.rajenimbalkar (2/8/2012)
So can you able just look to where i need to change something in the SQL?
Not without what I asked for, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 9:09 am
Cadavre (2/8/2012)
At best, your post is like telling a car mechanic the make and model of your car, telling him it's broken, but without telling him in what way it is broken or letting him look at it.
Of course there are visible dents in the side panels.
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'
As said before post some detailed info and there will be tons of people willing and able to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2012 at 9:15 am
Sean Lange (2/8/2012)
Cadavre (2/8/2012)
At best, your post is like telling a car mechanic the make and model of your car, telling him it's broken, but without telling him in what way it is broken or letting him look at it.Of course there are visible dents in the side panels.
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'
The join's not that bad. The columns are not in the function, so it's not as bad as it looks.
The table-valued function might or might not be a problem. Without seeing its definition....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2012 at 9:22 am
GilaMonster (2/8/2012)
Sean Lange (2/8/2012)
Cadavre (2/8/2012)
At best, your post is like telling a car mechanic the make and model of your car, telling him it's broken, but without telling him in what way it is broken or letting him look at it.Of course there are visible dents in the side panels.
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'
The join's not that bad. The columns are not in the function, so it's not as bad as it looks.
The table-valued function might or might not be a problem. Without seeing its definition....
True enough, but I was really pointing out that the same function is called twice in a row (which of course this av_begin_of_day is still totally unknown and could also be an issue). Seems that could benefit by moving that function outside of the join to a variable before the select. You are probably right that it isn't as bad as it looks.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 3:11 am
Hi Professional,
Just please tell me which HINT's i need to use in the mentioned above SQL for retrieving the data fast.
Regards,
Kiran
February 9, 2012 at 3:13 am
kiran.rajenimbalkar (2/9/2012)
Hi Professional,Just please tell me which HINT's i need to use in the mentioned above SQL for retrieving the data fast.
Regards,
Kiran
Here's a hint. . . read this article and post the items that it suggests[/url].
February 9, 2012 at 3:31 am
kiran.rajenimbalkar (2/9/2012)
Just please tell me which HINT's i need to use in the mentioned above SQL for retrieving the data fast.
SQl doesn't have a 'run faster' hint, and without what I asked you for, there is little to nothing that can be said about your query.
Please post the things I asked for: table definitions, not names, index definitions and execution plan(as a .sqlplan file, not a picture)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2012 at 6:45 am
kiran.rajenimbalkar (2/9/2012)
Hi Professional,Just please tell me which HINT's i need to use in the mentioned above SQL for retrieving the data fast.
Regards,
Kiran
Kiran, you have visited this site 135 times I thought you would already know the HINT about how to make any SQL query to run faster. I'll give you this gem-hint for free, just add the following statement before your query:
DECLARE @i INT
BEGIN TRY
EXEC ('SET MyQuery PLEASE_RUN_FASTER by Magic = ON');
EXEC ('SET MyQuery RETRIVE_DATA by Magic = IN 1 MILLISECOND');
END TRY
BEGIN CATCH
SET @i = 0
END CATCH
That should help :hehe:
If not, as the last resort, if you could provide what everyone else is asking you...
Follow the link in my signature.
February 9, 2012 at 7:12 am
kiran.rajenimbalkar (2/9/2012)
Hi Professional,Just please tell me which HINT's i need to use in the mentioned above SQL for retrieving the data fast.
Regards,
Kiran
I can also suggest which hints you want to stop using in the SQL above (and probably all sql going forward). NOLOCK.
It is NOT a magic go fast pill. Read this article about dirty reads. http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 9, 2012 at 7:21 am
thannks for you help definetly I will help yo
February 9, 2012 at 7:35 am
Kiran... Please take it positively. Professionals here are volunteers and can’t help you if you can't assist them in helping you.
DDLs, SQL Scripts, Execution Plans are required for Performance Tuning exercise. Just by looking at the query, nobody (even experts here) can help much.
HINTs are not always the solution for performance tuning; there are many more options available. And also, unwise uses of HINTs are equally bad for query performance.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply