klintan
October 11th, 2005, 04:10 AM
Say you have a database like the Microsoft Northwind sample database: with customers, orders, order details, products, suppliers, and categories (these are related: customer-orders 1:N, orders - order details 1:N, order details - products N:1, products - suppliers 1:N, and products - categories 1:N).
In your application you want to get everything from customers down to products, suppliers and categories, for either all customers in a country, or all customers with a given company name.
(My problem is of course with another database but I use the Northwind DB as an example.)
Two qualities are important: performance (the queries should be as fast as possible) and maintainability (if another column is added to any of the tables few query changes should be required).
Two design restrictions are important: the solution should work both for MS Access and MS SQL Server (meaning that the queries/stored procedures have to be very simple), and queries/stored procedures in the database are prefered over having them in code (code is written in c#), since I think this gives better performance.
In my current solution I have made one query per table, making joins so that I directly get only the required data, e.g.:
SELECT companyname ... FROM customers WHERE country=@country
SELECT orderid ... FROM orders
INNER JOIN customers ON orders.customerid=customers.customerid
WHERE country=@country
...
SELECT DISTINCT productname ... FROM products
INNER JOIN [order details] ON [order details].productid=products.productid
INNER JOIN orders ON orders.orderid=[order details].orderid
INNER JOIN customers ON orders.customerid=customers.customerid
WHERE country=@country
...
This gives me good performance, but I don't know if it is the best though. Is there a better way of doing it? (Maybe joining even more, so that I can get the data with fewer queries, maybe views will help me?)
My main problem though is that if I want to make the same queries also for company name I end up duplicating all queries. How can I avoid this? (Move the queries away from the db and to code would make it easier to dynamically just change the WHERE condition)
Suggestions, hints anyone?
In your application you want to get everything from customers down to products, suppliers and categories, for either all customers in a country, or all customers with a given company name.
(My problem is of course with another database but I use the Northwind DB as an example.)
Two qualities are important: performance (the queries should be as fast as possible) and maintainability (if another column is added to any of the tables few query changes should be required).
Two design restrictions are important: the solution should work both for MS Access and MS SQL Server (meaning that the queries/stored procedures have to be very simple), and queries/stored procedures in the database are prefered over having them in code (code is written in c#), since I think this gives better performance.
In my current solution I have made one query per table, making joins so that I directly get only the required data, e.g.:
SELECT companyname ... FROM customers WHERE country=@country
SELECT orderid ... FROM orders
INNER JOIN customers ON orders.customerid=customers.customerid
WHERE country=@country
...
SELECT DISTINCT productname ... FROM products
INNER JOIN [order details] ON [order details].productid=products.productid
INNER JOIN orders ON orders.orderid=[order details].orderid
INNER JOIN customers ON orders.customerid=customers.customerid
WHERE country=@country
...
This gives me good performance, but I don't know if it is the best though. Is there a better way of doing it? (Maybe joining even more, so that I can get the data with fewer queries, maybe views will help me?)
My main problem though is that if I want to make the same queries also for company name I end up duplicating all queries. How can I avoid this? (Move the queries away from the db and to code would make it easier to dynamically just change the WHERE condition)
Suggestions, hints anyone?