To View or not to View
-
Hi-
I have a question regarding the usage of Views in my system.
We have 3 "Layers" in our system - Relationships, Clients, and Accounts.
Relationships own Clients and Clients own Accounts.
At each level, I want to display total dollars and counts of things.
Relationship Example:
Relationship X has 3 Clients, 7 Accounts, and 15 dollars.
I don't want to "Pre-Aggregate" these counts and store them in the DB
since new Accounts or Account ownership changes really throw a wrench in
things.
So, I have created 3 views which do the "Summing" and "Counting" aggregations.
I cannot index the views since I do not have the "Enterprise" edition of
SQL Server and I feel that joining to the views is slowing me down a bit.
If I join to a non-indexed view - what happens regarding locking, etc..
During a join does the whole view have to be created, then joined?
If so - should I use a table lock on the view?
-
SQL Server has been around for a while and the nerds developing SQL Server have already thought of your situation.
In SQL Analyzer create an SQL Statement that performs your agrregation. Now, press CTRL-L or press the "Display the Estimated Execution Plan" button on the menu.
Read the execution plan, back to front <------.
Now wrap the SQL in a view removing any filters.
In SQL Query Analyzer type
SELECT * FROM myview where... (put the filters here)
Again press CTRL_L.
and read the execution plan, back to front <------.
Yours,
-
I'm familiar with this in general but it doesn't:
1) Tell me what I want to know
2) or - I don't know how to read the output correctly.
I still cannot tell if:
1) The whole view gets created first and then filtered
2) What kind of locking is being used
Thanks but I need more help please...
-
I'm not a huge fan of views. If it can be done in a view I usually do it in a stored procedure. The only downside is that I am recreating joins on a regular basis. In answer or your questions I believe the view slects all the rows and then your filter takes effect. If you could post some code it might be easier to make a suggestion.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I think what srankin is saying, is that if you have a SELECT that JOINS and aggregates your data, and then you create a VIEW based on that SELECT (less the WHERE clause filters). The execution plan should be the same whether using the VIEW or the initial SELECT, meaning SQL Server will use the same idecies, locking etc. whether accessing the tables directly or in a VIEW. This should be true in almost all situations, thus no real penalty for using the VIEW for simplification of data access. Note: If view is to be used in "read-only" situations, using WITH NOLOCK hints may help in the area of "locking".
Once you understand the BITs, all the pieces come together
-
CREATE VIEW dbo.v_Clients
WITH SCHEMABINDING
AS
SELECT
REL.id_Relationship,
REL.RelationshipNme,
LC.id_LogicalClient,
LC.ID_LogicalClientType,
CT.TypeNme AS ClientTypeNme,
LC.LogicalClientNme,
LC.ID_CommissionGroup,
ISNULL(COMMS.GroupNamePublic, 'N/A') AS CommissionGroupNme,
ISNULL(COMMS.GroupNameOperations, 'N/A') AS CommissionGroupOpsNme,
COUNT(AC.id_Account) AS aAccountCnt,
SUM(ISNULL(AC.aHoldingCnt, 0)) AS aHoldingCnt, -- Percents are based on MARKET Value !!!!
SUM(ISNULL(AC.aUSDMarketValueAmt, 0)) AS aUSDMarketValueAmt,
SUM(ISNULL(AC.aUSDAccruedValueAmt, 0)) AS aUSDAccruedValueAmt,
SUM(ISNULL(AC.aUSDTaxCostAmt, 0)) AS aUSDTaxCostAmt,
SUM(ISNULL(AC.caTotalUSDValueAmt, 0)) AS caTotalUSDValueAmt,
dbo.GetSafePercent_Money(
SUM(ISNULL(AC.aFixedUSDAmt, 0)),
SUM(ISNULL(AC.aUSDMarketValueAmt, 0)))
AS aFixedPct,
dbo.GetSafePercent_Money(
SUM(ISNULL(AC.aEquityUSDAmt, 0)),
SUM(ISNULL(AC.aUSDMarketValueAmt, 0)))
AS aEquityPct,
LC.sys_CreatedDte
FROM
dbo.cbRelationship REL WITH (TABLOCK)
JOIN dbo.cbLogicalClient LC WITH (TABLOCK)
ON REL.id_Relationship = LC.ID_Relationship
LEFT JOIN dbo.cdAccount AC WITH (TABLOCK)
ON LC.id_LogicalClient = AC.ID_LogicalClient
LEFT JOIN dbo.cbLogicalClientType CT WITH (NOLOCK)
ON LC.ID_LogicalClientType = CT.id_LogicalClientType
LEFT JOIN dbo.v_CommissionGroups COMMS WITH (NOLOCK)
ON LC.ID_CommissionGroup = COMMS.id_CommissionGroup
GROUP BY
REL.id_Relationship,
REL.RelationshipNme,
LC.id_LogicalClient,
LC.ID_LogicalClientType,
LC.id_Relationship,
CT.TypeNme,
LC.LogicalClientNme,
LC.ID_CommissionGroup,
COMMS.GroupNamePublic,
COMMS.GroupNameOperations,
LC.sys_CreatedDte
********************************
Since I assume the WHOLE view is created before filtering - perhaps a TABLOCK is appropriate rather than SQL Server starting with a ROWLOCK and then escalating up?
Also, the CommissionGroup and ClientType tables almost NEVER change - so I use NOLOCK for speed.
*********************************
Anyway- I then join to the view like this (remember the view is not indexed):
CREATE PROCEDURE dbo.HCV_IOClientsAttributedTo
(
@id_PhysicalEntity int
)
AS
/*
Return core Relationship/Client links per Employee
*/
SET NOCOUNT ON
-- Get Summary/Parent Items
SELECT
vLC.id_LogicalClient,
vLC.LogicalClientNme,
dbo.GetEquityFixedString(vLC.aEquityPct, vLC.aFixedPct) AS d_ClientAllocations,
aAccountCnt,
aUSDTaxCostAmt,
aUSDMarketValueAmt,
COMENT.GroupNamePublic
FROM
dbo.v_CLients vLC
JOIN dbo.v_CommissionEntities COMENT
ON vLC.ID_CommissionGroup = COMENT.id_CommissionGroup
WHERE
COMENT.id_PhysicalEntity = @id_PhysicalEntity
ORDER BY
2
********************************************
The deal is... the aggregate totals
< one example below..
SUM(ISNULL(AC.aUSDMarketValueAmt, 0)) AS aUSDMarketValueAmt
>
at the Client level are used in many web pages for display. So I created the view to make things simple and consistent. But now I'm concerned about performance. The same is done with the Relationships - I sum the Clients totals into a Relationship total.
I guess in a nutshell that for tables that never get edited I can use NOLOCK safely. But what about in Views or Stored Procs that access views.
Even more - it appears that I can add hints to "View Joins" as well - WTF happens then ? FROM ATABLE JOIN AVIEW WITH (TABLOCK) etc..
Either way thanks for the info so far - this is interesting.
- B
-
Thomas H wrote:
>Note: If view is to be used in "read-only" situations, using >WITH NOLOCK hints may help in the area of "locking".
This will prevent locking on the tables right?
-- Francisco
-
My understanding, and what I've noticed...
If an underlying "real" table is accessed WITH NOLOCK, the only "locks" on that table would be a "Schema" lock that could prevent structural changes to the table. It should not prevent other INSERT, UPDATE, DELETE operations from occurring. However, the result set may contain information based on data that has never been "commited" or has already been changed in the table.
These are just thoughts "off the top", so meant in a general sense, not an absolute rule or anything. Test Test Test....
Once you understand the BITs, all the pieces come together
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply