Loading ...
Sorry, an error occurred while loading the content.

Fastest way to search data from table ..

Expand Messages
  • ☺Aνιηαѕн ∂єѕαι™☺
    Hi All I have a table where it has 45000 Rows of data and a Column Named Description will be used to search Just if user enters the Text like this * Bailed out
    Message 1 of 16 , Aug 7 2:45 AM
    • 0 Attachment
      Hi All

      I have a table where it has 45000 Rows of data and a Column Named
      Description will be used to search

      Just if user enters the Text like this *"Bailed out With"*
      Where i split the String and will build the Query String

      and Query will be like this Select * from CaseList where Description
      Like'%bailed%' or description like '%Out%' or description Like '%With%'

      This above Query Takes 15.6 Minutes to search data

      I think this can be solved by FullText Index search But i am using SQL
      Express...???

      Can any one tell me what to do to search data very fast??? please let me
      know as soon as possible..


      --
      Thanks & Regards
      Avinash Desai
      *SAVE Nature SAVE Tree SAVE*

      Think Before you print: Please consider our environment before printing this
      e-mail

      Charles de Gaulle<http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
      - "The better I get to know men, the more I find myself loving dogs."


      [Non-text portions of this message have been removed]
    • PogoWolf
      If I m reading this correctly.. Your query is written thusly: SELECT * FROM CaseList WHERE Description Like %bailed% OR description like %Out% OR
      Message 2 of 16 , Aug 7 4:51 AM
      • 0 Attachment
        If I'm reading this correctly.. Your query is written thusly:
        SELECT *
        FROM CaseList
        WHERE Description Like'%bailed%'
        OR description like '%Out%'
        OR description Like '%With%'

        This query will return ALL rows with the keyword of 'Bailed', 'out', and
        'with'
        is this what you are looking for? Or do you only want records where the
        description has the keywords in the record?

        If you are looking for those 3 keywords in the description column then
        remove the OR statements.
        so the query would be:

        SELECT *
        FROM CaseList
        WHERE Description Like '%bailed%Out%With%'

        if not, than look into how you have your table indexes set up. You can also
        twike the query out by removing the '*' and replacing it with all the column
        names that you need to return.
        like:

        SELECT Name, Vehicle, Description, ReasonCode
        FROM CaseList
        WHERE Description Like '%bailed%Out%With%'

        hope this helps! :)



        ---===/// The PogoWolf \\\===---
        http://www.GamersVue.com (Video Game News and Information)



        2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...>

        >
        >
        > Hi All
        >
        > I have a table where it has 45000 Rows of data and a Column Named
        > Description will be used to search
        >
        > Just if user enters the Text like this *"Bailed out With"*
        > Where i split the String and will build the Query String
        >
        > and Query will be like this Select * from CaseList where Description
        > Like'%bailed%' or description like '%Out%' or description Like '%With%'
        >
        > This above Query Takes 15.6 Minutes to search data
        >
        > I think this can be solved by FullText Index search But i am using SQL
        > Express...???
        >
        > Can any one tell me what to do to search data very fast??? please let me
        > know as soon as possible..
        >
        > --
        > Thanks & Regards
        > Avinash Desai
        > *SAVE Nature SAVE Tree SAVE*
        >
        > Think Before you print: Please consider our environment before printing
        > this
        > e-mail
        >
        > Charles de Gaulle<
        > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
        > - "The better I get to know men, the more I find myself loving dogs."
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >


        [Non-text portions of this message have been removed]
      • Noman Aftab
        Hi all, I have a table named Asset having columns: Asset_Id, Asset_Symbol, Asset_Name, etc. I got an excel sheet from the client as a change to merge few
        Message 3 of 16 , Aug 7 4:57 AM
        • 0 Attachment
          Hi all,
          I have a table named Asset having columns: Asset_Id, Asset_Symbol, Asset_Name, etc.
          I got an excel sheet from the client as a change to merge few assets (actually duplicates).
          The table Asset is referenced by many tables (by foriegn keys).

          n.b. SQL Server 2008 is the database

          For e.g. we have data like (in excel):
          1 [DHAFRA] [AL DHAFRA INSURANCE]
          2 [DAFRA] [AL DHAFRA INSURANCE CO.]
          ...
          ...
          almost 20 duplicates

          I was about to create a temp table with columns: CORRECT_SYMBOL, WRONG_SYMBOL, CORRECT_SYMBOL_ASSET_ID, WRONG_SYMBOL_ASSET_ID.
          Assuming 1st row is correct and 2nd is wrong, and then importing the excel file to this temp table.

          Please provide some suggestions on how to update the referenced tables upon/bofore/after deletion of the duplicate record.

          What I actually need is to:
          1. get the list of all tables which are referening Asset table
          2. for each referencing table
          2a. enable (cascade on update on the table)
          2b. update key (for e.g. update asset_id=1 in referencing table where asset_id=2)
          2c. disable (cascade on update on the table)
          3. delete duplicate record


          Best Wishes,
          Noman Aftab

           
          http://www.jalandhari.qsh.eu 




          [Non-text portions of this message have been removed]
        • ☺Aνιηαѕн ∂єѕαι™☺
          Hi PogoWolf Thanks for reply you are correct . I want to search the DESCRIPTION column where Either it can have bailed or Out or With Text init.. With My Query
          Message 4 of 16 , Aug 7 6:35 AM
          • 0 Attachment
            Hi PogoWolf

            Thanks for reply you are correct .
            I want to search the DESCRIPTION column where Either it can have bailed or
            Out or With Text init..

            With My Query it takes 16 minutes to return data and as what you said the
            query


            SELECT *
            FROM CaseList
            WHERE Description Like '%bailed%Out%With%'

            Even it takes the same time and I need to Display all the Column from the
            Table so i used '*'..

            IF any other method please let me know. I need to search the Description
            which contains *Bailed *or *Out *Or *with *Texts.

            Regards
            Avinash



            On Fri, Aug 7, 2009 at 5:21 PM, PogoWolf <pogowolf@...> wrote:

            >
            >
            > If I'm reading this correctly.. Your query is written thusly:
            > SELECT *
            > FROM CaseList
            > WHERE Description Like'%bailed%'
            > OR description like '%Out%'
            > OR description Like '%With%'
            >
            > This query will return ALL rows with the keyword of 'Bailed', 'out', and
            > 'with'
            > is this what you are looking for? Or do you only want records where the
            > description has the keywords in the record?
            >
            > If you are looking for those 3 keywords in the description column then
            > remove the OR statements.
            > so the query would be:
            >
            > SELECT *
            > FROM CaseList
            > WHERE Description Like '%bailed%Out%With%'
            >
            > if not, than look into how you have your table indexes set up. You can also
            > twike the query out by removing the '*' and replacing it with all the
            > column
            > names that you need to return.
            > like:
            >
            > SELECT Name, Vehicle, Description, ReasonCode
            > FROM CaseList
            > WHERE Description Like '%bailed%Out%With%'
            >
            > hope this helps! :)
            >
            > ---===/// The PogoWolf \\\===---
            > http://www.GamersVue.com (Video Game News and Information)
            >
            > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...<avinashdesai10%40gmail.com>
            > >
            >
            >
            > >
            > >
            > > Hi All
            > >
            > > I have a table where it has 45000 Rows of data and a Column Named
            > > Description will be used to search
            > >
            > > Just if user enters the Text like this *"Bailed out With"*
            > > Where i split the String and will build the Query String
            > >
            > > and Query will be like this Select * from CaseList where Description
            > > Like'%bailed%' or description like '%Out%' or description Like '%With%'
            > >
            > > This above Query Takes 15.6 Minutes to search data
            > >
            > > I think this can be solved by FullText Index search But i am using SQL
            > > Express...???
            > >
            > > Can any one tell me what to do to search data very fast??? please let me
            > > know as soon as possible..
            > >
            > > --
            > > Thanks & Regards
            > > Avinash Desai
            > > *SAVE Nature SAVE Tree SAVE*
            > >
            > > Think Before you print: Please consider our environment before printing
            > > this
            > > e-mail
            > >
            > > Charles de Gaulle<
            > > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
            > > - "The better I get to know men, the more I find myself loving dogs."
            > >
            > > [Non-text portions of this message have been removed]
            > >
            > >
            > >
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >



            --
            Thanks & Regards
            Avinash Desai
            *SAVE Nature SAVE Tree SAVE*

            Think Before you print: Please consider our environment before printing this
            e-mail

            Ogden Nash <http://www.brainyquote.com/quotes/authors/o/ogden_nash.html> -
            "The trouble with a kitten is that when it grows up, it's always a cat."


            [Non-text portions of this message have been removed]
          • PogoWolf
            Well listing out the columns won t shave that much off the query, just makes for some cleaner code and adheres to T-SQL standards. :) as for the query you ll
            Message 5 of 16 , Aug 7 7:03 AM
            • 0 Attachment
              Well listing out the columns won't shave that much off the query, just makes
              for some cleaner code and adheres to T-SQL standards. :) as for the query
              you'll need to go back to the other version, to make sure you are pulling
              the description from a record where ANY of the keywords match.
              so:
              SELECT Description
              FROM CaseList
              WHERE Description LIKE '%bailed%'
              OR Description LIKE '%Out%'
              OR Description LIKE '%With%'

              The only other suggestion I would have would be to verify your index's
              and/or make sure your tables are set up in the most efficient way possible.
              You should only need to worry about the tables if your 'CaseList' table
              is pulling data from other tables. If not, then it's all about the table
              Index's and the memory and speed of the computer that's running the query.

              The nature of that type of search is just slow.. Worst since you are
              querying on 3 or more keywords. Indexing the correct column (play around
              until you get the fastest time) will help a lot. Also putting the database
              on faster computer or moving the database to a database server can speed up
              the query.


              ---===/// The PogoWolf \\\===---
              http://www.GamersVue.com (Video Game News and Information)



              2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...>

              >
              >
              > Hi PogoWolf
              >
              > Thanks for reply you are correct .
              > I want to search the DESCRIPTION column where Either it can have bailed or
              > Out or With Text init..
              >
              > With My Query it takes 16 minutes to return data and as what you said the
              > query
              >
              >
              > SELECT *
              > FROM CaseList
              > WHERE Description Like '%bailed%Out%With%'
              >
              > Even it takes the same time and I need to Display all the Column from the
              > Table so i used '*'..
              >
              > IF any other method please let me know. I need to search the Description
              > which contains *Bailed *or *Out *Or *with *Texts.
              >
              > Regards
              > Avinash
              >
              >
              > On Fri, Aug 7, 2009 at 5:21 PM, PogoWolf <pogowolf@...<pogowolf%40gmail.com>>
              > wrote:
              >
              > >
              > >
              > > If I'm reading this correctly.. Your query is written thusly:
              > > SELECT *
              > > FROM CaseList
              > > WHERE Description Like'%bailed%'
              > > OR description like '%Out%'
              > > OR description Like '%With%'
              > >
              > > This query will return ALL rows with the keyword of 'Bailed', 'out', and
              > > 'with'
              > > is this what you are looking for? Or do you only want records where the
              > > description has the keywords in the record?
              > >
              > > If you are looking for those 3 keywords in the description column then
              > > remove the OR statements.
              > > so the query would be:
              > >
              > > SELECT *
              > > FROM CaseList
              > > WHERE Description Like '%bailed%Out%With%'
              > >
              > > if not, than look into how you have your table indexes set up. You can
              > also
              > > twike the query out by removing the '*' and replacing it with all the
              > > column
              > > names that you need to return.
              > > like:
              > >
              > > SELECT Name, Vehicle, Description, ReasonCode
              > > FROM CaseList
              > > WHERE Description Like '%bailed%Out%With%'
              > >
              > > hope this helps! :)
              > >
              > > ---===/// The PogoWolf \\\===---
              > > http://www.GamersVue.com (Video Game News and Information)
              > >
              > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...<avinashdesai10%40gmail.com>
              > <avinashdesai10%40gmail.com>
              >
              > > >
              > >
              > >
              > > >
              > > >
              > > > Hi All
              > > >
              > > > I have a table where it has 45000 Rows of data and a Column Named
              > > > Description will be used to search
              > > >
              > > > Just if user enters the Text like this *"Bailed out With"*
              > > > Where i split the String and will build the Query String
              > > >
              > > > and Query will be like this Select * from CaseList where Description
              > > > Like'%bailed%' or description like '%Out%' or description Like '%With%'
              > > >
              > > > This above Query Takes 15.6 Minutes to search data
              > > >
              > > > I think this can be solved by FullText Index search But i am using SQL
              > > > Express...???
              > > >
              > > > Can any one tell me what to do to search data very fast??? please let
              > me
              > > > know as soon as possible..
              > > >
              > > > --
              > > > Thanks & Regards
              > > > Avinash Desai
              > > > *SAVE Nature SAVE Tree SAVE*
              > > >
              > > > Think Before you print: Please consider our environment before printing
              > > > this
              > > > e-mail
              > > >
              > > > Charles de Gaulle<
              > > > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
              > > > - "The better I get to know men, the more I find myself loving dogs."
              > > >
              > > > [Non-text portions of this message have been removed]
              > > >
              > > >
              > > >
              > >
              > > [Non-text portions of this message have been removed]
              > >
              > >
              > >
              >
              > --
              > Thanks & Regards
              > Avinash Desai
              > *SAVE Nature SAVE Tree SAVE*
              >
              > Think Before you print: Please consider our environment before printing
              > this
              > e-mail
              >
              > Ogden Nash <http://www.brainyquote.com/quotes/authors/o/ogden_nash.html> -
              > "The trouble with a kitten is that when it grows up, it's always a cat."
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >


              [Non-text portions of this message have been removed]
            • ☺Aνιηαѕн ∂єѕαι™☺
              But Although my PC has 1GB ram and 500GB HDD .. and Its Taking So much time.. So is it possible to use Full text index search In SQL EXPRESS if yes then how
              Message 6 of 16 , Aug 7 7:11 AM
              • 0 Attachment
                But Although my PC has 1GB ram and 500GB HDD .. and Its Taking So much
                time..

                So is it possible to use Full text index search In SQL EXPRESS if yes then
                how can i use it???

                [?][?][?][?][?][?]



                On Fri, Aug 7, 2009 at 7:33 PM, PogoWolf <pogowolf@...> wrote:

                >
                >
                > Well listing out the columns won't shave that much off the query, just
                > makes
                > for some cleaner code and adheres to T-SQL standards. :) as for the query
                > you'll need to go back to the other version, to make sure you are pulling
                > the description from a record where ANY of the keywords match.
                > so:
                > SELECT Description
                > FROM CaseList
                > WHERE Description LIKE '%bailed%'
                > OR Description LIKE '%Out%'
                > OR Description LIKE '%With%'
                >
                > The only other suggestion I would have would be to verify your index's
                > and/or make sure your tables are set up in the most efficient way possible.
                > You should only need to worry about the tables if your 'CaseList' table
                > is pulling data from other tables. If not, then it's all about the table
                > Index's and the memory and speed of the computer that's running the query.
                >
                > The nature of that type of search is just slow.. Worst since you are
                > querying on 3 or more keywords. Indexing the correct column (play around
                > until you get the fastest time) will help a lot. Also putting the database
                > on faster computer or moving the database to a database server can speed up
                > the query.
                >
                >
                > ---===/// The PogoWolf \\\===---
                > http://www.GamersVue.com (Video Game News and Information)
                >
                > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...<avinashdesai10%40gmail.com>
                > >
                >
                > >
                > >
                > > Hi PogoWolf
                > >
                > > Thanks for reply you are correct .
                > > I want to search the DESCRIPTION column where Either it can have bailed
                > or
                > > Out or With Text init..
                > >
                > > With My Query it takes 16 minutes to return data and as what you said the
                > > query
                > >
                > >
                > > SELECT *
                > > FROM CaseList
                > > WHERE Description Like '%bailed%Out%With%'
                > >
                > > Even it takes the same time and I need to Display all the Column from the
                > > Table so i used '*'..
                > >
                > > IF any other method please let me know. I need to search the Description
                > > which contains *Bailed *or *Out *Or *with *Texts.
                > >
                > > Regards
                > > Avinash
                > >
                > >
                > > On Fri, Aug 7, 2009 at 5:21 PM, PogoWolf <pogowolf@...<pogowolf%40gmail.com>
                > <pogowolf%40gmail.com>>
                >
                > > wrote:
                > >
                > > >
                > > >
                > > > If I'm reading this correctly.. Your query is written thusly:
                > > > SELECT *
                > > > FROM CaseList
                > > > WHERE Description Like'%bailed%'
                > > > OR description like '%Out%'
                > > > OR description Like '%With%'
                > > >
                > > > This query will return ALL rows with the keyword of 'Bailed', 'out',
                > and
                > > > 'with'
                > > > is this what you are looking for? Or do you only want records where the
                > > > description has the keywords in the record?
                > > >
                > > > If you are looking for those 3 keywords in the description column then
                > > > remove the OR statements.
                > > > so the query would be:
                > > >
                > > > SELECT *
                > > > FROM CaseList
                > > > WHERE Description Like '%bailed%Out%With%'
                > > >
                > > > if not, than look into how you have your table indexes set up. You can
                > > also
                > > > twike the query out by removing the '*' and replacing it with all the
                > > > column
                > > > names that you need to return.
                > > > like:
                > > >
                > > > SELECT Name, Vehicle, Description, ReasonCode
                > > > FROM CaseList
                > > > WHERE Description Like '%bailed%Out%With%'
                > > >
                > > > hope this helps! :)
                > > >
                > > > ---===/// The PogoWolf \\\===---
                > > > http://www.GamersVue.com (Video Game News and Information)
                > > >
                > > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...<avinashdesai10%40gmail.com>
                > <avinashdesai10%40gmail.com>
                > > <avinashdesai10%40gmail.com>
                > >
                > > > >
                > > >
                > > >
                > > > >
                > > > >
                > > > > Hi All
                > > > >
                > > > > I have a table where it has 45000 Rows of data and a Column Named
                > > > > Description will be used to search
                > > > >
                > > > > Just if user enters the Text like this *"Bailed out With"*
                > > > > Where i split the String and will build the Query String
                > > > >
                > > > > and Query will be like this Select * from CaseList where Description
                > > > > Like'%bailed%' or description like '%Out%' or description Like
                > '%With%'
                > > > >
                > > > > This above Query Takes 15.6 Minutes to search data
                > > > >
                > > > > I think this can be solved by FullText Index search But i am using
                > SQL
                > > > > Express...???
                > > > >
                > > > > Can any one tell me what to do to search data very fast??? please let
                > > me
                > > > > know as soon as possible..
                > > > >
                > > > > --
                > > > > Thanks & Regards
                > > > > Avinash Desai
                > > > > *SAVE Nature SAVE Tree SAVE*
                > > > >
                > > > > Think Before you print: Please consider our environment before
                > printing
                > > > > this
                > > > > e-mail
                > > > >
                > > > > Charles de Gaulle<
                > > > > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
                > > > > - "The better I get to know men, the more I find myself loving dogs."
                > > > >
                > > > > [Non-text portions of this message have been removed]
                > > > >
                > > > >
                > > > >
                > > >
                > > > [Non-text portions of this message have been removed]
                > > >
                > > >
                > > >
                > >
                > > --
                > > Thanks & Regards
                > > Avinash Desai
                > > *SAVE Nature SAVE Tree SAVE*
                > >
                > > Think Before you print: Please consider our environment before printing
                > > this
                > > e-mail
                > >
                > > Ogden Nash <http://www.brainyquote.com/quotes/authors/o/ogden_nash.html>
                > -
                > > "The trouble with a kitten is that when it grows up, it's always a cat."
                > >
                > > [Non-text portions of this message have been removed]
                > >
                > >
                > >
                >
                > [Non-text portions of this message have been removed]
                >
                >
                >



                --
                Thanks & Regards
                Avinash Desai
                *SAVE Nature SAVE Tree SAVE*

                Think Before you print: Please consider our environment before printing this
                e-mail
                Sent from Bangalore, KA, India
                Charles de Gaulle<http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
                - "The better I get to know men, the more I find myself loving dogs."


                [Non-text portions of this message have been removed]
              • John Warner
                First have a look at this article: http://www.wherecanibuyit.co.uk/ASP/full-text-search.html John Warner
                Message 7 of 16 , Aug 7 7:14 AM
                • 0 Attachment
                  First have a look at this article:
                  http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of PogoWolf
                  > Sent: Friday, August 07, 2009 10:04 AM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..
                  >
                  > Well listing out the columns won't shave that much off the query, just makes
                  > for some cleaner code and adheres to T-SQL standards. :) as for the query
                  > you'll need to go back to the other version, to make sure you are pulling
                  > the description from a record where ANY of the keywords match.
                  > so:
                  > SELECT Description
                  > FROM CaseList
                  > WHERE Description LIKE '%bailed%'
                  > OR Description LIKE '%Out%'
                  > OR Description LIKE '%With%'
                  >
                  > The only other suggestion I would have would be to verify your index's
                  > and/or make sure your tables are set up in the most efficient way possible.
                  > You should only need to worry about the tables if your 'CaseList' table
                  > is pulling data from other tables. If not, then it's all about the table
                  > Index's and the memory and speed of the computer that's running the query.
                  >
                  > The nature of that type of search is just slow.. Worst since you are
                  > querying on 3 or more keywords. Indexing the correct column (play around
                  > until you get the fastest time) will help a lot. Also putting the database
                  > on faster computer or moving the database to a database server can speed up
                  > the query.
                  >
                  >
                  > ---===/// The PogoWolf \\\===---
                  > http://www.GamersVue.com (Video Game News and Information)
                  >
                  >
                  >
                  > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...>
                  >
                  > >
                  > >
                  > > Hi PogoWolf
                  > >
                  > > Thanks for reply you are correct .
                  > > I want to search the DESCRIPTION column where Either it can have bailed
                  > or
                  > > Out or With Text init..
                  > >
                  > > With My Query it takes 16 minutes to return data and as what you said the
                  > > query
                  > >
                  > >
                  > > SELECT *
                  > > FROM CaseList
                  > > WHERE Description Like '%bailed%Out%With%'
                  > >
                  > > Even it takes the same time and I need to Display all the Column from the
                  > > Table so i used '*'..
                  > >
                  > > IF any other method please let me know. I need to search the Description
                  > > which contains *Bailed *or *Out *Or *with *Texts.
                  > >
                  > > Regards
                  > > Avinash
                  > >
                  > >
                  > > On Fri, Aug 7, 2009 at 5:21 PM, PogoWolf
                  > <pogowolf@...<pogowolf%40gmail.com>>
                  > > wrote:
                  > >
                  > > >
                  > > >
                  > > > If I'm reading this correctly.. Your query is written thusly:
                  > > > SELECT *
                  > > > FROM CaseList
                  > > > WHERE Description Like'%bailed%'
                  > > > OR description like '%Out%'
                  > > > OR description Like '%With%'
                  > > >
                  > > > This query will return ALL rows with the keyword of 'Bailed', 'out', and
                  > > > 'with'
                  > > > is this what you are looking for? Or do you only want records where the
                  > > > description has the keywords in the record?
                  > > >
                  > > > If you are looking for those 3 keywords in the description column then
                  > > > remove the OR statements.
                  > > > so the query would be:
                  > > >
                  > > > SELECT *
                  > > > FROM CaseList
                  > > > WHERE Description Like '%bailed%Out%With%'
                  > > >
                  > > > if not, than look into how you have your table indexes set up. You can
                  > > also
                  > > > twike the query out by removing the '*' and replacing it with all the
                  > > > column
                  > > > names that you need to return.
                  > > > like:
                  > > >
                  > > > SELECT Name, Vehicle, Description, ReasonCode
                  > > > FROM CaseList
                  > > > WHERE Description Like '%bailed%Out%With%'
                  > > >
                  > > > hope this helps! :)
                  > > >
                  > > > ---===/// The PogoWolf \\\===---
                  > > > http://www.GamersVue.com (Video Game News and Information)
                  > > >
                  > > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺
                  > <avinashdesai10@...<avinashdesai10%40gmail.com>
                  > > <avinashdesai10%40gmail.com>
                  > >
                  > > > >
                  > > >
                  > > >
                  > > > >
                  > > > >
                  > > > > Hi All
                  > > > >
                  > > > > I have a table where it has 45000 Rows of data and a Column Named
                  > > > > Description will be used to search
                  > > > >
                  > > > > Just if user enters the Text like this *"Bailed out With"*
                  > > > > Where i split the String and will build the Query String
                  > > > >
                  > > > > and Query will be like this Select * from CaseList where Description
                  > > > > Like'%bailed%' or description like '%Out%' or description Like '%With%'
                  > > > >
                  > > > > This above Query Takes 15.6 Minutes to search data
                  > > > >
                  > > > > I think this can be solved by FullText Index search But i am using SQL
                  > > > > Express...???
                  > > > >
                  > > > > Can any one tell me what to do to search data very fast??? please let
                  > > me
                  > > > > know as soon as possible..
                  > > > >
                  > > > > --
                  > > > > Thanks & Regards
                  > > > > Avinash Desai
                  > > > > *SAVE Nature SAVE Tree SAVE*
                  > > > >
                  > > > > Think Before you print: Please consider our environment before printing
                  > > > > this
                  > > > > e-mail
                  > > > >
                  > > > > Charles de Gaulle<
                  > > > > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
                  > > > > - "The better I get to know men, the more I find myself loving dogs."
                  > > > >
                  > > > > [Non-text portions of this message have been removed]
                  > > > >
                  > > > >
                  > > > >
                  > > >
                  > > > [Non-text portions of this message have been removed]
                  > > >
                  > > >
                  > > >
                  > >
                  > > --
                  > > Thanks & Regards
                  > > Avinash Desai
                  > > *SAVE Nature SAVE Tree SAVE*
                  > >
                  > > Think Before you print: Please consider our environment before printing
                  > > this
                  > > e-mail
                  > >
                  > > Ogden Nash
                  > <http://www.brainyquote.com/quotes/authors/o/ogden_nash.html> -
                  > > "The trouble with a kitten is that when it grows up, it's always a cat."
                  > >
                  > > [Non-text portions of this message have been removed]
                  > >
                  > >
                  > >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                • ☺Aνιηαѕн ∂єѕαι™☺
                  Hey john I tried with that Article It says you can not create Full Text index on SQL express or Its not installed..??? ... -- Thanks & Regards Avinash Desai
                  Message 8 of 16 , Aug 7 7:18 AM
                  • 0 Attachment
                    Hey john I tried with that Article It says you can not create Full Text
                    index on SQL express or Its not installed..???

                    On Fri, Aug 7, 2009 at 7:44 PM, John Warner <john@...> wrote:

                    >
                    >
                    > First have a look at this article:
                    > http://www.wherecanibuyit.co.uk/ASP/full-text-search.html
                    >
                    > John Warner
                    >
                    >
                    > > -----Original Message-----
                    > > From: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>]
                    > On Behalf Of PogoWolf
                    > > Sent: Friday, August 07, 2009 10:04 AM
                    > > To: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                    > > Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..
                    > >
                    > > Well listing out the columns won't shave that much off the query, just
                    > makes
                    > > for some cleaner code and adheres to T-SQL standards. :) as for the query
                    > > you'll need to go back to the other version, to make sure you are pulling
                    > > the description from a record where ANY of the keywords match.
                    > > so:
                    > > SELECT Description
                    > > FROM CaseList
                    > > WHERE Description LIKE '%bailed%'
                    > > OR Description LIKE '%Out%'
                    > > OR Description LIKE '%With%'
                    > >
                    > > The only other suggestion I would have would be to verify your index's
                    > > and/or make sure your tables are set up in the most efficient way
                    > possible.
                    > > You should only need to worry about the tables if your 'CaseList' table
                    > > is pulling data from other tables. If not, then it's all about the table
                    > > Index's and the memory and speed of the computer that's running the
                    > query.
                    > >
                    > > The nature of that type of search is just slow.. Worst since you are
                    > > querying on 3 or more keywords. Indexing the correct column (play around
                    > > until you get the fastest time) will help a lot. Also putting the
                    > database
                    > > on faster computer or moving the database to a database server can speed
                    > up
                    > > the query.
                    > >
                    > >
                    > > ---===/// The PogoWolf \\\===---
                    > > http://www.GamersVue.com (Video Game News and Information)
                    > >
                    > >
                    > >
                    > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...<avinashdesai10%40gmail.com>
                    > >
                    > >
                    > > >
                    > > >
                    > > > Hi PogoWolf
                    > > >
                    > > > Thanks for reply you are correct .
                    > > > I want to search the DESCRIPTION column where Either it can have bailed
                    > > or
                    > > > Out or With Text init..
                    > > >
                    > > > With My Query it takes 16 minutes to return data and as what you said
                    > the
                    > > > query
                    > > >
                    > > >
                    > > > SELECT *
                    > > > FROM CaseList
                    > > > WHERE Description Like '%bailed%Out%With%'
                    > > >
                    > > > Even it takes the same time and I need to Display all the Column from
                    > the
                    > > > Table so i used '*'..
                    > > >
                    > > > IF any other method please let me know. I need to search the
                    > Description
                    > > > which contains *Bailed *or *Out *Or *with *Texts.
                    > > >
                    > > > Regards
                    > > > Avinash
                    > > >
                    > > >
                    > > > On Fri, Aug 7, 2009 at 5:21 PM, PogoWolf
                    > > <pogowolf@... <pogowolf%40gmail.com><pogowolf%40gmail.com>>
                    > > > wrote:
                    > > >
                    > > > >
                    > > > >
                    > > > > If I'm reading this correctly.. Your query is written thusly:
                    > > > > SELECT *
                    > > > > FROM CaseList
                    > > > > WHERE Description Like'%bailed%'
                    > > > > OR description like '%Out%'
                    > > > > OR description Like '%With%'
                    > > > >
                    > > > > This query will return ALL rows with the keyword of 'Bailed', 'out',
                    > and
                    > > > > 'with'
                    > > > > is this what you are looking for? Or do you only want records where
                    > the
                    > > > > description has the keywords in the record?
                    > > > >
                    > > > > If you are looking for those 3 keywords in the description column
                    > then
                    > > > > remove the OR statements.
                    > > > > so the query would be:
                    > > > >
                    > > > > SELECT *
                    > > > > FROM CaseList
                    > > > > WHERE Description Like '%bailed%Out%With%'
                    > > > >
                    > > > > if not, than look into how you have your table indexes set up. You
                    > can
                    > > > also
                    > > > > twike the query out by removing the '*' and replacing it with all the
                    > > > > column
                    > > > > names that you need to return.
                    > > > > like:
                    > > > >
                    > > > > SELECT Name, Vehicle, Description, ReasonCode
                    > > > > FROM CaseList
                    > > > > WHERE Description Like '%bailed%Out%With%'
                    > > > >
                    > > > > hope this helps! :)
                    > > > >
                    > > > > ---===/// The PogoWolf \\\===---
                    > > > > http://www.GamersVue.com (Video Game News and Information)
                    > > > >
                    > > > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺
                    > > <avinashdesai10@... <avinashdesai10%40gmail.com><avinashdesai10%
                    > 40gmail.com>
                    > > > <avinashdesai10%40gmail.com>
                    > > >
                    > > > > >
                    > > > >
                    > > > >
                    > > > > >
                    > > > > >
                    > > > > > Hi All
                    > > > > >
                    > > > > > I have a table where it has 45000 Rows of data and a Column Named
                    > > > > > Description will be used to search
                    > > > > >
                    > > > > > Just if user enters the Text like this *"Bailed out With"*
                    > > > > > Where i split the String and will build the Query String
                    > > > > >
                    > > > > > and Query will be like this Select * from CaseList where
                    > Description
                    > > > > > Like'%bailed%' or description like '%Out%' or description Like
                    > '%With%'
                    > > > > >
                    > > > > > This above Query Takes 15.6 Minutes to search data
                    > > > > >
                    > > > > > I think this can be solved by FullText Index search But i am using
                    > SQL
                    > > > > > Express...???
                    > > > > >
                    > > > > > Can any one tell me what to do to search data very fast??? please
                    > let
                    > > > me
                    > > > > > know as soon as possible..
                    > > > > >
                    > > > > > --
                    > > > > > Thanks & Regards
                    > > > > > Avinash Desai
                    > > > > > *SAVE Nature SAVE Tree SAVE*
                    > > > > >
                    > > > > > Think Before you print: Please consider our environment before
                    > printing
                    > > > > > this
                    > > > > > e-mail
                    > > > > >
                    > > > > > Charles de Gaulle<
                    > > > > > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html
                    > >
                    > > > > > - "The better I get to know men, the more I find myself loving
                    > dogs."
                    > > > > >
                    > > > > > [Non-text portions of this message have been removed]
                    > > > > >
                    > > > > >
                    > > > > >
                    > > > >
                    > > > > [Non-text portions of this message have been removed]
                    > > > >
                    > > > >
                    > > > >
                    > > >
                    > > > --
                    > > > Thanks & Regards
                    > > > Avinash Desai
                    > > > *SAVE Nature SAVE Tree SAVE*
                    > > >
                    > > > Think Before you print: Please consider our environment before printing
                    > > > this
                    > > > e-mail
                    > > >
                    > > > Ogden Nash
                    > > <http://www.brainyquote.com/quotes/authors/o/ogden_nash.html> -
                    > > > "The trouble with a kitten is that when it grows up, it's always a
                    > cat."
                    > > >
                    > > > [Non-text portions of this message have been removed]
                    > > >
                    > > >
                    > > >
                    > >
                    > >
                    > > [Non-text portions of this message have been removed]
                    > >
                    > >
                    > >
                    > > ------------------------------------
                    > >
                    > > Yahoo! Groups Links
                    > >
                    > >
                    > >
                    >
                    >
                    >



                    --
                    Thanks & Regards
                    Avinash Desai
                    *SAVE Nature SAVE Tree SAVE*

                    Think Before you print: Please consider our environment before printing this
                    e-mail
                    Sent from Bangalore, KA, India
                    Ted Turner <http://www.brainyquote.com/quotes/authors/t/ted_turner.html> -
                    "Sports is like a war without the killing."


                    [Non-text portions of this message have been removed]
                  • John Warner
                    Make sure that you download the version of SQL Server Express with the additional full-text search capabilities. This is the larger one
                    Message 9 of 16 , Aug 7 7:25 AM
                    • 0 Attachment
                      <quote from="article">
                      Make sure that you download the version of SQL Server Express with the additional full-text search capabilities. This is the larger one of the two downloads possible.
                      </quote>

                      Question: Did you download the proper version?

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of ?A???a??
                      > ???a?™?
                      > Sent: Friday, August 07, 2009 10:18 AM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..
                      >
                      > Hey john I tried with that Article It says you can not create Full Text
                      > index on SQL express or Its not installed..???
                      >
                      > On Fri, Aug 7, 2009 at 7:44 PM, John Warner <john@...> wrote:
                      >
                      > >
                      > >
                      > > First have a look at this article:
                      > > http://www.wherecanibuyit.co.uk/ASP/full-text-search.html
                      > >
                      > > John Warner
                      > >
                      > >
                      > > > -----Original Message-----
                      > > > From:
                      > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
                      > ps.com>
                      > > >
                      > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yah
                      > oogroups..com>]
                      > > On Behalf Of PogoWolf
                      > > > Sent: Friday, August 07, 2009 10:04 AM
                      > > > To:
                      > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
                      > ps.com>
                      > > > Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..
                      > > >
                      > > > Well listing out the columns won't shave that much off the query, just
                      > > makes
                      > > > for some cleaner code and adheres to T-SQL standards. :) as for the query
                      > > > you'll need to go back to the other version, to make sure you are pulling
                      > > > the description from a record where ANY of the keywords match.
                      > > > so:
                      > > > SELECT Description
                      > > > FROM CaseList
                      > > > WHERE Description LIKE '%bailed%'
                      > > > OR Description LIKE '%Out%'
                      > > > OR Description LIKE '%With%'
                      > > >
                      > > > The only other suggestion I would have would be to verify your index's
                      > > > and/or make sure your tables are set up in the most efficient way
                      > > possible.
                      > > > You should only need to worry about the tables if your 'CaseList' table
                      > > > is pulling data from other tables. If not, then it's all about the table
                      > > > Index's and the memory and speed of the computer that's running the
                      > > query.
                      > > >
                      > > > The nature of that type of search is just slow.. Worst since you are
                      > > > querying on 3 or more keywords. Indexing the correct column (play around
                      > > > until you get the fastest time) will help a lot. Also putting the
                      > > database
                      > > > on faster computer or moving the database to a database server can
                      > speed
                      > > up
                      > > > the query.
                      > > >
                      > > >
                      > > > ---===/// The PogoWolf \\\===---
                      > > > http://www.GamersVue.com (Video Game News and Information)
                      > > >
                      > > >
                      > > >
                      > > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺
                      > <avinashdesai10@...<avinashdesai10%40gmail.com>
                      > > >
                      > > >
                      > > > >
                      > > > >
                      > > > > Hi PogoWolf
                      > > > >
                      > > > > Thanks for reply you are correct .
                      > > > > I want to search the DESCRIPTION column where Either it can have
                      > bailed
                      > > > or
                      > > > > Out or With Text init..
                      > > > >
                      > > > > With My Query it takes 16 minutes to return data and as what you said
                      > > the
                      > > > > query
                      > > > >
                      > > > >
                      > > > > SELECT *
                      > > > > FROM CaseList
                      > > > > WHERE Description Like '%bailed%Out%With%'
                      > > > >
                      > > > > Even it takes the same time and I need to Display all the Column from
                      > > the
                      > > > > Table so i used '*'..
                      > > > >
                      > > > > IF any other method please let me know. I need to search the
                      > > Description
                      > > > > which contains *Bailed *or *Out *Or *with *Texts.
                      > > > >
                      > > > > Regards
                      > > > > Avinash
                      > > > >
                      > > > >
                      > > > > On Fri, Aug 7, 2009 at 5:21 PM, PogoWolf
                      > > > <pogowolf@...
                      > <pogowolf%40gmail.com><pogowolf%40gmail.com>>
                      > > > > wrote:
                      > > > >
                      > > > > >
                      > > > > >
                      > > > > > If I'm reading this correctly.. Your query is written thusly:
                      > > > > > SELECT *
                      > > > > > FROM CaseList
                      > > > > > WHERE Description Like'%bailed%'
                      > > > > > OR description like '%Out%'
                      > > > > > OR description Like '%With%'
                      > > > > >
                      > > > > > This query will return ALL rows with the keyword of 'Bailed', 'out',
                      > > and
                      > > > > > 'with'
                      > > > > > is this what you are looking for? Or do you only want records where
                      > > the
                      > > > > > description has the keywords in the record?
                      > > > > >
                      > > > > > If you are looking for those 3 keywords in the description column
                      > > then
                      > > > > > remove the OR statements.
                      > > > > > so the query would be:
                      > > > > >
                      > > > > > SELECT *
                      > > > > > FROM CaseList
                      > > > > > WHERE Description Like '%bailed%Out%With%'
                      > > > > >
                      > > > > > if not, than look into how you have your table indexes set up. You
                      > > can
                      > > > > also
                      > > > > > twike the query out by removing the '*' and replacing it with all the
                      > > > > > column
                      > > > > > names that you need to return.
                      > > > > > like:
                      > > > > >
                      > > > > > SELECT Name, Vehicle, Description, ReasonCode
                      > > > > > FROM CaseList
                      > > > > > WHERE Description Like '%bailed%Out%With%'
                      > > > > >
                      > > > > > hope this helps! :)
                      > > > > >
                      > > > > > ---===/// The PogoWolf \\\===---
                      > > > > > http://www.GamersVue.com (Video Game News and Information)
                      > > > > >
                      > > > > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺
                      > > > <avinashdesai10@...
                      > <avinashdesai10%40gmail.com><avinashdesai10%
                      > > 40gmail.com>
                      > > > > <avinashdesai10%40gmail.com>
                      > > > >
                      > > > > > >
                      > > > > >
                      > > > > >
                      > > > > > >
                      > > > > > >
                      > > > > > > Hi All
                      > > > > > >
                      > > > > > > I have a table where it has 45000 Rows of data and a Column
                      > Named
                      > > > > > > Description will be used to search
                      > > > > > >
                      > > > > > > Just if user enters the Text like this *"Bailed out With"*
                      > > > > > > Where i split the String and will build the Query String
                      > > > > > >
                      > > > > > > and Query will be like this Select * from CaseList where
                      > > Description
                      > > > > > > Like'%bailed%' or description like '%Out%' or description Like
                      > > '%With%'
                      > > > > > >
                      > > > > > > This above Query Takes 15.6 Minutes to search data
                      > > > > > >
                      > > > > > > I think this can be solved by FullText Index search But i am using
                      > > SQL
                      > > > > > > Express...???
                      > > > > > >
                      > > > > > > Can any one tell me what to do to search data very fast??? please
                      > > let
                      > > > > me
                      > > > > > > know as soon as possible..
                      > > > > > >
                      > > > > > > --
                      > > > > > > Thanks & Regards
                      > > > > > > Avinash Desai
                      > > > > > > *SAVE Nature SAVE Tree SAVE*
                      > > > > > >
                      > > > > > > Think Before you print: Please consider our environment before
                      > > printing
                      > > > > > > this
                      > > > > > > e-mail
                      > > > > > >
                      > > > > > > Charles de Gaulle<
                      > > > > > > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html
                      > > >
                      > > > > > > - "The better I get to know men, the more I find myself loving
                      > > dogs."
                      > > > > > >
                      > > > > > > [Non-text portions of this message have been removed]
                      > > > > > >
                      > > > > > >
                      > > > > > >
                      > > > > >
                      > > > > > [Non-text portions of this message have been removed]
                      > > > > >
                      > > > > >
                      > > > > >
                      > > > >
                      > > > > --
                      > > > > Thanks & Regards
                      > > > > Avinash Desai
                      > > > > *SAVE Nature SAVE Tree SAVE*
                      > > > >
                      > > > > Think Before you print: Please consider our environment before printing
                      > > > > this
                      > > > > e-mail
                      > > > >
                      > > > > Ogden Nash
                      > > > <http://www.brainyquote.com/quotes/authors/o/ogden_nash.html> -
                      > > > > "The trouble with a kitten is that when it grows up, it's always a
                      > > cat."
                      > > > >
                      > > > > [Non-text portions of this message have been removed]
                      > > > >
                      > > > >
                      > > > >
                      > > >
                      > > >
                      > > > [Non-text portions of this message have been removed]
                      > > >
                      > > >
                      > > >
                      > > > ------------------------------------
                      > > >
                      > > > Yahoo! Groups Links
                      > > >
                      > > >
                      > > >
                      > >
                      > >
                      > >
                      >
                      >
                      >
                      > --
                      > Thanks & Regards
                      > Avinash Desai
                      > *SAVE Nature SAVE Tree SAVE*
                      >
                      > Think Before you print: Please consider our environment before printing this
                      > e-mail
                      > Sent from Bangalore, KA, India
                      > Ted Turner <http://www.brainyquote.com/quotes/authors/t/ted_turner.html> -
                      > "Sports is like a war without the killing."
                      >
                      >
                      > [Non-text portions of this message have been removed]
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                    • Arnie Rowland
                      Then you should re-read the article. You CAN use Full-Text indexing with the version of SQL Express that supports Full-text Indexing. You have to download the
                      Message 10 of 16 , Aug 7 7:32 AM
                      • 0 Attachment
                        Then you should re-read the article. You CAN use Full-Text indexing with the version of SQL Express that supports Full-text Indexing. You have to download the 'Advanced' version of SQL Express.

                        There is no possible way to use 'normal' indexing to help this query. To those that have proposed creating more or 'better' indexes: Anytime the search value begins with a wildcard -a full scan is required. Even if you build a index on the search column, the index is likely to have as many rows as the table, so the query processor will most likely select a table scan. ON the chance that the index has SUBSTAINTIALLY fewer rows (a lot of non-indexed, or NULL values), maybe an INDEX scan will be used.

                        But the point is, ALL rows that have values will have to be examined BECAUSE of the leading wildcard.

                        Full-Text indexing is the only reasonable option for data such as this.

                        Regards,

                        Arnie Rowland, MVP (SQL Server)

                        "Fortune favors the prepared mind." Louis Pasteur




                        -----Original Message-----
                        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of ?A???a?? ???a?™?
                        Sent: Friday, August 07, 2009 7:20 AM
                        To: Arnie
                        Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..

                        Hey john I tried with that Article It says you can not create Full Text
                        index on SQL express or Its not installed..???

                        On Fri, Aug 7, 2009 at 7:44 PM, John Warner <john@...> wrote:

                        >
                        >
                        > First have a look at this article:
                        > http://www.wherecanibuyit.co.uk/ASP/full-text-search.html
                        >
                        > John Warner
                        >
                        >
                        > > -----Original Message-----
                        > > From: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                        > > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>]
                        > On Behalf Of PogoWolf
                        > > Sent: Friday, August 07, 2009 10:04 AM
                        ..
                        > >
                        > > The only other suggestion I would have would be to verify your index's
                        > > and/or make sure your tables are set up in the most efficient way
                        > possible.
                        ..
                        > > Indexing the correct column (play around
                        > > until you get the fastest time) will help a lot.





                        Disclaimer - August 7, 2009
                        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                      • PogoWolf
                        If you are running XP 1 gig of RAM is very underpowered. I tell my friends not to even talk to me about how sloe their computers are if they are running XP
                        Message 11 of 16 , Aug 7 7:33 AM
                        • 0 Attachment
                          If you are running XP 1 gig of RAM is very underpowered. I tell my
                          friends not to even talk to me about how sloe their computers are if they
                          are running XP with anything less than 2 gig of RAM. and HDD really
                          doesn't have anything to do with it (unless it's full).. it's more about RAM
                          and CPU speed.
                          as for the full text search.. honestly, I do not know. :( I have
                          never had the need to use SQL Express for testing or production. I've
                          always had a server to play with.


                          2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...>

                          >
                          >
                          > But Although my PC has 1GB ram and 500GB HDD .. and Its Taking So much
                          > time..
                          >
                          > So is it possible to use Full text index search In SQL EXPRESS if yes then
                          > how can i use it???
                          >
                          > [?][?][?][?][?][?]
                          >
                          >
                          > On Fri, Aug 7, 2009 at 7:33 PM, PogoWolf <pogowolf@...<pogowolf%40gmail.com>>
                          > wrote:
                          >
                          > >
                          > >
                          > > Well listing out the columns won't shave that much off the query, just
                          > > makes
                          > > for some cleaner code and adheres to T-SQL standards. :) as for the query
                          > > you'll need to go back to the other version, to make sure you are pulling
                          > > the description from a record where ANY of the keywords match.
                          > > so:
                          > > SELECT Description
                          > > FROM CaseList
                          > > WHERE Description LIKE '%bailed%'
                          > > OR Description LIKE '%Out%'
                          > > OR Description LIKE '%With%'
                          > >
                          > > The only other suggestion I would have would be to verify your index's
                          > > and/or make sure your tables are set up in the most efficient way
                          > possible.
                          > > You should only need to worry about the tables if your 'CaseList' table
                          > > is pulling data from other tables. If not, then it's all about the table
                          > > Index's and the memory and speed of the computer that's running the
                          > query.
                          > >
                          > > The nature of that type of search is just slow.. Worst since you are
                          > > querying on 3 or more keywords. Indexing the correct column (play around
                          > > until you get the fastest time) will help a lot. Also putting the
                          > database
                          > > on faster computer or moving the database to a database server can speed
                          > up
                          > > the query.
                          > >
                          > >
                          > > ---===/// The PogoWolf \\\===---
                          > > http://www.GamersVue.com (Video Game News and Information)
                          > >
                          > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...<avinashdesai10%40gmail.com>
                          > <avinashdesai10%40gmail.com>
                          > > >
                          > >
                          > > >
                          > > >
                          > > > Hi PogoWolf
                          > > >
                          > > > Thanks for reply you are correct .
                          > > > I want to search the DESCRIPTION column where Either it can have bailed
                          > > or
                          > > > Out or With Text init..
                          > > >
                          > > > With My Query it takes 16 minutes to return data and as what you said
                          > the
                          > > > query
                          > > >
                          > > >
                          > > > SELECT *
                          > > > FROM CaseList
                          > > > WHERE Description Like '%bailed%Out%With%'
                          > > >
                          > > > Even it takes the same time and I need to Display all the Column from
                          > the
                          > > > Table so i used '*'..
                          > > >
                          > > > IF any other method please let me know. I need to search the
                          > Description
                          > > > which contains *Bailed *or *Out *Or *with *Texts.
                          > > >
                          > > > Regards
                          > > > Avinash
                          > > >
                          > > >
                          > > > On Fri, Aug 7, 2009 at 5:21 PM, PogoWolf <pogowolf@...<pogowolf%40gmail.com>
                          > <pogowolf%40gmail.com>
                          > > <pogowolf%40gmail.com>>
                          >
                          > >
                          > > > wrote:
                          > > >
                          > > > >
                          > > > >
                          > > > > If I'm reading this correctly.. Your query is written thusly:
                          > > > > SELECT *
                          > > > > FROM CaseList
                          > > > > WHERE Description Like'%bailed%'
                          > > > > OR description like '%Out%'
                          > > > > OR description Like '%With%'
                          > > > >
                          > > > > This query will return ALL rows with the keyword of 'Bailed', 'out',
                          > > and
                          > > > > 'with'
                          > > > > is this what you are looking for? Or do you only want records where
                          > the
                          > > > > description has the keywords in the record?
                          > > > >
                          > > > > If you are looking for those 3 keywords in the description column
                          > then
                          > > > > remove the OR statements.
                          > > > > so the query would be:
                          > > > >
                          > > > > SELECT *
                          > > > > FROM CaseList
                          > > > > WHERE Description Like '%bailed%Out%With%'
                          > > > >
                          > > > > if not, than look into how you have your table indexes set up. You
                          > can
                          > > > also
                          > > > > twike the query out by removing the '*' and replacing it with all the
                          > > > > column
                          > > > > names that you need to return.
                          > > > > like:
                          > > > >
                          > > > > SELECT Name, Vehicle, Description, ReasonCode
                          > > > > FROM CaseList
                          > > > > WHERE Description Like '%bailed%Out%With%'
                          > > > >
                          > > > > hope this helps! :)
                          > > > >
                          > > > > ---===/// The PogoWolf \\\===---
                          > > > > http://www.GamersVue.com (Video Game News and Information)
                          > > > >
                          > > > > 2009/8/7 ☺Aνιηαѕн ∂єѕαι™☺ <avinashdesai10@...<avinashdesai10%40gmail.com>
                          > <avinashdesai10%40gmail.com>
                          > > <avinashdesai10%40gmail.com>
                          >
                          > > > <avinashdesai10%40gmail.com>
                          > > >
                          > > > > >
                          > > > >
                          > > > >
                          > > > > >
                          > > > > >
                          > > > > > Hi All
                          > > > > >
                          > > > > > I have a table where it has 45000 Rows of data and a Column Named
                          > > > > > Description will be used to search
                          > > > > >
                          > > > > > Just if user enters the Text like this *"Bailed out With"*
                          > > > > > Where i split the String and will build the Query String
                          > > > > >
                          > > > > > and Query will be like this Select * from CaseList where
                          > Description
                          > > > > > Like'%bailed%' or description like '%Out%' or description Like
                          > > '%With%'
                          > > > > >
                          > > > > > This above Query Takes 15.6 Minutes to search data
                          > > > > >
                          > > > > > I think this can be solved by FullText Index search But i am using
                          > > SQL
                          > > > > > Express...???
                          > > > > >
                          > > > > > Can any one tell me what to do to search data very fast??? please
                          > let
                          > > > me
                          > > > > > know as soon as possible..
                          > > > > >
                          > > > > > --
                          > > > > > Thanks & Regards
                          > > > > > Avinash Desai
                          > > > > > *SAVE Nature SAVE Tree SAVE*
                          > > > > >
                          > > > > > Think Before you print: Please consider our environment before
                          > > printing
                          > > > > > this
                          > > > > > e-mail
                          > > > > >
                          > > > > > Charles de Gaulle<
                          > > > > > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html
                          > >
                          > > > > > - "The better I get to know men, the more I find myself loving
                          > dogs."
                          > > > > >
                          > > > > > [Non-text portions of this message have been removed]
                          > > > > >
                          > > > > >
                          > > > > >
                          > > > >
                          > > > > [Non-text portions of this message have been removed]
                          > > > >
                          > > > >
                          > > > >
                          > > >
                          > > > --
                          > > > Thanks & Regards
                          > > > Avinash Desai
                          > > > *SAVE Nature SAVE Tree SAVE*
                          > > >
                          > > > Think Before you print: Please consider our environment before printing
                          > > > this
                          > > > e-mail
                          > > >
                          > > > Ogden Nash <
                          > http://www.brainyquote.com/quotes/authors/o/ogden_nash.html>
                          > > -
                          > > > "The trouble with a kitten is that when it grows up, it's always a
                          > cat."
                          > > >
                          > > > [Non-text portions of this message have been removed]
                          > > >
                          > > >
                          > > >
                          > >
                          > > [Non-text portions of this message have been removed]
                          > >
                          > >
                          > >
                          >
                          > --
                          > Thanks & Regards
                          > Avinash Desai
                          > *SAVE Nature SAVE Tree SAVE*
                          >
                          > Think Before you print: Please consider our environment before printing
                          > this
                          > e-mail
                          > Sent from Bangalore, KA, India
                          > Charles de Gaulle<
                          > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>
                          > - "The better I get to know men, the more I find myself loving dogs."
                          >
                          > [Non-text portions of this message have been removed]
                          >
                          >
                          >


                          [Non-text portions of this message have been removed]
                        • PogoWolf
                          Thank you Arnie, Guess I m a little green with some of the under the hood stuff. :) ... http://www.GamersVue.com (Video Game News and Information) ...
                          Message 12 of 16 , Aug 7 7:35 AM
                          • 0 Attachment
                            Thank you Arnie, Guess I'm a little green with some of the 'under the
                            hood' stuff. :)


                            ---===/// The PogoWolf \\\===---
                            http://www.GamersVue.com (Video Game News and Information)



                            On Fri, Aug 7, 2009 at 10:32 AM, Arnie Rowland <arnie@...> wrote:

                            >
                            >
                            > Then you should re-read the article. You CAN use Full-Text indexing with
                            > the version of SQL Express that supports Full-text Indexing. You have to
                            > download the 'Advanced' version of SQL Express.
                            >
                            > There is no possible way to use 'normal' indexing to help this query. To
                            > those that have proposed creating more or 'better' indexes: Anytime the
                            > search value begins with a wildcard -a full scan is required. Even if you
                            > build a index on the search column, the index is likely to have as many rows
                            > as the table, so the query processor will most likely select a table scan.
                            > ON the chance that the index has SUBSTAINTIALLY fewer rows (a lot of
                            > non-indexed, or NULL values), maybe an INDEX scan will be used.
                            >
                            > But the point is, ALL rows that have values will have to be examined
                            > BECAUSE of the leading wildcard.
                            >
                            > Full-Text indexing is the only reasonable option for data such as this.
                            >
                            > Regards,
                            >
                            > Arnie Rowland, MVP (SQL Server)
                            >
                            > "Fortune favors the prepared mind." Louis Pasteur
                            >
                            >
                            > -----Original Message-----
                            > From: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>[mailto:
                            > SQLQueriesNoCode@yahoogroups.com <SQLQueriesNoCode%40yahoogroups.com>] On
                            > Behalf Of ?A???a?? ???a?�?
                            > Sent: Friday, August 07, 2009 7:20 AM
                            > To: Arnie
                            > Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..
                            >
                            > Hey john I tried with that Article It says you can not create Full Text
                            > index on SQL express or Its not installed..???
                            >
                            > On Fri, Aug 7, 2009 at 7:44 PM, John Warner <john@...<john%40jwarner.com>>
                            > wrote:
                            >
                            > >
                            > >
                            > > First have a look at this article:
                            > > http://www.wherecanibuyit.co.uk/ASP/full-text-search.html
                            > >
                            > > John Warner
                            > >
                            > >
                            > > > -----Original Message-----
                            > > > From: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                            > <SQLQueriesNoCode%40yahoogroups.com>
                            > > > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                            > <SQLQueriesNoCode%40yahoogroups.com>]
                            > > On Behalf Of PogoWolf
                            > > > Sent: Friday, August 07, 2009 10:04 AM
                            > ..
                            > > >
                            > > > The only other suggestion I would have would be to verify your index's
                            > > > and/or make sure your tables are set up in the most efficient way
                            > > possible.
                            > ..
                            > > > Indexing the correct column (play around
                            > > > until you get the fastest time) will help a lot.
                            >
                            > Disclaimer - August 7, 2009
                            > This email and any files transmitted with it are confidential and intended
                            > solely for SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>.
                            > If you are not the named addressee you should not disseminate, distribute,
                            > copy or alter this email. Any views or opinions presented in this email are
                            > solely those of the author and might not represent those of Westwood
                            > Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken
                            > reasonable precautions to ensure no viruses are present in this email, the
                            > company cannot accept responsibility for any loss or damage arising from the
                            > use of this email or attachments.
                            > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                            >
                            >
                            >


                            [Non-text portions of this message have been removed]
                          • John Warner
                            Hmm I assumed with Wildcards like this that a full table scan would happen no matter what indexes exist on any of the columns. I had no idea the processor
                            Message 13 of 16 , Aug 7 7:38 AM
                            • 0 Attachment
                              Hmm I assumed with Wildcards like this that a full table scan would happen no matter what indexes exist on any of the columns. I had no idea the processor might actually consider otherwise ...

                              Once again Arnie you show how much wise you are than I am.

                              John Warner


                              > -----Original Message-----
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                              > Sent: Friday, August 07, 2009 10:32 AM
                              > To: SQLQueriesNoCode@yahoogroups.com
                              > Subject: RE: [SQLQueriesNoCode] Fastest way to search data from table ..
                              >
                              > Then you should re-read the article. You CAN use Full-Text indexing with the
                              > version of SQL Express that supports Full-text Indexing. You have to download
                              > the 'Advanced' version of SQL Express.
                              >
                              > There is no possible way to use 'normal' indexing to help this query. To those
                              > that have proposed creating more or 'better' indexes: Anytime the search value
                              > begins with a wildcard -a full scan is required. Even if you build a index on the
                              > search column, the index is likely to have as many rows as the table, so the
                              > query processor will most likely select a table scan. ON the chance that the
                              > index has SUBSTAINTIALLY fewer rows (a lot of non-indexed, or NULL
                              > values), maybe an INDEX scan will be used.
                              >
                              > But the point is, ALL rows that have values will have to be examined
                              > BECAUSE of the leading wildcard.
                              >
                              > Full-Text indexing is the only reasonable option for data such as this.
                              >
                              > Regards,
                              >
                              > Arnie Rowland, MVP (SQL Server)
                              >
                              > "Fortune favors the prepared mind." Louis Pasteur
                              >
                              >
                              >
                              >
                              > -----Original Message-----
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of ?A???a??
                              > ???a?™?
                              > Sent: Friday, August 07, 2009 7:20 AM
                              > To: Arnie
                              > Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..
                              >
                              > Hey john I tried with that Article It says you can not create Full Text
                              > index on SQL express or Its not installed..???
                              >
                              > On Fri, Aug 7, 2009 at 7:44 PM, John Warner <john@...> wrote:
                              >
                              > >
                              > >
                              > > First have a look at this article:
                              > > http://www.wherecanibuyit.co.uk/ASP/full-text-search.html
                              > >
                              > > John Warner
                              > >
                              > >
                              > > > -----Original Message-----
                              > > > From:
                              > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
                              > ps.com>
                              > > >
                              > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yah
                              > oogroups.com>]
                              > > On Behalf Of PogoWolf
                              > > > Sent: Friday, August 07, 2009 10:04 AM
                              > ...
                              > > >
                              > > > The only other suggestion I would have would be to verify your index's
                              > > > and/or make sure your tables are set up in the most efficient way
                              > > possible.
                              > ..
                              > > > Indexing the correct column (play around
                              > > > until you get the fastest time) will help a lot.
                              >
                              >
                              >
                              >
                              >
                              > Disclaimer - August 7, 2009
                              > This email and any files transmitted with it are confidential and intended solely
                              > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                              > addressee you should not disseminate, distribute, copy or alter this email. Any
                              > views or opinions presented in this email are solely those of the author and
                              > might not represent those of Westwood Consulting, Inc. Warning: Although
                              > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                              > viruses are present in this email, the company cannot accept responsibility for
                              > any loss or damage arising from the use of this email or attachments.
                              > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                              >
                              >
                              > ------------------------------------
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              >
                            • Arnie Rowland
                              The real issue is this: How is it possible that a computer would possibly know that those are duplicates? It is only because a human tells it that they are
                              Message 14 of 16 , Aug 7 7:41 AM
                              • 0 Attachment
                                The 'real' issue is this: How is it possible that a computer would possibly know that those are duplicates?

                                It is only because a human tells it that they are duplicates. You can either visually match and handle the duplicates, OR

                                You can visually match and write some code that tells the computer that the next time it finds the two particular values, to handle them as duplicates.

                                In both circumstances, a human is required. So if these particular duplicates are encountered frequently, take the time to write the code. If this is a one-time operation, handle it manually and be done.


                                Regards,

                                Arnie Rowland, MVP (SQL Server)

                                "Fortune favors the prepared mind." Louis Pasteur




                                -----Original Message-----
                                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                                Sent: Friday, August 07, 2009 4:59 AM
                                To: Arnie
                                Subject: [SQLQueriesNoCode] Update all referenced tables upon removal of duplicated records

                                Hi all,
                                I have a table named Asset having columns: Asset_Id, Asset_Symbol, Asset_Name, etc.
                                I got an excel sheet from the client as a change to merge few assets (actually duplicates).
                                The table Asset is referenced by many tables (by foriegn keys).

                                n.b. SQL Server 2008 is the database

                                For e.g. we have data like (in excel):
                                1 [DHAFRA] [AL DHAFRA INSURANCE]
                                2 [DAFRA] [AL DHAFRA INSURANCE CO.]
                                ..
                                ..
                                almost 20 duplicates

                                I was about to create a temp table with columns: CORRECT_SYMBOL, WRONG_SYMBOL, CORRECT_SYMBOL_ASSET_ID, WRONG_SYMBOL_ASSET_ID.
                                Assuming 1st row is correct and 2nd is wrong, and then importing the excel file to this temp table.

                                Please provide some suggestions on how to update the referenced tables upon/bofore/after deletion of the duplicate record.

                                What I actually need is to:
                                1. get the list of all tables which are referening Asset table
                                2. for each referencing table
                                2a. enable (cascade on update on the table)
                                2b. update key (for e.g. update asset_id=1 in referencing table where asset_id=2)
                                2c. disable (cascade on update on the table)
                                3. delete duplicate record


                                Best Wishes,
                                Noman Aftab


                                http://www.jalandhari.qsh.eu




                                [Non-text portions of this message have been removed]



                                ------------------------------------

                                Yahoo! Groups Links







                                Disclaimer - August 7, 2009
                                This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                              • Arnie Rowland
                                John, The key is the Query Processor making the determination that it would be cheaper to scan the index, and then also do the bookmark lookup to the table
                                Message 15 of 16 , Aug 7 7:45 AM
                                • 0 Attachment
                                  John,

                                  The key is the Query Processor making the determination that it would be cheaper to scan the index, and then also do the bookmark lookup to the table versus just scan the table. One of those places that the 'statistics' come into consideration.

                                  But in reality, my guess is that close to 100% of the searches, table scans would be used.

                                  Regards,

                                  Arnie Rowland, MVP (SQL Server)

                                  "Fortune favors the prepared mind." Louis Pasteur




                                  -----Original Message-----
                                  From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                  Sent: Friday, August 07, 2009 7:41 AM
                                  To: Arnie
                                  Subject: RE: [SQLQueriesNoCode] Fastest way to search data from table ..

                                  Hmm I assumed with Wildcards like this that a full table scan would happen no matter what indexes exist on any of the columns. I had no idea the processor might actually consider otherwise ...

                                  Once again Arnie you show how much wise you are than I am.

                                  John Warner


                                  > -----Original Message-----
                                  > From: SQLQueriesNoCode@yahoogroups.com
                                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                  > Sent: Friday, August 07, 2009 10:32 AM
                                  > To: SQLQueriesNoCode@yahoogroups.com
                                  > Subject: RE: [SQLQueriesNoCode] Fastest way to search data from table ..
                                  >
                                  > Then you should re-read the article. You CAN use Full-Text indexing with the
                                  > version of SQL Express that supports Full-text Indexing. You have to download
                                  > the 'Advanced' version of SQL Express.
                                  >
                                  > There is no possible way to use 'normal' indexing to help this query. To those
                                  > that have proposed creating more or 'better' indexes: Anytime the search value
                                  > begins with a wildcard -a full scan is required. Even if you build a index on the
                                  > search column, the index is likely to have as many rows as the table, so the
                                  > query processor will most likely select a table scan. ON the chance that the
                                  > index has SUBSTAINTIALLY fewer rows (a lot of non-indexed, or NULL
                                  > values), maybe an INDEX scan will be used.
                                  >
                                  > But the point is, ALL rows that have values will have to be examined
                                  > BECAUSE of the leading wildcard.
                                  >
                                  > Full-Text indexing is the only reasonable option for data such as this.
                                  >
                                  > Regards,
                                  >
                                  > Arnie Rowland, MVP (SQL Server)
                                  >
                                  > "Fortune favors the prepared mind." Louis Pasteur
                                  >
                                  >
                                  >
                                  >
                                  > -----Original Message-----
                                  > From: SQLQueriesNoCode@yahoogroups.com
                                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of ?A???a??
                                  > ???a?™?
                                  > Sent: Friday, August 07, 2009 7:20 AM
                                  > To: Arnie
                                  > Subject: Re: [SQLQueriesNoCode] Fastest way to search data from table ..
                                  >
                                  > Hey john I tried with that Article It says you can not create Full Text
                                  > index on SQL express or Its not installed..???
                                  >
                                  > On Fri, Aug 7, 2009 at 7:44 PM, John Warner <john@...> wrote:
                                  >
                                  > >
                                  > >
                                  > > First have a look at this article:
                                  > > http://www.wherecanibuyit.co.uk/ASP/full-text-search.html
                                  > >
                                  > > John Warner
                                  > >
                                  > >
                                  > > > -----Original Message-----
                                  > > > From:
                                  > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
                                  > ps.com>
                                  > > >
                                  > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yah
                                  > oogroups.com>]
                                  > > On Behalf Of PogoWolf
                                  > > > Sent: Friday, August 07, 2009 10:04 AM
                                  > ...
                                  > > >
                                  > > > The only other suggestion I would have would be to verify your index's
                                  > > > and/or make sure your tables are set up in the most efficient way
                                  > > possible.
                                  > ..
                                  > > > Indexing the correct column (play around
                                  > > > until you get the fastest time) will help a lot.
                                  >
                                  >
                                  >
                                  >
                                  >
                                  > Disclaimer - August 7, 2009
                                  > This email and any files transmitted with it are confidential and intended solely
                                  > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                                  > addressee you should not disseminate, distribute, copy or alter this email. Any
                                  > views or opinions presented in this email are solely those of the author and
                                  > might not represent those of Westwood Consulting, Inc. Warning: Although
                                  > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                                  > viruses are present in this email, the company cannot accept responsibility for
                                  > any loss or damage arising from the use of this email or attachments.
                                  > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                                  >
                                  >
                                  > ------------------------------------
                                  >
                                  > Yahoo! Groups Links
                                  >
                                  >
                                  >



                                  ------------------------------------

                                  Yahoo! Groups Links
                                • Noman Aftab
                                  Yes, it is a one time operation, and the excel file is provided by the client containing data as: 1. correct record 1 2. duplicate record 13. correct record
                                  Message 16 of 16 , Aug 7 12:06 PM
                                  • 0 Attachment
                                    Yes, it is a one time operation, and the excel file is provided by the client containing data as:

                                    1. correct record 1
                                    2. duplicate record 13. correct record 2
                                    4. duplicate record 25. correct record 3
                                    6. duplicate record 3
                                    ...
                                    ...
                                    and so on.

                                    Can I enable/disable "cascade updates" check through sql scripts?

                                     




                                    ________________________________
                                    From: Arnie Rowland <arnie@...>
                                    To: SQLQueriesNoCode@yahoogroups.com
                                    Sent: Friday, 7 August, 2009 19:41:33
                                    Subject: RE: [SQLQueriesNoCode] Update all referenced tables upon removal of duplicated records

                                     
                                    The 'real' issue is this: How is it possible that a computer would possibly know that those are duplicates?

                                    It is only because a human tells it that they are duplicates. You can either visually match and handle the duplicates, OR

                                    You can visually match and write some code that tells the computer that the next time it finds the two particular values, to handle them as duplicates.

                                    In both circumstances, a human is required. So if these particular duplicates are encountered frequently, take the time to write the code. If this is a one-time operation, handle it manually and be done.

                                    Regards,

                                    Arnie Rowland, MVP (SQL Server)

                                    "Fortune favors the prepared mind." Louis Pasteur

                                    -----Original Message-----
                                    From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Noman Aftab
                                    Sent: Friday, August 07, 2009 4:59 AM
                                    To: Arnie
                                    Subject: [SQLQueriesNoCode] Update all referenced tables upon removal of duplicated records

                                    Hi all,
                                    I have a table named Asset having columns: Asset_Id, Asset_Symbol, Asset_Name, etc.
                                    I got an excel sheet from the client as a change to merge few assets (actually duplicates).
                                    The table Asset is referenced by many tables (by foriegn keys).

                                    n.b. SQL Server 2008 is the database

                                    For e.g. we have data like (in excel):
                                    1 [DHAFRA] [AL DHAFRA INSURANCE]
                                    2 [DAFRA] [AL DHAFRA INSURANCE CO.]
                                    ..
                                    ..
                                    almost 20 duplicates

                                    I was about to create a temp table with columns: CORRECT_SYMBOL, WRONG_SYMBOL, CORRECT_SYMBOL_ ASSET_ID, WRONG_SYMBOL_ ASSET_ID.
                                    Assuming 1st row is correct and 2nd is wrong, and then importing the excel file to this temp table.

                                    Please provide some suggestions on how to update the referenced tables upon/bofore/ after deletion of the duplicate record.

                                    What I actually need is to:
                                    1. get the list of all tables which are referening Asset table
                                    2. for each referencing table
                                    2a. enable (cascade on update on the table)
                                    2b. update key (for e.g. update asset_id=1 in referencing table where asset_id=2)
                                    2c. disable (cascade on update on the table)
                                    3. delete duplicate record

                                    Best Wishes,
                                    Noman Aftab

                                    http://www.jalandha ri.qsh.eu

                                    [Non-text portions of this message have been removed]

                                    ------------ --------- --------- ------

                                    Yahoo! Groups Links

                                    Disclaimer - August 7, 2009
                                    This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@ yahoogroups. com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                                    This disclaimer was added by Policy Patrol: http://www.policypa trol.com/






                                    [Non-text portions of this message have been removed]
                                  Your message has been successfully submitted and would be delivered to recipients shortly.