Post reply

Need Query

  • February 8, 2014 at 4:44 am

    #289333

    Hi,

    I have a two table. One Table having Country code and country name details. Another table having

    Customer mobile nos and Its around 24 crore records. I need to update country in customer mobile details table.

    Sample Records:

    Table1:

    1323XXXXXX

    1240XXXXXX

    1215XXXXXX

    1682XXXXXX

    1403XXXXXX

    Table2:

    Code Country

    1 USA

    1204 Canada

    1242 Bahamas

    1246 Barbados

    1250 Canada

    1264 Anguilla

    1268 AntiguaandBarbuda

    1284 VirginIslands(UK)

    1289 Canada

    1306 Canada

    1340 VirginIslands(US)

    1345 CaymanIslands

    1403 Canada

    1416 Canada

    1418 Canada

    Query I'm using:

    select a.MobileNo,CCD.Country

    into tempCountry

    from CustomerMobileno(nolock) a Left join Country_Code_Name CCD(nolock)

    on CCD.code=left(a.Dialed_number,len(CCD.code))

    That query take more time to update country name. Pls help me.

  • February 8, 2014 at 5:29 am

    #1687308

    Please post the actual execution plan as a .sqlplan file. If you're not sure how to do this, you can find instructions in this article by Gail Shaw[/url].

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • February 8, 2014 at 7:47 am

    #1687322

    Hi,

    Pls find the plan details.

  • February 8, 2014 at 8:05 am

    #1687324

    That's great, thanks. What else can you tell us about your update?

    Are you only updating country if it's null in the target table? What if it is already populated?

    You're using a left join in your query - is this deliberate, so that non-matches will update country to null in the target?

    Do you know anything about the distribution of LEN(CCD.code) in the source table? This will tell you:

    SELECT LEN(CCD.code), COUNT(*)

    FROM Tariff_Blucher_Country_Code CCD

    GROUP BY LEN(CCD.code)

    Are you able to create indexes on these tables?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • February 10, 2014 at 2:10 am

    #1687455

    Hi,

    Ya i want to update country.For left join, If any non matches means the country should be null.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply