How to performance this query

Question

How to performance this query

ljun100

Grasshopper

Points: 15
More actions
May 31, 2009 at 3:22 am

#210312

CREATE PROCEDURE [dbo].[getScreenProvider]
@p_resultSize SMALLINT = 12,
@p_ProviderServiceCodes VARCHAR(50) = NULL,
@p_Regions VARCHAR(50) = NULL,
@p_States VARCHAR(255) = NULL,
@p_MetroIds VARCHAR(255) = NULL,
@p_AssetsServicedMin FLOAT = NULL,
@p_AssetsServicedMax FLOAT = NULL,
@p_AvgFeesMin FLOAT = NULL,
@p_AvgFeesMax FLOAT = NULL,
@p_AvgFeePerParticipantMin INT = NULL,
@p_AvgFeePerParticipantMax INT = NULL,
@p_FeeOfAssetsPercentMin DECIMAL(6,3) = NULL,
@p_FeeOfAssetsPercentMax DECIMAL(6,3) = NULL,
@p_PlansServicedMin INT = NULL,
@p_PlansServicedMax INT = NULL,
@p_ParticipantsServicedMin INT = NULL,
@p_ParticipantsServicedMax INT = NULL,
@p_PlanTypes VARCHAR(50) = NULL,
@p_IsNationalProvider BIT = NULL,
@p_IsMiddleMarketFocus BIT = NULL,
@p_Year SMALLINT = 2006
AS
BEGIN
SET NOCOUNT ON;
SELECT res.*,
pro.ProviderName,
pro.Address1,
pro.Address2,
pro.Address3,
pro.City,
pro.State,
pro.Zip,
pro.Phone,
pro.Homepage,
pro.LogoUrl
FROM (
SELECT TOP 1000 --(@p_resultSize)
p.ProviderEIN,
SUM(NetAssets)/1000000 AS TotalAssetsMil,
SUM(CAST(ProviderFees AS FLOAT)) AS TotalFees,
AVG(CAST(ProviderFees AS FLOAT)) AS AvgFees,
COUNT(f.DLN) AS PlansServiced,
SUM(Participants) AS ParticipantsServiced
FROM dbo.Provider p
JOIN dbo.ProviderPlanInfo i
ON p.ProviderEIN = i.ProviderEIN
LEFT JOIN dbo.Plans f
ON f.DLN=i.DLN
LEFT JOIN dbo.DLNStateLink d
ON d.DLN=i.DLN
LEFT JOIN dbo.States s
ON s.State = d.State
LEFT JOIN dbo.MetroPlanLink m
ON m.DLN=i.DLN
LEFT JOIN dbo.PlanHistoricalOperation h
ON h.DLN=i.DLN
WHERE (@p_ProviderServiceCodes IS NULL
OR i.ProviderServiceCode IN (SELECT Value FROM dbo.fn_SplitStringToTable(@p_ProviderServiceCodes,',')))
AND(@p_Regions IS NULL
OR s.Region IN (SELECT Value FROM dbo.fn_SplitStringToTable(@p_Regions,',')))
AND(@p_States IS NULL
OR s.State IN (SELECT Value FROM dbo.fn_SplitStringToTable(@p_States,',')))
AND(@p_MetroIds IS NULL
OR MetroId IN (SELECT Value FROM dbo.fn_SplitStringToTable(@p_MetroIds,',')))
AND(@p_PlanTypes IS NULL
OR f.PlanType IN (SELECT Value FROM dbo.fn_SplitStringToTable(@p_PlanTypes,',')))
AND(@p_IsNationalProvider IS NULL
OR p.IsNationalProvider = @p_IsNationalProvider)
AND(@p_IsMiddleMarketFocus IS NULL
OR p.IsMiddleMarketFocus = @p_IsMiddleMarketFocus)
AND h.[YearId]=@p_Year
GROUP BY p.ProviderEIN
HAVING (@p_AvgFeesMin IS NULL OR AVG(CAST(ProviderFees AS FLOAT)) > @p_AvgFeesMin)
AND (@p_AvgFeesMax IS NULL OR AVG(CAST(ProviderFees AS FLOAT)) < @p_AvgFeesMax)
--------Average Fee Per Participant range
AND (@p_AvgFeePerParticipantMin IS NULL OR SUM(CAST(ProviderFees AS FLOAT))/SUM(Participants) > @p_AvgFeePerParticipantMin)
AND (@p_AvgFeePerParticipantMax IS NULL OR SUM(CAST(ProviderFees AS FLOAT))/SUM(Participants) < @p_AvgFeePerParticipantMax)
--------AssetsServiced range
AND (@p_AssetsServicedMin IS NULL OR SUM(NetAssets) > @p_AssetsServicedMin)
AND (@p_AssetsServicedMax IS NULL OR SUM(NetAssets) < @p_AssetsServicedMax)
--------PlansServiced range
AND (@p_PlansServicedMin IS NULL OR COUNT(f.DLN) > @p_PlansServicedMin)
AND (@p_PlansServicedMax IS NULL OR COUNT(f.DLN) < @p_PlansServicedMax)
--------ParticipantsServiced range
AND (@p_ParticipantsServicedMin IS NULL OR SUM(Participants) > @p_ParticipantsServicedMin)
AND (@p_ParticipantsServicedMax IS NULL OR SUM(Participants) < @p_ParticipantsServicedMax)
--------Fees
And (@p_FeeOfAssetsPercentMin IS NULL OR SUM(CAST(ProviderFees AS decimal(18,2)))*100/SUM(NetAssets)>@p_FeeOfAssetsPercentMin)
And (@p_FeeOfAssetsPercentMax IS NULL OR SUM(CAST(ProviderFees AS decimal(18,2)))*100/SUM(NetAssets)<@p_FeeOfAssetsPercentMax)
ORDER BY COUNT(f.DLN) DESC
) res
JOIN dbo.Provider pro WITH(NOLOCK)
ON pro.ProviderEIN = res.ProviderEIN
ORDER BY PlansServiced DESC
END
This is the exec plan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I don't consider myself a tuning expert at all but I recognized a few things:

1) The parameter @p_Regions,@p_States, @p_MetroIds seems to hold comma separate lists. Instead of calling the split function inside the main query, get the data in table variables (since the parameter are of varchar(255) I wouldn't expect to get too many rows, so table variables should do it).

2) If there are one or more tables that are fairly large consider using CTEs to reduce the rows to work with in the group by join.

3) Since you're running a "catch-all-query" you might check the following link http://www.sommarskog.se/dyn-search-2005.html to avoid the [IS NULL OR] statement

I'm sure one or more of the experts here on SSC will show up shortly...

Please note that there is no exec plan attached. That's probably the first thing the gurus will ask for 🙂

Second thing you might want to add is the source code of your split function.

Edit: typo fixed

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Table definitions, index definitions and execution plan (saved as a .sqlplan file, zipped and attached) please.

I'll be blunt, that query will not perform well and there's probably not much that can be done without a partial re-write. You can start by reading this.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

And when you post the execution plan, please make it the actual execution plan. Thanks.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning