Multiple Local Variables the best way to go ? 1

[Kalisto]

I have a Database Table that defines a Product.
Each Product has 3 Different Options, each of which is actually a set of items in a different table, referred to by a foreign key.

eg, Colours this item is available in, Sizes it is available in, Accessories that may be supplied.

I now have a requirement to retrieve all the options for a product in the most efficient way possible.

It seems to me that 3 stored procedures, each of which returns one of the subsets is inefficient, as a single operation to find all the options will involve 3 calls from my code to the SQL, and make the DB perform 3 almost identical options.

My next thought was to retrieve all the data in a single stored procedure, and use UNION to return a single table of all the options.

i.e.

SELECT * FROM ColoursAvailable where ColourSetId = pfkColourSetId

UNION

SELECT * FROM SizesAvailable where SizeSetId = pfkSizeSetId

UNION

SELECT * FROM AccessoriesAvailable where AccessorySetId = pfkAccessorySetId

that works, but the foreign keys are not available to my code at this time. Now this means that I either need to expand my objects to allow for 3 additional keys, simply so I can query the DB, or I use the Id of the product to determine the foreign keys.

Which leads me to this.

DECLARE @Colours AS INT
DECLARE @Sizes AS INT
DECLARE @Accessories AS INT

SET @Colours = 
  (SELECT pfkColourSetId from Products
   WHERE Product.Id = @Id)

SET @Sizes = 
  (SELECT pfkSizeSetId from Products
   WHERE Product.Id = @Id)

SET @Accessories = 
  (SELECT pfkAccessorySetId from Products
   WHERE Product.Id = @Id)

 
SELECT * FROM ColoursAvailable where ColourSetId = @Colours

UNION

SELECT * FROM SizesAvailable where SizeSetId = @Sizes

UNION

SELECT * FROM AccessoriesAvailable where AccessorySetId = @Accessories

Which again works, but it seems inefficient to be making what is now 6 select statements to retrieve my data.

So, is there a way of setting 3 Local Variables at once (I tried using set and a select but that didnt work)

or is there a better way of retrieving and returning the data from 3 tables, where I only have the Id of a record in another table that has foreign keys to the 3 sets?

 

[gmmastros]

I'm not saying this is the best way (overall), but....

Select @Colours = pfkColourSetId,
       @Sizes = pfkSizeSetId,
       @Accessories = pfkAccessorySetId
from   Products
WHERE  Product.Id = @Id)

This will allow you to set the 3 variables at the same time.

Also..... UNION is probably NOT what you want. UNION will perform a DISTINCT operation over (and amongst) each query. UNION ALL will allow you to return duplicates. In your case, there won't be duplicates, so UNION ALL will be faster because it's not performing the distinct operation.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Kalisto]

Thanks, ok, that answers my question.

But, as you say its not the best way overall, and Im always looking to learn / improve, how would you approach my problem differently ?

 

[gmmastros]

Well.... you mention primary keys and foreign keys, so I assume all the key columns are indexed. As such, I would probably write 3 queries and UNION all. I wouldn't bother with local variables.

SELECT * 
FROM   ColoursAvailable 
       Inner Join Products
         On ColoursAvailable.ColourSetId = Products.pfkColourSetId
Where  Products.Id = @Id

UNION ALL

SELECT * 
FROM   SizesAvailable 
       Inner Join Products
         On SizesAvailable.SizeSetId = Products.pfkSizeSetId
where  Products.Id = @id

UNION ALL

SELECT * 
FROM   AccessoriesAvailable 
       Inner Join Products
         On AccessoriesAvailable.AccessorySetId = Products.pfkAccessorySetId
where  Products.Id = @id

You may (incorrectly) think that joining to the Products table 3 times (each for the same row) will be inefficient. Given that you are filtering on the Primary Key column.... this join will be extremely efficient, even if you have billions of rows in the table.

There are other things you could probably do to speed this up to. My suggestion is to try this query. If it takes longer than 10 milli-seconds, let me know and we can start playing around with indexes to improve performance. I doubt it will be necessary though.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Kalisto]

Ok, thanks. I'll test the running speed tomorrow, we dont have billions of rows in the table, but its nice to plan for some expansion

I actually thought that the inner joins would be more inefficient than the way I was doing it, assuming that creating / assigning the local variables once and then running the 3 querys without joins would be better.

Goes to show, you do learn something new every day !

Thanks, I'll be back if it runs slower than expected!