5

My issue started with Entity Framework query executing very slowly (~2 minutes). So I started investigating

Meanwhile it seems the issue is also in standard SqlConnection

I have a pretty simple query

SELECT 1 AS [C1], [Extent1].[OldReleaseID] AS [OldReleaseID], [Extent1].[ProductName] AS [ProductName], [Extent1].[Price] AS [Price], [Extent1].[DiscountAmount] AS [DiscountAmount], [Extent1].[DiscountRate] AS [DiscountRate], [Extent1].[AbsorbVat] AS [AbsorbVat], [Extent1].[SerialCode] AS [SerialCode], [Extent1].[BrandName] AS [BrandName] FROM [dbo].[LocalSaleProductExts]() AS [Extent1]

When I run it in SSMS it executes in 0-1 seconds and returns about 30k rows

The same precise query runs in .net ExecuteReader about 100 seconds!

Online research points to basically 2 solutions: ARITHABORT and parameter sniffing, so for kicks, i added all this in SSMS

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
SET ARITHABORT OFF

Still 0-1 seconds.

And in my code I added SET ARITHABORT ON. Here's my simple code

Using sc = New SqlClient.SqlConnection("data source=MYHOST;initial catalog=MYDB;persist security info=True;user id=MYUSER;password=MYPASS;MultipleActiveResultSets=True;")
       Dim txt = "SELECT 1 AS [C1], [Extent1].[OldReleaseID] AS [OldReleaseID], [Extent1].[ProductName] AS [ProductName], [Extent1].[Price] AS [Price], [Extent1].[DiscountAmount] AS [DiscountAmount], [Extent1].[DiscountRate] AS [DiscountRate], [Extent1].[AbsorbVat] AS [AbsorbVat], [Extent1].[SerialCode] AS [SerialCode], [Extent1].[BrandName] AS [BrandName] FROM [dbo].[LocalSaleProductExts]() AS [Extent1] OPTION (RECOMPILE)"
        sc.Execute("SET ARITHABORT ON")
        sc.TryOpen()
        Dim ret As List(Of Common.DbDataRecord)
        Using cmd = sc.CreateCommand(txt)
            Using dr = cmd.ExecuteReader
                ret = dr.Cast(Of Common.DbDataRecord).ToList
                dr.Close()
            End Using
        End Using
        Dim a = ret
    End Using

Parameter sniffing I believe is irrelevant here, since I'm not sending any parameters

Neither is it a blocking issue, since I'm testing them at the same time (as much as humanly possible...), so if it's a timing issue, both should be slow

So what else can the issue be?

Thanks so much

Yisroel M. Olewski
  • 1,560
  • 3
  • 25
  • 41
  • 1
    `[dbo].[LocalSaleProductExts]()` is a table-valued function. You'll have to dig deeper and open up the black box to see what's inside -- and capture execution plans for the "fast" and "slow" cases to compare. Does it speed up if you add `OPTION (RECOMPILE)` to the query? – Jeroen Mostert Jun 03 '19 at 09:56
  • 3
    Have a read of [Slow in the Application, Fast in SSMS?](http://www.sommarskog.se/query-plan-mysteries.html) as well. There is a section on [It's Not Always Parameter Sniffing...](http://www.sommarskog.se/query-plan-mysteries.html#otherreasons). – Thom A Jun 03 '19 at 10:02
  • Why are you using a table value function in the first place? – betelgeuce Jun 03 '19 at 10:05
  • Hi. with `OPTION (RECOMPILE) ' it took 52 seconds. still way too long. Regarding the internals of the `User Defined Function`, if that would be the issue, it should be identical whether from `SSMS` or `ADO.NET`. or am I wrong? – Yisroel M. Olewski Jun 03 '19 at 10:33
  • You're focusing on "it should be identical". This doesn't matter, does it? You want to know *where the actual difference is coming from*. You can only get to that by digging at it. So: look at the function, look at the execution plans. For all you know the function filters data based on the user you're connected with, or otherwise produces a different query depending on the context. The only *other* difference is that SSMS produces a raw result set, and you are calling `.GetList`, whatever that does -- creation of the entities is another thing to look at. – Jeroen Mostert Jun 03 '19 at 10:48
  • I'm reading the link provided by @Larnu, most ideas don't apply (im not using 2000, nor linked servers, nor any parameters etc). Removing `MARS` from my connection string made a big improvement. it's now down to about 20 seconds. but still a far cry of what I get in SSMS – Yisroel M. Olewski Jun 03 '19 at 12:11
  • I'm using a `Table Value Function` because I really do need parameters. For this debugging session I've removed the parameter from the function, and just use an arbitrary value internally in the UDF – Yisroel M. Olewski Jun 03 '19 at 12:13
  • Thanks @JeroenMostert, How can i get the execution plan from a query ran through `ADO.NET`? I now how to get it in `SSMS`, but not via code. thanks. – Yisroel M. Olewski Jun 03 '19 at 12:15
  • Most convenient on 2016+: turn on Query Store for the database, run your queries, then use the UI or look in `sys.query_store_plan` -- you also get the option to force plans this way. Less convenient: manually consult the query plan cache with `sys.dm_exec_query_stats` ([example](https://www.sqlshack.com/searching-the-sql-server-query-plan-cache/)). Actually getting the execution plan *in* the query batch is possible (`SET STATISTICS XML ON` and use `DataReader.NextResult`) but is hardly convenient and I've never done it. ([See also](https://stackoverflow.com/q/7359702/4137916).) – Jeroen Mostert Jun 03 '19 at 12:24
  • @JeroenMostert, It seems like you hit upon something :-). the queryplan returned via code has this line: `.` Sounds pretty bad. Any special reason this should happen? and only in code? – Yisroel M. Olewski Jun 03 '19 at 13:18
  • 1
    Does the code run from the same machine as SSMS? Are you using the same connection in both cases (i.e. `MYHOST` resolving to the same server, same credentials)? `ASYNC_NETWORK_IO` means the server is waiting for the client to process packets, either because the connection itself is rubbish, or because the processing speed is. The minimal loop you can have to read results is `While dr.Read() End While`, try that to see if fetching the columns is the problem. Another thing worth trying is adding `Packet Size=32767` to your connection string. If necessary, pull out Wireshark. – Jeroen Mostert Jun 03 '19 at 13:25
  • yes. same host and same credentials.how can the connection be rubbish? did i write anything wrong in the connectionstring/code? – Yisroel M. Olewski Jun 03 '19 at 13:32
  • WOW!! @JeroenMostert! You really did it! the packet size did THE trick. simply adding that to my connection string made the query return in 0 seconds! Please make this an answer so I can accept it. Now I have to go back to my `EF` Issues.... – Yisroel M. Olewski Jun 03 '19 at 13:42
  • Interesting. I thought the default packet size was the same between SSMS and ADO.NET, but it's not -- ADO.NET uses 8000 for the default, SSMS uses 4096. Neither is particularly more correct than the other, and depending on your network setup you can get fragmentation issues with either value, of course. Unfortunately the [docs](https://learn.microsoft.com/dotnet/api/system.data.sqlclient.sqlconnection.packetsize) still say that using a value of more than 8000 can reduce scalability, which I've never observed, but still. Does, say, 4000 work equally well? – Jeroen Mostert Jun 03 '19 at 13:48
  • hi. i ran some very basic unscientific tests, and no, 4000 does NOT perform the same. its excruciatingly slower. 32767 gives results between 10-19 seconds. whereas 4000 gives results between 66-82 seconds! So please make an 'answer'. you really deserve recognition here:-) – Yisroel M. Olewski Jun 03 '19 at 14:42
  • I can see how increasing the packet size speeds things up (I've had plenty of experience with that myself) but that still doesn't really explain why there's such a performance difference between SSMS (which just dumps the results into a grid) and this client code, if both run on the same machine. As mentioned, SSMS uses a default packet size of 4096, so getting results there should be equally slow. If that manages to run in 1 second you've still got an order of magnitude performance problem there! – Jeroen Mostert Jun 03 '19 at 14:47

0 Answers0