I am having a problem in an ASP site (HTTP/1.1:New application failed which
I believe has been caused by inefficient database connections/memory leaks).
One of the recent changes to the way connections are made is
changed connection string from
driver={SQL Server};server=production;uid=pub;pwd=pub;
database=pub
to
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pub;Data Source=Production
I thought that the OLE DB Provider is quicker/more efficient than the ODBC
driver for SQL Server, isn't it (although in one of the ASP file, I had to
change the CursorLocation used by ADO Recordset from adUseServer to
adUseClient to get a specific ASP file functioning).
Would I normally expect any other adverse outcome switching from ODBC to
SQLOLEDB ?Hi Patrick,
Thank you for using the newsgroup an it is my pleasure to help you with you
issue.
As for you question of when develope ASP application, which is the
recommended method to connect the database, from my experiece, for the
script, ADO is preferable.
1) ODBC is an API for database access that's both a formal and a de facto
industry standard. Besides being one of the most popular database
interfaces used by applications today, ODBC has gained status as the formal
call-level interface standard by American National Standards Institute
(ANSI) and International Organization for Standardization (ISO). SQL Server
provides a high-performance ODBC interface for all Windows-based
programming environments, and it can be distributed royalty-free with any
application. The SQL Server ODBC driver implements every function in the
ODBC 3 specification. In ODBC-speak, this makes it fully Level 2 (the
highest level) conformant.
RDO is an object interface that's closely tied to ODBC, which means that it
exposes all the functionality in the ODBC driver and is easily available to
Visual Basic programs. RDO supports building visual controls tied directly
to SQL Server data, which greatly reduces the amount of code that must be
written to display data on the screen.
2) OLE DB
OLE DB was first released by Microsoft in 1996 to provide a COM interface
to any tabular data source (that is, data that can be represented with rows
and columns). This includes data in spreadsheets and even text files. OLE
DB can be considered an object version of ODBC but is more powerful in that
it can access data from data sources beyond those that ODBC can access.
Unlike other object interfaces to SQL Server such as RDO, OLE DB doesn't
make programming a call-level interface like ODBC any easier. Also, because
OLE DB uses pointer data types extensively, it's only accessible from C and
C++.
3) ADO
ADO is a higher-level object interface on top of OLE DB that provides much
of the same functionality and performance. Because ADO is pointerless, it
can be accessed from scripting languages such as JScript and development
software such as Visual Basic, as well as from C and C++.
ADO is the recommended and supported interface for Internet applications
written using the Microsoft Visual InterDev development tool. Applications
written with Visual InterDev can call ADO from Active Server Pages (ASP)
and incorporate code written in VBScript or JScript.
For more information, I recommend you to ask you question in our related
newsgroup and the right engineer there will provide you detailed answer
about it:
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.publi
c.inetserver.asp.general
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||The ASP code is already using ADO (adodb.connection, adodb.recordset
objects). However, the connectionString used for opening a DB connection
has been changed from
driver={SQL Server};server=production;uid=pub;pwd=pub;database=pub
in old environment to
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=pub;Data Source=Production
in new environment
The new provider is better than ODBC isn't it, in terms of the underlying
way ADO use to connect to SQL Server 2000?
Also, could any of the following have any effect on memory usage (e.g., poor
garbage collections in terms of not closing connections):
- SQL Server Client Network Utility- "Enable Shared Memory": I am not sure
whether this should be ticked or not. The Server is a Windows 2000 Server
SP4 which is a Domain Controller, running SQL Server 2000 Standard Edition,
ASP, ASP.NET as well as Microsoft Content Management Server 2002 SP1a. (The
application that has failed is the ASP application connecting using ADO)
- SQL Server Client Network Utility- Protocol: Should Named Pipes be used in
preference to TCP/IP
?
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:5JdW06MAEHA.320@.cpmsftngxa06.phx.gbl...
> Hi Patrick,
> Thank you for using the newsgroup an it is my pleasure to help you with
you
> issue.
> As for you question of when develope ASP application, which is the
> recommended method to connect the database, from my experiece, for the
> script, ADO is preferable.
> 1) ODBC is an API for database access that's both a formal and a de facto
> industry standard. Besides being one of the most popular database
> interfaces used by applications today, ODBC has gained status as the
formal
> call-level interface standard by American National Standards Institute
> (ANSI) and International Organization for Standardization (ISO). SQL
Server
> provides a high-performance ODBC interface for all Windows-based
> programming environments, and it can be distributed royalty-free with any
> application. The SQL Server ODBC driver implements every function in the
> ODBC 3 specification. In ODBC-speak, this makes it fully Level 2 (the
> highest level) conformant.
> RDO is an object interface that's closely tied to ODBC, which means that
it
> exposes all the functionality in the ODBC driver and is easily available
to
> Visual Basic programs. RDO supports building visual controls tied directly
> to SQL Server data, which greatly reduces the amount of code that must be
> written to display data on the screen.
> 2) OLE DB
> OLE DB was first released by Microsoft in 1996 to provide a COM interface
> to any tabular data source (that is, data that can be represented with
rows
> and columns). This includes data in spreadsheets and even text files. OLE
> DB can be considered an object version of ODBC but is more powerful in
that
> it can access data from data sources beyond those that ODBC can access.
> Unlike other object interfaces to SQL Server such as RDO, OLE DB doesn't
> make programming a call-level interface like ODBC any easier. Also,
because
> OLE DB uses pointer data types extensively, it's only accessible from C
and
> C++.
> 3) ADO
> ADO is a higher-level object interface on top of OLE DB that provides much
> of the same functionality and performance. Because ADO is pointerless, it
> can be accessed from scripting languages such as JScript and development
> software such as Visual Basic, as well as from C and C++.
> ADO is the recommended and supported interface for Internet applications
> written using the Microsoft Visual InterDev development tool. Applications
> written with Visual InterDev can call ADO from Active Server Pages (ASP)
> and incorporate code written in VBScript or JScript.
> For more information, I recommend you to ask you question in our related
> newsgroup and the right engineer there will provide you detailed answer
> about it:
>
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.publi
> c.inetserver.asp.general
> Hope this helps.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Patrick,
Thank you for your update.
In my last reply, I just want to say to choose the OLEDB, sorry for the
mistype. For you questions, I will answer them one by one
1)As for the ASP will use the ADO to connect the database and in my last
email, I said ADO is a higher-level object interface on top of OLE DB, so
the level of connect the database would be:
For the provider of OLEDB:
ASP -> ADO -> OLEDB -> Database
For the the provider of ODBC:
ASP -> ADO -> OLEDB -> ODBC -> Database
Also, if the database is SQL Server, you could get for more from OLEDB for
both of them are Microsoft technologies.
Please refer to the following link for detailed information
Choosing Your Data Access Strategy
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoledb/htm
l/choosingcomponents.asp
2) Named Pipes vs. TCP/IP Sockets
In a fast local area network (LAN) environment, Transmission Control
Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are
comparable in terms of performance. However, the performance difference
between the TCP/IP Sockets and Named Pipes clients becomes apparent with
slower networks, such as across wide area networks (WANs) or dial-up
networks. This is because of the different ways the interprocess
communication (IPC) mechanisms communicate between
peers.
For named pipes, network communications are typically more interactive. A
peer does not send data until another peer asks for it using a read
command. A network read typically involves a series of peek named pipes
messages before it begins to read the data. These can be very costly in a
slow network and cause excessive network traffic, which in turn affects
other network clients.It is also important to clarify if you are talking
about local pipes or network pipes. If the server application is running
locally on the computer running an instance of Microsoft SQL Server?2000,
the local Named Pipes protocol is an option. Local named pipes runs in
kernel mode and is extremely fast.
For TCP/IP Sockets, data transmissions are more streamlined and have less
overhead. Data transmissions can also take advantage of TCP/IP Sockets
performance
enhancement mechanisms such as windowing, delayed acknowledgements, and so
on, which can be very beneficial in a slow network. Depending on the type
of
applications, such performance differences can be significant.TCP/IP
Sockets also support a backlog queue, which can provide a limited smoothing
effect compared to named pipes that may lead to pipe busy errors when you
are attempting to connect to SQL Server.
In general, sockets are preferred in a slow LAN, WAN, or dial-up network,
whereas named pipes can be a better choice when network speed is not the
issue, as it
offers more functionality, ease of use, and configuration options. You
could just choose which protocol to use depends on you production
environment.
3) As for Shared Memory Net-Library is used only for intra-computer
communications, you could let it as the default setting on the client
network utility.
Hope this helps!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
