to set the scene, i have 2 databases - they both contain the same data, i replicate from the bad one, to the 'good' one via some .net code.
the problem with the bad database is that way too much information is packed into the sales Table, for example multiple columns representing information abuot the product sold, rather than an ID linking to the Products table. The 'good' database attempts to remove all of these inefficiencies and duplications.
The result is a 60gb database (bad) and a new 5gb database.
Of course, to query useful information (ie the NAME of a product and all sales associated) i must now use SQL joins. if i wanted to know all sales between a certain date by Supplier, i would need many joins, ie: Sales -> ProductBarcodes -> Products -> ProductLines -> Suppliers
If i perform a query of this nature on both databases, i find that no matter what date range, or how many joins, it always takes ~16-30 seconds to return results from the new database.
In contrast, the processing time for the same data from the old database varies depending on the range of the parameters - for example the dates i wish results for. If i query the database for all sales from yesterday, it returns results nearly intstantly, but if i query for the last months sales, it starts to take longer, if i query for sales from this month in 2005, it would take even longer.
The fact that it can produce some date based results quite quickly suggests that SQL has some sort of date based indexing on the table? Or is this simply a byproduct of SQL's automatic caching?
On to the important question: Is there any way (settings, etc) to optimize the performance of the new database design. Is SQL still able to cache results based on complex joins? For example i have a project currently that queries the database using the same joins, but for varying types of data - ie a query to return the total sales per store per day for the last 3 months, then a query to return the total sales per store for the last 3 months. I would expect that if SQL was caching well, the second query would run much faster - but they always take the same length of time (`16-30 seconds depending on other loads on the serveR).
If this is the case, would creating a View based on my complex join, then performing simple queries on that View cause consecutive queries to be run much faster? I have read some comments that Views cannot be cached - and thus would have no impact.
Any advice is very welcome!