Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,524
questions
0
votes
0answers
10 views
What is the most efficient query in SQLITE for checking if a PRIMARY KEY, a PRIMARY KEY that is also the rowid, or a Value exists?
I am a beginner to SQLITE, and I am building a library in Qt that interfaces with it.
The methods I am working on right now are:
bool QDatabase::contains ( const T_Key &key ) { ... }
bool ...
0
votes
3answers
70 views
adding the right index to a table in a MySQL Database
We are having a big problem of high CPU usage of MySQL process in our database server, after investigation we have found that a big part of slow queries are related to a specific table and a big ...
0
votes
2answers
88 views
SQL Server not creating parallel plan with OPTION(MAXDOP 20)
We have a UAT3 server hosted on VM with 8 sockets and 20 processer, we have similar UAT2 server hosted on the same VM with same configuration.
We are running a below query on both the server
select ...
0
votes
1answer
53 views
Optimizing big sorted join (nested loop) based on value frequency
I have a query involving two tables, let's call them timeline and events, defined as follows:
create table timeline (
event_id int,
ts timestamptz
);
create table events (
id int primary key,...
0
votes
0answers
28 views
Execution plan for slow-running query shows table spool operator generating millions of rows [closed]
I'm an aspiring Database Administrator and I've taken on the monumental task of performance tuning our decade-old sql server. After watching countless Brent Ozar videos, I now feel like I can ...
0
votes
1answer
40 views
Should I use composite key or primary key from other table
We have a Education Project which has following entities :
Domain (e.g. Programming, UI/UX, AI, ML), each Domain has 5 Levels (1, 2, 3, 4, 5)
Building Blocks - which are like small topics e.g. java, ...
5
votes
2answers
215 views
How can I achieve predicate pushdown in my view
I have a reporting table (about 1bn rows), and a tiny dimension table:
CREATE TABLE dbo.Sales_unpartitioned (
BusinessUnit int NOT NULL,
[Date] date NOT NULL,
SKU ...
1
vote
1answer
37 views
DISTINCT forces re-sorting of data on PostgreSQL
I have a table recording transactions between accounts, looking like this:
txid varchar(64)
account varchar(64)
ts timestamp
amount numeric
(there are more fields but they are irrelevant for ...
0
votes
2answers
96 views
Postgres: Faster query when adding useless JOIN
I was looking at a query to make it more performant and I encountered an interesting case. This query executes much faster when I add a (redundant) JOIN that doesn't change the actual result set.
...
0
votes
0answers
24 views
PostgreSQL: Filter for many values is slow
I am surprised by the slow response time of a type of query and am hoping to get some advice.
Let's imagine a table person with the columns id (integer and primary key) and age (integer). I would like ...
1
vote
1answer
38 views
Very different performance between two similar queries
We are working on a view (multiple joins in this view). And I have these two queries:
Query 1:
select *
from v_investment_planning_ticket v
where v.ticket_id in
(...
0
votes
2answers
121 views
An error occurred while updating the entries. See the inner exception for details.; [INNER] Execution Timeout Expired [closed]
One of our production SQL Servers database is performing slow. On the application side, they are experiencing this error:
An error occurred while updating the entries. See the inner exception for ...
2
votes
1answer
188 views
Multiple Outer Joins vs. Single Join with Multiple OR Conditions in ON
Which of the following is preferable? These queries give the same results (correct me if I'm wrong). The first takes 75-80ms while the second takes 35-40ms in my tests. Should the first approach be ...
7
votes
3answers
958 views
Recommended way: “SELECT *” vs “SELECT COLUMN_LIST” from same table, multiple times
This question is very much near to what I wanted to ask, but both question and answers are focusing more on selecting random rows.
Since there is general rule that, "SELECT COLUMN_LIST" is ...
2
votes
1answer
509 views
How can I speed up this mysql (mariadb) Query?
I'm averaging about 1000 queries per minute with the below query. Most of the query will stay the same with variations on the date,hour,minute and itemName (2021-06-02, 10, 30 and "football" ...
1
vote
0answers
21 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 ...
0
votes
2answers
16 views
How to check the source of analysis task in Oracle Performance Analyzer?
We have provided sql tuning set as input to the analysis task of performance analyzer
variable l_task_id VARCHAR2(64);
exec :l_task_id:=dbms_sqlpa.create_analysis_task(
sqlset_name => '<...
0
votes
0answers
25 views
Slow query in SSD when enable_bitmapscan=on
Table INFO and index INFO:
CREATE TABLE PART (
P_PARTKEY SERIAL,
P_NAME VARCHAR(55),
P_MFGR CHAR(25),
P_BRAND CHAR(10),
P_TYPE VARCHAR(25),...
0
votes
0answers
30 views
Performant way to limit selected rows but make sure the sum of the rows is large enough
Here's a simplified version of the problem. You have a marketplace where users can publish how many items they have for sale and the price. You would like to buy 1 000 000 items, and you want to ...
0
votes
1answer
17 views
Why doesn't MySQL optimizer do index merge from indexes in two different tables?
Query1 takes almost 2000x more time than Query2.
The criteria is of form
tbl1.key1 = x or tbl2.key1 = y
Query 1
SELECT
`t1`.`t1_PK`
FROM
`t1`
JOIN
`rel_t1_t2` ON `t1`.`t1_PK` = `...
1
vote
1answer
39 views
Massive performance degradation in upgrade from MariaDB 5.5 to MySQL 8
As part of a complete server move and upgrade (OS, Apache, and db) went from MariaDB 5.5 to MySQL 8.0.21. Hardware specs unchanged. Unfortunately despite many hours of reading and experimental tuning ...
0
votes
2answers
55 views
Does the order of MongoDB search terms matter for query performance?
Does the order of search terms in MongoDB matter? I came across this documentation from learnmongodbthehardway that cautions against the use of $ne and $nin:
If you need to use these [$nin and $ne] ...
2
votes
1answer
67 views
Improve MySql query performance in large table
I have a MySql database that is used to store events from an app, since we created it, we only inserted and selected data, we've never deleted any rows. I'm not a DB admin and there's no DB admin in ...
10
votes
1answer
301 views
Why does a clustered Key Lookup on a primary key have a high estimated rows per execution?
We have a query with a Key Lookup which is estimating thousands of rows per execution. As I understand it, there should only ever be one row per execution. I understand statistics can be misleading ...
0
votes
3answers
40 views
Force MySQL index on OR query?
I have a query that could be resolved 99% of the time using an index but needs a table scan for a few queries:
SELECT * FROM mytable WHERE idx=123 OR name like "%foo%" limit 1
Logically ...
1
vote
1answer
137 views
Mysql showing 100% CPU usage
We are facing problem of high cpu usage for mysql process (almost 100%). Here is the information related to server
Server Infos:
VPS - CENTOS 7.9 kvm - 6 GB RAM - 4 Core CPU - 180 GB SSD - MariaDB
And ...
1
vote
2answers
65 views
Can parameter sniffing happen even after disabling it?
I'm having a very strange issue with an EF linq query, the query runs a dynamic sql, something like this:
EXEC sp_executesql N'select 1 from X',N'@parameter1 int',@parameter1 = 123
The query was ...
0
votes
1answer
35 views
Same query shows significant differences in performance on different data [closed]
I'm having a problem with writing a Postgres query that really appreciates anyone's help. I have 3 tables related to each other as below:
Order:
id (pk)
updated_at (datetime)
owner (foreign key, to ...
0
votes
1answer
31 views
Query store - Is it possible to tell - WHICH stored procedure calls to functions cause the most resource usage?
The database that I'm working on unfortunately has a lot of functions, with select statements in them, that are heavily used in where clauses in multiple stored procedures. This is not good design or ...
0
votes
1answer
44 views
Performance issue when multiple columns of same table are grouped
I am dealing with large amount of data approx 1 million rows with 100s of columns.
I have this proc which performs some calculation over this data based on grouped by a colum1.
Now the same ...
0
votes
2answers
56 views
In PostgreSQL, how can I make Bitmap Index Scan parallelized?
I want to improve the performance of an SQL statement.
I am on version 13.
Here are the sample codes and the query I am interested in.
drop table ords;
CREATE TABLE ords (
ORD_ID INT NOT NULL,
CUST_ID ...
-3
votes
1answer
22 views
How to arrange documents in a collection alphabetically in mongodb?
How do I arrange my document names in a collection alphabetically and henceforth make my searches for a particular document faster?
Say I have a million documents in a collection. I want to search for ...
3
votes
2answers
98 views
Optimization of recursive view
I have an optimization problem when using a recursive view on PostgreSQL. When I perform a trivial query using this view, the execution time is abnormally long.
To expose my problem, here is a ...
1
vote
0answers
46 views
Query performance in describing a race's relative finishing positions
Is there a faster, and more parsimonious way to do this?
For each race (i.e. 1, 2, and 3), there are many race entries X (horses), some details including it's finishing position, and in the same row ...
0
votes
0answers
32 views
Is there any way I can optimize this merge ranges function?
I have this this function which takes in an array of tsranges, and convert them into one array with tsranges, where each range contains all overlapping ranges from the input.
Version: 12
https://...
0
votes
0answers
25 views
Datawarehouse design for searching (and not analytics)
I'm looking for input on data warehouse designed focused on querying/searching and not analytics. Eg. aggregations especially on time are not a concern or point of interest. Really about searching the ...
0
votes
2answers
27 views
Is pg12 cache used with two SELECT on same table but different fields
Figure a table with fields a, b and c
Figure two requests on this table :
SELECT a, b from table
SELECT b, c from table
Is postgres able to reuse cache for the second request?
1
vote
2answers
35 views
Postgresql update slow on a relative small/medium table
I'm trying to run an update on a table with 10 million rows (I just need to update a few), but the query is taking more time than I would like.
This is the table deinifition:
CREATE TABLE ...
0
votes
1answer
77 views
How to optimizate COUNT query with multiple LEFT JOIN
I have query that select filters for current category and show how many products in each filter are
Atm i have 20k products and 10k filters for test that take around 1sec. to execute that is not to ...
1
vote
2answers
74 views
Improving sql query execution time for 3 billion rows table
I have a 2+ billion row mysql table. I am also writing 1 million rows each day. The table contains some large columns like varchar(255) (to hold long urls)
In order to perform analytics on this table ...
0
votes
1answer
38 views
handling primary key with large tree, best way to get root id from child table X
I always used database design using this pattern;
root table with PK id1
child1 table with PK id2 FK id1
child2 table with PK id3 FK id2
child3 table with PK id4 FK id3
etc...
so if I have to get ...
6
votes
3answers
484 views
Is there a better way to handle a multi-level ParentId table structure?
I work for a publisher, and our products are mainly books and journals. They are most commonly structured as follows:
Book > Chapter
Book Series > Book > Chapter
Book > Volume > ...
0
votes
2answers
49 views
Is MySQL trigger for 100 Millions daily updates can impact data base cluster performance?
I have a MySQL (Aurora 5.7) table in data base which has
700 Million rows
9 Indexes
25 Columns
100 Million daily updates
and 5 Million daily inserts
Overall this table is very busy and now we need ...
0
votes
1answer
20 views
lock and get next n records from table in oracle
I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS
PHONE_NUMBER
PHONE STATUS
1 U
2 O
3 U
U stands for used , O is open/UnUSED .
in my input I will get n as input ...
0
votes
1answer
48 views
PostgreSql subquery evaluated without filtering
I am struggling to optimize the following query:
SELECT tbA.id, tbB.createDate
FROM
tbA
LEFT OUTER JOIN
(SELECT id, MAX(createDate) as createDate FROM tb GROUP BY id) as tbB
ON tbA.id=tbB.id
WHERE ...
0
votes
2answers
60 views
Slow query with where and index on some fields
I'm working on a project with the PostgreSQL 12 database.
I have a table of about 500 MB.
The structure of the table is as follows:
create table table_name
(
id serial not null
constraint ...
0
votes
2answers
50 views
Complex `where` dramatically affects query performance
There are much more complex queries running on these tables that are much faster but this one takes 90+ seconds to run.
What am I doing wrong here? How can this be improved?
SELECT
r.id ...
1
vote
0answers
48 views
Why does a GIST index on a cube column in PostgreSQL actually make K-Nearest Neighbor (KNN) ORDER BY queries worse?
Adding a GIST index actually seems to make K-Nearest Neighbor (KNN) ORDER BY queries on cube columns worse in PostgreSQL. Why would that be, and what can be done about it?
Here's what I mean. In a ...
1
vote
0answers
13 views
Prevent Certain Types of Queries (Redshift)
Some IDEs will allow users to "peak" at data by hitting some button in a menu, and under the hood the IDE performs a select * without a where clause. This can be a problem because our ...
0
votes
2answers
60 views
High CPU usage on Azure SQL Server ~1.6m rows
I'm currently running into an issue with one of our databases in Azure where 1 table is forcing us to scale the entire database up to cope with the CPU requirements. The basic structure of the table ...
