Query optimisation help - SELECTs within SELECT? 2

[Ach005ki]

Hello tech experts, users and fellow novices like myself.
I'm in need of some help with my query text - when it runs it apparently spikes our server and hogs resource (but our dba won't see fit to letting us use anything other than MS Query for querying our database, with the queries usually being refreshed within MS Excel spreadsheets).

I can't actually paste in the exact code at present (Citrix server is down) but essentially the query contains four selects nested within the select command - two of which are selecting different records from the same table

SELECT InvSales.Warehouse, InvSales.Code, InvMaster.Descript, InvMaster.ExtraDesc, InvMaster.PackSize,  InvSales.Last12wksSales, InvWarehouse.QtyOnHand, (SELECT MAX(LastReceivedDate) FROM InvReceipts WHERE Code = InvSales.Code), (SELECT COUNT (Bin) FROM InvBins WHERE Code = InvSales.Code), (SELECT AlphaValue FROM CustomFormData WHERE Keyfield = InvSales.Code AND FieldName = 'Owner'), (SELECT AlphaValue FROM CustomFormData WHERE Keyfield = InvSales.Code AND FieldName = 'SubDiv') 
FROM InvSales INNER JOIN InvMaster ON InvSales.Code = InvMaster.Code, INNER JOIN InvWarehouse ON InvSales.Code = InvWarehouse.Code AND InvSales.Warehouse = InvWarehouse.Warehouse
WHERE InvSales.Warehouse = 'BC'
ORDER BY InvSales.Code

My question is can anyone help me optimise the code to help prevent the resource spike that apparently manifests when the query is run... btw the InvSales table in the code appears to be a dba-created view and not an actual table (and I think this is where the resource-eating begins but the dba won't confirm this for me)

Background - don't know if its relevant but hey!
SQL Server 2000 (dunno SP)
Syspro 6.09 (Frontend database app)

Query purpose:
Existing report shows slow / non-moving stock items.
Data is selected from a view "InvSales" which gathers stock-on-hand and sales-usage information for a particular client warehouse, and inner joins this by the item Code field (key field) to the InvMaster table to show item description, packsize and product-class.

New requirements:
a) latest receipt date of a stock item (contained within the InvReceipts table)
b) the number of pallet spaces occupied (contained within the InvBins table)
c) the subdivision of the item's productclass - stored as a customformfield in CustomFormData table
d) the named owner for that product classs / subdivision - which is again stored as a customformfield in CustomFormData table.

The latter two fields are a pain but are there due to existing database design constraints and the understandable reluctance of the dba in not allowing the existing productclass field to be linked to any other table with spare fields large enough to accommodate the class owner-name and subdivision.

I hope you haven't fallen asleep.

So I've had to take the original query - an MS Query which was graphical drag-n-drop wizard-built where the JOIN info was originally built into the WHERE clause.... and change it to a query that contains several SELECTs nested within the main SELECT, and specify the join type and conditions.

I sincerely hope someone can help guide a novice in this matter...

 

[Hmadyson]

First off you will want to make sure that your database is optimized for this query. You will want to make sure that there are indexes on the following fields:

InvSales.Warehouse
InvSales.Code
InvReceipts.Code
InvSales.Code
InvMaster.Code
InvSales.Warehouse
InvBins.Code
CustomFormData.FieldName
CustomFormData.KeyField

I am not sure how easy this is since you have to go through the dba. If you find that all of those fields have indexes on them, check to see if any of the tables(?) used in your query are made up of the same tables. Look at InvSales to see what tables make it up.

If you think that InvSales is your culprit, remove it from the query and see if it runs fast. If it does, then we have a place to start, and I can help you create something that runs better.

Please let me know if I can provide more assistance.

 

[cfStarlight]

In addition to what Hmadyson said:

You said you rewrote the query to use nested selects. Did you have this problem with the original query? I ask because subqueries can sometimes be poor performers. Did the original version (that used joins) also cause a resource spike?

 

[Ach005ki]

Thank you Hmadyson and cfStarlight, for your quick responses; I'm very grateful for your input and assistance.

Hmadyson & cfStarlight - I think the view/table InvSales isn't indexed. When the original query is opened it takes an age to appear; the query isn't set to auto-query and this suggests to me that the problem lies firstly with the view... even if my nesting of selects is exacerbating the problem.

Secondly, I'm sure that InvSales does use fields from InvWarehouse but as I'm currently locked into using MS Query I'm relying on the dba who - I'm presuming through a lack of time and resource - is giving me precious little advice / support.

Also, I think I oversimplified the bin select - normally this would be a straight inner join to return the count of bins for the item, but some bins are pallet spaces and others are small pick-faces, and they are differentiated with their prefix F & G respectively. The client is charged a higher storage rate for pallet spaces... so with slow moving stock it is a significant depreciator of stock value over time (longer storage, higher cost, less worth etc), hence their request for inclusion of number of pallet spaces in the report.

(SELECT COUNT (Bin) FROM InvBins WHERE Code = InvSales.Code AND warehouse = InvSales.Warehouse AND Bin LIKE 'F%')

Further info:
I recently found a query which didn't check for null values when evaluating a criteria; the result was only a subset of product information was being sent to our clients and that this may have been the case for some time. The dba had to make changes to the tables (rather than re-write all existing distributed reports)... perhaps in being helpful I also upset someone but this was never my intent!

 

[cfStarlight]

Well, its going to harder to diagnose the problem if you cannot get access to the relevant information, like the sql for the "views", index information, etc. But.. I know you have to work with what you're given.

The only thing I would add is - have you ruled out the simple causes already? For example

- Are you trying to retrieve more records than MS Query can comfortably handle

- Are the queries joining on all the proper columns? If they are missing a column join that could produce a cartesian product

- If you do a simple and separate select on each table/view in the from clause, what are the results? If the simple select is slow, that suggests a problem with the base object.

- If the simple queries run fast, start adding back the joins (one table/view at a time) and check the performance of each one.

 

[Ach005ki]

Hi Guys - further developments; dba gave me access to the sql for the view InvSales and it turns out that this view is a table with an IJ to another table (primary keys) but then also has four LOJs to four different views! More surprising is that each of the views appear to reference the same sales-data table in order to arrive at different sales-usage-periods??? I was promised the sql to this last evening but the Citrix server was again down during the evening (UK time of posting is now 8:00am approx).

When I get the sql I'll post up the actual code (my original code post was an abridged version which simplified table names and all of the joins, but only the relevant selected columns). Thanks for bearing with me.

One further question on this... I didn't know if I was posting in the correct forum for this query as I'm using MS Query as part of Office 2003 whilst querying tables from SQL Server 2000. I know MSQ was a Jet query engine upto Office 2000 but I thought I'd heard it may have then shipped as a lite SQL2000 query engine... so apologies if I'm posting my question in the wrong forum; I just thought this was the most relevant one.

Mark, HH Associates

 

[Hmadyson]

Yes, if a bunch of the views that you are using are actually just views on the same table, you will probably be better off referring to the underlying table once instead of to all of the views.

I do not know much about MS Query, but I know that many people use MS Access to get at SQL Server data without problems. One thing that you may want to look at is whether MS Query will allow you to create a pass through query like Access does. That way the processing will all be done on the server and may speed things up a bit.

I also know that there is a way to view query execution plans by changing a setting in the registry. There is definitely a way to see what the query execution plan is in SQL Server, so now that you have access that may be a great way to do it.

Please let me know if you need more assistance.

 

[Ach005ki]

Hello again cf & hma

I now have the full code from my new query - in my original example I simplified the query and changed the table names to protect the innocent; here I'm posting my native code.

The code is being executed through MS Query onto an SQL Server 2000 installation using Syspro as our ERP front-end app to the underlying SQL Server 2000 data.

If anyone can suggest ways in which I can streamline the data (I don't have the facility to create a view - tried it in MSQ but it fails and I don't have any other query tool atm) then I'd be very grateful. Well, here goes:

Purpose: to select and return a particular client's entire range of stock item codes, along with other connected information on the stock-holding and sales information.

Guys I am aware that the nested selects I've used are correlated subqueries and as such each subquery executes once for every row in the main query.

My original request was based on a report which was linking two tables to a view which contained at least four other views (one of which took info from both original tables) and for a while I had no access to the view sql definitions.

