Very Computer

How should I test to see which kind of queries give me the best performance?
I want to know exactly whether joins, subqueries, derived tables, EXISTS,
etc. results in the best performance of my application.  Does SQL Profiler
do this?  Should I use the difference between time values to gauge the
length of time each query takes?  How is this sort of thing usually done?
Please give examples if possible.  I don't know how SQL Profiler works.
I've tried performance tuning of any kind.  Any help would be greatly
appreciated!

Thank you very much!

Run the Graphical Query Execution Plan and look at IO stats and subtree
cost.

Subtree is generally the #1 stat I choose to focus on. If Subtree goes down,
performance goes up.

you can see this by holding your mouse pointer over the white paper icons in
the Query Plan.

hope this helps,

Cheers,

Greg Jackson
PDX, Oregon

Quote:>> How should I test to see which kind of queries give me the best

performance? I want to know exactly whether joins, subqueries, derived
tables, EXISTS, etc. results in the best performance of my application.
<<

There is no magic, simple answer.

1) How a query performs from one release to the next of the same product
will vary.  

2) There many differences in various SQL engines, since the real world
is made up of heterogenous environments.

3) The statistical distribution in the data can completely change the
execution plan.

4) The size of the tables, the result set, the cache, and other physical
factors can change performance.

5) The number of other users can change performance.

Quote:>> How is this sort of thing usually done? <<

By starting with a sound data model, then implementing a schema in 5NF
that is appropriate for that model.  This makes it easy to write queries
in the simplest possible manner.  At that point, you trust the optimizer
to do a better job than you would.  

Bottlenecks are then handled as exceptions, one at a time.  

Quote:>> Please give examples if possible. <<

Darn! I left my CD with all possible queries that have been and ever
shall be asked against your unknown schema in all future releases in my
other suit :)

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Look up the command "set statistics IO on". The logical IO is the number of
times SQL had to hit a data page (not number of pages) That combined with
execution time are good indicators. Using that command you can play with
syntax or indexes to see which is best.

--
Ray Higdon MCSE, MCDBA, CCNA
---

Quote:> How should I test to see which kind of queries give me the best
performance?
> I want to know exactly whether joins, subqueries, derived tables, EXISTS,
> etc. results in the best performance of my application.  Does SQL Profiler
> do this?  Should I use the difference between time values to gauge the
> length of time each query takes?  How is this sort of thing usually done?
> Please give examples if possible.  I don't know how SQL Profiler works.
> I've tried performance tuning of any kind.  Any help would be greatly
> appreciated!

> Thank you very much!

Use Query Analyzer and the graphical execution plans.

Focus more on IO and less on execution time, unless you are working on a
single user database.  Execution time can be affected by so many other
factors not caused by your query.

Use SET STATISTICS IO ON and run your queries in Query Analyzer, and monitor
the logical reads.  These numbers should stay fairly constant.  If you just
use Profiler to monitor the reads, this may be affected by data already in
the cache, thus lowering the reads, unless you use DBCC DROPCLEANBUFFERS
everytime before you run your query, but this will affect everyone too.  Ok
if it's a test system, bad if it's a live system.

Having said that, the results reported by SET STATISTICS IO ON may sometimes
differ if you run DBCC DROPCLEANBUFFERS, but this has happened to me only
once.

--
Regards
Ray Mond

Quote:> How should I test to see which kind of queries give me the best
performance?
> I want to know exactly whether joins, subqueries, derived tables, EXISTS,
> etc. results in the best performance of my application.  Does SQL Profiler
> do this?  Should I use the difference between time values to gauge the
> length of time each query takes?  How is this sort of thing usually done?
> Please give examples if possible.  I don't know how SQL Profiler works.
> I've tried performance tuning of any kind.  Any help would be greatly
> appreciated!

> Thank you very much!