June 3, 2025 at 7:10 am
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
June 3, 2025 at 1:15 pm
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.
June 5, 2025 at 9:35 am
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