Performance Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Performance Issue

Author

Topic

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-30 : 01:28:22

I have a querystring inside a cursor. In my query string 6 insert statements are there. So my sp is taking time. How to reduce the time?
Please help me.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-30 : 01:31:11

Did you really need cursors?
For what purpose you are using cursor?

If you post that SP, then we can suggest

--
Chandu

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-30 : 01:39:21

Could you also post execution plan?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 01:40:17

in most cases you can write cursor logic in a set based manner which will perform much better
If you can post what you're trying to implement using some sample data and output someone will suggest a set based alternative.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-30 : 01:42:38

It is also worth doublechecking you're not experiencing any other delaying issues - such as blocking.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-30 : 02:15:55

Thanks all for ur reply.
This is my cursor.

Declare curGroup cursor local fast_forward
for select answervalue,metric,brand,datayear,label,timeid from #answervalue1
Open curGroup
Fetch curGroup into @answervalue,@metric1,@brandname,@datayear,@label,@timeId
while (@@FETCH_STATUS=0)
BEGIN
select dv.variable,row_number() over (order by dv.variable) Rownumber into #ListofVariable from dimvariable dv,dimlabelmapping dlm where dv.label=dlm.label and dlm.Category not in ('category','Main Brand')
and dlm.metric = @group and dv.[Year]=@datayear and dlm.[Year]=@datayear

select @variable = variable from #ListofVariable where Rownumber = 1
select @variable1 = variable from #ListofVariable where Rownumber = 2
select @variable2 = variable from #ListofVariable where Rownumber = 3

select @metricquery = @metricquery +' ('+ a.variable + ' = ' + convert(varchar(10),@answervalue) + ') OR' from #variabletable1 a where a.Metric=@metric1 and a.datayear=@datayear
select @databasename = [database] from #databasetable where metric = @metric1 and datayear=@datayear
select @databasename1 = [database] FROM [dimlabelmapping] where metric = 'month' and [YEAR]=@datayear and Category not in ('category','Main Brand')
select @mapvariable = Variable from dimvariable dm where Label = 'month' and [YEAR] =@datayear

set @QueryString = 'Insert into #tempVolumevariable select '+convert(varchar(10),@datayear)+','''+@label+''','+convert(varchar(10),@timeid)+','''+@variable+''','+@variable+','''+@brandname+''', '''+@metric1+''' ,'+convert(varchar(10),@answervalue)+', sum(convert(float,(t2.w0))),count(distinct(t2.v0)) from '+@databasename+' t1 ,'+@databasename1+' t2
where t1.v0=t2.v0 and t1.'+@mapvariable+'=t2.'+@mapvariable+' ' +@FilterQuery +'and (' +@metricquery +') and '+@timequery+' group by '+@variable+'; '
+'Insert into #tempVolumevariable select '+convert(varchar(10),@datayear)+','''+@label+''','+convert(varchar(10),@timeid)+','''+@variable1+''','+@variable1+','''+@brandname+''', '''+@metric1+''' ,'+convert(varchar(10),@answervalue)+', sum(convert(float,(t2.w0))),count(distinct(t2.v0)) from '+@databasename+' t1 ,'+@databasename1+' t2
where t1.v0=t2.v0 and t1.'+@mapvariable+'=t2.'+@mapvariable+' ' +@FilterQuery +'and (' +@metricquery +') and '+@timequery+' group by '+@variable1+'; '
+'Insert into #tempVolumevariable select '+convert(varchar(10),@datayear)+','''+@label+''','+convert(varchar(10),@timeid)+','''+@variable2+''','+@variable2+','''+@brandname+''', '''+@metric1+''' ,'+convert(varchar(10),@answervalue)+', sum(convert(float,(t2.w0))),count(distinct(t2.v0)) from '+@databasename+' t1 ,'+@databasename1+' t2
where t1.v0=t2.v0 and t1.'+@mapvariable+'=t2.'+@mapvariable+' ' +@FilterQuery +'and (' +@metricquery +') and '+@timequery+' group by '+@variable2+'; '

set @QueryString1 = @QueryString + 'Insert into #tempVolumevariable select '+convert(varchar(10),@datayear)+','''+@label+''','+convert(varchar(10),@timeid)+','''+@variable+''',0,'''+@brandname+''', '''+@metric1+''' ,'+convert(varchar(10),@answervalue)+', sum(convert(float,(t2.w0))),count(distinct(t2.v0)) from '+@databasename+' t1 ,'+@databasename1+' t2
where t1.v0=t2.v0 and t1.'+@mapvariable+'=t2.'+@mapvariable+' ' +@FilterQuery +'and (' +@metricquery +') and '+@timequery+'; '
+'Insert into #tempVolumevariable select '+convert(varchar(10),@datayear)+','''+@label+''','+convert(varchar(10),@timeid)+','''+@variable1+''',0,'''+@brandname+''', '''+@metric1+''' ,'+convert(varchar(10),@answervalue)+', sum(convert(float,(t2.w0))),count(distinct(t2.v0)) from '+@databasename+' t1 ,'+@databasename1+' t2
where t1.v0=t2.v0 and t1.'+@mapvariable+'=t2.'+@mapvariable+' ' +@FilterQuery +'and (' +@metricquery +') and '+@timequery+'; '
+'Insert into #tempVolumevariable select '+convert(varchar(10),@datayear)+','''+@label+''','+convert(varchar(10),@timeid)+','''+@variable2+''',0,'''+@brandname+''', '''+@metric1+''' ,'+convert(varchar(10),@answervalue)+', sum(convert(float,(t2.w0))),count(distinct(t2.v0)) from '+@databasename+' t1 ,'+@databasename1+' t2
where t1.v0=t2.v0 and t1.'+@mapvariable+'=t2.'+@mapvariable+' ' +@FilterQuery +'and (' +@metricquery +') and '+@timequery+'; '
print 'qq'
print @QueryString
exec (@QueryString1)

set @DefBaseQuery = 'Insert into #DefBaseTable1 select '+convert(varchar,@datayear)+','''+@label+''','''+@variable+''','+@variable+', '''+@brandname+''', '''+@metric1+''' ,sum(convert(float,(w0))),count(distinct(v0)) from '+@databasename1+' t2 where 1=1 '+@filterquery+' and '+@timequery+' group by '+@variable+'; '
+'Insert into #DefBaseTable1 select '+convert(varchar,@datayear)+','''+@label+''','''+@variable1+''','+@variable1+', '''+@brandname+''', '''+@metric1+''' ,sum(convert(float,(w0))),count(distinct(v0)) from '+@databasename1+' t2 where 1=1 '+@filterquery+' and '+@timequery+' group by '+@variable1+'; '
+'Insert into #DefBaseTable1 select '+convert(varchar,@datayear)+','''+@label+''','''+@variable2+''','+@variable2+', '''+@brandname+''', '''+@metric1+''' ,sum(convert(float,(w0))),count(distinct(v0)) from '+@databasename1+' t2 where 1=1 '+@filterquery+' and '+@timequery+' group by '+@variable2+'; '

set @DefBaseQuery = @DefBaseQuery + 'Insert into #DefBaseTable1 select '+convert(varchar,@datayear)+','''+@label+''','''+@variable+''',0, '''+@brandname+''', '''+@metric1+''' ,sum(convert(float,(w0))),count(distinct(v0)) from '+@databasename1+' t2 where 1=1 '+@filterquery+' and '+@timequery+'; '
+'Insert into #DefBaseTable1 select '+convert(varchar,@datayear)+','''+@label+''','''+@variable1+''',0, '''+@brandname+''', '''+@metric1+''' ,sum(convert(float,(w0))),count(distinct(v0)) from '+@databasename1+' t2 where 1=1 '+@filterquery+' and '+@timequery+'; '
+'Insert into #DefBaseTable1 select '+convert(varchar,@datayear)+','''+@label+''','''+@variable2+''',0, '''+@brandname+''', '''+@metric1+''' ,sum(convert(float,(w0))),count(distinct(v0)) from '+@databasename1+' t2 where 1=1 '+@filterquery+' and '+@timequery+'; '

exec (@DefBaseQuery)

Fetch curGroup into @answervalue,@metric1,@brandname,@datayear,@label,@timeId
END
Close curGroup
deallocate curGroup

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 02:26:59

I repeat

If you can post what you're trying to implement using some sample data and output someone will suggest a set based alternative

I dont think anybody will have patience to look through the code you posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-30 : 02:51:08

Sorry i dont have time to prepare sample data. Actually this query string is taking too much time. Can u tell any other option without using query string.?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 03:40:40

quote:
Originally posted by suriyarupa

Sorry i dont have time to prepare sample data. Actually this query string is taking too much time. Can u tell any other option without using query string.?

I cant suggest anything unless I understand what your actual requirement is which is why I suggested to post some sample data to illustrate the same!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-30 : 04:30:08

I have following table.
Table name - answervalue1

Brand answervalue Metric Label datayear TimeId
~%base Coca-Cola 1 daily drinkers Jan 11 12MMT 2011 1
~%base Coca-Cola 1 daily drinkers Feb 11 12MMT 2011 2
~%base Coca-Cola 1 daily drinkers Mar 11 12MMT 2011 3
Coca-Cola 2 daily drinkers Jan 11 12MMT 2011 1
Coca-Cola 2 daily drinkers Feb 11 12MMT 2011 2
Coca-Cola 2 daily drinkers Mar 11 12MMT 2011 3

For each label,metric,answervalue,year.... I want to find volume.

This is my output

year timeinterval SubGroup timevariable brandname Metric Volume SampleSize Significance
2011 Jan 11 12MMT Total 1 Coca-Cola daily drinkers 0.090669447 615075 -1
2011 Jan 11 12MMT 13-18 Years 1 Coca-Cola daily drinkers 0.104762381 326 0
2011 Jan 11 12MMT 19-24 Years 1 Coca-Cola daily drinkers 0.09141765 271 0
2011 Jan 11 12MMT 25-34 Years 1 Coca-Cola daily drinkers 0.111113303 515 0
2011 Jan 11 12MMT 35-49 Years 1 Coca-Cola daily drinkers 0.097389269 769 0

I am creating cursor for answervalue table.
For each label, I have 3 variables.
For eg... a1,a2,a3 are the variables for label Jan 11 12MMT

a1,a2,a3 are the column names which exists in one more table called Base_2011

From these a1,a2,a3 columns, I have to find timeintervals(13-18 Years,19-24 Years).
Then for all timeintervals, I need to calculate volume from Base_2011 table.

So this Base_2011 table will be varying for each label. Becos of this I am using querystring.

Now can anyone help me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 04:38:09

where do you get subgroup information from? also what are the rules for aclculating Volumne etc?
Please post data within code tags to ensure correct alignment

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-30 : 04:47:45

From a1,a2,a3 columns, i will get values like 1,2,3,4,5,6
These values I have to map with mappingtable to find the sub groups. sub groups are (13-18 Years,19-24 Years).
In Base_2011 table, I have a column called v0. For each sub groups, I will get the v0.
Then for each label like 'Jan 11 12MMT', I have one more base table called UP_Base_2011.
Fron this table I will get default volume for each label. Then v0/defaultvolume is the final volume.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-30 : 04:51:20

Its getting really confusing Would you mind posting all required tables data in below format along with output?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-30 : 05:21:11

quote:

Sorry i dont have time to prepare sample data. Actually this query string is taking too much time. Can u tell any other option without using query string.?

add 10g ram?

suriyarupa
Starting Member

19 Posts

Posted - 2013-05-30 : 05:24:21

Its ok vishak. Thanks for ur replies. It will be like confusing only.
In my sp, I am using many temp tables also. I am trying to reduce temp tables.
If u know any other option instead of using querystring, pls tell me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources