Just read that oracle optimizer processes the right most table first to cut down the resultset. Is this the same in SQLServer 2005?

Also any links to SQLServer 2005 specific optimizer tips?

Thanks

Just read that oracle optimizer processes the right most table first to cut down the resultset.

I can't comment on SQL Server but what you are saying above is only true in a "rule-based" optimizer. Oracle introduced a "cost-based" optimizer many years ago and although rule-based still co-exists with cost-based in the database engine it is unlikely to be used. Cost-based optimization relies on statistics gathered on the underlying database tables.

Therefore, syntactically, it should not matter what order you place your table in your SQL.

You should always go with cost-based. I can give you many reasons why rule-based is not so good.

The estimated/actual execution plan feature of SSMS is your friend.

It details the cost, scan types and index usage stats of your query.

have fun 😯

When SQL Server executes a SQL statement, it uses the query optimizer to construct an execution plan for the statement. An execution plan is a series of steps that produces the desired result for the query. SQL Server's Showplan feature allows users to see the execution plan that the optimizer creates for a specific statement. This information can be used to help decide how a statement's performance might be improved, either by changing the query or by adding a database index. There are ways to generate an execution plan in MS SQL Server (note: the examples below apply to MS SQL Server 7.0 and SQL Server 2000):

Using Showplan with the Set statement

There are two Transact-SQL statements that can be used to see a SQL query statement's execution plan:
Set Showplan_Text On
or
Set Showplan_All On
Both statements cause SQL Server to not execute subsequent SQL statements for the connection until another Showplan_Text or Showplan_All command is executed with "Off" as the new setting. When Showplan_Text or Showplan_All is in effect, SQL Server returns information about the execution plan for each subsequently executed statement. Showplan_Text causes basic information to be returned in a format suitable for users to read directly, whereas Showplan_All returns more comprehensive information in a result table suitable for processing by an application program.

Given below is an example of Showplan_Text:

Set Showplan_Text On
go
Select * From S_ORG_EXT
Order by NAME

StmtText
—————————————–
Select * From S_ORG_EXT
Order by NAME

(1 row(s) affected)

StmtText
——————————————————————————————
|–Clustered Index Scan(OBJECT:([sb995ms70].[dbo].[S_ORG_EXT].[S_ORG_EXT_U1]), ORDERED)

(1 row(s) affected)
The output is structured as a tree. In this example, the first execution step is the clustered index scan, and the results of this step are used in a sort step. The results of the sort step are returned to the application using Graphical Showplan. Use Query Analyzer in MS SQL Server 7.0 to review execution plan for a statement. Simply enter the statement in the query pane and select Query > Display Estimated Execution Plan menu. This will display a flow diagram for the execution plan. To print the graphical plan, click the cursor in the SQL Execution Plan pane to make it active and select Print from the File menu.

http://msdn.microsoft.com/en-us/library/ms187713.aspx

Thanks very much everybody. I will be feeding off this for the rest of the afternoon I think.

Cost-based. Makes lots of sense. Must be been an old article I googled.

I have been looking at the execution plan and can understand it a bit but not enough to make significant use of it. Will hit the help files.

With Oracle at the sql*plus prompt you can just set autotrace on for the session.

SQL> set autotrace on

This auto populates and displays results from the plan_table.

Its easier to read and use than EXPLAIN PLAN but doesn't quite give as much info. but very useful all the same. In most situations it's all you'll need.

There's plenty of information on the web on how to interpret the results. Just requires a little background reading first.

bol.