|
Ihmemies posted:Actually what the gently caress these tasks are.. This sounds like it's asking "have you read the documentation/lecture notes closely enough to find the CONNECT BY operator"
|
# ? Nov 12, 2023 01:26 |
|
|
# ? May 4, 2024 10:23 |
|
Anybody at PASS this week? I’ll be the guy forcing myself to socialize cause my boss said to ‘be sure to network and have fun’.
|
# ? Nov 15, 2023 02:06 |
|
GigaPeon posted:Anybody at PASS this week? I’ll be the guy forcing myself to socialize cause my boss said to ‘be sure to network and have fun’. I am not but I'd really like to be there. More importantly, I'd like to be on a team where people want to go to those types of events. Yes, I am a nerd.
|
# ? Nov 15, 2023 02:12 |
|
Hughmoris posted:I am not but I'd really like to be there. More importantly, I'd like to be on a team where people want to go to those types of events. Yes, I am a nerd. Yeah I’m glad I’m here. The other DB nerds couldn’t make the time for it with their own stuff going on. It’s just regular goon-style social anxiety. Like going to a wedding where you don’t know anyone. GigaPeon fucked around with this message at 02:28 on Nov 15, 2023 |
# ? Nov 15, 2023 02:18 |
|
GigaPeon posted:Yeah I’m glad I’m here. The other DB nerds couldn’t make the time for it with their own stuff going on. It’s just regular goon-style social anxiety. Like going to a wedding where you don’t know anyone. Yeah, flying solo at those things can always be a little intimidating at the start. Just remember that mostly everyone is there because they want to talk cool tech with like-minded strangers, so chat them up. Let us know your thoughts on the event as a whole, after things wrap up. I'm tempted to try and make the SQLBits 2024 in Scotland.
|
# ? Nov 15, 2023 02:42 |
|
It's in England though? https://sqlbits.com/news/sqlbits-2024-unveiled/
|
# ? Nov 15, 2023 02:46 |
|
MrMoo posted:It's in England though? Thanks. For some reason I had it in my had as Edinburgh. England could be cool, too.
|
# ? Nov 15, 2023 02:51 |
|
Hughmoris posted:England could be cool, too. (a thousand goons hurtle into the thread at breakneck speed, eager to tell you about all the ways England is not cool)
|
# ? Nov 15, 2023 09:49 |
|
My takeaway so far is that I want Redgate’s toys and gizmos but my boss probably won’t buy them. This thing is hosted by Redgate though so I’m probably being brainwashed. Also there’s so much AI poo poo. How are people just cool with hooking up their environments to the internet and sending it to a third party? GigaPeon fucked around with this message at 05:02 on Nov 17, 2023 |
# ? Nov 17, 2023 04:58 |
|
GigaPeon posted:My takeaway so far is that I want Redgate’s toys and gizmos but my boss probably won’t buy them. Yeah, I get tired of reading random articles about AI so I can't even imagine how much that crap is getting pushed there. Also, are you seeing much of anything Postgres there? I saw they had a few sessions lined up on the topic but I imagine they're shoved off in a corner somewhere.
|
# ? Nov 17, 2023 05:23 |
|
There’s definitely non MS stuff going on here. Postgres even has a vendor booth. We’re a MS shop though so I haven’t dug too deep. I feel like I could give some of these talks. I figured I could learn some cool tricks at the Git + SQL talk so I wouldn’t have to do weird things to version views and procs but they’re just like ‘here’s what a branch is’ Cool thanks. Also at a whiskey based after party and found someone to talk about smoked meats with so that helps with anxiety. GigaPeon fucked around with this message at 07:04 on Nov 17, 2023 |
# ? Nov 17, 2023 06:57 |
|
GigaPeon posted:Also there’s so much AI poo poo. How are people just cool with hooking up their environments to the internet and sending it to a third party? Look, we have to take risks, it's part of doing business. At least, that's what Scott Brighton, CEO of Bonterera, parent company of NGP VAN, the Democrat Party's purpose-built voter engagement and fundraising platform has to say about leveraging AI. God I can't wait 'til '24 kicks into high gear and the bottom falls out.
|
# ? Nov 17, 2023 13:48 |
|
GigaPeon posted:My takeaway so far is that I want Redgate’s toys and gizmos but my boss probably won’t buy them. Tell me more about the latest redgates gizmos - I used to use their sql toolbelt in another life, and their dependency sniffing tool was a legit lifesaver for the end-of-lifed products I was extracting data from at the time, but I never really got into much else of theirs.
|
# ? Nov 19, 2023 18:37 |
|
mortarr posted:Tell me more about the latest redgates gizmos - I used to use their sql toolbelt in another life, and their dependency sniffing tool was a legit lifesaver for the end-of-lifed products I was extracting data from at the time, but I never really got into much else of theirs. Here's what I was looking at SQL Prompt: https://www.red-gate.com/products/sql-prompt/ People at the summit I was talking to were big on this one. Saying it saved them 100s of hours. Refactoring and templates and macros. Let's you define or pick a house style. Of course now they're using ChatGPT integration for some stuff. "Make this sp more efficient". Talking to a Redgate guy at an afterparty, he said that they're planning on making it so you can limit training to your own data for security concerns. I've been using their online formatter for a long time (https://www.red-gate.com/website/sql-formatter) so I was interested in the more robust features of this Flyway: https://www.red-gate.com/products/flyway/ Devops for DBs. I've always wished that SSMS had some sort of Git integration and that seems to do that as well as automating deployments. I'm mostly an Enterprise Apps guy so I don't build a lot of my own stuff, so I'm sure I won't get to play with it a lot. Test Data Manager: https://www.red-gate.com/products/test-data-manager/ This is their new thing. Generate large sets of test data based on your own criteria. Also data anonymization/sanitization stuff for PII.
|
# ? Nov 21, 2023 17:59 |
|
GigaPeon posted:SQL Prompt Flyway is pretty nice, although you have to do the work to make it powerful. That is an impressive choice of styling.
|
# ? Nov 21, 2023 18:47 |
|
i use SQLPrompt and it is indeed very handy
|
# ? Nov 21, 2023 19:14 |
|
Commas before, you filthy animals
|
# ? Nov 21, 2023 19:15 |
|
i have successfully convinced my team to start all of their where clauses with WHERE 1=1 and sqlprompt is very good at making things like this easier
|
# ? Nov 21, 2023 19:18 |
|
GigaPeon posted:Here's what I was looking at a few of the older members of my team use this and swear by it. it looks ok but i do balk at the concept of having to pay for plugins to fix the barebones functionality of SSMS. datagrip is in the same ballpark price wise and is generally just way more full featured and integrated; it’s the nicest way i’ve found to write sql.
|
# ? Nov 21, 2023 20:14 |
|
SQL Prompt is really handy for refactoring but I don't know that I'd pay redgate prices just for that. We were always told that its use was non-optional because of stuff like detecting UPDATE or DELETE statements without WHERE clauses and less about making our code pretty.
|
# ? Nov 21, 2023 20:24 |
|
I always found SQL Complete to be better than SQL Prompt, with more features and better formatting at a lower price. They're both great though. I don't work for them or anything but I was at a company where all of us were using SQL Prompt and I personally evaluated and compared it to SQL Complete. After my evaluation was done we made the switch. This was a few years ago so things could have changed since then.
|
# ? Nov 21, 2023 20:28 |
|
SQL Prompt was very good, but I swear with each update it’s gotten worse. I spend an amount of time I’m not happy to be spending fighting it some days. The snippets manager is a huge timesaver for sure, but it does some real dumb things sometimes. For example, we have a table called Customers, and there is a built in snippet for creating a user that you trigger by typing in ‘cu’, which is fine! Except when I type in ‘Select customerId from cu’ and hit tab expecting the table name to be inserted and not the code to create a new db user, because that’s totally a thing I want in a from clause.
|
# ? Nov 21, 2023 22:36 |
|
Thanks all, interesting to see the inclusion of chatgpt although I guess it was inevitable.kumba posted:i have successfully convinced my team to start all of their where clauses with I thought I was the only person who did the 1=1 thing. So handy.
|
# ? Nov 21, 2023 23:53 |
|
Lib and let die posted:SQL Prompt is really handy for refactoring but I don't know that I'd pay redgate prices just for that. We were always told that its use was non-optional because of stuff like detecting UPDATE or DELETE statements without WHERE clauses and less about making our code pretty. I always write my UPDATE/DELETEs as SELECT statements first because of that one time. Thanks for all the pointers to Prompt alternatives. Not sure the powers that be will shell out $200/head/year. GigaPeon fucked around with this message at 00:32 on Nov 22, 2023 |
# ? Nov 22, 2023 00:15 |
|
Lib and let die posted:Commas before, you filthy animals Sincerely appreciating this way of coping with dogmatic intolerance of trailing commas in TYOOL 2024.
|
# ? Nov 22, 2023 00:52 |
|
lazerwolf posted:I’d be in for a data thread as well. Transitioning my role from Full stack developer to more of a Data Engineering role so I’m interested in discussing more. A Data Engineering thread is finally upon us, huzzah! https://forums.somethingawful.com/showthread.php?threadid=4050611&pagenumber=1#lastpost thanks to monochromagic!
|
# ? Dec 29, 2023 15:05 |
|
i have a crazy query that I need a bit of help optimizing. most of it is fast, but there's one piece of it that is slowing it down to a screeching halt Basically I have an endpoint that is returning me a paginated array of posts that belong to threads, which themselves belong to forums. I want to get the users active ban. This all works fine. The part that's slowing this down is the "author_total_posts" because I want to return the count of every single post the user made JavaScript code:
SQL code:
Hopefully this makes sense. I can provide more information about the tables if need be but yeah the gist is just that last LEFT OUTER JOIN takes forever to get the total post count for each post author that's being returned in a list of posts teen phone cutie fucked around with this message at 02:03 on Jan 15, 2024 |
# ? Jan 14, 2024 23:54 |
|
is that absolutely the smallest example you can create that exhibits the undesired behaviour? you refer to "that second LEFT OUTER JOIN" but then you refer to it as a query that counts posts. which appears to be the third LEFT OUTER JOIN in that query; the second "lateral" one. I don't understand why you need to use this fancy LATERAL feature that I've never encountered before; why do you need to do that rather than just put the join logic in the ON clause? What, if anything, is different if you do? (this applies to both "lateral" joins)
|
# ? Jan 15, 2024 01:45 |
|
In most cases where you refer to multiple instances of a table you give each instance a unique alias, but you didn't do that for "threads". It's referenced in the outermost query and in the counting query as "threads". I can't imagine that "threads" in the counting query could sensibly refer to anything but the most local one, but for consistency if nothing else you might as well make sure.
|
# ? Jan 15, 2024 01:49 |
|
Hammerite posted:is that absolutely the smallest example you can create that exhibits the undesired behaviour? yep sorry i meant the third. The lateral thing I picked up from stackoverflow, so I'm not sure if there's an easier way to accomplish what I want. SQL is not my biggest strength. As I understand it, the reason I need the lateral subquery is because the main query is getting a page of posts. So let's say for example the query is getting page 301 of the "SELECT * FROM Questions WHERE Type = 'Stupid'" thread. BUT I want to also get all the posts you've made on SA total and list it under your avatar. So in your case you have "14748" posts and I need to get that from all threads that are not deleted. So the thread in the outer-most query is just this thread, but in the lateral subquery, it's all threads you've posted in that are not deleted Hammerite posted:In most cases where you refer to multiple instances of a table you give each instance a unique alias, but you didn't do that for "threads". It's referenced in the outermost query and in the counting query as "threads". I can't imagine that "threads" in the counting query could sensibly refer to anything but the most local one, but for consistency if nothing else you might as well make sure. so you're saying make 2 different aliases for each, the outer and the subquery? teen phone cutie fucked around with this message at 02:22 on Jan 15, 2024 |
# ? Jan 15, 2024 02:03 |
|
Hammerite posted:is that absolutely the smallest example you can create that exhibits the undesired behaviour? When I remove the last outer join, the query goes from 2 seconds to 100ms, so I'm 100% certain it's the problem. Unless, you're suggesting I try something else?
|
# ? Jan 15, 2024 02:05 |
|
teen phone cutie posted:When I remove the last outer join, the query goes from 2 seconds to 100ms, so I'm 100% certain it's the problem. Unless, you're suggesting I try something else? How does that last query in the outer join run by itself for a single poster id or all posters since you're using it that way? Have you run an explain plan on that? e: Oh, just read what that lateral join does, that's likely the problem. Don't see why you can't just use a standard outer join here with grouping, that should still work with pagination. RandomBlue fucked around with this message at 02:45 on Jan 15, 2024 |
# ? Jan 15, 2024 02:40 |
|
teen phone cutie posted:When I remove the last outer join, the query goes from 2 seconds to 100ms, so I'm 100% certain it's the problem. Unless, you're suggesting I try something else? What's EXPLAIN say?
|
# ? Jan 15, 2024 02:53 |
|
McGlockenshire posted:What's EXPLAIN say? Yeah, in my limited experience this is the first place to start when you want to make a query run faster. Look at the executed query plan. Share here if you're able to.
|
# ? Jan 15, 2024 02:55 |
|
Not directly answering your question, but in many cases where something turns out to be too expensive to compute on every single page load, the right answer is to store the computed result somewhere and update it every time it changes.
|
# ? Jan 15, 2024 03:28 |
|
use a separate query to get the total count. less to do means less chance the execution engine will gently caress you over
|
# ? Jan 15, 2024 07:23 |
|
Jabor posted:Not directly answering your question, but in many cases where something turns out to be too expensive to compute on every single page load, the right answer is to store the computed result somewhere and update it every time it changes. This is probably the most practical solution to your immediate problem. Denormalise by storing a user's number of posts as a column in the User table. If it's important that the number be accurate and you're worried that you'll miss cases where it should be updated, there are strategies for dealing with that (triggers, scheduling tasks to update the column) teen phone cutie posted:so you're saying make 2 different aliases for each, the outer and the subquery? I don't think so. I mean that where you have "INNER JOIN threads" in the outer query, to be consistent with your practice for other tables it should have been "INNER JOIN threads AS threads_1" and where you have "INNER JOIN threads" in the subquery it would be "INNER JOIN threads AS threads_2". What happens if you replace the problematic JOIN with this: code:
I only just noticed that the outer query has a GROUP BY clause, and I don't understand why that is there or why those specific columns are the grouping ones. If it's necessitated by the uses of Group_concat in the outer query, doesn't it make more sense to put that grouping in a subquery defining offenses_1?
|
# ? Jan 15, 2024 10:07 |
Or you could put the posts per user per thread logic into a CTE which is easy to join with.
|
|
# ? Jan 15, 2024 11:04 |
|
McGlockenshire posted:What's EXPLAIN say? Hammerite posted:This is probably the most practical solution to your immediate problem. Denormalise by storing a user's number of posts as a column in the User table. If it's important that the number be accurate and you're worried that you'll miss cases where it should be updated, there are strategies for dealing with that (triggers, scheduling tasks to update the column) lemme give this stuff a shot when i get some time. will report back!
|
# ? Jan 16, 2024 00:41 |
|
|
# ? May 4, 2024 10:23 |
|
Can someone help me understand what is happening in this WHERE clause?SQL code:
Does this entire mess just reduce down to v.contract_nbr >= 0? kiwid fucked around with this message at 20:02 on Jan 31, 2024 |
# ? Jan 31, 2024 19:59 |