How to optimize this huge and ugly query, ignoring duplicates?

I'm not an SQL expert, here is my SQLite query on table "Query" (key: SystemId, TopicId, DocumentId, all also foreign keys) which gets the foreign keys and insert avoiding duplicates. But it is huge, ugly and I have to execute it thousands of times:

command.CommandText = "INSERT INTO Query (SystemId, TopicId, DocumentId) " +
  "(SELECT Id FROM System WHERE Tag = @SystemTag COLLATE NOCASE), " +
  "(SELECT Id FROM Topic WHERE Number = @TopicNumber COLLATE NOCASE), " +
  "(SELECT Id FROM Document WHERE Number = @DocNumber COLLATE NOCASE) " +
  "WHERE NOT EXISTS (SELECT 1 FROM Query WHERE " +
    "SystemId = (SELECT Id FROM System WHERE Tag = @SystemTag) AND " +
    "TopicId = (SELECT 1 FROM Topic WHERE Number = @TopicNumber) AND " +
    "DocumentId = (SELECT Id FROM Document WHERE Number = @DocNumber))";

Question: Any way to tell sql "don't worry about duplicates, ignore the insert statement". Or maybe using variables/temporary tables, AD statements?

EDIT: Straight query:

INSERT INTO Query (SystemId, TopicId, DocumentId)
  (SELECT Id FROM System WHERE Tag = @SystemTag COLLATE NOCASE),
  (SELECT Id FROM Topic WHERE Number = @TopicNumber COLLATE NOCASE),
  (SELECT Id FROM Document WHERE Number = @DocNumber COLLATE NOCASE)
  WHERE NOT EXISTS (SELECT 1 FROM Query WHERE 
    SystemId = (SELECT Id FROM System WHERE Tag = @SystemTag) AND
    TopicId = (SELECT 1 FROM Topic WHERE Number = @TopicNumber) AND
    DocumentId = (SELECT Id FROM Document WHERE Number = @DocNumber));

Answers


To prevent inserting a duplicate, you need two things:

A table definition that identifies the columns as needing to be unique. For example:

CREATE TABLE Query (
 SystemId INTEGER, 
 TopicId INTEGER,
 DocumentId INTEGER,
 PRIMARY KEY (SystemId, TopicId, DocumentId));

or

CREATE TABLE Query (
 SystemId INTEGER, 
 TopicId INTEGER,
 DocumentId INTEGER,
 PRIMARY KEY (SystemId, TopicId, DocumentId));

And a conflict clause. You can do this in one of two ways, either in your table definition (leaving it like above will make it default to IGNORE, which is pretty much what you want), or in your insert command:

INSERT OR IGNORE INTO Query...

If you have you table setup with the UNIQUE constraint, you really don't need to change your INSERT query (besides removing the admittedly ugly WHERE NOT EXISTS bit.

The drawback is that yes, it make your code attempt all sorts of insertions and fail. But look at it the other way: it makes your database behave the way you want it to behave. And that is key in working with databases, you don't want to have to do a full manual scan of all tables when you're doing an operation. You want to let the database do the dirty work.


Need Your Help

Sending JSON from iOS, weird response

ios objective-c json

I am trying to send JSON from my iOS.

JSch sftp Transfer Stripping Windows Line Endings

java sftp line-endings jsch

I think I understand the difference between ASCII mode and Binary mode on regular FTP transfers -- in Binary mode the file is copied exactly, and in ASCII mode the client may modify line endings

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.