db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kathey Marsden <kmarsdende...@sbcglobal.net>
Subject Nice feedback about protocol fixes and optimizer work on IRC
Date Wed, 07 Jun 2006 18:11:28 GMT
IRC was unusually busy this morning. There was no talk about code but 
there was some nice feedback from users on
on  the protocol fixes  contributed by Bryan (fixed X-gGen's problem on 
the spot)  and  the optimizer work  that Army has contributed. Also some 
interesting chat about paid jobs and open source and great news that we 
have a patch for DERBY-1374.

   <cloder>    morning
   <kmarsden>    hello
   -->|    X-Gen (n=X-Gen@dsl-145-251-57.telkomadsl.co.za) has joined 
   <X-Gen>    hey freaks
   <X-Gen>    this the apache derby place ?
   * X-Gen    thinks kmarsden = Kathey Marsden maybe. so this must be 
the correct place
   <X-Gen>    Whoot
   * X-Gen    shakes the # about
   <cloder>    er
   <cloder>    do you folks use outer joins much in derby?
   <cloder>    the optimizer is doing nasty things
   <cloder>    foo left outer join a.z ON a.x = b.x
   <kmarsden>    can you describe nasty?
   <cloder>    um, hm
   <cloder>    ok, doing a repeated table scan of a huge table
   <cloder>    to the point where i had to rewrite the left outer join 
as a UNION of two selects (one of which uses a NOT EXISTS subquery)
   <cloder>    in other words, rewriting the left outer join as 
UNION(inner-joined-rows, missing-rows-with-null-constant)
   <X-Gen>    org.apache.derby.impl.drda.DRDAProtocolException is nasty 
right ? how do i go about getting more informatio as to why my query is 
failing ?
   <cloder>    i have the output of the query analyzer
   <cloder>    trying to think of ways to rewrite this
   <kmarsden>    Yes. org.apache.derby.impl.drda.DRDAProtocolException 
is nasty and usually has nothing to do with the query being executed.
   <cloder>    other than what i've done, which is quite ugly (even 
though it performs about 1000x faster)
   <kmarsden>    You are saying that your query gives a protocol 
exception and you rewrite it and it does not?
   <cloder>    me?
   <X-Gen>    kmarsden, yup
   <cloder>    two separate people i think
   <kmarsden>    oops
   <X-Gen>    kmarsden, if i have one order by col. the query works, i 
add another col to the order by statement and boom. im using 10.1.1 
going to update quick and see if its fixed
   <kmarsden>    Oh well trying to do this while cooking my son's 
breakfast. Anyway, for the query issue try with the10.1.2.4 snapshot 
with DERBY-805/649 fixes.
   <cloder>    i will give it a shot
   <kmarsden>    yes. both update to the snashot and see. There have 
been even more protocol and optimizer fixes since too that will make it 
into 10.1.3
   <cloder>    i think maybe i will experiment with rewriting this as a 
right outer join
   <cloder>    seems the choice of join order is basically fixed
   <kmarsden>    X-Gen, I was bothered by your initial greeting. Please 
be nice.
   <X-Gen>    kmarsden, sorry to have upset u so early in the AM. (hey 
freaks is my usual greeting when joining a channel. but ill refrain from 
this in #derby)
   <kmarsden>    X-Gen: Thanks!
   <X-Gen>    kmarsden, do you have a link handy where i can get the 
snapshot from without builing it myself ?
   <cloder>    x-gen: google for 'derby download'
   <X-Gen>    aah thanks
   <cloder>    i'm impresed with army, whoever s/he is
   * X-Gen    gets the same error on, going to download snapshot quick
   <daghw>    Kathey, I think Fernanda has uploaded a new version of 
   <cloder>    from my reading of the predicate pushdown patch notes, 
this looks promising
   <cloder>    hmm. any way to force soft upgrade from production to alpha?
   <daghw>    yes, but it is only intended for developers, since it wont 
upgrade to the next snapshot. It is intended for developers to test 
upgrade paths,,, but if you are sure you can drop the db later you coudl 
try it out: Set this property:derby.database.allow PreReleaseUpgrade
   <cloder>    ok
   <cloder>    this is a fairly large, problematic database of which i 
have many copies
   <cloder>    our schema is over-normalized, to be frank
   <cloder>    SELECT d.dev_id, d.dev_addr, na.addr_ipv4, nn.name, 
d.dev_tag, s.site_id, s.riskfactor, d.riskfactor, ds.os_id, ds.hw_id, 
ds.riskscore, ds.vcnt_crit, ds.vcnt_sev, ds.vcnt_mod, sc.end_time FROM 
sites s, site_devices d, device_synopsis ds, scans sc, node_addr_ip4 na, 
nodes n LEFT OUTER JOIN node_name nn ON n.node_id = nn.node_id WHERE 
s.status IS NULL AND s.site_id = 1 AND s.site_id = d.site_id AND 
d.dev_id = ds.dev_id AND ds.node_id = n.node_id AND
   <cloder>    n.scan_id = sc.scan_id AND na.node_id = n.node_id ORDER 
BY 15 DESC, 11 DESC, 3 ASC, 1 ASC
   <cloder>    you get the idea
   <cloder>    the nodes (n) table has about 50,000 rows, the node_name 
table (nn) has about 75,000
   <cloder>    and it's doing: hash-left-outer-join(table-scan(nodes), 
   <X-Gen>    Whooha it werks it werks. Thanks kmarsden ;)
   -->|    FernandaTP (n=fernanda@amfea-proxy-2.sun.com) has joined #derby
   <daghw>    cloder: Did you put this question to the derby-user or 
derby-developer list? I think you will have a better change of a good 
answer if you do, coz none of the compiler experts are here right now.
   <kmarsden>    kmarsden: Thanks goes to Bryan not me.
   <cloder>    daghw: no, i'll do that
   <cloder>    who are the compiler experts, if you know
   <cloder>    i'd sooner hire one of them than ask the list :)
   <daghw>    haha! we need'em!
   <cloder>    oh, i'd hire them to work on optimizing our use of derby 
and of course, optimizing derby itself
   <cloder>    so the benefits would still be committed to derby
   <daghw>    i think they already have an employer :)
   <cloder>    yes, i get the impression that many of the experts would 
rather not say who their employers are, either :)
   -->|    pmcmahan (n=pmcmahan@bi01p1.nc.us.ibm.com) has joined #derby
   <pmcmahan>    I have versions of an sql script for db2, firebird, 
hypersonic, interbase, jdatastore, mysql, oracle, postgres, sap, and sql 
   <pmcmahan>    Can someone advise which version might be the best 
starting point for a derby version of the script?
   <cloder>    db2 is somewhat similar
   <cloder>    at least in my experience
   <pmcmahan>    thanks cloder
   <cloder>    oracle is vastly different
   <cloder>    of course :)
   <pmcmahan>    yeah that's my experience too :-)
   <kmarsden>    cloder: I am curious why you think that about folks 
hiding their employers.
   <cloder>    kmarsden: i'm involved in other open source projects 
where that is the case
   <cloder>    it is very common to have people who use something at 
work, and spend employer time hacking on it and improving it, but not 
wanting their employer to know
   <kmarsden>    I use a non-IBM email address. I hope it is not 
interpreted that want to hide that I work for IBM as I stated that 
clearly with my initial mail to derby-dev. I do it because The Apache 
Way is for us all to act as individual volunteers, expressing our own 
opinions and calls for diversity. I think using my own email promotes 
equality and community and makes it clear that my opinions are my own 
regardless of who pays my paycheck.
   <cloder>    yeah, but obviously you can only speak for yourself
   <cloder>    btw, what do you do at ibm if you don't mind me asking
   <cloder>    i used to work there :)
   <cloder>    well, for lotus
   <kmarsden>    I work on Derby
   <cloder>    oh, nice :)
   <daghw>    kathey, did you see my reply?
   |<--    daghw has left freenode (Remote closed the connection)
   <--|    pmcmahan has left #derby
   -->|    daghw (n=dw136774@brmea-proxy-3.Sun.COM) has joined #derby
   <kmarsden>    dagw: Yes you said there is a patch to fix 1374. That 
is good. Not good to have those compat tests broken for long. Off to 
help with homework.
   <kmarsden>    cloder: Actually what I do for IBM is I work in 
advanced support. I help users like you and X-Gen get past your problems 
and try to make sure they are fixed before you hit them (or at least we 
have a fix waiting). It is great to get early feedback from users like 
you as we put snapshots out to get feedback on regressions and potential 
compatibility issues.

View raw message