A have a big issue, when I run a SP from an ASP.Net application, the query speed is so slow that it times out after a while (more than a min). When I run the exact same Stored Procedure with the same criteria parameters passed running in SQL Server Management Studio, it returns results back within 5 secs. How can this be? Any suggestions or insight would be helpful.

David

ALTER PROCEDURE [dbo].[usp_REQUEST_Search2]
@RTN varchar(20) = null,
@EventType varchar(15) = null,
@RequestType varchar(15) = null,
@CopyType varchar(15) = null,
@FormType varchar(15) = null,
@ApplicantAgencyName varchar(255) = null,
@ApplicantFirstName varchar(50) = null,
@ApplicantLastName varchar(50) = null,
@ApplicantAddressLine1 varchar(255) = null,
@ApplicantAddressLine2 varchar(255) = null,
@ApplicantAddressLine3 varchar(255) = null,
@ApplicantCity varchar(255) = null,
@ApplicantState varchar(50) = null,
@ApplicantZipCode varchar(15) = null,
@ApplicantPhoneNumber varchar(20) = null,
@RecipientAgencyName varchar(255) = null,
@RecipientFirstName varchar(50) = null,
@RecipientLastName varchar(50) = null,
@RecipientAddressLine1 varchar(20) = null,
@RecipientAddressLine2 varchar(255) = null,
@RecipientAddressLine3 varchar(255) = null,
@RecipientCity varchar(50) = null,
@RecipientState varchar(50) = null,
@RecipientZipCode varchar(15) = null,
@RegistrantFirstName varchar(50) = null,
@RegistrantMiddleName varchar(50) = null,
@RegistrantLastname varchar(50) = null,
@StartingEventDate datetime = null,
@EndingEventDate datetime = null,
@StartingDateReceived smalldatetime = null,
@EndingDateReceived smalldatetime = null,
@EventLocation varchar(20) = null,
@BanknoteNumber varchar(20) = null,
@Mother_ParentFirstName varchar(50) = null,
@Mother_ParentLastName varchar(50) = null,
@Mother_ParentBirthLastName varchar(50) = null,
@Father_ParentFirstName varchar(50) = null,
@Father_ParentLastName varchar(50) = null,
@Father_ParentBirthLastName varchar(50) = null,
@PartyA_FirstName varchar(50) = null,
@PartyA_LastName varchar(50) = null,
@PartyA_BirthLastName varchar(50) = null,
@PartyB_FirstName varchar(50) = null,
@PartyB_LastName varchar(50) = null,
@PartyB_BirthLastName varchar(50) = null,
@StartingCreateDate smalldatetime = null,
@EndingCreateDate smalldatetime = null,
@CreateUser varchar(50) = null,
@Location varchar(40) = null,
@ExcludeNullWorkFlow bit = 0
AS
BEGIN

SET NOCOUNT ON;

if( @EndingEventDate IS NULL AND @StartingEventDate IS NOT NULL )
SET @EndingEventDate = @StartingEventDate;
if( @EndingDateReceived IS NULL AND @StartingDateReceived IS NOT NULL )
SET @EndingDateReceived = @StartingDateReceived;
if( @EndingCreateDate IS NULL AND @StartingCreateDate IS NOT NULL )
SET @EndingCreateDate = @StartingCreateDate;

SELECT
DISTINCT
a.[request_id] "RequestID",
a.[request_tracking_number] "RTN",
b.[first_name] + ' ' + b.[last_name] "Requestor",
m.[account_name] "Requesting Agency",
c.[first_name] + ' ' + c.[last_name] "Recipient",
c.[organization_name] "Recipient Agency",
d.[first_name] + ' ' + d.[last_name] "Registrant",
z.[received_date] "Receive Date",
a.[request_status] "Status",
k.unit_owner "Unit",
j.queue_entry_time "Queue Entry Date",
e.event_type "Event Type",
e.request_type "Request Type",
e.copy_type "Copy Type"

FROM dbo.Request a
JOIN dbo.Request_Container z ON a.container_id = z.container_id
JOIN dbo.Person b ON a.applicant_id = b.person_id
LEFT OUTER JOIN dbo.Account m ON a.account_id = m.account_id
LEFT OUTER JOIN dbo.Person c ON a.Recipient_id = c.person_id
LEFT OUTER JOIN dbo.Person d ON a.Registrant1_id = d.person_id
LEFT OUTER JOIN dbo.Person i ON a.Registrant2_id = i.person_id
LEFT OUTER JOIN dbo.Product e ON a.product_id = e.product_id
LEFT OUTER JOIN dbo.BankNote f ON a.request_id = f.request_id
LEFT OUTER JOIN dbo.Contact g ON a.applicant_id = g.contact_id
LEFT OUTER JOIN dbo.Contact h ON a.Recipient_id = h.contact_id
LEFT OUTER JOIN dbo.workflow_history j ON a.request_id = j.request_id and
a.current_workflow_state_id = j.workflow_state_id and
j.sequence_no = ( SELECT max( sequence_no ) FROM workflow_history
WHERE request_id = a.request_id )
LEFT OUTER JOIN dbo.Workflow_state_unit_owner k
ON e.request_type = k.request_type and
a.current_workflow_state_id = k.workflow_state_id

WHERE
( @RTN IS NULL OR a.request_tracking_number LIKE @RTN ) AND
( @Location IS NULL OR a.original_document_location LIKE @Location ) AND
( @FormType IS NULL OR a.form_type LIKE @FormType ) AND
( @ApplicantAgencyName IS NULL OR m.account_name LIKE @ApplicantAgencyName ) AND
( @ApplicantFirstName IS NULL OR b.first_name LIKE @ApplicantFirstName ) AND
( @ApplicantLastName IS NULL OR b.last_name LIKE @ApplicantLastName ) AND
( @ApplicantAddressLine1 IS NULL OR g.address_line_1 LIKE @ApplicantAddressLine1 ) AND
( @ApplicantAddressLine2 IS NULL OR g.address_line_2 LIKE @ApplicantAddressLine2 ) AND
( @ApplicantAddressLine3 IS NULL OR g.address_line_3 LIKE @ApplicantAddressLine3 ) AND
( @ApplicantCity IS NULL OR g.city LIKE @ApplicantCity ) AND
( @ApplicantState IS NULL OR g.state = @ApplicantState ) AND
( @ApplicantZipCode IS NULL OR g.postal_code LIKE @ApplicantZipCode ) AND
( @ApplicantPhoneNumber IS NULL OR ( g.phone1 LIKE @ApplicantPhoneNumber OR
g.phone2 LIKE @ApplicantPhoneNumber ) ) AND
( @RecipientAgencyName IS NULL OR c.organization_name LIKE @RecipientAgencyName ) AND
( @RecipientFirstName IS NULL OR c.first_name LIKE @RecipientFirstName ) AND
( @RecipientLastName IS NULL OR c.last_name LIKE @RecipientLastName ) AND
( @RecipientAddressLine1 IS NULL OR h.address_line_1 LIKE @RecipientAddressLine1 ) AND
( @RecipientAddressLine2 IS NULL OR h.address_line_2 LIKE @RecipientAddressLine2 ) AND
( @RecipientAddressLine3 IS NULL OR h.address_line_3 LIKE @RecipientAddressLine3 ) AND
( @RecipientCity IS NULL OR h.City LIKE @RecipientCity ) AND
( @RecipientState IS NULL OR h.State = @RecipientState ) AND
( @RecipientZipCode IS NULL OR h.postal_code LIKE @RecipientZipCode ) AND
( @RegistrantFirstName IS NULL OR d.first_name LIKE @RegistrantFirstName ) AND
( @RegistrantMiddleName IS NULL OR d.Middle_name LIKE @RegistrantMiddleName ) AND
( @RegistrantLastName IS NULL OR d.last_name LIKE @RegistrantLastName ) AND
( @EventType IS NULL OR e.event_type = @EventType ) AND
( @EventLocation IS NULL OR a.event_location = @EventLocation ) AND
( @BanknoteNumber IS NULL OR f.banknote_number = @BanknoteNumber ) AND
( @RequestType IS NULL OR e.request_type = @RequestType ) AND
( @CopyType IS NULL OR e.copy_type = @CopyType ) AND
( ( @StartingEventDate IS NULL AND @EndingEventDate IS NULL ) OR
( IsDate(a.event_date_txt) = 1 AND cast(a.event_date_txt as datetime) BETWEEN @StartingEventDate AND @EndingEventDate ) ) AND
( ( @StartingDateReceived IS NULL AND @EndingDateReceived IS NULL ) OR
( z.received_date BETWEEN @StartingDateReceived AND @EndingDateReceived ) ) AND
( @Mother_ParentFirstName IS NULL OR d.mother_first_Name LIKE @Mother_ParentFirstName OR
d.father_first_Name LIKE @Mother_ParentFirstName ) AND
( @Mother_ParentLastName IS NULL OR d.mother_last_Name LIKE @Mother_ParentLastName OR
d.father_last_Name LIKE @Mother_ParentLastName ) AND
( @Mother_ParentBirthLastName IS NULL OR d.mother_birth_Name LIKE @Mother_ParentBirthLastName OR
d.father_birth_Name LIKE @Mother_ParentBirthLastName ) AND
( @Father_ParentFirstName IS NULL OR d.father_first_Name LIKE @Father_ParentFirstName OR
d.mother_first_Name LIKE @Father_ParentFirstName ) AND
( @Father_ParentLastName IS NULL OR d.father_last_Name LIKE @Father_ParentLastName OR
d.mother_last_Name LIKE @Father_ParentLastName ) AND
( @Father_ParentBirthLastName IS NULL OR d.father_birth_Name LIKE @Father_ParentBirthLastName OR
d.mother_birth_Name LIKE @Father_ParentBirthLastName ) AND
( @PartyA_FirstName IS NULL OR d.first_name LIKE @PartyA_FirstName OR
i.first_name LIKE @PartyA_FirstName ) AND
( @PartyA_LastName IS NULL OR d.last_name LIKE @PartyA_LastName OR
i.last_name LIKE @PartyA_LastName ) AND
( @PartyA_BirthLastName IS NULL OR d.birth_name LIKE @PartyA_BirthLastName OR
i.birth_name LIKE @PartyA_BirthLastName ) AND
( @PartyB_FirstName IS NULL OR d.first_name LIKE @PartyB_FirstName OR
i.first_name LIKE @PartyB_FirstName ) AND
( @PartyB_LastName IS NULL OR d.last_name LIKE @PartyB_LastName OR
i.last_name LIKE @PartyB_LastName ) AND
( @PartyB_BirthLastName IS NULL OR d.birth_name LIKE @PartyB_BirthLastName OR
i.birth_name LIKE @PartyB_BirthLastName ) AND
( ( @StartingCreateDate IS NULL AND @EndingCreateDate IS NULL ) OR
( z.create_date BETWEEN @StartingCreateDate and @EndingCreateDate ) ) AND
( @CreateUser IS NULL OR z.Create_User LIKE @CreateUser ) AND
( ( @ExcludeNullWorkFlow = 1 and a.Current_WorkFlow_State_Id IS NOT NULL ) OR
( @ExcludeNullWorkFlow = 0 ) )

END