Query Performance - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Performance

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-07-14 : 10:58:24

Hi,

I have two tables -

Table1 - Customer
CustomerID int Primary Key
CustomerName varchar(100)
Age int

Table2 - Preference
PreferenceID int Primary Key
PreferenceOpt
CustomerID

Preference.CustomerID is foreign key references Customer.CustomerID

Our system uses '-M-' in PreferenceOpt to identify the major preference of each customer (i know it's a little strange). There is only one preference with '-M-' for each customer. But there are customers that don't have major preference. I'm asked to create a stored procedure to return the major preference for each customer in a certain age range. For those without major preference, return top 1 PreferenceOpt.

I have got one as following -

------------------------------------------------------------
create procedure Get_Customer_Preference
@Age1 int,
@Age2 int
as
select A.CustomerID,A.CustomerName,
coalesce(B.PreferenceOpt,C.PreferenceOpt) as PreferenceOpt
from Customer A left join Preference B on A.CustomerID = B.CustomerID
and B.PreferenceName like '%-M-%'
left join (select * from Preference where PreferenceID in
(select min(PreferenceID) from Preference group by CustomerID)) C
on A.CustomerID = C.CustomerID
where A.Age between @Age1 and @Age2

return @@error
------------------------------------------------------------

The problem is the stored procedure takes 5 minutes to get the resultset. When I looked at the execution plan, the derived table with GROUP BY is the one that cost most. Can anyone tell me how to tune it up?

Thanks,

George

mr_mist
Grunnio

1870 Posts

Posted - 2004-07-14 : 11:06:47

On first glance the things I would look at would be the SELECT * (cut it down to just the needed columns) and the like '%-M-%'. If you search for anything that starts with the wildcard then the use of indexes will be compromised or eliminated for that statement. Essentially it would boil down to a design issue here, but you may be able to get around it by introducing a column into your table that copies out the -M- preference, and then searching that column instead of looking in a list for -M-.

-------
Moo. :)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-14 : 11:09:41

The LIKE '%-M-%' hurts here, but a restructured derived table could help.

create procedure Get_Customer_Preference
@Age1 int,
@Age2 int
as
select	  A.CustomerID
	, A.CustomerName
	, coalesce(B.PreferenceOpt,C.PreferenceOpt) as PreferenceOpt 
	
from 	Customer A left join Preference B 
	  on 	A.CustomerID = B.CustomerID
	  and 	B.PreferenceName like '%-M-%'
	left join	(
		SELECT	CustomerID, PreferenceOpt
		FROM	Preference x JOIN
			(
			SELECT	CustomerID, MIN(PreferenceID) 'PreferenceID'
			FROM	Preference
			GROUP BY CustomerID
			) y 
			  on 	x.CustomerID = y.CustomerID
			  and 	x.PreferenceID = y.PreferenceID
		) C
	  on A.CustomerID = C.CustomerID

where	A.Age between @Age1 and @Age2

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2004-07-14 : 16:18:41

Thanks all.

The performance is much better by adding where clause to the subquery -

select A.CustomerID,A.CustomerName,
coalesce(B.PreferenceOpt,C.PreferenceOpt) as PreferenceOpt
from Customer A left join Preference B on A.CustomerID = B.CustomerID
and B.PreferenceOpt like '%-M-%'
left join (select * from Preference where PreferenceID in
(select min(P.PreferenceID) as Preference from Preference P join Customer CUST
on P.CustomerID = CUST.CustomerID
where C.Age between @Age1 and @Age2
group by P.CustomerID)) C
on A.CustomerID = C.CustomerID
where A.Age between @Age1 and @Age2

It looks a little ugly. But it's the best I can do.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-14 : 23:42:43

Get rid of the select *. That's bad, especially in a subquery. Also, change the IN to EXISTS.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources