Which is the better approach?

R

Rob Meade

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
 
B

Bob Barrows [MVP]

Rob said:
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 CourseAttributes. It would have three columns: product
code, Attribute, Attribute Type (either "Feature", "Objective" or
"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
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 CourseAttributes table above - you could make a view
out of this.

Bob Barrows
 
R

Rob Meade

...
Hmm, I would probably have made a single table out of these, perhaps
calling the table CourseAttributes. It would have three columns: product
code, Attribute, Attribute Type (either "Feature", "Objective" or
"PreRequisite")

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
:eek:D), 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 CourseAttributes 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
 
B

Bob Barrows [MVP]

Rob said:
...


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.


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
SP's I mentioned in my other post that might not be so bad...I guess
the execution time might be slightly longer?


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.NextRecordset.
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.
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, coursecolvarchar,
'' 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
 
B

Bob Barrows [MVP]

Rob said:
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.
 
R

Rob Meade

...
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 :eek:) 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.NextRecordset.
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
 
R

Rob Meade

...
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
 
B

Bob Barrows [MVP]

Rob said:
...


I see, or rather I don't :eek:) 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.
 
M

Mike Brind

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 :)
 
R

Rob Meade

...
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 :eek:)

Oh - and with regards to the RS.NextRecordSet 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
 
B

Bob Barrows [MVP]

Rob said:
...


OIC! Thanks :eek:)

Oh - and with regards to the RS.NextRecordSet 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
set rs=cn.execute(<proc_that_returns_3_resultsets>)
if not rs.eof then
...
end if
set rs=rs.nextrecordset
if not rs.eof then
...
end if
set rs=rs.nextrecordset
if not rs.eof then
...
end if
 
R

Rob Meade

...
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.NextRecordset.

I'm getting this Bob - any ideas?

ADODB.Recordset error '800a0cb3'
Current provider does not support returning multiple recordsets from a
single execution.
/parasolit/statics/mainbody-training-course.asp, line 78
That line would read:

Set RS2 = RS.NextRecordset

Basically, because I need to keep the "course" recordset alive, I've created
two - an outer looping one, and a series of inner looping ones...

Regards

Rob
 
R

Rob Meade

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

hehe, hi Mike - no sh!t :eek:)

The problem now is that I need to get the heirarchy too! Each course
belongs to a "series" - the series can be made up of several courses - they
have yet to send me this - which, I'd put good money on the fact that
they'll use some other kind of identifier that I *dont* have in the courses
xml file!!!

They've clearly got all this in a database at their end, a simple export
would have sufficed, but ooooohhhh no - couldn't just have it made easy
could I! :eek:)

Regards

Rob
PS: If you know anything about multiple recordsets out of a stored proc -
feel free to check out the problems I'm having ;o)
 
B

Bob Barrows [MVP]

Rob said:
...


I'm getting this Bob - any ideas?

ADODB.Recordset error '800a0cb3'
Current provider does not support returning multiple recordsets from a
single execution.
/parasolit/statics/mainbody-training-course.asp, line 78
That line would read:

What provider are you using? You're using SQL Server, right? I've never used
the MSDASQL provider (ODBC) with SQL Server, but I assume that can handle
multiple resultsets ...
If you're using SQLOLEDB, well, I've never run into this error message ...
Set RS2 = RS.NextRecordset

Basically, because I need to keep the "course" recordset alive, I've

Well, you could use GetRows to put the first one into an array ... nothing
says you HAVE to loop through a recordset, does it?
 
R

Rob Meade

...
What provider are you using? You're using SQL Server, right? I've never
used the MSDASQL provider (ODBC) with SQL Server, but I assume that can
handle multiple resultsets ...
If you're using SQLOLEDB, well, I've never run into this error message ...

My connection string stuff goes like this:

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=TITUS;User
ID=<userid>;Password=<password>;Initial Catalog=ParasolTraining"
Set objCommand = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")
objCommand.CommandText = SQL
objCommand.CommandType = adCmdText
Set objCommand.ActiveConnection = objConnection
RS.Open objCommand,,adOpenKeySet, adLockOptimistic

I don't really know what the adOpenKeySet/ adLockOptimistic stuff does (I
never really have) - could it be anything to do with that?
Well, you could use GetRows to put the first one into an array ... nothing
says you HAVE to loop through a recordset, does it?

Wouldn't I have the same problem though Bob? ie, populating "anything" from
a recordset it cant get? Or did I miss a step here?

Thanks for any further help

Rob
 
B

Bob Barrows [MVP]

Rob said:
...


My connection string stuff goes like this:

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source=TITUS;User
ID=<userid>;Password=<password>;Initial Catalog=ParasolTraining"
Set objCommand = Server.CreateObject("ADODB.Command")
Set RS = Server.CreateObject("ADODB.Recordset")
objCommand.CommandText = SQL
objCommand.CommandType = adCmdText
Set objCommand.ActiveConnection = objConnection

Nothing to do with your problem, but I'm not sure why you are bothering with
an explicit Command object here - you aren't passing any parameters that I
can see. And why use adCmdText when calling a stored procedure? Use
adCmdStoredProc in this case.

Oh! Are you concatenating the parameter values into the SQL variable? IMO,
that's a bad idea. See the text at the end of this message for my reasoning.

RS.Open objCommand,,adOpenKeySet, adLockOptimistic

I don't really know what the adOpenKeySet/ adLockOptimistic stuff
does (I never really have) -

Time to learn :)
http://msdn.microsoft.com/library/en-us/ado270/htm/mdcstcursortypeenum.asp

http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthnextrec.asp

I probably would not use a keyset cursor in this situation. Actually I would
not use anything but either a server-side forward-only cursor (most likely)
or a client-side static cursor (rare - only if I need bookmark support -
http://msdn.microsoft.com/library/en-us/ado270/htm/mdprobookmark.asp - very
rare) in ASP. I am never intending to have a cursor open long enough to care
about changes made by other users, which is the main reason for using
dynamic or keyset cursors.
could it be anything to do with that?

I don't think so, but I've never tested it. Try testing using a simple
forwardonly cursor:

Set RS = Server.CreateObject("ADODB.Recordset")
objConnection.NameOfProcedure Parm1,...,ParmN,RS
if not rs.eof then arCourses = rs.GetRows
Set rs=rs.NextRecordset
if not rs.eof then arFeatures=rs.GetRows
etc.

Wouldn't I have the same problem though Bob? ie, populating
"anything" from a recordset it cant get?

I was not addressing your inability to use NextRecordset. I was addressing
your assertion that you needed a second recordset object.

-------------------------------------------------------------------------------------------
There are several ways to pass parameter values to stored procedures:


1. Use the technique described here: http://www.aspfaq.com/show.asp?id=2201


Personally, I don't like this technique since:
a. You have to worry about preventing hackers from injecting SQL into your
code (there are ways to prevent this - see the SQL Injection FAQ at
www.sqlsecurity.com)


b. You have to correctly delimit your parameter values, just as if you were
creating a dynamic SQL statement (actually, that is exactly what you are
doing here). You also have to correctly handle string values that contain
literal characters that are normally used as delimiters. While I've done
this enough times so that it is second nature to me now, in the beginning
this was the largest stumbling block to my learning how to create strings
containing dynamic SQL statements.


c. There is some performance-impairing overhead involved with both the
concatenation of the SQL statement that ultimately runs the stored
procedure, and the preparation of the statement on the SQL Server box, which
happens before the statement is actually executed.


d. It forces you to return data only by recordsets: no output or return
values can be used with this technique. Recordsets require substantial
resources, both on the SQL Server which has to assemble the resultset and
pass it back to the client, and on the web server which has to marshal the
resultset and transform it into an ADO recordset. This is a lot of overhead
when we're talking about returning one or two values to the client.


However, a lot of people do like this technique because:
a. They have no problem knowing when and how to concatenate delimiters into
the SQL statement, and how to handle string parameters that contain literal
characters that are normally used as delimiters
b. They have taken the necessary steps to prevent SQL Injection
c. You can assign the statement to a variable and, if there's an error
during the debug process, you can response.write the variable to see the
actual statement being sent to the SQL Server. If the statement has been
created correctly, you can copy and paste it from the browser window into
Query Analyzer and further debug it
d. They are aware of the performance hit, and consider it to be too minor to
worry about. (To be fair, in many cases, this perfomance hit is relatively
minor)


The alternatives I prefer completely eliminate objection b from above.


1. If you have output parameters, or you are interested in using the Return
value from your procedure, use an explicit ADO Command object. Now, this can
be tricky, especially if you do it the correct way (manually create the
Parameters collection using CreateParameter instead of using
Parameters.Refresh which involves an extra time-consuming trip to the
database). However, there are many stored procedure code generators out
there that vastly simplify this process, including the one I wrote which is
available here:
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

2. The technique I use most often is the "procedure-as-connection-method"
technique. With ADO 2.5 and higher, stored procedures can be called as if
they were native methods of the connection object, like this:
conn.MyProcedure parmval1,...,parmvalN
This completely avoids the need to worry about delimiters, literal or
otherwise. Plus it turns out that this technique also causes the procedure
to be executed in a very efficient manner on the SQL Server box.

You can also use this technique if your procedure returns a recordset:
set rs=server.createobject("adodb.recordset")
'optionally, set the cursor location and type properties
conn.MyProcedure parmval1,...,parmvalN, rs

HTH,
Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top