SQL Clone
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
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 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 Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219329 Visits: 46279
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
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11933 Visits: 37513
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
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11933 Visits: 37513
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
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 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 Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210559 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210559 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
born2achieve
born2achieve
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 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