My roommate found a neat site today, http://highscalability.com/, which gives a (unfortunately non-comprehensive) overview of a lot of successful websites and how they coped with growing hordes of traffic. Its way neat. 🙂

I’ve been really really busy lately working on my latest upgrades to OWS. The problem was that after putting in a few million rows into the database, queries took FOREVER. So.. I got myself a few books/articles about OLAP and star schemas and I think I’ve devised a high performance way of storing/retrieving the data. Its not done yet, but once it is I’ll probably write a series of blog posts about it — its some really neat stuff.

But really, what I wanted to mention today is the silliness of a lot of enterprise-level so called “Web Applications”. Prominent examples I can think of that we use/maintain at my workplace are things like Blackboard WebCT, Sungard Banner, and Kronos Workforce Central.

These silly applications call themselves “Web Apps”, but when you go to use them on a brand new computer, they all have the same problem — you need a runtime of some kind to be installed on the computer, or applets, or some other such nonsense. Which really, thats fine and all, but I hate that they call themselves web apps, since they’re just really ‘fat’ applications that happen to run from a web browser. If you need to install something other than a web browser, its NOT a web application anymore (at least, in my opinion). Of course, don’t get me started on the complexity of installing some of the Oracle ‘web’ stuff that some of our people use…. gah.

You know, its funny, most of the capabilities that these applications implement can easily be done using AJAX and javascript technologies — look at google maps, or any one of a number of google products. Or OWS. All of these products act like an application on a desktop, except that they work in the web browser with no strings attached, and will work on a Windows PC or a Mac or Linux… or even a Wii.

These other apps — its not a web application if it only runs on Internet Explorer on Windows (granted, some have firefox plugins too… or use Java, but thats a whole other realm right there ) . At least, not in my book.

Haha… I was browsing the Facebook developer site and they linked to Microsofts website in relation to MS’s popfly application. Apparently it has support for Facebook application development or some such thing. Well if you take one good look at the screenshot they were using…

Recognize those tabs? Its Firefox! Apparently Microsoft likes Firefox too. 😀 Of course, we probably already knew that, but this is further proof!

Link: http://msdn.microsoft.com/vstudio/express/showcase/

Image Link: http://msdn.microsoft.com/vstudio/express/images/facebook/popfly.jpg

Ok, so while working on Obsessive Website Statistics (OWS), I’ve hit a situation. See, OWS tries to be semi-intelligent and combine the parameters of all the installed plugins into really giant/nasty SQL queries that make you shudder, but tend to work.

So right now, I’m trying to select the following (at the same time):

• All pages, grouped
• COUNT() of all hosts
• COUNT() of all pages that end with .html, .htm, .php, /
• COUNT() of all pages
• SUM() of filesizes

So of course, getting the 1st, 2nd, 4th, and 5th items is pretty easy. However, the third item is getting annoying. I tried using a subquery, but considering the table is 100,000 rows this is a particularly slow query:

SELECT 
	virtualroadside_com.request_str,
	COUNT(DISTINCT virtualroadside_com.host),
	c.b,
	COUNT(virtualroadside_com.filename),
	SUM(virtualroadside_com.bytes) 
FROM 
	(	
		SELECT 
			virtualroadside_com.request_str AS a,
			COUNT(virtualroadside_com.id) AS b 
		FROM 
			virtualroadside_com 
		WHERE 
			(virtualroadside_com.filename LIKE '%.html' 
			OR virtualroadside_com.filename LIKE '%/' 
			OR virtualroadside_com.filename LIKE '%.htm' 
			OR virtualroadside_com.filename LIKE '%.php') 
		GROUP BY virtualroadside_com.request_str 
		ORDER BY virtualroadside_com.request_str DESC 
		LIMIT 0,100
	) c,
	virtualroadside_com 
WHERE 
	virtualroadside_com.request_str = c.a
GROUP BY virtualroadside_com.request_str 
ORDER BY virtualroadside_com.request_str DESC 
LIMIT 0,100;

So, the big question here is: Is there better ways to accomplish this sort of thing without using subqueries? This particular query takes around 40 seconds on 105,000 rows to execute on my computer (Dual PIII, 500Mhz). I’m positive theres a way to do with with a JOIN of some kind, but I can’t get any of those to work correctly either. Let me know if you have any good ideas! I’ll publish a better way to do this hopefully in the next few days once I figure it out. 🙂

A friend just gave me a link to a piece of software called TiddlyWiki. Besides the neat name, it is actually a ridiculously useful piece of software. Its a Wiki inside of an HTML file. Seriously. So you can bring a wiki around with you anywhere that you can edit at any time on any (modern) browser without requiring server software.

Anyways, its way cool, and I’ve already thought of a million different uses for it… though really, you have to use it to understand why its so freaking sweet. Go there. Now.
http://www.tiddlywiki.com/