about sorting

Hi all,
i use a full text for my db
however, when i write the SQL w/ order by and w/o order by
it's obviously time gap.
w/o using order by SQL, which return record within 5 seconds
w/ using order by SQL, which return record over 20 seconds
how can i elimiate the searching time by using order by SQL in fulltext
database ?
Thanks in advance!
Q,
Could you post the full output of SELECT @.@.version -- as this will provide
information about your environment. Could you also provide the total number
of rows in your FT-enabled table along with the expected number of rows to
be returned by your contains or freetext query?
Depending upon your answers, you may want to use CONTAINSTABLE or
FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
2000 rows or less and use an ORDER BY clause based upon RANK.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> i use a full text for my db
> however, when i write the SQL w/ order by and w/o order by
> it's obviously time gap.
> w/o using order by SQL, which return record within 5 seconds
> w/ using order by SQL, which return record over 20 seconds
> how can i elimiate the searching time by using order by SQL in fulltext
> database ?
> Thanks in advance!
>
|||"Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)"
actually, i have a 4 diff. full-text field, in one table which are (field_1,
field_2, field_3, field_4)
my query like this
select * from tblTest
where contains(field_1, '"my words"')
OR contains(field_2, '"my words"')
OR contains(field_3, '"my words"')
OR contains(field_4, '"my words"')
order by my_year desc, my_date desc
i MUST divided into 4 fields for searching, because sometimes, it only
involced field_1 for searching purpose!
"John Kane" <jt-kane@.comcast.net> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> Q,
> Could you post the full output of SELECT @.@.version -- as this will provide
> information about your environment. Could you also provide the total
> number
> of rows in your FT-enabled table along with the expected number of rows to
> be returned by your contains or freetext query?
> Depending upon your answers, you may want to use CONTAINSTABLE or
> FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
> 2000 rows or less and use an ORDER BY clause based upon RANK.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
|||Q,
First of all, thank you for providing the requested info!
You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
infosoft.dll as the OS-supplied wordbreaker.
What is causing the slow performance is not the ORDER BY clause, but the
multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
below query is reading the FT Catalog four times! Unfortunately, this is "by
design" for SQL Server 2000. As a workaround, you may want to try using one
FREETEXT query and using the "*" (asterisk) for all FT-enabled columns, this
should perform better, but may give you more results than CONTAINS as
FREETEXT can search "across columns", and is less precise in its results
than CONTAINS.
See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with CONTAINS
Clause Search Across Columns" at
http://support.microsoft.com/default...b;en-us;294809 for more info
as this behavior is also the default behavior for SQL Server 2000. You
should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank as
this parameter restricts the full "round trip" to just the Top N (some
specified number) by RANK.
FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
column syntax, for example:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
the above query is functionally equivalent to the following query:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)"
> actually, i have a 4 diff. full-text field, in one table which are
(field_1,
> field_2, field_3, field_4)
> my query like this
> select * from tblTest
> where contains(field_1, '"my words"')
> OR contains(field_2, '"my words"')
> OR contains(field_3, '"my words"')
> OR contains(field_4, '"my words"')
> order by my_year desc, my_date desc
> i MUST divided into 4 fields for searching, because sometimes, it only
> involced field_1 for searching purpose!
>
> "John Kane" <jt-kane@.comcast.net>
glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...[vbcol=seagreen]
provide[vbcol=seagreen]
to
>
|||Thanks John Kane alot!
"John Kane" <jt-kane@.comcast.net> glsD:OFskTDQmFHA.2860@.TK2MSFTNGP15.phx.g bl...
> Q,
> First of all, thank you for providing the requested info!
> You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
> infosoft.dll as the OS-supplied wordbreaker.
> What is causing the slow performance is not the ORDER BY clause, but the
> multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
> i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
> below query is reading the FT Catalog four times! Unfortunately, this is
> "by
> design" for SQL Server 2000. As a workaround, you may want to try using
> one
> FREETEXT query and using the "*" (asterisk) for all FT-enabled columns,
> this
> should perform better, but may give you more results than CONTAINS as
> FREETEXT can search "across columns", and is less precise in its results
> than CONTAINS.
> See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with
> CONTAINS
> Clause Search Across Columns" at
> http://support.microsoft.com/default...b;en-us;294809 for more
> info
> as this behavior is also the default behavior for SQL Server 2000. You
> should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank
> as
> this parameter restricts the full "round trip" to just the Top N (some
> specified number) by RANK.
> FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
> column syntax, for example:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
> the above query is functionally equivalent to the following query:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
> CONTAINS(FTSCol3, '"john" AND "kane"')
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> (field_1,
> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> provide
> to
>