SQL Optimization Strategy

I'm a student and I'm doing my database assignment. I want to use indexing and query optimization for my database optimization strategy. The problem is how can I prove my strategy make a improvement? my lecture said about query optimization that I can prove by calculation, anyone got more ideas? what to calculate? what 开发者_StackOverflow社区about indexing, I need evidence to prove it. how??

In terms of evidence of optimization, you have to have instrumented code for your test cases (e.g. you can take timings accurately) and re-runnable test cases. The ideal situation for a re-runable set of test cases is to also be able to reset to a baseline database so you can guarentee the starting conditions of the data is the same per test run.

You also need to understand for each test case other more subtle factors:

• Are you running against a cold procedure cache / warm procedure cache.
• Are you running against a cold data cache / warm data cache.
• For larger datasets, are you using the exact same table, e.g. no page splits have occured since.

I would think a before and after explain plan would go a long way towards proving an improvement.

See SQL Server Performance HERE.

Which DBMS are you using?

I suggest you take a look at what tracing options your DBMS product provides. For example, in Oracle you can use SQL Trace and parse the output using tkprof to provide you with the figures you'll need to prove that your database optimization strategy shows an improvement.