Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help me make this query run faster!

Posted on 2004-11-22
6
Medium Priority
?
319 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:Mitchell_
6 Comments
 
LVL 11

Expert Comment

by:ram2098
ID: 12651163
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
 

Author Comment

by:Mitchell_
ID: 12651170
I am using a distinct :)

I wasn't really comparing.. more just showing that a query with similar structure is much faster.
0
 
LVL 11

Expert Comment

by:ram2098
ID: 12651245
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Accepted Solution

by:
etmendz earned 2000 total points
ID: 12651833
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
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12652151
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12652477
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question