Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
51