Are there any areas that need improvement for this query

Transact-SQL
1

Hi friends,

I wonder if there are any improvements to my query.

Thank you in advance.

Select DISTINCT gcr.productCode, gcr.productDescription, gcr.quantity, c.Pin, c.Serial
from GameConfirmResponses gcr
INNER JOIN GameRequests gr ON gcr.referenceId = gr.referenceId
INNER JOIN Coupons c ON gcr.Id = c.ConfirmResponseID
INNER JOIN BulkPurchases bp ON gr.clientTrxRef = CAST(bp.BulkID AS VARCHAR(10))
where bp.BulkID = 14 and gcr.status = 1 and gcr.service = 'RAZER'

Here are schema and sample data.

USE [GameAPI]
GO
/****** Object:  Table [dbo].[BulkPurchases]    Script Date: 6.02.2023 08:59:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BulkPurchases](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[BulkID] [int] NOT NULL,
	[Amount] [int] NOT NULL,
	[ProductCode] [nvarchar](50) NOT NULL,
	[Status] [int] NULL,
	[PurchaseDateTime] [datetime] NULL,
 CONSTRAINT [PK_dbo.BulkPurchases] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Coupons]    Script Date: 6.02.2023 08:59:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Coupons](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ConfirmResponseID] [int] NOT NULL,
	[expiryDate] [datetime] NULL,
	[Serial] [nvarchar](max) NOT NULL,
	[Pin] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_dbo.Coupons] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GameConfirmResponses]    Script Date: 6.02.2023 08:59:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GameConfirmResponses](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[referenceId] [uniqueidentifier] NOT NULL,
	[version] [nvarchar](max) NULL,
	[signature] [nvarchar](max) NULL,
	[paymentID] [nvarchar](max) NULL,
	[productCode] [nvarchar](max) NULL,
	[quantity] [int] NOT NULL,
	[deliveredQuantity] [nvarchar](max) NULL,
	[productDescription] [nvarchar](max) NULL,
	[totalPayablePrice] [float] NOT NULL,
	[currency] [nvarchar](max) NULL,
	[ApplicationCode] [nvarchar](max) NULL,
	[unitPrice] [float] NOT NULL,
	[totalPrice] [float] NOT NULL,
	[purchaseStatusCode] [nvarchar](max) NULL,
	[purchaseStatusDate] [datetime] NULL,
	[requestDateTime] [datetime] NULL,
	[merchantProductCode] [nvarchar](max) NULL,
	[used] [int] NOT NULL,
	[status] [int] NOT NULL,
	[service] [nvarchar](max) NULL,
	[clientTrxRef] [varchar](25) NULL,
 CONSTRAINT [PK_dbo.GameConfirmResponses] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[GameRequests]    Script Date: 6.02.2023 08:59:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GameRequests](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[referenceId] [uniqueidentifier] NULL,
	[productCode] [nvarchar](max) NULL,
	[quantity] [int] NOT NULL,
	[version] [nvarchar](max) NULL,
	[signature] [nvarchar](max) NULL,
	[ApplicationCode] [nvarchar](max) NULL,
	[requestDateTime] [datetime] NULL,
	[merchantProductCode] [nvarchar](max) NULL,
	[customerID] [int] NULL,
	[password] [nvarchar](max) NULL,
	[shopNo] [nvarchar](max) NULL,
	[safeNo] [nvarchar](max) NULL,
	[cashierNo] [nvarchar](max) NULL,
	[validatedToken] [nvarchar](max) NULL,
	[service] [nvarchar](max) NULL,
	[clientTrxRef] [varchar](25) NULL,
 CONSTRAINT [PK_dbo.GameRequests] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] ON 
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (8, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.487' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (9, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.533' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (10, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.537' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (11, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.543' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (12, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.547' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (13, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.553' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (14, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.560' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (15, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.567' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (16, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.570' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (17, 14, 100, N'0187209', 1, CAST(N'2023-02-02T16:56:42.577' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (18, 15, 100, N'0187209', 1, CAST(N'2023-02-02T18:00:54.533' AS DateTime))
GO
INSERT [dbo].[BulkPurchases] ([Id], [BulkID], [Amount], [ProductCode], [Status], [PurchaseDateTime]) VALUES (19, 15, 75, N'0187209', 1, CAST(N'2023-02-02T18:00:54.570' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[BulkPurchases] OFF
GO
SET IDENTITY_INSERT [dbo].[Coupons] ON 
GO

INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14388, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017731', N'00000000017731')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14389, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017733', N'00000000017733')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14390, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017735', N'00000000017735')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14391, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017737', N'00000000017737')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14392, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017739', N'00000000017739')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14393, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017741', N'00000000017741')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14394, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017743', N'00000000017743')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14395, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017745', N'00000000017745')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14396, 2949, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017747', N'00000000017747')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14397, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017749', N'00000000017749')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14398, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017751', N'00000000017751')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14399, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017753', N'00000000017753')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14400, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017755', N'00000000017755')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14401, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017757', N'00000000017757')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14402, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017758', N'00000000017758')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14403, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017760', N'00000000017760')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14404, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017762', N'00000000017762')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14405, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017765', N'00000000017765')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14406, 2951, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017767', N'00000000017767')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14407, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017769', N'00000000017769')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14408, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017771', N'00000000017771')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14409, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017772', N'00000000017772')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14410, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017774', N'00000000017774')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14411, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017776', N'00000000017776')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14412, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017773', N'00000000017773')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14413, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017778', N'00000000017778')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14414, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017780', N'00000000017780')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14415, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017782', N'00000000017782')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14416, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017775', N'00000000017775')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14417, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017784', N'00000000017784')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14418, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017777', N'00000000017777')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14419, 2952, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017786', N'00000000017786')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14420, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017779', N'00000000017779')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14421, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017781', N'00000000017781')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14422, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017783', N'00000000017783')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14423, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017785', N'00000000017785')
GO
INSERT [dbo].[Coupons] ([Id], [ConfirmResponseID], [expiryDate], [Serial], [Pin]) VALUES (14424, 2953, CAST(N'2024-01-17T00:00:00.000' AS DateTime), N'0000017787', N'00000000017787')
GO
SET IDENTITY_INSERT [dbo].[Coupons] OFF
GO
SET IDENTITY_INSERT [dbo].[GameConfirmResponses] ON 
GO

GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2944, N'82a8bb81-589d-4d95-9262-569f7e6b6d01', N'V1', N'e68d56d3eb46a7ca3f4e1adaa583b935', N'MDO165206', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:16.000' AS DateTime), CAST(N'2023-02-05T20:59:08.890' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2945, N'79481ab9-4212-49ce-bed0-3119aed58cf2', N'V1', N'28ff550b2b3b9b4dbca1c9b3c4d2cded', N'MDO165205', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:16.000' AS DateTime), CAST(N'2023-02-05T20:59:08.890' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2946, N'a1310752-b75a-4621-95a4-8799d926c631', N'V1', N'3ae49b61d61fbd747284c651edc6367e', N'MDO165208', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:18.000' AS DateTime), CAST(N'2023-02-05T20:59:10.803' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2947, N'27f9473d-a046-40f4-986e-83daae9e4dcf', N'V1', N'23088f38669aa39c5b6259e0919df57c', N'MDO165207', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:18.000' AS DateTime), CAST(N'2023-02-05T20:59:10.800' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2948, N'021804dd-3b7e-493f-96c3-410a433bea17', N'V1', N'f94934393a2cdd97b7f8b9ef470c484d', N'MDO165210', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:20.000' AS DateTime), CAST(N'2023-02-05T20:59:12.350' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2949, N'133ff1ae-7f25-43ee-b726-d11c706cb89e', N'V1', N'52a4e8095ef0b90f32a6737cfcfbbbbd', N'MDO165209', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:20.000' AS DateTime), CAST(N'2023-02-05T20:59:12.350' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2950, N'c9c5d65e-43ca-426f-bda5-4f9d48f5dfbe', N'V1', N'b3e918d1675111daecbad3d5b62f6230', N'MDO165211', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:21.000' AS DateTime), CAST(N'2023-02-05T20:59:13.987' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2951, N'3a89843b-3d93-448d-b851-9fe5c5951c8f', N'V1', N'4d19505cc1f9fd9a497ee5025f6fcc42', N'MDO165212', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:21.000' AS DateTime), CAST(N'2023-02-05T20:59:13.987' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2952, N'b50a311a-af48-4000-948b-86b00471aa03', N'V1', N'03a13882b87e6340b068fe04a30a76e8', N'MDO165213', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:23.000' AS DateTime), CAST(N'2023-02-05T20:59:15.527' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
INSERT [dbo].[GameConfirmResponses] ([Id], [referenceId], [version], [signature], [paymentID], [productCode], [quantity], [deliveredQuantity], [productDescription], [totalPayablePrice], [currency], [ApplicationCode], [unitPrice], [totalPrice], [purchaseStatusCode], [purchaseStatusDate], [requestDateTime], [merchantProductCode], [used], [status], [service], [clientTrxRef]) VALUES (2953, N'dccb6d9f-f188-48d2-8a7e-58c55ec9de7b', N'V1', N'35434b87454428bcb6f856563b52933a', N'MDO165214', N'0187209', 10, N'10', N'Bim 10 TL Razer Gold Pin', 90, N'TRY', N'202009020478', 10, 100, N'00', CAST(N'2023-02-05T20:59:23.000' AS DateTime), CAST(N'2023-02-05T20:59:15.533' AS DateTime), N'', 0, 1, N'RAZER', NULL)
GO
SET IDENTITY_INSERT [dbo].[GameConfirmResponses] OFF
GO
SET IDENTITY_INSERT [dbo].[GameRequests] ON 
GO

GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (5997, N'82a8bb81-589d-4d95-9262-569f7e6b6d01', N'0187209', 10, N'V1', N'dd5a6ec6939d8dad3aa8e2684f12ee73', N'202009020478', CAST(N'2023-02-05T20:59:04.593' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (5998, N'79481ab9-4212-49ce-bed0-3119aed58cf2', N'0187209', 10, N'V1', N'6056bb51486232286a9d2ccce5d03f9b', N'202009020478', CAST(N'2023-02-05T20:59:04.593' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (5999, N'27f9473d-a046-40f4-986e-83daae9e4dcf', N'0187209', 10, N'V1', N'1a5dd916043d169705b139545ab0e551', N'202009020478', CAST(N'2023-02-05T20:59:09.300' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (6000, N'a1310752-b75a-4621-95a4-8799d926c631', N'0187209', 10, N'V1', N'c34b08bf07f174dc210e333cfef0af87', N'202009020478', CAST(N'2023-02-05T20:59:09.297' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (6001, N'133ff1ae-7f25-43ee-b726-d11c706cb89e', N'0187209', 10, N'V1', N'599d9dc630913919ce78893d3591c510', N'202009020478', CAST(N'2023-02-05T20:59:10.810' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (6002, N'3a89843b-3d93-448d-b851-9fe5c5951c8f', N'0187209', 10, N'V1', N'522b2914ae3ba1d66e47286f257b8d18', N'202009020478', CAST(N'2023-02-05T20:59:12.350' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (6003, N'021804dd-3b7e-493f-96c3-410a433bea17', N'0187209', 10, N'V1', N'65b4bec4d774f92e841be3ad99c8aea7', N'202009020478', CAST(N'2023-02-05T20:59:10.810' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (6004, N'dccb6d9f-f188-48d2-8a7e-58c55ec9de7b', N'0187209', 10, N'V1', N'43a82eace1f4707f79cae8a0587ce649', N'202009020478', CAST(N'2023-02-05T20:59:13.987' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (6005, N'c9c5d65e-43ca-426f-bda5-4f9d48f5dfbe', N'0187209', 10, N'V1', N'b2c3d07d5dae804a4a9ca33bfb949f5c', N'202009020478', CAST(N'2023-02-05T20:59:12.350' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
INSERT [dbo].[GameRequests] ([Id], [referenceId], [productCode], [quantity], [version], [signature], [ApplicationCode], [requestDateTime], [merchantProductCode], [customerID], [password], [shopNo], [safeNo], [cashierNo], [validatedToken], [service], [clientTrxRef]) VALUES (6006, N'b50a311a-af48-4000-948b-86b00471aa03', N'0187209', 10, N'V1', N'948fa49e2365ca3ab3a8f7fd84f9955e', N'202009020478', CAST(N'2023-02-05T20:59:13.987' AS DateTime), NULL, 7, NULL, NULL, NULL, NULL, NULL, N'RAZER', N'14')
GO
SET IDENTITY_INSERT [dbo].[GameRequests] OFF
GO
2

hi

i made some changes

-- removed distinct added group by
-- changed to this cast(gr.clientTrxRef as int) = bp.BulkID .. this is integer comparision ..yours is varchar comparision
-- ( select * from GameConfirmResponses where status = 1 and service = 'RAZER' ) gcr
here i am filtering the data first and then joining

please compare the execution plans of my query and your query

Select 
                gcr.productCode
	          , gcr.productDescription
			  , gcr.quantity
			  , c.Pin
			  , c.Serial
from 
   ( select * from GameConfirmResponses where status = 1 and service = 'RAZER' ) gcr
         INNER JOIN 
   GameRequests gr ON gcr.referenceId = gr.referenceId
         INNER JOIN 
  Coupons c ON gcr.Id = c.ConfirmResponseID
        INNER JOIN 
   ( select * from BulkPurchases where BulkID = 14 ) bp ON cast(gr.clientTrxRef as int)  = bp.BulkID 
group by 
        gcr.productCode
	    , gcr.productDescription
		, gcr.quantity
		, c.Pin
		, c.Serial

you might want to test this by putting in a million records in each table and then compare

3

It gives this error,

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type int.
4

you can change it back to the wya you had it

also you can add indexes

5

Actually, I have millions of records in production, I will do it when I fix the problem in your query :slight_smile:

6

production

oh god

good luck :+1: :+1:

7

there might be null values for gr.clientTrxRef, Could this be the problem? I will handle for non nulls, can we filter it like this? @harishgg1

8

you can say
WHERE column IS NOT NULL
or
ISNULL ( column , '999999') =

also you break up the joins into steps

select * into #Temp1 from table1 join table2
select * from #Temp1 join table3

9

Here is how it is ended.

Select gcr.productCode, gcr.productDescription, gcr.quantity, c.Pin, c.Serial
from ( select * from GameConfirmResponses where status = 1 and service = 'RAZER' ) gcr
INNER JOIN (select * from GameRequests WHERE clientTrxRef IS NOT NULL ) gr ON gcr.referenceId = gr.referenceId
INNER JOIN Coupons c ON gcr.Id = c.ConfirmResponseID
INNER JOIN ( select * from BulkPurchases where BulkID = 14 ) bp ON gr.clientTrxRef  = CAST(bp.BulkID AS VARCHAR(10)) 
group by gcr.productCode, gcr.productDescription, gcr.quantity, c.Pin, c.Serial
10

hi

did it work

did it resolve your issue ?

11 
SELECT DISTINCT gcr.productCode, gcr.productDescription, gcr.quantity, c.Pin, c.Serial
FROM dbo.GameConfirmResponses gcr
	JOIN Coupons c
		ON gcr.Id = c.ConfirmResponseID
WHERE gcr.[status] = 1 and gcr.[service] = 'RAZER'
	AND EXISTS
	(
		SELECT 1
		FROM GameRequests gr
		WHERE gr.referenceId = gcr.referenceId
			AND gr.clientTrxRef = '14'
			AND EXISTS
			(
				SELECT 1
				FROM BulkPurchases bp
				WHERE bp.BulkID = 14
			)
	);
1 Like
12

Thank you @harishgg1 and @Ifor for your fast help. Could you please show me how should i interpret execution plans?

13

https://www.sqlshack.com/interpreting-execution-plans-of-t-sql-queries/

14

A few flashing warning sings come up (other than some design issues of having id on one table, and better names on others: (And a column like ReferenceId is going to confuse the heck out of people who use this database)

DISTINCT: Why. At my last company we had a rule that if you used DISTINCT, it better come with an explanation. There are reasons for it, but in a query like this, where you first table is references in the SELECT clause, it is really rare with a good table design

This is the worst offender:

INNER JOIN BulkPurchases bp ON gr.clientTrxRef = CAST(bp.BulkID AS VARCHAR(10))

Casting a value in a join means it cannot use an index and will always do a scan of the object. A column named BulkId sounds like there will be a lot of data.

Select DISTINCT gcr.productCode, gcr.productDescription, gcr.quantity, c.Pin, c.Serial
from GameConfirmResponses gcr
INNER JOIN GameRequests gr ON gcr.referenceId = gr.referenceId
INNER JOIN Coupons c ON gcr.Id = c.ConfirmResponseID
INNER JOIN BulkPurchases bp ON gr.clientTrxRef = CAST(bp.BulkID AS VARCHAR(10))
where bp.BulkID = 14 and gcr.status = 1 and gcr.service = 'RAZER'

Not performance oriented, but I really don't like seeing numbers in a WHERE clause like status = 1, Use a table to define stauts, or spell it out. You put status in an int, so it takes 4 bytes ('ACTV','INAC') takes the same space!

You might could use indexes on a few columns, but that would be too hard to tell with such small amounts of data. The ones in the WHERE clause maybe. I don't see indexes in the script

1 Like