none
10 Million Plus row Table with 500K NOT NULL rows RRS feed

  • Question

  • So we have this query that is going up against a Rate Shop Response Table which has over 10.5 Million Rows...sooo it contains all the rate shop responses and there is only one-winner...where the Tracking Number is actually NOT NULL...500,000 "Winners".

    So when we query this table for the "Winners", it is taking FOREVER! There is an Index on the Tracking Number.

    Besides the obvious of querying NOT NULL and building and loading a table of "Winners" for reporting and extract purposes, is there a more efficient means of getting the NOT NULLs out of that table?

    Thanks for your review and am hopeful for a reply.

    Thursday, October 17, 2019 6:39 PM

All replies

  • Thanks for your review and am hopeful for a reply.

    Review of what?

    May I kindly remind you that this is a forum for technical assistence, and not a training camp for ladies with a crystal ball?

    We should need to see the CREATE TABLE and CREATE INDEX statements for the tables involved, the query itself, and the actual execution plan in XML format. The latter you cannot post here, but you can upload it at http://www.brentozar.com/pastetheplan


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, October 17, 2019 9:33 PM
  • Hi ,

    Thank you for your issue.

     

    Could you  please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

    Did you mean that you would like to improve your performance? In your query , you might have a WHERE clause 'where column is not null '. Please try to create an index on your 'column'.


     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 18, 2019 6:04 AM
  • You didn't tell us what version or edition you are using, so this may not work.

    But you could use a "filtered index" on tracking number where tracking number is not null.

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes?view=sql-server-ver15 

    Friday, October 18, 2019 4:33 PM
    Moderator