how do we tune a large sql query

hi,
I have a problem asked by one of my senior person and finding the
answer .
What is the step by step procedure for tune a large sql query.
OR how do we tune a large SQL query with somany joins

kvsnramesh@gmail.com  wrote:
> hi,
> I have a problem asked by one of my senior person and finding the
> answer .
> What is the step by step procedure for tune a large sql query.
> OR how do we tune a large SQL query with somany joins

Assuming "tune" means "make faster".  One methodology: look at the
execution plan and see where the DB spends the time.  Then go from there.
See also http://www.sql-server-performance.com/

Kind regards

robert

>> What is the step by step procedure for tuning a large sql query. <<

What is the Good Life and how can I lead it, Socrates?  Ghod, you work
for the iignorant.

Steps?  There are only guidelines:
1) Have a normalized design, so the data has integrity.
2) The queries are a few magnitude of orders easily with 5NF
3) Trust thr optimizer, Luke

trust the optimizer, NOT.

wow.   you START by trusting the optimizer. when stuff is slow, you dig
into it.

My point is a that slow code is usually the result of bad DDL, whcih in
turn leads to complex DML.  You do not start by adding indexes and
hints.   You fix the leak instead of mopping the floor,  over and over.


But you remark is interesting,  A friend of mine who writes database
engines for a major vendor observed that SQL Server people do not trust
their optimizer like Ingres, Postgres, DB2 people do.  That is a bad
thing to say about the product.

The classic one was a test done by Fabian Pascal in DATABASE
PROGRAMMING & DESIGN years ago. It should still be in the CMP archives.
He wrote the same query seven different ways and ran them on various
SQL products on the same desktop hardware.

Ingres knew they were logically identical and produces the fastest
results of any
of the products.   Oracle produced different execution plans for each
query and the slowest running one had to be shut down after grinding
for hours instead of seconds.  The other products were all over the
place.  But by now, everyone who is stil in business has improved since
those days.

The result is that many ms sql programmers do not trust the optimizer.

Your conclusion is that the optimizer in MS SQL can't be trusted.

Another possible conclusion is that there are no other engines widely
used.

I think the mssql engine can be trusted 99.9 percent of the time.  But
if you step up into the big leagues, then you should have someone on
your team that knows what they are doing.

I know for a FACT that Oracle is the same way. Someone with good
business knowledge of the data can often speed large databases up.

>> A friend of mine who writes database
engines for a major vendor observed that SQL Server people do not trust

their optimizer like Ingres, Postgres, DB2 people do.  That is a bad
thing to say about the product.
<<

that really depends on your perspective. For many practitioners RDBMS
as the whole package is much much more than just the optimizer. In many
situations the _whole solution_ implemented with SQL Server (including
DTS, reports, Web services etc.) is quite cheap to develop and to
maintain.

What's the point of developing the best optimizer in the world if all
the other links of the chain are weak?