performance issues in parsing multiselect string paramters in SSRS, pfb the code

Question

performance issues in parsing multiselect string paramters in SSRS, pfb the code

saxena200

Ten Centuries

Points: 1156
More actions
March 4, 2013 at 10:46 am

#271094

i am having trouble in parsing organisation name around(2000) and getting data doing filteration on these names -
please note that organisation and organisation_id are not one to one mapped, need distinct organisations in the parameters drop down and all id's for calculation, for which i am putting where condition on organisation names.
selectvd.Month,
vd.month_No,
vd.Year,
vd.Week,
vd.Date,
vd.Organisation,
vd.[Business Line],
vd.[Sales Executive],
count(distinct vd.CSTMR_KEY)[Total no of Lines],
(
case when SUM(vd.[Total Amount Due])>0
then COUNT(distinct vd.CSTMR_KEY) else 0 end
)[Total No Of Unpaid Lines],
SUM(vd.[Total Amount Due])[Total Amount Due]
from IV_DBT_RPT_FR_ORGNSTN VD
where (vd.Date>=@FromDate and vd.Date<=@ToDate)
AND VD.Organisation IN (@Organisation)
AND VD.[Business Line] IN (@BusinessLine)
AND VD.[Sales Executive] IN (@SalesExecutive)
group by vd.AR_BHVR_KEY,
vd.[Business Line],
vd.C,
vd.CNTRCT_DD,
vd.CSTMR_KEY,
VD.Year,
vd.Date,
vd.month_No,
vd.Month,
VD.Week,
vd.Organisation,
vd.[Sales Executive]
--order by VD.Date

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

It is very unclear what is going on here. Is your query working but the results are too slow? Is the query not working as expected? If it is a performance issue we need to have table definitions including indexes, if it is the query we need table definitions and some sample data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

saxena200 Ten Centuries Points: 1156 More actions · Answer 2

Thanks for your concern Mate!!

well the query is working absolutely fine, it is taking 10 seconds in SSMS, however on SSRS it is taking 3 mins.

Problem here is - During run time i am giving organisation name as parameter and in where clause i am filtering my result on Organisation name.

This type of parameter parsing and where clause filtering is taking a lot of time.

i thought of giving organisation_id in parameter value and organisation_name in parameter label, but organisation names and organisation id's are not one to one mapped so cant do that....

i am looking forward to get a better way of handelling this problem of parameter parsing in SSRS