Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Need Help on Fastest Search Logic Expand / Collapse
Author
Message
Posted Tuesday, October 1, 2013 8:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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
Post #1500445
Posted Tuesday, October 1, 2013 8:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 42,761, Visits: 35,853
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 2008, MVP
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

Post #1500449
Posted Tuesday, October 1, 2013 8:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.....
Post #1500450
Posted Tuesday, October 1, 2013 12:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 1,899, Visits: 18,898
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 !
__________________________________________________________________
Post #1500534
Posted Tuesday, October 1, 2013 1:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.

Post #1500559
Posted Tuesday, October 1, 2013 2:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 1,899, Visits: 18,898
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 !
__________________________________________________________________
Post #1500595
Posted Tuesday, October 1, 2013 2:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351


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.

Post #1500599
Posted Wednesday, October 2, 2013 12:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:46 PM
Points: 36,944, Visits: 31,446
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500661
Posted Wednesday, October 2, 2013 7:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:46 PM
Points: 36,944, Visits: 31,446
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500964
Posted Thursday, October 3, 2013 4:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
Hi Jeff,

Thanks a lot for your time on this and product_name on the table1 is unique.
Post #1501111
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse