Re: Difficulty in implementing

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 10/26/04 

Date: Mon, 25 Oct 2004 20:41:44 -0400

The textual content in the parent table will make the "table wider". If this
textual column is involved in other queries, storing it in a child table
will require the use of a join and you will not get performance
improvements. However, if this textual column is not used in most queries or
to resolve queries, you will get better performance by storing it in a child
table, as your remaining parent table will be considerably narrower and
hence more rows can be stored per database page. Consequently SQL Server
will have to go to disk less to return pages.

Also when you are doing a ContainsTable or FreeTextTable query, you can join
on the parent table and not have to query the child table directly at all.
Depending on the number of rows and the size of your tables the performance
improvement can by huge.

If everytime you need to display a record you have to return the contents on
this field, it probably should be in the parent.

You will have to test to see what works best for you. In my experience on
most of the search applications I have worked on, splitting your textual
data which you are indexing into a seperate table is a must. Your results
may vary.

"Shabam" <blislecp@hotmail.com> wrote in message
news:vIidnfVIBYAy5eDcRVn-3A@adelphia.com...
> > First you need to create a catalog and build the index for the column
you
> > wish to index.
> >
> > Then you add a contains or freetext clause to your where query, ie:
> >
> > select col1, col2, col3 from tablename where contains(IndexedColumnName,
> > 'SearchPhrase').
> >
> > For performance reasons you will want to limit your results set and use
> > ContainsTable, and possibly put your data you are indexing in a child
> table.
>
> Why should the data be put in a child table? How does that help with
> performance?
>
> Right now, I have a "Users" table. It contains various profile fields.
> Some of the fields contain comma-delimited list items (hobbies, separated
by
> commas). When viewing the user's profile, these hobby items are displayed
> and linked to a search, item by item.
>
> I suggested to the programmer it would be much better to put the
individual
> hobby items in a separate table, linked with an index of the user ID.
> However he's saying it would cause more overhead during the display of the
> user profile page. What are your thoughts? (I know this is a tangeant
from
> the original question).
>
>
>

Relevant Pages

  • Safely timing out DBI queries
    ... plenty of data and hence plenty of rope. ... themselves by timing-out long-running queries and killing the MySQL ... Parent forks a child before starting long-running query. ...
    (perl.dbi.users)
  • Re: How do I empty a table?
    ... You write a series of queries that delete data from each table. ... Delete the records in any child tables before you delete the records in any ... parent tables. ... Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia ...
    (microsoft.public.access.gettingstarted)
  • Please help
    ... LotId can have 1-8 components). ... Both parent and child ... When I run queries on ... it best to copy that information from parent to child lot. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Linking 4 child tables to 1 Parent table
    ... There are situations in which keeping 1-1 "child" tables makes more sense. ... separate tables will probably be OK. ... I'm not clear which queries you are referring to, but I do know that you can ... right now they are linked to the key field of the parent, ...
    (microsoft.public.access.tablesdbdesign)
  • Unix Programming FAQ (v1.37)
    ... Why use _exit rather than exit in the child branch of a fork? ... Why doesn't my process get SIGHUP when its parent dies? ... How do I create a named pipe? ... How do I compare strings using regular expressions? ...
    (comp.unix.programmer)