SQL Server Performance Forum – Threads Archive

Querying views vs Sending TSQL vs Stored Procs

Heya,
It seems a lot of the code in the program that accesses my database queries views. I have had a look at the ‘How to perform a SQL Server Database Application & Transact SQL Performance Audit’ page, and have a few questions regarding the use of views. The page says

quote:Views are best used for handling security-related issues, not as a lazy developer’s method to store often-used queries. For example, if you need to allow a user adhoc access to SQL Server data, then you might consider creating a view for that user (or group), then giving that user access to the view, and not the underlying tables. On the other hand, from within your application, there is no good reason to SELECT data from views, instead, use Transact-SQL code to SELECT exactly what you want from the tables directly. A view adds unnecessary overhead, and in many cases, causes more data than necessary to be returned, which uses up unnecessary overhead.

So from this, i gather that it is better to code the whole query in the application rather than query a view. The section on Stored Procedures says

quote:Stored procedures offer many benefits to developers. Some of them include: Reduces network traffic and latency, boosting application performance. For example, instead of sending 500 lines of Transact-SQL over the network, all that is need to be sent over the network is a stored procedure call, which is much faster and uses less resources.

So, would best practice be for me to code these views as stored procedures (where possible) that will accept the parameters that would usually be passed when querying the view? I guess this kind of brings up the possibility of dynamic SQL, which i have read numerous times, should be avoided. Any tips on what the best approach would be for removing queried views from the application code? Hope I have been clear,
Thanks in advance, Ben

I have thought on this a bit more, and i suppose i wouldnt need dynamic SQL…say the procedure can accept 3 parameters, it could check which of the parameters are non-null (using IF statements) and execute the appropriate statement based on which IF statements are met. I suppose the problem with this would be where there were say 10 parameters, there would be thousands of possible sql statments to include. Is dynamic SQL still evil in this situation, or would be we better just passing the entire query?

The answer to this…..well, it depends. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I would say the answer rarely, if ever, should be dynamic SQL. I would use the IF logic for 6 parameters or less and have a catch all at the end. You can call functions with each IF statement (it avoids recompiles). You can also use the following logic if you need to: WHERE (@variable IS NULL OR a.column = @variable) AND @variable1 IS NULL OR b.column = @variable1) etc, etc, etc If you give us a specific example, we could probably help you more. It’s possible that a view is the right answer. Very hard to tell without seeing a sample. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Thanks Derrick,
I would never have thought of creating a where statement like that! Here is an example of one of the views: SELECT TOP 100 PERCENT dbo.tbl_ApplicationResult.TransactionId, dbo.tbl_ProductResult.ApplicationResultId, dbo.tbl_ProductResult.ProductName,
dbo.tbl_ProductResult.ProductCode, dbo.tbl_RegisteredProduct.Financier, dbo.tbl_Financier.Account, dbo.tbl_ProductResult.ResultStatus,
dbo.tbl_ProductResult.InterestRate, dbo.tbl_ProductResult.LoanPrincipal, dbo.tbl_ProductResult.LoanTerm,
dbo.tbl_ApplicationResult.PaymentFrequency, dbo.tbl_ApplicationResult.Residual, dbo.tbl_ProductResult.PaymentAmount,
dbo.tbl_ProductResult.AAPR, ISNULL
((SELECT SUM(FeeAmount)
FROM tbl_ProductResultFee
WHERE ApplicationResultId = tbl_ProductResult.ApplicationResultId AND ProductName = tbl_ProductResult.ProductName AND
tbl_ProductResultFee.FeeType = ‘Initial’), 0) AS InitialFees, ISNULL
((SELECT SUM(FeeAmount)
FROM tbl_ProductResultFee
WHERE ApplicationResultId = tbl_ProductResult.ApplicationResultId AND ProductName = tbl_ProductResult.ProductName AND
tbl_ProductResultFee.FeeType = ‘Ongoing’), 0) AS OngoingFees, dbo.tbl_ProductResult.ApprovalTime, dbo.tbl_ProductResult.Details,
dbo.tbl_ProductResult.FullDetails, dbo.tbl_RegisteredProduct.ApplicationForm, dbo.tbl_RegisteredFinancier.LogoPath, dbo.tbl_ProductResult.UniqueId,
dbo.tbl_RegisteredProduct.ContactFirstName, dbo.tbl_RegisteredProduct.ContactLastName, dbo.tbl_RegisteredProduct.ApplicationEmailAddress,
dbo.tbl_ProductResult.RequestApproval, dbo.tbl_ProductResult.ShowProduct, dbo.tbl_ProductResult.ContainerName,
dbo.tbl_ApplicationResult.ContainerName AS AppResultContName, dbo.tbl_ProductResult.Scorecard, dbo.tbl_ProductResult.Score,
dbo.tbl_RegisteredProduct.ProductType, dbo.tbl_RegisteredProduct.ProductTypeDescription, dbo.tbl_RegisteredProduct.DisplaySecurityDetails,
dbo.tbl_RegisteredProduct.EmailOnConfirmation, dbo.tbl_RegisteredProduct.CompressAttachments, dbo.tbl_RegisteredProduct.PrintAppForReferral,
dbo.tbl_ProductResult.ServiceFee, dbo.tbl_RegisteredProduct.DisbursementType, dbo.tbl_ProductResult.CompliantApplicant,
dbo.tbl_ProductResult.SecondaryApplicantScore, dbo.tbl_RegisteredProduct.ServiceProvider, dbo.tbl_RegisteredProduct.DisplayAccountantDetails,
dbo.tbl_RegisteredProduct.DisplayABN, dbo.tbl_RegisteredProduct.CompRatePageCount, dbo.tbl_RegisteredProduct.SubPrimeLender,
dbo.tbl_ProductResult.AcceptsLoanCommission, dbo.tbl_RegisteredProduct.ShowEnquiries, dbo.tbl_ProductResult.IgnoreDecision,
dbo.tbl_ProductResult.OfferReason, dbo.tbl_ProductResult.ApplicationType, dbo.tbl_ProductResult.ProductResultStatus,
dbo.tbl_RegisteredFinancierBranchDetail.BSB, dbo.tbl_RegisteredFinancierBranchDetail.OUN, dbo.tbl_RegisteredFinancierBranchDetail.State,
dbo.tbl_RegisteredFinancierBranchDetail.Branch, dbo.tbl_RegisteredFinancierBranchDetail.Suburb, dbo.tbl_RegisteredFinancierBranchDetail.Address,
dbo.tbl_RegisteredFinancierBranchDetail.Postcode, dbo.tbl_RegisteredFinancierBranchDetail.Phone, dbo.tbl_RegisteredFinancierBranchDetail.Fax,
dbo.tbl_RegisteredFinancierBranchDetail.EmailAddress
FROM dbo.tbl_Quote RIGHT OUTER JOIN
dbo.tbl_Financier INNER JOIN
dbo.tbl_RegisteredFinancier ON dbo.tbl_Financier.Financier = dbo.tbl_RegisteredFinancier.Financier INNER JOIN
dbo.tbl_RegisteredFinancierBranchDetail ON
dbo.tbl_RegisteredFinancier.Financier = dbo.tbl_RegisteredFinancierBranchDetail.Financier RIGHT OUTER JOIN
dbo.tbl_ProductResult INNER JOIN
dbo.tbl_ApplicationResult ON dbo.tbl_ProductResult.ApplicationResultId = dbo.tbl_ApplicationResult.ApplicationResultId ON
dbo.tbl_RegisteredFinancierBranchDetail.BSB = dbo.tbl_ProductResult.BranchBSB ON
dbo.tbl_Quote.TransactionId = dbo.tbl_ApplicationResult.TransactionId FULL OUTER JOIN
dbo.tbl_RegisteredProduct ON dbo.tbl_ProductResult.ProductName = dbo.tbl_RegisteredProduct.ProductName AND
dbo.tbl_RegisteredFinancier.Financier = dbo.tbl_RegisteredProduct.Financier
ORDER BY dbo.tbl_ProductResult.InterestRate
As you can see, it gets data from a lot of different tables, and if it is run as is, returns abound 90000 rows at present. In some situations though, they only want records for 1 TransactionId (~5 records) or for a particular financier (~10000 records) or 1 transactionId for a financier (only 1 record)…there are numerous other situations, but that should give you an idea! Ben

]]>