"TS" <tanujabrainsoft.ch> wrote in message
> We are having a query comprising of joins between 8 tables.
> The query fetches 30 fields.
> This query takes 55 seconds to complete executing.
> If the same query is re-written such that only 3 fields are fetched then> takes 15 seconds to complete.
> What are the possible problems that causes this difference in execution
> Any solutions for faster performance of queries having several joins?
When you left join to a primary key, or inner join from a non-null column to
a primary key, and don't select any columns from the joined table, the join
can logically be ignored since it can neither add nor subtract a row from
SQLServer uses this as an optimization to avoid joining tables it doesn't
really need. This is usefull when you have a view that joins a number of
lookup-type foreign keys. When selecting from the view and not returning
any columns from the joined table you don't pay a price for having joined
them in the view definition.
EG of some queries where joins can be ignored
left join t2
on t1.c = t2.c
Where t2.c is unique
inner join t2
on t1.c = t2.c
where t1.c is not null and t2.c is unique.
So a change in the columns selected can change the query execution
Now for your problem. The basic rule of thumb for yzing execution plans
involving multiple joins is this:
Get to your final row number as quickly and cheaply as possible.
If your execution starts off with 70,000 rows (a fat grey arrow), and ends
up with 50 rows (a skinny grey arrow), then try to get it to drop to 50 rows
in the first step (or two) using an index seek, or index scan.
Once you have narrowed to a small number of rows, joining many additional
tables is cheap.