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

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

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

Author

Topic

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-05-12 : 20:48:39

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 ....

I'm stuck. Need help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 20:57:12

your posted data is not full. for example you've a.jantina = 'L' as one of conditions and i cant even see that column in posted data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-05-12 : 21:04:24

??

Got sir. I can send my full scripts to you. Give me your email

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 21:16:33

post it here so that others can also see and help you. Also post what should be your end result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-05-12 : 21:25:42

My xtApplicantQuota data have 187 rows, and xtApplicantApply data have 41450 rows. The result was I cannot post my scripts here. May be it's too long

My expected result in tbl_MST_Penempatan something like,

idx	tbl_MST_Pemohon_idx	tbl_DirKursus_Jurisdiction_idx	noAkaunPelajar	noAkaunBank	statusDaftar	tagTerimaTawaran	tagCetakSuratTawaran	tkhCetakSuratTawaran	tkhDaftar	tkhTerimaTawaran	noResitBayaran	tagDokumenLengkap	ciptaOleh	TkhCipta	editOleh	tkhEdit
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL	-2147483508	-2147483601	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	-2147483512	-2147483587	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	-2147483504	-2147483599	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	-2147483505	-2147483585	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	-2147483506	-2147483585	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	-2147483498	-2147483582	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	-2147483497	-2147483629	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
NULL	-2147483502	-2147483628	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

/*
and so on ...................
*/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-05-12 : 21:55:49

I'm crazy stuck. Need help on add appropriate index, re-writing T-SQL, and anything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-12 : 22:15:12

quote:
Originally posted by Idyana

I'm crazy stuck. Need help on add appropriate index, re-writing T-SQL, and anything

is posted result correct?
i see only NULL s as values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Idyana
Yak Posting Veteran

96 Posts

Posted - 2012-05-13 : 00:27:44

The result was correct. I only need to fill up tbl_MST_Pemohon_idx, tbl_DirKursus_Jurisdiction_idx in tbl_MST_Penempatan

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-13 : 07:52:54

[code]
For Query to run fast do following things

1.Find out the unused indexes from all tables which are being used in your procedure/query and ask DBA to Drop them all.
2.Rebuild indexes =for which average_fragmentation_% is more 30 %
Reorganize indexes =for which average_fragmentation_% is between 5 to 30 %

3.After doing step2 update the states of all tables .

Try to do above mentioned things then we will see what exactly happening.

[/code]

Vijay is here to learn something from you guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 15:23:04

the logic can be achieved by below set based solution



SELECT t.[applicantIdx],a.idx
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY [applicantIdx] ORDER BY  [Rnk] ASC) AS Rn,*
FROM [dbo].[xtApplicantApply] 
)t
INNER JOIN [dbo].[xtApplicantQuota]  a
ON a.[iptsIdx] = t.[iptsIdx]
AND a.[kursusIdx] = t.[kursusIdx]
AND Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-14 : 11:23:28

Again with the extra simple code and NO CROSS APPLY!

You are slipping visakh!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources