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
11 views

Condition filtering estimate calculating double dipped extra value without performing range reduction

In a query like below select e.emp_no, concat(e.first_name, ' ', e.last_name), d.dept_name, t.title_name, es.salary, es.insurance, es.pf, ea.city, ea.state, ea.phone from ...
1
vote
2answers
70 views

How can i re-write below query better [closed]

I am trying to understand better how to improve performance of query written somewhat similar below Yes datatypes and indexes do not match but as a general, i am looking if there is a better way to ...
0
votes
2answers
69 views

T-SQL Query or DB Engine Optimization?

This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second. Any ...
1
vote
0answers
13 views

MySQL condition filtering estimate while planning includes `A.col op B.col` but excludes `A.col op val or B.col op val`

In the condition filtering feature that MySQL introduced from version 5.7, has filter estimates for various condition formats in the join order, to estimate the number of rows that enter the join ...
1
vote
1answer
14 views

Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?

I'm a data scientist who's more on the math and modeling side of things than the engineering side, and I don't know much about the logic, data structures and design principles that underly database ...
1
vote
0answers
26 views

How did we get condition filtering value of 5 percent in this plan instead of 10 percent? MySQL 8.0

Assume the below schema, this is just created to test some features, and not a valid design for production. When I run the below query and check it's optimizer trace, I find lot of plans. select e....
1
vote
1answer
30 views

Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the ...
-1
votes
1answer
13 views

Find closest follower in a rank list

On my database I have the table named Rank_List_T: Rank Name Time 1 John 110 2 David 120 3 Bob 121 4 Hans 124 5 Claus 126 6 Gustav 140 I am interested in the person, who ...
1
vote
1answer
29 views

Slow Database with concurrent queries, Table cache hitrate 0% [Newbie! Need help :)]

I'm a bit of a newbie when it comes to dba work so please take it easy! I have gone through countless threads trying to improve the performance of my database, looking for someone who could give me ...
0
votes
1answer
23 views

Postgres foreign data wrappers weird join behavior

So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins. Keep in mind the following snippets are ...
0
votes
1answer
17 views

Index dive/sampling row count very inaccurate in MySQL

