Stored Proc creating 100% load on processor #1

[RogueSuit]

When ever I run this stored proc my SQL servers processor immediately hits 100% usage and the proc runs for quite a while (The table this runs from has over 9.7 million rows with 4 indexes). The 4 indexes were created based on the fields called in the where clauses. Any suggestions for optimization would be appreciated…

Thanks.

--****sp begin****
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE UserExtract
@BeginDateTime datetime
AS

EXEC sp_recompile UserExtract

--***********************************************
-- Description: Return a list of all users who --information has been updated (or added) since --the date passed in the @BeginDateTime --parameter. User information spans, --potentially, 3 tables(UserObject, --OrganizationObject and Addresses) and 4 --components (user, user's addresses,user's --organization, and user's organization's --addresses). If any one of these components --changed, the user's information should be --extracted.
--
--***********************************************

SET NOCOUNT ON

--***********************************************
-- Results from each recordset are saved in a --temporary table so that the final query will --return a single result set.
--***********************************************
CREATE TABLE #UpdatedUser
(
reg_date datetime,
db_data char(2),
datasource_name char(6),
g_user_id nvarchar(38),
g_org_id nvarchar(38),
RS_cust_type tinyint,
RS_cust_group char(1),
title nvarchar(50),
title2 nvarchar(20),
firstname nvarchar(50),
lastname nvarchar(50),
email varchar(60),
contactnumber2 nvarchar(20),
contactnumber3 nvarchar(20),
co_name nvarchar(50),
addr1 nvarchar(64),
addr2 nvarchar(64),
addr3 nvarchar(50),
city nvarchar(40),
state nvarchar(8),
zip nvarchar(20),
country nvarchar(5),
contactnumber1 nvarchar(20),
date_reg datetime,
RS_marketme_optout bit,
RS_email_optout bit,
RS_passport_id numeric(18,0),
RS_csa_contactme_id bit,
how_buy nvarchar(50),
how_research nvarchar(50),
RS_annualsales nvarchar(50),
RS_co_empcount nvarchar(50),
RS_bus_seg nvarchar(50),
RS_email_topics nvarchar(50),
RS_dist_enroll_nbr nvarchar(50),
gsa_smartpay bit,
RS_prod_interest nvarchar(50),
vpa_disc_pct int,
vpa_start_date datetime,
vpa_end_date datetime,
fed_tax_id nvarchar(13),
tax_ex_start_date datetime,
tax_ex_end_date datetime,
tax_ex_state nvarchar(50),
comm_chg_allow bit
)

--***********************************************
-- Find the user id, address reference id (g_id) --and organization id for all users, user --address, user organizations,or user --organization addresses changed. Note that the --id on the Address table can be either a user or --an organization. Three separate queries are --unioned together to get a list of user ids --about which some information has changed. The --first query checks if the user changed, the --second if the user address or user's company --address changed, and the third if the user's --company changed.
--***********************************************
DECLARE Users CURSOR
READ_ONLY

FOR SELECT g_user_id, g_org_id
FROM Profiles.dbo.UserObject WITH (NOLOCK)
WHERE d_date_last_changed > @BeginDateTime AND i_user_type in (1, 99) UNION
SELECT ISNULL(au.g_user_id, ao.g_user_id), ISNULL(au.g_org_id, ao.g_org_id)
FROM Profiles.dbo.Addresses a
LEFT OUTER JOIN Profiles.dbo.UserObject au WITH (NOLOCK) ON (a.g_id = au.g_user_id and au.i_user_type in (1, 99))
LEFT OUTER JOIN Profiles.dbo.UserObject ao WITH (NOLOCK) ON (a.g_id = ao.g_org_id and ao.i_user_type in (1, 99))
WHERE a.d_date_last_changed > @BeginDateTime
UNION
SELECT u.g_user_id, u.g_org_id
FROM Profiles.dbrganizationObject o
JOIN Profiles.dbo.UserObject u WITH (INDEX=IX_OrgID, NOLOCK) ON (o.g_org_id = u.g_org_id and u.i_user_type in (1, 99))
WHERE o.d_date_last_changed > @BeginDateTime

DECLARE @g_id nvarchar(38)
DECLARE @g_org_id nvarchar(38)
DECLARE @g_user_id nvarchar(38)
DECLARE @g_address_id nvarchar(38)
OPEN Users

FETCH NEXT FROM Users INTO @g_user_id, @g_org_id
WHILE (@@fetch_status <> -1)
BEGIN

--***********************************************
--Clear out the @g_address_id from the previous --row.
--***********************************************
SET @g_address_id = NULL
--*********************************************** -- Get the appropriate address for this user id, based on the following priority:
-- Address return order:
-- 1. Personal shipping
-- 2. Organization shipping
-- 3. Personal Mailing
-- 4. Organizational Mailing
-- 5. Personal Billing
-- 6. Organizational Billing
-- 7. Organizational Company Address
--
-- Note the Shipping Type Indicators --(i_address_type):
-- 0 Company Address
-- 1 Shipping Address
-- 2 Billing Address
-- 3 Mailing Address
--
-- If multiple addresses are found return the --most recent changed address.
--
-- If an address changes and the address type --does not fit the above rules then no change is --sent.
--*********************************************** SELECT TOP 1 @g_address_id = g_address_id
From Profiles.dbo.Addresses
Where (g_id = @g_user_id Or g_id = @g_org_id)
ORDER BY CASE WHEN @g_user_id = g_id AND i_address_type = 1 THEN 1
WHEN g_id = @g_org_id AND i_address_type = 1 THEN 2
WHEN g_id = @g_user_id AND i_address_type = 3 THEN 3
WHEN g_id = @g_org_id AND i_address_type = 3 THEN 4
WHEN g_id = @g_user_id AND i_address_type = 2 THEN 5
WHEN g_id = @g_org_id AND i_address_type = 2 THEN 6
WHEN g_id = @g_org_id AND i_address_type = 0 THEN 7
ELSE 8
END, Profiles.dbo.addresses.d_date_last_changed desc
--*********************************************** -- Insert the information for this user into the --temporary UpdatedUser table so it can later be --returned as a single recordset.
--*********************************************** INSERT INTO #UpdatedUser

SELECT u.d_date_last_changed,'RS', 'RS_WEB', u.g_user_id,u.g_org_id,u.u_CustomerType,
u.i_CustomerGroup, u.u_Salutation, u.u_user_title,u.givenname,u.sn,u.UPN,
u.u_tel_number,u.u_fax_number,u.u_CompanyName,
a.u_address_line1,a.u_address_line2,a.u_MailStop,a.u_city,a.u_region_code,a.u_postal_code,
a.u_country_code,a.u_tel_number,
u.d_date_registered,u.b_MarketMe,
u.b_EmailMe,u.n_PassportID,u.b_CSAContactMe,
u.u_PurchaseMethod,u.u_ResearchMethod,
u.i_AnnualSales,u.i_EmployeeCount,
u.u_BusinessSegment,u.u_EmailTopics,
u.i_DistrictEnrollNum,u.b_GSASmartPay,
u.u_ProductInterest,o.i_VPADiscountPercent,
o.d_VPAStartDate,o.d_VPAEndDate,o.u_FederalTaxID,o.d_TaxExemptStartDate,o.d_TaxExemptEndDate,
o.u_TaxExemptState,o.b_CommercialChargeAllow
FROM Profiles.dbo.UserObject u
LEFT OUTER JOIN Profiles.dbrganizationObject o ON (o.g_org_id = @g_org_id)
LEFT OUTER JOIN Profiles.dbo.Addresses a ON (g_address_id = @g_address_id)
WHERE u.g_user_id = @g_user_id AND
(u.d_date_last_changed > @BeginDateTime OR o.d_date_last_changed > @BeginDateTime OR a.d_date_last_changed > @BeginDateTime)

FETCH NEXT FROM Users INTO @g_user_id, @g_org_id
END

CLOSE Users
DEALLOCATE Users

--***********************************************
-- Return the full list of updated users and --their relevant information.
--***********************************************
SELECT DISTINCT reg_date, db_data, datasource_name, g_user_id,g_org_id,
RS_cust_type, RS_cust_group,title, firstname, lastname, email, contactnumber2, contactnumber3,
co_name, addr1, addr2, addr3, city, state, zip, country,contactnumber1,date_reg,
RS_marketme_optout, RS_email_optout, RS_Passport_id, RS_csa_contactme_id, how_buy,
how_research, RS_annualsales, RS_co_empcount, RS_bus_seg, RS_email_topics,RS_dist_enroll_nbr, gsa_smartpay, RS_prod_interest, vpa_disc_pct,
vpa_start_date, vpa_end_date, fed_tax_id, tax_ex_start_date, tax_ex_end_date,
tax_ex_state, comm_chg_allow

FROM #UpdatedUser
DROP TABLE #UpdatedUser

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--****sp end****