?
Solved

scalar function brings performance problems

Posted on 2010-08-15
4
Medium Priority
?
413 Views
Last Modified: 2012-05-10
Hi experts, a friend told me that a scalar function brings performance problems because it runs row by row, is that true? but does not use scalar function then how would you do?. ULR know of any that speaks this kind of thing
0
Comment
Question by:enrique_aeo
[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 2

Assisted Solution

by:coalanexpert
coalanexpert earned 332 total points
ID: 33443071
Depending on the cost of the query that the function actually executes it might affect performance. The book says to try to use inline table functions instead, but again if your scalar function is simple, no need to complicate things.
Here a reference on how to use inline table valued functions

http://stackoverflow.com/questions/800017/why-do-sql-server-scalar-valued-functions-get-slower

0
 

Author Comment

by:enrique_aeo
ID: 33443117
I have this function
CREATE FUNCTION dbo.SumVta(@ProductID int)
RETURNS int
AS
BEGIN
  DECLARE @ret int
  SELECT @ret = SUM(Quantity)
  FROM [Order Details]
  WHERE ProductID = @ProductID
  IF (@ret IS NULL)
  SET @ret = 0
RETURN @ret
END
how could I make a table inline Functions?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 336 total points
ID: 33443484
>because it runs row by row

yes.

your function would require a (clustered) index on ProductID on table order details to run fastest, unless you can build up the "sum_quantity" column in the products table directly, and keep that updated with triggers (real-time) or regular jobs (scheduled updates)
0
 
LVL 10

Assisted Solution

by:joriszwaenepoel
joriszwaenepoel earned 332 total points
ID: 33445631
I think you can solve this with a JOIN and a GROUP BY:

Something like:

SELECT Order.OrderID, OD.ProductID, SUM(OD.Quantity) AS SumVta
FROM [Order]
INNER JOIN [Order Details] OD
ON Order.OrderID = OD.OrderID
GROUP BY Order.OrderID, OD.ProductID

0

Featured Post

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

762 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