poor performance of managed data providers.

[dyk]

Hello!

I'am trying to run T-SQL query based on stored procedure and I was surprised
by essential diferences in the time of execution of the same query using
..NET application & SQL Query Analizer.

The results of analyzing my query (using SQL Profiler) are bellow:
1.------------------------------------------------
TEXTDATA: EXEC WayBillBrowse @PersonalAccountNumber = NULL, @GarageNumber =
NULL, @RegSign = NULL, @FromDate = 'Jul 1 2003 12:00:00:000AM', @ToDate =
'Sep 1 2003 12:00:00:000AM', @Status1 = 1, @Status2 = 2, @Status3 = 3,
@Status4 = NULL, @Status5 = NULL
APPLICATION NAME: SQL Query Analyzer
CPU: 1252
READS: 133327
WRITES: 0
DURATION: 2683
2.------------------------------------------------
TEXTDATA: the same
APPLICATION NAME: .Net SqlClient Data Provider
CPU: 50843
READS: 1562583
WRITES: 0
DURATION: 53266
---------------------------------------------------

Could you please explain why results so different?

I'm using
Client: .NET Framefork 1.1, Windows XP (SP1), 512 Mb RAM
Server: SQL Server 2000 Dev. (SP3) ,Windows Server 2K SP4 (no other users
connected)

Thank you.

 

[William Ryan]

Did you send it in with CommandType.StoredProcedure and explicitly type your
Parameters? Post the called that called the second one and it'd probably be
easier to figure out.

HTH,

Bill

 

[David Browne]

Did you send it in with CommandType.StoredProcedure and explicitly type your
Parameters? Post the called that called the second one and it'd probably be
easier to figure out.

Yes. And post the stored procedure code. And cross-post to
..sqlserver.programming.

This isn't a problem with the provider, per-se. Since the server-side
execution appears to be different, there's probably a server-side reason for
it.

Off the top of my head, there's a couple of things it could be.

1-Different session settings (can block use of indexed views and indexes on
computed columns).
2-Parameter sniffing.

Turn on profiler and capture the complete session for each case, and post
the trace. This will contain the session settings as well as the stored
procedure invocation methods.

David

 

[bruce barker]

if you do a paramertized text query with the managed provider, it first
produces and parameterized query string. then it calls a builtin stored proc
to execute it. this requires extra cpu on the first call, but if you make a
send call on the same cmd object it will call the builtin proc will use the
compiled query.

you can avoid all this but using a commandtype of stored proc and skip all
this.

-- bruce (sqlwork.com)

 

[dyk]

Thank you all!

Managed data provider for SQL Server creates session with SET ARITHABORT OFF
by default, Query Analyzer uses SET ARITHABORT ON. This is only diference in
session state, as result query plan for managed provider completly
different... I don't understand why, if you know please explain this
behavior.

Thank you.

 

[Pablo Castro [MS]]

Did you try setting SET ARITHABORT ON with SqlClient? Did the perf come
back?

Also, did you use CommandType.StoredProcedure?

Thanks,

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jeff Davis]

This is because indexes on views are only used if the connection options are set a certain way. This is because one of these options were set differently it could change the data being returned from the view, which would invalidate the index. If the connection settings are wrong for SELECT statements SQL Server will just not use the indexed view, while errors will be generated if you attempt to update the view data with incorrect connection settings.

Here is a quote from SQL Books Online from the heading "SET Options That Affect Results":

SET Option Settings
Any SET options that affect the results generated by Transact-SQL statements must have the same settings for all operations referencing the index. There are seven SET options that affect the results stored in computed columns and returned by views. All connections using indexes on computed columns or indexed views must have the same settings for these seven options:

a.. These six SET options must be set to ON:
a.. ANSI_NULLS


b.. ANSI_PADDING


c.. ANSI_WARNINGS


d.. ARITHABORT


e.. CONCAT_NULL_YIELDS_NULL


f.. QUOTED_IDENTIFIER
b.. The NUMERIC_ROUNDABORT option must be set to OFF.
These SET options must be set correctly for any connection that creates an index on a view or computed column. Any connection executing INSERT, UPDATE or DELETE statements that change data values stored in the indexes must have the correct settings. This includes bulk copy, Data Transformation Services (DTS), and replication operations. Microsoft® SQL ServerT 2000 generates an error and rolls back any insert, update, or delete operation attempted by a connection that does not have the proper option settings.



Jeff Davis