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