General configuration question - maximum performance.

This is a hyperthetical question.
If a web site is running slowly then we can create a web farm and spilt the
problem between multiple servers.
However if my database is running slowly, and given that its schema is well
designed and the application that is communicating with it is well designed,
what options do I have to speed it up? Does SQL Server have a concept of
a database farm? Can I throw another server at it?
I understand that I could peel off read-only data to another database but
what would happen if it was all read-write? eg maybe like a banks computer
system or a credit card processing authority?
I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
goes so far.
What databases do the BIG database users use? eg AMEX? Walmart? etc. I
guess they are not on SQL Server?
Regards
Dave ADave
I'd turn on SQL Server Profiler to identify long running queries , stored
procedures and try to imrove them. How to improve, that is probably playing
with indexes and/or if you are in SQL Server 2005 let the 'problematic'
query run throu Tuning wizard to advise you.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
SQL Server has no problem to keep TERA byte databases , the point is how
well the db is designed?
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt
> the problem between multiple servers.
> However if my database is running slowly, and given that its schema is
> well designed and the application that is communicating with it is well
> designed, what options do I have to speed it up? Does SQL Server have a
> concept of a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A
>|||Remember, this is a hyperthetical question. There is no need to run Profile
since there is no database to profile.
I have no doubt that SQL Server can handle multiterabyte datbases, my
problem is when there are a million conurrenct users updating and reading
that data, what options does an SQL Server designer have at thier disposal?
(Don't worry, I am an SQL Server zealot. I am just wondering what happens
when we push the limits and how far can it go.)
So, my question remains. Is there a concept of a farm of sql servers? - just
like there is a cluster of sql servers or a farm of web servers.
Regards
Dave A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ec0KCYFJIHA.5468@.TK2MSFTNGP05.phx.gbl...
> Dave
> I'd turn on SQL Server Profiler to identify long running queries , stored
> procedures and try to imrove them. How to improve, that is probably
> playing with indexes and/or if you are in SQL Server 2005 let the
> 'problematic' query run throu Tuning wizard to advise you.
>> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>> guess they are not on SQL Server?
>
> SQL Server has no problem to keep TERA byte databases , the point is how
> well the db is designed?
>
>
> "Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
> news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> This is a hyperthetical question.
>> If a web site is running slowly then we can create a web farm and spilt
>> the problem between multiple servers.
>> However if my database is running slowly, and given that its schema is
>> well designed and the application that is communicating with it is well
>> designed, what options do I have to speed it up? Does SQL Server have
>> a concept of a database farm? Can I throw another server at it?
>> I understand that I could peel off read-only data to another database but
>> what would happen if it was all read-write? eg maybe like a banks
>> computer system or a credit card processing authority?
>> I could throw more CPU, RAM, hard disk, etc at it but ultimately this
>> only goes so far.
>> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>> guess they are not on SQL Server?
>> Regards
>> Dave A
>|||When it comes to CPU and RAM, you can only scale up, not scale out. So
"throwing another server at it" will not help.
With the long standing trend of ever faster CPU's and the growing number
of cores per computer this can go a long way.
But even if SQL Server supported scaling out, this would only go "so
far" too. If you have one server and add an identical server, the
performance gain will never be 100%, it will be less. There is a limit
as to how much you can scale out, just as there is a limit to how much
you can scale up.
You should have a look at the TPC numbers show (see www.tpc.org). An
interesting chart is the Top 10 TPC-C by Performance. You will notice
that there is only one clustered configuration in this top 10, which
uses Oracle 10g. All others are nonclustered configuration are
nonclustered, including 4 other Oracle 10g configurations.
I therefore conclude that currently, there is no practical need for a
clustered SQL Server solution.
--
Gert-Jan
Dave A wrote:
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt the
> problem between multiple servers.
> However if my database is running slowly, and given that its schema is well
> designed and the application that is communicating with it is well designed,
> what options do I have to speed it up? Does SQL Server have a concept of
> a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A|||On Sun, 11 Nov 2007 20:54:20 +1000, "Dave A"
<dave@.sigmasolutionsdonotspamme.com.au> wrote:
>This is a hyperthetical question.
>If a web site is running slowly then we can create a web farm and spilt the
>problem between multiple servers.
>However if my database is running slowly, and given that its schema is well
>designed and the application that is communicating with it is well designed,
>what options do I have to speed it up? Does SQL Server have a concept of
>a database farm? Can I throw another server at it?
No. Not automagically. You are of course free to put all the A's on
one server, etc, and replicate data between them. This is often
enough done, but it is a lot of work. SQL Server 2008 looks like it's
aimed at much bigger configs, but I'm not up on the details.
>I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
>goes so far.
It goes pretty far, on a maxed-out Wintel machine, these days, with
SAN storage, etc. But increasingly, you actually have to know what
you're doing, config the hardware right, and be very careful of the
execution plans going sour on you. But do all that, and we're
probably running about 10^6 bigger and faster than SQL 6.5 on a 100mhz
server ten years ago! On a given hardware platform, for an average
transactional mix (whatever that might mean), or for a preplanned OLAP
environment, SQLServer is at least competitive with anyone.
>What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>guess they are not on SQL Server?
Oracle is still your game if you want to throw hardware at the
problem. Teradata is another way to go on humongous databases and
ad-hoc queries. I suppose DB2 fits in there somewhere, but I don't
know quite where.
J.

General configuration question - maximum performance.

This is a hyperthetical question.
If a web site is running slowly then we can create a web farm and spilt the
problem between multiple servers.
However if my database is running slowly, and given that its schema is well
designed and the application that is communicating with it is well designed,
what options do I have to speed it up? Does SQL Server have a concept of
a database farm? Can I throw another server at it?
I understand that I could peel off read-only data to another database but
what would happen if it was all read-write? eg maybe like a banks computer
system or a credit card processing authority?
I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
goes so far.
What databases do the BIG database users use? eg AMEX? Walmart? etc. I
guess they are not on SQL Server?
Regards
Dave ADave
I'd turn on SQL Server Profiler to identify long running queries , stored
procedures and try to imrove them. How to improve, that is probably playing
with indexes and/or if you are in SQL Server 2005 let the 'problematic'
query run throu Tuning wizard to advise you.

> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
SQL Server has no problem to keep TERA byte databases , the point is how
well the db is designed?
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt
> the problem between multiple servers.
> However if my database is running slowly, and given that its schema is
> well designed and the application that is communicating with it is well
> designed, what options do I have to speed it up? Does SQL Server have a
> concept of a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A
>|||Remember, this is a hyperthetical question. There is no need to run Profile
since there is no database to profile.
I have no doubt that SQL Server can handle multiterabyte datbases, my
problem is when there are a million conurrenct users updating and reading
that data, what options does an SQL Server designer have at thier disposal?
(Don't worry, I am an SQL Server zealot. I am just wondering what happens
when we push the limits and how far can it go.)
So, my question remains. Is there a concept of a farm of sql servers? - just
like there is a cluster of sql servers or a farm of web servers.
Regards
Dave A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ec0KCYFJIHA.5468@.TK2MSFTNGP05.phx.gbl...
> Dave
> I'd turn on SQL Server Profiler to identify long running queries , stored
> procedures and try to imrove them. How to improve, that is probably
> playing with indexes and/or if you are in SQL Server 2005 let the
> 'problematic' query run throu Tuning wizard to advise you.
>
>
> SQL Server has no problem to keep TERA byte databases , the point is how
> well the db is designed?
>
>
> "Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
> news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>|||When it comes to CPU and RAM, you can only scale up, not scale out. So
"throwing another server at it" will not help.
With the long standing trend of ever faster CPU's and the growing number
of cores per computer this can go a long way.
But even if SQL Server supported scaling out, this would only go "so
far" too. If you have one server and add an identical server, the
performance gain will never be 100%, it will be less. There is a limit
as to how much you can scale out, just as there is a limit to how much
you can scale up.
You should have a look at the TPC numbers show (see www.tpc.org). An
interesting chart is the Top 10 TPC-C by Performance. You will notice
that there is only one clustered configuration in this top 10, which
uses Oracle 10g. All others are nonclustered configuration are
nonclustered, including 4 other Oracle 10g configurations.
I therefore conclude that currently, there is no practical need for a
clustered SQL Server solution.
Gert-Jan
Dave A wrote:
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt th
e
> problem between multiple servers.
> However if my database is running slowly, and given that its schema is wel
l
> designed and the application that is communicating with it is well designe
d,
> what options do I have to speed it up? Does SQL Server have a concept o
f
> a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A|||On Sun, 11 Nov 2007 20:54:20 +1000, "Dave A"
<dave@.sigmasolutionsdonotspamme.com.au> wrote:

>This is a hyperthetical question.
>If a web site is running slowly then we can create a web farm and spilt the
>problem between multiple servers.
>However if my database is running slowly, and given that its schema is well
>designed and the application that is communicating with it is well designed
,
>what options do I have to speed it up? Does SQL Server have a concept of
>a database farm? Can I throw another server at it?
No. Not automagically. You are of course free to put all the A's on
one server, etc, and replicate data between them. This is often
enough done, but it is a lot of work. SQL Server 2008 looks like it's
aimed at much bigger configs, but I'm not up on the details.

>I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
>goes so far.
It goes pretty far, on a maxed-out Wintel machine, these days, with
SAN storage, etc. But increasingly, you actually have to know what
you're doing, config the hardware right, and be very careful of the
execution plans going sour on you. But do all that, and we're
probably running about 10^6 bigger and faster than SQL 6.5 on a 100mhz
server ten years ago! On a given hardware platform, for an average
transactional mix (whatever that might mean), or for a preplanned OLAP
environment, SQLServer is at least competitive with anyone.

>What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>guess they are not on SQL Server?
Oracle is still your game if you want to throw hardware at the
problem. Teradata is another way to go on humongous databases and
ad-hoc queries. I suppose DB2 fits in there somewhere, but I don't
know quite where.
J.

General Advice: Multiple queries VS large joins

I am wondering: I have a database with a few disjoint sets, and I was wondering what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks!
Andrew Wied
It really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied

General Advice: Multiple queries VS large joins

I am wondering: I have a database with a few disjoint sets, and I was wondering what people's thoughts and reccomendations are
In general, does it make more sense to make multiple queries on a database, or use large joins (in my case, outer joins)
Is there different scenarios where one approach is better than the other
I have an environment which has both (initially) small to mid - sized data size and rather light accesses to my app. Any suggestions?
Thanks
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied

General Advice: Multiple queries VS large joins

I am wondering: I have a database with a few disjoint sets, and I was wonder
ing what people's thoughts and reccomendations are:
In general, does it make more sense to make multiple queries on a database,
or use large joins (in my case, outer joins)?
Is there different scenarios where one approach is better than the other?
I have an environment which has both (initially) small to mid - sized data s
ize and rather light accesses to my app. Any suggestions?
Thanks!
Andrew WiedIt really depends on way too many factors... how much data is coming from
each side of the join? how many rows are in each table? how effective are
the on and where clauses in limiting results based on an index (or a scan)?
Note that when you run a query against 10,000 rows and then a query against
100,000 rows, your physical I/O will be as much as 110,000. However if you
join these two tables, depending on the above and other factors, your I/O
could be 10,000 * 100,000 (there may be an operation for every row in one
table against every row in the other).
Also remember that if you are using a client to display these results, it
may also be better to stagger the output instead of giving them one big
chunk of data all at once.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew Wied" <anonymous@.discussions.microsoft.com> wrote in message
news:4F8067E3-E4C3-4A24-A7E9-705295107956@.microsoft.com...
> I am wondering: I have a database with a few disjoint sets, and I was
wondering what people's thoughts and reccomendations are:
> In general, does it make more sense to make multiple queries on a
database, or use large joins (in my case, outer joins)?
> Is there different scenarios where one approach is better than the
other?
> I have an environment which has both (initially) small to mid - sized data
size and rather light accesses to my app. Any suggestions?
> Thanks!
> Andrew Wied

Gather aggregate data from two tables

I have three tables:

1) Contract - columns ContractNo and ContractName

2) SalesTransactions - multiple rows for each contract - relevant columns: ContractNo, InvoiceDate, Value

3) CostOfSaleTransactions - multiple rows for each contract - relevant columns: ContractNo, TransactionDate, Cost

How do I write a SELECT statement to produce rows containing:

ContractNo ContractName Sum of Value for ContractNo between @.FromDate and @.ToDate Sum of Cost for ContractNo between @.FromDate and @.ToDate

Not all Contracts have either Sales or CostOfSales Transactions in the relevant date range and so one or both totals can be zero.

I've written something like:

SELECT CT.ContractNo, CT.ContractName, sum(CT.Value) as TotalValue, Sum(CS.Cost) as TotalCost

FROM Contract CT

INNER JOIN SalesTransactions ST ON CT.ContractNo=ST.ContractNo

INNER JOIN CostOfSaleTransactions CS ON CT.ContractNo=CS.ContractNo

WHERE (ST.InvoiceDate BETWEEN @.FromDate AND @.ToDate) AND (CS.TransactionDate BETWEEN @.FromDate AND @.ToDate)

GROUP BY CT.ContractNo, CT.ContractName

The TotalValue and TotalCost figures I get are much higher than expected. I presume this is something to do with the JOINs or WHERE clause. Please can you advise how I get the correct values?

You can do the following:

SELECT CT.ContractNo, CT.ContractName

, (SELECT sum(ST.Value)

FROM SalesTransactions ST

WHERE CT.ContractNo=ST.ContractNo

AND ST.InvoiceDate BETWEEN @.FromDate AND @.ToDate) as TotalValue

, (SELECT Sum(CS.Cost)

FROM CostOfSaleTransactions CS

WHERE CT.ContractNo=CS.ContractNo

AND CS.TransactionDate BETWEEN @.FromDate AND @.ToDate) as TotalCost

FROM Contract CT

Fuzzy Lookup error - Multiple Identity Columns?

I'm developing an ETL solution that needs to look for duplicate records using a fuzzy lookup. If the lookup table has an identity column, I get the following error. I can get this to work on a local desktop instance of SQL server, but not on my development server or production box. Any help is greatly appreciated.

Also I've stripped down the incoming data for the lookup to a very simplified version of what I'd like to use, but if I can't get it to work then I can't add addtional columns to match with.

It's like it's trying to add it's own Id to the temp table created for the tokens

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070403_10:09:36_3532_aeac56a4-8bc0-4ff4-ac41-0984e293261a'. Only one identity column per table is allowed.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".

Error: 0xC004701A at Move Clean Records into Clean Enrollment, DTS.Pipeline: component "Fuzzy Lookup" (300) failed the pre-execute phase and returned error code 0xC0202009.

I have found an interesting solution to this problem, but I'm still left wondering if this is by design or error. The reference table must be in the dbo schema to work. I began with a reference table in a schema owned by me. I finally moved to another DB on the same server and it worked. The difference I then noticed was that the table was now in dbo. I went back to the original development DB and changed ownership on my schema to dbo. I ran the package and got the same error. So I then moved the table to dbo and it worked just fine. If anybody knows why this might be, I'd really like to know. If you'd like to see where I started out, get a copy of Hands-On SQL Server 2005 Integration Services by Ashwani Nanda. In Chapter 10 there is a good section on removing duplicates.

Unaswered questions

Is it a permission issue, a DB confituration issue or the design of the Fuzzy Lookup or an error.

It is interesting that if I remove the ID column (which uses identity(1,1))from my reference table in my schema it will work, but its sort of the point to be able to go back to the duplicate records for updates, inserts or deletes etc.

Fully Qualified Query Accross Databases

Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.

The only limitations that I'm aware of are the same limitations if the queries only run against one database, i.e. the databases have to be properly optimized for performance. If one database is properly indexed and one is not the bottleneck will be the database that isn't properly indexed.
|||

That is a fact, but I am really trying to determine if there are any optimizer limitations or nuances like there are if you are using partitioning.

|||The fact that there may be partitioning really isn't relevant since partitioning is only undertaken to optimize query performance anyway by distributing data across multiple files. As I mentioned, if the databases are optimized, whether through the use of partitioning or any other strategy, then cross database queries aren't an issue. There aren't any optimizer limitations or nuances that need to be considered.

If you were performing distributed queries across multiple linked servers or across multiple resource engines, i.e. SQL Server/Oracle then there might be some things to consider but even then, the issue would probably still come down to whether the databases are properly optimized.

Full-Text Search on Multiple Tables & Columns

Hi,

I have tried this code fromhttp://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.

Here's my code:

SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,
CONTAINSTABLE([tStaffDir], *, @.Name) as A
where
A.[KEY] = e.[ID] and
t.[ID] = e.[ID]

I have FT the both the tables above and I am able to get results from the [tStaffDir] table but not the [tStaffDir_PrevEmp] table.
The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.

Please advise what I should do and look out for.

Many Thanks.

Will appreciate if anyone can advise. Thanks.|||

Seems like I am not able to get a answer.

Anyone able to point me in the correct direction?

FullText Search on Multiple Language

Hi All,
I am trying to implement a FullText search for a table which contains
translations for many different languages. However, different languages
utilized different word breaker for the FullText search to work properly for
that language but I can only assign a single word breaker to a table (for
example, traditional chinese uses Chinese(Taiwan) word breaker, simplified
chinese uses Chinese(PRC), etc).
To overcome this problem, I used horizontal partitioning to split the
table into smaller tables according to its various languages; each assigned
with the word breader for that language. And I try to insert, update, delete
the data in all the tables thru a view which union all the smaller tables.
This seems to work well.
Next, I created a storeprocedure to do a full-text search by searching
each smaller table and appending the result.to a temp table
Example
CREATE TABLE #Temp
(
searchresult ntext
)
INSERT INTO #Temp (searchresult)
SELECT translation FROM translation_german WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
:
The problem is when I do a full-text search for a chinese string in the
table with german word break, it will give the error#7619 ('A clause of the
query contained only ignored words.') and terminate the storeprocedure
immediately. There doesn't seem to be anyway to ignore the error and
continue to the search in the next table(s).
1. Does anyone has anyway to handle the error#7619 in such a way that it
will not terminate the Storeprocedure but continue to search the next table?
OR
2. Is way any other method to implement a full-text search for a table
containing multiple languages (with word breaker correctly implemented)
other then the one described above?
Many thanks in advance
Royston
Royston,
Could you post the full output of -- SELECT @.@.version -- as this would be
most helpful in understanding your environment and providing you with
answers.
First of all, and assuming (for now) that you're using SQL Server 2000, you
do not need to split your table into multiple smaller tables, (one for each
language), as SQL Server 2000 supports multiple collation per column and SQL
Full Text Indexing supports different "Languages for Word Breaker" per
column. Therefore, you can have one column per language and set the
collation and "Language for Word Breaker" at a per column level in one
table. This is one reason I'm requesting the @.@.version info.
In regards to error 7619, when FT Searching for a Chinese string in the
table with German word break, you will need to capture the language before
you issue the FTS query and then run it against the appropriate table (or
column) to avoid this error. Note, that in SQL Server 2005 (currently, still
in beta), you will be able to store multiple languages in one column, and
then issue language specific queries based upon the LCID of the language.
Just curious, why are you running a FT Search query for a Chinese string in
the table with German?
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Royston" <royston@.earth9.com> wrote in message
news:euxMNcUKFHA.2212@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I am trying to implement a FullText search for a table which contains
> translations for many different languages. However, different languages
> utilized different word breaker for the FullText search to work properly
for
> that language but I can only assign a single word breaker to a table (for
> example, traditional chinese uses Chinese(Taiwan) word breaker, simplified
> chinese uses Chinese(PRC), etc).
> To overcome this problem, I used horizontal partitioning to split the
> table into smaller tables according to its various languages; each
assigned
> with the word breader for that language. And I try to insert, update,
delete
> the data in all the tables thru a view which union all the smaller tables.
> This seems to work well.
> Next, I created a storeprocedure to do a full-text search by searching
> each smaller table and appending the result.to a temp table
> Example
> CREATE TABLE #Temp
> (
> searchresult ntext
> )
> INSERT INTO #Temp (searchresult)
> SELECT translation FROM translation_german WHERE CONTAINS(*,
@.searchstring)
> INSERT INTO #Temp (searchresult)
> SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
> INSERT INTO #Temp (searchresult)
> SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
> :
> The problem is when I do a full-text search for a chinese string in
the
> table with german word break, it will give the error#7619 ('A clause of
the
> query contained only ignored words.') and terminate the storeprocedure
> immediately. There doesn't seem to be anyway to ignore the error and
> continue to the search in the next table(s).
> 1. Does anyone has anyway to handle the error#7619 in such a way that
it
> will not terminate the Storeprocedure but continue to search the next
table?
> OR
> 2. Is way any other method to implement a full-text search for a table
> containing multiple languages (with word breaker correctly implemented)
> other then the one described above?
> Many thanks in advance
> Royston
>
>
>
>
|||Hi John,
Yes, I am using SQL Server 2000. I am currently using a single column to
store all the various translations, therefore I need to do a FT search for a
Chinese string in the German table (actually, all the smaller tables). The
logic for insertion, update, and delete is simpler with this method (normal
query thru a view will do). Adding of new language requires only addition of
a new smaller table and a quick update to the view. There's no change to the
existing storeprocedures as the storeprocedures access the translation thru
a view.
Here's my simplified sample schema,
Create Table [English_Table]
(
string_id int primary key,
english_text ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
)
Create Table [Translation_German_Table]
(
string_id int,
culture varchar(5),
translation ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
Constraint pk_german Primary Key (string_id., culture)
Check (culture = 'de-DE')
)
Create Table [Translation_Chinese_Table]
(
string_id int,
lang_id varchar(5),
translation ntext,
lastupdated_by int,
lastupdated_on datetime,
search_id uniqueidentifier not null Unqiue
Constraint pk_chinese Primary Key (string_id, culture)
Check (culture = 'zh-CN')
)
Create View [Translation]
(
Select * From [Translation_German_Table]
Union All
Select * From [Translation_Chinese_Table]
)
Storing each language per column will require more complex logic on the
Client side to determine the current language of the translation and to
insert into/ update the appropriate column (addition of new language will
require adding a new column and may also require changes to existing logic).
I will still have to check the language of the search string to implement
the FT search on the correct column (this may pose a problem if the search
string is user input and I don't know what language the user is inputing).
Thanks,
Royston
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OENJA9XKFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Royston,
> Could you post the full output of -- SELECT @.@.version -- as this would be
> most helpful in understanding your environment and providing you with
> answers.
> First of all, and assuming (for now) that you're using SQL Server 2000,
you
> do not need to split your table into multiple smaller tables, (one for
each
> language), as SQL Server 2000 supports multiple collation per column and
SQL
> Full Text Indexing supports different "Languages for Word Breaker" per
> column. Therefore, you can have one column per language and set the
> collation and "Language for Word Breaker" at a per column level in one
> table. This is one reason I'm requesting the @.@.version info.
> In regards to error 7619, when FT Searching for a Chinese string in the
> table with German word break, you will need to capture the language before
> you issue the FTS query and then run it against the appropriate table (or
> column) to avoid this error. Note, that in SQL Server 2005 (currently,
still
> in beta), you will be able to store multiple languages in one column, and
> then issue language specific queries based upon the LCID of the language.
> Just curious, why are you running a FT Search query for a Chinese string
in[vbcol=seagreen]
> the table with German?
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Royston" <royston@.earth9.com> wrote in message
> news:euxMNcUKFHA.2212@.TK2MSFTNGP12.phx.gbl...
contains[vbcol=seagreen]
> for
(for[vbcol=seagreen]
simplified[vbcol=seagreen]
the[vbcol=seagreen]
> assigned
> delete
tables.[vbcol=seagreen]
searching[vbcol=seagreen]
> @.searchstring)
> the
> the
> it
> table?
table
>
|||Royston, I am not sure what you suggest would work.
First, I don't think you can do Full text search on view, in other word, do
select * from View1 where contains (data,'some search string')
Second, you would still have to choose ONE word breaker on the indexed data
column, so if you have Germany and Chinese in the same column, What word
breaker can you choose?
I am running the similar problem, in my case, I want to full text search on
chinese and english,but struggling to find a solution for it.
Any suggest are welcome.
--Xin Chen
"Royston" <royston@.earth9.com> wrote in message
news:uspzi$cKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Hi John,
> Yes, I am using SQL Server 2000. I am currently using a single column
to
> store all the various translations, therefore I need to do a FT search for
a
> Chinese string in the German table (actually, all the smaller tables). The
> logic for insertion, update, and delete is simpler with this method
(normal
> query thru a view will do). Adding of new language requires only addition
of
> a new smaller table and a quick update to the view. There's no change to
the
> existing storeprocedures as the storeprocedures access the translation
thru
> a view.
> Here's my simplified sample schema,
> Create Table [English_Table]
> (
> string_id int primary key,
> english_text ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> )
> Create Table [Translation_German_Table]
> (
> string_id int,
> culture varchar(5),
> translation ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> Constraint pk_german Primary Key (string_id., culture)
> Check (culture = 'de-DE')
> )
> Create Table [Translation_Chinese_Table]
> (
> string_id int,
> lang_id varchar(5),
> translation ntext,
> lastupdated_by int,
> lastupdated_on datetime,
> search_id uniqueidentifier not null Unqiue
> Constraint pk_chinese Primary Key (string_id, culture)
> Check (culture = 'zh-CN')
> )
> Create View [Translation]
> (
> Select * From [Translation_German_Table]
> Union All
> Select * From [Translation_Chinese_Table]
> )
> Storing each language per column will require more complex logic on
the
> Client side to determine the current language of the translation and to
> insert into/ update the appropriate column (addition of new language will
> require adding a new column and may also require changes to existing
logic).[vbcol=seagreen]
> I will still have to check the language of the search string to implement
> the FT search on the correct column (this may pose a problem if the search
> string is user input and I don't know what language the user is inputing).
> Thanks,
> Royston
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OENJA9XKFHA.3336@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
> you
> each
> SQL
before[vbcol=seagreen]
(or[vbcol=seagreen]
> still
and[vbcol=seagreen]
language.[vbcol=seagreen]
string[vbcol=seagreen]
> in
> contains
languages[vbcol=seagreen]
properly[vbcol=seagreen]
> (for
> simplified
> the
> tables.
> searching
in[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]
> table
implemented)
>
|||Hi Xin Chen,
No, I am not doing the FT search on the view. My solution is to utilize
horizontal partitioning to split my translation table into multiple child
tables each storing only a particular language, and then assign a different
word breaker to each of the child table according to the language it is
storing. I am using a view to union all the child tables so that I can
access all the child tables thru the view as though it is a single table
(that is, I can insert, update, delete via the view instead of referencing
the child tables). Note that I implemented a check on the primary key column
of each of the child table, this will allow the view to know where to
insert/ update or delete the row referenced in the view. I have tried this
and it seems to work.
However, to implement FT search, I created a temporary table and for each
child table I will do a FT search with the search string and append the
result to the temporay table (like the example below). The problem I
encountered is if the search string contain a particular language string
like chinese string, and the storeprocedure is doing a FT search on a child
table with german word breaker, the storeprocedure will generate an error
#7619 ('A clause of the query contained only ignored words.') and terminate
execution. There doesn't seem to be any way I can catch the exception in the
storeprocedure so as to ignore the error and continue the search on the next
child table (that is, the one with the correct word breaker). As suggested
by John, it seems the only way to resolve this is to check the language of
the search string beforehand somehow and direct the FT search to the
respective child table. This shall work, but I am hoping if anyone know how
to catch the error#7619 exception and prevent the storeprocedure from
terminating since it may be difficult to determine the language of the
search string without restricting the user input.
Simplified Sample Code Example,
CREATE TABLE #Temp
(
searchresult ntext
)
INSERT INTO #Temp (searchresult)
SELECT translation FROM translation_german WHERE CONTAINS(*,@.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_japanese WHERE CONTAINS(*, @.searchstring)
INSERT INTO #Temp (searchresult)
SELECT * FROM translation_chinese WHERE CONTAINS(*, @.searchstring)
Regards,
Royston
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:eNm4kjfKFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Royston, I am not sure what you suggest would work.
> First, I don't think you can do Full text search on view, in other word,
do
> select * from View1 where contains (data,'some search string')
> Second, you would still have to choose ONE word breaker on the indexed
data
> column, so if you have Germany and Chinese in the same column, What word
> breaker can you choose?
> I am running the similar problem, in my case, I want to full text search
on[vbcol=seagreen]
> chinese and english,but struggling to find a solution for it.
> Any suggest are welcome.
> --Xin Chen
> "Royston" <royston@.earth9.com> wrote in message
> news:uspzi$cKFHA.1284@.TK2MSFTNGP14.phx.gbl...
column[vbcol=seagreen]
> to
for[vbcol=seagreen]
> a
The[vbcol=seagreen]
> (normal
addition[vbcol=seagreen]
> of
> the
> thru
> the
will[vbcol=seagreen]
> logic).
implement[vbcol=seagreen]
search[vbcol=seagreen]
inputing).[vbcol=seagreen]
> be
2000,[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
> before
> (or
> and
> language.
> string
> languages
> properly
split[vbcol=seagreen]
update,[vbcol=seagreen]
> in
> of
storeprocedure[vbcol=seagreen]
> that
next
> implemented)
>

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.com
You might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
--
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegroups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegroups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.com
You might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Full-text search across multiple tables

Hello,

We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.

As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.

Thanks.

John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.

--
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

"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com