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 2008 Forums
 Transact-SQL (2008)
 Need Help on Fastest Search Logic

Author  Topic 

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-28 : 08:18:30
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

940 Posts

Posted - 2013-09-28 : 10:44:31
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

52326 Posts

Posted - 2013-09-29 : 02:57:56
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

338 Posts

Posted - 2013-09-30 : 07:05:27
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

52326 Posts

Posted - 2013-09-30 : 10:00:54
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
Aged Yak Warrior

550 Posts

Posted - 2013-09-30 : 15:24:21
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

338 Posts

Posted - 2013-10-01 : 17:01:00
thanks visakh and scott for the reply
Go to Top of Page
   

- Advertisement -