Database performance 5 times faster with Access than with MS SQL

[Andy314]

1. Fast Secenario

I have the following connections.
Qt (Driver 1)-> MS Access (Driver 2) -> MS SQL
All Tables and Querys are linked in the Access DB.

Driver 1:
DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:__CarDamageDeveloppement\Exe\UserData\ldata.accdb

Driver 2:
DRIVER=SQL Server;Server=localhost\SQLEXPRESS;Database=CarDamage;Uid=yyyy;Pwd=xxx;WSID=.

With this two-chained connectioin I can load with one query 1 Million records in 35 Seconds.

2. Slow Secenario

This is a direct connection to the MS SQL Server
Qt (Driver 2) -> MS SQL

Same query costs about 200 seconds.

This is very strange, because Scenario 1 makes more an should be slower. Why is the direct connection to MS SQL that uses the SAME connition configuration as from Access to MSSql so slow ?

[JonB]

@Andy314
A vital factor for SQL queries is void QSqlQuery::setForwardOnly(bool forward). Have you set that true on the direct connection to the MS SQL Server at least?

[Andy314]

@JonB said in Database performance 5 times faster with Access than with MS SQL:

@Andy314
A vital factor for SQL queries is void QSqlQuery::setForwardOnly(bool forward). Have you set that true on the direct connection to the MS SQL Server at least?

Oh man, this is really a booster. Now, the direct connection is with 15 sec faster than the Access version. Access itself is with 25 sec faster too.
Thank you very much for this tip.

[JonB]

@Andy314
Yep :) I guess you can assume the Access driver maybe does some forward optimization for you, in some shape or form. IIRC the MS SQL driver is the most sensitive to/affected by forward-onlyness, more so than e.g. MySQL. But it's always worth adding Qt's forwardOnly, makes one think maybe it should have been default as there is probably much code out there doing just forward queries and not realizing how much the default is penalising them.