Stored Procedure not needed, want to do just 1 Query 2

[zyman10]

I am not that familiar with the ins and outs of SQL queries, but I can usually get things done with a stored procedure, but I have decided this is one time where I absolutely know what I am doing can be done in just one simple query, I am just not sure what I need to do next.

My Stored Proc queries my Sales table using the @input_order_id variable so I can get the two fields (@shipping_address_id, @buyer_id) for that particular order_id that was input. Then I do another select on the SAME sales table and use these two fields in my WHERE clause. The reason I do this is because if a Buyer orders two separate orders, they will have two different order_ids but they should essentially be grouped together and shipped all together at the end of the day. So if two different order_id's have the same shipping_address_id and buyer_id then they ship together and essentially I need to pull these together.

I know I can do all of this comparing in one query, but everything I try is giving me the wrong results.

The Stored Procedure below exactly accomplishes what I want to do, but I'd much rather just use 1 simple query instead.

ALTER PROCEDURE [dbo].[Get_PartsToShip]
	@input_order_id nvarchar(25)
AS
BEGIN
	DECLARE @shipping_address_id int
	DECLARE @buyer_id int
	
	SET @shipping_address_id = (SELECT ShippingAddressID FROM Sales WHERE Sales.OrderID = @input_order_id)
	SET @buyer_id = (SELECT BuyerID FROM Sales WHERE Sales.OrderID = @input_order_id)
	
	SELECT S.OrderID, S.BuyerID, S.ShippingAddressID,  I.ItemID, INV.InventoryID, INV.PartNum 
	FROM Sales AS S
		INNER JOIN	Items		I		ON I.ItemID				=	S.ItemID
		INNER JOIN	Inventory	INV		ON INV.InventoryID		=	I.InventoryID
	WHERE BuyerID = @buyer_id AND ShippingAddressID = @shipping_address_id

END

Essentially I need all of the PartNums for an order_id.

Sales Table contains:
OrderID, ItemID

Items Table contains:
ItemID, IventoryID

Iventory Table contains:
InventoryID, PartNum <--- That's the one I want

Any ideas to help me condense this down to just one query?

 

[markros]

Use INNER JOIN with Sales table.

 

[zyman10]

markros

I am not sure I understand... I am inner joining with the sales table. How would I do this?

 

[gmmastros]

First of all... you can condense your code down a little.

[tt][blue]
SET @shipping_address_id = (SELECT ShippingAddressID FROM Sales WHERE Sales.OrderID = @input_order_id)
SET @buyer_id = (SELECT BuyerID FROM Sales WHERE Sales.OrderID = @input_order_id)
[/blue][/tt]

Can be replaces with:

    Select @shipping_address_id = ShippingAddressID,
           @buyer_id = BuyerID 
    FROM   Sales 
    WHERE  Sales.OrderID = @input_order_id

This will set both variables at the same time, speeding up the overall time required to execute the procedure.

Making the change above will certainly improve your performance. Be aware that 'condensing' code does not always lead to better performance. Sometimes it actually hurts performance. Bottom line is... you should test the query multiple ways to see which is faster, and then keep the faster one. To be clear... accurate data is absolutely the most important thing when it comes to code. But... assuming there are multiple ways to accomplish the same thing, and multiple methods return the same data but one performs better over the other, then you should choose the method that performs the fastest.

Anyway... if I understand correctly, the one query solution you are looking for is this:

    SELECT S.OrderID, S.BuyerID, S.ShippingAddressID,  I.ItemID, INV.InventoryID, INV.PartNum
    FROM   Sales AS S
           [!]Inner Join Sales As Buyer
             On  S.BuyerID = Buyer.buyer_id 
             AND S.ShippingAddressID = Buyer.shipping_address_id
             And Buyer.OrderId = @input_order_id[/!]
        INNER JOIN    Items        I        ON I.ItemID                =    S.ItemID
        INNER JOIN    Inventory    INV        ON INV.InventoryID        =    I.InventoryID

Notice that the sales table is included twice, once with an alias of S and again with an alias of buyer. For the buyer version, we are filtering on order id. But... since we are inner joining back to the sales table on buyerid and shipping address id, this is acting like a filter condition.

Now, you may be wondering why one query would perform better than another. The simple answer is indexes. In this code, we are using OrderId, ShippingAddessId, BuyerId, and ItemId from the sales table.

Based on how the data is used, and what your indexes are, one version may perform better than another. Because I am curious, can you modify your original code like I suggest at the beginning of this post, and then compare it against the query I present above. Then, please post back with timing/performance information.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, George explained my idea better than I would be able to while I was watching my favorite serial

 

[zyman10]

George, thanks a lot!!! That was an awesome explanation.

I want to compare these performance numbers but I don't know any other way to compare them besides just looking at how long it took the query to run in the bottom right corner of SSMS. Usually it either says 0.00 or 1 second.

So is there a way to get a more precise number for you?

 

[markros]

SET STATISTICS TIME ON -- before the query

and

SET STATISTICS TIME OFF -- after the query

 

[gmmastros]

There are many ways to benchmark your code. The simplest way is to store a start time, and report in after the code finishes.

Ex:

Declare @Start DateTime
Set @Start = GetDate()

-- Put your code here
-- Exec MyStoredProcedure 'Param1','Param2'

Select DateDiff(millisecond, @Start, GetDate())

This will at least allow you to time your code down to the millisecond. When I do this, I do NOT trust any values that are less than 15 milliseconds. I mean... I trust that it's less than 15 milliseconds, but I wouldn't necessarily trust a value that was less. Also, remember that concurrent queries made from other applications/workstations will probably affect the numbers too. Additionally, there are sometimes differences in execution time based on SQL Server's data cache. You see... when you run a query, it must first get the data. If the data is not in memory, then it must go to the disk (which is slow) to get the data. It puts the data in to memory and then uses that copy of the data to perform your query. (I'm over-simplifying things here). Then... when you run the query a second time, the data is already loaded in to memory so it can skip the relatively slow process of getting the data from the disk.

There are other ways to get performance data, like IO statistics and times. For example...

[tt][blue]
Set Statistics IO ON
-- Your query here.
[/blue][/tt]

When you run this, you will see IO information like logical reads (from memory), physical reads (from disk), etc.... Some people would argue that minimizing IO is more important than reducing execution time because often times IO is your bottleneck so reducing the amount of data that must go through the bottleneck will ultimately have a greater impact on performance.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[zyman10]

George + Markros

I just used both of your methods and it seems everything returned is 0 or 1 ms.

I guess my DB is too small to really measure the query effectiveness?? The DB is 3 GB, but the Sales table is only about 250 MB and the Items is only 200 MB and Inventory is even smaller.

Is there any way I can run the query 100's or 1000's of times over and over to gauge performance?

Thanks

 

[markros]

declare @Loop int
set @Loop = 0
set statistics time on

while @Loop <=1000
begin
run my query
set @Loop = @Loop + 1
end

set statistics time off

 

[markros]

declare @Loop int
set @Loop = 0
set statistics time on

while @Loop <=1000
  begin
    run my query
    set @Loop = @Loop + 1
  end

set statistics time off

BTW, I got tek-tips error the second time in a row.

 

[zyman10]

I didn't forget about this post, I plan to post my stats.

But, I am actually adjusting my query a little bit, and trying to apply George's and Markros' ideas to a larger query and I am having troubles. I just need a little time to tinker, I guess I am not a SQL master like you two yet!

I'll post back soon!

 

[zyman10]

Ok, I think I have bitten off more than I can chew once again. I used the idea of a WHILE loop in SQL like gmmastros said to make a whole new query. I am pretty new to JOINs and avoiding Stored Procedures for everything (such a bad habit).

But alas, I had to revert to the Stored Procedure for this one it looks like. But if you guys wouldn't mind, can you give me some pointers on how to improve my Stored Procedure? I really want to learn how to THINK in SQL. I don't just want to know an answer on this one, and then end up hacking away some stuff when it comes time to change it or make it better. I want to figure out how to use whatever methodology comes up with the best SQL code to accompish this, so I don't act like a silly bumpkin like this anymore.

Back Story: I need to import orders from my POS to my new non-existent WMS so I can keep track of things like inventory and what's going in/out of the warehouse. I would just buy a WMS but they are all way too feature filled, and most of the time too $$$, and I want to learn more about SQL, so what better way!? So I decided to just Code up something really small in my spare time. Now it has become an obsession.

So Here Goes:

