SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 really needed SPs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

under2811
Constraint Violating Yak Guru

India
359 Posts

Posted - 04/15/2013 :  21:57:12  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/15/2013 :  23:31:09  Show Profile  Visit russell's Homepage  Reply with Quote
Many reasons for poor performance.

You need to examine the queries and their execution plans 1st.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/16/2013 :  02:27:35  Show Profile  Reply with Quote
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
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
359 Posts

Posted - 04/16/2013 :  12:25:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/17/2013 :  01:02:22  Show Profile  Reply with Quote
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
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
359 Posts

Posted - 04/19/2013 :  11:11:50  Show Profile  Reply with Quote
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

Edited by - under2811 on 04/19/2013 11:19:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000