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.

Joins

I am trying to join multiple tables using FROM joins. I want to pick up
only OB/GYN Companies but am instead getting everything.
Here's my SQL...
SELECT COMPANY.[NAME],
COMPANY.[ADDRESS],
COMPANY.[CITY],
COMPANY.[STATE],
COMPANY.[ZIP],
COMPANY.[MAIN_CONTACT],
COMPANY.[MAINCONTCTPHONE],
CMDBSPEC.[CMDB_SPEC_DESC],
CMDBCOMP.[CMDB_COMPANY_PRODUCT_PURCHASE]
FROM [magictsd].[_SMDBA_].[_COMPANY_] COMPANY
INNER JOIN [magictsd].[_SMDBA_].[CMDB_COMPANY] CMDBCOMP
ON COMPANY.SEQUENCE = CMDBCOMP.SEQUENCE
INNER JOIN [magictsd].[_SMDBA_].[CMDB_SPEC] CMDBSPEC
ON CMDBCOMP.CMDB_COMPANY_SEQ_SPECIALTY = CMDBSPEC.SEQUENCE
AND CMDBSPEC.CMDB_SPEC_DESC LIKE '%Ob%'
OR CMDBSPEC.CMDB_SPEC_DESC LIKE '%Gyn%'
Can anyone help me? I thought INNER JOIN was the answer since I want
ONLY the OB/GYN Companies.
Any help is GREATLY appreciated!Without DDLs & sample data this is only a guess: Try changing the OR in your
JOIN clause to an AND.
Anith|||Well the last two Join condiditons should be surrounded by Parentheses, but
actually even better, would be to put them in a Where clause instead of as a
Join Condition...
Select C.Name, C.Address, C.City, C.State,
C.Zip, C.Zip,Main_Contact, C.Zip,MainContactPhone,
S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
From _Company C
Join Cmdb_Company CC
On CC.Sequence = C.Sequence
Join Cmdb_Spec S
On S.Sequence = CC.Cmdb_Company_Seq_Speciality
Where S.Cmdb_Spec_Desc Like '%Ob%'
Or S.Cmdb_Spec_Desc Like '%Gyn%'|||Sorry, your field names are a bit unusual, I typoed trying t ocopy them...
Here is corrected version...
Select C.Name, C.Address, C.City, C.State,
C.Zip, C.Main_Contact, C.MainContctPhone,
S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
From _Company C
Join Cmdb_Company CC
On CC.Sequence = C.Sequence
Join Cmdb_Spec S
On S.Sequence = CC.Cmdb_Company_Seq_Speciality
Where S.Cmdb_Spec_Desc Like '%Ob%'
Or S.Cmdb_Spec_Desc Like '%Gyn%'
"CBretana" wrote:

> Well the last two Join condiditons should be surrounded by Parentheses, bu
t
> actually even better, would be to put them in a Where clause instead of as
a
> Join Condition...
> Select C.Name, C.Address, C.City, C.State,
> C.Zip, C.Zip,Main_Contact, C.Zip,MainContactPhone,
> S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
> From _Company C
> Join Cmdb_Company CC
> On CC.Sequence = C.Sequence
> Join Cmdb_Spec S
> On S.Sequence = CC.Cmdb_Company_Seq_Speciality
> Where S.Cmdb_Spec_Desc Like '%Ob%'
> Or S.Cmdb_Spec_Desc Like '%Gyn%'