Why is using queries from C++ going to lead to poor performance? If you have a complex query that you need to execute multiple times just prepare it before hand so that you only have the parsing overhead price to pay once.

Oh, I thought that the query would have to be parsed every time I executed it. Thanks for the info!

However, it would make the DB more portable if all the views and functions I needed were directly in the DB, no?

Do you know of a way of accessing my functions and views, or I’d better forget about that?

valandil211 wrote:

Hello Qt Devs! Is there any way to access PostgreSQL functions from a Qt GUI? I’ve read some of the documentation on <QtSql>, but from a performance persepective, I’d rather not embed SQL queries in the C++ source code.

Yes. You have to build the PostgreSQL driver manually, shouldn’t be too hard on a unix/linux like box.

As your’e a programming newbie, it’s not time yet to care about speed. On what experience is your expectation based? And if you do not want to emebed them in C++, where else?

valandil211 wrote:

Also, can I access views I created in Postgres in Qt? Thanks for all your answers!

Yes. They are just tables. You cannot insert or update in views though.

QtSql is a set of classes to access SQL databases. I guess that is what you will indeed need to use. Of course you can also use the plain postgres libraries (most likely C).

I am curious as to how you want to use postgres from Qt without putting SQL queries into the code.

I am curious as to how you want to use postgres from Qt without putting SQL queries into the code.

If Qt can call PostgreSQL functions and parse their results, I would not have to use

but I have not seen any documentation on that yet. As far as I know, PostgreSQL functions can return a single row to whole columns. If Qt could parse the results of a function the same way it can parse the results of a query (like the one above), I could use functions stored in the DB.

Volker wrote:

Yes. They are just tables. You cannot insert or update in views though.

This is partly why I want to use views. There are some data that I do not want the user to be able to update or insert into.

As Volker and Tobias have already said, how else were you planning on executing queries? Surely not with QProcess and the postgres cli interface? That would be much slower as it would involve launching an entire process for each query.

A database view is essentially just a read-only table. Just query it in the same way.

If you need any specific help then please feel free to ask for help here. We are a friendly bunch ;-)

If you wish to restrict operations then get your application to connect as a user that has the minimum rights to do what is needed. This is good practise in general.

Yes you could use the postgres client library directly and bypass the QtSql stuff entirely but then all the data type conversions will need to be performed by you rather than Qt.

I would use QtSql unless you have some specific requirement that it cannot meet.

You will have to call SQL anyways. I think you focus on the wrong topics.

Make the SQL queries using Qt. It’s enough work to learn the framework and C++. Don’t wast time on hypothetical optimizations that you don’t even know if they’re necessary at all.

Qt SQL itself is platform independent. As long as you are using standard SQL queries (insert, update, select…), you should be database agnostic. Calling stored procedures from Qt breaks this, as it is not cross DB!

Some tips:
If you do insert or select the same query with different parameters frequently, prepare the statement (QSqlQuery::prepare()) and save that query. This speeds up the queries significantly.

Set reasonable indexes on your tables, this speeds up the execution of queries in the database itself (independent from the used client).

ZapB wrote:

As Volker and Tobias have already said, how else were you planning on executing queries? Surely not with QProcess and the postgres cli interface? That would be much slower as it would involve launching an entire process for each query.

Well, my father-in-law uses SQL Server and Visual Studio and uses stored procedures to access the DB. These are stored directly in the DB and called from the GUI. I thought there was an equivalent for PostgreSQL and Qt.

ZapB wrote:

If you need any specific help then please feel free to ask for help here. We are a friendly bunch ;-)

Yes, I noticed that. Thanks for all the help!

Thanks Volker, I will keep what you said in mind. Thanks for your time.

Stored procedures are just like prepared queries except that they persist across sessions (I think that is true across DB’s that support them). With prepared queries you just pay the cost of parsing them once per session – which is very minimal.

As a general rule – get it working first. Worry about optimisation later when you have somethign to profile and can find out if anything needs profiling and if so which bit is it.

I recently created a SQLITE database with a simple Qt interface, resorting to using raw SQL only for the CREATE TABLE statements when initializing an empty db. I used Qt classes for everything else, as this application is to take data from a giant POSTGRESQL db on the web and store some of it locally in SQLITE.

I .append() then .setValue() QSqlFields to QSqlRecords that I .insertRecord() into QSqlTableModels which I had bound with .setModel() to my QTableViews.

I had also done this whole project using .prepare() and .exec() of QSqlQueries. This way was a little less typing, and a little quicker to code. The simple SQL that I used could have been tailored for POSTGRESQL, which SQLITE would have converted internally (data types in particular), so it could be “cross db.”

I did the all SQL to all QSql-model comparison strictly to judge performance. There was simply no discernable difference in performance of the db actions when the app was being developed with a half-million records and the SQLITE .filename() was set to “:memory:”. Disk access was the bottleneck in the final creation of the complete db.

In the end- I prefered the QSql-classes all the way, especially for binding to my QModelViews and letting it negotiate the lazy population of views in my 16 million record db.