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.
  • 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