November 18, 2010 at 8:20 pm
Hi,
Can you please help me tune a query?
I have a dimension table PRODUCT which is having product data with millions of rows and another table (fact table) with more than millions of rows and it is called PRODUCT TRANSACTION.
The PRODUCT TRANSACTION table has multiple rows for a given product with multiple issue dates. EX.
PRODUCT ISSUE DATE
A 10/28/2008
B 11/02/2008
C 05/28/2008
D 06/27/2008
E 07/28/2008
I am doing JOIN between PRODUCT (dimension table) and fact (PRODUCT TRANSACTION TABLE) and just want a minimum of ISSUE DATE (means only one record from fact table for each product in dimension table).
I have written query as
SELECT PRODUCT_ID FROM
PRODUCT_TRANSACTION AS PT
INNER JOIN PRODUCT P ON
P.PRODUCT_ID IN (SELECT PT.PRODUCT_ID FROM
(
SELECT PT1.PRODUCT_ID, MIN (PT1.ISSUE_DATE) AS MINC
FROM PRODUCT_TRANSACTION PT1
GROUP BY PT1.PRODUCT_ID) B)
This query takes forever. Is there a better way to accomplish this?
Please suggest.
November 18, 2010 at 9:03 pm
Indexes? Actual Execution Plan?
Please read the second article I reference below in my signature block, it will help with the information you need to post for us to help you with the performance problem. Please remember that we are volunteers and can't see what you see.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply