Query - need help using the IN function/statement

Hello all,
I am trying to find the instances in a field containg specific keywords or
strings of information. My table name is History, and my field name is
Notes. So what I am trying to do is find every record where History.Notess
conatins;
'chrom' or 'cell' or 'lab'
I think I need the IN functin as opposed to using a bunch of OR statements.
The OR statments work, but there are so many different keywords/strings,
that it is a real mess to enter all of the information.
Thank you for your help
JohnJohn,
You can put all the keywords in a table variable / temporary table /
permanent table a use:
select distinct notes
from history as h inner join t1 on h.notes like '%' + t1.keyword + '%'
Example:
use northwind
go
create table t1 (
c1 varchar(255)
)
go
create table t2 (
keyword varchar(25) not null unique
)
go
insert into t1 values('microsoft')
insert into t1 values('oracle')
insert into t1 values('microfocus')
go
insert into t2 values('micro')
insert into t2 values('of')
go
select distinct
t1.c1
from
t1
inner join
t2
on t1.c1 like '%' + t2.keyword + '%'
go
drop table t1, t2
go
Column [notes] can not be of type text / ntext.
AMB
"John Lloyd" wrote:

> Hello all,
> I am trying to find the instances in a field containg specific keywords or
> strings of information. My table name is History, and my field name is
> Notes. So what I am trying to do is find every record where History.Notes
s
> conatins;
> 'chrom' or 'cell' or 'lab'
> I think I need the IN functin as opposed to using a bunch of OR statements
.
> The OR statments work, but there are so many different keywords/strings,
> that it is a real mess to enter all of the information.
> Thank you for your help
> John
>sql

Query

Hi
I need this statement to print to the same log.txt when running xp_cmdshell
PRINT 'Upgrading.... ' + @.DataBaseName
How do I fit it into this command?
set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Thanks
JacoHi
Use
PRINT 'Upgrading.... ' + DB_NAME()
instead.
John
"Jaco" wrote:

> Hi
> I need this statement to print to the same log.txt when running xp_cmdshel
l
> PRINT 'Upgrading.... ' + @.DataBaseName
> How do I fit it into this command?
> set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
> c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
> exec master..xp_cmdshell @.command
> Thanks
> Jaco
>|||On Tue, 20 Sep 2005 03:16:05 -0700, Jaco wrote:

>Hi
>I need this statement to print to the same log.txt when running xp_cmdshell
>PRINT 'Upgrading.... ' + @.DataBaseName
>How do I fit it into this command?
>set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
>c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
>exec master..xp_cmdshell @.command
>Thanks
>Jaco
Hi Jaco,
If you can change the contents of c:\temp\Complete(7-9).sql, then add
this command at the beginning:
PRINT 'Upgrading.... ' + DB_NAME()
If you can't change c:\temp\Complete(7-9).sql, then use this:
set @.command = 'echo Upgrading.... ' + @.DataBaseName
+ ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Dariusz
Yes , if you use LIKE '%test%' so SQL Server probably will choose
TABLE/INDEX SCAN to perfom the query , however using LIKE 'test'% will
INDXEX/CLUSTERED INDEX SEEK. Well , obviously it depends on many things and
we don't know how do you run the queries?
I can't say that you are going to gain some benefits (in terms of
perfomance) of using FTI due to not using this feature for long time , so
maybe someone else can provide more info.
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23uECaDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||In most cases it will. If you have a restriction the restriction will be
applied after the results set comes back from the query of the full text
catalog. If you are returning a large number of rows this will be
expensive.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:%23uECaDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Please don't multipost , I have just answered the question in .programming
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||ok, sorry
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euvWZNZfGHA.1856@.TK2MSFTNGP03.phx.gbl...
> Please don't multipost , I have just answered the question in .programming
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>

Queries with "like" and full text indexes

Hi
I use ASPNET application using Sql Ser 2000 database. There are a lot of
queries using "like" statement.
I suffer bad performance of that application and I noticed in SQL Profiler
that those "like" queries takes a lot of time.
I heard about full text queries and I wonder if it could boost the queries
performance.
Please advise me if I'm right.
Best Regards
Darek T.Please don't multipost , I have just answered the question in .programming
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Hi
> I use ASPNET application using Sql Ser 2000 database. There are a lot of
> queries using "like" statement.
> I suffer bad performance of that application and I noticed in SQL Profiler
> that those "like" queries takes a lot of time.
> I heard about full text queries and I wonder if it could boost the queries
> performance.
> Please advise me if I'm right.
>
> Best Regards
> Darek T.
>|||ok, sorry
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:euvWZNZfGHA.1856@.TK2MSFTNGP03.phx.gbl...
> Please don't multipost , I have just answered the question in .programming
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:uEudZDZfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Hi
>> I use ASPNET application using Sql Ser 2000 database. There are a lot of
>> queries using "like" statement.
>> I suffer bad performance of that application and I noticed in SQL
>> Profiler that those "like" queries takes a lot of time.
>> I heard about full text queries and I wonder if it could boost the
>> queries performance.
>> Please advise me if I'm right.
>>
>> Best Regards
>> Darek T.
>

QA vs sproc mystery

Here's a poser (at least to me!)
When I run a select statement in QA the statement uses available indexes
(Index Scan) and is quite fast, but when I take the same script, place it in
a sproc, and execute the sproc, it uses a Table Scan rather than an Index
S on the Juvenile table - killing performance.
Any pointers would be appreciated
here's the script:
Select
J.JuvenileID,
(J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1)
+ ')' ) as JuvFullName,
Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
From Juvenile J
Inner Join Placement P on J.JuvenileID = P.JuvenileID
Where juvPlaSite = @.SitLocationCode
and juvPlaEndDate IS NULL
and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
@.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
@.JuvenileLNameBeginsWith + '%' END
Order By JuvFullNameWhat column(s) is indexed in Juvenile?|||Can you post the sp?
AMB
"Dazed and Confused" wrote:

> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
> Any pointers would be appreciated
> here's the script:
> Select
> J.JuvenileID,
> (J.JuvLName + ', ' + J.JuvFName + ' (' + convert(varchar(10), J.juvDOB, 1
)
> + ')' ) as JuvFullName,
> Convert(varchar(10), J.JuvDOB, 1) as JuvDOB
> From Juvenile J
> Inner Join Placement P on J.JuvenileID = P.JuvenileID
> Where juvPlaSite = @.SitLocationCode
> and juvPlaEndDate IS NULL
> and J.JuvLName LIKE CASE When (@.JuvenileLNameBeginsWith = '' OR
> @.JuvenileLNameBeginsWith = 'ALL') then J.JuvLName ELSE
> @.JuvenileLNameBeginsWith + '%' END
> Order By JuvFullName
>|||"Dazed and Confused" <Dazed and Confused@.discussions.microsoft.com> wrote in
message news:C09CDD00-02A2-4199-9A1B-8420771CDF43@.microsoft.com...
> Here's a poser (at least to me!)
> When I run a select statement in QA the statement uses available indexes
> (Index Scan) and is quite fast, but when I take the same script, place it
in
> a sproc, and execute the sproc, it uses a Table Scan rather than an Index
> S on the Juvenile table - killing performance.
Obviate parameter sniffing by assigning parameters to local variables and
using the local variables in the query.