Monthly Archives: October 2013

About Enova, and Why I Love Working Here

Yesterday I was on Skype with my ESL teacher for my English pronunciation class, and she asked me whether I am having a good time on my vacation.

-I am not on vacation, I am just working remotely! – I replied.

She was very surprised to hear this:

– Your company allows you to do this? That’s such a good company!

I told her: yes, that’t the best place I’ve  worked so far. And then I thought, that although I am telling people all the time, how much I love working for Enova, I never talked about this in that blog, so now it’s probably a good time for it!

I really love it here due to the number of reason:

– Because the majority of my co-workers are really passionate about their work, so I always feel that we share the same values, and it makes it easier to communicate and do things

– Because our management on all levels is super-flexible, and the only thing which is really important is that the job is done on time (not particular schedule or location)

– Everybody are ready to try new things, to learn from each other, and to learn in general

What really surprised and impressed me soon after I joined Enova, was:

– that it was the first of my workplaces in 17 years, where there were no objections to me going to the conferences, and not even to just “industrial” ones but “CS in general”;

-that it was a first place in 17 years where I could spend some of my work time writing scientific papers;

-that it was a first place in 10 years where I could teach classes to my co-workers;

– and it was the first place ever, where nobody asked me “What’s ACM?” but rather my manager told me: “Expense your membership!”

I’d say – that’s all I could possibly dream about :). And I guess, it’s not the last time I’m going to write about it 🙂

2 Comments

October 29, 2013 · 9:08 am

The Mysteries of NULL values, Part 2

So, if we are talking about the OLTP environment, and we want to generate an efficient SQL statement executed from our application, where the user can choose a random set of search criteria, and any of criteria can be omitted. In this case  the process of SQL generation (no matter, whether we generate it in the stored procedure, db function or in the programming language method) should look like sketched below, For the first iteration we assume that we only have four tables to join, and that a select list contains attributes from all 4 tables, so that none can be omitted

DECLARE v_select TEXT;

v_select_list TEXT;

v_conditions TEXT;

BEGIN

v_select_list:=<select list>

v_select:=v_select_list ||

‘FROM t1

LEFT OUTER JOIN t2 ON t2.id=t1.t2_id

LEFT OUTER JOIN t3 ON t3.id=t1.t3_id

LEFT OUTER JOIN t4 ONt4.id=t1.t4-id

WHERE‘;

/*Now we start to generate a condition. Remember, that any of the lists of values of any of the attributes can be NULL. */

IF v_string1 IS NOT NULL THEN

v_condition:=’attr1 IN(‘||v_string1||’)’;

AND IF; — first condition added

IF v_string2 IS NOT NULL THEN

IF v_condition is NOT NULL THEN – if we already have something in conditions

v_condition:=v_condition|| “AND ‘ –then we need to add ‘AND’

END IF;

V_coundition :=v_condition||

‘attr2 IN (‘||v_string2||’)’;

END IF;

Then you need to repeat a similar sequence of actions for tables t3  and t4.

Yes, it has to be written in such a “non-automated” way, unless you can write something, which will analyze this list of tables more or less “automatically”  in less time than this query generation…

And yes, if some of the tables do not contribute to the select list and at the same time the joins with them are not semi-joins, we can skip the join altogether, if the user did not select any conditions on this table. And yes, that means, that you also need to generate the from-list differently, depending on whether you need all tables in the join, or not… And yes, execution seconds may depend on it…

And at the end you append conditions to the select string:

v_select :=v_select||v_condition

And now happily execute!

Leave a comment

Filed under Data management, SQL

The Mysteries of NULL values, Part 1

On Tuesday I’ve got a question from one of our developers, which sounded like this: Hettie, apparently I am doing something wrong. Is it true, that I can’t include NULL into the list of values? I need to write something like

WHERE customer_id in (12345, NULL)

meaning,  need to select all items, which are assigned to this customer or not assigned at all.

I said: No, you can’t so this. You can’t do this, because NULL is not a value. NULL is  a condition. You can’t compare anything to NULL,  you can just say whether some values IS NULL or IS NOT NULL.

– Now I understand, – said my co-worker.

– Do you want me to help you to right this query?

– You see, the problem is – I need to generate it in my program…

… NULLs are as tricky to understand for developers, probably as much as articles are tricky for foreigners: both seem so easy, if not trivial,  and the rules seem pretty obvious, but the moment you start to actually use them, you have no clue which rule to apply!

Brief summary:

– NULLS are not values, they are conditions

– You can’t compare any value to NULL, unless this particular SQL implementation has some special tools to process it, but in any case it’s not a part of standard SQL

– Each implementation may have it’s a own way of sorting by the column, which have NULL values; Postgres allows explicitly define the sorting order

But where NULLS become especially tricky is when we need to generate a SQL statement!

Generating queries is a problem of it’s own (although, I have to admit, it can be an immense fun!). Usually we choose to generate queries rather then to write a procedure or function with parameter, when we expect the queries to be really different, execution different queries depending on parameters passed. On the other hand, carefully writing a query generating code is such a pain and requires such a tedious debugging, that developers are trying (almost subconsciously) to reduce the number of different cases, which is the opposite of our goal – to write the best query for each case!

I’ve stared this post with one of such examples, where the query should look like

WHERE (customer_id=12345 OR customer_id IS NULL)

or, if there are other conditions we  need to apply, the better SQL will look like this:

SELECT ….

FROM…..

WHERE (…. AND customer_id=12345)

UNION ALL

SELECT ….

FROM…..

WHERE (…. AND customer_id IS NULL)

Substituting OR with UNION ALL almost always gives a huge performance improvement, and I will write a separate post about this and similar tricks!

When we need to generate a SQL statement, we ofter face a way bigger problem. A very common situation involves several drop-down lists, and a user can select from any of them, and any number of lists can be omitted. The very common mistake developers make is to generate a WHERE condition as something like

WHERE attr1 IN ||v_string1||   OR v_string1  IS NULL

AND attr2 IN ||v_string2||  OR v_string2  IS NULL

This will work, but it will work awfully slow! And that’s exactly the case where we need to apply the intelligent SQL generation.

Does anybody have any idea how this condition should be rewritten?  Please reply with your suggestions, and I will show my solution in the next post!

Leave a comment

October 26, 2013 · 1:51 pm

Happy Belated Ada Lovelace Day!

Last Tuesday, October 15 was a very important day, which I missed due to a pure ignorance including but mot limited to not reading the ACM newsletter:)

I am not going to miss this day anymore, so I put it on my Google calendar with two reminders: one 10 days before, and another 2 days before it… hopefully, this will work!

So, what is this day, which I am so upset I’ve missed? It’s Ada Lovelace Day, the day when we celebrate the achievements of women in STEM (Science, Technology, Engineering and Maths).

The article published in Bits reminds us again, that the first computer programmer was a woman, and it also tells why it’s so important to remember this fact.  Here is what the article says:

Women software developers earn 80 percent of what men with the same jobs earn. Just 18 percent of computer science degrees are awarded to women, down from 37 percent in 1985. Fewer than 5 percent of venture-backed tech start-ups are founded by women.

Why does it happen? The author cites recent researches, which indicate that

 girls begin to shy away from computer science when they are young, because of a lack of role models and encouragement from parents and teachers

The story of Ada Lovelace is a great example of an intellectual woman, who was encouraged by her mother to study math and science.  Let’s not focus on the fact,  that the reason for this encouragement was, that she didn’t want her daughter to inherit “insanity” of her father, Lord Byron. So, even in the nineteenth century, when women were routinely considered “half-humans”, she was able to develop an interest in STEM, and to actually study maths and sciences, and perform research. And her researches were recognized and appreciated by many scientists.

This year Ada Lovelace Day was marked by the mass Wiki editing session to recognize women in science.

Now, let me tell you something. Quite often I hear comments like this: why to promote women in any special way? If the goal of the feminists is to establish the real equality of men and women, shouldn’t then we judge their researches just… well, equally? And my answer is: no. Because traditionally, and this “tradition” goes many centuries back, women are under-appreciated, meaning that the majority of people subconsciously have a prejudgement towards women’s ability to produce high-quality research, to work in STEM fields efficiently, and even… hmmm.. think. Think “like a man”… 

So for me recognizing a highlighting women’s achievements in science is something  like “pushing the pendulum in the opposite direction until we reach the real equality.

This been said, let’s celebrate the First Programmer – who happened to be a woman 🙂

 

Leave a comment

October 20, 2013 · 10:52 am

PG Open 2013 – Robert Haas talk

Love this one! (And you can hear me asking him questions :))

2 Comments

October 19, 2013 · 10:22 pm

PG Open 2013 videos are now available – and here is my talk!

Although the PG Open 2013 conference ended almost a month ago, I am still blogging about it – it was a really great event, which I enjoyed a lot (not like I didn’t say it already a several dozen times!). I knew, that all the talks at the conference were recorded, and yesterday the videos were published on Youtube. Today Chetana informed me ,that I can have a pleasure of viewing myself …. Well… you know, it’s often embarrassing to watch yourself on the video, seeing clearly what did you do wrong:). I know it’s supposed to be educational, you are supposed to watch it, to analyze you mistakes and to correct them next time you will deliver the same (or similar) talk. However… well, it’s still embarrassing… Nevertheless, I am embedding my presentation here, because,  with all it’s faults, I consider it to be the most detailed description of my current research field, and what exactly I am doing, and why it’s important…  Comments and questions are very welcomed!

5 Comments

October 19, 2013 · 10:27 am

Enova Tech Talk in UC

On Thursday October 10 I was giving a talk at the University of Chicago.

It was very exciting since I realized that this was the first time I was talking to the students in the US. All the other times I was teaching, my audience were “adults”, so I’ve realized I really missed talking to young people. May be that was the reason why I’ve gathered a crowd at ADBIS 2013 in Genoa!

In any case, for those of you who never been to the University of Chicago – it’s a really beautiful campus, but it takes a while to get there from Chicago Downtown, so the name is misleading 🙂

I have been there twice before, but really long time ago, and I didn’t remember much about what is where and how to get from one place to another. Besides, the campus is so big, that you might know one part of it, and do not know other parts.

A couple of pictures from the campus:


Continue reading

Leave a comment

October 12, 2013 · 10:01 pm