![]() It's probably assuming the implied row goal of 1 from the EXISTS (remember, it's transforming the IF (SELECT COUNT.)>0 to an EXISTS) is going to reduce the rows coming out of some of the operators much more than it actually is in this case. Specifically, I would guess that in the actual plan for the long-running query that the outer input for the nested loop operator joining the Sort output to the scan of InvTransactions has well more than 1 row, and that the scan is reading more than 76,000 rows for each execution (note that in the "good" plan the estimated row count for that scan is 2.6 million). In this case, I suspect we would see something like those discrepancies at work. The estimated plans lack information that could be helpful (discrepancies between estimated and actual rows, and between estimated executions and actual executions, for example). ![]() I know the actual plan for the bad query would be rough to acquire, if it's taking 2.5 hours to run, but that would be nice to have. The second question should be more easily answered than the first, but sometimes the optimizer's assumptions are very, very wide of the mark. With the IF.>0, you're just asking if 1 such row exists, and you don't need the exact count. Without the IF, the query is asking for the actual count of rows from the view that meet the WHERE clause criteria. Remember, to SQL Server, the queries are not the same.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |