0

SQL server hangs when I try to run a simple Select Distinct query from a linked table. "Executing Query. Waiting for response from data source." Then returns an error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

SELECT DISTINCT UR_RISK_ID AS [Risk ID]
FROM         FWLIVE..LMDATA.UDS_RISK

Without the "distinct" it is fine and runs very quickly.

Any thoughts why?

user6184786
  • 21
  • 6
  • what happens when you discard result sets :http://stackoverflow.com/questions/6386587/how-to-execute-sql-query-without-displaying-results – TheGameiswar Oct 10 '16 at 15:12

1 Answers1

1

The select distinct is essentially the same as:

SELECT UR_RISK_ID AS [Risk ID]
FROM FWLIVE..LMDATA.UDS_RISK
GROUP BY UR_RISK_ID;

In other words, the engine has to do a lot of work to remove the duplicates. I would suggest creating an index on UDS_RISK(UR_RISK_ID), so the index can be used for the query.

I should also note that the SELECT can start returning rows as they are encountered. The SELECT DISTINCT needs to process all the data before returning any rows. If you have a unique id in the table, then there might be other options that returns some rows more quickly.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Will this do better `select * from (select row_number().. from ) a where rn = 1` just curious – Pரதீப் Oct 10 '16 at 15:14
  • I would expect `select distinct` to be faster because the engine would (probably) assign `rn` to each row and then do the filtering. The `distinct` would just filter. But, SQL Server might have optimizations so the two are essentially the same. – Gordon Linoff Oct 10 '16 at 15:19