Indexing Results of Stored Proc (or new table created by one)

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • HumanJHawkins
    #1

    Indexing Results of Stored Proc (or new table created by one)

    Hi,

    I am using data from multiple databases and/or queries. It would greatly
    simplify and speed things up if I could use CONTAINS in processing the
    results. However, "CONTAINS" requires the data to be indexed. Due to the
    amount of processing, I think it would be faster even if I had to re-index
    every time.

    For example, I would like to do something like this (simplified to
    illustrate the desired functionality.. . This should show all of the words
    from one table that are not contained in their current or inflectional forms
    within another table):

    SELECT W1.Content
    FROM
    (SELECT Word AS Content
    FROM MyTable) W1
    LEFT OUTER JOIN
    (SELECT Phrase AS Content
    FROM MyOtherTable) W2
    ON W2.Content CONTAINS(INFLEC TIONAL, W1.Content)
    WHERE W2.Content IS NULL

    Can the results of a procedure be indexed? If not, can I drop the results
    into a new table and trigger an automatic index of it, pausing the procedure
    until the indexing is done?

    Or, it there another way?

    Thanks!


    Tags: None
    • Simon Hayes
      #2
      Re: Indexing Results of Stored Proc (or new table created by one)


      "HumanJHawk ins" <JHawkins@Human itiesSoftware.C om> wrote in message
      news:sRhdc.1459 $k05.510@newsre ad2.news.pas.ea rthlink.net...[color=blue]
      > Hi,
      >
      > I am using data from multiple databases and/or queries. It would greatly
      > simplify and speed things up if I could use CONTAINS in processing the
      > results. However, "CONTAINS" requires the data to be indexed. Due to the
      > amount of processing, I think it would be faster even if I had to re-index
      > every time.
      >
      > For example, I would like to do something like this (simplified to
      > illustrate the desired functionality.. . This should show all of the words
      > from one table that are not contained in their current or inflectional[/color]
      forms[color=blue]
      > within another table):
      >
      > SELECT W1.Content
      > FROM
      > (SELECT Word AS Content
      > FROM MyTable) W1
      > LEFT OUTER JOIN
      > (SELECT Phrase AS Content
      > FROM MyOtherTable) W2
      > ON W2.Content CONTAINS(INFLEC TIONAL, W1.Content)
      > WHERE W2.Content IS NULL
      >
      > Can the results of a procedure be indexed? If not, can I drop the results
      > into a new table and trigger an automatic index of it, pausing the[/color]
      procedure[color=blue]
      > until the indexing is done?
      >
      > Or, it there another way?
      >
      > Thanks!
      >
      >[/color]

      You may be able to use CONTAINSTABLE() instead, as it returns a table, which
      you can then join on. But I'm not really familiar with it -
      microsoft.publi c.sqlserver.ful ltext may be a better place to ask.

      As a general answer, you could start fulltext indexing on a table from
      within a stored procedure, but that's likely to be very slow and there may
      be security implications as well.

      Simon


        Comment

        • Simon Hayes
          #3
          Re: Indexing Results of Stored Proc (or new table created by one)


          "HumanJHawk ins" <JHawkins@Human itiesSoftware.C om> wrote in message
          news:sRhdc.1459 $k05.510@newsre ad2.news.pas.ea rthlink.net...[color=blue]
          > Hi,
          >
          > I am using data from multiple databases and/or queries. It would greatly
          > simplify and speed things up if I could use CONTAINS in processing the
          > results. However, "CONTAINS" requires the data to be indexed. Due to the
          > amount of processing, I think it would be faster even if I had to re-index
          > every time.
          >
          > For example, I would like to do something like this (simplified to
          > illustrate the desired functionality.. . This should show all of the words
          > from one table that are not contained in their current or inflectional[/color]
          forms[color=blue]
          > within another table):
          >
          > SELECT W1.Content
          > FROM
          > (SELECT Word AS Content
          > FROM MyTable) W1
          > LEFT OUTER JOIN
          > (SELECT Phrase AS Content
          > FROM MyOtherTable) W2
          > ON W2.Content CONTAINS(INFLEC TIONAL, W1.Content)
          > WHERE W2.Content IS NULL
          >
          > Can the results of a procedure be indexed? If not, can I drop the results
          > into a new table and trigger an automatic index of it, pausing the[/color]
          procedure[color=blue]
          > until the indexing is done?
          >
          > Or, it there another way?
          >
          > Thanks!
          >
          >[/color]

          You may be able to use CONTAINSTABLE() instead, as it returns a table, which
          you can then join on. But I'm not really familiar with it -
          microsoft.publi c.sqlserver.ful ltext may be a better place to ask.

          As a general answer, you could start fulltext indexing on a table from
          within a stored procedure, but that's likely to be very slow and there may
          be security implications as well.

          Simon


            Comment

            Previous template Next
            Working...