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]

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]

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]

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