Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help on Fastest Search Logic


Need Help on Fastest Search Logic

Author
Message
born2achieve
born2achieve
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 474
Hi Jeff,

Thanks a lot for your time on this and from temp table i am taking data and doing some other process. that will not be a deal. the actual issues is what you are looking on. if Cartesian product helps that would be great.

My eyes are rolling over to see your reply always.

Many thanks
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
Cartesian products aren't things that help ;-) This is a cartesian product:
SELECT <stuff> FROM LargeTable1 CROSS JOIN LargeTable2

You get a huge resultset with a number of row equal to the product of the row counts in each table (hence cartesian product). It's a great way to kill a server.

Is a table redesign not an option? It really, really would make stuff like this far easier.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


born2achieve
born2achieve
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 474
Hi Gila,

Thanks for your time,

The problem now is that production rolled out. so at this point of time the table design cannot be changed. looking for some temp solution.

Any hope.....
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33040
you appear to want to match "corn" with "cornil"...is this correct?

some set up data to play with,,,,,




CREATE TABLE [dbo].[Table1] (
[id] [bigint] NULL,
[product_name] [nvarchar](1000) NULL,
[quantity] [int] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2] (
[id] [bigint] NULL,
[product_name] [nvarchar](1000) NULL,
[details] [nvarchar](1000) NULL,
[description] [nvarchar](1000) NULL
) ON [PRIMARY]

INSERT INTO [Table1]([id],[product_name],[quantity])
VALUES(1,'canola',120)
INSERT INTO [Table1]([id],[product_name],[quantity])
VALUES(2,'bread',130)
INSERT INTO [Table1]([id],[product_name],[quantity])
VALUES(3,'sauce',140)
INSERT INTO [Table1]([id],[product_name],[quantity])
VALUES(4,'corn',120)

INSERT INTO [Table2]([id],[product_name],[details],[description])
VALUES(1,'canola,tea,muffin,cheese ','jellyproducts',NULL)
INSERT INTO [Table2]([id],[product_name],[details],[description])
VALUES(2,'vinegar,canola,sunflower ','oilproducts',NULL)
INSERT INTO [Table2]([id],[product_name],[details],[description])
VALUES(3,'cornil,vegoil,canola,sesameoil ','oilproducts',NULL)

GO

/*Try1 : */
SELECT t2.id,
t2.product_name,
t2.details,
t1.id AS T1_row
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.product_name LIKE '%' + t1.product_name + '%'

/*Try2: */
SELECT T2.id,
T2.product_name,
T2.details,
T1.id AS T1_row
FROM Table2 AS T2
INNER JOIN Table1 AS T1 ON CHARINDEX(T1.product_name, T2.product_name) > 0





________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

born2achieve
born2achieve
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 474
Hi Livingston,

thanks for your time on this and i have tried whatever you have shown couple of days before, the problem is time consuming and how to fasten the search process.
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33040
born2achieve (10/1/2013)
Hi Livingston,

thanks for your time on this and i have tried whatever you have shown couple of days before, the problem is time consuming and how to fasten the search process.



hello...the sql code was provided as a set up for others that may like to get engaged in this thread...this is something that you have not provided so far.

can you please confirm that, as in your original post, you want to match "corn" with "cornil" ??

good luck

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

born2achieve
born2achieve
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 474
Hi Livingston,

my bad. i didn't realize your set up data. The data is perfect but it's not cornil. it should be corn on the last insert row.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45127 Visits: 39923
Sorry about that... I was going to make a large amount of test data to test performance of various methods with but got caught up in another discussion.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45127 Visits: 39923
Haven't abandoned this and still working on a test data generator that everyone could use on this problem. Is the Product_Name in Table1 UNIQUE in Table1? Or can it be duplicated with multiple different Qty numbers?

Also, this is part of the reason why we ask for "the DDL and some readily consumable test data". It answers so many questions. Please see the first "Helpful Link" in my signature line below.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
born2achieve
born2achieve
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 474
Hi Jeff,

Thanks a lot for your time on this and product_name on the table1 is unique.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search