Post reply

Computed Column in optimizing query returning large data

  • November 16, 2016 at 1:04 pm

    #1912908

    comic_rage (11/16/2016)

    was able to improve the query by moving the isnull to the select and adding the column to a filtered index. Was able to reduce query time from 9+ min to 7+ min. Also changed the where clause to something like column = 1. As many posts have stated, avoid using ISNULL in the whereclause.

    Have you tested whether or not your grid will handle this volume of data? Or the impact of adding an additional index to your base table? Have you measured the time from calling the query to the grid becoming responsive? Are your users prepared to wait ten minutes or more before they can resume work with the application?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • November 16, 2016 at 1:55 pm

    #1912914

    comic_rage (11/16/2016)

    was able to improve the query by moving the isnull to the select and adding the column to a filtered index. Was able to reduce query time from 9+ min to 7+ min. Also changed the where clause to something like column = 1. As many posts have stated, avoid using ISNULL in the whereclause.

    What was the change in the execution plan. Is it now using index and bookmark lookups instead of table scan.

    Also, run DBCC DROPCLEANBUFFERS and then re-test.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 2 posts - 16 through 16 (of 16 total)

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