December 16, 2013 at 10:54 am
Hello I am trying to pick a few ideas up on the most efficient way to gather about 400 million records.....I was looking for ideas on improving in the way of efficiency and speed.
WITH C
AS (
SELECT E.EmpID,
T.TrainingDateTime,
ROW_NUMBER() OVER
(PARTITION BY E.EmpID ORDER BY T.TrainingDateTime DESC) AS RowNumber
FROM emp.employee AS E
JOIN emp.training AS T
ON E.EmpID=T.EmpID)
SELECT * INTO emp.recent_training FROM C WHERE RowNumber <=10
December 16, 2013 at 11:06 am
Both queries are not equivalent. The first will return 10 rows and the second one will return 10 rows per EmpID.
December 16, 2013 at 11:44 am
Sorry Luis....I edited the post. Is there a more efficient way/Faster option to produce results than what I have listed now?
December 16, 2013 at 12:03 pm
Maybe I'm missing something but it seems that you're not using any column from table employee. You could remove it. If you need the information, I would join it out of the CTE. I left this last option commented so you can test by yourself. I'm not sure if will actually help the performance but you can try. I can't find any better option at the time.
WITH C
AS (
SELECT T.EmpID,
T.TrainingDateTime,
ROW_NUMBER() OVER
(PARTITION BY T.EmpID ORDER BY T.TrainingDateTime DESC) AS RowNumber
FROM emp.training AS T)
SELECT *
INTO emp.recent_training
FROM C
--JOIN emp.employee AS E ON C.EmpID = E.EmpID
WHERE RowNumber <=10
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply