Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Help ...Performance Question

Status
Not open for further replies.
fmrock

fmrock

Programmer
Sep 5, 2006
510
US
I have the following stored procedure...

Code: 
CREATE PROCEDURE [dbo].[Quicksilver_Get_Charges_Pmts_Adj_Amt]
	(
		@sSiteType varchar(5)
		,@sState varchar(5)
		,@sStartMonth varchar(6)
		,@sEndMonth varchar(6)
		,@sLOB varchar(50)
		)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT
	T1.PRType
	, isnull(SUM(CASE WHEN (T1.IT='B') THEN T1.Total_Amt ELSE 0 END),0) "B"
	, isnull(SUM(CASE WHEN (T1.IT='D') THEN T1.Total_Amt ELSE 0 END),0) "D"
	, isnull(SUM(CASE WHEN (T1.IT='E') THEN T1.Total_Amt ELSE 0 END),0) "E"
	, isnull(SUM(CASE WHEN (T1.IT='H') THEN T1.Total_Amt ELSE 0 END),0) "H"
	, isnull(SUM(CASE WHEN (T1.IT='O') THEN T1.Total_Amt ELSE 0 END),0) "O"
	, isnull(SUM(CASE WHEN (T1.IT='R') THEN T1.Total_Amt ELSE 0 END),0) "R"
	, isnull(SUM(CASE WHEN (T1.IT='S') THEN T1.Total_Amt ELSE 0 END),0) "S"
	, isnull(SUM(T1.Total_Amt),0) "Total"	

FROM
(
SELECT     
	Q.Site
	, isnull(M.NewIT,Q.IT) "IT"
	, Q.LockBox
	, Q.YearMonth
	, Q.PRType
	, Q.PRCode
	, Q.Total_Count
	, Q.Total_Amt
FROM
	Quicksilver AS Q
	JOIN fn_QuickSilver_SiteList(@sSiteType,@sState) SL ON (Q.Site=SL.sSiteID)
	LEFT JOIN Quicksilver_PRCode_IT_Map M ON (Q.PRType=M.PRType) AND (Q.PRCode=M.PRCode)
	JOIN fn_ParseDelimitedStrings(@sLOB,',') L ON (Q.Location_Code=convert(int, L.Value))
WHERE     
	(Q.YearMonth BETWEEN @sStartMonth AND @sEndMonth)
	AND (Q.PRType IN ('C', 'P', 'A'))
	
)T1
WHERE 
	(T1.IT IN ('B','D','E','H','O','R','S'))
GROUP BY
	T1.PRType

And it calls this function...
Code: 
ALTER FUNCTION [dbo].[fn_QuickSilver_SiteList]
(
	@SiteType varchar(3)
	, @SiteState varchar(2)
)
RETURNS @t TABLE (sSiteID varchar(10))

BEGIN

IF @SiteType = 'COM'
	BEGIN
		INSERT @t (sSiteID)
		SELECT DISTINCT CPCODE 
		FROM MEDGROUP G	
		JOIN MEDLOCATIONS L ON G.CPCODE = L.LOCCPCODE 
		WHERE (REPLACE(CPSHORTNAME, ' ', '') NOT LIKE '%-OPM%')
		AND ((@SiteState = '') OR (L.LOCSTATE=@SiteState))
	END
IF @SiteType = 'OPM'
	BEGIN
		INSERT @t (sSiteID)
		SELECT DISTINCT CPCODE 
		FROM MEDGROUP G	
		JOIN MEDLOCATIONS L ON G.CPCODE = L.LOCCPCODE
		WHERE (REPLACE(CPSHORTNAME, ' ', '') LIKE '%-OPM%')
		AND ((@SiteState = '') OR (L.LOCSTATE=@SiteState))
	END
IF @SiteType = 'ALL'
	BEGIN
		INSERT @t (sSiteID)
		SELECT DISTINCT LOCCPCODE 
		FROM MEDLOCATIONS L 
		WHERE ((@SiteState = '') OR (L.LOCSTATE=@SiteState))
	END

RETURN
END

Could I eliminate this function...

Our SQL Tuning Advisory errors out when analyzing the trace on this application.

Also, the Quicksilver table has just less than a million rows...

Any help or advice would be great.

 
Sort by date Sort by votes
I think your function can be replaced with this code.

Code: 
Declare @SiteType VarChar(3),
        @SiteState VarChar(2)
		
Select  @SiteType = 'OPM',
        @SiteState = 'NY'
        
SELECT  DISTINCT CPCODE 
FROM    MEDGROUP G    
        JOIN MEDLOCATIONS L 
          ON G.CPCODE = L.LOCCPCODE
WHERE   ((@SiteState = '') OR (L.LOCSTATE=@SiteState))
        And (
          @SiteType = 'OPM' And REPLACE(CPSHORTNAME, ' ', '') LIKE '%-OPM%'
          Or 
          @SiteType = 'COM' And REPLACE(CPSHORTNAME, ' ', '') NOT LIKE '%-OPM%'
          )

Can you please try this? Try changing the SiteType and SiteState to make sure it is returning the correct data.

Also, please let me know how long it takes for this code to run and also how long it takes for your original stored procedure to run.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
sorry. minor mistake with the previous code.

Code: 
Declare @SiteType VarChar(3),
        @SiteState VarChar(2)
        
Select  @SiteType = 'OPM',
        @SiteState = 'NY'
        
SELECT  DISTINCT CPCODE 
FROM    MEDGROUP G    
        JOIN MEDLOCATIONS L 
          ON G.CPCODE = L.LOCCPCODE
WHERE   ((@SiteState = '') OR (L.LOCSTATE=@SiteState))
        And (
          (@SiteType = 'OPM' And REPLACE(CPSHORTNAME, ' ', '') LIKE '%-OPM%')
          Or 
          (@SiteType = 'COM' And REPLACE(CPSHORTNAME, ' ', '') NOT LIKE '%-OPM%')
          )

-George
Microsoft SQL Server MVP
[url=http://blogs.lessthandot.com/index.php/All/?disp=authdir&author=10]My Blogs[/url]
[url=http://sqlcop.lessthandot.com]SQLCop[/url]
[url=http://www.twitter.com/gmmastros]twitter[/url]
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I just had to add logic for the all.

Code: 
Declare @SiteType VarChar(3),
        @SiteState VarChar(2)
        
Select  @SiteType = 'ECI',
        @SiteState = 'IL'
        
SELECT  DISTINCT CPCODE 
FROM    MEDGROUP G    
        JOIN MEDLOCATIONS L 
          ON G.CPCODE = L.LOCCPCODE
WHERE   ((@SiteState = '') OR (L.LOCSTATE=@SiteState))
        And (
          (@SiteType = 'OPM' And REPLACE(CPSHORTNAME, ' ', '') LIKE '%-OPM%')
          Or 
          (@SiteType = 'COM' And REPLACE(CPSHORTNAME, ' ', '') NOT LIKE '%-OPM%')

			Or 
          (@SiteType = 'ALL')
          )

Would you recommend removing the function from my stored proc or just updating the function to use this logic.
 
Upvote 0 Downvote
Since we got this down to a single query, I would recommend removing the function from the stored procedure and replacing it with the query.

like this:

Code: 
CREATE PROCEDURE [dbo].[Quicksilver_Get_Charges_Pmts_Adj_Amt]
    (
        @sSiteType varchar(5)
        ,@sState varchar(5)
        ,@sStartMonth varchar(6)
        ,@sEndMonth varchar(6)
        ,@sLOB varchar(50)
        )

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

SELECT
    T1.PRType
    , isnull(SUM(CASE WHEN (T1.IT='B') THEN T1.Total_Amt ELSE 0 END),0) "B"
    , isnull(SUM(CASE WHEN (T1.IT='D') THEN T1.Total_Amt ELSE 0 END),0) "D"
    , isnull(SUM(CASE WHEN (T1.IT='E') THEN T1.Total_Amt ELSE 0 END),0) "E"
    , isnull(SUM(CASE WHEN (T1.IT='H') THEN T1.Total_Amt ELSE 0 END),0) "H"
    , isnull(SUM(CASE WHEN (T1.IT='O') THEN T1.Total_Amt ELSE 0 END),0) "O"
    , isnull(SUM(CASE WHEN (T1.IT='R') THEN T1.Total_Amt ELSE 0 END),0) "R"
    , isnull(SUM(CASE WHEN (T1.IT='S') THEN T1.Total_Amt ELSE 0 END),0) "S"
    , isnull(SUM(T1.Total_Amt),0) "Total"    

FROM
(
SELECT     
    Q.Site
    , isnull(M.NewIT,Q.IT) "IT"
    , Q.LockBox
    , Q.YearMonth
    , Q.PRType
    , Q.PRCode
    , Q.Total_Count
    , Q.Total_Amt
FROM
    Quicksilver AS Q
    JOIN [!](
         SELECT  DISTINCT CPCODE As sSiteId
         FROM    MEDGROUP G    
                 JOIN MEDLOCATIONS L 
                   ON G.CPCODE = L.LOCCPCODE
         WHERE   ((@SiteState = '') OR (L.LOCSTATE=@SiteState))
                 And (
                   (@SiteType = 'OPM' And REPLACE(CPSHORTNAME, ' ', '') LIKE '%-OPM%')
                   Or 
                   (@SiteType = 'COM' And REPLACE(CPSHORTNAME, ' ', '') NOT LIKE '%-OPM%')
         
                     Or 
                   (@SiteType = 'ALL')
                   )
          )[/!] SL ON (Q.Site=SL.sSiteID)
    LEFT JOIN Quicksilver_PRCode_IT_Map M ON (Q.PRType=M.PRType) AND (Q.PRCode=M.PRCode)
    JOIN fn_ParseDelimitedStrings(@sLOB,',') L ON (Q.Location_Code=convert(int, L.Value))
WHERE     
    (Q.YearMonth BETWEEN @sStartMonth AND @sEndMonth)
    AND (Q.PRType IN ('C', 'P', 'A'))
    
)T1
WHERE 
    (T1.IT IN ('B','D','E','H','O','R','S'))
GROUP BY
    T1.PRType

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
That is exactly what I did. My asp.net does seem a bit quicker.

I have that other function

fn_ParseDelimitedStrings(@sLOB,',') L ON (Q.Location_Code=convert(int, L.Value))

My app is passing in a location number or set of location numbers.

Code: 
           <Items>
            <telerik:RadComboBoxItem Text="All" Value="1,2,3,4,5,6,7,8,9,10" />
            <telerik:RadComboBoxItem Text="AA" Value="1,2,3" />
            <telerik:RadComboBoxItem Text="AA & BB" Value="1,2,3,4" />
            <telerik:RadComboBoxItem Text="CC" Value="4" />
            <telerik:RadComboBoxItem Text="DD" Value="5" />
           </Items>

Here is the function code.

Code: 
ALTER Function [dbo].[fn_ParseDelimitedStrings]
	(@String nvarchar(2000), @Delimiter char(1))
Returns @Values Table 
(
RowId int Not Null Identity(1,1) Primary Key
,Value nvarchar(255) Not Null
)
As
Begin
	Declare @startPos smallint ,@endPos smallint

	If (Right(@String, 1) != @Delimiter)
		BEGIN
		Set @String = @String + @Delimiter
		END

		Set @startPos = 1
		Set @endPos = CharIndex(@Delimiter, @String)

		While @endPos > 0
		Begin
			Insert @Values(Value)
			Select LTrim(RTrim(SubString(@String, @startPos, @endPos - @startPos)))

			-- remove the delimiter just used
			Set @String = Stuff(@String, @endPos, 1, '')

			-- move string pointer to next delimiter
			Set @startPos = @endPos

            Set @endPos = CharIndex(@Delimiter, @String)
		End
Return

End

Think it is possible to get rid of this function also?

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
326
pjw001
pjw001
Andrzejek
  • Locked
  • Question
SSMS question 1
Replies
4
Views
664
Sana Shazadi
S
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
190
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
483
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
228
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top