I have a SQL Server function that uses several WITH CTEs to pre-process some data prior to the output in FOR JSON PATH format.

The query runs sub-second in SSMS but is taking twelve seconds when called from our test PHP web application.

I am aware of ‘parameter sniffing’ for stored procedures, but I am unclear whether this is the same for functions, and if so how to fix the issue. In stored procedures, the use of local variables improves the parameter sniffing issues, but I am unaware of a similar fix for functions.

Where else are good starting points to investigate such an issue?

As requested below the PHP code is as follows:

include '../connections/iconnection.inc';
$inputJSON = trim(file_get_contents("php://input"));
$decodedJSON = (json_decode($inputJSON, true));
$SQLStmt = "SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (?,?,?,?,?,?,?,?,?,?,?);";
$SQLPrms=array();
(!empty($decodedJSON['siteid']) ? array_push($SQLPrms,$decodedJSON['siteid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['contractorid']) ? array_push($SQLPrms,$decodedJSON['contractorid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['containerid']) ? array_push($SQLPrms,$decodedJSON['containerid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['wastetypeid']) ? array_push($SQLPrms,$decodedJSON['wastetypeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['containersizeid']) ? array_push($SQLPrms,$decodedJSON['containersizeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['productcategoryid']) ? array_push($SQLPrms,$decodedJSON['productcategoryid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['producttypeid']) ? array_push($SQLPrms,$decodedJSON['producttypeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['productid']) ? array_push($SQLPrms,$decodedJSON['productid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['serviceid']) ? array_push($SQLPrms,$decodedJSON['serviceid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['wastesubtypeid']) ? array_push($SQLPrms,$decodedJSON['wastesubtypeid']) : array_push($SQLPrms,NULL));
(!empty($decodedJSON['wastesubtypeqty']) ? array_push($SQLPrms,$decodedJSON['wastesubtypeqty']) : array_push($SQLPrms,NULL));
$RS_Result01 = sqlsrv_query($conn01,$SQLStmt,$SQLPrms);
if ($RS_Result01 === false) {
    $_SESSION['error-url'] = basename(__FILE__);
    $_SESSION['error-line'] = __LINE__;
    $_SESSION['error-sqlmsg'] = sqlsrv_errors();
    $_SESSION['error-sqlstmt'] = $SQLStmt;
    $_SESSION['error-sqlprms'] = $SQLPrms;
    header("Location: "."error.php");
    exit();
}
$ROW_Result01 = sqlsrv_fetch_array($RS_Result01);