Inner join Solution

30 pts.
Tags:
INNER JOIN statement
SQL
i write the following Query to execute against the 1500000 in SQL Server 2000 DECLARE Cur_Prefix CURSOR FOR SELECT PrefixNo,PrefixLen,AfterPrefixLen FROM NoSeriesMaster_Prefix WHERE PrefixType='SMS' order by ID OPEN Cur_Prefix FETCH NEXT FROM Cur_Prefix INTO @PrefixNo,@PrefixLen,@AfterPrefixLen WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLStatement = 'update '+@TABLE+' set AuditData.TATCallType=''12'', AuditData.TATCallUnit=''1'' ' + 'from '+@TABLE+' 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 '+ 'where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,'+@PrefixLen+'))='''+ @PrefixNo + ''' and len(AuditData.CallTo)='+convert(varchar(10),convert(int,@PrefixLen)+convert(int,@AfterPrefixLen))+' and '''+@PrefixNo+'''+ns.NoSeries=Left(AuditData.CallTo,len(ns.NoSeries)+convert(int,'+@PrefixLen+')) and AuditData.AuditMaster_ID='+@AuditMasterID+' ' print(@SQLStatement) exec(@SQLStatement) FETCH NEXT FROM Cur_Prefix INTO @PrefixNo,@PrefixLen,@AfterPrefixLen END CLOSE Cur_Prefix DEALLOCATE Cur_Prefix the above Query take about 1 hrs to execute against 1500000 is any modification is require in the inner join plz help me its urgent

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi –

Are the columns involved in the join indexed?
What is the cardinality of these columns (are they unique)?
Do you have current statistics on these tables?

Have you explained the access path for this statement? EXPLAIN
will give you lots of information about how the data is being
accessed and with that you can probably improve the performance
of this statement.

Good luck.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following