really needed SPs - 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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

really needed SPs

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2013-04-15 : 21:57:12

Hi

Currently we are struggling with performance. Our report server is stand alone server 2008 R2 and shared point 2010 retrieving report in native mode. Data in db tables are not huge. Still rendering time is more than 8 sec. Report scripts are T-SQL. If we convert these Scripts in stored proc then is there any gain in performance really??

Need expert advice.

T.I.A

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-15 : 23:31:09

Many reasons for poor performance.

You need to examine the queries and their execution plans 1st.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-16 : 02:27:35

yep..definitely making them procedures will have performance benefit as optimizer can cache and reuse the plan when possible

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

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2013-04-16 : 12:25:42

thank you for reply

i have 5 input parameter out of which 4 are optional and if i go for SP then i have to write SP (usp_myreport) which create runtime query and the use in report to get columns

DECLARE @runtime_tbl TABLE (ID int, Name varchar(1000), IdNum int, Description varchar(8000),
Rating varchar(50), Own varchar(4000), TargetDate datetime, Recommended varchar(8000)
, Status varchar(8000))

INSERT @runtime_tbl (ID , Name , IdNum , Description,
Rating , Own , TargetDate , Recommended
, Status )

EXEC [dbo].[usp_myreport] @pEff, @pSplit,
@pFromTgtDt,@pToTgtDt,@pIncl

SELECT
ID , Name , IdNum , Description,
Rating , Own , TargetDate , Recommended
, Status
FROM @runtime_tbl

Correct? these steps required?

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-17 : 01:02:22

why are you trying to populate table from outside? why cant you do insert inside procedure?

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

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2013-04-19 : 11:11:50

I am not sure...but have a look at below and let me know performance point of view any thing needed......

** Imp--- Can I avoid table variable (inside proc or outside proc) here as it is taking extra step to create and use .......????????

I am using Stored Proc taking one input string

like

Create Proc test_test @str varchar(8000) -- XXX~null~yyy
as

/* here i am splitting string to my actual query parameter using patindex, substring, stuff.

and get

@para1 = XXX
@para2 = null
@para3 = yyy
*/
-- Now actual query start
/*
if @para1 = 'null'
begin
set @QueryStr = 'Select **********'
end
else
begin
set @QueryStr1 = 'Select **********'
set @QueryStr2 = 'Select **********'

-- Please let me know why i was getting error.....I was using only one variable as @QueryStr1 varchar(8000) but i was getting error due to my query length extended to max length but when i chk that one then it was more than 4000 but less than 5000 so i decided to take two variables for exec (@var1 + @var2)
--

end

if @para1 = 'null'
begin
exec (@QueryStr1 + @QueryStr2)
end
else
exec (@QueryStr)

end

*/

Now in report file if i take query type as text and write as

exec test_test @str

I am getting nothing as column for that dataset

So i am taking table variable and insert there and then populating columns for my report.

like (above post)

DECLARE @runtime_tbl TABLE (ID int, Name varchar(1000), IdNum int, Description varchar(8000),
Rating varchar(50), Own varchar(4000), TargetDate datetime, Recommended varchar(8000)
, Status varchar(8000))

INSERT @runtime_tbl (ID , Name , IdNum , Description,
Rating , Own , TargetDate , Recommended
, Status )

EXEC [dbo].[usp_myreport] @pEff, @pSplit,
@pFromTgtDt,@pToTgtDt,@pIncl

SELECT
ID , Name , IdNum , Description,
Rating , Own , TargetDate , Recommended
, Status
FROM @runtime_tbl

Please let me know if any modification needed or i am going away from performance by doing these steps.

T.I.A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources