Comment by dbatten | Hacker News Reader

Comment by dbatten | Hacker News Reader

Parent Full thread

> This implies that SQL is not reusable, causing similar code with slightly different logic to be repeated all over the place. For example, one cannot easily write a SQL ‘library’ for accounting purposes and distribute it to the rest of your team for reuse whenever accounting-related analysis is required.

Data scientist here. I think some of this problem is handled by effective use of views. Oh, everybody is constantly joining these three accounting-related tables and aggregating by, say, order number? Have your Data Engineer/DBA/analyst/whoever create a view that takes care of that. Boom. Now everybody's using the same data, calculated the same way, nobody's reinventing the wheel, and you don't have to worry about somebody fat-fingering something when they re-write that query for the 10th time.

With that being said, I still think there's some truth to this criticism, in that it's not as easy/common to be able to build an abstract query that does a common operation on arbitrary data. You can't import trend_forecast.sql, hand it arbitrary time-series data, and generate an N-month linear forecast from your historical data points. At least, not easily in ANSI SQL.

imo any SQL that requires regression (I have implemented least squares in TSQL w/ recursive CTEs) is probably a bad use case for SQL in general - projecting, filtering, grouping, and sorting is what SQL engines are great at - repeating row access or referencing the previous row on the next row and then running a function is not going to be fast in most of the databases.

To the point on views - they can seem really useful, but many people dont understand that views dont eliminate "useless code" - most engines are going to evaluate everything in the view, and the nested subviews, and of course, the 10 depth views that were created because they are such a great abstraction.

This is where (to me) other non-database programming languages definitely come in - you can codify codegen or other methods that are locked in, not just a huge string and get a lot of the same value as your views (unless of course, everyone is querying the db directly.) If that's the case, then templating out code is still a good plan, but you can usually accomplish it via the client tooling people are using (most of the DB IDEs have pretty decent snippet support.)

Agreed on regression. You should probably be doing that level of analytical work in a stats package. Perhaps I should have chosen a better example to illustrate the "SQL doesn't facilitate creating abstract libraries that operate on arbitrary data" point.

I've found views views wildly useful for defining a single definition of a complex heirarchy defined by business rules. Especially for reporting in 'pre-packaged' software with a fixed table structure. Often in these generic CRM or ERP systems it's surprisingly hard to actually get at the root cardinally-1 data and it's 1-n categories.

My strategy for these systems is to first create a view that captures the complete business' desired cardinality (which may be surprisingly complicated), and all the reporting views and stored procedures start with that view as the first table to join off of. Ideally you can implement it with left joins in such a way that allows you to use it everywhere you need any part of the heirarchy and the SQL engine will trim the parts that you don't use in each particular query that uses it.

With this you get the abstraction / reusability that enhances productivity and you get more reliable & reproducible results everywhere. It's much more likely that cross checking to separate reports works when everything is starting with the same cardinality.

Are you saying you build a completely de-normalized View of data? Could you please provide a small example?

My problem with views is that you do the join before the filtering.

Any query optimizer worth its salt will push the filters below the join.

I'm curious, what DBMS are you using whose optimizer can't see through views?

For what it's worth, MSSQL, PostgreSQL and Oracle don't have that limitation.

Bigquery.

If a make a view that joins table a and b, and I query that view with a filter, bigquery won’t push the filtering down unto a and b and then join.

Ah. That's not too surprising, then, is it? Bigquery's column-oriented, so I imagine efficient row-selective queries isn't really what it's for in the first place.

Predicate pushdown is one of the first optimizations implemented in SQL planners and all the OLAP and OLTP solutions that I'm aware of already have this feature in place.

Reply on news.ycombinator.com