Hi, 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 featch.is there any possibility to feacth the entire recors in 5 to 10 mins of time. Please advice.

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.

You may need to add an index. More information: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/indexes?view=sql-server-ver16

Hi @ Raj0125 You can refer to this link: https://medium.com/learning-sql/12-tips-for-optimizing-sql-queries-for-faster-performance-8c6c092d7af1 . Best regards, Aniya

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?

Improve performance of select statement

Accepted answer

Dan Guzman 9,236 Reputation points

2023-05-11T11:15:50.1066667+00:00

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.
Please sign in to rate this answer.

1 person found this answer helpful.
Raj0125 511 Reputation points

2023-05-12T08:06:05.5166667+00:00

Thank for Reply.I am planning to create clustered Index on skey column.so that Insertion and select will improve the performance.

We have only 5 columns in the table.width of the table is less but depth is more.More than 10 million records are exists in table.

Thank you

Dan Guzman 9,236 Reputation points

2023-05-12T11:35:06.3433333+00:00

@Raj0125 , out of curiosity, how many rows in the table? The clustered index should provide a seek in the query plan so that only the specified skey rows are touched for the minimal database server time.

Raj0125 511 Reputation points

2023-05-12T12:23:23.8+00:00

@Dan Guzman

12 Million records are there in table.

Dan Guzman 9,236 Reputation points

2023-05-12T12:35:33.06+00:00

12 Million records are there in table

I expect the clustered index will improve SELECT performance slightly (17%) by eliminating the 2 million rows not returned. That probably won't be enough to meet your objective of a 60%+ improvement. In that case, run the query in SSMS as I suggested to capture the bytes received from server. Also, check for blocking during query execution as that will increase times due to the waits.

Raj0125 511 Reputation points

2023-05-12T14:02:01.5766667+00:00

@Dan Guzman

I need to use the select statement in ETL transformation to feach the records for furthur processing.In this case ssms bytes info capture would not help right.Is there any other way to better optimize rather than Clustered Index in this case.

Dan Guzman 9,236 Reputation points

2023-05-12T14:52:01.2233333+00:00

There is no faster way to retrieve data than a clustered index seek. How long does the query run in SSMS?

Raj0125 511 Reputation points

2023-05-12T15:57:10.6533333+00:00

@Dan Guzman it is taking 32 minutes to featch 12 million records in ssms.

Dan Guzman 9,236 Reputation points

2023-05-12T16:18:45.7666667+00:00

@Raj0125, 32 minutes is much longer than it should be, IMHO. Have you monitored server activity for blocking during query execution?

Dan Guzman 9,236 Reputation points

2023-05-12T16:49:22.6266667+00:00

I just ran a 12M row query returning 5 columns of 500-bytes total in SSMS with the discard results option and the duration was under 40 seconds. Same query with results with the results displayed in grid took 352 seconds due to client processing time (rendering results). If you see a observe a similar difference in your environment and have ruled out other server activity (including blocking), it indicates the ETL performance needs optimization and/or more compute to meet your SLA.

Raj0125 511 Reputation points

2023-05-12T22:15:37.65+00:00

@Dan Guzman Is there any indexing coloumns in your table.

Dan Guzman 9,236 Reputation points

2023-05-13T14:11:33.34+00:00

The execution plan in my case used a clustered index seek because the WHERE clause specified the leftmost index column. The other indexes on the table were not used since the clustered index was the most efficient.

Raj0125 511 Reputation points

2023-05-13T16:25:54.7333333+00:00

I also try create Cluseterd index on skey column and update you.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Improve performance of select statement

3 additional answers

Your answer