Improve performance of select statement

Raj0125 511 Reputation points


I have a requirment to use Query to featch 1 crore records in select statement from single table alone.

Select * from dbo.polHist where skey = 12567

Using the Query above to feach 1 crore records but it is taking 30 mins time to there any possibility to feacth the entire recors in 5 to 10 mins of time.

Please advice.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 9,221 Reputation points

    The time to return 10 million rows will depend on both query execution time on the database server, network, and processing time by the client application.

    An index on skey (or with skey as the first index column) will help optimize query execution time and, since you are selecting all columns, the index needs to be clustered. A full table scan may be needed if you don't have such a supporting index and increase query duration because every row in the table will need to be read and evaluated for the filter criteria even if not returned. This will be costly if the table contains many rows for other skey values.

    Network time and client processing time also affect overall duration. If rows are wide (especially MAX types), network bandwidth and latency may be limiting factor. It will take roughly 1.5 minutes to transfer 10M rows with a 1K row size over a 1gb network, not including server or client time. Large columns (MAX types) are especially problematic. If possible, specify an explicit column list to avoid returning columns not actually needed for your task.

    Execute the query from SSMS with the discard results after execution option (menu Query-->Query Options-->Results-->Grid-->Discard results after execution) and the include client statistics option (menu Query-->Include client statistics). This will report client processing time, bytes received from server, and wait time on server replies so you can focus your efforts accordingly.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Wilko van de Velde 2,226 Reputation points

  2. AniyaTang-MSFT 12,341 Reputation points Microsoft Vendor
    0 comments No comments

  3. Olaf Helper 42,576 Reputation points

    to feach 1 crore records

    1 crore = 100 lakh = 10 million rows?

    What for a performance improvement do you expect by that amount of data?