I rewrote my query once I received the sql code for the views (with my dba becoming ever more abusive on being asked for the code; wtf?)

Now I've resolved that particular report, my next task is to create a summary /overview by product class for stockholding and despatch information and the client has asked to introduce a product class subdivision and the name of the person within their organisation who is responsible for that product class, or the subdivision of the class where a subdiv exists.

[navy]Here's my code and I'd like to know if (and how) this could be optimised, bearing in mind I can't CREATE views or SPs...

My query works fine; I would just like to learn if I can tweak it whilst remaining within the boundaries in which I'm forced to work.[/navy]
I'm building myself an SQL Server so that I can develop my understanding, even if I cant make use of it fully whilst at work in my present employ!

I've tried to annotate it but I'm tired, its 3am and I need to post this NOW.... can anyone tell me how to go back & edit my own posts?

SELECT 
   InvMaster.StockCode AS 'StockCode', -- from Inventory Master file
   InvMaster.ProductClass AS 'PrdClass',
   SalProdClass.Description AS 'PrdClassDescription', -- from SalesAnalysis ProductClass file
   InvMaster.Description AS 'Desc', 
   InvMaster.LongDesc AS 'ExtraDescription', 
   InvMaster.AlternateKey1 AS 'PackSize', -- userdefined field used to denote an items packsize, e.g compliment slips are in packs of 50, 1 unit = pack of 50
   InvWarehouse.QtyOnHand AS 'InStock', -- from Inventory Warehouse file; each client's stock is held under different warehouse codes
   Max(InvFifoLifo.LastReceiptDate) AS 'LastReceipt', 
   HaveStock = 
	CASE InvWarehouse.QtyOnHand
		WHEN 0 THEN 0
		ELSE 1
	END, -- allows the summation of whether or not the item is in stock (to be used as a SUM in a pivot table for a count function)
   OnWeb = 
	CASE AFDWeb.AlphaValue -- customform data item (criteria in where clause) from table AdmFormData which is a table used again as a nested select...
		WHEN 'Y' THEN 'Yes'
		WHEN 'N' THEN 'No'
		ELSE '???'
	END, -- allows the summation of whether or not the item is visible to client on the web (to be used as a SUM in a pivot table for a count function)

/* HERE BEGINS MY SERIES OF NESTED SELECTS - I APPRECIATE THESE MAY BE BETTER SERVED AS VIEWS (OR EVEN SPs) but I can't create views with my level of access
** 
** 1st nested select (correlated subquery?) to return a count of bin locations which are pallet-spaces (others are pick-faces)
** 
** Uses InvMaster.StockCode as the code for which to obtain the number of the appropriate bin locations where stock is present for that client
**
*/
   (
	SELECT 
		count(InvMultBin.Bin)
	FROM 
		SysproCompanyH.dbo.InvMultBin InvMultBin 
	WHERE 
		InvMultBin.StockCode = InvMaster.StockCode
		AND InvMultBin.Bin like 'F%' 
		AND InvMultBin.QtyOnHand1 >$0 
   ) AS 'PalletCount',

/* 2nd nested select returns a subdivision of the ProductClass, and combines this with the client-owner
** (client owner of a subdivision, or of ProductClass where there is no subdivision)
**
** Product Class "subdivision" and "client owner" are new data items introduced by the client.
** Our dba advised there were no remaining fields available to link this to existing productclass data
** we had to use the CustomFormField capability of the Syspro system to introduce two extra fields (per stock item in InvMaster)
** these extra fields are stored in the AdmFormData table whose Keyfield is linked to the InvMaster.StockCode field
**
** this portion uses the AdmFormData table and links to itself as an alias to pik up both fields in the same pass
** both fields are then concatenated into one returned value passed back to the main query
*/

   (
	 SELECT 
	    (RTRIM(AFD.AlphaValue)+' : '+RTRIM(AFD_1.AlphaValue))
	 FROM 
	    SysproCompanyH.dbo.AdmFormData AFD
	    INNER JOIN SysproCompanyH.dbo.AdmFormData AFD_1
		  ON AFD.KeyField = AFD_1.KeyField 
		  AND AFD.FormType = AFD_1.FormType
	 WHERE 
	    (
		  (AFD.KeyField = InvMaster.StockCode) 
		  AND (AFD.FieldName='SubDiv') 
		  AND (AFD_1.FieldName='Owner')
	    )
   ) AS 'PC-Ctg-Owner',

-- 3rd nested select to return the 12wkusage of the stock item, taken from the InvMovements table
   (
	 SELECT 
	    SUM(TrnQty) 
	 FROM 
	    SysproCompanyH.dbo.InvMovements InvMovements
	 WHERE 
	    InvMovements.StockCode = InvMaster.StockCode
	    AND (MovementType = 'S')
	    AND (EntryDate >= DATEADD([DAY], - 83, { fn CURDATE() })) 
	    AND (EntryDate <= DATEADD([DAY], 0, { fn CURDATE() }))
   )  As 'Usage12Wks',


/* HELP REQUIRED HERE
** 4th nested select to return different elements of sales order throughput for the last 7 days
** Because nested selects (correlated subqueries) can only return one value I've had to string the values together
** and when the results are returned to excel I extrapolate the values based on the position of the changing delimiters
** delimiters are    ,  ;  :  #
*/

   (
	 SELECT 
		RTRIM(LTRIM(STR(SUM(SorDetail.MOrderQty))))+ ',' +
		RTRIM(LTRIM(STR(SUM(SorDetail.MBackOrderQty))))  + ';' +
		RTRIM(LTRIM(STR(SUM(SorDetail.MQtyDispatched)))) + ':' +
		RTRIM(LTRIM(STR(SUM(SorDetail.MShipQty))))  + '#' +
		RTRIM(LTRIM(STR(SUM(SorDetail.MStockQtyToShp)))) 
	 FROM 
		SysproCompanyH.dbo.SorDetail SorDetail
		INNER JOIN SysproCompanyH.dbo.SorMaster SorMaster
		ON SorDetail.SalesOrder = SorMaster.SalesOrder
		AND SorMaster.Warehouse = 'BC'
	 WHERE
		SorDetail.MStockCode = InvMaster.StockCode
		AND SorMaster.Warehouse = 'BC'
		AND SorMaster.OrderStatus NOT IN ('*', '\')  -- these are cancelled-order statuses
		AND SorDetail.MProductClass <> '_FRT'
		AND (DATEDIFF(DAY, SorMaster.OrderDate, GETDATE()) BETWEEN 7 AND 13)
/* 
** I'll refine this later so that it always configures the date range to be from the previous Monday to the previous Sunday's dates.
*/ 
   )  As 'Last7Days'


FROM 
   SysproCompanyH.dbo.InvMaster InvMaster
   
   INNER JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse 
	 ON InvMaster.StockCode = InvWarehouse.StockCode 
     
   INNER JOIN SysproCompanyH.dbo.SalProductClassDes SalProdClass
	 ON InvMaster.ProductClass = SalProdClass.ProductClass
     
   LEFT OUTER JOIN SysproCompanyH.dbo.AdmFormData AFDWeb
	 ON InvMaster.StockCode = AFDWeb.KeyField
	 AND AFDWeb.FieldName = 'WEBRQD'
     
   LEFT OUTER JOIN SysproCompanyH.dbo.InvFifoLifo InvFifoLifo 
	 ON InvWarehouse.StockCode = InvFifoLifo.StockCode 
	 AND InvWarehouse.Warehouse = InvFifoLifo.Warehouse

WHERE 
   InvWarehouse.Warehouse='BC'  
   
GROUP BY 
   InvMaster.StockCode, 
   InvMaster.Description, 
   InvMaster.LongDesc,
   InvMaster.AlternateKey1, 
   InvMaster.ProductClass, 
   SalProdClass.Description,
   InvWarehouse.QtyOnHand, 
   AFDWeb.AlphaValue

ORDER BY 
   InvMaster.StockCode

Mark, HH Associates

 

[Ach005ki]

I posted a similar and related request in the SQL Server Programming forum branch under a question on using derived tables and received a great deal of help in resolving my problem.

Thank you guys for your assistance with your very helpful comments and directions!

Mark, HH Associates