All Questions
Tagged with sqlite query-performance
28 questions
0
votes
1
answer
50
views
Sqlite fetching speed problem - how to understand explain and compare two queries
I need to join two tables product_features and feature_text to get this output
From a previous question i got two answers that both return the expected result for a small sample set: join so that
...
- 219
2
votes
2
answers
75
views
How can I find a topmost row and count the rows with only one scan?
Suppose that I have a schema like the following:
-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);
-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
SELECT ...
- 123
0
votes
2
answers
79
views
Can many table names having the same prefix slow down a database?
I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key ...
- 123
0
votes
1
answer
89
views
Performance Quetions. Search database with range query or with and id
I have a database, consisting of the following columns
id, a string looking like this 8b28347448d3fff (15 length)
x, a decimal (8,6)
y, a decimal (9,6)
All the columns have Indexes on them. Now, I ...
- 177
5
votes
1
answer
570
views
Optimize a search between two values
I have a table that has an integer timestamp as primary key. Almost every query on this table will be of the pattern
SELECT * FROM table WHERE timestamp BETWEEN x AND y
These ranges are usually ...
- 175
1
vote
0
answers
255
views
Proceeds JOIN only if condition met on the Owner table
I have many hasOne relationships like this:
components
component_headings
component_images
Here one Component can only have one hasOne to any of [headings, images].
Logically when a Component.type =...
- 111
1
vote
0
answers
41
views
Creating the right index/es for querying views in SQL(ite3)
I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and ...
- 111
0
votes
2
answers
462
views
Optimizing nested SQLite query
I have a SQLite table named hashbands that looks like this:
hashband | file_id | window_id
------------------------------
potato | 0 | 0
potato | 1 | 0
The table has 100M+ rows (lol). ...
- 129
1
vote
2
answers
959
views
Comparing Query Performance Across DBMSs
I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL.
I have been looking into command line tools for each system to get the ...
0
votes
1
answer
476
views
Optimizing regex based query in sqlite
I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 ...
- 115
0
votes
1
answer
357
views
How to improve poor performance with two joined CTE expressions on hierarchical data?
I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched ...
- 103
0
votes
1
answer
50
views
WITH-clause not pre-calculated, lineal resource wasting, why?
I'm trying to save resources, and shorten code, but getting lineal resource wasting with "WITH-clause".
This is my base example:
#!/bin/bash
echo "CREATE TABLE 'clients_table' ( id ...
- 18
0
votes
1
answer
298
views
Improve sqlite mapping query
I have an SQLite DB that has couples of tables. one of them looks like this:
(id integer primary key, location integer, name text, desc text, version integer)
select id,other_colum from table where ...
- 1
0
votes
1
answer
131
views
accelerating a SQL quadruple self-join with a complex alternation in the WHERE clause
The following Sqlite query has a triple self-join on table t:
SELECT "update_variable",
lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
op.span,
op.path
FROM t op
JOIN t lhs_acc ON (...
- 121
0
votes
0
answers
608
views
Sqlite Join with 1 million records
I have 3 tables:
Device: Id, Name.
Machine: Id, P1, P2, date_time, IdDevice.
Parameters: Id, T, V, I, date_time, IdDevice.
In device there isn't a lot of records. But in the other way, inside ...
- 1
1
vote
2
answers
2k
views
join vs virtual table in query performance
Suppose I have two tables linked by a foreign-key
example DDL's
CREATE TABLE raw_collection (
info_datetime DATETIME NOT NULL,
asset_id INTEGER NOT NULL,
price FLOAT,
PRIMARY KEY (...
- 197
0
votes
1
answer
2k
views
sqlite - very slow performance of WHERE EXISTS without index on EXISTS
I've got a strange case of (very) slow performance of an update query when I don't have an index on the table in the EXISTS clause.
For example:
UPDATE tbl1
SET col1 = "Y"
WHERE EXISTS (SELECT ...
- 103
2
votes
2
answers
8k
views
How to find exact string from a sentence
I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
- 123
6
votes
1
answer
433
views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
- 163
0
votes
1
answer
742
views
How to denormalize many-to-many relation without nested select?
I have the following schema:
house has many students, student has one house
gender has many students, student has one gender
game has many students. student has many games
For instance, consider
...
- 101
1
vote
1
answer
5k
views
SQLite - is it faster to use SELECT NOT EXISTS / EXISTS or IN / NOT IN in a WHERE clause?
In SQLite's query planners is it generally faster to do this kind of thing?
SELECT foo.bar,foo.baz
FROM foo
WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha)
... or ...
SELECT ...
- 121
1
vote
1
answer
64
views
How can this query be improved to be faster?
I am trying to make a search in a db which contains tables poems and authors. I am using replace function to ignore accent characters in db while making the search.
If the title column matches the ...
- 111
3
votes
1
answer
222
views
Retreive data from two tables of the same structure
I have a SQLite database where I fetch data from two tables with the following query.
select ie.*
from (select *
from History where Station = @station and TimeStampCome <= @till and ...
- 285
1
vote
1
answer
6k
views
Efficient way to get last 10 rows of each group in SQLite, can CTE help?
Consider there is a table of job runs history with the following schema:
job_runs
(
run_id integer not null, -- identifier of the run
job_id integer not null, -- identifier of the job
...
- 113
1
vote
1
answer
290
views
Improve Query Performance
I have the following sqlite3-Tables:
db.execute("""CREATE TABLE IF NOT EXISTS STATIONS
(StationUID INTEGER PRIMARY KEY AUTOINCREMENT,
StationNumber TEXT NOT NULL,
...
- 113
7
votes
2
answers
11k
views
Improving `GROUP BY` query performance in sqlite3
I have a little web-application that is using sqlite3 as it's DB (the db is fairly small).
Right now, I am generating some content to display using the following query:
SELECT dbId,
dlState,
...
- 1,366
0
votes
2
answers
261
views
How to optimize query
I'm using SQLite3 and the following query, It seems like there should be a better performing way to write this query; Is there?
Select Name,
Sum(Case
When
Used
...
- 143
8
votes
1
answer
445
views
Performance of query with a range condition and order by
We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:
lexikon
-------
_id integer PRIMARY KEY
word text
frequency integer
...
- 98.7k