Insert bulk fails when Online Index Rebuild is done

Question

Insert bulk fails when Online Index Rebuild is done

saum70

SSC Eights!

Points: 999
More actions
June 3, 2025 at 7:10 am

#4601759
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, 3 weeks ago by saum70.
- This topic was modified 3 months, 3 weeks ago by saum70.

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

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

ratbak Default port Points: 1423 More actions · Answer 1

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.

saum70 SSC Eights! Points: 999 More actions · Answer 2

Hi,

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

C.nSerialNo = B.nSerialNo

Thanks

Saumik Vora