select with subquery takes about 1min in sqlite and < 1s in SQL Server

Question

I already looked up many posts about this problem (subqueries being very slow in sqlite). but I'm not really good with sql and i don't know what i can do.

i have this query:

SELECT * FROM data d WHERE d.category = 3 AND 
(SELECT COUNT(id) FROM data_tag WHERE data = d.id AND (tag = 2136 OR tag = 8)) >= 2 
ORDER BY id ASC

i have 3 tables data,tag and data_tag (n-m of data and tag) every data has n tags and in this query i search datas by tags (2 tags, both must be in data)

i switched my database from SQL Server to sqlite and besides this query everything works fine. in SQL Server this one took less then 1 sec to execute and in sqlite it takes about 1min. plz give me some tips.

Answer 1

SELECT d.* FROM data d
INNER JOIN data_tag ON data_tag.data = d.id AND (tag = 2136 OR tag = 8)
WHERE d.category = 3 
GROUP BY d.id
HAVING COUNT(data_tag.id) >= 2 
ORDER BY id ASC

Answer 2

Try:

  SELECT d.* 
    FROM DATA d 
    JOIN (SELECT dt.data,
                 COUNT(id) AS num_tags
            FROM DATA_TAG dt
           WHERE dt.tag IN (2136, 8)
        GROUP BY dt.data
          HAVING COUNT(id) >= 2) x ON x.data = d.id
ORDER BY d.id 

Don't need ASC in the ORDER BY - that's the default.