Help me make this query run faster!

Hi all:

looking for a better way to join these tables so that it doesn't take 30 seconds for the query to run.

SELECT DISTINCT mfg.ManufacturerID, mfg.ManufacturerDesc FROM vwManufacturer mfg
INNER JOIN vwModel mod ON mfg.ManufacturerID = mod.ManufacturerID
INNER JOIN vwProductLine prod ON mod.ProductLineID = prod.ProductLineID
INNER JOIN vwIntModelRam intram ON mod.ModelID = intram.ModelID
INNER JOIN vwPartnerSKUs sku ON sku.CorsairRamID = intram.RamID
WHERE prod.ProductLineDesc = 'motherboards' AND sku.PartnerID=5

it's strange, because the partner query (different data set) only takes a few seconds:

SELECT DISTINCT mfg.PK_ID, mfg.Name FROM vwCNETManufacturers mfg
INNER JOIN vwCNETModels mod ON mfg.PK_ID = mod.FK_ManufacturerID
INNER JOIN vwIntCorsairCNETRam intram ON mod.PK_ID = intram.ModelID
INNER JOIN vwPartnerSKUs sku ON sku.CorsairRamID = intram.CorsairRamID
WHERE mod.FK_Class =100 AND sku.PartnerID=5 ORDER BY mfg.Name

the only extra join I'm doing is the product line table..

Any advice?
Mitchell_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ram2098Commented:
Not sure why you are comparing both of them..but both are using totally different views altogether...(may be I'm overlookiing something).

Also, use DISTINCT if it is necessary.
0
Mitchell_Author Commented:
I am using a distinct :)

I wasn't really comparing.. more just showing that a query with similar structure is much faster.
0
ram2098Commented:
I mean..avoid DISTINCT if it is not necessary..it will have impact on the performance :)

Your query looks simple and cleaner..only thing you might have to look at is...the views you are using in the query and see whether you can tune them further.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

etmendzCommented:
DISTINCT is slow! Instead of using DISTINCT, try GROUP BY which should give the same result but faster:

SELECT mfg.ManufacturerID, mfg.ManufacturerDesc
FROM vwManufacturer mfg
INNER JOIN vwModel mod ON mfg.ManufacturerID = mod.ManufacturerID
INNER JOIN vwProductLine prod ON mod.ProductLineID = prod.ProductLineID
INNER JOIN vwIntModelRam intram ON mod.ModelID = intram.ModelID
INNER JOIN vwPartnerSKUs sku ON sku.CorsairRamID = intram.RamID
WHERE prod.ProductLineDesc = 'motherboards' AND sku.PartnerID=5
GROUP BY mfg.ManufacturerID, mfg.ManufacturerDesc

I am not sure why you use a lot of views but it is safe to say that views are also slow (I/O related stuffs). You might want to consider using derived tables which are faster because they are created in memory. Derived tables are useful enough especially if the equivaluent views are not very big (in terms # of records).

Derived tables are like views except that they default to memory rather than to disk. Derived tables are somewhat like sub-queries but derived tables are the kind used as the table data type. For example:

SELECT h.hdrID, d.dtlID, d.itmID, d.itmName
FROM Header h
INNER JOIN (
----- start derived table block
   SELECT d.dtlID, d.hdrID, d.itmID, i.itmName
   FROM Detail d
   INNER JOIN Item i ON d.itmID = i.itemID
----- end derived table block
) d ON h.hdrID = d.hdrID

You use derived tables to create "tables" that does not exist but which you can "virtually" make available when needed -- on the fly.

Other things to read about with regards to tables in memory:

   - table returning user-defined functions
   - table data type (vs. temporary tables)

These features are in SQL Server 2000.

Have fun...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
muzzy2003Commented:
If there are no foreign-key constraints in the underlying database tables corresponding to the joins you make, then you should ensure that there are indexes on the relevant columns (for instance, the ManufacturerID in the Model table). And yes, it would be better to use the tables rather than the joins, though the query optimizer will handle that for you to some extent by using the indexes on the underlying tables where they exist.
0
LowfatspreadCommented:
the way to tackle this is to look at teh execution plan of the statement giving you a problem ....

> use query analyser...

also consider usng the index wizard


the basic choices come down to adding / using the best indexes,
removing any redundant operations...

your using the views but do they do background thinks which aren't necessary in your actual statement?

e.g. inner join to a table you have no interest in (checking)

the other question to ask is if the select gives multiple rows without the distinct
what other criteria could you specify which would either eliminate the DISTINCT or remove most of the duplication
e.g. an exists, or group by at some level...


 

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.