Replies

I wouldn't use stored procedures for data input, I would seperate that logic out into code (it will run faster, and won't be a 'single-db' lockin).

But that's all beside the point. You just have a grudge against Microsoft, and I'm not going to help you indulge it any more this morning.

Ciao...

There is no way that database statements embedded in code (which make programs harder to maintain, BTW - since you have to touch your code every time there is a database change) will run faster than compiled stored procedures.

I once thought so too. But test it side by side.

And now that I've looked into it, it makes perfect sense.

A DB is not optimized for number-crunching. It's only optiimized for data storage and retrieval.

As you know, there are a hundred ways you can code the number-crunching part of report building. You often have to try it a half-dozen different ways to get the best performance. The DB will not do this.

Having the DB "build" a report is going to be slower than having a programming language build the report. The SP will have to build temporary tables, and the like, and that stuff is *slooooow*.

Try it. Try just doing simple SQL calls and bringing back the raw data as much as possible, and then building the report yourself. If you're MS-only, try C#.

And you do *not* have to touch the code when the db changes -- unless they change the names of fields, which would also disable the SPs.

In fact, you have a much *easier* time both writing and maintaining the code if it's in a language other than SQL.

Programming a complex report in SQL is a nightmare, compared to Java or C#.

And then there's the portability thing -- with SPs you're going to have a major problem migrating to other DBs. And with your architecture, if you have to add into that report some data that comes from another DB you're dead in the water, and need an entire re-write. That architecture will not scale.

I'm sorry you're closing your mind to non-MS solutions. I suppose if you work for an MS-only shop then that's sort of where you started, so I guess that's why you see me as just someone with an axe to grind.

Oh, well, good luck on your work.

Please. There is no way that database statements embedded in code (which make programs harder to maintain, BTW - since you have to touch your code every time there is a database change) will run faster than compiled stored procedures.

Don't waste your time with Harr: It's obvious that he doesn't know what he's talking about. As you've correctly pointed out, stored procedures are faster precisely because they're precompiled and the SQL engine doesn't have to build a query plan -- it's done in advance, at the time you add the stored proc to the database. Likewise, stored procs are not susceptible to SQL injection attacks because all of the incoming data is considered literal and not part of the query semantics.