Best practice ASP SQL

Discussion in 'ASP .Net Web Controls' started by ThatsIT.net.au, Mar 21, 2009.

  1. 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?
    ThatsIT.net.au, Mar 21, 2009
    #1
    1. Advertising

  2. 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?
    >
    >
    Luis Roquette Valdez, Mar 21, 2009
    #2
    1. Advertising

  3. ThatsIT.net.au

    Arne Vajhøj Guest

    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
    Arne Vajhøj, Mar 21, 2009
    #3
  4. ThatsIT.net.au

    Mr. Arnold Guest

    "ThatsIT.net.au" <me@work> wrote in message
    news:...
    >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.
    Mr. Arnold, Mar 22, 2009
    #4
  5. 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" <> wrote
    in message news:...
    > 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?
    >>
    >>
    ThatsIT.net.au, Mar 22, 2009
    #5
  6. "Mr. Arnold" <MR. > wrote in message
    news:...
    >
    > "ThatsIT.net.au" <me@work> wrote in message
    > news:...
    >>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.
    ThatsIT.net.au, Mar 22, 2009
    #6
  7. "Arne Vajhøj" <> wrote in message
    news:49c57ab1$0$90264$...
    > 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)
    ThatsIT.net.au, Mar 22, 2009
    #7
  8. ThatsIT.net.au

    Arne Vajhøj Guest

    ThatsIT.net.au wrote:
    >
    > "Arne Vajhøj" <> wrote in message
    > news:49c57ab1$0$90264$...
    >> 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
    Arne Vajhøj, Mar 22, 2009
    #8
  9. "Arne Vajhøj" <> wrote in message
    news:49c59119$0$90265$...
    > ThatsIT.net.au wrote:
    >>
    >> "Arne Vajhøj" <> wrote in message
    >> news:49c57ab1$0$90264$...
    >>> 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
    ThatsIT.net.au, Mar 22, 2009
    #9
  10. ThatsIT.net.au

    Tom Dacon Guest

    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:...
    >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?
    >
    >
    Tom Dacon, Mar 22, 2009
    #10
  11. ThatsIT.net.au

    Mr. Arnold Guest

    "ThatsIT.net.au" <me@work> wrote in message
    news:...
    >
    > "Mr. Arnold" <MR. > wrote in message
    > news:...
    >>
    >> "ThatsIT.net.au" <me@work> wrote in message
    >> news:...
    >>>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.


    This is not correct. They are not linked objects. They are two individual
    selects and reads of the data that happen in sequence, and the objects are
    independent of each other. It just happens that you read the data for both
    tables with one call to the database.
    Mr. Arnold, Mar 22, 2009
    #11
  12. ThatsIT.net.au

    Mr. Arnold Guest

    "Tom Dacon" <> wrote in message
    news:...
    > 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


    Yes, use a sproc that has two selects in it no inners joins. You have to
    different results sets returned. You use datareader to built the first
    collections of objects. You do a Resultset.MoveNext and with another
    datareader, one builds the second set of objects.

    Yes, short a sweet and you only need to access the data once.
    Mr. Arnold, Mar 22, 2009
    #12
  13. In my idea it is all wrong practice. It depends very much if you will update
    that data or not.

    However still is taking the less data as needed is the best way. Selecting
    column by column is real a bad practice.

    Cor

    "ThatsIT.net.au" <me@work> wrote in message
    news:...
    >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?
    >
    >
    Cor Ligthert[MVP], Mar 22, 2009
    #13
  14. If you are really so worried about perfomance then it would also be a good
    idea to implement caching
    you should really perform a load test with the expected amount of traffic on
    your site .

    My personal opinion is load what you need when you need it, and about all
    those connections don`t worry they invented connection pooling for that
    some smart guys at MS have already taken care of that :) .

    HTH

    Michel Posseth





    "ThatsIT.net.au" <me@work> schreef in bericht
    news:...
    >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?
    >
    >
    Michel Posseth [MCP], Mar 22, 2009
    #14
  15. "Michel Posseth [MCP]" <> wrote in message
    news:e$...
    >
    > If you are really so worried about perfomance


    Well i'm not overerly concerned, but i just wanted to know how bad it it was

    then it would also be a good
    > idea to implement caching
    > you should really perform a load test with the expected amount of traffic
    > on your site .


    I will use caching but not in the classes themselves

    >
    > My personal opinion is load what you need when you need it, and about all
    > those connections don`t worry they invented connection pooling for that
    > some smart guys at MS have already taken care of that :) .
    >



    Now that what I wanted to here, its not the end of the world?
    This is the way I would like to do it as it makes coding more simple and
    more elegant


    > HTH
    >
    > Michel Posseth
    >
    >
    >
    >
    >
    > "ThatsIT.net.au" <me@work> schreef in bericht
    > news:...
    >>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?
    >>
    >>

    >
    >
    ThatsIT.net.au, Mar 22, 2009
    #15

  16. > Now that what I wanted to here, its not the end of the world?
    > This is the way I would like to do it as it makes coding more simple and
    > more elegant


    It is always good coding practice to try to keep things as simple as
    possible , also for future maintainebility this pays back

    With web projects for a fact you are in a luxury position , with a desktop
    app the user is annoyed after just a few seconds of no response while with a
    web app the user becomes annoyed after 8 seconds of no interaction .

    It is hard to tell wich of the 2 solutions would give the highest perfomance
    in a ASP.Net scenario as solution 2 would consume more memory and thus a
    recycle of the ASP.NET worker process is more frequent to occure , so what
    started out as a possible perfomance boost might in the end degrade the
    perfomance of the app

    So my opinion would be try to keep it as OOP as possible and keep the
    thought in mind that the web should be stateless so this means in short a
    challenge response scenario , MS SQL server is perfect in this scenario as a
    db backend due to its superior connection pooling mechanism .

    HTH

    Michel Posseth






    "ThatsIT.net.au" <me@work> schreef in bericht
    news:...
    >
    > "Michel Posseth [MCP]" <> wrote in message
    > news:e$...
    >>
    >> If you are really so worried about perfomance

    >
    > Well i'm not overerly concerned, but i just wanted to know how bad it it
    > was
    >
    > then it would also be a good
    >> idea to implement caching
    >> you should really perform a load test with the expected amount of traffic
    >> on your site .

    >
    > I will use caching but not in the classes themselves
    >
    >>
    >> My personal opinion is load what you need when you need it, and about
    >> all those connections don`t worry they invented connection pooling for
    >> that
    >> some smart guys at MS have already taken care of that :) .
    >>

    >
    >
    > Now that what I wanted to here, its not the end of the world?
    > This is the way I would like to do it as it makes coding more simple and
    > more elegant
    >
    >
    >> HTH
    >>
    >> Michel Posseth
    >>
    >>
    >>
    >>
    >>
    >> "ThatsIT.net.au" <me@work> schreef in bericht
    >> news:...
    >>>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?
    >>>
    >>>

    >>
    >>

    >
    Michel Posseth [MCP], Mar 22, 2009
    #16
  17. ThatsIT.net.au

    Arne Vajhøj Guest

    ThatsIT.net.au wrote:
    > "Arne Vajhøj" <> wrote in message
    > news:49c59119$0$90265$...
    >> ThatsIT.net.au wrote:
    >>> "Arne Vajhøj" <> wrote in message
    >>> news:49c57ab1$0$90264$...
    >>>> 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.

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


    No.

    The key difference is between getting all subcategories now and only
    getting those needed when they are needed.

    Arne
    Arne Vajhøj, Mar 22, 2009
    #17
  18. "Michel Posseth [MCP]" <> wrote in message
    news:...
    >
    >> Now that what I wanted to here, its not the end of the world?
    >> This is the way I would like to do it as it makes coding more simple and
    >> more elegant

    >
    > It is always good coding practice to try to keep things as simple as
    > possible , also for future maintainebility this pays back
    >
    > With web projects for a fact you are in a luxury position , with a desktop
    > app the user is annoyed after just a few seconds of no response while with
    > a web app the user becomes annoyed after 8 seconds of no interaction .
    >
    > It is hard to tell wich of the 2 solutions would give the highest
    > perfomance in a ASP.Net scenario as solution 2 would consume more memory
    > and thus a recycle of the ASP.NET worker process is more frequent to
    > occure , so what started out as a possible perfomance boost might in the
    > end degrade the perfomance of the app
    >
    > So my opinion would be try to keep it as OOP as possible and keep the
    > thought in mind that the web should be stateless so this means in short a
    > challenge response scenario , MS SQL server is perfect in this scenario as
    > a db backend due to its superior connection pooling mechanism .
    >
    > HTH
    >
    > Michel Posseth
    >
    >
    >



    thanks for your input


    >
    >
    >
    > "ThatsIT.net.au" <me@work> schreef in bericht
    > news:...
    >>
    >> "Michel Posseth [MCP]" <> wrote in message
    >> news:e$...
    >>>
    >>> If you are really so worried about perfomance

    >>
    >> Well i'm not overerly concerned, but i just wanted to know how bad it it
    >> was
    >>
    >> then it would also be a good
    >>> idea to implement caching
    >>> you should really perform a load test with the expected amount of
    >>> traffic on your site .

    >>
    >> I will use caching but not in the classes themselves
    >>
    >>>
    >>> My personal opinion is load what you need when you need it, and about
    >>> all those connections don`t worry they invented connection pooling for
    >>> that
    >>> some smart guys at MS have already taken care of that :) .
    >>>

    >>
    >>
    >> Now that what I wanted to here, its not the end of the world?
    >> This is the way I would like to do it as it makes coding more simple and
    >> more elegant
    >>
    >>
    >>> HTH
    >>>
    >>> Michel Posseth
    >>>
    >>>
    >>>
    >>>
    >>>
    >>> "ThatsIT.net.au" <me@work> schreef in bericht
    >>> news:...
    >>>>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?
    >>>>
    >>>>
    >>>
    >>>

    >>

    >
    >
    ThatsIT.net.au, Mar 22, 2009
    #18
  19. ThatsIT.net.au

    tomilay Guest

    On Mar 21, 6:23 pm, "ThatsIT.net.au" <me@work> 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 tripsto
    > 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?


    There is a performance hit each trip to the database. This may not be
    a big issue depending on circumstances. However you could use XML to
    get the categories and sub-categories from the database in one trip.
    You can then serialize this XML into your collection using the
    XMLSerializer class.
    tomilay, Mar 24, 2009
    #19
  20. ThatsIT.net.au

    sloan Guest

    If you look at my examples (at the URL below)

    I use the IDataReader and the .NextResult to hit the database one time.

    However, I use the (results) to populate objects and child collection
    objects.

    Its based on Northwind

    Customer --> Orders --> Order_Details

    http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry

    I use Controller(or Manager) classes to control the "correct level of
    deepness" ......

    Sometimes I want a list of customers. Sometimes I want a customer with
    their orders.
    Sometimes I want a customer and their orders and their order details.

    That's what the Controller class takes care of. Not too much, not too
    little, "just right".

    Code is downloadable and if you check the blog, there is a 2.0 version as
    well.




    "ThatsIT.net.au" <me@work> wrote in message
    news:...
    >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?
    >
    >
    sloan, Mar 24, 2009
    #20
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robert Rossney
    Replies:
    0
    Views:
    547
    Robert Rossney
    Feb 7, 2006
  2. Brian Muth
    Replies:
    1
    Views:
    538
    bruce barker
    Dec 17, 2004
  3. Mok
    Replies:
    1
    Views:
    3,172
    Gerhard Pretorius
    Sep 7, 2005
  4. Replies:
    2
    Views:
    528
  5. oldyork90
    Replies:
    1
    Views:
    155
    Jeremy J Starcher
    Sep 10, 2008
Loading...

Share This Page