Questions tagged [optimization]
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
369
questions with no upvoted or accepted answers
11
votes
0answers
320 views
Constant Scan applying
This question is a continuation of optimizer behavior exploration in regards to VALUES construct started here and here. I want to ask about VALUES and APPLY this time.
Using CROSS APPLY to alias an ...
6
votes
1answer
266 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 ...
6
votes
0answers
668 views
How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?
In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
5
votes
0answers
3k views
Slow Postgresql query with left join
I have a query on Postgres, and I also have added proper index. Is there anything missing here?
SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
...
5
votes
1answer
4k views
SQL Server performance issue (CXPACKET wait type)
I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I ...
4
votes
3answers
2k views
Optimise Large Record Deletion Procedure
I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes.
Ensuring we've enough data to operate on as an engineering team, I'm ...
4
votes
1answer
1k views
Slow postgres query that uses quick sort instead of hash-aggregate
I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic.
I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
4
votes
0answers
5k views
Materialized Views and Indexes
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
4
votes
0answers
2k views
Why is adding a FK constraint slow in MySQL?
I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand.
CREATE TABLE A (
`pk` INT(11) NOT NULL,
`fk` INT(11) NOT NULL,
PRIMARY KEY `pk_A` (`...
3
votes
0answers
69 views
Poor query planning in Postgres 10
The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join.
Here's my ...
3
votes
1answer
148 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 ...
3
votes
0answers
365 views
Searching between two BIGINT
I have a table defined as:
CREATE TABLE [dbo].[IPS_INTEGERS](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_start_integer] [bigint] NULL,
[network_last_integer] [bigint] NULL,
[...
3
votes
0answers
5k views
Optimize long LEFT OUTER JOIN query
I have query which has 40+ left outer joins. Query is not that slow but I'm interested that can I optimize this (procedure is most used one in db). I had idea that "a" table has type column which ...
3
votes
2answers
45 views
How to avoid inner query im MYSQL
I have a table with 4.7 million rows and I am executing this query on this table:
SELECT * FROM students WHERE `eid` IN
(SELECT DISTINCT `eid` FROM students WHERE code IN ('120', '330'));
This is ...
3
votes
0answers
78 views
Understanding Database Query Cost Optimzation
I have been learning Query Optimization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding on how some variables are being derived in ...
3
votes
1answer
113 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 ...
3
votes
0answers
573 views
How to optimize update using join in postgresql
I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
3
votes
0answers
79 views
Tagged questions
I have similar to StackExchange network that utilises tagged questions.
In my web application Users also can find questions by tags. The query below returns a set of tagged questions:
SELECT ...
3
votes
0answers
430 views
Postgres - Partitioning old tables, partition query planning, optimisation
I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options".
I've already run pg_tune and bumped a ...
3
votes
0answers
1k views
MySQL: Improve performance for one row insert into table with unique constraint
I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255).
Now the insert speed is about 30/s, is this expected? how could I improve it?
I can't use bulk ...
3
votes
4answers
192 views
Optimizing slow query
For a few days now, i'm trying to optimize this query.
But still I haven't found the right solution in how to fix this.
The contents table contains about 20 million records. As the connect table ...
3
votes
2answers
159 views
Changing database compatibility from legacy CE to 120
Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of ...
2
votes
1answer
75 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,...
2
votes
1answer
33 views
Mysql self Join query with Limit never terminates
I'm running an experiment with the query
Select Distinct table_1.id1, table_2.id1
FROM image as table_1, image as table_2
WHERE table_1.id2 = table_2.id2
LIMIT K;
When I run the query ...
2
votes
1answer
113 views
Index or query tuning for complex predicates
I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates:
JOIN on a date from one table being in range - between two date ...
2
votes
0answers
27 views
MSSQL: Conditional noexpand hint for view that may be indexed or not
As documented in other Q&A on this site, if you create an indexed view you may have to give the noexpand hint to use it as such. Without the hint, cheaper versions of MSSQL will always treat it ...
2
votes
0answers
33 views
Improve split large table algorithm in MySQL
I have a very large table ~1TB of history data in MySQL 5.6. I want to split the data into many smaller tables per sites. My current idea is to iterate in chunks of 10'000 records and inside this loop ...
2
votes
0answers
43 views
Why can't outer join simplification happen for embedding outer join?
The MySQL documentation here suggests the following:
Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
2
votes
0answers
41 views
Querying a High Cardinality Field
I am designing a data model for our orders for our upcoming Cassandra migration. An order has an orderId (arcane UUID field) and an orderNumber (user-friendly number). A getOrder query can be done by ...
2
votes
1answer
48 views
How can performance of queries containing keywords like IN, EXISTS, etc. be optimized?
I was interested in some of the general ways one can use to optimize the performance of queries that use keywords like IN and EXISTS (relying on the state of the entire database).
For example, ...
2
votes
0answers
57 views
Optimizing a paginated query with various filters and dynamic sorting
Introduction
Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and can ...
2
votes
0answers
116 views
View with where clause slower than query with same where clause
This query
with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as
(select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension
from Base a
...
2
votes
0answers
67 views
What's the more efficient way of selecting with multi left joins?
I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities.
All tables have a id column as the primary key. student.id is varchar(64) (similar ...
2
votes
0answers
27 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 ...
2
votes
1answer
44 views
How can I optimize this MYSQL Script For many Records?
I have This script by Helping @lukstorms ,
-- DROP TABLE IF EXISTS test_words;
CREATE TABLE IF NOT EXISTS test_words (
id int unsigned NOT NULL PRIMARY KEY,
words text NOT NULL
);
-- DROP TABLE ...
2
votes
0answers
82 views
Problem query - execution plan
I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
2
votes
1answer
264 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 ...
2
votes
0answers
454 views
Poor SQL query optimization of Recursive CTE with iTVF, environment specific
I'm developing a recursive CTE which utilizes an inlined TVF. In our dev environment, the recursive CTE executes exactly as expected.
In our production environment, the first iteration of the CTE ...
2
votes
1answer
2k views
How to optimize a Full Text Search
So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.
Doing this search slows the query down by about 300ms and since the query is run a few ...
2
votes
0answers
187 views
Index statistics are requiring constant updates due to timeouts
We have been experiencing an odd behavior in our application where various modules will begin to timeout in SQL Server 2012. Each time we stumble across this issue, we find that the statistics require ...
2
votes
0answers
63 views
different ways to optimise union all on a particular staging process
quite often while tuning queries and stored procedures I come across the situation where I have a number of different types of the same object, each type has different characteristics but all of them ...
2
votes
1answer
368 views
MySQL using variable in SELECT command with view affecting performance
sorry if this has been asked already. I've just moved from MySQL 5.6 to 5.7.17 and I'm struggling to understand this issue.
I have a view "delete_me_test_" which has around 26k rows.
SELECT
...
2
votes
0answers
129 views
Why does optimizer estimate 1 row for this plan?
Here's my plan I'm having trouble with: https://www.brentozar.com/pastetheplan/?id=r168tltbe
I'm betting this is something easy I'm not seeing. I have a query that is running very slowly because ...
2
votes
0answers
186 views
Procedure runs slower over time; fixed by cache clear or reboot
We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, ...
2
votes
0answers
113 views
Advice on how to improve this stored procedure
I have 2 tables:
ReportItem
id (int, primary key clustered index)
report_item (nvarchar(100))
flow_name (nvarchar(50))
source (nvarchar(50))
Tech_Details
id (int, primary key clustered index)
...
2
votes
1answer
54 views
Online text based game design - Am I linking the databases correctly?
I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it ...
2
votes
0answers
56 views
Postgres planner overerstimate index only scan
\d hits;
Column | Type
--------------+-----------------------------
id | integer
link_id | integer
...
2
votes
0answers
87 views
Disable/Remove Table Index for INSERT Performance
I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
2
votes
0answers
192 views
SQL - Optimize table population with dynamic queries
I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
2
votes
0answers
371 views
How to optimize inefficient query in PostGIS
I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow).
This query takes about ...
