How to speedUp Random selecting in SQL Server

MoHaMmAd

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 :

enter image description here

Thanks ...

Abhay Chauhan

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

selecting random rows with normal distribution based on a column in SQL Server 2012

From Dev

selecting random rows with normal distribution based on a column in SQL Server 2012

From Dev

Selecting in SQL Server 2008

From Dev

How to generate random data in SQL server

From Dev

How to speedup codeigniter/php pagination, sql-query

From Dev

Selecting N rows in SQL Server

From Dev

Selecting a group of dates in SQL Server

From Dev

Selecting N rows in SQL Server

From Dev

Sql Server 2008 Selecting Distinct

From Dev

How to speedup this query?

From Dev

SQL Server Insert Random

From Dev

How to generate random boolean value in sql server 2008?

From Dev

How to Generate Alphanumeric Random numbers using function in SQL Server 2008

From Dev

How to generate random passwords in SQL Server using stored procedure?

From Dev

How can I generate a random timestamp appended to getdate() in SQL Server?

From Dev

How to generate 100000 no repeat random numbers in SQL SERVER2008

From Dev

How to Generate Alphanumeric Random numbers using function in SQL Server 2008

From Java

SQL Server query - Selecting COUNT(*) with DISTINCT

From Dev

Selecting between quotes (") in SQL Server 2012

From Dev

SQL Server - Selecting periods without changes in data

From Dev

SQL Server 2008 : selecting one record, group by

From Dev

sql server - selecting top x rows by group

From Dev

Selecting record range dynamically in a SQL Server query

From Java

SQL server getting random number of random rows

From Dev

SQL Server random number generator is not random

From Dev

How to speedup iOS Xcode compilation

From Dev

How to speedup the bootstrap of single node

From Dev

How to calculate execution time (speedup)

From Dev

How can I get count of one select during selecting in SQL Server

Related Related

  1. 1

    selecting random rows with normal distribution based on a column in SQL Server 2012

  2. 2

    selecting random rows with normal distribution based on a column in SQL Server 2012

  3. 3

    Selecting in SQL Server 2008

  4. 4

    How to generate random data in SQL server

  5. 5

    How to speedup codeigniter/php pagination, sql-query

  6. 6

    Selecting N rows in SQL Server

  7. 7

    Selecting a group of dates in SQL Server

  8. 8

    Selecting N rows in SQL Server

  9. 9

    Sql Server 2008 Selecting Distinct

  10. 10

    How to speedup this query?

  11. 11

    SQL Server Insert Random

  12. 12

    How to generate random boolean value in sql server 2008?

  13. 13

    How to Generate Alphanumeric Random numbers using function in SQL Server 2008

  14. 14

    How to generate random passwords in SQL Server using stored procedure?

  15. 15

    How can I generate a random timestamp appended to getdate() in SQL Server?

  16. 16

    How to generate 100000 no repeat random numbers in SQL SERVER2008

  17. 17

    How to Generate Alphanumeric Random numbers using function in SQL Server 2008

  18. 18

    SQL Server query - Selecting COUNT(*) with DISTINCT

  19. 19

    Selecting between quotes (") in SQL Server 2012

  20. 20

    SQL Server - Selecting periods without changes in data

  21. 21

    SQL Server 2008 : selecting one record, group by

  22. 22

    sql server - selecting top x rows by group

  23. 23

    Selecting record range dynamically in a SQL Server query

  24. 24

    SQL server getting random number of random rows

  25. 25

    SQL Server random number generator is not random

  26. 26

    How to speedup iOS Xcode compilation

  27. 27

    How to speedup the bootstrap of single node

  28. 28

    How to calculate execution time (speedup)

  29. 29

    How can I get count of one select during selecting in SQL Server

HotTag

Archive