Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Unanswered: Query optimisation help needed!!

    hi i am really stuck with a query i have been somewhat forced in to using.

    to try and explain simply i used to have a table with entries that consited of a timestamp and a cycle time since the last entry. so basically a list of cycle times. now i been forced to accept the data in a new manor.

    the new timestamp, value. value is either 1 or 0. i have created a view to loop through the table to find the 1's and calc the cycle time between the time stamps. this is proving to be far too slow!! can someone offer any advice on optimising this query???

    SELECT _TIMESTAMP AS time_stamp, CONVERT(float, DATEDIFF(ms,
    (SELECT MAX(_TIMESTAMP) AS Expr1
    FROM dbo.t_idi_10
    WHERE (_VALUE = 1) AND (_TIMESTAMP < T._TIMESTAMP)), _TIMESTAMP)) / 1000 AS cycle_time, _VALUE
    FROM dbo.t_idi_10 AS T
    WHERE (_VALUE = '1')
    i have a stored proceedure that retrived data from the old table structure that had the data in the required format. so now to accomodate the new data structure i have simply replaced the table referance in the stored proceedure with the view above. essentially reformatting the data in to the original structure!! makes sence i hope!!!

    this is far to slow and often times out as it processes 2k - 3k rows frequently!!!

    any suggestions on how to process this faster are very welcome

    cheers,

    Matt

  2. #2
    Join Date
    Jul 2010
    Posts
    8
    table structure and sample data if it helps??


    ID, Area, _value, _timestamp, quality
    220184, Side Panels.Side Panel Presses.Counter 7KG, 0, 07/07/2010 13:42:41, 192
    220185, Side Panels.Side Panel Presses.Counter 7KG, 1, 07/07/2010 13:42:48, 192
    220186, Side Panels.Side Panel Presses.Counter 7KG, 0, 07/07/2010 13:42:49, 192
    220187, Side Panels.Side Panel Presses.Counter 7KG, 1, 07/07/2010 13:42:55, 192

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's the result to be from that? Also what version of SQL Server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2010
    Posts
    8
    view result (this is the old table structure basically) sql 2000

    _timestamp, cycle time, _value
    06/07/2010 06:09:50, 8.78, 1
    06/07/2010 06:09:58, 8.483, 1
    06/07/2010 06:10:05, 6.563, 1
    06/07/2010 06:10:12, 6.686, 1
    06/07/2010 07:10:48, 3636.203, 1
    06/07/2010 07:10:55, 7.5, 1
    06/07/2010 07:11:03, 8.046, 1
    06/07/2010 07:11:11, 7.89, 1
    06/07/2010 07:26:59, 947.453, 1
    06/07/2010 07:27:06, 7.373, 1
    06/07/2010 07:27:14, 7.393, 1
    06/07/2010 07:27:21, 7.31, 1

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's not the result for the data you posted though right?
    SQL 2000 severely reduces your options I'm afraid.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2010
    Posts
    8
    yes that is the result

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    It is not possible to produce the result set of 12 rows (post #4)
    using your query (post #1) and sample data provided (post #2)

  8. #8
    Join Date
    Jul 2010
    Posts
    8
    Quote Originally Posted by pdreyer View Post
    It is not possible to produce the result set of 12 rows (post #4)
    using your query (post #1) and sample data provided (post #2)
    please explain why as i have 50 tables here that do work which i need to explain this to

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What he means is the expected result you give us should be for the sample data you supplied. We have sample data with no corresponding result set and a result set with no corresponding sample data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2010
    Posts
    8
    ok lets simplify then. i wont get a view to run much quicker will i when it is cycling 4k records and doing a datediff between them no???? i thought about putting in a stored proceedure that would cycle the table and do the cycletime calcs and add this in to another column but for the life of me i cannot get this quite right!!! this is where i am at!

    alter PROCEDURE [sp_insert_idi_10_data]

    as update [m_data_aq].[dbo].[t_idi_10]
    set [cycle_time]= cycle_time

    SELECT CONVERT(float, DATEDIFF(ms,
    (SELECT MAX(_TIMESTAMP) AS Expr1
    FROM [m_data_aq].[dbo].t_idi_10
    WHERE (_VALUE = 1) AND (_TIMESTAMP < T._TIMESTAMP)), _TIMESTAMP)) / 1000 AS cycle_time
    FROM [m_data_aq].[dbo].t_idi_10 AS T
    WHERE (_VALUE = '1')


    update [m_data_aq].[dbo].[t_idi_10]
    set cycle_time= [cycle_time]
    GO

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You haven't simplified - you've just repeated your first post and still not supplied sample data and corresponding result set.

    Create an index on (value, timestamp) and check the running time. Remove the index and create one on (timestamp, value) and check the running time. I expect the first to execute faster but it won't be so good for other queries.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2010
    Posts
    8
    sorry as requested:-

    table data
    265860 Side Panels.Side Panel Presses.Counter 7KG 1 13/07/2010 14:26:44 192
    265861 Side Panels.Side Panel Presses.Counter 7KG 0 13/07/2010 14:26:45 192
    265862 Side Panels.Side Panel Presses.Counter 7KG 1 13/07/2010 14:26:51 192
    265863 Side Panels.Side Panel Presses.Counter 7KG 0 13/07/2010 14:26:52 192
    265864 Side Panels.Side Panel Presses.Counter 7KG 1 13/07/2010 14:26:57 192
    265865 Side Panels.Side Panel Presses.Counter 7KG 0 13/07/2010 14:26:59 192
    265866 Side Panels.Side Panel Presses.Counter 7KG 1 13/07/2010 14:27:05 192
    265867 Side Panels.Side Panel Presses.Counter 7KG 0 13/07/2010 14:27:06 192


    view results

    13/07/2010 14:26:44 NULL 1
    13/07/2010 14:26:51 6.533 1
    13/07/2010 14:26:57 6.623 1
    13/07/2010 14:27:05 7.78 1

  13. #13
    Join Date
    Jul 2010
    Posts
    8
    i have played about with indexes and this seems to make very little difference to the query times... any further suggestions are welcome!

    Thanks,

    Matt

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post the execution plans for the two queries and your current set up?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by finalmatt View Post
    processes 2k - 3k rows
    That should not take more than a couple of seconds
    However I deduct from
    Quote Originally Posted by finalmatt View Post
    i have 50 tables here that do work
    there is a lot more to it than your simplified version showed to us
    And your sample data with ID=265860 might imply hundreds of thousands of rows

    I suggest you try something like this in your proc.

    Insert the smaller portion of data that you need to work with into a temp table
    e.g.
    Code:
    select nid=identity(int)
    , _timestamp into #t1 
    FROM t_idi_10 AS T
    WHERE _VALUE = '1'
    order by _timestamp
    Then to get cycle_time
    Code:
    select T._TIMESTAMP
    , datediff(ms,M._TIMESTAMP,T._TIMESTAMP) / 1000. AS cycle_time
    , 1 as _VALUE
    FROM #t1 T
    left join #t1 M
      on M.nid = T.nID-1 
    order by T._TIMESTAMP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •