Post reply

SQL query

  • June 4, 2015 at 12:15 am

    #317903

    Could you please tell how do I optimize this SQL query ? ( I am using MySql)

    select DU.kUserId, DU.kUserName , DU.email,DU.contactNo,SID.intDtlId,SID.cbFlag,SID.pcbt,G1.gName as gen1,G2.gName as gen2 from (select * from (select intDtlId ,cbFlag,cUserId,pcbt,stsId,actedOn,startedOn,actedBy from student_dtl order by intDtlId desc)as temp group by cUserId) as SID left join CGTBL_ as CG ON SID.stsId = CG.cgncId left join CGANT_ as CA ON SID.actedBy =CA.cgntId

    left join dst_user_ as DU ON SID.cUserId =DU.kUserId

    left join PBCDTL_ as PBR ON SID.cUserId = PBR.userId left join GNC_ as G1 ON PBR.pcById = G1.gnId"

    left join GNC_ as G2 ON PBR.sourceId = G2.gnId

    where CA.cgntId = ? and (SID.stsId=?

    OR (SID.stsId=? and SID.actedOn is null)) and SID.startedOn >= ? and SID.startedOn < ?

    and DU.kUserId is not null order by SID.intDtlId asc

  • June 4, 2015 at 7:18 am

    #1802976

    spectra (6/4/2015)

    Could you please tell how do I optimize this SQL query ? ( I am using MySql)

    select DU.kUserId, DU.kUserName , DU.email,DU.contactNo,SID.intDtlId,SID.cbFlag,SID.pcbt,G1.gName as gen1,G2.gName as gen2 from (select * from (select intDtlId ,cbFlag,cUserId,pcbt,stsId,actedOn,startedOn,actedBy from student_dtl order by intDtlId desc)as temp group by cUserId) as SID left join CGTBL_ as CG ON SID.stsId = CG.cgncId left join CGANT_ as CA ON SID.actedBy =CA.cgntId

    left join dst_user_ as DU ON SID.cUserId =DU.kUserId

    left join PBCDTL_ as PBR ON SID.cUserId = PBR.userId left join GNC_ as G1 ON PBR.pcById = G1.gnId"

    left join GNC_ as G2 ON PBR.sourceId = G2.gnId

    where CA.cgntId = ? and (SID.stsId=?

    OR (SID.stsId=? and SID.actedOn is null)) and SID.startedOn >= ? and SID.startedOn < ?

    and DU.kUserId is not null order by SID.intDtlId asc

    -- Try this:

    select

    DU.kUserId,

    DU.kUserName ,

    DU.email,

    DU.contactNo,

    SID.intDtlId,

    SID.cbFlag,

    SID.pcbt,

    G1.gName as gen1,

    G2.gName as gen2

    from (

    select

    intDtlId, -- output

    cbFlag, -- output

    cUserId, -- join/output

    pcbt,

    stsId, -- join/filter

    actedOn, -- filter

    startedOn, -- filter

    actedBy -- join

    from student_dtl

    GROUP BY intDtlId, cbFlag, cUserId, pcbt, stsId, actedOn, startedOn, actedBy

    ) as SID

    left join CGTBL_ as CG ON SID.stsId = CG.cgncId

    INNER join CGANT_ as CA ON SID.actedBy = CA.cgntId

    INNER join dst_user_ as DU ON SID.cUserId = DU.kUserId

    left join PBCDTL_ as PBR ON SID.cUserId = PBR.userId

    left join GNC_ as G1 ON PBR.pcById = G1.gnId --" hanging double-quote

    left join GNC_ as G2 ON PBR.sourceId = G2.gnId

    where CA.cgntId = ?

    and (SID.stsId=? OR (SID.stsId=? and SID.actedOn is null))

    and SID.startedOn >= ?

    and SID.startedOn < ?

    and DU.kUserId is not null

    order by SID.intDtlId asc

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 4, 2015 at 7:36 am

    #1802984

    You'll probably get a better answer on the MySQL forums. This site is for Microsoft SQL Server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2015 at 8:44 am

    #1803028

    Dear @ChrisM@Work

    I had

    .....from student_dtl order by intDtlId desc)

    did you miss this ?

    I wanted to join with the latest record of every profile in this table.

  • June 4, 2015 at 8:50 am

    #1803030

    spectra (6/4/2015)

    Dear @ChrisM@Work

    I had

    .....from student_dtl order by intDtlId desc)

    did you miss this ?

    I wanted to join with the latest record of every profile in this table.

    In SQL Server it would raise an error so I removed it. That's why Gail is recommending that you post on a MYSQL forum - the dialect differences could make this a frustrating exercise.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 4, 2015 at 8:53 am

    #1803032

    Ok. not an issue. Is it possible to write this in pure SQL way ? (so that I don't face database migration issue later)

    My query works fine. I am trying to optimize it.

  • June 4, 2015 at 8:57 am

    #1803038

    spectra (6/4/2015)

    Ok. not an issue. Is it possible to write this in pure SQL way ? (so that I don't face database migration issue later)

    My query works fine. I am trying to optimize it.

    You could try ROW_NUMBER:

    select

    DU.kUserId,

    DU.kUserName ,

    DU.email,

    DU.contactNo,

    SID.intDtlId,

    SID.cbFlag,

    SID.pcbt,

    G1.gName as gen1,

    G2.gName as gen2

    from (

    SELECT

    ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS rn,

    intDtlId, -- output

    cbFlag, -- output

    cUserId, -- join/output

    pcbt,

    stsId, -- join/filter

    actedOn, -- filter

    startedOn, -- filter

    actedBy -- join

    from student_dtl

    GROUP BY intDtlId, cbFlag, cUserId, pcbt, stsId, actedOn, startedOn, actedBy

    ) as SID

    left join CGTBL_ as CG ON SID.stsId = CG.cgncId

    INNER join CGANT_ as CA ON SID.actedBy = CA.cgntId

    INNER join dst_user_ as DU ON SID.cUserId = DU.kUserId

    left join PBCDTL_ as PBR ON SID.cUserId = PBR.userId

    left join GNC_ as G1 ON PBR.pcById = G1.gnId --" hanging double-quote

    left join GNC_ as G2 ON PBR.sourceId = G2.gnId

    where SID.rn = 1

    AND CA.cgntId = ?

    and (SID.stsId=? OR (SID.stsId=? and SID.actedOn is null))

    and SID.startedOn >= ?

    and SID.startedOn < ?

    and DU.kUserId is not null

    order by SID.intDtlId asc

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • June 4, 2015 at 12:51 pm

    #1803126

    ChrisM@Work (6/4/2015)

    spectra (6/4/2015)

    Ok. not an issue. Is it possible to write this in pure SQL way ? (so that I don't face database migration issue later)

    My query works fine. I am trying to optimize it.

    You could try ROW_NUMBER:

    MySQL doesn't have the ROW_NUMBER function.

    The differences between T-SQL and MySQL's flavour of SQL is going to make tuning the query more frustrating than trying to catch mosquitoes with chopsticks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2015 at 3:28 pm

    #1803184

    GilaMonster (6/4/2015)

    ChrisM@Work (6/4/2015)

    spectra (6/4/2015)

    Ok. not an issue. Is it possible to write this in pure SQL way ? (so that I don't face database migration issue later)

    My query works fine. I am trying to optimize it.

    You could try ROW_NUMBER:

    MySQL doesn't have the ROW_NUMBER function.

    The differences between T-SQL and MySQL's flavour of SQL is going to make tuning the query more frustrating than trying to catch mosquitoes with chopsticks.

    Haha! I like that.

    DB2 has ROW_NUMBER. It was an assumption. They should end here. I know this can be done in SQL2K flavour but that's a copout if MYSQL has a tool for the job.

    [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]

Viewing 9 posts - 1 through 8 (of 8 total)

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