Baby Steps

A friend of mine had some trouble transitioning from her community college to a four year one. Right now she is in limbo. Looks like she is reading books and playing video games. That is fun for a while. But I think she should be working on personal development projects.

I decided to work on some side projects with my friend to motivate her to start writing code again. She had an idea for a fiction database. She tried to do it once. But her queries were not working and she gave up. That is so not like her.

My first idea was for us to produce a minimum viable product (MVP) for her fiction database. I helped trim the specs down to a bare bones app. I cooked up a simple single database table to hold the data. Then I reviewed the SQL needed to get records of interest from that table. That's when I saw my friend had some trouble constructing the WHERE clause.

With the SQL confusion resolved, my friend banged out the app. I was happy. We decided to write the app in PHP and use MySQL on the back end. Next step is to modify the app to work with her more complicated database schema. We have the basics down. Moving forward should be easy.

ODP.NET

Just read a article that describes how to use the latest Oracle Data Provider for .NET, also known as "ODP.NET". You must know that there are type types of this provider. One is the managed driver. The other is the unmanaged one. I was interested in the unmanaged driver.

You need to have, at a minimum, Visual Studio 2010. At work I run with Visual Studio 2008. But at home I use Visual Studio 2010 for school. So I am good to go to try things out. Apparently you also need to download Oracle Developer Tools for Visual Studio, also know as ODT.

The example I read had me setting up my Visual Studio project to add a reference to Oracle DataAccess. Then in my code I would add using statements to Oracle.DataAccess.Client and Types. After that I should be good to go to use the driver.

There is some bolierplate for the data access code I write. Create an OracleConnection. Set the ConnectionString. Call the CreateCommand. Set CommandText to my SQL. Then ExecuteReader and Read. Simple right?

Performance Tradeoff

I remember the first huge PL/SQL stored procedure I wrote. The thing was highly modular. It came out to maybe 10k lines of code. I liked that no procedure or function was too long. I broke things down into small pieces. Did a lot of testing to ensure the functionality was correct. Then I shipped it to a production environment.

 

Immediately I got complaints that my stored procedure took too long to complete. The thing might have been running for almost an hour. Yeah. The data I was processing was on the order of millions of rows. I had to do a lot of crunching to determine the outputs I had to produce. Nobody told me about the performance requirements.

 

I received some help from some performance tuners. They had some tricks to make my jobs run in parallel. They rewrote some of my long running queries. Some problems such as many SQL statements to ensure modularity could not be fixed without a rewrite. Ouch. In the end, I think the average time of my runs was brought down to 20 minutes. It was acceptable but not great.

 

Now I try to produce code that runs fast. From the get go, I code in a way that does not duplicate SQL. I try to get everything done in minimal large SQL select statements. However there comes a time when I might need to duplicate a SQL query to ensure the code base is maintainable. It is a delicate balance.

Errors in Report

A tester informed me with a number of problems with one of the reports out code produces. One specific problem was that the title of the report indicated there were errors. The report itself was not that big. The SQL was a bit complex.

I asked for some logs from the server. When I poured through them, I found references to an ORA-14551. That error indicates some code is trying to do some DML in the middle of my SELECT statement. I did not see any explicit UPDATE/INSERT/DELETE statements.

Then I went to the end of the procedures called by the report. Turns out the exception handler tried to add some records to the database. Ooops. You can't do that in the middle of a SELECT statement. I think I could just push that error handling down to the report, out of the stored procedure. Problem solved.

Unique Joins

I was wrapping up the analysis on a problem the customer had reported. I needed to do one last check for a certain set of records. So I joined two tables. I tried to exclude records that had duplicates in one of the tables. That should be easy, right? I did a GROUP BY, and ensured HAVING(COUNT(*) = 1).

Turns out I kept getting false positives in my query results. I could not explain it. I tried rewriting the query using an inner query. This still resulted in false positives. This was really annoying. It was the last query I needed to perform to wrap up my high priority analysis.

I told one of the leads that I was having trouble with this final SQL.He told me he could assist. I said come on and help me figure this out. We joined a conference call. The lead informed us that he was a SQL guru. I explained what I was trying to do. He rewrote the query too. But there was one problem. He could not get his query to compile.

Around an hour later, I told everyone I needed to go. My lead was still trying to get permutations of the SQL to compile. We were getting nowhere. I would have appreciated a little help here. I was not going to get it. In the end, the lead assigned the task to the database lead, who figured it out in no time.

What is the moral of the story? Beware of self professed SQL gurus.

Testing Concurrency

We released some big new functionality this past year in our system. There was a bunch of testing that was done. Some scenarios were difficult to accurately test. Developmewnt and testers must have skipped over some of those tricky areas. The customer found that certain combinations result in the application aborting. Not good.

I spied on the data in our production database. Saw the malformed records. Traced it back to our new development for the year. Even the comments in the code were wrong. Must have added to the confusion. I used the debugger to step through the code to get the records in a bad state. Then I made a code change, stepped through the code once more, and was convinced the fix was good.

Testers cannot run the code in debug mode. They use the release version. I told them it would be difficult to emulate the concurrency needed to see the problem. They gave it a try. A manager asked if there was anything we could do to help. His idea was to write a trigger on the table in question. We could then make a call to the Sleep procedure in the DBMS_LOCKS package. With things slowed down, he figured the testers could have a good chance to debug the problem. Somehow this did not feel right. But hey. It was worth a try.

The SQL Job Interview

Jitbit Software recently shared the SQL questions that ask potential employees at interviews. They give you a small schema and ask you to write the SQL queries to achieve a few goals. I looked through the questions. Some were easy. Others had me sweating. I bet I could hack through most of them. But if I was under pressure in an interview, I don't think I could solve them all. Oh oh.

It seems this test is to weed out developers who do database programming, but just click buttons on an ORM tool. You need more than that kind of knowledge if you want to tune SQL statements. Personally I don't want to tune SQL. There are specialists for that. I like writing apps that serve business needs. Yeah I try to make sure my SQL works fast enough to not be a problem. I even submit my queries to DBA peer review just in case I overlook anything.

Maybe it is time to brush up on the old plain SQL skills. The first step should be to install a free Oracle database on the computer I use the most. Used to have one on there. Then I got a new laptop, and haven't got around to doing this chore. The time is now. My SQL skills may depend on it. By the way, I don't ever use an ORM myself. I roll all my own SQL. Turns out that my SQL statements are usually quite basic indeed.