ansaurus

Question

Answer 1

+3 A:

I would try to post the query beautified. Otherwise it is hard to help. Try to explain also what are you trying to do so. There are 6 Inner joins. Which one would you like to change. Anyway. Your question seems to be an quite similar to This one. Please don't post questions twice.

Apart from that It seems that you are trying to optimize this query. Why don't you just ask who to make it faster? In my humble opinion using subquerys will make everything worse. If you need help optimizing we would need more information like the table structure, indexes, number of rows, etc...

select distinct Auditdata.ID, 
       ns.ProviderMaster_ID as CDRComment
  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 AuditData.CallTo like PD.PrefixNo + '%'
                                    AND AuditData.calltolen = PD.PrefixLen + PD.AfterPrefixLen
                                    AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, NoSeriesLen + PD.PrefixLen)
 where AuditData.TATCallType is null
   and AuditData.AuditMaster_ID = 74
   AND PD.PrefixType = 'SMS

borjab 2009-07-15 12:55:06

Can we remove the inner join from above Query and write subquery instead of inner join

John 2009-07-15 13:00:40

so how can Fast Above Query....

John 2009-07-15 15:20:17

one question is that... is inner join run row by row or not ..means just like loops???

John 2009-07-15 15:27:28

Of course it depends on the database implementatio but inner joins are usually executed in parallel so they are quite efficient. A good index policy can help a lot. You could try to find the execution plan to find inneficiencies.

borjab 2009-07-16 06:44:09

ansaurus

tags:

views:

answers:

Convert inner join to SQL Subquery

related questions