Solved

Transact SQL Query - how to force the join order ?

Posted on 2013-12-12
3
521 Views
Last Modified: 2013-12-12
Hello,
I'm using a query for years but this time it makes me getting creasy ...

UPDATE TABLE_1 set TABLE_1.CHANGEMENT = 1 ,
    TABLE_1.VALEUR_POINTS = TABLE_2.VALEUR_POINTS,
    TABLE_1.DESIGNATION_PRODUIT = TABLE_2.DESIGNATION_PRODUIT
     FROM TABLE_2 JOIN TABLE_1 ON TABLE_1.REF_EDITEUR = TABLE_2.REF_EDITEUR  AND  TABLE_1.REF_PRODUIT = TABLE_2.REF_PRODUIT  AND ACTUEL = 1
     WHERE
     TABLE_2.REF_EDITEUR = 'ZIM'
     AND TABLE_1.GAMME = TABLE_2.GAMME
     AND TABLE_1.PROGRAM_TYPE = TABLE_2.PROGRAM_TYPE
     AND( TABLE_1.VALEUR_POINTS <> TABLE_2.VALEUR_POINTS
     OR TABLE_1.DESIGNATION_PRODUIT <> TABLE_2.DESIGNATION_PRODUIT )

this query should returned 0 because there is no entry to join.
TABLE_2 is a very little table (less than 300 entries)

if I run the query like this, it is running for a long, long , long time and have to stop it.
if I remove this line :  AND TABLE_1.PROGRAM_TYPE = TABLE_2.PROGRAM_TYPE
it is running in 0 secs.

attached the estimated plan.

I added the option FORCE ORDER but it didn't help....

thanks to help me, very urgent and important ....
plan.xml
0
Comment
Question by:bruno_boccara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39714615
Would like to see full table and index definitions.

But, based on the query plan, the Produits PROGRAM_TYPE is having to go thru an implicit conversion to be compared to ~tmp_Produits.  Implicit conversions can have a very bad effect on performance (likely tmp_ is nvarchar, nontmp is varchar).  If you're going to join on a column, it's best to make sure they have exactly the same data types.
0
 

Author Comment

by:bruno_boccara
ID: 39714701
unbelieveable ....

IN TABLE_2 it was nvarchar(50) , in TABLE_1 it was varchar(50)

I changed table_2 to the same and it is working in 17ms ....


MANY THANKS !! I LEARNED A LOT IN WITH YOUR ANSWER !!!
0
 

Author Closing Comment

by:bruno_boccara
ID: 39714702
wonderful
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question