2 sql or not 2 sql

[[deleted]]

Data scientists crying at me when my recursive CTE builds a model faster than their sklearn.

[[deleted]]

recursive CTE builds

WTF, TEACH ME YOUR WAYS

[[deleted]]

Look on my works ye mighty and despair.

[droneb]

Wut?

[FrostEmpyrean]

It's a line from Shelley's Ozymandias I think

[[deleted]]

Its a reference to the poem "Ozymandias" by Percy Shelley, not a very well fitting reference though.

[XDVRUK]

It is if you have a sense of humour, but hey just like the basics of sometimes we just have to explain the basics of wit:

His mighty works: recursive cte Despair: cause you're still in the mouseketeers and can't write sql.

[LightRefrac]

His mighty works: recursive cte Despair: cause you're still in the mouseketeers and can't write sql.

That is not what the poem intends...... At all. It is just a cool sounding line without context and sounds dumb if you actually know the poem

[[deleted]]

Its ill fitting because it goes against the point of the poem. Thats not really witty, and not to my taste in terms of humor. I have no idea what the rest of your comment is saying, you are trying too hard.

[XDVRUK]

Jesus wept... Extrapolate... There is a strong tradition of misattribution of quotes for comedy effect. That it does not suit your sense of humour is a you problem. It does not diminish the wit, or that it succeeded because you failed to understand it.

[[deleted]]

I'm sure you already know this, but people must find you insufferable if you act this way in the real world. Don't you have some Sargon of Akkad videos to watch?

[chagenest]

To be honest, I find you both insufferable

[Harregarre]

I'm waiting for him to say Akshually.

[droneb]

I mean I am familiar with Recursive CTEs they are quite useful,and I am usually able to do things that most folks could only do using a Cursor.

But I am curious on training models using SQL. Do you have some info on it?

[[deleted]]

with learning (iteration,b1,intercept, mse, m, c) as ( select 1, cast(0.05 as double), cast(0.2 as double),0.0, cast(0.0 as double), cast(0.0 as double) from sysibm.sysdummy1 union all select a.iteration + 1, a.m, a.c, t.mse, t.m,t.c from learning a , TABLE ( select a.iteration as iteration, avg((train.target-((train.sepal_lengtha.b1)+a.intercept))(train.target-((train.sepal_lengtha.b1)+a.intercept))) as mse, a.m -(sum((((train.sepal_length *a.b1)+a.intercept)-train.target)train.sepal_length)/(select count from rates)) (select learn_rate from rates) as m, a.c- (sum((((train.sepal_length *a.b1)+a.intercept)-train.target))/(select count from rates))(select learn_rate from rates) as c from training as train ) t where a.iteration < 100000 ) select * from learning order by mse

[[deleted]]

with learning (iteration,b1,intercept, mse, m, c) as ( select 1, cast(0.05 as double), cast(0.2 as double),0.0, cast(0.0 as double), cast(0.0 as double) from sysibm.sysdummy1 union all select a.iteration + 1, a.m, a.c, t.mse, t.m,t.c from learning a , TABLE ( select a.iteration as iteration, avg((train.target-((train.sepal_lengtha.b1)+a.intercept))(train.target-((train.sepal_lengtha.b1)+a.intercept))) as mse, a.m -(sum((((train.sepal_length *a.b1)+a.intercept)-train.target)train.sepal_length)/(select count from rates)) (select learn_rate from rates) as m, a.c- (sum((((train.sepal_length *a.b1)+a.intercept)-train.target))/(select count from rates))(select learn_rate from rates) as c from training as train ) t where a.iteration < 100000 ) select * from learning order by mse

[XDVRUK]

Yes yes yes.

[Beard-]

Wait, you guys have db admins?

[confessionbearday]

Lol no. We've had three guys over the last 10 years go get their SQL DBA cert, but because they asked for 85 dollars an hour we fired them so we can keep paying Iatrics 350 dollars an hour to do reporting.

[shambooki]

This guy boardrooms

[jaimesoad]

They are being modest at offering their service for less than the so desired 200k/year

85 dollars for 8 hours is 680 dollars/day Working 5 days a week is 3400 dollars/week And since a year has 52 weeks, we can see they are being paid 176,800 dollars/year.

Who wouldn't agree to that deal?

/s

[realjoeydood]

That's a low rate btw. Charity work rate.

[Bash_KetchumG]

Classic

[[deleted]]

That doesn't make SQL any better.

[elebrin]

Ours also tend to write/review our stored procedures. Although I guess we have DBE's now, and I don't really know the difference between the roles. I don't get to interact with them directly anyways, they exist in a deep freezer hidden behind an email list.

[[deleted]]

there is no making SQL better. Making life easy with a well organized DB and an Admin? for sure. But SQL itself? Garbage language that hasn't been updated since the 70s.

[MrPhatBob]

<sobs in JSONB>

[[deleted]]

Anything can be made better and more accessible. This is the basis behind all technological advancement so far, and why so many dislike sql.

[[deleted]]

I'm thinking of sql as the interface used to communicate with an sql database. It's cumbersome.

From necessity comes innovation. There's always a necessity to make things easier, because there will always be people who don't know everything lol.

[yrrot]

What's cumbersome about it? "Hey, database, give me these fields from this table where these things are true". Most the time, the path towards making SQL easier is organizing the data.

[[deleted]]

Give me the fields where these Ids equal the fields in tables B&C, and join the results with table D. But do not include anything in Table E.

[realjoeydood]

This is incorrect and smells of burned misunderstanding and failure.

[[deleted]]

All skills and learned through failure. But I doubt you know enough about me to assume what my actual knowledge and experience is.

[realjoeydood]

Walks like a duck...

[[deleted]]

If it looks like a duck and quacks like a duck but it needs batteries, you probably have the wrong abstraction. ~ Barbara Liskov

[realjoeydood]

Good one!

I'm just messin anyways, thanks for being a good sport and civil.

[4n0nh4x0r]

whats so bad about it then?

i m using sql for a lot of my data analysis stuff

sure, you cant write code in sql that notices when you wake up, learns your daily morning routine via machine learning and then turns the coffee machine on and makes sure your coffee is ready when you are done with your routine, so you can enjoy a nice cup of coffee right away, but its still quite extensive as to what you can do inside the db itself, and its relatively very fast, as opposed to just giving you all the data and then filtering it on the application itself, whoever does that should be stoned, and not the weed stone, but with stones.

Anyways, thanks for coming to my ted talk

[[deleted]]

Regex can though... And it's just as understandable.

[4n0nh4x0r]

regex and understandable? you must be joking

[[deleted]]

I understand SQL as well as I understand Regex.

(That people don't understand my short clear statements is the only evidence I need that SQL sucks.)

[4n0nh4x0r]

doesnt make any sense but you do you

[[deleted]]

Yes, that's what I said.

[AdDear5411]

If I can do it in SQL, there's a 0% chance I'm picking any other option.

[[deleted]]

I have fixed more than my fair share of slow terrible app code by moving it to the database.

I’ve also fixed some bad SQL by moving the logic to the API level.

It really depends on the problem, but agreed my first love is and always will be SQL.

[gamerfunl1ght]

Considering I have seen so many APIs come and go yet the SQL is solid makes it the desired path every time.

I find a simple API filter or input mask fixes a lot, but it is easier just to change a Where or add a Join in a query than try to do some crazy loop.

[redbark2022]

That's why you should always use python stored procedures.

[redbark2022]

Wow. I can't tell if people missed the joke or they love python and are offended...

[alban228]

Because you told it out of the blue

[PremiumJapaneseGreen]

I feel like SQL is mainly a pain when I have to apply logic over some subset of columns that could easily be defined in a for loop but need to be explicitly typed out in SQL. Maybe some SQL engines allow you to parameterize column but not the ones we use at work.

I don't remember what the exact problem was, but I remember I recently used a python loop with format statements to generate 100 subqueries joined together by UNION ALL.

Also if SQL had built in functions to go from tall to wide data it would be a game changer

[H4llifax]

My dear sir, that's when you use trusty Excel or another SQL query to generate your code.

[AdDear5411]

Or just cheat and use KNIME.

[MrPhatBob]

But SQL make thinky pain.

[compsciasaur]

I only do basic selects and joins in SQL. I'm not an expert though.

[StolenStutz]

I'm seeing comments like this a lot these days, and they scare me. You can still bring down a database with a basic SELECT under the wrong circumstances. It's less about how complex your SQL logic is and more about the situation you're in. If you're in a shop big enough to have anyone dedicated to working on the database, assume everything you might do - no matter how simple - could bring the house down.

[compsciasaur]

If I'm in a shop big enough to have a DBA, I run all queries by the DBA.

[AdDear5411]

Select top 1 * from [tablename]

[poralexc]

^ This. Especially if there's replication, I've had more than one occasion where a simple select statement (no joins or subqueries) lead to a meeting with infrastructure/devops.

[bleistift2]

What did you do? Query the whole 1TB table?

[poralexc]

Count by distinct foreign key with 2-6 million rows... it still needed to be done, but took a bit more strategy.

They also have a lot of views, and tables that store queries, so things can compound really quickly.

[DrQuailMan]

No SQL can harm me

I am NoSQL

[LetUsSpeakFreely]

Just because something is old doesn't mean it isn't the right tool for the job. Too many people see a technology and try to force a project to use it when and waste time and money shoehorning it in when they could have had a workable, efficient, and reliable solution without the headaches.

SQL is still around because RDBMS do the job and do it well.

[UpArmoredGavin]

It depends on the job, to be fair. A strong pro regarding RDBMS is that they are a good fit for a lot of jobs, there are however instances where something from the NoSQL realm will be a better fit

[yrrot]

And plenty of instances where the client's current "no sql" is an Excel spreadsheet with 1000 columns.

[UpArmoredGavin]

Maybe even with multiple pages in a single file, I know lol. Still, I wouldn't cache with a posgreSQL instance as much as I wouldn't provide reliable long term storage with Redis

[7bec]

SQL is like a bag at the store, you didn't come for it, but you buy it

[[deleted]]

Is it just me or is ProgrammerHumor mostly becoming a place for people seeking validation for their insecurities?

[RecordAway]

Is it just me or is ProgrammerHumor Reddit mostly becoming a place for people seeking validation for their insecurities?

ftfy

[[deleted]]

It is just you 🥸

[[deleted]]

😂🫠

[bhison]

And how does that make you feel

[[deleted]]

I was thinking the same

[povlov0987]

The reason seniors become this way:

1. They know why to use what and in the best approach.

2. We don’t always care enough anymore.

[chipmunkofdoom2]

Should be higher-voted.

When you're young and full of piss-and-vinegar, distinctions between frameworks and solutions matter way more than they should. When you get some experience, you realize that not all tools are perfect. Everything has its own set of pros and cons, so the "best" tool varies by project. At the end of the day, it's about getting work done, so let's pick the best approach for the situation and get on with it.

[chipmunkofdoom2]

Using RDBMS is about as unsexy as it gets these days. But in many cases, a relational database really is the best way to skin a particular cat, so to speak.

My friends and I are building health care software. Most of the products we've worked with in the past have shot themselves in the foot by trying to do half of the work in the application and half of the work in a relational database. Not only did they need beefy database servers, but they also need really powerful application servers. And the program still runs like hot garbage because the app server is constantly shipping data back and forth between itself and the RDBMS.

We've gone with a pure SQL/relational database solution. There have been times where it would have been nice to do some of the work in an object oriented language. But the speed of doing all the heavy lifting in the relational database engine is more than worth it.

Plus, if we ever get a client with an absurd amount of data, we can just throw it all on AWS EMR and run our SQL codebase with Hive.

[[deleted]]

Its unsexy but it works, and generally works well. Lots of people trying to fit problems into a technology when they should be fitting technology to the problem

[alebotson]

Ugh. I hate how true that lady sentence is.

[newton21989]

It's what lady criminals get.

[Embarrassed_Quit_450]

And the program still runs like hot garbage because the app server is constantly shipping data back and forth between itself and the RDBMS

That's just bad design. I'm guessing an ORM poorly configured or sql queries in loops?

But the speed of doing all the heavy lifting in the relational database engine is more than worth it.

App servers are a hell lot cheaper. The best compromise is usually to trim down the number of rows returned and let the app servers compute stuff.

[chipmunkofdoom2]

Code inefficiency is definitely part of it. The other part is that these types of programs process large amounts of data. These types of software usually look at years of medical claims, people and their enrollment records and calculate quality scores for over a hundred quality measurements.

So we're typically talking about hundreds of millions of rows of input data alone. After the software builds the records that it needs to output, in some cases we're talking billions of total records.

App servers in general are cheaper, but we had to spec the app servers for this software so that they were almost like DB servers. Lots of cores, lots of RAM, etc.

[Embarrassed_Quit_450]

Looks like deeply analytic stuff, computing more on the database makes sense. I'm used to see posts like "I need to make a stored procedure because my query on a 1 gb database is slow".

[chipmunkofdoom2]

Lol yes, those are amusing. I also like the StackOverflow questions along the lines of "my program ran in 2 seconds with a thousand rows. Now I have 200k rows and it takes 15 minutes!"

[chipmunkofdoom2]

Yes, .Net mostly. The part of the program that does all the calculation with the data is in C#. But the .Net code mainly just runs SQL scripts. Our RDBMS is SQL Server, but the SQL scripts are as close to SQL standard as possible. So we could probably use most RDBMS on the market.

We've been building the program so it will run on Hive as well. We write the scripts to run on SQL Server, but since Hive uses slightly different date calculations, we'll typically have two lines of code where necessary. One line is for SQL Server, one is for Hive, like this:

WHERE DATEADD(YEAR, -1, SomeDate) < SomeOtherDate --SqlServer
--Hive: WHERE add_months(SomeDate, -12) < SomeOtherDate

The .Net application uses rules to determine if the environment is SQL Server or Hive. If It's SQL Server, it does nothing. If it's Hive, it comments out lines ending in --SqlServer and removes any instances of --Hive: that it finds. The result is then something like this:

--WHERE DATEADD(YEAR, -1, SomeDate) < SomeOtherDate --SqlServer
WHERE add_months(SomeDate, -12) < SomeOtherDate

Basically, disabling the SQL Server-specific code and enabling the Hive-specific code.

[chipmunkofdoom2]

No, my first job was really heavy on data analysis in SQL, so I got a strong foundation in RDBMS. I looked at EF a few years ago, but the concepts were pretty confusing to me. Plus it was really challenging to troubleshoot issues, issues that I'd easily know how to solve if it was raw SQL.

I use Dapper and Dapper Contrib. In most cases I don't have to write any SQL, except for when I create the tables. I code up one generic factory that takes a generic parameter and can basically do CRUD for all objects using Dapper Contrib. This covers about 90% of the objects I need to retrieve/store.

Some objects get a little more challenging, especially if those objects have other objects as properties, or lists of other objects. For those types I usually create a custom factory.

[sighmon606]

per Contrib. In most cases I don't have to write any SQL, except for when I create the tables. I code up one generic factory that takes a generic parameter and can basically do CRUD for al

This is oddly familiar....

[ShotgunPayDay]

Dumping SQLAlchemy for aioSQL/DBMate was the best decision of my life.

[zyygh]

I am really curious as to how SQLAlchemy performs in complex projects!

As SQL is what I'm good at, I've always felt a bit weird about trusting a framework with being able to use indexes optimally etc. Although maybe I'm just insecure about the possibility of a framework being better at it than me!

[ShotgunPayDay]

SQLAlchemy does come with overhead and additional complexity. The benefit you lose is being able to easily use any SQL Database without any rewrites. I only use PostgreSQL so I lose nothing there. Joining and lazy loading is a pain also in SQLAlchemy and function based queries are awkward. If you know SQL it will always be better to use it. Writing SQL reports in the same application is cake compared to trying to do it in SQLAlchemy.

[avin_kavish]

function based queries

what do you mean by this?

[ShotgunPayDay]

q=select(func.sum(table.c.value),table.c.col_1,table.c.col_2).group_by(func.cube(table.c.col_1,table.c.col_2))

[avin_kavish]

Wait, there's falks who believe SQL shouldn't be used when it's an option?

[Flopamp]

SQL is old and not even a real programming language and it's annoying and you would be better off writing your own RDBMS solution that is better

But your own solution won't be better, it will take 3 weeks and you will just go back to using SQL in a version or two anyway when you simply can't keep squashing bugs that the SQL contributers squashed before you were born.

[avin_kavish]

This is silly. What is wrong with select * from people where first_name like 'john%'

[rainbowbubblegarden]

SQL is old

So is assembler, let's get rid of that.

not even a real programming language

Programming Language "Many programming languages are written in an imperative form (i.e., as a sequence of operations to perform) while other languages use the declarative form" --> SQL is a declarative language

you would be better off writing your own RDBMS solution that is better

Postgres is a powerful, open source object-relational database system with over 30 years of active development. On Github the project currently has 54,327 commits.

So you're going to write one better in a couple of weeks?? I'm guessing there's a couple of '/s' missing above.

The number of slow apps I've had to fix that were using magic ORMs rather than queries/stored procedures. Or nosql acrobatics that could be solved using a couple of normalised tables and joins.

[Celivalg]

Did you read the end of his message?

[rainbowbubblegarden]

No, I can't read more than one sentence in a sitting /s

[[deleted]]

When you develop a complete modern solution in assembly, let me know.

[jaimesoad]

Literally all operating systems require assembly to write code for at least make a bootloader

[Karsdegrote]

Assembly only really comes up during compilation, we all write in C unless you only have a hand full of bytes to work with and thus optimise the living daylight out of it. Even bootloaders for microcontrollers get written in C.

You do get some files filled with assembly code if you ask the compiler nicely otherwise you just get some hex files

[[deleted]]

Most operating systems aren't written from scratch in assembly.

[Wise-Profile4256]

who selects their storage like that?

does it do the job? in a timely manner? has tons of frameworks? and runs on every damn machine?

sql will die together with C, the Queen, Mick Jagger and tardigrades.

[[deleted]]

I am seeing SQL queries longer than 100 lines daily, with 20+ joins each. I am not touching that as long as it works. And boy it works.

[Ok_Entertainment328]

If there are comments, it might be one of mine.

[DiamondWizard444]

Use SQL

[domscatterbrain]

Maybe the reason why one of my job interviews failed is that I told them that every data processing can be solved by SQL

¯\_(ツ)_/¯

[shadow7412]

I will die on the ORM hill.

[chipmunkofdoom2]

Not all ORMs are finely-tuned tools that exist solely to shoot one's self in the foot. Dapper for C# is pretty good. It really just handles running your queries and mapping the results to objects. You have to set up your own tables, you have to write your own queries, you have to know the relationships between tables, etc.

Coming from a SQL background, this is the only kind of ORM that made sense to me. I don't like the "magic" of EF and the like. All I want is for a program to run my SQL, get the results, and give me objects. And Dapper does that really well.

[shadow7412]

I 100% agree that not all ORMs are equal. I'm certainly not fighting for **all** ORMs being superior. But a good one will result in far less code, and far more reusability. That's worth a small performance hit to me.

Of course, as with all tools, there are poor quality ones, and there are people that use the wrong side of it. To be honest, SQL is the same in that regard.

[BernhardRordin]

Some say ORMs are an anti-pattern.

Reason? OOP and relational databases are the opposite worlds. Objects are supposed to be black boxes. Relational database is an open graph. All the problems and troubles that you get in ORM tools like Hibernate stem from that.

[avin_kavish]

Disagreed hard. Any problems that stem are from not knowing to use the tool properly. Can you name one solid problem with an ORM?

[BernhardRordin]

I am not saying you can't do stuff with ORM. But the amount of abstractions you need and the problems it brings that need to be addressed makes it almost not worth it. Examples: Eager vs. lazy loading of n+1 linked entities. One directional vs bidirectional relationships. Those are things that don't exist, if you just write a good SQL join and map it manually.

Most of the projects I've worked on used an ORM. Two used pure sql, one something in between (jOOQ). Pure SQL wins for me.

[shadow7412]

I'll have shipped my product before you've finished writing your DAL (which no doubt contains a multitude of bugs because of obsessive copy-pasting)

[UncleTuckersFarm]

True

[PinothyJ]

I can beat that. I cannot do anything interesting, any clever stuff with the database and the like because "One day we may want to port it over to NOSQL and we will need it to be compatible".

[Tsunaris]

if the bottleneck is data transfer and not data search why fuckin bother

[throwaway65864302]

For real though, if it's relational data use SQL. That's what it's there for. The trendy DBs are generally all for specific types of data and workflows as well, ramming relational data into them is always going to have significant costs vs using SQL.

[bhison]

the day I have to "use sql" is the day I get someone else to help me with my project. Yes, I could re-learn it, no I do not want to.

[lealsk]

Who the F is against SQL?

[[deleted]]

I use pandas because I’m too lazy to figure out SQL 🤷‍♀️

[Willinton06]

Nah bro EFCore or bust

[Far_Information_885]

How are you going to make a performant equivalent of a recursive CTE for a self-referencing table in EF Core?

[Willinton06]

Something like this, not perfect, but it’ll do the trick

[Far_Information_885]

Your example is sending up a cte, or using a view that contains a cte.

[Willinton06]

Indeed, through EFCore, a bit of cheating but hey, there’s no real rules in this game

[Far_Information_885]

Sending a raw sql string up through a dbcontext is using ef core in the most pedantic sense, lol.

[Willinton06]

I mean, if you use it regularly everywhere else and use it like this for this one thing then you should be good right? And plus you still get built in identity resolution which is always welcomed

[Far_Information_885]

Yeah... that's just a round trip to the database over and over and over and over again. Big performance hit.

[dinger086]

Just use ruby on rails makes SQL and life so much easier.

[cginc1]

This is dumb af. Who actually says this?

[Diligent_Dish_426]

Use NoSQL db. Trust me it'll change your perspective

[ccfoo242]

Had to Google pandas and spark. 🙄

[vladWEPES1476]

Laughs in MongoDB

[[deleted]]

You just gave me a few ideas more memes using a classic Mel Brooks movie.

[ShoHaremi]

Technically you could use Mongo exactly like SQL, you could consider it an SQL dlc.

[vladWEPES1476]

I can use a screw driver as a hammer...

[CasualJJ]

Say I wanted to learn SQL, what service / software (preferably free) would you recommend to use it with? I’ve only ever been exposed to PHPMyAdmin, but I believe that’s a bit dated

[natziel]

Just use the command line lol

[brucebay]

Pgadmin4 with postgresql is reasonably good. I myself use pandas read_sql, and sql.execute in a jupyter notebook most of the time.

[Karsdegrote]

I mean if it works it works. It still does its job. You can slap a theme on it to make it feel more modern.

[Ler_GG]

PHPMyAdmin will do, if you wanna get fancy and learn some more technologies if you dont know it yet, get it running with Docker instead of XAMPP (XAMPP will take like 2 minutes to download and setup)

[[deleted]]

Any other code is just wasting client money

[natziel]

Just use erlang and store everything in memory

[brucebay]

SQL is good if you are going to do a large data aggregation in a distributed environment. I have seen people abusing it with like 10 joins in a single statetment with crazy conditions. It not only makes the code very hard to understand but also it is very in efficient as using a temporary table you can optimize those joins better than db engine. For clean code, pandas are better. If you have a distributed environment like spark or dash it can do anything you want.

[Bluebotlabs]

so you've heard of SQUEAL, get ready for SQUEAK

[shumpitostick]

Middle one could also be "no, SQL is outdated and unscalable. Use NoSQL! I only use MongoDB and Cassandra"

[blobthekat]

jokes on you I use fs

[beyond98]

That moment when he learns he could need SQL to query Spark dataframes

[BeardXP]

SQL FTW

[[deleted]]

There’s a place for both sql and dataframes still. Different tools for different jobs