where to put = in a where clause

[timc]

I have a 3rd party app which generates the following in it's where clause. COnsidering all the ROJ's on this table, would it not be better to put the definitive part
"AND pe.enc_id = '8E487A58-E53A-42B3-8D0E-07AA1BE2E5E5'" before the joins, or does it really make a difference?


WHERE physical_exam_.enc_id =* pe.enc_id AND
immunization_mast_.enc_id =* pe.enc_id AND intk_trnf_master_.enc_id
=* pe.enc_id AND vital_signs_.enc_id =* pe.enc_id AND
med_sys_rev_.enc_id =* pe.enc_id AND tb_isolation_.enc_id
=* pe.enc_id AND zinmate_encounter_.enc_id
=* pe.enc_id AND intk_trnf_master8_.enc_id =* pe.enc_id AND pe_head_neck_.enc_id
=* pe.enc_id AND pe_eye_exam_.enc_id =* pe.enc_id AND pe_ears_.enc_id
=* pe.enc_id AND pe_throat_.enc_id =* pe.enc_id AND pe_skin_.enc_id
=* pe.enc_id AND pe_cardio_.enc_id =* pe.enc_id AND
pe_musculoskeletl_.enc_id =* pe.enc_id AND pe_neurological_.enc_id
=* pe.enc_id AND pe_breast_.enc_id =* pe.enc_id
AND pe_lymph_new_.enc_id =* pe.enc_id AND pe_abdomnl_.enc_id
=* pe.enc_id AND pe_male_.enc_id =* pe.enc_id AND pe_female_.enc_id
=* pe.enc_id AND pe_proctological_.enc_id =* pe.enc_id
AND pe.enc_id = '8E487A58-E53A-42B3-8D0E-07AA1BE2E5E5'

[Rawhide]

You should specify your joins in the From clause. Using =* and *= is not SQL-92 compliant and isn't likely to be supported in future versions of SQL Server.

That aside, yes, you will get better performance by putting the "pe.enc_id = '8E487A58-E53A-42B3-8D0E-07AA1BE2E5E5'" as early as possible if it will greatly reduce the number of rows resulting in the joins. I try to include things like this in my first join statement if possible.

[timc]

Thanks, like I said this is a 3rd party app, I have no control over their sql, I haven't seen this bad of sql in ages

[Rawhide]

Oh right, forgot that part. Good luck.

[rmiao]

Talk to vendor and ask them make changes in next release.