SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 "where exists" doesn't do what I think it should..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 08/23/2001 :  14:23:50  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
Hi. I wrote some pretty fancy code that used a cursor within a cursor to update a database table. This took my computer over an hour to run and I have to do it quite often. After reading all the cursor hate mail on this site I thought I'd try to rewrite it without a cursor. I'll post the before and after code below, but basically I have this scenario. I have an orders_lineItem table (quorters_lineItem) that contains, well, items that have been ordered. Some items are parents, some are subordinates. So if you buy a parent item (the shopping cart automatically adds some subordinate items). Before, it only added subordinate items if the customer had changed the quantity from default. I'm going back now and changing the way it works so this table stores ALL the subordinate items. So I have to go through and for each order, get the subordinate items for each parent item, plus the quantity, ratio, etc., then put the subordinate item into the quorter_lineItem table IF IT ISN'T THERE ALREADY. But my "where not exists" in my new code doesn't stop the query from pulling back ALL the subordinate line items, even the ones that the custome already has in their cart. I've been messing with this for several hours and it's getting quite frustrating...

Hmm I've previewed this and it looks like I'm being concatenated. I'll put the code in separate postings.

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 08/23/2001 :  14:25:26  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
Old Code:


declare @now datetime set @now = getDate()
declare @quorterID integer
declare @parentSKU varchar(50)
declare @sqFt integer
declare @subordinateSKU varchar(50)
declare @ratio float
declare @subcategoryID integer
declare @categoryID integer
declare @price money
declare @qty integer

/* Cursor for going through all the parent items in the lineItem table */
declare c_lineItem cursor for
select distinct quorterID, SKU
from quorter_lineItem
where time_validFrom <= @now and time_validTo >= @now and subordinate = 0

open c_lineItem
fetch c_lineItem into @quorterID, @parentSKU

while (@@fetch_status = 0) begin
/* Get all the subordinate items for this parent item
that aren't already in the lineItems table. We're going to use another cursor for this */
declare c_sub cursor for
select distinct
li.sqFt,
Subordinate.SubordinateSKU, subordinate.ratio,
Subcategory.subcategoryID, Subcategory.CategoryID,
coalesce(SubcategorySKU.Price, Subcategory.Price) as Price

FROM Quorter_LineItem li
LEFT JOIN Subordinate ON li.SKU = Subordinate.SKU
LEFT JOIN SubcategorySKU ON Subordinate.SubordinateSKU = SubcategorySKU.SKU
LEFT JOIN Subcategory ON SubcategorySKU.SubCategoryID = Subcategory.subcategoryID

WHERE li.SKU = @parentSKU
and li.quorterID = @quorterID
and li.Subordinate = 0
and subcategory.time_validFrom <= @now
and subcategory.time_validTo >= @now
and subcategorySKU.time_validFrom <= @now
and subcategorySKU.time_validTo >= @now
and li.time_validFrom <= @now
and li.time_validTo >= @now
and ratio is not NULL
and ratio > 0

and subordinateSKU not in (select distinct SKU from quorter_lineItem where quorterID = @quorterID
and parentSKU = @parentSKU and subordinate = 1 and time_validFrom <= @now and time_validTo >= @now)

open c_sub
fetch c_sub into @sqFt, @subordinateSKU, @ratio, @subcategoryID, @categoryID, @price

-- Go through each of the subordinate items for this parent SKU / quorter
while (@@fetch_status = 0) begin
if (@ratio >= 999999) begin
set @qty = 1
end else begin
set @qty = ceiling(@sqFt / @ratio)
end

insert into quorter_lineItem (
quorterID,
SKU,
categoryID,
subcategoryID,
price,
qty,
subordinate,
parentSKU,
time_transactionStart,
time_transactionEnd,
time_validFrom,
time_validTo,
modifiedBy
)

values (
@quorterID,
@subordinateSKU,
@categoryID,
@subcategoryID,
@price,
@qty,
1,
@parentSKU,
@now,
'9999-12-31',
@now,
'9999-12-31',
5
)

fetch c_sub into @sqFt, @subordinateSKU, @ratio, @subcategoryID, @categoryID, @price
end

close c_sub
deallocate c_sub

fetch c_lineItem into @quorterID, @parentSKU
end

close c_lineItem
deallocate c_lineItem

GO


Go to Top of Page

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 08/23/2001 :  14:30:20  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
Here's the new code that doesn't work...


/*****************************************************************************/
/* ADD ALL THE SUBORDINATE ITEMS FOR ALL PARENT ITEMS IN THE LINEITEMS TABLE */
/*****************************************************************************/
print 'Starting "ADD ALL THE SUBORDINATE ITEMS FOR ALL PARENT ITEMS IN THE LINEITEMS TABLE"'

declare @now dateTime set @now = getDate()

insert into quorter_lineItem (
quorterID,
SKU,
subcategoryID,
categoryID,
price,
qty,
subordinate,
sqFt,
parentSKU,
time_validFrom,
time_validTo,
time_transactionStart,
time_transactionEnd,
modifiedBy
)

select distinct
li.quorterID,
Subordinate.SubordinateSKU as SKU,
Subcategory.subcategoryID,
Subcategory.CategoryID,
coalesce(SubcategorySKU.Price, Subcategory.Price) as Price,
cast(ceiling(sqFt / ratio) as integer) as qty,
1 as subordinate,
li.sqFt as sqFt,
li.SKU as parentSKU,
'1980-01-01' as time_validFrom,
'9999-12-31' as time_validTo,
@now as time_transactionStart,
'9999-12-31' as time_transactionEnd,
5 as modifiedBy

FROM Quorter_LineItem li
LEFT JOIN Subordinate ON li.SKU = Subordinate.SKU
LEFT JOIN SubcategorySKU ON Subordinate.SubordinateSKU = SubcategorySKU.SKU
LEFT JOIN Subcategory ON SubcategorySKU.SubCategoryID = Subcategory.subcategoryID

WHERE li.Subordinate = 0
and subcategory.time_validFrom <= @now
and subcategory.time_validTo >= @now
and subcategorySKU.time_validFrom <= @now
and subcategorySKU.time_validTo >= @now
and li.time_validFrom <= @now
and li.time_validTo >= @now
and subordinate.ratio is not NULL
and subordinate.ratio > 0
and li.quorterID is not NULL

and not exists (select ID from quorter_lineItem li2 where li2.quorterID = li.quorterID
and li2.parentSKU = li.parentSKU and li2.SKU = subordinate.subordinateSKU
and subordinate = 1 and time_validFrom <= @now and time_validTo >= @now)

GO


Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 08/23/2001 :  17:48:40  Show Profile  Reply with Quote
i think it's something to do with the usage of the @now variable....

from part 2, to debug same...dump the insert statement...concentrate on getting the select statement right...excuse me if this is stating the obvious....

i think in part 1...you pre-calc the @now variable whereas in part 2 if re-calced for each row?

maybe i've misread the query....but as a line of attrack it might be worth following.

Go to Top of Page

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 08/23/2001 :  18:02:12  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
Hello Andrew. Thanks for the response. Yeah I've been debugging without the insert statement. I just put it back in for context in the message.

Regarding @now and the date/time stamps, those don't really change the problem I'm having (they just obfuscate it). If you look at the first line of the old code, I'm declaring @now and setting it = getDate(). I just use @now instead of getDate() so I'm not calling the getDate() thousands of times unnecessarily.

Thanks for your thoughts!

Go to Top of Page

btrimpop
Posting Yak Master

USA
214 Posts

Posted - 08/24/2001 :  12:06:15  Show Profile  Reply with Quote
Seems like you're missing table aliases in the exists select on
subordinate, time_validFrom, and time_validto.

Also you could probably get rid of the exists altogether and change it to a left join adding a condition where li2.quorterid is null

Hope this helps


Go to Top of Page

aclarke
Posting Yak Master

Canada
133 Posts

Posted - 08/24/2001 :  15:07:59  Show Profile  Visit aclarke's Homepage  Send aclarke an AOL message  Click to see aclarke's MSN Messenger address  Send aclarke a Yahoo! Message  Reply with Quote
Hey. OK I tried using a temp table (first time I've used one) and I ended up with the right answer. Here's a simplified version of the answer:


select distinct
li.quorterID,
Subo.SubordinateSKU as SKU,
sc.subcategoryID,
sc.CategoryID,
coalesce(sSKU.Price, sc.Price) as Price,
cast(ceiling(sqFt / ratio) as integer) as qty,
1 as subordinate,
li.sqFt as sqFt,
li.SKU as parentSKU

into #temptable

FROM quorter_lineItem_new li
LEFT JOIN Subordinate subo ON li.SKU = Subo.SKU
LEFT JOIN SubcategorySKU sSKU ON Subo.SubordinateSKU = sSKU.SKU
LEFT JOIN Subcategory sc ON sSKU.SubCategoryID = sc.subcategoryID

WHERE li.Subordinate = 0
and subo.ratio is not NULL
and subo.ratio > 0
and li.quorterID is not NULL

-- Proof of concept
select *
from #tempTable t
where not exists (
select * from quorter_lineItem_new li

where li.quorterID = t.quorterID
and li.parentSKU = t.parentSKU
and li.SKU = t.SKU
and li.subordinate = 1
)


drop table #temptable


I still don't know why my other version without the temp table didn't work, but maybe some day I'll figure that out!

As an interesting side-note, this is a great example of why cursors are baad. My original version using cursors took 1 hour 17 minutes to complete. The new version with the temporary table finished in *28 seconds* -- 165x faster!!



Edited by - aclarke on 08/24/2001 15:29:19
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000