Just venting a bit here... I just spent the past four hours sorting out what my 'replacement' (I'm retired) did to one of my former clients... he went through and combined every possible separate query I had into single ones... Which conventional wisdom from every alleged SQL "expert" says is the right course of action. Net result? Page construction time went from 2/5ths of a second to over 9 seconds! Worse, during daytime traffic (since it's MOSTLY, but not entirely a LAN app) the high request count was making the server barf up 500 errors and timeouts... until eventually the dropped transactions screwed up everything and took the whole kit and kaboodle down hard. He apparently failed to realize that this client is hosting on a decade old P4 with a gig of RAM over a 10mbps symmetric connect from their office, and NO, switching to something better was not considered an option... Contrary to what many "experts" in the industry say, combining queries should be done with an eye-dropper, with a eye for result set size kept in mind. The greatest offender in the code I just had to undo back to it's November baseline without breaking other changes, was a four table check (it's a relational database for double entry accounting)... that how I was handling it was pulling up the relational tables (which just fill in selects) first, and just indexing those to the data values -- he combined it into one massive query with the result that the average fetch size was pushing past 4 megs... since it was now outputting multiple rows for one result! (on something that originally was maybe 40k?) SQL is passed over the network, even locally it's a socket operation. Sure, less connections is a good idea, but single massive connections that hog it so nothing else even has a chance to run, that passes massive amounts of data over the network? That's just stupid. Sometime folks, multiple smaller targeted queries results in faster operations through smaller result sets and better leveraging of indexes. Flies in the face of what a lot of people are being taught, but that's just common sense. Concerns like this are an issue whenever shared hosting, old hosting, or just plain sitting alongside other programs on the server not as well written as your own. (yes Goldmine, I'm looking at YOU!)... and let's be brutally frank, if it runs better this way on lesser hardware -- why the devil would any other way work better on more? Though there is a wee bit of satisfaction in having the client you yelled at "If you're going to micromanage it this much ignoring every piece of my advice, what the **** did you hire me for? Think you can do better, you do it!" come crawling back hat in hand for help. Of course it's also a laugh when after this fiasco they're willing to finally update to something a bit more modern for hosting... A whopping Atom powered dedicated server (hey, at least it's in a real data center!) ... and I FINALLY get to get them off of Win2k server (not joking) and onto Debian. Some retirement ... Though I'm also apparently now a "god" (according to said client) for having repaired in four hours what it took this dipshit four weeks to code and three days to deploy... I even recovered about 8 dozen 'lost' transactions; which in a double entry accounting system is NOT a good thing. You know something is wrong when the combined total of all accounts does NOT equal zero. This was time critical too -- place deals with retirement benefits; today is SSI deposit day; when all the elderly, retired and disabled get their monthy dose of the government take... They NEED that system up... as of 8AM EST. Did it with two hours to spare, only finding out about it at midnight and starting on it at 2AM.
I feel your pain. It seems that they manufacture that client in trainload lots. (And that replacement is fairly easy to find too.) I've designed a lot of apps to run on 640MHz Win2K servers (yeah, they made "servers" back then). I've always maintained that one of the design goals is to load the network as lightly as possible. Your post is just another bullet in that gun.
In a lot of ways that's what kills me about some of the business software today -- Where user and database accesses that are choking out quad core Xeons with gigs of RAM are doing what I used to host in the early to mid-90's on a 486/66 with 16 megs of RAM running Netware 3.12 with three or four times as many client connections -- most of the time the client software being done in either Clipper or Borland Paradox running on DOS. Admittedly that was LAN, not Internet -- but surely the gigabit width pipes over 10bT twisted pair averaging well below 200ms response time is superior to 1mbit 10b2 coax at one second? I've been half tempted to drag out Paradox 3.5 or 4 for DOS and 'classic' Netware 3.x, load them up in a VM, and write a forum software that runs on them for laughs. I don't think there's any reason I can't make Paradox output HTML... the trick would be getting old-school Netware to talk TCP/IP instead of IPX, and getting Paradox to run as a CGI. ... and yeah, they made servers back then. Hell, at one point I was managing an office that was a pair of TRS-80 DT-1 Terminals(basically gutted down TRS-80 Model III) connected to a TRS-80 Model II running Microsoft Xenix. (with the optional 6mhz Motorola 68000 board/model 16 upgrade kit)... ah the joys of running a business' database off of four 8" floppy drives.
Sounds like my RCPM running on an Apple ][ with 4 drives. And the business ran on an IBM PC (one of the original model 2s [floppy, the model 1 was cassette) running - you guessed it - dBase (uncompiled Clipper) and an Orchid network on 10baseT (early 80s). And even earlier, we ran businesses on stuff running SBasic in CP/M. 64k RAM and a couple of 128meg floppies. One essential tool was a shoe horn - to cram every last byte into the system. BTW, I still have my 2 Shugart 8" drives.
Fully agree. Even if you've got an ancient machine running as your database server (as here), the more work you do at the server end, and the less data you ship over the network, the better. And, if you really can't get a reasonable response speed, at least you only have to upgrade the "engine room" (i.e. the database server) rather than the various client machines. (Which, in a web environment, may not even be yours to upgrade anyway.) With a three-tier set-up (database, app server and client), the logistics get a bit more complicated, but the basic principle of "only bring back the data you need" still has to be the right one.
That holds true even when you're not doing a database query - as little data on the network as you can. Caching helps too.