July 4, 2011 at 5:13 am
That's the best one, so far!
July 4, 2011 at 6:09 am
quan23 (7/4/2011)
I don't think it sounds like the cascade that is the problem, otherwise you wouldn't be able to select values for all the parametersHave you set up the parameters to allow multiple selection, and are you using IN rather than = in your sql code?
Thanks for your reply. I have checked the multi-value option in the Report Parameter properties for al the parameters where multiple-values can be selected. Also, I am using IN instead of '=' in the stored procedure.
I remember assigning nvarchar (4000) as the datatype length to the string columns in one of the similar reports i had worked on earlier. However, it does'nt work if I apply the same functionality in this report. I am stuck and dont know what else to do.
Regards,
Paul
July 4, 2011 at 6:12 am
Please post the sp code here.
July 4, 2011 at 6:56 am
Ninja's_RGR'us (7/4/2011)
Please post the sp code here.
Hi, Thanks, pls find the SP code below(its actually a sub-procedure):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_QM_Details_Sailing]
@From_date [varchar](30),
@To_date [varchar](30),
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@scnolike [nvarchar](4000),
@CPSHCNNPLike [nvarchar](4000),
@Commoditylike [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000)
WITH RECOMPILE
AS
SET NOCOUNT ON
SET ANSI_WARNINGS Off
SELECT BLNO, SAILING_DT, ON_BOARD_SAILING_DT, DIRECTION, [F/E], SCNO, SHIPPER,FORWARDER,
CONSIGNEE, NOTIFY, [CONTROL PARTY], [NAYOSE NAME], COMMODITY, ORIGIN, POR, POL, POD, DEST, PDL, TRADE_CD, SAISAN_SERVICE, VESSEL, VOYAGE, LEG, TEU,
(VESSEL + VOYAGE) AS VSLVOY
FROM tbl_TMIS_DATASET
WHERE
SAILING_DT >= Convert(datetime, @From_date, 101) and
SAILING_DT <= Convert(datetime, @To_date, 101) and
DIRECTION IN (@direction) and
[F/E] IN (Left(@fe,1)) and
origin IN (@Countryofreceipt) and
POR IN (@POR) and
POL IN (@POL) and
POD IN (@POD) and
DEST IN (@Countryofdelivery) and
PDL IN (@PDL) and
TRADE_CD IN (@Trade) and
SAISAN_SERVICE IN (@Service) and
(VESSEL + VOYAGE) IN (@VesselVoyage) AND
(SCNO like '%' + @scnolike + '%' )
AND
([CONTROL PARTY] like '%' + @CPSHCNNPLike + '%'
or SHIPPER like '%' + @CPSHCNNPLike + '%'
or CONSIGNEE like '%' + @CPSHCNNPLike + '%'
or NOTIFY like '%' + @CPSHCNNPLike + '%')
AND Commodity like '%' + @Commoditylike + '%'
-----------------------------------------------------------------
the above SP is called from the main SP:
-----------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_QM_Details]
@Choose_Date [nvarchar](10),
@From_date [varchar](30),
@To_date [varchar](30),
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@scnolike [nvarchar](4000),
@CPSHCNNPLike [nvarchar](4000),
@Commoditylike [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000)
WITH RECOMPILE
AS
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
if @Choose_Date = 'SAILING'
exec usp_QM_Details_Sailing @From_date,@To_date,
@direction, @fe ,
@scnolike , @CPSHCNNPLike , @Commoditylike,
@Countryofreceipt,
@POR , @POL , @POD , @Countryofdelivery , @PDL , @Trade ,
@Service , @VesselVoyage
if @Choose_Date = 'ONBOARD'
exec usp_QM_Details_Onboard @From_date,@To_date,
@direction, @fe ,
@scnolike , @CPSHCNNPLike , @Commoditylike,
@Countryofreceipt,
@POR , @POL , @POD , @Countryofdelivery , @PDL , @Trade ,
@Service , @VesselVoyage
July 4, 2011 at 7:00 am
Ninja's_RGR'us (7/4/2011)
The usual suspect is that you have used this type of syntaxe which works in the ssrs query generator, but then fails in the sp.where something IN (@MultiSelectParam).
If you converted your datasource to a stored proc, then you need to split the values. The best way for this type of reports is to build a temp table (with PK) and dump the values in there. The PK will force stats and give better plan. I've not tested simply adding stats instead of PK.... but on a 3-4 rows table it won't make much of a difference perf wise.
The other option is that 1 of the filters just doesn't return anything. It's hard to tell which one it is without full access to your systems.
and...
July 4, 2011 at 7:00 am
Ninja's_RGR'us (7/4/2011)
That's the best one, so far!
July 4, 2011 at 7:39 am
Using the DelimitedSplit8K function that The Ninja provided the link to, you would need to change the where clause in the sp from
DIRECTION IN (@direction)
to
DIRECTION IN (select item from dbo.DelimitedSplit8k(@direction,','))
If you need the parameter below to be multi-select then things will get a bit more complicated as you can't do LIKE with an IN without a bit more work
(SCNO like '%' + @scnolike + '%' )
July 4, 2011 at 7:42 am
quan23 (7/4/2011)
Using the DelimitedSplit8K function that The Ninja provided the link to, you would need to change the where clause in the sp fromDIRECTION IN (@direction)
to
DIRECTION IN (select item from dbo.DelimitedSplit8k(@direction,','))
If you need the parameter below to be multi-select then things will get a bit more complicated as you can't do LIKE with an IN without a bit more work
(SCNO like '%' + @scnolike + '%' )
Actually no. It works better with a temp table intermediate step. The reason is that you get better stats and a much better execution plan.
yes it's annoying but I preffer 5 seconds over 5 minutes any day!
July 4, 2011 at 7:54 am
Would you make the creation of the temp table conditional so you only create it if the passed in parameter contains a ',' character?
(I am assuming here that the actual dataload will not contain any commas...)
July 4, 2011 at 7:58 am
quan23 (7/4/2011)
Would you make the creation of the temp table conditional so you only create it if the passed in parameter contains a ',' character?(I am assuming here that the actual dataload will not contain any commas...)
Can't. You'll be using the temp tables later in the query.
Also this MIGHT be a case where dynamic sql is better than inline. Depends on how the users use the parameters. I've found out that even if they have 1 bazillion options, the users tend to always use the same 2-3 combos and them play with it in Excel.
July 4, 2011 at 8:12 am
Ninja's_RGR'us (7/4/2011)
quan23 (7/4/2011)
Would you make the creation of the temp table conditional so you only create it if the passed in parameter contains a ',' character?(I am assuming here that the actual dataload will not contain any commas...)
Can't. You'll be using the temp tables later in the query.
Also this MIGHT be a case where dynamic sql is better than inline. Depends on how the users use the parameters. I've found out that even if they have 1 bazillion options, the users tend to always use the same 2-3 combos and them play with it in Excel.
Thanks again. The report now starts to render but then ends with the outofmemory exception. Is that the reason you have mentioned the use of temp table for faster execution. I dont know how to go about it ? Can you give me some idea about using the temp table in th query ?
July 4, 2011 at 8:13 am
Fair enough, I guess the overhead of having an IF to decide whether or not to build the table, and then another IF to tell the query whether to look for a temp table or use the original passed in parameter would be an unnecessary burden compared to always using the temp table even if it only includes a single record
So would you do something likes this at the start of the proc to populate the temp table
select Item into TmpDirections
from dbo.DelimitedSplit8k(@Direction,',')
and then do this in the where clause
DIRECTION IN (select Item from TmpDirections)
And then drop all the temp tables at the end of the sp?
The only issue I can see with that is potential conflict if multiple users try and call the proc at the same time then it will produce an error if User2 tries to create TmpDirections before User1 drops it
July 4, 2011 at 8:15 am
You can do select into #tmp. Which is a local temp table.
I personally choose to explicitly drop them for code clarity. But it's not necessary.
This is a nice read too.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
July 4, 2011 at 8:17 am
pwalter83 (7/4/2011)
Ninja's_RGR'us (7/4/2011)
quan23 (7/4/2011)
Would you make the creation of the temp table conditional so you only create it if the passed in parameter contains a ',' character?(I am assuming here that the actual dataload will not contain any commas...)
Can't. You'll be using the temp tables later in the query.
Also this MIGHT be a case where dynamic sql is better than inline. Depends on how the users use the parameters. I've found out that even if they have 1 bazillion options, the users tend to always use the same 2-3 combos and them play with it in Excel.
Thanks again. The report now starts to render but then ends with the outofmemory exception. Is that the reason you have mentioned the use of temp table for faster execution. I dont know how to go about it ? Can you give me some idea about using the temp table in th query ?
If it's vs throwing that error, deploy the report and see if the prod server handles it. It usually does.
The only other solution is to remove groupings, sortings or aything else that requires a lot of ressources to execute... rarely possible with the user's requirements.
July 4, 2011 at 8:22 am
Aha, I was thinking that local #tmp tables don't have statistics generated for them, but I was getting confused with table variables
That solves the potential issue with concurrent users then
🙂
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply