SQL Server 2008: ARITHABORT error with Spatial data

This is a nice cryptic error:


UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. 

If you get the following error with a SQL Server 2008 database when your trying to update some spatial data, the solution is to check the compatibility level for the database, I was working on an application which has been around for a while and it was set to be compatible with Sql Server 2000, hence the error.

I was a bit stumped initially, as this was my first look into spatial data with MS SQL Server, luckily Jackie Ng sits next to me and he found the solution pretty quickly.

Overall, my initial impressions of the Spatial support in SQL Server is that it's pretty basic compared to Oracle which is a much more complete implementation, but I'm glad spatial is finally a standard feature. I am rather surprised that re-projection is not part of the standard feature set.

I really loathe Oracle for it's recalcitrant attitude to Oracle Spatial licensing, but having seen what Sql Server offers, I understand why Oracle still does what it does, as SQL Server is not nearly equivalent.

Of course, IMHO anyone starting out on a new project requiring a spatial database should use PostGIS period.

Coldfusion Performance Consulting

Apart from writing applications from scratch, which I have been lucky enough to do a lot in my career, I also have done a fair bit of application taming.

Performance tuning is a technical art form and something I really enjoy, there's nothing more frustrating as a business owner as paying for some development and then being given a solution which is frustratingly slow.

Coldfusion is great to get started fast, but often this leads poor database design and other problems which can cripple performance.

A good web based application should be able to do most things under a second, post initial load of course. If your's doesn't, generally speaking, the application isn't complete, if your the client, i suggest with holding payment.

That said, it depends on your focus. Admin functionality might simply take longer to run, it's the core of the application I'm talking about.

For example opening the home page, searching for client records, viewing and editing the main record type in your organisation, these should be quick items. If they aren't, your wasting money on staff watching hour glasses.

Anyone needing some consulting in this area, please contact me, I enjoy diagnosing and fixing these problems!

KISS: Avoid Stored Procedures and save money!

I'm not a fan of stored procedures (SPs) for anything except complex data processing generally.

Having just read Writing and Calling Stored Procedures, I felt like explaining my point of view on this stuff. It's sunday after all.

Firstly, I'm developer who considers sql to be mandatory for the developer, rather than having dba's doing all the sql stuff.

That's just an approach which creates an unnecessary bottleneck in the development cycle. I have often seen bad results when developers don't understand the database and it's concepts, or maybe the DBA's didn't understand (or have time to) the application either.

DBA's are there to help with the complex stuff. Use them wisely (and cost effectively!)

This example of using SP's to ends up adding yet another layer to your app and is more complex & longer that the code it intends to replace. Suddenly it's also more work to say add a column to the insert as you have yet another chunk of code to update.

Using stored procedures means you can't easily work with multiple versions of code against the same database (as your logic in SP's is only once per db, not back in the app layer). You lose the ability to easily version your data access layer and hence things tend to stagnate.

If you database is 10 GB, do you really want to have to be running a different instance for each build with changes to the db layer. Doesn't that sounds like a rather expensive approach? It's a recession after all.

Tuning SQL for Coldfusion app with stored procedures, is also more complex in many ways! Personally I love how easy it is to tweak the sql in normal bound CFQUERIES. Copy from debug, tweak in something like TOAD and then update the CF code, easy!

What I love the most about Coldfusion is how clean looking (aside from the verbose cfqueryparam) the database support is when compared to other programming langauges.

Alas the ugly embedded sql in strings syntax of some other languages, almost necessitates SP's

Plus you can't port the app to another (perhaps free database) without rewriting the entire sql layer! Expensive huh?

SQL Server people seem to love (and needs) SP's more than with say Oracle. With Oracle you get the same result and performance just by using simple bound sql. I think the horrible muck that was classic ASP database access really started this trend.

A lot of the common uses of SP's I have seen is what I consider Premature Optimisation

Most of the performance myths about SP's date back to the old days of 100Mhz servers. Servers are cheaper than developers right? Not that I'm advocating something which is going to make your app run slower either (generally speaking).

My Advice, avoid SP's (until there's a clear issue which they can resolve) and enjoy the nice flexibility of bound sql and faster development cycles.