speed up 6 consecutive left outer joins

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)

speed up 6 consecutive left outer joins

Author

Topic

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-28 : 11:32:26

Hi,

The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.

declare @keyWord varchar(40)
set @keyWord =  'merc'
set NOCOUNT on

SELECT distinct clr.clr_id
FROM CLR
	LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID
	LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID
	LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID
	LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID
	LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID
WHERE co.long_nm like '%'+@keyWord+'%'
	OR clr_nm.clr_nm like '%'+@keyWord+'%'
	OR clr_use_yr.yr_num like '%'+@keyWord+'%'
	OR paint_cd.paint_cd like '%'+@keyWord+'%'
	OR modl.modl_nm like '%'+@keyWord+'%'

The query runs at 3secs.
Could I improve the query somehow?
I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 13:13:54

Post the DDL for all 6 tables including the indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 16:51:42

Are you sure you need LEFT JOIN?
As the query is written now, it does matter if there is a match in any of the LEFT JOINed tables because the way you have written the WHERE clause.
Try replace LEFT JOIN with INNER JOIN to see if there is a performance gain.

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

bfoster
Starting Member

30 Posts

Posted - 2008-04-28 : 18:26:41

It depends on what you are actually intending to do in your query, but I thought your intent might be along the lines of this. You'll have to check if this returns the intended results and is any faster than your original query.

SELECT distinct clr.clr_id
FROM clr
WHERE exists(select 1 from co WHERE co.long_nm like '%'+@keyWord+'%' and co.co_id = clr.co_id)
OR exists(select 1 from clr_nm WHERE clr_nm.clr_nm like '%'+@keyWord+'%' and CLR.CLR_ID = CLR_NM.CLR_ID)
OR exists(select 1 from clr_use_yr LEFT OUTER JOIN modl ON CLR_USE_YR.MODL_ID = MODL.MODL_ID WHERE (clr_use_yr.yr_num like '%'+@keyWord+'%' or modl.modl_nm like '%'+@keyWord+'%') and CLR.CLR_ID = clr_use_yr.CLR_ID)
OR exists(select 1 from paint_cd WHERE paint_cd.paint_cd like '%'+@keyWord+'%' and CLR.CLR_ID = PAINT_CD.CLR_ID)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 18:33:52

Always use select * when using exists. It is faster than anything else.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-29 : 02:59:06

Hi all,
Thanks for the replies!
I haven't been into SQL that much. At least not when performance mattered. So it's pretty possible I'm making loads of noob mistakes here. I hope this forum can help me improve this skill.

@tkizer: I read I should avoid to use * in queries as that needs another call to get the columns. This sounds logical to me, but I checked just to be sure. When I run the query without just clr_id it returns in about 2secs. With * it gives me a result in about 3secs.
Maybe the distinct makes the difference?
I don't know what a DDL is. Is it an alternative to ERD? ERD's I do know about. You can find a simplified, visio-fied version here: [url]http://users.pandora.be/boersnoes/cdbErd.jpg[/url]

@bfoster: I checked the query. It returns different results and performs at about 5 secs (ran it 8 times in a row).

@Peso: If I'm not mistaking, a left join is needed here. Otherwise, if a colour (clr) would for example have a name (clr_nm) but no paint code (paint_cd) it would get excluded from the result. This is not the result I'm looking for.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 05:14:14

Yes, I understand that.
But the way the WHERE is written, the query is treated INNER JOIN.
Then you can write INNER JOIN instead.

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

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-29 : 05:29:09

Could you elaborate on that?
Is the where clause wrong? How should it be then?

What I think should be happening is a table

clr_id      long_nm                   clr_nm                                   yr_num      modl_nm              paint_cd
----------- ------------------------- ---------------------------------------- ----------- -------------------- --------------------
11619       MERCEDES                  ANTHRACITE GRAY                          1970        ALL MODELS           NULL
11619       MERCEDES                  ANTHRACITE GRAY                          1971        ALL MODELS           NULL
11619       MERCEDES                  ANTHRACITE GRAY                          1972        ALL MODELS           NULL
etc...

