View / SQL / T-SQL

[1DMF]

Hi,

Could you please advise the difference in efiificeny between these three options.

1. a SQL command is executed within code using joins to retrieve a recordset

2. a Stored Procedure (T-SQL) - is executed from within code which has the same joins and returns the same recordset

3. a table view is created of these SQL joins and a SQL command is executed to retrieve the recordset via the table view.

This is all via a locally running web application directly accessing SQL via a DSN.

Thanks,

1DMF


__________________

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Qik3Coder]

The stored proc will let you cache an execution plan, so it is able to run faster than the random script.

Procs and views have different purposes.
A procedure is a fixed set of steps/actions that you need to contain into an atomic unit.
A view is use to standardize some logic, but the final set of data is still up for debate, as you can choose not to return certain columns, or join in other data.

You didn't talk about table functions, but I've had fun using them to hide logic that was too complex for a view, but needed to be more dynamic than a stored proc.

Lodlaiden

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[1DMF]

Hi,

What I'm trying to understand is why a software company wishes to turn

SELECT A.x,B.y,B.z FROM A JOIN B ON A.id = B.id

into a table view

 SELECT x,y,z FROM C

with the following comment

• One view could store joins from multiple tables, which would mean that you would only have to refer this view as opposed to refer to multiple tables within your code, which would bring with it performance benefits.

• Views can also be used to hide columns which you do not require within your dataset, thus making the data easier to interpret.

I'm curious as to the first point, and how a view is performed, if I then in my code use a select statement to retrieve certain records from the view, doesn't SQL still have to perform a dynamic join to create the view before it returns the recordset?

So how does the view give so much performance boost over a simple select with a join on an adhoc basis?

2ndly how does using a view hide columns? - I only select the columns I want when using a select against the DB, so my returned recordset only has the data i'm interested in.

What's the purpose or point of using

SELECT * FROM TableView

vs

SELECT x,y,z FROM Table

The returned recordsets are identical what benefit does this give and how does a tableview hide columns?

I'm trying to understand this statement as it doesn't make sense to me.

Thanks,

1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Qik3Coder]

The view will enable you to cache some of the query, but you must still use the view in some fashon to get data, which is why I "normally" bypass that step.

If you only grant access to the view and not the underlying tables, then those users do not have access to the "extra" columns.
I normally don't think of this as a "benefit", as my users don't have ANY access at all and have to go through whatever app I have created.

Lodlaiden

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[gmmastros]

Here's my take on it.


Like Qik3coder said, stored procedures are "compiled" on the server. When you run a query, there are multiple steps that SQL Server goes through to provide you with the requested data. One of the steps is to determine the most efficient way to return the data. The "compiled" part is this step where it determines the optimal way to get the data. Unless your query is very complicated, this step is usually very fast (think micro-seconds).

Using a stored procedure will allow you to bypass this step (that usually takes micro-seconds). When you run a query (not in a stored procedure), SQL Server needs to perform this step for you. A lot of people mistakenly think that this step is done every time you run the query, which is not exactly true. Even for ad-hoc queries (not in a stored procedure), SQL Server can still re-use the execution plan.

Which is faster? I would say that they should all run in approximately the same amount of time.

Personally, I prefer to use stored procedures. My main reason is that I think they are easier to use. Let me explain...

If you discover a bug in the application, and that bug involves a stored procedure, you can connect to the database, fix the query, and move on.

If you discover a bug in the application and that bug involves a view, it's a similar situation where you go in to the database, fix the view, and move on.

If you discover a bug in the application and that bug involves a query, you would then need to find the query in the code, and change it. The part that bothers me about this is... you have to replace the entire code file. In some situations, someone else may have made a change to the code file which hasn't been completely tested yet, which could lead to other bus with other parts of the application.

By keeping the query contained within the database, it is easier to change just one small part without it affecting other parts of the system.

I usually stay away from views. The problem with a view is that it is essentially a query underneath. The underlying query usually has some sort of complexity that you want to hide. The problem is, the underlying query could use another view, which uses another view, and so on. At some point, when there are too many levels of nested views, the query optimizer has a difficult time generating the most optimal execution plan.

These are my reasons. Your mileage may vary.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkSweetland]

If you repeatedly used the same tables joined in the same manner in multiple stored procedures you'd be better off with a view that simplifies the coding.

As to the second point, hiding columns returned in the view can be accomplished by

SELECT x,y FROM C

verses

SELECT x,y,z FROM C

whereas column Z, even though present in the join, is not returned with the query result.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[1DMF]

Thanks Guys, as I thought, the explination of why they want such indepth information about the system I am coding, all the columns I'm using and their purpose, proposing those statements as the reason is pure baloney!

Now I use views for some of my MS Access apps, because I know that joins via the JET driver are not performed at SQL server level, Access gets data from all tables involved, then client side performs the join and delivers the recordset. So for perfomance purposes in this environment views (or SP's) are essential.

Howver, as this application will be running localy accessing SQL direct which I manage, there is no-one to 'hide' columns from, I have SQL EM!! There is little to no benefit using a View vs a standard SQL select.

In fact I would rather use an SP (T-SQL) with parameters/arguments than a view, it gives me more scope, SQL is optomised for T-SQL isn't it?, and I would argue that potentially this is faster than a View.

As you say George, underneath the view (as I suspected) there is an underlying query with a join powering it, so their reasoning is BS!

The other point is who is trying to hide what from whom?

It's our SQL server, our data and I'm the only one with access to it (other than the software provider), there is no requirement to hide anything from anyone!

Much obliged for your input!

1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

I suspect that it's not really about "hiding" the data. It's probably more about only providing the information needed for different functionality within the application. I would not jump to the conclusion that they are trying to hide anything from you.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Hi George,

I'm the one writing the application, I have all the queries I need against the real tables, I'm struggling to see the reasoning behind wantng to provide the same data but via views instead?

As I said I have SQL Enterprise Manager (Studio - can't rememebr what the new name is in SQL 2008R2), anyways, I have access to the entire kit and kaboodle, yet the reason for this request they give as 'hiding columns' , from whom and for what purpose, as mark and myself pointed out, you do that in a select statement anyway, that's how select statements work, it's not like i'm using a wildcard select against everytable!

Anyway, have a great weekend, I'll worry about this more on Monday!



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads