The Daily WTF: Curious Perversions in Information Technology

ferrengi · 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

How about cases where you need to run a bunch of stored procedures. The names of the procedures are not known at compile time. Furthermore, the user can decide to make certain procedures active and others inactive.
Do you have a better way to handle this type of situation that does not involve dynamic SQL?

John Bigboote · 2006-02-01 Reply Admin

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

I'm scared of clowns. And spiders.

2006-02-01 Reply Admin

Anonymous:
Vector:
First Post!

*sigh*

Some people just never learn...

But they're fun to watch and laugh at.

ammoQ · 2006-02-01 Reply Admin

In Oracle, using non-dynamic SQL provides two advantages:
a) in case of stored procedures or preprocessors: a lot of checks during compile time, catches many typos (table names, column names, etc.)
b) the parsed statement is cached; this reduces the overhead for the execution of the statement (this is especially important if the statement accesses relatively small tables); dynamic sql may flood the cache of the pre-parsed statements (espcially if constants are used in the statement instead of bind variables)

For that reasons, the (undeniable) advantages of dynamic sql should always be weighted against the advantages of static sql.

2006-02-01 Reply Admin

Dynamic SQL should only be used as a last resort, even then I would try and find another way, horrible, almost as bad as cursor's /shudder

fcarlier · 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?

I always use something like this:

SELECT row_id FROM Table WHERE @Parameter IS NULL OR @Parameter = Column

and use the WITH RECOMPILE option. I'm eager to find out why dynamic SQL would be a better option.

fcarlier · 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?

I always use something like this:

SELECT row_id FROM Table WHERE @Parameter IS NULL OR @Parameter = Column

and use the WITH RECOMPILE option. I'm eager to find out why dynamic SQL would be a better option.

fcarlier · 2006-02-01 Reply Admin

Well... so much for trying to make this board software do what I want. WTF...

Arancaytar · 2006-02-01 Reply Admin

Alex Papadimoulis:

<font color="#000099">ELSE
IF</font> @Archive_Desc <font color="#000099">IS NULL</font>
@sql = <font color="#990000">'SELECT * FROM [Archive_Letters]
WHERE [Access_Code] = '</font> + <font color="#000099">CAST</font>(@Access_Code <font color="#000099">AS VARCHAR</font>(5))
<font color="#000099">EXEC</font> sp_executesql @sql
<font color="#000099">ELSE</font>
@sql = <font color="#990000">'SELECT * FROM [Archive_Letters]
WHERE [Access_Code] = '</font> + <font color="#000099">CAST</font>(@Access_Code <font color="#000099">AS VARCHAR</font>(5)) + <font color="#990000">'
AND [Archive_Desc] IS NULL'</font>
<font color="#000099">EXEC</font> sp_executesql @sql

Oh, but don't say you didn't expect this.

Brings to mind the "exception handling" post though - the one that caught an exception only to throw it again...

2006-02-01 Reply Admin

A Wizard A True Star:
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.

A lot more goes into it than just number of rows. The most important element is statistics as they relate to selectivity. And in this case you have an extremely selective index, so 255 rows is plenty for a seek:

---
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 = @mysearch
go

set statistics profile off
go

drop table xyz
go
---

2006-02-01 Reply Admin

fcarlier:
I always use something like this:
SELECT row_id FROM Table WHERE @Parameter IS NULL OR @Parameter = Column

and use the WITH RECOMPILE option. I'm eager to find out why dynamic SQL would be a better option.

Please see some of my earlier responses where I posted code fragments. You'll see that the WHERE @Param IS NULL OR ... option results in a table scan. Proper use of dynamic SQL eliminates that problem.

2006-02-01 Reply Admin

Just a FYI for those of us using Oracle. The WHERE @Param IS NULL OR ... option does not perform a full table scan.

It must be a mssql-ism.

2006-02-01 Reply Admin

Vector:
*sigh*

Some people just never learn...

First Post!

Goodie for you, here's your gold start. OH WAIT - You weren't first! Here's your sign.

2006-02-01 Reply Admin

Exactly! Thank you. Dynamic SQL is definately a WTF.

Quinnum · 2006-02-01 Reply Admin

Indeed. Yes. Dynamic SQL (shudder) is a WTF, all the time, regardless of the situation, ever, period, end of story. Never ever use Dynamic SQL (shudder)

It's definitely better to hand code all possible combinations of user input on a complex search form. Yes. All the time, just so we can avoid the horribleness of the Dynamic SQL (shudder).

</sarcasm, for those who missed it>

I'm guessing you purists are paid by the line then, and love huge amounts of code??

2006-02-01 Reply Admin

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

Variables are very bad. I only code using constants. That way the results are always predictable.

Anonymoose · 2006-02-01 Reply Admin

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

Variables are very bad. I only code using constants. That way the results are always predictable.

What? Haven't you heard of 'dynamic constants' yet?
They can save you a pile of code, but I guess they introduce some sort of performance impact.

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...

I'm confused. No where in the queries does the expression "[Archive_Desc] = @Archive_Desc" appear.

If @Archive_Desc is NOT NULL (the else branch), then the where condition is "[Archive_Desc] IS NULL".

If you are going to re-write the code, at least make sure it does the same thing as the original queries.

(Why you want to get all of the rows where Archive_Desc IS NULL when the input parameter @Archive_Desc is NOT NULL is beyond me and also WTF IMHO.)

2006-02-01 Reply Admin

Anonymous:
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...
I'm confused. No where in the queries does the expression "[Archive_Desc] = @Archive_Desc" appear.

If @Archive_Desc is NOT NULL (the else branch), then the where condition is "[Archive_Desc] IS NULL".

If you are going to re-write the code, at least make sure it does the same thing as the original queries.

(Why you want to get all of the rows where Archive_Desc IS NULL when the input parameter @Archive_Desc is NOT NULL is beyond me and also WTF IMHO.)

The quoting function is also 100% pure WTF.

2006-02-01 Reply Admin

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

Variables are very bad. I only code using constants. That way the results are always predictable.

What? Haven't you heard of 'dynamic constants' yet?
They can save you a pile of code, but I guess they introduce some sort of performance impact.

testing the quote function. Why the hell doesn't it work? What user error am I committing? Maybe I am WTF.

2006-02-01 Reply Admin

Whooo Hoooo,

I'm Back. Be afraid, and all that.

Having being chastised for being "arrogent". . .

A database, after all is merely a collection of files.

If, one, (a person) can grok (understand in the ultimate sense of understanding) what significance fields in one or more collections of files might have to drawing meaningful conclusions, then that one (hopefully, the same person) might manipulate the FILES in a manner that might extract meaningful conclusions.

On the gripping hand. . . If all you know about your job is "joins", "outer joins". . .

Sadly, you are at the mercy of your software.

He who lives by the software, dies by the software.

Quite frankly, ALEX these posts are not WTF. They are WGAS (who gives a sh*t).

Bottom Feeders.

I wish I was Sincerly

Gene Canuck

2006-02-02 Reply Admin

Sorry, Gene.

I meant, "Sincerely"

Hopefully, your genes have been successful.

Simplifier.

Let's see if fortune is captha

Obviously, not quick enough. reference might do

2006-02-02 Reply Admin

Wow! Talk about broken software.

If anyone is interested in seeing a blog that works, I would refer you to

http://www.felbers.net/fa/

"Fanatical Apathy"

Apart from the best (most intelligent) comments to a blog (as opposed to this site) -- nyah nyah nyah, I'm wearing asbestos. . . It's a really well done site.

Comment by number, for example.

"Free is worth whatever it costs you"

badly paraphrased from Robert A. Heinlein.

Magic Duck · 2006-02-02 Reply Admin

Arancaytar:

Brings to mind the "exception handling" post though - the one that caught an exception only to throw it again...

This is obviously some kind of general wtf pattern as it reminds me of the guy who had to modify his über long if-elseif construct into switch-case construct and ended up having all of his his previous code in default block...

Arancaytar · 2006-02-02 Reply Admin

Magic Duck:
Arancaytar:

Brings to mind the "exception handling" post though - the one that caught an exception only to throw it again...

This is obviously some kind of general wtf pattern as it reminds me of the guy who had to modify his über long if-elseif construct into switch-case construct and ended up having all of his his previous code in default block...

I've helped correct Java homework sometimes, and I came across something resembling this bit (reproduced as well as I remember):

for (int i=0;i<5;i++) {
  if (i==0) callFunction(5);
  if (i==1) callFunction(4);
  if (i==2) callFunction(3);
  if (i==3) callFunction(2);
  if (i==4) callFunction(1);
}

Someone had evidently been told to "use loops, for goodness' sake!"

gremlin · 2006-02-02 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.

Google says:
Did you mean: shagability

ammoQ · 2006-02-02 Reply Admin

Arancaytar:
I've helped correct Java homework sometimes, and I came across something resembling this bit (reproduced as well as I remember):
for (int i=0;i<5;i++) {
 if (i==0) callFunction(5);
 if (i==1) callFunction(4);
 if (i==2) callFunction(3);
 if (i==3) callFunction(2);
 if (i==4) callFunction(1);
}
Someone had evidently been told to "use loops, for goodness' sake!"

This is a classic WTF, though most people use switch/case instead of ifs.

Anonymouse · 2006-02-02 Reply Admin

cwolves:
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

Ohh! Structured Quine Language! Nifty!

2006-02-02 Reply Admin

Quinnum

Actually one of the last projects I did, was to take some very very unreadable dynamic SQL Server SQL code and structure it using some pretty standard logic. Yes the procedure was roughly 20 times longer when I had finished, however it was also infinitely more readable and roughly five times as quick.

In terms of coding, 99% of the time Dynamic SQL is the quick fix (as you have stated), however in terms of efficiency Dynamic SQL is the wrong choice if the logic can be coded in a more conventional manner, try it yourself, you may be surprised!!!

I am not a purist by nature, however saying that I look for the most efficient way for something to work, are you saying that you dont?

fcarlier · 2006-02-02 Reply Admin

Anonymous:
fcarlier:
I always use something like this:
SELECT row_id FROM Table WHERE @Parameter IS NULL OR @Parameter = Column

and use the WITH RECOMPILE option. I'm eager to find out why dynamic SQL would be a better option.

Please see some of my earlier responses where I posted code fragments. You'll see that the WHERE @Param IS NULL OR ... option results in a table scan. Proper use of dynamic SQL eliminates that problem.

Google for

sargability site:microsoft.com

did not return anything.

stevekj · 2006-02-02 Reply Admin

Able to correctly read the post:

I'm confused. No where in the queries does the expression "[Archive_Desc] = @Archive_Desc" appear.
If @Archive_Desc is NOT NULL (the else branch), then the where condition is "[Archive_Desc] IS NULL".

If you are going to re-write the code, at least make sure it does the same thing as the original queries.

(Why you want to get all of the rows where Archive_Desc IS NULL when the input parameter @Archive_Desc is NOT NULL is beyond me and also WTF IMHO.)

I was going to bring up this point too, but you beat me to it. I was hoping that there was some kind of weird SQL logic going on here that I didn't understand as a non-SQL expert, but it doesn't look like it. It's just WTF.

2006-02-02 Reply Admin

Neither if/then nor switch/case is needed :

for (int i=0;i<5;i++) {
  callFunction(5 - i);
}

LabSpecimen · 2006-02-02 Reply Admin

I'm pretty sure this guy works for me now.

brazzy · 2006-02-02 Reply Admin

Anonymous:
Neither if/then nor switch/case is needed :
for (int i=0;i<5;i++) {

callFunction(5 - i);
}

Thank you,

2006-02-02 Reply Admin

http://blog.ragan.com/archives/sqlblog/2005/08/sargable.html

2006-02-02 Reply Admin

After doing some testing, it looks like doing the 'null is null' thing sometimes causes a full table scan. I tested with Oracle 10g, on a table with about 200k rows.

Most unique!

whojoedaddy · 2006-02-02 Reply Admin

Wow. You've got to be kidding me.

JohnO · 2006-02-02 Reply Admin

Anonymous:

In terms of coding, 99% of the time Dynamic SQL is the quick fix (as you have stated), however in terms of efficiency Dynamic SQL is the wrong choice if the logic can be coded in a more conventional manner, try it yourself, you may be surprised!!!

If you are using sp_executesql with parameters on MS SQL, your statement is completely untrue.

twks · 2006-02-02 Reply Admin

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

Amen!

Dynamic SQL is a bad idea for performance and security reasons (it requires that all users have full access to the table). It should be avoided as much as possible. The logic could easily be cleaned up instead.

2006-02-02 Reply Admin

twks:

Dynamic SQL is a bad idea for performance and security reasons (it requires that all users have full access to the table). It should be avoided as much as possible. The logic could easily be cleaned up instead.

Can you show me an example of how or when properly used dynamic SQL (parameterized) would cause a performance problem compared with static SQL?

And as for security, that's no longer an issue in SQL Server 2005. Look up the EXECUTE AS option.

Bustaz Kool · 2006-02-02 Reply Admin

brazzy:

Thank you,

Funniest F***in' thing I've seen in a while.

Thank-You, Master!

2006-02-02 Reply Admin

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

right :-))

Maurits · 2006-02-02 Reply Admin

Dynamic SQL is not a WTF.

The WTF is the implementation.

Dynamic SQL without parameters is not a WTF. In fact there are cases where dynamic SQL without parameters OUTPERFORMS dynamic SQL with parameters.

See Ian Jose's post on condition-specific stored procedures for an example:
http://blogs.msdn.com/ianjo/archive/2005/11/10/491547.aspx

2006-02-02 Reply Admin

Maurits:

In fact there are cases where dynamic SQL without parameters OUTPERFORMS dynamic SQL with parameters.

Such as?

See Ian Jose's post on condition-specific stored procedures for an example:
http://blogs.msdn.com/ianjo/archive/2005/11/10/491547.aspx

His suggestion for mulitple stored procedures will fix some performance problems but could introduce a type of coupling between the application and the database; the application might have to know which stored procedure to call. If you're going to do this, encapsulate the fact that there are two stored procedures instead of one by using a shell stored procedure to make the decision. Or, see my post on this topic, in which I show how to deal with this problem using (surprise, surprise) dynamic SQL:

http://sqljunkies.com/WebLog/amachanic/articles/StoredProcedureCaching.aspx

And in response to your unanswered question in Ian Jose's weblog, I suggest that you read the following article:

http://www.sommarskog.se/dyn-search.html

ammoQ · 2006-02-02 Reply Admin

Maurits:

Dynamic SQL without parameters is not a WTF. In fact there are cases where dynamic SQL without parameters OUTPERFORMS dynamic SQL with parameters.

See Ian Jose's post on condition-specific stored procedures for an example:
http://blogs.msdn.com/ianjo/archive/2005/11/10/491547.aspx

The same is true for Oracle. Having a literal instead of a variable in an SQL-Statment can lead to a different execution plan, which may perform better. (Same rationale as given in Ian Jose's post)

t-bone · 2006-02-02 Reply Admin

Quinnum:
Indeed. Yes. Dynamic SQL (*shudder*) is a WTF, all the time, regardless of the situation, ever, period, end of story. Never ever use Dynamic SQL (*shudder*)

It's definitely better to hand code all possible combinations of user input on a complex search form. Yes. All the time, just so we can avoid the horribleness of the Dynamic SQL (*shudder*).

I'm guessing you purists are paid by the line then, and love huge amounts of code??

I'll reiterate one more time, dyn sql in the hands of a skillfull dev can be a last resort, but suggesting it to the original coder WILL result in multiple wtf's.
Like he knows about execution plans etc.

I'm sure the newbie dev could use some more pointers than just "use dyn sql"

2006-02-02 Reply Admin

t-bone:

I'm sure the newbie dev could use some more pointers than just "use dyn sql"

The developer in question was shown two generalized examples and linked to at least three articles on the topic. Shouldn't that have been enough?

jsmith · 2006-02-02 Reply Admin

Dynamic SQL is a good solution for a lot of problems, but it has its own problems. I usually use dynamic SQL to get out from under existing WTFs -- like when someone decided to make two different entities where one would be just fine (think CustomersCanada and CustomersUS). My biggest complaint about dynamic SQL is the effect on security. The whole idea that an SP can wrap the security of a call is lost with dynamic SQL.

Personally, I think SQL is a horrible language for string manipulation. I'd much rather do it in a data access layer class. Think about how much easier a lot of things like cursors and dynamic SQL are in C# instead of T-SQL. Heck, that's why they put CLR languages in SQL 2005, now I can put my code in the database so I can use it in the reporting system too. Sure SPs can provide a performance increase, but 99.9% of the time it isn't worth it -- buy another server and let the developers get the job done better and faster.

BTW, SQL 7 and later have ad-hoc batch caching. That means that code-generated blocks of T-SQL enjoy the same pre-compiled performance benefits that SPs do. The practice of writing everything as an SP as a general rule is pre-optimization at its worst. There aren't a whole lot of people that can write quality T-SQL compared to the number that can write quality C#. The tools for T-SQL suck too.

2006-02-03 Reply Admin

As far as I know, this is a special case in SQL Server. Since you're evaluating '@var is null', SQL Server will realize that this boils down to a constant, and resolve it before resolving the rest of the query. So the performance of:
    select * from table where (@var is null or field2 = @var)
should be comparable to:

if @var is null
    select * from table where @var is null
else
    select * from table where field2 = @var

In one case it's evaluated in the where clause ahead of time, in the other it's evaluated in the if statement. I think the second condition is actually subject to a bug too, I believe SQL Server will, on first run when it compiles it, optimize for one branch only and always table scan in the other branch. Which is very bad in a stored procedure. Though I may have that backwards - any DBAs out there?

2006-02-06 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.

A common belief, but incorrect in recent versions of SQL Server. Also note that if you are using CASE statements in a stored proc the query plan is bloody useless anyway (if its even created) since each branch of the CASE will need a separate query plan. Although you believe otherwise, the use of sp_executesql will usually speed up this kind of situation.

e.thermal:
I would argue the style the original programmer was using would result in the best runtime performance.

And you'd be wrong.

Let's Go Dynamic

Leave a comment on “Let's Go Dynamic”