Need help on improving my search query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help on improving my search query

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-25 : 08:23:52

I really need help on this search query:

I imported 3 DBF files into my DB, then I wrote the following SP to search in the 3 tables AND product table.

My problem is that it is taking for ever to run this query. I have following table properties:
O2_xREF = 4000 rows, 10 columns
SPO_xREF = 1000 rows, 15 columns
ECG_xREF = 400 rows, 5 columns

Products = 200 rows, 10 columns

-------------------- MY SP -----------------

CREATE Procedure spv_Products_CrossReferenceSearch
(
@SearchText nvarchar (50),
@Type int
)

AS

select distinct p.ProductID, p.ProductName, p.ShortDescription,
p.ImageUrl, ukprice=0, europrice=0

from Products p, O2_xREF o2, ECG_xREF ecg, SPO_xREF spo

WHERE (
p.ProductID LIKE @SearchText+ '%'
OR p.ProductName LIKE '%'+@SearchText+'%'
OR p.ShortDescription LIKE '%'+@SearchText+'%'
OR p.LongDescription LIKE '%'+@SearchText+'%'

-------- search in the O2 -----
OR o2.STK_REF LIKE @SearchText+ '%'
OR o2.SENSOR_ LIKE @SearchText+ '%'
OR o2.DISTRIBUTO LIKE @SearchText+ '%'
OR o2.PT_NOS_ LIKE @SearchText+ '%'
OR o2.OEM LIKE @SearchText+ '%'
OR o2.INSTRUMENT LIKE @SearchText+ '%'
OR o2.MAN__NO LIKE @SearchText+ '%'
OR o2.MANUFACTUR LIKE @SearchText+ '%'
OR o2.SOURCE LIKE @SearchText+ '%'

-- search in the ECG ---
OR ecg.Manufactur LIKE @SearchText+ '%'
OR ecg.Manufactu2 LIKE @SearchText+ '%'
OR ecg.Tech_MSB_N LIKE @SearchText+ '%'
OR ecg.Part_numbe LIKE @SearchText+ '%'
OR ecg.Opera_desc LIKE @SearchText+ '%'

---- search in the SPO ---

OR spo.VI_NO LIKE @SearchText+ '%'
OR spo.MANUFACTUR LIKE @SearchText+ '%'
OR spo.MFR_PT_NO_ LIKE @SearchText+ '%'
OR spo.PRODUCT_TY LIKE @SearchText+ '%'
OR spo.MODEL1 LIKE @SearchText+ '%'
OR spo.OEM LIKE @SearchText+ '%'
OR spo.VERIFICATI LIKE @SearchText+ '%'
OR spo.MCI_98 LIKE @SearchText+ '%'
OR spo.CONNECTOR LIKE @SearchText+ '%'
OR spo.EPIC_NO LIKE @SearchText+ '%'
OR spo.MME_NO LIKE @SearchText+ '%'
OR spo.ENVITEC_NO LIKE @SearchText+ '%'
OR spo.AMC_N0 LIKE @SearchText+ '%'
OR spo.AMC_CONNEC LIKE @SearchText+ '%'
OR spo.STOCK_REF LIKE @SearchText+ '%'

and p.productid = o2.stk_ref
and p.productid = ecg.part_numbe
and p.productid = spo.stock_ref
)

order by p.productid desc
GO
-------------------END ------------

Any idea how I can improve this search query

kind regards

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-25 : 08:29:50

first of all... you ands at the bottom don't really fit as you don't have your 'or's seperated...(Joins would be better) I would suggest a union of individual column searches...


select p.ProductID, p.ProductName, p.ShortDescription, p.ImageUrl, ukprice=0, europrice=0
from Products p
Inner Join O2_xREF o2, 
On p.productid = o2.stk_ref
Inner Join ECG_xREF ecg, 
On p.productid = ecg.part_numbe
Inner Join SPO_xREF spo
On p.productid = spo.stock_ref
WHERE p.ProductID LIKE @SearchText+ '%'

Union All

