The Daily WTF: Curious Perversions in Information Technology

2006-02-01 Reply Admin

sigh So very sad.

Vector · 2006-02-01 Reply Admin

sigh

Some people just never learn...

First Post!

Xargon · 2006-02-01 Reply Admin

You can lead a horse to water...

...but even if you shove a hose in the horse's mouth and duct tape it close, he can still refuse to swallow.

cwolves · 2006-02-01 Reply Admin

just be glad you didn't get:

@sql = 'IF @Access_Code = 0
IF @Archive_Desc IS NULL
SELECT * FROM [Archive_Letters]
ELSE
SELECT * FROM [Archive_Letters]
WHERE [Archive_Desc] IS NULL

/* Snip 10 Similar Cases */

ELSE
IF @Archive_Desc IS NULL
SELECT * FROM [Archive_Letters]
WHERE [Access_Code] = @Access_Code
ELSE
SELECT * FROM [Archive_Letters]
WHERE [Access_Code] = @Access_Code
AND [Archive_Desc] IS NULL'
EXEC sp_executesql @sql

2006-02-01 Reply Admin

The suggestion of dynamic SQL is a wtf in of itself. What's really needed is non-retarded control logic.

2006-02-01 Reply Admin

I am not familiar with SQL server, but in Oracle we tend to do such things as this:

(I'm using the SQL server syntax)

<font>SELECT * FROM</font> [Archive_Letters]
     <font>WHERE</font> (@Archive_Desc is null or [Archive_Desc] = @Archive_Desc)<font></font>

This way, you just short circuit the where clause if the parameter is null. If it is not null, it gets pulled into the query.

It can make for one big ugly statement, though. I am not sure which is worse, one big ugly statement, or 5 small ugly statements. :-)

2006-02-01 Reply Admin

Unfortunately, SQL Server will not short-circuit something like that in most cases. As a result, using control-of-flow or dynamic SQL in a case like that will often result in much better performance.

2006-02-01 Reply Admin

I'm not sure how dynamic code makes this more maintanable. ... Besides, if you hard code the SQL statement in a string, how is that dynamic?

It looks like the real problem here is that the guy didn't know how to use CASE effectively.

ferrengi · 2006-02-01 Reply Admin

2 submissions in a row from someone named Adam.
Adam isn't even a very common programmer name like Steve.
I better be careful not to name my child Adam lest he get stuck with coworkers who produce WTF code.

2006-02-01 Reply Admin

I always did this in MSSQL by using COALESCE function, such as:

where t.f = coalesce(@prm, t.f)

I would avoid dynamic SQL at almost any cost. I've seen some tremendous performance increases by removing an executed string, no matter how complex the resulting query is.

2006-02-01 Reply Admin

Non-DBA,

Doing that will force a table scan. This will cause a decrease in performance, not an increase. Google "sargability" for more information.

It's funny that so many developers think that dynamic SQL will cause performance problems; I often use it to fix them.

2006-02-01 Reply Admin

Choose whatever name you want - the WTFs will happen anyway. [8-|]

ferrengi · 2006-02-01 Reply Admin

At least Adam does code reviews.
This way he can catch the WTFs before they make it into production.
There would be a lot less material for Alex to choose from if all code were reviewed.
What usually happens is that the program is turned it over to a QA person who only looks at how the program functions and does not see or understand the actual code.

ferrengi · 2006-02-01 Reply Admin

Anonymous:
Choose whatever name you want - the WTFs will happen anyway. [8-|]

No Way!
Your name is the single most important factor that influences whether you will get stuck with a WTF or not.
I have proof!

WTF Batman · 2006-02-01 Reply Admin

Anonymous:
I am not familiar with SQL server, but in Oracle we tend to do such things as this:

(I'm using the SQL server syntax)
SELECT * FROM [Archive_Letters]
WHERE (@Archive_Desc is null or [Archive_Desc] = @Archive_Desc)

This way, you just short circuit the where clause if the parameter is null. If it is not null, it gets pulled into the query.

It can make for one big ugly statement, though. I am not sure which is worse, one big ugly statement, or 5 small ugly statements. :-)

I was thinking the same thing. Dynamic SQL is powerful, but tends to be used to create its own special variety of WTFs, since the code itself varies depending on the data. That can make finding the correct combination of data to generate the buggy SQL very difficult. I used to do this all the time -- formulating SQL in PERL depending on program conditions and executing it. Trouble is that your single SQL statement just turned into a permutation of 20 distinct sql statements. Finding which one is causing issues can be a real b... er.. bummer.

mozka · 2006-02-01 Reply Admin

Anonymous:
Unfortunately, SQL Server will not short-circuit something like that in most cases. As a result, using control-of-flow or dynamic SQL in a case like that will often result in much better performance.

well i ran a very simple test and it can be done, check it out:

-- Create temp table
create table #shorcircuit(
field1 numeric,
field2 numeric null
)

-- Populate temp table
insert into #shorcircuit
values(1, 1)

insert into #shorcircuit
values(2, null)

insert into #shorcircuit
values(3, 3)

insert into #shorcircuit
values(4, null)

declare @var numeric

select @var = null
select * from #shorcircuit where (@var is null or field2 = @var)

drop table #shorcircuit

res2 · 2006-02-01 Reply Admin

Anonymous:
Non-DBA,

Doing that will force a table scan. This will cause a decrease in performance, not an increase. Google "sargability" for more information.

It's funny that so many developers think that dynamic SQL will cause performance problems; I often use it to fix them.

Google gives me:

Did you mean: shagability

hmmmmm, prehaps I DID!

2006-02-01 Reply Admin

mozka:
well i ran a very simple test and it can be done, check it out:

All your test shows is that OR can be used to logically chain predicates. It does not tell us whether or not the query engine evaluates both predicates for each row of the table.

e.thermal · 2006-02-01 Reply Admin

By using executesql your sql code in the stored procedure is never compiled and gets a new query plan everytime it is executed, completely removing some of the reasons for using a stored procedure in the first place. I would say dynamic sql is the WTF here. So Alex are you saying we should trade run time performance for little to no increase in developer maintainability? I would argue the style the original programmer was using would result in the best runtime performance.

Jeff S · 2006-02-01 Reply Admin

Anonymous:
Non-DBA,

Doing that will force a table scan. This will cause a decrease in performance, not an increase. Google "sargability" for more information.

It's funny that so many developers think that dynamic SQL will cause performance problems; I often use it to fix them.

Great point. Ironically, when it comes to performance, dynamic SQL is probably the most efficent if you need to build a complicated condition based on a wide variety of parameter values. Flexbile reporting apps are great candidates for dynamic SQL.

Another misunderstood thing about dynamic SQL is people think that using it automatically means that you are concatentating user input into your SQL strings and are open to injection or other issues. As the original post points out, the spec is to use sp_executesql, allowing you can use parameterized sql statements. This is, of course, one of the subtle points missed by the creator of the WTF as he merrily concatenates the parameters into the strings ....

2006-02-01 Reply Admin

e.thermal:
By using executesql your sql code in the stored procedure is never compiled and gets a new query plan everytime it is executed, completely removing some of the reasons for using a stored procedure in the first place. I would say dynamic sql is the WTF here. So Alex are you saying we should trade run time performance for little to no increase in developer maintainability? I would argue the style the original programmer was using would result in the best runtime performance.

Untrue. sp_executesql reuses query plans for the same query strings as long as they use the same named parameters. You can verify this using a Profiler trace on the SP:CacheHit event.

As for maintainability, it's quite clear to me that maintaining one query, even with permutations, is far superior to maintaining 20.

2006-02-01 Reply Admin

Vector:
First Post!

*sigh*

Some people just never learn...

t-bone · 2006-02-01 Reply Admin

o my god

you just suggested to fix a wtf (the extreme branching) with another wtf (dynamic sql)...
and you're even proud enough of it to post it here....
and then its posted...

I give up, I'll be a janitor or something from now on

