Stack Exchange Network

Stack Exchange network consists of 174 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
1answer
34 views

Oracle 12.1 optimize_adative_features = FALSE ; using new statistics

In Oracle 12.1, does setting optimize_adaptive_features to FALSE mean that the optimizer will no longer use new statistics generated by dbms_stats.gather_schema_stats?
1
vote
0answers
19 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 ...
-1
votes
1answer
30 views

intermittent High Volume of I/O Reads for a SP [on hold]

On my production SQL Server, there are incidents of 95-100% CPU usage almost everyday (1-2 times in a day), lasting for 5-10 mins. Although it recovers itself and no intervention is required. It is ...
1
vote
1answer
43 views

Oracle Optimizer refuses to use my index

I have a relatively simple query that SELECT data in a table. SELECT l.columnRecovered FROM schema.TABLE l WHERE (column1 = :a or (:a is null AND column1 is null)) AND (column2 = :b or (:b is null ...
0
votes
0answers
16 views

Upgrade query with LEAD simulation in mysql

I'm here for ask how to upgrade this query localized in SQL Fiddle. There, we have column Time_Stamp with dates of two days diferrents, and value column where values they vary between 0 and 1 ...
1
vote
0answers
50 views

Different operations in different databases

So I have just executed EXPLAIN PLAN FOR on the same query in two databases that I've been told are identical in every regard except for slight variations in data amount. And the result of it seems ...
3
votes
1answer
110 views

Any way to tune / optimize this query?

Actual Execution Plan here: https://www.brentozar.com/pastetheplan/?id=H1fgOQTRX Non Clustered Indices did not seem to change much. SELECT DISTINCT TOP ? Object1.Column1 AS Column2, Object1....
4
votes
1answer
51 views

Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?

I have the following table in Postgres 9.6: Table "public.TagNotifications" Column | Type | Modifiers ...
0
votes
1answer
31 views

What slows down this histogram query?

I have a table my_link_t with columns t.weight type number and categorical speedcat type integer values from 2 to 8. I want to split data in buckets from min=0, max=0.6, step = 0.001 and build a 3D ...
0
votes
1answer
23 views

SQL Query Optimization (Mysql 5.7)

I have following query to execute in live mysql 5.7, which takes around 7 sec and we want to reduce their time, please support SELECT COUNT( DISTINCT InternalInquiry.inquiry_id ) ...
0
votes
0answers
26 views

Query time breakdown (parsing, “sub-fetching”, regular fetching)

Is there a way to distinguish the time a query spends on each of its execution stages (widely understood)? My questions arises from the fact that I test two methods of data retrieval: view vs. ...
0
votes
1answer
44 views

BETWEEN query on two indexed columns exponentially takes longer on minimal changes to LIMIT

This is a minimal example of my problem, but I reproduced the problem with this structure. I have the following table: | column name | type | |----------------+-------------| | id ...
1
vote
1answer
48 views

Mongo queries are slow and yielding/waiting for locks thousand times

We are using mongo setup with replica sets on AWS. Setup Details: 1 Primary node: r3.8xlarge 4 secondary nodes: r5.xlarge Mongo version: 3.0.8 (WiredTiger) Database size: 358GB We have configured ...
1
vote
1answer
41 views

Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows

We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered ...
1
vote
1answer
87 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
47 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
0answers
24 views

when to use Simple or Forced Parameterization in my databases?

I have recently created a filtered index in one of my databases but it would neither be used nor accepted in an index hint in any query unless I added the option(recompile) hint in it. it turned out ...
2
votes
1answer
42 views

Query processor could not produce a query plan because of the hints defined in this query

There is a similar question, but it is not the same: The query processor could not produce a query plan I have the following query, and the following filtered index, and I cannot see any reason why ...
-2
votes
0answers
12 views

Measure Database performance on localhost

i have enabled query cache and set cache size, settings shown in the picture below. how do i measure the performance of the database and if the speed of my PHP web application improved after enabling ...
0
votes
1answer
59 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
52 views

Why does a SELECT with no WHERE cause a full table scan, (or does it)?

I have the following table with ~2000000 rows CREATE TABLE TableA (idA integer PRIMARY KEY, idB TEXT UNIQUE) From time to time, I need to process each rows, the order does not mater. So I process ...
1
vote
0answers
83 views

Percona 5.6.28-76.1: optimize config for database (Invision Power Board)

At the beginning I would like to explain that I'm not a DB expert. My hosting provider is struggling with DB optimization for my website. Unfortunately seems that they are out of ideas. I would like ...
1
vote
0answers
29 views

mysql 5.6 Unknown system variable 'optimizer_trace'

I'm trying to follow this tutorial to trace optimizer that is used by mysql for certain query. But when I do SET optimizer_trace="enabled=on"; It said ERROR 1193 (HY000): Unknown system variable '...
1
vote
1answer
82 views

MariaDB writes a lot on disk [closed]

Configuration 10.2.15-MariaDB 20 GB RAM Total DB size - 16 GB, almost all tables are MyISAM. Some tables have rather big index files, about 400-700 Mb. Observations Writes about 4 GB per hour ...
1
vote
0answers
5 views

More than one MATCH/AGAINST clause becomes too slow in MySQL

I have the following query: SELECT SQL_CALC_FOUND_ROWS gtd_items.* FROM gtd_items LEFT JOIN gtd ON gtd_items.gtd = gtd.id JOIN names importers ON gtd.importer = importers.id JOIN ...
0
votes
0answers
45 views

Very slow query because of bad frequency estimate

(PostgreSQL 10) I have to run a query like: SELECT id FROM tbl WHERE field1 = true AND field2 = 'x' LIMIT 200; What I know about this table is that: tbl has about 3M rows about 99% of rows match ...
10
votes
1answer
344 views

What Rules Determine When SQL Server Use a CTE as an “Optimization Fence”?

A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL: Two Important Differences Between SQL Server and PostgreSQL The first point (“...
0
votes
1answer
41 views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
0
votes
2answers
32 views

Index columns on pre-production?

If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed. Say your app is new and you have identified columns which are good candidates for indexing (based on ...
0
votes
2answers
48 views

Optimising MySQL database with read only tables

I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd ...
0
votes
1answer
43 views

MySQL Generated columns on non-deterministic function

ON MySQL 5.7, generated columns are not allowed with non-deterministic function. The query that I need to optimize is executing on a bulky table. Where clause is causing a full scan on the table, ...
2
votes
0answers
35 views

How to get a single row per group where MAX(column) or first if none?

I have the following schema: users: id email 1 'user.one@test.com' 2 'user.two@test.com' video_group: id title 1 'Group 1' 2 'Group 2' videos: id group_id rank title 1 1 1 'Group ...
6
votes
1answer
410 views

“Warnings: Operation caused residual I/O” versus key lookups

I've seen this warning in SQL Server 2017 execution plans: Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40. ...
0
votes
0answers
24 views

How to make two sequential query into a single query in MongoDB

I MongoDB collection, I am storing multiples records by one batch ID. I have kept batch Id as milliseconds of Date() object. How to retrieve all records which have recent/max batch ID in single ...
2
votes
2answers
136 views

Force SQL Engine to prepare Non-Null plan only for a query that use variable

I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours. Approach 1 SELECT *, SUBSTRING(IDAndCode,CHARINDEX ('$', ...
0
votes
1answer
33 views

how to optimise this group by on sys.dm_exec_query_stats?

I would like to know the last 1008 queries run in my systems and with this intent I have put this query together: -- get the last 1008 queries run SELECT top 1008 execquery.last_execution_time AS [...
3
votes
2answers
60 views

Optimization of a query on a table that represents a weak entity type

I've been trying to figure this problem out for a while now, so I decided I'll try my luck here. I have a very complex online shop DB structure (multiple shops in one database, hundreds of thousands ...
1
vote
2answers
40 views

Mysql optimization edit my.cnf

Firstly, I am a beginner. I have a server, which has 32 GB RAM and 16 CPUs. I am working on MySQL optimization, but I have a problem. I am trying to set the InnoDB buffer pool instances = 8. But it'...
0
votes
1answer
46 views

Optimizing SQL query with two table joins and over 40 million rows in partitioned table

I have three tables, lets say their names are: table1 (has total over 40 million rows, but is partitioned with Declarative partition https://www.postgresql.org/docs/10/static/ddl-partitioning.html ...
0
votes
1answer
21 views

DB2 Workfile scan

We just migrate from LUW to zos and we're finding that in z/os the accesspath explains show new node named WFSCAN (Workfile scan) and this rises when there is a join, group by or any kind of sort. ...
1
vote
1answer
49 views

Why is my query taking so long if my field is correctly indexed?

I’m trying to figure out why my query is taking so long. I have indexed my property_id field but still get awful results when querying it. I have the following Explain and wonder if anyone can shed ...
1
vote
0answers
32 views

Complex query help needed

Dear Stackexchange members, My problem I need help with the following in MYSQL: For a website that rents accommodations I need to calculate the availability of the rooms. A scenario: A user wants ...
0
votes
2answers
86 views

why optimizer in Oracle database does not use index?

when I execute following queries see completely different result where is problem? here I execute a simple select query which takes a few seconds to complete: SELECT * FROM PRODUCTION....
0
votes
1answer
64 views

SQL Server Version Store Performance differences between RCS and ASI

I understand that Read Committed Snapshot (RCS) operates at statement level, and Allow Snapshot Isolation (ASI) operates at transaction level, and both store row versions in tempdb. I also understand ...
1
vote
0answers
15 views

SQLite database: many-to-many optimization (simple but large example) without IDs available?

I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of ...
1
vote
0answers
38 views

mongodb aggregation query performance

We have a collection PurchaseOrder, it has embedded array of documents PurchaseOrderLines and another embedded document Warehouse. The record count is about ~4m for PurchaseOrder and each purchase ...
0
votes
0answers
21 views

What is the best value for MAX DOP and Cost Threshold for Parallelism in a Datawarehouse [duplicate]

We are having much troubles on our ETL project in SQL Server 2016. The project keeps hanging on different tasks. After searching for possible solutions in the web I found that, in order to increase ...
1
vote
0answers
13 views

What is the “Optimize Response” connection string attribute used for?

My apologies if this is not the correct forum to be asking this in. I am having trouble finding information regarding the "Optimize Response" connection string attribute. The most promising source of ...
0
votes
1answer
70 views

What are the reasons to create a key-less, index-less database? [closed]

TL;DR: why would you create a database with no keys of any kind (not even primary), and no indexes? I joined a non-profit organization that was 14 months into migration to a custom built front-end ...
0
votes
1answer
32 views

Delete using another table with index

I'm trying to delete rows using another table and wondering why postgres doesn't use an index, here are the steps, on pg10.5: CREATE TABLE parent (id int); CREATE TABLE child (pid int); CREATE INDEX ...