Can u try this Query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Can u try this Query

Author

Topic

ereader
Yak Posting Veteran

50 Posts

Posted - 2002-12-09 : 01:16:26

H All!

I am going to find the country in my table of 300000 rows
USA in col1 of my table like
-USA
USA
USA-
-USA-

The following are the queries i have tried but anyone has a better query to seacrh a specific pattern

-- 1 =================================================
select col1 from tb1
where col1 like '%USA%'
-- 2 =================================================
select col1 from tb1
where(PATINDEX('%[^a-z]' + 'USA' + '[^a-z]%', ' ' + col1 + ' ') != 0)

Both of the query taking lot of resources and 2nd one is the bit
costly, i have tried full text searching also but it's not upto the mark.

Can anyone try hands on it

Edited by - ereader on 12/09/2002 03:02:57

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-09 : 01:57:55

quote:

I am going to find the country in my table of 300000 rows
USA in col1 of my table like
-USA
USA
USA-

how about
select col1 from tab1 where col1 in ('-USA','USA','USA-')

PS - don't double-post - means that unscrupulous people like me can get a lot of extra credit for the same information.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

ereader
Yak Posting Veteran

50 Posts

Posted - 2002-12-09 : 03:01:08

Thanks rrb

************************************************
select col1 from tab1 where col1 in ('-USA','USA','USA-')
************************************************
i think u must be very happy
but the query i am looking for is not like that

so be cool and have some more information

col1's lenght is 500 varchar

and the data can be like
-SED-FRT-FTG-LOK-MJK-LOI-DRF-USQ-USA-PDW
now that's my actual requ.

can u pls

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-12-09 : 04:15:08

The trouble for you in getting the right answer...is that you didn't say "and the data can be like -SED-FRT-FTG-LOK-MJK-LOI-DRF-USQ-USA-PDW " FIRST......

While simplfying your data for presentation to those not familiar with your application MAY seem a good idea....in effect it prolongs the time until you get a proper solution....because we respond with solutions to your problem as 1st PRESENTED.

Now.....regarding your TRUE problem....
both your original queries work....but the response time isn't great looking....right?

why?.....take an example.....say....I want to find all occurrance of the 2 consecutative characters "the" in a book (any book, maybe a phone book)....I'd have to read every page and inspect every line and identify the relevent lines.....quite an intensive task

same here....

what would make it easier would be it I only had to find words that BEGAN with "the"...because then I might be able to use the index of names/words at the back of the book to narrow the search down....

but that's a problem with using a LIKE statement with the leading "%"

Either you put up with your performance problem or you re-design your data structure to be simpler to use.

And if your text string "SED-FRT-FTG-LOK-MJK-LOI-DRF-USQ-USA-PDW" actually represents MULTIPLE individual values (ie SED, FRT, FTG, etc) concatanated together....then best advice would be to split them up into multiple records....looking something like the following....

mainrecord (id)
subrecord (fkid, sequence, value)

subrecord values....
1, 1, SED
1, 2, FRT
1, 3, FTG
1, 4, LOK
1, 5, MJK
1, 6, LOI
1, 7, DRF
1, 8, USQ
1, 9, USA
1, 10,PDW

While it might look as if your data problem will expand a number of rows from 300,000 to 3,000,000...this isn't a problem to SQL Server.....and it does mean a proper solution to your perofrmance problem would be available......

ereader
Yak Posting Veteran

50 Posts

Posted - 2002-12-09 : 05:04:28

quote:

mainrecord (id)
subrecord (fkid, sequence, value)
subrecord values....
1, 1, SED
1, 2, FRT
1, 3, FTG
1, 4, LOK
1, 5, MJK
1, 6, LOI
1, 7, DRF
1, 8, USQ
1, 9, USA
1, 10,PDW

Thanks

It seems to be tuff one to split the pre-existing data of 3,00,000 rows
is it

But i'll try

ereader
Yak Posting Veteran

50 Posts

Posted - 2002-12-09 : 05:52:02

Andrew

will it be ok

if i'll implement it on the 4 col with each contains data length
of at least of 300 characters
Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-12-09 : 08:40:03

300,000 records each 400 chars long will take up (in general) the same amount of space as 3,000,000 each 40 chars wide....

so volume of data (and thus database size) isn't your problem....it's the access method of searching through the 300,000 records.

What i'm pointing out is that under my structure you can put an index on the subrecord table (value column) to help improve searching for records matching that criteria.....an index on the value column in your current structure, would only be of use if USA was in the 1st 3 characters....

my advice to you is to try the new structure....create a new table with some sample data (sufficient to show the benefit of the different structure)....remember to put an index on the value column (with the value column being the 1st column if more than 1 column in the index)

then look at the execution plan....and try some sample searches.
best evidence is often what can been seen through experimentation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources