Full Time Database Consultants Needed

Jobs at MySQL AB: / Field Sales Engineer/Consultant

MySQL is rapidly growing and looking for high-caliber technical
engineers who will work to both support the field sales staff, as well
as working within our Professional Services group for onsite and
remote consulting opportunities. We offer competive salaries, a fun
and exciting work environment, five weeks of paid vacation, and the
opportunity to grow within a fast-paced, leading open source company!
Primary Responsibilities

* Consulting projects involving application development, database
driven web applications, database tuning and optimization, enterprise
integration.
* Provide field support to sales teams
* Migration from Oracle, MS SQL Server, Informix to MySQL
* Design, implement, and tune databases for data matching and
merging

Secondary Responsibilities

* Assistance with technical whitepapers
* Conduct performance and benchmarking analysis
* Support other consultants and other team members

Mandatory Qualifications

* Database expertise: deep skill and at least 5 years experience
with schema design (DDL), database programming, and database
performance tuning, especially for complex queries
* Deep understanding of MySQL, Oracle, MS SQL Server
* Experience with Oracle 8i/9i
* Language skills: C++, Java, PHP, Perl, Python
* BS or MS in Computer Science or equivalent experience
* Three years of application development experience
* Experience with JDBC and ODBC
* Team-player with good communication skills
* Can work independently with critical thinking
* Do-whatever-it-takes commitment

Desirable Qualifications

* Expertise with in-memory databases and other high-performance
alternatives
* Experience with other DBMS including Oracle RAC, Sybase,
Informix, PostgreSQL, Pervasive, Times Ten and Cach is a plus
* Experience with application servers such as WebLogic, WebSphere,
JBoss, Apache Tomcat
* Experience with setting up various replication schemes
* Experience producing shrink-wrapped software or software used at
dozens of installations
* Experience with data modeling
* Project management skills
* Development experience under Windows, Linux, Solaris, UNIX

Please fill in our Job Application Form or send your application to
jobs@.mysql.com, marked "JOB ID# 1001, Sales Engineer". You can speed
up the process by including your CV as plain text at the end of the
email message and not as an attachment.Philip (philip@.mysql.com) writes:
> Jobs at MySQL AB: / Field Sales Engineer/Consultant

I always wondered how come there are so many questions about MySQL in
the SQL Server groups. Now I know! Not even the MySQL folks can tell
them apart!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns944CC8FCDA5Yazorman@.127.0.0.1...
> Philip (philip@.mysql.com) writes:
> > Jobs at MySQL AB: / Field Sales Engineer/Consultant
> I always wondered how come there are so many questions about MySQL in
> the SQL Server groups. Now I know! Not even the MySQL folks can tell
> them apart!

Nah, they just know where to look for good talent. :-)

> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Full Text Searching

I am trying to run a full text serach on one field, a Varchar 2000.
say the field contains:
(before you break the seal of your new product box, please be careful to read all the instructions) ...for example
I search for keywords that may be in this field
Like:
product box
seal
instructions
and this row is included in the result set

but I would like to leave out words like all pronouns and 'a' and 'I' ...words that aren't going to matter to the search.

Does someone know where I can stgart in doing this full text searching?

Thanks,
EricWell I still haven't found much on this
Got the following articles
http://www.freevbcode.com/ShowCode.asp?ID=4224
(zip file is empty)
and http://www.microsoft.com/sql/evaluation/features/fulltext.asp (just says nothing really)

Do anyone know how this full text search works... an example perhaps?

Would be greatly appreciated.
Thanks,
Eric|||Can someone tell me please where this is wrong?
sSQL.Append("and (sr.description_of_problem = isnull(@.description, FREETEXT(sr.description_of_problem, @.description)) or sr.description_of_problem is null) ")|||look into"noise words" and"filters" in the full text search problem.

Full text search?

