ServiceStack taking a long time to execute stored procedure
I have implemented ServiceStack (v4.0.36) with an ORMLite connection to my SQL Server 2014 database. There is a search form on my website that passes any populated fields to a "/search" route as querystring parameters. My request DTO looks like:
[Route("/search", "GET")]
public class SearchRequest
{
public string FirstName { get; set; }
public string LastName { get; set; }
...
}
(there are 8 total parameters) I then have a service that calls a stored procedure in SQL, passing all parameters like:
public class MyServices : Service
{
public object Get(SearchRequest request)
{
using (var db = AppHostBase.Instance.Container.TryResolve<OrmLiteConnectionFactory>().OpenDbConnection())
{
List<SearchResponse> searchResults = db.SqlList<SearchResponse>("EXEC sp_web_ResultListByNameOrAddress @firstName, @lastName, ...",
new
{
firstName = request.FirstName,
lastName = request.LastName,
...
});
return searchResults;
}
}
}
The problem is that if I do a search through the website, it takes up to 30 seconds to return results (or it times out and drops the connection). If I execute the same stored procedure in SQL, I get results instantly (search fields and combinations are indexed).
I noticed some performance gains after changing my web.config
<compilation targetFramework="4.5" debug="false"/>
...but I'm still confused as to where the bottleneck is occurring. Any help would be greatly appreciated!
na na na na na na na na...BATMAN! ?
UPDATE: Just FYI, I am connecting to this service via a stand-alone AngularJS web app, but also get the slowness or timeout issues when testing the service via a REST client like Postman. Thanks.
UPDATE 2: I enabled the Mini Profiler...the delay is in the "Execute Service" step. Here's a sample search query:
I'm going to turn on the SQL profiling on the ORMLite connection and will post any updates then.
UPDATE 3: Not super helpful (I think)...the SQL Profiler just shows the stored proc that was executed:
UPDATE 4: Interesting. So I have SSMS's SQL Server Profiler running alongside my request with the Mini Profiler turned on, and I also set a breakpoint in the service implementation on the line that runs the stored proc.
When I load the page, the breakpoint is immediately hit. When I continue, I see an "Audit Login" message in SQL Server Profiler. Then nothing for a couple of minutes, then an "RPC:Completed" message with the stored proc and passed arguments.
So the query is not hitting SQL until the very end, where it is returned almost immediately (as would be expected). Why is there a major delay between ServiceStack submitting the request and it actually executing on the SQL Server? Everything is local, so I don't see network issue being the culprit. Anybody?
Found this link and it solved my issue: What is happening in debug compilation that is causing the query to take longer to execute?
I had a bigint data type being returned by the stored procedure that was mapping to a string data type in my service response POCO. I added a CONVERT(varchar...) statement to the stored proc and things are running much faster now. Thanks!
Re-Opening! Sorry to be so flip-floppy on this. My single form submits all parameters to the stored procedure. I just increased the ORMLiteConfig timeout to 500 seconds, and have noticed that certain searches are taking about 4 minutes to complete. Again, executing the same thing within SSMS returns results instantly. What is going on??
Adding an additional answer because really there were two issues. The real root seems to be with the stored procedure. I don't know why it wasn't consistently causing problems, but I rebuilt it using dynamic SQL to only include WHERE filters for parameters that are present. Originally, I had something like this:
CREATE [sp_name]
@Name varchar(60) NULL,
@Address varchar(150) NULL
...
AS
BEGIN
SELECT *
FROM tbl_A a
WHERE (@Name IS NULL OR a.Name = @Name)
AND (@Address IS NULL OR a.Address = @Address)
My thought was the "OR" operator would evaluate the first half first, and never try to evaluate the second half if the first was false. Maybe that's not the way it works? In any case, I ended up re-writing it as:
CREATE [sp_name]
@Name varchar(60) NULL,
@Address varchar(150) NULL
...
AS
BEGIN
DECLARE @SQL NVARCHAR(4000);
DECLARE @ParamDef NVARCHAR(4000);
SELECT @ParamDef = '
@NameParam varchar(60),
@AddressParam varchar(150),
';
SELECT @SQL = N'
SELECT *
FROM tbl_A a
WHERE 1=1';
IF @Name IS NOT NULL
SELECT @SQL = @SQL + N'
AND a.Name = @NameParam ';
IF @Address IS NOT NULL
SELECT @SQL = @SQL + N'
AND a.Address = @Address ';
EXEC sp_executeSQL
@SQL,
@ParamDef,
@NameParam = @Name,
@AddressParam = @Address;
END
Works much better now. Thanks!
链接地址: http://www.djcxy.com/p/65968.html