How to speed up retrieval time of this query:

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

SQL Server Development (2000)

How to speed up retrieval time of this query:

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2007-06-01 : 09:47:04

Hi Guys!

Very simple query here, but one table is 'looking up' for information from another table based on text values rather than int values so it is taking forever to retrieve. Any idea how to speed it up?

SELECT DISTINCT p1.Manufacturer_Family_Member,
p1.Manufacturer_Family,
p1.Manufacturer,
fm1.Description,
MIN (p1.Image) AS Image
FROM dbo.Products p1
JOIN dbo.FamilyMembers fm1 ON fm1.Manufacturer_Family = p1.Manufacturer_Family
JOIN dbo.FamilyMembers fm2 ON fm1.Manufacturer_Family_Member = p1.Manufacturer_Family_Member
GROUP BY p1.Manufacturer, p1.Manufacturer_Family, p1.Manufacturer_Family_Member, fm1.Description
ORDER BY p1.Manufacturer_Family_Member

The problem is where the two JOINs are. "Manufacturer_Family" would retrieve a value like "TX Series" and "Manufacturer_Family_Member" would a retrieve value like "15.4 inch". Combined they would display "TX Series 15.4 inch" (its a laptop made by Sony in case you were wondering!).

So the dbo.Products table simply looks up in the dbo.FamilyMembers table for the corresponding Manufacturer_Family and Manufacturer_Family_Member values and then grabs the Description field value. This look up process is taking forever to complete. Any ideas how to speed it up?

Thanks for any replies, they are much appreciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-01 : 10:59:58

why are you joing on dbo.FamilyMembers twice??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

token
Posting Yak Master

133 Posts

Posted - 2007-06-01 : 11:27:17

quote:
Originally posted by spirit1

why are you joing on dbo.FamilyMembers twice??

I don't know. What should I do? I need the Products table to look at the FamilyMembers where both Products.Manufacturer_Family and Products.Manufacturer_Family_Member match the same values in the FamilyMembers table.

Have I done it wrong?

Kristen
Test

22859 Posts

Posted - 2007-06-01 : 11:32:58

"why are you joing on dbo.FamilyMembers twice??"

Looks like a substitute for EXISTS to me ...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-01 : 11:39:17

You can use just single JOIN with two conditions combined with AND operator.

FROM dbo.Products p1
JOIN dbo.FamilyMembers fm1 ON fm1.Manufacturer_Family = p1.Manufacturer_Family and fm1.Manufacturer_Family_Member = p1.Manufacturer_Family_Member

Moreover, there is CROSS JOIN happening there (causing all the problem) since in the second Join you are not referring alias fm2 anywhere in the join condition !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

token
Posting Yak Master

133 Posts

Posted - 2007-06-01 : 12:13:09

Awesome it worked harsh_athalye. I am very thankful to you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources