z***y
发帖数: 7151 | 1 这是我们印度同事的一封email，是问关于view 和 stored procedure 的。转到这里，
木有标准答案.
=============================================================================
This question is related to the performance of using Stored Procedure OR
Views in the following scenario.
We are building an application for Multiple Organizations (Tenants) and
using the Shared Database approach for maintaining the data. So Every Table
in our database will have TenantId column. Sample Table is shown here:
CREATE TABLE [ent].[tbl_NetworkProfi | m***0
发帖数: 15 | 2 i agree with your company's dba. u will have problem with scalability.
especially when that view is in place, and other developer are using it for
god's know what purpose. and since they are not aware of the indexes on
that table, it can be troublesome down the road.
btw, i won't use 'select *' in your view, should spell out all the columns
in that table instead, this good habit will save you a tons of trouble one
day.
===
Table
【在 z***y 的大作中提到】
: 这是我们印度同事的一封email，是问关于view 和 stored procedure 的。转到这里，
: 木有标准答案.
: =============================================================================
: This question is related to the performance of using Stored Procedure OR
: Views in the following scenario.
: We are building an application for Multiple Organizations (Tenants) and
: using the Shared Database approach for maintaining the data. So Every Table
: in our database will have TenantId column. Sample Table is shown here:
: CREATE TABLE [ent].[tbl_NetworkProfi
| z***y
发帖数: 7151 | 3 不错。
有两点：
１. Ｓｃａｌａｂｉｌｉｔｙ
２. Ｐｅｒｆｏｒｍａｎｃｅ
能讲具体一些更加好！
那个 select * 不是主要的。那并不是一个真正会出现在代码里的语句。
for
columns
【在 m***0 的大作中提到】
: i agree with your company's dba. u will have problem with scalability.
: especially when that view is in place, and other developer are using it for
: god's know what purpose. and since they are not aware of the indexes on
: that table, it can be troublesome down the road.
: btw, i won't use 'select *' in your view, should spell out all the columns
: in that table instead, this good habit will save you a tons of trouble one
: day.
:
: ===
: Table
| m***0
发帖数: 15 | 4 １. Ｓｃａｌａｂｉｌｉｔｙ
this is hard to say, if you manage/use view properly, it's fine. but it
gives false idea to developers as this is that table, use it however you
like, and they start joining that table left and right.
２. Ｐｅｒｆｏｒｍａｎｃｅ
for this particular case, i don't even see index on FK_TenantId, and even
there is, i guess for this table, every row will have a unique FK_TenantId,
so the base is too big for this index, and it's not a good way to retrieve
data based on this column.
【在 z***y 的大作中提到】
: 不错。
: 有两点：
: １. Ｓｃａｌａｂｉｌｉｔｙ
: ２. Ｐｅｒｆｏｒｍａｎｃｅ
: 能讲具体一些更加好！
: 那个 select * 不是主要的。那并不是一个真正会出现在代码里的语句。
:
: for
: columns
| z***y
发帖数: 7151 | 5 Following is my answer,excerpt from my email 个人看法,呵呵。关于更准确的描述
，看BOL....
======================================================================
......blah....
.....Let’s start with the comments from your DBAs. Before I say Amen to my
peers, there are two arguments here:
1. Scalability
a. Stored procedure provides you extra layer to deal with changes down
the road—it can be added with more complex business logic than view does if
needed.
b. There are too many restrictions to use view,
【在 m***0 的大作中提到】
: １. Ｓｃａｌａｂｉｌｉｔｙ
: this is hard to say, if you manage/use view properly, it's fine. but it
: gives false idea to developers as this is that table, use it however you
: like, and they start joining that table left and right.
: ２. Ｐｅｒｆｏｒｍａｎｃｅ
: for this particular case, i don't even see index on FK_TenantId, and even
: there is, i guess for this table, every row will have a unique FK_TenantId,
: so the base is too big for this index, and it's not a good way to retrieve
: data based on this column.
| S***k
发帖数: 370 | 6 SYSTEM_USER returns the login name of the current connection. The value
should be a char type. It seems FK_TenantId is an INT. Of cause, the login
name may be named to some integer value, like ‘1’, ‘2’, or ‘3’. So
when a new login is created, the creator has to keep in mind the login name
must be an integer value. If a login name is changed, the FK_TenantId in all
tables has to be changed accordingly.
A table contains the FK_TenantId and Login Name may be needed to make the
case of changing log | c*****d
发帖数: 6045 | 7 "store procedure have better performance than view"
在oracle里很多dba也赞同这种看法
我不知道他们的观点是从哪本书上看来的
场景是怎么样?
store procedure有如下几个特点,
1. when it is first executed, store procedure is parsed and optimized. execu
te plan remains in memory cache for later use. 这样可以节省大量的cpu时间.但是
,如果对一个view,相同的sql语句,情况是相同的.procedure在这一点上没有优势.
2. reduce network traffic. "store procedure have better performance than vie
w" is true. But it is trivial especially in small application.
3. it is hard to abuse store procedure, but it
【在 z***y 的大作中提到】
: 这是我们印度同事的一封email，是问关于view 和 stored procedure 的。转到这里，
: 木有标准答案.
: =============================================================================
: This question is related to the performance of using Stored Procedure OR
: Views in the following scenario.
: We are building an application for Multiple Organizations (Tenants) and
: using the Shared Database approach for maintaining the data. So Every Table
: in our database will have TenantId column. Sample Table is shown here:
: CREATE TABLE [ent].[tbl_NetworkProfi
| p********l
发帖数: 279 | 8 I agree with you.
A properly indexed view won't be worst than store procedures on perfermance.
execu
但是
vie
【在 c*****d 的大作中提到】
: "store procedure have better performance than view"
: 在oracle里很多dba也赞同这种看法
: 我不知道他们的观点是从哪本书上看来的
: 场景是怎么样?
: store procedure有如下几个特点,
: 1. when it is first executed, store procedure is parsed and optimized. execu
: te plan remains in memory cache for later use. 这样可以节省大量的cpu时间.但是
: ,如果对一个view,相同的sql语句,情况是相同的.procedure在这一点上没有优势.
: 2. reduce network traffic. "store procedure have better performance than vie
: w" is true. But it is trivial especially in small application.
| j*****n
发帖数: 1781 | 9 First I noticed that this is a partitioned table based on [FK_TenantId], the
partition scheme and function are needed for better understanding.
2nd I think this clustered index does not help quires such as
SELECT *
FROM [ent].[tbl_NetworkProfileTemplate]
WHERE FK_TenantId= system_user
a table scan would present no matter SP or View is using. However, since
table is partitioned, the performance could still better.
3rd, since SP is pre-compiled, we will save a little compare to SQL Server
has to p | w*******e
发帖数: 1622 | 10 我同意第3点....对这个view, 不同的user是用不同的execution plan的, 而sproc却是
用的同一个plan.
the
【在 j*****n 的大作中提到】
: First I noticed that this is a partitioned table based on [FK_TenantId], the
: partition scheme and function are needed for better understanding.
: 2nd I think this clustered index does not help quires such as
: SELECT *
: FROM [ent].[tbl_NetworkProfileTemplate]
: WHERE FK_TenantId= system_user
: a table scan would present no matter SP or View is using. However, since
: table is partitioned, the performance could still better.
: 3rd, since SP is pre-compiled, we will save a little compare to SQL Server
: has to p
|
|
