How to implement multi threading in SSRS batch job ?

(0) Share

Report

Posted on by SarikaS

Hello Team,

I have a huge data SSRS report to process , when we are running it in batch job its taking a lot of time , I tried to improve the performance following every best practice available online , but it didnt help much , now I'm only left with option of multi-threading , but I'm not very sure of using Multi-threading in SSRS batch (never implemented multi threading before as well) I need help here.

I have the same question (0)

All responses (11)

Answers (0)

nmaenpaa 101,162 Moderator on at

Like (0)

Report
Hi,

I think that you need to start by understanding which part of your report execution takes a lot of time. For example, if it's a temp table report, is it actually the temp table population that take time? Or the actual report execution on the SSRS server?

You also say "when we are running it in batch job it's taking a lot of time". Do you mean that it runs fast when you don't run it in batch? That would be strange.

Populating the temp table in multiple threads could be challenging, and not sure if even possible. But you might be able change your approach and prepare the data differently - in advance - before the report is even run.

And multi-thread processing of the actual SSRS report is not possible as far as I know.

Could you please provide answers to all of the following questions so that we can continue troubleshooting:

What part of the report execution is really slow? Temp table population or SSRS generation or both?

If the temp table population is slow, could you please share all relevant code regarding that part?

Is it really slow only in batch?

How long does the execution take? What would be a satisfying execution time?

What business impact / issues does the current execution cause?

Was this reply helpful? Yes No
SarikaS 40 on at

Like (0)

Report

Hello , thanks for reply ,

The data is very huge in table from which I'm populating my temp table (almost 30k records for a single day).

We have date time parameter and also other parameters to fetch data into report , but User generally uses only data (from date , to date parameter) to get data (like suppose for a month or 3 months)

Now even if I'm trying to pull out data for 1 day , its taking almost half an hour (offcourse I need to use batch job).

What part of the report execution is really slow? Temp table population or SSRS generation or both? : Its both

If the temp table population is slow, could you please share all relevant code regarding that part?

Its a very direct code , fetching query in while(queryRun.next()) and assigning values in temp table

Is it really slow only in batch? : Yes

Please see below , I ran this for a day only

How long does the execution take? What would be a satisfying execution time? : The report runs for sometime complete day with showing 0 progress and its expected to take some lesser time

What business impact / issues does the current execution cause?: End User has to wait a lot to get data and the progress on report shows 0 only till its not completed fully

Was this reply helpful? Yes No
nmaenpaa 101,162 Moderator on at

Like (0)

Report

Hi,

thanks for the information.

I think you should look deeper into the query (and share with us). All queries can be iterated with QueryRun, and the fact that you use QueryRun doesn't tell anything about the possible performance issues in your query. Please share us all the relevant details about the query, and the code that populates the temp table.

Also, does your report have 30 000 rows per day? And if user prints it for one month, it has one million records? How many pages is that? What is it needed for?

Perhaps you could also store the processed data in a normal table, so that you don't need to fetch it again and again for the same time period (assuming that the data of past dates is not changing).

Is it a problem if the user just schedules the report batch to run during the night and can see the report in the morning?

If it's some financial / ledger data, could they use Management reporter instead of an SSRS report?

Was this reply helpful? Yes No
SarikaS 40 on at

Like (0)

Report

Thanks again for reply,

So here is the thing , we are not using Management Reporter (yes they should have) but its already live project and we are doing performance improvements.

We came to a conclusion , that if we can specify the number of task to be created as a parameter and somehow split the logic to do so , something like below (we agreed to use regular table)

denistrunin.com/.../

But I'm still not getting the clear picture how to do this for a SSRS report , how can I merge the above proces with my SSRS processReport() method , or something else to be followed ?

In addition to this , we need to have a cleanup batch job to be executed before the batch for SSRS report runs everytime

Please give me some insight , how can I proceed with my development

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

If reading 30000 records from database takes half an hour, something is wrong with your query and multithreading won't fix it. You need to fix the query. I would quickly review the query (to make sure that all relations are set correctly, only the needed fields are returbned and indexes look more-or-less correct) and then looked at the execution plan to understand which part is so slow.

I too would like to hear answers to these questions of Nikolaos: "Does your report have 30 000 rows per day? And if user prints it for one month, it has one million records? How many pages is that? What is it needed for?"

Was this reply helpful? Yes No
SarikaS 40 on at

Like (0)

Report
Hello Martin ,

