Timeout Expired but the query runs fine in SSMS

[hammad]

I have this query that generates employee salaries. It is a bit involved but runs quite fast (under 5 sec) in Sql Server Management Studio. It was running fine in Stimulsoft was well but I mad some change ( i have change @month and @year by @fromdate and @ToDate). Now the query times out in Stimulsoft MRT Report.

I have tried to increase timeout by selecting datasource, press F4 and change timeout from 30 to 60 but to not affect. In fact it still times out after 30 sec.

Now sure what is wrong. I am using version 2008.1.129. Please see the error and my Stimulosft version below. I have spend more than a day on it but not clue why I am getting this error.

https://www.anony.ws/image/DykK

[Alex K.]

Hello,

Please try to use the following code in BeginRender event:

Code: Select all

((Stimulsoft.Report.Dictionary.StiSqlSource)this.Dictionary.DataSources["Name"]).CommandTimeout = 60;

Or try to check the last version.

Thank you.

[hammad]

Thanks Aleksey for the code. I have tried it but it still times out at 30 second. Timeout = 60 is not taking effect.

Update: Ok found there was missing quote(") around name. I have added that but still not seeing the query result. It times out at 30 when I run the query in designer. If I run my report on web, it simply wait and wait and does not return.

[HighAley]

Hello.

Maybe you made a error in the query.
Could you check the query in SQL Profiler?

Thank you.

[hammad]

It is a stored procedure which runs fine in SSMS (take only 2 sec)

Code: Select all

exec SPR_HRM_GetEmployeeSalaries @FromDate='01-Jun-15', @ToDate ='26-Jun-15'

I have tried to replace stored procedure with the actual query, still same problem. I have verified the connection string, which is OK.

So two problem here,

1. The query timesout while it shouldn't
2. If I change timeout to 60, it is not taking effect. It always times out at 30 sec

Thanks

[HighAley]

Hello.

There was some issues with CommandTimeout in our old builds. Please, try our latest build.
If you get the same error, send us your report template with database backup for analysis.

Thank you.

[hammad]

Ok So I troubleshooted into more. Found out the problem was my query in SQL Server Management Studio (SSMS) not Stimulsoft. My query was running under 2 sec in SSMS. It was probably getting some cache data may be. The next day when I came and opened a new instance of SSMS, my query was not running in 30 second. In fact I left it running and it took 42 minutes to complete!

Since then I have added indexes. The problem was a subquery which had big data. So I put that data into #temp table and applied index that significantly reduced the time of the query + did some other improvement.

I still have a problem. Now when the query run, it wait and wait and then returns a blank page. Inside designer the query does return results in 12 sec which is correct according to SSMS. So there are still issues to be resolved!

[HighAley]

Hello.

We made many performance improvements in our product.
Please, try our latest build and let us know about results.

Thank you.