I have a table for phone numbers like this :
ID PhoneNumber Enabled GrupID CountryID
----------- -------------------- ------- ------ -----------
10444 ***001000999 1 NULL 1
10445 ***001000998 1 NULL 1
10446 ***001000994 1 NULL 1
10447 ***001000990 1 NULL 1
10448 ***001000989 1 NULL 1
This table has 68992507 rows.
I want to select some random phone number from it.
I can get my random number query by this stored procedure:
here I select random numbers, insert to a @table
and then update the selected numbers .
CREATE proc [dbo].[Mysp_GetRandom]
@countryid int,
@count int
as
declare @tbl table([ID] [int] ,
[PhoneNumber] [nchar](20) NOT NULL,
[Enabled] [bit] NULL,
[GrupID] [tinyint] NULL,
[CountryID] [int] NULL)
INSERT INTO @tbl
SELECT TOP (@count) *
FROM tblPhoneNumber
WHERE CountryID = @countryid
AND GrupID is null
ORDER BY binary_checksum(ID * rand())
UPDATE tblPhoneNumber
SET GrupID = 1
WHERE ID IN (SELECT ID FROM @tbl)
SELECT * FROM @tbl
The problem is that it takes a long time for the query to run. For example this query takes 12:30 minutes ...
DECLARE @return_value int
EXEC @return_value = [dbo].[Mysp_GetRandom]
@countryid = 14, @count = 3
SELECT 'Return Value' = @return_value
and I have an ndex on this table :
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150415-172433]
ON [dbo].[tblPhoneNumber] ([CountryID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Execution plan is as below :
Thanks ...
Add grupID to index key column and add other required columns in include clause of your NC index NonClusteredIndex-20150415-172433.
Execution plan is already giving you the same hint on adding missing index.
P.S Mark it as answer if it helped you.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments