Parameterized Query
-
My program inserts thousands of records into a SQLite database. That works fine. I do it by building an INSERT statement with 1000 VALUE groups, each group representing a different record to add. If I were to execute a separate INSERT statement for each record, then the insert would take hours, so I make the compound INSERT statement. So the question is, how can I transform the INSERT query into a parameterized query, if every VALUE group has unique values? There are four fields that need to be populated for each record, so that means I must create a SQLiteCommand with 4000 parameters? Or is there a better way?
The difficult we do right away... ...the impossible takes slightly longer.
-
My program inserts thousands of records into a SQLite database. That works fine. I do it by building an INSERT statement with 1000 VALUE groups, each group representing a different record to add. If I were to execute a separate INSERT statement for each record, then the insert would take hours, so I make the compound INSERT statement. So the question is, how can I transform the INSERT query into a parameterized query, if every VALUE group has unique values? There are four fields that need to be populated for each record, so that means I must create a SQLiteCommand with 4000 parameters? Or is there a better way?
The difficult we do right away... ...the impossible takes slightly longer.
I don't think you have much of a choice. Sqlite doesn't support any bulk insert so you're either going to reuse the same command over and over again or build a block of inserts. You also have a command length limit of 1GB but, unless you go nuts building a huge block of insert statements, you'll not likely hit that. The other problem is a limit on the number of parameters you can have. The default using numbered named parameters is 32766 for recent versions of Sqlite.
Quote:
Maximum Number Of Host Parameters In A Single SQL Statement A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123". Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark. SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0. The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
My program inserts thousands of records into a SQLite database. That works fine. I do it by building an INSERT statement with 1000 VALUE groups, each group representing a different record to add. If I were to execute a separate INSERT statement for each record, then the insert would take hours, so I make the compound INSERT statement. So the question is, how can I transform the INSERT query into a parameterized query, if every VALUE group has unique values? There are four fields that need to be populated for each record, so that means I must create a SQLiteCommand with 4000 parameters? Or is there a better way?
The difficult we do right away... ...the impossible takes slightly longer.
SQLite isn't open to SQL Injection, because it doesn't support multiple commands in the same command*: it doesn't support bulk inserts, even via a DataAdapter. it is meant to be a "lite" version, after all! So if you build your command with multiple VALUE fields, it is technically safe (but needs to be commented in case someone later converts to MSSql / MySql) I'd recommend using a transaction though, just to speed the operation up if nothing else... This may help: https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite[^] * Unless you use the CommandText property instead of a SqlLiteCommand - then you are wide open to SQL Injection.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
I don't think you have much of a choice. Sqlite doesn't support any bulk insert so you're either going to reuse the same command over and over again or build a block of inserts. You also have a command length limit of 1GB but, unless you go nuts building a huge block of insert statements, you'll not likely hit that. The other problem is a limit on the number of parameters you can have. The default using numbered named parameters is 32766 for recent versions of Sqlite.
Quote:
Maximum Number Of Host Parameters In A Single SQL Statement A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123". Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark. SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0. The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave KreskowiakThanks, just wanted to be sure I wasn't overlooking something. :thumbsup:
The difficult we do right away... ...the impossible takes slightly longer.
-
SQLite isn't open to SQL Injection, because it doesn't support multiple commands in the same command*: it doesn't support bulk inserts, even via a DataAdapter. it is meant to be a "lite" version, after all! So if you build your command with multiple VALUE fields, it is technically safe (but needs to be commented in case someone later converts to MSSql / MySql) I'd recommend using a transaction though, just to speed the operation up if nothing else... This may help: https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite[^] * Unless you use the CommandText property instead of a SqlLiteCommand - then you are wide open to SQL Injection.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
* Unless you use the CommandText property instead of a SqlLiteCommand - then you are wide open to SQL Injection.
Uh oh, that's what I'm using. I'm setting the CommandText property of a SQLiteCommand to the long INSERT statement. I wasn't aware that there's any different way. Is there? EDIT: I misread your post. What is the other way of using the CommandText property instead of a SQLiteCommand? (Just curious)
The difficult we do right away... ...the impossible takes slightly longer.
-
OriginalGriff wrote:
* Unless you use the CommandText property instead of a SqlLiteCommand - then you are wide open to SQL Injection.
Uh oh, that's what I'm using. I'm setting the CommandText property of a SQLiteCommand to the long INSERT statement. I wasn't aware that there's any different way. Is there? EDIT: I misread your post. What is the other way of using the CommandText property instead of a SQLiteCommand? (Just curious)
The difficult we do right away... ...the impossible takes slightly longer.
No, the CommandText is a property of the SqLiteCommand object: SqliteCommand.CommandText Property (Microsoft.Data.Sqlite) | Microsoft Learn[^] SqLite is just that: a "lite" version of SQL - it isn't meant for big jobs. I'd be tempted to say that you might be better off using SQL Server if you are working with large databases - but that's your decision and I don't know enough about your system(s) to reccomend it.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
My program inserts thousands of records into a SQLite database. That works fine. I do it by building an INSERT statement with 1000 VALUE groups, each group representing a different record to add. If I were to execute a separate INSERT statement for each record, then the insert would take hours, so I make the compound INSERT statement. So the question is, how can I transform the INSERT query into a parameterized query, if every VALUE group has unique values? There are four fields that need to be populated for each record, so that means I must create a SQLiteCommand with 4000 parameters? Or is there a better way?
The difficult we do right away... ...the impossible takes slightly longer.
Did you actually time the separate inserts? "Thousands" isn't very much and the server is "local". The usual consideration is don't do "mass inserts" with an index ... drop and build the index afterwards. I've experience seconds, but never "hours".
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
Did you actually time the separate inserts? "Thousands" isn't very much and the server is "local". The usual consideration is don't do "mass inserts" with an index ... drop and build the index afterwards. I've experience seconds, but never "hours".
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
I might have used hyperbole, but the database will contain upwards of 4 million rows.
The difficult we do right away... ...the impossible takes slightly longer.
-
No, the CommandText is a property of the SqLiteCommand object: SqliteCommand.CommandText Property (Microsoft.Data.Sqlite) | Microsoft Learn[^] SqLite is just that: a "lite" version of SQL - it isn't meant for big jobs. I'd be tempted to say that you might be better off using SQL Server if you are working with large databases - but that's your decision and I don't know enough about your system(s) to reccomend it.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
Anyway, I appreciate you pointing out that it's not susceptible to injection attacks. That was the reason for my question. Thanks!
The difficult we do right away... ...the impossible takes slightly longer.
