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

[zeandave]

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?

[zeandave]

I found thati it was because MultipleActiveResultSets=True in my connection string in App that connect with Microsoft.Data.SqlClient. I tried removing it, and now they take the same amount of time.