Category: Performance

WITH RETURNS NULL ON NULL INPUT in SQL Server

While I was going through a review of a sql server function, it is observed there are many checks implemented if any of input parameter is null then return null. Then, it got strike on mind about “WITH RETURNS NULL ON NULL INPUT” in SQL Server. This is available in SQL server from 2015 version, however, I have not seen people used it efficiently. So thought of sharing about this intelligent way of handling such situations.

Let us start with a sample function as below:

Mighty Sample function

create or alter function fn_validateinparam(@col1 varchar(50), @col2 varchar(50))
returns  varchar(100)
as
Begin

	Declare @outputval varchar(100)
	If (@Col1 is null or @Col2 is null)
		set @outputval= NULL
	Else
		set @outputval= 'Valid params'

	return @outputval
End

Select dbo.fn_validateinparam('You','me')
Select dbo.fn_validateinparam('','')
Select dbo.fn_validateinparam('You',NULL)
Select dbo.fn_validateinparam(NULL,NULL)
Select dbo.fn_validateinparam(NULL,'me')

function using WITH RETURNS NULL ON NULL INPUT

The above is a sample function that we can see in many places to check the null param check. There is an option introduced in SQL Server 2005 “WITH RETURNS NULL ON NULL INPUT” to handle this with a grace as below. This way, if any of your input parameters is passed as NULL value, then the function will immediately return NULL value without actually invoking the body of the function. That clears the theory and its time for us to check with sample code as below.

create or alter function fn_Efficientvalidateinparam(@col1 varchar(50), @col2 varchar(50))
returns  varchar(100)
WITH RETURNS NULL ON NULL INPUT
as
Begin

	Declare @outputval varchar(100)
	If (@Col1 is null or @Col2 is null)
		set @outputval= 'Wrong params'
	Else
		set @outputval= 'Valid params'

	return @outputval
End

Select dbo.fn_Efficientvalidateinparam('You','me')
Select dbo.fn_Efficientvalidateinparam('','')
Select dbo.fn_Efficientvalidateinparam('You',NULL)
Select dbo.fn_Efficientvalidateinparam(NULL,NULL)
Select dbo.fn_Efficientvalidateinparam(NULL,'me')

Sample output

The below screenshot explains that the option returns without getting into execution of the function whenever one of the parameter is having NULL value. (Please note I used “Wrong value” to assign to the output variable to understand it actually returns in the beginning or only after the execution.)

If we have not used this technique so far, start using this one, it helps to avoid lots of junk codes in the definition that makes it clean. In addition, it has also observed a small factor of performance improvement for the function.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

On-Demand Performance Test Rig with JMeter

These days it has become necessary to execute the performance test at low cost. This blog details about the how to setup  “On-Demand & low cost” Performance test rig on Azure and execute performance test with it.

Primarily, to setup the on-demand performance test rig below are the prerequisites

  1. JMeter scripts needs to be check-in to GIT Repo and keep it updated
  2. Azure Subscriptions with the Resource Group created in which the on-demand test rig will be created.
  3. Azure container Registry (ACR) – The JMeter docker image will be stored here
  4. JMeter plugin Referencehttps://github.com/adrianmo/jmeter-backend-azure

Azure CLI Command must include the following:

  1. Azure container Registry with JMeter Image Path
  2. Region in which the Container need to be spin up
  3. CPU, need for the load test execution
  4. Memory in GB, needed for the test execution
  5. GIT Repo Mount Path

Referencehttps://docs.microsoft.com/en-us/cli/azure/container?view=azure-cli-latest

How does it work?
JMeterTestRig

There will be more posts to come on this topic, until then stay tuned and stay safe!!!