Ayende @ Rahien

It's a girl

Shocking Rob

I am posting this mainly because I want to see how far I can shock Rob Conery

image

The exception is raised by the EnsureMaxNumberOfQueriesPerRequestModule, and it is currently set on the development level, for QA/Staging, I would probably reduce it further, although I have some pages where I

Oh, and to Rob, that was a classic error of doing query per node (instead of doing a single query) (added an eager load instead of a query and was done). I am doing some performance tuning right now, and all in all, it is very boring. Find a hot spot, consolidate data access, use MultiCriteria or MultiQuery, move on.

Comments

PeterI
06/20/2007 04:31 PM by
PeterI

Hmm... Two problems here

a) Performed has a typo

b) "hack" is a really awful parameter name, possibly it should be 'overridequerycheck' which even it did escape into production wouldn't be too embarrassing.

I hate it when something like this escapes into the outside world and a parameter name of "hack" is already digging the tunnel under the wall and begging to be an embarrassment in the first client demo.

Ayende Rahien
06/20/2007 04:37 PM by
Ayende Rahien

A/ fixed, thanks for noticing.

B/ that is not enabled for production or demos

Harry
06/20/2007 05:05 PM by
Harry

Very cool. Can't wait to see what you will come up with. You do record videos on this, right?

Thank you very much.

pete w
06/20/2007 05:24 PM by
pete w

Interesting post oren.

on a side note, I would like to mention my biggest problem with Rob's example: Just like so many other developers he makes the all-too-common mistake of misusing stored procedures.

Stored procedures are for side effects and not for read-only operations. UDFs are for parameter-based select statements. This has been around since SQL 2000, and its the same in 2005.

Why are UDFs better for read-only? Think of UDFs as views with parameters. They have strongly typed values while stored procedures expose no explicit output contract.

I can say 'select * from ' but I cannot say 'select * from '

Schema binding aside, this is important when you want to reuse the return values from your function in other database code. Its also better because a UDF has similar syntax to a view.

Cheers

-p

Ayende Rahien
06/20/2007 05:51 PM by
Ayende Rahien

@Pete,

How do you handle multiply result sets from a UDF?

pete w
06/20/2007 06:10 PM by
pete w

Well, UDFs can be defined to return a single value, or a table.

If the UDF returns a table, then you can handle multiple result sets just like you would a table, with a slight syntax change.

for example

select * from UDF1(@val);

select * from UDF2(@val);

go

or you can join them

select * from UDF1(@val) inner join UDF2(@val) on

go

so UDF1 and UDF2 can be treated like tables provided UDF1 and UDF2 are defined to return tables in their signature. The alias names are frequently used with UDFs, as you can imagine.

I'm not sure if you knew this already, but due to the nature of UDFs, they are read-only, while anything can happen inside of a sproc. This is better because there is no way to enforce a sproc to be read-only (triggers anye? ;) )

Evan
06/20/2007 06:47 PM by
Evan

@Ayende

I just about spit my coffee when I read that image...haha

Ayende Rahien
06/20/2007 07:03 PM by
Ayende Rahien

@pete,

Yes, that will work, thanks

Ayende Rahien
06/20/2007 07:05 PM by
Ayende Rahien

For fun, it was the project manager trying to get a snick preview of the new super admin stuff, which we haven't had the time to test yet. That is what he gets for working against my staging server.

josh
06/20/2007 07:37 PM by
josh

UDF's (User Defined Functions) aren't really meant for direct data access. They are useful in simplifying complex joins or sub queries. Also, useful for joins or subqueries that are used in multiple data access queries. (http://www.15seconds.com/issue/000817.htm)

It could see using UDF's in a direct sql call or in a stored proc, but calling a UDF directly doesn't seem right not to mention asking for performance problems. Just use the SQL statement from the UDF or call a sproc.

Here's a big performance warning! It is pretty common to return table variables from UDF's, but avoid using that for high volume reads. I recently ran into major locking/blocking in the Master DB because of a UDF that was getting called very often.

Oh and, personal opinion, triggers are evil and rarely necessary.

Rob Conery
06/20/2007 08:08 PM by
Rob Conery

LOL I literally am wiping the Dr. Pepper off my laptop! That's hysterical! I can see smoke coming out of your server now... "help me I'm melting...."

@pete: Perhaps, while i'm in Redmond, I can put you in touch with the SQL team so you can tell them how SPs are supposed to be used :p.

Your example of using a Function is nice, but UDFs are for internal ops, not for direct access and using them as such is poor design for any application. Gotta say I'm surprised that you would 1) suggest such a thing (and moreover complain about not being able to say "select from sp" - that's silly) and 2) not back anything up with numbers or citations.

Pete w
06/20/2007 09:47 PM by
Pete w

OK I'm surprised that I'm the only one using them in this manner, but I'll be happy to share with you where I learned this trick!

I dont have the exact quote handy, but Rob Vieira's professional SQL books recommends UDFs for fast read-only operations, custom aggregations and parameterized views, this was when I started to use it, and I was pleased by the results.

@josh:

UDFs are like sprocs in the sense that their query plans are compiled and stored for reuse, so they have the same speed benefits as sprocs, and I dont see a reason why they should be called any less directly than sprocs. But I do agree with you on triggers, they are only useful for audit/logging purposes IMO.

@rob:

I would refer to this link as a backup:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/stored-procedures-are-not-parameterized-views.aspx

so maybe you could enlighten me on the sql team's opinion of my "foreign ways". Seriously, I would be excited to hear what they say!

thanks for reading

-p

Anastasiosyal
06/22/2007 09:08 AM by
Anastasiosyal

Hi, I am also very much interested in what the sql team would have to say as to what would be a best practice to handle parameterized read only queries (SPs or Table funcs?). Rob if you could give us some feedback on this that would be great :)

pete w
06/22/2007 04:32 PM by
pete w

I'm disappointed that Rob didn't write us back :(

in the meantime, I'll keep writing UDFs.

Adam
06/28/2007 12:02 AM by
Adam

Hi,

Just thought i'd let you know I am using this technique to great effect.

In addition, I added an appSetting key that lets you enter that start of a query eg. "select foo0", and in the appender I check if the query I am logging starts with this string, and if so, break into the debugger.

This lets you quickly find where are particular query is being executed in your codebase.

Cheers,

Adam.

Comments have been closed on this topic.