BEGIN
    -- SET NOCOUNT ON;
    
    -- DECLARE Temp RTSOrders Table to hold all OrderID's that qualify to go into the WMS 
    -- RTS == READY TO SHIP
    DECLARE @RTSOrders TABLE (
		[RTSOrderID] [int] IDENTITY(1,1) NOT NULL,
		[OrderID] nvarchar(25) NOT NULL
	)
	
	DECLARE @MatchingOrders TABLE (
		[MatchingID] [int] IDENTITY(1,1) NOT NULL,
		[OrderID] nvarchar(25) NOT NULL,
		[AddressID] int NOT NULL,
		[BuyerID] int NOT NULL,
		[ListingID] int NOT NULL 
	)
	
	DECLARE @curr_rts_ord_id int,	 @max_rts_ord_id int, @curr_order_id nvarchar(25),
			@curr_buyer_id int,		 @curr_addr_id int,
			@curr_match_id int,		 @max_match_id int,
			@curr_order_cust_id int, @curr_WMS_order_id int
     
    -- INSERT into RTSOrders all Orders that Qualify to be Processed (Packing Slip Printed)
    -- Criteria: StatusID = 39 and Going UPS Shipping Method 
    INSERT INTO @RTSOrders(
		OrderID
	)
	SELECT OrderID FROM Sales 
	WHERE StatusID = 39 AND Shipping LIKE 'UPS%'
	
    SELECT	@curr_rts_ord_id = MIN(RTSOrderID), 
			@max_rts_ord_id = MAX(RTSOrderID)
	FROM @RTSOrders
	
	-- Loop Through RTSOrders which contains orders we want to process
	WHILE @curr_rts_ord_id < @max_rts_ord_id
		BEGIN
			-- SET the current OrderID from RTSOrders
			SET @curr_order_id = (SELECT OrderID FROM @RTSOrders WHERE RTSOrderID = @curr_rts_ord_id)

Here is a real problem... I need to insert the related Customer for this OrderID into the OrderCust. The problem is, I need to compare literally all of the fields in OrderCust to make sure that someone didn't buy something a second time but moved addresses or something like that. So I need to compare OrderCust.Email to Email I get from this query, etc etc Until I know its not the same person. Then INSERT the record. Otherwise, don't INSERT. When I search Google, everyone has simple things in their WHERE NOT EXISTS and it seems like nobody is talking about comparing all the fields in the TABLE...

			-- INSERT the related buyer for this order 
			-- INTO OrderCust (A Table of Customer Data)
			INSERT INTO OrderCust (
				Email, BuyerID, AddressID, FirstName, Initial, LastName, Company, 
				AddressLine1, AddressLine2, AddressLine3, City, [State], Zip, Country, Phone
			)
			SELECT  B.Email, S.BuyerID, A.AddressID, A.FirstName, A.Initial, A.Initial, A.Company,
					A.AddressLine1, A.AddressLine2, A.AddressLine3, A.City, A.[State], A.Zip, A.Country, A.Phone
			FROM	Sales AS S
					INNER JOIN Buyers		AS B ON B.BuyerID = S.BuyerID
					INNER JOIN Addresses	AS A ON A.AddressID = S.ShippingAddressID
			WHERE	S.OrderID = @curr_order_id
			-- I want to INSERT only if a Record for this customer doesn't exist!!!
			
			-- SET the current OrderCustID
			SET @curr_order_cust_id = (SELECT MAX(OrderCustID) FROM OrderCust)

			-- SELECT the BuyerID and AddressID for that OrderID
			-- This is faster than two SET statements (Thanks George!)
			SELECT	@curr_buyer_id = Sales.BuyerID,
					@curr_addr_id = Sales.ShippingAddressID
			FROM Sales
			WHERE OrderID = @curr_order_id 
			
			-- Get the next matching order that will be inserted's MatchingID
			SET	@curr_match_id = (SELECT MAX(MatchingID) FROM @MatchingOrders) + 1
			
			-- Find all orders that have the same BuyerID and ShippingAddressID from above
			-- INSERT them into a @MatchingOrders Temp Table.  We will use this table to 
			-- insert 1 or more OrderIDs into the OrderMap table which will map them to one 
			-- WMSOrderID for processing
			INSERT INTO @MatchingOrders (
				OrderID, AddressID, BuyerID, ListingID
			)
			SELECT OrderID, ShippingAddressID, BuyerID, ListingID 
			FROM Sales
			WHERE Sales.BuyerID = @curr_buyer_id AND Sales.ShippingAddressID = @curr_addr_id

			-- Get the max of the MatchingID's so we can use it and the curr_match_id to get
			-- the correct OrderIDs when we join @MatchingOrders with Sales later.	
			SET @max_match_id = (SELECT MAX(MatchingID) FROM @MatchingOrders)
			
			-- Get the Current WMSOrderID for insertion into the OrderMap Table for the next batch
			-- of OrderID's (We do this because multiple OrderID's might need to map to one WMSOrderID,
			-- so we set this now, and then we can insert as many OrderIDs as we need to under the same
			-- WMSOrderID) OrderMap maps these to WMSOrderID
			SET @curr_WMS_order_id = (SELECT MAX(WMSOrderID) FROM OrderMap) + 1
			
			-- INSERT orders into OrderMap
			INSERT INTO OrderMap (
				OrderCustID, OrderID, PartNum, WMSOrderID
			)
			SELECT @curr_order_cust_id, M.OrderID, INV.PartNum, @curr_WMS_order_id
			FROM @MatchingOrders AS M
				INNER JOIN Sales		AS  S		ON S.OrderID = M.OrderID
				INNER JOIN Listings		AS  L       ON L.ListingID = S.ListingID    
				INNER JOIN Items		AS  I		ON I.ItemID = L.ItemID
				INNER JOIN Inventory	AS  INV		ON INV.InventoryID = I.InventoryID				
			WHERE M.MatchingID >= @curr_match_id AND M.MatchingID <= @max_match_id
			-- Only if it doesn't exist!!!
			
			-- Start Order Processing by entering WMSOrders into OrderProc Table
			INSERT INTO OrderProc (
				WMSOrderID, ProcStatusID
			)
			SELECT @curr_WMS_order_id, 1
			-- Only if it doesn't exist
			
			-- Start Order History by Entering All Required Products into OrderHist Table
			-- with related WMSOrderID
			INSERT INTO OrderHist (
				WMSOrderID, ProductID, Quantity, Scanned
			)
			SELECT @curr_WMS_order_id, P.ProductID, 1, 'False'
			FROM OrderMap AS OM
				INNER JOIN Products		AS P	ON P.ProdNum = OM.PartNum
			WHERE OM.WMSOrderID = @curr_WMS_order_id 
						
			-- Increment Loop Counter
			SET @curr_rts_ord_id = @curr_rts_ord_id + 1
		END
