Questions tagged [optimization]

In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.

Filter by
Sorted by
Tagged with
0
votes
1answer
22 views

MySQL 8 - optimize all tables + indexes

I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have ...
-1
votes
1answer
50 views

Long Running Query Optimization

I need to optimize this query as this query is taking minutes of time in execution. Below is the query. i'm giving date parameters and shift id is optional. as query is returning same in and out from ...
1
vote
2answers
28 views

How to index range condition and ordering by a different colum?

I use PostgreSQL and i have a table like this: create table transactions ( uuid uuid, user_id integer, created_at timestamp, amount numeric ); create index idx_transactions_user_id_created_at ...
6
votes
1answer
224 views
+100

Demystification of SQL Server optimization process

We would like to see all variants of query plan considered during a query optimization by a SQL Server optimizer. SQL Server offers quite detailed insight using querytraceon options. For example ...
1
vote
2answers
64 views

How to optimize Mysql database of 250 million rows for bulk inserts and selects

I am a student, who have been given the task of designing a database for sensor data. My university currently has a large database which is being filled with these data, but a lot of what is being ...
9
votes
2answers
432 views

Building an execution plan takes too long on SQL Server

I have an SQL Server 2014 instance (12.0.2000.8) and a quite complex SELECT statement with about 20 joins. This query works fine with the same data set on PostgreSQL, Oracle and other databases, and ...
0
votes
1answer
27 views

How long does query optimisation take relative to the execution time?

