November 13, 2009 at 1:20 pm
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
......
November 14, 2009 at 5:14 am
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.
November 16, 2009 at 8:21 am
Sorry.
I reduced the amount of code.
November 16, 2009 at 12:28 pm
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
November 16, 2009 at 12:45 pm
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
November 16, 2009 at 1:15 pm
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.
November 16, 2009 at 1:15 pm
Florian Reischl (11/16/2009)
Hi Lutz and RigaBe 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...
November 16, 2009 at 1:23 pm
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....
November 16, 2009 at 1:27 pm
Would it be possible for you to show us the execution plan for the modified query (including the creation of the temp table)?
November 16, 2009 at 1:30 pm
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
November 16, 2009 at 1:33 pm
Florian,
I use JOIN:
......
INNER JOIN #ParseString2 ON F.RiskIdShortName = #ParseString2.item
November 16, 2009 at 1:39 pm
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
November 16, 2009 at 1:45 pm
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?
November 16, 2009 at 1:56 pm
: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
November 16, 2009 at 2:04 pm
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