SQL server view behavior = nasty!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL server view behavior = nasty!

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-27 : 02:08:12

I'm not sure if this is the right forum to post this in. I've got a serious complaint with the way the SQL server query optimizer works with views.

I'm feeling a bit lazy to post exact code, but consider this example:

- A view that takes the primary key from one table, along with two or three other columns (username and password, for instance)

- That same view joins against 2-3 other tables linked by that same primary key. 1 or 2 of those tables joins against another table or two.

...if you do a simple query for username/password against that view, SQL server still goes to all of the work of joining the other tables, and a fairly high computational / IO expense. It's stupid. Just changing the query to reference the base table, in my instance, resulted in a 600% performance gain, and query cost dropped from 2.31 to .006.

Why is SQL Server going to all the work of joining tables in a view when no columns from the secondary or tertiary tables are used in the actual query? This seems like a huge oversight.

Am I missing something? Is there some way to give the optimizer a hint that not all columns in the view are being used?

I can post exact code to recreate the problem if it's actually news, but I have to expect that this is a known issue.

Thanks
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-27 : 07:01:32

Sounds like you expect sql server to give back incorrect results.

The server still has to join to the other tables to see if the records exist. If they don't exists then the server will not return the rows from the first table for that PK.

select a.i
from a join b on a.pk = b.pk

is not the same as

select a.i
from a

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-27 : 09:34:12

quote:
Am I missing something?

Yes, you are. As Nigel pointed out, SQL Server will process the joins in order to determine if any rows will actually be returned from those join conditions.

The whole idea behind views is that they allow you to construct virtual tables from base tables. That way you don't have to create a base table with all the columns you want (and waste space), and you don't have to repeat complex join clauses in every query you write.

If you don't like the results from querying the view, then DON'T! Query the base table instead if the columns you want only exist there.

Not to sound harsh, but it's silly to complain about SQL Server's performance when it's doing EXACTLY WHAT YOU TOLD IT TO DO. SQL Server's optimizer can't correct for someone's misperception of what it does.

Edited by - robvolk on 01/27/2002 09:36:28

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-27 : 16:46:27

Bit harsh robvolk.
Almost as harsh as what I almost posted.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-27 : 21:22:42

Well, I suppose I deseved that :)

What I neglected to say is that these are LEFT OUTER JOINS, so every row is going to be returned no matter what. I'm looking up the username and password, and I don't care if they've supplied the additional info.

In that light, does my irritation make more sense?

Cheers
-b

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-28 : 00:53:08

No, it doesn't. That's like being irritated during sex because you had an orgasm, but not the BEST orgasm ever.

If you don't want the other tables to be included in the query plan, don't include them in the view. The type of join has nothing to do with it. Which columns you do or don't include in the SELECT statement has nothing to do with it. The view is considered a single, whole entity composed of the tables and columns that define it.

This is not a bug, shortcoming, oversight, or known issue with SQL Server. It's doing exactly what you told it to do. The optimizer doesn't read minds. Think about it: why include the other tables in the view IF YOU DON'T WANT TO SEARCH THEM? Why would/should the optimizer assume it's OK to ignore them?

Finally, YOU PROVIDED A FAR BETTER SOLUTION IN YOUR ORIGINAL POST. Why not use it? Instead of assuming that SQL Server has some bug in it, use the method that works best for you.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-28 : 01:19:15

Well, heck, why not say "why use views when you can just create huge joins each time" (indexed views aside).

In my case, the situation actually has to do with the integration of snitz forums (the very thing we're exchanging notes on) into an existing production environment. Snitz has a members table with a huge number of columns, most of which duplicate data I have in other tables. I've used a view that combines many of my existing tables with some snitz-specific stuff, so snitz queries run transparently.

What I'm finding now is that I need to look at each snitz query and decide what columns it's going to use, then re-write it to use base tables rather than that view.

What you seem to be saying is that it's unreasonable to expect the query optimizer to optimize queries. That seems a bit strange to me.

My original point, which I stand by, is that when querying dynamically generated views, it would be trivial to only do the joins necessary for the particular query, rather than *first* building the entire view, and then running the query. If none of the columns in an outer joined table are used, don't do the join.

I'm perfectly willing to think that I've missed something here, and that there's some reason it has to do it the way it does. But a lot of the point of views is convenience. I know that in a lot of development, you have to sacrifice performance for convenience. But I really don't see why you have to do so in this instance.

Tell me where I'm wrong, and I'll cop to it. But don't tell me I shouldn't expect SQL server to seize obvious optimizations.

Cheers
-b

Nazim
A custom title

1408 Posts

Posted - 2002-01-28 : 02:05:18

Aiken , how do you percieve views to be?. if they are not build everytime how will it include the records which were added or updated on its last access??.

the point is , as we all know views dont have any physical data with it . so everytime you access it .it has to go thru the base table and do the necessary stuff to get the data.

It makes perfect sense to me whatever Robvolk has stated( except that Orgasm thingy . he need to xplain more on it

)
to give a eg:

create view a
as
select * from tablename where columnname>3000

now if i state
select * from tablename where columnname>3000
or
select * from a

R you tring to imply that the second one should be faster? . if so then your understanding of views is misplaced.

quote:

My original point, which I stand by, is that when querying dynamically generated views, it would be trivial to only do the joins necessary for the particular query, rather than *first* building the entire view, and then running the query. If none of the columns in an outer joined table are used, don't do the join.

From your first post. dont you think though you might not use the columns but are not there chances that coz of joins rows will be filtered?

quote:

Why is SQL Server going to all the work of joining tables in a view when no columns from the secondary or tertiary tables are used in the actual query? This seems like a huge oversight.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 01/28/2002 02:25:14

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-28 : 05:19:56

select a.*
from a left outer join b on a.pk = b.a_pk

If there are two rows in b for the a_pk then it will return two identicle rows for the join. It cannot know this without doing the join to b.
so again

select a.*
from a left outer join b on a.pk = b.a_pk

is not the same as

select a.*
from a

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-01-28 : 12:45:42

I think nr hit the nail on the head and found what I was missing; I had forgotten the way outer join behaves. My fault!

I definitely don't expect views to be faster than a query that references base tables; however, there must be some way to build (and use) a view so that it's not that much *slower* than using base tables when there are joins involved.

In this example:


create table users
  (i int identity,
   login_name varchar(25) NOT NULL,
   password varchar(25) NOT NULL)

crate table user_info
  (i_users int NOT NULL,
   i_genders tinyint,
   age tinyint)

create table genders
  (i int identity,
   name varchar(25) NOT NULL,
   abbr char(1) NOT NULL)

create view big_users as
  select users.i as user_id,login_name,password,age,
     genders.name as gender
  from users
    left outer join user_info on i_users=users.i
    left outer join genders on genders.i=user_info.i_genders

If I then simply want to look up a username/password, I'm much better off referencing the base "users" table than the "big_users" view. Now, if I were writing the app, I'd be silly to do it any other way. But if I'm using the view to facilitate integration or to maintain modularity in my codebase, what do I do?

Creating an index on users.i and then playing with this example, I found


A.  select login_name, password from users where i=3

B.  select login_name, password from users u
       left outer join user_info ui on u.i=ui.i_users

C.  select distinct login_name, password from users u
       left outer join user_info ui on u.i=ui.i_users

Options A and C get the same (fast, single table) query plan. Clearly, I was wrong in expecting the view equivelent of option B to get that same plan.

Is there a way to tell the view that there will be only one row for each unique users.i? I may be joining all over the place to additional tables, but they will all return unique values; never multiple rows.

Cheers
-b

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-28 : 15:06:14

I don't think you'll be able to create a one-size-fits-all view that will give you the performance you need. If performance is that crucial you will (and should) have to write something specifically tailored to return the results you need quickly.

Why not use stored procedures? You can pass parameters to them, and based on what's passed you can choose the most efficient query plan for the option you need. If you only pass the users.i value and nothing else, an IF statement can execute the faster plan insted; otherwise it will run the full result.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-28 : 15:23:55

Had you written:


create table users
  (i int identity PRIMARY KEY,
   login_name varchar(25) NOT NULL,
   password varchar(25) NOT NULL)


create table user_info
  (i_users int PRIMARY KEY,
   i_genders tinyint,
   age tinyint)


create table genders 
  (i tinyint identity PRIMARY KEY,
   name varchar(25) NOT NULL,
   abbr char(1) NOT NULL)

It might have had something to optimize. Note that the foreign key (declared or no) types have to match their primary keys for this optimization to take place -- your genders.i and user_info.i_genders differed.

Edited by - Arnold Fribble on 01/28/2002 15:29:17

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-29 : 02:20:02

You have found one of the problems with views. I tend to prohibit their use as it protects developers from the database schema and makes it a lot more dificult to optimise queries - or in fact tell what is happenning in the database.
They are never necessary and can cause unexpected errors to crop up and unnexpected locking.

You might have a look at partitioned views as one area where they can be useful - I would probably code it in the SP though.
Another area is bcp where you want to exclude columns from the insert/output.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources