The OR's were the quickist way, faster than using a union or insert into a temp table, but I wasnt able to match up which query produced which result. What I have done is export the items I need from the view into a static table and then use a UNION to select the data. This is running much fast (under 1000ms for 10-12 queries at once). I am now trying to integrate it into a COM object!
Dan -----Original Message----- From: David L. Penton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 13, 2002 14:46 To: ActiveServerPages Subject: RE: Complex SQL/Stored Proc question They are all your problem. OR's set up like that are inherently slow, especially over a view. David L. Penton, Microsoft MVP JCPenney Application Specialist / Lead "Mathematics is music for the mind, and Music is Mathematics for the Soul. - J.S. Bach" [EMAIL PROTECTED] Do you have the VBScript Docs or SQL BOL installed? If not, why not? VBScript Docs: http://www.davidpenton.com/vbscript SQL BOL: http://www.davidpenton.com/sqlbol -----Original Message----- From: Daniel Field [mailto:[EMAIL PROTECTED]] Yep, tried all 3 already! I have 'compuled' the main view used and managed to get the query time down to 4-500ms for each pass which is too slow. If i combine all the queries into one by using a OR... eg: select package_id from [my_compiled_view] where ( first query ) OR ( second query ) OR ( third query ) etc Then the query runs fast enough, but I have no way of knowing which result was from which query, which is my problem. Dan -----Original Message----- From: Kelly, Shawn [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 20:31 To: ActiveServerPages Subject: RE: Complex SQL/Stored Proc question I thought of a couple of other things that could help the DB performance. * You could combine parameters so that you do not have to perform such a complex lookup. * You could normalize your data * You could also create compiled views with one or more criteria items already "selected" (This depends on what DB engine you are using.) -----Original Message----- From: Daniel Field [mailto:[EMAIL PROTECTED]] Sent: Monday, August 12, 2002 11:11 AM To: ActiveServerPages Subject: OT: Complex SQL/Stored Proc question Hi All, I have a Stored procedure that queries a SQL2k database for various items and returns a single ID for each result found. Now this work pretty well, taking about 400-500ms for each query. The problem is that I need to call this procedure up to 10 times in a go with different parameters so the time would go up to and over 5 seconds sometimes which is way to slow for what we are doing. I have tried passing all of the parameter in one go (Using a seperator and iterating through the list). If I query using a "union" or a temp table then the perfomance isnt realy any better, but if i do one query but use a large "OR" for the different parameters the perfomance is fine for what we want. The only problem with this is I need a way of matching up the original parameters with the results, I dont think this is going to be possible but anyone have any suggestions? Dan --- You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% _____________________________________________________________________ This e-mail has been scanned for viruses by the WorldCom Internet Managed Scanning Service - powered by MessageLabs. For further information visit http://www.worldcom.com _____________________________________________________________________ This e-mail has been scanned for viruses by the WorldCom Internet Managed Scanning Service - powered by MessageLabs. For further information visit http://www.worldcom.com --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
