Fog Creek Software
Discussion Board




Can too so nyahh.

Sorry, I have to post this at the top because I hate being wrong.  Philo said that views make your queries slower, which is correct, unless you add Indexes to the equation.

Sorry, I did not mention that you can index the view.  Here's a good article on it: 

http://www.sqlteam.com/item.asp?ItemID=1015

According to the article: "With SQL Server 2000 (Enterprise Edition), Microsoft has introduced the concept of Indexed Views, which can make your applications and queries run faster in the right circumstances"

The beneficiaries of indexed views are any applications that join big tables that get selected against using the same keys all the time.  Your inner join between the Documents and Supplier tables may qualify this.

Basically, you create a view and then you add indexes to it.  Obviously the indexes take up space, but depending on the frequency of queries against this set of data it is worth the HDD space.

Wayne
Wednesday, September 17, 2003

People can also fly 'in the right circumstances'.

Do it both ways and measure, don't take SQLTeam.com's or MS' word for it.

Rick Childress
Wednesday, September 17, 2003

Indexed views in SQL Server are similar to (though not functionally equivalent) materialized views in some of RDBMS'.

I missed the conversation about views, however unless I'm mistaken the claim that views decrease performance is untrue, and this is easily provable by going into Query Analyzer and examining execution plans. What you will find is that a view isn't really a "pre-faucet" upon your data, but instead is more of a modification of queries run against it. i.e.

View1
  SELECT id, name FROM SomeTable WHERE name LIKE 'd%'

Proc1
  SELECT * FROM View1 WHERE id>100

In the execution plan this effectively becomes

  SELECT * FROM View1 WHERE id>100 AND name LIKE 'd%'

(And through sysdepends it knows to regenerate the execution plans whenever the view changes)

The value of it, though, is that because you've created an intermediary between your procs and your data, you now make schema changes without necessarily modifying all of your procs that references that "table" (view), perhaps normalizing or denormalizing as circumstances prescribe. Even better you can now create INSTEAD OF triggers so with a bit of logic views can be fully writable (for example allowing you to highly normalize your data without changing procs that think they're writing to a wide table).

To really put the cherry on the top of the view sundae, user defined functions offer tremendous inline functionality to do some very cool stuff within view returned resultsets.

On new development I make it a habit of always doing all data access through views, giving greatly increased versatility in the design for the future.

Dennis Forbes
Wednesday, September 17, 2003

Views are slow in Sybase, mostly because the optimizer is pathetic.


Wednesday, September 17, 2003

Ugh...when I said

"In the execution plan this effectively becomes

SELECT * FROM View1 WHERE id>100 AND name LIKE 'd%' "

...that should read...

"In the execution plan this effectively becomes

SELECT id, name FROM SomeTable WHERE id>100 AND name LIKE 'd%' "

Dennis Forbes
Wednesday, September 17, 2003

The INSTEAD OF TRIGGER statement rocks,  You are the man!  Curiosity piqued so I looked up some examples in the Transact SQL Help.  Very interesting.

My database knowledge is intermediate so maybe you can tell me: Is it counter-productive to use UDF's to manage a small comma delimited list of foreign keys in a parent table varchar field?

For instance tblDocument has a varchar(4000) field called Viewers which holds a list of User ID's ('1,2,3') that are reading the document.  This is in lieu of a 2nd table (tblDocumentUsers).

During a stored proc that is called when a user wants to open(view) or close a document, a UDF is called to add or delete that user's ID in the list.

The functions I've written to deal with the field are:

fnListAdd(sList, sVal, sDelim)
fnListDel(...)
fnListHas(...)

I don't expect a huge amount of viewers per document in this particular situation, otherwise I would surely use a relationship table.

Your thoughts?

Wayne
Wednesday, September 17, 2003

Wayne,

<quote>
My database knowledge is intermediate so maybe you can tell me: Is it counter-productive to use UDF's to manage a small comma delimited list of foreign keys in a parent table varchar field?
</quote>

In short - yes. It is far better to use a relationship table (generally) because:

a) That is how you are supposed to do it (by 'it' I mean indicate relationships in tables).
b) Because of a), it is easier for the next person to come along to understand what you were doing.
c) Performance wise, I can't imagine your idea will be real good. Your SQL will get tricky too. If 1, 2 and 3 are reading the document and 2 stops reading the document, the SQL to update 1, 2, 3 to 1, 3 is non-trivial (a DELETE of a relationship table would be fairly easy OTOH). Because of the way MS SQL Server does locking, managing concurrency on the table would be more difficult too.

Seeya

Matthew
Thursday, September 18, 2003

*  Recent Topics

*  Fog Creek Home