END

Wow... that doesn't look too bad. Basically it works besides the fact that I don't know how to INSERT where NOT EXISTS. But I would appreciate any evaluation of this since I am completely self taught, and I know there are probably best practices that I am slaughtering.

I will provide all of my TABLE Structure if people think that will help.

Also, I will provide any testing results anyone thinks would be interesting or they are curious about now that I know how to loop.

 

[gmmastros]

I don't know how to INSERT where NOT EXISTS

That part isn't so bad. There is an exists function in SQL that you can use. Exists is very efficient, too. It essentially returns a boolean (true/false). The reason it's so efficient is because it can stop looking (and return true) as soon as it finds a row. I use this function a lot for "save" stored procedures.

Imagine we have a table of people and we want to save data to the table. In my front end app, I would have a details screen where the user can set certain information. I use the same screen for adding new people, and also modifying an existing person. On the form, I would store the PersonID (never shown to the user). If I am modifying a person, the PersonId would correspond to a value in the table. For new people, the PersonId = 0.

I would then have a save procedure like this...

Create Procedure SavePerson
  @PersonId Int,
  @EyeColor VarChar(20),
  @ShoeSize Numeric(3,1)
As
SET NOCOUNT ON

If Exists(Select 1 From People Where PersonId = @PersonId)
  Begin
    -- Person exists, do an update
    Update People 
    Set    EyeColor = @EyeColor,
           ShoeSize = @ShoeSize
    Where  PersonId = @PersonId
  End
Else
  Begin
    -- Person does not exist, insert it.
    Insert Into People(EyeColor, ShoeSize)
    Values (@EyeColor, @ShoeSize)

    Select @PersonId = Scope_Identity()
  End

Select @PersonId As PersonId

Mostly, your code doesn't look too bad. I'm a little uncomfortable with the way you are dealing with some of your data. There is a potential problem with your code that you need to understand. When you select the max value and add 1, and then later use that value, you are opening yourself up to potential problems. You see, if 2 different users are executing the same procedure at the same time, each user could get the same MAX value, and then each could try inserting the same MAX + 1 value in to the table. If there is a database constraint that the value must be unique, one user will get an error and the other won't. This isn't likely to occur in a database that is seldom used, but in higher transaction databases, this could be a real problem.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom