Solved

Intermediate Query, not sure how to word this...

Posted on 2008-06-15
8
224 Views
Last Modified: 2010-03-19

I'm wanting to get the last vendor used for the item, and I'm not sure how to word the max statement and whether I have to use a subquery, or whether it can be done in a join... I just want something that is fast...

Below is the query that I got now.  I need some info from 3 more tables.  

PurchaseOrder
------------------
keyPurchaseOrder
keyVendor
othCreationTime

PurchaseOrderItem
-----------------------
keyPurchaseOrder
keyItem

Vendor
------------------
keyVendor
nameVendor

So if I need the last vendor, then I need to find the purchaseorder with the latest creation time that contains the item which is i.keyItem.  

So you need v.nameVendor where i.keyItem = por.keyItem with max(p.creationTime)

Sorry thats the best pseudo I can use to explain it.  I think its understandable.  I guess I could finish off the query if I could do a little better pseudo...



Alter PROCEDURE [dbo].[p_GetTotalInventoryByVendorPar]
      @keyCompany int,
      @keyLocation int      
as

set nocount on

Begin Try

 Select
  i.keyItem,  
  i.nameItem,
  i.othCategoryType,
  i.othDescription,
  i.othItemType,
  i.othItemValue,
  i.othMinorClassification,
  i.othMinorType,
  i.othMinorValue,
  i.sysUpc,  
  sum(iv.othQuantity) as othQuantity,
  p.othPar
  v.keyVendor,
  v.nameVendor  
From ItemGrouping ig
  Inner Join Item i
    Left Join Inventory iv on iv.keyItem = i.keyItem and iv.keyLocation = @keyLocation
    Left Join Par p on i.keyItem = p.keyItem and p.keyLocation = @keyLocation
Where (i.keyLocation = @keyLocation      
              or (i.keyLocation is null and i.keyCompany = @keyCompany))
  and  i.keyChildItem < 1
  and  i.sysActive = 1  
  and  othQuantity < p.othPar
  and  
Group By
  i.keyItem,  
  i.nameItem,
  i.othCategoryType,
  i.othDescription,
  i.othItemType,
  i.othItemValue,
  i.othMinorClassification,
  i.othMinorType,
  i.othMinorValue,
  i.sysUpc,
  p.othPar
Order By i.nameItem          
             
return 0

End Try

Begin Catch      
      return error_number()
End Catch

Tony

0
Comment
Question by:picsnet
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Expert Comment

by:Banthor
Comment Utility
A sub-query is always more efficient than a outer join (Left or right)
I am also betting that ItemGrouping is a view.
If that is so, and the view needs only the last vender, make the change and get the information closer to those indexes.

This is really badly written from the start,
Left Joining on our conditional column
Grouping all things that provided by a Key
And returns a fail even when successful.

I'll dump some tsql in a minute



0
 
LVL 2

Expert Comment

by:howyue
Comment Utility
i somewhat disagree with banthor that a sub-query is ALWAYS more efficient than a join. it can be true  only when ur resultset or the master table being select has a very limited record and do a complex calculation, otherwise join can be faster. imagine if u hav 100 records, the subquery will probably execute 100 times where a join query to execute only 1 times. anyway, i do agree that certain situation sub-query is faster. which method to use is very much depends on ur situation and indexing.
0
 
LVL 10

Accepted Solution

by:
Banthor earned 500 total points
Comment Utility
POP this in your try block

 

 Select 

  i.keyItem,  

  sum(iv.othQuantity) as othQuantity,

  p.othPar,

	Cast(0 as int) as keyVendor,

	Cast('' as varchar(400) as nameVendor 

Into #TempStore

From ItemGrouping ig

  Inner Join Item i

    Left Join Inventory iv on iv.keyItem = i.keyItem and iv.keyLocation = @keyLocation

    Left Join Par p on i.keyItem = p.keyItem and p.keyLocation = @keyLocation

Where (i.keyLocation = @keyLocation      

              or (i.keyLocation is null and i.keyCompany = @keyCompany))

  and  i.keyChildItem < 1

  and  i.sysActive = 1  

  and  othQuantity < p.othPar
 

/*

	 If this table has an incremental Identity key (INT) , 

	use that instead of the sale date, 

	unless your sales take such a long time 

	that the last sale, may not be the last sale added to the table (yachts and Land might be this way)

*/

UPdate #TempStore Set keyVendor = (Select top 1 KeyVendor from dbo.purchaseorders  where keyItem=#TempStore.KeyITem order by PurchaseDate desc)  

/* INDEX Seek */

UPdate #TempStore Set nameVendor = (Select top 1 NameVendor from dbo.purchaseorders  where keyVendor=#TempStore.keyVendor)
 
 

Select 

  i.keyItem,  

  i.nameItem,

  i.othCategoryType,

  i.othDescription,

  i.othItemType,

  i.othItemValue,

  i.othMinorClassification,

  i.othMinorType,

  i.othMinorValue,

  i.sysUpc,  

  ts.othQuantity,

  ts.othPar

  ts.keyVendor,

  ts.nameVendor   

From 

	#TempStore ts

	Inner Join Item i on ts.KeyItem = i.KeyITem 

Order By 

	i.nameItem 

Open in new window

0
 
LVL 10

Expert Comment

by:Banthor
Comment Utility
Careful there howyue

I said an Outer (Left or Right)

Inner Joins are very efficient, even without indexing, becuase the Seeks/Scans are executed seperatly one after the other. The Minute you have an outer join. you have to retrieve the complete indexes and all table data before the outer can compare. If you have a large enough database to play with, watch the memory usage for a big surprise.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 2

Expert Comment

by:howyue
Comment Utility
whoops banthor, my apology here.
i really overlooked that u r saying outer join. anyway what query n methods to use stil very much depends on the situation. certain query can be slow while processing a huge table, but it can be a fast query while processing smaller table.
thanks for remind banthor.
0
 
LVL 1

Author Comment

by:picsnet
Comment Utility
i'll look at this in the morning... I don't have any views... Item Grouping is just a table.  

ItemGrouping holds the tree-representation, and Item holds the Item.  ItemGrouping holds Groups and Items and their relationships...

Tony
0
 
LVL 1

Author Comment

by:picsnet
Comment Utility
and i don't see what is so badly written about it... I don't say that you have to include every column in the group by.  

Sql does... I would much rather shorten it down instead of that long group by statement.  

That being said, I'm probably pretty ignorant on it cause I'm self taught on sql, and it is far weaker than my .Net code.  
0
 
LVL 1

Author Comment

by:picsnet
Comment Utility
pretty close... i saw where you were coming from on it being bad last night... i didn't see that half of the stuff i'd copied over from the other query wasn't needed, and i'd removed some stuff, and left other stuff, so it was kinda in a mess...

ALTER PROCEDURE [dbo].[p_GetTotalInventoryByVendorPar]
      @keyCompany int,
      @keyLocation int      
as

set nocount on

Begin Try

Select
  i.keyItem,  
  sum(iv.othQuantity) as othQuantity,
  p.othPar,
  Cast(0 as int) as keyVendor,
  Cast('' as varchar(100)) as nameVendor
Into #TempStore
From Item i
    Left Join Inventory iv on iv.keyItem = i.keyItem and iv.keyLocation = @keyLocation
    Left Join Par p on i.keyItem = p.keyItem and p.keyLocation = @keyLocation
Where (i.keyLocation = @keyLocation      
       or (i.keyLocation is null and i.keyCompany = @keyCompany))
  and  i.keyChildItem < 1
  and  i.sysActive = 1  
  and  othQuantity <= p.othPar
Group By
 i.keyItem,
 p.othPar

Update #TempStore
Set keyVendor =
(Select top 1 keyVendor
From PurchaseOrder p
      Inner Join PurchaseOrderItem po on p.keyPurchaseOrder = po.keyPurchaseOrder
Where po.keyItem = #TempStore.KeyItem
and   p.keyLocation = @keyLocation
Order by p.keyPurchaseOrder Desc)

Update #TempStore
Set nameVendor =
(Select nameVendor
From Vendor
Where keyVendor = #TempStore.keyVendor)
 
Select
  i.keyItem,  
  i.nameItem,
  i.othCategoryType,
  i.othDescription,
  i.othItemType,
  i.othItemValue,
  i.othMinorClassification,
  i.othMinorType,
  i.othMinorValue,
  i.sysUpc,  
  ts.othQuantity,
  ts.othPar,
  ts.keyVendor,
  ts.nameVendor  
From  #TempStore ts
      Inner Join Item i on ts.KeyItem = i.KeyItem
Order By i.nameItem  
            
return 0

End Try

Begin Catch      
      return error_number()
End Catch





0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now