Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Controls > Best practice ASP SQL

Reply
Thread Tools

Best practice ASP SQL

 
 
ThatsIT.net.au
Guest
Posts: n/a
 
      03-21-2009
I was wondering about performance implications for 2 ways of creating a
collection of objects.

Lets say I have 2 classes category and subcategory

in the category class I have a function that returns a collection of
subcategory objects.

what I do here is open a reader to a table in the database with the
subcategory info in it.
I then call the subcategory class each loop of the reader creating an array
of objects like so

myarray(n) = new subcategory(id,name,other,other,other...)

as you can see am passing all the data from the database table to the class
so that no trip to the database is needed from the subcategory class all
data is supplied.

Now I know that this second way is not as efficient as it make many trips to
the database like this

myarray(n) = new subcategory(id)

passing the id only I then have to load the other data from the database
each time a subcategory object is created.

Now I know that this is a performance cost but how much of a coast I don't
know. some times these second way is a real time saver where there is much
data to pass across. since in the second way you are only getting one row
from the database each time the amount of data retrieved from the database
is the same but there is many connections to the database the second way.

Should I worry too much about this overhead?


 
Reply With Quote
 
 
 
 
Luis Roquette Valdez
Guest
Posts: n/a
 
      03-21-2009
Hi there,

It's quite a picke you got there... lol...

Well... I'd advise you to get your SQL profiler out of your pocket and test
it by yourself...
I would personally just pass the ID and let my method get all the data it
needed for it. This way, next time you add another different column you want
to get from your database, all you need to change is your method and not all
calls to it... but that's about good/bad development and not SQL...

Do have a look at Profiler... get the times from it and make your judgement
based on that...

Cheers

"ThatsIT.net.au" wrote:

> I was wondering about performance implications for 2 ways of creating a
> collection of objects.
>
> Lets say I have 2 classes category and subcategory
>
> in the category class I have a function that returns a collection of
> subcategory objects.
>
> what I do here is open a reader to a table in the database with the
> subcategory info in it.
> I then call the subcategory class each loop of the reader creating an array
> of objects like so
>
> myarray(n) = new subcategory(id,name,other,other,other...)
>
> as you can see am passing all the data from the database table to the class
> so that no trip to the database is needed from the subcategory class all
> data is supplied.
>
> Now I know that this second way is not as efficient as it make many trips to
> the database like this
>
> myarray(n) = new subcategory(id)
>
> passing the id only I then have to load the other data from the database
> each time a subcategory object is created.
>
> Now I know that this is a performance cost but how much of a coast I don't
> know. some times these second way is a real time saver where there is much
> data to pass across. since in the second way you are only getting one row
> from the database each time the amount of data retrieved from the database
> is the same but there is many connections to the database the second way.
>
> Should I worry too much about this overhead?
>
>

 
Reply With Quote
 
 
 
 
Arne Vajh°j
Guest
Posts: n/a
 
      03-21-2009
ThatsIT.net.au wrote:
> I was wondering about performance implications for 2 ways of creating a
> collection of objects.
>
> Lets say I have 2 classes category and subcategory
>
> in the category class I have a function that returns a collection of
> subcategory objects.
>
> what I do here is open a reader to a table in the database with the
> subcategory info in it.
> I then call the subcategory class each loop of the reader creating an
> array of objects like so
>
> myarray(n) = new subcategory(id,name,other,other,other...)
>
> as you can see am passing all the data from the database table to the
> class so that no trip to the database is needed from the subcategory
> class all data is supplied.
>
> Now I know that this second way is not as efficient as it make many
> trips to the database like this
>
> myarray(n) = new subcategory(id)
>
> passing the id only I then have to load the other data from the database
> each time a subcategory object is created.
>
> Now I know that this is a performance cost but how much of a coast I
> don't know. some times these second way is a real time saver where there
> is much data to pass across. since in the second way you are only
> getting one row from the database each time the amount of data retrieved
> from the database is the same but there is many connections to the
> database the second way.
>
> Should I worry too much about this overhead?


The first way is OK.

The second way is bad. It will cost in performance.

The third (!) way i OK.

It is the same as the second but with an important twist. You
only load categories and hand it to the caller. When the caller
then tries to access the subcategory information it get loaded.
If subcategory information may not be needed or only be needed
for one or a few categories, then this lazy loading may even
perform better than the first way.

Arne
 
Reply With Quote
 
Mr. Arnold
Guest
Posts: n/a
 
      03-22-2009

"ThatsIT.net.au" <me@work> wrote in message
news:(E-Mail Removed)...
>I was wondering about performance implications for 2 ways of creating a
>collection of objects.
>
> Lets say I have 2 classes category and subcategory
>
> in the category class I have a function that returns a collection of
> subcategory objects.
>
> what I do here is open a reader to a table in the database with the
> subcategory info in it.
> I then call the subcategory class each loop of the reader creating an
> array of objects like so
>
> myarray(n) = new subcategory(id,name,other,other,other...)
>
> as you can see am passing all the data from the database table to the
> class so that no trip to the database is needed from the subcategory class
> all data is supplied.


That's the way I would do it.

>
> Now I know that this second way is not as efficient as it make many trips
> to the database like this
>
> myarray(n) = new subcategory(id)
>
> passing the id only I then have to load the other data from the database
> each time a subcategory object is created.
>
> Now I know that this is a performance cost but how much of a coast I don't
> know. some times these second way is a real time saver where there is much
> data to pass across. since in the second way you are only getting one row
> from the database each time the amount of data retrieved from the database
> is the same but there is many connections to the database the second way.
>
> Should I worry too much about this overhead?



I don't know why you wouldn't have a sproc with two T-SQL statements.

1) to select category
2) to select subcategory by category-id assuming that there is a parent
child relationship.

If you do that with two Selects pulling the data, then you can do a read on
category with a data reader building its array of objects. Then you do a
Result Set.Move Next and with a new data reader read the subcategories into
its array.


 
Reply With Quote
 
ThatsIT.net.au
Guest
Posts: n/a
 
      03-22-2009
thanks I know profiler, but a bit to lazy to use it when there is people
that have analyzed thing better then I would who I ca ask, but for now I
will just pass the id in this project because its never going to be a
scalability problem, but would like to know for further projects what is the
cost

"Luis Roquette Valdez" <(E-Mail Removed)> wrote
in message news:(E-Mail Removed)...
> Hi there,
>
> It's quite a picke you got there... lol...
>
> Well... I'd advise you to get your SQL profiler out of your pocket and
> test
> it by yourself...
> I would personally just pass the ID and let my method get all the data it
> needed for it. This way, next time you add another different column you
> want
> to get from your database, all you need to change is your method and not
> all
> calls to it... but that's about good/bad development and not SQL...
>
> Do have a look at Profiler... get the times from it and make your
> judgement
> based on that...
>
> Cheers
>
> "ThatsIT.net.au" wrote:
>
>> I was wondering about performance implications for 2 ways of creating a
>> collection of objects.
>>
>> Lets say I have 2 classes category and subcategory
>>
>> in the category class I have a function that returns a collection of
>> subcategory objects.
>>
>> what I do here is open a reader to a table in the database with the
>> subcategory info in it.
>> I then call the subcategory class each loop of the reader creating an
>> array
>> of objects like so
>>
>> myarray(n) = new subcategory(id,name,other,other,other...)
>>
>> as you can see am passing all the data from the database table to the
>> class
>> so that no trip to the database is needed from the subcategory class all
>> data is supplied.
>>
>> Now I know that this second way is not as efficient as it make many trips
>> to
>> the database like this
>>
>> myarray(n) = new subcategory(id)
>>
>> passing the id only I then have to load the other data from the database
>> each time a subcategory object is created.
>>
>> Now I know that this is a performance cost but how much of a coast I
>> don't
>> know. some times these second way is a real time saver where there is
>> much
>> data to pass across. since in the second way you are only getting one row
>> from the database each time the amount of data retrieved from the
>> database
>> is the same but there is many connections to the database the second way.
>>
>> Should I worry too much about this overhead?
>>
>>


 
Reply With Quote
 
ThatsIT.net.au
Guest
Posts: n/a
 
      03-22-2009

"Mr. Arnold" <MR. http://www.velocityreviews.com/forums/(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "ThatsIT.net.au" <me@work> wrote in message
> news:(E-Mail Removed)...
>>I was wondering about performance implications for 2 ways of creating a
>>collection of objects.
>>
>> Lets say I have 2 classes category and subcategory
>>
>> in the category class I have a function that returns a collection of
>> subcategory objects.
>>
>> what I do here is open a reader to a table in the database with the
>> subcategory info in it.
>> I then call the subcategory class each loop of the reader creating an
>> array of objects like so
>>
>> myarray(n) = new subcategory(id,name,other,other,other...)
>>
>> as you can see am passing all the data from the database table to the
>> class so that no trip to the database is needed from the subcategory
>> class all data is supplied.

>
> That's the way I would do it.
>
>>
>> Now I know that this second way is not as efficient as it make many trips
>> to the database like this
>>
>> myarray(n) = new subcategory(id)
>>
>> passing the id only I then have to load the other data from the database
>> each time a subcategory object is created.
>>
>> Now I know that this is a performance cost but how much of a coast I
>> don't know. some times these second way is a real time saver where there
>> is much data to pass across. since in the second way you are only getting
>> one row from the database each time the amount of data retrieved from the
>> database is the same but there is many connections to the database the
>> second way.
>>
>> Should I worry too much about this overhead?

>
>
> I don't know why you wouldn't have a sproc with two T-SQL statements.
>
> 1) to select category
> 2) to select subcategory by category-id assuming that there is a parent
> child relationship.
>
> If you do that with two Selects pulling the data, then you can do a read
> on category with a data reader building its array of objects. Then you do
> a Result Set.Move Next and with a new data reader read the subcategories
> into its array.
>


the maintenance, if a column is added, you need to alter code as well as
depending on the size of the table can be a lot of work when you have many
linked classes exposing its collections this way.

 
Reply With Quote
 
ThatsIT.net.au
Guest
Posts: n/a
 
      03-22-2009

"Arne Vajh°j" <(E-Mail Removed)> wrote in message
news:49c57ab1$0$90264$(E-Mail Removed)...
> ThatsIT.net.au wrote:
>> I was wondering about performance implications for 2 ways of creating a
>> collection of objects.
>>
>> Lets say I have 2 classes category and subcategory
>>
>> in the category class I have a function that returns a collection of
>> subcategory objects.
>>
>> what I do here is open a reader to a table in the database with the
>> subcategory info in it.
>> I then call the subcategory class each loop of the reader creating an
>> array of objects like so
>>
>> myarray(n) = new subcategory(id,name,other,other,other...)
>>
>> as you can see am passing all the data from the database table to the
>> class so that no trip to the database is needed from the subcategory
>> class all data is supplied.
>>
>> Now I know that this second way is not as efficient as it make many trips
>> to the database like this
>>
>> myarray(n) = new subcategory(id)
>>
>> passing the id only I then have to load the other data from the database
>> each time a subcategory object is created.
>>
>> Now I know that this is a performance cost but how much of a coast I
>> don't know. some times these second way is a real time saver where there
>> is much data to pass across. since in the second way you are only getting
>> one row from the database each time the amount of data retrieved from the
>> database is the same but there is many connections to the database the
>> second way.
>>
>> Should I worry too much about this overhead?

>
> The first way is OK.
>
> The second way is bad. It will cost in performance.
>
> The third (!) way i OK.
>
> It is the same as the second but with an important twist. You
> only load categories and hand it to the caller. When the caller
> then tries to access the subcategory information it get loaded.
> If subcategory information may not be needed or only be needed
> for one or a few categories, then this lazy loading may even
> perform better than the first way.
>
> Arne


I only put forward 2 ways, so I assume by 3 you mean 2
myarray(n) = new subcategory(id)

 
Reply With Quote
 
Arne Vajh°j
Guest
Posts: n/a
 
      03-22-2009
ThatsIT.net.au wrote:
>
> "Arne Vajh°j" <(E-Mail Removed)> wrote in message
> news:49c57ab1$0$90264$(E-Mail Removed)...
>> ThatsIT.net.au wrote:
>>> I was wondering about performance implications for 2 ways of creating
>>> a collection of objects.
>>>
>>> Lets say I have 2 classes category and subcategory
>>>
>>> in the category class I have a function that returns a collection of
>>> subcategory objects.
>>>
>>> what I do here is open a reader to a table in the database with the
>>> subcategory info in it.
>>> I then call the subcategory class each loop of the reader creating an
>>> array of objects like so
>>>
>>> myarray(n) = new subcategory(id,name,other,other,other...)
>>>
>>> as you can see am passing all the data from the database table to the
>>> class so that no trip to the database is needed from the subcategory
>>> class all data is supplied.
>>>
>>> Now I know that this second way is not as efficient as it make many
>>> trips to the database like this
>>>
>>> myarray(n) = new subcategory(id)
>>>
>>> passing the id only I then have to load the other data from the
>>> database each time a subcategory object is created.
>>>
>>> Now I know that this is a performance cost but how much of a coast I
>>> don't know. some times these second way is a real time saver where
>>> there is much data to pass across. since in the second way you are
>>> only getting one row from the database each time the amount of data
>>> retrieved from the database is the same but there is many connections
>>> to the database the second way.
>>>
>>> Should I worry too much about this overhead?

>>
>> The first way is OK.
>>
>> The second way is bad. It will cost in performance.
>>
>> The third (!) way i OK.
>>
>> It is the same as the second but with an important twist. You
>> only load categories and hand it to the caller. When the caller
>> then tries to access the subcategory information it get loaded.
>> If subcategory information may not be needed or only be needed
>> for one or a few categories, then this lazy loading may even
>> perform better than the first way.

>
> I only put forward 2 ways, so I assume by 3 you mean 2
> myarray(n) = new subcategory(id)


I explain above what the third method is.

Arne
 
Reply With Quote
 
ThatsIT.net.au
Guest
Posts: n/a
 
      03-22-2009

"Arne Vajh°j" <(E-Mail Removed)> wrote in message
news:49c59119$0$90265$(E-Mail Removed)...
> ThatsIT.net.au wrote:
>>
>> "Arne Vajh°j" <(E-Mail Removed)> wrote in message
>> news:49c57ab1$0$90264$(E-Mail Removed)...
>>> ThatsIT.net.au wrote:
>>>> I was wondering about performance implications for 2 ways of creating a
>>>> collection of objects.
>>>>
>>>> Lets say I have 2 classes category and subcategory
>>>>
>>>> in the category class I have a function that returns a collection of
>>>> subcategory objects.
>>>>
>>>> what I do here is open a reader to a table in the database with the
>>>> subcategory info in it.
>>>> I then call the subcategory class each loop of the reader creating an
>>>> array of objects like so
>>>>
>>>> myarray(n) = new subcategory(id,name,other,other,other...)
>>>>
>>>> as you can see am passing all the data from the database table to the
>>>> class so that no trip to the database is needed from the subcategory
>>>> class all data is supplied.
>>>>
>>>> Now I know that this second way is not as efficient as it make many
>>>> trips to the database like this
>>>>
>>>> myarray(n) = new subcategory(id)
>>>>
>>>> passing the id only I then have to load the other data from the
>>>> database each time a subcategory object is created.
>>>>
>>>> Now I know that this is a performance cost but how much of a coast I
>>>> don't know. some times these second way is a real time saver where
>>>> there is much data to pass across. since in the second way you are only
>>>> getting one row from the database each time the amount of data
>>>> retrieved from the database is the same but there is many connections
>>>> to the database the second way.
>>>>
>>>> Should I worry too much about this overhead?
>>>
>>> The first way is OK.
>>>
>>> The second way is bad. It will cost in performance.
>>>
>>> The third (!) way i OK.
>>>
>>> It is the same as the second but with an important twist. You
>>> only load categories and hand it to the caller. When the caller
>>> then tries to access the subcategory information it get loaded.
>>> If subcategory information may not be needed or only be needed
>>> for one or a few categories, then this lazy loading may even
>>> perform better than the first way.

>>
>> I only put forward 2 ways, so I assume by 3 you mean 2
>> myarray(n) = new subcategory(id)

>
> I explain above what the third method is.
>
> Arne


I think that pretty much is what I'm doing, only when you call the method
getsubcategories from the categories object are they loaded but all are
loaded at that time

 
Reply With Quote
 
Tom Dacon
Guest
Posts: n/a
 
      03-22-2009
You're right that making each object do a separate select for its data is a
loser from a performance standpoint. It's that kind of thing that gave
n-tier object-oriented programming a bad name for performance back in the
early days when people were working this problem out.

The way I do this is I have the collection execute a stored procedure that
returns a DataReader with one row for each of the objects that it is going
to contain. Then I write a constructor for the class that represents the
content objects, that takes a DataRow as its single argument. Each instance
of the contained class then populates itself from the contents of the
DataRow.

One select, and an iteration over the contents of the DataReader, and all
the contained objects are populated. Short and sweet. And the container
class doesn't have to know anything about how the objects populate
themselves (except for the stored procedure, of course).

Try it out.

The one small downside of this is that the contained object also has to have
a stored procedure that selects the same columns, for the case where it
needs to populate itself separately from the collection. So there's a small
amount of duplicated code in the two stored procedures, and if you change
the table or view you need to make corresponding changes in two places. But
it's never been a problem for me.

Tom Dacon
Dacon Software Consulting

"ThatsIT.net.au" <me@work> wrote in message
news:(E-Mail Removed)...
>I was wondering about performance implications for 2 ways of creating a
>collection of objects.
>
> Lets say I have 2 classes category and subcategory
>
> in the category class I have a function that returns a collection of
> subcategory objects.
>
> what I do here is open a reader to a table in the database with the
> subcategory info in it.
> I then call the subcategory class each loop of the reader creating an
> array of objects like so
>
> myarray(n) = new subcategory(id,name,other,other,other...)
>
> as you can see am passing all the data from the database table to the
> class so that no trip to the database is needed from the subcategory class
> all data is supplied.
>
> Now I know that this second way is not as efficient as it make many trips
> to the database like this
>
> myarray(n) = new subcategory(id)
>
> passing the id only I then have to load the other data from the database
> each time a subcategory object is created.
>
> Now I know that this is a performance cost but how much of a coast I don't
> know. some times these second way is a real time saver where there is much
> data to pass across. since in the second way you are only getting one row
> from the database each time the amount of data retrieved from the database
> is the same but there is many connections to the database the second way.
>
> Should I worry too much about this overhead?
>
>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Best Practice for Inserting Data into SQL in 2.0 hooterbite@yahoo.com ASP .Net 2 04-18-2006 12:52 PM
ADO.NET, SQL Server, and FOR XML: what's the best practice? Robert Rossney ASP .Net 0 02-07-2006 09:08 PM
Using a datagrid to update values in sql server (best practice) Mok ASP .Net 1 09-07-2005 06:57 AM
Remember when your piano teacher taught you, "Practice, practice,practice ...?" Wayne Wastier Windows 64bit 3 06-10-2005 08:29 PM
Best practice for identity when accessing SQL Server...L Brian Muth ASP .Net 1 12-17-2004 01:35 AM



Advertisments