June 20, 2003 at 6:19 am
I have an application which stores all of the codes and descriptions in a single table.
The situation I find myself is that I need to return a list of user ids and names. The user ids are in one table (these are users who meet specific criteria) and their descriptions are in the table containing all the codes and descriptions.
I can write this in two ways:
1. A single SQL select statement along the lines of:
select a.userid, b.description
from tableA a, tableB b
where a.userid = b.code
and b.codetype = 'USERNAME'
2. Take advantage of code reuse:
select a.userid, dbo.fn_Description(a.userid,'USERNAME')
from tableA A
NB dbo.fn_Description is a user defined function :
create function dbo.fn_Description (@code varchar(25), @codename varchar(10) )
returns varchar(50) as
begin
return (select description
from code_description_table
where code = @code
and codename = @codename)
end
Both SQL statements return exactly the same results. However, there is a significant difference in the time they take to execute. The first query completes in about 0.2 seconds but the second one take about 1.5 seconds. On the other hand, the second query reuses code so if I need to make a change to the table containing the codes and descriptions I only need to change it once rather than in every SQL statement where the table is used.
The question is simple: which is better - speed of execution or code reuse?
Jeremy
June 20, 2003 at 7:08 am
In general I'd vote for code reuse, but not at the expense of acceptable performance. If a 1 second delay is bearable, why not keep it reusable. Acceptable being the big word of course. In practice good cost and fast are often the same, reusable code at the SQL level usually (to me) means breaking down a large proc into smaller pieces that I can reuse, rather than having one super can do it all proc.
Andy
June 20, 2003 at 7:39 am
Same here, I would go for code reuse IF (and only if) the performance remains acceptable.
Maybe, an in-between approach, would be to create a view for each 'codename' you have. This way, you still have 'reusable' code to some extent and all references to the Description table are localised in a bunch of views.
An additional advantage would be the improved readability of the derived queries.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply