Функция выполняется медленно (12 с) в PHP, но быстро (до секунды) в SSMS. Каковы некоторые области расследования? - Цифровое Кемерово

Функция выполняется медленно (12 с) в PHP, но быстро (до секунды) в SSMS. Каковы некоторые области расследования? ⇐ Php

Ответить

1 сообщение • Страница 1 из 1

Anonymous

Функция выполняется медленно (12 с) в PHP, но быстро (до секунды) в SSMS. Каковы некоторые области расследования?

Цитата

Сообщение Anonymous » 08 июл 2024, 14:34

У меня есть функция SQL Server, которая использует несколько CTE CAN для предварительной обработки некоторых данных перед выводом в формате FOR JSON PATH.
запрос выполняется в SSMS менее чем за секунду, но при вызове из нашего тестового веб-приложения PHP занимает двенадцать секунд.
Мне известно о «обнюхивании параметров» для хранимых процедур, но я не знаю, является ли это то же самое для функций, и если да, то как решить проблему. В хранимых процедурах использование локальных переменных улучшает проблемы с анализом параметров, но мне неизвестно о подобном исправлении для функций.
Где еще можно найти хорошую отправную точку для исследования такой проблемы?< /p>
Как указано ниже, PHP-код выглядит следующим образом:

Код: Выделить всё

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

РЕДАКТИРОВАНИЕ: добавление ОПЦИИ (RECOMPILE) к инициирующему вызову следующим образом:

Код: Выделить всё

$SQLStmt = "SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (?,?,?,?,?,?,?,?,?,?,?) OPTION (RECOMPILE);";

привело к тому, что время отклика функции в среде разработки составило менее секунды.
Я впервые использую OPTION (RECOMPILE), и я Я знаю, что это не идеальное решение этой проблемы. Есть ли лучший способ обеспечить постоянное применение передовой практики?
Определение функции следующее:

Код: Выделить всё

USE [upgradeverify]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_JSON_QueryPricingForOrder_v5.0]    Script Date:  07/07/2024 14:22:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION
[dbo].[udf_JSON_QueryPricingForOrder_v5.0]
(
@SiteId INT,
@ContractorId INT = NULL,
@ContainerId INT = NULL,
@WasteTypeId INT = NULL,
@ContainerSizeId INT = NULL,
@ProductCategoryId INT = NULL,
@ProductTypeId INT = NULL,
@ProductId INT = NULL,
@ServiceId INT = NULL,
@WasteSubTypeId VARCHAR(300) = NULL,
@WasteSubTypeQty VARCHAR(300) = NULL
)

