Addendum: one that stands out was an in-house survey app that allowed you to create questionnaires and email them to people in the company. Responses were saved in a database and you could make complicated statistical inquiries like, show me how people answered questions 7 and 8 who said Yes to question 12, No to 13, and filled in the “Other” blank for 19.
My job was to speed up the SQL queries, which were so complex and slow the max runtime had to be increased to like an hour to let them finish. This was because the original database of questions and multiple-choice answers had been modified in several stages, which ended up with response details in multiple fields in multiple tables depending on the type of question. After about a month I managed to streamline the queries so the longest one took less than 10 minutes, but this was still enormously slow because questionnaires had maybe 2000 responses max. The problem was the database structure relationships was too complex because things had been scabbed onto it.
One day at lunch I spent about 20 minutes noodling a redesign with fewer tables. All user responses would be in one place, and the longest queries probably would have run in a second or two, plus maintenance and enhancements would be WAY easier. When I proposed actually doing this, management said they would think about it if they ever did a new version.
Addendum: one that stands out was an in-house survey app that allowed you to create questionnaires and email them to people in the company. Responses were saved in a database and you could make complicated statistical inquiries like, show me how people answered questions 7 and 8 who said Yes to question 12, No to 13, and filled in the “Other” blank for 19.
My job was to speed up the SQL queries, which were so complex and slow the max runtime had to be increased to like an hour to let them finish. This was because the original database of questions and multiple-choice answers had been modified in several stages, which ended up with response details in multiple fields in multiple tables depending on the type of question. After about a month I managed to streamline the queries so the longest one took less than 10 minutes, but this was still enormously slow because questionnaires had maybe 2000 responses max. The problem was the database structure relationships was too complex because things had been scabbed onto it.
One day at lunch I spent about 20 minutes noodling a redesign with fewer tables. All user responses would be in one place, and the longest queries probably would have run in a second or two, plus maintenance and enhancements would be WAY easier. When I proposed actually doing this, management said they would think about it if they ever did a new version.
At least I tried.