Post reply

MAX() Function Call from Application

  • August 17, 2008 at 7:52 pm

    #859235

    You might be able to get a whole lot more retrieval speed if you created an Indexed View. Be warned that an indexed view can slow down inputs some.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 17, 2008 at 7:59 pm

    #859236

    ... and it will require a change to your front end statement. You will need to select from the indexed view, instead.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 19, 2008 at 2:00 am

    #859831

    karthikeyan,

    Your idea for saving the max value in a special table is feasible. For example, a system that creates a sales order with increasing sales order code (SO0001, SO0002, and so on). Instead of select max(code) from SalesOrder each time we need to create a Sales Order then you can just query the special table for the last code value. This way also comes with disadvantages as well. The most likely concern is that you need to update the code value in the special table each time you insert, update, or delete entry inside table SalesOrder. So whether you'll use index or this special table, you need to think about how often you need to update that special table and how long it takes to update it. Is it worthwhile the complexity rather than just adding an index for it? Will the data grow rapidly? In case of the Sales Order above then it is better to have special table since the code can't be updated and deleting a Sales Order doesn't reduce the code value. So, the only thing that update that value is the insert.

    Ivan Budiono

  • August 20, 2008 at 12:40 pm

    #860834

    The index is the best solution, except that as you are querying for the MAX date, then I would override the default and define the index as descending.

    create index IX_Name on DC_DETAILS (DOC_RECEIVED_DATE desc)

    This puts the latest dates at the beginning of the index instead of the end. Now, this is not so much for performance as the database engine should be able to get to the end of the index just as fast as the beginning, but I'm not sure of the internals at this level so this is just "playing it safe".

    As for the other idea of placing the last received date in a separate table, yes that will work. But there are other considerations.

    Maintaining the separate table will take a little processing time during Inserts but it will save some query time. So you have to consider which operation will be done more often relative to the other. If there are many Inserts relative to queries, then you are adding a little processing time to an operation that is performed often in order to save a little processing time in an operation that is performed less often. So you may not really be saving time in what I call Overall System Performance (OSP).

    Generally, when you speed up a query by making changes to the schema, you do so by making CRUD operation a little slower. This includes creating an index. However, maintaining an index will be much, much faster than accessing a separate table. Suppose the system performs 1000 CRUD operations in a day and the index increases the execution time an average of 1 ms for each such operation for a total of 1 second "hit" on OSP. In that day only one query for the Max date is executed but creating the index takes the execution time from over 3 minutes to less than 2 seconds. Obviously, the total OSP has been improved.

    Now look at the separate table. It may slow down CRUD operations by, say, 100 ms each or 100 seconds total. The query may speed up by the same amount as with the index. So total OSP will still improve but not as much as using the index. You are, after all, turning every Insert into an "Insert into this table then Update that table" operation. A Delete is turned into "Delete from this table then query that[\i] table to see if an Update is needed then Update if it is needed" operation. The same with Update unless modification of the date value is not allowed.

    As another twist, you should consider that creating an index does not really alter the overall maintainability of your database, but creating a separate table does. And we haven't even begun to consider the effect of locking...

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • August 20, 2008 at 7:54 pm

    #860985

    Indexed view materializes the SUM just as if it were in a table... except you don't have to worry about whether it's up to date or not. Just having the index may or may not be the "best" option... "It Depends" and you need to test the methods to see which works best for you. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 19 (of 19 total)

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