One of our report is now running in approximately 10 seconds. The SSRS Report was modified to force sequential execution of the stored procedures. 2 of the 3 Stored Procedures that support the report call PPT through a Linked Server. If these 2 Stored Procedures run in parallel, response is 1 minute. If these 2 Stored Procedures run sequentially, response is about 10 seconds. My question is... Why is PPT or the Linked Server causing this delay when access is read only? Is sequential processing the long term solution or is there a better solution that allows parallel processing? How can I reduce the time of stored procedure when run in parallel? Please advice.

Have you used nolock on your select statements? even if there is a read only access, the records get locked while retrieving. And based on your information, looks like there are some records common for both the stored procedures. select * from employee with nolock The above will retrieve the records without locking the table. If this is done and you are still getting errors, we need to look at other aspects where there could be a lock.

The two queries are accessing the same data against the same table. I do expect some type of locking is delaying the response. I wasn’t sure if nolock would make a difference when using a MS SQL stored procedure to access an Oracle table through a linked server. I double checked the queries and one query had nolock but the other query did not. I will put nolock on both and see if that makes a difference. I will let you know. Thanks for the feedback.

Hi Perry_newbie, As I have searched, there are a few options for linked server which might affect performance greatly. Pleas inveatigate and set options using sp_helpserver and sp_serveroption. Thanks, Lola Please remember to mark the replies as answers if they help.

It tried using NOLOCK on both queries but it did not make a difference. When these two queries are executed in parallel it takes about a minute.The report runs OK with these queries running sequentially. Lola - Let me try these options to check if this makes any difference in quering the result sets. Thanks, Perry.