asked
Faster method to Aggregat large of data using MAX
We have a table A with 25 million records and a table B with 9000 records. We would like to retrieve the Maximum date value from Table A for every record in Table B. For Example: Table SAMPLE_A has the following columns ID, B_ID StartDateID, EndDateID, Record_Create_Date, Record_Create_User and 107 other columns in the table SAMPLE_A key columns such as ID, User_ID and 3 other columns are Indexed while the B_ID is not indexed Table SAMPLE_B has B_ID User_Name Record_Create_Date, Record_Create_User The query we currently use is: - INSERT INTO #GetMaxStartDate SELECT TBL_A.B_ID, MAX(TBL_A.StartDateID) SD_ID FROM SAMPLE_A (NOLOCK) TBL_A WHERE TBL_A.B_ID IN (SELECT B_ID FROM SAMPLE_B) GROUP BY TBL_A.B_ID The query above takes about 15 minutes to execute. Is there a faster way to get the data. We are looking at a peak time of less than a minute to retrieve this data. Please do help me in this regard...
Can you please provide the execution plan for the query? Secondly you could alter the where clause to reflect: INSERT INTO #GetMaxStartDate SELECT TBL_A.B_ID, MAX(TBL_A.StartDateID) SD_ID FROM SAMPLE_A TBL_A INNER JOIN Sample_B tbl_B on tbl_a.B_ID = tbl_B.B_ID GROUP BY TBL_A.B_ID I would also suspect that you're missing an index on B_ID & StartDateID on Sample_A however seeing the execution plan can help to confirm that. Also, unless you are comfortable with potential dirty reads & inconsistent data, remove the (NOLOCK). If you are aware of the dangers of NOLOCK, then feel free to leave it in place. Totally up to you. =) Hope this helps!
I think that's a pretty accurate series of estimates based on very little information. Well done.
14 People are following this question.
