Hi group,
I have a select statement that if run against a 1 million record
database directly in query analyzer takes less than 1 second.
However, if I execute the select statement in a stored procedure
instead, calling the stored proc from query analyzer, then it takes
12-17 seconds.
Here is what I execute in Query Analyzer when bypassing the stored
procedure:
USE Verizon
GO
DECLARE @phonenumber varchar(15)
SELECT @phonenumber = '6317898493'
SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance
FROM BadDebt
WHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =
@phonenumber)
order by Service_Connection_Date desc
RETURN
GO
Here is what I execute in Query Analyzer when calling the stored
procedure:
DECLARE @phonenumber varchar(15)
SELECT @phonenumber = '6317898493'
EXEC Verizon.dbo.baddebt_phonelookup @phonenumber
Here is the script that created the stored procedure itself:
CREATE PROCEDURE dbo.baddebt_phonelookup @phonenumber varchar(15)
AS
SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance
FROM BadDebt
WHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =
@phonenumber)
order by Service_Connection_Date desc
RETURN
GO
Using SQL Profiler, I also have the execution trees for each of these
two different ways of running the same query.
Here is the Execution tree when running the whole query in the
analyzer, bypassing the stored procedure:
--------------------------------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Verizon].[dbo].[BadDebt]))
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Concatenation
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),
SEEK:([BadDebt].[Telephone_Number]=[@phonenumber]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),
SEEK:([BadDebt].[Telephone_Number_Redef]=[@phonenumber]) ORDERED
FORWARD)
--------------------------------------
Finally, here is the execution tree when calling the stored procedure:
--------------------------------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
--------------------------------------
Thanks for any help on my path to optimizing this query for our
production environment.
Regards,
Warren Wright
Scorex Development Team
