HomeClarion Live! UniversityDiscussion on using Views vs. In-Line Table Functions in MSSQL Server - Dennis Evans, Rick Smith, Ubaidullah Nubar, Michael, Terry Mullican
 
  • PDF

Discussion on using Views vs. In-Line Table Functions in MSSQL Server - Dennis Evans, Rick Smith, Ubaidullah Nubar, Michael, Terry Mullican

Dennis E Evans:
    FYI folks, when using views, which are not good for much of anything, try to convert to inline table functions, if possiable,  MS Sql Server anyway, they require a lot less resources and typically produce a better execution plan.  For simple test create a view that returns 6 or 7 columns of from two or three tables and a inline function that does the same.   run both in a batch and the wiew will take 75-80 percent of the cost and the inline function wil ltake the rest, then look at the plans.  Not sure why yet, I suspect it has to do with the way views bind to the various columns. Note as with all things test and evaluate against your own tables, indexes etc...
 
Rick Smith:
    When u say views aren't good for much of anything, do u mean Clarion views or MSSQL views?

Dennis E Evans:
    Rick, MS Views clarions are needed.

Rick Smith:
    I'm curious as to why u say that. We r using *tons* of MSSQL views here and they work great. In fact, our Clarion DCT points to very few tables. We have the DCT pointing to views instead of table and of course Clarion doesn't know any different.
   
Dennis E Evans:
     well, a view is just a select statement and is limited to one.  So what does it bring to the table?  While I have a few I seldom use them, the more I work with inlines functions, I suspect I will use them less, but as with all things sql, it depends.
     
Michael:
    come on, SQL views offer a terrific way to abstract complexity. don't they?

Rick Smith:
    Right. A view is nothing more than a SELECT statement. But by creating the view in the backend, I can create the "SELECT" statement as the view and not have to worry about maintaining it anywhere but in the view. One of our goals is to get as much business logic into the backend as we possibly can.

Dennis E Evans:
    vs what?  a select statemetn in a stored procedure?  what is the difference? when you create a view what is written?

Ubaidullah Nubar:
    A view can also consist of multiple SELECT statements combined with a UNION ALL. I use that for some ERP backend DBs and all Clarion sees is a table. I think the more important question is, can an inline table be used by Clarion just like a view ?
   
Dennis E Evans:
    anyway, if you like views use them, I am not a big fan.  and it in the initial tests looks like table functions produce better results.  and they can have more than one select statement and they accept parameters.  the parameters is a big one for me.
   
Michael:
    I am not sure of the question but if you are asking if it can be used just like a table. I would say with caution, I tried it and found that the browse table/view didnt reflect the update. this could be an issue of how and when a view regenerates itself. So, I limit the use of SQL views to building a complex vides and only where clarion view would not work.  In fact clarion views are limited to simple Joins.

Dennis E Evans:
    also, I have not compared against indexed views, in that case the view may come out on top,

Ubaidullah Nubar:
    Ok, how does one use table functions from clarion? Can they be declared in the dictionary ?
   
Terry Mullican:
    SQL table function? define a view struction which matches your table function, viewtemp{prop:sql} = 'Select fields from dbo.tablefunction'
   
Ubaidullah Nubar:
    Inline Table-Valued Functions -- to be more precise
   
Dennis E Evans:
    the function would not need to be declared just the buffer, typically a file or a queue
   
Terry Mullican:
    yes we use temp file definition
   
Ubaidullah Nubar:
    so, that means view+function and the view buffer is declared as a table in the Clarion dictionary. right?

Dennis E Evans:
    I don't use clarions file drivers so I really do not know, I use the OLE db or ODBC with queue and groups as the buffers

Terry Mullican:
    that is how we have it set up
   
Dennis E Evans:
    ie there is no dictionary, at least not clarions dictionary
   
Ubaidullah Nubar:
    that's interesting. what do you use for browses? handcoded? ABC?
   
Dennis E Evans:
    various classes, not really abc but does the same,  mainly just load a queue and go.
   
Ubaidullah Nubar:
    I would assume that lots of handcoding is involved. What kind of apps do you produce ?
   
Terry Mullican:
    it is a lot simpler than you think...
   
Dennis E Evans:
    mostly all had coded but only coded once.    I work for a small company that develops software for Petroleum Engineers, Engineering Consulting firms and a few banks that evaluate the industry. in out main apps, in one there is really only three browses, that do different items, in the other there is two or three.   you really do not need to create a new window, procdure etc.. for every browse,
Joomla Templates: by JoomlaShack