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
0answers
21 views

Need help with indexes on 2 slow wordpress query

Here are my my 2 slow query i want to improve. SELECT object_id, term_taxonomy_id FROM wp_term_relationships INNER JOIN wp_posts ON object_id = ID WHERE term_taxonomy_id IN (525627,516360,525519,...
-1
votes
0answers
16 views

How can I improve and tweak my mysql using mariadb 10.5 -| mysqltuner results [closed]

Here are my mysqltuner results: >> MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run ...
0
votes
2answers
24 views

Need help in optimizing a slow Postgres Query

So, I need to calculate a rolling Monthly active users on everyday for last 100 days. In output, one column should represent the dates of last 100 days from today and against every single day, number ...
0
votes
2answers
17 views

How does MySQL skip ranges (nodes) in a loose index scan?

I've read the MySQL loose index scan, and it makes sense as optimization, however, when using an example B+tree, I don't understand how nodes can be efficiently skipped, due to potential matches ...
2
votes
2answers
76 views

How to make DDL changes to large tables and cause minimum server and object contention?

I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
0
votes
2answers
52 views

How to store daily records in SQL Server 2017 database? [closed]

So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries. Example:...
2
votes
1answer
38 views

Strange performance of a top-N query with Postgres

I have a table transfer_event from which I need to find the latest n entries for a set of tokens; both n and the tokens are supplied by the user. I am using Postgres 9.6. The question is that if I ...
2
votes
1answer
43 views

Finding stored procedures with XACT ABORT ON OR OFF

I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog ...
1
vote
2answers
38 views

Are there side effects to Postgres 12's NOT MATERIALIZED directive?

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
0
votes
0answers
28 views

optimize query with fulltext index in mysql

I have a column inside my mysql table that has a fulltext index and I need to make a query in which I get all the rows that do not have a specific word or those that start with those letters, so I ...
0
votes
0answers
25 views

Small read cache on SSD (MySQL)

I have a question concerning performance settings/setup in MySQL DB/InnoDB. The server has SSD disks so general access to data is fast. Although there is enough of RAM, but enough is never enough and ...
0
votes
1answer
23 views

How to avoid index_merge in a query?

I have multiple queries using index_merge but for put an example, I show you the following query EXPLAIN select count(*) From Clientes Where id_campana='1849' and id_empresa='55' and (...
0
votes
1answer
18 views

Multiple query to columns indexed in MySql

I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure: +----------------+ | products | |----------------| | ...
-1
votes
0answers
53 views

Query optimization (Index spool)

I have a query that need to be optimized. You can see the query plan here There you can see that 85% of the cost belows to an Index Spool but I cannot find the way to optimize it. There is a ...
1
vote
1answer
31 views

Why is it important to carefully determine and select statistics for the query optimization process?

It's a general question rather than a db engine specific one. In popular db engines, we see the query optimization process taking statistics into consideration. I'm trying to understand what types of ...
0
votes
0answers
25 views

Mysql: extremely weird index behaviour on “SELECT WHERE IN”?

I've just been stuck for a bit on a couple of very weird index choices from mysql. I have a table with half a billion entries and primary key is an auto inc ID Original query: "select id from table ...
0
votes
1answer
19 views

Optimal Table Structure

We have a parent table, Journal and a child table, Stops. A Journal can have X amount of Stops. For a good 80% of our SQL queries, our customers are just interested in the first and last Stop for ...
-1
votes
2answers
46 views

MySQL Select Query not using Required Index

I have a Table which has 2 Million Records and No primary or Unique Keys. I added Two Indexes and I'm expecting an Index to be used for my Select Query. When I saw the Explain Plan , Optimizer is not ...
0
votes
0answers
10 views

Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial. Or in other words, does the query optimizer ...
2
votes
1answer
124 views

Mysql GROUP BY is really slow on a simple view

I have these tables. CREATE TABLE `movements` ( `movementId` mediumint(8) UNSIGNED NOT NULL, `movementType` tinyint(3) UNSIGNED NOT NULL, `deleted` tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB ...
1
vote
0answers
69 views

Database - limit of reading a table with a lot of concurrent batch read and writes

We have this table. It contains data which will be picked up by an hourly batch job for processing. Five times a day, there are also multiple batch ETL jobs which get data from other source table and ...
1
vote
1answer
102 views

How to optimize this Select MAX() SQL query?

In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this ...
2
votes
3answers
237 views

SQL Server 2017 Stored Proc & TVF Performance Problem

I have a SQL Server 2017 Enterprise Edition instance where a stored procedure was taking approx. five minutes to execute. After reviewing the stored procedure code, I could see that there was an ...
1
vote
2answers
28 views

Postgres doesn't use expression index for group by

I'm doing a simple group by on a single column with a matching index and it works fine: create index on contacts (home_city); select home_city, count(*) from contacts group by home_city limit 50; ...
0
votes
0answers
28 views

Union the same table. Optimize query

I'm coming with a question about merging two data sets. I am using STI (single table inheritance) in the products table, so one column defines what type of product the specific row stores. The ...
1
vote
0answers
28 views

Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?

I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
4
votes
1answer
235 views

SQL Server chooses unselective index

I was testing SQL Server indexes and found very strange behavior. Here is my code: DROP TABLE IF EXISTS dbo._Test DROP TABLE IF EXISTS dbo._Newtest GO CREATE TABLE _Test( ID INT NOT NULL, ...
1
vote
1answer
38 views

Explanation for second Compute Scalar

I noticed a peculiar thing. This query declare @t int = 3; select distinct top(@t) Number - Number + ABS(CHECKSUM( CAST(NEWID() as binary(16)) )) from Numbers as n; produces this plan dbo....
0
votes
0answers
23 views

SARGABLE predicate must be in DNF. Why?

This famous paper about finding Access Path in Relational Database Management System by Selinger et al. says the following about criteria tree and predicates (at the end of 3rd section). A sargable ...
-2
votes
1answer
53 views

Optimize query with nested inner query and case statements [closed]

I have below query written by someone targeting Microsoft SQL server 2017. I do not have access to database to create indexes etc., So only way I can do is optimize below query as efficient as ...
0
votes
1answer
60 views

Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?

If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored ...
2
votes
2answers
36 views

Postgresql expanding view multiple times instead of executing it once

I have a rahter long subquery which I store as a view CREATE VIEW scans_minmax AS SELECT tmp.source, value ->> 'Ok' AS "Ok", ...
0
votes
2answers
38 views

How to write SQL queries to populate the tables while synchronizing transactions and account balance?

I have two tables having the following schema. CREATE TABLE users( `id` INT AUTO_INCREMENT, `balance` DECIMAL(10, 2), PRIMARY KEY(`id`) ); CREATE TABLE user_transactions( `id` INT ...
1
vote
0answers
30 views

Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error [duplicate]

I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX) Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a ...
2
votes
2answers
74 views

Postgresql Query plan different on two servers

I'm having some strange issue of running the same query between prod and dev and receiving a bit of a different query plan and I'm not sure why. I'll try to cover everything I can, but if I forgot ...
0
votes
1answer
26 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
1
vote
0answers
61 views

Slow MySql with left join and Group by

Below is the Mysql 5.7 Table query which has a left join with the group by. When I execute it takes more execution time. line_items have around 35 Lakhs (3.5M) data and users have 1000 rows. Below is ...
1
vote
1answer
69 views

Is there a maximum IOPS for MySQL asynchronous replication on the slave server?

I have been struggling with this issue for a while now. I have a simple Master-Slave setup with MySQL 8.0.15 installed on both systems. In day-to-day operations the slave keeps up the Master with no ...
1
vote
1answer
124 views

How can Inner join be optimize? [closed]

I have a table where there are in some cases twin records. In the table, I have the following columns: id (identity column) action_id session_id row_id timestamp ...and more records. I need to ...
4
votes
0answers
54 views

How to set up a solid MySQL/MariaDB Server for +3000 active connections? [closed]

Background. I have an App connecting to an RDS instance on Amazon (r3.Large) and it's working smoothly. However, the boss does not like to spend ~ $1,000 bucks a month on it so we are moving In-house....
6
votes
2answers
114 views

Why do we have Top N Sort in this example?

In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top? use tempdb; go create table dbo.t1 (id int identity primary key, v int); ...
0
votes
1answer
32 views

Is there a way to figure out an executing query's plan handle by the query text?

Is there a way to determine the plan handle for the query plan of an executing query by that query's text? Perhaps in any of the DMVs? (I'm trying to find the query stats on an ad-hoc query.)
1
vote
2answers
34 views

Infix search on large table when majority of rows can be filtered out

We have a Postgres table, which looks like this: +-----------+---------+---------+---------+ | CLIENT_ID | VALUE_1 | VALUE_2 | VALUE_3 | +-----------+---------+---------+---------+ | 11234 | ...
1
vote
1answer
33 views

When SELECTing an expression that is an expression based index does the expression get processed? [closed]

I am asking this mainly for postgres expression based indexes but would be interested to know for other SQL implementations that have expression based indexes. Example: CREATE INDEX people_names ON ...
3
votes
3answers
160 views

PostgreSQL NOT IN array slow query

I have a large table with millions of rows. Each row has an array field tags. I also have the proper GIN index on tags. Counting the rows that have a tag is fast (~7s): SELECT COUNT(*) FROM "...
0
votes
2answers
45 views

Postgres using seq scan with filter on indexed column + EXISTS on related table

I've following query which takes about 25-35 seconds to execute: EXPLAIN (analyze, buffers, format text) SELECT booking.* FROM booking WHERE booking.reference_number = '9999999999' OR booking....
2
votes
1answer
37 views

How to get more physical details when executing an SQL query?

I want to get some physical features about a SQL query execution with PostgreSQL, like Read/write page accesses I/O operations Response time Sequential I/O throughput rate Deadlocks Pages scanned ...
5
votes
1answer
1k views

Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table? If I have a table where some of the rows will be ...
7
votes
1answer
473 views

Is there a reliable way to determine how long it took the optimizer to generate a query plan?

I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
9
votes
1answer
228 views

Is it possible to re-use a query plan from the plan cache from one database in another database?

For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query ...