0

Suppose I have a table of log changes. Each change could override to other change of the same group and same module. I want to get a list of changes along with their last change overwritten in one SELECT statement. How can I get the best performance on it?

Please use these table/column names in your answer:

  • log_changes: id, groupid, moduleid, date

I tried to follow this awesome answer: SQL join: selecting the last records in a one-to-many relationship But the performance is not so good, I am talking of a table with more than one million of rows and there are two differences (maybe for that is not the correct answer) the join is with the same table and with two columns.

I tried to use LAG function but I get a bad performance. When I talk about bad performance it means all the table is taking more that 5 seconds, I am trying to get a better time.

Edit:

If I have: 

id  groupid moduleid    date 
1   1       1           2016/09/07 
2   1       1           2016/09/06 
3   1       1           2016/09/05 
4   1       2           2016/09/07 
5   1       2           2016/09/06 
6   2       3           2016/09/07 
7   2       3           2016/09/08

The query should return: 

id  last 
1   2 
2   3 
3   (0 or null)
4   5 
5   (0 or null) 
6   7 
7   (0 or null)

Does it make sense?

Community
  • 1
  • 1
Andres GR
  • 47
  • 4
  • Your question doesn't have enough details. Show us some sample data (few dozen rows) and what the query should return. Tag with the version of SQL Server that you use. – Vladimir Baranov Aug 26 '16 at 00:45
  • If I have: id groupid moduleid date 1 1 1 2016/09/07 2 1 1 2016/09/06 3 1 1 2016/09/05 4 1 2 2016/09/07 5 1 2 2016/09/06 6 2 3 2016/09/07 7 2 3 2016/09/08 The query should return: id last 1 2 2 3 3 0 or null 4 5 5 0 or null 6 7 7 0 or null Does it make sense? – Andres GR Sep 07 '16 at 23:25
  • Please edit the question and put your sample data there. Please format it properly, so it is readable. – Vladimir Baranov Sep 08 '16 at 01:56

1 Answers1

0

If you're dealing with millions of records being evaluated, anything you do is going to take some time. How much time will depend on your hardware. If you can reduce your query with some sort of filter (that uses a supported index on the table in question) you'll get faster results regardless of the query. I think the LAG window function is going to tend to be your quickest method, but that will be impacted by whether or not you have an index that can retreive the data in an order that corresponds with your partition and order in your LAG. Having that index would allow the engine to avoid an expensive sort operation.

btberry
  • 377
  • 1
  • 7