Could anyone please point me to a benchmark comparing the time spent by the query optimiser relative to the overall query execution time (any relational DBMS will do, but PostgreSQL would be ...
1
vote
1answer
149 views

Optimizing concurrent read performance

I wrote a program to test concurrent reads to a single database table. I'm expecting high amounts of read (no deletes, inserts, or updates) traffic in the future, using ad hoc queries, which is why I ...
0
votes
2answers
54 views

Postgres: Performance: Slow index scan

I have a really big query, that queries data from various tables. I have tried various approaches to optimize the query. Made possible LEFT JOIN as LEFT LATERAL JOIN, so that the lateral join queries ...
1
vote
2answers
64 views

Detection of contradictions

I found some old notes of mine from 2011 and I also re-read the excellent article 10 Cool SQL Optimisations That do not Depend on the Cost Model by @lukas-eder. So I thought I would take my notes for ...
0
votes
2answers
50 views

Mysql query time massively affected by limit

I have this mysql query, where I am getting 250ms query time when the limit is 500. If the limit is 1000, it's 1,5 seconds if the limit is 1300 , it's 2,5 seconds select hotel_code, ...
2
votes
1answer
48 views

Optimizing cloud server (php and mysql)

I am running a vBuletin forum as a hobby and I was using a shared hosting service until a couple of days ago. Because the amount of visitors grew and the forum seemed to be slower and slower every day,...
1
vote
0answers
123 views

Anomalous performance of mass INSERT in Oracle

Can you please help me diagnose, explain, and possibly solve the problem of unstable peformance of mass insertions in Oracle? I need periodically to load large amounts of data into an Oracle database. ...
15
votes
3answers
3k views

Colleague says to never use an OR statement in SQL, is this true?

My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't ...
1
vote
2answers
40 views

Does MySQL use its index metadata to optimize COUNT(*) queries?

Let's consider a user_actions table with a standard indexed primary key user_action_id. I want to know the number of user actions up to a certain key (say 100 millions): SELECT COUNT(*) AS `...
0
votes
2answers
37 views

Optimize duplicated but negated subquery

I have what I call a 'Hamlet' query in the form: select * from stuff a left join otherStuff b on (a.x=b.x) -- lots of joins ommited where (b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
0
votes
1answer
20 views

check if each value of array are equal

I try to get d.id only if all rows with same id have same pday: with dif_zero as (d.id, date_part('day', p.docdate)::text pday from sh_billing.dogovor d join sh_billing.pays p on p.dogovor_id = d.id ...
0
votes
1answer
54 views

Decrease Data IO operation on query to lower the DTU cost on Azure

We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer ...
0
votes
1answer
22 views

Statistics not collected on Insert-Select

Based on the information here, I expected the statistics to be automatically collected during an Insert-Select operation on a new table in Oracle. However, I don't see any table-level statistics ...
3
votes
1answer
59 views

Query optimization - unread messages in all channels of a conversation

I have a slow query which I'm trying to tune. Tables in DB (mssql): Users, ChatGroups, ChatMessages, ChatGroupChannels, ChatMessageSeenFast The query: WITH messages_ranked AS( SELECT p.*, ROW_NUMBER(...
0
votes
1answer
45 views

Postgres query slow despite index being used

I have the following tables: The main lead table with close to 500M rows: create table lead ( id integer, client_id integer, insert_date integer (a ...
0
votes
0answers
18 views

SQL Server Persisted Computed Columns, Issues during Mass Insert or Update?

I have a database with a computed, persisted column which validates columns, checks if its an integer, and converts. Sometimes streetnumber is alphanumeric, like 5C, other times it's simply an integer....
1
vote
1answer
46 views

PostgreSQL Sorting an output of a fulltext search query takes very long time

I have a table with a few millions of rows where I perform a SELECT with a complex fulltext search query in the WHERE clause (over a fulltext search index column) and with ORDER BY id DESC at the end. ...
2
votes
2answers
101 views

SQL Server Index Scan when using 'OR' operator

We implemented a Google-style search where SQL queries are run after a debounce is triggered from the front-end. (We know SQL is probably the wrong technology for this, but I'm knee-deep in startup-...
1
vote
1answer
31 views

How to run my query on a one-to-many relation to run efficiently

I have two tables in one-to-many rel, I want to run a query efficiently returning data from the base table and some aggregates from the joined subtable. Adding in some indices, I still did not manage ...
1
vote
2answers
111 views

Postgres NOT NULL optimization

I am trying to optimize this SQL query: select topics.id from "topics" left join "articles_topics" on "topics"."id" = "articles_topics"."...
8
votes
1answer
902 views

Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?

I am trying to work out ways to improve the performance on a very slow query. It has more obvious problems but one thing I noticed is that one of the conditions in the WHERE clause is 'AND t.data IS ...
0
votes
1answer
88 views

Query takes long time to execute while there is an index configured

We have a table with 190M records. there is an index on a fairly unique column but still, if I run a query on the table selecting top 10 with only the indexed column in the where and select clause it ...
11
votes
2answers
406 views

Inner join elimination inhibited by prior outer join

Synopsis: Inner joins that can be logically eliminated are instead retained if there is an non-eliminated outer join earlier in the logical tree. Why? Examples run in AdventureWorks2008R2 and later. ...
3
votes
1answer
137 views

Postgres Query Optimization, high cost functions

Currently I'm working with a postgres table that looks like this (postgres12) create table if not exists asset ( id text, symbol text not null, name text not null primary key (id) ); create ...
3
votes
1answer
52 views

What is search space when it comes to query optimization?

I am quite new to learning about databases and I have come across the term "search space" in the context of query optimization. I am a little unsure of what the term means and why it is ...
3
votes
1answer
45 views

What exactly does PostgreSQL (or other databases) do internally when you “prepare” a query rather than just running it directly?

When I face a huge number of repeated INSERTs in a loop, I tend to first create a "prepare skeleton query" prior to the loop, and in the loop, I simply "execute" this prepared ...
3
votes
1answer
69 views

Query optimisation

I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many Now I make one select from orders: SELECT "id" FROM "orders" WHERE "orders"."...
7
votes
3answers
502 views

Consistent query performance over time

We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of ...
0
votes
2answers
59 views

How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?

I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys ...
0
votes
1answer
56 views

Have I misunderstood the amount of “automatic optimization” that PostgreSQL does without me having to be “clever” when designing my tables? [duplicate]

Suppose I have a table such as this, for logging all web requests made to my website: CREATE TABLE weblog ( id bigserial, moment timestamp with time zone NOT NULL DEFAULT now(), ...
1
vote
2answers
63 views

Creating an Index based on Execution Plan

I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the ...
5
votes
1answer
107 views

Logical trees: difference between Before Project Normalization and Join Collapsed

The Before Project Normalization tree follows the Join Collapsed tree. Using a sample set of ~250 queries - and Traceflags 8605, 8606, 8607, 3604 - I have not detected any difference between these two ...
1
vote
0answers
23 views

Why does adding an index increase the execution time in SQLite?

I'll just show you an example. Here candidates is a table of 1000000 candidates from 1000 teams and their individual scores. We want a list of all teams and whether the total score of all candidates ...
1
vote
0answers
46 views

Correlated subquery with common table expressions in SQLite

Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
0
votes
1answer
17 views

Which SQL table statistics are used after they are updated when NORECOMPUTE is turned on?

Background: I have a large table whose values are significantly rewritten with a switch ~once a week, but the table's primary key values remain relatively constant. After the switch, statistics are ...
0
votes
1answer
39 views

How to benchmark Switchable Optimizations in MySQL

I want to benchmark the performance of Switchable Optimizations in MySQL, i.e., comparing the query latency and the result of EXPLAIN with/without using these optimization switches. I have already ...
0
votes
1answer
61 views

Avoid duplicate WHERE clause on both sides of a LEFT JOIN, without changing semantics or impairing query optimization

I have a table recording the results of a web crawl. (Table definition at the end of this question.) The relevant part of the data stored in it looks like this: experiment_id | url_id | ...
2
votes
2answers
60 views

How to create index to improve performance of an aggregate function that creates a table in oracle

I am creating an Oracle ORDS API using APEX_JSON. I recently started using bind variables instead of string concatenation using ||. I am trying to use an in clause in my where condition. The problems ...
0
votes
0answers
17 views

High-performance query of two tables with count from second table?

I am searching for a high-performance query using two tables to output all locations (name) from country (adm0 = 'DE') in the geodb_locations table, sorted in descending order by the number of users ...
1
vote
0answers
30 views

Unexpected result by MySQL Optimizer for identical queries?

Following query is working like expected and uses index Query takes 0,0481 sec SELECT geodb_locations.name, geodb_locations.name_url, COUNT(user.uid) AS useranzahl FROM user LEFT ...
0
votes
1answer
55 views

Optimizer logical trees: Order of the different tree types?

Based on observing output from Traceflags 8605, 8606 and 8607 (and comments from i-one), I have this as the order in which the logical trees are generated. Converted Input Simplified Join-Collapsed ...
1
vote
1answer
27 views

How to improve performance merging multiple table via cursor

I have the following stored procedure to merged multiple tables into 1 table. The records that im tryin to merge is about 150M. The procedure is quite slow. Here is the query BEGIN DECLARE done INT ...
2
votes
2answers
75 views

Optimizing my query

Here's my query: DECLARE @monthStartDate date; DECLARE @monthEndDate date; SET @monthStartDate = DATEFROMPARTS(2020,6,1); SET @monthEndDate = EOMONTH(@monthStartDate); SELECT @...
1
vote
2answers
99 views

Why is index not being used? Can I replicate plan?

I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment. Specifically, in the UAT environment the ...

1
2 3 4 5
37