Sorry for late reply , running query in SQL is not taking much time.

I used trace parser to analyse which part of code is taking more time and it is while loop used to insert data into TempDB table

while(queryRun.next()) { tempDB table.field1 = table.fields1 and follows TempDB.insert() }

so I tried to use Query::insertRecordSet function to avoid calling while loop multiple times and below is my code :

query = new Query(this.parmQuery()); query.clearAllfields(); qbds = query.dataSourceTable(tableNum(AXLedgerARAPTable)); qbfl = qbds.fields(); datasourceUniqueId = qbds.uniqueId(); this.addInsertFieldMapping( qbfl, insertRecordSetMap,dataSourceUniqueId, fieldNum(AXLedgerARAPTable, AccountNum), fieldStr(AXLedgerARAPTable, AccountNum), fieldStr(CNLAS_SRS_AXLedgerARAPList, AccountNum)); this.addInsertFieldMapping( qbfl, insertRecordSetMap, dataSourceUniqueId, fieldNum(AXLedgerARAPTable, AccountName), fieldStr(AXLedgerARAPTable, AccountName), fieldStr(CNLAS_SRS_AXLedgerARAPList, AccountName)); //more fields in same way ttsBegin; Query::insert_recordset(CNLAS_SRS_AXLedgerARAPList, insertRecordSetMap,query); ttsCommit;

But data is not getting inserted and report is opening without data.

Please let me know where I'm doing wrong and what else can be done.

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

Please use Insert > Code (in the rich-formatting view) to paste source code. I've already updated your post above, because the code was difficult to read.

When you claim that the first block is slow, analyze which part of it is slow. It's more likely that the problem is in next() (where the query gets executed) than in insert(). If so, you're solving a wrong problem.

If you find that the problem is in insert(), think about why. Is it caused by some code in insert() method? Or is the caused by database? Then address the problem. For example, if insert() is to blame, your code above won't solve anything, because it'll still call the method.

By the way, you didn't show us enough code to identify why nothing got inserted. The first think I would check is whether you're inserting data to the right temporary buffer.

Was this reply helpful? Yes No
SarikaS 40 on at

Like (0)

Report

Hello Martin ,

Thank you for the reply , so the slow part is QueryRun::Next function only

Please let me know what can be done to improve this , we have huge data in the table (regular table used to insert data into TempDB table) and user is mostly using date parameter on report like extracting out 3 months date or so (1 month data has almost more than 1 crore records almost).

I have already analysed for duplicate data if any , but we are already taking care of it in code and no duplicate records are there.

Please let me know what is the best approach to improve report performance now ?

Was this reply helpful? Yes No
Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report

QueryRun::Next() executes the query, therefore you have a problem with the query. See my suggestions in my first reply.

Was this reply helpful? Yes No
SarikaS 40 on at

Like (0)

Report

Hello Martin , so if I go with first suggestion of yours

If reading 30000 records from database takes half an hour, something is wrong with your query and multithreading won't fix it.

No , the reading of records from db is not taking more than 2 mins for a months data

You need to fix the query. I would quickly review the query (to make sure that all relations are set correctly, only the needed fields are returbned and indexes look more-or-less correct) and then looked at the execution plan to understand which part is so slow.

I have analysed for the indexes and we cannot have any unique index , non- clustered indexes we have already created , we are returning only those fields that are needed for further processing , what here do you mean by execution plan ? I'm not sure on this part please help how can I use it any documents ?

I too would like to hear answers to these questions of Nikolaos: "Does your report have 30 000 rows per day? And if user prints it for one month, it has one million records? How many pages is that? What is it needed for?"

Yes , unfortunately we have huge data and around 570 pages are getting printed for a months data.

In this report , we already have one more report (which gets called on condition if met) to export data into excel already

Code for it :

public void run()
{
c1 contract;
c2 cnlAs_ExcelExport (new SSRS report class with SRSReportDataProvideBase extension)

contract = this.parmReportContract().parmRdpContract() as c1;
cnlAs_ExcelExport = new c2();

if (contract.parmExportToExcel())
{
cnlAs_ExcelExport.parmQuery(this.getFirstQuery());
cnlAs_ExcelExport.exportToExcel(contract);
}
else
{
this.runReport();
}
}

Please let me know if the multi -threading is going to be the best possible option or anything else can be tried ?

The report is taking a lot of time and system gets hanged

Was this reply helpful? Yes No