• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

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 '''
0
kennma1
Asked:
kennma1
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now