Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [optimization]

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

0
votes
0answers
6 views

How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

I wrote the following SQL query to group results and then sort results in each group by date: SELECT origin, provider, SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY departure_date ...
-2
votes
0answers
28 views

How would you do this simple query in the most optimized way MYSQL and also MARIADB

pkid clientname location 1 Jones NY 2 Jones NJ 3 Jones TX 4 Jones FL 5 Jones IA 6 Jones NC 7 Jones SC 8 Jones VA 9 Smith FL 10 Smith CO ...
0
votes
0answers
22 views

Query top and specific ratings for table in postgresql [on hold]

Say, for example, we have players with their current level and timestamp when this level was got. Table looks like: CREATE TABLE tst_ratings ( player varchar PRIMARY KEY, level integer, ...
0
votes
0answers
9 views

Join hive tables which are being populated in real time

I have two tables in hive which are being populated in real time from a set of files. There is a request_time and trx_id columns in both tables where trx_id is the joining key. I want to join them ...
1
vote
1answer
46 views

SQL Server Query Store - Force Plan does not work

Here is my situation: I have a simple SP (containing giant joins and a few temp tables), which essentially returns a resultset, works in 15 seconds in 2008 compatibility level, but takes 3 hours when ...
0
votes
0answers
8 views

Postgis KNN in Postgres 9.5

I'm trying to use the Postgis KNN functionality as described here: https://postgis.net/docs/geometry_distance_knn.html I have one table with all point geometries. The table has many samples, and in ...
2
votes
1answer
50 views

Calculate difference between current value and next value without CTE/ inline table

I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits. With Visits AS ( SELECT [uid],[...
0
votes
0answers
26 views

Optimise multiple inner JOIN

Consider the following query: mysql> SELECT GROUP_CONCAT(a.id SEPARATOR '\n') -> FROM assignment a -> INNER JOIN statements s ON a.id = s.assignment_id -> INNER JOIN ...
3
votes
1answer
152 views

Adding an additional “order by” column gives me a much worse plan

in other words, how can I get rid of the sort operator on the picture below? the picture above shows the execution plan of the following 2 selects together: SELECT TOP 1 so.OrgType,...
0
votes
0answers
18 views

How to optimize a query with select, row number and a lot of LIKE

I try to find search rank of a product in today's product history records. For this I use 6 ILIKE and 2 inner join. I have indexed all columns but still so slow. It takes approximately 10 sec. to ...
1
vote
0answers
38 views

What is the best option for a large slowly changing dimension lookup table

This question is an extension of a previously asked question Index Strategy and Query optimization, which further extends this question: Search between IP string addresses I implemented the two new ...
-1
votes
0answers
81 views

Selecting data from Oracle is delayed because of parsing SQL

my procedure selects data from from Oracle database using procedure which consists of complicated SQL statement. It usually returns result in 30 ml but sometimes it needs more then 20 s. During this ...
1
vote
2answers
42 views

What is the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives

A Microsoft Technet article recommends creating a secondary file group as the default file group (see reference below). The secondary file group should have a number of files, say four, that are each ...
7
votes
2answers
102 views

SQL Server creates different plans when OR condition is re-arranged

I was reviewing an under-performing query which looks like this: WHERE manymany.Active = -1 AND manymany.Check1 = -1 AND manymany.WebsiteID = @P1 AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany....
0
votes
1answer
21 views

Filter predicates executing before Access predicates

This is oracle 11g. When I try to do a select query on a table, in the explain plan option I see that the filter predicates of the query(predicates on column without indexes) are executed before the ...
3
votes
0answers
52 views

Seeking Help Optimizing Self-Joins

This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the ...
0
votes
2answers
30 views

How does MySQL store data, can I detach a mysql Database?

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
1
vote
1answer
38 views

Advice on fine-tuning query and index for performance

Currently my query is as below: select id_request,username_request from darkhast_follower where darkhast_follower.id_request != '9762952594' AND darkhast_follower.status =0 AND NOT exists (...
0
votes
2answers
79 views

Optimize MySQL update statement

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.1 million rows. I have ...
1
vote
2answers
26 views

How to most effectively get the first preceding/subsequent entry outside a given time interval?

We have a SQL Server DB that contains a large table (>100M rows) with archive values, basically in the form of [Id], [StationId], [DatapointId], [Timestamp], [Value]. As the stations do not get polled,...
-1
votes
1answer
42 views

MySQL - Optimize a table which has mostly text data type

I recently faced some performance issue on a CMS based application. I can somehow fix other tables. But Im not able to optimize the table which has 40+ columns and 10 columns are having text as the ...
1
vote
0answers
80 views

Optimize multiple JOINs with large data

I have 3 tables with this structure: +-----------+ +-----------+ +-----------+ | Files | | Programs | | Channels | |-----------| |-----------| |-----------| | ID | | ID ...
0
votes
0answers
33 views

DB Structure and Search with weighted ratings

Let's assume the following structure. For each pair of item and category we have a rating (between 0 and 10) item_id | category_id | rating --------+-------------+------- 21 | 31 | ...
0
votes
1answer
55 views

Different manifestations of the same logic yield very different execution times [closed]

Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested. For every widget in the ...
0
votes
0answers
28 views

Improving/ Optimizing Performance Query in MySQL PHP

I have a invoices, invoices_items, order, order_items. Invoices and Orders tables contains around 1 Millions records. Invoices_items and Orders_items tables contains more than 2 Millions records. ...
0
votes
0answers
28 views

add index and change int data type to bigint in mysql 5.7 with 1.3 billion records the fastest way

how do i do this without downtime? or at least minimal downtime (3hrs max) Is there any way to tweak my.cnf config to at least make this process of indexing and change data type faster? Below is the ...
0
votes
0answers
25 views

Left join with same table making query too slow — MySQL

We have a table server_user_disk_usage which tracks the disk usage of users from different servers. Query is to find sum of last disk usage of each user from different servers SELECT m1.user_name, ...
1
vote
1answer
77 views

MySQL: Performance issues in heavy multi-threaded environment

I have 23 of these tables in one database: CREATE TABLE foo1 ( DateTime datetime NOT NULL, BidOpen decimal(11, 5) NOT NULL, BidHigh decimal(11, 5) NOT NULL, BidLow decimal(11, 5) NOT NULL, ...
1
vote
3answers
133 views

Improve Query Performance of Select Statment inside IN Statment

My query is: (3) UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and post_id in ( (2)SELECT post_id FROM ( (1) ...
0
votes
0answers
15 views

any knowledge or experience with dynamic ax [duplicate]

I support a system called "dynamics ax" and sometimes my optimizer picks a good plan, drops it and then picks a bad plan (where the same code is been ran). users who usually wait for 10 seconds start ...
0
votes
2answers
53 views

Is a millions-row query fetched in the SQL Studio client slower than other applications?

I'm not sure if want I'm thinking makes sense. I have a SQL Server database table with 30 million rows. If I do a select A, B, C, D from huge_table --- (about 30 million--- total 4 GB footprint) --- ...
5
votes
1answer
116 views

Why doesn't first execution plan use RowCount Spool?

Here is my repro that was tested on SQL Server 2008 R2, 2012, 2016. The second and the third queries do use RowCount Spool, why the first does not? create table dbo.t (id int identity primary key, v ...
0
votes
0answers
26 views

Mysql performance lagging in Azure VM

I've been having a big mysql performance problem for a while now after a migration from a dedicated server to a cloud VM. The issue ultimately requires production load to test, so it has been ...
3
votes
1answer
55 views

Is this the right attitude from the DBA team? [closed]

I'm a software developer at a rather large company with a small team of software developers. I am the only remaining developer (.NET developer) in the "reports" team of the company. The rest of the ...
2
votes
0answers
24 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
0
votes
0answers
33 views

Why my heap read/heap hit ratio is negative?

I have a database with 200k rows (one of this column is a jsonb data type column), i'm using this database to store some various devices informations (currents, pressure, etc...) and i'm using this ...
0
votes
1answer
26 views

Would querying from a partitioned table be faster than from multiple tables with the same table definition?

I have a table that contains about 50 million rows that I have partitioned by 5 million rows per partition. I have manually partitioned it into Table_1, Table_2, Table_3, etc., not using MySql's built-...
2
votes
4answers
96 views

Should I redesign my “events” table

My system needs to store an append only log of events. Currently I have a database table that stores all the relevant data in one table: CREATE TABLE `events` ( `event_id` VARCHAR(255) NOT ...
0
votes
1answer
23 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
2
votes
1answer
43 views

Why does elimination of an if-statement cause notable speed up in my function?

I have the following table: CREATE TABLE [dbo].[StateChanges] ( [RecordId] uniqueidentifier NOT NULL, [LinkedObjectId] uniqueidentifier NOT NULL, [ChangeType] TINYINT not null, [...
0
votes
1answer
40 views

Is it possible to rewrite this Function for optimization?

I need help optimizing this function as it takes more than 2 minutes currently which is far from optimal. This function basically gives you the name of column, corresponding table and list of unique ...
1
vote
1answer
104 views

Adhoc queries memory consumption in SQL Server

I have a SQL Server 2012 and it has just one (1) database in it. When I generate a server dashboard report it shows that 50% of CPU usage is related to my database and the rest is associated to Adhoc ...
0
votes
0answers
28 views

Where each partition has only one value in the constraint column, can I avoid filtering?

I have partitioned a table in postgres 9.6 by date. Every date within a parition is identical. When I select data, a relatively expensive Seq Scan filter operation happens against every row in the ...
0
votes
4answers
38 views

MariaDB User Define Variable throws warning if it is empty

I have 2 queries: the first query result will be passed to the second query using a user-defined variable. (This implementation is because of a performance issue with the table after applying proper ...
0
votes
0answers
50 views

Slow PostgreSQL query - how to improve it?

[Beginners question :-) hopefully on-topic here.] In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to ...
0
votes
3answers
52 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
0
votes
1answer
43 views

what is more important on a query execution plan, cardinality or selectivity?

The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. ...
10
votes
2answers
1k views

Why is this faster and is it safe to use? (WHERE first letter is in the alphabet)

Long story short, we're updating small tables of people with values from a very large table of people. In a recent test, this update takes ca 5 minutes to run. We stumbled upon what seems like the ...
1
vote
2answers
68 views

Poor SQL Server Performance With Nested “OR” Conditions In LEFT OUTER JOIN

I have a tricky bit of SQL that's giving me a bit of trouble. I'd like to introduce several new columns to a key table whose components may or may not be used based on user configuration (Dimension's ...
0
votes
1answer
55 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...