Post reply

EOD

  • September 14, 2005 at 12:33 am

    #94891

    HI Guys..

    is there any fastest method End Of Day process in sql server 2000.?

  • September 14, 2005 at 1:40 am

    #589614

    Can you explain what you mean by "End Of Day process"

     

    --------------------
    Colt 45 - the original point and click interface

  • September 14, 2005 at 2:50 am

    #589627

    thx for interest phill..

    i work for small banking ... and at the end of the day ..i have to calculate loan payment the loan interest and make auto debet from loan to saving..etc

    if data size about 10000 record maybe it fast but how if the data size over than 1 billion record..

    and the technique i'm used is Cursor . load all active record to cursor then loop each one to get exact payment

    in table kredit

    sory for my english phill..

  • September 14, 2005 at 4:41 am

    #589637

    Ok, first thing you need to do is get rid of the cursor. If you take a set based approach you'll find your performance will increase dramatically. Now as this is banking and you want to be 200% sure that everything is correct and processed accurately, here is what I would do.

    Load your active records into a worktable. Given the number of records I'd suggest making this a permanent table in the database. In a very simplistic form this table would have fields something like,

    loan_account

    savings_account

    interest_rate

    loan_amount

    payment_amount

    Plus any other relevant fields you need

    Don't worry too much about populating all the fields with one INSERT at the start. Not knowing your database, but having seen a couple of "bank" database, you could easily end up with a huge query that takes forever to run. Instead concentrate on taking small nibbles. eg: load the loan account numbers, then update the loaded records with the savings account numbers, etc...

    Once loaded with the relevant data, you'll be able to calculate something like a payment amount using a simple T-SQL UPDATE statement that updates all records in the worktable. Once you've performed all the necessary calculations in your worktable you can run INSERT and UPDATE statements as required to update your database where appropriate. You can also use the worktable to verify/audit that calculations and changes were done correctly before, and after updating your database.

     

     

    --------------------
    Colt 45 - the original point and click interface

  • September 15, 2005 at 9:07 pm

    #590419

    Sory For Delay Phill .. I got out of duty yesterday ..

    so do you think i must avoid using cursor.. and

    when end of day start.. i must using temporary table .. right..? and looping for each record in Temporary table..

    and i think one of my begin tran causing long running query , is there another technique except begin tran to keep consistency ..?

    thx for advance

  • September 15, 2005 at 9:22 pm

    #590421

    Yes get rid of the cursor.

    Also, don't use a # temporary table. Make it a permanent table in the database.

    Using my technique the only transaction consistency you need to ensure is the final update. You can verify all the calculations in your worktable before updating, so you don't need to use a transaction for this part.

    Also, don't loop through each record in the temp table if you can avoid it. Just run select/insert/update statements that affect the whole set of records. eg:

    UPDATE myWorktable

    SET payment_amount = loan_amount * interest_rate

    This will update all records in the worktable in one go, whether there are 1000 records or 1000000 records. As you're not affecting production data you can leave the transaction handling to SQL Server.

     

    --------------------
    Colt 45 - the original point and click interface

  • September 16, 2005 at 1:12 am

    #590439

    but phill, i have litle question

    1. how if when Production data transformed to WorkTable something bad happen. like Broken Connection, etc. so i have delete and reimport to worktable again.?

    2.What's technique to stop user access sql server via my application while end of day running..

    thx lots for help phill

  • September 16, 2005 at 1:24 am

    #590443

    1. In your current process, how do you determine how many rows to load into the cursor? You could perform a count using a similar query. Run the insert into the worktable. Count how many records where inserted. Compare the record count.

    2. Best technique is for the client application to check for a database flag, or file. The existence of these indicates the process is running and the application acts accordingly. EG: Something that is regularly done for websites that access databases. The main page check the file system for a certain file. This file is used to indicate that the website is not-available. If the file exists then user is re-directed to a maintenance page.

    You can use a similar technique to allow users to view the data and prevent changes. That way the "business" can still function with limited capabilities. As your main activity will be taking place in a seperate worktable, you shouldn't be bothered by them reading data.

     

    --------------------
    Colt 45 - the original point and click interface

  • September 16, 2005 at 1:28 am

    #590444

    Also, everthing I'm mentioning here is T-SQL based so therefore there is no application that is external to the database. This makes it very unlikely that things like broken connections will affect the processing.

     

    --------------------
    Colt 45 - the original point and click interface

  • September 16, 2005 at 1:31 am

    #590445

    ok phil thx a lot for help and salut..

Viewing 10 posts - 1 through 10 (of 10 total)

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