DB optimisation vs OOP layer separation

[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.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "ASC")
* the presentation layer gets the open connection back from
Person.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "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.GetAll OrderBy("LastNa me", "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
ddlIntroducerNa mes.DataSource =
Person.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "ASC")
ddlIntroducerNa mes.DataTextFie ld = "WholeName"
ddlIntroducerNa mes.DataValueFi eld = "PersonID"
ddlIntroducerNa mes.DataBind()

'DDL2
ddlWECReps.Data Source = Wec.User.GetAll OrderBy("LastNa me", "DESC")
ddlWECReps.Data TextField = "WholeName"
ddlWECReps.Data ValueField = "UserID"
ddlWECReps.Data Bind()

'DDL3
ddlIntroducedBy .DataSource = Person.GetAllAc tivesOrderBy("L astName", "ASC")
ddlIntroducedBy .DataTextField = "WholeName"
ddlIntroducedBy .DataValueField = "PersonID"
ddlIntroducedBy .DataBind()

[Justin Rogers]

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.

--
Justin Rogers
DigiTec Web Consultants, LLC.
"Jon Maz" <jo****@surfeu. de.no.spam> wrote in message
news:Oo******** ******@TK2MSFTN GP12.phx.gbl...

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.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "ASC")
* the presentation layer gets the open connection back from
Person.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "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.GetAll OrderBy("LastNa me", "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
ddlIntroducerNa mes.DataSource =
Person.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "ASC")
ddlIntroducerNa mes.DataTextFie ld = "WholeName"
ddlIntroducerNa mes.DataValueFi eld = "PersonID"
ddlIntroducerNa mes.DataBind()

'DDL2
ddlWECReps.Data Source = Wec.User.GetAll OrderBy("LastNa me", "DESC")
ddlWECReps.Data TextField = "WholeName"
ddlWECReps.Data ValueField = "UserID"
ddlWECReps.Data Bind()

'DDL3
ddlIntroducedBy .DataSource = Person.GetAllAc tivesOrderBy("L astName", "ASC") ddlIntroducedBy .DataTextField = "WholeName"
ddlIntroducedBy .DataValueField = "PersonID"
ddlIntroducedBy .DataBind()

[Jon Maz]

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

[Morgan]

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.
"Jon Maz" <jo****@surfeu. de.no.spam> wrote in message
news:Oo******** ******@TK2MSFTN GP12.phx.gbl...

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.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "ASC")
* the presentation layer gets the open connection back from
Person.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "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.GetAll OrderBy("LastNa me", "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
ddlIntroducerNa mes.DataSource =
Person.GetAllAc tiveNonIntroduc ersOrderBy("Las tName", "ASC")
ddlIntroducerNa mes.DataTextFie ld = "WholeName"
ddlIntroducerNa mes.DataValueFi eld = "PersonID"
ddlIntroducerNa mes.DataBind()

'DDL2
ddlWECReps.Data Source = Wec.User.GetAll OrderBy("LastNa me", "DESC")
ddlWECReps.Data TextField = "WholeName"
ddlWECReps.Data ValueField = "UserID"
ddlWECReps.Data Bind()

'DDL3
ddlIntroducedBy .DataSource = Person.GetAllAc tivesOrderBy("L astName", "ASC") ddlIntroducedBy .DataTextField = "WholeName"
ddlIntroducedBy .DataValueField = "PersonID"
ddlIntroducedBy .DataBind()

[Jon Maz]

Every time, I'm afraid...

[Jon Maz]

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

[Fergus Cooney]

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

[Fergus Cooney]

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.Sql Client

Public Class clsConnectionCa che

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

Private Sub New 'No instantiation.
End Sub

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

Private Shared m_aloConnection s As ArrayList 'of CachedConnectio n

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

'Look for the connection string in the cache.
For Each oConnection In m_aloConnection s
If oConnection.sCo nnString = sConnString Then
'Return the cached SqlConnection.
Return oConnection.oSq lConnection
End If
Next
'The connection string was not found and
'therefore the SqlConnection is not cached.

'Create a new CachedConnectio n.
oConnection = New CachedConnectio n
oConnection.oSq lConnection = New SqlClient.SqlCo nnection (sConnString)
oConnection.sCo nnString = sConnString
oConnection.iUs ageCount = iNumTimesToReus e

'Add it to the cache.
m_aloConnection s.Add (oConnection)

'Return the new SqlConnection.
Return oConnection.oSq lConnection
End Function

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

'Look for the SqlConnection in the cache.
For Each oConnection In m_aloConnection s
If oConnection.oSq lConnection Is oSqlConnection Then
'Another disconnect - reduce the usage count.
oConnection.iUs ageCount = oConnection.iUs ageCount - 1

'Is it the last?
If oConnection.iUs ageCount = 0 Then
'Do disconnect stuff here.
oSqlConnection. Close()
oSqlConnection. Dispose()
m_aloConnection s.Remove (oConnection)
End If

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

End Class
'============== =============== =============== =============== ==============
Regards,
Fergus

[Jon Maz]

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

Thanks for all the help,

JON