Somewhat useless but rather neat statistics

Ess Four got deleted. His posts are in there with Anonymous now (along with a few others who got deleted)
 
Shadowfax said:
I'm still on top of LL :D
Click to expand...


How nice for you. Scanty is on top of me, I like that a lot more to be honest :p





I agree with fury on the SQL thing btw, SQL commands in caps is sooo much easier when you're reviewing your own code after 6 months...

Never tried Sybase, Oracle is too expensive for me, Interbase is quite nice but I never worked with it for long, MySQL just rules. It may not have the most features, but it's easy, portable, supported by most hosts, and works great with Delphi :headbang:
 
I agree with fury on the SQL thing btw, SQL commands in caps is sooo much easier when you're reviewing your own code after 6 months...
Click to expand...
hm, for me it looks neater just like that in lowercase :) personal preference I guess

Never tried Sybase, Oracle is too expensive for me, Interbase is quite nice but I never worked with it for long, MySQL just rules. It may not have the most features, but it's easy, portable, supported by most hosts, and works great with Delphi
Click to expand...
Oracle is very stable, very powerful, and very neat. I work with it on Sun Enterprise servers. At home I have an Oracle server, but the windows one just isn't the UNIX one. MySQL fucks up my queries too much. I had one query that had 13 nested selects in each other (highly highly optimized on Oracle) that took under 0.5 seconds to run on Oracle, but well over 23 minutes on MySQL. :)
 
Like I said, Oracle is better, but a hell of a lot more expensive. Besides, I never need queries which are that complex anyways, I only use it for my website and to store some stuff in my applications, basic SELECT and UPDATE queries are all that is needed... in that case, I'd prefer MySQL ;)
 
fury said:
Duh, MySQL doesn't even have subselects ;)
Click to expand...
Hm that might be why the query never finished. I pulled the plug after 23 minutes. Duh, I'm stupid. I write nested selects ALL THE TIME and can't live without it.
 
unclehobart said:
Rerun it.. I think I might have jumped about 3 slots in the last 15 min. ;)
Click to expand...
Yup, you're now between GOD and DuronClocker with 16.1290 average replies per thread started.
 
This is what I'm talking about:

Code: 
select distinct a.target, b.region, b.country
from typeEntries a, locations b
where a.origination =
 (select distinct a.origination
 from typeEntries a, locations b
 where a.origination = b.Location and a.r_type =
  (select distinct a.r_type
  from services a, types b
  where a.r_type = b.r_type and a.masterlistID =
   (select masterlistID from masterlists where DisplaySequence =
    (select min(DisplaySequence) from masterlists)) and a.siServiceTypeId =
     (select siServiceTypeId from services where DisplaySequence =
      (select min(DisplaySequence) from services) and masterlistID =
       (select masterlistID from masterlists where DisplaySequence =
        (select min(DisplaySequence) from masterlists)))
     and a.DisplaySequence = 
      (select min(DisplaySequence) from types
      where masterlistID = 
       (select masterlistID from masterlists where DisplaySequence =
        (select min(DisplaySequence) from masterlists)))))
   and a.r_type = 
   ((select distinct a.r_type
   from services a, types b
   where a.r_type = b.r_type and a.masterlistID =
    (select masterlistID from masterlists where DisplaySequence =
     (select min(DisplaySequence) from masterlists)) and a.siServiceTypeId =
       (select siServiceTypeId from services where DisplaySequence =
        (select min(DisplaySequence) from services) and masterlistID =
         (select masterlistID from masterlists where DisplaySequence =
          (select min(DisplaySequence) from masterlists)))
       and a.DisplaySequence = 
        (select min(DisplaySequence) from types
        where masterlistID = 
         (select masterlistID from masterlists where DisplaySequence =
           (select min(DisplaySequence) from masterlists)))))
and a.target = b.Location
order by country, region
:D :D
 
cool... running the math, all I have to do is milk one of my threads for another 900 replies to sneak into the top 10.
 
LastLegionary said:
This is what I'm talking about:

Code: 
select distinct a.target, b.region, b.country
from typeEntries a, locations b
where a.origination =
 (select distinct a.origination
 from typeEntries a, locations b
 where a.origination = b.Location and a.r_type =
  (select distinct a.r_type
  from services a, types b
  where a.r_type = b.r_type and a.masterlistID =
   (select masterlistID from masterlists where DisplaySequence =
    (select min(DisplaySequence) from masterlists)) and a.siServiceTypeId =
     (select siServiceTypeId from services where DisplaySequence =
      (select min(DisplaySequence) from services) and masterlistID =
       (select masterlistID from masterlists where DisplaySequence =
        (select min(DisplaySequence) from masterlists)))
     and a.DisplaySequence = 
      (select min(DisplaySequence) from types
      where masterlistID = 
       (select masterlistID from masterlists where DisplaySequence =
        (select min(DisplaySequence) from masterlists)))))
   and a.r_type = 
   ((select distinct a.r_type
   from services a, types b
   where a.r_type = b.r_type and a.masterlistID =
    (select masterlistID from masterlists where DisplaySequence =
     (select min(DisplaySequence) from masterlists)) and a.siServiceTypeId =
       (select siServiceTypeId from services where DisplaySequence =
        (select min(DisplaySequence) from services) and masterlistID =
         (select masterlistID from masterlists where DisplaySequence =
          (select min(DisplaySequence) from masterlists)))
       and a.DisplaySequence = 
        (select min(DisplaySequence) from types
        where masterlistID = 
         (select masterlistID from masterlists where DisplaySequence =
           (select min(DisplaySequence) from masterlists)))))
and a.target = b.Location
order by country, region
:D :D
Click to expand...
Its hypnotic... almost like a lava lamp.
 
Holy FARK that's a long query :eek:

I imagine there's some advantage to using subselects over using multiple queries, but I don't know why MySQL doesn't have them if it's faster.
 
fury, each of the 5 or so tables involved have over 30,000 records, and it takes oracle 0.3 or so seconds to run that query. MySQL just crashed (I thought it was working or something). Its very highly optimized and very specific. We did a crap load of traces and stuff, and the indices are just right :D I'm an Oracle DBA btw, which is why i'm defending it so much :p I like MySQL for its simplicity, but in a production environment I need my Oracle.
 
my guess is that you use nested selects because you're too lazy to optimize your queries.
 
ohhhh sorry, i just read it fast (too lazy of me :D), i kind of see it again, and yeap, it looks ok, however, there must be room for some optimization ;)
 
Luis G said:
ohhhh sorry, i just read it fast (too lazy of me :D), i kind of see it again, and yeap, it looks ok, however, there must be room for some optimization ;)
Click to expand...
By all means, PLEASE! My boss will kiss you (a she). We spent days on all our queries running lots of traces, looking for table scans, performing special indices, moving tablespaces, etc., and of course the query itself, moving compares, switching first and second parameters, etc., and this is as good as we got it.
 
You must log in or register to reply here.
Back
Top