How fetching data from MSSQL can be increased>

[M.H.H]

Hi there,

The speed of fetching data from SQLite is significantly more than MSSQL.
Is there any way to increase fetching data speed on the MSSQL connection?

[JonB]

@M-H-H
SQLite only has to fetch directly from a file. MSSQL requires using a server process. So there is bound to be some overhead. And you may be using the ODBC connection to MSSQL, which won't be as fast as a SQL Native Client?

[M.H.H]

Thanks for replying,
I used ODBC and it is slow. I didn't find any example for SQL Native Client. ( Qt does not support this type of connection. )

[JonB]

@M-H-H said in How fetching data from MSSQL can be increased>:

I used ODBC and it is slow. I didn't find any example for SQL Native Client. ( Qt does not support this type of connection. )

I am aware of this! I just drew your attention to the fact that ODBC is likely slower than SQL Native Client would be, and hence a possible factor in your tests, though you can't alter that from Qt.

One obvious observation might be: if speed is a concern, why use MSSQL if you are on a single PC? And if you are using it across a network, then it might well be faster, and at least more secure, than SQLite?

BTW, are you using the "forward only" parameter where you can, e.g. doing SELECTs? I found this had a considerable speed up at least for MySQL, if you're not using where appropriate for MSSQL it's worth a try to see if you improve?

[Christian Ehrlicher]

Use another database like e.g. PostgreSQL which is better supported will maybe help :)

[M.H.H]

@JonB
I use 'where' in my SQL script to speed up and reduce the amounts of records that are required. Actually, the SQL command runs very fast but getting the result in a loop is the problem. For example, in C# you can get the result in bulk which helps to reduce the overhead.
Also, I use indexing to improve the performance which helps a lot.

[Christian Ehrlicher]

Blame the ODBC driver :)

[M.H.H]

@Christian-Ehrlicher
Thanks for your mention.
I might check MySQL instead of PostgreSQL.

[Christian Ehrlicher]

@M-H-H But then you have to compile the Qt plugin by your own due to licensing issues. Use a database where you don't have such problems.

[M.H.H]

@Christian-Ehrlicher
Indeed.👍

[JonB]

@M-H-H
As I said, at least try void QSqlQuery::setForwardOnly(bool forward). With any driver.