Performance Question

Hi All
I have a database of students which contains a table StoredCvs. This
contains all of their CVs (Resume's) and has a full text index stored in a
"StoredFilesCatalog". It is unlikely that this table will grow to more than
1 million rows although it is possible.
I now want to start storing files (for searching) for other types of records
in our system i.e. company files, project files, other candidate files etc.
etc. I am now faced with a few choices and wandered which would be the best
one. I have decided not to create another catalog at this stage as I feel
this could be done later if performance is really bad. However, I was
wandering what the difference would be between: a) Creating another table
(StoredFiles), which uses the "StoredFilesCatalog", but would have a
seperate fulltextindex. b) Add a FileTypeID column to my StoredCVs table
(would rename this to StoredFiles) and store all files in the one table
(with one fulltextindex).
The main function of the system is to search Candidate Cvs (Resume's) so I
was wandering if there is any performance advantage of having 2 tables or if
the searches would be the same as they both use the same
"StoredFilesCatalog" anyway.
Hope this makes sense and really appreciate any advice.
Cheers
Joe
You will get better performance with two catalogs - one for each table. You
could add a separate column for FileType but this could be problematic if
you are using top_n_by_Rank
For instance suppose you do a query like this
Select * from StoredCvs join containstable(StoredCvs,*,'microsoft',200) as T
on T.[Key]=StoredCvs.PK
where filetype='resume'
order by rank desc
If the first 200 hits returned were all not of the fileType resume, you
would get no hits, even though there could be matches that might not occur
in the first 200 hits.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:u619ySFBFHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I have a database of students which contains a table StoredCvs. This
> contains all of their CVs (Resume's) and has a full text index stored in a
> "StoredFilesCatalog". It is unlikely that this table will grow to more
than
> 1 million rows although it is possible.
> I now want to start storing files (for searching) for other types of
records
> in our system i.e. company files, project files, other candidate files
etc.
> etc. I am now faced with a few choices and wandered which would be the
best
> one. I have decided not to create another catalog at this stage as I feel
> this could be done later if performance is really bad. However, I was
> wandering what the difference would be between: a) Creating another table
> (StoredFiles), which uses the "StoredFilesCatalog", but would have a
> seperate fulltextindex. b) Add a FileTypeID column to my StoredCVs table
> (would rename this to StoredFiles) and store all files in the one table
> (with one fulltextindex).
> The main function of the system is to search Candidate Cvs (Resume's) so I
> was wandering if there is any performance advantage of having 2 tables or
if
> the searches would be the same as they both use the same
> "StoredFilesCatalog" anyway.
> Hope this makes sense and really appreciate any advice.
> Cheers
> Joe
>
|||Joe,
So, I can be sure of your environment, could you also post the full output
of -- SELECT @.@.version -- as this is most helpful in understanding your
environment. As I understand it you have one table: "StoredCvs with <1
million rows and one FT Catalog:StoredFilesCatalog", and you've decided not
to create another FT Catalog. Note, you can only have one FT Catalog defined
per table, but that one FT Catalog can support multiple column per table as
well as multiple tables.
Your decision is between the two option (a or b) below. Correct?
a) Creating another table (StoredFiles), which uses the
"StoredFilesCatalog", but would have a separate fulltextindex
b) Add a FileTypeID column to my StoredCVs table (would rename this to
StoredFiles) and store all files in the one table (with one fulltextindex).
If I have your environment correct, I'd recommend option a - create another
table (StoredFiles). Primarily because, SQL Server 2000 FT Catalogs start to
have performance issues with SQL Server 2000 tables at approx. 1 million
rows (still functional, but just need performance tuning) and adding
addition file types and larger files to your existing table (StoredCvs) will
cause it to grow above the 1 million row threshold. See SQL Server 2000 BOL
title "Full-text Search Recommendations" for more information on performance
tuning FT Catalogs on tables with more than 1 million rows.
Another issue/question that you did not mention is whether or not these two
tables will be often (always, sometimes, never) joined together in common
queries or in common FTS queries. If they are seldom or never joined in
frequently used queries, then it makes more sense for their to be separate
tables.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
news:u619ySFBFHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi All
> I have a database of students which contains a table StoredCvs. This
> contains all of their CVs (Resume's) and has a full text index stored in a
> "StoredFilesCatalog". It is unlikely that this table will grow to more
than
> 1 million rows although it is possible.
> I now want to start storing files (for searching) for other types of
records
> in our system i.e. company files, project files, other candidate files
etc.
> etc. I am now faced with a few choices and wandered which would be the
best
> one. I have decided not to create another catalog at this stage as I feel
> this could be done later if performance is really bad. However, I was
> wandering what the difference would be between: a) Creating another table
> (StoredFiles), which uses the "StoredFilesCatalog", but would have a
> seperate fulltextindex. b) Add a FileTypeID column to my StoredCVs table
> (would rename this to StoredFiles) and store all files in the one table
> (with one fulltextindex).
> The main function of the system is to search Candidate Cvs (Resume's) so I
> was wandering if there is any performance advantage of having 2 tables or
if
> the searches would be the same as they both use the same
> "StoredFilesCatalog" anyway.
> Hope this makes sense and really appreciate any advice.
> Cheers
> Joe
>
|||Good Point!
I was thinking along the 2 table line anyway so thanks for your help.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJX5EQIBFHA.1392@.TK2MSFTNGP14.phx.gbl...
> You will get better performance with two catalogs - one for each table.
You
> could add a separate column for FileType but this could be problematic if
> you are using top_n_by_Rank
> For instance suppose you do a query like this
> Select * from StoredCvs join containstable(StoredCvs,*,'microsoft',200) as
T[vbcol=seagreen]
> on T.[Key]=StoredCvs.PK
> where filetype='resume'
> order by rank desc
> If the first 200 hits returned were all not of the fileType resume, you
> would get no hits, even though there could be matches that might not occur
> in the first 200 hits.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Joe Zammit" <zammit_joe@.hotmail.com> wrote in message
> news:u619ySFBFHA.824@.TK2MSFTNGP11.phx.gbl...
a[vbcol=seagreen]
> than
> records
> etc.
> best
feel[vbcol=seagreen]
table[vbcol=seagreen]
I[vbcol=seagreen]
or
> if
>
sql

Performance problems ADODB connection

Hi,
My programm (24/7 - VB6) is connected to a MSSQL Database with only one
table where data is stored with a time_stamp field as index. Automatically
the software updates each 2 minutes the table and with the same frequency
querys and display data for the last 3 days.
I have also one function where the user can select the amount of the
displayed data. When the software is working for a few ws in automatic
mode (allways 3 day - data displaying) and the a user start a request to
display all the datas in the table, at the first time the query is failing
with timeout and at the second request is returning the requested data. I
use the ADODB Connection object with client side cursor which is used in a
ADODB.Recordset Open method to return the data.
Has anybody a ideea why is the first time query failing and /or how to
prevent this ?
Thank you !Hi
It appears to be contention problem.Try using lower level locks and if
needed you may use NOLOCK HINT with the select query in backend if possible
.
Regards
R.D
"Marius Cecon" wrote:

> Hi,
> My programm (24/7 - VB6) is connected to a MSSQL Database with only one
> table where data is stored with a time_stamp field as index. Automatically
> the software updates each 2 minutes the table and with the same frequency
> querys and display data for the last 3 days.
> I have also one function where the user can select the amount of the
> displayed data. When the software is working for a few ws in automatic
> mode (allways 3 day - data displaying) and the a user start a request to
> display all the datas in the table, at the first time the query is failing
> with timeout and at the second request is returning the requested data. I
> use the ADODB Connection object with client side cursor which is used in a
> ADODB.Recordset Open method to return the data.
> Has anybody a ideea why is the first time query failing and /or how to
> prevent this ?
> Thank you !
>
>|||Hi RD,
Thank you for trying to help me.
I've changed my select query adding the NOLOCK hint but is no change in the
behaviour.
They are no multiuser access to the table, my programm is the only one
connected to the table over a Connection object created at programm start
and closed when programm ends.
Marius.
"R.D" <RD@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF677CC9-1EB4-4EA9-A588-BC7FA12E4E09@.microsoft.com...
> Hi
> It appears to be contention problem.Try using lower level locks and if
> needed you may use NOLOCK HINT with the select query in backend if
possible.
> Regards
> R.D
> "Marius Cecon" wrote:
>
Automatically
frequency
automatic
failing
I
a|||On Wed, 31 Aug 2005 09:33:20 +0200, "Marius Cecon" <m.cecon@.hydrovision.de>
wrote:
in <ugELI5frFHA.2076@.TK2MSFTNGP14.phx.gbl>

>Hi,
>My programm (24/7 - VB6) is connected to a MSSQL Database with only one
>table where data is stored with a time_stamp field as index. Automatically
>the software updates each 2 minutes the table and with the same frequency
>querys and display data for the last 3 days.
>I have also one function where the user can select the amount of the
>displayed data. When the software is working for a few ws in automatic
>mode (allways 3 day - data displaying) and the a user start a request to
>display all the datas in the table, at the first time the query is failing
>with timeout and at the second request is returning the requested data. I
>use the ADODB Connection object with client side cursor which is used in a
>ADODB.Recordset Open method to return the data.
>Has anybody a ideea why is the first time query failing and /or how to
>prevent this ?
>Thank you !
Try setting the connection object's CommandTimeout property to zero (infinit
e)
just prior to the recordset Open method.
Stefan Berglund

Performance on SQL 2000

I have a SQL server with one file group that's about 600GB and two files
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks and
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:
> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks and
> see that they often break up the database into a number of files with 200GB
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney among
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>> I have a SQL server with one file group that's about 600GB and two files
>> residing on a clariion array. Index jobs, etc. appear to run faster if
>> I
>> create more files. I looked at some of microsoft's tpc server benchmarks
>> and
>> see that they often break up the database into a number of files with
>> 200GB
>> as the max file size I've seen.
>> Does anyone out there know of any articles, web sites or whatever that
>> specifically address this? Are users out there creating large numbers
>> of
>> files and I'm in the dark? Articles by reputable sources (K. Delaney
>> among
>> just a few) are what I'm looking for.
>> --
>> burt_king@.yahoo.com

Performance on SQL 2000

I have a SQL server with one file group that's about 600GB and two files
residing on a clariion array. Index jobs, etc. appear to run faster if I
create more files. I looked at some of microsoft's tpc server benchmarks an
d
see that they often break up the database into a number of files with 200GB
as the max file size I've seen.
Does anyone out there know of any articles, web sites or whatever that
specifically address this? Are users out there creating large numbers of
files and I'm in the dark? Articles by reputable sources (K. Delaney among
just a few) are what I'm looking for.
--
burt_king@.yahoo.comOK, since no one answered I'll beg for conjecture, random thoughts or
otherwise...
--
burt_king@.yahoo.com
"burt_king" wrote:

> I have a SQL server with one file group that's about 600GB and two files
> residing on a clariion array. Index jobs, etc. appear to run faster if I
> create more files. I looked at some of microsoft's tpc server benchmarks
and
> see that they often break up the database into a number of files with 200G
B
> as the max file size I've seen.
> Does anyone out there know of any articles, web sites or whatever that
> specifically address this? Are users out there creating large numbers of
> files and I'm in the dark? Articles by reputable sources (K. Delaney amo
ng
> just a few) are what I'm looking for.
> --
> burt_king@.yahoo.com|||It's generally not about more file but more disk spindles. When parts of
the database reside on different disk drives, seeks can be done in parallel
so the overall performance is improved. There are a few cases where
multiple files allow parallel operations which may help performance but this
generally is not as big a factor as splitting logs, indexes and data into
different disks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"burt_king" <burt_king@.yahoo.com> wrote in message
news:6040A038-EB9E-4D03-8023-BA5A79A8F469@.microsoft.com...[vbcol=seagreen]
> OK, since no one answered I'll beg for conjecture, random thoughts or
> otherwise...
> --
> burt_king@.yahoo.com
>
> "burt_king" wrote:
>