SQL - easiest way to keep DRY
-
@Karla said in SQL - easiest way to keep DRY:
Performance is not my priority.
Yup, but I suspect so isn't a performance regression.
Anyway, I don't know how large your dataset is (in % of work memory on your server). And this matters a lot. You can have peak speeds and nothing to worry, and one day under a particular load the server has an irresistible urge to flush your temporary table to disk. You and the other clients will see that all of a sudden TPS numbers drop southwards and hit the bottom very hard, and it's kinda hard to debug because your code is not the direct source, it's the just right mix of circumstances.
But maybe this won't happen. I don't know. :)
-
@wft said in SQL - easiest way to keep DRY:
@Karla said in SQL - easiest way to keep DRY:
Performance is not my priority.
Yup, but I suspect so isn't a performance regression.
Anyway, I don't know how large your dataset is (in % of work memory on your server). And this matters a lot. You can have peak speeds and nothing to worry, and one day under a particular load the server has an irresistible urge to flush your temporary table to disk. You and the other clients will see that all of a sudden TPS numbers drop southwards and hit the bottom very hard, and it's kinda hard to debug because your code is not the direct source, it's the just right mix of circumstances.
But maybe this won't happen. I don't know. :)
Good point.
These aren't huge datasets and I'm not bringing back a lot of data.
-
We had the same problem years ago: A bunch of SPs that did basically the same, only slighty different (different JOINs, different GROUP BYs). And every time there was a new requirement, we had to change all SPs.
Now we have one huge-ass 3000 line SP that does dynamic SQL.
Which actually is not as bad as it sounds, performance-wise. It still uses the same query plans underneath as with the separate SPs.(Plus this is the app I mentioned months ago with the 12 dedicated SQL servers each running 64 cores, 128 gigs of RAM and RAID 10 on PCIe-SSDs, which doesn't hurt
)
-
Here is what I ended up doing:
The master SP and and a couple views does all the common stuff putting the pool of data into a temp table.
All the specific sps do now is using CTEs perform all the functions and filters (I optimized the order of this so functions are performed on a small subset of data) and then the ids of the results are put into another temp table.
The master sp returns results by using joining the two temp tables.
Lines of code dramatically reduced and actually decent performance.
It took longer than I planned but it was far more interesting to work on.
-
@ChrisH said in SQL - easiest way to keep DRY:
Now we have one huge-ass 3000 line SP that does dynamic SQL.
We have like seven of those.
-
@Yamikuronue I re-wrote most of ours.
-
@ChrisH Are you a Scientologist and are talking about suppressive persons?
-
@lucas1 Yes. Because this thread is all about the Scientology of Queen Lovers.
-
@ChrisH An SP is a "Suppressive Person" apparently. It was a shite joke.
-
@lucas1 Yes. Yes, it was.
-
Well, this is an interesting thread derailment.
And by, interesting, I mean,
-
I think this is the most appropriate thread for this, even though it's only tangentially related.
I'm collapsing several groups of tables the differ only in the the GUI category prefix is (i.e. Whether it's an A or a B or a S). So good, so far.
Then I come across a table that's essentially describing groups of text labels (positions, font name, etc) for printing formats. I'm thinking to myself, we're not displaying these bits individually, searching, mathetising, or anything, it's going to end up glomming together into a JSON when sent out anyways, why are we dedicating many rows and columns when it should be just a blob with maybe some meta fields?
Can someone play devil's advocate?
Current code (as it stands) can be found here:
concrescent/cm2/lib/database/badge-artwork.php at master · Tsaukpaetra/concrescent
CONcrescent is the fandom convention registration system. - Tsaukpaetra/concrescent
Effectively, I'm going to end up with "id, format name, data, some meta maybe"
-
@Tsaukpaetra You bring me back to this thread and can't help but
that not much has changed in the last 5 years.I probably can do many things more efficiently and do my best to make appropriate functions/sub-stored procedures.
Each report is still doing a shit ton of work. <snark> If it wasn't all in CRM it could be extremely fast and broken down to a couple stored procedures</snark>
Today, I do not think I have mentioned how much I hate CRM.
I'm not a fan of SSRS because I love SQL and I feel like SSRS makes me ask SQL to do things it really doesn't want to do. And I really don't want to do it to SQL
I feel dirty.
I just don't know how to do it better.
-
@Tsaukpaetra said in SQL - easiest way to keep DRY:
Then I come across a table that's essentially describing groups of text labels (positions, font name, etc) for printing formats. I'm thinking to myself, we're not displaying these bits individually, searching, mathetising, or anything, it's going to end up glomming together into a JSON when sent out anyways, why are we dedicating many rows and columns when it should be just a blob with maybe some meta fields?
Why is that even in the database? Yes, you can keep such things in there, but why when all it does is sit there and not participate in queries or even be updated all that often? (If it is being updated frequently, that would be both a good reason for having it in the DB and
at the same time, if I guess the application domain correctly.)Things go in the DB if they need to be modifiable or they need to participate in queries (or constraints, or foreign keys).
-
@dkf said in SQL - easiest way to keep DRY:
Why is that even in the database?
I don't really know. Well, the undergoing reason for it being in the database is not needing to write icky filesystem code, but after that I think the "well it looks like normalized data so why not expand things into columns?" concept came along.
@dkf said in SQL - easiest way to keep DRY:
even be updated all that often?
In theory you can piece-meal update the bits of the labels? But it's not like the bits are shared across labels so the granularity isn't really needful.
@dkf said in SQL - easiest way to keep DRY:
Things go in the DB if they need to be modifiable or they need to participate in queries (or constraints, or foreign keys).
Yeah, that's why I'm just moving the set of rows and columns into a blob and treating it like the unit of data it is. The main reason I'm not putting it into an actual file is it's easier to have it all in one database than to add more stuff to put it into (for example) S3 buckets. Well, for the half-dozen files that will be held there, if it were an order of magnitude larger I might rethink but for this application the difference in performance should be negligible...
-
@dkf said in SQL - easiest way to keep DRY:
@Tsaukpaetra said in SQL - easiest way to keep DRY:
Then I come across a table that's essentially describing groups of text labels (positions, font name, etc) for printing formats. I'm thinking to myself, we're not displaying these bits individually, searching, mathetising, or anything, it's going to end up glomming together into a JSON when sent out anyways, why are we dedicating many rows and columns when it should be just a blob with maybe some meta fields?
Why is that even in the database? Yes, you can keep such things in there, but why when all it does is sit there and not participate in queries or even be updated all that often?
It is part of the display. So it is queried to display.
(If it is being updated frequently, that would be both a good reason for having it in the DB and
at the same time, if I guess the application domain correctly.)The survey is intended to be dynamic. We may only use it once a year, but it likely changes every year.
I'm sure there is a better way. But I was responsible for the database end and it seemed easier for those working on the front end.
Things go in the DB if they need to be modifiable or they need to participate in queries (or constraints, or foreign keys).
They do.
ETA: Oh you weren't asking me.
-
@Karla said in SQL - easiest way to keep DRY:
ETA: Oh you weren't asking me.
I was like "Hrm, I think she's talking over me, but I'm not quite sure..."
