Questions tagged [optimization]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
517 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
60 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
175 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
2answers
3k 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 ...
0
votes
1answer
75 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
94 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
108 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
194 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
0answers
25 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 ...
10
votes
1answer
494 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
56 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
36 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
239 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
150 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, ...
7
votes
1answer
1k 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. ...
2
votes
2answers
148 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
38 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
77 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
97 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
62 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
40 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
58 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 ...
0
votes
2answers
321 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
132 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
88 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
335 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 ...
1
vote
0answers
22 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
79 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
35 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
338 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
59 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
111 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
425 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
111 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 ...
1
vote
0answers
9 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
465 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
209 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
896 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 ...
0
votes
2answers
62 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
60 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
1answer
32 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
63 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). ...
6
votes
1answer
5k 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 ...
1
vote
0answers
35 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
1k 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
177 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
892 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
91 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`.`...
11
votes
2answers
3k 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 ...
4
votes
1answer
328 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', @...