select p.ProductID, p.ProductName, p.ShortDescription, p.ImageUrl, ukprice=0, europrice=0
from Products p
Inner Join O2_xREF o2, 
On p.productid = o2.stk_ref
Inner Join ECG_xREF ecg, 
On p.productid = ecg.part_numbe
Inner Join SPO_xREF spo
On p.productid = spo.stock_ref
WHERE p.ProductName LIKE '%'+@SearchText+'%'

Union All

select p.ProductID, p.ProductName, p.ShortDescription, p.ImageUrl, ukprice=0, europrice=0
from Products p
Inner Join O2_xREF o2, 
On p.productid = o2.stk_ref
Inner Join ECG_xREF ecg, 
On p.productid = ecg.part_numbe
Inner Join SPO_xREF spo
On p.productid = spo.stock_ref
WHERE p.ShortDescription LIKE '%'+@SearchText+'%' 

Union All

select p.ProductID, p.ProductName, p.ShortDescription, p.ImageUrl, ukprice=0, europrice=0
from Products p
Inner Join O2_xREF o2, 
On p.productid = o2.stk_ref
Inner Join ECG_xREF ecg, 
On p.productid = ecg.part_numbe
Inner Join SPO_xREF spo
On p.productid = spo.stock_ref
WHERE p.LongDescription LIKE '%'+@SearchText+'%'

...

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-25 : 08:35:47

There are several improvements which could be made.

This one is only syntax. LEFT OUTER reads better to me than the CROSS / WHERE that appears below.

How are the tables indexed?


CREATE Procedure spv_Products_CrossReferenceSearch
(
    @SearchText nvarchar (50),
@Type int
)

AS

select distinct p.ProductID, p.ProductName, p.ShortDescription, 
p.ImageUrl, ukprice=0, europrice=0

  FROM Products P
  LEFT OUTER JOIN O2_xREF o2 ON p.productid = o2.stk_ref
  LEFT OUTER JOIN ECG_xREF ecg p.productid = ecg.part_numbe
  LEFT OUTER SPO_xREF spo p.productid = spo.stock_ref

WHERE (
p.ProductID LIKE @SearchText+ '%'
 OR p.ProductName LIKE '%'+@SearchText+'%'
		 OR p.ShortDescription LIKE '%'+@SearchText+'%' 
		 OR p.LongDescription LIKE '%'+@SearchText+'%'


-------- search in the O2 -----
 OR o2.STK_REF LIKE @SearchText+ '%'
 OR o2.SENSOR_ LIKE @SearchText+ '%'
 OR o2.DISTRIBUTO LIKE @SearchText+ '%'
 OR o2.PT_NOS_ LIKE @SearchText+ '%'
 OR o2.OEM LIKE @SearchText+ '%'
 OR o2.INSTRUMENT LIKE @SearchText+ '%'
 OR o2.MAN__NO LIKE @SearchText+ '%'
 OR o2.MANUFACTUR LIKE @SearchText+ '%'
 OR o2.SOURCE LIKE @SearchText+ '%'

-- search in the ECG ---
OR ecg.Manufactur LIKE @SearchText+ '%'
 OR ecg.Manufactu2 LIKE @SearchText+ '%'
 OR ecg.Tech_MSB_N LIKE @SearchText+ '%'
 OR ecg.Part_numbe LIKE @SearchText+ '%'
 OR ecg.Opera_desc LIKE @SearchText+ '%'

---- search in the SPO  ---

OR spo.VI_NO LIKE @SearchText+ '%'
OR spo.MANUFACTUR LIKE @SearchText+ '%'
OR spo.MFR_PT_NO_ LIKE @SearchText+ '%'
OR spo.PRODUCT_TY LIKE @SearchText+ '%'
OR spo.MODEL1 LIKE @SearchText+ '%'
OR spo.OEM LIKE @SearchText+ '%'
OR spo.VERIFICATI LIKE @SearchText+ '%'
OR spo.MCI_98 LIKE @SearchText+ '%'
OR spo.CONNECTOR LIKE @SearchText+ '%'
OR spo.EPIC_NO LIKE @SearchText+ '%'
OR spo.MME_NO LIKE @SearchText+ '%'
OR spo.ENVITEC_NO LIKE @SearchText+ '%'
OR spo.AMC_N0 LIKE @SearchText+ '%'
OR spo.AMC_CONNEC LIKE @SearchText+ '%'
OR spo.STOCK_REF LIKE @SearchText+ '%'
)

