SQL Query taking WAY too long

Question

I have a query that's taking way too long.

There's not an index on any column and I'm pretty sure the way the OR are acting in this are making this too hard on the server.

This is a view I have and I'm making a SELECT * on this view that is taking 4 minutes to complete.

After revision, the query that I'm doing on this view is taking the most time.

SELECT * FROM Penny_Assoc_PCB WHERE PRODUCT_ID=68 ORDER BY RECORD_DT, ASSOCIATION_TYPE

/***** Here is the execution plan *******/ https://www.brentozar.com/pastetheplan/?id=Bki03eIHK

SELECT dbo.synfact_record.RECORD_ID
    ,dbo.synfact_record.PART_ID
    ,dbo.synfact_record.RECORD_DT
    ,dbo.synfact_association.ASSOCIATION_PART_A
    ,dbo.synfact_association.ASSOCIATION_PART_B
    ,dbo.synfact_association.ASSOCIATION_TYPE
    ,dbo.synfact_association.ASSOCIATION_ID
    ,dbo.synfact_record.PRODUCT_ID
FROM dbo.synfact_association
INNER JOIN dbo.synfact_record ON dbo.synfact_association.RECORD_ID = dbo.synfact_record.RECORD_ID
WHERE (
        dbo.synfact_record.PART_ID IN (
            SELECT PART_ID
            FROM dbo.synfact_record AS synfact_record_1
            WHERE (RECORD_STATUS = 1)
                AND (RECORD_TYPE = 0)
            )
        )

    AND dbo.synfact_record.PRODUCT_ID IN(
        8,
        9,
        10,
        15,
        27,
        31,
        34,
        56,
        60,
        61,
        62,
        66,
        67,
        68)
    AND (dbo.synfact_record.RECORD_ID > 499)
    AND (dbo.synfact_record.RECORD_STATUS = 1)
GROUP BY dbo.synfact_record.RECORD_ID
    ,dbo.synfact_record.PART_ID
    ,dbo.synfact_record.RECORD_DT
    ,dbo.synfact_association.ASSOCIATION_PART_A
    ,dbo.synfact_association.ASSOCIATION_PART_B
    ,dbo.synfact_association.ASSOCIATION_TYPE
    ,dbo.synfact_association.ASSOCIATION_ID
    ,dbo.synfact_record.PRODUCT_ID
    ,dbo.synfact_record.RECORD_STATUS

*Please please please*.... Format your code and alias your tables. With respect, that is a mess of a unreadable text. [3+ part naming on Columns will be Deprecated](https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/) anyway, so you *really* shouldn't be doing it. — Thom A, Oct 14 '21 at 18:14
At a very minimum you should [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Also try to read it yourself, maybe you can figure out the performance issue(s) with your query. Finally include the [schema DDL](https://en.wikipedia.org/wiki/Data_definition_language) along with the query you are executing. — Igor, Oct 14 '21 at 18:14
Please share the query plan via https://brentozar.com/pastetheplan. Are all the tables unindexed, even a clustered index? — Charlieface, Oct 14 '21 at 18:15
Why do you have a `GROUP BY` when you have no aggregate functions and why are those clauses in the `HAVING` and not the `WHERE`? — Thom A, Oct 14 '21 at 18:16
Actually, why all those `OR`s when you could just use an `IN`... Checking `(dbo.synfact_record.RECORD_STATUS = 1)` twice is a little pointless too; just once is fine. — Thom A, Oct 14 '21 at 18:22
I'd be curious to know if the bracketing is actually correct anyway — Charlieface, Oct 14 '21 at 18:27
Thank you guys I'll update after a little bit of work. It's not my code I'm starting in this company so thanks for bearing with me — JoeBackstab, Oct 14 '21 at 18:49
You've added what looks like the estimated plan, not *actual* plan. — Stu, Oct 14 '21 at 18:57
The `HAVING` condition is a mix of 3 `AND` operators and 1 `OR` operator at the same (outer) parenthesis level. This needs some serious reverse-engineering and then refactoring. Maybe there should be a law against companies hiring new people to maintain their low-quality legacy queries... — Ruud Helderman, Oct 14 '21 at 19:20
Thanks everyone! The cleanup of that helped a lot and I did some index and it completly dropped the execution time. I'd love to like all you comments but I don't have enough reputation. Thanks for taking the time — JoeBackstab, Oct 14 '21 at 19:41

score 1 · Accepted Answer · answered Oct 14 '21 at 20:11

You can substantially simplify your query.

I have removed the GROUP BY, which was acting as a giant DISTINCT with no aggregation. If you get duplicates, I suggest you put more thought into your join. Perhaps you need a better join condition, or a top-1-per-group.

SELECT r.RECORD_ID,
       r.PART_ID,
       r.RECORD_DT,
       a.ASSOCIATION_PART_A,
       a.ASSOCIATION_PART_B, 
       a.ASSOCIATION_TYPE,
       r.ASSOCIATION_ID,
       r.PRODUCT_ID
FROM
    dbo.synfact_association AS a
INNER JOIN
    dbo.synfact_record AS r ON a.RECORD_ID = r.RECORD_ID
WHERE
       (r.PART_ID IN (
            SELECT PART_ID
            FROM dbo.synfact_record AS r1
            WHERE (r1.RECORD_STATUS = 1)
                AND (r1.RECORD_TYPE = 0)
            )
        )
    AND r.PRODUCT_ID IN
        (8,9,10,15,27,31,34,56,60,61,62,67,68)
    AND (r.RECORD_ID > 499)
    AND (r.RECORD_STATUS = 1);

Based on this query alone, I would recommend the following indexes:

CREATE CLUSTERED INDEX IX_synfact_association_RECORD_ID
  ON synfact_association (RECORD_ID)
-- for non clustered add: INCLUDE (ASSOCIATION_PART_A, ASSOCIATION_PART_B, ASSOCIATION_TYPE)

CREATE CLUSTERED INDEX IX_synfact_record_RECORD_ID
  ON synfact_record (RECORD_STATUS, RECORD_ID)
-- for non clustered add: INCLUDE (PART_ID, RECORD_DT, ASSOCIATION_ID, PRODUCT_ID)

In this second index it maybe worth swapping RECORD_ID and PART_ID

CREATE NONCLUSTERED INDEX IX_synfact_record_RECORD_TYPE
  ON synfact_record (RECORD_STATUS, RECORD_TYPE, PART_ID)

This last index is necessary for the IN clause

Thank you so much. The indexing with the clean up and the group by removed really did it. I have two questions on this website and both of them you're the replier, thank you very much! — JoeBackstab, Oct 14 '21 at 20:22

SQL Query taking WAY too long

1 Answers1