I seem to have a problem with how Full-text search works in SQL Server 2000.
Here is an example, I have the following two "Notes" field values in two
different records:
Notes for record 1: 'hello this is a test'
Notes for record 2: 'hello again'
When I use this select statement:
select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
'%hello%'
it returns both records.
When I use this select statement with CONTAIN keyword:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, 'hello')
it returns only 1 record, the first one.
When I try to add the wildcard in the selection as follows:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, '*hello*')
it also returns only 1 record, the first one.
Therefore, this seems to be a problem with how CONTAINS keyword
works. We have to keep the 'CONTAINS' keyword because that it is
how Full-Text search works in SQL. Is this correct?
The first thing I would do is to rebuild the full text indexes... They are
not (generally) kept up to date with inserts, etc... so the ft indexes could
be behind...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:Ohr$qDXMFHA.1308@.tk2msftngp13.phx.gbl...
>I seem to have a problem with how Full-text search works in SQL Server
>2000.
> Here is an example, I have the following two "Notes" field values in two
> different records:
> Notes for record 1: 'hello this is a test'
> Notes for record 2: 'hello again'
> --
> When I use this select statement:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
> '%hello%'
> it returns both records.
> --
> When I use this select statement with CONTAIN keyword:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, 'hello')
> it returns only 1 record, the first one.
> When I try to add the wildcard in the selection as follows:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, '*hello*')
> it also returns only 1 record, the first one.
> Therefore, this seems to be a problem with how CONTAINS keyword
> works. We have to keep the 'CONTAINS' keyword because that it is
> how Full-Text search works in SQL. Is this correct?
>

Full text search?

I seem to have a problem with how Full-text search works in SQL Server 2000.
Here is an example, I have the following two "Notes" field values in two
different records:
Notes for record 1: 'hello this is a test'
Notes for record 2: 'hello again'
When I use this select statement:
select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
'%hello%'
it returns both records.
--
When I use this select statement with CONTAIN keyword:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, 'hello')
it returns only 1 record, the first one.
---
When I try to add the wildcard in the selection as follows:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, '*hello*')
it also returns only 1 record, the first one.
---
Therefore, this seems to be a problem with how CONTAINS keyword
works. We have to keep the 'CONTAINS' keyword because that it is
how Full-Text search works in SQL. Is this correct?The first thing I would do is to rebuild the full text indexes... They are
not (generally) kept up to date with inserts, etc... so the ft indexes could
be behind...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:Ohr$qDXMFHA.1308@.tk2msftngp13.phx.gbl...
>I seem to have a problem with how Full-text search works in SQL Server
>2000.
> Here is an example, I have the following two "Notes" field values in two
> different records:
> Notes for record 1: 'hello this is a test'
> Notes for record 2: 'hello again'
> --
> When I use this select statement:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
> '%hello%'
> it returns both records.
> --
> When I use this select statement with CONTAIN keyword:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, 'hello')
> it returns only 1 record, the first one.
> ---
> When I try to add the wildcard in the selection as follows:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, '*hello*')
> it also returns only 1 record, the first one.
> ---
> Therefore, this seems to be a problem with how CONTAINS keyword
> works. We have to keep the 'CONTAINS' keyword because that it is
> how Full-Text search works in SQL. Is this correct?
>sql

Full text search?

I seem to have a problem with how Full-text search works in SQL Server 2000.
Here is an example, I have the following two "Notes" field values in two
different records:
Notes for record 1: 'hello this is a test'
Notes for record 2: 'hello again'
--
When I use this select statement:
select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
'%hello%'
it returns both records.
--
When I use this select statement with CONTAIN keyword:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, 'hello')
it returns only 1 record, the first one.
---
When I try to add the wildcard in the selection as follows:
select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
(b.*, '*hello*')
it also returns only 1 record, the first one.
---
Therefore, this seems to be a problem with how CONTAINS keyword
works. We have to keep the 'CONTAINS' keyword because that it is
how Full-Text search works in SQL. Is this correct?The first thing I would do is to rebuild the full text indexes... They are
not (generally) kept up to date with inserts, etc... so the ft indexes could
be behind...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:Ohr$qDXMFHA.1308@.tk2msftngp13.phx.gbl...
>I seem to have a problem with how Full-text search works in SQL Server
>2000.
> Here is an example, I have the following two "Notes" field values in two
> different records:
> Notes for record 1: 'hello this is a test'
> Notes for record 2: 'hello again'
> --
> When I use this select statement:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where notes like
> '%hello%'
> it returns both records.
> --
> When I use this select statement with CONTAIN keyword:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, 'hello')
> it returns only 1 record, the first one.
> ---
> When I try to add the wildcard in the selection as follows:
> select notes from DOCUMENTS_CATALOGPRODUCTS b where contains
> (b.*, '*hello*')
> it also returns only 1 record, the first one.
> ---
> Therefore, this seems to be a problem with how CONTAINS keyword
> works. We have to keep the 'CONTAINS' keyword because that it is
> how Full-Text search works in SQL. Is this correct?
>

Full Text Search Question.

Does anyone know if full text search has a problem with numbers?

I have a text field in a table that has been full text indexed. Some of the data in some of the fields start with numbers.
For Example
"123p45 the bla bla bla"
When I run a standard SELECT statement on LIKE '%p4%' it returns the record.
If I run a full text query using CONTAINS(fieldname,'"*p4*"') it returns nothing.
After much head banging I was able to get the CONTAINS to work when I used *123p4*
I figured since there are alpha characters after the p4 the * works however since there are no preceeding alpha characters before the p4 it fails.
I would like to be able to do a "full text" search for the "p4" and get the record above.

Any feedback would be appreciated..

Hello,

You can take a look here

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.databasejournal.com/features/mssql/article.php/1438211

http://www.sql-server-performance.com/full_text_search.asp

|||

These articles explain how to set up a full text index. I already have it set up.
I just want to know if there is any reason why when I search for the p4 listed above
using a CONTAINS select it returns nothing.

Full text search on TEXT field

I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.

I want to do full text search on this TEXT datatype field.
So I build catalog and run

"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.

but null value returns. however I know the table has 3 records.

Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.

I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot

YusufBesides,
I insert text in text datatype field and returs true values...

The problem occours when insert only MS Word or MS Excel etc files.

Full Text Search in text field for 'JN00001'

Hi
(I posted this on the full text newsgroup but it seems pretty quiet at the
mo and I was hoping someone on this list might know the answer).
I have a full text index on a table on a few columns including a text data
type field, and I'm searching for JN00001 which I know is in the text column
that is indexed by the full text index, however the result is not being
returned. Is this an issue related to noise words, or why would the result
not be being returned?
Thanks for any help
Cheers
MattHi Matt,
Can you post the query please.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:uW7jVOPeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi
> (I posted this on the full text newsgroup but it seems pretty quiet at the
> mo and I was hoping someone on this list might know the answer).
> I have a full text index on a table on a few columns including a text data
> type field, and I'm searching for JN00001 which I know is in the text
> column
> that is indexed by the full text index, however the result is not being
> returned. Is this an issue related to noise words, or why would the result
> not be being returned?
> Thanks for any help
> Cheers
> Matt
>
>|||Hi, thanks, the query is
SELECT rank, doc_id, pub_date, title, synopsis, notes
FROM doc JOIN CONTAINSTABLE(doc, *, 'JN00001') AS Result
ON doc.doc_id=Result.[Key]
But when I replace 'JN00001' with 'EHPs' then record 538 gets returned,
which has the text column in question with the 'JN00001' data in it and
other data, as follows:
RESEARCH SUMMARY
Title:
Emergency Help Points (EHP) at bus stations
Objective:
Passengers' awareness of EHP's, perceived uses and attitudes towards them
as a personal security aid.
Date:
February 2001
Methodology:
387 passengers were interviewed waiting for buses at Crystal Palace and
Addington Village bus stations between 9AM and 9PM. Interviews achieved are
broadly representative of London Buses users in terms of gender and age.
Key findings
<trimmed text>Three fifths of passengers at Addington station were aware
that there was an EHP at their station compared with only two fifths of
passengers at Crystal Palace station. Some passengers at Addington had
learned of the EHPs in the media
</trimmed text>.
Job number: JN00001
Any ideas?
Cheers
Matt
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ODYnsZPeGHA.3952@.TK2MSFTNGP04.phx.gbl...
> Hi Matt,
> Can you post the query please.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
> news:uW7jVOPeGHA.4900@.TK2MSFTNGP02.phx.gbl...
>|||Does it return when you do just this?
SELECT *
FROM CONTAINSTABLE(doc, *, 'JN00001') AS Result
I know this works because I have a process that takes the USENET groups and
puts them into a SQL Server 2000 database that has a full-text catalogue on
it and the following query brings back our posts!
select *
from mb_message_detail
where contains( body, 'JN00001' )
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:%239QaFuPeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks, the query is
> SELECT rank, doc_id, pub_date, title, synopsis, notes
> FROM doc JOIN CONTAINSTABLE(doc, *, 'JN00001') AS Result
> ON doc.doc_id=Result.[Key]
> But when I replace 'JN00001' with 'EHPs' then record 538 gets returned,
> which has the text column in question with the 'JN00001' data in it and
> other data, as follows:
> RESEARCH SUMMARY
> Title:
> Emergency Help Points (EHP) at bus stations
> Objective:
> Passengers' awareness of EHP's, perceived uses and attitudes towards them
> as a personal security aid.
> Date:
> February 2001
> Methodology:
> 387 passengers were interviewed waiting for buses at Crystal Palace and
> Addington Village bus stations between 9AM and 9PM. Interviews achieved
> are broadly representative of London Buses users in terms of gender and
> age.
>
> Key findings
> <trimmed text>Three fifths of passengers at Addington station were aware
> that there was an EHP at their station compared with only two fifths of
> passengers at Crystal Palace station. Some passengers at Addington had
> learned of the EHPs in the media
> </trimmed text>.
> Job number: JN00001
>
> Any ideas?
> Cheers
> Matt
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:ODYnsZPeGHA.3952@.TK2MSFTNGP04.phx.gbl...
>|||No...
Thanks
Matt
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uG$1CXQeGHA.3932@.TK2MSFTNGP05.phx.gbl...
> Does it return when you do just this?
> SELECT *
> FROM CONTAINSTABLE(doc, *, 'JN00001') AS Result
> I know this works because I have a process that takes the USENET groups
> and puts them into a SQL Server 2000 database that has a full-text
> catalogue on it and the following query brings back our posts!
> select *
> from mb_message_detail
> where contains( body, 'JN00001' )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
> news:%239QaFuPeGHA.380@.TK2MSFTNGP04.phx.gbl...
>|||Hi Matt,
Do a full rebuild on the catalogue, the row can't be indexed for some
reason, the noise file won't effect that being indexed.
Can you script out the CREATE TABLE and full-text catalogue creation for me.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:eQyl6pQeGHA.2188@.TK2MSFTNGP04.phx.gbl...
> No...
> Thanks
> Matt
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uG$1CXQeGHA.3932@.TK2MSFTNGP05.phx.gbl...
>

Full text Search and Numbers

Does Full text search have problems with numbers?

I have a text field in a table that has been full text indexed. Some of the data in some of the fields start with numbers.
For Example
"123p45 the bla bla bla"
When I run a standard SELECT statement on LIKE '%p4%' it returns the above record.
If I run a full text query using CONTAINS(fieldname,'"*p4*"') it returns nothing.
After much head banging I was able to get the CONTAINS to work when I used *123p4*
I figured since there are alpha characters after the p4 the second * works however since there are no preceeding alpha characters before the p4 it fails.
I would like to be able to do a "full text" search for the "p4" and get the record above.

Any feedback would be appreciated..

Unfortunately the word breaker will break the word at the beginning and the end of the numeric character. A workaround would be to use a function which translates the digits into words. The same would have to be done after specifying the search string.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The asterisk at the beginning of a CONTAINS searchword substitute not n-characters like it is in the LIKE search, ikt substitutes n-(broken) words, so if the parts before your *searchword* it not a valid word, the wordbreaker will not find it.

e.g. You saved in the database in one datawor the word TRACE in another the word SPEEDRACE, the query CONTAINS(SomeColumn, '"*Race*"') will find the Speedrace, but not the TRACE as the word will not be broken after the T of TRACE.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Full Text Indexing

I have made a table called BOB in BOB I have a field called skills.
I have created a full text index on this field.
In this field there is A+, N+, C and C++ along with the usual sentences e.g. "I have a diploma in A+" or " I worked for the C.N.A using C++ ..."

My problem:

In order to be able to search for "C" I've had to remove it from the noise word file. Unfortunately when I now search for "C" I also get C++ which I don't want, and visa versa.

When I search for A+ it returns an error... "your query contained ignored words."
Now I can't remove "a" from the noise words as the result of the query would be rediculous. i.e. I would recieve everything that has the letter "a" in it . Believe me I've tried :confused: .

Does anyone have any idea how I could overcome this rather difficult dilema.

With Many Thanks
Briansince you have alist of known skiles you can make acolum for every skile , make it a true / fals colum then put an index on thes colums
(its a bad index i know but it will suit you fine)

Full Text Index

helloin Full Text SearchAre there method when add record in Field for properties "Full Text Index " , update catalogs ?thankshelloplease help me !!!thanks|||

Yavari:

helloin Full Text SearchAre there method when add record in Field for properties "Full Text Index " , update catalogs ?thanks

I have no idea what you are asking. Could you rephrase your question(s?) differently?

Full Text - Index Population - HDD Access

Hi,
I'm indexing a single field on a table with 10 million rows in it, its
currently taking about 48 hours to build.
I've been looking into performance bottlenecks on the system and see that
this delay is being mainly caused by MSSearch writing massive amounts of
data to the FT Index location. Since the finished index is only about 400mb
I can only imagine that the population process is rewriting the data again
and again to the disk as it is updated.
Is this a fair assumption? If so is there anyway around it, maybe to tell
MSSearch to use ram until the population is build then write the index to
disk?
Thanks
Michael
No.
There is the resource usage setting which may increase performance during
the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
The more resources you can spend to improve the indexing process the better,
normally you get the best bang for your buck by investing in the disk
subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
01 is not considered to be a good choice as it does not offer the the
protection 10 does.
Also your database should be raid 5 depending on the level of write activity
on it.
What type of content are you indexing. You will get best performance by
indexing text content as opposed to indexing documents in their native
format (doc, xls, pdf, etc).
Please review the full text white paper on SQL FTS performance at:
http://support.microsoft.com/support...er.asp?GSSNB=1
Save this document as a zip and extract it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Mike
What hardware is it running on?
Is the Full-Text Index on the same physical drive/lun as the database? It
should be possible to increase IO throughput by placing the Full-Text Index
on its own drive.
Are you using SCSI disks? If so are you using the faster 15k RPM disks as
apposed to 10k RPM disks.
How much RAM does the machine have?
How many processors and what spec are they?
Is SQL Server configured to leave some RAM free for the Operating System and
other Applications, i.e. if you have 2GB RAM is SQL configured to use max
1.5GB?
You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
Search is much faster at indexing.
http://msdn.microsoft.com/library/en...asp?frame=true
Rich
"Hilary Cotter" wrote:

> No.
> There is the resource usage setting which may increase performance during
> the indexing process (sp_fulltext_service 'catalogname','resource_usgae',5)
> The more resources you can spend to improve the indexing process the better,
> normally you get the best bang for your buck by investing in the disk
> subsystem. For instance placing your catalogs on a RAID 10, 1, or 01 array.
> 01 is not considered to be a good choice as it does not offer the the
> protection 10 does.
> Also your database should be raid 5 depending on the level of write activity
> on it.
> What type of content are you indexing. You will get best performance by
> indexing text content as opposed to indexing documents in their native
> format (doc, xls, pdf, etc).
> Please review the full text white paper on SQL FTS performance at:
> http://support.microsoft.com/support...er.asp?GSSNB=1
> Save this document as a zip and extract it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
> news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> 400mb
>
>
|||Mike,
Yes, it is a fair assumption. You should review the SQL Server 2000 BOL
title "Full-text Search Recommendations" as well as the FTS Deployment white
paper. Are there workarounds? Yes. You can ensure that the disk drive that
your FT Catalog resides is separate from your database files as well as very
fast (>10,000 RPMs) and configured as RAID0 or RAID10. The Full Text
Indexing process is primarily I/O bound, but can also consume much memory
and CPU usage from time-to-time during Shadow Merges and at the end of the
process due to Master Merge.
Regards,
John
"Mike Davies" <michael.davies@.synsoft.co.uk> wrote in message
news:ezLfihZmEHA.3824@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm indexing a single field on a table with 10 million rows in it, its
> currently taking about 48 hours to build.
> I've been looking into performance bottlenecks on the system and see that
> this delay is being mainly caused by MSSearch writing massive amounts of
> data to the FT Index location. Since the finished index is only about
400mb
> I can only imagine that the population process is rewriting the data again
> and again to the disk as it is updated.
> Is this a fair assumption? If so is there anyway around it, maybe to tell
> MSSearch to use ram until the population is build then write the index to
> disk?
> Thanks
> Michael
>
|||Hi Rich,
It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
currently has access to all system RAM but is sat at approx 1.7Gb during the
indexing process.
Both the FullText index and the SQL database are on separate physical SATA
drives, however because of an apparent incompatibility issue with Windows
and running the two SATA drives on their own bus they are both running
through the standard IDE bus, I imagine this is not doing the performance
any favours.
Thanks for the 2005 tip, I'll give that a try.
Can you offer any other suggestions to the setup?
Thanks
Mike
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Mike
> What hardware is it running on?
> Is the Full-Text Index on the same physical drive/lun as the database? It
> should be possible to increase IO throughput by placing the Full-Text
Index
> on its own drive.
> Are you using SCSI disks? If so are you using the faster 15k RPM disks as
> apposed to 10k RPM disks.
> How much RAM does the machine have?
> How many processors and what spec are they?
> Is SQL Server configured to leave some RAM free for the Operating System
and
> other Applications, i.e. if you have 2GB RAM is SQL configured to use max
> 1.5GB?
> You might want to try SQL Server 2005 Beta 2. I understand that Full-Text
> Search is much faster at indexing.
>
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
> Rich
>
> "Hilary Cotter" wrote:
during[vbcol=seagreen]
'catalogname','resource_usgae',5)[vbcol=seagreen]
better,[vbcol=seagreen]
array.[vbcol=seagreen]
activity[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
that[vbcol=seagreen]
of[vbcol=seagreen]
again[vbcol=seagreen]
tell[vbcol=seagreen]
to[vbcol=seagreen]
|||Mike
Have you implemented the /3GB switch in the OS boot.ini? If not by default
2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
aside for apps and 1GB for OS. This probably explains why SQL Server is only
using 1.7GB RAM. It would probably be using more if you had the /3GB switch
set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
apps including MSSearch (I think), file system caching, etc.
Even if you don't have the budget for a 2 proc SQL license I would always
recommend purchasing a second physical processor and configuring SQL Server
to only use one of the processors. This leaves the second processor free for
other applications, OS, remote administration.
You might want to try the manufacturers web-site regarding BIOS / firmware
updates for your SATA / IDE issue.
Rich
"Mike Davies" wrote:

> Hi Rich,
> It's running on a single P4 Prescott at 3Ghz with 4GB of RAM. SQL Server
> currently has access to all system RAM but is sat at approx 1.7Gb during the
> indexing process.
> Both the FullText index and the SQL database are on separate physical SATA
> drives, however because of an apparent incompatibility issue with Windows
> and running the two SATA drives on their own bus they are both running
> through the standard IDE bus, I imagine this is not doing the performance
> any favours.
> Thanks for the 2005 tip, I'll give that a try.
> Can you offer any other suggestions to the setup?
> Thanks
> Mike
>
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:692389A4-ED05-42C7-B645-982E7E9CE989@.microsoft.com...
> Index
> and
> http://msdn.microsoft.com/library/en...asp?frame=true
> during
> 'catalogname','resource_usgae',5)
> better,
> array.
> activity
> http://support.microsoft.com/support...er.asp?GSSNB=1
> that
> of
> again
> tell
> to
>
>
|||Mike,
Rich, while the /3GB switch can help SQL Server 2000, it has no affect on
the "Microsoft Search" (mssearch.exe) FT Search engine as it can only use up
to a max of 512MB of physical RAM. However, you must set the resource_usage
value to 5 (dedicated) via sp_fulltext_service 'resource_usage', 5 and you
must have at least 512Mb of RAM reserved for the MSSearch service that is
NOT used by the OS or by SQL Server.
If you can purchase an additional CPU, you can set the MSSearch service's to
use one CPU and then use sp_configure to set the affinity of SQL Server to
that cpu to avoid cpu contention during heavy MSSearch CPU usage via the
following method:
You can launch the Tskmgr.exe from the AT command on the multi-proc server
where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers.
Regards,
John
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for[vbcol=seagreen]
> other applications, OS, remote administration.
> You might want to try the manufacturers web-site regarding BIOS / firmware
> updates for your SATA / IDE issue.
> Rich
>
> "Mike Davies" wrote:
Server[vbcol=seagreen]
the[vbcol=seagreen]
SATA[vbcol=seagreen]
Windows[vbcol=seagreen]
performance[vbcol=seagreen]
It[vbcol=seagreen]
as[vbcol=seagreen]
System[vbcol=seagreen]
max[vbcol=seagreen]
Full-Text[vbcol=seagreen]
http://msdn.microsoft.com/library/en...asp?frame=true[vbcol=seagreen]
disk[vbcol=seagreen]
the[vbcol=seagreen]
by[vbcol=seagreen]
native[vbcol=seagreen]
http://support.microsoft.com/support...er.asp?GSSNB=1[vbcol=seagreen]
its[vbcol=seagreen]
see[vbcol=seagreen]
amounts[vbcol=seagreen]
about[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
index[vbcol=seagreen]
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
>
<SNIP>
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
> Server
> to only use one of the processors. This leaves the second processor free
> for
> other applications, OS, remote administration.
>
</SNIP>
Please be careful about that because, in general, you have to purchase a
processor license for every processor that the OS can see, whether that
processor is configured to run SQL Server or not. For more information,
please refer to the SQL Server 2000 Licensing FAQ web page at
http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
Licensing is reproduced below for your benefit.
Q. Do I have to license all of the processors in a server?
A. You only have to acquire licenses for processors that are
accessible to any copy of the operating system upon which SQL Server 2000 is
installed.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
begin 666 ts.gif
J1TE&.#EA`0`!`( ``````/___R'Y! $`````+ `````!``$```(!1 `[
`
end
|||"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> Mike
> Have you implemented the /3GB switch in the OS boot.ini? If not by default
> 2GB is set aside for apps and 2GB for OS. By using this switch 3GB is set
> aside for apps and 1GB for OS. This probably explains why SQL Server is
only
> using 1.7GB RAM. It would probably be using more if you had the /3GB
switch
> set. Try setting /3GB switch, instruct SQL Server to use Physical RAM, and
> set max memory for SQL Server to be 2.5GB. This will leave 0.5GB for other
> apps including MSSearch (I think), file system caching, etc.
> Even if you don't have the budget for a 2 proc SQL license I would always
> recommend purchasing a second physical processor and configuring SQL
Server
> to only use one of the processors. This leaves the second processor free
for
> other applications, OS, remote administration.
Unless thigns have changed, last I knew, MS didn't allow that.
|||Stephen
Thanks for clarifying that.
Obviously there is a difference between what the product is capable of and
what the licensing folk think the product is or should be capable of.
In this instance I feel the Product Team got it right. In the real world
people often have to run more than one application on a server and should not
be forced to buy additional licenses.
With the move in the industry to consolidate this could a lot of unnecessary
expense.
Lets hope this license issue is addressed in the next release of SQL Server
2005.
Rich
"Stephen Dybing [MSFT]" wrote:

> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:2AEB4663-795F-48F6-BA68-DEAA0D1C4BD8@.microsoft.com...
> <SNIP>
> </SNIP>
> Please be careful about that because, in general, you have to purchase a
> processor license for every processor that the OS can see, whether that
> processor is configured to run SQL Server or not. For more information,
> please refer to the SQL Server 2000 Licensing FAQ web page at
> http://www.microsoft.com/sql/howtobuy/faq.asp. The third question under
> Licensing is reproduced below for your benefit.
> Q. Do I have to license all of the processors in a server?
> A. You only have to acquire licenses for processors that are
> accessible to any copy of the operating system upon which SQL Server 2000 is
> installed.
>
>
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights

FTS Performance

WE have sql server 2000 with a table around 10 text field and one full-text field. over 1million rows.

How can we realize a 5 seconds or less full text query?
resuts should be order by a date field.

Note: full text field is in Chinese.

if in solution of asp: by what sentence.
if in solution of isapi, by what dev tool?nobody?

FTS CONTAINS not working

Hi There,

I have table called tblFileInfo, full text indexing is done on the field KEYWORDS
FTS is enabled on the server. I have done full population.
If I use Like 21 results are returned, but using Contains is not returning any results what may be the cause.

0 results returned
Select * From tblFileInfo Where Contains(Keywords,'abc')

21 results returned
Select * From tblFileInfo Where Keywords like '%abc%'

Regards
Rohan

Please note that those two queries are not same.

Contains(keywords, 'abc') will return rows has exact word 'abc'.

like '%abc%' will return any row which contains substring 'abc', for example, rows contains "fooabcbar", "foo abcbar", etc.

So depend on the data in your table, you may get match by like query but not contains query.

thanks,

Jingwei

|||

Hi Jingwei,

in the field KEYWORDS there are values as ABC which CONTAINS may return normally, not 21 but atleast records having abc as a whole word should be returned but it not happening, CONTAINS is not working at all.

Pls Help

Regards
Rohan

|||

What is itemcount on the full-text index? You can use objectproperty(<table_id>, 'TableFulltextItemCount') to get it. "abc" is not in noise word list and contains should be able to find it. Please check SQL<dbid>+<catalogid>.LOG file under sql LOG directory to see if there is any suspecials error.

If you can attach a repro script, I can try out on my machine.

thanks

|||Hi Rohan,

I'm currently marking one of Jingwei's replies as a correct answer until you provide more information for us to reproduce the problem. You can unmark the question as being a correct answer and this will bring it to our attention again.

Alternatively, if you found a resolution to your problem, please post it here as it will help others troublehsooting similar problems.

Regards,
Boris.