Jim Lawrence (AccessD)
accessd at shaw.ca
Fri May 21 20:22:55 CDT 2004
Another grim tale from the other-side...the first one gets the money (and
the glory?) and the next get the blame. Pretty standard fare for a
developer.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Friday, May 21, 2004 11:29 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various
I hear ya on that one! Let tell you a little about a 'job' I've been
working on for a few months, off and on.
I do a few 'sub-contract' jobs for my sister. She does web development, but
specializes in Front End stuff. When she has BE stuff, she usually
delegates that stuff to me. One such job came her way last October.
http://www.edlevinjewelry.com . It's a jewerly wholesale (and no ladies, I
can't get a discount...<grin>). Anyhow, they had some 'developer', and I
use that term VERY loosely, build this product/shopping cart database. He
did a few neat things, but had no real concept of relational databases, and
quite frankly it seemed like he took a sledgehammer approach to many tasks.
For example, the website displays products from the database. Each product
has a price 'table' (if you log in as a merchant...sorry can't hand out
accounts for testing...). This HTML 'table', was actually a memo field in a
table. He had the entire price structure for each product as an HTML table
in a memo field. I know I just repeated myself, but to me, that is the
equivalent to storing an entire record of employee information in a memo
field. Yes, it stores the data, but it is just a glob, no easy way to do
anything with it other then just display it in it's entirety.
I was asked to quote them on making their 'products' updatable. Apparently
they would send an excel spreadsheet with updated product info to their
'developer', and a few weeks later, the data was updated on the web. Well
duh, he was building HTML tables on an individual product basis! Go figure
it took him three weeks to do! Now they were on the bends with this guy, so
they wanted the ability to change the data themselves. So I looked into
their system, tried desperately to not have a coronary when I saw what the
original developer had done, and quoted them a very fair price to redesign
their product information. So I built an actual 'relational' product system
within their existing database. imported the old data (which was a job and
a half!), and then built them an interface to allow them to actually update
the data within the relational system.
When I was done, they had a very simple interface to allow them to
create/modify any price structure they wanted.
Done, right? Nope, the client wasn't happy now, because even though they
had a working product database, their shopping cart page was still using old
data. As it turns out, this schmuck (sorry, it's the nicer term I have for
this guy...) apparently built TWO systems in the database. What he
displayed on the website to potential customers was in the memo field, but
what the 'shopping cart' used for pricing was the most goofy and completely
bizare '|' parsed text field system I have ever seen. So now I was being
asked to update the shopping cart to use MY new data structure, for free. I
was working for my sister, who was working for a guy, who actually had Ed
Levin as a client. Somewhere between Ed Levin and myself, it went from 'we
need everything to work', to 'the product pricing doesn't work'. My sister
gave me a little more (about 20%) out of pocket, because it was a goof up on
their part, but what took me a few weeks to 'build', now took me about two
months to complete. It was a nightmare. What they had in the end worked,
but I to cut my 'time' (since I was essentially working for free), I kept a
lot of what the original guy had done. Because of that, there have been
little 'issues' here and there, which his code does something very bizarre
and unexpected when someone makes a little variance in data (like going from
2 to 3 characters on a 'product type'). Because I worked on it, they expect
me to 'warrantee' HIS work. (That had an abrupt end put to it recently, I'll
explain in a bit). Their way of thinking is that I am 'breaking' the
original code, when I make a change to their site. What is really happening
is that they original site had NO method of them being able to make changes
or updates that they wanted. I built the system for them to do that, but a
large portion of their site still uses the original code, which may or may
not handle new oddities. (Remember, this guy took 3 weeks to change the
price on something for them.....probably because he was recoding half the
site every time he did it! <grin>)
The straw that broke the camel's back happened pretty recently. Their site
has a 'storefinder'. We (my sister and I) were tasked to update their
zipcode database. Apparently it was missing quite a few towns, and they
wanted the most recent data they could get in there. So my sister found and
purchased a zipcode database, with the latest information, and I imported it
into their system. Case closed. Or so we thought. Their store finder then
began to 'act funny'. They began to get repeats of stores in their results.
Immediately we (my sister and I) were being accused of 'breaking' the
original developers code. It was actually getting very ugly. (Which is why
I prefer to do sub-contract stuff, because with rare exceptions don't have
to get involved in the ugly stuff). Anyhow, I ported that entire 'system'
into VB, and walked through it. I did this because I walked through the ASP
mentally, and realized it would take a few lifetimes to try and figure it
out in my head. What this guy did, was pull up a query that listed ALL of
the stores, with their distance from the zipcode in question. (If a
city/state was used, it retrieved the appropriate zipcode first). Then it
looped through every returned record, and determined if it was closer then
one of the 5 elements in an array. If it was, it replaced the 'furthest'
element. IT did that for several thousand 'stores', then it went and sorted
the results. (in the array).
Ugly. Just plain ugly. Looking through the ASP, I just couldn't see what
the issue was. When I ported it in VB, I was able to watch things a little
easier, of course, and noticed that with the 'original' database, it was
doing the distance calculation against a recordset with a few thousand
records. However, the new database was doing that process to 80k+ records.
Huh? Then I looked at the SQL that was pulling up that recordset.
Okey, we have two tables, Stores and Zips. Both have a Zip field (zipcode),
the Zips table has Lattitude and Longitude, and the store table has a store
name, etc. Here is the original SQL (NO KIDDING!)
SELECT ID, Lat, Lon, add1, add2, vis_addr FROM Zips, Stores WHERE Zips.Zip =
Stores.Zip and Stores.is_active='YES'
ID, add1, add2, and vis_addr are Store fields, Lat, Lon are Zip fields (and
were used in a rectangular fashion to determine distance (which if you
understand lats and longs, you can get close with distance just using
'square' distances, but to get actual distances you have to use spherical
geometry, just another ugliness to the original code). Please note in the
SQL statement above, the FROM statement has no join, but has two tables.
There is a half baked attempt at a join in the WHERE clause. What the
original @#$#@$^ had done, is just stripped the Zips table, so that a Zip
code only showed up once, even though one zip code should show up multiple
times for multiple towns.
Anyhow, the fix was this:
SQLstmt = "SELECT Stores.ID, Avg(Zips.Lat) AS AvgOfLat, Avg(Zips.Lon) AS
AvgOfLon, First(Stores.add1) AS FirstOfadd1, First(Stores.add2) AS
FirstOfadd2, First(Stores.vis_addr) AS FirstOfvis_addr " & _
"FROM Zips RIGHT JOIN Stores ON Zips.Zip = Stores.zip " & _
"WHERE (((Stores.is_active)='YES') AND ((Zips.Zip) Is Not Null)) " &
_
"GROUP BY Stores.ID;"
but it wasn't put in place until we had agreement on pay. Essentially we
told them we were tired of being blamed for bad design on the original
developers part, and we would either put their old data back (for a price)
and walk away, or fix the problem for a price, and continue working with
them.
All because of a bad initial developer. Ugh.
Wow, lengthy post, sorry!
Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Friday, May 21, 2004 11:16 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various
Thanks, but only the context of bad performance made me look good.
Anyone on this list would not have done it that way, and I would have
been groping for optimizations of 10% rather than brain-dead-simple opts
that deliver 100%. That said, there are guys and gals hanging a shingle
out there who don't even comprehend what a PK or FK is, and why they are
important. I've never been much of a fan of certs, but I'm beginning to
rethink my stance on this.
Yes, it's money in my pocket -- about which I never complain. (Fuller's
Second Law: money never arrives at an inopportune moment.) But part of
me wishes that the client shouldn't have to re-pay for such low-level
and obvious design decisions. The client got hosed, pure and simple, and
it angers me that I'm in the same business as the previous developer.
Oh, one more thing. When the problem I was hired to fix was explained to
the previous developer, he quoted a MONTH. I quoted a day. I was wrong;
it took three. I billed for two and the client happily paid. A MONTH! He
must be operating from the premise that if the client is ignorant enough
to accept the proposal, nuff said.
I'm beginning not to like this business.
Arthur
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Friday, May 21, 2004 7:27 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various
Congratulations :-) Arthur.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Thursday, May 20, 2004 5:32 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] On DB Bloat, Bad DB Design, and various
Just a note on this bloat subject. I acquired a new client recently,
whose DB was 220 MB. They burned me a CD containing same last weekend,
so I could work on it while they were closed. Dumbass previous developer
hadn't even split it into FE-BE! Everyone was loading the same instance
of the whole thing across the net!
They had a problem which I fixed. I estimated a day but once I got into
it, 3 days elapsed. I split the difference and billed for 2 days.
Upon inspection of said database, I discovered some incredible and
bizarre anomalies/stupidities. Several tables of critical importance had
no PKs and no FK indexes! Unbelievable!
Anyway... I split the datbase, then made some mods to the BE which in
theory ought to have almost doubled it (i.e. I made copies of every
transaction table, but not the static lookup tables). Then I ran
compact/repair, and the db came down to (gasp) 40 MB. This includes the
doubled tables and the new PK and FK indexes that I added. From 200+ MB
to 40 MB -- and this with doubled transaction tables. Once we get the
s**t sorted out, I expect that it will come down to 25 MB.
I have never programmed in an automatic compact/repair before -- I
suppose because I tend to be available and do it as a matter of course.
But this app has caused me to rethink that.
More seriously, this app has caused me to rethink the virtues of
certification. No BE! No PKs! No FK indexes! No wonder the bloddy app
was slow with only 20 users on a net!
The up side is, it's really easy to look good in a situation like this.
The performance gains were spectacular. I got high-fives from all
directions :)
This is a strange business :)
Arthur
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com