Solved

Query timeout expired message when running stored procedure in Access 2003 ade file

Posted on 2014-01-31
4
910 Views
Last Modified: 2014-03-01
Hi all.

I have an Access 2003 ade file that runs a stored procedure (the ade file's backend is SQL Server 2000) and recently we began getting the following message: "Query timeout expired".

Now, the table has grown a lot.

Below is the stored procedure. Any ideas what I can do to have the stored procedure run and not have that message pop up? Thank you in advance!

ALTER PROCEDURE InventoryOH
(	@PeriodName varchar(18)
)



AS
	SELECT OrderID, ProjectManager, CustomerName, Community, Lot, Block, TotalCosts, TotalBilling, ReturnDate, WorkType, ProductType, Warehouse, 
			(SELECT begindate FROM periods WHERE name = @PeriodName) AS begindate,
			(SELECT enddate FROM periods WHERE name = @PeriodName) AS enddate,
			(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS ReceiveDate,
			(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS IssueDate,
			(SELECT SUM(Price) FROM WorkOrderItems WHERE ItemType = '1' AND WorkOrderID = OrderID) AS TotalMaterial,
			(SELECT SUM(Price) FROM WorkOrderItems WHERE ItemType = '2' AND WorkOrderID = OrderID) AS TotalLabor
		FROM WorkOrders
		WHERE OrderID IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1) AND
			OrderID NOT IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1) AND
			Void = 0 AND
			OrderID NOT IN (SELECT OrderID FROM WorkOrders WHERE ReturnDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1)
		ORDER BY OrderID
	RETURN

Open in new window

0
Comment
Question by:Sim1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 250 total points
ID: 39824210
In Access Database Properties there is a setting for OLE/DDE Timeout.  Change that number.  This will get it working while you work on speeding up the query.

Then I would try a couple of changes to the query:
1.  use Joins instead of SubQueries

INNER JOIN periods ON [Name] = @PeriodName

With WorkOrdersWarehouseLog try to develop a view to bring back both values using MIN() and Max().  Using TOP 1 requires that all of the values be calculated and sorted which takes a while. MIN and Max don't require the sort.

You will need to test various ideas to determine which one runs fastest.  You may also want to check your indexes to make sure that, e.g. TransactionDate has an index.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 250 total points
ID: 39824217
My opinion the "NOT IN"s in your where clause is the problem and try convert it to "NOT EXISTS" like in example below:

SELECT DISTINCT
     a.user_name,
     a.event_type,
     a.time_stamp
FROM
     sys_event_log a
WHERE NOT EXISTS
 (
     SELECT
          1
     FROM
          sys_event_log b
     WHERE
          b.time_stamp > a.time_stamp
          AND a.user_name = b.user_name
          AND a.event_type = "Logout Event"
 )
AND a.event_type = "Login Success";

More at:
http://msdn.microsoft.com/en-us/library/office/aa217680(v=office.11).aspx
0
 

Author Comment

by:Sim1980
ID: 39825140
Thank you both! I will try both of your suggestions, but I was playing with the SUM(price) portion of the stored procedure while waiting for a reply to my initial question and I got the stored procedure to run really fast (about 5 seconds) but what is happening is that it is creating 2 lines for every record because I have to Group By the "ItemType". So one line will show all of the data with the TotalMaterial summed up and the TotalLabor = 0 and another line with the exact data but with TotalMaterial = 0 and the TotalLabor summed up. How can I get it so both totals appear on one line.

If you compare to my initial post you'll see the addition of the Case statements and the removal of the Sum(price) from the subqueries in my initial post.

SELECT OrderID, ProjectManager, CustomerName, Community, Lot, Block, TotalCosts, TotalBilling, ReturnDate, WorkType, ProductType, Warehouse,CASE WHEN ItemType = '1' THEN SUM(Price) ELSE 0 END AS TotalMaterial, 
                      CASE WHEN ItemType = '2' THEN SUM(Price) ELSE 0 END AS TotalLabor, 
			(SELECT begindate FROM periods WHERE name = @PeriodName) AS begindate,
			(SELECT enddate FROM periods WHERE name = @PeriodName) AS enddate,
			(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS ReceiveDate,
			(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS IssueDate		
FROM WorkOrders INNER JOIN
                      WorkOrderItems ON WorkOrders.OrderID = WorkOrderItems.WorkOrderID
		WHERE OrderID IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1 ) AND
			OrderID NOT IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1) AND
			Void = 0 AND
			OrderID NOT IN (SELECT OrderID FROM WorkOrders WHERE ReturnDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1)
		Group By WorkOrders.OrderID, WorkOrders.ProjectManager, WorkOrders.CustomerName, WorkOrders.Community, WorkOrders.Lot, WorkOrders.Block, WorkOrders.TotalCosts, WorkOrders.TotalBilling, 
                      WorkOrders.ReturnDate, WorkOrders.WorkType, WorkOrders.ProductType, WorkOrders.Warehouse,ItemType
ORDER BY WorkOrders.OrderID

Open in new window

0
 

Author Comment

by:Sim1980
ID: 39825234
Nevermind my previous post. I figured it out:

SELECT OrderID, ProjectManager, CustomerName, Community, Lot, Block, TotalCosts, TotalBilling, ReturnDate, WorkType, ProductType, Warehouse,CASE WHEN ItemType = '1' THEN SUM(Price) ELSE 0 END AS TotalMaterial, 
                      CASE WHEN ItemType = '2' THEN SUM(Price) ELSE 0 END AS TotalLabor, 
			(SELECT begindate FROM periods WHERE name = @PeriodName) AS begindate,
			(SELECT enddate FROM periods WHERE name = @PeriodName) AS enddate,
			(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS ReceiveDate,
			(SELECT TOP 1 TransactionDate FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND WorkOrdersWarehouseLog.OrderID = WorkOrders.OrderID ORDER BY TransactionDate) AS IssueDate		
FROM WorkOrders INNER JOIN
                      WorkOrderItems ON WorkOrders.OrderID = WorkOrderItems.WorkOrderID
		WHERE OrderID IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber > 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1 ) AND
			OrderID NOT IN (SELECT OrderID FROM WorkOrdersWarehouseLog WHERE UnitsNumber < 0 AND TransactionDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1) AND
			Void = 0 AND
			OrderID NOT IN (SELECT OrderID FROM WorkOrders WHERE ReturnDate < (SELECT enddate FROM periods WHERE name = @PeriodName) +1)
		Group By WorkOrders.OrderID, WorkOrders.ProjectManager, WorkOrders.CustomerName, WorkOrders.Community, WorkOrders.Lot, WorkOrders.Block, WorkOrders.TotalCosts, WorkOrders.TotalBilling, 
                      WorkOrders.ReturnDate, WorkOrders.WorkType, WorkOrders.ProductType, WorkOrders.Warehouse,ItemType
ORDER BY WorkOrders.OrderID

Open in new window


I will try your suggestions and report back.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Viewers will learn how the fundamental information of how to create a table.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question