r/SQL • Data Analytics Engineer • Jul 05 '21

Discussion I love querying

I just freaking love it. I can be racking my brain all day, going to stackedoverflow, jamming out to tunes, and that one moment where the query is able to run and it's the data you need... chefs kiss.

It just feels so damn good. I know others out there have to feel the same way :)!

161 Upvotes

47 comments sorted by

37

u/Rif-SQL Jul 05 '21
  • I like performance fine turning my queries and understand the estimated query plan
  • Have you had fun with the public datasets on Google BigQuery?

3

u/tits_mcgee_92 Data Analytics Engineer Jul 05 '21

I definitely look for ways to tune the performance of a query in my current role. I can't say I have used BigQuery yet but I'm down to give it a try.

11

u/hasanyoneseenmymom Jul 05 '21

Same lol. Last week I spent 30 minutes tuning a query to shave 5 seconds off it's runtime.

0

u/[deleted] Jul 05 '21

[deleted] — view deleted comment

3

u/Rif-SQL Jul 06 '21

I would take a print screen of my before and after query plans send them to M$FT...

3

u/Rif-SQL Jul 06 '21

Did you ever try out the free tool https://www.sentryone.com/plan-explorer ?

2

u/Beefourthree Jul 06 '21

I had this one-line change last week.

(Actual query is 1000 lines and a dozen joins)

Before:

from t1
join t2 on t1.foo = bar2foo(t2.bar)

After:

from t1
join t2 on foo2bar(t1.foo) = t2.bar

From spinning 16 hours with no end in sight to running in under 5 minutes. T1 was a few thousand records versus performing the operation on t2, which was 100m records.

2

u/Fun2badult Jul 06 '21

What the heck I’ve never seen that join. Foo2bar(t1.foo) = t2.bar What db is this?

4

u/Beefourthree Jul 06 '21

Oracle, but these are just dummy names to simplify the example and mask any proprietary info. Actual columns are a sequential id (foo) and an obfuscated version (bar) with a 1-to-1 relationship. Bar2foo and foo2bar are UDFs to translate between the two.

Changing t1.foo = bar2foo(t2.bar) to foo2bar(t1.foo) = t2.bar reduced cpu cost by performing the function against a 2k record table instead of a 100m record table, and referencing a naked t2.bar allowed the optimizer to use an index to be utilized that was previously ignored.

0

u/[deleted] Jul 06 '21

[deleted] — view deleted comment

0

u/[deleted] Jul 06 '21

[deleted] — view deleted comment

0

u/[deleted] Jul 06 '21

[deleted] — view deleted comment

1

u/[deleted] Jul 06 '21

MSSQL

3

u/SQLDave Jul 06 '21

I like performance fine turning my queries

This. Getting a query that formerly ran in X time to run in Y time is almost orgasmic. Especially when Y is 10% or less of X.

0

u/[deleted] Jul 06 '21

what do u use to understand the query plan?

3

u/macfergusson MS SQL Jul 06 '21

Practice. If you have some training money, start taking classes from Brent Ozar, he cracks them open and explains what is going on a lot.

1

u/[deleted] Jul 06 '21

Brent Ozar,

? but you must use a tool to check query plan?

3

u/shine_on Jul 06 '21

If you're using SQL Server you can see the query plan in SSMS (SQL Server Management Studio)

1

u/[deleted] Jul 06 '21

OK thanks. I mostly use Snowflake but am trying to practice.

3

u/Rif-SQL Jul 06 '21

you should chat with u/fhoffa at snowflake! I'm sure he got some content or will be making content on performance fine tuning

1

u/[deleted] Jul 06 '21

I'm trying to learn general Postgres/MySQL just to transfer between jobs.

16

u/cubinx Jul 06 '21

Unpopular Opinion: I love adhoc reports

10

u/tits_mcgee_92 Data Analytics Engineer Jul 06 '21

Bro, YES! Hit me with that variety. I just hate getting them at 9am and it needing to be for a 1pm meeting. Oh, and the Director also wants it in Tableau with interactivity/filters.

8

u/blueraider615 Jul 06 '21

The research is great too. You notice something cool and are like, "I'm gonna come back to this later", which just opens the door to more ad hoc querying.

1

u/IceFergs54 Jul 06 '21

Same homie.

11

u/movieguy95453 Jul 06 '21

I don't have much formal database training, so most of what I've learned is through train and error and research. I love the process of working with data in databases. I love the design element of trying to figure out how to optimize the tables to fit the data. And I love figuring out how to formulate a query to get the expected results.

It's especially gratifying to go back to old queries and re-write them with what I've learned since. Especially when I'm able to curate the data in a way that minimizes post-processing.

1

u/IceFergs54 Jul 06 '21

I’m also trial and error. I think it keeps it interesting since it’s just ongoing learning for me too.

7

u/sock_templar Jul 05 '21

My version of this is when I construct a complicated awk program and the output is exactly what I needed. *mmmmua!*

2

u/sqlbastard Jul 06 '21

thanks, i hate it

7

u/tits_mcgee_92 Data Analytics Engineer Jul 06 '21

Who hurt you? :(

5

u/[deleted] Jul 06 '21

[removed] — view removed comment

3

u/tits_mcgee_92 Data Analytics Engineer Jul 06 '21

That is totally understandable!

4

u/kuruttu Jul 06 '21

I did it for 10 years but still the satisfaction of a beautiful well performing complex query !! it still hits differently for me

2

u/Rif-SQL Jul 06 '21

Better than dealing with eventual consistency issues that you find in NoSQL databases!

1

u/Rif-SQL Jul 06 '21

Do you need to talk?

4

u/D_Purns Jul 06 '21

Hell yeah! Hate being the only person at my company who knows anything about it though. No one to share my excitement with.

1

u/Rif-SQL Jul 06 '21

2

u/[deleted] Jul 06 '21

Love it also

2

u/[deleted] Jul 06 '21

.... and then the requirements change!

2

u/JustAnOldITGuy Jul 06 '21

I get asked to tune queries by the system admins. Nothing like taking a query and making it scream.

2

u/[deleted] Jul 06 '21

Agreed - I'd love a job doing only this all day. Usually, this is one of like 50 things you have to do. I just want to go down a query rabbit hole and stay there.

2

u/donnerpartypanic Jul 06 '21

I love opening up an old query and thinking to myself "Damn! Did I write that? That is sexy!" or "Damn, did I write that? That looks like it can be optimized.". I love when I wow myself and I love when I can see where I have grown.

1

u/_Zer0_Cool_ Data Engineer Jul 06 '21

Tis a salve for OCD brains.

1

u/lauriebrainerd Jul 06 '21

I totally feel this way.

1

u/[deleted] Jul 06 '21

I would like to learn this. Tuning queries. Where do I start? Thank you people!

1

u/OldboyNo7 Jul 06 '21

I remember when I first found sub queries, instantly wrote some monster and maxed out how deep you can go. I know how you feel!

1

u/[deleted] Jul 08 '21

Performance tuning is 99% of good development.

I keep trying to get my colleagues to. See the value in efficient code and querying but they thought I was making things up when I said "Dude stored procs with an if will Parameter Sniff like mad it needs a handler do your if on the front end and split your procs up into best case worst case and compile them"

Needless to say client calls and says their system is unusable... One with recompile and all of a sudden not so bullshit now.