473,554 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Which is the better approach?

Hi all,

I have a databse which I'm pulling the data from for my ASP page.

I have 4 tables, Course, Feature, Objective, and PreRequisite. The last
three all contain a course product code and a text column - the course
product code links the row(s) to the Course table.

So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in Objective
and 2 in PreRequisite.

I grab all of these using my stored procedure which, using the LEFT JOIN's
returns a multitude of rows, what I'm wondering is whether I am better
returning say 20 rows of data, and then having to iterate through
determining where the Features start/end, Objectives start/end and
PreRequisites start/end (ie, they are repeated in the data) or - whether it
would be more effecient to hit each of the tables with a stored procedure,
using the same connection and thus getting just the 1 course, then 3
features to iterate through, then 5 objectives to iterate through and then 2
pre-requisites to iterate through - all of which would then get displayed to
the page.

The latter option sounds "clearer", and obviously a bit easier code wise -
but I just wondered whether there would be much of a difference performance
wise - seems that I either get lots of rows in one hit and walk away from
SQL Server and let the web server do the work, or I do less on the web
server and hit the SQL Server several times....

Any suggestions would be appreciated.

Best regards

Rob
May 31 '06 #1
15 3170
Rob Meade wrote:
Hi all,

I have a databse which I'm pulling the data from for my ASP page.

I have 4 tables, Course, Feature, Objective, and PreRequisite. The
last three all contain a course product code and a text column - the
course product code links the row(s) to the Course table.

So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in
Objective and 2 in PreRequisite.
Hmm, I would probably have made a single table out of these, perhaps
calling the table CourseAttribute s. It would have three columns: product
code, Attribute, Attribute Type (either "Feature", "Objective" or
"PreRequisi te")

I grab all of these using my stored procedure which, using the LEFT
JOIN's returns a multitude of rows, what I'm wondering is whether I
am better returning say 20 rows of data, and then having to iterate
through determining where the Features start/end, Objectives
start/end and PreRequisites start/end (ie, they are repeated in the
data) or - whether it would be more effecient to hit each of the
tables with a stored procedure, using the same connection and thus
getting just the 1 course, then 3 features to iterate through, then 5
objectives to iterate through and then 2 pre-requisites to iterate
through - all of which would then get displayed to the page.

The latter option sounds "clearer", and obviously a bit easier code
wise - but I just wondered whether there would be much of a
difference performance wise - seems that I either get lots of rows in
one hit and walk away from SQL Server and let the web server do the
work, or I do less on the web server and hit the SQL Server several
times....

Any suggestions would be appreciated.

Best regards

Rob

My inclination is to make as few trips to the database (out-of-process)
as possible.
Two options:
1 Return multiple resultsets from the procedure, using the recordset's
NextRecordset method to move to the next recordset.
2.Use a union query to return a single resultset with the structure
suggested for the CourseAttribute s table above - you could make a view
out of this.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
May 31 '06 #2
"Bob Barrows [MVP]" wrote ...
Hmm, I would probably have made a single table out of these, perhaps
calling the table CourseAttribute s. It would have three columns: product
code, Attribute, Attribute Type (either "Feature", "Objective" or
"PreRequisi te")
ok - at this time it seems that the data structure is pretty set, ie, I have
no knowledge of additional items being need for a type of "Feature" only for
example - but if there were I'd probably have to adopt the existing
structure at that point - so I could make this change now that you've
suggested.
My inclination is to make as few trips to the database (out-of-process)
as possible.
Would you define a "trip" as a connection, or the execution of a command? I
was always lead to believe that it was the number of connections that could
have a big impact on the server (especially if you dont close 'em - tee hee
:oD), but I thought perhaps if there was one connection that fired those 4
SP's I mentioned in my other post that might not be so bad...I guess the
execution time might be slightly longer?
Two options:
1 Return multiple resultsets from the procedure, using the recordset's
NextRecordset method to move to the next recordset.
I've never tried that in ASP - I've done something similar in .net with the
dataset/datatables - I guess its similar is it?
2.Use a union query to return a single resultset with the structure
suggested for the CourseAttribute s table above - you could make a view
out of this.


If I do this Bob, isn't that going to be exactly what I get from the SP
right now though? ie, about 20 rows per course where most of the columns in
the rows have the same data (ie the data from the Course table) because it
cant have the empty columns? I'd still have to iterate through a larger
record set and look for the differences to know when I'd got all of the
features or all of the objectives wouldn't I?

Thanks for your reply Bob - and further information appreciated.

Regards

Rob

Jun 1 '06 #3
Rob Meade wrote:
"Bob Barrows [MVP]" wrote ...
Hmm, I would probably have made a single table out of these, perhaps
calling the table CourseAttribute s. It would have three columns:
product code, Attribute, Attribute Type (either "Feature",
"Objective" or "PreRequisi te")
ok - at this time it seems that the data structure is pretty set, ie,
I have no knowledge of additional items being need for a type of
"Feature" only for example - but if there were I'd probably have to
adopt the existing structure at that point - so I could make this
change now that you've suggested.
My inclination is to make as few trips to the database
(out-of-process) as possible.


Would you define a "trip" as a connection, or the execution of a
command?


I'm referring to the execution of a command, which requires the command to
be sent out-of-process to the database, and results to be marshalled back
into process.
I was always lead to believe that it was the number of
connections that could have a big impact on the server (especially if
you dont close 'em - tee hee
oD), but I thought perhaps if there was one connection that fired
those 4 SP's I mentioned in my other post that might not be so bad...I guess
the execution time might be slightly longer?
Two options:
1 Return multiple resultsets from the procedure, using the
recordset's NextRecordset method to move to the next recordset.


I've never tried that in ASP - I've done something similar in .net
with the dataset/datatables - I guess its similar is it?


Very. A stored procedure with multiple select statements, or a batched set
of queries, will return multiple resultsets which can be processed by using
set rs = rs.NextRecordse t.
But, this is really only a little more efficient than using multiple calls
to separate procedures, since the request for the next resultset has to be
sent to the database which has the results cached. It's the caching that
makes the difference.
2.Use a union query to return a single resultset with the structure
suggested for the CourseAttribute s table above - you could make a
view out of this.
If I do this Bob, isn't that going to be exactly what I get from the
SP right now though? ie, about 20 rows per course where most of the
columns in the rows have the same data (ie the data from the Course
table) because it cant have the empty columns?


Well, you could do something like:

select 'Course' as Source, courseid, coursecolint, coursecolvarcha r,
'' as Attribute
from Course where courseid = ...
union all
select 'Feature', courseid,null,' ', Feature
from Feature where courseid = ...
etc.
I'd still have to
iterate through a larger record set and look for the differences to
know when I'd got all of the features or all of the objectives
wouldn't I?

The cost of iterating through 20 rows is likely to be small, especially if
you disconnect from the database while doing it.
How many columns from the Course table are we talking about? If only a few,
don't worry about it. If there are a large number of columns, then I might
lean toward using two resultsets: one containing the data from the Course
table, and the other containing the attributes.

Only testing can tell which approach is better.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jun 1 '06 #4
Rob Meade wrote:
Hi all,

I have a databse which I'm pulling the data from for my ASP page.

I have 4 tables, Course, Feature, Objective, and PreRequisite. The
last three all contain a course product code and a text column - the
course product code links the row(s) to the Course table.

So, I might have 1 entry in Course, but perhaps 3 in Feature, 5 in
Objective and 2 in PreRequisite.


Another option would be to pivot (crosstab) the data in the "attribute"
tables so you wind up returning a single row for each course to the client.
Unless you are using sql2005, which AIUI may contain builtin pivoting
functionality, this will likely involve some messy dynamic sql. Google
should yield you several examples of how to do it.

Again. Each approach should be tested and benchmarked for comparison.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jun 1 '06 #5
"Bob Barrows [MVP]" wrote ...
I'm referring to the execution of a command, which requires the command to
be sent out-of-process to the database, and results to be marshalled back
into process.
I see, or rather I don't :o) Not sure what you mean by "out of process" and
"into process" etc etc
Very. A stored procedure with multiple select statements, or a batched set
of queries, will return multiple resultsets which can be processed by
using set rs = rs.NextRecordse t.
But, this is really only a little more efficient than using multiple calls
to separate procedures, since the request for the next resultset has to be
sent to the database which has the results cached. It's the caching that
makes the difference.
I see..thanks..
The cost of iterating through 20 rows is likely to be small, especially if
you disconnect from the database while doing it.
How many columns from the Course table are we talking about?
There's about 8 or 9 - not many, just the items from the course description
(xml) that didn't have repeating items...for example, CourseTitle,
CourseSynopsis, CourseAudience etc etc
If only a few, don't worry about it. If there are a large number of
columns, then I might lean toward using two resultsets: one containing the
data from the Course table, and the other containing the attributes.

Only testing can tell which approach is better.


Thanks Bob - appreciate you reply and info - originally the course pages
were comprimising of 1587 html files, I needed to discard parts of these and
insert bits here and there - thankfully I managed to get an XML source for
each course so was able to write a little .net importer which populated the
database (hence being able to change the design if its deemed
inappropriate). There will be ONE .asp page which will receive a course id
and populate the page with the details, I dont think its going to be
massively popular hits wise, although the rest of the site is so I guess it
could be over time. Just wanted to try and get things as good as I could
from the outset rather than have to re-address it later on.

Regards

Rob
Jun 1 '06 #6
"Bob Barrows [MVP]" wrote ...
Another option would be to pivot (crosstab) the data in the "attribute"
tables so you wind up returning a single row for each course to the
client.
Unless you are using sql2005, which AIUI may contain builtin pivoting
functionality, this will likely involve some messy dynamic sql. Google
should yield you several examples of how to do it.


Hi Bob,

I remember trying to do this a long time ago with a dataset at "work" - what
a nightmare that was - I dont think we even managed to do it in the end - I
think at the moment, whilst I do want something really efficient, I would
probably pass on this option unless someone could give me a REALLY easy to
understand example etc - not because I'm lazy, but because at the moment I
can't justify the time to research that against getting the rest of the
project launch (I've gotta through some eCommerce stuff together for this
yet too - never done that before)...

Cheers for the thought though,

Rob
Jun 1 '06 #7
Rob Meade wrote:
"Bob Barrows [MVP]" wrote ...
I'm referring to the execution of a command, which requires the
command to be sent out-of-process to the database, and results to be
marshalled back into process.


I see, or rather I don't :o) Not sure what you mean by "out of
process" and "into process" etc etc

Well, you have a process in inetinfo processing the vbscript code in
your asp page. You have another process on your sql server processing
commands, etc. It's always expensive to marshal information between
processes.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jun 1 '06 #8

Rob Meade wrote:
[snip]

Thanks Bob - appreciate you reply and info - originally the course pages
were comprimising of 1587 html files, I needed to discard parts of these and
insert bits here and there - thankfully I managed to get an XML source for
each course


LOL. I bet you were relieved! That saved you having to fiddle around
with regular expressions :-)

--
Mike Brind

Jun 1 '06 #9
"Bob Barrows [MVP]" wrote...
Well, you have a process in inetinfo processing the vbscript code in
your asp page. You have another process on your sql server processing
commands, etc. It's always expensive to marshal information between
processes.


OIC! Thanks :o)

Oh - and with regards to the RS.NextRecordSe t stuff.....how would I do
this...

Get first course from first record set

Get second recordset and iterate through all records

Get third recordset and iterate through all records

Get forth recordset and iterate through all records

Move on to the next course

??

I've just typed out what I thought, but then it didn't look right - I'll
need more than 1 recordset object won't I? ie, one for the courses (big
outer loop), and then 1 that gets used several times but re-populated from
the 2nd, 3rd, and 4th recodsets iterating through all rows in each - then,
the RS.MoveNext at the end to move to the next course - that sound about
right?

Regards

Rob
Jun 1 '06 #10

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

Similar topics

17
2528
by: lawrence | last post by:
How is it possible that the question "How do I detect which browser the user has" is missing from this FAQ: http://www.faqts.com/knowledge_base/index.phtml/fid/125 and is only here on this with a link to old information that suggests use of "navigator": http://developer.irt.org/script/43.htm
10
2034
by: heromull | last post by:
We have an asp.net app with about 200 data entry forms. Customers may enter data into any number of forms. Each form's data is persisted in a corresponding sql table. When data entry is complete, it needs to be processed. Here's where the questions start. How can we easily determine in which tables a customer has data and how best to...
14
2330
by: J.S. | last post by:
In a Windows Form application, which is the better method to concatenate large blocks of code? 1. Reading the text from text files. 2. Adding the text to the VB file itself? Thanks! J.S. --
2
2080
by: zefciu | last post by:
In the tutorial there is an example iterator class that revesrses the string given to the constructor. The problem is that this class works only once, unlike built-in types like string. How to modify it that it could work several times? I have tried two approaches. They both work, but which of them is stylistically better? class...
1
1521
by: Saba | last post by:
Which of the following is a better approach to implement generic algorithms ; overloading or templates? Justify with the help of examples.
3
1276
by: =?Utf-8?B?Um9sYW5kcGlzaA==?= | last post by:
Hi, I'm doing an application using C# and I have this question: I have a method called sqlQueryBD which receives a string sql query and executes it against a database. I also have a class called sqlCompat which has one property "sqlText" with its getter and setter methods. The purpose of this class to translate the receiving sql query into...
23
2338
by: mike3 | last post by:
Hi. (posted to both newsgroups since I was not sure of which would be appropriate for this question or how specific to the given language it is. If one of them is inappropriate, just don't send replies to it.) I'm making a bignum package for use in a program I've got (this is something different from the pi program you may have heard...
20
3055
by: mike3 | last post by:
Hi. (Xposted to both comp.lang.c++ and comp.programming since I've got questions related to both C++ language and general programming) I've got the following C++ code. The first routine runs in like 65% of the time of the second routine. Yet both do the same thing. However, the second one seems better in terms of the way the code is written...
84
3904
by: Patient Guy | last post by:
Which is the better approach in working with Javascript? 1. Server side processing: Web server gets form input, runs it into the Javascript module, and PHP collects the output for document prep. 2. Client side processing: Web server gets form input and passes it to PHP which includes the Javascript written in a way to make the form input...
0
7603
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7806
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8047
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7568
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7891
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6151
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3568
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3553
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2021
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.