Performance Difference Between Microsoft.Data.SqlClient and OdbcConnection for Inserting into Temp Table in SQL Server

Question

Microsoft.Data.SqlClient is slower than OdbcConnection for some parameterized queries. i write the same statement in C# Code.

This is the query generated by Microsoft.Data.SqlClient that I captured using SQL Profiler:

exec sp_executesql N'Insert into #TmpData select Distinct T1.Brand from Vw_Brand T1, Vw_Brand2 T2 Where T1.BrandID = T2.BrandID and Amount <> 0 and BuyDate >= @Begin_Date and BuyDate <= @End_Date and ExpireDate <= @Expire_Date ', N'@Begin_Date datetime, @End_Date datetime, @Expire_Date datetime', @Begin_Date='2024-11-30 00:00:00', @End_Date='2024-12-30 00:00:00', @Expire_Date='2024-12-30 00:00:00'

This is the query generated by OdbcConnection

exec sp_executesql N'Insert into #TmpData select Distinct T1.Brand from Vw_Brand T1, Vw_Brand2 T2 Where T1.BrandID = T2.BrandID and Amount <> 0 and BuyDate >= @P1 and BuyDate <= @P2 and ExpireDate <= @P3 ', N'@P1 datetime2, @P2 datetime2, @P3 datetime2', '2024-11-30 00:00:00', '2024-12-30 00:00:00', '2024-12-30 00:00:00'

The results show that Microsoft.Data.SqlClient takes about 60 ms, while OdbcConnection takes about 10 ms. I also tried using Dapper, but the result is the same as SqlClient. From my observation.I found that the performance difference only occurs when inserting into a temp table with a condition. There is no difference in simple query statements. Is there any way to make a Microsoft.Data.SqlClient query faster, like with an ODBC driver?

Answer 1

I don't think the client API has anything to do with it. The plans above have the same shape, except that that the plan for SqlClient has some extra operators due to the data-type mismatch. Those operators are not likely to cost you 50 seconds, though. There can, however, be other differences not revealed in the graphics, but we would need to see the XML for that.

I would guess that it is a parameter-sniffing issue, so that the ODBC client gets a cached plan optimised for a different set of parameter values than the one you have now. And by some chance, that plan is faster than one optimised for the current values.

ODBCClient and SQLClient gets different cache entry since the text is different.

If you want general help with optimising the query, we need to see the view definition, the definition of the underlying tables with their indexes and the execution plans in XML form. Since you mask names in the graph, I can understand that you may not want to do this in a public forum. But we can help with optimisation without facts to work from.

Answer 2

Hi @Knot

Have you compared the execution plan? The execution plans generated by SQL Server might be different depend on the client driver used.

Try adding OPTION (RECOMPILE) to avoid cached plans.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".