Pythian Group

Persistence Smoothie: Blending NoSQL and SQL – see user feedback and comments at http://joind.in/talk/view/1332.

Michael Bleigh from Intridea, high-end Ruby and Ruby on Rails consultants, build apps from start to finish, making it scalable. He’s written a lot of stuff, available at http://github.com/intridea. @mbleigh on twitter

NoSQL is a new way to think about persistence. Most NoSQL systems are not ACID compliant (Atomicity, Consistency, Isolation, Durability).

Generally, most NoSQL systems have:
Read the rest of this entry . . .

I’ve recently learned that Chris Date is giving a three days seminar on January 26–28, 2010 in Dallas, TX, organized by Method-R. It must be one of the unique opportunities to learn from the world renown expert in relational database theory.

The seminar title is “How to Write Correct SQL and Know It: A Relational Approach to SQL“. It’s focused on writing reliable SQL. While SQL has been designed as a simple access interface to relational data, it turned out to be quite complex and requires your to follow a certain disciplines to produce truly reliable SQL code — relational discipline.

From my experience, I find that many database problems (and that’s probably underestimation) could be avoided with better design and better SQL so I’m genuinely interested to have people do that better and better and better — managing well written applications is so much more fun!

See the course outline for all the glory details. The pricing is very fair and $600 per day is a bargain if you ask me.

Using OLE DB to get SQL Server to connect to Oracle servers can be done quite easily, but there are a few little tricks you should know to make it go smoothly. Once it’s working it seems to work quite well. I hope this blog post will save you a few headaches.

Recently a client asked me to create a simple SSIS package that would connect to Oracle, pick up some data with queries they provided, import it to SQL Server, and eventually export the data as flat, delimited text files.

With SSIS you can use the OLE DB provider that Oracle provides. If your SQL Server is 32-bit, you can install the 32-bit Oracle client and stop there.

If it’s 64-bit, there are a couple different ways to get the Oracle providers working. Read the rest of this entry . . .

I love puzzles. So when I heard about the NoCoug SQL Challenge I felt tempted to give it a go.

The Northern California Oracle Users Group (NoCoug) has challenged us to find a good way to calculate the probability of getting different sums for x throws of a n-sided die using only SQL. The probabilities for the faces of a single die are stored in a table and that’s all you need to start playing with the problem. The SQL Challenge rules can be found on the NoCoug website, along with some other relevant information.

After working out my very first solution, I read the rules and found it wasn’t fit for the challenge, as it used non-SQL extensions (SQL*Plus). So I started again, this time using pure SQL. I came up with a few options but wasn’t happy with them from a performance perspective. They needed more sweating.

I find that walking is very good for thinking. Whenever I can, and when weather permits, I walk home from work at the end of the day. The distance between work and home is about 6 km, which takes me around one hour to cover. After you’ve done it a few times the walking becomes automatic and  you don’t have to think about it anymore; obstacles, kerbs, corners, street-crossings are all handled in auto-pilot mode. Then, as you don’t have anything else to do, you think.

During the days I was working on the challenge solution, the SQL query used to occupy my thoughts along most of my (almost) daily walk. I decided to use only standard SQL features and wanted to be able to run the solution on both Oracle and SQL Server. While walking, I started thinking on ways to improve my initial solution’s performance without having to resort to non-standard tricks.

Read the rest of this entry . . .

Over the last few weeks I’ve been looking at several customers’ slow query logs, and I found in many of them an odd type of query. These are SELECT statements that contain an IN clause that includes dozens, sometimes hundreds of values. These statements often end in the slow query log. I’m not sure if these queries are this way by design or if they are generated by a specific database development tool.

I did some tests in one of my own databases, one with only around 10K rows in its largest table. The database corresponds to the Amarok media player. For example, I queried for songs by B. B. King (spelled “BB King”, “B.B. King”, etc. or with other artists: “B. B. King & Eric Clapton”).

The first query used a JOIN and an IN clause with all the spellings in my db; the second used the same JOIN and WHERE ... name LIKE "BB%" OR name LIKE "B.%". Both had the same execution plan, and both retrieved the same number of results. In MySQL version 4.1 there were some enhancements to the optimizer for treating these massive IN clauses, which means that for smaller databases, this is expected.

With bigger databases and more complex queries, things are different. Read the rest of this entry . . .

Hello everyone,

I think this will be a great log buffer.

Dave has been sick these past two days and as a result, we do not have a comprehensive log buffer ready the way we or a volunteer usually do. This was bound to happen to log buffer at some point and today it has happened.

So I had two choices – cancel this week’s log buffer, or try to make it great despite this adversity. Never one to accept defeat easily, I’ll go for the second option. At least if this is the lamest log buffer ever it won’t be because I didn’t try something new that had a shot at a good result.

So this week’s log buffer is as follows: If you came here to read interesting content from the database community, you probably spend some time each day learning and reading about things we would all find interesting. We are counting on each and every one of you, our faithful readers, to propose the one article you read in the last week (and preferably was written in the last week but I can’t exactly be choosy at this point can I?), and include a short paragraph as to why this article was interesting to you and why it should interest us. Do this in the comments to this post; the akismet spamfilter will tend to eat comments with URLs so please include the magic word “contribution” somewhere in your comment so I can go dig them out for approval.

Feel free to link to your own blog posts if you are proud of them. It is a carnival of the vanities, after all.

A typical log buffer gets over a thousand reads over the week it is active. I am going to call this experiment a success if there are at least 25 interesting links written by the community posted below! And if there are not, well it was worth a try and much better than saying it was canceled.

And so this will be the first log buffer truly written by the readership – join us to make it a good one!

Thanks

Paul
P.S.

Get well soon, Dave