June 2, 2008 at 2:48 am
pls help me find out where is the error of this view and stored procedure.. it taking long time to view the result.. so slow response.. can u figure out what the problem?
this is my view..
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.vw_item_rpt
AS
SELECT DISTINCT
region, division, bedrnm, SUM(ChineseGold) AS ChineseGold, SUM(ChineseGold_wt) AS ChineseGold_wt, SUM(ChineseGold_loan) AS ChineseGold_loan, SUM(Italian) AS Italian, SUM(Italian_wt)
AS Italian_wt, SUM(Italian_loan) AS Italian_loan, SUM(OldGold) AS OldGold, SUM(OldGold_wt) AS OldGold_wt, SUM(OldGold_loan) AS OldGold_loan, SUM(Singapore) AS Singapore,
SUM(Singapore_wt) AS Singapore_wt, SUM(Singapore_loan) AS Singapore_loan, CAST([month] AS int) AS [month], CAST([year] AS int) AS [year]
FROM (SELECT DISTINCT
h.ptn, h.region, h.division, h.bedrnm, h.ChineseGold, h.ChineseGold_wt, h.ChineseGold_loan, h.Italian, h.Italian_wt, h.Italian_loan, h.OldGold, h.OldGold_wt,
h.OldGold_loan, 0 AS Singapore, 0 AS Singapore_wt, 0 AS Singapore_loan, h.month, h.year
FROM (SELECT DISTINCT
g.ptn, g.region, g.division, g.bedrnm, g.ChineseGold, g.ChineseGold_wt, g.ChineseGold_loan, g.Italian, g.Italian_wt, g.Italian_loan, 0 AS OldGold,
0 AS OldGold_wt, 0 AS OldGold_loan, g.month, g.year
FROM (SELECT DISTINCT
f.ptn, f.region, f.division, f.bedrnm, f.ChineseGold, f.ChineseGold_wt, f.ChineseGold_loan, 0 AS Italian, 0 AS Italian_wt, 0 AS Italian_loan,
f.month, f.year
FROM (
a.PTN, y.class_03 AS region, a.division, y.bedrnm, 0 AS ChineseGold,
9 AS ChineseGold_wt, 9 AS ChineseGold_loan,
MONTH(a.sortdate) AS [month], YEAR(a.sortdate)
AS [year]
FROM dbo.tbl_action b INNER JOIN
dbo.tbl_forced a ON b.action_id = a.sortaction_id INNER JOIN
dbo.tbl_amount c ON a.amount = c.amount INNER JOIN
dbo.tbl_item x ON c.item = x.item INNER JOIN
dbo.vw_all y ON a.division = y.bedrnr
WHERE (a.sortaction_id = 1)
GROUP BY a.PTN, a.division, a.division_name, MONTH(a.sortdate),
YEAR(a.sortdate), a.sortaction_id, y.class_03, y.bedrnm,
a.division
UNION ALL
SELECT DISTINCT
a.ptn, y.class_03 AS region, a.division, y.bedrnm,
SUM(x.Quantity) AS ChineseGold, SUM(x.ALL_WT) AS ChineseGold_wt, SUM(c.PTNPrincipal) AS ChineseGold_loan,
month(a.sortdate) AS month, year(a.sortdate) AS year
FROM dbo.tbl_action b INNER JOIN
dbo.tbl_Forced a ON b.action_id = a.sortaction_id INNER JOIN
dbo.tbl_amount c ON a.amount = c.amount INNER JOIN
dbo.tbl_item x ON c.item = x.item INNER JOIN
dbo.vw_all y ON a.division = y.bedrnr
WHERE a.sortaction_id = 6 and sort_code = 'C'
GROUP BY a.PTN, a.division, a.division_name, MONTH(a.sortdate),
YEAR(a.sortdate), a.sortaction_id, y.class_03, y.bedrnm,
a.division) f
UNION ALL
SELECT DISTINCT
a.ptn, y.class_03 AS region, a.division, y.bedrnm, 0 AS ChineseGold, 0 AS ChineseGold_wt, 0 AS ChineseGold_loan, SUM(x.Quantity) as Italian,
SUM(x.ALL_WT) AS Italian_wt, SUM(c.PTNPrincipal) AS Italian_loan, month(a.sortdate) AS month, year(a.sortdate)
AS year
FROM dbo.tbl_action b INNER JOIN
dbo.tbl_forced a ON b.action_id = a.sortaction_id INNER JOIN
dbo.tbl_amount c ON a.amount = c.amount INNER JOIN
dbo.tbl_item x ON c.item = x.item INNER JOIN
dbo.vw_all y ON a.division = y.bedrnr
WHERE a.sortaction_id = 6 and a.sort_code = 'I'
GROUP BY a.PTN, a.division, a.division_name, MONTH(a.sortdate), YEAR(a.sortdate), a.sortaction_id, y.class_03,
y.bedrnm, a.division) g
UNION ALL
SELECT DISTINCT
a.ptn, y.class_03 AS region, a.division, y.bedrnm, 0 AS ChineseGold, 0 AS ChineseGold_wt, 0 AS ChineseGold_loan, 0 AS Italian,
0 AS Italian_wt, 0 AS Italian_loan, SUM(x.Quantity) AS OldGold, SUM(x.ALL_WT) AS OldGold_wt, SUM(c.PTNPrincipal) AS OldGold_loan, month(a.sortdate) AS month, year(a.sortdate)
AS year
FROM dbo.tbl_action b INNER JOIN
dbo.tbl_forced a ON b.action_id = a.sortaction_id INNER JOIN
dbo.tbl_amount c ON a.amount = c.amount INNER JOIN
dbo.tbl_item x ON c.item = x.item INNER JOIN
dbo.vw_all y ON a.division = y.bedrnr
WHERE a.sortaction_id = 6 and a.sort_code = 'OG'
GROUP BY a.PTN, a.division, a.division_name, MONTH(a.sortdate), YEAR(a.sortdate), a.sortaction_id, y.class_03, y.bedrnm,
a.division) h
UNION ALL
SELECT DISTINCT
a.ptn, y.class_03 AS region, a.division, y.bedrnm, 0 AS ChineseGold, 0 AS ChineseGold_wt, 0 AS ChineseGold_loan, 0 AS Italian, 0 AS Italian_wt,
0 AS italian_loan, 0 AS OldGold, 0 AS OldGold_wt, 0 AS OldGold_loan, SUM(x.Quantity) AS Singapore, SUM(x.ALL_WT) AS Singapore_wt, SUM(c.PTNPrincipal) AS Singapore_loan,
month(a.sortdate) AS month, year(a.sortdate) AS year
FROM dbo.tbl_action b INNER JOIN
dbo.tbl_forced a ON b.action_id = a.sortaction_id INNER JOIN
dbo.tbl_amount c ON a.amount = c.amount INNER JOIN
dbo.tbl_item x ON c.item = x.item INNER JOIN
dbo.vw_all y ON a.division = y.bedrnr
WHERE a.sortaction_id = 6 and a.sort_code = 'S'
GROUP BY a.PTN, a.division, a.division_name, MONTH(a.sortdate), YEAR(a.sortdate), a.sortaction_id, y.class_03, y.bedrnm, a.division) i
GROUP BY region, division, bedrnm, [month], [year]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
this is the stored porcedure..
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_item '
set @sqlcommand = @sqlcommand + ' FROM dbo.vw_item_rpt d inner join (select b.class_03 as region, sum(a.ptnprincipal) as loan_prenda_item '
set @sqlcommand = @sqlcommand + ' from tbl_amount a inner join vw_all b on a.division = b.bedrnr '
set @sqlcommand = @sqlcommand + @where1
set @sqlcommand = @sqlcommand + ' and mptn in(select distinct mptn from tbl_item where itemcode >= 100 and itemcode <= 199 '
set @sqlcommand = @sqlcommand + ' and mptn in(select distinct mptn from tbl_amount '
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
thanks..
jay
June 2, 2008 at 3:18 am
First:
Do you really need every DISTINCT and GROUP BY? These are performance killers, so if you don't need it, don't use it. If it is needed, try to first select all required data and finally perform one DISTINCT or GROUP BY to the complete result.
Second:
Try to use JOINS instead of sub-queries. Most of the times this will perform better (test it) and it sure makes reading the query more clear.
June 2, 2008 at 5:11 am
Could you post the execution plans please. (in xml format)
Without that, we're guessing at best.
Also helpful would be the table and index definitions.
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
June 2, 2008 at 6:33 am
what i might to do?.. as a newbie.. i dont know how to use a distinct and group. is it a function or not..? can you share with what is the other function of SQL?..
June 2, 2008 at 6:38 am
Distinct means that SQL will eliminate duplicate rows from the result set. If you don't have duplicate rows (which in your case you won't because of the group by) you shouldn't use it.
Distinct is an expensive operation as it requires the result set to be sorted. Putting DISTINCT into every query even if not necessary is asking for sow-running queries.
Using distinct along with an IN (as in following example) is also unnecessary. IN just checks whether or not a value is present. It doesn't matter how many times its present.
WHERE SolmeColumn IN (SELECT DISTICNT ...
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
June 2, 2008 at 6:02 pm
i've edited my view but the results were the same, slow response.. is it in my db?.. because all my tables has so many data it comes a million.. can you give a sample that the same result as my view...
June 3, 2008 at 12:06 am
Can you post the execution plan please, along with the table and index definitions? If you've ghanged the view please post the revised code.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply