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 taking too much time

I have a SSIS package where a small table of 270 rows are fuzzy looked up with a table in another sql server and inserts the records to a temporary table. This takes more than 3 hours in debug mode or so and never goes beyond this step.I have used a OLE DB destination to insert to temporary table and temporary table doesn't get a value.

How big is that other table? The fuzzy will build an index to start with, and this can take time. Review the options on the second tab to influence the index. Also check obvious things like blocking or such like? What about a profiler trace, anything untoward?

|||other table also is the same size and has same no of rows.Thank for the clues I found that some of the NOT null columns in the destination table were not mapped which result in failure of insert. Now it inserts correct rows to temp table and fails with a primary key violation which means its going in a never ending sort of a loop. I am going to put a profiler trace to identify the problem.Any advice welcome

Fuzzy Lookup componant eating all available virtual memory

Hey,

I have a large set of data that I need to match against another large set of data. The reference table has 9.8mill rows and my input has 14.6mill rows. I started with a new project. I added my connection, then a task to clear the result table, then my data flow, then my OLE source, then my Fuzzy Lookup task, then my SQL Server Destination. I set the connection of my OLE source and set the query to pull the data. Then I set the connection of my Fuzzy Lookup task, set the reference table and told it to create a new index (the problem also occurs if I use a generated index) and then set up the matching criteria. Then I set the connection and destination for the SQL Server Destination.

After setting all this up, I hit Run. The thing ran great until ~ 800k rows and then it failed. I ran it several times and it always failed right around 800k with a message saying there was not enough space and then an error with buffers being passed to the Fuzzy Lookup component. I opened Task Manager and watched the resources as it ran and was amazed at what I saw. The Fuzzy Lookup component eats up every bit of Virtual Memory available and when it can't take any more, it errors out. I tried setting the Max Memory setting on the component and it seems to have no effect. I also played with the buffer settings on the data flow task to no avail. I even went as far as to put an identity on my input table and create a function that outputs selects that use a between on the identity to break the data into 600k chunks. I set up a ForEach component and DTS variables, but the Fuzzy Lookup component does not free the VM after the iteration of the ForEach component!

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 3068 on component "Fuzzy Lookup 1" (3067) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup 1" (3067) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1923) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

I found that thread after posting and tried the solutions there, but they had no effect on the package. It still almost linearly eats up all virtual memory. By the time it is at 800k records, it has eaten up 40gb of virtual memory.

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

|||I had the VM size increased to 200gb and I can do 3m sets now. It still isn't to where I need to to be for automation, but it will let me get the job done for now. If anyone has some ideas on what might be wrong, please please let me know!!|||

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

SL

|||There's been fixes for memory leak issues with the fuzzy components in the past. Could you check KB 912423: "FIX: Memory leaks occur when you use Fuzzy Lookup and Fuzzy Grouping to transform a SQL Server 2005 Integration Services package"?

Fuzzy Lookup componant eating all available virtual memory

Hey,

I have a large set of data that I need to match against another large set of data. The reference table has 9.8mill rows and my input has 14.6mill rows. I started with a new project. I added my connection, then a task to clear the result table, then my data flow, then my OLE source, then my Fuzzy Lookup task, then my SQL Server Destination. I set the connection of my OLE source and set the query to pull the data. Then I set the connection of my Fuzzy Lookup task, set the reference table and told it to create a new index (the problem also occurs if I use a generated index) and then set up the matching criteria. Then I set the connection and destination for the SQL Server Destination.

After setting all this up, I hit Run. The thing ran great until ~ 800k rows and then it failed. I ran it several times and it always failed right around 800k with a message saying there was not enough space and then an error with buffers being passed to the Fuzzy Lookup component. I opened Task Manager and watched the resources as it ran and was amazed at what I saw. The Fuzzy Lookup component eats up every bit of Virtual Memory available and when it can't take any more, it errors out. I tried setting the Max Memory setting on the component and it seems to have no effect. I also played with the buffer settings on the data flow task to no avail. I even went as far as to put an identity on my input table and create a function that outputs selects that use a between on the identity to break the data into 600k chunks. I set up a ForEach component and DTS variables, but the Fuzzy Lookup component does not free the VM after the iteration of the ForEach component!

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 3068 on component "Fuzzy Lookup 1" (3067) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup 1" (3067) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1923) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

I found that thread after posting and tried the solutions there, but they had no effect on the package. It still almost linearly eats up all virtual memory. By the time it is at 800k records, it has eaten up 40gb of virtual memory.

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

|||I had the VM size increased to 200gb and I can do 3m sets now. It still isn't to where I need to to be for automation, but it will let me get the job done for now. If anyone has some ideas on what might be wrong, please please let me know!!|||

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

SL

|||There's been fixes for memory leak issues with the fuzzy components in the past. Could you check KB 912423: "FIX: Memory leaks occur when you use Fuzzy Lookup and Fuzzy Grouping to transform a SQL Server 2005 Integration Services package"?

Functions in SQL Server7

Is it possible to create Functions in SqlServer 7?
I have a huge query > 500,000 rows that I want to select a subset of using a
function
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
**************************************
--Function
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
****************************************
******Just in SQL Server 2000 for now.
AMB
"Sanjay Pais" wrote:

> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
a
> function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>
>|||No, but you can do this in the where clause:
Select IdentityInd, ColA, ColB
From TableA
Where EXISTS( Select inExists.IdentityInd
From TableA as inExists
Where IdentityInd = tableA.IdentityInd
AND CONTAINS(inExists.ColA, tableA.ColB))
Can't you? It should be preferrable performancewise anyhow, I would expect.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
> a function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>|||You can't use two columns in a contains clause which caused my dilema in the
first place :)
Sanjay
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
> No, but you can do this in the where clause:
> Select IdentityInd, ColA, ColB
> From TableA
> Where EXISTS( Select inExists.IdentityInd
> From TableA as inExists
> Where IdentityInd = tableA.IdentityInd
> AND CONTAINS(inExists.ColA, tableA.ColB))
> Can't you? It should be preferrable performancewise anyhow, I would
> expect.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
>|||Ah, sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:uI%23xQvWBFHA.3700@.tk2msftngp13.phx.gbl...
> You can't use two columns in a contains clause which caused my dilema in
> the first place :)
> Sanjay
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
>

Full-Text Search Query Question - Performance

I have a table with 3M rows that contains a varchar(2000) field with
various keywords. Here is the table structure:

PKColumn
ImageID
FullTextColumn

There is an association table:
ImageID
ContractID

Now, I want to do a query where the ContractID = x and Contains some
word in the FullTextColumn. There is an association table that maps
Images to Contracts - so I can't use the trick of putting the Contract
code in the FullTextColumn.

I'm finding that first the FTS service is performing a search on the
Keyword (which can take a long time if 100K rows are returned) then
joining to the association table for the particular contract.

Is there anyway to make this faster by telling the FTS service, only
search this subset of rows for the keyword based on the contract.

Sorry if this sounds convoluted. Appreciate any help you can suggest.

Thanks!jimdandy@.shaw.ca (Jim Dandy) wrote in message news:<705c8539.0405271024.5ce1d19b@.posting.google.com>...
> I have a table with 3M rows that contains a varchar(2000) field with
> various keywords. Here is the table structure:
> PKColumn
> ImageID
> FullTextColumn
> There is an association table:
> ImageID
> ContractID
> Now, I want to do a query where the ContractID = x and Contains some
> word in the FullTextColumn. There is an association table that maps
> Images to Contracts - so I can't use the trick of putting the Contract
> code in the FullTextColumn.
> I'm finding that first the FTS service is performing a search on the
> Keyword (which can take a long time if 100K rows are returned) then
> joining to the association table for the particular contract.
> Is there anyway to make this faster by telling the FTS service, only
> search this subset of rows for the keyword based on the contract.
> Sorry if this sounds convoluted. Appreciate any help you can suggest.
> Thanks!

You might want to post this in microsoft.public.sqlserver.fulltext to
see if you get a better reply.

Simon

FullText Search data model for speed

Which method is faster for full-text search
One row big varchar field
comment VARCHAR(1000)
on lots of small varchar fieds
like 10 rows comment VARCHAR(100)
Message posted via http://www.sqlmonster.com
Kuido,
Could you post the full output of the below SQL code as this is very helpful
to understanding your environment as well as troubleshooting SQL FTS issues
as both SQL Server version and the OS platform play a part in FTS
performance tuning:
use <your_database_name>
SELECT @.@.version
SELECT @.@.language
SELECT count(*) from <your_true_FT-enabled_table_name>
The biggest factor in both FT Indexing and FT Searching is the number of
rows in your FT-enable table. Specifically, for a table with one row of
large text will be just as fast as 10 rows with smaller text. Furthermore,
in this situation (1 row table vs. 10 row table), T-SQL LIKE will be faster
as with very small tables all the rows will fit in one or a couple of data
pages, while the CONTAINS FTS queries will have to use the external MSSearch
service.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||SQL FTS query performance is most sensitive to the number of rows returned
in a query. So if you can limit the number of rows returned you will get
better performance. So 1 big varchar field would probably offer better
performance.
However if you can partition your table into sub tables, you will get even
better performance this way as long as you are only doing a single hit on
MSSearch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:35824a78f71441eda1460869a906ea07@.SQLMonster.c om...
> Which method is faster for full-text search
> One row big varchar field
> comment VARCHAR(1000)
> on lots of small varchar fieds
> like 10 rows comment VARCHAR(100)
> --
> Message posted via http://www.sqlmonster.com
|||I'm just planning database application
SQL-server will be Microsoft SQL Server 2000
Language - eesti (Estonian)
and there will be 15 000 000 rows in database
Message posted via http://www.sqlmonster.com
|||Kuido,
Then you should review all the SQL FTS links and resources at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
You should also review SQL Server 2000 Books Online (BOL) and using the
search tab, search on "full text" (with the double quotes) and especially
the BOL title: "Full-text Search Recommendations". Additional, since your
language will be Estonian, you will need to use the "neutral wordbreaker" as
Estonian is not one of the subset of languages supported by SQL FTS.
Specifically, for each of your FT-enabled columns, set the "Language for
Word Breaker" to Neutral.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Kuido K?lm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:677e90c6408e4927ad6a290c05d61745@.SQLMonster.c om...
> I'm just planning database application
> SQL-server will be Microsoft SQL Server 2000
> Language - eesti (Estonian)
> and there will be 15 000 000 rows in database
> --
> Message posted via http://www.sqlmonster.com

Full-text search

I am working as a sr. Developer for one of the application.
The search should allow to use whild cards....
Consider that I have three rows in table
1. Mark
2. Marketing
3. LandMark
When I search using contains and "Mark*" phrase it will return
Mark and Marketing
When I search using contains and "*Mark*" phrase I expect it to return all
rows but I am getting the same result as "Mark*"
It seems that
full text search does not work for postfix terms....
e.g. if user specifies 'Mark' as keyword the search should match 'Mark',
'Marketing' and 'Landmark'... right now the CONTAINS clause ignores the
'LandMark'
The leading * is thrown away in a search, the trailing on is treated as wild
card character.
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
"Raj Gandhi" <RajGandhi@.discussions.microsoft.com> wrote in message
news:D0334F24-F6F3-4909-A327-9E212D8AE376@.microsoft.com...
>I am working as a sr. Developer for one of the application.
> The search should allow to use whild cards....
> Consider that I have three rows in table
> 1. Mark
> 2. Marketing
> 3. LandMark
> When I search using contains and "Mark*" phrase it will return
> Mark and Marketing
> When I search using contains and "*Mark*" phrase I expect it to return all
> rows but I am getting the same result as "Mark*"
> It seems that
> full text search does not work for postfix terms....
> e.g. if user specifies 'Mark' as keyword the search should match 'Mark',
> 'Marketing' and 'Landmark'... right now the CONTAINS clause ignores the
> 'LandMark'

Full-text search

I am working as a sr. Developer for one of the application.
The search should allow to use whild cards....
Consider that I have three rows in table
1. Mark
2. Marketing
3. LandMark
When I search using contains and "Mark*" phrase it will return
Mark and Marketing
When I search using contains and "*Mark*" phrase I expect it to return all
rows but I am getting the same result as "Mark*"
It seems that
full text search does not work for postfix terms....
e.g. if user specifies 'Mark' as keyword the search should match 'Mark',
'Marketing' and 'Landmark'... right now the CONTAINS clause ignores the
'LandMark'The leading * is thrown away in a search, the trailing on is treated as wild
card character.
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
"Raj Gandhi" <RajGandhi@.discussions.microsoft.com> wrote in message
news:D0334F24-F6F3-4909-A327-9E212D8AE376@.microsoft.com...
>I am working as a sr. Developer for one of the application.
> The search should allow to use whild cards....
> Consider that I have three rows in table
> 1. Mark
> 2. Marketing
> 3. LandMark
> When I search using contains and "Mark*" phrase it will return
> Mark and Marketing
> When I search using contains and "*Mark*" phrase I expect it to return all
> rows but I am getting the same result as "Mark*"
> It seems that
> full text search does not work for postfix terms....
> e.g. if user specifies 'Mark' as keyword the search should match 'Mark',
> 'Marketing' and 'Landmark'... right now the CONTAINS clause ignores the
> 'LandMark'

Full-text search

I am working as a sr. Developer for one of the application.
The search should allow to use whild cards....
Consider that I have three rows in table
1. Mark
2. Marketing
3. LandMark
When I search using contains and "Mark*" phrase it will return
Mark and Marketing
When I search using contains and "*Mark*" phrase I expect it to return all
rows but I am getting the same result as "Mark*"
It seems that
full text search does not work for postfix terms....
e.g. if user specifies 'Mark' as keyword the search should match 'Mark',
'Marketing' and 'Landmark'... right now the CONTAINS clause ignores the
'LandMark'The leading * is thrown away in a search, the trailing on is treated as wild
card character.
--
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
"Raj Gandhi" <RajGandhi@.discussions.microsoft.com> wrote in message
news:D0334F24-F6F3-4909-A327-9E212D8AE376@.microsoft.com...
>I am working as a sr. Developer for one of the application.
> The search should allow to use whild cards....
> Consider that I have three rows in table
> 1. Mark
> 2. Marketing
> 3. LandMark
> When I search using contains and "Mark*" phrase it will return
> Mark and Marketing
> When I search using contains and "*Mark*" phrase I expect it to return all
> rows but I am getting the same result as "Mark*"
> It seems that
> full text search does not work for postfix terms....
> e.g. if user specifies 'Mark' as keyword the search should match 'Mark',
> 'Marketing' and 'Landmark'... right now the CONTAINS clause ignores the
> 'LandMark'

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text query (Freetexttable) returning duplicate rows

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:

Code Snippet

SELECT *

FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL

ORDER BY RANK DESC

vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

What you do get when you use LIKE.

Code Snippet

SELECT *

FROM vwSubtable

WHERE TitleSearch LIKE '%Across five Aprils%'

If you get dupes then take a closer look at your data.

|||

My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.

Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.

HTH!

|||Thanks! Rebuilding the catalog fixed the problem.

Full-text population very slow

Hello, I have a vague question. I have a table with two full-text indexed
columns and 500 000 rows. Previously when only one of the columns was
full-text indexed, the full population took only minutes. Now that there are
two columns, it seems to never finish, or at least is very slow. I have
searched for solution but found nothing, except that perhaps the November CTP
version of SQL 2005 could fix this problem. Does that version fix any known
bugs?
Any help much appreciated...
Thanks, Harri
It is strange. My table is:
CREATE TABLE [dbo].[Search](
[Customer] [varchar](20) NOT NULL,
[Modified] [datetime] NOT NULL DEFAULT ('1900-01-01 00:00:00.000'),
[RowVer] [timestamp] NOT NULL,
[Search] [nvarchar](3999) NOT NULL DEFAULT (''),
[Search2] [ntext] NULL,
CONSTRAINT [PK_Search] PRIMARY KEY CLUSTERED ([Customer] ASC)
)
If I full-text index only Search column, it succeeds in 4 minutes. If I
full-text index only Search2 column, and if all the values in that column are
NULL (but there are over 500 000 rows), then population never finishes.
CREATE FULLTEXT INDEX ON dbo.Search(Search2 LANGUAGE 0) KEY INDEX PK_Search
ON CPMCatalog WITH CHANGE_TRACKING MANUAL
|||Okay, problem solved. It is just the "ntext" data type, it is awfully slow
(same with nvarchar(max)). I guess that I have to create another table, and
remove Search2 column.
|||TEST2
"Harri Pesonen" <HarriPesonen@.discussions.microsoft.com> wrote in message
news:E7A12825-3326-488E-8F47-3F664A6A959E@.microsoft.com...
> Currently I am suspecting that the problem happens because most of the
> rows
> contain same words (this is testing material only). I will next try to
> randomize the words.
|||testyaser
"Harri Pesonen" <HarriPesonen@.discussions.microsoft.com> wrote in message
news:3E84A976-E2DD-4A99-818E-6C923D069E74@.microsoft.com...
> Hello, I have a vague question. I have a table with two full-text indexed
> columns and 500 000 rows. Previously when only one of the columns was
> full-text indexed, the full population took only minutes. Now that there
> are
> two columns, it seems to never finish, or at least is very slow. I have
> searched for solution but found nothing, except that perhaps the November
> CTP
> version of SQL 2005 could fix this problem. Does that version fix any
> known
> bugs?
> Any help much appreciated...
> Thanks, Harri