Database access and speed

[JJ]

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]

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" <ab*@xyz.comwro te in message
news:u3******** ******@TK2MSFTN GP04.phx.gbl...

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]

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" <ma***********@ RemoveThis.optu snet.com.auwrot e in message
news:%2******** *******@TK2MSFT NGP06.phx.gbl.. .

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" <ab*@xyz.comwro te in message
news:u3******** ******@TK2MSFTN GP04.phx.gbl...

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

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" <ab*@xyz.comwro te in message
news:eM******** ******@TK2MSFTN GP06.phx.gbl...

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" <ma***********@ RemoveThis.optu snet.com.auwrot e in message
news:%2******** *******@TK2MSFT NGP06.phx.gbl.. .

>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" <ab*@xyz.comwro te in message
news:u3******* *******@TK2MSFT NGP04.phx.gbl.. .

>>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
organisatio n 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
organisatio n there? i.e. one big fetch instead of lots of little ones??

JJ

[JJ]

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" <ma***********@ RemoveThis.optu snet.com.auwrot e in message
news:%2******** *******@TK2MSFT NGP06.phx.gbl.. .

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" <ab*@xyz.comwro te in message
news:eM******** ******@TK2MSFTN GP06.phx.gbl...

>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" <ma***********@ RemoveThis.optu snet.com.auwrot e in message
news:%2******* ********@TK2MSF TNGP06.phx.gbl. ..

>>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" <ab*@xyz.comwro te in message
news:u3****** ********@TK2MSF TNGP04.phx.gbl. ..
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
organisati on 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

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