Proc timing out
-
When I run the SP using the QA it runs under a second.
However, when they run it via the application it timesout.
I can see it hanging on the profiler, it does eventually finish, but takes quite a while.
Is there anyway to speed up the query?
CREATE PROC [dbo].[usp_Dive_Validation] (
@DateFr datetime,
@DateTo datetime
)
AS
SET NOCOUNT ON
--set @DateFr=@DateFr + ' 12:00:00AM'
--set @DateTo=@DateTo + ' 11:59:59PM'
-- for debugging
/*
DECLARE @DateFr datetime
DECLARE @DateTo datetime
SET @DateFr = '5/11/2006 12:00:00AM'
SET @DateTo = '5/11/2006 11:59:59PM'
*/
SELECT '<XMLData>
<Decisions>
<Decision char="N" desc="Not Verified" />
<Decision char="R" desc="Rejected" />
<Decision char="V" desc="Verified" />
</Decisions>'
SELECT
DiveInfo.RCID,
DiveInfo.First_Name,
DiveInfo.Last_Name,
CAST(DiveInfo.Phone_Number AS VARCHAR) + (
CASE WHEN DiveInfo.Phone_Ext IS NULL THEN ''
ELSE ' x' + CAST(DiveInfo.Phone_Ext AS VARCHAR)
END) AS Phone_Number,
DiveInfo.Lead_Type,
DiveInfo.DecisionChar,
CASE WHEN LQ.validate_reason IS NOT NULL THEN PS.label
ELSE DiveInfo.[Reason]
END AS Reason,
CASE ISNULL(LQ.AdminUserID, 0)
WHEN 0 THEN 'Dive'
ELSE AU.first_name + ' ' + AU.last_name
END AS DecisionBy,
DiveInfo.Date_IN,
DiveInfo.Affiliate_ID
FROM (
SELECT
RCRM.RCID,
LRQ.First_Name,
LRQ.Last_Name,
(
CAST(LRQ.phone_prefix AS VARCHAR) + '-' +
CAST(LRQ.phone_prefix AS VARCHAR) + '-' +
CAST(LRQ.phone_suffix AS VARCHAR)
) AS Phone_Number,
NULLIF(LRQ.phone_ext, '') AS Phone_Ext,
CASE LRQ.Lead_Type_ID
WHEN 1 THEN 'BUY'
WHEN 2 THEN 'SELL'
ELSE 'BUYSELL'
END AS Lead_Type,
LRQ.DecisionChar,
TTT.Test_Description AS Reason,
LRQ.Date_IN,
LRQ.Affiliate_ID
FROM
dbo.tbl_Leads_Result_Queue LRQ (NOLOCK),
dbo.tbl_Dive_Test_Description TTT (NOLOCK),
dbo.tbl_RCResultMatch RCRM (NOLOCK)
WHERE LRQ.ruleNum = TTT.RuleNO
AND RCRM.ResultID = LRQ.Lead_ID
AND (LRQ.Date_IN BETWEEN @DateFr AND @DateTo /*'4/21/2006 12:00:00AM' AND '4/21/2006 12:00:00PM' */)
) DiveInfo INNER JOIN rcexec.tbl_leads_queue LQ ON LQ.parent_lead_id = DiveInfo.RCID
LEFT OUTER JOIN dbo.tbl_admin_users AU ON AU.admin_user_id = LQ.AdminUserID
LEFT OUTER JOIN dbo.tbl_label_repository PS ON PS.label_id = LQ.validate_reason
WHERE --LQ.parent_lead_id = LQ.lead_id AND
(LQ.Date_IN BETWEEN @DateFr AND @DateTo /*'4/21/2006 12:00:00AM' AND '4/21/2006 12:00:00PM' */)
Order by RCID
for xml raw
SELECT '</XMLData>'
SET NOCOUNT OFF
GO
CREATE TABLE [dbo].[tbl_leads_result_queue] (
[lead_id] [int] NOT NULL ,
[parent_lead_id] [int] NULL ,
[partner_id] [int] NOT NULL ,
[DecisionChar] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DecisionStr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DecisionFlag] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlternatePhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RuleNum] [int] NULL ,
[ProcessStatus] [int] NULL ,
[IsEmail] [bit] NULL ,
[ErrorCode] [int] NULL ,
[ErrorDescription] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DesiredMonthlyPayment] [float] NOT NULL ,
[realtor_id] [bigint] NULL ,
[lead_type_id] [int] NULL ,
[lead_status_id] [int] NULL ,
[buy_property_type_id] [int] NULL ,
[sell_property_type_id] [int] NULL ,
[time_frame_id] [int] NULL ,
[best_time_id] [int] NULL ,
[matched_on] [varchar] (767) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_area2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_prefix2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_suffix2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_1] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_2] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_3] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_price_range_start] [money] NULL ,
[buy_price_range_end] [money] NULL ,
[buy_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_price_desired] [money] NULL ,
[sell_price_qualifying] [money] NULL ,
[sell_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[additional_info] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_in] [datetime] NULL ,
[date_sent] [datetime] NULL ,
[TrafficLogID] [bigint] NULL ,
[notify_offers] [bit] NULL ,
[credit_history] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[has_agent] [bit] NULL ,
[found_home] [bit] NULL ,
[cell_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[why_selling] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[is_buysell] [bit] NULL ,
[Affiliate_ID] [bigint] NULL ,
[ExternalAdID] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE INDEX [index_lead_id] ON [dbo].[tbl_leads_result_queue]([lead_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [index_date_in] ON [dbo].[tbl_leads_result_queue]([date_in]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_RCResultMatch] (
[RCID] [bigint] NULL ,
[ResultID] [bigint] NULL ,
[DecisionChar] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlternatePhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_in] [datetime] NULL ,
[Acceptance_Failure_Description] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_admin_users] (
[admin_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[group_id] [int] NOT NULL ,
[user_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_created] [datetime] NOT NULL ,
[date_updated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_label_repository] (
[label_id] [int] NOT NULL ,
[label] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[label_type_id] [int] NOT NULL ,
[delete_flag] [int] NOT NULL ,
[label_order_id] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Dive_test_description] (
[RuleNO] [int] NOT NULL ,
[Test_Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Rationale] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Positions] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-
I've not used XML in an SP, so I do not know what this is doing:
SELECT '<XMLData>
<Decisions>
<Decision char="N" desc="Not Verified" />
<Decision char="R" desc="Rejected" />
<Decision char="V" desc="Verified" />
</Decisions>'
Also, I think your phone values may need to be checked - you seem to re-using the same value twice, (and if any of the concatenated phone values are NULL, the entire expression will return NULL).
Can you verify what you are passing in for DateFr and DateTo? That may be causing diffulty... Can you print those values to screen?
I am also not seeing either implicit or explicit joins on: dbo.tbl_Dive_Test_Description TTT, dbo.tbl_RCResultMatch RCRM. You may have them and I simply overlooked them...
Are you seeing blocking in the profiler? Can you provide a smattering of sample data?
I wasn't born stupid - I had to study.
-
I see the issue with the phone value being used twice..
The datefr and dateto are
SET @DateFr = '5/11/2006 12:00:00AM'
SET @DateTo = '5/11/2006 11:59:59PM'
But it also accepts @DateFr = '5/11/2006' and dateto = '5/11/2006'
I look into the joins.. and I didnt see any blocking in the profiler.
Here's an example of the output..
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<XMLData>
<Decisions>
<Decision char="N" desc="Not Verified" />
<Decision char="R" desc="Rejected" />
<Decision char="V" desc="Verified" />
</Decisions>
XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<row RCID="2064338" First_Name="Joelle" Last_Name="doe" Phone_Number="799-799-2389" Lead_Type="SELL" DecisionChar="V" Reason="Name, Address and Phone Match" DecisionBy="Dive" Date_IN="2006-05-11T00:01:06" Affiliate_ID="99999"/><row RCID="2064339" First
onBy="dive" Date_IN="2006-05-11T00:08:41" Affiliate_ID="99999"/><row RCID="2064347" First_Name="Jerod" Last_Name="doe" Phone_Number="576-576-9879" Lead_Type="BUY" DecisionChar="V" Reason="Name, Address and Phone Match" DecisionBy="dive" Date_IN="2006-
t_Name="Lee" Phone_Number="475-475-9228" Lead_Type="BUY" DecisionChar="N" Reason="Phone is cellular (regardless of name and address match)" DecisionBy="(IC) Hope Thorson" Date_IN="2006-05-11T00:29:06" Affiliate_ID="31"/>
----------
</XMLData>
-
The difference is likely due to parameter sniffing. Search around, there are many references to the issue, both here
and in the SQL Server newsgroups
Here's one:
-
You may want to try: CONVERT( varchar(10), LQ.Date_IN, 101) BETWEEN @DateFr AND @DateTo
Instead of: LQ.Date_IN BETWEEN @DateFr AND @DateTo
I wasn't born stupid - I had to study.
-
Note: using CONVERT( varchar(10), LQ.Date_IN, 101) BETWEEN @DateFr AND @DateTo may eliminate the use of an index on LQ.Date_IN column which may be undesireable!
-
Yip! Major downside if Timeout is an issue. But might be a good test to see if the dates are having trouble...
I wasn't born stupid - I had to study.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply