Customize SQL Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Customize SQL Query

Customize SQL Query

Rate Topic

Display Mode

Topic Options

Author

Message

maxyogesh2002

Posted Saturday, July 11, 2009 8:58 AM

SSC 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

John H Marsh

Posted Saturday, July 11, 2009 9:42 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, June 14, 2013 9:18 AM
Points: 1,325, Visits: 1,389

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

LutzM

Posted Saturday, July 11, 2009 10:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:04 PM
Points: 6,739, Visits: 12,167

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

maxyogesh2002

Posted Saturday, July 11, 2009 12:49 PM

SSC 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

LutzM

Posted Saturday, July 11, 2009 1:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:04 PM
Points: 6,739, Visits: 12,167

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)

Post #751640

maxyogesh2002

Posted Saturday, July 11, 2009 1:48 PM

SSC 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

LutzM

Posted Saturday, July 11, 2009 2:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:04 PM
Points: 6,739, Visits: 12,167

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.

Post #751643

maxyogesh2002

Posted Monday, July 13, 2009 12:26 AM

SSC 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

maxyogesh2002

Posted Monday, July 13, 2009 1:40 AM

SSC 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

LutzM

Posted Monday, July 13, 2009 10:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:04 PM
Points: 6,739, Visits: 12,167

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...

Post #752228

« Prev Topic | Next Topic »

Permissions