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
3
votes
1answer
109 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
0
votes
1answer
99 views

Having four different tables or having one table with one more field?

I have a website with 4 translation (That is Arabic with 4 different Persian translation), and I had exported each of these translations to different tables. Which one of these ways are faster and ...
1
vote
1answer
61 views

Optimisation select with large IN and millions of records in result

I work with a table which consists of 1.5B records. DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU Schema: CREATE TABLE public.trip ( id bigint NOT NULL, cell_to character varying NOT NULL, ...
0
votes
1answer
156 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
0
votes
1answer
44 views

CPU High usage crashing our server

The server where my database is running is suffering from CPU spikes. We're having trouble identifying what is causing these CPU spikes, and consequently how to mitigate them. I've tried adding some ...
1
vote
1answer
200 views

Why would there be varying response times for the same query?

Is there a reason why the same query executed a number of times have huge variance in response times? from 50% - 200% what the projected response time is? They range from 6 seconds to 20 seconds even ...
-1
votes
0answers
20 views

Query Performance Help - HIGH CPU (100%)

thanks in advance for your time and consideration. One of my server is having a harding time, CPU is on 100% because of following query: SELECT DISTINCT t0.fieldn..., t1.fieldn..., ...
0
votes
1answer
94 views

Why a query takes too long in statistics thread state in AWS Aurora MySQL?

The following query execution too long in statistics state and I couldn't figure out why. DB engine - 5.7.mysql_aurora.2.07.2 DB Size - db.r5.4xlarge Sample Query Profile output +----------------------...
0
votes
1answer
49 views

Query data via username - pair - best performance

RDBMS: MariaDB 10.3 Let's say you have a table which stores specific information (info_1, info_2, etc.) according to a given pair of usernames; like so: +----------+----------+----------+----------+---...
-1
votes
2answers
33 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 ...
9
votes
2answers
14k views

Do covering indexes in PostgreSQL help JOIN columns?

I have a whole lot of tables that look vaguely like this: CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER); CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES ...
0
votes
1answer
266 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 ...
6
votes
1answer
240 views

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
votes
1answer
55 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
239 views

What is the recommended my.cnf settings for 32 GB Server

I am just moving my website to a new server and ı want to know what is the recommended settings for 32 GB server? Mysql tunning for my old server (16 GB) https://pastecode.xyz/view/577e824e
1
vote
2answers
65 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 ...
3
votes
1answer
102 views

Commit() takes 50 seconds after inserting about 100000 rows

I have a sqlite database that is, (I think), fairly small. I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb. I then open a transaction and insert about ...
0
votes
1answer
104 views

Fiscal Year to Date filter

This is my data: I need a Fiscal Year to Date logic that will gather all previous months starting October. However, for October 2018 I want to start form 15th, for remaining years it must start from ...
2
votes
1answer
245 views

MySQL InnoDB or MySQL NDB Cluster which one to use

We have an application with 8000 users accessing in parallel(max). We are trying to bring in horizontal scaling. I came across MySQL NDB cluster. Should I go with it? Just for auto sharding and other ...
1
vote
2answers
29 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 ...
3
votes
1answer
22k views

Truncating Table Partitions Containing Data and Global Indexes

I have to truncate a partition (392,000 records) of a table with 20.000.000 records. At the moment, I'm making a truncate table and rebuilding indexes. But the table has 37 indexes and it takes 20 ...
0
votes
1answer
217 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
-1
votes
2answers
121 views

How to implement GROUP BY aggregation with holistic aggregation if partitions do not fit into main memory?

Let's say I want to compute the median of some data given some grouping attributes G, e.g. the median price of all product sold daily (assuming a star-schema) SELECT T.day_id, MEDIAN(price) FROM ...
0
votes
1answer
192 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
119
votes
4answers
113k views

Optimizing queries on a range of timestamps (two columns)

I use PostgreSQL 9.1 on Ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
9
votes
2answers
433 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
84 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 ...
0
votes
1answer
798 views

MySQL CPU usage 128%?

I backed up my site using an all in one WordPress migration plugin from shared hosting and restored on VPS hosting server. Everything worked fine except for 1 MySQL task eating up whole CPU usage.. I ...
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
110 views

MySQL optimization for mapping service — getting MySQL to use more memory?

Time to upgrade hardware? ... I'm not sure if there is much more I can do, spent a few days tweaking/googling -- before taking the upgrade plunge I thought I'd check here... to see if the well ...
1
vote
1answer
151 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
2k views

MySQL crashes and currently the error.log file is huge

I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs ...
1
vote
1answer
187 views

Logic of Assigning values to variables in Scalar-valued Functions

I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs. While troubleshooting my rewrites I came across some ...
0
votes
2answers
61 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 ...
4
votes
1answer
226 views

Execution Plan is different when Constant Variable is Null

I am working on a scenario in which I am providing a variable a value. When I pass Null value, the Query Engine didn't scan the Join tables. As per Logical Query Processing, first FROM Clause Executes,...
0
votes
1answer
120 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 (...
2
votes
1answer
640 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
1
vote
2answers
65 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
1answer
336 views

Database Design for Maintaining Supplier Invoice

I am not sure whether it is right to ask such question here. However, It comes to my mind this would be the best place to start. My needs are quite simple. As a distributor, I want to maintain all ...
2
votes
3answers
315 views

Query to categorize rows based on a “time” column without using a CASE expression

There is a ProductTT table as you can see below: [dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int) ...which contains the following rows: 1 XX 0030 2 UY 0354 3 YY 0517 4 ZZ ...
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
2answers
148 views

Mysql Database optimize suggestions

I have a server with this configuration: Intel Core i5-3550 4 x 3.4Ghz 16GB DDR3 2 x 1000GB Software: nginx php7.4 mariadb wordpress xenforo I have 30.0000 unique visitors and 250.000 pageviews a ...
4
votes
1answer
5k views

How to improve MySQL Server Performance..?

As a MySQL DBA most of the times we are supposed to optimize a poorly performing MySQL Servers. Now my question is where to start from like we will need to find out many things as 1.Find the ...
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 ...
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,...
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
2answers
230 views

usage percentage of Mongodb internal cache

I've ran two performance test on a MongoDB server with identical environment/settings AFAIK. I found that the throughput was 10% apart. When I inspected the mongostat logs, I found that Mongodb in ...
0
votes
2answers
243 views

How to improve query count execution with mySql replicate?

For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table). And its really time consuming when it ...
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 `...
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. ...

1
2 3 4 5
37