Stored procedure slow, how to optimize?

Question

Hello, I have a Silverlight solution using RIA Services. There is a SQL Server stored procedure that I use which executes quickly (< 0.1 seconds) but RIA takes 1.5 seconds either to issue the SQL or to compose the results (not sure which yet).

The stored procedure is defined with function mapping, and is configured to return an entity. The entity was created by making a view that returns the same columns as the stored procedure (it ruturns 0 records; it's only purpose is to establish the entity type).

What are my options for determining the root cause and optimizing the performance? I know that one suggestion will be to use a view instead of a stored procedure, but the TSQL logic in the stored procedure is not mappable to any kind of view...

Thanks,
-Jay

Answer 1

jay343

Most stored procedure return Complex type. Did you return complex type? Then track the Id at metadata file.

Or use DTO approach.

 

Answer 2

I should have mentioned that I'm using an Entity Framework model that was generated from an existing SQL Server database. I found that creating an entity that matches the stored procedure's output was the simplest way to integrate it with RIA (but maybe not the best? Anyone?). Consequently, the method in the RIA service class that calls the stored procedure looks like this:

public IQueryable<DiscoveredSWL> DiscoveredSW(int assimid, string nt)
{
    return ObjectContext.DMTSoftware(assimid, nt).AsQueryable();
}

I only needed to add the .AsQueryable() to make it work. This method is callable in the Silverlight project, and it functions correctly, but slower than it should. Maybe there is a way to pre-compile this?

Incidentallly, the execution time appears to be directly related to the number of rows returned. it typically returns 5 - 30 rows, and is only a problem when the number of rows is greater than 10 or so.

I suspect that somewhere under the covers there is a collection that is being rebuilt each time a member is added, or something of that nature.

Answer 3

Change your code to look like this:

public IEnumerable<DiscoveredSWL> DiscoveredSW(int assimid, string nt)
{
    List<DiscoveredSWL> results = ObjectContext.DMTSoftware(assimid, nt).ToList();   
    return results;
}

By making that change you can separate the database part from the RIA Services part. You can put your breakpoint in and step through. You should notice that I made the return IEnumerable instead of IQueryable. The DomainService is happy either way but since a call to a stored procedure is not composable there is no reason to pretend that that it is.
 

Answer 4

Using that code, I was able to create the following table:

Start         Timeline 
53:56.310 00:00.000 Begin RIA method
53:56.360 00:00.050 Call EF
53:56.400 00:00.090 SQL Begin
53:58.083 00:01.773 SQL End (duration = 1683 which is exactly what SQL Profiler reports)
53:58.100 00:01.790 End RIA method

So, I need to concentrate on optimizing the stored procedure...

Thanks, Colin!