SQL Detective

I saw some full page ads from Conquest Corporation in the latest issue of Oracle Magazine. They were compelling advertisements. So I decided to try and download two of their new products.

Unfortunately, one of the download links did not work. The other one downloaded fine. However after installation, I could not figure out how to work the product. Was not sure what the heck the thing did either.

This exercise was almost a bust. However I was given the opportunity to downloaded Conquest's flagship product. It is SQL Detective. Once again I was unsure from the hype what this tool did exactly. After downloading I found it to be like PL/SQL Developer or Toad.

Here are my first impressions. Logon took a long time. The default layout is not intuitive. You type you SQL statement below the results window. You have to use a window scroller to get more query results (I prefer a button). There were some positive features though. I like having an explain plan tab. You can also click the users node in the tree and figure out who has accounts in your database.

The pricing page on the web was not intuitive either. I determined that the bare bones edition cost $136. For that price, I did not find any SQL Detective features which would make me abandon PL/SQL Developer, which I use for my customer's work. At least Conquest had a compelling enough marketing in their ad to get me to give it a try.

Competition for MySQL

MySQL was previously owned by Sun Microsystems. Then Oracle bought Sun. So now it is managed by Oracle. This is an open source product. However there are other offerings out there. Let look at one or two of them.

The most direct competition from Microsoft would be their SQL Server Express. However that product is not as simple as it sounds. I want to call attention to another product. That is SQLLite.

SQLLite is actually a library. There is no configuration required. You link in a database engine. The contents of the database are stored in a single file.

SQLLite is implemented in C++. The API to the database is low level. This thing was made with C programmers in mind. Good luck if you want to access it through some .NET code.

You should realize that this is a scaled down database. It tries to implement most of the SQL-92 standard. But it is still low power. You don't even want to try to access the database from multiple threads. Things will not go well. I must confess that I am not a MySQL expert. So I cannot as of yet compare SQLLite with it. However I do have a personal project which is outgrowing Oracle Express Edition. MySQL may be my next bet.

Tuning

I have written about Oracle database tuning in the past. How about we see how other players conquer this issue. Let us assume that we have Microsoft technologies on the front and back end. Microsoft provides tier interaction profiling (TIP) to sort out performance issue.

TIP adds code to profile your application. It records timing information. The actions timed are all those that result in a database hit. These include SQL, ODBC, and OLE DB. Those are all Microsoft database access technologies. Developers use frameworks that often abstract them from actual database code. TIP helps get the developer back in touch with the actual database interaction.

Oracle XE and JDBC Performance

I am using an Oracle Express Edition database to prototype a very large data set application. My programming language is Java. I am using JDBC to get to the database.

For starters I thought I needed to download the
JDBC drivers from Oracle. This required me to sign into the Oracle Technology Network. However the login kept saying my password was wrong. It was a pain.

I got the drivers. Now there are several flavors of JDBC drivers. Oracle says you should stay away from the OCI version. Ok. There is also a JDBC driver that bridges to an ODBC driver. This is a slow configuration. I chose a thin JDBC driver. By default it is auto committing each insert statement I make.

So I did a quick timing test. I inserted 10,000 records into a small table. I stored the timestamp for each insert into the table. The whole thing took a total of 19 seconds. That is 500 records per second inserted. That feels pretty good since I a doing a commit after each insert, and this is the free edition of the Oracle database. The only other test I might try is to see whether a PL/SQL script can load the data faster.

Query Tools

I am working on a new project that has a lot of data. So I know I will need some good tools. My database is Oracle XE, which comes with a limited command line tool. I decided to look around to see what other tools I could get.

My current tool of choice is PL/SQL Developer. I checked their web site. A single user license goes for $180. Plus there is a $50 charge to installation media and documentation. There is a 30-day trial period version available. But there is no free option. I passed on it.

Next I tried SQL Developer form Oracle. It is free of charge. The thing requires the Java Development Kit. I already have that. So the downloaded was smaller. It installed fine. However when I queried a bunch of records, only the first 50 were shown. I had to keep sliding down the control on the side of the results window to get more data. Also the default display for the date columns was MM/DD/YYYY. This product was also out.

Finally I tried Toad for Oracle Freeware. It is, as the name implies, totally free. The install states that it has the same functionality as Toad For Oracle Base Edition. I found it odd that I downloaded the installer. After installation, it said that there was a more recent version available. What? Luckily there was a button to get the whole result set in one swoop. Nice. I did not like that the query results did not append a record number to the left of each record. But I can live with that. Toad you are my tool of choice for now.

Choosing an Oracle Version

I've got a hot new side project. There is a list of 170 million URLs I want to scrape from the web. I want to use Oracle to hold my data. What version of the database do I need? My first choice was Oracle Express Edition, also known as Oracle XE. It is free. However it has a limit of 4 Gigabytes of user data. My first database table with 170 million records will blow past that limit.

Next I took a quick look at the different Oracle Standard Edition offerings. They go from $180 to $350 per user, with a 5 user minimum. This cost is not astronomical. But my project is in a research mode right now. I don't want t0 shell out that kind of cash unless I have a money making opportunity. So I will do a prototype with Oracle XE.

Oracle XE comes with a Run SQL Command Line tool. It looks like a DOS box, and behaves like SQL*Plus. That is too bare bones for me. Let's see what kind of tools there are out there that can help me. I am used to PL/SQL Developer myself. However others swear by Toad. And Oracle has SQL Developer. Next time I will let you know my initial experience with these tools for my new project.