RiskIdShortName IN (select * from dbo.fn_ParseString2(@P_RISKID,','))

  • I have trouble with Multi-value parameter.

    WHERE F.RiskIdShortName IN ('CAD,CAD+CSW,EUR,USD,USD+USW') 10 min

    *************************

    DECLARE @P_RISKID varchar(40)

    SET @P_RISKID = 'CAD,CAD+CSW,EUR,USD,USD+USW'

    ....

    WHERE F.RiskIdShortName IN (select * from dbo.fn_ParseString2(@P_RISKID,',')) 2 min

    fn_ParseString2 simply converts 'CAD,CAD+CSW,EUR,USD,USD+USW' into table:

    CAD

    CAD+CSW

    EUR

    USD

    USD+USW

    But you know in SSRS I can't use anything but IN (@P_RISKID) for multi-value parameter.

    But IN (@P_RISKID) version works much longer.

    I'm wondering what's so different between

    IN (@P_RISKID) and

    IN (select * from dbo.fn_ParseString2(@P_RISKID,','))

    Here is an extract from the code:

    (HistVarValue table has one billion records, VarValue table has 50 million records)

    SET @COBDATE1 = '2009-11-12'

    SET @COBDATE2 = '2009-11-03'

    DECLARE @P_RISKID varchar(40)

    SET @P_RISKID = 'CAD,CAD+CSW,EUR,USD,USD+USW'

    .......

    INSERT INTO

    #FlatResults

    (

    HierarchyID,

    COBDate,

    BusinessEntityShortName,

    RiskMeasureShortDescr,

    RiskIdShortName,

    TimeBucketDays,

    Value,

    VarCurrency

    )

    SELECT

    @HIERARCHYID AS HierarchyID,

    VV.COBDate,

    F.BusinessEntityShortName ,

    F.RiskMeasureShortDescr ,

    F.RiskIdShortName ,

    F.TimeBucketDays,

    SUM(VV.Value * FX.Rate) AS Value,

    FX.Cur

    FROM

    #Filter F

    INNER JOIN VarValue VV (NOLOCK) ON F.VarID = VV.VarID

    INNER JOIN xrefSourceTableHierarchy X (NOLOCK) ON VV.SourceID = X.SourceID

    INNER JOIN #tmpBESNList TB ON F.BusinessEntityShortName = TB.BusinessEntityShortName

    INNER JOIN #FXRates FX ON VV.COBDate = FX.COBDate AND VV.VarCurrency = FX.Cur

    WHERE

    VV.COBDate IN (@COBDATE1, @COBDATE2)

    --AND VV.COBDate > GETDATE() - 7

    ANDF.RiskMeasureShortDescr IN ('IRDlt', 'IRGma')

    AND(F.CurveRefShortDescr = @PARAMCURVE OR @PARAMCURVE IS NULL)

    AND F.RiskIdShortName IN (select * from dbo.fn_ParseString2(@P_RISKID,','))

    AND X.HierarchyID = 1

    GROUP BY

    VV.COBDate,

    F.BusinessEntityShortName,

    F.RiskMeasureShortDescr,

    F.RiskIdShortName,

    F.TimeBucketDays,

    FX.Cur

    ......

  • I think if you need somebody to work on more than (!!)1100(!!) lines of code, you should get a consultant in to help you.

    If you'd like us to help you with a specific subject, please reduce the information you provide to the issue your struggling with. For some guidelines on how to do that please see the first link in my signature.

    The only thing your 1000+ lines will do is to chase away people that were originally intended to help you (like I wanted to do before facing the monster code...).

    Sorry.



    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]

  • Sorry.

    I reduced the amount of code.

  • I'm not sure if SSIS would allow it:

    I would store the output from the string parser into a separate table (temp table or table variable), do a direct join in the query:

    select * into #ParseString(items)

    from dbo.fn_ParseString2(@P_RISKID,',')

    --And move the condition from the WHERE clause to the JOIN section

    INNER JOIN #ParseString p ON F.RiskIdShortName = p.item

    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]

  • Hi Lutz and Riga

    Be careful with joining table valued functions. Especially if the string split function works with schema table like spt_values or a Numbers/Tally table.

    Just had some nice performance issues with such an approach, here:

    Strange behavior with TVF execution plan

    Greets

    Flo

  • Florian,

    You were probably right.

    With JOIN #ParseString ON #ParseString.item

    it was running for 3.5 minutes and then crashed with an error:

    Msg 0, Level 20, State 0, Line 0

    A severe error occurred on the current command.

  • Florian Reischl (11/16/2009)


    Hi Lutz and Riga

    Be careful with joining table valued functions. Especially if the string split function works with schema table like spt_values or a Numbers/Tally table.

    Just had some nice performance issues with such an approach, here:

    Strange behavior with TVF execution plan

    Greets

    Flo

    .. thats why I recommended to store the results of the TVF into a temp/intermediate table and use the temp table for the join - just havebeen too lazy to post the whole query again...



    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]

  • That's exactly what I did...

    SET @P_RISKID = 'CAD,USD,EUR,GBP,JPY,CAD+CSW,USD+USW'

    create table #ParseString2(item varchar(15))

    insert into #ParseString2(item)

    select * from dbo.fn_ParseString2(@P_RISKID,',')

    .......

    INNER JOIN #ParseString2 ON F.RiskIdShortName = #ParseString2.item

    and it runs endlessly....

  • Would it be possible for you to show us the execution plan for the modified query (including the creation of the temp table)?



    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]

  • Hi

    @Lutz:

    Oups. Didn't recognize the SELECT INTO. :ermm:

    @riga1966:

    How did you change your query? Do you use a "WHERE IN" a "WHERE EXISTS" or a "JOIN"? How many items does your input string contain? Could you post the current version of the above statement, please?

    Greets

    Flo

  • Florian,

    I use JOIN:

    ......

    INNER JOIN #ParseString2 ON F.RiskIdShortName = #ParseString2.item

  • Hi

    As Lutz already wrote. If it runs almost endless it would be helpful if you could provide the execution plan of this query. To provide this enclose the specific query with the following two lines:

    SET STATISTICS XML ON;

    -- your query

    SET STATISTICS XML OFF;

    This creates a output XML which can be saved as SQL Execution Plan.

    Greets

    Flo

  • I included STATISTICS ON...

    and got an error:

    'XML' is not a recognized statistics option

    The database is running on SQL Server 2000 server.

    I guess that's why?

  • :w00t:

    Could you try to isolate this query into an own batch (without other statements) and executed it in Query Analyzer with "Show Actual Execution Plan" option enabled. You find this option anywhere in menu of QA.

    Save the execution plan and attach it here. (I really hope SSMS 2k8 can display a SS2k execution plan...)

    Thanks

    Flo

  • I'm on SSMS 2005

    and not sure how to save Graphical Execution Plan.

    Plus it never stops executing. It gets stuck at

    INSERT INTO

    #FlatResults

    (........

Viewing 15 posts - 1 through 14 (of 14 total)

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