Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Tips and Tricks

github.com

Post reply on HN
Print view View original

111–120 of 168 posts

Re: SQL Tips and Tricks

#111

Post by hot_gril »

Izkata wrote: post #90
abrookewood wrote: post #71

Earlier quoted context omitted.

I don't understand the point at all. If you need to add some condition later on, why not just add it then? What benefit is there to just marking out the spot where you might add the condition at some point in the future?

For their one here it's just the ability to rapidly comment/uncomment conditions in a query editor while exploring the data or debugging the query, and not having to worry about the leading AND or OR. I've also seen it in code with iterative adds, for example: for crit in criteria: sql += " AND " + crit No needed to add a sentinel or other logic to skip the first AND. I saw it a lot before people got used to " AND ".…

Yeah. Especially when I'm trying to see what makes some query slow.

Re: SQL Tips and Tricks

#112

Post by rawgabbit »

My tips for working with complex Stored Procedures.

1. At the beginning of the proc, immediately copy any permanent tables into temporary tables and specify/limit/filter only for the rows you need.

2. In the middle of the proc, manipulate the temporary tables as needed.

3. At the end of the proc, update the permanent tables enclosed within a transaction. Immediately rollback transaction/exit the proc, if an error is detected. (By following all three steps, this will improve concurrency and lets you restart the proc without manually cleaning up any data messes).

4. Use extreme caution when working with remote tables. Remote tables do not reside in your RDBMS and most likely will not utilize any statistics/indexes your RDBMS has. In many cases, it is more performant to dump/copy the entire remote table into a temporary table and then work with that. The most you can expect from a remote table is to execute a Where clause. If you attempt Joins or something complicated, it will likely timeout.

5. The Query Plan is easily confused. In some cases, the Query Plan will resort to perform row by row processing which will bring performance to a halt. In many cases, it is better to break up a complex stored procedure into smaller steps using temporary tables.

6. Always check the Query Plan to see what the RDBMS is actually doing.

Re: SQL Tips and Tricks

#113

Post by remus »

rawgabbit wrote: post #112

My tips for working with complex Stored Procedures. 1. At the beginning of the proc, immediately copy any permanent tables into temporary tables and specify/limit/filter only for the rows you need. 2. In the middle of the proc, manipulate the temporary tables as needed. 3. At the end of the proc, update the permanent tables enclosed within a transaction. Immediately rollback transaction/exit the proc, if an error is…

1-3 are nice if you can guarantee your data is reasonably sized, but if it gets too big for your hardware taking copies of large datasets and then doing updates on large datasets can add a lot of overhead.

Re: SQL Tips and Tricks

#114

Post by wvenable »

rawgabbit wrote: post #112

My tips for working with complex Stored Procedures. 1. At the beginning of the proc, immediately copy any permanent tables into temporary tables and specify/limit/filter only for the rows you need. 2. In the middle of the proc, manipulate the temporary tables as needed. 3. At the end of the proc, update the permanent tables enclosed within a transaction. Immediately rollback transaction/exit the proc, if an error is…

I've significantly improved the performance of queries by undoing someone who did #5 when it wasn't strictly needed. Sometimes breaking a query into many smaller queries is significantly less efficient than giving the query optimizer the entire query and letting it find the best route to the data.

If you've done #5 without doing #6 then you'll likely not see that you're doing something not optimal. My advice is avoid premature optimization and do things the most straight forward way first and then only optimize if needed. Most importantly, don't code in SQL procedurally -- you're describing the data you want not giving the engine instructions on how to get it.

Re: SQL Tips and Tricks

#115

Post by the_gorilla »

rawgabbit wrote: post #112

My tips for working with complex Stored Procedures. 1. At the beginning of the proc, immediately copy any permanent tables into temporary tables and specify/limit/filter only for the rows you need. 2. In the middle of the proc, manipulate the temporary tables as needed. 3. At the end of the proc, update the permanent tables enclosed within a transaction. Immediately rollback transaction/exit the proc, if an error is…

I hate having to use a bunch of temp tables, but I regularly run into queries that would never finish if you let the query planner do its thing. Like compilers their ability is highly overrated. Meanwhile, microsoft places constant warnings against trying to even tune their query planner because it supposedly knows best.

Re: SQL Tips and Tricks

#116

Post by the_gorilla »

AtNightWeCode wrote: post #107
halayli wrote: post #42

Earlier quoted context omitted.

A random person claims adding 1=1 is a security risk and you are going to add it as caveat without verifying if the claim is true nor knowing why? That's how misinformation spreads around. OP doesn't know what they are talking about because adding 1=1 is not a security risk. 1=1 is related to sql injections where a malicious attacker injects 'OR 1=1' into the end of the where clause to disable the where clause comple…

Read my other comments. I worked with SQL on and off since the last century. It has nothing to do with your poor assumptions.

Duration of working with SQL doesn't matter. The better SQL programmers don't do it specifically, and have experience in real languages that they bring over to database queries.

Re: SQL Tips and Tricks

#118

Post by brikym »

I like SQL but I think it's time for the big players like MySQL, MSSQL, Postgres etc to start using FROM-first and piping syntax. I've had the pleasure of using Kusto query language and it's a huge leap forward in DX.

Re: SQL Tips and Tricks

#119

Post by ray_v »

magicalhippo wrote: post #63
code_biologist wrote: post #60

Earlier quoted context omitted.

Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. What does this mean? Running SELECT column1, ( SELECT column2, column3, ... FROM table_b WHERE table_a.id = table_b.a_id ) FROM table_a Results in "subquery must return only one column" as I expected. You mean returning the multiple columns as a record / composite type? Keep in mind GROUP BY c…

Sorry, was on mobile so hadn't patience to type examples. SELECT column1, ( SELECT column2 FROM table_b WHERE table_a.id = table_b.a_id ) as b_column2, ( SELECT column3 FROM table_b WHERE table_a.id = table_b.a_id ) as b_column3 FROM table_a It might look like a lot more work, but in my experience it's usually a lot faster. YMMV but check it.

You have to be careful here that a one-to-many relationship doesn't exist and returns more than 1 row -- it'll cause an error and halt your query

Re: SQL Tips and Tricks

#120

Post by renhanxue »

isoprophlex wrote: post #5

Wow, that EXCEPT trick is neat! ~10 years of using SQL almost daily, and I never knew...

Unfortunately EXCEPT is almost never what you actually want. It's a set operator, like UNION, and just like UNION it also has the side effect of removing duplicates from the result set unless you explicitly say EXCEPT ALL.

Because it's a set operator and not a join, it's usually very hard for the query planner to optimize it beyond the most trivial cases. It usually ends up being one of the last steps in the query plan. In a good query plan you almost always want to eliminate rows you don't care about as early as possible so you don't have to drag them along in every join operation only to have the data discarded at the end, but if you're using EXCEPT instead of the explicit anti-semi-join operator (that is NOT EXISTS()), you're making that very difficult for the query planner.

EXCEPT and INTERSECT are sometimes a handy shortcut when you're writing some quick and dirty query by hand, but I have literally never used either of them in a production query. You almost always want to use EXISTS() and NOT EXISTS(). They explicitly communicate intent, which is appreciated both by people reading the query and by the query planner, and they lack the footguns some of the other alternatives have.

Post reply on HN