tags:

views:

381

answers:

6

I have a server with a SQL2008 Database and IIS7. I created a WCF service, that access the SQL-Server and returns the resultset in form of a List<T>.

My problem is, that I get timeouts when accessing the service from my client, although the query should not take too long (about 1 sec in SQL-manager) it fetches 17.256 lines.

When I constrain the query to just read a dozen lines, it runs fine:

SqlCommand command = new SqlCommand("SELECT stammDatenId, position, latitude, longitude FROM geoKoordinates ORDER BY stammDatenId, position", connection);
IDataReader reader = command.ExecuteReader();
int count = 0;

while (reader.Read())
{
    GeoKoordinates geoKoors = new GeoKoordinates();
    geoKoors.stammDatenId = reader.GetInt32(0);
    geoKoors.position = reader.GetInt32(1);
    geoKoors.latitude = reader.GetDouble(2);
    geoKoors.longitude = reader.GetDouble(3);

    resultSet.Add(geoKoors);
    if (count > 10)
        break;

    count++;
}
reader.Close();

But as you might guess, I need all the 17k lines. The network connection speed should not be an issue, for the server and my client both are inside our LAN, with 100 MBit lines. When saving the result set to csv from SQL-Manager it is just 600 kb big.

Any idea where the bottleneck might be and how to solve this? I have no idea, what part of the code might be relevant to answer this question. If you need to see some special parts, please note it here and I'll provide it.

A: 

Increase time out in the config file for the binding. Check if the error still occurs. See this link. Might help.

danish
A: 

You might run into a problem not with timeout, but that the default WCF message size is not big enough.

By default WCF is limited to 64K messages - maybe with 17K of rows, that's too much already - sounds like it from what you write.

You can increase these messages sizes by a multitude of settings - most of them on your binding. These would be:

  • maxBufferPoolSize
  • maxBufferSize
  • maxReceivedMessageSize

To configure the values for e.g. the wsHttpBinding, use this fragment:

<bindings>
  <wsHttpBinding>
    <binding name="LargeWsHttp"
             maxBufferSize="512000"
             maxBufferPoolSize="512000"
             maxReceivedMessageSize="512000" />
  </wsHttpBinding>

And then just reference that binding in your endpoints (both client and server side!):

<endpoint address="......"
          binding="wsHttpBinding" 
          bindingConfiguration="LargeWsHttp" 
          contract="......" />

Other settings are on the <ReaderQuotas> (as a subtag in your binding) - those include:

  • maxStringContentLength
  • maxArrayLength
  • maxBytesPerRead
  • maxNameTableCharCount

These - as the name "reader quotas" implies, are probably more relevant to the server when it receives messages.

Marc

marc_s
the maxBufferPoolSize is not supportet as attribute in the clients ServiceReferences.ClientConfig file. Anyway, there is a basicHttpBinding (I use basic because of Silverlight) already on the clientside with large enough numbers (2147483647). I included your binding on server side, using 2147483647 as value. I still run into timeouts, but it just occures after ~45 seconds.
Aaginor
I played a bit around with the number of lines after I break the while-loop. Result was, that with 13k lines I get the result almost immediatly and with 13.5k lines, it runs into a timeout (after 45 secs.) So it seems to to be with the size of the returning result. I have used your bindingConfiguration on serverside and the standard large binding config on clientside, but the problem seems to be elswhere
Aaginor
A: 

What kind of timeout?

You could be having:

  • SQL timeouts. Check CommandTimeout on SqlConnection/DataContext
  • Transaction timeouts, set TransacitonOptions.Timeout for your own TransactionScopes or put the following the WCF Server's config, under the relevant behaviour
<serviceTimeouts transactionTimeout="00:10:00"/>
  • Or its a WCF timeout, this is probably most likely, as you'll be converting all the data to XML, and these default to 1 minute. Suggest the receiveTimeout is set on the binding on the client. As marc_s states, you may encountere other issues (but not timeouts) based on the size of the data, and the below has examples of this too:

.

<wsHttpBinding>
  <binding name="PcsInterfaceSecureBinding" openTimeout="00:10:00" receiveTimeout="00:10:00"
   sendTimeout="00:10:00" maxBufferPoolSize="8000000" maxReceivedMessageSize="2147483647">
    <readerQuotas maxDepth="32" maxStringContentLength="65536000"
     maxArrayLength="2147483647" maxBytesPerRead="8192" maxNameTableCharCount="16384" />
    <security mode="Transport" />
  </binding>
</wsHttpBinding>
MattH
I can't see why my latter XML wont show up! So I gave up there, but the attribute is on the <binding> element. Appreciate if a mod can fix this up as I don't know why <code> isnt displaying it
MattH
@MattH: seems to be a problem if you add a block of code after a bullet point - I added an empty line, a line with just a "." and another empty line to make it work...
marc_s
Thanks marc - strange. I'll probably avoid the bullet point syntax next time
MattH
It's a System.TimeoutException with two cascaded System.Net.WebException inner exceptions at InternalEndGetResponse(...).
Aaginor
A: 

Don't return 17,000 lines. Page through them. That will also allow the client to begin processing while SQL is doing its thing and getting more data from the DB.

kyoryu
The strange thing is, that 13k lines comes very fast, but >13.5k lines it times out.
Aaginor
A: 

It is strange that it takes 1 sec in SQL manager and timesout in WCF. The default WCF timeout is 10 mins.

Do you get the "timeout" after 10 mins or does it come much faster?

If you get it after 10 mins it could be caused by the server waiting for a lock, or something to do with the order by.

If it comes much faster it is probably due to the amount of data. In that case the answer from marc_s covers it.

Shiraz Bhaiji
timeout without marc_s' code comes after ~10 sek, with marc_s' code after ~45 sec. Although, I am sure that it is the amount of data, because 13k lines are ok (and really fast) and 13.5k lines times out.
Aaginor
Try increasing the values in marc_s' code,add an extra 0 at the end. This should get the data back but it will still take very long. Do you process the rows in any way or do any string concatination that could cause it to take so long?
Shiraz Bhaiji
I already increased the values to 2147483647. It still times out. Interesting thing is: When I limit the resultset to 13k lines, it takes 1 or 2 seconds to finish, with 13.5k it times out after ~45 sec. It seems to be the size of the result set (a list<GeoKoordinates>). Is there an option somewhere to set the max size of the returned object (other than MaxBufferSize)?
Aaginor
in other words, it seems to be a "sizeOut" not a "timeOut"
Aaginor
For size other than MaxBufferSize look at the read quotas in marc_s' answer
Shiraz Bhaiji
+1  A: 

Here's one small thing that may be complicating your results. You should implement using blocks:

using (SqlCommand command = new SqlCommand(
   "SELECT stammDatenId, position, latitude, longitude "+
   "FROM geoKoordinates ORDER BY stammDatenId, position", connection))
{
    using (IDataReader reader = command.ExecuteReader())
    {
        // ...
    }
}
John Saunders
As far as I know using sets the scope for the reader to the to the curly braces after the Using-statement. What reason (beside coding style) is there to use using?
Aaginor
`using` should be used whenever you create an instance of a class that implements the `IDisoposable` interface. It ensures that the `Dispose` method gets called, even if there is an exception.
John Saunders
ah, I didn't know that. Thanks for the info!
Aaginor