Query running slowly - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query running slowly

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-23 : 13:53:53

I inherited this query. It takes a long time to run. A couple of questions...

1. Is it because there's no join (should a left join be in between the two tables in the From section?

2. Should the bold part have "on" in front of where fields are joining

3. Should the where clause go after the bold section?



SELECT DISTINCT AL1.CLMS, 
	AL1.COS, 
	AL2.JURIS_MVT_SEQ_NUM, 
	RIGHT(AL2.ORG_ID,3)AS ORG_ID, 
	AL2.JURIS_MVT_TYP 

FROM MT2.CLAIM AL1, MT2.JURISMVT AL2 

WHERE (AL1.CLMS=AL2.CLMS AND AL1.BICNUM=AL2.BICNUM 
		AND AL1.REF_NUM=AL2.REF_NUM AND
		AL1.TYP_OF_ACTN=AL2.TYP_OF_ACTN AND 
		AL1.APP_STDT=AL2.APP_STDT AND 
		AL1.ACTV_IND_CD=AL2.ACTV_IND_CD)  
AND ((AL1.ACTV_IND_CD='A' AND AL1.ADJU_LVL='I' AND AL1.WKLD='04' AND
(NOT AL1.APP_RCPDT IS NULL)))

Would those changes be efficient and make the query run faster?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 14:01:19

nope. whatever you've specifying is new join synatx which is as per ANSI standards. The posted query uses old join syntax. It wont bring about any major change in efficency though it will improve readability of query a lot.
For improving performance have a look at execution plan and see if you could add few indexes to speed up process, in case it lacks any.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-23 : 14:09:09

So is the old join located in the where clause?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 14:15:34

yep. the where condition includes join condition also. the section is bold specifies the ON condition.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-23 : 14:50:38

Okay thanks for looking at it and explaning as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources