Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. sql server comparing two huge tables

sql server comparing two huge tables

Scheduled Pinned Locked Moved Database
databasehelpsql-serversysadmin
14 Posts 4 Posters 1 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • P ps_prakash02

    Hi Guys, I'm in need of your help. my problem is, i have two tables namely MASTER and LINECONTENT. "MASTER" table contains 5,00,000 records, The columns in MASTER table is masterid int, masterdesc varchar(1000). "LINECONTENT" table contains more than 1000 records & its columns are pageno int, lineno int, content varchar(2000). I want to compare each row of "MASTER" table, masterdesc column value with the "LINECONTENT" table content column, If any records matched i want to take the matched master record and store it in another table. for this i used the below query. select b.* from linecontent a join master b on a.content like '%'+b.masterdesc+'%' but this query is taking much. more than 15 minutes i want to decrease the time give me some suggestions handle this problem thank you regards, prakash

    M Offline
    M Offline
    Mycroft Holmes
    wrote on last edited by
    #2

    Throw an index on MasterDesc Try and use charindex in the where clause - it may not help, just an idea.

    Never underestimate the power of human stupidity RAH

    P 1 Reply Last reply
    0
    • P ps_prakash02

      Hi Guys, I'm in need of your help. my problem is, i have two tables namely MASTER and LINECONTENT. "MASTER" table contains 5,00,000 records, The columns in MASTER table is masterid int, masterdesc varchar(1000). "LINECONTENT" table contains more than 1000 records & its columns are pageno int, lineno int, content varchar(2000). I want to compare each row of "MASTER" table, masterdesc column value with the "LINECONTENT" table content column, If any records matched i want to take the matched master record and store it in another table. for this i used the below query. select b.* from linecontent a join master b on a.content like '%'+b.masterdesc+'%' but this query is taking much. more than 15 minutes i want to decrease the time give me some suggestions handle this problem thank you regards, prakash

      B Offline
      B Offline
      Blue_Boy
      wrote on last edited by
      #3

      Try this select master.masterdesc from master ,linecontent where master.masterdesc = linecontent.content

      I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

      P 1 Reply Last reply
      0
      • M Mycroft Holmes

        Throw an index on MasterDesc Try and use charindex in the where clause - it may not help, just an idea.

        Never underestimate the power of human stupidity RAH

        P Offline
        P Offline
        ps_prakash02
        wrote on last edited by
        #4

        Hi, Thanks for your reply. sorry i forgot tell you that i have created nonclustered index in both tables. In "MASTER" i have created non-clustered index for masterdesc column similarly in "LINECONTENT" table i have created non-clustered index for "content" field. even though its taking time. thanks & regards P.Prakash

        D 1 Reply Last reply
        0
        • B Blue_Boy

          Try this select master.masterdesc from master ,linecontent where master.masterdesc = linecontent.content

          I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

          P Offline
          P Offline
          ps_prakash02
          wrote on last edited by
          #5

          Hi, Thanks for your reply. i tried for your solution but still i'm having same performance just give me some solutions thanks & Regards Prakash

          B 1 Reply Last reply
          0
          • P ps_prakash02

            Hi, Thanks for your reply. sorry i forgot tell you that i have created nonclustered index in both tables. In "MASTER" i have created non-clustered index for masterdesc column similarly in "LINECONTENT" table i have created non-clustered index for "content" field. even though its taking time. thanks & regards P.Prakash

            D Offline
            D Offline
            David Skelly
            wrote on last edited by
            #6

            Putting an index on masterdesc will not make any difference to this query because you are using a leading wildcard: LIKE '%abcde%'. The query optimiser will not even try to use an index for this: it will just go through the rows one by one checking each in turn, because that's all it can do. So basically the query will take every row in LINECONTENT and test it against every row in MASTER. That's why it's taking so long.

            P 1 Reply Last reply
            0
            • D David Skelly

              Putting an index on masterdesc will not make any difference to this query because you are using a leading wildcard: LIKE '%abcde%'. The query optimiser will not even try to use an index for this: it will just go through the rows one by one checking each in turn, because that's all it can do. So basically the query will take every row in LINECONTENT and test it against every row in MASTER. That's why it's taking so long.

              P Offline
              P Offline
              ps_prakash02
              wrote on last edited by
              #7

              Hi, but my functionality is like that. i want to compare "master" table with "linecontent" table and matching rows should be taken. so i have written like that but its taking nearly 15 minutes. i want to decrease the time to some 2 to 3 minutes. please tell me the solution Thanks & Regards, P.Prakash

              D 1 Reply Last reply
              0
              • P ps_prakash02

                Hi, Thanks for your reply. i tried for your solution but still i'm having same performance just give me some solutions thanks & Regards Prakash

                B Offline
                B Offline
                Blue_Boy
                wrote on last edited by
                #8

                Perhaps you should check this result.[^]

                I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

                P 1 Reply Last reply
                0
                • P ps_prakash02

                  Hi, but my functionality is like that. i want to compare "master" table with "linecontent" table and matching rows should be taken. so i have written like that but its taking nearly 15 minutes. i want to decrease the time to some 2 to 3 minutes. please tell me the solution Thanks & Regards, P.Prakash

                  D Offline
                  D Offline
                  David Skelly
                  wrote on last edited by
                  #9

                  I understand what you are trying to do. The problem is that a leading wildcard search is a performance killer, end of story. There is no quick fix or easy answer. Depending on which version of SQL Server you are using, you may be able to use Full Text indexing. I can't remember whether that was added at SQL Server 2005 or SQL Server 2008 off the top of my head. Otherwise, I would recommend looking at something like Lucene or Microsoft Search Server to see how they work, as they do something similar to what you are trying to achieve.

                  M P 3 Replies Last reply
                  0
                  • D David Skelly

                    I understand what you are trying to do. The problem is that a leading wildcard search is a performance killer, end of story. There is no quick fix or easy answer. Depending on which version of SQL Server you are using, you may be able to use Full Text indexing. I can't remember whether that was added at SQL Server 2005 or SQL Server 2008 off the top of my head. Otherwise, I would recommend looking at something like Lucene or Microsoft Search Server to see how they work, as they do something similar to what you are trying to achieve.

                    M Offline
                    M Offline
                    Mycroft Holmes
                    wrote on last edited by
                    #10

                    I wonder if he tested charindex and if it give a better performance. I would be surprised if it did but needs testing.

                    Never underestimate the power of human stupidity RAH

                    P 1 Reply Last reply
                    0
                    • D David Skelly

                      I understand what you are trying to do. The problem is that a leading wildcard search is a performance killer, end of story. There is no quick fix or easy answer. Depending on which version of SQL Server you are using, you may be able to use Full Text indexing. I can't remember whether that was added at SQL Server 2005 or SQL Server 2008 off the top of my head. Otherwise, I would recommend looking at something like Lucene or Microsoft Search Server to see how they work, as they do something similar to what you are trying to achieve.

                      P Offline
                      P Offline
                      ps_prakash02
                      wrote on last edited by
                      #11

                      Hi, thanks for your answer, we are using sql server 2005 i'll look for those two options you have specified. regards, P.Prakash

                      1 Reply Last reply
                      0
                      • M Mycroft Holmes

                        I wonder if he tested charindex and if it give a better performance. I would be surprised if it did but needs testing.

                        Never underestimate the power of human stupidity RAH

                        P Offline
                        P Offline
                        ps_prakash02
                        wrote on last edited by
                        #12

                        Hi, I could not understand what you have said. Could me please explain me deeply. Thanks & Regards, P.Prakash

                        1 Reply Last reply
                        0
                        • B Blue_Boy

                          Perhaps you should check this result.[^]

                          I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

                          P Offline
                          P Offline
                          ps_prakash02
                          wrote on last edited by
                          #13

                          Hi, thank u i'm looking for it regards Prakash

                          1 Reply Last reply
                          0
                          • D David Skelly

                            I understand what you are trying to do. The problem is that a leading wildcard search is a performance killer, end of story. There is no quick fix or easy answer. Depending on which version of SQL Server you are using, you may be able to use Full Text indexing. I can't remember whether that was added at SQL Server 2005 or SQL Server 2008 off the top of my head. Otherwise, I would recommend looking at something like Lucene or Microsoft Search Server to see how they work, as they do something similar to what you are trying to achieve.

                            P Offline
                            P Offline
                            ps_prakash02
                            wrote on last edited by
                            #14

                            Hi David Skelly, for this problem can we use table partition method. will it help me to solve the problem. Thanks & Regards, Prakash

                            1 Reply Last reply
                            0
                            Reply
                            • Reply as topic
                            Log in to reply
                            • Oldest to Newest
                            • Newest to Oldest
                            • Most Votes

                            • Login
                            • Don't have an account? Register
                            • Login or register to search.
                            • First post
                              Last post
                            0
                            • Categories
                            • Recent
                            • Tags
                            • Popular
                            • World
                            • Users
                            • Groups