Customize SQL Query

  • Dear Friends

    i m using the following which Execute against 1500000 rows

    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 takes 60 minutes to run against 1500000 rows

    is any customization require in above query

    please help me its urgent

    thanx in advance...

  • Hello,

    Obviously I don’t know the structure of your DB, but would it not be possible to somehow replace the Cursor and Dynamic SQL with a Join on the NoSeriesMaster_Prefix table?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • Hi,

    the following part of your code is a little confusing to me:

    ... 'update '+@TABLE+' set AuditData.TATCallType=''12'', AuditData.TATCallUnit=''1'' ' ...

    If I'm reading your code correctly you're referring to @table with the alias [AuditData]. But your update statement refers still to @table instead of the alias [AuditData].

    I'd expect the statement would fail with the error

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "AuditData.TATCallType" could not be bound.

    Could you please provide a sample of one value for @SQLStatement for verification?

    To follow John's recommendation I would create one dynamic SQL per @TABLE variable using a join to NoSeriesMaster_Prefix instead of a cursor.

    @john-2: As far as I can see the OP has a number of tables with identical structure he's referring to with the variable @TABLE to perform updates on the common columns TATCallType and TATCallUnit. Seems like some sort of "horizontal split table" I guess...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Dear Friends,

    Thanks for your Reply...

    Actually the above code is my Store Procedure i m passing value to @Table and @AuditMasterID through the front end

    i m passing value to the below SP through the Query Analyzer is follows:

    exec USP_12 'Auditdata','24'

    My SP is as follows:

    CREATE Procedure USP_12(@AuditMasterID as varchar(10),@TABLE as Varchar(50))

    as

    BEGIN

    Declare @SQLStatement varchar(2000)

    Declare @PrefixNo varchar(20)

    Declare @PrefixLen varchar(20)

    Declare @AfterPrefixLen varchar(20)

    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

    end

    plz help me

    what should i do.....

  • Hi,

    please check if the following code will help you to get the expected results.

    IMPORTANT NOTE:

    Since there are no table definitions nor any sample data the code below is completely untested and should be only used a something to start with on your test system. Therefore, it does not contain any data modifaction.

    SET @SQLStatement =

    'SELECT AuditData.*, ''CallType=12'', ''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

    INNER JOIN NoSeriesMaster_Prefix nsp

    ON nsp.PrefixNo = substring(AuditData.CallTo,1,convert(int,PrefixLen))

    AND nsp.PrefixType=''SMS''

    and len(AuditData.CallTo) = convert(varchar(10),convert(int,nsp.PrefixLen)+convert(int,nsp.AfterPrefixLen))

    where AuditData.TATCallType is null

    and nsp.PrefixNo + ns.NoSeries = Left(AuditData.CallTo,len(ns.NoSeries)+convert(int,PrefixLen))

    and AuditData.AuditMaster_ID=' +@AuditMasterID + ''

    print(@SQLStatement)



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Dear Friends,

    thanks for your Reply..

    if u want to make any modfication in my Above SQL Query u can do that..

    Because Currently i m testing the Query on Test Server.

    Thanks in Advance

  • It's hard to make any tested/verified modification to a query without having any table definition or data to test against.

    If you'd like us to have a more detailed look at the query then I kindly ask you to provide table definition (including index definition), sample data and expected result as described in my signature.

    Other than the answer I already gave I don't see much more room for improvement/modification without any more detailed information from your side.

    You need to help us help you.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Dear Friends

    Thanks for your reply

    if i run below query its take more than 35 minutes

    insert into sms_tatcalltype Select distinct AuditData.ID,'12','1','null' 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 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.CallTypeTagValue=AuditData.CallTypeTag

    where AuditData.TATCallType is null and pm_ctm.CallTypeMaster_ID=101 and substring(AuditData.CallTo,1,convert(int,2))='91' and len(AuditData.CallTo)=12 and '91'+ns.NoSeries=Left(AuditData.CallTo,len(ns.NoSeries)+convert(int,2)) and AuditData.AuditMaster_ID=74

  • Hi,

    Even when i execute below Simple Query its take more than 20 minutes

    update Auditdata set tatcalltype = 'null' where tatcalltype is null

    tatcalltype datatype is varchar

    i put the index on tatcalltype column

  • Hi,

    I'm a little confused... :ermm:

    your first and your second post both describe a rather large update statement.

    However, your third post is regarding an insert statement and in the last post you're talking about a completely different, but more simple update statement.

    I would have expected data definition and sample data for the first case rather than two new issues leaving the first one unanswered at the same time...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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