Post reply

Using Stored Procedure Or View (Scenario descriobed in the Message Section)

  • November 1, 2008 at 9:50 pm

    #200862

    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_NetworkProfileTemplate](

    [PK_NetworkProfileTemplateId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [ProfileTemplateName] [nvarchar](20) NOT NULL,

    [ProfileTemplateDescription] [nvarchar](255) NULL,

    [FK_TenantId] [int] NOT NULL,

    CONSTRAINT [PK_tbl_NetworkProfileTemplate] PRIMARY KEY CLUSTERED

    (

    [PK_NetworkProfileTemplateId] ASC,

    [FK_TenantId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [TenantPScheme]([FK_TenantId])

    )

    Now I want to Protect (filter) the data at the database level: If User belonging to Tenant Id =1 logins into the application that user will see records only for that Tenant. And I want this filtration of data at the Database level so developers writing the code in the business layer doesnot need to worry about the filtration of data.

    So there are possibly two solutions for this

    1.Create stored procedures for each table to fetch, insert, delete and update the records. The logic in the stored procedure will filter the records based upon the Connection with the Database. (Each Tenant will have separate database User)

    2.Create views of each table that will filter out the records based upon the Connection with the database.

    Create VIEW [ent].[vw_NetworkProfileTemplate]

    AS

    SELECT

    *

    FROM [ent].[tbl_NetworkProfileTemplate]

    WHERE FK_TenantId= system_user

    I see lot of advantages of using views in my applications

    Many applications these days are created where we write inline queries and stored procedures also, so using views does not effect our development of Middle layer too much. Also these days we are using OR Mapper tools so this will be helpful in case of views but not in stored procedures approach.

    Some of my company DBA’s told me that there is considerable performance hit and scalability issues when using Views, so use Stored procedures. I did some research on that and see the execution plan for the various queries like fetching the data from these tables using joins with other tables etc. but execution plan is exactly same.

    My question is that the view which I have created above is slower or have scalability issues compared to using the stored procedures?

    Please answer by looking by my View. This view is very simple and have only one where clause in it.

  • November 1, 2008 at 11:38 pm

    #892786

    Views are no worse than "Inline Views" like CTEs or Derived Tables (sub-queries that are used like tables)... proper views, that is.

    The problem with a good number of views is that people tend to make them do too much or return too much data in some vain effort to encapsulate functionality. Then, too, is how some people use them... for example... if you use a criteria in a query that uses a view, and the criteria is something like WHERE DailyTotal <> 0, AND DailyTotal is actually an aggregated column, then the whole bloody view must resolve to get a return.

    So, it all "depends" just like any other code you might write... how well it was written and how you use it will make a view or break it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply