Access Sub queries vs. SQL functions

We're running SQL Server Express 2008.  Usually a query ran in Access using linked SQL tables runs much slower same query converted and run as a stored procedure in SQL Server.  However, there is one Access query that has two subqueries that was converted to a storedprocedure that has two functions and the stored procedure (when there are lots of records) runs much slower.  Could this be because in Access the sub queries are processed first and then the results are used in the main query, whereas in SQL Server the both functions and the storedprocedure are all processed at once?  If so, is there a way to get SQL Server to process functions used in stored procedure first?  Also, how do you optimize stored procedures that run particularly slow in the rare case when there are a lot of records but run much faster when there aren't.  Note: the design of our applications would make archiving records difficult.
LVL 1
Declan_BasileITAsked:
Who is Participating?
 
RemRemRemCommented:
Looking at your code, my expectation is that your massive slow down is caused by the fact that you're using a function to call a full table in:

((((fnTempSQLPartsNeeded(161050) As fn1 INNER JOIN
fnTempSQLPartsPulled(161050) As fn2

That means that the function codes are being pulled and calculated for EVERY record in your base dataset - then the functions inside the functions are being called every time for each record in the called record...which is being called too many times already....it becomes iterative. That's an incredible resource hog. Add to that that one of your inner layer functions has a union query on it, and you're getting even uglier in terms of processing time.

Instead, look into making a stored procedure, in which you pull what is currently set up as a series of functions, as selects into temporary tables. In the end, you then use the joins of the temp tables to get your results.

The (minor by comparison) fix I was recommending with (NOLOCK) is that your selects/joins can read, for example:

SELECT Kits.KitId, TempSQLPartIds.PartId,
            Sum([ActualDistribs].[Quantity]*-1) AS QtyPulled
            FROM Kits with (NOLOCK)
           INNER JOIN ActualDistribs with (NOLOCK)
            ON Kits.KitId = ActualDistribs.KitId)
            INNER JOIN TempSQLPartIds with (NOLOCK)
            ON ActualDistribs.PartId = TempSQLPartIds.PartId...

-Rachel
0
 
ZberteocCommented:
Functions can be performance killers if they access database objects like tables, views and/or other functions. It may be the case that you will have to redesign the database querying.

There is no short and universal answer to performance problems and they have to be attacked one at a time. Code sample would help a lot.

STill some general guidelines are:

- avoid using loops, like WHILE or cursors in functions and procedures
- avoid using functions calling other objects, usually they generate nested loops
- if you have to use functions you should use simple queries or only variable manipulation and definitely NOT ANY loops
- make sure you have proper indexes on your tables on all the columns that you use in your queries in ON and WHERE clauses. Sometimes composite indexes are better than simple ones.
- make sure that your queries are clean and not cumbersome; this usually comes with experience as there are no predefined recipes.

Hope this helps.
0
 
RemRemRemCommented:
You may want to determine if the slow down is happening when the stored proc is called server side - if it's ONLY when it's called through to Access, that's a different issue than if it's slow to begin with. If it's the SQL side:

Make sure your functions use tables set to WITH (NOLOCK) on the SQL side - you can slow your response down if you're calling a self-referencing table in the function and this will keep it from holding records each time it runs. (Your stored proc can use this, too, especially if you're not editing data on the return run).

If it's the Access side, check that you're passing all variables in the expected data type and check response time when JUST opening the SP from Access as opposed to using the SP in something else. In other words, slowly nibble at the layers it runs through to determine where the greatest impact is. Unfortunately, with speed response issues, it may not be one easy answer, but a series of smaller bites you have to take out of the problem.

-Rachel
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
Please post the relevant SQL and SP code.
0
 
Declan_BasileITAuthor Commented:
Rachel,
   This is slow even when called on the SQL side.  What do you mean by a "self-referencing table", and where is the property to set a table to WITH(NOLOCK)?  I only heard of adding this hint to the end of a SQL statement.  Is there a table property that can specify this?

For those interested, here is the code ...

SELECT fn2.PartId, Kits.KitDate,
IsNull([QtyPulled],0)-IsNull([QtyNeeded],0) AS Qty,
WorkOrders.Number AS WO, Kits.Kit, fn1.FirstCode AS Code,
Parents.ParentName AS Parent, Assemblies.Number AS AssyNo
FROM ((((fnTempSQLPartsNeeded(161050) As fn1 INNER JOIN
fnTempSQLPartsPulled(161050) As fn2 ON (fn1.PartId = fn2.PartId)
AND (fn1.KitId = fn2.KitId)) INNER JOIN Kits
ON fn1.KitId = Kits.KitId) INNER JOIN WorkOrders
ON Kits.WorkOrderId = WorkOrders.WorkOrderId)
INNER JOIN Assemblies ON Kits.AssemblyId = Assemblies.AssemblyId)
INNER JOIN Parents ON Assemblies.ParentId = Parents.ParentId
WHERE (((IsNull([QtyPulled],0)-IsNull([QtyNeeded],0))>0))
ORDER BY Kits.KitDate DESC;

ALTER FUNCTION [dbo].[fnTempSQLPartsNeeded]
      (@ProgramSessionId int)
      RETURNS table
AS
      RETURN (
            SELECT Kits.KitId, fn1.PartId, Min(Items.Code) AS FirstCode,
            Sum([Kits].[KitQty]*[Items].[Qty]) AS QtyNeeded
            FROM (fnRelTempSQLPartItem(@ProgramSessionId) AS fn1
            INNER JOIN Items ON fn1.ItemId = Items.ItemId)
            INNER JOIN Kits ON Items.AssemblyId = Kits.AssemblyId
            GROUP BY Kits.KitId, fn1.PartId
      )

ALTER FUNCTION [dbo].[fnRelTempSQLPartItem]
      (@ProgramSessionId As Int)
      RETURNS table
As
      RETURN (
            SELECT TempSQLPartIds.PartId, RelPartItem.ItemId
            FROM TempSQLPartIds INNER JOIN RelPartItem
            ON TempSQLPartIds.PartId = RelPartItem.PartId
            WHERE TempSQLPartIds.ProgramSessionId = @ProgramSessionId
            UNION SELECT TempSQLPartIds.PartId, Items.ItemId
            FROM (TempSQLPartIds INNER JOIN RelPartCustPart
            ON TempSQLPartIds.PartId = RelPartCustPart.PartId)
            INNER JOIN Items ON RelPartCustPart.CustPartId = Items.CustPartId
            WHERE TempSQLPartIds.ProgramSessionId = @ProgramSessionId
      )

ALTER FUNCTION [dbo].[fnTempSQLPartsPulled]
      (@ProgramSessionId int)
      RETURNS table
AS
      RETURN (
            SELECT Kits.KitId, TempSQLPartIds.PartId,
            Sum([ActualDistribs].[Quantity]*-1) AS QtyPulled
            FROM (Kits INNER JOIN ActualDistribs
            ON Kits.KitId = ActualDistribs.KitId)
            INNER JOIN TempSQLPartIds
            ON ActualDistribs.PartId = TempSQLPartIds.PartId
            WHERE ActualDistribs.TransactionTypeId = 2
            And TempSQLPartIds.ProgramSessionId = @ProgramSessionId
            GROUP BY Kits.KitId, TempSQLPartIds.PartId
      )
0
 
Anthony PerkinsCommented:
Make sure you have indexes on the following columns:
Kits.KitId
Kits.WorkOrderId
Kits.AssemblyId
Kits.KitDate

Items.ItemId
Items.CustPartId
Items.AssemblyId

WorkOrders.WorkOrderId

Assemblies.AssemblyId
Assemblies.ParentId

Parents.ParentId

TempSQLPartIds.PartId
TempSQLPartIds.ProgramSessionId

RelPartItem.PartId
RelPartCustPart.PartId
RelPartItem.ItemId

ActualDistribs.KitId
ActualDistribs.PartId
ActualDistribs.TransactionTypeId

Does it have to be UNION in the UDF fnRelTempSQLPartItem?  Or can you use UNION ALL?
0
 
Declan_BasileITAuthor Commented:
Rachel - What you wrote helped a lot.  Thank you.  I changed the code to use temporary tables.  Also, I didn't know about the NOLOCK hint and I think it will help with another problem I've been having.  I want to understand one more thing before closing out this post ... (Note: I posted the new code).  I realize that an execution plan is created for the stored procedure, but is an execution plan created and saved for each SQL Statement in the stored procedure that I used to insert records into the temporary tables?  Is it possible to call a stored procedure for each temporary table to create a recordset to insert into the temporary table?  If so, would that be more efficient because an execution plan would be saved for each stored procedure?

acperkins - Thanks for the tip.  I didn't know about "UNION ALL".  I just read up on it.  There should never be any duplicate records so I'll use UNION ALL for this query.

CREATE PROCEDURE [dbo].[procExtraPartsPulled]
      @intProgramSessionId As Int
As

DECLARE @RelTempSQLPartItem TABLE      (PartId INT,
                                                      ItemId INT)
                                                      
DECLARE @PartsNeeded TABLE                  (KitId INT,
                                                      PartId INT,
                                                      FirstCode varchar(10),
                                                      QtyNeeded INT)

DECLARE @PartsPulled TABLE                  (KitId INT,
                                                      PartId INT,
                                                      QtyPulled INT)
                                                                                                            
INSERT  INTO @RelTempSQLPartItem (PartId, ItemId)
            SELECT TempSQLPartIds.PartId, RelPartItem.ItemId
            FROM TempSQLPartIds INNER JOIN RelPartItem
            ON TempSQLPartIds.PartId = RelPartItem.PartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            UNION SELECT TempSQLPartIds.PartId, Items.ItemId
            FROM (TempSQLPartIds INNER JOIN RelPartCustPart
            ON TempSQLPartIds.PartId = RelPartCustPart.PartId)
            INNER JOIN Items ON RelPartCustPart.CustPartId = Items.CustPartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId

INSERT INTO @PartsNeeded (KitId, PartId, FirstCode, QtyNeeded)
            SELECT Kits.KitId, fn1.PartId, Min(Items.Code) AS FirstCode,
            Sum([Kits].[KitQty]*[Items].[Qty]) AS QtyNeeded
            FROM (@RelTempSQLPartItem AS fn1
            INNER JOIN Items ON fn1.ItemId = Items.ItemId)
            INNER JOIN Kits ON Items.AssemblyId = Kits.AssemblyId
            WHERE DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            GROUP BY Kits.KitId, fn1.PartId
            
INSERT INTO @PartsPulled  (KitId, PartId, QtyPulled)
            SELECT Kits.KitId, TempSQLPartIds.PartId,
            Sum([ActualDistribs].[Quantity]*-1) AS QtyPulled
            FROM (Kits INNER JOIN ActualDistribs
            ON Kits.KitId = ActualDistribs.KitId)
            INNER JOIN TempSQLPartIds
            ON ActualDistribs.PartId = TempSQLPartIds.PartId
            WHERE ActualDistribs.TransactionTypeId = 2
            And TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            And DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            GROUP BY Kits.KitId, TempSQLPartIds.PartId


      SELECT fn2.PartId, Kits.KitDate,
      IsNull([QtyPulled],0)-IsNull([QtyNeeded],0) AS Qty,
      WorkOrders.Number AS WO, Kits.Kit, fn1.FirstCode AS Code,
      Parents.ParentName AS Parent, Assemblies.Number AS AssyNo
      FROM ((((@PartsNeeded As fn1 INNER JOIN
      @PartsPulled As fn2 ON (fn1.PartId = fn2.PartId)
      AND (fn1.KitId = fn2.KitId)) INNER JOIN Kits
      ON fn1.KitId = Kits.KitId) INNER JOIN WorkOrders
      ON Kits.WorkOrderId = WorkOrders.WorkOrderId)
      INNER JOIN Assemblies ON Kits.AssemblyId = Assemblies.AssemblyId)
      INNER JOIN Parents ON Assemblies.ParentId = Parents.ParentId
      WHERE (((IsNull([QtyPulled],0)-IsNull([QtyNeeded],0))>0))
      ORDER BY Kits.KitDate DESC;

RETURN
0
 
ZberteocCommented:
Definitely you need to re-write the query. I what I suggested in my first post.

Using a query that joins 2 functions of which one calls another function and all of them are querying multiple tables is what generates nested loop at multiple levels which will create profound performance issues.

You will have to re-write the query.

Using partial results in tables is a solution but I suggest to NOT use tables variable. They are only good if the result set that they keep is not big otherwise they will again create performance issues. If the sets are big the tables variables will still create temp tables in the background and the overall load is actually bigger than using temp tables from starters.

Use TEMPORARY tables instead!
0
 
Anthony PerkinsCommented:
>>I changed the code to use temporary tables.<<
These ar not temporary tables, they are variables of type table.  Different animal.

>>Also, I didn't know about the NOLOCK hint and I think it will help with another problem I've been having.<<
The NOLOCK allows you to read "dirty" data.  Understand when that is a good idea and when not.

>>I realize that an execution plan is created for the stored procedure, but is an execution plan created and saved for each SQL Statement in the stored procedure that I used to insert records into the temporary tables?<<
The Execution Plan is cached, so that the second time the Stored Procedure is executed, it may be able to use it if it is still in cache.
0
 
Declan_BasileITAuthor Commented:
acperkins - I did read up on the NOLOCK hint.  I don't have any "Begin Transaction/Rollback or Commit" code in any of my programs, so we shouldn't get any "dirty" reads.  Thank you for the warning.  Occasionally a user won't be able to update data because another user ran a select statement which caused a lock.  I either have to kill that user's SPID or close the user out of the form of the Access database program that caused the lock before the first user can update.  This typically happens if there are two comboboxes, the first one being used to filter the second one.  After the first one is updated and the second one is requeried, if there are alot of records in the second combobox that meet the criteria, it causes a lock.  Do you understand why that happens?  I'm hoping that the NOLOCK hint will prevent this.

Zberteoc and acperkins - Here is my new code.

ALTER PROCEDURE [dbo].[procExtraPartsPulled]
      @intProgramSessionId As Int
As
                                                                                                            
SELECT  PartId, ItemId INTO #RelTempSQLPartItem FROM (
            SELECT TempSQLPartIds.PartId, RelPartItem.ItemId
            FROM TempSQLPartIds INNER JOIN RelPartItem
            ON TempSQLPartIds.PartId = RelPartItem.PartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            UNION ALL SELECT TempSQLPartIds.PartId, Items.ItemId
            FROM (TempSQLPartIds INNER JOIN RelPartCustPart
            ON TempSQLPartIds.PartId = RelPartCustPart.PartId)
            INNER JOIN Items ON RelPartCustPart.CustPartId = Items.CustPartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId) As tmp

SELECT  Kits.KitId, fn1.PartId, Min(Items.Code) AS FirstCode,
            Sum([Kits].[KitQty]*[Items].[Qty]) AS QtyNeeded INTO #PartsNeeded
            FROM (#RelTempSQLPartItem AS fn1
            INNER JOIN Items ON fn1.ItemId = Items.ItemId)
            INNER JOIN Kits ON Items.AssemblyId = Kits.AssemblyId
            WHERE DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            GROUP BY Kits.KitId, fn1.PartId
            

SELECT  Kits.KitId, TempSQLPartIds.PartId,
            Sum([ActualDistribs].[Quantity]*-1) AS QtyPulled INTO #PartsPulled
            FROM (Kits INNER JOIN ActualDistribs
            ON Kits.KitId = ActualDistribs.KitId)
            INNER JOIN TempSQLPartIds
            ON ActualDistribs.PartId = TempSQLPartIds.PartId
            WHERE ActualDistribs.TransactionTypeId = 2
            And TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            And DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            GROUP BY Kits.KitId, TempSQLPartIds.PartId


SELECT fn2.PartId, Kits.KitDate,
      IsNull([QtyPulled],0)-IsNull([QtyNeeded],0) AS Qty,
      WorkOrders.Number AS WO, Kits.Kit, fn1.FirstCode AS Code,
      Parents.ParentName AS Parent, Assemblies.Number AS AssyNo
      FROM ((((#PartsNeeded As fn1 INNER JOIN
      #PartsPulled As fn2 ON (fn1.PartId = fn2.PartId)
      AND (fn1.KitId = fn2.KitId)) INNER JOIN Kits
      ON fn1.KitId = Kits.KitId) INNER JOIN WorkOrders
      ON Kits.WorkOrderId = WorkOrders.WorkOrderId)
      INNER JOIN Assemblies ON Kits.AssemblyId = Assemblies.AssemblyId)
      INNER JOIN Parents ON Assemblies.ParentId = Parents.ParentId
      WHERE (((IsNull([QtyPulled],0)-IsNull([QtyNeeded],0))>0))
      ORDER BY Kits.KitDate DESC;

RETURN
0
 
ZberteocCommented:
You should be fine. I also recommend to add indexes to the temp tables on the columns that are used in joins. In the same idea make sure that you have indexes in all the permanent tables on all the columns that are used in JOIN and WHERE clauses. Composite keys are more efficient if you join or filter on multiple columns.

ALTER PROCEDURE [dbo].[procExtraPartsPulled]
      @intProgramSessionId As Int
As
                                                                                                            
SELECT  PartId, ItemId INTO #RelTempSQLPartItem FROM (
            SELECT TempSQLPartIds.PartId, RelPartItem.ItemId
            FROM TempSQLPartIds INNER JOIN RelPartItem 
            ON TempSQLPartIds.PartId = RelPartItem.PartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            UNION ALL 
            SELECT TempSQLPartIds.PartId, Items.ItemId
            FROM (TempSQLPartIds INNER JOIN RelPartCustPart 
            ON TempSQLPartIds.PartId = RelPartCustPart.PartId) 
            INNER JOIN Items ON RelPartCustPart.CustPartId = Items.CustPartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId) As tmp

CREATE INDEX IX_RelTempSQLPartItem_ItemId on #RelTempSQLPartItem(ItemId)

SELECT  Kits.KitId, fn1.PartId, Min(Items.Code) AS FirstCode, 
            Sum([Kits].[KitQty]*[Items].[Qty]) AS QtyNeeded INTO #PartsNeeded
            FROM (#RelTempSQLPartItem AS fn1 
            INNER JOIN Items ON fn1.ItemId = Items.ItemId) 
            INNER JOIN Kits ON Items.AssemblyId = Kits.AssemblyId
            WHERE DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            GROUP BY Kits.KitId, fn1.PartId
            
CREATE INDEX IX_PartsNeeded_ItemId on #PartsNeeded(PartId)

SELECT  Kits.KitId, TempSQLPartIds.PartId, 
            Sum([ActualDistribs].[Quantity]*-1) AS QtyPulled INTO #PartsPulled
            FROM (Kits INNER JOIN ActualDistribs 
            ON Kits.KitId = ActualDistribs.KitId) 
            INNER JOIN TempSQLPartIds 
            ON ActualDistribs.PartId = TempSQLPartIds.PartId
            WHERE ActualDistribs.TransactionTypeId = 2
            And TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            And DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            GROUP BY Kits.KitId, TempSQLPartIds.PartId

CREATE INDEX IX_PartsNeeded_ItemId on #PartsNeeded(PartId)

SELECT fn2.PartId, Kits.KitDate, 
      IsNull([QtyPulled],0)-IsNull([QtyNeeded],0) AS Qty, 
      WorkOrders.Number AS WO, Kits.Kit, fn1.FirstCode AS Code, 
      Parents.ParentName AS Parent, Assemblies.Number AS AssyNo
      FROM ((((#PartsNeeded As fn1 INNER JOIN 
      #PartsPulled As fn2 ON (fn1.PartId = fn2.PartId) 
      AND (fn1.KitId = fn2.KitId)) INNER JOIN Kits 
      ON fn1.KitId = Kits.KitId) INNER JOIN WorkOrders 
      ON Kits.WorkOrderId = WorkOrders.WorkOrderId) 
      INNER JOIN Assemblies ON Kits.AssemblyId = Assemblies.AssemblyId) 
      INNER JOIN Parents ON Assemblies.ParentId = Parents.ParentId
      WHERE (((IsNull([QtyPulled],0)-IsNull([QtyNeeded],0))>0))
      ORDER BY Kits.KitDate DESC;

Open in new window

0
 
ZberteocCommented:
I actually made few more modifications that will improve performance. I modified the indexes to composite and added 1 more. Also in the where clause I eliminated the functions, DATEDIFF and ISNULL:


            -- And DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            And Kits.KitDate > dateadd(MM, -7, GETDATE())


in 2 spots and


      -- WHERE (((IsNull([QtyPulled],0)-IsNull([QtyNeeded],0))>0))
      WHERE [QtyPulled]>[QtyNeeded]


the expressions are equivalent but will be much faster becasue they will actually use indexes, providing that you have indexex on Kits.KitDate, [QtyPulled] and [QtyNeeded] columns.

Keep in mind, every time you wrap the columns in functions like ISNULL, DATEDIFF, etc, th eSQL engine is not able to make use of the indexes on those columns so is recommended to NOT to tha.
ALTER PROCEDURE [dbo].[procExtraPartsPulled]
      @intProgramSessionId As Int
As
                                                                                                            
SELECT  PartId, ItemId INTO #RelTempSQLPartItem FROM (
            SELECT TempSQLPartIds.PartId, RelPartItem.ItemId
            FROM TempSQLPartIds INNER JOIN RelPartItem 
            ON TempSQLPartIds.PartId = RelPartItem.PartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            UNION ALL 
            SELECT TempSQLPartIds.PartId, Items.ItemId
            FROM (TempSQLPartIds INNER JOIN RelPartCustPart 
            ON TempSQLPartIds.PartId = RelPartCustPart.PartId) 
            INNER JOIN Items ON RelPartCustPart.CustPartId = Items.CustPartId
            WHERE TempSQLPartIds.ProgramSessionId = @intProgramSessionId) As tmp

