220 reputation
17
bio website
location
age
visits member for 1 year, 10 months
seen Sep 27 at 16:37

Sep
26
awarded  Yearling
Sep
22
comment Replace relational database with “poor” design in software product with non-relational DB?
The last might be helpful. A few years ago, SQL/HQL queries still had parameter values concatenated into strings, which I wanted to fix. One day, a customer complained about poor database performance, long waiting times and timeouts, making serious noise about it. So, alongside with some quick fixes, I took a night shift on my own account and changed all the query building routines in the web service to parameterized queries. There was, however, some risk to it; had there been problems by this, I would have gotten into problems. But if I had asked: "lets' parameterize queries" - no chance!
Sep
21
comment Replace relational database with “poor” design in software product with non-relational DB?
I called nobody an idiot. Many flaws have understandable causes, but that doesn't make them right. NHibernate lazy loading at least requires the related objects to be mapped, which is not the case. Most NHibernate mappings only map single objects. Some time ago, some programmers would say: "don't use those bags in mappings, nobody can handle them!". For now, it gets slow from time to time, with occasional deadlock problems. But copying table contents into the metadata table, as it has been started, will likely run it into disaster.
Sep
21
comment Replace relational database with “poor” design in software product with non-relational DB?
Sorry, but "a few extra joins" would, in this case, result in an ugly cartesian product. NHibernate's fetch-mode="select" + batch-size or "subselect" proved much better! And it would be far better if multiple objects could be loaded using NHibernate's optimizations.
Sep
21
comment Replace relational database with “poor” design in software product with non-relational DB?
The pivoted metadata table is some kind of legacy, some older programs relied on it (and their successors still use it), and I heard repeatedly that "loading metadata in one row is faster than loading many rows, one for each metadata value". Not my opinion, or at least, the performance gain is rather irrelevant. Also, the pivoted table had been used for "poor man's search engine" (LIKE '%...%') until some time ago. BTW, I am in no responsible position for any "my" programmers I could give orders.
Sep
21
comment Replace relational database with “poor” design in software product with non-relational DB?
...And suddenly you notice that your 150 hp car, reduced to 20 hp, can't climb that steep mountain road. Are you going to repair it now? No, that's costly and might make further repairs necessary, or break it down completely! Instead, take out the backseats and spare wheel, fill in only as much gas as needed, to make it lighter. If that doesn't help, select a lengthy alternate route or load it on a truck, to carry it up the mountain! Problem solved!
Sep
21
comment Replace relational database with “poor” design in software product with non-relational DB?
This is exactly the prevalent attitude towards the system and it's flaws. So, what do you call a problem? A nominal 150 hp car which can actually deliver max. 20hp while consuming ten times as much gas as supposed to? But it's running, you don't need 150 hp, and you don't care for the costs of gas (imagine it would be really cheap!). So, no reason to change anything.
Sep
21
comment Replace relational database with “poor” design in software product with non-relational DB?
An object document should be read in a single request, instead of a relation based object, which has to be traversed, causing at least half a dozen additional queries for sub-entities and properties. Memory in database server is not currently a problem, neither disk nor RAM. Unfortunately, the "never change a running system" attitude greatly contributed to the problems, because design related problems got only quick workarounds and patches, but never lead to correction of the flawed design elements.
Sep
21
comment Replace relational database with “poor” design in software product with non-relational DB?
What I see is that the system had certain flaws for some time, but is about to go downhill now, mainly due to some client applications now demanding all required data in the "horizontal" metadata table (which is already copied and thus bad design). So many data tables will have their data represented in and copied to the "vertical" global metadata table (which is supposed to be for customized properties), and from there again copied to it's "horizontal" representation. Sometimes by trigger, sometimes by applications reacting on entity change events, themselves again changing the entities.
Sep
21
revised Replace relational database with “poor” design in software product with non-relational DB?
detail set vs. single object
Sep
21
revised Replace relational database with “poor” design in software product with non-relational DB?
added web services scope
Sep
21
revised Replace relational database with “poor” design in software product with non-relational DB?
code formatting
Sep
21
asked Replace relational database with “poor” design in software product with non-relational DB?
Jul
8
comment How to update ID=null values in table to incremental counter values?
This looks like the cursor solution I previously used. That one simply updated with an incrementing number, and it would have taken about 3 days in my case for about 20 million new IDs.
Jul
8
answered How to update ID=null values in table to incremental counter values?
Jul
2
awarded  Curious
Jun
28
asked How to update ID=null values in table to incremental counter values?
Jun
13
comment Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?
I finally managed to implement the above Union-Subselect syntax, since it was a very specific part of the code. Oracle now uses all my indices and has decent performance. Even SQL Server performs notably better (25%:75% showing exe plans for both queries), although it never fell back to the full-table-scan disaster seen in Oracle. I wonder if the databases can't do any better, or if it still was a statistics problem.
Jun
5
awarded  Commentator
Jun
5
comment Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?
Currently, the Tasks table has more than 300000 rows, ObjectAffectingTasks only 20 (to become more), where tasks were started on parent/container objects. Finally, the direct/indirect task ratio (obejctID in Tasks vs AffectingTasks table) may be 3 to 1. The number of tasks per object ID is usually 5 to 20. However, the number of rows in Tasks and also the left joined tables is quite huge.