Full text or not full text?

I have problem with the speed of queries.
I'm searching for UK car number (registration) plates consisting of 2
letters, followed by 2 numbers, followed by 3 letters.
ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
Most of these number plates are in their own separate row, but some of
them are in a comma delimited string,
Now suppose I want to find 'AB12ABC' in a string that consists of
'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
Now at the moment, I've got a database table consisting of nearly
380,000 rows.
I've got indexs placed on the 'plate' column, and I've even tried
setting up full-text indexing, none of which have increased the speed
of the query:
SET NOCOUNT ON
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
BEGIN
SELECT 'Yes'
END
ELSE
BEGIN
SELECT 'N/A'
END
which, at [resent takes around 30 seconds to complete, so running a
report for say 300 number plates to find if they have been ordered or
not, takes around 30+ minutes to complete.
If there another way of doing things that any one can suggest? Apart
from splitting the strings up?That is the price of not normalizing. If the data was in its own
table, as all repeating groups should be, performance would not be a
problem at all. It would be pretty much instantaneous.
If doing them one at a time takes 30 seconds each, don't do them one
at a time. Load all 300 into a table, and run them all in one pass.
Performance won't be good, but it should not be as bad.
--This version just lists the ones that match
SELECT M.RegNum
FROM MatchList as M
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
--This version lists them all, with results of the match for each
SELECT M.RegNum,
CASE WHEN V.plates IS NOT NULL
THEN 'Yes'
ELSE 'N/A'
END as Found
FROM MatchList as M
LEFT OUTER
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
Roy Harvey
Beacon Falls, CT
On 6 Jul 2006 05:03:40 -0700, "pinhead" <dlynes2005@.gmail.com> wrote:

>I have problem with the speed of queries.
>I'm searching for UK car number (registration) plates consisting of 2
>letters, followed by 2 numbers, followed by 3 letters.
>ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
>Most of these number plates are in their own separate row, but some of
>them are in a comma delimited string,
>Now suppose I want to find 'AB12ABC' in a string that consists of
>'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
>Now at the moment, I've got a database table consisting of nearly
>380,000 rows.
>I've got indexs placed on the 'plate' column, and I've even tried
>setting up full-text indexing, none of which have increased the speed
>of the query:
>SET NOCOUNT ON
>IF EXISTS (SELECT id
>FROM vclivePlates
>WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
>BEGIN
>SELECT 'Yes'
>END
>ELSE
>BEGIN
>SELECT 'N/A'
>END
>which, at [resent takes around 30 seconds to complete, so running a
>report for say 300 number plates to find if they have been ordered or
>not, takes around 30+ minutes to complete.
>If there another way of doing things that any one can suggest? Apart
>from splitting the strings up?|||You run into performance problem because you store few plates in one
row. If you can modify the database and store each plate in its own
row, then you won't have to use wildcard in the beginning of your
search criteria and the server will be able to use index seek instead
of table scan. If you must use one row to store few plates, then you
can try something else. In your post you said that most plates are in
there own rows and only some rows store more then one plate. If only
small percentage of the rows store few plates, then maybe this will
help - create a computed column on the table that counts the number of
comas in the column that holds the registration plate. Then create an
index on that column. Modify you query so it will look like this:
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
1)
OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
This might cause the server to use the indexes, but it depends on the
number of rows that contain more then one plate.
Adi
pinhead wrote:
> I have problem with the speed of queries.
> I'm searching for UK car number (registration) plates consisting of 2
> letters, followed by 2 numbers, followed by 3 letters.
> ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> Most of these number plates are in their own separate row, but some of
> them are in a comma delimited string,
> Now suppose I want to find 'AB12ABC' in a string that consists of
> 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> Now at the moment, I've got a database table consisting of nearly
> 380,000 rows.
> I've got indexs placed on the 'plate' column, and I've even tried
> setting up full-text indexing, none of which have increased the speed
> of the query:
> SET NOCOUNT ON
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> BEGIN
> SELECT 'Yes'
> END
> ELSE
> BEGIN
> SELECT 'N/A'
> END
> which, at [resent takes around 30 seconds to complete, so running a
> report for say 300 number plates to find if they have been ordered or
> not, takes around 30+ minutes to complete.
> If there another way of doing things that any one can suggest? Apart
> from splitting the strings up?|||Having a major braindead day today, so forgive me for this...
how would I go about counting the number of commas in the data row?
Adi wrote:[vbcol=seagreen]
> You run into performance problem because you store few plates in one
> row. If you can modify the database and store each plate in its own
> row, then you won't have to use wildcard in the beginning of your
> search criteria and the server will be able to use index seek instead
> of table scan. If you must use one row to store few plates, then you
> can try something else. In your post you said that most plates are in
> there own rows and only some rows store more then one plate. If only
> small percentage of the rows store few plates, then maybe this will
> help - create a computed column on the table that counts the number of
> comas in the column that holds the registration plate. Then create an
> index on that column. Modify you query so it will look like this:
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
> 1)
> OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
>
> This might cause the server to use the indexes, but it depends on the
> number of rows that contain more then one plate.
> Adi
> pinhead wrote:|||Sorry -
DATALENGTH(plates) - DATALENGTH(REPLACE(plates, ',', ''))
works well for me
pinhead wrote:[vbcol=seagreen]
> Having a major braindead day today, so forgive me for this...
> how would I go about counting the number of commas in the data row?
>
> Adi wrote:

Full text or not full text?

I have problem with the speed of queries.
I'm searching for UK car number (registration) plates consisting of 2
letters, followed by 2 numbers, followed by 3 letters.
ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
Most of these number plates are in their own separate row, but some of
them are in a comma delimited string,
Now suppose I want to find 'AB12ABC' in a string that consists of
'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
Now at the moment, I've got a database table consisting of nearly
380,000 rows.
I've got indexs placed on the 'plate' column, and I've even tried
setting up full-text indexing, none of which have increased the speed
of the query:
SET NOCOUNT ON
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
BEGIN
SELECT 'Yes'
END
ELSE
BEGIN
SELECT 'N/A'
END
which, at [resent takes around 30 seconds to complete, so running a
report for say 300 number plates to find if they have been ordered or
not, takes around 30+ minutes to complete.
If there another way of doing things that any one can suggest? Apart
from splitting the strings up?That is the price of not normalizing. If the data was in its own
table, as all repeating groups should be, performance would not be a
problem at all. It would be pretty much instantaneous.
If doing them one at a time takes 30 seconds each, don't do them one
at a time. Load all 300 into a table, and run them all in one pass.
Performance won't be good, but it should not be as bad.
--This version just lists the ones that match
SELECT M.RegNum
FROM MatchList as M
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
--This version lists them all, with results of the match for each
SELECT M.RegNum,
CASE WHEN V.plates IS NOT NULL
THEN 'Yes'
ELSE 'N/A'
END as Found
FROM MatchList as M
LEFT OUTER
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
Roy Harvey
Beacon Falls, CT
On 6 Jul 2006 05:03:40 -0700, "pinhead" <dlynes2005@.gmail.com> wrote:
>I have problem with the speed of queries.
>I'm searching for UK car number (registration) plates consisting of 2
>letters, followed by 2 numbers, followed by 3 letters.
>ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
>Most of these number plates are in their own separate row, but some of
>them are in a comma delimited string,
>Now suppose I want to find 'AB12ABC' in a string that consists of
>'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
>Now at the moment, I've got a database table consisting of nearly
>380,000 rows.
>I've got indexs placed on the 'plate' column, and I've even tried
>setting up full-text indexing, none of which have increased the speed
>of the query:
>SET NOCOUNT ON
>IF EXISTS (SELECT id
>FROM vclivePlates
>WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
>BEGIN
>SELECT 'Yes'
>END
>ELSE
>BEGIN
>SELECT 'N/A'
>END
>which, at [resent takes around 30 seconds to complete, so running a
>report for say 300 number plates to find if they have been ordered or
>not, takes around 30+ minutes to complete.
>If there another way of doing things that any one can suggest? Apart
>from splitting the strings up?|||You run into performance problem because you store few plates in one
row. If you can modify the database and store each plate in its own
row, then you won't have to use wildcard in the beginning of your
search criteria and the server will be able to use index seek instead
of table scan. If you must use one row to store few plates, then you
can try something else. In your post you said that most plates are in
there own rows and only some rows store more then one plate. If only
small percentage of the rows store few plates, then maybe this will
help - create a computed column on the table that counts the number of
comas in the column that holds the registration plate. Then create an
index on that column. Modify you query so it will look like this:
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
1)
OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
This might cause the server to use the indexes, but it depends on the
number of rows that contain more then one plate.
Adi
pinhead wrote:
> I have problem with the speed of queries.
> I'm searching for UK car number (registration) plates consisting of 2
> letters, followed by 2 numbers, followed by 3 letters.
> ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> Most of these number plates are in their own separate row, but some of
> them are in a comma delimited string,
> Now suppose I want to find 'AB12ABC' in a string that consists of
> 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> Now at the moment, I've got a database table consisting of nearly
> 380,000 rows.
> I've got indexs placed on the 'plate' column, and I've even tried
> setting up full-text indexing, none of which have increased the speed
> of the query:
> SET NOCOUNT ON
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> BEGIN
> SELECT 'Yes'
> END
> ELSE
> BEGIN
> SELECT 'N/A'
> END
> which, at [resent takes around 30 seconds to complete, so running a
> report for say 300 number plates to find if they have been ordered or
> not, takes around 30+ minutes to complete.
> If there another way of doing things that any one can suggest? Apart
> from splitting the strings up?|||Having a major braindead day today, so forgive me for this...
how would I go about counting the number of commas in the data row?
Adi wrote:
> You run into performance problem because you store few plates in one
> row. If you can modify the database and store each plate in its own
> row, then you won't have to use wildcard in the beginning of your
> search criteria and the server will be able to use index seek instead
> of table scan. If you must use one row to store few plates, then you
> can try something else. In your post you said that most plates are in
> there own rows and only some rows store more then one plate. If only
> small percentage of the rows store few plates, then maybe this will
> help - create a computed column on the table that counts the number of
> comas in the column that holds the registration plate. Then create an
> index on that column. Modify you query so it will look like this:
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
> 1)
> OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
>
> This might cause the server to use the indexes, but it depends on the
> number of rows that contain more then one plate.
> Adi
> pinhead wrote:
> > I have problem with the speed of queries.
> >
> > I'm searching for UK car number (registration) plates consisting of 2
> > letters, followed by 2 numbers, followed by 3 letters.
> >
> > ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> >
> > Most of these number plates are in their own separate row, but some of
> > them are in a comma delimited string,
> >
> > Now suppose I want to find 'AB12ABC' in a string that consists of
> > 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> >
> > Now at the moment, I've got a database table consisting of nearly
> > 380,000 rows.
> >
> > I've got indexs placed on the 'plate' column, and I've even tried
> > setting up full-text indexing, none of which have increased the speed
> > of the query:
> >
> > SET NOCOUNT ON
> >
> > IF EXISTS (SELECT id
> > FROM vclivePlates
> > WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> > BEGIN
> > SELECT 'Yes'
> > END
> > ELSE
> > BEGIN
> > SELECT 'N/A'
> > END
> >
> > which, at [resent takes around 30 seconds to complete, so running a
> > report for say 300 number plates to find if they have been ordered or
> > not, takes around 30+ minutes to complete.
> >
> > If there another way of doing things that any one can suggest? Apart
> > from splitting the strings up?|||Sorry -
DATALENGTH(plates) - DATALENGTH(REPLACE(plates, ',', ''))
works well for me
pinhead wrote:
> Having a major braindead day today, so forgive me for this...
> how would I go about counting the number of commas in the data row?
>
> Adi wrote:
> > You run into performance problem because you store few plates in one
> > row. If you can modify the database and store each plate in its own
> > row, then you won't have to use wildcard in the beginning of your
> > search criteria and the server will be able to use index seek instead
> > of table scan. If you must use one row to store few plates, then you
> > can try something else. In your post you said that most plates are in
> > there own rows and only some rows store more then one plate. If only
> > small percentage of the rows store few plates, then maybe this will
> > help - create a computed column on the table that counts the number of
> > comas in the column that holds the registration plate. Then create an
> > index on that column. Modify you query so it will look like this:
> >
> > IF EXISTS (SELECT id
> > FROM vclivePlates
> > WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
> > 1)
> > OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
> >
> >
> > This might cause the server to use the indexes, but it depends on the
> > number of rows that contain more then one plate.
> >
> > Adi
> >
> > pinhead wrote:
> > > I have problem with the speed of queries.
> > >
> > > I'm searching for UK car number (registration) plates consisting of 2
> > > letters, followed by 2 numbers, followed by 3 letters.
> > >
> > > ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> > >
> > > Most of these number plates are in their own separate row, but some of
> > > them are in a comma delimited string,
> > >
> > > Now suppose I want to find 'AB12ABC' in a string that consists of
> > > 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> > >
> > > Now at the moment, I've got a database table consisting of nearly
> > > 380,000 rows.
> > >
> > > I've got indexs placed on the 'plate' column, and I've even tried
> > > setting up full-text indexing, none of which have increased the speed
> > > of the query:
> > >
> > > SET NOCOUNT ON
> > >
> > > IF EXISTS (SELECT id
> > > FROM vclivePlates
> > > WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> > > BEGIN
> > > SELECT 'Yes'
> > > END
> > > ELSE
> > > BEGIN
> > > SELECT 'N/A'
> > > END
> > >
> > > which, at [resent takes around 30 seconds to complete, so running a
> > > report for say 300 number plates to find if they have been ordered or
> > > not, takes around 30+ minutes to complete.
> > >
> > > If there another way of doing things that any one can suggest? Apart
> > > from splitting the strings up?sql

FULL TEXT CATALOGS CANT BE OPEN

I have number of catalogs on my server. when i am clicking on the full text
catalogs section on the enterprise manager the enterprise manager is stucked
and i cant see any catalogs. anyone knows what is the problem? thanks!
Run sp_who2 to see if there is any deadlocking. Also run profiler to
determine what is going on on your system.
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
"Ruby Nadler" <RubyNadler@.discussions.microsoft.com> wrote in message
news:EF872289-8106-4DEA-B7E4-8CC6D16842FF@.microsoft.com...
> I have number of catalogs on my server. when i am clicking on the full
text
> catalogs section on the enterprise manager the enterprise manager is
stucked
> and i cant see any catalogs. anyone knows what is the problem? thanks!

Full backup invisible to the next differential backup

Hi
Given that differential backups are labelled with a base Log Sequence Number
(LSN) as to know which full backup they're related to, and that they store
all changes made after that LSN. I want to know if it's possible to have a
full backup that is not taken into account when a differential backup is
created.
For instance:
1. On Monday a full backup is created
2. On Tuesday another full backup is automatically created by a 3rd-party
application
3. On Wednesday a differential backup is created.
We want this diff backup to ignore the Tuesday full backup and store all
changes since the Monday one.
4. On Thursday the DB crashes and we're able to restore in order:
- Full backup from step 1
- Diff backup from step 3
- All log backups after the diff backup on step 3
The key here is to be able to have something like a disconnected backup that
can be performed by this 3rd-party application, that doesn't interfere on
the regular backup chain.
Any thoughts are welcome.
Thanks!Dallara,
"Dallara" <someone@.microsoft.com> wrote in message
news:ObpEKvh5DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> For instance:
> 1. On Monday a full backup is created
> 2. On Tuesday another full backup is automatically created by a 3rd-party
> application
> 3. On Wednesday a differential backup is created.
> We want this diff backup to ignore the Tuesday full backup and store

---

all

quote:

---

> changes since the Monday one.

---

This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes|||The application has an automated process that
1 - gets exclusive access to the DB
2 - back it up
3 - does whatever needs to be done
if 3 fails the DB is restored, otherwise the backup can be discarded
4 - release exclusive access to DB
The idea is not to interfere with the client maintenance plans and backup
policy (if they have one).
It's basically the same idea of the COPY BACKUP for the NT backup.
"James Hokes" <noemail@.noway.com> wrote in message
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes

quote:

---

> Dallara,
>
> "Dallara" <someone@.microsoft.com> wrote in message
3rd-party[QUOTE]
> all
>

---

|||Why not take the database offline, copy the database files, put the database
back online. See ALTER DATABASE in BOL.
HTH
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dallara" <someone@.microsoft.com> wrote in message
news:Om5pYkj5DHA.2348@.TK2MSFTNGP10.phx.gbl...

quote:

---

> The application has an automated process that
> 1 - gets exclusive access to the DB
> 2 - back it up
> 3 - does whatever needs to be done
> if 3 fails the DB is restored, otherwise the backup can be discarded
> 4 - release exclusive access to DB
> The idea is not to interfere with the client maintenance plans and backup
> policy (if they have one).
> It's basically the same idea of the COPY BACKUP for the NT backup.
>
>
> "James Hokes" <noemail@.noway.com> wrote in message
> This is not possible.
> What's the point of the third party backup if it is disregarded?
> James Hokes
>
> 3rd-party
store[QUOTE]
>

---

Full backup invisible to the next differential backup

Hi
Given that differential backups are labelled with a base Log Sequence Number
(LSN) as to know which full backup they're related to, and that they store
all changes made after that LSN. I want to know if it's possible to have a
full backup that is not taken into account when a differential backup is
created.
For instance:
1. On Monday a full backup is created
2. On Tuesday another full backup is automatically created by a 3rd-party
application
3. On Wednesday a differential backup is created.
We want this diff backup to ignore the Tuesday full backup and store all
changes since the Monday one.
4. On Thursday the DB crashes and we're able to restore in order:
- Full backup from step 1
- Diff backup from step 3
- All log backups after the diff backup on step 3
The key here is to be able to have something like a disconnected backup that
can be performed by this 3rd-party application, that doesn't interfere on
the regular backup chain.
Any thoughts are welcome.
Thanks!Dallara,
"Dallara" <someone@.microsoft.com> wrote in message
news:ObpEKvh5DHA.1636@.TK2MSFTNGP12.phx.gbl...
> For instance:
> 1. On Monday a full backup is created
> 2. On Tuesday another full backup is automatically created by a 3rd-party
> application
> 3. On Wednesday a differential backup is created.
> We want this diff backup to ignore the Tuesday full backup and store
all
> changes since the Monday one.
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes|||The application has an automated process that
1 - gets exclusive access to the DB
2 - back it up
3 - does whatever needs to be done
if 3 fails the DB is restored, otherwise the backup can be discarded
4 - release exclusive access to DB
The idea is not to interfere with the client maintenance plans and backup
policy (if they have one).
It's basically the same idea of the COPY BACKUP for the NT backup.
"James Hokes" <noemail@.noway.com> wrote in message
This is not possible.
What's the point of the third party backup if it is disregarded?
James Hokes
> Dallara,
>
> "Dallara" <someone@.microsoft.com> wrote in message
> > For instance:
> > 1. On Monday a full backup is created
> > 2. On Tuesday another full backup is automatically created by a
3rd-party
> > application
> > 3. On Wednesday a differential backup is created.
> > We want this diff backup to ignore the Tuesday full backup and store
> all
> > changes since the Monday one.
>|||Why not take the database offline, copy the database files, put the database
back online. See ALTER DATABASE in BOL.
HTH
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Dallara" <someone@.microsoft.com> wrote in message
news:Om5pYkj5DHA.2348@.TK2MSFTNGP10.phx.gbl...
> The application has an automated process that
> 1 - gets exclusive access to the DB
> 2 - back it up
> 3 - does whatever needs to be done
> if 3 fails the DB is restored, otherwise the backup can be discarded
> 4 - release exclusive access to DB
> The idea is not to interfere with the client maintenance plans and backup
> policy (if they have one).
> It's basically the same idea of the COPY BACKUP for the NT backup.
>
>
> "James Hokes" <noemail@.noway.com> wrote in message
> This is not possible.
> What's the point of the third party backup if it is disregarded?
> James Hokes
>
> > Dallara,
> >
> >
> > "Dallara" <someone@.microsoft.com> wrote in message
> > > For instance:
> > > 1. On Monday a full backup is created
> > > 2. On Tuesday another full backup is automatically created by a
> 3rd-party
> > > application
> > > 3. On Wednesday a differential backup is created.
> > > We want this diff backup to ignore the Tuesday full backup and
store
> > all
> > > changes since the Monday one.
> >
>

FTS newbie - Searching accross multiple tables using CONTAINSTABLE

Hi all
I have a database with a number of tables with related information. I would
like to do a search across all these tables using AND, OR etc, which means
using CONTAINSTABLE, right?
Using something like:
searchStr = "dog AND cat"
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
I get some results... The problem is, of course, that this means that both
dog and cat have to be in each child table, but I want to get all parent
table rows where dog AND cat appear ANYWHERE in the child tables (eg cat in
one and dog in the other, or both in one child table and none in the other,
and so on). Is this possible? Any suggestions?
This would be used on a search page on an ASP site. The real tables are more
like grandparent - parent - child, child, child.
Thanks a lot in advance!
Marcus
does the key column correspond to the pk of the parent table?
If so, this should work.
|||Thanks for the reply!
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> does the key column correspond to the pk of the parent table?
> If so, this should work.
Yes, the key column corresponds to the pk of the parent, but it doesn't
work. I am testing on some records where all the rows of child1 contain
"cat" and all the rows of child2 contain "dog". If I set the search string
to "cat" I get all the parent rows, like I should. Likewise, I get all rows
if I set the search string to "dog", of course. However, if I set the search
string to "cat AND dog" I get no rows.
This is not particularly surprising to me, since each "child query" executed
independently returns zero keys for "cat AND dog". Am I missing something
here? What can I do?
Here's the query again, for readability:
SELECT a FROM parenttable
WHERE parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
OR parentid IN
(SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
Thank you!
Regards,
Marcus
|||No replies... So I take it this is not possible? Any suggestions on other
solutions?
Thanks,
Marcus
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply!
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:3BEBA99D-395A-4186-AAC6-6F22F7FE9E28@.microsoft.com...
> Yes, the key column corresponds to the pk of the parent, but it doesn't
> work. I am testing on some records where all the rows of child1 contain
> "cat" and all the rows of child2 contain "dog". If I set the search string
> to "cat" I get all the parent rows, like I should. Likewise, I get all
rows
> if I set the search string to "dog", of course. However, if I set the
search
> string to "cat AND dog" I get no rows.
> This is not particularly surprising to me, since each "child query"
executed
> independently returns zero keys for "cat AND dog". Am I missing something
> here? What can I do?
> Here's the query again, for readability:
> SELECT a FROM parenttable
> WHERE parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable1, *, searchStr))
> OR parentid IN
> (SELECT [KEY] FROM CONTAINSTABLE(childtable2, *, searchStr))
> Thank you!
> Regards,
> Marcus
>
|||Marcus,
I had assumed that from your previous "Thanks for the reply" reply that the
[Primary] key (PK) column corresponds to the PK in the parent table and
therefore is solution was viable for you. Is that not correct? If so, then
could you provide more details on your table structures and the real SQL FTS
query?
Thanks,
John
"Marcus" <lumbus@.ludd.luth.se> wrote in message
news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> No replies... So I take it this is not possible? Any suggestions on other
> solutions?
> Thanks,
> Marcus
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uXlNUDROEHA.2876@.TK2MSFTNGP09.phx.gbl...
string[vbcol=seagreen]
> rows
> search
> executed
something
>
|||John,
Thanks for the reply. Either I'm missing something here or there is some
confusion about what I'm looking for. I'll try to explain this very clearly:
My real tables are pretty complicated and I think they will only confuse. I
have made a test database that looks like this:
TABLE [Parent]:
[ParentID] [int] IDENTITY (1, 1) NOT NULL
[Data] [varchar] (100)
TABLE [Child1]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
TABLE [Child2]:
[ParentID] [int] NOT NULL ,
[Data] [varchar] (100)
I then insert the following rows:
TABLE [Parent]:
ParentID=1, Data='dummy'
TABLE [Child1]:
ParentID=1, Data='the dog says woof'
TABLE [Child2]:
ParentID=1, Data='the cat says miau'
Now, remember: What I want to do is to search for "dog AND cat" in this
structure, returning the IDs for the parent rows where the words "cat and
"dog" appear anywhere in the tables, for example "cat" in Child1 and "dog"
in Child2.
This query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat AND dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat AND dog'))
...returns nothing. This is the problem I'm asking about.
Just to show that the tables are healthy, this query...
SELECT ParentID FROM Parent
WHERE ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog'))
OR ParentID IN
(SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog'))
...returns ParentID=1. And these queries...
SELECT [KEY] FROM CONTAINSTABLE(Child1, *, 'cat OR dog')
SELECT [KEY] FROM CONTAINSTABLE(Child2, *, 'cat OR dog')
...return KEY=1.
So the question is whether there is a solution to this or whether I should
give up on using FTS for my search functionality.
Thank you!
Regards,
Marcus
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Op2OC3gPEHA.3524@.TK2MSFTNGP09.phx.gbl...
> Marcus,
> I had assumed that from your previous "Thanks for the reply" reply that
the
> [Primary] key (PK) column corresponds to the PK in the parent table and
> therefore is solution was viable for you. Is that not correct? If so, then
> could you provide more details on your table structures and the real SQL
FTS[vbcol=seagreen]
> query?
> Thanks,
> John
>
> "Marcus" <lumbus@.ludd.luth.se> wrote in message
> news:uBT$PwdPEHA.4036@.TK2MSFTNGP12.phx.gbl...
other[vbcol=seagreen]
doesn't[vbcol=seagreen]
contain
> string
> something
>