As you know, I've been building a web app that does stuff for us.
I have a page that displays ticket data. The data is pulled from many different tables. So each time I want to view a different table, there's about 8 queries that have to run to pull all the data from each table. There is noticeable lag as each query is run, and the data populates the fields.
First question: Should I have one query with 8 inner joins pulling everything at once, or 8 different queries like I have now. I feel like the inner joins are better, but also didn't know if that was bad practice, as in, having too many joins.
Second question: If I just wanted to run more basic queries, that pulled every record from every table, then each field hits those query results to display data. I would only have to run each query once, then all the data is available regardless of which 'ticket' I wanted to look at, making changing tickets much faster. (cause you didn't know that. . .) Question is, where is that data stored? Is it local to the browser that's running the app, or on the AWS server that is hosting the app?
I know this has to be considered bad practice (pulling all records so they are in memory), but my record count is low enough that I can get away with it. Then as I learn more about this stuff, I can make changes later to do it better. If it's stored with the browser running the app, that's no good because the clients are on phones. But if it's local to the AWS server, then no big deal.
Web app questions
Web app questions
The 2nd question depends on your technology stack.
If all the data is for a given ticket, then using a single query that represents its data is good. If you have lookup tables that are pretty much always the same (possible status fields, technicians, etc) those should be cached in server memory and not requeried.
If all the data is for a given ticket, then using a single query that represents its data is good. If you have lookup tables that are pretty much always the same (possible status fields, technicians, etc) those should be cached in server memory and not requeried.
It's not me, it's someone else.
Web app questions
I rewrote the query with a bunch of INNER JOINS. While the initial load of the screen takes a few seconds longer, interacting with the data is much faster, and more stable. For some reason, the DB will sometimes refuse to answer a query. This leaves you with empty fields. Over all, a big improvement.
Next question:
When creating a ticket, I have to create records in multiple tables. Create tech assignment records, invoice records, attached document records, etc. Right now, I have multiple queries inserting into each table. Some are actually dependent on previous queries finishing. For instance, the first thing I do is insert the new ticket. This generates the ticketID. I then grab that ticketID and use it in all the other records as the common field. Sometimes, again because the DB I use is not 100% reliable, as is the app tool I'm using, it seems like the other queries will run before the ticket is created. This results in them using the previous ticket's ID. Obviously a problem. Plus there's the time when the DB will just refuse a query, and not create the other records that are needed. So I'm looking at transactions, which you mentioned in another thread. But I get the impression that a transaction has to be contained in a single query. Can a single transaction span multiple queries? If not, is it bad practice to have one query that inserts into multiple tables. Everything in one, I can do the BEGIN/COMMIT per the tutorials I've seen. None of them talk about doing it across multiple queries.
Next question:
When creating a ticket, I have to create records in multiple tables. Create tech assignment records, invoice records, attached document records, etc. Right now, I have multiple queries inserting into each table. Some are actually dependent on previous queries finishing. For instance, the first thing I do is insert the new ticket. This generates the ticketID. I then grab that ticketID and use it in all the other records as the common field. Sometimes, again because the DB I use is not 100% reliable, as is the app tool I'm using, it seems like the other queries will run before the ticket is created. This results in them using the previous ticket's ID. Obviously a problem. Plus there's the time when the DB will just refuse a query, and not create the other records that are needed. So I'm looking at transactions, which you mentioned in another thread. But I get the impression that a transaction has to be contained in a single query. Can a single transaction span multiple queries? If not, is it bad practice to have one query that inserts into multiple tables. Everything in one, I can do the BEGIN/COMMIT per the tutorials I've seen. None of them talk about doing it across multiple queries.
Web app questions
As is often the case, typing out the issue here causes rethinking stuff and thinking of different ways to search for help.
Ticket creation is now one query. I do NOT have it explicitly set as a transaction. I'm told that it is automatically considered one in postgresql. That being said. . . one of the queries will find a recently added document. As in, when I add a document, it creates the record with 0 as the ticketID, because the ticket hasn't been created yet, but the doc has been uploaded. When I create the ticket, part of the create query will update all document records with ticketID=0 to ticketID=[new ticket's ID]. If there were no documents updated, then the query returns 0 records updated. Would this trigger a transaction failure? Since no action was taken? I'm assuming no, since the query didn't fail, it just didn't find a match. Just want to make sure this isn't evidence of this query NOT being run as a transaction.
I was even able to figure out how to create multiple records from an array. Previously, I had a function that was iterate through the array, and call the query for each element. Now the array is unnesting in the VALUES of the query. No matter how I asked how to do this, the function was the way I kept finding. I stumbled on unnest while looking at other stuff.
I'm getting really good at this stuff.
Ticket creation is now one query. I do NOT have it explicitly set as a transaction. I'm told that it is automatically considered one in postgresql. That being said. . . one of the queries will find a recently added document. As in, when I add a document, it creates the record with 0 as the ticketID, because the ticket hasn't been created yet, but the doc has been uploaded. When I create the ticket, part of the create query will update all document records with ticketID=0 to ticketID=[new ticket's ID]. If there were no documents updated, then the query returns 0 records updated. Would this trigger a transaction failure? Since no action was taken? I'm assuming no, since the query didn't fail, it just didn't find a match. Just want to make sure this isn't evidence of this query NOT being run as a transaction.
I was even able to figure out how to create multiple records from an array. Previously, I had a function that was iterate through the array, and call the query for each element. Now the array is unnesting in the VALUES of the query. No matter how I asked how to do this, the function was the way I kept finding. I stumbled on unnest while looking at other stuff.
I'm getting really good at this stuff.
Web app questions
Having a hard time coming up with an easy way to handle timesheets.
Currently, a tech will log time attached to a ticket. They select a ticket, log in and out as needed while working on that ticket. This allows me to track time spent on any given ticket. Working great.
Then for payroll, I grab that time and calculate total hours, OT, etc.
We pay OT for any hours over 8 in one day, and/or over 40 for the week.
So I take the total hours for the day, subtract 8, left overs are OT, etc.
This all works well when any given shift is on the same day. In other words, if I start at 8pm and work till 11:30, break for lunch and start back up at 12:30am and work till 5, the first half of the shift is on day 1, second half on day 2. So for the week, the first half of each shift is paired with the previous night's second half of the shift. This messes up the OT calculations.
I'm currently about to set it up where I have to go to each time entry and assign it to the day I want the time to apply to. But I feel like there's some better logic/formula that can be used to calculate times than doing that manually.
Currently, a tech will log time attached to a ticket. They select a ticket, log in and out as needed while working on that ticket. This allows me to track time spent on any given ticket. Working great.
Then for payroll, I grab that time and calculate total hours, OT, etc.
We pay OT for any hours over 8 in one day, and/or over 40 for the week.
So I take the total hours for the day, subtract 8, left overs are OT, etc.
This all works well when any given shift is on the same day. In other words, if I start at 8pm and work till 11:30, break for lunch and start back up at 12:30am and work till 5, the first half of the shift is on day 1, second half on day 2. So for the week, the first half of each shift is paired with the previous night's second half of the shift. This messes up the OT calculations.
I'm currently about to set it up where I have to go to each time entry and assign it to the day I want the time to apply to. But I feel like there's some better logic/formula that can be used to calculate times than doing that manually.
Web app questions
So you need to track "shifts" not "days", is what it sounds like to me?
It's not me, it's someone else.
Web app questions
Ok. . . . but how? I guess I assign that when I create the ticket? Except it's possible for one ticket to have multiple days of work associated with it, some being day, some being over night shifts.