August 17, 2012 at 10:00 am
Hi,
Just curious to understand...
I have a stored procedure... I put 7 tables into temp tables, indexed them using cluster index...
All are LEFT JOINED by me in SQL Statement...
When I look at the Execution Plan, I see it's don Hash Match (Right Outer Join)??? Why does it do a Hash when I've indexed, and why does it say RIght Outer Join when I've said LEFT JOIN???
Also, everything pretty much states the COST at 0%, where the Insert was 97%...
Again, just curious for me...
FYI...
The original query, was straight SQL and joined on VIEWS and TABLES, 2 across a network... Took 10 Min to run that bad boy...
So I went straight to the tables, queried only the columns needed for results and joins inserted each into it's own temp table, indexed the columns that were being joined on (CLUSTERED)... took 8 seconds... I'm not sure If I should be excited because it's only 60,000 rows... Better architecture is needed I'm thinking...
Thanks,
John
August 17, 2012 at 10:02 am
Forgot to add this too...
When I insert into the temp tables, I noticed the execution plans show that it did, sorts, and index inserts (Clustered)...
Why would it do that, when I just wanna get the data in there... can that be stopped???
August 17, 2012 at 10:35 am
The Index insert (clustered) is the insert into the table itself (clustered indexes are the table)
Having indexes on a column doesn't mean that SQL will use a specific join. The optimiser calculated that the optimal join was hash (probably because of the number of rows involved) and hence the query uses a hash join.
SQL doesn't have to process the tables in the order you specify them in the where clause, in this case the optimiser figured out that it would probably be more efficient to reverse the order of tables, hence the left join becomes a right join when the table order is reversed.
As for the time, remote queries are notoriously slow, lots of possible reasons. Can you perhaps post the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2012 at 12:07 pm
Take a look at the first operator in the execution plan and look at it's properties. Is the reason for early termination "Good enough plan found"? Then the optimizer did it's work and you need to look at the indexes or statistics or something else for why it found the plan it did. If it says "timeout" then the plan was complex enough that the optimizer didn't find a good enough plan. That's when you don't trust what the optimizer did with a plan and try to find solutions such as worrying about the order of joins, etc.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply