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 _____________________________________________________________________ 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.unsub%% CONFIDENTIALITY NOTICE: This E-Mail is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you have received this communication in error, please do not distribute and delete the original message. Please notify the sender by E-Mail at the address shown. Thank you for your compliance. --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