CREATE INDEX IX_RelTempSQLPartItem_ItemId on #RelTempSQLPartItem(ItemId)

SELECT  Kits.KitId, fn1.PartId, Min(Items.Code) AS FirstCode, 
            Sum([Kits].[KitQty]*[Items].[Qty]) AS QtyNeeded INTO #PartsNeeded
            FROM (#RelTempSQLPartItem AS fn1 
            INNER JOIN Items ON fn1.ItemId = Items.ItemId) 
            INNER JOIN Kits ON Items.AssemblyId = Kits.AssemblyId
            -- WHERE DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            WHERE Kits.KitDate > dateadd(MM, -7 GETDATE())
            GROUP BY Kits.KitId, fn1.PartId
            
CREATE INDEX IX_PartsNeeded_ItemId on #PartsNeeded(PartId,KitId)
CREATE INDEX IX_PartsNeeded_ItemId on #PartsNeeded(KitId)

SELECT  Kits.KitId, TempSQLPartIds.PartId, 
            Sum([ActualDistribs].[Quantity]*-1) AS QtyPulled INTO #PartsPulled
            FROM (Kits INNER JOIN ActualDistribs 
            ON Kits.KitId = ActualDistribs.KitId) 
            INNER JOIN TempSQLPartIds 
            ON ActualDistribs.PartId = TempSQLPartIds.PartId
            WHERE ActualDistribs.TransactionTypeId = 2
            And TempSQLPartIds.ProgramSessionId = @intProgramSessionId
            -- And DATEDIFF(MM, Kits.KitDate, GETDATE()) < 7
            And Kits.KitDate > dateadd(MM, -7, GETDATE())
            GROUP BY Kits.KitId, TempSQLPartIds.PartId

CREATE INDEX IX_PartsPulled_ItemId on #PartsPulled(PartId,KitId)

SELECT fn2.PartId, Kits.KitDate, 
      IsNull([QtyPulled],0)-IsNull([QtyNeeded],0) AS Qty, 
      WorkOrders.Number AS WO, Kits.Kit, fn1.FirstCode AS Code, 
      Parents.ParentName AS Parent, Assemblies.Number AS AssyNo
      FROM ((((#PartsNeeded As fn1 INNER JOIN 
      #PartsPulled As fn2 ON (fn1.PartId = fn2.PartId) 
      AND (fn1.KitId = fn2.KitId)) INNER JOIN Kits 
      ON fn1.KitId = Kits.KitId) INNER JOIN WorkOrders 
      ON Kits.WorkOrderId = WorkOrders.WorkOrderId) 
      INNER JOIN Assemblies ON Kits.AssemblyId = Assemblies.AssemblyId) 
      INNER JOIN Parents ON Assemblies.ParentId = Parents.ParentId
      -- WHERE (((IsNull([QtyPulled],0)-IsNull([QtyNeeded],0))>0))
      WHERE [QtyPulled]>[QtyNeeded]
      ORDER BY Kits.KitDate DESC;

Open in new window

0
 
ZberteocCommented:
About this:

      -- WHERE (((IsNull([QtyPulled],0)-IsNull([QtyNeeded],0))>0))
      WHERE [QtyPulled]>[QtyNeeded]

If you think you have situations where one of the [QtyPulled] and [QtyNeeded] is NULL and the other has a value then you should keep the ISNULL because my version will eliminate cases like:

value > NULL

and

NULL > value
0
 
Declan_BasileITAuthor Commented:
Boy did I learn a lot from this one post.  Thank you everyone for your input.  All of it was helpful.  I never created a temporary table in SQL Server before, not to mention one with indexes, and I didn't know about "UNION ALL" or the "NOLOCK" hint.
0
 
Anthony PerkinsCommented:
>> I don't have any "Begin Transaction/Rollback or Commit" code in any of my programs, so we shouldn't get any "dirty" reads.  <<
Than you have not read enough.  You do not need an explicit transaction to have a dirty read.  A simple INSERT/UPDATE/DELETE will do it.
0
 
Declan_BasileITAuthor Commented:
OK, I'll read up on it more.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.