Assess performance impact when using local variables inside stored procedures

When we need to use local variables inside stored procedures and use them as predicates in queries involving large tables, make sure that performance impact of the same is assessed correctly.

While we can’t completely avoid using local variables, we should test the respective SQL in 3 different ways to determine if there are significant performance issues due to local variables.

Test case 1

Use literal and test the query

Test case 2

Create a stored procedure with local variable definition and respetive SQL in body of the SP and test by running the stored procedure

Test case 3

Remove local variable and directly use the parameter passed to the stored procedure as a predicate for the query.

In usual scenarios, Test case 3 will perform better and most preferred.

More information at below URLs.

 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s