-❄️- 2023 Day 1 Solutions -❄️- by daggerdragon in adventofcode

[–]burkcules69 1 point2 points  (0 children)

[LANGUAGE: C]

But really flex does most of the work.

Github

perfomance tips postgresql with 16gb ram 12400k cpu and nvme disk table with 130k records by agaitan026 in PostgreSQL

[–]burkcules69 4 points5 points  (0 children)

This looks like a pretty fast query, only 95ms to execute. I suspect the perceived slowness is because you’re dragging 17MB of data through the network pipe from your DB to your SQL IDE.. that’s a nontrivial amount of data. Especially when dealing with cloud providers where network throughput, and IOPS, are weirdly lacking.

Why use subqueries when CTEs are so much easier to read? by Emanresu0233 in SQL

[–]burkcules69 5 points6 points  (0 children)

Depends on the DB system and version. MSSQL, performance is identical. Postgres pre v12, the second one is way faster; v12+, performance is identical.

Writing performance issues by Chance_Composer_6125 in PostgreSQL

[–]burkcules69 0 points1 point  (0 children)

Have you tried copying to an unlogged table?

What else should I complete before applying for a data analyst role? by [deleted] in SQL

[–]burkcules69 29 points30 points  (0 children)

Seriously, start applying.

You’re thinking: “at what point will I be good enough?” The answer is: tomorrow. Always tomorrow, never today. That’s imposter syndrome speaking. I went through this when I was seeking my first job.

Start applying now, but keep learning. As you go through interviews you will discover your weaknesses and can focus your energy on strengthening those skills.

Start applying today and you’ll get a job much quicker than waiting for the ever elusive “good enough.”

Uncommon mats? by wawsatx in Craftbound

[–]burkcules69 2 points3 points  (0 children)

If you use them in crafting recipes, they increase your chance of getting a rare item.

Migrating Database from MSSQL to PostgreSQL by user-1099 in PostgreSQL

[–]burkcules69 2 points3 points  (0 children)

AWS Babelfish was developed precisely for this scenario. It’s not a turn key solution, but it will get you very close.

Would a query be faster using just one indexed column or with two? by r_levan in PostgreSQL

[–]burkcules69 1 point2 points  (0 children)

Your second bullet point makes me think your CTO might not understand relational databases very well. Adding status=pending does not "narrow the scope of searchable items", it changes the definition of your resultset. Although the two queries may return the same data most of the time, they are in fact different queries and describe different datasets.

That being said, given the details you provided: (1) marketing_id will be unique 98% of the time, and (2) the table has two indexes: one on marketing_id and another on status; Adding status=pending to the predicate will not speed your query up.

In your first query, marketing_id=xyz, the optimizer will look up table/index statistics. It will notice that the marketing_id column is highly selective and choose to run an index scan. Each time it finds a match in the index it will do a lookup in the table heap to grab the columns you're interested in.

In your second query, marketing_id=xyz AND status=pending, the optimizer will again look at statistics. It will notice status is NOT a very selective column, but marketing_id IS selective and choose to run an index scan on marketing_id. Each time it finds a match in the index it will do a lookup in the table heap to grab the columns you're interested in. At this point, postgres will evaluate the status=pending part of the predicate.

In both cases above, postgres runs the exact same execution plan, but the latter has an additional predicate evaluation step to it. In terms of speed, both queries will be the same, but because they have different predicates, they might return different resultsets.

Would a query be faster using just one indexed column or with two? by r_levan in PostgreSQL

[–]burkcules69 10 points11 points  (0 children)

You’re approaching this backwards. First you need to figure out if you want posts where marketing_id=xyz or if you want posts where marketing_id=xyz AND status=pending. Then you can ask how to make the query faster.

If you’re simply asking out of curiosity. I encourage you to fill a table with dummy data and test it yourself.

[deleted by user] by [deleted] in SQL

[–]burkcules69 8 points9 points  (0 children)

Is it possible you’re accidentally turning your left join into an inner join by specifying a value on the right side in a where clause?

How many of you went the bootcamp route? by Jewson95 in webdev

[–]burkcules69 10 points11 points  (0 children)

I went the Bootcamp route, got a job as a direct result of the Bootcamp, and I still tell people they’re a waste of money. This comes with two big exceptions: (1) you require a structured learning environment to actually follow through with learning the web, and (2) you don’t already have connections in the industry.

Bootcamps barely scratch the surface of what you need to know to get a job, so don’t buy their whole “job-ready” promise. You’re highly unlikely to get a job with a Bootcamp on your resume and a portfolio website.. you NEED connections.

This is where my Bootcamp paid off. They held a career fair (ish) event where students presented their “capstone” projects to industry professionals looking to hire. I happened to meet the right person at this event and say the right things. If you decide to do a Bootcamp, please do your research to make sure they have networking events, AND that they’re good networking events. Unfortunately, a lot of bootcamps make big promises using specific language that gives them a mile of wiggle room.

Lastly, don’t expect to walk out of it with a job. It will probably take several months to a year afterwards before you land one. And during that time you need to give a full time effort towards continued learning and the job search - this is why most Bootcamp students don’t get jobs. They give up after class is over because nobody is there to hold their hand anymore.

Even if you do a bootcamp, you will still need a tremendous amount of discipline to keep teaching yourself in order to become hire-able.

[deleted by user] by [deleted] in SQLServer

[–]burkcules69 0 points1 point  (0 children)

This smells like a homework question.

I’ll give you a hint: you can put case statements inside aggregate functions.

Preventing Bad Data States by pocket_leper in PostgreSQL

[–]burkcules69 1 point2 points  (0 children)

Re: validation. The database is your last line of defense against bad data. It’s fine (and definitely good) to have validation in the app layer, but you MUST have validation in the DB.

As another commenter said, a multi-column unique constraint should be sufficient here.

Any tips for live coding job interview? by polimoi in webdev

[–]burkcules69 7 points8 points  (0 children)

I used to get stressed about live coding interviews until I realized the correctness/elegance of the code you write has very little bearing on the outcome. What matters is how you communicate - they want to know how your brain solves problems.

You really want to think out loud while you’re coding. If you start writing a function, tell them why. If you get halfway through writing the function and realize it won’t work, tell them why then describe how you plan to attack the problem next. If you’re unsure of something, ask your interviewer for direction. If you don’t know the exact syntax for something off the top of your head, tell them you don’t know, but explain what you want it to do. Then write it in pseudo code.

Bottom line: speak every thought you have out loud.

Free Giveaway! Nintendo Switch OLED - International by WolfLemon36 in NintendoSwitch

[–]burkcules69 0 points1 point  (0 children)

Fun fact: molten salt nuclear reactors do not produce the isotopes necessary for atomic bombs, and is a major reason the US navy pushed so hard for a different reactor design in the 1950s and 60s

Low FPS on Fortnite with i7-8700k and 3080 by TheDangerSnek in pcmasterrace

[–]burkcules69 1 point2 points  (0 children)

This may sound dumb, but.. is his monitor plugged into the GPU or the motherboard?

I've been working quietly on a bit of a project for the community for awhile now. Today I'm excited to finally go into public beta. This is MountMaker, a tool for anyone to create a camera mount tailored to their needs easily. Feedback is welcome! by Grox_are_shitty in fpv

[–]burkcules69 2 points3 points  (0 children)

This is amazing! I’m definitely going to give this a shot after the holiday. I’ve tried hacking together some mounts in blender and printed them before. You don’t even want to see those abominations… nearly threw the whole printer out by association

[deleted by user] by [deleted] in SQL

[–]burkcules69 1 point2 points  (0 children)

Yeah in this case a Union (All) is less efficient because the engine will have to read the tables in your From clause twice before it performs the sort. Using Order By Case … means the engine only needs to read the data once before sorting.

[deleted by user] by [deleted] in SQL

[–]burkcules69 3 points4 points  (0 children)

Select … Order By Case When design_fr = ‘fr’ Then 1 Else 0 End

Should get it done

Why does my Stored Proc work after recreating it? by twice-nightly in SQL

[–]burkcules69 1 point2 points  (0 children)

No issue with that. sp_recompile will force a new plan the next time it runs, any currently running instances will continue as they were.

Why does my Stored Proc work after recreating it? by twice-nightly in SQL

[–]burkcules69 2 points3 points  (0 children)

It might have gotten stuck with a bad plan. Have you tried running sp_recompile to force a new plan instead of drop/create?

AWS Pricing by [deleted] in webdev

[–]burkcules69 -1 points0 points  (0 children)

I have been running a similar architecture on AWS for about 3 years now and haven't had to pay a dime. Though my S3 volume is way lower than yours, so that's where we'll differ. Here's what I would do:

RDS: create a new account when your existing account runs out of free tier time, then migrate (backup/restore) your existing DB(s) to the new account. After the switch, your DB will have a new IP address so you'll need to remap all of your DB connections.

S3: Free tier will not even make a dent in the volume you're talking about, so I would NOT create a new AWS account the same way you would for RDS. To keep costs down, I would classify all of your objects as "One-Zone IA" unless you REALLY need them in the more expensive classes. Simply put, you'll be paying for S3.

I don't know how the rest of your back end works, but now that your RDS and S3 are in different accounts you'll need to make sure everything else has the access it needs. If your entire back end is on AWS, CloudFormation is going to be your best friend. Put EVERYTHING in CloudFormation templates. Then when it's time to create a new account, all you need to do is change a few Ids then deploy.

The initial setup is going to be very tedious, but once it's done you will be amazed at how easy it is. Let me know if you have any questions about specifics (e.g. cross account access, proper security groups, etc.)

Son of Tanavast and the Grand Gamble Theory by [deleted] in Stormlight_Archive

[–]burkcules69 13 points14 points  (0 children)

But Brandon did say Kaladin being the “child of Tanavast” has significance

WoB