|
Jason100
Starting Member
34 Posts |
 Posted - 2009-05-31 : 05:09:25
|
| Hi,guy Sql 2000 VS Sql 2005 ,Which performance is fine? I have a qurey ,It just need 2 ms on ql 2005 but 8ms on Sql 200who can help me performance it ? DECLARE @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 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) 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) AND (@p_AssetsServicedMin IS NULL OR SUM(NetAssets) > @p_AssetsServicedMin) AND (@p_AssetsServicedMax IS NULL OR SUM(NetAssets) < @p_AssetsServicedMax) AND (@p_PlansServicedMin IS NULL OR COUNT(f.DLN) > @p_PlansServicedMin) AND (@p_PlansServicedMax IS NULL OR COUNT(f.DLN) < @p_PlansServicedMax) AND (@p_ParticipantsServicedMin IS NULL OR SUM(Participants) > @p_ParticipantsServicedMin) AND (@p_ParticipantsServicedMax IS NULL OR SUM(Participants) < @p_ParticipantsServicedMax) 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 |
|
diff performance on sql server 2000 VS 2005