May 22, 2006 at 3:56 am
What is the order of execution of conditions in a where clause.
Ex:1
Select * from A
Wher A.a1 = 1000 and A.a2 = 'Y' and ....
And what if the conditions are part of Join condition of 2 or mort table.
Ex:2
Select * from A,B
Where A.a1 = B.a1 and B.b2= 1000 and B.b1='Y' ...
Is the order hampers the performance of the Query or is taken care by the Query Optimizer.
Thanks.
May 22, 2006 at 4:37 am
Whatever order the optimiser feels is best.
That said, it is considered beter to join in the from clause, rather than the where clause. for readability, if nothing else.
SELECT * FROM A INNER JOIN B ON A.a1 = B.a1
WHERE B.b2 and B.b1='Y' ....
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
May 22, 2006 at 4:42 am
Thanks Addict,
What if the Join condition has two clauses.
e.g
SELECT * FROM A INNER JOIN B ON A.a1 = B.a1 and A.a2 = B.a2
Now what will be order of evaluation in such case?
Is it left to right or right to left. Or is it entirly a function of SQL Optimizer?
What if, the second clause (A.a2= B.a2) results less no. of records than the 1st clause.
May 22, 2006 at 4:49 am
Whatever order the optimiser feels is best, based on it's knowledge of the data distribution statistics and the indexes that are available on the tables.
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
May 22, 2006 at 4:53 am
Ok.
Thanks for clarification.
May 22, 2006 at 8:15 am
That's why it's best to keep your stats updated. In the BOL, look up sp_updatestats and UPDATE STATISTICS commands.
-SQLBill
May 23, 2006 at 10:14 am
SQLBill,
Unless you want a specific resample done, is it necessary to run sp_Updatestats if the database option of Auto Update statistics is turned on for the database?
Would the SQL Query Optimizer run better if the stats are refreshed so to speak than if it relies on the Auto Update feature of the database?
May 23, 2006 at 1:27 pm
The problem with Auto anything, is that SQL Server will do it 'when it feels like it'. It chooses when is the best time to run that command, so it might be a while. That can cause your statistics to be out of date when you need them most. So, if you do something on the database that might get your stats 'out of whack', update the stats before you do anything else.
-SQLBill
May 23, 2006 at 1:31 pm
Thanks SQLBill
May 23, 2006 at 10:51 pm
I've noticed 'problems' with the auto stats with large tables (+20 million rows) with clustered indexes on an increasing field.
I had a table a while back with DateAdded as the clustered index. It had several million rows in it. If I did a select from the table, I had dates between the 1st Oct and the 25th Jan. According to the statistics however, I had dates ranging from 1st Oct to 20th Jan.
As you can imagine, queries that retrieved the previous day's data were exceedingly slow. I ended up adding an update statistics statement to the end of the job that populated the table. took about 10 sec to run th update stats and I never had slow queries on the table again.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply