SQL Query taking WAY too long

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

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