Hello dear SQLite users, Hello Dr. Hipp, Given the following table: CREATE TABLE [History] ( [License] VARCHAR(50) COLLATE NOCASE, [ControlCode] VARCHAR(20) COLLATE NOCASE, [ActivationKey] VARCHAR(50) COLLATE NOCASE, [Contact] VARCHAR(160) COLLATE NOCASE, [Obs] VARCHAR(255) COLLATE NOCASE, [Date], [UserIP] VARCHAR(20) COLLATE NOCASE, [UserName] VARCHAR(20) COLLATE NOCASE); )
having 3 indexes created on the table: CREATE INDEX idxHistory0 ON History (License); CREATE INDEX idxHistory1 ON History (Date); CREATE INDEX idxHistory2 ON History (ControlCode); and approximatively 17.500 records in the table. The table holds data for software activation. It is possible to have records containing same License/ControlCode pair, since the software has been activated more than once. I'm trying to find the License/ControlCode pair which has been activated in the past but is no longer valid (any past ControlCodes on one License are no longer valid). To be more specific, I'll try to give an example. Given the data: License ControlCode Date Lic1 CC2 2004.Jan.03 Lic1 CC1 2004.Jan.01 Lic2 CC3 2004.Feb.02 Lic2 CC3 2004.Feb.01 the output of the query should be: Lic1 CC1 2004.Jan.01 since Lic2 has been activated in the past but is still valid (since the current active pair is Lic2/CC3) and Lic1/CC1 is no longer valid because it has been activated in the past but the active license has another ControlCode. For this, I run the following query: SELECT DISTINCT LH.License, LH.ControlCode FROM History LH LEFT JOIN ( SELECT L1.License AS Lic, L1.ControlCode AS CC FROM History L1 INNER JOIN ( SELECT License, MAX([Date]) AS MaxDeDate FROM History GROUP BY License ) AS L2 ON L1.License = L2.License AND L1.[Date] = L2.MaxDeDate ) AS J2 ON J2.Lic = LH.License AND J2.CC = LH.ControlCode WHERE J2.CC IS NULL; I have two major problems with the above query: 1. It takes 91 seconds to complete! (Microsoft Access runs it in 2 seconds) Any hints for making it at least as fast as Access? 2. While the query executes, the whole database is locked! Why? If the engine creates temporary tables, should they block the whole database for writing? This is one major problem for me: does this mean that if I'll have more long-running queries, I shouldn't be able to update the database while they run? Please explain the way things work so I can understand better what it happens. I was really expecting to be able to make changes to the database while SELECT queries are executing... I'm using version sqlite command-line v3.1.1 beta. Thanks. George Ionescu.
