SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help on Fastest Search Logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
296 Posts

Posted - 09/28/2013 :  08:18:30  Show Profile  Reply with Quote
Hi,

I have two tables named "Table1" and "Table2".

Table1 Details:

id bigint, product_name nvarchar(1000),quantity int

records count on Table1 : 25000( may increase in future)

Table2 Details:

id bigint, product_name nvarchar(max),details nvarchar(1000), description nvarchar(1000)

Note: Table2 productName column will have comma separated values
records count on Table2 : 186289( may increase in future)

sample data:


Table1:

1 canola 120
2 bread 130
3 sauce 140
4 corn 120

Table2:

1 canola,tea,muffin,cheese jellyproducts null
2 vinegar,canola,sunflower oliproducts null
3. cornil,vegoil,canola,sesameoil oilproducts null

my requirement is i will take each prodcutname from Table1 and will search it in Table2's prodcutname column. if matches found then get the row and insert into temp table.

i tried with while loop/cursor with "like" condition but it takes 15+ hours to process the records. I know searching in comma separate data with "like" will take time, but it take 15+ hours and still running.

if am not wrong that looping will be bad on this larger records. so i have tried with joins as well. but nothing seems reducing the processing time.




Try1 : 

select T2.* 
from dbo.Table1 t1
inner join dbo.Table2 t2 on t2.product_name like '%' + t1.product_name + '%' 


Try2: 

select T2.* 
from dbo.Table2 T2
inner join dbo.Table1 T1 on CHARINDEX(T1.product_name, T2.product_name) > 0 





Is there any fastest way to achieve this requirement? please help me on this and share with me sample code if there is any.

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 09/28/2013 :  10:44:31  Show Profile  Reply with Quote
You should scrap the comma separated approach in table2 and create a proper, normalised schema with a single-product table. When you've done this then you can do a normal join which will be very fast once correctly indexed.
Then you can work on why you need a temporary table, which is likely to be unnecessary (but it's a distraction for now so don't concern yourself with this for now)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/29/2013 :  02:57:56  Show Profile  Reply with Quote
I second last suggestion. You should normlaize and store values in separate rows against the same id value if you want to get best performance

The current way you have it best possible option is to use tally table approach to parse out values and then do search with it

http://www.sqlservercentral.com/articles/Tally+Table/72993/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
296 Posts

Posted - 09/30/2013 :  07:05:27  Show Profile  Reply with Quote
Thanks everyone for the response. i agree by changing the table structure we ma achieve this, but unfortunately by this time i cannot do anything as it is already defined schema. Is there any other work around for this?

Tally table concept is weird and trying hard to understand.any more help please Visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/30/2013 :  10:00:54  Show Profile  Reply with Quote
quote:
Originally posted by sqllover

Thanks everyone for the response. i agree by changing the table structure we ma achieve this, but unfortunately by this time i cannot do anything as it is already defined schema. Is there any other work around for this?

Tally table concept is weird and trying hard to understand.any more help please Visakh



See the UDF created in the article

Create it in your database and then call it passing the comma separated value column to get individual values out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
268 Posts

Posted - 09/30/2013 :  15:24:21  Show Profile  Reply with Quote
Create a full-text index on table2.product_name. You can then use CONTAINS and the query will be vastly faster.
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
296 Posts

Posted - 10/01/2013 :  17:01:00  Show Profile  Reply with Quote
thanks visakh and scott for the reply
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000