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.
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 intrecords 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 valuesrecords count on Table2 : 186289( may increase in future)sample data: Table1:1 canola 1202 bread 1303 sauce 1404 corn 120Table2:1 canola,tea,muffin,cheese jellyproducts null2 vinegar,canola,sunflower oliproducts null3. cornil,vegoil,canola,sesameoil oilproducts nullmy 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 t1inner join dbo.Table2 t2 on t2.product_name like '%' + t1.product_name + '%' Try2: select T2.* from dbo.Table2 T2inner 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) |
|
|
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 performanceThe current way you have it best possible option is to use tally table approach to parse out values and then do search with ithttp://www.sqlservercentral.com/articles/Tally+Table/72993/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 articleCreate it in your database and then call it passing the comma separated value column to get individual values out.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
sqllover
Constraint Violating Yak Guru
338 Posts |
Posted - 2013-10-01 : 17:01:00
|
thanks visakh and scott for the reply |
|
|
|
|
|
|
|