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?

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!

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.

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



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:







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

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 ?

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

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

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

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

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

Hi Trent,

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

Edhy Rijo