Required to do a Bulk insert in to a Microsoft SQL Server.
-
I have a data in a Standard Template Library QMap ex: 10000 records.
My requirement is to do a bulk insert of 1000 records each to a stored procedure written in Microsoft SQL server. Is there any way to interact with Stored proc apart from regular types like QString,QBytearray. I wanted a cached table into which i will be inserting 1000 records and send that to Stored proc to parse that cached table and insert data into regular table. The stored procedure implementation is straight forward, But i am not getting a way to communicate it via Qt application. Motto is to reduce CPU utilization insert queries. -
There is no other way than QSqlQuery::prepeare() / bindValue().
Did you actually measure the cpu utilization? Did you use a database transaction to commit the whole data only once? How large is your data?Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
Visit the Qt Academy at https://academy.qt.io/catalog -
There is no other way than QSqlQuery::prepeare() / bindValue().
Did you actually measure the cpu utilization? Did you use a database transaction to commit the whole data only once? How large is your data?@Christian-Ehrlicher Based on some rough calculation if 1 user is using the application approximately 1,60,000 insert queries is being triggered by user. The application is being used by 100 user simultaneously and to hit the server where SQL is installed there are 5 hops inbetween, it is taking time. It is a remote application.
The current queries are written using sqlquery::prepare()/bindvalue. which is one row at a time. -
This is really a huge amount of data. The only thing I see here is to first check if Qt is really the bottleneck and if so directly access the database instead going though the Qt layer.
-
I tried with some experiments to handle such huge data, A SQL stored procedure is implemented which accepts XML as input reads it and stores in a table variable and insert in a single shot of e.g 7000 inserts in 1 to 1.5 seconds. My Qt application bundles the data in xml format of required amount of entries controlled in application.
The stats i can compare here the sequential query call takes around 15-20 seconds to store 7000 records, The same 7000 records takes only 1 to 1.5 seconds to store via XML-stored proc implementation.