RETURNS TABLE
AS RETURN
(
-- Create CTE of child Client records

WITH
/************************************************************************************************************************/
/****** Create ClientList - list of all parent to this site                                                        ******/
/************************************************************************************************************************/
ClientList AS
(
SELECT
ClientHierarchy.ParentClientId,
ClientHierarchy.ChildClientId,
ClientHierarchy.Level,
Client.*
FROM
ClientHierarchy
INNER JOIN
Client ON Client.ClientId = ClientHierarchy.ChildclientId
WHERE
(
(ChildClientId = (SELECT ClientId FROM Site WHERE Site.SiteId = @SiteId)) -- define the starting point
)
UNION ALL
SELECT
ClientHierarchy.ParentClientId,
ClientHierarchy.ChildClientId,
ClientHierarchy.Level,
Client.*
FROM
ClientHierarchy
INNER JOIN
Client ON Client.ClientId = ClientHierarchy.ChildclientId
INNER JOIN
ClientList ON ClientHierarchy.ChildClientId = ClientList.ParentclientId
),

/************************************************************************************************************************/
/****** Create Markup - Retrieval of markup feilds from BaselinePriceMarkup                                        ******/
/************************************************************************************************************************/

Markup AS
(
SELECT
SalePriceMarkup,
RetailPriceMarkup
FROM
BaselinePriceMarkup
),

/************************************************************************************************************************/
/****** Split the data input strings into CTE                                                                      ******/
/************************************************************************************************************************/

SplitWasteSubTypeId AS
(
SELECT
value AS WasteSubTypeId,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
FROM STRING_SPLIT(@WasteSubTypeId, ',')
),

SplitWasteSubTypeQty AS
(
SELECT
value AS WasteSubTypeQty,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
FROM STRING_SPLIT(@WasteSubTypeQty,  ',')
),

/************************************************************************************************************************/
/****** Cretae the Breakdown CTE from the split CTEs                                                               ******/
/************************************************************************************************************************/

Breakdown AS
(
SELECT
s1.WasteSubTypeId,
s2.WasteSubTypeQty
FROM SplitWasteSubTypeId s1
LEFT OUTER JOIN SplitWasteSubTypeQty s2
ON s1.row_num = s2.row_num
),

/************************************************************************************************************************/
/****** Create PriceList - Retrieval of raw data from ContractorPricing and BaselinePricing                        ******/
/****** This will only Retrieve baseline prices for any client With Reseller = 1 Of Reseller is 0 then             ******/
/****** ContractorPrice is returned                                                                                ******/
/************************************************************************************************************************/

Pricelist AS
(
SELECT
CASE
WHEN
ContainerID IS NOT NULL
THEN
ROW_NUMBER() OVER (PARTITION BY ContractorId,ContainerId,ContainerSizeId,WasteTypeId ORDER BY ContractorId,ContainerId,ContainerSizeId,WasteTypeId,Level DESC)
WHEN
ProductCategoryId IS NOT NULL
THEN
ROW_NUMBER() OVER (PARTITION BY ContractorId,ProductCategoryId,ProductTypeId,ProductId ORDER BY ContractorId,ProductCategoryId,ProductTypeId,ProductId,Level DESC)
WHEN
ServiceId IS NOT NULL
THEN
ROW_NUMBER() OVER (PARTITION BY ContractorId,ServiceId,WasteTypeId,WasteSubTypeId ORDER BY ContractorId,ServiceId,WasteTypeId,WasteSubTypeId,Level DESC)
END AS RowNumber,
*
FROM
(
SELECT
ContractorPrice.ClientId,
ContractorPrice.SiteId,
CASE
WHEN ClientList.Level IS NULL
THEN
40
ELSE
ClientList.Level
END AS Level,
0 AS BaselinePrice,
CASE
WHEN EXISTS (SELECT BrokerPricing FROM ClientList WHERE BrokerPricing = 1)
THEN
1
ELSE
0
END AS Reseller,
ContractorPrice.Reference,
ContractorPrice.ContractNotes,
ContractorPrice.ContractorId,
Contractor.ContractorName,
Contractor.Reference AS ContractorReference,
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3  '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4  '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorAddress,
Contractor.ContractorName + ', ' +
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3   '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4   '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorFullAddress,
ContractorPrice.ContainerId,
ContractorPrice.WasteTypeId,
ContractorPrice.ContainerSizeId,
ContractorPrice.ProductCategoryId,
ContractorPrice.ProductTypeId,
ContractorPrice.ProductId,
ContractorPrice.ServiceId,
ContractorPrice.WasteSubTypeId,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(CostHaulage AS NUMERIC(8,2))
END AS CostHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(CostTonnage AS NUMERIC(8,2))
END AS CostTonnage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(SaleHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(SaleHaulage AS NUMERIC(8,2))
END AS SaleHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(SaleTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(SaleTonnage AS NUMERIC(8,2))
END AS SaleTonnage,
IncludedTonnage
FROM
ContractorPrice
INNER JOIN
Contractor ON Contractor.ContractorId = ContractorPrice.ContractorId
LEFT OUTER JOIN
ClientList ON ClientList.ClientId = ContractorPrice.ClientId
WHERE
(
(
(ContractorPrice.SiteId = @SiteId) OR
(ContractorPrice.ClientId IN (SELECT ClientId FROM ClientList))
) AND
(
(
(ContractorPrice.ContractorId = @ContractorId) AND
(@ContractorId IS NOT NULL)
) OR
(@ContractorId IS NULL)
) AND
(Contractor.Active = 1) AND
(
(
(ContractorPrice.ContainerId = @ContainerId) AND
(@ContainerId IS NOT NULL)
) AND
(
(ContractorPrice.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(ContractorPrice.ContainerSizeId = @ContainerSizeId) AND
(@ContainerSizeId IS NOT NULL)
)
)  OR
(
(
(ContractorPrice.ProductCategoryId = @ProductCategoryId) AND
(@ProductCategoryId IS NOT NULL)
) AND
(
(ContractorPrice.ProductTypeId = @ProductTypeId) AND
(@ProductTypeId IS NOT NULL)
) AND
(
(ContractorPrice.ProductId = @ProductId) AND
(@ProductId IS NOT NULL)
)
) OR
(
(
(ContractorPrice.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(ContractorPrice.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(ContractorPrice.WasteSubTypeId IN (SELECT WasteSubTypeId FROM Breakdown)) AND
(@WasteSubTypeId IS NOT NULL)
)
) OR
(
(
(ContractorPrice.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(ContractorPrice.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(ContractorPrice.WasteSubTypeId IS NULL)
)
)
)
UNION

SELECT
NULL AS ClientId,
SiteId,
50 AS Level,
1 As BaselinePrice,
CASE
WHEN EXISTS (SELECT BrokerPricing FROM ClientList WHERE BrokerPricing = 1)
THEN
1
ELSE
0
END AS Reseller,
BaseLinePriceHeader.Reference AS Reference,
'Baseline Price agreed with contractor' AS ContractNotes,
BaseLinePriceHeader.ContractorId,
Contractor.ContractorName,
Contractor.Reference AS ContractorReference,
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3  '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4  '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorAddress,
Contractor.ContractorName + ', ' +
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3  '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4   '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorFullAddress,
BaseLinePriceDetail.ContainerId,
BaseLinePriceDetail.WasteTypeId,
BaseLinePriceDetail.ContainerSizeId,
BaseLinePriceDetail.ProductCategoryId,
BaseLinePriceDetail.ProductTypeId,
BaseLinePriceDetail.ProductId,
BaseLinePriceDetail.ServiceId,
BaseLinePriceDetail.WasteSubTypeId,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CostHaulage AS NUMERIC(8,2))
END AS CostHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CostTonnage AS NUMERIC(8,2))
END AS CostTonnage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CEILING(BaselinePriceDetail.CostHaulage + ((BaselinePriceDetail.CostHaulage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CEILING(BaselinePriceDetail.CostHaulage + ((BaselinePriceDetail.CostHaulage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2))
END AS SaleHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CEILING(BaselinePriceDetail.CostTonnage + ((BaselinePriceDetail.CostTonnage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CEILING(BaselinePriceDetail.CostTonnage + ((BaselinePriceDetail.CostTonnage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2))
END AS SaleTonnage,
IncludedTonnage
FROM
Site
INNER JOIN
[Postcodetools].[dbo].[PostcodeData] PCD ON REPLACE(PCD.PostCode,' ','') = REPLACE(Site.AddrPostcode,' ','')
INNER JOIN
BaseLinePriceSector ON BaseLinePriceSector.Sector = PCD.Sector
INNER JOIN
BaseLinePriceHeader ON BaseLinePriceHeader.BaseLinePriceHeaderId = BaseLinePriceSector.BaseLinePriceHeaderId
INNER JOIN
BaseLinePriceDetail ON BaseLinePriceDetail.BaseLinePriceHeaderId = BaseLinePriceHeader.BaseLinePriceHeaderId
INNER JOIN
Contractor ON Contractor.ContractorId = BaseLinePriceHeader.ContractorId
WHERE
(
(Site.SiteId = @SiteId) AND
(
(
(BaseLinePriceHeader.ContractorId = @ContractorId) AND
(@ContractorId IS NOT NULL)
) OR
(@ContractorId IS NULL)
) AND
(Contractor.Active = 1)  AND
(
(
(BaseLinePriceDetail.ContainerId = @ContainerId) AND
(@ContainerId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.ContainerSizeId = @ContainerSizeId) AND
(@ContainerSizeId IS NOT NULL)
)
) OR
(
(
(BaseLinePriceDetail.ProductCategoryId = @ProductCategoryId) AND
(@ProductCategoryId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.ProductTypeId = @ProductTypeId) AND
(@ProductTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.ProductId = @ProductId) AND
(@ProductId IS NOT NULL)
)
) OR
(
(
(BaseLinePriceDetail.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteSubTypeId IN (SELECT WasteSubTypeId FROM Breakdown)) AND
(@WasteSubTypeId IS NOT NULL)
)
) OR
(
(
(BaseLinePriceDetail.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteSubTypeId IS NULL)
)
)
)
) a
WHERE
(
(a.BaselinePrice = a.Reseller)
)
)

/************************************************************************************************************************/
/****** Select results  based on the MAx Level for that particular service (Waste or Product)                      ******/
/************************************************************************************************************************/

SELECT
(
SELECT
leveldescription,
contractorid,
contractorname,
contractorreference,
contractoraddress,
contractorfulladdress,
SUM(CostHaulage) AS costhaulage,
SUM(CostTonnage) AS costtonnage,
SUM(SaleHaulage) AS salehaulage,
SUM(SaleTonnage)  AS saletonnage,
includedtonnage
FROM
(
SELECT
CASE
WHEN
(
SELECT
COUNT(RowNumber)
FROM
PriceList
WHERE
RowNumber = 1
) > 1
THEN
CASE Pricelist.Level
WHEN 50 Then 'Baseline'
ELSE
'Contractor Price'
END
ELSE
CASE Pricelist.Level
WHEN 10 Then 'Head'
WHEN 20 Then 'Area'
WHEN 30 Then 'Branch'
WHEN 40 Then 'Site'
ELSE
'Baseline'
END
END AS leveldescription,
Pricelist.ContractorId AS contractorid,
Pricelist.ContractorName AS contractorname,
Pricelist.ContractorReference AS contractorreference,
Pricelist.ContractorAddress AS contractoraddress,
Pricelist.ContractorFullAddress AS contractorfulladdress,
Pricelist.Reference AS reference,
Pricelist.CostHaulage AS costhaulage,
Pricelist.CostTonnage AS costtonnage,
Pricelist.SaleHaulage AS salehaulage,
Pricelist.SaleTonnage AS saletonnage,
Pricelist.IncludedTonnage AS includedtonnage
FROM
Pricelist
WHERE
RowNumber = 1
) a
GROUP BY
leveldescription,
contractorid,
contractorname,
contractorreference,
contractoraddress,
contractorfulladdress,
includedtonnage
ORDER
BY SaleHaulage
FOR JSON PATH
) AS JSONData
)

Из запроса следует отметить одну интересную вещь: при запуске в SSMS
текст запроса выглядит следующим образом

Код: Выделить всё

(@0 int,@1 int,@2 int,@3 varchar(8000),@4 varchar(8000))select * from [dbo] . [udf_JSON_QueryPricingForOrder_v5.0] ( @0 , null , null , @1 , null , null , null , null , @2 , @3 , @4 )

но при запуске из PHP используются огромные поля VARCHAR для всех параметров:

Код: Выделить всё

(@P1 nvarchar(4000),@P2 varchar(max),@P3 varchar(max),@P4 nvarchar(4000),@P5 varchar(max),@P6 varchar(max),@P7 varchar(max),@P8 varchar(max),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000))SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)

Подробнее здесь: https://stackoverflow.com/questions/787 ... at-are-som

1720438449

Anonymous

У меня есть функция SQL Server, которая использует несколько CTE CAN для предварительной обработки некоторых данных перед выводом в формате FOR JSON PATH.
запрос выполняется в SSMS менее чем за секунду, но при вызове из нашего тестового веб-приложения PHP занимает двенадцать секунд.
Мне известно о «обнюхивании параметров» для хранимых процедур, но я не знаю, является ли это то же самое для функций, и если да, то как решить проблему. В хранимых процедурах использование локальных переменных улучшает проблемы с анализом параметров, но мне неизвестно о подобном исправлении для функций.
Где еще можно найти хорошую отправную точку для исследования такой проблемы?< /p>
Как указано ниже, PHP-код выглядит следующим образом:
[code]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);
[/code]
РЕДАКТИРОВАНИЕ: добавление ОПЦИИ (RECOMPILE) к инициирующему вызову следующим образом:
[code]$SQLStmt = "SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (?,?,?,?,?,?,?,?,?,?,?) OPTION (RECOMPILE);";[/code]
привело к тому, что время отклика функции в среде разработки составило менее секунды.
Я впервые использую OPTION (RECOMPILE), и я Я знаю, что это не идеальное решение этой проблемы.  Есть ли лучший способ обеспечить постоянное применение передовой практики?
Определение функции следующее:
[code]USE [upgradeverify]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_JSON_QueryPricingForOrder_v5.0]    Script Date:  07/07/2024 14:22:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION
[dbo].[udf_JSON_QueryPricingForOrder_v5.0]
(
@SiteId INT,
@ContractorId INT = NULL,
@ContainerId INT = NULL,
@WasteTypeId INT = NULL,
@ContainerSizeId INT = NULL,
@ProductCategoryId INT = NULL,
@ProductTypeId INT = NULL,
@ProductId INT = NULL,
@ServiceId INT = NULL,
@WasteSubTypeId VARCHAR(300) = NULL,
@WasteSubTypeQty VARCHAR(300) = NULL
)

RETURNS TABLE
AS RETURN
(
-- Create CTE of child Client records

WITH
/************************************************************************************************************************/
/****** Create ClientList - list of all parent to this site                                                        ******/
/************************************************************************************************************************/
ClientList AS
(
SELECT
ClientHierarchy.ParentClientId,
ClientHierarchy.ChildClientId,
ClientHierarchy.Level,
Client.*
FROM
ClientHierarchy
INNER JOIN
Client ON Client.ClientId = ClientHierarchy.ChildclientId
WHERE
(
(ChildClientId = (SELECT ClientId FROM Site WHERE Site.SiteId = @SiteId)) -- define the starting point
)
UNION ALL
SELECT
ClientHierarchy.ParentClientId,
ClientHierarchy.ChildClientId,
ClientHierarchy.Level,
Client.*
FROM
ClientHierarchy
INNER JOIN
Client ON Client.ClientId = ClientHierarchy.ChildclientId
INNER JOIN
ClientList ON ClientHierarchy.ChildClientId = ClientList.ParentclientId
),

/************************************************************************************************************************/
/****** Create Markup - Retrieval of markup feilds from BaselinePriceMarkup                                        ******/
/************************************************************************************************************************/

Markup AS
(
SELECT
SalePriceMarkup,
RetailPriceMarkup
FROM
BaselinePriceMarkup
),

/************************************************************************************************************************/
/****** Split the data input strings into CTE                                                                      ******/
/************************************************************************************************************************/

SplitWasteSubTypeId AS
(
SELECT
value AS WasteSubTypeId,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
FROM STRING_SPLIT(@WasteSubTypeId, ',')
),

SplitWasteSubTypeQty AS
(
SELECT
value AS WasteSubTypeQty,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
FROM STRING_SPLIT(@WasteSubTypeQty,  ',')
),

/************************************************************************************************************************/
/****** Cretae the Breakdown CTE from the split CTEs                                                               ******/
/************************************************************************************************************************/

Breakdown AS
(
SELECT
s1.WasteSubTypeId,
s2.WasteSubTypeQty
FROM SplitWasteSubTypeId s1
LEFT OUTER JOIN SplitWasteSubTypeQty s2
ON s1.row_num = s2.row_num
),

/************************************************************************************************************************/
/****** Create PriceList - Retrieval of raw data from ContractorPricing and BaselinePricing                        ******/
/****** This will only Retrieve baseline prices for any client With Reseller = 1 Of Reseller is 0 then             ******/
/****** ContractorPrice is returned                                                                                ******/
/************************************************************************************************************************/

Pricelist AS
(
SELECT
CASE
WHEN
ContainerID IS NOT NULL
THEN
ROW_NUMBER() OVER (PARTITION BY ContractorId,ContainerId,ContainerSizeId,WasteTypeId ORDER BY ContractorId,ContainerId,ContainerSizeId,WasteTypeId,Level DESC)
WHEN
ProductCategoryId IS NOT NULL
THEN
ROW_NUMBER() OVER (PARTITION BY ContractorId,ProductCategoryId,ProductTypeId,ProductId ORDER BY ContractorId,ProductCategoryId,ProductTypeId,ProductId,Level DESC)
WHEN
ServiceId IS NOT NULL
THEN
ROW_NUMBER() OVER (PARTITION BY ContractorId,ServiceId,WasteTypeId,WasteSubTypeId ORDER BY ContractorId,ServiceId,WasteTypeId,WasteSubTypeId,Level DESC)
END AS RowNumber,
*
FROM
(
SELECT
ContractorPrice.ClientId,
ContractorPrice.SiteId,
CASE
WHEN ClientList.Level IS NULL
THEN
40
ELSE
ClientList.Level
END AS Level,
0 AS BaselinePrice,
CASE
WHEN EXISTS (SELECT BrokerPricing FROM ClientList WHERE BrokerPricing = 1)
THEN
1
ELSE
0
END AS Reseller,
ContractorPrice.Reference,
ContractorPrice.ContractNotes,
ContractorPrice.ContractorId,
Contractor.ContractorName,
Contractor.Reference AS ContractorReference,
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3  '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4  '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorAddress,
Contractor.ContractorName + ', ' +
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3   '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4   '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorFullAddress,
ContractorPrice.ContainerId,
ContractorPrice.WasteTypeId,
ContractorPrice.ContainerSizeId,
ContractorPrice.ProductCategoryId,
ContractorPrice.ProductTypeId,
ContractorPrice.ProductId,
ContractorPrice.ServiceId,
ContractorPrice.WasteSubTypeId,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(CostHaulage AS NUMERIC(8,2))
END AS CostHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(CostTonnage AS NUMERIC(8,2))
END AS CostTonnage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(SaleHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(SaleHaulage AS NUMERIC(8,2))
END AS SaleHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(SaleTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = ContractorPrice.WasteSubTypeId)
ELSE
CAST(SaleTonnage AS NUMERIC(8,2))
END AS SaleTonnage,
IncludedTonnage
FROM
ContractorPrice
INNER JOIN
Contractor ON Contractor.ContractorId = ContractorPrice.ContractorId
LEFT OUTER JOIN
ClientList ON ClientList.ClientId = ContractorPrice.ClientId
WHERE
(
(
(ContractorPrice.SiteId = @SiteId) OR
(ContractorPrice.ClientId IN (SELECT ClientId FROM ClientList))
) AND
(
(
(ContractorPrice.ContractorId = @ContractorId) AND
(@ContractorId IS NOT NULL)
) OR
(@ContractorId IS NULL)
) AND
(Contractor.Active = 1) AND
(
(
(ContractorPrice.ContainerId = @ContainerId) AND
(@ContainerId IS NOT NULL)
) AND
(
(ContractorPrice.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(ContractorPrice.ContainerSizeId = @ContainerSizeId) AND
(@ContainerSizeId IS NOT NULL)
)
)  OR
(
(
(ContractorPrice.ProductCategoryId = @ProductCategoryId) AND
(@ProductCategoryId IS NOT NULL)
) AND
(
(ContractorPrice.ProductTypeId = @ProductTypeId) AND
(@ProductTypeId IS NOT NULL)
) AND
(
(ContractorPrice.ProductId = @ProductId) AND
(@ProductId IS NOT NULL)
)
) OR
(
(
(ContractorPrice.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(ContractorPrice.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(ContractorPrice.WasteSubTypeId IN (SELECT WasteSubTypeId FROM Breakdown)) AND
(@WasteSubTypeId IS NOT NULL)
)
) OR
(
(
(ContractorPrice.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(ContractorPrice.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(ContractorPrice.WasteSubTypeId IS NULL)
)
)
)
UNION

SELECT
NULL AS ClientId,
SiteId,
50 AS Level,
1 As BaselinePrice,
CASE
WHEN EXISTS (SELECT BrokerPricing FROM ClientList WHERE BrokerPricing = 1)
THEN
1
ELSE
0
END AS Reseller,
BaseLinePriceHeader.Reference AS Reference,
'Baseline Price agreed with contractor' AS ContractNotes,
BaseLinePriceHeader.ContractorId,
Contractor.ContractorName,
Contractor.Reference AS ContractorReference,
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3  '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4  '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorAddress,
Contractor.ContractorName + ', ' +
Contractor.AddrLine1 + ', ' +
CASE WHEN ((Contractor.AddrLine2 IS NOT NULL) AND (Contractor.AddrLine2  '')) THEN Contractor.AddrLine2 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine3 IS NOT NULL) AND (Contractor.AddrLine3  '')) THEN Contractor.AddrLine3 + ', ' ELSE '' END +
CASE WHEN ((Contractor.AddrLine4 IS NOT NULL) AND (Contractor.AddrLine4   '')) THEN Contractor.AddrLine4 + ', ' ELSE '' END +
Contractor.AddrCity + ', ' +
Contractor.AddrCounty + ', ' +
Contractor.AddrPostcode AS ContractorFullAddress,
BaseLinePriceDetail.ContainerId,
BaseLinePriceDetail.WasteTypeId,
BaseLinePriceDetail.ContainerSizeId,
BaseLinePriceDetail.ProductCategoryId,
BaseLinePriceDetail.ProductTypeId,
BaseLinePriceDetail.ProductId,
BaseLinePriceDetail.ServiceId,
BaseLinePriceDetail.WasteSubTypeId,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostHaulage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CostHaulage AS NUMERIC(8,2))
END AS CostHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CostTonnage AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CostTonnage AS NUMERIC(8,2))
END AS CostTonnage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CEILING(BaselinePriceDetail.CostHaulage + ((BaselinePriceDetail.CostHaulage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CEILING(BaselinePriceDetail.CostHaulage + ((BaselinePriceDetail.CostHaulage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2))
END AS SaleHaulage,
CASE
WHEN EXISTS (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId AND WasteSubTypeQty IS NOT NULL)
THEN
CAST(CEILING(BaselinePriceDetail.CostTonnage + ((BaselinePriceDetail.CostTonnage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2)) * (SELECT WasteSubTypeQty FROM Breakdown WHERE WasteSubTypeId = BaseLinePriceDetail.WasteSubTypeId)
ELSE
CAST(CEILING(BaselinePriceDetail.CostTonnage + ((BaselinePriceDetail.CostTonnage / 100) * (SELECT SalePriceMarkup FROM Markup))) AS NUMERIC(8,2))
END AS SaleTonnage,
IncludedTonnage
FROM
Site
INNER JOIN
[Postcodetools].[dbo].[PostcodeData] PCD ON REPLACE(PCD.PostCode,' ','') = REPLACE(Site.AddrPostcode,' ','')
INNER JOIN
BaseLinePriceSector ON BaseLinePriceSector.Sector = PCD.Sector
INNER JOIN
BaseLinePriceHeader ON BaseLinePriceHeader.BaseLinePriceHeaderId = BaseLinePriceSector.BaseLinePriceHeaderId
INNER JOIN
BaseLinePriceDetail ON BaseLinePriceDetail.BaseLinePriceHeaderId = BaseLinePriceHeader.BaseLinePriceHeaderId
INNER JOIN
Contractor ON Contractor.ContractorId = BaseLinePriceHeader.ContractorId
WHERE
(
(Site.SiteId = @SiteId) AND
(
(
(BaseLinePriceHeader.ContractorId = @ContractorId) AND
(@ContractorId IS NOT NULL)
) OR
(@ContractorId IS NULL)
) AND
(Contractor.Active = 1)  AND
(
(
(BaseLinePriceDetail.ContainerId = @ContainerId) AND
(@ContainerId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.ContainerSizeId = @ContainerSizeId) AND
(@ContainerSizeId IS NOT NULL)
)
) OR
(
(
(BaseLinePriceDetail.ProductCategoryId = @ProductCategoryId) AND
(@ProductCategoryId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.ProductTypeId = @ProductTypeId) AND
(@ProductTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.ProductId = @ProductId) AND
(@ProductId IS NOT NULL)
)
) OR
(
(
(BaseLinePriceDetail.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteSubTypeId IN (SELECT WasteSubTypeId FROM Breakdown)) AND
(@WasteSubTypeId IS NOT NULL)
)
) OR
(
(
(BaseLinePriceDetail.ServiceId = @ServiceId) AND
(@ServiceId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteTypeId = @WasteTypeId) AND
(@WasteTypeId IS NOT NULL)
) AND
(
(BaseLinePriceDetail.WasteSubTypeId IS NULL)
)
)
)
) a
WHERE
(
(a.BaselinePrice = a.Reseller)
)
)

/************************************************************************************************************************/
/****** Select results  based on the MAx Level for that particular service (Waste or Product)                      ******/
/************************************************************************************************************************/

SELECT
(
SELECT
leveldescription,
contractorid,
contractorname,
contractorreference,
contractoraddress,
contractorfulladdress,
SUM(CostHaulage) AS costhaulage,
SUM(CostTonnage) AS costtonnage,
SUM(SaleHaulage) AS salehaulage,
SUM(SaleTonnage)  AS saletonnage,
includedtonnage
FROM
(
SELECT
CASE
WHEN
(
SELECT
COUNT(RowNumber)
FROM
PriceList
WHERE
RowNumber = 1
) > 1
THEN
CASE Pricelist.Level
WHEN 50 Then 'Baseline'
ELSE
'Contractor Price'
END
ELSE
CASE Pricelist.Level
WHEN 10 Then 'Head'
WHEN 20 Then 'Area'
WHEN 30 Then 'Branch'
WHEN 40 Then 'Site'
ELSE
'Baseline'
END
END AS leveldescription,
Pricelist.ContractorId AS contractorid,
Pricelist.ContractorName AS contractorname,
Pricelist.ContractorReference AS contractorreference,
Pricelist.ContractorAddress AS contractoraddress,
Pricelist.ContractorFullAddress AS contractorfulladdress,
Pricelist.Reference AS reference,
Pricelist.CostHaulage AS costhaulage,
Pricelist.CostTonnage AS costtonnage,
Pricelist.SaleHaulage AS salehaulage,
Pricelist.SaleTonnage AS saletonnage,
Pricelist.IncludedTonnage AS includedtonnage
FROM
Pricelist
WHERE
RowNumber = 1
) a
GROUP BY
leveldescription,
contractorid,
contractorname,
contractorreference,
contractoraddress,
contractorfulladdress,
includedtonnage
ORDER
BY SaleHaulage
FOR JSON PATH
) AS JSONData
)

[/code]
Из запроса следует отметить одну интересную вещь: при запуске в SSMS
текст запроса выглядит следующим образом
[code](@0 int,@1 int,@2 int,@3 varchar(8000),@4 varchar(8000))select * from [dbo] . [udf_JSON_QueryPricingForOrder_v5.0] ( @0 , null , null , @1 , null , null , null , null , @2 , @3 , @4 )[/code]
но при запуске из PHP используются огромные поля VARCHAR для всех параметров:
[code](@P1 nvarchar(4000),@P2 varchar(max),@P3 varchar(max),@P4 nvarchar(4000),@P5 varchar(max),@P6 varchar(max),@P7 varchar(max),@P8 varchar(max),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000))SELECT * FROM [dbo].[udf_JSON_QueryPricingForOrder_v5.0] (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)[/code] 

Подробнее здесь: [url]https://stackoverflow.com/questions/78717301/function-runs-slowly-12s-from-php-but-quick-sub-second-in-ssms-what-are-som[/url]

Ответить

1 сообщение • Страница 1 из 1

Быстрый ответ

Заголовок:

Имя пользователя:

Изменение регистра текста:

Смайлики

Ещё смайлики…

К этому ответу прикреплено по крайней мере одно вложение.

Если вы не хотите добавлять вложения, оставьте поля пустыми. Можно прикреплять файлы, перетаскивая их в окно сообщения.

Максимально разрешённый размер вложения: 15 МБ.

Имя файла:

Комментарий к файлу:

Имя файла	Комментарий к файлу	Размер	Статус

Вернуться в «Php»