order by p.productid desc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 08:47:28

Refer this also
http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-25 : 10:20:34

Thanks everyone.

I started off using INNER JOIN but that did not work...so I wrote the above query.

(1)But SamC, your query has worked perfectly well.
Can you provide a quick explanation why Inner Join did not work, but Left Outer Join works???

(2) Seventhnight, if I was going to use your query, would I add other statements to search in other columns...like:

Union All

select p.ProductID, p.ProductName, p.ShortDescription, p.ImageUrl, ukprice=0, europrice=0
from Products p
Inner Join O2_xREF o2,
On p.productid = o2.stk_ref
Inner Join ECG_xREF ecg,
On p.productid = ecg.part_numbe
Inner Join SPO_xREF spo
On p.productid = spo.stock_ref
WHERE o2.stk_ref '%'+@SearchText+'%'

regards

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-10-25 : 10:43:10

2) yes.... you got it

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-26 : 09:38:31

Hi
I think I need some help again. I have following SP. It seaches for a word in the tables and gets category names AND how many records are found for that particularly category:

CREATE Procedure spv_Products_CRSearchCategories
(
@SearchText nvarchar (50),
@Type int
)

AS

select (cat.CategoryID), (cat.Name) , count(p.productid) as RecordsFound

FROM Products p
Inner JOIN Categories cat ON p.categoryid = cat.categoryID
LEFT OUTER JOIN O2_xREF o2 ON p.productid = o2.stk_ref
LEFT OUTER JOIN ECG_xREF ecg ON p.productid = ecg.part_numbe
LEFT OUTER JOIN SPO_xREF spo ON p.productid = spo.stock_ref

WHERE (
p.ProductID LIKE @SearchText+ '%'
OR p.ProductName LIKE '%'+@SearchText+'%'
OR p.ShortDescription LIKE '%'+@SearchText+'%'
OR p.LongDescription LIKE '%'+@SearchText+'%'
........................
and
cat.verified =1
)

GROUP BY cat.CategoryID, cat.Name

order by cat.name desc
GO
------------------END -------------

This query runs and I can get the categories, BUT the RecordsFound is in correct. I only have 200 rows in my Product table, so I would expect the RecordsFound to be within it. I am getting RecordsFound=3000 for some categories

Can anyone suggestion how I can improve this query so I can get correct RecordsFound counter.

regards

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-26 : 11:11:32

First - you're asking for the count of records in the Products table that satisfy the condition and why your count is high?

This is a good question.

Visualize the [Products INNER JOIN Categories] table of 200 or so rows. You want to count the rows that satisfy the WHERE condition and it won't be more than the 200 rows. Problem is, LEFT OUTER JOINING this resultset with the three MMM_xRef tables will have multiple matches, expanding the resultset rowcount GREATLY. Let's rewrite the query so this doesn't happen.

To do this, I'd suggest JOINING to a subquery that has UNIQUE (not multiple) matches so the resulting recordset of [Products INNER JOIN Categories] will be smaller (not greater). Here's a subquery that returns a recordset of unique ProductIDs that meet your WHERE condition.


SELECT P.ProductID -- This recordset contains ProductIDs that match the WHERE condition.
  FROM Products P
  LEFT OUTER JOIN O2_xREF o2 ON p.productid = o2.stk_ref
  LEFT OUTER JOIN ECG_xREF ecg ON p.productid = ecg.part_numbe
  LEFT OUTER JOIN SPO_xREF spo ON p.productid = spo.stock_ref

  WHERE (
    p.ProductID LIKE @SearchText+ '%'
    OR p.ProductName LIKE '%'+@SearchText+'%'
    OR p.ShortDescription LIKE '%'+@SearchText+'%'
    OR p.LongDescription LIKE '%'+@SearchText+'%' -- Et Cetera

  GROUP BY P.ProductID -- Ensures the returned recordset has no duplicate ProductIDs

Now you need to join this subquery to get the final recordset and accurate counts


select cat.CategoryID, cat.Name , count(*) as RecordsFound

  FROM Products p
  Inner JOIN Categories cat ON p.categoryid = cat.categoryID
  INNER JOIN (
    -- Insert the above subquery here
  ) A ON P.ProductID = A.ProductID
  GROUP BY cat.CategoryID, cat.Name

There may be other ways of getting the same result. Anyone?

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 01:05:06

hasanali00 this is always going to be a server killer. Can the user tell you which columns to search in, rather than searching the lot?

If so, and you had a parameter for each one, then you could do something like:


WHERE     (@Search_MANUFACTUR = '' OR spo.MANUFACTUR LIKE @Search_MANUFACTUR + '%')
      AND (@SearchText_MFR_PT_NO = '' OR spo.MFR_PT_NO_ LIKE @SearchText_MFR_PT_NO + '%')

and assuming the user only gives you one or two bits of data then this should be quicker, as the columns they are not interested in will not be searched, and particularly if the "most commonly used columns" are indexed.

Alternatively you might be better using the Full Text search option in SQL

Kristen

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-11-08 : 08:04:45

Dear all,

I was happy that (thanks to Sam and Steven) I got my search SP working.

Hhowever, recently I have been asked to change my SP from using OR to AND. I am passing in a string like 'monitors#sensos' and I want to return records that contain both of these words. I am spliting the string with Steves'function (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648)

Previously, I had a SP like:

------------------------
CREATE Procedure spv_Products_CRSearchCategories
(
@SearchText nvarchar (50),
@Type int
)

AS

select (cat.CategoryID), (cat.Name) , count(cat.categoryid) as RecordsFound

FROM Products pr
Inner JOIN Categories cat ON pr.categoryid = cat.categoryID
INNER JOIN (

SELECT P.ProductID -- This recordset contains ProductIDs that match the WHERE condition.
FROM Products P

LEFT OUTER JOIN O2_xREF o2 ON p.productid = o2.stk_ref
LEFT OUTER JOIN ECG_xREF ecg ON p.productid = ecg.part_numbe
LEFT OUTER JOIN SPO_xREF spo ON p.productid = spo.stock_ref

inner join (Select data from dbo.Split(@SearchText,'#')) B on p.productname like '%' + B.Data + '%' or p.productid like '%' + B.Data + '%'

OR p.ShortDescription LIKE '%'+B.data+'%'
OR p.LongDescription LIKE '%'+B.data+'%'

-------- search in the O2 -----
OR o2.STK_REF LIKE B.data+ '%'
OR o2.SENSOR_ LIKE '%' +B.data+ '%'
OR o2.DISTRIBUTO LIKE '%' +B.data+ '%'
OR o2.PT_NOS_ LIKE '%' +B.data+ '%'
OR o2.OEM LIKE '%' +B.data+ '%'
OR o2.INSTRUMENT LIKE '%' +B.data+ '%'
OR o2.MAN__NO LIKE '%' +B.data+ '%'
OR o2.MANUFACTUR LIKE '%' +B.data+ '%'
OR o2.SOURCE LIKE '%' +B.data+ '%'

GROUP BY P.ProductID
)
A ON Pr.ProductID = A.ProductID

GROUP BY cat.CategoryID, cat.Name

order by cat.name desc
GO
---------

Obviously, if I change the OR to AND, nothing is returned.

So can anyone tell me how I can change the above code to pass in a string like 'monitor#sensor#accessories' and use the AND operator to search for each word in my table.

kind regard

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-08 : 09:09:12

Have you oversimplified the requirement? Won't the passed string contain either AND or OR operators?

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-11-08 : 09:28:43

Sorry, Sam, I did not quite understand you. Do you mean, I should pass in the string as 'monitors AND sensors' instead of 'monitors#sensors'

regards

SamC
White Water Yakist

3467 Posts

Posted - 2005-11-08 : 14:15:01

What about

(monitors displays lcd)#sensors

(monitors OR displays OR lcd) AND sensors

or

(monitors displays lcd)#(sensors devices widget)

(monitors OR displays OR lcd) AND (sensors OR devices OR widget)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources