.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
Best Practices - Connecting to Oracle from a fat client
Messages   Related Types
This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
Post a new message to this list...

Clint
Hello all -

I'm currently working on a program that connects to both an Oracle 9i
data warehouse as well as a SQL Server 2k server. The application will
be used mainly by 30 - 50 people internally, and will connect directly
to the respective servers (ie, not using remote objects via remoting
or web services).

In addition, the Oracle server is simply going to have single selects
against it, retrieving only one value (a name based on an account
number); nothing overly intensive. The SQL Server, on the other hand,
will be handling the logging and auditing, storage, and retrieval of
any information for the program - items that could be intensive.

That said, my question is this: What is the best practice when it
comes to opening these data connections? Would it be best to have a
single connection for each server opened when the program starts, or
would it be best to have the connections open as they are needed? The
Oracle server would be queried once every few minutes or so per user,
and only for a specific user role (once the lookup's done, the data's
stored in SQL Server for historical purposes). The SQL Server, on the
other hand, can expect to see a number of queries per minute per user,
for all user roles.

I can see benefits for both sides, but I haven't seen a good solid
"this is how you should do it" answer.

Any advice is appreciated - thanks!
Clint
Reply to this message...
 
    
Cowboy \(Gregory A. Beamer\) (VIP)
Connects pool automatically, so opening a single connection is not wise. In
addtion, each connection consumes memory. Do not open a single connection
and hold it. Good enough for a best practice.

Within a single page that pings a database over and over? Sure, open and run
everything without problem. Across pages, or across actual data calls? There
are some instances where this works, but it is an exception, not a rule.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside the box!
*************************************************
"Clint" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
Michael D. Long
Bad advice. Connection pools are process based, and a FAT client (aka.
2-tier) is a single process. Close the ONLY active connection and the pool
goes poof!

Actually, in high volume transaction processing applications where users are
posting transactions at a fairly steady pace you can often achieve the
lowest total cost of operations by using a 2-tier architecture and having
the client application maintains active connections. Oracle Session
creation is a very expensive operation. In this particular case I'd have to
second Bill V's earlier comments and recommend keeping the connections open
for the life of the application, with appropriate logic to handle
reconnecting on fatal errors.

Note: you can take advantage of connection sharing by configuring Oracle
Server to use Multi-Threaded Server (yet another MTS). For anyone
interested, Oracle MTS doesn't provide any benefits for n-tier
applications - it just chews up resources on the database server. I leave
the understanding of why as an exercise in logic for the reader. Hint:
consider the behavior of connection pooling.

--
Michael D. Long
********************************************************
Don't be trapped in an n-tier mind-set! Sometimes the old box is good. ;-)
********************************************************

"Cowboy (Gregory A. Beamer)" <Click here to reveal e-mail addressamM> wrote in
message news:u%Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
Clint (VIP)
I think I understand what you mean here, and I generally agree - but only in
reference to the web world or 2-tier applications. If all the data access
logic is on the client, and not on a centralized server, connection pooling
would become irrelevant, right? Unless it's a basic misunderstanding on my
part as to how connection pooling operates, pooling wouldn't be taken into
account in cases where there is only one connection repeatedly opening and
closing, right?

Thanks for your reply!

"Cowboy (Gregory A. Beamer)" wrote:

[Original message clipped]
Reply to this message...
 
    
William \(Bill\) Vaughn
Each connection consumes resources. The process of opening the connection
(especially the first time or when no connection is available in the pool)
is expensive. However, SQL Server is designed to handle hundreds to
thousands of connections without breathing hard--40 or so should not be a
problem. Consider than we ran systems with 800 plus connections on 386/33
systems with 4mb of RAM. Keeping the connection open has its benefits. You
don't have to wait for SSPI authentication to be repeated and there is no
wait while the pooling mechanism looks for an open connection. Since the
various Windows applications won't be sharing the connection pool, and you
won't be closing the connection you'll never have to worry about pool
overflow.

What you do need to be concerned with is that "stuff happens". A connection
is like a cell-phone connection. From time to time you'll lose connectivity.
In this case your application needs to be smart enough to recover from the
disconnect and continue on.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Clint" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
Clint (VIP)
That does make sense. I had figured SQL Server wouldn't even cough at 50-odd
connections, given the machine it's currently running on. I guess I was more
worried about what would be better for the client, and it appears that
opening and maintaining the connection might be the best bet. I think it's
safe to assume the same will hold true for the Oracle side of things as well.

(x-posted to languages.csharp for this part of the question)
On a tangent - if I'm going to attempt to keep the connection open
throughout the execution of the program, is there a better method to test
whether a connection is open than simply executing the method and hoping to
catch an exception? I noticed there wasn't a SqlConnection.IsConnected or
similar. I wouldn't be against putting a timer running on a background thread
constantly checking the connection ... but I'm not sure what the best way (in
relation to client performance) to do so would be.

Thanks, Bill, for your quick reply, though - that does narrow down question!

Clint

"William (Bill) Vaughn" wrote:

[Original message clipped]
Reply to this message...
 
    
William \(Bill\) Vaughn
Until ADO 2.0, you won't know (or have a suitable property) to determine the
viability of a connection. Again it's like a cell-phone connection--if no
one is talking it's tough to know if the other party is still there after
you come out of the tunnel. Some folks poll the server with a query. That
can work, but I also query the service. I have an example I demonstrate in
my workshop that shows how this can determine if the SQL Server service
(mssqlservice) is running on the target system. This does not guarantee that
the connection is viable, but it can signal when the server goes down...

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Clint" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
Clint (VIP)
My coworker here had advised me that there was a connection status property
available for both SqlConnection and OracleConnection called ConnectionState:

// lets hope the formatting isn't messed up in this:

if(sq.State == System.Data.ConnectionState.Closed)
{ ... } // open connection
else
{ ... } // do query

Is this a viable option, or is that property less than reliable?

Thanks again for all your help!

"William (Bill) Vaughn" wrote:

[Original message clipped]
Reply to this message...
 
    
William \(Bill\) Vaughn
Nope. The State property does not change state if the server or network goes
down. It does change state if YOU or the underlying code closes the
connection but not otherwise.

Pooling was invented for situations when you connect to databases that took
a long time to connect or where the application had to close the connection
(as in ASP). In client/server or local database use, there is no advantage
to using the connection pool. When your c/s systems start stressing the
remote server it starts to make sense to consider a more active
connect/disconnect strategy.

Another advantage to leaving the connection open is the ability to build
useful server state. This means #temp tables, server-side cursors or other
custom SET properties to make your application work more efficiently.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Clint" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
 
System.Data.ConnectionState
System.Data.OracleClient.OracleConnection
System.Data.SqlClient.SqlConnection




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification