asked on

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

CtrlAltDl

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?

kennma1

ASKER

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 & ") "

kennma1

ASKER

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

Specifically line 148 (objConn.Execute(SQL))

WMIF

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)

Anthony Perkins

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

kennma1

ASKER

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

Anthony Perkins

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.

kennma1

ASKER

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)

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kennma1

ASKER

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,

kennma1

ASKER

The suggested query continues without issue! Thank you acperkins the expert insight.

Cheers