|
raminasi posted:If you remove all join predicates and WHERE clauses other than the ones that link tables together, does the result set contain the row you want? (Hopefully that query is tractable to run.) No. Only the row with the blank invoice value is in the data set.
|
#
?
Nov 30, 2022 01:52
|
|
|
|
| # ? Jun 10, 2024 13:03 |
|
|
NPR Journalizard posted:No. Only the row with the blank invoice value is in the data set. Sigh. Did more digging, and its a data issue, not a join logic issue, which puts it firmly in someone elses court and I dont need to worry about it any more. I dont know why I trust people who say they have checked things any more.
|
|
#
?
Nov 30, 2022 02:13
|
|
|
Hughmoris posted:Rookie question: Follow-up question, is there no way to backup these databases (sql server)? Dumping a small database to its create and insert commands is a fairly direct method of migration. Tooling introduces overhead but for large enough data sets becomes more efficient. ps https://stackoverflow.com/questions/811603/dump-of-sql-server-insert-data PhantomOfTheCopier fucked around with this message at 14:06 on Nov 30, 2022 |
|
#
?
Nov 30, 2022 14:04
|
|
|
Is there anyway to clean this up?SQL code:
|
|
#
?
Nov 30, 2022 18:39
|
|
|
kiwid posted:Is there anyway to clean this up? You could use a CTE: SQL code:
|
|
#
?
Nov 30, 2022 19:22
|
|
|
raminasi posted:You could use a CTE: Thanks, that's what I ended up doing.
|
|
#
?
Dec 2, 2022 18:00
|
|
|
Ok, next project... I've been tasked to create a complex electronic form for our contractors to fill out. Similar to something like the following:   There are several obstacles with something like this but here I'm focusing on one specifically which is that I have no idea how to store the submissions to these forms in the database considering the following: a) The forms will change over time. Questions and answers will be added or removed. b) The forms will need to be editable by the user. E.g. The form has changed and two days later the contractor tries to edit the form with questions now missing. Now, from a programming perspective, I can likely figure out the answer to option B which would be to version my views in the web app. However, from the storage perspective, I'm lost. I asked this question on a discord I'm in and the suggestion was to use a key-value store with questions and answers in their own tables and version the forms. At first this sounded like a good idea but the more I research the more I see people recommend against going down the EAV path. Then I got looking into document stores like MongoDB which I have zero experience with but I'm willing to learn. Though, before I go down this rabbit hole I wanted to get more feedback. What do you think? kiwid fucked around with this message at 20:49 on Dec 14, 2022 |
|
#
?
Dec 14, 2022 20:46
|
|
|
personally i wouldn't try to reinvent the wheel unless absolutely necessary, i'd see if microsoft forms + power automate + sharepoint list would work for your use case first assuming you have access to MS tools
|
|
#
?
Dec 14, 2022 21:01
|
|
|
kumba posted:personally i wouldn't try to reinvent the wheel unless absolutely necessary, i'd see if microsoft forms + power automate + sharepoint list would work for your use case first assuming you have access to MS tools I considered Microsoft Forms but the issue with that is the form is submit and forget and can't be modified later. In our case it would be adding lockout tagout close times, etc.
|
|
#
?
Dec 14, 2022 21:04
|
|
|
How fancy does it need to look? This sounds like it could be done with key-value (or question-answer) pairs with some kind of default question set stored in a separate table.
|
|
#
?
Dec 14, 2022 21:15
|
|
|
kiwid posted:I considered Microsoft Forms but the issue with that is the form is submit and forget and can't be modified later. In our case it would be adding lockout tagout close times, etc. that's what the sharepoint list is for - once the item is submitted all the details can land in a list, and with proper access management you can have someone access the list and update poo poo afterwards e.g., the form is just the intake. the list is where everything after intake happens
|
|
#
?
Dec 14, 2022 21:16
|
|
|
kumba posted:that's what the sharepoint list is for - once the item is submitted all the details can land in a list, and with proper access management you can have someone access the list and update poo poo afterwards I'll check this out again, thanks.
|
|
#
?
Dec 14, 2022 21:37
|
|
|
You could always build a bespoke form app. At least that way you earn yourself (and possibly someone else) a little job security!!! I’m not lying. I built a custom form app from the ground up at my old job. It was a MS SQL db, C# backend, and React frontend with a fancy UI and everything. It’s still around two years after I left for big tech and it puts food on the table for two or three full-time developers now! The real answer is that this “forms” thing is a common business request. I’m surprised there aren’t more robust solutions on the market, honestly. Using an out of the box solution is ideal if you can convince your stakeholders that’s the best course of action, but of course my stakeholders couldn’t be convinced because *reasons*. So off I went and built a thing. I’m gonna post about my experience but I want to first slap on a big huge disclaimer that creating a custom solution to this problem is a HUGE commitment. So don’t take this next post as me advocating for making your own bespoke solution.
|
|
#
?
Dec 15, 2022 07:24
|
|
|
Ok, here goes. Phone posting btw so give me grace! In my case, my stakeholders and their sub teams were already collecting all of this data, but each team was doing it in their own custom, non-reportable way (it was a HUGE mess). But, because they were already running their processes, we had a good sense for the requirements: - Processes were fluid, so the forms would have to be too. Teams might decide to start or stop collecting data at any point, and wanted to be able to edit the form definition to reflect this. - Users needed the ability to pre-fill form data before they had whatever meeting drove the finalization of the data (e.g. deciding what stoplight color to rate their project). And forms could be quite long. So they needed the ability to save forms as in progress and resume later. - Admins might edit form definitions for forms which had instances that were in progress, and the users needed to be able to update their form definition before submitting without transcribing all the data. - The submitted data had to be linked to discrete data elements and had to be reportable, because none of it was in the old system and it was driving the execs batshit crazy. I’m sure there’s something missing but yeah… the challenge was doing this without creating a monster of a database. What I eventually landed on was the following: - A data elements table, defining discrete data elements and their metadata (e.g. data type, default value, etc). Each data element got an ID. - A form, section, and items table - pretty basic data normalization. This held forms’ current structure. If the item was a question, it would be linked to a data element. It could also not be linked, and be something like a header or instructional text. My app got fancy with the ability to create col, row, and table based layouts with the items and other stuff, which was all stored as metadata in these tables. All of this was serialized as a JSON object and piped through to the react app, which rendered the form based on this metadata. - A responses table that held each form submission, with a FK back to the form table (which form) and some other FKs for stuff like which team/product this form was submitted for. It also had two columns for JSON data: the first held a serialized version of the form’s structure, and the second held a JSON object mapping data element IDs to response values. These were captured/overwritten whenever a form was saved as in progress or submitted. By capturing this, we were able to allow the current state of the form to change without creating a billion versioning rows everywhere. As long as we stuck to the same serialization format we could load up any form and view / edit it in the state it was in when submitted. The data was kept separately from the form structure so that we could update the form’s structure without impacting any of the values the user had filled out (this mostly happened on the front-end). - A data value table that had a compound key of the response and the data element, with a row for each discrete data point captured on that form. Separate columns for the different data types (bool, int, double, string, etc). Whenever a user submitted a form, we’d process the aforementioned data values JSON into this table. If the user was resubmitting the form with changes, we wiped and replaced the data for that submission. So, the flow was basically: - Admin user creates a form. Someone fills it out and submits. Response row gets added and full JSON of form structure and data gets saved in that row. Then, the JSON is parsed and each captured data value is put into the data value table. - Later, someone starts and saves an in progress form, but doesn’t submit. Admin changes the form. User reopens the form and gets a prompt that there is a new version of the form to update to. They choose to update and we pull the new form structure from the db and the app updates seamlessly, like they had been filling the new form out all along. They can choose to save or submit, or they can abort and revert to their last in-progress save. - Later, someone opens a form and changes data. Maybe it isn’t a current version of the form anymore, whatever. They update if they want, or they don’t, and then they resubmit. Again we save the form’s structure and data to the response table in JSON format, then parse the data and replace this response’s data in the data values table. There was a lot more nice-but-unnecessary complexity that I didn’t describe here, stuff like copy-forward data elements (so that a form could be pre-populated with or display the previous stoplight status) and conditional display / validation rules (e.g. only show this question if this other question was answered in this way, or only allow a value in this range). Happy to talk about any of that if this is at all interesting, but that’s all sugar on top. Not sure how much of this would have been possible without robust JSON support in MSSQL. It sure as hell wouldn’t have been anywhere near as easy. This scaled really well for our needs. For reference we had about 2-3k daily users, mostly project managers responsible for installing a suite of products at a customer site. There was something on the order of 20k discrete data elements and 400+ forms for everything from a monthly overall health report for their projects to forms that were filed out during product-specific oversight check-ins. Lots and lots of data, on the order of millions of rows of discrete data points, and we used the data to directly feed both in-app dashboards as well as ETL-based reports for execs. Whew.
|
|
#
?
Dec 15, 2022 07:51
|
|
|
Jotform has webhooks so you can kind of dump the results anywhere. Remember to store forms as a list of questions and answers so it’s not a schema change freakout when the form changes.
|
|
#
?
Dec 18, 2022 20:49
|
|
|
Ruggan posted:Whew. This sounds awesome but also incredibly complex. I'm not sure I fully understand it without seeing it in action though.
|
|
#
?
Dec 19, 2022 16:26
|
|
|
Yeah. Regardless of how it is designed, I think the simplest way to explain it at a super high level is: 1. Store the form layout and data with the response on save/submission. I liken this to putting a physical paper in a filing cabinet, where it becomes an immutable record of the submission. 2. Do whatever you need to do to ensure backwards compatibility with old form layouts (conversions, sensible default values for new form field metadata if you change your schema, etc.). That frees you from the need of storing anything other than current state in the normalized data structure.
|
|
#
?
Dec 19, 2022 16:54
|
|
|
Quick postgres question. I have a pretty simple CRUD service using Posrgres as a data store. I want to add a health check endpoint that essentially just ensure that the database is alive and responding to requests. On Oracle I could do something like "select 1 from dual" to handle this case and I'm curious what the best way to go about doing that on postgres is. My requirements are just that is have no side effects, not lock any tables while it is running, and work even on an empty database where I haven't set up any tables yet. Is "select 1" what I need? That seems to work but I'm not sure if there is some other more standard way to check.
|
|
#
?
Dec 29, 2022 22:44
|
|
|
That does verify the existence of an available client port but doesn't really tell you about "health". The immediate side effect is that it uses a client port, preventing another client from using it while you're making the request. It also provides no distinguishing information that the connection is to the expected server. At a very basic level, something from SHOW ALL could provide minimal validation. In general though consider https://www.postgresql.org/docs/current/monitoring-stats.html
|
|
#
?
Dec 31, 2022 00:28
|
|
|
SELECT 1 will work perfectly fine for what you want. Though, you can easily get some more information on the DB state than a simple yes/no, virtually for free, by querying a table that's not going to be normally modified (and which will therefore be cached into RAM), such as information_schema.tables to check if the schema is up to date.
|
|
#
?
Dec 31, 2022 00:29
|
|
|
Anyone here an Oracle DBA? If so, can you give thoughts on the work and what you think the future is for it? My understanding is that Oracle in general has a poo poo reputation but the US govt seemingly loves them some Oracle contracts and there appears to be lots of opportunity for the DBA work. Most of my current experience is analyst work with MSSQL and Postgres.
|
|
#
?
Dec 31, 2022 01:12
|
|
|
oracle the database can be migrated to postgres as long as you haven't gone too hardcore on oracle-specific features. but oracle the entire erp platform is not going to be migrated to anything open source anytime soon. there are lots of large enterprises that are sufficiently invested in oracle's stack that the database itself should be around for a looong time. it's also a good database, like it performs well, it's just that every design decision makes you throw up a little
|
|
#
?
Jan 1, 2023 21:12
|
|
|
DELETE CASCADE posted:oracle the database can be migrated to postgres as long as you haven't gone too hardcore on oracle-specific features. but oracle the entire erp platform is not going to be migrated to anything open source anytime soon. there are lots of large enterprises that are sufficiently invested in oracle's stack that the database itself should be around for a looong time. it's also a good database, like it performs well, it's just that every design decision makes you throw up a little Thanks!
|
|
#
?
Jan 2, 2023 23:16
|
|
|
For sql server: Any idea what would cause a merge that uses update when matched and insert when not to have varied performance? It is a stored procedure that randomly slows down. Early in day, I can do 20k in 1.2 seconds. Later in the day the query takes 10 minutes. For the exact same data set. As I was testing front end software. I could run the query back to back when test without any issues There were no other major queries running. The matching columns are all in a single non clustered index. I take the json data in drop it into a temp table then merge Update/ insert I could understand if it was Always slow. But it seems to happen randomly
|
|
#
?
Jan 20, 2023 04:26
|
|
|
What hardware is the database running on? Dedicated used for nothing else, or shared with other things on a cloud or other VM based hosting? It could be something on a different virtual machine on the same hardware taking the compute resources, or it could be some other job running on the OS doing it.
|
|
#
?
Jan 20, 2023 06:34
|
|
|
It's a windows vm. It is a dedicated sql server, however there are other instances on the server. None of the other instances seem to be affected at the times when the stored procedure in question is slow
|
|
#
?
Jan 20, 2023 12:45
|
|
|
Do you get different query plans at the different times? Is anything else accessing the table when you get the bad behaviour?
|
|
#
?
Jan 20, 2023 14:17
|
|
|
What's the query analyzer say?
|
|
#
?
Jan 20, 2023 14:21
|
|
|
Neither showed any issues. As when I ran them it the stored procedure was running fast. There doesn't seem to be a set time for it to slow down. So outside of just spending the whole day watching both the os performance and dbs stats I'm not sure what else I can look for. This morning I pushed 20k rows from 3 different clients at the same time from a single work station trying to force a bottle neck. No issues at all
|
|
#
?
Jan 21, 2023 02:54
|
|
|
the query store might help - it's theoretically good for recording and finding query regressions. you might need to turn it on, if it's not enabled already. i've always found it a pain to query, but that's probably because i don't use it much at all (our workload isn't well suited)
|
|
#
?
Jan 21, 2023 21:04
|
|
|
redleader posted:the query store might help - it's theoretically good for recording and finding query regressions. you might need to turn it on, if it's not enabled already. i've always found it a pain to query, but that's probably because i don't use it much at all (our workload isn't well suited) Speaking of Query Store, I stumbled upon this video yesterday that might be helpful to those exploring it: https://www.youtube.com/watch?v=WBy4FPlL0EA&t=1s
|
|
#
?
Jan 21, 2023 21:53
|
|
|
Apparently it was my own stupidity. I left off the primary key. Once did that and created an index with the primary key and uuidColumn it worked with no issues. Thanks
|
|
#
?
Jan 23, 2023 17:01
|
|
|
I may be chasing down something that just isn't possible with SQL (T-SQL, to be specific) but I'm trying to disentangle records with a '&' delimited between two first names, and then create two rows from that process, one for each person. It's...almost working, but I'm not playing horseshoes so I'm not happy with almost. My sample data is as follows: code:SQL code: There are more cleanup steps I have planned but I'm also starting to think at this point that maybe I want to route my data through some python code to manipulate it instead? Ultimately my goal is to have a result table that has an entry code:Am I just barking up the wrong tree with SQL for this kind of thing?
|
|
#
?
Jan 25, 2023 17:52
|
|
|
Lib and let die posted:I may be chasing down something that just isn't possible with SQL (T-SQL, to be specific) but I'm trying to disentangle records with a '&' delimited between two first names, and then create two rows from that process, one for each person. This gives you the output you're looking for, not sure if it's quite what you're looking for because I don't know what other cleanup steps you're going to do: code:
Just-In-Timeberlake fucked around with this message at 18:58 on Jan 25, 2023 |
|
#
?
Jan 25, 2023 18:33
|
|
|
Thanks! Most of the cleanup I need to apply is covered by the replace and trim operators so that's one less checkbox for me! Is there a particular functional reason for using a table variable rather than SELECTing right from my already-populated Contacts table?
|
|
#
?
Jan 25, 2023 19:00
|
|
|
Lib and let die posted:Thanks! Most of the cleanup I need to apply is covered by the replace and trim operators so that's one less checkbox for me! Is there a particular functional reason for using a table variable rather than SELECTing right from my already-populated Contacts table? I just created that to have a dataset to work with, you don't need it. just replace the @tbl with your table name, using the alias is up to you code:Just-In-Timeberlake fucked around with this message at 19:07 on Jan 25, 2023 |
|
#
?
Jan 25, 2023 19:03
|
|
|
i know it's been solved, but if you're wondering, the reason you're getting a bunch more rows than you expect is because this statement doesn't have a where clause: code:
|
|
#
?
Jan 26, 2023 08:00
|
|
|
Much like where all stupid questions come from, I got asked to do a "small" coding project for a friend. Mind you haven't done actual database design since college. My friend has a martial arts business and he wanted a self check in thing for his school to replace his current paper tracking system, and doesn't want to pay money for one. I know generally how to accomplish all of it by basically pushing SQL commands to a database from a website, my problem is I don't know how I'm supposed to design the keeping track of attendance part of the database. My googling sees that the attendance thing is a very common question, but generally all the answers still don't make any sense to me, the one that was closest to making some amount of sense was the following schema member_table [contains member info, with member_id primary key] date_table [a prefilled out table containing all the dates a class will be 260 entries in my case] attendence_table [relates the previous two tables together] I think it'd work, but my problem is is that whoever is maintaining this would have to go back and "refill" the date table every year? There a better way of accomplishing this?
|
|
#
?
Feb 2, 2023 23:12
|
|
|
Maybe instead of using a table pre-populated with dates, use a more sparse implementation using a table with just member id and a date where they attended? Then a list of class dates (with at least one attendee) could be built with “select distinct date from …” and present/absent lists could be built from that if you need more than just dates present. After all, if they weren’t present you can assume they were absent so the existence of present dates implies the absent dates.
|
|
#
?
Feb 3, 2023 00:01
|
|
|
|
| # ? Jun 10, 2024 13:03 |
|
|
Worry more about data integrity than "optimization". There's basically no way in 2023 to hit a speed issue in the scenario you've described. A table of members, yes. Need that. (id,first,last). Things like rank/weight/belts/awards go somewhere else. In official record keeping, there must be an entry. Attendance would be (timestamp,member,entry). Honestly I'd recommend entries of P/A/X at the least (present, absent, excused). For example an absent member might still be charged, whereas an excused member might be allowed to attend at a later date without penalty. And, yeap, the UI stuffs a letter in there. (A beginner++ might use an enum field instead of an open text field.) Also, what happens when someone leaves before the 50% mark? Updated to absent? Excused? Or is that present with a notation elsewhere? More to the point: The UI needs to show all members, not just those remaining to be checked in. Despite having a timestamp field, it's probably a bad idea to think of it as a log. Make it (date,member,time,entry) unique on (date,member). If you ever have two classes on the same day, however, or two choices for a class on different days, this simple approach won't work. For that you need class (id,date,time,course,description,...), possibly "registration", and then attendance is (class,member,time,entry). Shrug, start small.
|
|
#
?
Feb 3, 2023 01:04
|
|