So in each row it should at least contain one value that has my @keyWord in it.
Then from that I select only the clr_id since that is the only thing that interests me (currently).
But how I join them now a lot of duplicates get generated, so I have to distinct them.
I imagine this creates redundant steps. First I add them and them I distinct them.

Maybe I should do it the opposite way? Take all the colours and check in all the other fields if the colour has a matching field? Would that be more performant? How could I go about that?

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-29 : 05:34:21

off topic:
Thanks for saving the Yaks. A reference to Ren & Stimpy maybe?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 06:02:26

Try this

SELECT DISTINCT	Clr.Clr_ID,
FROM		Clr
LEFT JOIN	Co ON Co.Co_ID = Clr.Co_ID
			AND Co.Long_Nm LIKE '%' + @Keyword + '%'
LEFT JOIN	Clr_Nm ON Clr_Nm.Clr_ID = Clr.Clr_ID
			AND Clr_Nm.Clr_Nm LIKE '%' + @Keyword + '%'
LEFT JOIN	Clr_Use_Yr ON Clr_Use_Yr.Clr_ID = Clr.Clr_ID
			AND Clr_Use_Yr.Yr_Num LIKE '%' + @Keyword + '%'
LEFT JOIN	Modl ON Modl.Modl_ID = Clr_Use_Yr.Modl_ID
			AND Modl.Modl_Nm LIKE '%' + @Keyword + '%'
LEFT JOIN	Paint_Cd ON Paint_Cd.Clr_ID = Clr.Clr_ID
			AND Paint_Cd.Paint_Cd LIKE '%' + @Keyword + '%'
WHERE		Paint_Cd.Clr_ID IS NOT NULL
		OR Modl.Modl_ID IS NOT NULL
		OR Clr_Use_Yr.Clr_ID IS NOT NULL
		OR Clr_Nm.Clr_ID IS NOT NULL
		OR Co.Co_ID IS NOT NULL

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

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-29 : 06:15:45

Yes! Thank you.
That gives back the correct amount of records and processes in 0secs :)

Could you tell me what exactly I did wrong, what I should note etc?
I want to learn why my query was so bad.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 06:19:05

The basic idea is to get all Clr_ID from Clr table.
For a number of "subtables" you want to see if there is a match or not when @Keyword search is applied.

For each and one LEFT JOIN you only want to see if there is a match for a given ID and keyword pattern.

You do this for all ID columns.

And then, in the WHERE, you make sure that at least one of the "subtables" contains a value.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 06:21:34

What you did wrong was that you wrote the WHERE clause for "master" table Clr after joining to "subtables".
This allows not for NULL (no match).

Which means all id-columns had to match to get a record for Clr table, not just any one column/subtable.

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

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-29 : 06:25:10

I see.
Thanks for your support :)

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-29 : 08:07:46

What is the best approach in this situation to have multiple strings limit the results?

What I would do, but hope is too farfetched:

1) Pass in char delimited string
2) Parse string into table
3) Get result with first string
4) while (count result > 0 && more keyWords) do a query on the result
5) return the result

Or is there a way to include set of unknown size in the where clause?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 08:30:58

Make use of fnParseList to split keywords into a temporary table or table variable.
Then use this table to match underlying "subtables".

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

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-29 : 09:18:13

Thx.
I had written one myself, but yours is propably way faster :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-29 : 12:26:01

quote:
Originally posted by BorisCallens

@tkizer: I read I should avoid to use * in queries as that needs another call to get the columns. This sounds logical to me, but I checked just to be sure. When I run the query without just clr_id it returns in about 2secs. With * it gives me a result in about 3secs.
Maybe the distinct makes the difference?

I was referring to bfoster's code. You should use * instead of select 1 or anything else when using exists.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-30 : 03:15:45

Am I the only one getting an "Argument data type text is invalid for argument 1 of reverse function." error? A quick google tells me the reverse function doesn't accept text.
It points at the "SELECT @Text = REVERSE(@Text)" line.
You could make it REVERSE(CAST (@Text as varchar))

Also, why is the text datatype prefered over the ntext one?
I'm thinking of my website now. It will be localised and contain foreign chars. So it looks like a good idea to me to use ntext, not?

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-30 : 03:17:22

And that wasn't supposed to go in this browser tab.
Where the hell is the edit button in this forum?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources