StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Increase Connection Timeout

Increase Connection Timeout

Bill Cunnien — Thu, 15 Jan 2009 15:43:48 GMT

I have a query that is taking about 90 seconds. The window times out before any data is displayed. How do I increase the connection timeout for this query?

Thanks,
Bill

RE: Increase Connection Timeout

Trent L. Taylor — Thu, 15 Jan 2009 15:43:48 GMT

Cool. It is cool watching it show the progress while updating a single field, huh? I still enjoy uploading large files where we have this implemented just so I can watch it progress :D

RE: Increase Connection Timeout

Kenneth Langley — Tue, 13 Jan 2009 13:07:50 GMT

Thanks Trent

We have this working well now, thanks for the help

Kenneth langley

RE: Increase Connection Timeout

Trent L. Taylor — Mon, 12 Jan 2009 10:08:00 GMT

You can go at this a number of ways, but here is a quick sample. Let's first put a method on the BO where the varbinary field resides:

BO Method

[codesnippet]''' <summary>
''' Updates a blob or binary field on a thread with callback methods updating the progress
''' </summary>
Public Sub UpdateBlobData(ByVal pk As Integer, _
      ByVal data As Byte(), _
      ByVal displayText As String, _
                           ByVal progressCallback As BlobProgressDelegate, _
      ByVal completeCallBack As BlobPushCompleteDelegate, _
      ByVal errorCallback As BlobErrorDelegate)
'-- Establish Locals
Dim pkValue As New MicroFour.StrataFrame.Business.PrimaryKeyValue(New Object() {pk})

'-- Start the update progress
Me.PushBlobFieldAsync(pkValue, _
        "FieldName", _
        data, _
        84000, _
        displayText, _
        progressCallback, _
        completeCallBack, _
        errorCallback)
End Sub[/codesnippet]

In this example, the above field name is "FieldName" so we would add "FieldName" to the FieldsToExcludeFromUpdate and FieldsToExcludeFromInsert. This way the PK, time stamp columns, etc. would save before we try to save our blob data. So taking this example, and assuming that you have added the "FieldName" to the FieldsToExclude properties on the BO, the code would look something like this:

[codesnippet]
'-- Save the BO data to the server minus the blob field. The Blob field will not be included because
'    it should have been added to the FieldsToExcludeFrom... properties.
MyBO.Save()

'-- Now that all other fields of the BO have been saved, it is time to save the blob data
MyBO.UpdateBlobData(MyBO.PrimaryKeyField, _
                              BlobDataAsByteArray, _
                              "Saving large data", _
                              AddressOf HandlePushProgress, _
                              AddressOf HandlePushComplete, _
                              AddressOf HandlePushError)

''' <summary>
''' Handles the completion of a push of a media item to the serve
''' </summary>
Private Sub HandlePushComplete(ByVal state As Object)
'-- Place any post push logic here (i.e. update the dialog, start a new update, etc.)
End Sub

''' <summary>
''' Handles an error in the push process
''' </summary>
Private Sub HandlePushError(ByVal ex As Exception, ByVal state As Object)
MicroFour.StrataFrame.Application.StrataFrameApplication.ShowRedExceptionDialog(ex)
End Sub

''' <summary>
''' Handles the progress of an upload or download from the server
''' </summary>
Private Sub HandlePushProgress(ByVal currentCount As Long, ByVal totalCount As Long, ByVal nextIncrement As Byte(), ByVal state As Object)
'-- Update the progress
lblProgress.Text = CType(state, String)

'-- Update the thermo
Try
     '-- Don't let the progress go above the max value
     If prgProgress.Value > totalCount Then prgProgress.Value = totalCount

     prgProgress.Maximum = totalCount
     prgProgress.Value = currentCount
Catch ex As Exception
End Try
End Sub
[/codesnippet]

That should give you a good starting point.

RE: Increase Connection Timeout

Kenneth Langley — Thu, 08 Jan 2009 15:43:55 GMT

Trent

I cannot find any documentation for the push/pull BLOB functions you referenced. Could you please give us a sample or point me to where it is used.

RE: Increase Connection Timeout

Trent L. Taylor — Fri, 02 Jan 2009 15:25:58 GMT

Good point. Yes, they should.

RE: Increase Connection Timeout

Edhy Rijo — Fri, 02 Jan 2009 11:17:37 GMT

Hi Trent,

When using the Push/Pull methods should the VarBinary fields be included in the BO FieldsToExcludeFromInsert and FieldsToExcludeFromUpdate properties?

RE: Increase Connection Timeout

Trent L. Taylor — Fri, 02 Jan 2009 11:00:11 GMT

Guys, sorry for the super delayed response here. We have not been in the office (and still aren't) and I have not been able to get the forum until now.

Let me propose another avenue that is much more beneficial to you and the end-user when waiting for large VarBinary(MAX) fields. StrataFrame has two push methods and two pull methods for this very purpose called: PushBlobField, PushBlobFieldAsync, PullBlobField, and PullBlobFieldAsync.

Whether you choose to use the async method or block and use the non-async methods, this will never timeout and will stream as big of a file that you will ever use. If you use the async method (which I recommend) you can also add handlers to show a progress bar of the save and/or retrieval.

Push Async Example

[codesnippet]MyBO.PushBlobFieldAsync(PkValueToUpdate, "DataField", byteData, 84000, displayText, progressCallback, completeCallBack, errorCallback)[/codesnippet]

The pull will work very much like the push. This is a great method and is what we do when updating large binary data fields....that is how this made it into the framework :)

RE: Increase Connection Timeout

Larry Caylor — Tue, 30 Dec 2008 18:16:19 GMT

You may also want to look at your database maintenance plan and make sure that the statistics are being refreshed or try analyzing your query in the database engine tuning advisor. I've seen a query drop from 30+ seconds to under a second after tunng. You may also want to consider using the SF Enterprise server to eliminate the database traffic between the client and DB server over your wide area network.

-Larry

RE: Increase Connection Timeout

Peter Jones — Tue, 30 Dec 2008 16:40:42 GMT

Hi Bill,

Just looked at the code in your post and the line:

cmd.Parameters.AddWithValue("@invdate", InvDateDE.DateTime.ToShortDateString()).SqlDbType = SqlDbType.VarChar;

may need attention IF InvDate is an indexed column in your database. If it is then the parameter must a Date data type not VarChar.

If you need to pass in a VarChar data type then your proc needs to convert the value to a date data type before it is used in a Where clause. If this isn't done you will end up with a full table scan rather than a Seek on your InvDate index.

Cheers, Peter

RE: Increase Connection Timeout

Peter Jones — Tue, 30 Dec 2008 16:33:04 GMT

Hi Bill,

You could try putting:

Option (Recompile)

as the last line in your proc. This will cause SQL to recalculate its execution plan each time the proc runs. While this, in itself is an overhead, it is better than using an old and entirely inappropriate plan if you have a wide range of options that SQL should be considering on each execution although SQL is supposed to be pretty smart in knowing when to recalculate an execution plan.

Bottom line is that 90 seconds is a looooong time and the issue is probably in the area of your proc using full table scans rather than index seeks. Just in case you don't know - the best way to sort these issues is to put the proc's code is Query Analyzer and have a look at the Execution Plan

Cheers, Peter

RE: Increase Connection Timeout

Kenneth Langley — Tue, 30 Dec 2008 14:56:01 GMT

More detail, we have a varbinary(max) field with binary data. This field size could be in the megabytes range. It appears that when moving the binary data is when the timeout occurs. Any more ideas ?

RE: Increase Connection Timeout

Greg McGuffey — Tue, 30 Dec 2008 13:35:31 GMT

Wow, I remember when a T1 was a fast connection! :D

You might try just writing your own FillByPrimaryKey, put it your base BO, so all your BOs use it and none of the client code needs changing. You could then reference a property that you can set to increase the timeout. The FillByPrimaryKey code is easy, especially if you only need to overload one of them. Then you could set the timeout on the SqlCommand object:

[codesnippet]Public Overloads Sub FillByPrimaryKey(key As Integer)
  Dim sql As String
  sql = String.Format("Select * From {0} Where {1} = @key", Me.TableName, Me.PrimaryKeyField)
  Using cmd As New SqlCommand()
    cmd.CommandText = sql
    cmd.Parameters.AddWithValue("@key",key).SqlDbType = SqlDbType.int
    cmd.CommandTimeOut = 60 ' Time out in seconds...pull from a global property or something
    Me.FillDataTable(cmd)
  End Using
End Sub[/codesnippet]

There may be a way to set the timeout for a BusinessLayer also, but this might work (haven't tried it myself).

RE: Increase Connection Timeout

Kenneth Langley — Tue, 30 Dec 2008 12:57:03 GMT

I guess I should have said that the clients that are having the timeout issue are on a T1 line remote from the SQL server and that the line is used by several users. This timeout occurs based on the T1 load at the time. Hope this helps explain a little better.

RE: Increase Connection Timeout

Greg McGuffey — Tue, 30 Dec 2008 12:21:35 GMT

I'm not sure how to change the time out, but I'm suspicious that something else is going on. I've never had a time out except when I had a connection problem. I.e. only if I couldn't access the server....in which case more time isn't going to help. You many have already done this, but I'd check that your connection string is OK, that you have access to the server, that sort of thing. Most of my users are remote, sometimes with horrible connections. Rarely is there a time out, especially on a FillByPrimaryKey, which usually is using a clustered index.

I'll be interested to know about how to set the timeout too! :D

RE: Increase Connection Timeout

Kenneth Langley — Tue, 30 Dec 2008 11:58:32 GMT

How do you set the sql command timeout on SF built in calls such as FillbyPrimaryKey. We are using these commands and getting a timeout. Is there a global setting that SF will use for the SF system generated calls?

RE: Increase Connection Timeout

Bill Cunnien — Fri, 09 May 2008 11:06:20 GMT

Thanks. Took me a minute to make the right connection from the command to the BO. Here is my revised code:

ADUserBO mADUser = new ADUserBO();
String mPartNum = String.Empty;
if (PartNumCBE.SelectedValue != null)
{
mPartNum = (String)PartNumCBE.SelectedValue;
}
SqlCommand cmd = new SqlCommand("spx_GetRunningInventory_FinishedGoods");
cmd.CommandTimeout = 0;
cmd.Parameters.AddWithValue("@partnum", mPartNum).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("@div", mADUser.LocationIndex).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@invdate", InvDateDE.DateTime.ToShortDateString()).SqlDbType = SqlDbType.VarChar;
cmd.CommandType = CommandType.StoredProcedure;
rawMaterialValuationBO1.FillDataTable(cmd);
gridView1.Columns["CurrQty"].FilterInfo = new DevExpress.XtraGrid.Columns.ColumnFilterInfo("CurrQty > 0");
gridView1.ApplyColumnsFilter();

Works great! Thanks a bunch. Oh...and the wait window...awefully nice feature!!

Now, I just need to work on getting that sproc to run faster than 90 seconds. It has several nested CASE statements that I think are gumming up the works. Not too sure how to simplify those conversions that are going on.

Happy Friday!
Bill

RE: Increase Connection Timeout

Trent L. Taylor — Fri, 09 May 2008 09:52:02 GMT

[quote]If the command timeout can be set, how do I do that from the BO?[/quote]

Through a command:

[codesnippet]'-- Establish Locals
Dim cmd As New SqlCommand("MyStoredSproc")

'-- Never time out
cmd.CommandTimeout = 0

'-- Set the parms
cmd.Parameters.AddWithValue("@Parm1", 1).SqlDbType = SqlDbType.Int

'-- Set the command type
cmd.CommandType = CommandType.StoredProcedure

'-- Execute the command
Me.ExecuteNonQuery(cmd)[/codesnippet]

RE: Increase Connection Timeout

Bill Cunnien — Fri, 09 May 2008 09:36:04 GMT

Thanks Paul and Edhy!!

I am still using SQL Server 2000...I will see if I can find a corresponding configuration for setting the timeout.

Here is my code that I use for retrieving the data:

String mPartNum = String.Empty;
if (PartNumCBE.SelectedValue != null)
{
mPartNum = (String)PartNumCBE.SelectedValue;
}
SqlParameter mItemCode = new SqlParameter("@partnum", mPartNum);
ADUserBO mADUser = new ADUserBO();
SqlParameter mDiv = new SqlParameter("@div", mADUser.LocationIndex);
SqlParameter mInvDate = new SqlParameter("@invdate", InvDateDE.DateTime.ToShortDateString());
rawMaterialValuationBO1.FillByStoredProcedure("spx_GetRunningInventory_FinishedGoods", mItemCode, mDiv, mInvDate);
gridView1.Columns["CurrQty"].FilterInfo = new DevExpress.XtraGrid.Columns.ColumnFilterInfo("CurrQty > 0");
gridView1.ApplyColumnsFilter();

If the command timeout can be set, how do I do that from the BO?

Thanks,
Bill

RE: Increase Connection Timeout

Trent L. Taylor — Fri, 09 May 2008 09:24:01 GMT

Yeah, Paul nailed it on the last post. The most common timeout that is overlooked is on the command itself. You can set the connection timeout until you are blue in the face...but if you don't set the command time out it can still timeout on you. So if you set the command timeout you should be good to go! :)

RE: Increase Connection Timeout

Paul Chase — Fri, 09 May 2008 07:38:43 GMT

Bill,

I thought that I had found where to set it on a per query basis, I set it server wide to deal with an issue with E-connect for Great Plains, to set in on a per query basis simply set the commandtimeout property of the Sql Command.

Dim loCommand As New SqlCommand()

loCommand.CommandTimeout = 150

'-- Build the Query

loCommand.CommandText = "SELECT * FROM WHATEVER"

RE: Increase Connection Timeout

Paul Chase — Fri, 09 May 2008 07:18:03 GMT

Bill,

You can set the remote query timeout on the server, I had to change this when I was doing some import operations from my old foxpro data to Sql. I'm not sure if there is a way to change it ona per query basis but this did work for me with the same error.

RE: Increase Connection Timeout

Edhy Rijo — Thu, 08 May 2008 22:18:34 GMT

Hi Bill,

I found a thread that may give you an idea about using a separate thread for your query. Check it here: http://forum.strataframe.net/FindPost11979.aspx

RE: Increase Connection Timeout

Bill Cunnien — Thu, 08 May 2008 18:50:19 GMT

Here is the error:

"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

My query has been timed at about 90 seconds. What can I do to get StrataFrame to increase this timeout?

Thanks!!

RE: Increase Connection Timeout

Bill Cunnien — Thu, 08 May 2008 18:33:18 GMT

Perhaps I should have title this thread: Increase Query Timeout