 |
|
|
|
Next: RPC error -Link server from MSSQL 2005 (32 bit) t..
|
| Author |
Message |
External
Since: Mar 08, 2006
Posts: 24
|
(Msg. 1) Posted: Thu Jan 31, 2008 4:10 pm
Post subject: When the hash will involve in explain plan?
Archived from groups: microsoft>public>sqlserver>datamining (more info?)
|
|
|
Hi,
We runt he following question against both QA and Prod, but the sorting of
the data is different even though both server's sorting and table/index
structure are the same. (Region table -- clusterind:Region_number; Bank
table -- clusterind:BankID; Fileset table -- clisterind:filesetID)
select Region.*, bank.*, FileSet.*
from Region left join Bank on region.Region_Number = bank.Region_Number
left join FileSet on FileSet.bankID = Bank.bankID
where Region.Region_Number <> '00000'
In QA, the return record sorting is by Region.Region_number, In prod, the
sorting is by fileset.filesetid.
After examing the explain plan against QA and PROD, one step is different.
QA -- Nested Loop/Left join fileset on ..
Prod -- Hash Match/Left join fileset on ..
Not sure why QA and PROD use the different explain plan? Looks like
different explain plan will have different return record sorting. If I add
"order by region.region_number" in prod question, then the explain plan will
be the same as QA.
Please advise why this situation happen? And, in what situation, the Hash
Match will be used? Thank you.
--
Best Regards,
Lynn |
|
| Back to top |
|
 | |
External
Since: Mar 08, 2006
Posts: 24
|
(Msg. 2) Posted: Tue Feb 05, 2008 10:02 am
Post subject: RE: When the hash will involve in explain plan? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Dejan,
thank you for your response. The statistics is all updated very recent. In
fact, PROD server is more up-to-date than QA.
--
Best Regards,
Lynn
"Lynn" wrote:
> Hi,
>
> We runt he following question against both QA and Prod, but the sorting of
> the data is different even though both server's sorting and table/index
> structure are the same. (Region table -- clusterind:Region_number; Bank
> table -- clusterind:BankID; Fileset table -- clisterind:filesetID)
>
> select Region.*, bank.*, FileSet.*
> from Region left join Bank on region.Region_Number = bank.Region_Number
> left join FileSet on FileSet.bankID = Bank.bankID
> where Region.Region_Number <> '00000'
>
> In QA, the return record sorting is by Region.Region_number, In prod, the
> sorting is by fileset.filesetid.
>
> After examing the explain plan against QA and PROD, one step is different.
> QA -- Nested Loop/Left join fileset on ..
> Prod -- Hash Match/Left join fileset on ..
>
> Not sure why QA and PROD use the different explain plan? Looks like
> different explain plan will have different return record sorting. If I add
> "order by region.region_number" in prod question, then the explain plan will
> be the same as QA.
>
> Please advise why this situation happen? And, in what situation, the Hash
> Match will be used? Thank you.
> --
> Best Regards,
>
> Lynn |
|
| Back to top |
|
| |
External
Since: Mar 18, 2004
Posts: 317
|
(Msg. 3) Posted: Tue Feb 05, 2008 3:01 pm
Post subject: Re: When the hash will involve in explain plan? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
>
> We runt he following question against both QA and Prod, but the sorting of
> the data is different even though both server's sorting and table/index
> structure are the same. (Region table -- clusterind:Region_number; Bank
> table -- clusterind:BankID; Fileset table -- clisterind:filesetID)
>
> select Region.*, bank.*, FileSet.*
> from Region left join Bank on region.Region_Number = bank.Region_Number
> left join FileSet on FileSet.bankID = Bank.bankID
> where Region.Region_Number <> '00000'
>
> In QA, the return record sorting is by Region.Region_number, In prod, the
> sorting is by fileset.filesetid.
>
> After examing the explain plan against QA and PROD, one step is different.
> QA -- Nested Loop/Left join fileset on ..
> Prod -- Hash Match/Left join fileset on ..
>
> Not sure why QA and PROD use the different explain plan? Looks like
> different explain plan will have different return record sorting. If I
> add
> "order by region.region_number" in prod question, then the explain plan
> will
> be the same as QA.
>
> Please advise why this situation happen? And, in what situation, the Hash
> Match will be used? Thank you.
My guess is hash join is used because statistics is not updated on the
server where you use the PROD app. Do pelase check the UPDATE STATISTICS
command in Books OnLine.
--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx >> Stay informed about: When the hash will involve in explain plan? |
|
| Back to top |
|
| |
| Related Topics: | SBA Invoice email - Although the standard invoice within sba is lame, it is much easier to use than exporting to Word, etc. Is there a way to email this form? If printed to a file, it uses a format that no one else can read. There should be an easy way to email this..
Shrink .LDF File - Dear All, The SQL Server 2000 in my network is hosting many database for many business units within the organization. The server is heavily used for data collection and web hosting. One of the .LDF file has grown very fast and I am very sure that it....
SELECT in sections - I want to know how to retrieve records from a SELECT command in sections. I mean, if total records of a SELECT command is 100,000, I want to use SELECT that retrieves me them in 20 "sections" of 5000 : first 5000, second 5000, sixth 5000 or ...
One query result included in another - I'm stumped with trying to create a dynamic query against items in two many-to-many relations that are both related to a single features table. For simplicities sake, I'll say we've got a table called FEATURES, to which we can add "features"...
Comparing Data and Filtering Results - shopper_tracking shopper_id tracking_num closed date_entered 12345 1za45690987 1 8/15/2005 65984 1za45690988 1 8/15/2005 23569 1za45690990 1 8/15/2005 25648 1za45690991 ... |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
