Post reply

help with query

  • June 7, 2007 at 2:18 pm

    #92595

    I have the query below that is called in multiple places in the application and some times I get FTS on it.

    I'm trying to optimize it, but getting stucked.

    All I need to display is the last customer number by the last create date. I do not have an index on the date field, and was trying to pull it results set by the ID. There should be an easier way though...

    Any idea is appreciated.

    Thanks a lot for the help.

    mj

    SELECT

    n.cust_id,

    n.num_id,

    n.num_type_id,

    n.num_value,

    n.num_location,

    nt.num_type,

    nt.num_desc

    FROM nums n, num_type nt

    WHERE n.num_type_id = nt.num_type_id

    AND n.num_id = (SELECT max(n2.num_id) FROM nums n2

    WHERE n2.cust_id = n.cust_id AND

    n2.sys_delete_dt is null AND

    n2.sys_create_dt = (SELECT max(n3.sys_create_dt)

    FROM nums n3

    WHERE n3.cust_id = n.cust_id

    AND n3.sys_delete_dt is null))

  • June 7, 2007 at 2:31 pm

    #711532

    SELECT TOP 1 cust_id FROM nums ORDER BY sys_create_dt DESC ?

  • June 8, 2007 at 7:29 pm

    #711821

    I did retest it with order by desc but it looks even more resoure consuming...

    any other ideas?

    Thanks a lot for the help, mj

  • June 12, 2007 at 3:51 pm

    #712425

    If this query must executed frequently and from various contexts, you could put a trigger on nums that would store the most recent customer number. Then, you'd have instant performance.

    - - Herb

Viewing 4 posts - 1 through 3 (of 3 total)

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