Why does the query become slow after adding the where condition

Andrew Huang (Beyondsoft Corporation) 271 Reputation points
2021-09-08T01:15:45.177+00:00

No where condition, query takes about 20s 

SELECT 
COUNT_BIG(*) AS UserCount
FROM OPENROWSET(
BULK 'adl://adPlatform-AudienceIntelligence-Scoring-proxy-c11.azuredatalakestore.net/local/IEBKS/XboxAol/Config/Custom_All_History.ss',
FORMAT = 'SStream',PARSER_VERSION = '2.0')
WITH(
    GameId BIGINT,
    LastDate DATETIME2
)
AS ss

Added where condition, took longer than 10min, caused timeout to close connection.：
Websocket connection was closed unexpectedly. 

SELECT 
COUNT_BIG(*) AS UserCount
FROM OPENROWSET(
BULK 'adl://adPlatform-AudienceIntelligence-Scoring-proxy-c11.azuredatalakestore.net/local/IEBKS/XboxAol/Config/Custom_All_History.ss',
FORMAT = 'SStream',PARSER_VERSION = '2.0')
WITH(
    GameId BIGINT,
    LastDate DATETIME2
)
AS ss
WHERE
GameId =60666752
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,395 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 77,751 Reputation points Microsoft Employee
    2021-09-08T11:05:57.673+00:00

    Hello @Andrew Huang (Beyondsoft Corporation) ,

    Thanks for the question and using MS Q&A platform.

    If your query fails with the error message: 'Websocket connection was closed unexpectedly', it means that your browser connection to Synapse Studio was interrupted, for example because of a network issue.

    To resolve this issue, rerun this query. If this message occurs often in your environment, advise help from your network administrator, check firewall settings, and visit this troubleshooting guide for more information.

    • If the issue still continues, try Azure Data Studio or SQL Server Management Studio for the same queries instead of Synapse Studio for further investigation.
    • If the issue still continues, create a support ticket through the Azure portal .

    For more details, refer to Azure Synapse - Self-help for serverless SQL pool.

    Hope this will help. Please let us know if any further queries. Thank you.

    ---------------------------

    • Please don't forget to click on Accept Answer button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators