整理sproc [英] Tidying up of sproc
问题描述
嗨
我写过这个sproc。任何人都可以建议对
主查询及其子查询进行任何优化吗？
之后截断DDL创建程序dbo.spGetUnbilledInfoByClient
（
@ClientID varchar（5），
@Sort int = 0，
@Dir int = 0
）AS
设定NOCOUNT ON
- 主查询
SELECT mmatter，mdesc1，tkfirst +''''+ tklast AS联系人，
CONVERT（varchar（10），mopendt， 103）作为[Date]，CONVERT（DECIMAL（18,2），
tTime.Cost）AS COST，tTime.Hours，tDisbursements.Disbursements
来自物质
INNER JOIN timekeep ON mbillaty = timekeep.tkinit
lEFT JOIN（
SELECT cmatter，SUM（Disbursements）AS付款来自（
SELECT dbo.cost.cmatter，
CASE WHEN dbo.cost.cauth =''SO''或dbo.cost.cauth =''CF' '
那么dbo.cost.cbillamt * 1.175
ELSE
dbo.cost.cbillamt
END AS支付
来自dbo.cost
WHERE（dbo.cost.cstatus =' b''）AND（dbo.cost.cinvoice是空的）和
（左（dbo.cost.cmatter，5）= @ClientID）
）AS费用
GROUP BY cmatter
）AS tDisbursements
ON dbo.matter.mmatter = tDisbursements.cmatter
INNER JOIN
（
SELECT dbo.timecard.tmatter，SUM（dbo.timecard.tbillhrs）AS Hours，
SUM（dbo.timecard.tbilldol）AS费用
来自dbo.timecard加入dbo.batch ON dbo.timecard.tbatch =
dbo.batch.bbatch
WHERE（dbo.timecard.tinvoice为空）
AND（dbo.timecard.tstatus =''b''）
AND（LEFT（dbo。 timecard.tmatter，5）= @ClientID）
AND（dbo.timecard.tbilldt IS NULL）
AND（不是dbo.batch.bfindt IS NULL）
GROUP BY tmatter
）AS tTime
ON dbo.matter.mmatter = tTime.tmatter
- -Sort（参见 http://www.sqlteam.com/forums/topic=\"42&whichpage=3 ）
ORDER BY
案例 - 数字字段
WHEN @Sort = 4 AND @Dir = 0
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 0
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 0
那么tDisbursements.Disbursements
END DESC，
CASE - datetime
WHEN @Sort = 0 AND @Dir = 0
THEN mopendt
WHEN @Sort = 3 AND @Dir = 0
THEN mopendt
END DESC，
CASE - 字符字段
WHEN @Sort = 1 AND @Dir = 0
THEN mdesc1
WHEN @Sort = 2 AND @ Dir = 0
那么tkfirst +''''+ tklast
END DESC，
- ASC
案例 - 数字字段
WHEN @Sort = 4 AND @Dir = 1
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 1
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 1
那么tDisbursements.Disbursements
END ASC，
CASE - datetime
WHEN @Sort = 0 AND @Dir = 1
THEN mopendt
WHEN @Sort = 3 AND @Dir = 1
那么mopendt
结束ASC，
案例 - 字符字段
WHEN @Sort = 1 AND @Dir = 1
那么mdesc1
WHEN @Sort = 2 AND @Dir = 1
那么tkfirst +''''+ tklast
结束ASC
GO
CREATE TABLE [问题]（
[mname] [varchar]（24）COLLATE Latin1_General_CI_AS NULL ，
[mmatter] [varchar]（15）COLLATE Latin1_General_CI_AS NULL，
[mrtcode] [smallint] NULL，
[mdept] [ varchar]（10）COLLATE Latin1_General_CI_AS NULL，
[mopendt] [datetime] NULL，
[mbillaty] [varchar]（8）COLLATE Latin1_General_CI_AS NULL，
[mdesc1] [varchar]（48）COLLATE Latin1_General_CI_AS NULL
）ON [PRIMARY]
GO
CREATE TABLE [cost]（
[cindex] [int] IDENTITY（1,1）NOT NULL，
[cmatter] [varchar]（15）COLLATE Latin1_General_CI_AS NULL，
[camount] [money] NULL，
[cbilldt] [datetime ] NULL，
[cbillamt] [money] NULL，
[cinvoice] [int] NULL，
[ccode] [varchar]（ 7）COLLATE Latin1_General_CI_AS NULL，
[cbatch] [int] NULL，
[cauth] [varchar]（10）COLLATE Latin1_General_CI_AS NULL
）ON [PRIMARY]
GO
创建表[costcode]（
[cocode] [varchar]（7）COLLATE Latin1_General_CI_AS NULL，
[codesc1] [varchar]（48）COLLATE Latin1_General_CI_AS NULL
）ON [PRIMARY]
GO
CREATE TABLE [timecard]（
[tindex] [int] IDENTITY（1,1）NOT NULL，
[tmatter] [varchar]（15）COLLATE Latin1_General_CI_AS NULL，
[ttk] [varchar]（8）COLLATE Latin1_General_CI_AS NULL，
[tbilldt] [datetime] NULL，
[tbillhrs] [decimal]（16,2）NULL，
[tbillrt] [decimal]（16,2）NULL，
[tbilldol] [money] NULL，
[tinvoice] [int] NULL，
[tcode] [varchar]（7）COLLATE Latin1_General_CI_AS NULL，
[tbatch] [int] NULL
）ON [PRIMARY]
GO
创建表[批处理]（
[bbatch] [int] IDENTITY（1,1）NOT NULL，
[btype] [varchar]（2）COLLATE Latin1_General_CI_AS NULL，
[bfindt] [datetime] NULL，
[bop] [varchar]（8）COLLATE Latin1_General_CI_AS NULL，
[bper] [varchar]（ 4）COLLATE Latin1_General_CI_AS NULL，
[bpflag] [varchar]（1）COLLATE Latin1_General_CI_AS NULL，
[bdopen] [datetime] NULL，
[btothrs] [小数]（12,2）NULL，
[btotdol] [money] NULL
）ON [PRIMARY]
GO
Hi
I''ve written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after
CREATE PROCEDURE dbo.spGetUnbilledInfoByClient
(
@ClientID varchar(5),
@Sort int = 0,
@Dir int = 0
) AS
SET NOCOUNT ON
-- The Main Query
SELECT mmatter, mdesc1, tkfirst + '' '' + tklast AS Contact,
CONVERT(varchar(10), mopendt, 103) As [Date], CONVERT(DECIMAL(18,2),
tTime.Cost) AS COST, tTime.Hours, tDisbursements.Disbursements
FROM matter
INNER JOIN timekeep ON mbillaty = timekeep.tkinit
lEFT JOIN (
SELECT cmatter, SUM(Disbursements) AS Disbursements FROM (
SELECT dbo.cost.cmatter,
CASE WHEN dbo.cost.cauth = ''SO'' OR dbo.cost.cauth = ''CF''
THEN dbo.cost.cbillamt * 1.175
ELSE
dbo.cost.cbillamt
END AS Disbursements
FROM dbo.cost
WHERE (dbo.cost.cstatus = ''b'') AND (dbo.cost.cinvoice IS NULL) AND
(LEFT(dbo.cost.cmatter, 5) = @ClientID)
) AS costs
GROUP BY cmatter
) AS tDisbursements
ON dbo.matter.mmatter = tDisbursements.cmatter
INNER JOIN
(
SELECT dbo.timecard.tmatter, SUM(dbo.timecard.tbillhrs) AS Hours,
SUM(dbo.timecard.tbilldol) AS Cost
FROM dbo.timecard JOIN dbo.batch ON dbo.timecard.tbatch =
dbo.batch.bbatch
WHERE (dbo.timecard.tinvoice IS NULL)
AND (dbo.timecard.tstatus = ''b'')
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)
AND (dbo.timecard.tbilldt IS NULL)
AND ( NOT dbo.batch.bfindt IS NULL)
GROUP BY tmatter
) AS tTime
ON dbo.matter.mmatter = tTime.tmatter
--Sort (see http://www.sqlteam.com/forums/topic....42&whichpage=3)
ORDER BY
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 0
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 0
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 0
THEN tDisbursements.Disbursements
END DESC,
CASE -- datetime
WHEN @Sort = 0 AND @Dir = 0
THEN mopendt
WHEN @Sort = 3 AND @Dir = 0
THEN mopendt
END DESC,
CASE -- character fields
WHEN @Sort = 1 AND @Dir = 0
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 0
THEN tkfirst + '' '' + tklast
END DESC,
-- ASC
CASE -- numeric fields
WHEN @Sort = 4 AND @Dir = 1
THEN tTime.Cost
WHEN @Sort = 5 AND @Dir = 1
THEN tTime.Hours
WHEN @Sort = 6 AND @Dir = 1
THEN tDisbursements.Disbursements
END ASC,
CASE -- datetime
WHEN @Sort = 0 AND @Dir = 1
THEN mopendt
WHEN @Sort = 3 AND @Dir = 1
THEN mopendt
END ASC,
CASE -- character fields
WHEN @Sort = 1 AND @Dir = 1
THEN mdesc1
WHEN @Sort = 2 AND @Dir = 1
THEN tkfirst + '' '' + tklast
END ASC
GO
CREATE TABLE [matter] (
[mname] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[mmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[mrtcode] [smallint] NULL ,
[mdept] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[mopendt] [datetime] NULL ,
[mbillaty] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[mdesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [cost] (
[cindex] [int] IDENTITY (1, 1) NOT NULL ,
[cmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[camount] [money] NULL ,
[cbilldt] [datetime] NULL ,
[cbillamt] [money] NULL ,
[cinvoice] [int] NULL ,
[ccode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[cbatch] [int] NULL ,
[cauth] [varchar] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [costcode] (
[cocode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[codesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [timecard] (
[tindex] [int] IDENTITY (1, 1) NOT NULL ,
[tmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ttk] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[tbilldt] [datetime] NULL ,
[tbillhrs] [decimal](16, 2) NULL ,
[tbillrt] [decimal](16, 2) NULL ,
[tbilldol] [money] NULL ,
[tinvoice] [int] NULL ,
[tcode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[tbatch] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [batch] (
[bbatch] [int] IDENTITY (1, 1) NOT NULL ,
[btype] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[bfindt] [datetime] NULL ,
[bop] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[bper] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[bpflag] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[bdopen] [datetime] NULL ,
[btothrs] [decimal](12, 2) NULL ,
[btotdol] [money] NULL
) ON [PRIMARY]
GO
推荐答案
Samuel Hon（no*****@samuelhon.co.uk）写道：
Samuel Hon (no*****@samuelhon.co.uk) writes:
我写过这篇文章。任何人都可以建议对主要查询及其子查询进行任何优化吗？在
之后截断DDL
当你不知道
表中的内容以及索引是什么时，很难优化查询。但这里有一件事，
可能是一个严重的问题：
（LEFT（dbo.cost.cmatter，5）= @ClientID）
...
AND（LEFT（dbo.timecard.tmatter，5）= @ClientID）
I''ve written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after
It is difficult to optimize queries when you don''t know what''s in the
tables, and what the indexes are. But there is one thing here, which
is likely to be a serious issue:
(LEFT(dbo.cost.cmatter, 5) = @ClientID)
...
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)
如果你有这两列的索引，不太可能使用
。因为当你只是表达式中的表列时，SQL Server
不能再使用该索引来搜索值，因为索引
是根据原始值构建的。最好的情况是，SQL Server可以扫描
整个索引，但在这样的查询中很少会成为赢家。
您可以尝试将条件重写为
dbo.cost.cmatter LIKE @ClientID +''％''
另一种方法是向表中添加计算列： br />
clientid AS离开（cmatter，5）
然后索引该列。
注意对于要使用的计算列的索引，您必须在以下所有这些
设置：ANSI_NULLS，QUOTED_IDENTIFIER，ANSI_WARNINGS，ANSI_PADDING，
CONCAT_NULLS_YIELDS_NULL和ARITHABORT。在这两个中，ANSI_NULLS和
QUOTED_IDENTIFIER与存储过程一起保存 - 它不是适用的
运行时设置。默认情况下，所有这些都在运行时打开，
但是某些工具，企业管理器和OSQL，默认情况下没有QUOTED_IDENT
。大多数库默认情况下不会设置ARITHABORT。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/ books.asp
感谢Erland，我的帖子是在我完成打字之前被发送的
在点击时不小心
我会根据你的建议调查左问题。
你认为这个问题有什么问题吗？我选择了什么？
通常推荐这种方式吗？
再次感谢
Sam
Erland Sommarskog< es **** @ sommarskog.se>在消息新闻中写道：< Xn ********************** @ 127.0.0.1> ...
Thanks Erland, my post was sent before I''d finished typing by me being
careless when clicking
I''ll investigate the ''LEFT'' issue with your suggestions.
Do you think that there is any problem with the sub-selects I''ve used?
Is this approach normally recommended?
Thanks again
Sam
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
Samuel Hon（ no*****@samuelhon.co.uk）写道：
Samuel Hon (no*****@samuelhon.co.uk) writes:
我写了这篇文章。任何人都可以建议对主要查询及其子查询进行任何优化吗？在
I''ve written this sproc. Can any one suggest any optimisations to the
main query and its sub-queries please? Truncated DDL after
之后截断DDL
当你不知道
表中的内容以及索引是什么时，很难优化查询。但这里有一件事，这可能是一个严重的问题：
It is difficult to optimize queries when you don''t know what''s in the
tables, and what the indexes are. But there is one thing here, which
is likely to be a serious issue:
（LEFT（dbo.cost.cmatter，5）= @ClientID）
...
AND（LEFT（dbo.timecard.tmatter，5）= @ClientID）
(LEFT(dbo.cost.cmatter, 5) = @ClientID)
...
AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)
如果你有这两列的索引，它们不太可能使用
。因为当你只是表达式中的表列时，SQL Server不能再使用该索引来搜索值，因为索引是根据原始值构建的。最好的情况是，SQL Server可以扫描整个索引，但在这样的查询中很少会成为赢家。
您可以尝试重写条件
dbo.cost.cmatter LIKE @ClientID +''％''
另一种方法是将计算列添加到表中：
clientid AS left（cmatter，5）<然后索引该列。
请注意，对于要使用的计算列的索引，您必须在以下所有这些
设置：ANSI_NULLS，QUOTED_IDENTIFIER，ANSI_WARNINGS，ANSI_PADDING ，
CONCAT_NULLS_YIELDS_NULL和ARITHABORT。在这两个中，ANSI_NULLS和
QUOTED_IDENTIFIER与存储过程一起保存 - 它不适用于
运行时设置。默认情况下，所有这些都在运行时启用，但是某些工具（企业管理器和OSQL）默认情况下没有启用QUOTED_IDENT
。大多数库默认情况下不会设置ARITHABORT。
If you have indexes on these two columns, they are not very likely to be
used. Because when you but a table column in an expression, SQL Server
can no longer use that index to search for values, because the index
were built from the original values. At best, SQL Server can scan the
entire index, but this is rarely a winner in a query like this.
You could try rewriting the conditions as
dbo.cost.cmatter LIKE @ClientID + ''%''
Another alternative is to add computed columns to the table:
clientid AS left(cmatter, 5)
and then index that column.
Note that for index on computed column to be used, you must all these
settings on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDING,
CONCAT_NULLS_YIELDS_NULL and ARITHABORT. Of these two, ANSI_NULLS and
QUOTED_IDENTIFIER are saved with the stored procedure - it is not the
run-time settings that apply. All these are on by default at run-time,
but some tools, Enterprise Manager and OSQL, do not have QUOTED_IDENT
on by default. And most libraries does not set ARITHABORT ON by default.
Samuel Hon（no*****@samuelhon.co.uk）写道：
Samuel Hon (no*****@samuelhon.co.uk) writes:
你觉得我用过的子选择有什么问题吗？
这种方法通常是推荐吗？
Do you think that there is any problem with the sub-selects I''ve used?
Is this approach normally recommended?
要使用正确的术语，它们是派生表，因为它们会显示在查询中可以有表格的地方。
派生表非常编写SQL查询时的强大工具。 SQL Server
可以免费重新排序评估订单，只要它不影响
结果，并且SQL Server经常做得很好。在最坏的情况下，它们就像
临时表一样，除了它们从未实现。所以我认为
你的派生表很好。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf .. .2000 / books.asp
这篇关于整理sproc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
