SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Indexing Temporary Tables..

    Does anyone here put indexes on #temp tables, inside a stored proc, for example?

    I can certainly understand the need when thousands, ten of thousands or more records are concerned, but what's your take on the idea when you throw 25 records into a temp table? The table scan in the execution plan is just a horrid sight!

    Normally, I would say "Well, who cares for 25 records!" The thing is, I have a couple of procs that get hit very, very often (like the one that lists products, their categories, and subcategories on my nav bar)

    Is there any point in indexing in that case?

    Dan

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nav bar query should be in query cache, you should not hit the stored proc more than once a day (or however often you change your navigation LOL)

    what scripting language are you using? does it offer query caching?

    anyhow, no, you wouldn't need to index a 25-row temp table

    more promising would be to avoid the temp table in the first place!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Rudy..

    Yeah, caching will have to be part of Phase II as I have to get this site up and running asap.. Traffic will be very low to start, anyway. (not to make excuses) I'm all for coding queries properly! I just couldn't find a way around this one..

    I have a one-to-many relationship between SubCategories and the Items contained within them. I need to list the subcats, even if there are no Items associated with them. (sounds easy, but there's a catch..)
    Code:
    SubCategoryID SubCategory              Count  
    ------------- ------------------------ ------ 
    1             Banana With Logos        82
    2             Banana With CZ Stones    0
    3             With Attached Charms     2
    4             With Dangling Charms     1
    5             Spirals                  0
    6             Hand Painted Ceramics    0
    7             UV Acrylic Collection    0
    8             Ultra Jeweled            0
    9             Fancy                    1
    10            Sterling Silver          1
    If I use a single statement with a GROUP BY clause, then I do not get the records where there is a zero item count. Allow me to explain.

    The SELECT statement I used initially had a LEFT JOIN, giving me all of the subcategories by the nature of the join. The only problem is that I need to filter the items based on Enabled = 1 and Parent = 1. This filtration participates in keeping my COUNT(*) accurate, but then removes subcategories from my result set, defeating the purpose of the LEFT JOIN. -A double-edged sword of sorts.

    It took me a while to realize all this, but by separating the query into two parts, I can get the accurate counts AND the complete list of subcats. Sounds right to you, no?

    I'm an ASP programmer.. Since I already have an atomic function for displaying the menu, I can just store the HTML output in server memory. It's a single-server website, now.

    -Dan

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by danfran
    The SELECT statement I used initially had a LEFT JOIN, giving me all of the subcategories by the nature of the join. The only problem is that I need to filter the items based on Enabled = 1 and Parent = 1. This filtration participates in keeping my COUNT(*) accurate, but then removes subcategories from my result set, defeating the purpose of the LEFT JOIN. -A double-edged sword of sorts.
    you probably had those conditions in the WHERE clause instead of the ON clause where they belong!

    i can confirm this if i could see the query

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you are correct, I had those conditions in the WHERE clause..

    select SC.SubCategoryID, SC.SubCategory, count(*) as Count
    from tblSubCategories SC left join tblItems I on SC.SubCategoryID = I.SubCategoryID
    where I.Parent = 1 and I.Enabled = 1
    group by SC.SubCategoryID, SC.SubCategory


    The other problem I ran into is that the COUNT(*) function did what it's supposed to do, count the rows. I was technically getting the correct count of "1", even though there was a "0" item count. Oops.. I just realized that it should read COUNT(I.ItemID). That helps.. (is there a 'duh' smiley?)

    Now.. taking your advice, I have this: (which works)

    select SC.SubCategoryID, SC.SubCategory, count(I.ItemID) as Count
    from tblSubCategories SC left join tblItems I on SC.SubCategoryID = I.SubCategoryID and I.Parent = 1 and I.Enabled = 1
    group by SC.SubCategoryID, SC.SubCategory


    THANK YOU!!!

    Dan

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by danfran
    (is there a 'duh' smiley?)
    if there were, i'd use it a lot myself

    i take it the temp table is no longer required?

    isn't it nice when you ask "how can i do such-and-such" and the answer turns out to be "you don't need to"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, the temp table is no longer needed.

    #See ya!


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
  •