SQL query

• spectra

  SSCarpal Tunnel

  Points: 4120

  More actions

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• spectra

  SSCarpal Tunnel

  Points: 4120

  More actions

  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.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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

• spectra

  SSCarpal Tunnel

  Points: 4120

  More actions

  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.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  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]