SQL Server Performance

Order by Clause

Discussion in 'T-SQL Performance Tuning for Developers' started by darakhshan, Apr 24, 2006.

  1. darakhshan New Member


    SELECT CALLEDNUMBER FROM [CURRENTI] D
    INNER JOIN
    (select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes
    order by len(replacedcode) desc
    )c
    ON
    D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'
    AND
    D.CODEB IS NULL


    could i use order by in subquery if not then wats the solution
    i dont want to use cursor for Updation
  2. FrankKalis Moderator

    ORDER BY in the JOIN clause is unnecessary. What about


    SELECT CALLEDNUMBER FROM [CURRENTI] D
    INNER JOIN
    (select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes)c
    ON D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'
    AND D.CODEB IS NULL
    ORDER BY...

    May I say, that using LIKE in JOINs is not the best choice to get good performance.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  3. darakhshan New Member

    SELECT CALLEDNUMBER FROM [CURRENTI] DINNER JOIN(select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes)cON D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'AND D.CODEB IS NULLORDER BY...

    if i want to update the codes to its maximum to minimum
    Like if the callednubmer is 02158265
    and the replacementcode table have 2 codes 215 and 21
    the the Updation should b like this codeb=0215
    else id destinationnumber is 216 then 21

  4. Madhivanan Moderator

    Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  5. Adriaan New Member

    Coupe of things on this expression:
    D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'

    If you add the leading zero on the REPLACEDCODE column, and remove any leading zeroes - before trying to run the update - then performance should already improve. (This looks like a one-time update query, so performance is probably not a big issue, unless you have little time to run it.)

    If REPLACEDCODE is a numeric column, then you might try changing it to a type that matches the CALLEDNUMBER column - which would appear to be varchar or similar. This takes out the implicit conversion.
  6. darakhshan New Member

    Let say we have a table called replacement code
    cityformatch code
    Turkey Mobile 00901
    Turkey 0090

    if i wil select the table like this
    Select * from replacemetncode order by len(code) desc
    IT wil give the code from maximum to its minimum length

    So the others table have the callednumber
    like
    Callednumber
    00901
    0090
    00905


    All the callednumber must match to the maximum lenth of the
    replacementcode table
    like
    callednumber 00901 must match with Turkey Mobile 00901
    while
    callednumber 0090 must match with Turkey 0090 of the replacementcode table





  7. Madhivanan Moderator

    what is the datatype of code? If it is varchar then no need of appending 0

    Madhivanan

    Failing to plan is Planning to fail
  8. darakhshan New Member

    the question is how wil i update the CURRENTI table because in subquery
    v can'bt specify the order by clause to select the maximum order to the minimum order length of code

    Update D
    set b.codeb=c.codeb
    FROM [CURRENTI] D
    INNER JOIN
    (select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes
    order by len(replacedcode) desc)c
    ON
    D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'
    AND
    D.CODEB IS NULL
  9. Madhivanan Moderator

    You can use Top 100 percent in subquery

    Update D
    set b.codeb=c.codeb
    FROM [CURRENTI] D
    INNER JOIN
    (select Top 100 percent CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes
    order by len(replacedcode) desc)c
    ON
    D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'
    AND
    D.CODEB IS NULL


    Madhivanan

    Failing to plan is Planning to fail
  10. Adriaan New Member

    You need to have a WHERE NOT EXISTS subquery to verify that there is no longer matching replacementcode entry:

    Update D
    set b.codeb=c.codeb
    FROM [CURRENTI] D
    INNER JOIN
    (select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes)c
    ON D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'
    WHERE D.CODEB IS NULL
    AND NOT EXISTS
    (SELECT * FROM [MACHINE-09].POPS.dbo.replacementcodes X
    WHERE LEN(X.ReplacedCode) > LEN(C.ReplacedCode)
    AND X.ReplacedCode LIKE C.ReplacedCode + '%')
  11. Adriaan New Member

    Just spotted this error in your code:

    Update D
    set b.codeb=c.codeb
    FROM [CURRENTI] D

    The second line should of course be this:

    set D.codeb=c.codeb
  12. darakhshan New Member

    i have table name updatedcitya follwing the data

    22
    22
    2202
    2203
    2204
    2205
    2206
    2207
    221
    221
    221
    221
    221
    221
    222
    2221
    2221
    223
    2231
    2232
    2232
    2232
    2233
    2237
    2237
    2238
    224
    226
    227
    228
    229
    229
    2292

    select top 100 percent * from standard.dbo.updatedcitya where code like '22%' order by len(code) desc
    it wil produce the result from maximum to its minimum length
    but it wil not Update the other table called Test
    having the following callednumber column

    021545
    0223
    02238
    02202

    but the resut is
    testcodeb
    22
    22
    22



    please see this Update statement tel me wat is wrong with that

    Update T
    set testcodeb=c.code
    ---select *
    from Test T inner join
    (
    select top 100 percent * from standard.dbo.updatedcitya where code like '22%' order by len(code) desc )C
    on
    t.callednumber like '0'+rtrim(c.code)+'%'
    and
    t.testcodeb is null
  13. cmdr_skywalker New Member

    I am not sure of all the patterns since you did not post sample data from all tables (i.e. what should be the correct testcodeb for the above situation so we can set the select statement. If you could post the DDL with DML to generate sample data, then maybe we can post this accurately. Check this out though.

    UPDATE T
    SET testcodeb = c.code
    FROM
    TEST T,
    (SELECT
    code,
    MAX(codelen) maxcode
    FROM (
    SELECT code, len(code) codelen
    FROM standard.dbo.updatedcitya u
    WHERE
    left(code,2) = '22'
    ) MU
    GROUP BY code
    ) MAXT
    WHERE
    T.testcodeb is null
    AND T.callednumber like '0'+rtrim(MAXT.code)+'%'
    )

    the inner should return the code with only the maximum length. I cannot determine if the query should use the CHARINDEX instead of like since I don't have all the data. Anyway, you may get an idea from this.



    May the Almighty God bless us all!
    www.empoweredinformation.com
  14. Adriaan New Member

    If my bring this to your attention again:


    quote:Originally posted by Adriaan

    You need to have a WHERE NOT EXISTS subquery to verify that there is no longer matching replacementcode entry:

    Update D
    set b.codeb=c.codeb
    FROM [CURRENTI] D
    INNER JOIN
    (select CITYB,CODEB,REPLACEDCODE from [MACHINE-09].POPS.dbo.replacementcodes)c
    ON D.CALLEDNUMBER LIKE '0'+LTRIM(RTRIM(C.REPLACEDCODE))+'%'
    WHERE D.CODEB IS NULL
    AND NOT EXISTS
    (SELECT * FROM [MACHINE-09].POPS.dbo.replacementcodes X
    WHERE LEN(X.ReplacedCode) > LEN(C.ReplacedCode)
    AND X.ReplacedCode LIKE C.ReplacedCode + '%')

Share This Page