Large table/slow query/ can performance be improved?

I am having performance issues on a SQL query in Access. My query is
accessing and joining several tables (one very large one). The tables are
linked ODBC. The client submits the query to the server, separated by
several states. It appears the query is retrieving gigs of data from the
table and processing the joins on the client. Is there away to perform more
of the work on the server there by minimizing the amount of extraneous table
data moving across the network and improving performance (woefully slow
about 6 hours)?"Robert" <stop.spam@.whitehouse.gov> wrote in message
news:HLFKqK.MqD@.news.boeing.com...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform
more
> of the work on the server there by minimizing the amount of extraneous
table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?

Hmm, I haven't touched Access in years, but I recall it supporting what I
think it called Pass Thru Queries?

Or another option (perhaps ultimately your best one) is rewrite it as a
stored proc on the server.

If it's taking 6 hours, unless you're joining multigig table and doing
something like outer joins, I've got to imagine you're right, it's Access
trying to process things locally.
|||Hi,

These are some tips from my side.

1. Put indexes on column that is frequently used in query. This will
help database engine to search data quickly.
2. If the Recordset has less than 500 records, then the snapshot
Recordset will be faster than the dynaset Recordset.
3. Make query selection/criteria so as resultset will be small.

Finally, can you elaborate/post what exactly u r trying to do and
achive.

Amit

"Robert" <stop.spam@.whitehouse.gov> wrote in message news:<HLFKqK.MqD@.news.boeing.com>...
> I am having performance issues on a SQL query in Access. My query is
> accessing and joining several tables (one very large one). The tables are
> linked ODBC. The client submits the query to the server, separated by
> several states. It appears the query is retrieving gigs of data from the
> table and processing the joins on the client. Is there away to perform more
> of the work on the server there by minimizing the amount of extraneous table
> data moving across the network and improving performance (woefully slow
> about 6 hours)?|||As long as you're not using any client-side functions (Access or user-defined),
you should be able to make this into a Pass-Through query.

1) Copy the SQL Statement to the clipboard (or better yet, to Notepad)

2) In Access, make a new query, but don't choose any tables.

3) Choose Query/SQL Specific>Pass-Through

4) Paste the SQL Statement in there

5) Choose View/Properties

6) Set the Connect string (and ODBC Timeout!) appropriately

Run the query.

I have never seen an Access query take 6 hours to run...and I have an Access
database linked to over 68 MILLION records. Admittedly, I would never even WAIT
that long to find out if it could ever complete!

The slowest Access queries I've seen are the ones that I call "Query of a query
of a query...etc." with all records included the whole time, and a criteria at
the end; especially if Access or user-defined (VBA) function are invovled!

You really should learn the advantages of SQL Server's "SQL langauange" vs
Access. My favorite example is the FULL OUTER JOIN...with one statement in SQL
Server you get both sets of records from the eaither side of join whether or
not they match...can't do that in Access without making three queries! (OUTER,
Non-Match OUTER, then UNION)

Althogh the IIF in Access is convenient, it insists on evaluating both
outcomes...SQL Server's "CASE" construct makes much more sense.

If your query DOES use Access and/or user-defined VBA Functions, then you
should have the whole process converted to a stored procedure, and the use a
Pass-Through query to call the Stored Procedure. In all seriousness, EVERY
query you need should be converted to paramaterized Stored Procedures, and
called via Pass-Through...this is the best way to optimize performance.

CAVEAT: Pass-Through queries cannot be used as record sources for
linkchild/master situations (That's a JET-only feature), but you can always
program the same functionality yourself!

I'd be interested in seeing the SQL Statement of the 6-hr query.

Large table, really slow queries

I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.

The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:

select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0

As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).

may be you can try table partitioning in sql 2005..............but i am not sure about this....refer,

http://dumthata.com/sqlchn/articles/dpart.htm

|||

You should rewrite the query to remove the DATEDIFF function from the WHERE clause. This causes SQL Server to scan the table.

It is better to use WHERE dt_date BETWEEN '2007-07-26' AND '2007-07-27' for example.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Wow, what a world of difference it makes if I don't use datediff()!

Is there a general rule that will tell me whether the functions I use in my WHERE clause will cause a table scan versus index look-up? I'm not a DBA, just a .NET developer working on the back-end at the moment.

Immensely helpful, thank-you.
|||

Not just a rule of thumb, but an absolute no-no.

If you 'wrap' a field with a function, the query processor MUST apply that function to every row in the table (or index) to determine if the 'functionalized' value meets the criteria. As a result, the query will NOT efficiently utilize any indexing. It will require at least an index scan, and perhaps even a scan of the entire table.

If in a query, all rows newer than seven days ago are sought, you can either add seven days to the stored value and compare to the current datetime, or you can subract seven days from the current datetime and compare to the stored value.

For example, in this WHERE clause, :

WHERE dateadd( day, 7, MyColumn ) >= getdate()

Indexing on MyColumn cannot be properly used, the query will need to do an index scan (if MyColumn is indexed), and a table scan otherwise.

However you can easily use a function on the non-field side of the equality and still use indexes.

WHERE MyColumn <= dateadd( day, -7, getdate() )

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et=
c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KS
KS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegr oups.com...
Hello,
Im not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until thats done we have to deal with the following problem.
Our site is busiest over the weekend. Its not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but itd be
nice to know where to start ?
Thank you for your time
KS
|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again
|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegr oups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS
|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegr oups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>
|||Thank you both
|||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and the
sp_blocker_pss80 script. If you are looking for a third party monitoring tool
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:

> Hello,
> I’m not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until that’s done we have to deal with the following problem.
> Our site is busiest over the weekend. It’s not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but it’d be
> nice to know where to start ?
> Thank you for your time
> KS
>

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et= c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KSKS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I?m not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that?s done we have to deal with the following problem.
Our site is busiest over the weekend. It?s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it?d be
nice to know where to start ?
Thank you for your time
KS|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegroups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>|||Thank you both :)|||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and the
sp_blocker_pss80 script. If you are looking for a third party monitoring tool
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:
> Hello,
> Iâ'm not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until thatâ's done we have to deal with the following problem.
> Our site is busiest over the weekend. Itâ's not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but itâ'd be
> nice to know where to start ï'
> Thank you for your time
> KS
>

Large query, slow DB responses

Hello,
I=E2=80=99m not a DBA and only use SQL to write the odd stored procedure et=
c,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that=E2=80=99s done we have to deal with the following problem.
Our site is busiest over the weekend. It=E2=80=99s not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it=E2=80=99d be
nice to know where to start =EF=81=8A
Thank you for your time
KSKS
Run SQL Server Profiler to identify long running queries . Now that you
identified them see an execution plan of each query.
Does the optimizer use indexes defined on the table?
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
Im not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until thats done we have to deal with the following problem.
Our site is busiest over the weekend. Its not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but itd be
nice to know where to start ?
Thank you for your time
KS|||Thank you for your response Uri. I did think of using a sql trace to
see the duration of each execution, but will this put on any more
strain? Is it ok to have the trace running the whole day?
Thanks again|||If you have multiple/hyperthreaded processors, consider changing the 'max
degree of parallelism configuration option to '1' until you are able to
correct the underlying problem. This will prevent SQL Server from
generating a parallel plan for your BO users that can monopolize the
processors.
Hope this helps.
Dan Guzman
SQL Server MVP
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143361090.378310.151760@.e56g2000cwe.googlegroups.com...
Hello,
I'm not a DBA and only use SQL to write the odd stored procedure etc,
so please be patient with me.
We have a, very badly structured, DB which misbehaves every now and
then. We are in the process of re-writing the .NET code and some sps
but until that's done we have to deal with the following problem.
Our site is busiest over the weekend. It's not a big site (usual
number of DB connections over the weekend is ~200). The worse thing is
that the backoffice uses the same DB as the users at home. When a
backoffice query is run during those busy times, it will strain the DB
so much that no one else will be able to access the site.
So, my question is: is there anything within SQL that I can see which
query (or queries) is causing this? I would ultimately like to write an
app the support guys could use to find these things out, but it'd be
nice to know where to start ?
Thank you for your time
KS|||KS
> strain? Is it ok to have the trace running the whole day?
Yes, just make sure that you created a trace on the client and not a server
side
"KS" <kiki@.dyky.co.uk> wrote in message
news:1143376377.890485.156100@.z34g2000cwc.googlegroups.com...
> Thank you for your response Uri. I did think of using a sql trace to
> see the duration of each execution, but will this put on any more
> strain? Is it ok to have the trace running the whole day?
> Thanks again
>|||Thank you both |||Hi
SQL Profiler will allow you to determine which sps are taking the longest
durations, but will need to procedure to complete before giving you the
timings. If you are actually seeing blocking then sp_who2 will display
blocked processes or check out http://support.microsoft.com/kb/224453 and th
e
sp_blocker_pss80 script. If you are looking for a third party monitoring too
l
then check out the Quest offerings http://www.quest.com/sql_server/index.asp
John
"KS" wrote:

> Hello,
> I’m not a DBA and only use SQL to write the odd stored procedure etc,
> so please be patient with me.
> We have a, very badly structured, DB which misbehaves every now and
> then. We are in the process of re-writing the .NET code and some sps
> but until that’s done we have to deal with the following problem.
> Our site is busiest over the weekend. It’s not a big site (usual
> number of DB connections over the weekend is ~200). The worse thing is
> that the backoffice uses the same DB as the users at home. When a
> backoffice query is run during those busy times, it will strain the DB
> so much that no one else will be able to access the site.
> So, my question is: is there anything within SQL that I can see which
> query (or queries) is causing this? I would ultimately like to write an
> app the support guys could use to find these things out, but it’d be
> nice to know where to start ?
> Thank you for your time
> KS
>

Large FullText tables - slow queries

Hi,

I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.

To query the complete index is fast, for example:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

JOIN Pages ITP ON ITP.PageID = CT.[Key]

JOIN Feeds ITF ON ITP.IPID = ITF.IPID

JOIN Buyers ITB ON ITB.IBID = ITF.IBID

WHERE ITB.ID IN (1342,246)

These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).

One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:

'{ID:1342}' + [Body]

That sounds like a very good idea. I could then change the 2nd query above to be:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT

That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.

If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!

Many thanks for any advice.

Simon

I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||

Hi I am not sure if this can help:

35 Mio. I am looking forward to encounter more soon.

try:

Make sure to use the noiseword concept of FT, get rid of the noice first.

Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.

Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)

Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only

Try to Query also against other fields (not fulltext indexed)

Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!

.

*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!

Of course database needs field AutoId as Clusterd index.

8GB Memory / 64-bit version of SQL

Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)

How many keywords per record do you have, how long are they in average?

How big is the Textfile?

What is the CPU?

Thomas

Large FullText tables - slow queries

Hi,

I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.

To query the complete index is fast, for example:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT

JOIN Pages ITP ON ITP.PageID = CT.[Key]

JOIN Feeds ITF ON ITP.IPID = ITF.IPID

JOIN Buyers ITB ON ITB.IBID = ITF.IBID

WHERE ITB.ID IN (1342,246)

These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).

One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:

'{ID:1342}' + [Body]

That sounds like a very good idea. I could then change the 2nd query above to be:

SELECT 'ipod', COUNT(*)

FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT

That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.

If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!

Many thanks for any advice.

Simon

I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||

Hi I am not sure if this can help:

35 Mio. I am looking forward to encounter more soon.

try:

Make sure to use the noiseword concept of FT, get rid of the noice first.

Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.

Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)

Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only

Try to Query also against other fields (not fulltext indexed)

Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!

.

*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!

Of course database needs field AutoId as Clusterd index.

8GB Memory / 64-bit version of SQL

Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)

How many keywords per record do you have, how long are they in average?

How big is the Textfile?

What is the CPU?

Thomas