I don't think that was the case, Execution plans for both queries are different, Also these queries were executing frequently and I saw the difference even executing them in parallel (half queries in old way and half in new way simultaneously).
I see this is SQL Server and it will cache plan for an exact query you run. By moving date time clause to join condition you force a new plan generation.
You should be able to get same results by forcing plan recompilation
I looked at my query again and I found out that I had an extra clause NOT EXISTS which was causing difference in speed and plans, omitting not EXISTS didn't make difference in speed in both cases.
That's not luck, it would absolutely produce a new plan because the text changed. Even adding a space or making a letter upper case will generate a new plan.
I should've been more clear that moving clause from where to join does not add any performance on its own, new forcefully generated plan did. Thanks for bringing this up!
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
You were just lucky. Query optimizer has chosen a non cached version of plan to execute this query.
I don't think that was the case, Execution plans for both queries are different, Also these queries were executing frequently and I saw the difference even executing them in parallel (half queries in old way and half in new way simultaneously).
Plans have obviously have to be different.
I see this is SQL Server and it will cache plan for an exact query you run. By moving date time clause to join condition you force a new plan generation.
You should be able to get same results by forcing plan recompilation
I looked at my query again and I found out that I had an extra clause
NOT EXISTSwhich was causing difference in speed and plans, omittingnot EXISTSdidn't make difference in speed in both cases.That's not luck, it would absolutely produce a new plan because the text changed. Even adding a space or making a letter upper case will generate a new plan.
Yes, this is correct.
I should've been more clear that moving clause from where to join does not add any performance on its own, new forcefully generated plan did. Thanks for bringing this up!