alexander Posted July 9, 2008 Report Share Posted July 9, 2008 Ok, we havent had many interactive posts here recently, so i figured there is a good reason to start one. Ok, so this will be above normal level of geekiness here, post things that you encounter at work or elsewhere that hit or surpass the limit of dumbness, those are things that are either really inefficient, or generally anything that makes you go :thumbs_up I'll give you an example, one of the databases at at my work place, has a set of generic view's made by this software manufacturer, these views is where the reporting system pulls the information from. That's all good, until i looked closely at one of the views (call it A), that was pulling data from one view (B), and a table, the view (B) that it was pulling data from also pulled date from 7-8 tables, one of which happened to be the table that that other view (A) pulled from, it pulled a dozen fields from that table in that generic view (B), aliased them in the new view (A), and pulled the rest of the fields from that table in (A).... the result was a reporting view that had over 200 fields, most not even pertaining to any of the data that would be used for reporting (here anyways)..... and my biggest problem is the fact that there is no WHERE clause in the script.... seriously NO data filtering, even by something simple, like a date..... no wonder it takes more then a couple of minutes to run the report, thing polls 500+ kilorecords for data :D Quote Link to comment Share on other sites More sharing options...
Overdog Posted July 9, 2008 Report Share Posted July 9, 2008 How about this one. The development team decides they want to load some 40 million rows of raw data into the database so they can run a summary query against it from the front end application. Never occurs to them they could summarize the data up front and just load the summary records (which are only about 3 million rows). Ok, this is dumb, but not dumb enough, they added columns to an existing table which contained registrations (about 15,000 rows) and loaded the 40 million records of raw data into the new columns in the same table with the registrations!!! Then they wondered why the database performance totally sucked when they tried querying registrations! Quote Link to comment Share on other sites More sharing options...
InfiniteNow Posted July 9, 2008 Report Share Posted July 9, 2008 Wow. You guys are totally speaking my language now. We have a training system, and I had to assign about 8,000 training instances to roughly 3,000 users as part of a small pilot of our longer term plan. The interface that allows assignments only accepts about 150 student/course combinations at a time, so I used our batch upload tool instead, which allows 1000 assignments at a time through specifically formatted .txt files (so, I split my workload into roughly 8 upload files). The thing is, when an assignment is made, the system spits out an automated notification email to the assignee, letting them know they have been assigned this training and by when they must complete it. No worries with the regular user interface, but on testing, the batch upload was not triggering the notification to be sent. So, I contacted the Dev team with an urgent request to fix the system so that automated notifications could and were, in fact, sent when processed via the batch upload. I had a deadline, one which had already been communicated to EVERYONE involved, so this change needed to happen quick. So, they fixed it. Except, they set the system up in such a way that as my batch file was uploaded, a notification was sent row by agonizing row... one assignment would be made, then it would trigger the notification email... then the next assignment would be made, then it would trigger the notification email... over and over and over... and the server kept timing out after about 30 or 40 records. What should have taken 45 - 60 minutes to complete took me 3 days! I was working until midnight on a few of them. Let's just say I had some choice words for the dev group, and told them to immediately setup a log file, run the assignment first, then later in the day send all of the notifications from the log. How is it that I'm not even a programmer and I know how to do it better? :thumbs_up They're good guys, don't get me wrong, but MAN that chapped my ***! Quote Link to comment Share on other sites More sharing options...
Overdog Posted July 9, 2008 Report Share Posted July 9, 2008 Idiocracy is not in the future....:thumbs_up Quote Link to comment Share on other sites More sharing options...
alexander Posted July 9, 2008 Author Report Share Posted July 9, 2008 Ooh another one dealing with my issue. I just looked at the table that the data is being pulled from. There are multiple date columns, but all of them are defined as varchar.... and values are not consistent! some are ##/##/## some are ##/##/#### some are ##-##-## some are ##-##-#### some are ##/## some are ########How hard is it to parse a date in the software and enter it into the DB as datetime... problem is since i'm defining a view, i dont think there is any parsing of data in Simple Query Language.... oh numbering of records also does not reveal anything useful, as lookng at top 100 records, they all have seemingly random record number assignments... I dont understand it, simple design that would be client-customizeable should not be hard to do in an inventory application... and then when you do reporting, well cut out any data that is over 1-2 years old, most people dont need to query through that every time they run something against the database, so make a datetime field and use the function getdate to get it. It takes up a field, but if in the end it speeds up the operation 50 fold, it may not be a bad thing to implement.... off to deal with other dumb things Quote Link to comment Share on other sites More sharing options...
Overdog Posted July 10, 2008 Report Share Posted July 10, 2008 Ooh another one dealing with my issue. I just looked at the table that the data is being pulled from. There are multiple date columns, but all of them are defined as varchar.... and values are not consistent! some are ##/##/## some are ##/##/#### some are ##-##-## some are ##-##-#### some are ##/## some are ########How hard is it to parse a date in the software and enter it into the DB as datetime... problem is since i'm defining a view, i dont think there is any parsing of data in Simple Query Language.... Sounds like a mess. If you are using SQL Server or Sybase you shoud be able to convert the varchar to Datetime in the view, but the ideal solution is for the datatype to be datetime in the table and fix the app to use datetimes. In MS SQL: Select Convert(datetime, columnname) as my_date FROM Tablename If you have varchar date strings that won't convert, you'll have to fix them in the table...you can find them with... Select * from Tablename where ISDATE(columnname) = 0 HTH Quote Link to comment Share on other sites More sharing options...
Overdog Posted July 10, 2008 Report Share Posted July 10, 2008 So, once in the early days of the PC, I went on a sevice call to a customer site to fix a problem with a printer. It was a daisy-wheel type printer (a Diablo 630 iirc) and a common problem with those printers was the bottom half of the characters not showing up on the printed page. The cause of this problem was people were exerting too much force when replacing the ribbon cartridges, slightly bending the metal bracket the held the ribbon cartridge. This resulted in the ribbon being tilted up, so the ribbon was not centered in front of the print hammer. Well, there were three elderly people in that office, and in those days, nobody was computer savvy. So I pretended to examine the printer, then called them over. I told them I thought they had a timing problem. I told them that when they replace the ribbon, they needed to watch the clock on the wall, and snap the ribbon in when the second hand was precisely straight up. Then I demonstrated...while they were watching the wallclock, I bent the bracket back into position and snapped the ribbon in when the second hand was straight up. Fired up the printer...problem fixed. They were very impressed. I have always wondered how long after that visit they were watching the clock whenever they changed the ribbons....:eek: Quote Link to comment Share on other sites More sharing options...
freeztar Posted July 10, 2008 Report Share Posted July 10, 2008 @Overdog: :cheer: Lessee... In an effort to streamline efficiency, our IT department recently revamped the national network. Now when we have a computer problem (btw, I'm not *authorized* to make *any* changes to the computer) I have to submit a ticket to the national IT email and wait for a response. So far, response times have been in the range of 3-30 days. In the past, I would just call the office IT guys (2 for 200 people) and they would get me fixed in the same day. Here's the kicker. It's the same two IT guys that deal with my problems now. Why does it take so much longer then? Because those same 2 IT guys in my office now have to handle tickets from all the offices all over the country! Streamlined efficiency my ripped tunic! I know why they did it though. Some offices are very small and don't have enough personnel to warrant an IT staff. This is their way of providing IT support equally across the board. Sounds good doesn't it? :D Our IT guys love it btw. :eek: Here's another gem from work... I had a problem importing .e00 files into a GIS program and asked the IT guy what the heck was going on, the program would shut down immediately every time I made the request. He didn't have an answer (no surprise there). He told me he would research it and walked away (he didn't bother to write down the error code). I did some research of my own and within an hour I found a good workaround that allowed me to continue to work. About 1.5 months later the IT guy said he didn't know what was happening and said that he had ordered me a whole new machine. :hyper: :doh:Sweet! :D Quote Link to comment Share on other sites More sharing options...
Buffy Posted July 10, 2008 Report Share Posted July 10, 2008 We had a customer recently who got a big pat on the back for bringing our product into their department from the CEO. Well, the CIO would have none of that! So he went on a diatribe about *having* to take over as an intermediary between us and the user department for support and came up with a long list of requirements for implementing "best practices" which basically consisted of cutting off our direct access to their installation, requiring us to train their "help desk" staff in both supporting the product and doing self installations. Of course these were all services, so we happily wrote up some invoices for them to pay! Tripled our billings! Cool! Then the fun really began. The IT folk really wanted to show that they were masters of the universe, and since our application uses a database, they found that they could go in and fiddle with it. Finally a user made a request to delete some information, but since the IT folk had not bothered to really understand the database schema or the fact that there were cascading deletes built into the foreign keys, and of course didn't feel like they needed to check with the "stupid vendor," they managed to trash their database with a single command, requiring them to roll back several days of work--apparently "best practices" did not include daily database backups--and requiring the evil user department who had dared to cross IT with a bunch of data reentry. When there's this much incompetence available right here, I start to think that maybe shipping the work to India isn't such a bad idea.... Desperate? That can be a justification for all kinds of behavior, :eek:Buffy Quote Link to comment Share on other sites More sharing options...
alexander Posted July 10, 2008 Author Report Share Posted July 10, 2008 Buffy, i understand that there are two sides of it, and that is why i don't change the databases around (and documentation on the database layout is over 700 pages long and i just don't care to read and understand it) That's why i tinker with creating custom views, and i wouldn't even need that if the reporting engine they were using was anything decent, but the problem is, when you feed 500000+ records of 200 columns into a crystal (6, because of it's web interface licensing) report, and then have the report look for one of the records... SQL will return the data in literaly seconds, its efficient for what it does (methinks you would agree, its no MySQL for data retrieval, but MySQL is no MSSQL for record writing either... and i work a fair amount with MySQL databases, i am trying to change over to Postgres, and yeah, i guess somehow i get to deal with MS now.... hell, can anyone tell me how that's a part of my helpdesk job description? even since i'm fulfilling the job of a system admin, how's this a part of my sysadmin duties... back on topic), crystal takes minutes to process it, as well as lots of processing power and memory... Phone call: > My computer went kableweyMe > Computer went kablewey? What did you do this time?> It's all frozen, and i try to restart it, and it comes up frozen againMe > What do you mean it comes up frozen?> It doesn't ask me to even log in, just comes up the same as it wasMe > What did you do to restart?> I press the button down, the screen goes dark, i press the button again, it comes up frozenMe > Try holding down the power button on the tower, the monitor power button would not restart your system> Ooooooh, the tower, right... :phones: Quote Link to comment Share on other sites More sharing options...
Overdog Posted July 10, 2008 Report Share Posted July 10, 2008 ...That's why i tinker with creating custom views, and i wouldn't even need that if the reporting engine they were using was anything decent, but the problem is, when you feed 500000+ records of 200 columns into a crystal (6, because of it's web interface licensing) report, and then have the report look for one of the records... SQL will return the data in literaly seconds, its efficient for what it does (methinks you would agree, its no MySQL for data retrieval, but MySQL is no MSSQL for record writing either... and i work a fair amount with MySQL databases, i am trying to change over to Postgres, and yeah, i guess somehow i get to deal with MS now.... hell, can anyone tell me how that's a part of my helpdesk job description? Well, it's because there's such a big shortage of people who have database knowledge/skills. For example, the person who developed the crystal report you are talking about evidently had no idea that the right way to do this is to call a stored procedure in the database and pass parameters for the where clause so the database server returns exactly the records needed, and all crystal needs to is display them.... Quote Link to comment Share on other sites More sharing options...
alexander Posted July 11, 2008 Author Report Share Posted July 11, 2008 as i have mentioned, the reports are based on a view in the database, they are written according to the software manufacturer's specifications. Honestly the guy who wrote the 100 repors there has better ways to spend time then to read through 700+ pages of documentation of the database (there are probably 80-90 tables, and they are far from intuitive), to then reverseengineer the databases and be able to then bring down efficiency, plus that was not his function, he was not hired to do this, and probably won't be able to learn this stuff, not that he'd care to... And there aint no way that i am letting anyone who's not a SQL pro to access the database directly, never mind storing procedures...Case closed... Verdict: Flawed By Initial Design Quote Link to comment Share on other sites More sharing options...
LaurieAG Posted July 15, 2008 Report Share Posted July 15, 2008 Hi Alexander, as i have mentioned, the reports are based on a view in the database, they are written according to the software manufacturer's specifications. Honestly the guy who wrote the 100 repors there has better ways to spend time then to read through 700+ pages of documentation of the database (there are probably 80-90 tables, and they are far from intuitive), to then reverseengineer the databases and be able to then bring down efficiency, plus that was not his function, he was not hired to do this, and probably won't be able to learn this stuff, not that he'd care to... Surely he'd just take note of the structure of the key index fields from each table and just disregard elements in the view that are already represented at a higher level. Unless the idea was to have an obfuscated master view that was relatively meaningless to anybody unless they understood which duplicate field contained the real data And there aint no way that i am letting anyone who's not a SQL pro to access the database directly, never mind storing procedures... Too right. I lost a good job in 1991 because a novice hooked up to my clients project management database (non SQL) externally and, instead of upgrading field definitions, populated all of the financial data fields with 'uuuuuuu', without making a backup! Why didn't they just issue a new software update that backed up the database and updated the fields automatically on its first run. At least the developer has a record of all historic data changes at hand. BTW, In the late 80/early 90's DOS days I noticed that the only people in my office who had formatted their hard drives were electrical engineers. Quote Link to comment Share on other sites More sharing options...
alexander Posted July 15, 2008 Author Report Share Posted July 15, 2008 Surely he'd just take note of the structure of the key index fields from each table and just disregard elements in the view that are already represented at a higher level. It's not that difficult as some views allow you to see how they link the data in tables, but it's really less then simple, there are tables that are not linked, there are tables with useless fields that never get used, there are fully empty tables, and lastly there are tables that have random, and completely useless data in it. Problem is, there are various ways and tables that record the data, and when you start dealing with 15 different dates, all seemingly random, some put in by the DB engine, some by users, and so forth, it becomes simlply worthless to, without having solid SQL skillz (yes with the z, because skills wont help in this situation), try to reverse engineer this DB... Wow thats a mighty big mistake, almost as bad as a friend of mine, who ran mk2fs instead of fsck on his drive and reinitialized the file system on his hard drive.... talking about loosing 2 months worth of projects and data :eek_big: Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.