Advice Needed On Very Large Full Text Indexes

I'm currently building a product that will need to index and search roughly
500 million documents (perhaps more). Currently I have about 60 million
documents in the index (table size about 100GB and index size about 35GB)
Performance is (obviously) getting worse and I doubt my current architecture
will support the needed growth and still provide the required speed.
Documents are added 24x7 at the rate of 100's per minute (roughly 250,000
new ones each day). Plus, due to the search requirements, I had to clear
the noise file. I've been thinking of doing table partitioning (using
2005), creating indexed views and then using multiple full-text indexes to
query the data (however, of course, I will not be able to rank effectively
then).
So, I'm curious if any one else has this type of volume and has come up with
a solid solution. Appeciate any ideas.
I should also mention that I'm looking for short term consulting help on
this, as well as a full-time position - so while I'm grateful for free
suggestions from this board, should anyone be looking for work, please
contact me as well (company located in New Jersey).
Thanks!
Joel
Partitioning works, but you need to have well defined partitions which the
data and the queries will align itself with.
For example if you are querying by a search phrase and your
buckets/partitions are by date partitioning won't help you. If you are
partitioning by date you will get some advantages if you have time
dependencies. For example in Google, if you query on your name
http://www.google.com/search?sourcei...=Joel+Macaluso
you'll notice 70,100 hits. Then as you move to other pages you'll notice
that the number goes down to 69,900. What has happened is the first hit is
on one server which has mainly new stuff. The subsequent queries hit the
bigger archive which represents the totality. The time partition works for
them even though most of their queries are have no real time dependency, ie
web pages from 2001.
You might want to analyze your queries looking for colocants - words which
are search for as a unit - Bacon and Eggs, Sonny and Cher, George Bush.
Likewise you might choose not to index hapax legomenon, dis legomenon, tris
legomenon or even tetrakis legomenon. You also might want to look at words
over a certain length and not index them.
One other point that you aren't answering is while I know about your
indexing requirements - what are you querying requierments like? IE How may
queries per day/min/sec.
I am facing some of the same problems where I work now. I'd be interested in
following up with you on this one. I'm in NJ as well.
Hilary Cotter
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
"Joel Macaluso" <jmacaluso@.digitalgrit.com> wrote in message
news:%23OjwJkXGGHA.1088@.tk2msftngp13.phx.gbl...
> I'm currently building a product that will need to index and search
> roughly 500 million documents (perhaps more). Currently I have about 60
> million documents in the index (table size about 100GB and index size
> about 35GB) Performance is (obviously) getting worse and I doubt my
> current architecture will support the needed growth and still provide the
> required speed.
> Documents are added 24x7 at the rate of 100's per minute (roughly 250,000
> new ones each day). Plus, due to the search requirements, I had to clear
> the noise file. I've been thinking of doing table partitioning (using
> 2005), creating indexed views and then using multiple full-text indexes to
> query the data (however, of course, I will not be able to rank effectively
> then).
> So, I'm curious if any one else has this type of volume and has come up
> with a solid solution. Appeciate any ideas.
> I should also mention that I'm looking for short term consulting help on
> this, as well as a full-time position - so while I'm grateful for free
> suggestions from this board, should anyone be looking for work, please
> contact me as well (company located in New Jersey).
> Thanks!
> Joel
>

Advantages/Disadvantages of TEXT Column type in SQL 2005

Hello,
I know this question has been asked before, but I'd like to get the
opinions from others before I continue with my database. I am creating
a Database to track calls for a Tech Support dept. There are some
tables like tb_Call, tb_Ticket where I need to allow a user to enter a
long description if necessary. I am thinking of using the Text Field
type with a leght of 16. My question is what are the
advantages/divantages with this as opposed to using any other data
type. Any insight or recommendations you can provide will be greatly
appreciated!
Thanks in advance!The TEXT datatype is deprecated in SQL Server 2005. So do not use it for
any new work. Instead, use VARCHAR(MAX) -- that datatype has the same
maximum storage capacity as TEXT, and is a lot more flexible in terms of
being able to use string functions.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ed_p" <edp@.nomail.com> wrote in message
news:utvCvDY6FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I know this question has been asked before, but I'd like to get the
> opinions from others before I continue with my database. I am creating a
> Database to track calls for a Tech Support dept. There are some tables
> like tb_Call, tb_Ticket where I need to allow a user to enter a long
> description if necessary. I am thinking of using the Text Field type with
> a leght of 16. My question is what are the advantages/divantages with
> this as opposed to using any other data type. Any insight or
> recommendations you can provide will be greatly appreciated!
> Thanks in advance!|||
Adam Machanic wrote:

>The TEXT datatype is deprecated in SQL Server 2005. So do not use it for
>any new work. Instead, use VARCHAR(MAX) -- that datatype has the same
>maximum storage capacity as TEXT, and is a lot more flexible in terms of
>being able to use string functions.
>
>
It's worth noting that while for [text] the default is
to store data out-of-row, for the (max) types, the
default is to store data in-row. In some cases, this
can make a difference, and it may be useful to set the
table option so that the (max) type behaves like [text]
did:
exec sp_tableoption N'MyTable', 'large value types out of row', 'ON'
Steve Kass
Drew University|||In addition to the other posts, if you have several such columns, where each
will fit inside the
regular varchar or nvarchar limit: In 2005, you have page overflow, meaning
you can have for
instance two varchar(5000) each containing 5000 characters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ed_p" <edp@.nomail.com> wrote in message news:utvCvDY6FHA.1248@.TK2MSFTNGP14.phx.gbl...[colo
r=darkred]
> Hello,
> I know this question has been asked before, but I'd like to get the opinio
ns from others before I
> continue with my database. I am creating a Database to track calls for a
Tech Support dept.
> There are some tables like tb_Call, tb_Ticket where I need to allow a user
to enter a long
> description if necessary. I am thinking of using the Text Field type with
a leght of 16. My
> question is what are the advantages/divantages with this as opposed to
using any other data
> type. Any insight or recommendations you can provide will be greatly appr
eciated!
> Thanks in advance![/color]

Advantages and disadvantages

Code: ( text )

1. [list]what is the advantages and disadvantages of Ms SQL server and java servletts front-end on the clien end.[list]what is the advantages and disadvantages of Ms Access on the server, connected via JDBC and java on the client end.what is the advantages and disadvantages of HTML on the client end,coupled with SQL server and ODBL on the server end.

You'll want to check the version of the JDBC that you are using and how it was implimented.

My first experience with JDBC (several years ago) left much to be desired. It was simply a Java layer on top of ODBC and was God Awful slow and many ODBC features were not implimented or had some sort of custom implimentation. Simple things like execute calls could not be done in a standard query statement, they had to use a separate exec entry point.

Lately though my experience is MUCH better. The current implimentation I use does not use the ODBC layer and instead goes directly to the SQL dll -- MUCH, MUCH, MUCH faster and as far as I can tell, fully implimented without custom entry points. Oh, by the way, did I tell you it's MUCH faster!

My experience is with CFMX and the JDBC that comes with it. I can round up the version information if you need it but if you are using CFMX, then as long as you are on the latest and greatest, I don't see any disadvantages.

Advantage of Temp Table

When I import data I first import it from a text file into a table of
it's own, then using some logic insert some of the records into a
permanent table.

I am considering having the table that the data from the text file is
placed in being there all the time and just clearing it out after I do
the import, or creating it and after using it then drop it, or using a
temporary table.

What are the advantages of a temporary table as opposed to creating a
regular table and dropping it after use?Not much difference between a temp table and permenant table. Security,
automatic distruction, and one or the other may faster depending on the
disk/file layout. I would ask why not just push it directly into the
destination? Also if the text is coming from another SQL available source,
why not skip both the text file and the temporary table?

It also may be faster depending on need for availability, space, and
indexing to create a new table and do select into based on a union all of
the two tables. Then rename the table.

<shumaker@.cs.fsu.edu> wrote in message
news:1114111315.338927.231880@.g14g2000cwa.googlegr oups.com...
> When I import data I first import it from a text file into a table of
> it's own, then using some logic insert some of the records into a
> permanent table.
> I am considering having the table that the data from the text file is
> placed in being there all the time and just clearing it out after I do
> the import, or creating it and after using it then drop it, or using a
> temporary table.
> What are the advantages of a temporary table as opposed to creating a
> regular table and dropping it after use?|||Thanks.

"I would ask why not just push it directly into the
destination?"

Because I have to do some logic based on what is not in the imported
data. If a certain record exists in my table, but not in the data
being imported, then I need to modify some flags in the existing
record. So I import into an empty table, and do updates "if not in"
imported table then update my existing table.|||>> What are the advantages of a temporary table as opposed to creating
a regular table and dropping it after use? <<

A regular table will port, can have constraints, DRI actions, etc. But
do not drop it after use; just clean it out before and after you load
the data for scrubbing.

Adobe iFilter 5 & SQL Server 2000 full text indexing of PDFs?

hi all! i'm new and hoping you might help me - have searched E V E R Y W H E R E for this answer!!

--my ASP program is running on IIS box (call it "Lucy")

--my ASP program connects to SQL Server 2000 db on another box (call it "Linus") consisting of .doc, .xls, .PDF and the like

--all's well EXCEPT when PDFs are returned! sometimes browser gives blank screen instead of PDF <?> i figure iFilter will "cure" this.

--question:
WHICH SERVER should iFilter be installed on? Lucy (where IIS is running & ASP program is) or Linus (where SQL Server does its full text indexing)?

thanks in advance for any & all help!!
geekgirlYou will have to install IFilter on the box that runs index server. I am not sure why you should be worried about SQL server when you searching documents from a folder.

Are ur documents stored in a database?|||Originally posted by vibhu
You will have to install IFilter on the box that runs index server. I am not sure why you should be worried about SQL server when you searching documents from a folder.

Are ur documents stored in a database?

sorry, YES docs are stored directly in the SQL db. iFilter says to install it "where the index server is" -- but i don't know if that means the *** IIS Index Server*** or the ***SQL Server**** which is doing the full text indexing. <??>

AdjustTokenPrivileges () failed

I have an SSIS package that parses a text file into 3 smaller text files and then takes the data and puts it into tables. The package runs fine up to the point where it needs to insert the data. I turned logging on but no errors are generated. But I do get a file named SQLDUMPER_ERRORLOG.log that is generated with the info below. Any ideas of where to look?

11/16/06 13:31:38, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:

4 supplied

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID =

1844

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags

= 0x0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr =

0x0100C5D0

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr =

0x00000000

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName =

<NULL>

11/16/06 13:31:38, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump

completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0033.mdmp

11/16/06 13:31:43, ACTION, DtsDebugHost.exe, Watson Invoke: No

11/16/06 13:31:43, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters:

4 supplied

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID =

1844

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags

= 0x0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr =

0x0100C5D0

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr =

0x00000000

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName =

<NULL>

11/16/06 13:31:43, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

11/16/06 13:31:44, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpWriteDump

() Failed 0x80070005 - Access is denied.

11/16/06 13:31:44, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Watson Invoke: No

Do you have anything in your Windows Event log that might indicate a better error?

Also, how are you authenticating to the databases? SQL Server users? Active Directory?

I'm just blurting out stuff to check, I guess.

Phil|||Also, you didn't need to start this thread when you replied to the other one. We don't need two threads about the same topic. Having more than one thread only makes it cumbersome to help you.|||

Nothing in the Application Log. I've tried authentication using windows auth and sql auth both result in the same error.

I was using an OLE DB Destination and I changed it to a SQL Server Destination which got rid of the error above but presented another. I still believe OLE DB Destination should work though.

I should add that I can run the package from my machine without a problem. But whenever I try to run it from the server It will be scheduled on is when I have the problem