Re: Turning of ANSI Padding in a ado.net connection string
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 14 Apr 2006 22:05:28 +0000 (UTC)
Mark Talbot @ Rhetorik (MarkTalbotRhetorik@xxxxxxxxxxxxxxxxxxxxxxxxx)
writes:
I have a load of stored procedures to do some string analasis on our
database which are drasticaly faster in SQL Server 2005 Management
Studio than they are from ado.net. We have tracked this problem to the
fact that ansi padding is turned off by the database and manegment
studio, but the ado.net connector has it switch on by default. This is
creating an order of magnatude in performance degradtion and I cant find
any information on how to adjust the SET options that a connection
creates.
I doubt that your analysis is correct.
First of all, the database option ANSI_PADDING, only has an effect if
it's ON. Then it overrides a connection that comes in with OFF, which
mainly happens with legacy clients.
Second, ANSI_PADDING is by default ON in Mgmt Studio, even if there are
some scripting facilities that may end SET ANSI_PADDING ON at the end
for some reason. (This is a bug in my opinion.)
Third, the setting of ANSI_PADDING is saved per table column. Thus, at
run-time, ANSI_PADDING only affects table variables and temp tables.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Re: help. can't connect to sql server express with OleDbConnection
- Next by Date: Re: Accessing Excel data - Some columns are not coming through
- Previous by thread: help. can't connect to sql server express with OleDbConnection
- Next by thread: How to call data connect attribute in my program
- Index(es):
Relevant Pages
|
