Clever Geek Handbook

Need to speed up this query in SQL Server

SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value
    FROM prodtree_element pe
    LEFT JOIN prodtree_link pl
        ON pe.prodtree_element_id = pl.to_prodtree_node_id
    LEFT JOIN line li
        ON pe.line_code = li.line_code
    INNER JOIN attribute_values av
        ON av.attribute_definition_id = #statusCode# 
    LEFT JOIN attribute_values av2
        ON pe.prodtree_element_id = av.prodtree_element_id
    WHERE pe.prodtree_element_func_type <> 'WIZARD'
        AND pe.prodtree_element_topo_type = 'NODE'

"# statusCode #" is a static identifier that corresponds to an identifier in the attribute definition table (say 22 for an argument). The problem is that the request has some serious problems completing any reasonable amount of time. The big problem is that I need this to finish early, but the number of records is huge that I need to backtrack (about 30-50,000). I need data from multiple tables where it starts to slow down. This is just part of what I need, I also need whole other tables that are worth the data corresponding to the current "prodtree_elment_id".

I'm using ColdFusion, but even running a query directly in SQL Server 2005 creates a 15-30+ minute wait for that query (if it even ends). Is there any conceivable way to speed up this request so that it takes no more than 5 minutes?

+1


source to share


6 answers 


INNER JOIN attribute_values av
    ON av.attribute_definition_id = #statusCode# 
LEFT JOIN attribute_values av2
    ON pe.prodtree_element_id = av.prodtree_element_id

This is problem. There is a cross join between pe and av followed by an outer join on a cross join. You're in luck it only takes 30 minutes :-)



I think you want this:

SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value
FROM prodtree_element pe
LEFT JOIN prodtree_link pl
    ON pe.prodtree_element_id = pl.to_prodtree_node_id
LEFT JOIN line li
    ON pe.line_code = li.line_code
--replacement
LEFT JOIN
attribute_values av 
         ON pe.prodtree_element_id = av.prodtree_element_id AND
         av.attribute_definition_id = #statusCode# 
--end replacement
WHERE pe.prodtree_element_func_type <> 'WIZARD'
    AND pe.prodtree_element_topo_type = 'NODE'
+9


source


Without knowing DDL, it is very difficult to test it. Lines 30-50K should only take a few seconds.

Try switching the order of the where order. You should probably have this implemented



 INNER JOIN attribute_values av
        ON av.attribute_definition_id = #statusCode#

in the where clause.

0


source


First of all, I would suggest that you run it through the sql optimizer utility in the corporate manager providing installation. It usually offers indexes and things like that which can have a positive impact on query speed.

Other things to consider would split the request. At first glance, it seems that you are reading all the elements of the product that have a certain attribute corresponding to what you give (or something like that). I would suggest maybe:

select * from [bigLongjoin to producttree_element]
where prodtree_element_id
in(
select prodtree_element_id from 
  attribute_values where attribute_definition_id = #statusCode#)

Running it in Enterprise Manager with the query plan displayed can also show you where the bottle neck is

0


source


You can search millions of records in a few seconds with good optimization. While StingyJack is right about this without knowing the DDL, optimizing any query is tough.

What to do, although the execution plan is looked at when optimizing the query. Nested loops and the like are bad. Also make sure you are fully indexed. You don't mention anything about the indexes of the tables in question. Without indexes 30 - 50k rows can take a while with so many joins.

0


source


make sure all your ids are indexes, also if you can match the following:

pe.prodtree_element_func_type <> 'WIZARD'
        AND pe.prodtree_element_topo_type = 'NODE'

to be something like

pe.prodtree_element_func_type_ID <> 1
            AND pe.prodtree_element_topo_type_ID = 2

to reduce string comparisons, which takes longer to complete

0


source 


SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value
    FROM prodtree_element pe
    LEFT JOIN prodtree_link pl
        ON (pe.prodtree_element_id = pl.to_prodtree_node_id)
    LEFT JOIN line li
        ON (pe.line_code = li.line_code)
    LEFT JOIN attribute_values av2
        ON (pe.prodtree_element_id IN (SELECT av.prodtree_element_id FROM attribute_values av WHERE av.attribute_definition_id = #statusCode#))
    WHERE pe.prodtree_element_func_type <> 'WIZARD'
        AND pe.prodtree_element_topo_type = 'NODE'

gbn nailed it I guess. Even though you restrict the INNER JOIN with a value_attribute to a specific value, it still doesn't join with your main table or its relationships. So even if you get query results, I assume there are too many.

Depending on what you were planning and how your data is in the attribute_values ​​table, his query or mine will probably be faster.

0


source






More articles:


All Articles