SSRS with multiples datasets which query same table

Question

User-1350042179 posted

Hi

I have a SSRS which 6 datasets which read the same table and the report is very slow.

Should I check the option: Use Single Transaction?

Answer 1

User283571144 posted

Hi neoaguil17,

As far as I know, this option means SSRS opens a single connection, executes the datasets/queries that use the data source, and closes the connection.

You could use it to reduce the amount of open connections to the database. 

If you have it checked, then only one connection will be open to the database and all the datasets will return the data and the connection will be closed. 

This can be used to reduce network traffic and potentially increase performance.

Best Regards,

Brando

Answer 2

User-1350042179 posted

Hi

I verified that when I use transactions is better since without transactions I open many connections but there is blocking to the same table.

I read and followed this article: https://vsstack.blogspot.com/2014/01/ssrs-use-single-transaction-when.html?m=1

Without transactions the datasets run in parallel but  there is blocking to the same table.

With transactions the execution is one by one and faster