April 9, 2013 at 6:59 am
Hi,
I Need to remove subqueries and need to use Joins following query..
SELECT DISTINCT set_name,
a.seq_no AS set_id
FROM defulat_sets a
WHERE set_type = @set_type
AND ISNULL(qualifier1,'') = @qualifier1 AND ISNULL(qualifier2,'') = @qualifier2
AND ISNULL(qualifier3,'') = @qualifier3 AND ISNULL(qualifier4,'') = @qualifier4
--check against specialties
AND ((specialties LIKE '%'+@dept+'%' OR specialties LIKE '%all%') AND specialties NOT LIKE '%not '+@dept+'%')
--check against providers
and cast(a.seq_no as varchar(36)) in (select distinct set_id from default_provs where set_id=cast(a.seq_no as varchar(36))
and (
--ALL is in the list and there is not a corresponding NOT
(last_name='ALL' and set_id not in (select distinct set_id
from default_provs where set_id=cast(a.seq_no as varchar(36)) and provider_id=@provider_id
and not_ind=1))
OR
--There is an ID and not a corresponding NOT id
(provider_id=@provider_id and set_id not in (select distinct set_id
from default_provs where set_id=cast(a.seq_no as varchar(36)) and provider_id=@provider_id
and not_ind=1))
)
)
order by set_name
Please give your input.
April 9, 2013 at 8:12 am
I ran through a formatter so it is possible to see what is going on...
SELECT DISTINCT set_name
,a.seq_no AS set_id
FROM defulat_sets a
WHERE set_type = @set_type
AND ISNULL(qualifier1, '') = @qualifier1
AND ISNULL(qualifier2, '') = @qualifier2
AND ISNULL(qualifier3, '') = @qualifier3
AND ISNULL(qualifier4, '') = @qualifier4
--check against specialties
AND (
(
specialties LIKE '%' + @dept + '%'
OR specialties LIKE '%all%'
)
AND specialties NOT LIKE '%not ' + @dept + '%'
)
--check against providers
AND cast(a.seq_no AS VARCHAR(36)) IN (
SELECT DISTINCT set_id
FROM default_provs
WHERE set_id = cast(a.seq_no AS VARCHAR(36))
AND (
--ALL is in the list and there is not a corresponding NOT
(
last_name = 'ALL'
AND set_id NOT IN (
SELECT DISTINCT set_id
FROM default_provs
WHERE set_id = cast(a.seq_no AS VARCHAR(36))
AND provider_id = @provider_id
AND not_ind = 1
)
)
OR
--There is an ID and not a corresponding NOT id
(
provider_id = @provider_id
AND set_id NOT IN (
SELECT DISTINCT set_id
FROM default_provs
WHERE set_id = cast(a.seq_no AS VARCHAR(36))
AND provider_id = @provider_id
AND not_ind = 1
)
)
)
)
ORDER BY set_name
Why are you trying to use joins instead of subselects? I am going to guess that it is because the performance of this is horrible? This appears to be a type of catch all query. The reason the performance is so bad (and probably intermittently) is because the optimal execution plan changes based on all sorts of criteria.
Take a look at this article that should help sort out this query. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
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/
April 9, 2013 at 9:01 am
Hi,
Subqueries is taking more time.. so We want to increase the performance, is there anyway we can increase the performance avoiding subqueries..?
Please help me on this
Regards,
tony
April 9, 2013 at 9:06 am
tonyarp05 61903 (4/9/2013)
Hi,Subqueries is taking more time.. so We want to increase the performance, is there anyway we can increase the performance avoiding subqueries..?
Please help me on this
Regards,
tony
Did you read the article I suggested? Your performance issues here are not a direct result of your subqueries. It is a result of sub-optimal execution plans because your query has a number of different paths. The article that Gail wrote explains the exact situation you are facing and how to make it work.
_______________________________________________________________
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/
April 9, 2013 at 9:08 am
tonyarp05 61903 (4/9/2013)
Hi,Subqueries is taking more time.. so We want to increase the performance, is there anyway we can increase the performance avoiding subqueries..?
Please help me on this
Regards,
tony
Your other probably performance bottleneck is the LIKE searching you are doing.
AND (
(
specialties LIKE '%' + @dept + '%'
OR specialties LIKE '%all%'
)
AND specialties NOT LIKE '%not ' + @dept + '%'
These are all non-SARGable. That means that any indexes are ignored and a full scan will happen instead.
_______________________________________________________________
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/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply