Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Customize SQL Query Expand / Collapse
Author
Message
Posted Saturday, July 11, 2009 8:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 26, 2010 12:59 AM
Points: 42, Visits: 108
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...

Post #751591
Posted Saturday, July 11, 2009 9:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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
Post #751609
Posted Saturday, July 11, 2009 10:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 7,161, Visits: 13,234
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: 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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #751616
Posted Saturday, July 11, 2009 12:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 26, 2010 12:59 AM
Points: 42, Visits: 108
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.....
Post #751635
Posted Saturday, July 11, 2009 1:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 7,161, Visits: 13,234
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #751640
Posted Saturday, July 11, 2009 1:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 26, 2010 12:59 AM
Points: 42, Visits: 108
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
Post #751642
Posted Saturday, July 11, 2009 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 7,161, Visits: 13,234
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #751643
Posted Monday, July 13, 2009 12:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 26, 2010 12:59 AM
Points: 42, Visits: 108
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
Post #751811
Posted Monday, July 13, 2009 1:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 26, 2010 12:59 AM
Points: 42, Visits: 108
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
Post #751827
Posted Monday, July 13, 2009 10:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 7,161, Visits: 13,234
Hi,

I'm a little confused...
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #752228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse