Post reply

Query: Joining a record chronoligical with the next record from the table

  • October 18, 2009 at 4:48 pm

    #1067454

    Hi Tim,

    No, OPTION (MAXDOP 1) prevents parallelism occuring for the quirky update.

    I re-ran test on 20 million records of test data, i.e. (quirky) updating the seq and diff as per Seth's code for a given day and calculating the aggregated (grouped by employee id) sum for that day's diff execution time is coming in consistantly under 10 sec.

    Updates on the entire 20 million are taking approx 3 minutes. I'll try re-running on 10 million records tomorrow, to check out my 30 sec claim, I'd have thought this should be a linear relation, hmm... might be getting too tired to read.

    I'm not 100% sure what you are trying to achieve — what is your goal — if this isn't a good example, then what would be?

    Would it be an option to calculate the values just as needed? e.g. if your application shows details for an employee calculate the values on the request.

    Allister

    ///Edit — spelling, grammar, sense... def getting too tired 😉

  • October 19, 2009 at 4:03 am

    #1067557

    Tim,

    I'll hopefully have a chance to run some more tests today, couple of questions, just to get representative data: 30 million records, 15,000 employees, how many companies? what is the date range on Start_Trip? Level of index fragmentation?

    Cheers

    Allister

  • October 20, 2009 at 3:23 pm

    #1068552

    Thank you for all your replies. I do not have the perfect solution yet, but definitely a few new ideas. I tried ta add some good indexes on the tables and it worked. I could run the query within seconds which was a great improvement. With this index the insert of new record was slown down. So The solution lays within the indexes but I have to experiment some more with this.

    BTW the fastest query to get the time differences in this situation* **:

    * under 20.000 records from a table with 3M records

    ** with the right index and statistics

    Select t1.Employee_ID, t1.id, t1.Stop_Trip , Next_Start_Trip =

    (Select min(t2.Start_Trip) From Trips t2

    Where t1.Company_ID = t2.Company_ID

    And t1.Employee_ID = t2.Employee_ID

    And t1.Stop_Trip < t2.Start_trip)

    Execution time 3 seconds.

    Thanks.

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply