Eliminating the expensive remote join

I've got a table (dexIndex) with 9 million rows in it. It's got an
identity column (index_id) and a column that's full-text indexed
(insureds_name) and a column that I want to filter on (agency_number).
When I query like this:
select * from decIndex where agency_number = '0349323' and
contains(insureds_name,'"SMITH"')
The execution plan involves a remote scan (98%) and a clustered index
seek (0%) which lead into a hash match/inner join (2%). The problem is
that the remote scan is returning 51,000 rows even though only 1 comes
out of the hash match/inner join. Thus, the query takes over a minute
on a built-to-the-hilt server.
Is there a way to have the FT engine only look through or return a
subset of the FT index, like only those rows which correspond to
agency_number = '0349323'?
I'd like to avoid adding agency_number to the FT index.
Thanks!
Regretably there is no way of doing this. One of the strategies of getting
around this problem is to partition your table according to the other
elements of you where clause. Another option is to build full text indexes
on indexed views in SQL 2005. Again the view would be of a section of your
table which would match your where conditions.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:%233kbwJZJFHA.588@.TK2MSFTNGP15.phx.gbl...
> I've got a table (dexIndex) with 9 million rows in it. It's got an
> identity column (index_id) and a column that's full-text indexed
> (insureds_name) and a column that I want to filter on (agency_number).
> When I query like this:
> select * from decIndex where agency_number = '0349323' and
> contains(insureds_name,'"SMITH"')
> The execution plan involves a remote scan (98%) and a clustered index seek
> (0%) which lead into a hash match/inner join (2%). The problem is that
> the remote scan is returning 51,000 rows even though only 1 comes out of
> the hash match/inner join. Thus, the query takes over a minute on a
> built-to-the-hilt server.
> Is there a way to have the FT engine only look through or return a subset
> of the FT index, like only those rows which correspond to agency_number =
> '0349323'?
> I'd like to avoid adding agency_number to the FT index.
> Thanks!
|||Hilary Cotter wrote:
> Regretably there is no way of doing this. One of the strategies of getting
> around this problem is to partition your table according to the other
> elements of you where clause. Another option is to build full text indexes
> on indexed views in SQL 2005. Again the view would be of a section of your
> table which would match your where conditions.
I ended up clustering the index that corresponded to the unique column
for the full-text index and that sped it up a great deal.
Thanks,
Ryan

Effect of changing the DBID

I already know that changing the DBID by detaching and re-attaching a
database messes up the full-text catalog, but does it have any other adverse
effects? Do jobs, logins, logins, or anything else depend on the DBID?
Thanks,
Boris Zakharin
Prime Associates, inc
Hi,
As far as I know, There should not be any other issues. The job step stores
the database name
and logins also store the default database name for the login. So no issues
with database id changing.
Thanks
Hari
MCDBA
"Boris Zakharin" <bzakharin@.primeassociates.com> wrote in message
news:#FUWnEnbEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I already know that changing the DBID by detaching and re-attaching a
> database messes up the full-text catalog, but does it have any other
adverse
> effects? Do jobs, logins, logins, or anything else depend on the DBID?
> Thanks,
> Boris Zakharin
> Prime Associates, inc
>

Effect of changing the DBID

I already know that changing the DBID by detaching and re-attaching a
database messes up the full-text catalog, but does it have any other adverse
effects? Do jobs, logins, logins, or anything else depend on the DBID?
Thanks,
Boris Zakharin
Prime Associates, incHi,
As far as I know, There should not be any other issues. The job step stores
the database name
and logins also store the default database name for the login. So no issues
with database id changing.
Thanks
Hari
MCDBA
"Boris Zakharin" <bzakharin@.primeassociates.com> wrote in message
news:#FUWnEnbEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I already know that changing the DBID by detaching and re-attaching a
> database messes up the full-text catalog, but does it have any other
adverse
> effects? Do jobs, logins, logins, or anything else depend on the DBID?
> Thanks,
> Boris Zakharin
> Prime Associates, inc
>

Effect of changing the DBID

I already know that changing the DBID by detaching and re-attaching a
database messes up the full-text catalog, but does it have any other adverse
effects? Do jobs, logins, logins, or anything else depend on the DBID?
Thanks,
Boris Zakharin
Prime Associates, incHi,
As far as I know, There should not be any other issues. The job step stores
the database name
and logins also store the default database name for the login. So no issues
with database id changing.
Thanks
Hari
MCDBA
"Boris Zakharin" <bzakharin@.primeassociates.com> wrote in message
news:#FUWnEnbEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I already know that changing the DBID by detaching and re-attaching a
> database messes up the full-text catalog, but does it have any other
adverse
> effects? Do jobs, logins, logins, or anything else depend on the DBID?
> Thanks,
> Boris Zakharin
> Prime Associates, inc
>