Post reply

Scripting for deployment

  • March 15, 2011 at 9:42 am

    #246919

    Currently we have a outside vendors that want to add there products to our system. They supply a txt file which I then create a script off of. I loop through there data and generate DUI statements. That script is then run by an upgrade DB exe which takes that scripts and run it against various production DB's. We have several clients that use our application.

    Problem is with is the amount of data that is scripted. We are currently looking at over 130K in lines. It currently takes on my local PC about 1.5 minutes to run. There are going to be several vendors that want to do this. So here are the questions:

    1) I was thinking of making these scripts into stored procedures. I would then call the stored procedure if it is in the upgrade path. Are there issues with having an SP with over 100K in lines?

    2) Would making the procedures dynamic SQL be better speed wise

    3) Are there better solutions out there for what I am trying to do?

  • March 15, 2011 at 9:45 am

    #1298937

    I don't think SPs will speed things up.

    are these just new records that are being added? if so you could look at using SSIS or Bulk Insert.

  • March 15, 2011 at 9:56 am

    #1298948

    steveb's on the right track. DDL statements that create table and objects would take no time at all...but if you have 40K lines if INSERT INTO....statements

    that is what is eating up the processing time; BULK INSERT would be the way to go to speed that portion up, I agree.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 15, 2011 at 11:02 am

    #1298996

    I should mention that I would use Bulk and SSIS if those options were not off the table. Since bulk can be tricky permission wise on remote upgrades we do not use it. SSIS is not an option because of the execution also. My hands are tied on this. This is a DUI so there are updates and Inserts to the table ie

    if exist ()

    UPDATE

    else

    INSERT

    the users upgrading these DBs may only have R/W permission.

  • March 15, 2011 at 3:52 pm

    #1299151

    Thats always going to be a problem, then.... users not having enough privs to do their jobs.

    My recommendation would be to give those users bulk admin privs.

    --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 - 1 through 4 (of 4 total)

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