CODEWITHSUNDEEP
phpsql-servervariablesin-memory
LeeTee
LeeTee

Reputation: 6601

mssql - when /how to use table variables

I am scripting in PHP and using a MsSQL database.

My script loops though a list of product ids. For each id it creates an object of product data. For the 'postage' field within the product object I call a postage class that performs another query. 

$products = $productsObj->getProducts($i, $limit);

foreach($products as $product)
{

echo $product['productName'] . '|';
echo $product['productDescription'] . '|';
echo $product['productPrice'] . '|';

$postage = $postageObj->getPostageCostforProduct(1, $product['weight'], $product['productPrice']);

echo $postage . '|';

}

I have been told that I could possibly make use of 'table variables' to speed up this process in the event of lots of products. However, I am struggling to understand how this can help and how to implement this. Would I replace my existing postage query in the postage class with a table variable query? If so, exactly how does this work and help server load?

EDIT

The query to get a list of product IDs is:

SELECT * FROM  FROM products WHERE category='$this->cat'

I then loop through each of them and create and object for each product. The query to get the products data for the object is:

SELECT  p.id as prod_id, * FROM products AS p WITH(NOLOCK)
WHERE (p.id = '$this->prod_id')

When displaying the data I call the following query to get the postage:

SELECT Postage.postageName, Postage.Description AS postagedesc, Postage.id, Postage.qtyPer, Postage.pricePer, Postage.groupID, Groups.country_filter FROM  Postage 
LEFT OUTER JOIN Groups ON Postage.groupID = Groups.id 
WHERE  (Postage.minimumQty <= '$qty') AND (PostageRules.maximumQty >= '$qty') 
 (Postage.minPrice <= '$price') AND (Postage.maxPrice >= '$price')

Upvotes: 0

Views: 504

Answers (1)

Andriy M
Andriy M

Reputation: 77737

You could retrieve the products along with their postage cost values in a single query, using an outer join to Postage:

SELECT
  pr.productName,
  pr.productDescription,
  pr.productPrice,
  pr.productWeight,
  po.postageName,
  po.Description AS postagedesc,
  po.id,
  po.qtyPer,
  po.pricePer,
  po.groupID,
  gr.country_filter
FROM products AS pr
  LEFT JOIN Postage AS po
    ON po.minimumQty <= pr.productWeight
   AND po.maximumQty >= pr.productWeight
   AND po.minPrice   <= pr.productPrice
   AND po.maxPrice   >= pr.productPrice
  LEFT JOIN Groups  AS gr
    ON po.groupID = gr.id
WHERE pr.category = '$this->cat'

I guess, this query could be defined under a different name in productsObj (say, as getProductsWithPostageCosts) and used, perhaps, like this:

$products = $productsObj->getProductsWithPostageCosts($i, $limit);

foreach($products as $product)
{

echo $product['productName'] . '|';
echo $product['productDescription'] . '|';
echo $product['productPrice'] . '|';
echo $product['postageName'] . '|';
echo $product['postagedesc'] . '|';
...
echo $product['country_filter'] . '|';

}

Upvotes: 1

Related Questions