Joins Vs Where clause - Performance Query

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ??

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )

USING A WHERE CLAUSE and Alias
--------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

------------
It pays to be honest to your DBAmy experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.

If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.

Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.|||Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).

and avoid using AliasesNope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not. :)

I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
http://www.sql-server-performance.com/articles_performance.asp

HTH|||avoiding aliases is not "reasonable"

:)|||Some people think aliases make code easier to read, blindman does not. :)
My reputation preceeds me.
But even I don't claim the aliases hurt performance.