e.thermal · 2006-02-01 Reply Admin

Anonymous:
e.thermal:

By using executesql your sql code in the stored procedure is never compiled and gets a new query plan everytime it is executed, completely removing some of the reasons for using a stored procedure in the first place. I would say dynamic sql is the WTF here. So Alex are you saying we should trade run time performance for little to no increase in developer maintainability? I would argue the style the original programmer was using would result in the best runtime performance.

Untrue. sp_executesql reuses query plans for the same query strings as long as they use the same named parameters. You can verify this using a Profiler trace on the SP:CacheHit event.

As for maintainability, it's quite clear to me that maintaining one query, even with permutations, is far superior to maintaining 20.

If you notice the code in the original post that was suggested to be better did not use named parameters and would force a query plan regen on each call. You're right if you use sp_executesql correctly you can re-use plans but compiled stored procedures can still offer better performance.

2006-02-01 Reply Admin

t-bone:

you just suggested to fix a wtf (the extreme branching) with another wtf (dynamic sql)...

And why is dynamic SQL a WTF?

t-bone · 2006-02-01 Reply Admin

because it's often the very reason for the performance issues I get to fix, as noted above it's not always an issue, but suggesting it to a newbie/crap programmer like that will surely result in crap

- because it often indicates that the code calling te proc needs more reviewing than the query

- because it leads to huge and imo unreadable scripts, not any more readable than the original, especially since most sproc debuggers suck, and you have to resort to print @sqlstring to debug it

- because it's more prone to sql injection most of the time, and when you write the sproc you wouldn't imagine that it'll be used from a web page one day

etc etc

Alex Papadimoulis · 2006-02-01 Reply Admin

Jason:
I'm not sure how dynamic code makes this more maintanable. ... Besides, if you hard code the SQL statement in a string, how is that dynamic?
It looks like the real problem here is that the guy didn't know how to use CASE effectively.

I'm no fan of Dynamic SQL myself, but consider the case where you will need to return results from different tables. I've done this on a few occasions when I didn't want to duplicate some of the more complicated logic in views:

IF @SomeParam = 'someval' @TableName = 'Orders_Not_Authorized'
ELSE @TableName = 'Orders_Not_Approved'

e.thermal · 2006-02-01 Reply Admin

e.thermal:
Anonymous:
e.thermal:

By using executesql your sql code in the stored procedure is never compiled and gets a new query plan everytime it is executed, completely removing some of the reasons for using a stored procedure in the first place. I would say dynamic sql is the WTF here. So Alex are you saying we should trade run time performance for little to no increase in developer maintainability? I would argue the style the original programmer was using would result in the best runtime performance.

Untrue. sp_executesql reuses query plans for the same query strings as long as they use the same named parameters. You can verify this using a Profiler trace on the SP:CacheHit event.

As for maintainability, it's quite clear to me that maintaining one query, even with permutations, is far superior to maintaining 20.

If you notice the code in the original post that was suggested to be better did not use named parameters and would force a query plan regen on each call. You're right if you use sp_executesql correctly you can re-use plans but compiled stored procedures can still offer better performance.

i stand corrected, Microsoft has changed their guidance on this, check this link http://support.microsoft.com/default.aspx?scid=kb;en-us;243586 There is a situation where recompile is forced during exectuion and if that happens all gains of the stored procedure are lost to the recompile.

Jeff S · 2006-02-01 Reply Admin

t-bone:

- because it's more prone to sql injection most of the time, and when you write the sproc you wouldn't imagine that it'll be used from a web page one day

See what I mean about people thinking that dynamic SQL = concatenting user input into strings?

t-bone -- you can use parameters with dynamic SQL. In fact, that's the *only* way you use dynamic SQL.

Albatross · 2006-02-01 Reply Admin

t-bone:
o my god

you just suggested to fix a wtf (the extreme branching) with another wtf (dynamic sql)...
and you're even proud enough of it to post it here....
and then its posted...

I give up, I'll be a janitor or something from now on

It's just levels upon levels...

Jeff S · 2006-02-01 Reply Admin

Jeff S:
t-bone:

- because it's more prone to sql injection most of the time, and when you write the sproc you wouldn't imagine that it'll be used from a web page one day

See what I mean about people thinking that dynamic SQL = concatenting user input into strings?

t-bone -- you can use parameters with dynamic SQL. In fact, that's the only way you use dynamic SQL.

oops -- I mean the only way you should use dynamic sql. Big difference, sorry about that.

t-bone · 2006-02-01 Reply Admin

Jeff S:

Jeff S:
t-bone:

- because it's more prone to sql injection most of the time, and when you write the sproc you wouldn't imagine that it'll be used from a web page one day

See what I mean about people thinking that dynamic SQL = concatenting user input into strings?

t-bone -- you can use parameters with dynamic SQL. In fact, that's the *only* way you use dynamic SQL.

oops -- I mean the only way you *should* use dynamic sql. Big difference, sorry about that.

Maybe, but my guess is that when you have to resort to something like that, your app design is likely to be faulty
As I stated, I agree there are uses for dynamic sql in the hands of someone who knows what he's doing can be ok, but suggesting it to someone who has proven to have no clue, is a serious wtf

2006-02-01 Reply Admin

Jeff S:

See what I mean about people thinking that dynamic SQL = concatenting user input into strings?

t-bone -- you can use parameters with dynamic SQL. In fact, that's the *only* way you use dynamic SQL.

It's the only way I use dynamic SQL :)

WTF Batman · 2006-02-01 Reply Admin

Alex Papadimoulis:
Jason:
I'm not sure how dynamic code makes this more maintanable. ... Besides, if you hard code the SQL statement in a string, how is that dynamic?
It looks like the real problem here is that the guy didn't know how to use CASE effectively.

I'm no fan of Dynamic SQL myself, but consider the case where you will need to return results from different tables. I've done this on a few occasions when I didn't want to duplicate some of the more complicated logic in views:

IF @SomeParam = 'someval' @TableName = 'Orders_Not_Authorized'
ELSE @TableName = 'Orders_Not_Approved'

Again, the difference is between someone who understands the power and pitfalls, and someone who doesn't even know what Dynamic SQL is. Dynamic SQL quality goes downhill very quickly if the person writing it isn't an experienced programmer who has a sense of proportion or readable style.

I was handed about a hundred internal-use perl scripts to maintain at a previous job. All of them uses dynamic sql generation to the level of dynamically determining 'and' conditions in a where clause, or even '+' or '-' in expressions -- all predicated directly on web input. Talk about a nightmare!

Sean · 2006-02-01 Reply Admin

Anonymous:
I am not familiar with SQL server, but in Oracle we tend to do such things as this:

(I'm using the SQL server syntax)
SELECT * FROM [Archive_Letters]
WHERE (@Archive_Desc is null or [Archive_Desc] = @Archive_Desc)

This way, you just short circuit the where clause if the parameter is null. If it is not null, it gets pulled into the query.

It can make for one big ugly statement, though. I am not sure which is worse, one big ugly statement, or 5 small ugly statements. :-)

Actually, you'd want the WHERE clause to look like this...

WHERE (@Archive_Desc is null or (@Archive_Desc is not null AND [Archive_Desc] = @Archive_Desc))

Otherwise (in MS SQL Server at least) the SELECT will evaluate all the conditions of the OR (I've tested this both ways in case anyone is curious).

t-bone · 2006-02-01 Reply Admin

WTF Batman:

Again, the difference is between someone who understands the power and pitfalls, and someone who doesn't even know what Dynamic SQL is. Dynamic SQL quality goes downhill very quickly if the person writing it isn't an experienced programmer who has a sense of proportion or readable style.

Exactly, if the original programmer has no clue, and probably the same can be expected from the maintainers of the code, in a few months we can see the dynamic sql version posted here by George, the successor to Adam

2006-02-01 Reply Admin

t-bone:

Maybe, but my guess is that when you have to resort to something like that, your app design is likely to be faulty

Really? Let's delve into this a bit.

Assume you've created an application in which the user can search on any combination of 20 different fields. How would you design your application to search the database on the criteria in such a way that it would not only be as maintainable as possible, but would also perform well even with hundreds of millions of rows and thousands of concurrent users?

t-bone · 2006-02-01 Reply Admin

Anonymous:
t-bone:

Maybe, but my guess is that when you have to resort to something like that, your app design is likely to be faulty

Really? Let's delve into this a bit.

Assume you've created an application in which the user can search on any combination of 20 different fields. How would you design your application to search the database on the criteria in such a way that it would not only be as maintainable as possible, but would also perform well even with hundreds of millions of rows and thousands of concurrent users?

And you expect a dumbie like the one you quoted try and solve that with dynamic sql, I hope he doesn't ask you to fix it

Ytram · 2006-02-01 Reply Admin

t-bone:
- because it's often the very reason for the performance issues I get to fix, as noted above it's not always an issue, but suggesting it to a newbie/crap programmer like that will surely result in crap

- because it often indicates that the code calling te proc needs more reviewing than the query

- because it leads to huge and imo unreadable scripts, not any more readable than the original, especially since most sproc debuggers suck, and you have to resort to print @sqlstring to debug it

- because it's more prone to sql injection most of the time, and when you write the sproc you wouldn't imagine that it'll be used from a web page one day

etc etc

Point 1: Just because an inexperienced coder will screw something up doesn't mean that using something is bad. If we all thought that, then no one would use threading.

Point 2: No it doesn't, it usually means that there's a complicated search that needs to be done with multiple parameters, of which any or none could be null, or the columns they are searching on could be nullable as well. The alternative is table or index scans with COALESCE.

Point 3: It's a hell of a lot more readable than 2^x conditionals in a search procedure.

Point 4: Using sp_executesql correctly will make the dynamic query just as safe with regards to injection as any regular stored procedure.

t-bone · 2006-02-01 Reply Admin

t-bone:
Anonymous:
t-bone:

Maybe, but my guess is that when you have to resort to something like that, your app design is likely to be faulty

Really? Let's delve into this a bit.

Assume you've created an application in which the user can search on any combination of 20 different fields. How would you design your application to search the database on the criteria in such a way that it would not only be as maintainable as possible, but would also perform well even with hundreds of millions of rows and thousands of concurrent users?

And you expect a dumbie like the one you quoted try and solve that with dynamic sql, I hope he doesn't ask you to fix it

and what's wrong with case and if when inline?

2006-02-01 Reply Admin

Sean:

Actually, you'd want the WHERE clause to look like this...

WHERE (@Archive_Desc is null or (@Archive_Desc is not null AND [Archive_Desc] = @Archive_Desc))

Otherwise (in MS SQL Server at least) the SELECT will evaluate all the conditions of the OR (I've tested this both ways in case anyone is curious).

Are you SURE of that?

---
use tempdb
go

create table xyz (searchit int not null primary key)
go

insert xyz (searchit)
select number
from master..spt_values
where type = 'p'
go

set statistics profile on
go

declare @mysearch int
set @mysearch = 100

select *
from xyz
WHERE
(@mysearch is null or (@mysearch is not null AND [searchit] = @mysearch))
go

set statistics profile off
go

drop table xyz
go
---

osp70 · 2006-02-01 Reply Admin

Dynamic Sql has the advantage of being able to take into account the current structure of all tables and indexes as well as making better use of the augogenerated statistic sets created by the query optimizer. Ther can also be advantages in creating and executing dynamic SQL from your stored procs in some cases. You can also use dynamic SQL to replace variables with constant expressions to all the optimizer to have a clearer picture of the data being accessed by the query. This can result in faster execution time because the optimizer is able to accurately assess the data distribution when creating the execution plan.

However in this situation the above advantages don't clearly show themselves.

2006-02-01 Reply Admin

Anonymous:
I am not familiar with SQL server, but in Oracle we tend to do such things as this:

(I'm using the SQL server syntax)
SELECT * FROM [Archive_Letters]
WHERE (@Archive_Desc is null or [Archive_Desc] = @Archive_Desc)

This way, you just short circuit the where clause if the parameter is null. If it is not null, it gets pulled into the query.

It can make for one big ugly statement, though. I am not sure which is worse, one big ugly statement, or 5 small ugly statements. :-)

Arghhh... I wish people used ISNULL or COALESCE

SELECT *

FROM [Archive_Letters]

WHERE ISNULL(@Archive_Desc, [Archive_Desc]) = [Archive_Desc]

Nice and simple and easy... I'd have to say that the Dynamic SQL is a WTF in and of itself...

Jeff S · 2006-02-01 Reply Admin

Anonymous:
Anonymous:
I am not familiar with SQL server, but in Oracle we tend to do such things as this:

(I'm using the SQL server syntax)
SELECT * FROM [Archive_Letters]
WHERE (@Archive_Desc is null or [Archive_Desc] = @Archive_Desc)

This way, you just short circuit the where clause if the parameter is null. If it is not null, it gets pulled into the query.

It can make for one big ugly statement, though. I am not sure which is worse, one big ugly statement, or 5 small ugly statements. :-)
Arghhh... I wish people used ISNULL or COALESCE
SELECT *
FROM [Archive_Letters]
WHERE ISNULL(@Archive_Desc, [Archive_Desc]) = [Archive_Desc]
Nice and simple and easy... I'd have to say that the Dynamic SQL is a WTF in and of itself...

Yeah! Who needs use those silly indexes, right Jeremy?

2006-02-01 Reply Admin

Anonymous:
Arghhh... I wish people used ISNULL or COALESCE
SELECT *
FROM [Archive_Letters]
WHERE ISNULL(@Archive_Desc, [Archive_Desc]) = [Archive_Desc]
Nice and simple and easy... I'd have to say that the Dynamic SQL is a WTF in and of itself...

Yes, agreed... very simple and easy!

... Too bad such practices will drive performance into the ground. Table scans are not a good thing.

---
use tempdb
go

create table xyz (searchit int not null primary key)
go

insert xyz (searchit)
select number
from master..spt_values
where type = 'p'
go

set statistics profile on
go

declare @mysearch int
set @mysearch = 100

select *
from xyz
WHERE
searchit = COALESCE(@mysearch, searchit)
go

set statistics profile off
go

drop table xyz
go
---

2006-02-01 Reply Admin

The obvious answer is that you need an XML based dynamic query engine in front of this. That will fix all of your problems. The good news is that there is a company on this very website who could provide that technology

A Wizard A True Star · 2006-02-01 Reply Admin

Anonymous:

insert xyz (searchit)
select number
from master..spt_values
where type = 'p'
go

Not a terribly good example for benchmarking... On my SQL Server I only have 255 rows in xyz. It's my understanding that for tables with a small number of rows, the optimizer figures it's quicker to do a table scan then take any time to figure out which index to use, so it will just do a table scan by default. Try creating a table with a million rows and you might have a good test case.

Anonymoose · 2006-02-01 Reply Admin

Alex Papadimoulis:

... Adam asked the developer to use dynamic SQL. The developer was unfamiliar with this, so Adam explained that he simply needed to build a string that contained the SELECT statement, and then use sp_executesql to execute that statement. This way, Adam explained, all the common parts (the SELECT and FROM clauses) could be kept common and all the selection logic kept in the branching logic.

I wonder what the other developer was thinking, when he implemented these changes: "That Adam guy is such a retard... this is even worse than before!"

2006-02-01 Reply Admin

I work with people (plus legacy code) who are terrified of using a variable, let alone devising a data structure.

2006-02-01 Reply Admin

EXEC sp_executesql @sql executes the sql in an entirely different thread of execution. It can be really useful for custom dynamic reporting, but stick it into a sproc and you now have a nightmare for any DTS package that wants to use that data.

Let's Go Dynamic

Leave a comment on “Let's Go Dynamic”