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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy