SQL Ssrver Query Optimisation
Hi everyone,
I am currently building a SQL report, but i need somebody to help me optimize my query.
The query is working fine and pulling data, only if a selected a specific product (one or two), But when i need to pull data without that filter, the query is taking more time to show results, and i am not getting any data pulled.
Here is my SQL Query :
WITH WeighingData AS (
SELECT
CASE TRANS_TYPE
WHEN 2 THEN ‘Dispatch’
WHEN 1 THEN ‘Receiving’
WHEN 0 THEN ‘Cancelled’
END AS Transaction_Type,
w.TIMESTAMP_1,
w.TIMESTAMP_2,
w.TICKETNO,
w.HAULIER,
w.IMAGE_1_1,
w.IMAGE_1_2,
w.IMAGE_2_1,
w.IMAGE_2_2,
w.OPERATOR_1 AS ‘Operator ID1’,
w.OPERATOR_2 AS ‘Operator ID2’,
xind1.FULL_NM AS ‘Operator1_Fullname’,
xind2.FULL_NM AS ‘Operator2_Fullname’,
w.PRODUCT,
w.NET,
w.VEHICLE_MANIFEST,
w.DESPATCH_ORDER,
w.WEIGHT_1,
w.WEIGHT_2,
w.BOL,
ISNULL(d.DRIVER_NAME, N”) + ISNULL(d.DRIVER_SURNAME, N”) AS Driver_Fullname,
v.LICENSEPLATE,
st1.TERMINAL AS WBG1,
st2.TERMINAL AS WBG2,
p.NAME,
ROW_NUMBER() OVER (PARTITION BY w.TICKETNO, w.VEHICLE_MANIFEST, w.DESPATCH_ORDER ORDER BY st1.TRANSIT_TIME DESC) AS RowNum
FROM
[Multiweigh].[Flexpoint].[WEIGHING] w
INNER JOIN Flexpoint.PRODUCT p ON p.CODE = w.PRODUCT
INNER JOIN Flexpoint.DRIVER d ON d.DRIVER_ID = w.DRIVER_ID
LEFT OUTER JOIN Flexpoint.VEHICLE v ON v.LICENSEPLATE = w.VEHICLE
LEFT JOIN Flexpoint.STAGING_TRANSIT st1 ON st1.PARAMETER_3 = w.VEHICLE_MANIFEST OR st1.PARAMETER_4 = w.DESPATCH_ORDER
LEFT JOIN Flexpoint.STAGING_TRANSIT st2 ON st2.PARAMETER_3 = w.VEHICLE_MANIFEST OR st2.PARAMETER_4 = w.DESPATCH_ORDER
LEFT JOIN Flexpoint.XINDIVIDUAL xind1 ON xind1.USERINDIV = w.OPERATOR_1
LEFT JOIN Flexpoint.XINDIVIDUAL xind2 ON xind2.USERINDIV = w.OPERATOR_2
INNER JOIN Flexpoint.XSTATION x ON x.STATION = w.STATION
WHERE
w.TIMESTAMP_1 BETWEEN ‘2024-08-04 00:00:00’ AND ‘2024-08-04 23:59:59’
AND w.PRODUCT IN (@Product_Name)
AND w.TICKETNO LIKE @Ticket_No
AND TRANS_TYPE IN (@Transaction_Type)
)
SELECT
*,
CONCAT(CAST([Operator ID1] AS NVARCHAR(50)), ‘ – ‘, [Operator1_Fullname]) AS Operator1_Info,
CONCAT(CAST([Operator ID2] AS NVARCHAR(50)), ‘ – ‘, [Operator2_Fullname]) AS Operator2_Info
FROM
WeighingData
WHERE
RowNum = 1
AND ([WBG1] LIKE ‘%weighb%’ OR [WBG2] LIKE ‘%weighb%’);
Hi everyone, I am currently building a SQL report, but i need somebody to help me optimize my query.The query is working fine and pulling data, only if a selected a specific product (one or two), But when i need to pull data without that filter, the query is taking more time to show results, and i am not getting any data pulled. Here is my SQL Query : WITH WeighingData AS ( SELECT CASE TRANS_TYPE WHEN 2 THEN ‘Dispatch’ WHEN 1 THEN ‘Receiving’ WHEN 0 THEN ‘Cancelled’ END AS Transaction_Type, w.TIMESTAMP_1, w.TIMESTAMP_2, w.TICKETNO, w.HAULIER, w.IMAGE_1_1, w.IMAGE_1_2, w.IMAGE_2_1, w.IMAGE_2_2, w.OPERATOR_1 AS ‘Operator ID1’, w.OPERATOR_2 AS ‘Operator ID2’, xind1.FULL_NM AS ‘Operator1_Fullname’, xind2.FULL_NM AS ‘Operator2_Fullname’, w.PRODUCT, w.NET, w.VEHICLE_MANIFEST, w.DESPATCH_ORDER, w.WEIGHT_1, w.WEIGHT_2, w.BOL, ISNULL(d.DRIVER_NAME, N”) + ISNULL(d.DRIVER_SURNAME, N”) AS Driver_Fullname, v.LICENSEPLATE, st1.TERMINAL AS WBG1, st2.TERMINAL AS WBG2, p.NAME, ROW_NUMBER() OVER (PARTITION BY w.TICKETNO, w.VEHICLE_MANIFEST, w.DESPATCH_ORDER ORDER BY st1.TRANSIT_TIME DESC) AS RowNum FROM [Multiweigh].[Flexpoint].[WEIGHING] w INNER JOIN Flexpoint.PRODUCT p ON p.CODE = w.PRODUCT INNER JOIN Flexpoint.DRIVER d ON d.DRIVER_ID = w.DRIVER_ID LEFT OUTER JOIN Flexpoint.VEHICLE v ON v.LICENSEPLATE = w.VEHICLE LEFT JOIN Flexpoint.STAGING_TRANSIT st1 ON st1.PARAMETER_3 = w.VEHICLE_MANIFEST OR st1.PARAMETER_4 = w.DESPATCH_ORDER LEFT JOIN Flexpoint.STAGING_TRANSIT st2 ON st2.PARAMETER_3 = w.VEHICLE_MANIFEST OR st2.PARAMETER_4 = w.DESPATCH_ORDER LEFT JOIN Flexpoint.XINDIVIDUAL xind1 ON xind1.USERINDIV = w.OPERATOR_1 LEFT JOIN Flexpoint.XINDIVIDUAL xind2 ON xind2.USERINDIV = w.OPERATOR_2 INNER JOIN Flexpoint.XSTATION x ON x.STATION = w.STATION WHERE w.TIMESTAMP_1 BETWEEN ‘2024-08-04 00:00:00’ AND ‘2024-08-04 23:59:59’ AND w.PRODUCT IN (@Product_Name) AND w.TICKETNO LIKE @Ticket_No AND TRANS_TYPE IN (@Transaction_Type))SELECT *, CONCAT(CAST([Operator ID1] AS NVARCHAR(50)), ‘ – ‘, [Operator1_Fullname]) AS Operator1_Info, CONCAT(CAST([Operator ID2] AS NVARCHAR(50)), ‘ – ‘, [Operator2_Fullname]) AS Operator2_InfoFROM WeighingDataWHERE RowNum = 1 AND ([WBG1] LIKE ‘%weighb%’ OR [WBG2] LIKE ‘%weighb%’); Read More
