Slow SQL Query

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  July 13, 2009 at 10:10 am

  #1024019

  drop it into a text file and attach the text file.

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• maxyogesh2002

  Ten Centuries

  Points: 1062

  More actions

  July 13, 2009 at 10:18 am

  #1024023

  here i m attached the table defination...

  plz check & tell me is anything need to check.

  revert me back ASAP

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 13, 2009 at 10:50 am

  #1024046

  Dave Ballantyne (7/13/2009)

  ---

  Select into will be faster if 'select into/bulkcopy' is set in sp_dbOption as it will be a non-logged operation.

  From Books Online:

  Starting with Microsoft SQL Server 2000, if the recovery model of the database is currently set to FULL, using the select into/bulkcopy option resets the recovery model to BULK_LOGGED. The proper way to change the recovery model is by using the SET RECOVERY clause of the ALTER DATABASE statement.

  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

• maxyogesh2002

  Ten Centuries

  Points: 1062

  More actions

  July 14, 2009 at 3:05 am

  #1024283

  i optimize my SQL Query

  select Distinct Auditdata.ID into Auditdata_sms FROM auditdata AuditData

  INNER JOIN AuditMaster am ON am.ID = AuditData.AuditMaster_ID

  iNNER JOIN HomeCircleMaster hcm ON hcm.Ori_CircleMaster_ID = am.CircleMaster_ID AND hcm.Ori_ServiceTypeMaster_ID = 1 AND hcm.Dest_ServiceTypeMaster_ID = 1

  INNER JOIN AuditTaggingMaster atm ON atm.AuditMaster_ID = am.ID

  INNER JOIN NoSeriesMaster ns on (ns.CircleMaster_ID = am.CircleMaster_ID or ns.CircleMaster_ID = hcm.Dest_CircleMaster_ID) AND ns.ProviderMaster_ID = am.ProviderMaster_ID AND ns.ServiceTypeMaster_ID = 1

  INNER JOIN ProviderMaster_CallTypeMaster pm_ctm ON pm_ctm.ProviderMaster_ID = am.ProviderMaster_ID AND pm_ctm.CallTypeMaster_ID = 101 AND pm_ctm.CallTypeTagValue = AuditData.CallTypeTag

  INNER JOIN NoSeriesMaster_Prefix PD ON SUBSTRING(AuditData.CallTo, 1, CONVERT(INT, PD.PrefixLen)) = PD.PrefixNo AND LEN(AuditData.CallTo) = CONVERT(VARCHAR(10), CONVERT(INT, PD.PrefixLen) + CONVERT(INT, PD.AfterPrefixLen)) AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, len(ns.NoSeries) + CONVERT(INT, PD.PrefixLen))

  WHERE AuditData.TATCallType is NULL AND AuditData.AuditMaster_ID = 74 AND PD.PrefixType = 'SMS'

  now my above query takes 4 minutes to take against 1500000 rows

  using above query i m taking Original table ID to the temp table its take around 4.00 minutes

  but after that i need to update tatcalltype,tatcallunit to original table using ID columns for that i m using below query.

  update Auditdata set tatcalltype=20,tatcallunit=1 from auditdata auditdata

  inner join auditdata_sms a_sms on auditdata.id=a_sms.id

  the above query takes more than 4 minutes whats wrong in above query even both table ID column datatype is integer

  Please reply ASAP

• tosscrosby

  SSCoach

  Points: 18045

  More actions

  July 14, 2009 at 2:18 pm

  #1024705

  maxyogesh2002 (7/13/2009)

  ---

  revert me back ASAP

  I don't think there is any "revert" happening here anytime soon.........

  -- You can't be late until you show up.