From: = &nb= sp; = Amit Ghosh

Sent: = &nb= sp; = Thursday, March 01, 2007= 10:38 AM

To: = &nb= sp; = Noah Coad

Cc: = &nb= sp; = Brian Harry

Subject: &= nbsp; &nbs= p; &= nbsp; RE: The Cost of "Contains"

There is a significant c= ost difference between using Contains for a String field (like Title) and a long text field (like Description or History).

When you do a query whic= h has “Title Contains ‘watson’” , it does a SQL string comparison for “Title like ‘%watson%’“ (% being the= SQL wildcard) which means that even though an index exists on the Title field, = it cannot make the best possible use of the index, because of the wildcard at = the beginning of the search string. This query is therefore expensive.

The Description, History= and any other Long text fields that you create are always Full Text indexed by defa= ult. That allows for cheap and fast queries for tokens across large blocks of te= xt.

The one downside of Full= Text indexing is that the index can be out of date for a few minutes, which means that it is not a great mechanism for “exact” queries (say where= you are trying to make a go-no go decision based on the result of a query for a= ll Active Beta1 bugs). Also, FT search has some interesting semantics which ne= ed to be understood (you cannot search for “noise” words, the vari= ants of the word will not be picked up, etc)

If you want to compare F= T vs non-FT searches, connect to the Windows OS Bugs Product Studio DB and= do these 2 queries:

1.&n= bsp; Title Contains Crash -> This uses String search, same as TFS, Title field is indexed

2.&n= bsp; Title_FastSear= ch Contains Crash -> This uses a FT index on the Title field

One a general basis, the= second search takes about 1/3 the time of the other, and the result sets returned = are also different (the first will include occurrences of crash, cras= hes, crashing, etc, the second will only include crash). We do not have this feature in TFS, I implemented this as a quick fix for Windows= .

<= /p>

Let me know if you have = more questions.

Thanks,
Amit

From: Brian Harry <= br> Sent: Thursday, March 01, 2007 9:12 AM
To: Noah Coad
Cc: Amit Ghosh
Subject: RE: The Cost of "Contains"

Amit can talk to you.&nb= sp; We use full text indexing so contains is reasonably cheap.

Brian<= /p>

From: Noah Coad
Sent: Thursday, March 01, 2007 7:49 AM
To: Brian Harry
Subject: The Cost of "Contains"

Hey Brian,

Who could help me understand the cost of using the “Contains” operator in work item queries?

Specifically,

What is the cost comparison between using a Contains in Title vs Description vs History vs not using Contains but another operator? Is there someone I could chat with about it?

Thanks

Noah Coad | = blog= | = = web<= /span> | = = tdev<= span style=3D'font-size:7.5pt;font-family:"Verdana","sans-serif";color:silver'= > | = Know-a-Code
Microsoft Visual Studio Team Developer Program Manger