Friday 18 July 2008

An interesting query for potential optimisation...?

Just wanted to capture a query I was working on yesterday which was slightly 'interesting' in that it consists of a couple of layers of left outer joins and the joining with a derived aggregation table - and time permitting I'd like to revisit it because I'm sure it can be improved / optimised (but it is functionally correct).


select
t1.tid,
t1.alias as alias,
t1.asset_label as asset_label,
t1.terminal_id as terminal_id,
t1.location_address as terminal_address,
t1.last_updated as last_connection,
t1.name as questionnaire_name,
t1.questionnaire_id as questionnaire_id,
t1.state as state,
t1.value as publishing_state,
response_stats.rcount as rcount,
response_stats.last_response as last_response
from
(select
t.id as tid,
t.alias,
t.asset_label,
t.terminal_id,
t.location_address,
t.last_updated,
qnr.name,
qnr.id as questionnaire_id,
qnr.status as state,
at.value
from terminal t
left outer join
(delivery_stats d,
terminal_activity ta,
questionnaire qnr,
activity_type at)
on
d.terminal_id = t.id
and d.terminal_activity_id = ta.id
and ta.questionnaire_id = qnr.id
and ta.activity_type = at.id
and qnr.status = "L"
where
t.id in(1, 20, 21, 22, 23)) as t1
left outer join
(select
tcq.terminal_id tid,
tcq.questionnaire_id qid,
count(*) rcount,
max(uploaded_date) as last_response
from
tcquestionnaire tcq
group by
tcq.terminal_id, tcq.questionnaire_id) as response_stats
on
t1.tid = response_stats.tid
and t1.questionnaire_id = response_stats.qid;


Whilst technically correct - I'm not entirely satisfied of the need for the the top level left outer join which creates a full derived table rather than having a correlated sub-query / nested join that contains only the rows actually relevant to the result set. Something to think about perhaps, on a rainy (very rainy!?!) day :)

.

Wednesday 16 July 2008

Update vmware server 2.0 from beta 2 to rc1

Just some quick notes on this "upgrade" from beta 2 to rc1 - which is more of a reinstall!

Note: pre-release versions forcefully expire (becomes rather apparent when starting a vmware instance) - but the error message is not that clear - "Failed to start, General error" may appear in the logs as the error but is preceded by a more subtle "version expired, there's a new version out" type message - which is the actual problem.

Basic steps:

Stop/suspend all vm's

Stop services
#service vmware stop

[Recommended - move VMs (for safety/paranoia!), simply mv the whole vmware directory]
[Recommended - good time to check system is up-to-date]

Remove/uninstall existing version (based on 2.0 beta2):
#rpm -e VMware-server-e.x.p-84186.x86_64

install rc1
#rpm -ivh VMware-server-2.0.0-101586.x86_64.rpm
or
#rpm -Uvh VMware-server-2.0.0-101586.x86_64.rpm

configure as per normal
#vmware-config.pl

check running via web management console

service vmware stop

move vmware images back

start services again

Note, VM images have to be reloaded into the VM inventory - apart from that everything is entirely straightforward!

Alternative approach from tarball:

#tar xvfz VMware-server-*.tar.gz

#vmware-server-distrib

#vmware-install.pl


Some relevant links:

http://communities.vmware.com/thread/154503?tstart=0
http://communities.vmware.com/thread/154668?tstart=0
http://communities.vmware.com/message/901195;jsessionid=77A29EE9DF1ADEBF37027EB347D638F3
http://www.cyberciti.biz/tips/vmware-on-centos5-rhel5-64-bit-version.html
http://kirkpbm.wordpress.com/category/vmware-server/
http://communities.vmware.com/docs/DOC-4111
http://communities.vmware.com/thread/143674

.

Saturday 12 July 2008

OpenSolaris - upgrade to b93

More notes on OpenSolaris image-update, to b93 (in my case from b91)

See this thread http://opensolaris.org/jive/thread.jspa?messageID=258127&#258127 discussing the problem and it's solution.

Performing the standard image-update failed with an error of the form:

> pkg: attempt to mount opensolaris-3 failed.
> pkg: image-update cannot be done on live image

The problem is caused by:

2387 libbe.so:beCopy() frees nvlist variables before using them
http://defect.opensolaris.org/bz/show_bug.cgi?id=2387

So, time to find a work around. This wasn't as seamless as perhaps it should have been, this is how I did it:

#beadm list

> my active BE was opensolaris-3, which I was booted in

#export PKG_CLIENT_TIMEOUT=2000

#pfexec pkg refresh

create a temp BE
#pfexec beadm create opensolaris-4

mount it as /mnt
#pfexec mount -F zfs rpool/ROOT/opensolaris-4 /mnt

update it
#pfexec pkg -R /mnt image-update

#pfexec beadm unmount opensolaris-4

#pfexec beadm activate opensolaris-4

reboot into BE opensolaris-4 build 93

Note: b92 was skipped (not released)!

Also, this failed _every time_ when attempting it from desktop terminal (graphical login) - but worked as soon as I did the same thing from an ssh terminal!

Blog for installing / configuring mysql as service using gui http://weblogs.java.net/blog/bleonard/archive/2008/06/opensolaris_200_3.html


Additional notes:

BE_PRINT_ERR=true can be used to output diagnostic error logs from the be process,

e.g.

BE_PRINT_ERR=true beadm create test


.

Thursday 10 July 2008

C# delegates

A quick note on c# delegate functions:

I found the standard way to define delegates a little clumsy and verbose in some situations, for example when wanting to pass a function to a .Invoke method of a control to add it to its thread work queue. In this situation the delegate's purpose is really just to pass the method to the invoker, there is no other use of the delegate so declaring it at class level is a much wider scope that I would like. What I wanted is a more terse, compact and locally scoped way to achieve the same thing, here it is:

The simple way:

Declare delegate within class, e.g.

public delegate void updateStockView(Stock[] stocks);

use to create a new delegate instance to pass to invoke on a control, e.g.

instrumentDgv1.Invoke(new updateStockView(this.updateStockView), new object[] {instrumentResponse.Stocks});

This approach, whilst perfectly valid feels a little cumbersome. Firstly the delegate is at an uneccessarily high level of scope, secondly, the statment requires a new instance of the delegate to be created, passing the delegate's parameters down as an object array.

A more compact form:

instrumentDgv1.Invoke((MethodInvoker)delegate { this.updateStockView(instrumentResponse.Stocks); });

Here we see, no unnecessary declaration of the delegate type, no creation of the delegate instance, no passing of parameters as an object array - all round much tighter syntax for this type of use.

The key thing to make this work is the MethodInvoker cast. MethodInvoker is from the System.Windows.Forms namespace and is used to cast the existing method to the delegate type without requiring the declaration/instantiation of a new delegate.

.

Monday 7 July 2008

Netbeans 6.1 Rest JSR-311 RI library problem

Found a minor problem with Netbeans 6.1 and Rest (Jersey RI) today, thought I'd capture the problem and solution.

Symptom:
Can't launch a Rest application using Jersey (JSR-311 RI) libraries - error given is:

HTTP status 500

exception

javax.servlet.ServletException: java.lang.NoSuchMethodError: javax.servlet.jsp.JspFactory.getJspApplicationContext(Ljavax/servlet/ServletContext;)Ljavax/servlet/jsp/JspApplicationContext;
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:274)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

Because the Jersey (JSR-311 RI) libraries include jsp 2.0 jsp-api-2.0-20040521.jar from Netbeans6.1\enterprise5\modules\ext\rest\ which is an older version of the JSP API.

Fix, copy a newer jsp in or remove the entry from the library definition and add a newer jsp library in.


Example Solution:

Go to tools\libraries
Select the Jersey libary
In the Library classpath, remove the offending jsp-api-2.0-20040521.jar
Now add the newer JSP libary, e.g. Netbeans 6.1 ships with servlet2.5-jsp2.1-api.jar one level up in the Netbeans6.1\enterprise5\modules\ext\ directory.
Remove the Jersey libraries from your project (if already added).
Re-add the Jersey libraries to your project and recompile - problem solved.