Requesting for Performance Improvement on querying on Table in Azure Synapse

Question

I have a Table with 100k data rows in Azure Synapse. On running "Select Top(1000) from Table;" querry it takes 10s. If I run "Select * from Table;" it is takes 1560s and gets stuck. Is there anything that I am missing as it is taking a very long to fetch the data from table? 
And is there any tool where we can see the progress bar for the Querry which is run?

Answer 1

This looks quite strange to me. How is your network connectivity, aka latency to Azure?
For objective measuring these kind of queries, i suggest that you spin up an azure VM, like the data science VM.

This VM has SQL management studio installed, so you are sure that you query results are not delayed by a weak network connectivity. 

---

Rk

Answer 2

Hi Dimpu,

It has to do with with how Top returns the resultset of N versus how Select * from <table>. Please see TOP (Transact-SQL)

TOP is built-in and you likely would see similiar results with the select * statement if you increased your deployment service tier. 

I would start with the DMVs, such as the monitor query execution view:

-- Monitor active queries
SELECT * 
FROM sys.dm_pdw_exec_requests 
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

-- Find top 10 queries longest running queries
SELECT TOP 10 * 
FROM sys.dm_pdw_exec_requests 
ORDER BY total_elapsed_time DESC;

Another approach is to add a label to your select statement and then query for that label to see progress:

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it it a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

I hope this helps you but if you are seeking something more specific, please let us know.

Regards,

Mike

Answer 3

Just checking in to see if the above answer(s) helped.

 

If this answers your query, do click “Mark as Answer” and Up-Vote for the same which might be beneficial to other community members reading this thread .

And, if you have any further queries do let us know.