SQL Server Performance Forum – Threads Archive

Split function

You guys helped me with this one a while back and it was working great.
It is a poorly written procedure, we are in the middle of a rewrite, but until then I need to keep bandaiding…
I modified the proc to use the split function for the @string. It was working fine, but all of a sudden it’s taking minutes to run. When I run it via profiler I see it hanging on the split function. The old procedure is running faster than this one now.
Anyone have any ideas on where to begin my search??
ALTER PROCEDURE dbo.usp_GetRNLeadsCapOptimization –30,1,1, 0, 1, 94546
@LookBackDays INT,
@LeadTimeFrame INT,
@PropertyValue INT,
@WorkWithRealtor TINYINT,
@LeadTypeID TINYINT,
@strZIP VARCHAR(5)
AS
DECLARE @PriceRange MONEY,
@DateIn DATETIME,
@string varchar(200) BEGIN –SET @DateIn = CAST(CONVERT(VARCHAR(10), DATEADD(d, [email protected], CURRENT_TIMESTAMP), 101) AS DATETIME) + CAST(’12:00:00AM’ AS DATETIME)
–above line commented and substituted for devdb1 testing SET @DateIn = CAST(CONVERT(VARCHAR(10), DATEADD(d, [email protected], ’03/01/2006′), 101) AS DATETIME) + CAST(’12:00:00AM’ AS DATETIME)
SET @PriceRange =
(CASE WHEN @PropertyValue BETWEEN 1 AND 10
THEN @PropertyValue * 100000
WHEN @PropertyValue BETWEEN 11 AND 18
THEN (@PropertyValue -10) * 1000000 + 1000000
ELSE 10000000 end) if @LeadTimeFrame = 1 set @string=’24’
else if @LeadTimeFrame = 2 set @string=’23,24′
else if @LeadTimeFrame = 3 set @string=’23,24,25′
else if @LeadTimeFrame = 4 set @string=’23,24,25,107′
else if @LeadTimeFrame = 5 set @string=’96’ IF (@WorkWithRealtor = 0)
BEGIN
IF (@LeadTypeID = 1)
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, ”) AS buy_zip_2, ISNULL(tlq.buy_zip_3, ”) AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 1 — BUY
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id IN (select element from master.dbo.split(@string,’,’))
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, ”) = @strZIP OR ISNULL(tlq.buy_zip_3, ”) = @strZIP) UNION SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, ”) AS buy_zip_2, ISNULL(tlq.buy_zip_3, ”) AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3 –BUY/SELL
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (select element from master.dbo.split(@string,’,’))
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, ”) = @strZIP OR ISNULL(tlq.buy_zip_3, ”) = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, ”) AS buy_zip_2, ISNULL(tlq.buy_zip_3, ”) AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 2 –SELL
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id IN (select element from master.dbo.split(@string,’,’))
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP UNION SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, ”) AS buy_zip_2, ISNULL(tlq.buy_zip_3, ”) AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3 –BUY/SELL
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (select element from master.dbo.split(@string,’,’))
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
end
end

I would rather join table function instead of using IN predicate in the where clause. The last solution in the article bellow is probably the best for your problem: http://www.sql-server-performance.com/mm_list_random_values.asp

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mmarovic I would rather join table function instead of using IN predicate in the where clause. The last solution in the article bellow is probably the best for your problem: http://www.sql-server-performance.com/mm_list_random_values.asp</a> <hr height="1" noshade id="quote"></blockquote id="quote"> What a shameless plug this is. [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

Not to mention padding number of posts [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] post++

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mmarovic I would rather join table function instead of using IN predicate in the where clause. The last solution in the article bellow is probably the best for your problem: http://www.sql-server-performance.com/mm_list_random_values.asp</a> <hr height="1" noshade id="quote"></blockquote id="quote"> What a shameless plug this is. [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> <hr height="1" noshade id="quote"></blockquote id="quote"> I dont understand. Is there any homour? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> I dont understand. Is there any homour? <hr height="1" noshade id="quote"></blockquote id="quote"> Don’tknow if "shameless plug" is kind of slang, but since Mirko referenced his own article he did some kind of self-marketing. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

will try to help you geesathishkumar

You should probably get better performance if you rewrite the subquery part of the WHERE statement: eithr this: AND
(CASE @LeadTimeFrame
WHEN 1 THEN CASE WHEN tlq.time_frame_id = 24 THEN 1 ELSE 0 END
WHEN 2 THEN CASE WHEN tlq.time_frame_id IN (23, 24) THEN 1 ELSE 0 END
WHEN 3 THEN CASE WHEN tlq.time_frame_id IN (23, 24, 25) THEN 1 ELSE 0 END
WHEN 4 THEN CASE WHEN tlq.time_frame_id IN (23, 24, 25, 107) THEN 1 ELSE 0 END
WHEN 5 THEN CASE WHEN tlq.time_frame_id = 96 THEN 1 ELSE 0 END
END) = 1 or this: AND
((tlq.time_frame_id = 23 AND @LeadTimeFrame IN (2, 3, 4)
OR (tlq.time_frame_id = 24 AND @LeadTimeFrame IN (1, 2, 3, 4)
OR (tlq.time_frame_id = 25 AND @LeadTimeFrame IN (3, 4)
OR (tlq.time_frame_id = 107 AND @LeadTimeFrame = 4)
OR (tlq.time_frame_id = 96 AND @LeadTimeFrame = 5))

Do you truely need the unions to remove duplicates? if not, use "union all" instead of "Union" Michael B
Sr. DBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

]]>