Monthly Archives: December 2013

Lean UX: Book review

Some time ago our squad manager recommended to all of us to read a book Lean UX: Applying Lean Principles to Improve User Experience. Not only he recommended this book, but he actually delivered a copy to each of us! So, with all my you-know-how-I-am-about-paper-books – I had to read it! And now I would encourage everybody to take a look at this book (it’s really short , too:))

I  an not going to try to retell it in my blog; as I’ve mentioned, it’s probably easier and faster to read it. But I want to emphasis a couple of things, which I really liked about this book, and to share some of my thoughts in connection with it.

Here are some principles of SO development, mentioned in the book, which I find really important:

Seeking constant feedback from the user, prototyping, showing the results, making changes, showing results again. I always believed, that this is the most important part of the SO development – to find out, what the users really want. And sometimes they are not even sure, what they want, and you need to ask right questions, to make them to realize, what they really want.  And I still remember, how miserable I was on one of my previous jobs, when I was not allowed to talk to the end users, “because they are stupid, and do not know what they want”…

Not striving for the perfect solution “on the first try”, but rather approximating, and changing design based on the user’s feedback. This is a very sensitive subject, because, as it was noted decades ago in the classic book by Weinberg – “The Psychology of Computer Programming”, programmers tend to view the critiques of their programs very personally :).

“No heroes” approach. This is a very important concept, and I wish more people would realize it. Quite often people believe, that there is a limited number of “heroes”, who know, how to program “right”, and who can save the world. And that only geniuses make a difference in the world of SO development. And do not take me wrong, there is nothing bad in being a hero:). The problems begin, as the authors of the book point out correctly, when the “heroes” can’t work well in the team and when they can’t share their knowledge and techniques.

The question I’ve being asking myself while reading this book is. how all these principles can be applied to the database development. Through the whole book, although the authors talk about cross-functional team and about the fact that everybody should participate in the design sessions and other discussions, they do not really mention database developers. Meanwhile, you can hardly imagine any application these days, which would not interact with some database. And when we have a database, we immediately have to take into account, that database objects 1) are “more permanent objects” 2) most likely are used by different applications 3) are harder to modify, and their modification requires coordination between different applications.

Very often the database developers are convinced, that “this is not for us”. And yes, theoretically we would love to be able to perform more extensive analysis of all our data needs before we start prototyping, we would love not to rush – but we can’t! What we as professionals need to realize is that either we will become fast and flexible, or the application developers will proceed without us, creating such monsters as our “default rules hash”, when a big chunk of data is stored … in a Ruby model! I still firmly believe that this design disaster happened because n application developer got really tired of fruitless attempts to get a db dev review approving the changes he needed!

So – how database development can be Lean? How it can be Agile? The key is to use more database functions along with packages and procedures, when available. They can be modified as fast as an application code. In many cases no further database objects changes will be requires, in other cases we can change database objects later, after the “database interface” is changed. And what is more important – they can be changed independently.

And I am going to write more about this:)

Leave a comment

Filed under books, Systems, Team and teamwork

The misconceptions of nested loops

When I just started to work on queries optimization, I had the same misconceptions, as many people have – that having nested loops in the execution plan is good. In reality for this to be true, two conditions should be met: 1) this should be a short query 2) the nested loop should utilize unique key and/or primary-foreign key constraints.

For long queries (reminder: a query is considered long, when most of the records from the table(s) contribute to the result) the use of full scan (and consequently the hash join algorithm) can be more beneficial. Why? Because when the records are retrieved using an index, two reads are required – one to read the index block, and another – to read the actual data block. The order of index blocks often (not necessarily, but really often) does not match the order of the table blocks, so consecutive access by index may result in random access to the actual data. Now imaging, what will happen, if we need “almost all” records from the table: we will execute twice more reads, than we could, should be just ignore the index!

When I was reviewing my last class Power Point (SQL for Advanced Analytics) I’ve realized, that all example I had for the “Art of full scan” section were still coming from my work in the City of Chicago, from a very old version of Oracle Applications SUite. I had to come up with “in-house examples”, and I was worrying, that I won’t be able to find ones.

You know why? Because our database servers have huge main memory, and even big tables cab be read into it all at once, thereby making nested loops fast even when they should not :). So I was really happy when I started to reconstruct one City of Chicago Example in our environment, and it worked exactly how I wanted!

Now, let’s proceed with an example.

Than is how you calculate “sum accounts” for an individual loan:

SELECT
vl.value AS account,
SUM(CASE vl.value
WHEN pt.debit_account_cd THEN pt.amount ELSE 0 END)
– SUM(CASE vl.value WHEN pt.credit_account_cd THEN pt.amount ELSE 0 END) AS amount
FROM payment_transactions pt
INNER JOIN valuelists vl ON vl.type_cd = ‘transaction_account’ WHERE loan_id=30501044
GROUP BY vl.value

The output will look somewhat like this:
Continue reading

Leave a comment

Filed under Data management, SQL, talks

Too Few Women in Tech Jobs – Stil…

Many years ago, when I was a consultant at Spehrion, I worked on multiple assignments in the City of Chicago BIS. One of many projects I was involved in, was developing EEOC reports. The EEOC stand for Equal Employment Opportunities Commission; all government agencies are required to submit to this commission a number of reports, which show the differences in the number of employees, average salaries, number of promotions and such, based on gender and ethnicity (and possibly other characteristics).

The actual results of these reports looks really alarming! When you just think about people you know, you may think: OK, I know quite a bit of women, who are executives, or at least managers, they are not discriminated based on their gender. I know a lot of female professionals, which are respected for their knowledge and expertise, and why in the world you say that women are treated unequally?

But they actually are! Even in the City of Chicago Mayor’s office, where they paid special attention to gender and racial equality, the average numbers where severely disproportional! You could tell, that women and minorities where actually paid less in the same position! When I tell people about my experience with the EEOC reports, they tend to disbelieve. So – did things changed in recent years?

This article, titled TOO FEW WOMEN IN FED TECH JOBS; TOO MANY CHALLENGES FOR THEM  discusses the most recent report, issued by Women’s Work Group of EEOC. This report shows, that women are still getting less STEM degrees, than men, but even when they have a degree, it’s still harder for them ti fund employment.

Yet even for women who hold STEM-related degrees, many still encounter challenges in being hired, promoted and supported than their male counterparts. The lower percentage of women with STEM-related degrees makes it more difficult for agencies to recruit qualified women to STEM positions, and for those who are employed, many experience isolation, a lack of support and mentorship, and hostility, EEOC found

the article says.

So – there is still a lot to do to promote gender equality in the STEM field, to liberate women’s minds, to reassure them they can do great things!

Leave a comment

Filed under People

My paper got accepted!

After not posting anything serious for over a week and with two posts “in work”, instead of actually posting something smart, I am shamelessly bragging: My paper got accepted for EDBT/ICDT 2014 Industrial track! I am so-so-so excited!

And I will see Greece for the first time in my life!

The best Christmas present ever:)!

4 Comments

Filed under talks

About innovation and teamwork

Last week I was nominated for the “Ennovator 2013” (Enova + innovator) award. While I was not a winner, I was really happy for both the fact that I was nominated and that I made it to the short list of three finalists.

I realize that my work is mostly “invisible”, even business people who work very closely with our team, often care only about new features, not about why it works, and why in works fast.  And I would say, this is perfectly normal, because that means that we deliver what we promise, and that our customers expect us to deliver a high-quality applications.  And in comparison with other application developers the work of database developer is even less visible – I do not produce cool interfaces, “flashing cards”  and other “visible” things. I do not created new simpler scenarions for the application user… I am an  actual back end!

That’s why I was so happy to find out, that I was nominated – it meant, that people actually see, what I am doing :).

But there is something else about innovations, which is way more important. When my manager learned, that I nominated him, he said – why? I didn’t do anything. And I told him, that I won’t be able to accomplish anything without his innovative management. The consistency, with which he utilizes Agile technology earned our team a trust from business  – business knows, that we deliver what we promise, and thereby trust our judgement.

So, the other side of the innovation on the back and is, that I won’t be able to accomplish anything without support of my teammates and other coworkers, and this support has being tremendous! I can easily name a couple of dozen of people, who helped me way beyond there immediate job responsibilities. Not only application and UI developers, but also our wonderful QA, and DBA team, and managers of other squads, and other application and database developers, who absolutely did not have to help me – but they did. Not only they helped me by reviewing my code, testing my functions, gathering  execution statistics, but they also helped me, when they showed me their support. When I knew, they understand, what I am trying to accomplish, when they were telling me: do it!

So – thank you!

 

Leave a comment

Filed under People, Team and teamwork

Teaching New Class Next Week

On dec 16-17 I will be teaching a new class at Enova – Database Optimization for Advanced Analytics. This class includes SQL basics: relational models, relational operation, join algorithms and data retrieval. and also short queries optimization and long queries optimization. I did teach short queries optimization multiple times for the past two years, so by now almost all examples I have are our native Enova examples. But I never tough the long queries at Enova.

Originally I’ve estimated it will take me about 4 hours to rework this portion of the class, but it turned out, it takes way longer! I’ve also realized, that I am expected to squeeze virtually everything I know  into 4 hours! Which required rewriting of everything, even the parts I already tough at Enova!

Finding the right examples of long queries was (and is) rather difficult, since I didn’t really work with AA team yet. I had and old (and very good) example with run_balances view from Oracle Applications (back from the City of Chicago days), and by the end of the day today I’ve managed to construct a similar example using our local material.

I was not sure, whether I will be able to find a case, where Hash Join will be more efficient, than Nested Loops – our servers have so much memory, that Nested Loops often work “against all odds”:), but I finally found one!

We’ll see, how it will go next week. I am still far from being completely ready, and I will need to rehearse my timing, but I am more confident now, than in this morning 🙂

Leave a comment

Filed under SQL, talks

What are the short and long queries, and how to turn a long query into a short one…

You probably know, what is a long query and what is a short query, but just to make sure we all are on the same page, here is a precise definition: a query is considered short, when the results can be obtained using a small portion of all records from the table(s), for example, to find a mailing address of one specific customer. And as you can guess, the query is considered long, if you need to read “almost all” data to obtain the result, for example, to find a total of all sales for the previous quarter.

Note, that if a query returns a very little number of records, it does not automatically mean, that this is a short query. Actually, it may return just one number, like this total sales for a quarter, but in order to obtain this number, you need to read all records for the quarter, so this is definitely a “long” query.

Why we need to know, whether a query is a “short” or a “long” one? Because generally speaking we need to utilize different optimization techniques in these two cases, and (generally) we pursue different optimization goals.

For short queries our goal is to truncated “almost all” records by means of selecting the most selective semi-join (BTW, do you know, what “semi-join is?), while for long queries the goal is to ready “almost everything” the fastest possible way.

And now – here is a story about how to turn a long query into a short query.

Last week there was a batch job timing out, and when we talk about batches we almost instantaneously think about “long” queries. So when the question popped up in the mailing list with a comment that this query was running for hours, I assumed it is a “long query”. Recently I almost never respond to the optimization questions in the mailing list in a hopeless attempt to concentrate on just one thing, which is the most important at that time. But the day the question was posted I was too upset and frustrated with one issue which didn’t go as planned, so I had to do something satisfying :).

The query included a join of eight tables, and the  database developer who posted the question mentioned, that he knows which join should be executed first, but he is not sure how to make Postgres to choose a specific join order. I’ve checked the selectivity of different selection criteria, and he was right – the  set of joins he wanted to execute first, would indeed limit the output to 1300 records, which is nothing! I’ve suggested he just execute this join as a sub-select (or inline view) , and since the number of records returned was so small, it didn’t really matter, how the rest of the joins would be executed! He first tried to resist:), saying that the problem is in other joins, but finally agreed to give it a try.

Turned out, that the total execution time of the whole batch query was reduced to the execution time of this sub-select, which was just 19 seconds! Seconds, not minutes!

So, it turned out, that what we originally thought to be a “long” query, was actually a “short” query, and the problem was solved by finding a correct semi-join. Should we figure this out earlier, we could probably solve it faster!

The moral of the story – check the query(es) selectivity!

Leave a comment

Filed under SQL