Kognitio Lands Semi-interesting Client
Kognitio announced yesterday that the National Center for Genome Resources has "deployed Kognitio's WX2 purpose-built database". I find this semi-interesting for two reasons:• "Deployed" is very different than "selected"
• I'm a bit tired of hearing about "enterprises" who have "selected" a particular MPP database for this that or another. This isn't really Earth-shattering news either, but at least it's a bit different.
Now, I don't know the first thing about genome research, but I do know that at least one MPP vendor has added functionality specifically for genome research clients. As such I wondered whether Kognitio had done the same to close the NCGR deal. When asked, however, the folks at Kognitio said that "NCGR [is] using pure SQL as they migrate over". I'm not sure that means that NCGR is doing the same old boring SQL stuff as everybody else or that MPP databases have finally matured to the point that you can write the complex (and often ugly) SQL queries that mature OLTP systems have been supporting for years. My guess is that the answer is somewhere in the middle.
Like I said... semi-interesting.
Confessions of an Old SQL Hacker: I Like ANSI Joins
For longer than I care to remember, I've been writing joins like so:SELECT col1, col2 -- Boo on "SELECT *"!
FROM T1, T2
WHERE T1.x = T2.x
Over the last six months or so, however, I've found myself forced to used outer joins (which are evil, but that's another story) and thus having to write queries like this:
SELECT col1, col2 -- Seriously, "SELECT *" is for rookies
FROM T1
LEFT OUTER JOIN T2 ON(T1.x = T2.x)
Then, to keep things consistent (think self-Romanism) inner joins obviously get written like this:
SELECT col1, col2 -- Did you really think I was going to use "SELECT *"?
FROM T1
INNER JOIN T2 ON(T1.x = T2.x)
What's shocking about this is that I find myself liking this syntax. It's much clearer, at least in my opinion, especially when joining more than two tables or when there's more than one join criteria between two tables. Further, it separates the join criteria from the "filter" criteria, i.e. what's in the WHERE clause is only what belongs in the WHERE clause. Consider:
SELECT col1, col2 -- You get the point
FROM T1, T2, T3, T4
WHERE T1.x = T2.x
AND T2.x = T3.x
AND col3 = 'foo'
AND T3.x = T4.x
AND T2.y = T3.y
vs.
SELECT col1, col2 -- I hope you do anyway
FROM T1
INNER JOIN T2 ON(T1.x = T2.x)
INNER JOIN T3 ON(T2.x = T3.x AND T2.y = T3.y)
INNER JOIN T4 ON(T3.x = T4.x)
WHERE col3 = 'foo'
See the difference? Join criteria are forced to be grouped together logically, and the WHERE clause is cleaner. It's hard not to like it.
And it beats the hell out of the old Oracle outer join syntax. 'nuf said on that subject - you either know what I'm talking about or wouldn't believe that I was quoting real syntax. Anyway...
There you have it. I like the ANSI join syntax. I admit it.
I guess you can teach an old dog new tricks.
Vertica Offers Two-Day Training Class in April
I must be blind, because I can't find a link to this anywhere on Vertica's site, but... Vertica is offering a two-day training program in early April that looks very interesting. For details see http://www.vertica.com/vertica-training.Given my proximity to Billerica I might just have to go to this... I just have to convince my CFO to pay for it.
Things That You Just KNOW Ain't Gonna Happen
I received an interesting email from a database vendor this morning. Shortly afterword I received another from the same vendor that said this:Dear Thomas
I believe you received this communication in error. I would kindly ask that you delete it immediately.
Sorry for the inconvenience.
I hadn't exactly scrutinized the original email, but you can be damn sure I went back and read it after I got that second one.
(Did you really think I would just blithely delete it? Please.)
It was all pretty positive and interesting stuff. I look forward to them releasing it publicly.
Viewing the Execution Plan of a Running Query in SQL Server
Or, Simple SQL Server DBA TricksHere's a trick I wish I'd discovered long ago... given a long-running query started by session ID of 42, you can get the handle to the cached execution plan for the query by running:
SELECT plan_handle FROM sys.dm_exec_requests WHERE session_id = 42
Given that plan handle, you can get an XML representation of the query plan by running:
SELECT query_plan FROM sys.dm_exec_query_plan (0x06001D009FE38431400399D4000000000000000000000000);
(The hex value there is the value returned by the previous query.)
Now, save the output of that query to a file with a .sqlplan extension, and open it with Management Studio. Viola! A graphical representation of the query plan for the problem query as it was actually executed.
Grabbing the query and generating a plan for it will usually give you the same result, I believe. It has always bugged me that there was a potential difference between the plan generated for a problem query at run-time vs. the plan generated at problem diagnosis time though. This relieves that little concern and provides other options for working with the query plan as well (it is XML, after all).
