February 7, 2013 at 6:22 am
The developers have created a query that really could be more efficient, but that's not my job at this point.
On this SQL 2008R2 box I have run this query through SSMS and it returns 362 records in 3-4 seconds. When this same query is run through the app, .Net framework, it takes almost 30 seconds to run. These times are measured by SQL Profiler running on the local SQL server.
What I am wondering is what difference would the app that's running the query make in how fast SQL Server returns results. I'm trying to figure out what else I can check on my end, and what I can tell the developers to check to investigate the problem further.
Here is the query that Profiler captured, which ran in 29992 ms from the .Net client, but I copied and ran that same query from SSMS and it ran in 3267 ms for me.
exec sp_executesql N'SELECT
[Extent1].[GroupProviderXrefId] AS [GroupProviderXrefId],
[Extent1].[ProviderXrefId] AS [ProviderXrefId],
[Extent1].[ProviderKey] AS [ProviderKey],
[Extent1].[ParticipantKey] AS [ParticipantKey],
[Extent1].[PaymentYear] AS [PaymentYear],
[Extent1].[UserKey] AS [UserKey],
[Extent1].[OptOutOption] AS [OptOutOption],
[Extent1].[IsElected] AS [IsElected],
[Extent1].[IsNotifiedForSubmission] AS [IsNotifiedForSubmission],
[Extent1].[IsMultiGroup] AS [IsMultiGroup],
[Extent1].[IsElectedOtherGroup] AS [IsElectedOtherGroup],
[Extent1].[AboutYouSubmissionStepStatusTypeName] AS [AboutYouSubmissionStepStatusTypeName],
[Extent1].[EligiblitySubmissionStepStatusTypeName] AS [EligiblitySubmissionStepStatusTypeName],
[Extent1].[AttestationSubmissionStepStatusTypeName] AS [AttestationSubmissionStepStatusTypeName],
[Extent1].[ReviewAndSignSubmissionStepStatusTypeName] AS [ReviewAndSignSubmissionStepStatusTypeName],
[Extent1].[SubmissionSubmissionStepStatusTypeName] AS [SubmissionSubmissionStepStatusTypeName],
[Extent1].[AttestationEnabled] AS [AttestationEnabled],
[Extent1].[ReviewAndSignEnabled] AS [ReviewAndSignEnabled],
[Extent1].[AnyAttestationStarted] AS [AnyAttestationStarted],
[Extent1].[AnyAttestationInvalidHardStop] AS [AnyAttestationInvalidHardStop],
[Extent1].[AllAttestationCompleteOrSoftStop] AS [AllAttestationCompleteOrSoftStop]
FROM (SELECT
[GroupProviderView].[GroupProviderXrefId] AS [GroupProviderXrefId],
[GroupProviderView].[ProviderXrefId] AS [ProviderXrefId],
[GroupProviderView].[ProviderKey] AS [ProviderKey],
[GroupProviderView].[ParticipantKey] AS [ParticipantKey],
[GroupProviderView].[PaymentYear] AS [PaymentYear],
[GroupProviderView].[UserKey] AS [UserKey],
[GroupProviderView].[OptOutOption] AS [OptOutOption],
[GroupProviderView].[IsElected] AS [IsElected],
[GroupProviderView].[IsNotifiedForSubmission] AS [IsNotifiedForSubmission],
[GroupProviderView].[IsMultiGroup] AS [IsMultiGroup],
[GroupProviderView].[IsElectedOtherGroup] AS [IsElectedOtherGroup],
[GroupProviderView].[AboutYouSubmissionStepStatusTypeName] AS [AboutYouSubmissionStepStatusTypeName],
[GroupProviderView].[EligiblitySubmissionStepStatusTypeName] AS [EligiblitySubmissionStepStatusTypeName],
[GroupProviderView].[AttestationSubmissionStepStatusTypeName] AS [AttestationSubmissionStepStatusTypeName],
[GroupProviderView].[ReviewAndSignSubmissionStepStatusTypeName] AS [ReviewAndSignSubmissionStepStatusTypeName],
[GroupProviderView].[SubmissionSubmissionStepStatusTypeName] AS [SubmissionSubmissionStepStatusTypeName],
[GroupProviderView].[AttestationEnabled] AS [AttestationEnabled],
[GroupProviderView].[ReviewAndSignEnabled] AS [ReviewAndSignEnabled],
[GroupProviderView].[AnyAttestationStarted] AS [AnyAttestationStarted],
[GroupProviderView].[AnyAttestationInvalidHardStop] AS [AnyAttestationInvalidHardStop],
[GroupProviderView].[AllAttestationCompleteOrSoftStop] AS [AllAttestationCompleteOrSoftStop]
FROM [dbo].[GroupProviderView] AS [GroupProviderView]) AS [Extent1]
WHERE [Extent1].[GroupProviderXrefId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=12182
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 7, 2013 at 6:36 am
My question for you is about timing. If the query is run from the .NET application and takes 30 seconds, when it's run again right away, does it drop in execution time? Conversely, if you run if from SSMS without it having been run recently, does it take 30 seconds to run? If these cases are true, my guess would be that the query plan is probably cached and doesn't have to be rebuilt.
BTW, you're right. If that's the query they actually wrote, it looks like it could be more efficient. Good luck with them. 🙂
February 7, 2013 at 6:44 am
Welcome to troubleshooting entity framework and LINQ.
You probably have a bad case of parameter sniffing and a cached plan which doesnt meet the needs of the query.
If possible write it as a stored procedure and get your developers to call the proc instead of using inline LINQ in the app.
February 7, 2013 at 6:48 am
Is LINQ what generated a query that looks like that? I don't use it myself, since it seems like just another layer of obfuscation around using SQL and I don't really see an advantage to it. I'm a big believer in typing what I want instead of trying to click my way through things. I've never seen a code generator that worked well enough to replace understanding and doing it myself.
If LINQ did it, at least an person didn't actually sit down and write that query. 😀
February 7, 2013 at 6:57 am
Certainly looks like it, and especially so with the parameter names @p__linq__0.
95% of our website is in EF and LINQ and the queries look like that.
February 7, 2013 at 7:06 am
Gee, and you guys haven't even seen the underlying view this query is acting on. :w00t: I don't ever think I have seen a more complicated execution plan than what's in the view.
In this shop the DBAs do not get much hand in developing the queries and stored procs.
I do not know how they generated the query at this point.
I can say that repeated runs of this query through the app (web site) and through SSMS produce the same result times. Both my SSMS run and the app run are using the same criteria. I have not tried clearing the proc cache yet as there is a lot of other stuff going on here also.
I will ask about switching it to a proc and see what they say.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 7, 2013 at 7:26 am
Also if possible re-write the view and do the normal troubleshooting steps, index maintenance etc etc.
February 7, 2013 at 12:53 pm
After meeting with some of the principals we are looking at improving the query and view designs. I generated an execution plan for the query/view and found the largest, ugliest execution plan I have ever seen. I saved the XML. It was over 3M. Even so, through SSMS the query does execute in less than 4 seconds and returns 362 rows.
For now though, we are going to try extending the timeout for the app connection so the client is happy. Then we will get on the redesign and looking harder at how the app connects to the server.
The indexing is good. I have a job that takes care of that on a weekly basis, and the last run only re-orged 3 indexes, being fragmented less than 10%, so I know I'm good there.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply