sqlite query to get earliest date

debugcn 投稿 Dev

Mike

I have the following query:

  SELECT (CASE WHEN cc.id IS NULL THEN dc.id ELSE cc.id END) AS id,
 l.CALL,l.QSO_DATE,cc.prefix,l.state,l.QSL_RCVD,l.Band,l.Mode,
(CASE  WHEN CC.Country IS NULL THEN dc.Country ELSE cc.Country END) AS country,
(CASE  WHEN CC.Country IS NULL THEN 'Y' ELSE 'N' END) AS 'deleted',
(CASE  WHEN CC.flags IS NULL THEN dc.flags ELSE cc.flags END) AS 'flag'
FROM lotw l
LEFT OUTER JOIN CountryCode AS cc on cc.code = l.dxcc 
LEFT OUTER JOIN DeleteCountry AS dc on dc.code = l.dxcc 
WHERE l.DXCC > '' 

GROUP BY CC.Country,dc.Country  ORDER BY cc.COUNTRY,dc.COUNTRY.

which returns a set of unique country that matches the countrycode table code field. the speed is good less than a second.

I needed to expand this so the query select's the earliest QSLRDATE from the LOTW table. so I modify the query like this:

SELECT (CASE WHEN cc.id IS NULL THEN dc.id ELSE cc.id END) AS id, l.CALL,l.QSO_DATE,cc.prefix,l.state,l.QSL_RCVD,l.Band,l.Mode,
(CASE  WHEN CC.Country IS NULL THEN dc.Country ELSE cc.Country END) AS country,
(CASE  WHEN CC.Country IS NULL THEN 'Y' ELSE 'N' END) AS 'deleted',
(CASE  WHEN CC.flags IS NULL THEN dc.flags ELSE cc.flags END) AS 'flag'
FROM lotw l
LEFT OUTER JOIN CountryCode AS cc on cc.code = l.dxcc 
LEFT OUTER JOIN DeleteCountry AS dc on dc.code = l.dxcc 
WHERE l.QSLRDATE = (SELECT MIN(l2.QSLRDATE) FROM LOTW l2 WHERE l2.DXCC=l.DXCC) AND l.DXCC > '' 

GROUP BY CC.Country,dc.Country  ORDER BY cc.COUNTRY,dc.COUNTRY

this works but the performance went from less than a second to 15 seconds. The sub query I added:

l.QSLRDATE = (SELECT MIN(l2.QSLRDATE) FROM LOTW l2 WHERE l2.DXCC=l.DXCC)

can't be the best way to accomplish what I want. any help would be great.

MatBailie

Try joining on a sub-query rather than using a correlated sub-query in the WHERE clause.

SELECT
  (CASE WHEN cc.id IS NULL THEN dc.id ELSE cc.id END) AS id,
  l.CALL,l.QSO_DATE,cc.prefix,l.state,l.QSL_RCVD,l.Band,l.Mode,
  (CASE  WHEN CC.Country IS NULL THEN dc.Country ELSE cc.Country END) AS country,
  (CASE  WHEN CC.Country IS NULL THEN 'Y' ELSE 'N' END) AS 'deleted',
  (CASE  WHEN CC.flags IS NULL THEN dc.flags ELSE cc.flags END) AS 'flag'
FROM
  (SELECT dxcc, MIN(qslrdate) AS qslrdate FROM lotw GROUP BY dxcc) AS qslr
     INNER JOIN lotw AS l ON l.dxcc = qslr.dxcc AND l.qslrdate = qslr.qslrdate
LEFT OUTER JOIN CountryCode AS cc on cc.code = l.dxcc 
LEFT OUTER JOIN DeleteCountry AS dc on dc.code = l.dxcc 
WHERE l.DXCC > ''
GROUP BY CC.Country,dc.Country  ORDER BY cc.COUNTRY,dc.COUNTRY

Also, make sure that lotw has an index on (dxcc, qslrdate) to make ensure both the sub-query and join perform well.

Infact, ensure you have that index first, and retry your query. Then, if the performance is still poor, try the above query.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]