2

When I execute my stored procedure in SQL Server Management Studio, it returns the results in less than 1 second, but when I try to load data via LINQ and this code, it takes up to 5 seconds. Any quick suggestions?

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.SP_SELECT_MyData")]
public ISingleResult<SP_SELECT_MyDataResult> SP_SELECT_MyData([global::System.Data.Linq.Mapping.ParameterAttribute(DbType="VarChar(50)")] string bname)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bname);
    return ((ISingleResult<SP_SELECT_MyDataResult>)(result.ReturnValue));
}

This line takes all the time

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bname);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adeem
  • 1,296
  • 1
  • 16
  • 30
  • 2
    Is it related with SQL server execution plan? - [Performance of stored procedures & Entity Framework](http://stackoverflow.com/questions/10208921/performance-of-stored-procedures-entity-framework) – Ulugbek Umirov Jun 03 '14 at 01:26
  • 1
    As i mentioned above, SP returns result (well in time) in less than 1 second in fact and in execution plan it is using 97% clustered index of 1 of total 2 tables used via right outer join clause. There is non clustered index on column used in where clause and on one table in join clause. Problem is when we call this SP via LINQ query as shown above, it takes a lot of time. Can you plz guide me where to look for more in execution plan? – Adeem Jun 03 '14 at 04:15
  • 1
    As it pointed out in question I linked to, SQL Server may use different execution plans for SSMS-originated and ADO.NET-originated queries. Can you check via SQL profiler how fast does ADO.NET-originated query execute? And then compare it with SSMS-originated query. – Ulugbek Umirov Jun 03 '14 at 04:18
  • 1
    You were right, this was solved using "WITH RECOMPILE" your given link solved my issue, Thanks Dear, plz enter answer (better in detail) so i may mark it. – Adeem Jun 03 '14 at 04:28
  • 1
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Jun 03 '14 at 05:03
  • I agree, i never used this prefix. I got these already built from my client. Thanks for the tip though. – Adeem Jun 03 '14 at 06:24

2 Answers2

2

Thanks @Ulugbek Umirov for providing link to good answer This was caused by BAD execution plan in cache causing not using index. creating an index and disabling cache option by using WITH RECOMPILE in stored procedure improved performance a lot.

And it is further improved to perfection by improving filter option by using full text search index because like operator with wild char at both side LIKE '%abc%' cannot use non clustered index but full text search index can.

Community
  • 1
  • 1
Adeem
  • 1,296
  • 1
  • 16
  • 30
1

I would comment but don't have enough reputation yet.

Does it run faster the second time? If so, then what you're describing sounds like it could be resolved by using compiled queries, but according to this post, compiled queries do not work for stored procedures: http://aspguy.wordpress.com/2008/08/15/speed-up-linq-to-sql-with-compiled-linq-queries/

Can you write the query in linq instead and use compiled queries or does it have to be in a sproc?

Michael Adamission
  • 480
  • 3
  • 9
  • 1
    Looks like this could be it: http://stackoverflow.com/questions/12707375/stored-procedure-is-slow-in-entity-framework – Michael Adamission Jun 03 '14 at 01:25
  • parameter sniffing didn't work instead it caused SP to return data in 8 seconds instead of 1 second. however post helped me (gave direction) to improve performance (i am not sure how as DB was already returning results in 1 second) i added 1 index and it improved to some extent and now LINQ return data in 3 seconds. I think there is still lot of room to improve – Adeem Jun 03 '14 at 03:03
  • The only other thing I can think of is that maybe you can use a profiling tool like: http://www.jetbrains.com/profiler/ – Michael Adamission Jun 03 '14 at 12:39
  • my issue is resolved with answer provided in comment by Ulugbek Umirov. this is resolved by removing cache option from SP by using WITH RECOMPILE. thanks for your Answer. – Adeem Jun 07 '14 at 14:17