How to Execute Views from VB
-
Interesting Michael, by not letting your developers use views, you do kind of
keep their hands tied as far as being able to not run an open ended query, but
if you are implementing code reviews as you say, would not the code review catch
anyone writing open ended views? It is a double edged sword, by only writing
stored procs, you most likely will end up writing :
a) a LOT of procs to be able to handle every different single where that a developer
will need / want
b) Procs that have very imaginative where clauses using good boolean logic,
short circuiting, and grouping
c) Dynamic sql - YUCK
Code reviews are often a necessary function in todays programming world. I find
that they very rarely happen, even in some of the bigger shops I have worked at.
I am a big XP(not the OS) fan btw.
This quote from your first post :
quote:
By the way, why usage of a view instead of going directly to tables is essential? Is it permissions?This is actually quite puzzling to me. You will let them query the tables directly,
but then wont use views? Is that not a oxymoron? Like "Military Intelligence" or
"Advanced BASIC" or "Holy war"? Maybe I misunderstood the above statement, but letting
them query the tables directly lets them do even more weird stuff than using a canned
view does.
LAST NOTE : I firmly believe this : There is no one true way to do something in the computer
industry, so I respect your opinion Michael, even though I do not agree with it.
Tim C.
//Will write code for food
Tim C //Will code for food -
I'm going to add my two cents as well. This is a very interesting topic.
If the issue is with the complexities of the joins across the tables why don't you use the following:
1.) Create all the views needed for the generation of reports. You know the joins and these should be fairly easy to create. If using SQL 2K you can create indexed views which should help with execution CREATE VIEW [view name here] WITH SCHEMABINDING and then add INDEXES, KEYS as normal just like a table
2.) Create a new userid/group and grant access ONLY to those views.
3.) Provide the developers with the view names and the login/pwd information.
The above should resolve all of the issues. a.) You can now allow the developers to create their own queries.
b.) Use any tool(s) they want to query data.
c.) Still maintain a VERY good stranglehold on data integrity by managing the rights to the views.
Just another opinion to toss about
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!AJ Ahrens
webmaster@kritter.net -
quote:
This quote from your first post :
quote:
By the way, why usage of a view instead of going directly to tables is essential? Is it permissions?This is actually quite puzzling to me. You will let them query the tables directly,
but then wont use views? Is that not a oxymoron? Like "Military Intelligence" or
"Advanced BASIC" or "Holy war"?
...
Tim C.
Tim, what I meant was the following. Say, you are creating an application that queries database. These days it is usually a multitiered app with some sort of data access tier (should be). You create a single DB user account just for this application (either integrated or standard security is used) and grant is access to appropriate stored procedures only. Then you do not need to grant rights to tables nor views. Functionality of your app should be limited by Business Requirements anyway so number of SP is limited as well.
On your concern aboyut using dynamic SQL, let me say that I found a way to avoid it ALWAYS, even when an SP has a many optional parameters for, say, search.
Thanks for your reaction.
Michael
-
quote:
Mromm, are you meaning that you do not normally like to use views for the data retrieval? Just curious as to your reasons, plus I love to debate. (Some people say argue, but I say they are wrongI can't resist jumping in.
We use views in very limited circumstances: when providing information to non-SQL writing staff to give them a quick and easy way to access a set of information without IT involvement.
We use stored procedures for everything else, including data retrieval. The can do everything a view can do and faster. They can be made modular, they can populate temp tables (which can be indexed, a view can't except as below), and are easily accessed and manipulated in ADO using the command object. Compared to views I cannot think of a single disadvantage.
I know there has been some improvement in 2000, esp with indexing, but this only applies to the enterprise edition. For us DBAs at small and medium companies using the standard edition we can't do this.
You say there's no one good solution, and that may be true, but it's also true that for every rule there's an exception, and if this isn't an exception I don't know that I could think of one. Stored procedures are better in every way. Even if you have programmers who can't write SQL statements, you can either write views for them or stored procedures.
I have seen as one reason for using views is to deny your programmers access to sensitive information, but stored procedures will do this too.
-
quote:
We use stored procedures for everything else, including data retrieval. The cando everything a view can do and faster.
Hmmm, set out to prove this one way or the other. Found some interesting results.
Maybe one of the gurus on this site can explain why. First of all let my explain
my test. I created a view and a stored procedure to query from the authors table
in pubs. A simple query with no joins. Here is the sql :
USE PUBS
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'spAuthorsFetch'
AND type = 'P')
DROP PROCEDURE spAuthorsFetch
GO
CREATE PROCEDURE spAuthorsFetch(@au_id id = NULL)
AS
SELECT [au_id],
[au_lname],
[au_fname],
[phone],
[address],
[city],
[state],
[zip],
[contract]
FROM [authors]
WHERE (@au_id IS NULL OR [au_id] = @au_id) --if null select all rows, else only the author wanted
GO
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'vwAuthorsFetch')
DROP VIEW vwAuthorsFetch
GO
CREATE VIEW vwAuthorsFetch
AS
SELECT [au_id],
[au_lname],
[au_fname],
[phone],
[address],
[city],
[state],
[zip],
[contract]
FROM [authors]
GOI added no indexes, no hints, basically left the authors table as it came off
the install. I then created a test script using the SAME connection to run both
of these queries in loops, and recorded the millisecond difference between start
of loop and end of loop for each one. I used the exact same where clause for the
view that was in the procedure. I set a bit flag on whether to run the procs or
the views. I ran five tests of (with results):
-500 views with WHERE clause variable filled
----220 MILLISECONDS TO RUN 500 VIEWS
----220 MILLISECONDS TO RUN 500 VIEWS
----266 MILLISECONDS TO RUN 500 VIEWS
----250 MILLISECONDS TO RUN 500 VIEWS
----233 MILLISECONDS TO RUN 500 VIEWS
-500 views without WHERE clause variable filled (SET variable in test is commented out)
----2173 MILLISECONDS TO RUN 500 VIEWS
----2250 MILLISECONDS TO RUN 500 VIEWS
----2250 MILLISECONDS TO RUN 500 VIEWS
----2203 MILLISECONDS TO RUN 500 VIEWS
----2263 MILLISECONDS TO RUN 500 VIEWS
-500 procedures with WHERE clause variable filled
----220 MILLISECONDS TO RUN 500 STORED PROCEDURES
----233 MILLISECONDS TO RUN 500 STORED PROCEDURES
----220 MILLISECONDS TO RUN 500 STORED PROCEDURES
----250 MILLISECONDS TO RUN 500 STORED PROCEDURES
----233 MILLISECONDS TO RUN 500 STORED PROCEDURES
-500 procedures without WHERE clause variable filled (SET variable in test is commented out)
----2263 MILLISECONDS TO RUN 500 STORED PROCEDURES
----2280 MILLISECONDS TO RUN 500 STORED PROCEDURES
----2250 MILLISECONDS TO RUN 500 STORED PROCEDURES
----2293 MILLISECONDS TO RUN 500 STORED PROCEDURES
----2250 MILLISECONDS TO RUN 500 STORED PROCEDURES
Here is the test script :
USE PUBS
GO
DECLARE @RC int,
@au_id varchar(11),
@cntr int,
@maxcntr int,
@start datetime,
@end datetime,
@diffProc int,
@diffView int,
@runViews bit
--turn off records affected for all queries.
SET NOCOUNT ON
--init variables
--FOLLOWING 2 variables are part of the control, plus whether running views or procs
SET @maxcntr = 500
--SET @au_id = '527-72-3246' --comment this line out to run the query wide open
SET @runViews = 0 --SET to 1 for views, 0 for proc
IF @runViews = 1
BEGIN
PRINT 'RUNNING VIEWS'
PRINT 'RUNNING VIEWS'
SET @cntr = 1
SET @start = GETDATE()
--run the view with the EXACT same where clause is the procedure.
WHILE @cntr < @maxcntr
BEGIN
SELECT [au_id],
[au_lname],
[au_fname],
[phone],
[address],
[city],
[state],
[zip],
[contract]
FROM vwAuthorsFetch
WHERE (@au_id IS NULL OR [au_id] = @au_id)
SET @cntr = @cntr + 1
END
--record the elapsed time in milliseconds
SET @end = GETDATE()
SET @diffView = DATEDIFF(ms, @start, @end)
PRINT 'END RUNNING VIEWS'
PRINT 'END RUNNING VIEWS'
PRINT ''
END
IF @runViews = 0
BEGIN
PRINT 'RUNNING PROCS'
PRINT 'RUNNING PROCS'
--reset the variables
SET @cntr = 1
SET @start = GETDATE()
--run a loop pulling from a proc, this will cause plan cacheing, so we should get MAX speed
WHILE @cntr < @maxcntr
BEGIN
EXEC @RC = [pubs].[dbo].[spAuthorsFetch] @au_id
SET @cntr = @cntr + 1
END
--record the elapsed time in milliseconds
SET @end = GETDATE()
SET @diffProc = DATEDIFF(ms, @start, @end)
PRINT 'END RUNNING PROCS'
PRINT 'END RUNNING PROCS'
PRINT ''
END
--print the results
IF @runViews = 0
PRINT CAST(@diffProc as varchar) + ' MILLISECONDS TO RUN ' + CAST(@maxcntr as varchar) + ' STORED PROCEDURES'
IF @runViews = 1
PRINT CAST(@diffView as varchar) + ' MILLISECONDS TO RUN ' + CAST(@maxcntr as varchar) + ' VIEWS'
SET NOCOUNT OFFMaybe I am confused, but it sure does look like they perform similar. I am
posting my test script so that my results can be verified. Although a view does
allow a developer the luxury of writing a bad join / cross join or an invalid
where clause, or doing "SELECT *". All bad practices. I would like to see if
anyone else has done a similar test.
quote:
I know there has been some improvement in 2000, esp with indexing, butthis only applies to the enterprise edition. For us DBAs at small and medium
companies using the standard edition we can't do this.
Not true, there are ways to do this. See:
http://www.databasejournal.com/features/mssql/article.php/2119721
quote:
I have seen as one reason for using views is to deny your programmers access tosensitive information, but stored procedures will do this too.
Sounds like a trust issue with co-workers. If they are writing bad joins or
where clauses using views, these need to be addressed in development and
testing. But saying you wont use views for these reasons seems to me that you
will go through a lot of extra work for not much benefit except peace of mind.
Trust me, I understand you two guys point of view. I know many developers I
would not allow to touch SQL, much less write code. I agree with Michael, code
reviews must be done, but the groups must work together, and the developers
must communicate with the DBA's when they foray into the DB. Take a look at
Extreme Programming for some other ideas. I have gone on too long already.
Again, I do not disrespect your opinions guys, this is one of those issues where
people will take sides, and lob rocks at the other camp. One of my other
favorites is the XML - Attributes vs. Elements. In one corner.... LMAO
Tim C.
//Will write code for food
Tim C //Will code for food -
I tend to agree with everyone that has posted here regarding this issue.
I personally have worked closely with MS at a prior job. They were brought in to assist in a migration from a normal server to a UNISYS multi-million dollar box. They helped with indexes, performance tuning etc...
The first thing they stated was: Remove ALL access from the tables and allow ONLY execute on stored-procedures that had same owner as the tables. This caused issues with all of our programs, reports, etc... We were given 2 months to switch over.
The 3rd party app's were given read-only views to look at on our replicated server. Granted not everyone has replication but read-only views and execute-only stored-procedures sounds like the way you want to go.
Again I refer back to your comments earlier about the complex joins between tables. If you create the views and allow the read-only to them and create sp's to access tables all of your issues hopefully will go away
Again, I wish you all good luck in the battle between DBA/Developer
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!AJ Ahrens
webmaster@kritter.net -
I ran your test. Here is my initial set of results:
4516 MILLISECONDS TO RUN 500 STORED PROCEDURES
5173 MILLISECONDS TO RUN 500 STORED PROCEDURES
4153 MILLISECONDS TO RUN 500 STORED PROCEDURES
5080 MILLISECONDS TO RUN 500 STORED PROCEDURES
4783 MILLISECONDS TO RUN 500 VIEWS
6703 MILLISECONDS TO RUN 500 VIEWS
5060 MILLISECONDS TO RUN 500 VIEWS
4763 MILLISECONDS TO RUN 500 VIEWS
As you can see the results are comparable. However, I would not have written the stored procedure that way. I altered the test as follows:
ALTER PROCEDURE spAuthorsFetch
@au_id id = NULL
AS
IF @au_id IS NULL BEGIN
SELECT [au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]
FROM [authors]
END
ELSE BEGIN
SELECT [au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]
FROM [authors]
WHERE [au_id] = @au_id
END
GO
EXEC sp_recompile spAuthorsFetch
GOA view cannot have this decision making tree. With the stored procedure so, I got the following results:
6063 MILLISECONDS TO RUN 500 STORED PROCEDURES
4156 MILLISECONDS TO RUN 500 STORED PROCEDURES
4063 MILLISECONDS TO RUN 500 STORED PROCEDURES
4030 MILLISECONDS TO RUN 500 STORED PROCEDURES
The long first one is likely due to the recompile. The subsequent ones 20-25% faster than the view times.
It wasn't a particularly realistic test in that it is unlikely you would normally return an entire tableset of information.
Also, you didn't comment on the many other advantages I outlined: the easy interaction with ADO's command object; the ability to create modular stored procedures; there are others I didn't list. So not only do I get all these advantages, I get them faster too. Normally in the computer world things are a tradeoff: but not here.
By the way, when I mentioned the item about not trusting developers, I don't have that problem. I was simply remarking that I had read that IF you had the problem, you might want to consider using a view.
You can say that there is no one true way to do anything, but I will say that views are one to to retrieve data, and stored procedures are a better way to retrieve data.
-
Why did you change the where clause on the stored proc to use an IF? The OR gave
you the exact same results as the if you changed it to, and because of short
circuiting is a much smaller and cleaner alternative. What happens when as you
say you send in many more parameters, many of which can be null? Without short
circuiting you are going to have a MONSTER of a proc. To see the different
results from my original test modify this line in the test script :
Change
--SET @au_id = '527-72-3246' --comment this line out to run the query wide openTO
SET @au_id = '527-72-3246' --comment this line out to run the query wide openYou will see that I ran it selecting only one from the view AND the proc with
the short circuiting method.
quote:
Also, you didn't comment on the many other advantages I outlined: theeasy interaction with ADO's command object; the ability to create modular stored
procedures; there are others I didn't list.
I didn't comment on them because I agree with them, except you can create
modular views, and you can use views from the command object. Take a look at
the "Prepared Property" of the Command object. IMO Procs have capabilities way
beyond that of views. I do not always use views for reading, often times when as
you say I need a decision tree, a proc is the only way to go. I am basically
playing devils advocate here. I think almost anything in the computer industry
has a place and a reason when used judiciously. I think here is where we agree
to disagree.
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply