EF 6.1.3 takes more time (few minutes) but SSMS 17 executes in a second - SQL Server 2016 (AlwaysEncrypted) with encrypted columns

Asked Oct 09 '17 at 16:32

Active Oct 09 '17 at 16:54

Viewed 204 times

.NET Core REST API with EF 6.1.3 runs on net452 as targetFramework, takes minutes to execute a Procedure and return the results to model, but with same SP parameters it takes only a second to execute in SSMS 17. Its a SQL Server 2016 DB with few encrypted columns and have 30k+ records (DEV).

Initially I was getting SQL timeout - now fixed by having connection timeout in the appsettings.DEV.json, also increased command timeout to infinite by having below on the constructor of the context:
```
var adapter = (IObjectContextAdapter)this;
var objectContext = adapter.ObjectContext;
objectContext.CommandTimeout = 0 * 60;
```
In SSMS 17, turned OFF - SET ARITHABORT to reproduce this slowness but could not reproduce - it executes in a second with/without ARITHABORT flag. SP does only SELECT (since it has AE columns, only equality conditions are performed on it)

edited Oct 09 '17 at 16:54

TheGameiswar

27,855
8
56
94

asked Oct 09 '17 at 16:32

se7vanj

1

what happens when you try adding option(recompile) hint in our client code – TheGameiswar Oct 09 '17 at 16:54
[Slow in the Application, Fast in SSMS?](http://www.sommarskog.se/query-plan-mysteries.html) – TheGameiswar Oct 09 '17 at 16:54
@TheGameiswar: It worked when SQL Server StoreProc having **WITH RECOMPILE** - now it takes only few seconds from EF. Thanks – se7vanj Oct 09 '17 at 17:21

EF 6.1.3 takes more time (few minutes) but SSMS 17 executes in a second - SQL Server 2016 (AlwaysEncrypted) with encrypted columns

0 Answers0