2

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Aaginor
  • 4,516
  • 11
  • 51
  • 75
  • I tried to bring the code that calls the WCF on the same server, but noe i get a VERY annoying CommunicationException: NotFound. No word WHAT wcf not found or WHERE. Is there anyway to debug this or a get a meaningful errormessage? Anyway it's to darn hot today and my pulse is already at 180. I think I'll take a break and continue tomorrow. Thanks for all the good help I get here! – Aaginor Aug 20 '09 at 14:48

5 Answers5

2

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>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MattH
  • 4,166
  • 2
  • 29
  • 33
  • I can't see why my latter XML wont show up! So I gave up there, but the attribute is on the element. Appreciate if a mod can fix this up as I don't know why isnt displaying it – MattH Aug 20 '09 at 11:31
  • @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 Aug 20 '09 at 11:42
  • Thanks marc - strange. I'll probably avoid the bullet point syntax next time – MattH Aug 20 '09 at 12:28
  • It's a System.TimeoutException with two cascaded System.Net.WebException inner exceptions at InternalEndGetResponse(...). – Aaginor Aug 20 '09 at 12:38
1

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
  • 160,644
  • 26
  • 247
  • 397
  • 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 Aug 20 '09 at 14:18
  • `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 Aug 20 '09 at 17:23
0

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

Community
  • 1
  • 1
danish
  • 5,550
  • 2
  • 25
  • 28
0

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
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 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 Aug 20 '09 at 12:27
  • 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 Aug 20 '09 at 12:32
0

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
  • 12,848
  • 2
  • 29
  • 33