Professional Web Applications Themes

Index problem with Update - Microsoft SQL / MS SQL Server

I have two tables (Product and Category). Product has 2 columns: Product_temp_category_id and Product_final_category_id Category has 2 columns: Category_id and Category_default (int) I'm trying to improve this query. Overall it is updating the Product table with a final_category_id (from Category.Category_default) if Product_final_category_id IS NULL I have an index on Product_temp_category_id and on Category.Category_id but it is not using them. UPDATE Product SET Product_final_category_id= (SELECT TOP 1 Category_default FROM Category WHERE Product .Product_temp_category_id = Category.Category_id) WHERE Product_final_category_id IS NULL Help?...

  1. #1

    Default Index problem with Update

    I have two tables (Product and Category).
    Product has 2 columns: Product_temp_category_id and
    Product_final_category_id
    Category has 2 columns: Category_id and Category_default (int)

    I'm trying to improve this query. Overall it is updating the Product table
    with a final_category_id (from Category.Category_default) if
    Product_final_category_id IS NULL

    I have an index on Product_temp_category_id and on Category.Category_id but
    it is not using them.

    UPDATE Product SET Product_final_category_id= (SELECT TOP 1 Category_default
    FROM Category WHERE Product .Product_temp_category_id =
    Category.Category_id) WHERE Product_final_category_id IS NULL

    Help?


    Jason Davis Guest

  2. #2

    Default Re: Index problem with Update

    Jason
    I think If you perform select statement on the table so you would be concern
    about usage appropriate indexes in order to retrieve the data very quickly.
    In your case you are perfoming update statement so if it updates let me say
    clustered index than it will add some complexity to performance. I usually
    before any 'hard' update remove any indexes in order to improve transaction
    performance.




    "Jason Davis" <jasondavis19hotmail.com> wrote in message
    news:eQQanb6QDHA.2128TK2MSFTNGP12.phx.gbl...
    > I have two tables (Product and Category).
    > Product has 2 columns: Product_temp_category_id and
    > Product_final_category_id
    > Category has 2 columns: Category_id and Category_default (int)
    >
    > I'm trying to improve this query. Overall it is updating the Product table
    > with a final_category_id (from Category.Category_default) if
    > Product_final_category_id IS NULL
    >
    > I have an index on Product_temp_category_id and on Category.Category_id
    but
    > it is not using them.
    >
    > UPDATE Product SET Product_final_category_id= (SELECT TOP 1
    Category_default
    > FROM Category WHERE Product .Product_temp_category_id =
    > Category.Category_id) WHERE Product_final_category_id IS NULL
    >
    > Help?
    >
    >

    Uri Dimant Guest

  3. #3

    Default Re: Index problem with Update

    Is there any way to do it otherwise? maybe via an update of inner join
    field?

    Thanks

    "Uri Dimant" <uridiscar.co.il> wrote in message
    news:OssyUr6QDHA.1988TK2MSFTNGP12.phx.gbl...
    > Jason
    > I think If you perform select statement on the table so you would be
    concern
    > about usage appropriate indexes in order to retrieve the data very
    quickly.
    > In your case you are perfoming update statement so if it updates let me
    say
    > clustered index than it will add some complexity to performance. I
    usually
    > before any 'hard' update remove any indexes in order to improve
    transaction
    > performance.
    >
    >
    >
    >
    > "Jason Davis" <jasondavis19hotmail.com> wrote in message
    > news:eQQanb6QDHA.2128TK2MSFTNGP12.phx.gbl...
    > > I have two tables (Product and Category).
    > > Product has 2 columns: Product_temp_category_id and
    > > Product_final_category_id
    > > Category has 2 columns: Category_id and Category_default (int)
    > >
    > > I'm trying to improve this query. Overall it is updating the Product
    table
    > > with a final_category_id (from Category.Category_default) if
    > > Product_final_category_id IS NULL
    > >
    > > I have an index on Product_temp_category_id and on Category.Category_id
    > but
    > > it is not using them.
    > >
    > > UPDATE Product SET Product_final_category_id= (SELECT TOP 1
    > Category_default
    > > FROM Category WHERE Product .Product_temp_category_id =
    > > Category.Category_id) WHERE Product_final_category_id IS NULL
    > >
    > > Help?
    > >
    > >
    >
    >

    Jason Davis Guest

  4. #4

    Default Re: Index problem with Update

    Jason Davis (jasondavis19hotmail.com) writes:
    > I have two tables (Product and Category).
    > Product has 2 columns: Product_temp_category_id and
    > Product_final_category_id
    > Category has 2 columns: Category_id and Category_default (int)
    >
    > I'm trying to improve this query. Overall it is updating the Product table
    > with a final_category_id (from Category.Category_default) if
    > Product_final_category_id IS NULL
    >
    > I have an index on Product_temp_category_id and on Category.Category_id
    > but it is not using them.
    >
    > UPDATE Product SET Product_final_category_id=
    > (SELECT TOP 1 Category_default
    > FROM Category WHERE Product .Product_temp_category_id =
    > Category.Category_id) WHERE Product_final_category_id IS NULL
    You are not saying whether these indexes are clustered or not. If the
    index on Product_final_category_id is clustered, I would expect SQL
    Server to use it. As for the index on category_id, I would expect SQL
    Server to use it, no matter whether that index is clustered or not.
    Unless that table is very small.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

Similar Threads

  1. Problem with locked index.htm
    By Alex Mariño in forum Macromedia Contribute General Discussion
    Replies: 4
    Last Post: January 23rd, 02:25 PM
  2. Can index update when marked items move in the doent?
    By Tana_Reiff@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: September 21st, 12:44 PM
  3. Z-index problem
    By bhaktik in forum Dreamweaver AppDev
    Replies: 2
    Last Post: April 8th, 03:08 PM
  4. large index problem
    By Bill Peterson in forum Adobe Indesign Windows
    Replies: 0
    Last Post: June 28th, 03:24 PM
  5. index problem
    By Peter Notebaert in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 06:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139