Optimisation of Soft-Config Settings

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Optimisation of Soft-Config Settings

Author

Topic

Kristen
Test

22859 Posts

Posted - 2011-03-24 : 14:21:53

Hi Guys.

Just trying to work how to plan a query so that it will be optimal.

When a customer looks at a product we decide if it is out of stock, and if so whether the user should see "Email me when in stock" or "Back order". For stuff that is in stock they will see "Buy".

Customer may be logged in, or not (i.e. anonymous)

So for a given customer we have a "CanBackOrder" flag.

Not logged in? Get the default value (from a Config Table)

Logged in?

Check if they have a customer specific flag set to Yes/No

If that is NULL (usually) then check the profile assocaited with that account - that will be either Yes/No or NULL - if NULL use the same Config Setting as above.

So now we know if the Customer is allowed to back order.

Then check the product CanBeBackedOrdered flag. That will be Yes/No or NULL. If NULL then get the System Default flag (not the same as for the customer, this is a setting for the Brand/Store that the customer is shopping on). It also comes from [a different row in] the CONFIG table.

I could put all this in a Function easily enough.

So lets say I have something like:


SELECT Col1, Col2, ...
     [Button] = CASE WHEN StockLevel > 0 THEN 'Buy'
                     WHEN dbo.fnCanBackOrder(@CustomerID, P.CanBeBackedOrdered) = 1 THEN 'BackOrder'
                     ELSE 'Email when in stock'
                     END
FROM Products AS P
WHERE ... logic to get me a "page" of 20 products ...

Problem with this is that the function will use @CustomerID to get the CanBackOrder value for the customer. Will that lookup be repeated for each row? and will it be executed at all if all products in the resultset are in stock (i.e. StockLevel > 1)

I could get a value for @CustomerCanbackOrder before running the query, but if the query didn't find any back order products that would bave been an unnecessary waste of time.

For 99% of the SELECTs, or more, all rows/products will be in stock. Thus the fact that the "Can This Customer Backorder and Can This Product Be Backordered" logic is complex I would like to avoid it all together if the product is in stock.

Hopefully you get the gist of what I'm trying to do? I'd appreciate any suggestions..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 14:31:21

What about:

DECLARE @CanBackOrder bit
SELECT @CanBackOrder=CASE WHEN CanBackOrder='Yes' THEN 1 ELSE 0 END FROM Customers WHERE CustomerID=@CustomerID
SELECT Col1, Col2, ...
     [Button] = CASE WHEN StockLevel > 0 THEN 'Buy'
                     WHEN @CanBackOrder=1 AND P.CanBeBackedOrdered) = 1 THEN 'BackOrder'
                     ELSE 'Email when in stock'
                     END
FROM Products AS P
WHERE ... logic to get me a "page" of 20 products ...

Unless that function is a lot more complicated (post the code?) I can't see why that wouldn't work.

Kristen
Test

22859 Posts

Posted - 2011-03-24 : 16:31:58

My thought is that


SELECT @CanBackOrder=CASE WHEN CanBackOrder='Yes' THEN 1 ELSE 0 END FROM Customers WHERE CustomerID=@CustomerID

will be called every time, but that value is only needed when one, or more, of the rows selected from PRODUCTS table has StockLevel = 0 (which is a very rare occurrence). So I would prefer not to derive that until it was found to be needed.

I could do:


SELECT Col1, Col2, ...
     [Button] = CASE WHEN StockLevel > 0 THEN 'Buy'
                     WHEN EXISTS (SELECT * FROM FROM Customers WHERE CustomerID=@CustomerID AND CanBackOrder='Yes')
                          AND P.CanBeBackedOrdered = 1 THEN 'BackOrder'
                     ELSE 'Email when in stock'
                     END
FROM Products AS P
WHERE ... logic to get me a "page" of 20 products ...

Will


(SELECT * FROM FROM Customers WHERE CustomerID=@CustomerID AND CanBackOrder='Yes')

only be executed once? or once-per-row in Products? (which would be bad!
)
and will it never be executed if all rows selected from PRODUCTS have "StockLevel > 0" (which would be true 99% of the time)

A potentially further problem is that this code needs to exists in lots of places, therefore I would like to put in a Function, but that would potentially make the query less efficient.

One thought I have had is to resolve


DECLARE @CanBackOrder bit
SELECT @CanBackOrder=CASE WHEN CanBackOrder='Yes' THEN 1 ELSE 0 END FROM Customers WHERE CustomerID=@CustomerID

for the Session, and store it in the session variables, and then pass it as a parameter to the Sproc - which would remove the need for any lookup at this point. (Still the issue that Products.CanBeBackedOrdered is not as simple as a single column lookup, there are other rules at play, most of which won't apply most of the time. I'll have a ponder whether I could move any of them to the Application variables and thus only calculate them once per Application.)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 16:56:47

Without hands-on testing I really couldn't tell you how it will perform, except to say...test both scenarios!

You knew that was coming.

Kristen
Test

22859 Posts

Posted - 2011-03-25 : 03:37:19

Yup!. Was juts looking for a bit of a "steer" from others who perhaps had tried Plan-A and Plan-B and had suggestions

It does strike me that getting a flag for @CanBackOrder upfront is a bit of an expense that I would like to avoid.

(This is an e-commerce application, and this relates to the Product Listing page, which gets the most hits and also has very complex business rules, so I want to make it as "cheap" to deliver as possible.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources