Long delay when calling a WCF-service from silverlight

Question

Hi folks,

I have a silverlight 3 application, that fetches some simple data from a ms-sql-server 2008 via WCF. At first, it gets all ids (~2000) that are stored in the database and then subsequently fetches all the details of those ids from another table (~10 records per id on average).

My problem is, that it takes very long from calling the details to actually getting the results (~ 13-18 seconds). After the first details-item is fetched, the rest comes in quickly.

Where shall I look for the bottle neck?

Here's the code I use. At first, my two WCF-methods

This one gets the ids

    public HashSet<int> GetAllIds()
    {
        HashSet<int> resultSet = new HashSet<int>();
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        connection.Open();

        try
        {
            SqlCommand command = new SqlCommand("SELECT id FROM stammDaten", connection);

            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    resultSet.Add(reader.GetInt32(0));
                }
                reader.Close();
            }
        }
        catch (Exception e)
        {
            Logger.instance.ErrorRoutine(e, "");
        }

        connection.Close();

        return resultSet;
    }

This one gets the details for the single ids:

    public List<GeoKoordinates> GetGeoKoordinatesById(int stammDatenId)
    {
        List<GeoKoordinates> resultSet = new List<GeoKoordinates>();
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        connection.Open();

        try
        {
            SqlCommand command = new SqlCommand("SELECT stammDatenId, position, latitude, longitude FROM geoKoordinates WHERE stammDatenId=@stammDatenId ORDER BY stammDatenId, position", connection);
            command.Parameters.Add(new SqlParameter("@stammDatenId", SqlDbType.Int));
            command.Parameters["@stammDatenId"].Value = stammDatenId;

            using (IDataReader reader = command.ExecuteReader())
            {
                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);
                }
                reader.Close();
            }
        }
        catch (Exception e)
        {
            Logger.instance.ErrorRoutine(e, "");
        }

        connection.Close();

        return resultSet;
    }

And here are the functions of my silverlight-app, that consume those methods. _s1 is an instance of the ServiceReference to my WCF-app

private void InitMap()
{
            ...
        _s1.GetAllIdsCompleted += new System.EventHandler<OSMDeepEarthExample.ServiceReference1.GetAllIdsCompletedEventArgs>(s1_GetAllIdsCompleted);
 _s1.GetGeoKoordinatesByIdCompleted += new System.EventHandler<GetGeoKoordinatesByIdCompletedEventArgs>(s1_GetGeoKoordinatesByIdCompleted);
 _startTime = DateTime.Now;
 _s1.GetAllIdsAsync();
    }

This one is called, when the wcf-service returns the ids

    void s1_GetAllIdsCompleted(object sender, OSMDeepEarthExample.ServiceReference1.GetAllIdsCompletedEventArgs e)
{
 TextBlockTest.Text += (DateTime.Now - _startTime).Seconds.ToString();

 foreach (int id in e.Result)
 {
  _s1.GetGeoKoordinatesByIdAsync(id);
 }
}

And finally, the one that handles the returned detail-sets.

    void s1_GetGeoKoordinatesByIdCompleted(object sender, GetGeoKoordinatesByIdCompletedEventArgs e)
{
 TextBlockTest.Text += (DateTime.Now - _startTime).Seconds.ToString();

 if (e.Result.Count > 0)
 {
  Polygon thePoly = new Polygon();
  _myLayer.Add(thePoly);

  ObservableCollection<Point> myPoints = new ObservableCollection<Point>();

  foreach (GeoKoordinates ko in e.Result)
  {
   Point point = new Point(ko.longitude, ko.latitude);

   if (!myPoints.Contains(point))
    myPoints.Add(point);
  }

  thePoly.Points = myPoints;
                    ... more polygone formatting ...

 }

Thanks in advance, Frank

Answer 1

So this is what you're doing:

Call WCF
Open db connection
get 2000 records
close connection

for 1 to 2000
    Call WCF
    open db connection
    get 10 records
    close connection
next

20 seconds seems very fast to make 2001 calls to WCF and opening and closing the database connection 2001 times.

try it like this:

Call WCF once
Open db connection
get 2000 records
for 1 to 2000
    get 10 records
next
close db connection

return a List<GeoKoordinates> 

1 WCF call and 1 database connection should be much faster