October 20, 2008 at 6:22 am
In our database we have 1 big option table in which we store all possible options and types, currently there are 500 options in this table :
CREATE TABLE [dbo].[Options](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[IDParent] [smallint] NULL,
[ApplicationIdentifier] [tinyint] NULL,
[Code] [varchar](15) NOT NULL,
[Name] [varchar](75) NOT NULL,
)
IDParent is the parent option, which describes the type of option
ApplicationIdentifier is the ID that we use in de (java)software, eg: 1,2,3,4 instead of 312, 521, 522, 894
Code is a short description of the option
To make things a little more clear i post an example of periods stored in this table:
--Parent insertion:
INSERT INTO Options (ApplicationIdentifier, Code, Name) VALUES (0, 'PERIOD', 'Periods') --for further processing: ID will be 512
--Children:
INSERT INTO Options (IDParent, ApplicationIdentifier, Code, Name) VALUES (512, 1, 'D', 'Daily')
INSERT INTO Options (IDParent, ApplicationIdentifier, Code, Name) VALUES (512, 2, 'W', 'Weekly')
INSERT INTO Options (IDParent, ApplicationIdentifier, Code, Name) VALUES (512, 3, 'M', 'Monthly')
These Options are used in almost all queries, but also everywhere in the software (dropdowns are automaticly filled with these options when you give a certain parentID). The table isn't very big and there are enough indexes to make every possible query as fast as possible. But, let's take a look at a query that uses this option table:
SELECT ID
, Name
, f_getOptionAppIdentByID(IDPeriod) AS period
, f_getOptionAppIdentByID(IDType) AS type
FROM Tasks
The Tasks table uses the Options 2 times to store some kind of option (IDPeriod & IDType). When we run a query for usage in the software we don't want to see the real ID, but the applicationID (eg: 1 instead of 414 and 2 instead of 415). The translation from ID to ApplicationID is done with the f_getOptionAppIdentByID function, and when you inspect the queryplan you can see that this function costs hardly any time.
But now we come to the real problem: I looked into the dm_exec_query_stats view and found that this function was the Number 1 CPU user! 1 run costs only 9 cpuTime, but since it is run millions of times the costs for this function are extremely high. So to fix this i'm looking for some possible solutions:
1. Replace the function with joins on the option table, it seems like a very good idea.... except that the function is used all over the software so when we want to replace it we have to look into all our code. And besides that: in some tables the Option table is used 10 times, so in that case we have to join the Option table 10 times which makes the queries quite difficult to read & understand
2. Stop wining about the usage of ApplicationID's in the software and OptionIDs in the database, i never understood the idea behind the whole applicationID's but when i used an optionID in the software i would get into an argument with my colleagues. The problem behind this solution: my colleagues think that the usage of OptionIDs in the software make it unreadable and sensitive for errors. So they think this solution is the last resort...
Does anyone have some other suggestions, is there maybe some kind of solution in which the function returns the value even faster then the current 9 CPU cycly. Or should we rewrite the whole usage of this option table....
October 20, 2008 at 7:45 am
You definitely want to get rid of the scalar valued functions in the select list. Those will be performance killers almost every time. If you can do it with joins, derived tables, or a table valued function that you join to you'd be better off.
Oh and it looks like, based on your question, that you are not using stored procedures for data access. If you were using stored procedures you could "fix" this code without affecting the application code at all. Just make sure you are returning the same dataset as you optimize and your application will never know you changed anything, other than running faster.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2008 at 10:31 am
You REALLY should have given us the function code! Anyway, I can tell what is happening even without it: You are doing table accesses on the options table for each and every row you scan from the table you are selecting from -TWICE because you have two function calls. This is essentially a cursor under the covers. And the graphical query plan (and statistics IO) in SSMS doesn't even report it correctly. Set up a profiler trace and watch the reads. And if you want to be astounded, do a RPC: stmt completed event trace. I have seen literally millions of function executions for what looks like a totally innocuous select such as yours. Scary bad stuff.
Figure out a way to in-line the function code in your selects and you should open up all kinds of headroom on the box. I routinely get 4 to even 5 ORDERS OF MAGNITUDE improvement from this type of refactoring!
BTW, don't feel bad about this. I am a performance tuning expert and I am seeing this problem at all of my recent clients. You are not alone!! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 22, 2008 at 12:01 am
TheSQLGuru (10/21/2008)
You REALLY should have given us the function code! Anyway, I can tell what is happening even without it: You are doing table accesses on the options table for each and every row you scan from the table you are selecting from -TWICE because you have two function calls. This is essentially a cursor under the covers. And the graphical query plan (and statistics IO) in SSMS doesn't even report it correctly. Set up a profiler trace and watch the reads. And if you want to be astounded, do a RPC: stmt completed event trace. I have seen literally millions of function executions for what looks like a totally innocuous select such as yours. Scary bad stuff.Figure out a way to in-line the function code in your selects and you should open up all kinds of headroom on the box. I routinely get 4 to even 5 ORDERS OF MAGNITUDE improvement from this type of refactoring!
BTW, don't feel bad about this. I am a performance tuning expert and I am seeing this problem at all of my recent clients. You are not alone!! 🙂
First of all: the function is indeed a simple selectstatement from the Option table.
Coincidentally, i started a profiler trace yesterday because i was looking for more / other performance problemes. And i had to filter out the Option table queries because otherwise i couldn't see the normal queries between all this garbage. I know for sure now we HAVE to get rid of this stupid function!!
BTW. I don't feel bad at all! When i started working at this company 2 years ago this function was already in use. At the time my SQL knowledge was at a minimum so by then a hardly knew what a function was, so i took for granted what the other developers told my about SQL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply