DB optimisation vs OOP layer separation

  • Thread starter Thread starter Jon Maz
  • Start date Start date
J

Jon Maz

Hi,

I'm facing a code-optimisation issue on an asp.net/vb.net/SQL Server 2000
project. A web page containing not much more than 3 DropDownLists is taking
nigh on 6 seconds to load, because each ddl opens up a separate connection
to the DB, pulls out its data, and closes its own connection before the next
ddl repeats the process.

The code to handle each DDL's connection to the DB is packaged in an object
(presentation-layer code below); two of the hits go via a Person object, and
one via a User object. Both these objects are separated into a business
layer and a data layer.

My optimisation goal is to have all these three DB hits done on just one DB
connection. The only way I can think of doing this would be:

* create a connection object in the presentation layer (more likely, an
SqlCommand object which contains an open connection),
* pass this open connection to
Person.GetAllActiveNonIntroducersOrderBy("LastName", "ASC")
* the presentation layer gets the open connection back from
Person.GetAllActiveNonIntroducersOrderBy("LastName", "ASC"), laden with data
* the pres layer takes out the data, fills up DDL1 with it, and passes the
still-open-connection to Wec.User.GetAllOrderBy("LastName", "DESC")
* the open connection comes back once more to the presentation layer, laden
with the second batch of data
* DDL2 is filled up with this data, and the process repeats for DDL3
* once the pres layer has got all the necessary data it kills the connection

I dare say I could adapt the various Business and Data Layer routines to
handle this. My main worry is about violating the OOP principle of layer
separation - if I'm going to start creating SqlConnections / SqlCommands in
my *presentation layer*, I might as well slit my OOP wrists right now....

Any input welcome!

TIA,

JON


'DDL1
ddlIntroducerNames.DataSource =
Person.GetAllActiveNonIntroducersOrderBy("LastName", "ASC")
ddlIntroducerNames.DataTextField = "WholeName"
ddlIntroducerNames.DataValueField = "PersonID"
ddlIntroducerNames.DataBind()

'DDL2
ddlWECReps.DataSource = Wec.User.GetAllOrderBy("LastName", "DESC")
ddlWECReps.DataTextField = "WholeName"
ddlWECReps.DataValueField = "UserID"
ddlWECReps.DataBind()

'DDL3
ddlIntroducedBy.DataSource = Person.GetAllActivesOrderBy("LastName", "ASC")
ddlIntroducedBy.DataTextField = "WholeName"
ddlIntroducedBy.DataValueField = "PersonID"
ddlIntroducedBy.DataBind()
 
Opening and closing three connections shouldn't be your problem, unless
there aren't enough connections available in your pool. If there aren't
then you have a bigger problem than utilizing three connection objects. I
would suggest changing the Min and Max Pool Size to 150 and 300 respectively
(assuming you are running against a full version of SQL Server 2000).
Setting Connection Reset to false can help improve performance as long as
you realize the connection state isnt going to be reset each time you make a
database call.

In general Min Pooling is set to 0, and Max set to 100. These can be fairly
stringent when a server comes under load.

If none of these help, then I'd say do what is performant. There isn't much
you can do to keep your OO design as stringent as you've laid out here AND
share your connections.
 
Hi Justin,

Thanks for your comments. In this case, it can't be a pool issue - the page
in question is still in development on my local machine, so the server is
suffering under a maximum strain of one concurrent user (me).

I'm still relatively new to OOP and n-tier architecture, and I'm a bit
shocked at the thought that there might be no OOP solution to this issue
that keeps the layers separate. For the moment, I am still (naively?)
hoping that someone will post along the lines of: "oh yes, that's a common
situation, and here's the standard way round it"...

Fingers crossed,

JON
 
Is it slow when the page is first loaded or is it slow every time?
If it's slow only the first time, I wouldn't worry about it, as it will only
affect the first user hitting the page.
 
The important thing is to make sure that you use the same
EXACT Connection string to connect to your database. This
allows the system to do connection pooling. If you take
care of that, opening and closing the connection will not
consume any time. Try putting your connection string in
the App.config file, and use it everytime in your
SqlConnections.


-----Original Message-----
Hi,

I'm facing a code-optimisation issue on an asp.net/vb.net/SQL Server 2000
project. A web page containing not much more than 3 DropDownLists is taking
nigh on 6 seconds to load, because each ddl opens up a separate connection
to the DB, pulls out its data, and closes its own connection before the next
ddl repeats the process.

The code to handle each DDL's connection to the DB is packaged in an object
(presentation-layer code below); two of the hits go via a Person object, and
one via a User object. Both these objects are separated into a business
layer and a data layer.

My optimisation goal is to have all these three DB hits done on just one DB
connection. The only way I can think of doing this would be:

* create a connection object in the presentation layer (more likely, an
SqlCommand object which contains an open connection),
* pass this open connection to
Person.GetAllActiveNonIntroducersOrderBy ("LastName", "ASC")
* the presentation layer gets the open connection back from
Person.GetAllActiveNonIntroducersOrderBy
Click to expand...
("LastName", "ASC"), laden with data
* the pres layer takes out the data, fills up DDL1 with it, and passes the
still-open-connection to Wec.User.GetAllOrderBy ("LastName", "DESC")
* the open connection comes back once more to the presentation layer, laden
with the second batch of data
* DDL2 is filled up with this data, and the process repeats for DDL3
* once the pres layer has got all the necessary data it kills the connection

I dare say I could adapt the various Business and Data Layer routines to
handle this. My main worry is about violating the OOP principle of layer
separation - if I'm going to start creating
Click to expand...
SqlConnections / SqlCommands in
 
The ConnectionString is already in the web.config file, so that's not it
either...


----- Original Message -----
From: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Friday, August 29, 2003 4:43 PM
Subject: DB optimisation vs OOP layer separation


The important thing is to make sure that you use the same
EXACT Connection string to connect to your database. This
allows the system to do connection pooling. If you take
care of that, opening and closing the connection will not
consume any time. Try putting your connection string in
the App.config file, and use it everytime in your
SqlConnections.
 
Hi Fergus,

A Connection Cache class, eh? Intriguing, sounds like just the kind of
thing I'm after... Any chance of a code snippet, or perhaps a link or two,
to get me started?

Cheers,

JON
 
Hi Morgan,

Good idea - turns out the two DDLs that call the Person object are much
slower than the one which calls the User object. Looks like I should take a
closer look at what's going on in my Person data-layer...

Cheers,

JON
 
Hi Jon,

I made it up ;-)

But I can do one for you later. You may get one or two in the meantime.

Later, Dude,
Fergus
 
Hi Jon,

Here's a bit of code that may be useful:

'=========================================================================
'The following implements a very simple object cache.
'In this case the cached object is an SqlConnection.
'
'The class is told how many times an object
'will be used before it is to be discarded.
'On creation (initial connect) the object's count is set.
'For each disconnection, the count is decremented.
'The object is only released when the count goes to zero.

'UNTESTED AND INCOMPLETE.
'REQUIRES ERROR HANDLING.

Imports System.Data.SqlClient

Public Class clsConnectionCache

'The number of times a new connection gets used before being discarded.
Public Shared iNumTimesToReuse As Integer

Private Sub New 'No instantiation.
End Sub

'=========================================================================
Private Class CachedConnection
Public sConnString As String
Public oSqlConnection As SqlConnection
Public iUsageCount As Integer
End Class

Private Shared m_aloConnections As ArrayList 'of CachedConnection

'=========================================================================
Public Function Connect (sConnString As String) As SqlConnection
Dim oConnection As CachedConnection

'Look for the connection string in the cache.
For Each oConnection In m_aloConnections
If oConnection.sConnString = sConnString Then
'Return the cached SqlConnection.
Return oConnection.oSqlConnection
End If
Next
'The connection string was not found and
'therefore the SqlConnection is not cached.

'Create a new CachedConnection.
oConnection = New CachedConnection
oConnection.oSqlConnection = New SqlClient.SqlConnection (sConnString)
oConnection.sConnString = sConnString
oConnection.iUsageCount = iNumTimesToReuse

'Add it to the cache.
m_aloConnections.Add (oConnection)

'Return the new SqlConnection.
Return oConnection.oSqlConnection
End Function

'=========================================================================
Public Sub Disconnect (oSqlConnection As SqlConnection)
Dim oConnection As CachedConnection

'Look for the SqlConnection in the cache.
For Each oConnection In m_aloConnections
If oConnection.oSqlConnection Is oSqlConnection Then
'Another disconnect - reduce the usage count.
oConnection.iUsageCount = oConnection.iUsageCount - 1

'Is it the last?
If oConnection.iUsageCount = 0 Then
'Do disconnect stuff here.
oSqlConnection.Close()
oSqlConnection.Dispose()
m_aloConnections.Remove (oConnection)
End If

Return
End If
Next
'The SqlConnection was not found.
'Throw a wobbly.
End Sub

End Class
'=========================================================================


Regards,
Fergus
 
That's excellent Fergus, just what I need to get me started!

Thanks for all the help,

JON
 
Back
Top