Ayende @ Rahien

Hi!
My name is Ayende Rahien
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

@

Posts: 5,949 | Comments: 44,546

filter by tags archive

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

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

A/ fixed, thanks for noticing.

B/ that is not enabled for production or demos

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

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

@Pete,

How do you handle multiply result sets from a UDF?

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

@Ayende

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

Ayende Rahien

@pete,

Yes, that will work, thanks

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

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

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

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

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

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

in the meantime, I'll keep writing UDFs.

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.

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

  1. The RavenDB Comic Strip: Part III – High availability & sleeping soundly - 11 hours from now

There are posts all the way to May 28, 2015

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    20 May 2015 - Part II – a team in trouble!
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats