Limit Joins (or plain improve qry)

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

SQL Server 2005 Forums

Transact-SQL (2005)

Limit Joins (or plain improve qry)

Author

Topic

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 08:07:58

Hi,

I got a qry that takes 10s. This is off course too long.
As I described in my earlier posts, I'm no sql hero, but I'm on a neverending quest to improve.
Currently I need a select statement over several (7) tables and return fields from almost all of them.
Furthermore I have optional restrictions on fields spread over all those tables, based on a set of parameters.
What I initially do is inner join all my tables and then have a big where clause that adds where-rules depending on the arguments given.

Like so:

	select c.clr_id, n.clr_nm, co.long_nm, p.paint_cd, a.appl_desc, m.modl_nm, y.yr_num
	from CLR c
	inner join paint_cd p
		on c.clr_id = p.clr_id
	inner join clr_use_yr y
		on y.clr_id = c.clr_id
	inner join co
		on co.co_id = c.co_id
	inner join modl m
		on y.modl_id = m.modl_id
	inner join clr_appl ca
		on c.clr_id = ca.clr_id
		inner join appl a
		on ca.appl_cd = a.appl_cd
	inner join clr_nm n
		on c.clr_id = n.clr_id
	WHERE
		(CASE @regn when '' THEN '' ELSE n.regn_cd END) like (CASE @regn when '' THEN '' ELSE @regn END)
		AND (CASE @co when '' THEN '' ELSE co.long_nm END) like (CASE @co when '' THEN '' ELSE @co END)
		AND (CASE @clrNm when '' THEN '' ELSE n.clr_nm END) like (CASE @clrNm when '' THEN '' ELSE @clrNm END)
		AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END)
		AND (CASE @applDc when '' THEN '' ELSE a.appl_desc END) like (CASE @applDc when '' THEN '' ELSE @applDc END)
		AND (CASE @useYear when '' THEN '' ELSE  cast(y.yr_num as varchar(4)) end) like (CASE @useYear when '' THEN '' else @useYear END)
		AND (CASE @modlNm when '' THEN '' ELSE m.modl_Nm END) like (CASE @modlNm when '' THEN '' ELSE @modlNm END)

It appeared to me that by getting those where clauses in the join, I would limit the calculations. So the joins where similar to this:

inner join paint_cd p	
	on c.clr_id = p.clr_id
	AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END)

The result though, was exactly the same.
So, where in my thinking am I wrong and what is the correct way to go about this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 08:12:51

Will you be passing % containing values for all the parameters?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-03 : 08:18:31

Re-write those optional restrictions like this:

Where
 n.regn_cd = CASE @regn when '' THEN n.regn_cd ELSE @regn END
 And ...

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 08:22:27

Thanks for the quick reply visakh16.

The params indeed include the % signs.
This is not the whole procedure.
First I get a set of string params, together with a set of tinyint params. I have a quick routine that, based on the tinyint adds the % before, after, around or nowhere to the strings.

I know that = would be faster then like, but it would take away the ability to do a startswith, contains or endswith.

Is there any other way to improve the query?

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 08:32:28

Hi harsh_athalye
Thanks for the reply.
I altered my where clause as you said (changed the = to like though).
That already gives me a profit of 7s :)
The query still takes several seconds though.

Is this the most optimal way of joining all the tables?
Or are there any other improvements I should know about?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 08:45:53

LIKE is never a quick operation. Only if you write 'Yak%' you can make use of index.
If you write '%Yak%' or '%Yak' or '%Y%a%k' you can never use index.

E 12°55'05.25"
N 56°04'39.16"

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 09:10:26

I see.
So basically, I have to review my busines logic, or settle with this.

Thanks for the replies.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-06-03 : 09:32:22

Possibly an indexed view to materialize the 7 joins ... keep in mind though that whatever performance you gain on this select query, you'll lose on other DML (insert/update/delete) since the indexed view will need to be maintained.

Jay
to here knows when

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 09:38:57

Thanks Page47.

It could be an option. I have to look into that.

Also, I removed the like-s and put in =
Still performance is rather slow (7s)

I can't shake the feeling that this should work better.

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-06-03 : 10:00:18

I checked for the view solution.

I let the generation run for several minutes.
Rows just kept adding and adding (was at around 1mil)
Don't think this is an option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources