Wait for query to finish before returning results

  • When I do "select * from ... " from a large table, the result starts to "stream" before the query is completely done. Is there a way to prevent this? Some kind of oppositve of the "FAST" queryhint?

  • If your query is showing few data before it is completing the query execution, it means that the query is not blocked to stream data before execution.

    Include Order By at the end of your query to see if that helps you.

  • I'm a little confused as to why this is a problem. If you have a way to tell SQL Server, hold all the data on this query, then, you have to have the equivalent amount of memory to your entire table, or, SQL Server will spill it all to disk in tempdb as it gathers it, then have to read it off of tempdb, again, just like it's reading it from your disk. In short, this sounds like a great way to bring SQL Server to it's knees in terms of performance.

    Why is having SQL Server stream the results causing you issues?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I'm "playing around" with a script that feeds Reporting Services that starts another script. It seems (as far as I can see) the last script starts before the first one is actually finished and both scripts are pretty brutal in terms of performance, so running one at a time would be better (?)

  • Then you may need to create a temporary storage between the two. Load a table somewhere, then call the other query.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply