help....Large IN Clause Results in Stack Overflow

Hello,

I would like help re-writing the below ASP query.  Currently it produces a ' Stack Overflow' error when the associated data grows to large.  The data pulled can range from 0 to 20,000 records.   Any help or direction will be appreciated.

Cheers,

Mike




         objCreatedFile.WriteLine Row_Item
            rs.movenext
      wend
set objCreatedFile = nothing

'''  Time Stamp '''
timestamp=now
SQL="Update tblOrders_Products  set vendorName='" & updated_vendername & "' ,TimeStamp_Fullfill='" & timestamp &"',ShippingDate='" & timestamp & "' where ID in (SELECT op.ID FROM tblOrders_Products op  LEFT JOIN tblProducts p ON op.productID = p.productID left join tblsuppliers on p.supplierID=tblSuppliers.supplierID LEFT JOIN tblOrders o ON o.orderID = op.orderID WHERE tblsuppliers.supplierID=" & supplierid & " AND op.orderID in(" & Updated_OrderID_List & "))"
objConn.Execute(SQL)
sql="Update tblorders set ProblemStatus=1 where problemstatus is null and orderid in (select o1.orderid from tblorders_products op left join tblproducts p on op.productid=p.productid left join tblsuppliers on op.vendorname=tblSuppliers.companyname left join tblorders o1 on o1.orderid= op.orderid  where p.canorder=0  and o1.OrderNo >" & MinOrderNO & " and o1.OrderNo < " & MaxOrderNo & " and o1.PaymentSuccessful=1 and tblsuppliers.supplierID=" & supplierid & ") "
objConn.Execute(SQL)
sql="insert FF_Trans (FF_TimeStamp,FF_MaxOrderNO,FF_MinOrderNo,Supplier_ID) Values('" & timestamp & "'," & MaxOrderNO & "," & MinOrderNO & "," & supplierid &")"
objConn.Execute(SQL)
'''  Time Stamp '''
kennma1Asked:
Who is Participating?
 
Anthony PerkinsCommented:
Something like this (untested):

SQL = "Update     op set     vendorName='" & updated_vendername & "', TimeStamp_Fullfill='" & timestamp & "', ShippingDate='" & timestamp & "' " &  _
"From     tblOrders_Products op LEFT JOIN tblProducts p ON op.productID = p.productID LEFT JOIN tblsuppliers s on p.supplierID = s.supplierID LEFT JOIN tblOrders o ON o.orderID = op.orderID " & _
"WHERE     s.supplierID = " & CStr(supplierid) & " AND op.orderID In (" & Updated_OrderID_List & ")"
0
 
CtrlAltDlCommented:
Which query is taking the longest?

Break it down by each query and when you figure out which one takes the longest you could break up the query into nicer bite size chunks.

What kind of db (Access, SQL Server, MySQL) is this?
0
 
kennma1Author Commented:
im pretty new at this...so please bear with me.  : )


the db is a SQl database.
the error occurs on the second query:
sql="Update tblorders set ProblemStatus=1 where problemstatus is null and orderid in (select o1.orderid from tblorders_products op left join tblproducts p on op.productid=p.productid left join tblsuppliers on op.vendorname=tblSuppliers.companyname left join tblorders o1 on o1.orderid= op.orderid  where p.canorder=0  and o1.OrderNo >" & MinOrderNO & " and o1.OrderNo < " & MaxOrderNo & " and o1.PaymentSuccessful=1 and tblsuppliers.supplierID=" & supplierid & ") "
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
kennma1Author Commented:
just reviewed the error again and I was mistaken the error occurs on the 1st query!  sorry!

Specifically line 148 (objConn.Execute(SQL))
0
 
WMIFCommented:
next step is to print out the query to make sure its all valid.  do this, and paste the querythat it gives you.

response.write sql
response.end()
objConn.Execute(SQL)
0
 
Anthony PerkinsCommented:
>>next step is to print out the query to make sure its all valid.  do this, and paste the querythat it gives you.<<
And please don't paste the query with 20,000 items :)
0
 
kennma1Author Commented:
Below is the Query output:

tblOrders_Products set vendorName='BUD' ,TimeStamp_Fullfill='4/25/2006 6:28:29 PM',ShippingDate='4/25/2006 6:28:29 PM'
where ID in (SELECT op.ID
FROM tblOrders_Products op
LEFT JOIN tblProducts p
ON op.productID = p.productID left join tblsuppliers on p.supplierID=tblSuppliers.supplierID
 LEFT JOIN tblOrders o
ON o.orderID = op.orderID
WHERE tblsuppliers.supplierID=1
AND op.orderID in(0,165513,165848,166143,..........283782,283809))
0
 
Anthony PerkinsCommented:
The following may be a tad more efficient:

Update      op
set      vendorName='BUD' ,
      TimeStamp_Fullfill='2006-04-25 6:28:29 PM',
      ShippingDate='2006-04-25 6:28:29 PM'
From      tblOrders_Products op
      LEFT JOIN tblProducts p ON op.productID = p.productID
      left join tblsuppliers s on p.supplierID = s.supplierID
      LEFT JOIN tblOrders o ON o.orderID = op.orderID
WHERE      s.supplierID = 1
      AND op.orderID in (0,165513,165848,166143,..........283782,283809)

However you may still have that problem of the 20K items.  Is there any chance you can give us some clue as to how they are generated?  Perhaps there is another way.
0
 
kennma1Author Commented:
sorry..not sure on how to implement the query posted....here is the original.  Can you repost exactly as I should paste into the asp page.

SQL="Update tblOrders_Products  set vendorName='" & updated_vendername & "' ,TimeStamp_Fullfill='" & timestamp &"',ShippingDate='" & timestamp & "' where ID in (SELECT op.ID FROM tblOrders_Products op  LEFT JOIN tblProducts p ON op.productID = p.productID left join tblsuppliers on p.supplierID=tblSuppliers.supplierID LEFT JOIN tblOrders o ON o.orderID = op.orderID WHERE tblsuppliers.supplierID=" & supplierid & " AND op.orderID in(" & Updated_OrderID_List & "))"

objConn.Execute(SQL)
0
 
kennma1Author Commented:
The suggested query is being tested today!  So far so good!  THANKS to everyone for the help.  

I will post back after all testing is complete.

Cheers,
0
 
kennma1Author Commented:
The suggested query continues without issue!  Thank you acperkins the expert insight.

Cheers
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.