Insert bulk fails when Online Index Rebuild is done

  • Hi,

    We are getting issue of Insert bulk failed due to schema change of target table.

    We had created a job to check if fragmentation of table > 30% then to rebuild index online whilst bulk insert is happening in that table. We have around 3 transaction tables in which bulk insert happens and at a time thousands of records in a second. Only bulk insert is performed, no updates and no deletes during the whole business day. With change of business day, the tables get empty.

    My problem is, in case when my application restarts (one component of the entire system), that component reads data from these tables, through stored procedure. At the time of restart, the data keeps on getting inserted into these tables.

    The stored procedure takes time to execute and times take more than 30 mins to produce data. Below is the sample flow

    Table A is master table with around 20 columns. for an entire day this table contains around 30000000+ records. Table B is Details table with around 30 columns which can contain 100000000+ records during the day. Table D is another master table which contains client details

    Table A primary key - OrderNo

    Table B primary key - OrderNo, SerailNo, Code

    So the flow is as

    Select A.nOrderNo, Max SerialNo
    into #C
    from A
    inner join B
    on A.OrderNo = B.OrdeRNo
    where A.nRefNo>0

    Select all the A.columns, B.columns
    from temp C
    inner join A
    on C.nOrderNo =A.nOrderNo
    inner join B
    on C.nOrderNo = B.nOrderNo
    and C.nSerialNo = B.nSerialNo
    inner join D
    on A.nUserId = D.nUserId

    This is the overall content of store procedure. we cannot introduce any other index on the tables. We have checked the I/Os and whatever we could optimize we did. However i feel not much can be done further.

    Kindly guide what else can be done.

    Thanks

    Saumik

     

     

     

     

    • This topic was modified 3 months ago by saum70.
    • This topic was modified 3 months ago by saum70.
  • Look at this line:

    and C.nSerialNo = C.nSerialNo

    Did you mean for C.nSerialNo to join to a different table?

    If so, fix it.

    If not, remove it -- It's of no use.

     

  • Hi,

    I have changed the query.  It was a typo mistake. Else actually it is as below

    C.nSerialNo = B.nSerialNo

    Thanks

    Saumik Vora

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

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