We help IT Professionals succeed at work.
Get Started

How do i improve MS MSQL performance for my database

285 Views
Last Modified: 2012-06-21
Greetings,

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!

Charles
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE