Re: Testing Performance of Queries
From: Joe Celko (joe.celko_at_northface.edu)
Date: 03/01/04
- Next message: Tim Wallace: "setting up SQL Server data source"
- Previous message: Star: "Re: Help with recursive queries... BIG problem."
- In reply to: klnorris_at_comcast.net: "Testing Performance of Queries"
- Next in thread: Ray Higdon: "Re: Testing Performance of Queries"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 01 Mar 2004 15:01:19 -0800
>> 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.
>> 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.
>> 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!
- Next message: Tim Wallace: "setting up SQL Server data source"
- Previous message: Star: "Re: Help with recursive queries... BIG problem."
- In reply to: klnorris_at_comcast.net: "Testing Performance of Queries"
- Next in thread: Ray Higdon: "Re: Testing Performance of Queries"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
