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
_____________________________________________________________________
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.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]
