February 1, 2010 at 9:20 am
USE [DW]
GO
/****** Object: StoredProcedure [dbo].[spS_RS_MLH_BulkMail] Script Date: 01/29/2010 15:44:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[spS_RS_MLH_BulkMail]
@auctionnumbers varchar(4000)
,@bulkmailgroups varchar(200)
as
/******************************************************************************************
File:BULK MAIL DATA FILE
Comments:Executed on demand from Marketing. Uses DW tables.
History:
ChangeDate Author Description
---------- ---------------- ---------------------------------------------------------------
2009-03-09 K. Chong Receieved instruction from Ken
2009-03-10 W. Irwandy Add filter for 1 Mauchly
Add steps to dedupe the record
2009-04-17 Shotwell Added vars to drive behavior from a configuration.
2009-04-30 Shotwell Added following business rules
1) FirstName = "Test" and the LastName = "Test" then omit the row.
2) If the Email starts with "Test@" then omit the row. (Examples: Test@redcmail.com, Test@MyLandHome.com)
3) If the FirstName is a number then omit the row.
4) If the LastName is a number then omit the row.
5) Enhanced duplicate filtering to use transform_address_1 + zip
2009-05-27 Shotwell Added Brochure leads.
2009-06-08 Shotwell Added Registered Brokers to the selection options.
2009-07-21 HansonCreated in DW, minor changes for use in report
2009-07-21 HansonAdded creation of #tmp1 to reduce possibility of truncate errors
******************************************************************************************/
/*
DATA DEPENDENCIES
Nrpi-dw-sql01.dw. dbo.MLH_bidder_registration
Nrpi-dw-sql01.dw. dbo.MLH_states
Nrpi-dw-sql01.dw. dbo.MLH_buyer
Nrpi-dw-sql01.dw. dbo.MLH_property
REPORT PARAMETERS
Auction Number / Catalog ID
Lead Type (i.e. Pre-Registration, Bidder or Buyer)
*/
/*
go
-- CLEAN UP
if object_id('tempdb..#brochure') is not null
drop table#brochure
if object_id('tempdb..#tmp1') is not null
drop table#tmp1
if object_id('tempdb..#tmp2') is not null
drop table#tmp2
if object_id('tempdb..#tmp3') is not null
drop table#tmp3
go
*/
set nocount on
declare @IncludePreReg bit
, @IncludeBidders bit
, @IncludeBuyers bit
, @IncludeBrochure bit
, @IncludeBroker bit
, @BidBuyAuctionList varchar(200)
, @BrokerAuctionList varchar(200)
declare @stats table (Counter int, Label varchar(50), Stamp datetime)
declare @BidBuyData table (auctionNumber varchar(50))
declare @BrokerData table (auctionNumber varchar(50))
set @IncludePreReg = (select 1 where @bulkmailgroups like '%PreReg%')
set @IncludeBidders = (select 1 where @bulkmailgroups like '%Bidders%')
set @IncludeBuyers = (select 1 where @bulkmailgroups like '%Buyers%')
set @IncludeBrochure = (select 1 where @bulkmailgroups like '%Brochure%')
set @IncludeBroker = (select 1 where @bulkmailgroups like '%Broker%')
/* old manual code
set @IncludePreReg = 0
set @IncludeBidders = 0
set @IncludeBuyers = 0
set @IncludeBrochure = 1
set @IncludeBroker = 0
--set @BrokerAuctionList = 'H-082,H-063'
*/
set @BidBuyAuctionList = @auctionnumbers
set @BrokerAuctionList = @BidBuyAuctionList
insert @BidBuyData select ltrim(rtrim(val)) from dbo.udf_ParseToString(@BidBuyAuctionList,',')
insert @BrokerData select ltrim(rtrim(val)) from dbo.udf_ParseToString(@BrokerAuctionList,',')
insert @stats values(0,'Start',getdate())
-- STEP 1 : CREATE TEMP TABLE FOR RAW DATA FILE
create table #tmp1
(
keysk int identity(1,1)
,id int
,catalog_id varchar(50)
,first_name varchar(100)
,last_name varchar(100)
,address_1 varchar(1000)
,city varchar(100)
,state varchar(10)
,zip varchar(50)
,phone varchar(100)
,email varchar(100)
,posted_on datetime
,match_code varchar(50)
,transform_address_1 varchar(250)
,Source varchar(8)
)
-- ============================
-- LEAD TYPE : PRE-REGISTRATION
-- ============================
insert #tmp1(id, catalog_id, first_name, last_name, address_1, city, state, zip
, phone, email, posted_on, match_code, transform_address_1, Source)
select --identity(int,1,1) as keysk
a.bidderIDas id
, a.auctionNumberas catalog_id
, a.primarypurchaserfirstnameas first_name
, a.primarypurchaserlastnameas last_name
, a.primarypurchaseraddressas address_1
, ltrim(rtrim(a.primarypurchasercity))as city
-- , a.primarypurchaserstateas state
, b.abbrevation as state
, a.primarypurchaserzipas zip
, a.primarypurchaserworkas phone
, a.primarypurchaserEmailas email
, a.postedonas posted_on
, match_code =
dw.dbo.[fn_MatchCode] (a.primarypurchaserfirstname
,a.primarypurchaserlastname
,dbo.fnTransformAddressToken(a.primarypurchaseraddress)
,a.primarypurchaserzip)
, transform_address_1 = dbo.fnTransformAddressToken(a.primarypurchaseraddress)
, Source = cast('PreReg' as varchar(8))
--into #tmp1
from @BidBuyData x
join dw.dbo.MLH_bidder_registration a
on a.auctionNumber = x.auctionNumber
left
join dw.dbo.MLH_states b
on a.primarypurchaserstate = b.stateID
where @IncludePreReg = 1
and isregistered = 1
insert @stats values(@@rowcount,'PreReg',getdate())
insert #tmp1(id, catalog_id, first_name, last_name, address_1, city, state, zip
, phone, email, posted_on, match_code, transform_address_1, Source)
-- ===================
-- LEAD TYPE : BIDDERS
-- ===================
select a.bidderID as id
, a.auctionNumber as catalog_id
, a.primarypurchaserfirstnameas first_name
, a.primarypurchaserlastnameas last_name
, a.primarypurchaseraddress as address_1
, ltrim(rtrim(a.primarypurchasercity))as city
-- , a.primarypurchaserstateas state
, b.abbrevation as state
, a.primarypurchaserzipas zip
, a.primarypurchaserworkas phone
, a.primarypurchaserEmailas email
, a.postedonas posted_on
, match_code =
dw.dbo.[fn_MatchCode] (a.primarypurchaserfirstname
,a.primarypurchaserlastname
,dbo.fnTransformAddressToken(a.primarypurchaseraddress)
,a.primarypurchaserzip)
, transform_address_1 = dbo.fnTransformAddressToken(a.primarypurchaseraddress)
, Source = 'Bidder'
from @BidBuyData x
joindw.dbo.MLH_bidder_registration a
on a.auctionNumber = x.auctionNumber
left
join dw.dbo.MLH_states b
on a.primarypurchaserstate = b.stateID
where @IncludeBidders = 1
and isregistered = 1
and bidderNumber <> ''
insert @stats values(@@rowcount,'Bidder',getdate())
insert #tmp1(id, catalog_id, first_name, last_name, address_1, city, state, zip
, phone, email, posted_on, match_code, transform_address_1, Source)
-- =================
-- LEAD TYPE : BUYER
-- =================
select a.bidderIDas id
, a.auctionNumberas catalog_id
, a.primarypurchaserfirstnameas first_name
, a.primarypurchaserlastnameas last_name
, a.primarypurchaseraddressas address_1
, ltrim(rtrim(a.primarypurchasercity))as city
-- , a.primarypurchaserstateas state
, b.abbrevation as state
, a.primarypurchaserzipas zip
, a.primarypurchaserworkas phone
, a.primarypurchaserEmailas email
, a.postedonas posted_on
, match_code =
dw.dbo.[fn_MatchCode] (a.primarypurchaserfirstname
,a.primarypurchaserlastname
,dbo.fnTransformAddressToken(a.primarypurchaseraddress)
,a.primarypurchaserzip)
, transform_address_1 = dbo.fnTransformAddressToken(a.primarypurchaseraddress)
, Source = 'Buyer'
from @BidBuyData x
join dw.dbo.MLH_bidder_registration a
on a.auctionNumber = x.auctionNumber
join dw.dbo.MLH_buyer t2
on a.bidderid = t2.bidderid
join dw.dbo.MLH_property t3
on t2.property_id = t3.propertyid
left
join dw.dbo.MLH_states b
on a.primarypurchaserstate = b.stateID
where @IncludeBuyers = 1
insert @stats values(@@rowcount,'Buyer',getdate())
insert #tmp1(id, catalog_id, first_name, last_name, address_1, city, state, zip
, phone, email, posted_on, match_code, transform_address_1, Source)
-- =====================
-- LEAD TYPE : BROCHURE
-- =====================
select null as id
, a.CatalogID
, a.FirstNameas first_name
, a.LastNameas last_name
, a.AddressLine + isnull(space(1) + a.AddressLine2,'')as address_1
, ltrim(rtrim(a.City))as city
, a.Stateas state
, a.PostalCodeas zip
, a.Phoneas phone
, a.Emailas email
, a.CallDateas posted_on
, match_code =
dw.dbo.[fn_MatchCode] (a.FirstName
,a.LastName
,dbo.fnTransformAddressToken(a.AddressLine + isnull(space(1) + a.AddressLine2,''))
,a.PostalCode)
, transform_address_1 = dbo.fnTransformAddressToken(a.AddressLine + isnull(space(1) + a.AddressLine2,''))
, Source = 'Brochure'
from @BidBuyData x
join SharedSQL01.CallTrak.dbo.tblCalls a
on a.CatalogID = x.auctionNumber
where @IncludeBrochure = 1
insert @stats values(@@rowcount,'Brochure',getdate())
insert #tmp1(id, catalog_id, first_name, last_name, address_1, city, state, zip
, phone, email, posted_on, match_code, transform_address_1, Source)
-- =====================
-- LEAD TYPE : BROKER
-- =====================
select brokerID as id
, a.auctionNumber as CatalogID
, a.brokerFirstName as first_name
, a.brokerLastName as last_name
, a.Address as address_1
, ltrim(rtrim(a.City)) as city
--, a.State as state
, b.abbrevation as state
, a.zip as zip
, a.office as phone
, a.Email as email
, a.postedon as posted_on
, match_code =
dw.dbo.[fn_MatchCode] (a.brokerFirstName
,a.brokerLastName
,dbo.fnTransformAddressToken(a.Address)
,a.zip)
, transform_address_1 = dbo.fnTransformAddressToken(a.Address)
, Source = 'Broker'
from @BrokerData x
join dw.dbo.MLH_broker_registration a
on a.auctionNumber = x.auctionNumber
and a.isDel = 0
left
join dw.dbo.MLH_states b
on case when isnumeric(a.State) = 0
then 0
else cast(a.state as int)
end = b.stateID
where @IncludeBroker = 1
insert @stats values(@@rowcount,'Broker',getdate())
-------------
insert @stats select count(*), '*Total Raw data into #tmp1', getdate() from #tmp1
-- STEP 2 : CREATE TEMP TABLE FOR DISTINCT RECORD SET
-- ==================================================
select distinct *
into #tmp2
from #tmp1
where ltrim(isnull(address_1,'')) not in ('','PO Box','address','1 Mauchly')
and isnumeric(address_1) = 0
and isnumeric(First_Name) = 0
and isnumeric(Last_Name) = 0
and not (First_Name = 'Test' and Last_Name = 'Test')
and left(Email,5) != 'Test@'
insert @stats values(@@rowcount,'Simple distinct into #tmp2',getdate())
-- STEP 3 : CREATE TEMP TABLE FOR UNIQUE ADDRESS
-- SELECT ONLY ONE ADDRESS IF THERE ARE MULTIPLE NAMES ON THE SAME ADDRESS
-- =======================================================================
select a.*
into #tmp3
from #tmp2 a
join (select transform_address_1, zip
, keysk = min(keysk)
from #tmp2 b
group by transform_address_1, zip) b
on a.keysk = b.keysk
insert @stats values(@@rowcount,'De-dup by transform_address_1+zip into #tmp3',getdate())
-- Verify Duplicate Address
/*
select t3.*
from #tmp3 t3
join (select transform_address_1, zip
from #tmp3
group by transform_address_1, zip
having count(*)>1) x3
on t3.transform_address_1 = x3.transform_address_1
and t3.zip = x3.zip
order by 2,1 desc
--*/
-- STEP 4 : FINAL REPORT DATA SET
-- ==============================
-- Report will only shows the following columns
/**********************
Output stats
select * from @stats
**********************/
--/*
select catalog_id
, first_name
, last_name
, address_1
, city
, state
, zip
, source
from #tmp3
order by 1,3,2
--*/
/*
select Catalog_ID, Cnt = count(*)
select *
from #tmp3
group by Catalog_ID
order by Catalog_ID
compute sum(count(*))
--*/
GO
February 1, 2010 at 12:55 pm
Don't you think requesting to rewrite/optimize 400 lines of code crosses the line between forum help and consulting?
I personally think it does.
Howerver, here's something to start with:
To set your @Include* variables based on the value of @bulkmailgroups you should consider using the CASE statement.
Review your udf_ParseToString function whether it can be optimized.
The CASE statement might also help you to improve performance for filling #tmp1 (didn't look into it in detail).
If those hints won't help you to get the performance improved you might want to consider hiring a consultant for a day or two...
February 1, 2010 at 1:31 pm
Thank lutz your always very helpful, guess i should know the difference...ah
February 1, 2010 at 1:46 pm
Depending on how many records your inserting into your table variables, you may want to change those to temp tables. As the number of rows increases the chances of getting a poor execution plan increase greatly. The Query Optimizer will always assume that table variable contain 1 row of data even if it has several thousand rows.
February 1, 2010 at 2:15 pm
Thank you so much,its not actually my code i just want to simplify it and make it better
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply