Re: Not exists performance problem...
From: ashish (mittalashish_at_yahoo.com)
Date: 02/21/04
- Next message: ashish: "Re: Large Slowly Changing Dimension"
- Previous message: Belinda: "Re: SAP BW compared to Essbase/SQLServer/Oracle as a Enterprise Data"
- In reply to: Jéjé: "Not exists performance problem..."
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 20 Feb 2004 19:45:19 -0800
It is usually best not to use the "not in" clause for large tables. I
would have posed the query sas follows:
select * from (
select ...
from T1 inner join T2 ...
LEFT OUTER JOIN FT1 on FT1.A = C1.A and FT1.B=C2.B
) where FT1.A is null
On Wed, 18 Feb 2004 15:59:27 -0500, Jéjé <willgart@_A_hAotmail_A_.com>
wrote:
> Hi,
>
> I've a query to load my data warehouse, like this :
> select C1, C2, C3....
> from T1 inner join T2 ......
> where not exists (Select * from FT1 where FT1.A = C1.A and FT1.B=C2.B...)
>
> my T1 table (the biggest one) has 240 000 records
> my FT1 contain only 20 000 records.
>
> the result is near 160 000 records and takes 26 minutes to be
> completed!!!
>
> The query plan estimated that the query will return only 60 records!!!
> So there is a big mistake between the real number and the query plan, so
> I
> presume this is the problem because SQL Server don't optimize correctly
> my
> query.
> But my statistics are goods and my index are goods.
>
> The index tuning wizard don't find anything to optimize.
>
> What can I do?
> Any guide?
> Thanks.
>
> Jerome.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.591 / Virus Database: 374 - Release Date: 2004-02-17
>
>
-- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
- Next message: ashish: "Re: Large Slowly Changing Dimension"
- Previous message: Belinda: "Re: SAP BW compared to Essbase/SQLServer/Oracle as a Enterprise Data"
- In reply to: Jéjé: "Not exists performance problem..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
