3 ms·

I don't think pg understands the issue at a gut-feel level. There is a lot of benefit to processing your data using a declarative language, which is what SQL i

by ucee054 13y ago

I don't think pg understands the issue at a gut-feel level. There is a lot of benefit to processing your data using a declarative language, which is what SQL is. It's basically prolog, with less recursion and more separation of code and data. You get all of that power for all of the data manipulation code that you run inside the database. If you have the misfortune to be writing in VB or Java, that's a huge power-up that you gain by using an RDBMS. But pg is already using a declarative language, because he programs in Lisp. So the advantages are fewer in his case. So I doubt pg feels significantly empowered by an RDBMS.

wildgift 13y ago
I never thought of SQL as declarative or like Prolog. I think it's more like a filter-writing language, with set functions like join. Agreed that the distance between Lisp and SQL is a lot shorter so an RDMBS doesn't gain you very much. In functional languages, it's normal to write filters and apply functions to lists. You also end up performing your joins manually, and end up "optimizing the query" as you code. In SQL, filters and apply (update) are easier to write, but it's also easy to perform a join that is slow.
- ucee054 13y ago
  I never thought of SQL as declarative or like Prolog. Look at the different forms of relational calculus (SQL is intended to be basically syntactic sugar on top) or look at some research papers by Fariba Sadri - hell, look at the earliest papers on relational databases by EF Codd. RDBMSs were meant to be logic systems right from the start. it's also easy to perform a join that is slow Have you been using MySQL? Use a real database with a working query optimizer eg I know from experience that MS SQL will do its best even when you give it a dog of a query. For example it will re-arrange your "filter" and build a temporary index to make your join go at the fastest possible speed. If you have a specific use case nailed down you can always make it go faster outside of an RDBMS much the same way that you can always make something go faster if you hand-code it in machine code instead of using a compiler. But if you can't put that much effort into hand-optimizing things, say because your use case might change so you'd have to change the code, the query optimizer/compiler will do a better job.
- ksherlock 13y ago
  Try reading up on relational calculus (and relational algebra). You may find it enlightening
guard-of-terra 13y ago
Declarative often means opaque and hard to debug. SQL is just that over huge datasets. With properly partitioned text files, at least you know you're doing a full scan of a small partition that is practically real-time.
- ucee054 13y ago
  I don't think this is true at all ... you use the same query irrespective of the size of the data in the tables. So the size of the datasets is irrelevant except for surfacing performance problems. In the example you gave you shouldn't have problems if you put the right index on the table. What really causes the opacity is either (a) the complexity of the query or (b) the guy writing the clearly doesn't know how to write declarative code, so he tries to write eg procedural code in SQL, with cursors and triggers and other horrors. If you are in scenario (a) and you really do need to do something complex, I'd pick SQL over VB to do it in any day - the non-declarative style leads to huge code with side effects are more opportunities for bugs to creep in. If you are in scenario (b), the guy writing the queries really needs to read this: http://www.amazon.com/Database-Management-Systems-Raghu-Ramakrishnan/dp/0072465638 http://www.amazon.com/Database-Management-Systems-Raghu-Rama...
wglb 13y ago
I am at a loss to understand I don't think pg understands the issue at a gut-feel level. And Lisp as a declarative language? SQL being basically Prolog?
- enqk 13y ago
  It has been very helpful to me to consider SQL being an approximation of the prolog model. It made the understanding and creation of schemas and queries much easier.

by ucee054 13y ago

wildgift 13y ago

I never thought of SQL as declarative or like Prolog. I think it's more like a filter-writing language, with set functions like join. Agreed that the distance between Lisp and SQL is a lot shorter so an RDMBS doesn't gain you very much. In functional languages, it's normal to write filters and apply functions to lists. You also end up performing your joins manually, and end up "optimizing the query" as you code. In SQL, filters and apply (update) are easier to write, but it's also easy to perform a join that is slow.

ucee054 13y ago
I never thought of SQL as declarative or like Prolog. Look at the different forms of relational calculus (SQL is intended to be basically syntactic sugar on top) or look at some research papers by Fariba Sadri - hell, look at the earliest papers on relational databases by EF Codd. RDBMSs were meant to be logic systems right from the start. it's also easy to perform a join that is slow Have you been using MySQL? Use a real database with a working query optimizer eg I know from experience that MS SQL will do its best even when you give it a dog of a query. For example it will re-arrange your "filter" and build a temporary index to make your join go at the fastest possible speed. If you have a specific use case nailed down you can always make it go faster outside of an RDBMS much the same way that you can always make something go faster if you hand-code it in machine code instead of using a compiler. But if you can't put that much effort into hand-optimizing things, say because your use case might change so you'd have to change the code, the query optimizer/compiler will do a better job.
ksherlock 13y ago
Try reading up on relational calculus (and relational algebra). You may find it enlightening

guard-of-terra 13y ago

Declarative often means opaque and hard to debug. SQL is just that over huge datasets. With properly partitioned text files, at least you know you're doing a full scan of a small partition that is practically real-time.

ucee054 13y ago
I don't think this is true at all ... you use the same query irrespective of the size of the data in the tables. So the size of the datasets is irrelevant except for surfacing performance problems. In the example you gave you shouldn't have problems if you put the right index on the table. What really causes the opacity is either (a) the complexity of the query or (b) the guy writing the clearly doesn't know how to write declarative code, so he tries to write eg procedural code in SQL, with cursors and triggers and other horrors. If you are in scenario (a) and you really do need to do something complex, I'd pick SQL over VB to do it in any day - the non-declarative style leads to huge code with side effects are more opportunities for bugs to creep in. If you are in scenario (b), the guy writing the queries really needs to read this: http://www.amazon.com/Database-Management-Systems-Raghu-Ramakrishnan/dp/0072465638 http://www.amazon.com/Database-Management-Systems-Raghu-Rama...

wglb 13y ago

I am at a loss to understand I don't think pg understands the issue at a gut-feel level. And Lisp as a declarative language? SQL being basically Prolog?

enqk 13y ago
It has been very helpful to me to consider SQL being an approximation of the prolog model. It made the understanding and creation of schemas and queries much easier.