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
2
votes
1answer
37 views

Query optimization issue on view with RANK on DateTime for most recent rows

I'm having a problem with a view, that is designed to store sync data, for an iOS app, that syncs data back and forth to a web service. The XML fields are used to store both structured data for ...
1
vote
2answers
43 views

Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE?

I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with Parameters being passed in: @ID int ,@OtherID INT ...
0
votes
1answer
12 views

What's the optimal way to query data using another table column as a between filter

sorry for the ambiguous question title. I don't know what's it called. The thing is let's say I've a order table storing uid , order_id and ip looks like this | uid | order_id | ip | 10000 | ...
0
votes
0answers
31 views

Optimize/Speedup query

Below query is used for inserting and updating the tables in the mssql database. The xquery is slow while executing in ssms for first time. query: insert new Update BalanceTable set [...
3
votes
1answer
77 views

Aurora PostgreSQL database using a slower query plan than a normal PostgreSQL for an identical query?

Following the migration of an application and its database from a classical PostgreSQL database to an Amazon Aurora RDS PostgreSQL database (both using 9.6 version), we have found that a specific ...
0
votes
1answer
23 views

MySQL: Should I keep a running total column in one table instead of summing a second table?

I have 3 tables named users, achievements, and userAchievements. The users table contains information like username, password, e-mail, etc... and the achievements table contains a list of possible ...
0
votes
2answers
28 views

Inherit INNER JOIN fields in MySql SELECT QUERY

I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY. Here's what i got so far: In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC ...
4
votes
2answers
193 views

Execution Plan Warnings cardinality xml data type

I have the following statements: -- 1st DECLARE @AuditParameters XML = ( SELECT 1 AS AccountID, 2 AS CategoryID, 3 AS CategoryAttributeID, '4' AS SyncBatchGUID ...
0
votes
1answer
58 views

Implementing MySQL tuner suggesstions

Mine is GoDaddy VPS with 4CPU, 8GB -200GB Centos server with WHM. I am using latest Moodle with MySQL version 5.7.29 on 64-bit architecture. I was advised to tune DB for better concurrent usage. I ...
1
vote
2answers
73 views

Is this query too slow regarding the job it is doing?

It takes around 6 seconds for the following query to execute on a MacBook Air with 1,8 GHz Dual-Core Intel Core and 8 GB 1600 MHz DDR3, running mysql Ver 14.14 Distrib 5.7.27. I would like to know if ...
0
votes
0answers
10 views

Optimize table on large table online DDL Feature

I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB. mysql> show variables like '%tmp%'; +----------------------------+------------------+ | Variable_name ...
0
votes
1answer
60 views

Slow query when matching columns from second table in JOIN with GROUP BY

Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute ...
0
votes
0answers
24 views

SQL Query assistance (plan link pasted) [closed]

I have a query that I was able to take down from 6 minutes to 3 minutes - Just curious if anyone can see if there is anything additional I can do w/ this query or if I am missing something simple. ...
2
votes
1answer
69 views

Why does MySQL take so long to execute this query on a large dataset?

Been banging my head against the wall on this one for a while now and hoping someone can help me out. The SQL query: SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `...
0
votes
0answers
19 views

Properties of grouping in relational algebra

The properties of relational algebra (commutativity, associativity, distribution) allow us to take a relational algebra expression and transform/rewrite it into another one which is logically ...
0
votes
1answer
45 views

Optimizing calculation query

In a legacy project I've the following query: SELECT concat(SR_PO, '-', SR_RlsSequence) as Order, count(*) as Todo, sum(isnull(SR_Enabled,0)) as ToLoad, sum(isnull(SR_Done,0)) as ...
0
votes
0answers
15 views

Filtering on JOIN condition before executing GROUP BY

Have the following function: CREATE FUNCTION Game.operator_quotes(i_operator_name TEXT, i_locale_id SMALLINT) RETURNS TABLE ( name TEXT, rarity SMALLINT, quotes JSONB )...
16
votes
3answers
8k views

SQL Server - Select most recent record from each group when performance is critical

I run a SQL Server 2016 database where I have the following table with 100+ millions rows: StationId | ParameterId | DateTime | Value 1 | 2 | 2020-02-04 15:00:000 | 5....
0
votes
2answers
28 views

MySQL: SELECT more data than necessary or use multiple SELECT queries?

I'm wondering if it would be better to use a single query and SELECT more data than I might need, or use multiple queries and SELECT only what I need when I need it. For example when the client ...
0
votes
2answers
31 views

Query on large database much slower on HDD

I have the following table: CREATE TABLE `Geo1` ( `id` int(8) UNSIGNED NOT NULL, `nameRef` int(8) DEFAULT NULL, `placeRef` mediumint(7) UNSIGNED DEFAULT NULL, `category` enum(...) COLLATE ...
0
votes
2answers
52 views

How can I prevent the SQL Server Query Optimiser splitting a remote query?

I am using Microsoft SQL Server and I have a query which reads data from a remote server. This query reads data from 3 different tables across two different databases, all of which are on the same ...
0
votes
1answer
16 views

Optimizing a user retention query

I'm trying to automatically calculate a table for user retention, so that the end result is something like: Week | % 0 | 100 1 | 50 2 | 35 For now I'm not bothering with the % and just ...
0
votes
1answer
56 views

MariaDB Optimization with Mysql Tuner

I've read so many articles regarding MariaDB optimization for low memory servers. There are a lot of guides about that but people are always sharing their own configuration. That makes it difficult to ...
0
votes
1answer
29 views

Query with left join not using index

The below query is taking about 1 second to run SELECT `taxi_driver_mapping`.*, concat("dpk00", taxi_driver_mapping.mapping_payment_plan_id) AS package_text, `people`.*, ...
0
votes
1answer
33 views

Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will ...
0
votes
1answer
30 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,...
0
votes
2answers
29 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
22 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
80 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
55 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
44 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
48 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
52 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
31 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
29 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
vote
1answer
33 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
27 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
48 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
126 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
70 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 ...
0
votes
3answers
190 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
255 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
241 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 2 3 4 5 35