Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server query optimization

Posted on 2011-05-13
6
Medium Priority
?
424 Views
Last Modified: 2012-05-11
The following query gets executed a lot, and I need to optimize it.

Users get to search for property based on keywords so, I have a function that checks if one or more of the keywords that the users selected on the website are found in the keyword column of the property's record (a multi-valued, comma separated list of keywords).

The table has about 4 million records - ...and normalizing ... to get rid of the multi-valued keyWord field will mean that I have to create a table with at several million records assuming that at least half of the records have values in the KeyWord field.

Here is the query:
The function, fn_mvo_searchall_rtrn_bln (also below) simply returns a 1 if one of the keywords is found in the multi-valued column.

I will appreciate your advice in the best way to optimize it.

select TOP 601 a.property_type,a.mlsnum,a.status,dbo.fn_sort_status(a.status)
from mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum
LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum
where (((a.lp BETWEEN 0 AND 299000)) AND ((a.status IN (5))))
AND (a.br >= 3) AND (a.ba >= 2) AND (a.sf >= 1200)
AND (dbo.fn_mvo_searchall_rtrn_bln('Bank Owned,Builder/Developer,Fixer',m.keyword)  = 1)
AND (a.property_type = 0)


function:


--drop function fn_mvo_searchall_rtrn_bln

ALTER function [dbo].[fn_mvo_searchall_rtrn_bln]

      (@listq varchar(500),@listc varchar(500))

returns integer
 
as

begin

declare @searchq varchar(25)
declare @searchc varchar(25)
declare @templist varchar(1000)
declare @out tinyint

set @out = 0

--remove blanks from the text to search for and the data to search thru
set @listq = replace(@listq, ' ', '')
set @listc = replace(@listc, ' ', '')

--loop until you reach the end of the comma separated list of items to look for
-- print 'START'
-- print ''
-- print '@listq: ' + @listq
-- print ''
-- print '@listc: ' + @listc
-- print ''

while @listq != ''
      begin
            --grab leftmost element in listq and assign to @searchq
            --(note that @listq - 1 excludes the comma that separates this element from the next element)
            if charindex(',',@listq) != 0
                  begin
                        set @searchq = left(@listq, charindex(',', @listq)-1)
                        set @listq = right(@listq,len(@listq) - charindex(',', @listq))
                  end
            else
                  begin
                        set @searchq = @listq
                        set @listq = ''
                  end

--             print '@searchq: ' + @searchq
--             print ''
--
--             print '@listq remaining: ' + @listq
--             print ''

            set @templist = @listc

            while @templist != ''
                  begin
                        --grab the leftmost element in the @templist and assign to @searchc
                        if charindex(',',@templist) != 0
                              begin
                                    set @searchc = left(@templist, charindex(',', @templist)-1)      
                                    set @templist = right(@templist,len(@templist) - charindex(',', @templist))
                              end
                        else
                              begin
                                    set @searchc = @templist
                                    set @templist = ''
                              end

--                         print '@searchc: ' + @searchc
--                         print ''
--
--                         print '@templist remaining: ' + @templist
--                         print ''
--
--                         print 'COMPARE: @searchq: ' + @searchq + ' TO @searchc: ' + @searchc
--                         print ''

                        if @searchq = @searchc
                              begin
--                                     print 'MATCH FOUND'
--                                     print ''
                                    set @out = @out + 1
                                    goto endplay
                              end
--                         else
--                               begin
--                                     print 'NO MATCH FOUND'
--                                     print ''
--                               end
                  end  --end inner loop
                  
      end   --end outer loop

endplay:
return(@out)

--print convert(varchar(10),@out)

end



0
Comment
Question by:dteshome
6 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35752535
What is the performance when you use:
AND m.keyword like  '%Bank Owned%'
AND m.keyword like '%Builder/Developer%'
AND m.keyword like %Fixer%'
Might not return the same results, but it is just to see if it is worth exploring this technique further.

0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35752538
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 35752988
check if the execution plan of this query gives you the same as your original query (the trick here is to call the UDF less time):

select TOP 601 AA.property_type,AA.mlsnum,AA.status,dbo.fn_sort_status(AA.status)
from (
SELECT a.property_type,a.mlsnum,a.status, m.keyword
from mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
/*LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum  --not used */
/*LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum --not used */
where (a.lp BETWEEN 0 AND 299000) AND (a.status = 5)
AND (a.br >= 3) AND (a.ba >= 2) AND (a.sf >= 1200)
AND (a.property_type = 0)
) AS AA
WHERE (dbo.fn_mvo_searchall_rtrn_bln('Bank Owned,Builder/Developer,Fixer',AA.keyword)  = 1)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dteshome
ID: 35756793
Hi, Emoreau and Nicobo

Emoreau, the query you modified, has the exact same execution plan, and has the same CPU time.

Nicobo, it is Inconceivable (to me) and amazing beyond my expectation that a ... LIKE ... search ... is 2200 times faster than using the UDF!

I was so sure that it using ...LIKE '%'''%' will be much, much slower, I did not even consider it.

Well, lessons learned, IF YOU ARE NOT SURE, DON'T BE DEFINATLY SURE!

Here is the STATISTICS from the runs:

--
SET STATISTICS TIME ON
--
select TOP 601 a.property_type,a.mlsnum,a.status,dbo.fn_sort_status(a.status), m.keyword -- 520 recs, in 9 secs
from mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum
LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum
where (((a.lp BETWEEN 0 AND 999000)) AND ((a.status IN (5))))
AND (a.br >= 3) AND (a.ba >= 2) AND (a.sf >= 1200)
AND (dbo.fn_mvo_searchall_rtrn_bln_DT('Bank Owned,Builder/Developer,Fixer,pool,Beverly Hills Adjacent,Turnkey',m.keyword)  = 1)
AND (a.property_type = 0)
--
--(601 row(s) affected)
--
--(1 row(s) affected)
--
--SQL Server Execution Times:
--   CPU time = 22656 ms,  elapsed time = 24398 ms.
--
SET STATISTICS TIME OFF
--

--AND (dbo.fn_mvo_searchall_rtrn_bln('Bank Owned,Builder/Developer,Fixer,Pool, Beverly Hills Adjacent',m.keyword)  = 1)
--Move-in, Pool, Remodeled, Lease and Sale, Beverly Hills Adjacent
--Furnished, Granite, Move-in, Pool, Turnkey
--
SET STATISTICS TIME ON
--
select TOP 601 a.property_type,a.mlsnum,a.status,dbo.fn_sort_status(a.status), m.keyword -- 559 in 0 secs
from mls_unified_svo_tbl a (nolock)
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum
LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum
where (((a.lp BETWEEN 0 AND 999000)) AND ((a.status IN (5))))
AND (a.br >= 3) AND (a.ba >= 2) AND (a.sf >= 1200)
AND (a.property_type = 0)
--AND (dbo.fn_mvo_searchall_rtrn_bln('Bank Owned,Builder/Developer,Fixer',m.keyword)  = 1)
--AND (m.keyword like  '%Bank Owned%' OR m.keyword like '%Builder/Developer%' or m.keyword like '%pool%' OR m.keyword like '%Fixer%')
AND (m.keyword like  '%Bank Owned%' OR m.keyword like '%Builder/Developer%' OR m.keyword like '%Fixer%' OR m.keyword like '%pool%' OR m.keyword like '%Beverly Hills Adjacent%' OR m.keyword like '%Turnkey%')
--
--(601 row(s) affected)
--
--(1 row(s) affected)
--
--SQL Server Execution Times:
--   CPU time = 313 ms,  elapsed time = 319 ms.
--
SET STATISTICS TIME OFF
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 35756945
Udf are perf killer
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35757792
>>it is Inconceivable (to me) and amazing beyond my expectation that a ... LIKE ... search ... is 2200 times faster than using the UDF!<<
Not when you see the UDF code, though: A multi-statement function involving looping has to be the worst possible combination.  It it had been an inline UDF, than it would have been quite a different story.


0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question