I have a read query (not a stored procedure) within my Perl script for choosing the latest TestHistoryID
value for a Test against the TestID
. A Test (or simply a piece of code) runs multiple times as a part of regression process. While the TestID
remains same for a test, the test receives a new TestHistoryID
after each run. The pass/fail status, time-took-for-completing-run etc is stored against TestHistoryID
(TestHistoryID
is an integer that increases monotonously).
Since there are multiple TestHistoryID
against each TestID
(DB keeps history of last 50 runs and deletes the oldest entries if it goes beyond 50), I want to get the resultset containing latest TestHistoryIDs
against each TestID
.
I used the following partition query to obtain the row with the highest TestHistoryID
(followed this question on stackoverflow to create the query - How to query SQL Table and remove duplicate rows from a result set)
SELECT TestID, TestHistoryID, TestLabel
FROM
(
SELECT
TestID,
TestHistoryID,
TestLabel,
row_number() over(partition by TestID order by TestHistoryID DESC) rn
FROM TestHistoryView
WHERE TestID IN (@test_ids)
) content where rn = 1
@test_ids contains huge number array e.g. 504954, 504955, 504956, 504957, 504958, 504959, 504960, 504961, 504962 .... (40k numbers)
Snap of Table columns here that contains duplicates, I need to remove the duplicates and get the latest value of TestHistoryID
for each TestID
(https://i.stack.imgur.com/nJ59C.png)
The query works for up to approximately 10k TestID
s, if I try with 15k, I get the following error:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
I need help creating an efficient query that doesn't eat up all the resources for 40k parameters passed and still returns the intended results. Thanks a lot in advance.