Execute a Stored Procedure against every row insert in Table

Question

Execute a Stored Procedure against every row insert in Table

azhar.iqbal499

SSCrazy

Points: 2812
More actions
November 5, 2018 at 4:10 am

#416465

I have around 200 tables and we have insert in each table with in each seconds.
so there are around 90 million records in all tables. We want to execute an SP against each row in a table. Trigger is not a choice for us because it will further slow down things. I am making a custom solution to loop over a every row in a table and execute SP in a loop.
How can i make this in parallel by using SQL or SSIS package. I am using MSSQL Server 2016.
Please advice me for better solution.

Viewing 15 posts - 1 through 15 (of 22 total)

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

azhar.iqbal499 - Monday, November 5, 2018 4:10 AM
I have around 200 tables and we have insert in each table with in each seconds.
so there are around 90 million records in all tables. We want to execute an SP against each row in a table. Trigger is not a choice for us because it will further slow down things. I am making a custom solution to loop over a every row in a table and execute SP in a loop.
How can i make this in parallel by using SQL or SSIS package. I am using MSSQL Server 2016.
Please advice me for better solution.

You want to execute a stored procedure for every row in every table of your database? Can you describe the reason, please?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 2

azhar.iqbal499 - Monday, November 5, 2018 4:10 AM
I have around 200 tables and we have insert in each table with in each seconds.
so there are around 90 million records in all tables. We want to execute an SP against each row in a table. Trigger is not a choice for us because it will further slow down things. I am making a custom solution to loop over a every row in a table and execute SP in a loop.
How can i make this in parallel by using SQL or SSIS package. I am using MSSQL Server 2016.
Please advice me for better solution.

Can you transform the SP into a function and call it from a CROSS APPLY?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

azhar.iqbal499 - Monday, November 5, 2018 4:10 AM
I have around 200 tables and we have insert in each table with in each seconds.
so there are around 90 million records in all tables. We want to execute an SP against each row in a table. Trigger is not a choice for us because it will further slow down things. I am making a custom solution to loop over a every row in a table and execute SP in a loop.
How can i make this in parallel by using SQL or SSIS package. I am using MSSQL Server 2016.
Please advice me for better solution.

How long do you have and how often will you need to do such a thing? Executing a stored procedure 90 million times, even in parallel, is going to use a whole lot of resources and take a long time. If you can (and you should if this is going to happen again), rewrite the stored procedure so that it's not RBAR in nature. "Stop thinking about what you want to do to a row... instead, think about what you want to do to a column".

--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)

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 4

It is the business requirement to call the SPs against each Row. I am thinking to Call After trigger on each row insert and implement Service Broker Queue to call Sps. This will provides me parallel and asynchronous execution of SP. Or can I use SSIS for parallel processing.
Please suggest.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

azhar.iqbal499 - Tuesday, November 6, 2018 1:50 AM
It is the business requirement to call the SPs against each Row. I am thinking to Call After trigger on each row insert and implement Service Broker Queue to call Sps. This will provides me parallel and asynchronous execution of SP. Or can I use SSIS for parallel processing.
Please suggest.

What will the stored procedure (or whatever) actually do?
When should it execute?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 6

azhar.iqbal499 - Tuesday, November 6, 2018 1:50 AM
It is the business requirement to call the SPs against each Row. I am thinking to Call After trigger on each row insert and implement Service Broker Queue to call Sps. This will provides me parallel and asynchronous execution of SP. Or can I use SSIS for parallel processing.
Please suggest.

There is a business requirement. It may or may not be satisfied by calling an SP against 90 million rows individually, but that is a technical solution to the business requirement, not the requirement itself. As Jeff said, if you think about what you want to do to a column, there may be a better way, but we won't know unless you give us the underlying requirement.

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 7

Actually I am working over Drilling Software which insert 1 row a into a table after five second. We Pass Some columns as parameter to a SP for Each row. These sps are called after every insert and it should not impact the OLTP. These SP are used to build some R Model so they needs Parameters for each row.
Client suggest me implement Service Broker for this. But for me it is extra overhead to manage Service Queue for just calling Procedure.
So What I did, on every insert of Row, I add same row in Audit table through after trigger. Trigger is only for INSERT. Then Loop on this table to call sp and mark the Flag isExecuted=TRUE, so that i don't pick record next time.
I can create multiple SPs to run them parallel on the basis of ID identity column in the Audit table. I will move this stuff into a job and job will run continuously to cater new records in a base table.
is that make sense?
Any better idea would be highly appreciated.

Thanks in Advance.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

azhar.iqbal499 - Wednesday, November 7, 2018 1:46 AM
Actually I am working over Drilling Software which insert 1 row a into a table after five second. We Pass Some columns as parameter to a SP for Each row. These sps are called after every insert and it should not impact the OLTP. These SP are used to build some R Model so they needs Parameters for each row.
Client suggest me implement Service Broker for this. But for me it is extra overhead to manage Service Queue for just calling Procedure.
So What I did, on every insert of Row, I add same row in Audit table through after trigger. Trigger is only for INSERT. Then Loop on this table to call sp and mark the Flag isExecuted=TRUE, so that i don't pick record next time.
I can create multiple SPs to run them parallel on the basis of ID identity column in the Audit table. I will move this stuff into a job and job will run continuously to cater new records in a base table.
is that make sense?
Any better idea would be highly appreciated.
Thanks in Advance.

Have you considered some form of replication for this? The impact on your OLTP system would be minimal and it wouldn't require any code changes (to the OLTP system) at all.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 9

This option will not be viable for us, As our data is already there inside the SQL server.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

azhar.iqbal499 - Wednesday, November 7, 2018 6:21 AM
This option will not be viable for us, As our data is already there inside the SQL server.

This does not sound like a restriction. You can commence replication at any time.
Replication is an efficient, lightweight means of providing a copy of some or all of your data for reporting. Superficially, it would appear to be a good fit for your requirements.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 246979 More actions · Answer 11

azhar.iqbal499 - Wednesday, November 7, 2018 1:46 AM
Actually I am working over Drilling Software which insert 1 row a into a table after five second. We Pass Some columns as parameter to a SP for Each row. These sps are called after every insert and it should not impact the OLTP. These SP are used to build some R Model so they needs Parameters for each row.
Client suggest me implement Service Broker for this. But for me it is extra overhead to manage Service Queue for just calling Procedure.
So What I did, on every insert of Row, I add same row in Audit table through after trigger. Trigger is only for INSERT. Then Loop on this table to call sp and mark the Flag isExecuted=TRUE, so that i don't pick record next time.
I can create multiple SPs to run them parallel on the basis of ID identity column in the Audit table. I will move this stuff into a job and job will run continuously to cater new records in a base table.
is that make sense?
Any better idea would be highly appreciated.
Thanks in Advance.

And you have set this up for 200 tables? Does that also mean that you have 200 separate 'audit tables'?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 12

Audit Table will be only one and it will be deleted for older data of one day back.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 13

azhar.iqbal499 - Wednesday, November 7, 2018 9:44 AM
Audit Table will be only one and it will be deleted for older data of one day back.

So the requirement to execute against the 90 million existing rows has vanished?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 14

For Audit Table we store table Name to identify each table and we will delete older data for one day.