The table emp_address_phone is filled with many random values. CREATE TABLE `emp_address_phone` ( `address_no` int(11) NOT NULL, `emp_no` int(11) DEFAULT NULL, `street1` varchar(255), `...
0
votes
1answer
25 views

Range Optimizer in Mysql: What is an Index dive during Query Optimization?

On following Jorgen Loland post on range optimization, I have come to the concept of "index dive" which is made during MySQL query planning (aka in Mysql Optimizer). Quoting his point, For as long ...
1
vote
2answers
25 views

A SQL query optimzation issue with ORDER BY

An SQL query optimzation issue with ORDER BY Current status I have a database of Chess games and their corresponding moves (stored as strings called fens). I have two main tables 'Game' and '...
0
votes
1answer
32 views

problem with optimizing SQL query

I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query. Here are the relations: CREATE ...
0
votes
0answers
56 views

SQL Server Full-Text Search Order by another Column Performance

I have two tables, Entity and EntityDetails. Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime). EntityDetails table has columns EntityId, Details1, Details2. Where ...
0
votes
0answers
46 views

How to count references to fields given the definitions of views?

I have a bootstrapping script which defines views to be exposed to the data users in public schema. It filters out sensitive fields from the tables in private schema, also makes various aggregations. ...
1
vote
1answer
36 views

Does “Table Valued Function” force the plan to get into cache?

sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?
0
votes
1answer
30 views

UNION ALL vs CASE - SQL query optimization

I need to retrieve the count of both approved and unapproved data from my_table. And I have tried both queries and succeeded. Which query is optimized and why. Please give suggestion DDL Query ...
0
votes
0answers
14 views

do statistics need to be calculated each time or stored?

I have a "user" table in a video-game, which have these properties: Money Experience Number of friends Each of these properties can be either stored in the table, and updated when something happen (...
0
votes
2answers
55 views

Why does table SELECT become twice slower after restoring a db from 5.0.24 to 8.0.20?

I've got a new server so i had to configure MySQL on it, the old server had installed MySQL 5.0.24 so i've done backup of my DBs and restored them on newly installed MySQL 8.0.20 on the new server.. ...
0
votes
1answer
26 views

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how ...
2
votes
1answer
51 views

Optimization of a query to retrieve records randomly with multiple joins and filters

I have the following schema: This question was posted also in StackOverflow, but I want to consult also to specialists more focused on DB administration because the nature of my project. Sorry if ...
1
vote
1answer
80 views

Slow query time after upgrade to MySQL 8.0.20

I apologize in advance if a similar question has been asked. I've been digging through Google and StackExchange trying to find an answer but now I'm stuck and stumped. I am still relatively new in ...
2
votes
1answer
216 views

SQL Server Query with literals runs instantly, using variables takes minutes

For Microsoft SQL Server 2017 (RTM-CU20) on Azure running an ERP system (so changing the code here is not an option). I have a table with over 42 million rows. It's a machine operating stats ...
0
votes
1answer
27 views

Optimize query with a subquery (index is not used)

I have following query: SELECT `status`, `is_client_coordinator`, id, first_name, last_name FROM `users` `t` WHERE t.status!="CLOSED" and t.is_client_coordinator or t.id in (select ...
1
vote
1answer
23 views

How to identify hung queries, their importance, and then take action using command line?

From a production support perspective, how can we find hung queries and then find how important they are and then how to deal with them? I tried only processlist which gives currently running queries ...
1
vote
1answer
17 views

MySQL: Queries limitations per host/user

In MySQL 5.7, is there a way to limit specific type of queries up to a certain limit? select queries for instance to be max 100 query at a time. And is it doable to limit queries per host or user?
2
votes
1answer
30 views

accessing, updating a large MYSQL table advice and guidance

I'm after some guidance or advice really. We've built an application and its gone mental with custom since coronavirus. We are after some advice for something we have never tackled. The problem we ...
1
vote
0answers
9 views

Propagate where condition into CTE of a view, using index?

I have written a multi-step view using CTEs. The first step is a union on the source table (used to turn two columns begin_date and end_date into separate rows); then there is a group by that ...
0
votes
1answer
45 views

Indexing a GROUP BY Select with Multiple Inequalities

I have a pair of linked issues I need help with: Issue #1: How to Index a Table to Optimize for a "Group By" SELECT I have a table of stock price information with the following Create statement: ...
0
votes
1answer
44 views

Schema Design - would you split this table?

Table A - auto-incrementing integer primary key - 3 dates - 17 booleans - 7 varchars (less than 40 bytes in these combined varchars in 99% of rows) - 5 foreign keys Table B - auto-incrementing ...
1
vote
2answers
43 views

Mysql Database optimize suggestions

hey guys i hope you can help to tweak my database setup a lil bit. Server Intel Core i5-3550 4 x 3.4Ghz 16GB DDR3 2 x 1000GB Software nginx, php7.4, mariadb, wordpress, xenforo 30.0000 Unique ...
0
votes
0answers
34 views

Optimise the selection of two distinct column pairs from the same table

I need help with rewriting or editing the query/logic to improve it's performance. SQL Server 2014 I have the table structure specified below. I want to select two column pairs: (SourceID + ...
1
vote
1answer
24 views

Optimize JOIN query with DISTINCT

I have 2 tables, one for links (Links) and one for comments (Comments) where each comment contains a reference to a link (it can also point to NULL if it has no associated link, such comments we ...
0
votes
2answers
42 views

How to improve multiple case when query performance?

I have the following query which counts when the event_time is equal to the day of the month. I also have indexes on the id of each table, template_name, from_email_address, event_time, event_status....
0
votes
2answers
20 views

Optimize MariaDB query with table that self-joins

I have a query that I think is taking way too long (as in I've not yet seen it finish!) but I can't figure out why. The query (which is generated by an application that would be difficult but not ...
10
votes
2answers
732 views

Is there a way to see what were the Candidate Execution Plans generated by the Query Optimizer?

The Query Optimizer creates more than one possible execution plan. How can I see all the plans generated before the executed one was chosen? Microsoft says: The Query Optimizer must analyze the ...
1
vote
1answer
45 views

How to get for a percentage here in below query?

A small change: report for each year the percentage of movies in that year with only female actors, and the total number of movies made that year. SELECT Year, COUNT(*), (COUNT(*)*100/(...
2
votes
3answers
132 views

Why can't SQL Server find a seek plan? Is this a bug?

create table Test1 (Id int not null, H char, primary key (Id), index i1 unique (H)) create table Test2 (Id int not null, H char, primary key (Id), index i2 unique (H)) insert into Test1 values (1, 'A')...
1
vote
1answer
54 views

Postgresql 11 - Storing billions of relationships in normalized table paritions or intarray[] column

Some pre information Postgresql Version 11.2 GCE CPU: 6cores SSD: 250gig RAM: 64gigs We are currently deciding on how to store massive amounts (in the billions) of links/relationships between items ...
2
votes
1answer
77 views

Query planner enerates different plans in psql and application for the same query

I am optimising an application on a development server; the database is being dropped and restored from time to time (always the same database and data) and the caches flushed. I want to identify ...
1
vote
1answer
37 views

MariaDB - Slow GROUP BY on 55 Million row table

I have a table with ~55 million rows, which holds user music listening history. I want to generate a report for the last quarter detailing how many times songs have been played per user. The query ...
8
votes
2answers
486 views

EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan

The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT. What is the purpose of the projection? e.g. Is there a different EXCEPT ...
4
votes
1answer
77 views

Understand why the query plan was changed [duplicate]

We had some performance issues with our ERP system. Somewhere around 06:00 the problem started. Long story short; one of my colleagues executed a DBCC FREEPROCCACHE (I know that this is not a ...
0
votes
0answers
55 views

Calculating historical data over date ranges for up to 1 million rows

I need to be able to calculate the average sales of employees over a period of time. At the moment, I am doing this by using MySQL to get the total sales per employee per day: SELECT ANY_VALUE(...
0
votes
0answers
50 views

Is there a way to optimize the query if we are using ROUND function 100 times in a single query

The below query is to compare the last day of the month and select either zero or a value. I am not pasting the entire query as its huge and the i am just pasting the condition which if possible can ...
3
votes
0answers
103 views

Apply cardinality estimation problem in sql server

Now, I am faced with the problem of the logic of cardinality estimation that is not quite clear for me in a seemingly rather simple situation. I encountered this situation at my work, therefore, for ...
0
votes
1answer
23 views

Is it correct way having status or Summary columns in parent table or should choose another precedure?

My Parent Table is "Jobs" table, and there is child table that records Job's actions named "JobActions" as below: Jobs (JobId, JobType, CurrentStage, AssignedTo) JobActions (JobActionId, JobId, ...
0
votes
0answers
13 views

When will 'Max_execution_time_set_failed' status variable be incremented in Mysql?

Server: Mysql 5.7 By documentation, it has been said that The number of SELECT statements for which the attempt to set an execution timeout failed. Can anyone explain the scenarios or share ...
0
votes
3answers
63 views

Why does SQL Server not use the Index it recommends?

The query I had a specific query I wanted to optimize: SELECT * /*12 columns*/ FROM [dbo].[EnterpriseGroup] WHERE (EnterpriseGroup.ChildId = 123 OR EnterpriseGroup.FatherId = 234) AND StatusCd &...

1
2 3 4 5
37