|
Home > Archive > MS SQL Server > August 2005 > Left Join problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Bill Wang 2005-08-02, 11:23 am |
| Hi All,
There is a left join based on PK between two big tables (more than 10
milli records each). I know it's a problem on performance. But when I checed
the execution plan on a test server it will change in some situation. If it
uses "Hash Match/Right Outer Join" the perormance is very bad and cost much
memory (20 times than normal). If it uses "Nested Loops/Left Out Join" it's
very good and use little memory. It seemed that the change of execution plan
is caused by the data in cache because the execution plan will change to
"Hash Match" after I restart SQL Server. I don't know it's a problem/bug or
it's by design.
I appreciate any comments!
Bill
| |
| Bill Wang 2005-08-02, 11:23 am |
| Sorry, the system is SQL Server 2000 Enterprise (SP3) with 16 GB memory/ 4
CPUs with AWE enabled on windows 2003
"Bill Wang" wrote:
> Hi All,
> There is a left join based on PK between two big tables (more than 10
> milli records each). I know it's a problem on performance. But when I checed
> the execution plan on a test server it will change in some situation. If it
> uses "Hash Match/Right Outer Join" the perormance is very bad and cost much
> memory (20 times than normal). If it uses "Nested Loops/Left Out Join" it's
> very good and use little memory. It seemed that the change of execution plan
> is caused by the data in cache because the execution plan will change to
> "Hash Match" after I restart SQL Server. I don't know it's a problem/bug or
> it's by design.
> I appreciate any comments!
>
> Bill
| |
| Alejandro Mesa 2005-08-02, 11:23 am |
| Can you post ddl and the select statement?
AMB
"Bill Wang" wrote:
> Hi All,
> There is a left join based on PK between two big tables (more than 10
> milli records each). I know it's a problem on performance. But when I checed
> the execution plan on a test server it will change in some situation. If it
> uses "Hash Match/Right Outer Join" the perormance is very bad and cost much
> memory (20 times than normal). If it uses "Nested Loops/Left Out Join" it's
> very good and use little memory. It seemed that the change of execution plan
> is caused by the data in cache because the execution plan will change to
> "Hash Match" after I restart SQL Server. I don't know it's a problem/bug or
> it's by design.
> I appreciate any comments!
>
> Bill
| |
| Wayne Snyder 2005-08-02, 1:23 pm |
| The optimizer does take into account how much of the data is expected to be
in memory and whether that data has been used much recently to determine the
plan, and the locking scheme...
However, a hash match join is generally an indication the an appropriate
index to support the join does NOT exist.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Bill Wang" < BillWang@discussions
.microsoft.com> wrote in message
news:B97F28DF-06DA-48EF-B68A- 379319B4A799@microso
ft.com...
> Hi All,
> There is a left join based on PK between two big tables (more than 10
> milli records each). I know it's a problem on performance. But when I
> checed
> the execution plan on a test server it will change in some situation. If
> it
> uses "Hash Match/Right Outer Join" the perormance is very bad and cost
> much
> memory (20 times than normal). If it uses "Nested Loops/Left Out Join"
> it's
> very good and use little memory. It seemed that the change of execution
> plan
> is caused by the data in cache because the execution plan will change to
> "Hash Match" after I restart SQL Server. I don't know it's a problem/bug
> or
> it's by design.
> I appreciate any comments!
>
> Bill
| |
| Bill Wang 2005-08-02, 1:23 pm |
| Thanks all!
Wayne is right. Although there is no any changes on indexes on both
tables,the STATISTICS for tables and indexes didn't be refreshed up-to-date.
After I run UPDATE STATISTICS on the tables the execution plan changed to
good one.
Thanks again!
Bill
"Wayne Snyder" wrote:
> The optimizer does take into account how much of the data is expected to be
> in memory and whether that data has been used much recently to determine the
> plan, and the locking scheme...
>
> However, a hash match join is generally an indication the an appropriate
> index to support the join does NOT exist.
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
>
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Bill Wang" < BillWang@discussions
.microsoft.com> wrote in message
> news:B97F28DF-06DA-48EF-B68A- 379319B4A799@microso
ft.com...
>
>
>
|
|
|
|
|