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.
>

Q: Trigger - reference to fields in "inserted" ?

Hi all,

I have a ranking system where I wish to update the ranking every time a result is reported. Performance is no issue what-so-ever. More specifically, two players are to devide their points after each series of games to reflect the fraction of over-all games that each player have won.

I've written the trigger below, but Visual Web Developer 2005 Express (SQL Server 2005 Express) complains about the references to the 'inserted'-table.

I find it very difficult to transform the code below to something that looks like the examples found in documentation.

Could someone get me started in the right direction?

Thanks in advance,

Anders

create trigger result_insertonresult

after insert as

begin

declare@.won1as int

declare@.won2as int

declare@.oldRank1as float

declare@.oldRank2as float

declare@.oldranksumas float

select@.won1 =sum(wongames1)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.won2 =sum(wongames2)fromresultwhereplayer1 = inserted.player1andplayer2=inserted.player2

select@.oldrank1 = RankfromRankingInfowherememberid = inserted.playerid1

select@.oldrank2 = RankfromRankingInfowherememberid = inserted.playerid2

set@.oldranksum = @.oldrank1 + @.oldrank2

updaterankingInfosetRank = @.won1 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player1

updaterankingInfosetRank = @.won2 / ( @.won1+@.won2) * @.oldranksumwherememberid = inserted.player2

end

Hello Anders,

tha fact is theinserted table is but a table, so you need JOIN-ing to it as you would for any standard table...

Hope this helps. -LV