The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [optimization]

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

329 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
0answers
89 views

Constant Scan applying

This question is continuation of optimizer behavior exploration in regard 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
0answers
93 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
5
votes
0answers
593 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
2k 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
691 views

Optimized MySQL “optional” FULLTEXT search on multiple tables

I need to select data with optional columns in where clause based on client input. my query is like this: SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt FROM a LEFT JOIN b ON a.b_id=b.id ...
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
2answers
652 views

Column Cardinality and Total Number of Rows Are Equal

I have a table with 128'914'635 number of rows. Some columns cardinality (i.e. address, lastname, firstname) is equal to the total number of rows in the table (128'914'635). The queries are really ...
4
votes
0answers
1k 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
1answer
174 views

Optimize query without rewriting it - vendor app

Here is a query that uses a lot of CPU on my SQL Server (8 cores): DELETE TOP(1) rd FROM (SELECT TOP 99.9999 PERCENT * FROM APM_IisBb_Request_Details ORDER BY RequestDate ASC) rd JOIN ...
3
votes
3answers
792 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 ...
3
votes
0answers
3k 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
43 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
74 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
0answers
2k 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 ...
3
votes
0answers
489 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
418 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
1answer
624 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 ...
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
1answer
2k 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 ...
2
votes
0answers
26 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
2answers
86 views

Why max(id) with join not working or how to optimize it with join?

Below is my MESSAGES_THREAD table: CREATE TABLE `messages_thread` ( `id` varchar(64) NOT NULL, `creator` mediumint(9) NOT NULL, `other` mediumint(9) NOT NULL, `time` datetime DEFAULT ...
2
votes
0answers
24 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
42 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
53 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 ...
2
votes
0answers
60 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
41 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
48 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 ...
2
votes
1answer
83 views

SQL server slow query

I have the following query that is taking a long time to execute (nearly 20 seconds). SELECT Count(*) FROM "workorder" "wo" LEFT JOIN "workorder_fields" "wof" ON "wo"."...
2
votes
2answers
1k views

How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?

I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?) SELECT cars.*, users.*, makers.*, places.* ...
2
votes
0answers
79 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
0answers
138 views

What is the difference between “OPTION (OPTIMIZE FOR UNKNOWN)” and “OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'))”

Can you tell me what is difference between OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING')) and OPTION (OPTIMIZE FOR UNKNOWN)?
2
votes
0answers
193 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
2answers
221 views

On Duplicate Key Update - million times per day optimization?

I am using the following query with different variables millions of times per day, and I am wondering if there is any way to optimize it: INSERT INTO campaign_stats (user_id, date, $country) ...
2
votes
2answers
236 views

SQL Query optimization - Long running query

I have a database with the following tables : CREATE TABLE [Document]( [IdDocument] [int] NOT NULL, [DocumentType] [int] NOT NULL CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)...
2
votes
0answers
190 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, [...
2
votes
0answers
72 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
56 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
0answers
128 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
2answers
925 views

MySQL Optimization suggestions for E3-1246 with 16GB RAM

My server load is a bit high when many users are on and i'f like to know if i can get better results by tweaking mysql settings, this is a dedicated server and it seems that problems start when 500 ...
2
votes
0answers
165 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
110 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
53 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
55 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
2
votes
0answers
81 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
2answers
197 views

MariaDB server with 80K-100K DataBases

BACKGROUND : We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB ...
2
votes
0answers
188 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
348 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 ...