Database access and speed

Discussion in 'ASP .Net' started by JJ, Dec 20, 2006.

  1. JJ

    JJ Guest

    My question is basically about whether its best to do a small number
    database accesses and do most of the data organisation in sql stored
    procedures, or one larger fetch from the database and do the database
    organisation in the asp.net code:

    I am building a menu system for an online store. At the top will be the main
    'departments' and on the side will be the sub-departments of whichever main
    department is selected, with its sub-sub departments underneath each one.
    There will also be a breadcrumb at the top showing the route through the
    departments. It lookds something like:


    Store/Computing/Computers/Handhelds

    Clothes >Computing Home Music
    Computers
    - Desktops
    - Laptops
    - >Handhelds

    Accessories
    - Media
    - Cases

    Drives
    - CD
    - DVD
    - Hard Disk

    The possible issue I may have at the moment is the number of database
    accesses per click on an menu item. I though I was being efficient by doing
    much of the work of organising the data by using SQL 2005 stored procedures,
    but I am wondering whether there's a better way.

    Currently, there are 3-4 database accesses to display the above page (1 for
    breadcumb, 1 or 2 for sub menu and one for main menu). Ofcourse there will
    be another database fetch to display the details of the department selected
    in the centre of the page.

    Although I am caching as I go along I wonder if it would it be better to get
    all the departments (and their decendants) into a dataset and do the
    organisation there? i.e. one big fetch instead of lots of little ones??

    JJ
    JJ, Dec 20, 2006
    #1
    1. Advertising

  2. JJ

    Rotsey Guest

    JJ,

    The amount of data you will have is minimal on the client.

    Why don't you load all you data to a XML file and then
    traverse the XML using a script language.

    Funnily enough I am doing the same thing at the moment
    to produce a drill down report.

    Just an idea.

    rotsey


    "JJ" <> wrote in message
    news:...
    > My question is basically about whether its best to do a small number
    > database accesses and do most of the data organisation in sql stored
    > procedures, or one larger fetch from the database and do the database
    > organisation in the asp.net code:
    >
    > I am building a menu system for an online store. At the top will be the
    > main 'departments' and on the side will be the sub-departments of
    > whichever main department is selected, with its sub-sub departments
    > underneath each one. There will also be a breadcrumb at the top showing
    > the route through the departments. It lookds something like:
    >
    >
    > Store/Computing/Computers/Handhelds
    >
    > Clothes >Computing Home Music
    > Computers
    > - Desktops
    > - Laptops
    > - >Handhelds
    >
    > Accessories
    > - Media
    > - Cases
    >
    > Drives
    > - CD
    > - DVD
    > - Hard Disk
    >
    > The possible issue I may have at the moment is the number of database
    > accesses per click on an menu item. I though I was being efficient by
    > doing much of the work of organising the data by using SQL 2005 stored
    > procedures, but I am wondering whether there's a better way.
    >
    > Currently, there are 3-4 database accesses to display the above page (1
    > for breadcumb, 1 or 2 for sub menu and one for main menu). Ofcourse there
    > will be another database fetch to display the details of the department
    > selected in the centre of the page.
    >
    > Although I am caching as I go along I wonder if it would it be better to
    > get all the departments (and their decendants) into a dataset and do the
    > organisation there? i.e. one big fetch instead of lots of little ones??
    >
    > JJ
    >
    Rotsey, Dec 20, 2006
    #2
    1. Advertising

  3. JJ

    JJ Guest

    Well, I did think of that at first, but all the solutions that I came upon
    dealt with using sql stored procedures. It is working at the moment using
    recursive SQL statements. But the organisation of such a nested menu system
    does seem to point to an XML structure.

    I think I did attempt it, but as I am using self referencing tables (ie. one
    may be the parent or child of another) I think I got a little confused....

    How would you hold the XML file in memory - could you cache it? or would you
    actually write to a physical file?

    Thanks,

    JJ


    "Rotsey" <> wrote in message
    news:%...
    > JJ,
    >
    > The amount of data you will have is minimal on the client.
    >
    > Why don't you load all you data to a XML file and then
    > traverse the XML using a script language.
    >
    > Funnily enough I am doing the same thing at the moment
    > to produce a drill down report.
    >
    > Just an idea.
    >
    > rotsey
    >
    >
    > "JJ" <> wrote in message
    > news:...
    >> My question is basically about whether its best to do a small number
    >> database accesses and do most of the data organisation in sql stored
    >> procedures, or one larger fetch from the database and do the database
    >> organisation in the asp.net code:
    >>
    >> I am building a menu system for an online store. At the top will be the
    >> main 'departments' and on the side will be the sub-departments of
    >> whichever main department is selected, with its sub-sub departments
    >> underneath each one. There will also be a breadcrumb at the top showing
    >> the route through the departments. It lookds something like:
    >>
    >>
    >> Store/Computing/Computers/Handhelds
    >>
    >> Clothes >Computing Home Music
    >> Computers
    >> - Desktops
    >> - Laptops
    >> - >Handhelds
    >>
    >> Accessories
    >> - Media
    >> - Cases
    >>
    >> Drives
    >> - CD
    >> - DVD
    >> - Hard Disk
    >>
    >> The possible issue I may have at the moment is the number of database
    >> accesses per click on an menu item. I though I was being efficient by
    >> doing much of the work of organising the data by using SQL 2005 stored
    >> procedures, but I am wondering whether there's a better way.
    >>
    >> Currently, there are 3-4 database accesses to display the above page (1
    >> for breadcumb, 1 or 2 for sub menu and one for main menu). Ofcourse there
    >> will be another database fetch to display the details of the department
    >> selected in the centre of the page.
    >>
    >> Although I am caching as I go along I wonder if it would it be better to
    >> get all the departments (and their decendants) into a dataset and do the
    >> organisation there? i.e. one big fetch instead of lots of little ones??
    >>
    >> JJ
    >>

    >
    >
    JJ, Dec 20, 2006
    #3
  4. JJ

    Rotsey Guest

    JJ,

    You could seriailse your data to memory easily using a memorystream
    then to a string and and cache it quite easily in a cache object

    The key to what I did was I created class objects in the hierarchy
    I wanted the data then used xmlserialiser. That way when
    I traversed the XML it would be relatively fast.

    Your data model may be a bit more tricky from what you say.
    But there is no reason why you could do the same thing but
    duplicate any data that your relationships required to keep the
    correct hierarchy.
    Do you see what I mean?

    rotsey



    "JJ" <> wrote in message
    news:...
    > Well, I did think of that at first, but all the solutions that I came upon
    > dealt with using sql stored procedures. It is working at the moment using
    > recursive SQL statements. But the organisation of such a nested menu
    > system does seem to point to an XML structure.
    >
    > I think I did attempt it, but as I am using self referencing tables (ie.
    > one may be the parent or child of another) I think I got a little
    > confused....
    >
    > How would you hold the XML file in memory - could you cache it? or would
    > you actually write to a physical file?
    >
    > Thanks,
    >
    > JJ
    >
    >
    > "Rotsey" <> wrote in message
    > news:%...
    >> JJ,
    >>
    >> The amount of data you will have is minimal on the client.
    >>
    >> Why don't you load all you data to a XML file and then
    >> traverse the XML using a script language.
    >>
    >> Funnily enough I am doing the same thing at the moment
    >> to produce a drill down report.
    >>
    >> Just an idea.
    >>
    >> rotsey
    >>
    >>
    >> "JJ" <> wrote in message
    >> news:...
    >>> My question is basically about whether its best to do a small number
    >>> database accesses and do most of the data organisation in sql stored
    >>> procedures, or one larger fetch from the database and do the database
    >>> organisation in the asp.net code:
    >>>
    >>> I am building a menu system for an online store. At the top will be the
    >>> main 'departments' and on the side will be the sub-departments of
    >>> whichever main department is selected, with its sub-sub departments
    >>> underneath each one. There will also be a breadcrumb at the top showing
    >>> the route through the departments. It lookds something like:
    >>>
    >>>
    >>> Store/Computing/Computers/Handhelds
    >>>
    >>> Clothes >Computing Home Music
    >>> Computers
    >>> - Desktops
    >>> - Laptops
    >>> - >Handhelds
    >>>
    >>> Accessories
    >>> - Media
    >>> - Cases
    >>>
    >>> Drives
    >>> - CD
    >>> - DVD
    >>> - Hard Disk
    >>>
    >>> The possible issue I may have at the moment is the number of database
    >>> accesses per click on an menu item. I though I was being efficient by
    >>> doing much of the work of organising the data by using SQL 2005 stored
    >>> procedures, but I am wondering whether there's a better way.
    >>>
    >>> Currently, there are 3-4 database accesses to display the above page (1
    >>> for breadcumb, 1 or 2 for sub menu and one for main menu). Ofcourse
    >>> there will be another database fetch to display the details of the
    >>> department selected in the centre of the page.
    >>>
    >>> Although I am caching as I go along I wonder if it would it be better to
    >>> get all the departments (and their decendants) into a dataset and do the
    >>> organisation there? i.e. one big fetch instead of lots of little ones??
    >>>
    >>> JJ
    >>>

    >>
    >>

    >
    >
    Rotsey, Dec 20, 2006
    #4
  5. JJ

    JJ Guest

    I think I understand. I have got so far with it before and cached the stream
    from the database, but the realtionships are a little tricky, and displaying
    the data in nested repeaters was tricky too.

    I found lots of examples for two seperate tables and how to relate them in
    your dataset, turn them into XML and then use them in repeaters/menu's etc,
    but none for self referencing tables.

    Think I'll give it a go after Christmas.

    Thank you very much for your suggestion,
    JJ

    "Rotsey" <> wrote in message
    news:%...
    > JJ,
    >
    > You could seriailse your data to memory easily using a memorystream
    > then to a string and and cache it quite easily in a cache object
    >
    > The key to what I did was I created class objects in the hierarchy
    > I wanted the data then used xmlserialiser. That way when
    > I traversed the XML it would be relatively fast.
    >
    > Your data model may be a bit more tricky from what you say.
    > But there is no reason why you could do the same thing but
    > duplicate any data that your relationships required to keep the
    > correct hierarchy.
    > Do you see what I mean?
    >
    > rotsey
    >
    >
    >
    > "JJ" <> wrote in message
    > news:...
    >> Well, I did think of that at first, but all the solutions that I came
    >> upon dealt with using sql stored procedures. It is working at the moment
    >> using recursive SQL statements. But the organisation of such a nested
    >> menu system does seem to point to an XML structure.
    >>
    >> I think I did attempt it, but as I am using self referencing tables (ie.
    >> one may be the parent or child of another) I think I got a little
    >> confused....
    >>
    >> How would you hold the XML file in memory - could you cache it? or would
    >> you actually write to a physical file?
    >>
    >> Thanks,
    >>
    >> JJ
    >>
    >>
    >> "Rotsey" <> wrote in message
    >> news:%...
    >>> JJ,
    >>>
    >>> The amount of data you will have is minimal on the client.
    >>>
    >>> Why don't you load all you data to a XML file and then
    >>> traverse the XML using a script language.
    >>>
    >>> Funnily enough I am doing the same thing at the moment
    >>> to produce a drill down report.
    >>>
    >>> Just an idea.
    >>>
    >>> rotsey
    >>>
    >>>
    >>> "JJ" <> wrote in message
    >>> news:...
    >>>> My question is basically about whether its best to do a small number
    >>>> database accesses and do most of the data organisation in sql stored
    >>>> procedures, or one larger fetch from the database and do the database
    >>>> organisation in the asp.net code:
    >>>>
    >>>> I am building a menu system for an online store. At the top will be the
    >>>> main 'departments' and on the side will be the sub-departments of
    >>>> whichever main department is selected, with its sub-sub departments
    >>>> underneath each one. There will also be a breadcrumb at the top showing
    >>>> the route through the departments. It lookds something like:
    >>>>
    >>>>
    >>>> Store/Computing/Computers/Handhelds
    >>>>
    >>>> Clothes >Computing Home Music
    >>>> Computers
    >>>> - Desktops
    >>>> - Laptops
    >>>> - >Handhelds
    >>>>
    >>>> Accessories
    >>>> - Media
    >>>> - Cases
    >>>>
    >>>> Drives
    >>>> - CD
    >>>> - DVD
    >>>> - Hard Disk
    >>>>
    >>>> The possible issue I may have at the moment is the number of database
    >>>> accesses per click on an menu item. I though I was being efficient by
    >>>> doing much of the work of organising the data by using SQL 2005 stored
    >>>> procedures, but I am wondering whether there's a better way.
    >>>>
    >>>> Currently, there are 3-4 database accesses to display the above page (1
    >>>> for breadcumb, 1 or 2 for sub menu and one for main menu). Ofcourse
    >>>> there will be another database fetch to display the details of the
    >>>> department selected in the centre of the page.
    >>>>
    >>>> Although I am caching as I go along I wonder if it would it be better
    >>>> to get all the departments (and their decendants) into a dataset and do
    >>>> the organisation there? i.e. one big fetch instead of lots of little
    >>>> ones??
    >>>>
    >>>> JJ
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
    JJ, Dec 20, 2006
    #5
    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. Ham

    I need speed Mr .Net....speed

    Ham, Oct 28, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    2,320
    Antony Baula
    Oct 29, 2004
  2. efiedler
    Replies:
    1
    Views:
    2,019
    Tim Ward
    Oct 9, 2003
  3. Replies:
    2
    Views:
    2,272
    Howard
    Apr 28, 2004
  4. Weng Lei-QCH1840
    Replies:
    1
    Views:
    178
    Thomas
    Aug 15, 2003
  5. Replies:
    3
    Views:
    100
    -berlin.de
    Jan 24, 2007
Loading...

Share This Page