May 31, 2008 at 3:26 am
is anybody here knows how to improve performance of stored procedure and views..?..
this is my sample stored procedure..
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE spReport5
@month int,
@year int,
@prenda_month int,
@prenda_year int
AS
declare @sqlcommand as nvarchar(1000)
declare @where1 as nvarchar(200)
declare @where2 as nvarchar(200)
declare @where3 as nvarchar(100)
declare @lukat as nvarchar(5)
set @lukat = 'LUKAT'
set @where1 = ' where a.transtype <> ''' + @lukat + ''' and cast(month(a.transdate) as int) = ' + convert(nvarchar(2), @prenda_month) + ' and cast(year(a.transdate) as int) = ' + convert(nvarchar(4),@prenda_year)
set @where2 = ' where transtype <> ''' + @lukat + ''' and cast(month(transdate) as int) = ' + convert(nvarchar(2), @prenda_month) + ' and cast(year(transdate) as int) = ' + convert(nvarchar(4),@prenda_year)
set @where3 = ' where d.month = ' + convert(nvarchar(2), @month) + ' and d.year = ' + convert(nvarchar(4),@year)
set @sqlcommand = ' SELECT d.region
, d.ChineseGold
, d.ChineseGold_wt
, d.ChineseGold_loan
, d.Italian
, d.Italian_wt
, d.Italian_loan
, d.OldGold
, d.OldGold_wt
, d.OldGold_loan
, d.Singapore
, d.Singapore_wt
, d.Singapore_loan
, e.loan_prenda_jewelry '
set @sqlcommand = @sqlcommand + ' FROM vw_jewelry_summary_rpt_v1 d inner join (select b.class_03 as region, sum(a.ptnprincipal) as loan_prenda_jewelry '
set @sqlcommand = @sqlcommand + ' from tbl_pt_tran a inner join vw_bedryfluzon b on a.division = b.bedrnr '
set @sqlcommand = @sqlcommand + @where1
set @sqlcommand = @sqlcommand + ' and mptn in(select distinct mptn from tbl_ptn_item where itemcode >= 100 and itemcode <= 199 '
set @sqlcommand = @sqlcommand + ' and mptn in(select distinct mptn from tbl_pt_tran '
set @sqlcommand = @sqlcommand + @where2
set @sqlcommand = @sqlcommand + ' )) group by b.class_03) e on d.region = e.region '
set @sqlcommand = @sqlcommand + @where3
set @sqlcommand = @sqlcommand + ' order by d.region asc '
execute(@sqlcommand)
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
can you help me why is it this sp is so slow and it taking an hour to give an output.. pls help me.. i need your reply..
May 31, 2008 at 9:38 am
With the amount of info you have right here - it's just about impossible to give you anything mucvh to work with. You need to review the execution plan to see what it slow. Looking at the final SQL being executed (probably from profiler after all of the view calls have been "exploded" into their SQL text).
Red flags I can see from here (things to look at):
- you're joining views to views. That almost invariably leads to a perf nosedive. Without knowing what your views are doing, all I can say is - you probably want to avoid that.
- using the functions in the WHERE clause on your date fields the way you are is likely preventing any indexing you might have in place from being used, or from being used efficiently.
- It looks to me that you are "doing too much" in one single statement. You may do better breaking this down into smaller chunks, possibly some indexes temp table, possibly making your views indexed.
Otherwise - without an exec plan, what the views are doing, your table and indexing scheme, and some idea on cardinality - that's about all I can tell you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 31, 2008 at 12:58 pm
Adding to what Matt stated - I see no reason to use dynamic SQL here. This can all be rewritten as a standard select statement. I have rewritten it as:
ALTER PROCEDURE spReport5
@month int,
@year int,
@prenda_month int,
@prenda_year int
AS
declare @lukat as nvarchar(5)
set @lukat = 'LUKAT'
SELECT d.region
, d.ChineseGold
, d.ChineseGold_wt
, d.ChineseGold_loan
, d.Italian
, d.Italian_wt
, d.Italian_loan
, d.OldGold
, d.OldGold_wt
, d.OldGold_loan
, d.Singapore
, d.Singapore_wt
, d.Singapore_loan
, e.loan_prenda_jewelry
FROM vw_jewelry_summary_rpt_v1 d
inner join (
selectb.class_03 as region
, sum(a.ptnprincipal) as loan_prenda_jewelry
from tbl_pt_tran a
inner join vw_bedryfluzon b on a.division = b.bedrnr
where a.transtype <> @lukat
and month(a.transdate) = @prenda_month -- month returns an int, no need to convert
and year(a.transdate) = @prenda_year
and mptn in(select distinct mptn from tbl_ptn_item
where itemcode >= 100 and itemcode <= 199
and mptn in(select distinct mptn from tbl_pt_tran
where transtype <> @lukat and month(transdate) = @prenda_month and year(transdate) = @prenda_year))
group by b.class_03) e
on d.region = e.region
where d.month = @month and d.year = @year
order by d.region asc
return
Notice that I also removed all of the casting/converting since it is not necessary. The Month/Year functions return int already, and the input parameters are declared as int - so there is no reason to cast/convert anything.
Now, the next change you need to make is to remove the functions so SQL Server can use an index. The way you do that is to take the input parameters and convert them to actual dates and modify your criteria to use less than/greater than on the date columns:
[/code]
DECLARE @beginMonth datetime
,@endMonth datetime;
SET @beginMonth = cast(@prenda_year As char(4)) + cast(@prenda_month as char(2)) + '01';
SET @endMonth = dateadd(month, 1, @beginMonth);
-- and use them in your where clauses as...
WHERE a.transdate >= @beginMonth
AND a.transdate < @endMonth
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 1, 2008 at 9:18 pm
thanks for you reply.. i appreciate all.. more than a month i edit this code but i cannot solve.. can you help if i need your help.. because im a newbie.. thanks again..
June 1, 2008 at 9:57 pm
i have a problem also in my view i can't find where is the error.. if i run in sql server enterprise theres an message appear "[microsoft][ODBC SQL SERVER Driver] timeout expire." what does it mean?.. if i run my query one by one it will run no message appear.. can you explain to me what does it mean the message?..
June 1, 2008 at 10:01 pm
If I understand correctly, you have a view that is timing out. Can you post the definition of the view so we can see what is happening?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 1, 2008 at 10:03 pm
jryan_pun (6/1/2008)
thanks for you reply.. i appreciate all.. more than a month i edit this code but i cannot solve.. can you help if i need your help.. because im a newbie.. thanks again..
Any time you need help, go ahead and post here and I am sure somebody will be able to help you out.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2008 at 12:24 am
jryan_pun (6/1/2008)
thanks for you reply.. i appreciate all.. more than a month i edit this code but i cannot solve.. can you help if i need your help.. because im a newbie.. thanks again..
We need more information to help you.
Table definitions
View definitions
Index defined on those tables
Execution plan (in xml format, zipped and attached to your post)
Aprox row counts involved
How long the query currently takes to run
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply