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

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

0
votes
1answer
18 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
0answers
26 views

Remote Scan: What are some performance tuning techniques to improve a query when this operator is the bottleneck? [on hold]

I've ran into the Remote Scan operator as the bottleneck for a multitude of queries I've written (usually when I need to update data on one linked server from another). (The issue where the whole ...
0
votes
1answer
40 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
16 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
46 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
27 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
56 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
59 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
12 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
35 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
57 views

Maximum possible memory usage: 12.5G (162.36% of installed RAM) [closed]

I was trying to do mysql optimization. Yesterday the CPU usage was 100%. I am trying to optimize with mysqltuner but this time I gave a mysqltuner warning. Centos 6 8gb Ram and 4 prossesor my.cnf: ...
0
votes
0answers
16 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
69 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 ...
2
votes
2answers
77 views

Slow Bitmap Heap Scan, Order by killing the execution time

A query that is going to slow on table users, the table has 5 million records, and for increasing query performance I often index function outputs of aggregated data that are convenient for each case, ...
-1
votes
1answer
18 views

MariaDB query optimizer not using correct index

I got the following query and it was supposed to use index to speed up but it is using the wrong index image_likes and takes around 60 seconds to execute. If I add use index(image_user_id) then it ...
0
votes
1answer
64 views

How to improve execution time for queries by multiple fields of different data types on a single table?

Table design On SQL Server Microsoft SQL Server Enterprise Evaluation (64-bit), version 12.0.5207.0, I have this table, called InputResult: Table has 11.645.408 rows. The columns ValueAsString, ...
0
votes
1answer
66 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
3answers
78 views

Database performance improvements for current setup. (mysql - marriaDB)

I've currently got a pretty hefty database (for me anyway) with multiple tables exceeding 15 million rows. I'm trying to improve performance on the whole table lookup, I implemented memcached to cache ...
0
votes
0answers
7 views

Elastic: For single token `text` data, do you always want/need an Analyzer?

I was creating/designing a bunch of Elastic indices and came across one of the settings for Elastic that I didn't know about called "Analyzers". Most of what I've read regarding Elastic index ...
1
vote
0answers
8 views

Elastic: Are Analyzers only for `text` type fields?

Most of the documentation about Elastic Analyzers assumes that you NEED an Analyzer and so the documentation talks mostly about how Analyzers work and the differences between the different Analyzers. ...
0
votes
1answer
62 views

how to add option(recompile) within this stored procedure?

In my current environment, I have many instances of stored procedures, just like the one shown below, where a bunch of parameters are passed to the procedure, and then within the procedure a select ...
1
vote
1answer
48 views

what are memory fractions and how to get rid of them (or the sort operator) in the example below?

This execution plan has the following memory fractions: Memory fraction input:1, Memory Fraction Output:1. it is a very simple query: SELECT [a].[activityId], ...
5
votes
3answers
879 views

Does the 'quality' of query optimization differ by database provider? [closed]

I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in ...
1
vote
0answers
63 views

Unexpected workload (CPU and IO) on DB sql server [closed]

On the server, we have encountered some errors one day of this week between 8h35 and 8h50. It implies about 300 users unable to use their application during this time frame. Using monitoring solution,...
0
votes
2answers
53 views

Need to understand the drastic difference in execution plan between two queries

I have 2 queries. select guid=NEWID() into #guid --update statistics #guid with all --create statistics s1 on #guid(guid) select * from Party a join #guid b on a.[Party_GUID] = b.guid --...
2
votes
1answer
51 views

Are UDF used by a stored procedure optimised/merged in the SP itself?

The issue has sent me crazy for a few days, I may have found my answer, but I need a confirmation and to check for an eventual easy solution. When a stored procedure uses some user defined functions, ...
0
votes
0answers
11 views

Scrambling PII data in CLOB columns

I am working on scrambling PII tags in CLOB columns such as <FirstName> or <LastName> or <SSN_Number>. so I am using the below query to achieve the same. UPDATE /*+PARALLEL(8)*/ ...
2
votes
1answer
47 views

Optimizer failing to load statistic when creating plan in SQL Server 2017 “Calculator failed. Replanning.”

This morning, the statistics on one of our filtered indexes updated, and when a plan that uses that statistics recompiled it stopped using that index (and started scanning the entire clustered index). ...
3
votes
1answer
303 views

Warning about memory “Excessive Grant” in the query plan - how to find out what is causing it?

I am running a query that is giving the warning about a memory Excessive Grant. There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the ...
0
votes
0answers
25 views

Mysql Optimization - Search over two columns

I have the followed table with a list of 4 Millions of companies: Field Type name varchar(200) category varchar(200) I have added a fulltext index on name and category and the table is ...
1
vote
0answers
26 views

How to optimize this Postgresql query

Table A has columns (P1, P2, Value1) where P1 and P2 are a multi column primary key. Table B has columns (P3, P1, Q1) where P3 is the primary key. Table C has columns (P2, Q1, Value2) where P2 and Q1 ...
0
votes
2answers
88 views

How can I improve query performances using Using temporary; Using filesort

I am very new to sql and query optimization, I am sorry if its inappropriate to ask such as may be asked previously. But I need help and some thoughts where I can look and start ... EXPLAIN SELECT ...
0
votes
1answer
30 views

Is Query Slower When There Are More Columns in one Table?

I'm trying to build a login function and was wondering: Does more columns in a table mean the longer (and slower) the query takes to return a result? I'm asking because I'm debating on whether to ...
10
votes
2answers
137 views

Calculating stock quantity based on change log

Imagine that you have the following table structure: LogId | ProductId | FromPositionId | ToPositionId | Date | Quantity ---------------------------------------------------------------...
1
vote
2answers
32 views

Will moving the MATCH…AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance?

Basically, I have the following query: select `t`.* from `tracking` `t` where exists ( select `ti`.`id` from `tracking_items` `ti` inner join `cars` `c` on (`c`.`car_id` = `ti`.`...
10
votes
2answers
2k views

MAXDOP = 1, Query Hints and Cost Threshold For Parallelism

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually ...
3
votes
1answer
94 views

Bad row estimate following Compute Scalar operator in plan

I'm struggling to understand where a row estimate is coming from in an execution plan. Paste the plan link declare @BatchKey INT = 1, @ParentBatchKey INT = 1, @QuoteRef varchar(50) = 'Q00018249', @...
0
votes
0answers
35 views

MYSQL - Best way to search for a name or category like in the Yellow Pages

I have a website that is a business directory like YellowPages.com . I have the following table containing all the companies: mysql> describe companies; +-------------+--------------+------+-----+...
2
votes
2answers
60 views

How to spec a machine for a particular database load?

Our primary authentication database that is used for 100k+ users, and growing is a VM on a shared machine that we want to extract into its own isolated hardware. This will let us isolate resources, ...
0
votes
1answer
80 views

Optimize Stored Procedure

I am not an extremely experienced SQL Dev. I am worried that some of the Stored Procedures I have created for my database aren't exactly optimized, and may not be all efficient when they are running. ...
1
vote
1answer
36 views

Storing files with extension in database as BLOB

Recently during development process I've faced file storage issue. I'm not sure which solution is the best one that's why I decided to write this question. I don't have a lot experience of database ...
5
votes
2answers
98 views

How to teach the optimizer to use index instead of fts with join on data logging master/detail tables?

SQL Server 2012 optimizer does not get it right. Test case, summary: This is a simplified test scenario. DDL statements at the bottom. I have two tables for data logging, A and B. There is a 1:n ...
1
vote
1answer
79 views

Query planning for many-many select and sort query

I have a postgres database with a Genre table (40 records), a Book table (~1.3M records) and a many-many table to relate the two (~2M records). I want to select books by a particular Genre and then ...
0
votes
4answers
53 views

Is there any way to keep rows with same id sequential even if they are not stored sequentially?

Edit: I think i used the wrong word here. By "sequential" i don't mean that everything aid 1 enters has to be in the order of the time at which they enter it. What i mean is that they should be ...
2
votes
2answers
40 views

Multi-column and single-column index on the same column

I have two indexes on the same column: store_deleted_idx (store_id, deleted) store_idx (store_id) DDL: CREATE TABLE `stores_shoppers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `store_id` int(11) ...
0
votes
3answers
50 views

Query slower with index on 14M rows table

DB: Amazon RDS MySQL (OS: Linux, 2 vCPU, Memory: 8GB) I have a table with almost 14M rows of data. CREATE TABLE `meterreadings` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `meterid` varchar(16) ...
2
votes
1answer
27 views

Function in JOIN on DISTINCT values executes for each row instead of distinct parameters

I'm JOINing function execution using some of the values from table, expecting that it will execute only as many times as there are unique sets of parameters, but instead it executes for each row. ...
3
votes
2answers
238 views

Clustered Index Scan on partitioned table even when there is covering index

I have a partitioned table which is partitioned based on col1 int. I also have a covering index for the query that I am trying to troubleshoot. https://www.brentozar.com/pastetheplan/?id=BkNrNdgHm ...