Solved

SQL: View vs. Function

Posted on 2014-09-19
4
385 Views
Last Modified: 2014-09-29
I have an existing stored procedure (300 lines of code) for a report that creates two temp tables.

tempTable A gets all accounts that match a certain criteria. This table is then treated as a reference table.

tempTable B gets all accounts that match the reference accounts in tempTable A.

Lastly, a select statement is called to get all records from tempTable B.

There is now a need to modify an existing stored procedure called ABC for a different report, and I really don't want to COPY/PASTE 300 lines of code from one stored procedure to another, so I was thinking of creating a table-valued function and COPY/PASTE those 300 lines into it, and then just call this function from the ABC stored procedure.

However, I was also suggested that I could create a view instead, and perhaps a view could be more efficient?

I need your advice on how to design this further for stored procedure ABC.
0
Comment
Question by:pzozulka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40333885
a view on temp tables may also fail (when one or more of the tables aren't there)
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 40333934
An inline table valued function should perform just as well as a view, while allowing a greater level of control e.g. using parameters etc
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 40334275
First reading: copying the 300 lines of code from first procedure to UDF and call that UDF from old and new procedure means that you probably don't need a second procedure.  But guess that is to simple.

Different options and very based on what the difference is between procedures: criteria for table A, criteria or data for table B and/or the statement for your report data.  

There is the maintainability
- selection must be reliable, changes in one report must/may not  inpact in other report
- performance : touch as little data as possible from start to end of report


When criteria differ for the tempTable A
Make an (inline table valued ) UDF with parameters that returns the  selected accounts.  

Can A and B get combined?

Using views in a more complex situation (views on views, adding rownumbers....)  have the problem that the filter you apply on the end is translated in the usage of the proper index for that resultset.   For example you report on year or month-base your view does not know that you finnaly wil only filter the current month. An udf with a year/month parameter or daterange there the sql engine has less possibility to miss the index for that criteria.
If report A is all customers for last month then you make a udf that has date-parameters.   If report B is to report all customers for category x products then your filtering will be on a whole other level.

Separating filtering it in UDF's can then get the efect you touch same data on 2 occasions what may impact your performance.
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 250 total points
ID: 40335141
Hi!

Using UDF's (user defined functions ) in views, other UDF's or SP is very common when
1 ) the SQL of a view becomes to complex
2 ) parts of the sql can be reused in elsewhere  ( code reuse :)  )

The only thing that matters is that the sql is as fast and efficient  as possible. :)

Stored Procedures are great for data and sql where a join in a view becomes impossible or is way to slow due to complex data casting/manipulation before a join could occur. In such case  temporary tables  in a SP may become necessary to hold and process the data to achieve desired results.

Regards,
    Tomas Helgi
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
index related performance counter  for SQL server DB 11 41
Use SSRS to email customers? 4 30
When are cursors useful? 8 62
Do not display comma when no last name 8 48
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question