DB structure/speed

Hi,

I think you are on the right way.

Always normalize your database.
Never store duplicate data.
(Well, sometimes do it, but be carefull with it).

If you tell use you db definition (show us your create statements) we might be able to offer more support.

ciao
Anti

I see, so SQL server is fast in collecting many little pieces of data from huge tables?
Since I need to query the 3-tables-structure about 100 times per single user query, there will be about 300 SQL queries to resolve the strings.
How to make this fast, perhaps by combining sub-queries where applicable?
Can you give me some general hints to optimize speed and to lower SQL traffic?

Thanks,

Christian Becker

OK,
some general tips.

Let the Database-Software do the work.
That's what it was designed for.

Try to normalise your tables (at least BNF if not 6NF).

If you don't need intermediate results select the finals direcetly.
This can usually be achieved by (extensive) use of joins.

Use the joins that your dbsw handles best, usually left joins.

Don't select more columns than you need.

If I understood your problem right you should only need _ONE_ query and a few subsequent fetch-rows.

I'm still interested in your db contents.

I have no tables created yet, but everything will look as I explained (2 tables assigning strings, 1 table with data).
Thanks for your hints!

Christian Becker

The design depends on what you want to do with the table. If you're using it as a read-only repository of data, and will not be using it to write your transactions, then some data redundancy can be good. I'm not saying that you should plop everything into one big table (although this might be an option), but it depends on both the situation and how you will be using the data, especially in the future.

Here's an example. Let's say that you want to give your customers access to their order data. Let's say you have (in a separate database) a customer table, an order header table, an order line table, and a shipment table.

If this was the design you were forced to work with, you would have to get orders and sales separately if you wanted to provide a day-by-day list of tranactions. That's because you can't join orders to sales because there may be days with sales but no orders, or vice versa. Outer joins would be tricky. The SQL would be somewhat slow.

If, however, you exported your data in the format that you want to display it in, you could have a customer table and a orders/sales table. That orders/sales table could contain the order total on each line -- redundant, but it would allow you have one less join in your query and would also allow you to show a "percent of total" pretty easily.

That's why I say "it depends". You should try a few different designs to see what works best.

I also agree with Anti -- you should let the database do the work. Don't think this through like it's procedural code. Do one SELECT and return all the rows you need. Then fetch from your result set. If you send 300 queries per customer, that's 300 queries that have to be parsed and executed, and that equals more overhead.

Ralph Slate

------------------
http://www.hockeydb.com