Community
Showing results for 
Search instead for 
Do you mean 
Reply

Query timeout expired on Activity Table

Bronze Super Contributor
Posts: 1,231
Country: USA

Query timeout expired on Activity Table

 

Is there a way to adjust the SQL Statement timeout time?

 

I am using an OleDb.OleDbDataAdapter object and I tried adding the "connection Timeout = 36000"

I even tried  "connection Timeout = 0" which should be infinity.

 

On the same instance of SQL a few  ( not all )  databases are am getting the timeout error.

It happens on databases with more then a few thousand activities. If the database has less then 1000 it does not thro the error.

 

Is this a known defect?

 

Thanks

-- Jim Durkin

 

 

SELECT ACTIVITYID, START_DATE_TIME
FROM Nova.dbo.ACTIVITY
WHERE (START_DATE_TIME >'11/11/2011')

 

---------------------------
Microsoft Visual Studio
---------------------------
SQL Execution Error.
Executed SQL statement: SELECT ACTIVITYID, START_DATE_TIME, ACCESSOR_ACTIVITYID, ACCESSORID, ACKNOWLEDGED, ACTIVITY_TYPE, ACTIVITYPRIORITYID, ACTIVITYSERIESAPPLIEDID, ACTIVITYTYPEID, ALARMCLEARED_DATES, ALARMED, ALL_DAY_BANNER, BANNER_COLOR, BASE_RECORD, CLEARED, CLEARED_DA...
Error Source: ACT! OleDB Provider for Reporting 2.0
Error Message: Query timeout expired
---------------------------
OK Help
---------------------------

Employee
Posts: 1,163
Country: USA

Re: Query timeout expired on Activity Table

This is not a defect that has been submitted so I tried to reproduce to make sure that isn't the case. I was able executed the same query minus the WHERE clause in the demo database and it returned 17k+ rows in >15 seconds or so. 

 

You say that you're not getting this in all databases, are there any similarities in Act or SQL version on these machines where you are having timeouts?

Matthew Wood
Act! SDK Support
Community Moderator
Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Query timeout expired on Activity Table

All the databases reside on my development workstation using ACT! 2012 SP1.

Some DB throw error while others do not. The only similarity to the DB that do not throw errors is the activity count is less then 1000 records.

 

-- Jim Durkin

 

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Query timeout expired on Activity Table

Hi Jim,

 

I never have joy with the Activities view with the provider. I try and avoid it now in dashboards if possible! One over the top workaround I use is a simple Dashboard plugin with is basically an embedded IE instancewhich goes to the intranet where a custom "dashboard" SRS (SQL Reporting Services) report is posted. Obviously this is no good for generic plugin development!

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Employee
Posts: 1,163
Country: USA

Re: Query timeout expired on Activity Table

Vivek, 

 

Are you having timeout issues as well? The only other issue I've heard of with the activity table is an error message point to the stored procedure PRC_CLR_GET_ACTIVITIES, are you getting the error that includes this?

 

Jim,

 

Do you have another machine you can test on? I was able to query the demo on both my dev machine and another machine and in both cases the query would execute with timing out. I also tested with 2 other DB's that I had in house for other issues, and they didn't timeout either. If you'd like you can contact me via e-mail and send me one of the databases that's failing and I can try it on my end, I suspect however that it will be successful. 

 

I've had one issue with querying the activity table in the past and it only occurred in Excel, "Initialization of Data Source Failed", however if I did the same query through code or via the query builder in visual studio I had no issues.

Matthew Wood
Act! SDK Support
Community Moderator
Nickel Elite Contributor
Posts: 937
Country: USA

Re: Query timeout expired on Activity Table

The OLEDB Connection has a 30 second query time out by default if you want to alter this in your connection string issue:

 

Extended Properties="COMMAND_TIMEOUT=0"

 

0 is infinite so you might want to actually put a value there that represents a more reasonable time frame to avoid exceptionally long running queries.

 

just as a reminder when it comes to querying activities - we use a matrialization algorithm to retrieve recurring activities so it's always a good idea to filter any activity query by some date based filter to keep from having the algorithm run long materializing perpetual recurring activities.  I suggest always having a where clause member somethng  like:

 

WHERE (START_DATE_TIME >'11/11/2011') AND (END_DATE_TIME <= '05/11/2012')

 

 

 

 

BTW - Thanks to Konstantin for this info

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Query timeout expired on Activity Table

[ Edited ]

Allan,

I tried the 'extended property" in the connect string but could not get it to work past the default 30 seconds even when I set it to infinity. (0)

 

So I reworked our Durkin.Common.Data.GetDataTable function to create an OleDb.OleDbCommand object first, set the CommandTimeout to 300 then fill the dataAdapter

 

' OLD CODE

' Always threw a timeout error

' NEW CODE
' 130 activities returned in 82 seconds from a DB which has 30,000 contact and 80,000 activities,

 

For those it may help I have the code example is below.

 

Thanks for the info.

-- Jim Durkin

 

 

 Public Shared Function GetDataTable(ByVal CurrentConnectionString As String, _
                                        ByVal sSelectCommand As String, _
                                        ByVal TableName As String) As DataTable

        Try

            '---------------------------------------------------
            ' New code set to a 300 second timeout
            ' on the new OleDb.OleDbCommand object
            '---------------------------------------------------
            Dim selectCMD As OleDb.OleDbCommand = New OleDb.OleDbCommand(sSelectCommand, New OleDb.OleDbConnection(CurrentConnectionString))
            selectCMD.CommandTimeout = 300

            Using dataAdapter As New OleDb.OleDbDataAdapter(selectCMD)
                Dim dataTable As DataTable = New DataTable(TableName)
                dataAdapter.Fill(dataTable)
                Return dataTable
            End Using

            '---------------------------------------------------
            ' OLD which used the defautl 30 second timeout
            '---------------------------------------------------
            'Using dataAdapter As New OleDb.OleDbDataAdapter(sSelectCommand, New OleDb.OleDbConnection(CurrentConnectionString))
            '    Dim dataTable As DataTable = New DataTable(TableName)
            '    dataAdapter.Fill(dataTable)
            '    Return dataTable
            'End Using

        Catch ex As Exception
            Throw ex
        End Try

        '--------------------------
        ' return an empty dataset
        '--------------------------
        Return Nothing

    End Function