«

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

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

"Bob Barrows [MVP]" wrote ...

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

Rob Meade wrote:
> "Bob Barrows [MVP]" wrote ...
>
>> 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'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.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.

>
>> 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?

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

--
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"

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"

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

"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

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

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

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

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