Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder jfreak53's Avatar
    Join Date
    May 2004
    Location
    Guatemala
    Posts
    477
    Thanks
    19
    Thanked 10 Times in 10 Posts

    Question on temp table or Procedure

    So once again in this system that I am converting over they have done something that I see as stupid but I would like to ask some experts. In this one area of the system it is transferring all banking to accounting, the two different systems.

    This is the current process between program to database. They insert a bunch of data to temp table1, we will call tt1. Now on tt1 there is a trigger, that on insert of new data checks tt2 to see if the 4 ids with today's date already exist, if so it updates that record from inserted data and adds amount to what's already there. If it doesn't exist then it inserts to tt2 from it's inserted data. Simple trigger.

    At the end of the program's sequence it insert's all data in tt2 with the 4 ids and today's date to final table, call it f1. Once done inserting all records with todays date to f1 from tt2, it erases everything in tt1 and tt2.

    Now to me this seems like a lot I mean a lot of unnecessary work.

    Am I right?

    If so what would be better and easier.

    Option1:

    Run in my PHP script a select query on f1 to see if 4 ids exist with todays date. If so update and add to amount the current value. If not exist insert.
    Option2:

    Create a procedure to do all of that on f1 then call procedure.
    Either way I remove both tt1 and tt2 and cut down time plus remove the stupid trigger and let my DB do less work.

    Am I right in my assumption or am I being too naive?

    Thanks guys.
    "FORTRAN is not a language. It's a way of turning a multi-million dollar mainframe, into a $50 programmable scientific calculator."
    http://www.microfastcat.com -- FastCat Software, the fastest software on the NET!
    http://www.microthosting.com -- Free reseller web hosting, Hosting, VPS, FREE SMALL HOSTING!!!
    http://www.microtronix-tech.com -- Web design and programming

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    If there are really only a max of 4 records involved, I don't think it's worth the hassle of rewrite.

    But otherwise: Of course you are right.

    I'd be tempted to go the SP route, but that's my preference.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder jfreak53's Avatar
    Join Date
    May 2004
    Location
    Guatemala
    Posts
    477
    Thanks
    19
    Thanked 10 Times in 10 Posts
    Well it's actually more than 4 records, it's 4 ID columns that get matched. But it's two temp tables and the final table per each part of the system. There are two parts, and the old system writes to these tables at least 6 times in each part. Since they are part of a while loop from another select statement, it inserts into the temps. Then at the very end it select all from the tt2 and inserts into final of both final tables.

    So that's what I did, I cut the time in half and removed all four temp tables and just insert into the final tables from the get go. I didn't do a procedure since I'm under the gun and I'm still not so good with procedures even though they have been the primary talk we have had ha ha ha

    So I didn't want to mess with it, but yeah it's a lot just to those temp tables. And looking back I could have done it better also, I could do an insert on exist update, but I don't want to go back right now ha ha.

    I will fix it later.

    Thanks ped!
    "FORTRAN is not a language. It's a way of turning a multi-million dollar mainframe, into a $50 programmable scientific calculator."
    http://www.microfastcat.com -- FastCat Software, the fastest software on the NET!
    http://www.microthosting.com -- Free reseller web hosting, Hosting, VPS, FREE SMALL HOSTING!!!
    http://www.microtronix-tech.com -- Web design and programming


  •  

    Posting Permissions

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