SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 T-SQL took more than 45 minutes, and it still runs		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Idyana
Yak Posting Veteran

82 Posts
Posted - 05/12/2012 :  20:48:39  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 05/12/2012 :  20:57:12  Show Profile  Reply with Quote
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/
Go to Top of Page

Idyana
Yak Posting Veteran

82 Posts
Posted - 05/12/2012 :  21:04:24  Show Profile  Reply with Quote
??

Got sir. I can send my full scripts to you. Give me your email
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 05/12/2012 :  21:16:33  Show Profile  Reply with Quote
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/
Go to Top of Page

Idyana
Yak Posting Veteran

82 Posts
Posted - 05/12/2012 :  21:25:42  Show Profile  Reply with Quote
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 ...................
*/
Edited by - Idyana on 05/12/2012 21:28:42
Go to Top of Page

Idyana
Yak Posting Veteran

82 Posts
Posted - 05/12/2012 :  21:55:49  Show Profile  Reply with Quote
I'm crazy stuck. Need help on add appropriate index, re-writing T-SQL, and anything
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 05/12/2012 :  22:15:12  Show Profile  Reply with Quote
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/
Go to Top of Page

Idyana
Yak Posting Veteran

82 Posts
Posted - 05/13/2012 :  00:27:44  Show Profile  Reply with Quote
The result was correct. I only need to fill up tbl_MST_Pemohon_idx, tbl_DirKursus_Jurisdiction_idx in tbl_MST_Penempatan
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
311 Posts
Posted - 05/13/2012 :  07:52:54  Show Profile  Reply with Quote 

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.


Vijay is here to learn something from you guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 05/13/2012 :  15:23:04  Show Profile  Reply with Quote
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/
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2111 Posts
Posted - 05/14/2012 :  11:23:28  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000