473,245 Members | 1,740 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

DB optimisation vs OOP layer separation

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()

Nov 20 '05 #1
8 1871
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**************@TK2MSFTNGP12.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.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()

Nov 20 '05 #2
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
Nov 20 '05 #3
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**************@TK2MSFTNGP12.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.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()

Nov 20 '05 #4
Every time, I'm afraid...
Nov 20 '05 #5
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
Nov 20 '05 #6
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
Nov 20 '05 #7
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
Nov 20 '05 #8
That's excellent Fergus, just what I need to get me started!

Thanks for all the help,

JON
Nov 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: Griff | last post by:
Hi there I'm after some suggestions as to how one might best separate form and content in a normal run-of-the-mill web application. I'm sure whole bookshelves have been written on this, but I...
0
by: Pheller | last post by:
The time tracker example adds pieces of the Data Access layer to the business Object. Shouldn't there be a third layer that communicates with the DAL, Business Object and Presentation layer call...
4
by: MG | last post by:
A Newbie question perhaps, but here goes: Is it possible to seaprate the presentatieon layer, i.e., the html, from the application itself. For example, our developer has told us that we cannot...
16
by: simonwittber | last post by:
Hello People. I've have a very tight inner loop (in a game app, so every millisecond counts) which I have optimised below: def loop(self): self_pool = self.pool self_call_exit_funcs =...
11
by: Michael Rodriguez | last post by:
I have a windows form that makes a call to my BLL to get some data. The windows form has a progress bar component that I would like updated as the data retrieval takes place. However, strictly...
2
by: headware | last post by:
I'm relatively new to ASP.NET and ADO.NET, but I have a basic design question regarding the use of web services and APS.NET applications. Right now we have an application that uses web services to...
8
by: Rashmi | last post by:
Hi! I am new to web services. AFAIK, the advantages of using web services are : interoperable, easy integration and reuse, text based protocols and data formats - SOAP,WSDL,XML. Also, I read...
2
by: dkode | last post by:
Hello, I am laying out the architecture for a very large website that will scale to a very large degree. I have a couple of questions before I attempt to go and implement a Broker/Persistence...
5
by: Jens Jensen | last post by:
Hello, Does anyone know anything about how to optimise asp.net 2.0 pages for search engines? I'm thinking of a programmatic way of mapping sitemap structure to pages meta tags. Well it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.