T-SQL took more than 45 minutes, and it still Executing query

Question

T-SQL took more than 45 minutes, and it still Executing query

miss.delinda

SSCommitted

Points: 1616
More actions
May 12, 2012 at 6:56 pm

#257879

My tables as following,
USE [TUNEDB]
GO
/****** Object: Table [dbo].[xtApplicantQuota] Script Date: 05/13/2012 08:39:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[xtApplicantQuota](
[idx] [int] NOT NULL,
[iptsIdx] [int] NULL,
[kursusIdx] [int] NULL,
[mRemaining] [int] NULL,
[fRemaining] [int] NULL,
CONSTRAINT [PK_xtApplicantQuota] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [xtApplicantQuota_UQ1] UNIQUE NONCLUSTERED
(
[iptsIdx] ASC,
[kursusIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[xtApplicantApply] Script Date: 05/13/2012 08:39:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xtApplicantApply](
[applicantIdx] [int] NULL,
[jantina] [char](1) NULL,
[iptsIdx] [int] NULL,
[kursusIdx] [int] NULL,
[Rnk] [int] NULL,
[Processed] [int] NULL,
CONSTRAINT [xtApplicantApply_UQ1] UNIQUE NONCLUSTERED
(
[applicantIdx] ASC,
[iptsIdx] ASC,
[kursusIdx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tbl_MST_Penempatan] Script Date: 05/13/2012 08:39:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_MST_Penempatan](
[idx] [int] NULL,
[tbl_MST_Pemohon_idx] [int] NULL,
[tbl_DirKursus_Jurisdiction_idx] [int] NULL,
[noAkaunPelajar] [varchar](12) NULL,
[noAkaunBank] [varchar](20) NULL,
[statusDaftar] [int] NULL,
[tagTerimaTawaran] [bit] NULL,
[tagCetakSuratTawaran] [bit] NULL,
[tkhCetakSuratTawaran] [datetime] NULL,
[tkhDaftar] [datetime] NULL,
[tkhTerimaTawaran] [datetime] NULL,
[noResitBayaran] [varchar](50) NULL,
[tagDokumenLengkap] [bit] NULL,
[ciptaOleh] [varchar](50) NULL,
[TkhCipta] [datetime] NULL,
[editOleh] [varchar](50) NULL,
[tkhEdit] [datetime] NULL,
CONSTRAINT [tbl_MST_Penempatan_UQ1] UNIQUE NONCLUSTERED
(
[tbl_MST_Pemohon_idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
My xtApplicantQuota data have 187 rows, and the data as following,
/****** Object: Table [dbo].[xtApplicantQuota] Script Date: 05/13/2012 08:42:03 ******/
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483639, 22, 155, 13, 27)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483638, 23, 155, 52, 98)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483637, 24, 155, 38, 42)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483636, 20, 155, 21, 59)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483635, 34, 155, 12, 28)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483634, 22, 157, 29, 61)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483633, 22, 172, 13, 27)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483632, 34, 171, 12, 28)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483631, 21, 100, 20, 30)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483630, 20, 100, 20, 55)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483629, 21, 99, 20, 30)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483628, 20, 99, 26, 74)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483627, 22, 99, 13, 27)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483626, 24, 99, 38, 42)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483625, 21, 113, 20, 30)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483624, 23, 107, 52, 98)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483623, 34, 142, 25, 55)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483621, 21, 173, 31, 49)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483620, 23, 160, 26, 49)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483611, 2, 143, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483610, 2, 156, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483609, 2, 158, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483608, 2, 146, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483607, 2, 139, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483606, 2, 130, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483605, 2, 131, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483604, 2, 138, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483603, 12, 223, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483602, 12, 220, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483601, 12, 222, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483600, 12, 219, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483599, 12, 221, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483598, 13, 200, 15, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483597, 13, 201, 15, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483596, 13, 202, 15, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483595, 13, 199, 15, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483594, 13, 198, 15, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483593, 11, 186, 16, 4)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483592, 11, 185, 16, 4)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483591, 11, 183, 16, 4)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483590, 11, 187, 16, 4)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483589, 11, 184, 16, 4)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483587, 9, 175, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483586, 9, 176, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483585, 9, 177, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483584, 9, 178, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483583, 9, 179, 5, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483582, 7, 181, 20, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483581, 7, 180, 15, 5)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483580, 14, 206, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483579, 14, 207, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483578, 14, 208, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483577, 14, 210, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483576, 14, 209, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483575, 15, 211, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483574, 15, 212, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483573, 15, 213, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483572, 15, 214, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483571, 15, 215, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483570, 16, 216, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483569, 16, 217, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483568, 16, 218, 10, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483567, 16, 182, 14, 6)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483566, 17, 188, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483565, 17, 189, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483564, 17, 190, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483563, 17, 192, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483562, 18, 193, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483561, 18, 194, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483560, 18, 195, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483559, 18, 196, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483558, 18, 197, 15, 15)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483557, 8, 229, 32, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483556, 7, 229, 32, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483555, 10, 229, 32, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483554, 6, 110, 20, 10)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483552, 36, 236, 10, 6)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483551, 36, 247, 40, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483550, 36, 232, 16, 4)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483549, 37, 242, 20, 20)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483548, 37, 238, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483547, 37, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483546, 37, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483545, 37, 239, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483544, 37, 241, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483543, 37, 247, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483542, 36, 238, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483541, 40, 224, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483540, 40, 228, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483539, 40, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483538, 40, 245, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483537, 35, 248, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483536, 35, 241, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483535, 35, 238, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483534, 35, 242, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483533, 35, 249, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483532, 35, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483531, 35, 240, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483530, 35, 250, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483529, 35, 231, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483528, 36, 224, NULL, NULL)
GO
print 'Processed 100 total records'
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483527, 36, 228, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483526, 36, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483525, 36, 240, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483524, 7, 228, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483523, 7, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483522, 7, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483521, 7, 236, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483520, 7, 233, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483519, 7, 232, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483517, 7, 239, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483516, 7, 251, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483515, 7, 247, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483514, 7, 237, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483513, 8, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483512, 8, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483511, 8, 237, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483510, 8, 239, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483508, 8, 246, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483507, 8, 247, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483506, 8, 240, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483505, 37, 224, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483504, 37, 245, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483503, 37, 248, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483502, 37, 252, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483501, 37, 250, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483500, 37, 249, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483499, 37, 235, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483498, 38, 238, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483497, 38, 241, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483496, 38, 242, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483495, 38, 249, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483494, 39, 224, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483493, 39, 245, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483492, 39, 248, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483491, 39, 242, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483490, 39, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483489, 39, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483488, 39, 238, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483487, 39, 231, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483486, 39, 236, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483485, 39, 249, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483484, 39, 239, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483483, 39, 247, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483482, 39, 227, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483481, 39, 241, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483480, 41, 238, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483479, 41, 240, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483478, 41, 233, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483477, 41, 235, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483476, 41, 247, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483475, 41, 237, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483474, 10, 227, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483473, 10, 225, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483472, 10, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483471, 10, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483470, 10, 237, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483469, 10, 182, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483468, 10, 239, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483467, 10, 245, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483466, 10, 247, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483465, 42, 224, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483464, 42, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483463, 42, 242, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483462, 42, 241, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483461, 42, 240, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483460, 42, 245, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483459, 43, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483458, 43, 240, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483457, 43, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483456, 43, 243, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483455, 43, 244, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483454, 43, 235, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483453, 43, 231, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483450, 44, 267, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483449, 44, 268, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483448, 44, 269, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483447, 44, 256, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483446, 44, 257, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483445, 24, 170, 38, 42)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483444, 23, 167, 26, 49)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483443, 1, 270, 75, 125)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483442, 9, 271, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483441, 40, 230, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483440, 41, 234, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483439, 17, 191, NULL, NULL)
INSERT [dbo].[xtApplicantQuota] ([idx], [iptsIdx], [kursusIdx], [mRemaining], [fRemaining]) VALUES (-2147483438, 10, 226, NULL, NULL)
My xtApplicantApplydata have 41450 rows, and the data as following,
/****** Object: Table [dbo].[xtApplicantApply] Script Date: 05/13/2012 08:45:27 ******/
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483512, N'L', 9, 175, 6428, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483512, N'L', 9, 176, 21021, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483512, N'L', 9, 177, 29420, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483512, N'L', 9, 178, 34401, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483508, N'L', 12, 222, 6049, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483506, N'L', 2, 158, 29244, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483506, N'L', 9, 177, 7328, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483506, N'L', 9, 178, 16291, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483506, N'L', 17, 190, 33942, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483505, N'L', 9, 177, 6508, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483505, N'L', 16, 182, 21769, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483504, N'P', 12, 219, 35431, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483504, N'P', 12, 221, 6503, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483504, N'P', 22, 157, 31381, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483504, N'P', 23, 107, 21358, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483502, N'P', 20, 99, 13185, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483502, N'P', 34, 142, 23357, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483498, N'L', 7, 181, 9263, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483498, N'L', 7, 228, 28997, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483498, N'L', 39, 224, 19751, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483497, N'P', 2, 138, 33872, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483497, N'P', 21, 99, 11274, 0)
INSERT [dbo].[xtApplicantApply] ([applicantIdx], [jantina], [iptsIdx], [kursusIdx], [Rnk], [Processed]) VALUES (-2147483497, N'P', 22, 157, 31210, 0)
/*and so on ......*/
What I want to do?
1. Each data in xtApplicantApply need to be processed based on the data in xtApplicantQuota
2. Each data in xtApplicantApply will be processed by priority. The priority based on xtApplicantApply(Rnk) --- ORDER BY Rnk
3. The lowest Rnk is the strongest priority
4. If their condition is the same, the formula is first come first serve
5. Any data in xtApplicantApply those meet the requirement based on the data in xtApplicantQuota, it will be inserted into tbl_MST_Penempatan
6. If any applicantIdx HAS GAINED A PROGRAM - xtApplicantQuota(idx), others data in xtApplicantApply do not need to be further processed. So, the quota is NOT TAKEN and WILL GIVE TO OTHERS
7. If 1st selection is not qualify or no Quota, we have to move 2nd selection, 3rd selection and so on
I've done my T-SQL. It's shown as following,
use TUNEDB
Begin transaction
Begin Try
-- Run a while loop----------------------------------------
DECLARE @next INT = 1;
DECLARE @idx int, @applicantIdx INT, @jantina VARCHAR(1),
@iptsIdx INT, @kursusIdx INT;
DECLARE @cnt INT;
SELECT @cnt = COUNT(*) FROM xtApplicantApply
WHILE (@cnt > 0)
BEGIN
SET @cnt = @cnt - 1;
SELECT TOP 1
@applicantIdx = applicantIdx,
@jantina = jantina,
@iptsIdx = iptsIdx,
@kursusIdx = kursusIdx
FROM xtApplicantApply a
WHERE
processed = 0
AND EXISTS
(
SELECT idx FROM xtApplicantQuota q
WHERE q.iptsIdx = a.iptsIdx AND q.kursusIdx = a.kursusIdx
-- pastkan kuota kategori A shj
AND ( (a.jantina = 'L' AND q.mRemaining > 0) OR (a.jantina = 'P' AND q.fRemaining > 0) )
)
ORDER BY Rnk;
SET @next = @@ROWCOUNT;
IF (@next > 0)
BEGIN
-- find the id of the Quota from which this slot is dispensed.
SELECT @idx = idx FROM xtApplicantQuota
WHERE @iptsIdx = iptsIdx AND @kursusIdx = kursusIdx
-- subtract one from quota
UPDATE xtApplicantQuota SET
mRemaining = CASE WHEN @jantina = 'L' THEN mRemaining-1 ELSE mRemaining END,
fRemaining = CASE WHEN @jantina = 'P' THEN fRemaining-1 ELSE fRemaining END
WHERE
iptsIdx = @iptsIdx
AND kursusIdx = @kursusIdx
AND idx = @idx;
-- mark this applicant as processed
UPDATE xtApplicantApply SET
Processed = 1
WHERE
applicantIdx = @applicantIdx;
-- insert the selection into the table.
INSERT INTO tbl_MST_Penempatan(tbl_MST_Pemohon_idx,tbl_DirKursus_Jurisdiction_idx)
SELECT
@applicantIdx, @idx
END
END
COMMIT transaction
End Try
Begin Catch
-- Whoops, there was an error
ROLLBACK transaction
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
End Catch
The problem is T-SQL took more than 45 minutes, and it still Executing query ....
Need help on add appropriate index, re-writing T-SQL, and anything
I'm crazy stuck

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

CELKO (5/12/2012)
Code should be in Standard SQL as much as possible and not local dialect.

Heh... and you shouldn't use any of the higher level functions of scientific calculators because someone might only have a 4 function calculator. 😉 And when you come up with the differences between scientific calculators and reverse polish notation calculators, realize that true portability amongst calculators is just as much an impracticality there as it is in SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

This looks like a question I previously posted a solution to here:

http://www.sqlservercentral.com/Forums/Topic1290424-392-1.aspx

Even the field names are the same. Is this a homework problem or something?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

miss.delinda SSCommitted Points: 1616 More actions · Answer 3

Yes Mr Dwain. This is not homework or something. This is what I'm facing on

Till now, I did not see the supportive answer from expert

drew.allen SSC Guru Points: 76996 More actions · Answer 4

CELKO (5/12/2012)
We do not use BIT flags in SQL; that was assembly language.

T-SQL doesn't have a Boolean data type, so I see no problem with using a BIT to represent a Boolean value. It certainly makes more sense than using a CHAR(1) with a check constraint, or, even worse, a CHAR(1) without the check constraint (which I see all too often).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

drew.allen (5/14/2012)
CELKO (5/12/2012)
We do not use BIT flags in SQL; that was assembly language.
T-SQL doesn't have a Boolean data type, so I see no problem with using a BIT to represent a Boolean value. It certainly makes more sense than using a CHAR(1) with a check constraint, or, even worse, a CHAR(1) without the check constraint (which I see all too often).
Drew

I guess you've just misunderstood His Majesty... They don't use BIT flags, as They don't speak plebeian languages :hehe:

The rest of SQL developers can use it safely as provided by MS SQL Server...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]