Please help me with SQL query optimization

Question

Hi Team,

Please find the attached query. It is giving around 86M+ records as output. But while I am using Order By clause the query it getting stuck and even for top 100 records it is taking time. Please suggest.

Answer 1

Please find the attached query.

There is no query, only a photo of something which might could be a SQL query.

That's to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 2

SQL Query:
SELECT
concat(ep.endpoint_id,ep.version_number,client_received_start_timestamp,response_status_code) as u
,ep.endpoint_id
,ep.version_number
,client_received_start_timestamp
,client_received_start_date
from table1 ap
left outer join table2 ep on ap.api_endpoint=ep.api_endpoint
order by ep.endpoint_id,ep.version_number,client_received_start_timestamp

Answer 3

Since you're selecting into a temp table, there is no point in using ORDER BY (unless you want only a portion of records). In the latter case you may try to use ROW_NUMBER() approach and CTE to select rows you need.

Answer 4

Sorting 86 million rows will take quite some time. The only way to avoid that is to have indexes that support the ORDER BY clause.

Although, insult could be added to injury, if SQL Server misestimates the number of rows, and think that only, say, a million rows will be returned, since the memory grant will be too small, leading to serious sort spill.

We would need to see the estimated execution plan. You can upload it on http://www.pastetheplan.com. Even better would be the actual execution plan, since the query does not complete, I guess we will have to be without it.

It would also help to have the CREATE TABLE and CREATE INDEX statements for the two tables.

Answer 5

Hi，@Subhomoy Chakraborty

Welcome to Microsoft T-SQL Q&A Forum!

1->order by is useless as part of select into#temp (no identification, no clustered index), by adding an index to the temporary table, and then adding it after the data is loaded into the table;
2->Put the code into the CTE, you can try to create an index on the api_endpoint column in the CTE:

CREATE CLUSTERED INDEX AP_Index ON #TEMP1(api_endpoint);  

I suggest you refer to this article.
Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.