--- Dan <[EMAIL PROTECTED]> wrote: > So I decided to splitt my table in 31 tables for each day one, there > the entries are all day 1500entries/seconds and sometimes more but > this solutions gives my now an other problem for doing some queries. > Because when it was just in 1 table the insert took very long but the > executation of my query simple select field from table limit 100 and > so on was very fast +-50ms. Now that I have 31 tables i did a view > where i group the tables together with UNION and when i make the same > query on that view it takes 4minutes. > > Does somebody have an idea to get this again on the same speed?
SQLite is very slow when querying a VIEW that makes use of UNIONs. http://www.sqlite.org/cvstrac/tktview?tn=1924 http://marc.info/?l=sqlite-users&m=117958960408282&q=p3 You could either apply the SQLite patch above to speed up queries on UNIONS, or not use VIEWs at all and rewrite your query as follows: +** For example: +** +** SELECT * FROM ( +** SELECT a, b FROM t1 WHERE b!=7 +** UNION ALL +** SELECT x, sum(y) FROM t2 +** WHERE x<9 GROUP BY x HAVING sum(y)>7 +** ) WHERE a>b; +** +** will be transformed into: +** +** SELECT * FROM ( +** SELECT a, b FROM t1 WHERE b!=7 AND a>b +** UNION ALL +** SELECT x, sum(y) FROM t2 +** WHERE x<9 GROUP BY x HAVING sum(y)>7 AND x>sum(y) +** ); As this is more of an SQLite question, rather than a JDBC question, please follow-up to the SQLite mailing list if you have any other questions. http://www.sqlite.org/support.html ____________________________________________________________________________________ Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center. http://autos.yahoo.com/green_center/ --~--~---------~--~----~------------~-------~--~----~ Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en To unsubscribe, send email to [EMAIL PROTECTED] -~----------~----~----~----~------~----~------~--~---
