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
0answers
26 views

Slow PostgreSQL query - how to improve it?

[Beginners question :-) hopefully on-topic here.] In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to ...
0
votes
2answers
29 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
0
votes
1answer
33 views

what is more important on a query execution plan, cardinality or selectivity?

The paper "How good are query optimizers, really?" says that cardinality is the thing that most influence to find a better query execution plan in RDBMS. Summary of this paper can be found here. ...
10
votes
2answers
1k views

Why is this faster and is it safe to use? (WHERE first letter is in the alphabet)

Long story short, we're updating small tables of people with values from a very large table of people. In a recent test, this update takes ca 5 minutes to run. We stumbled upon what seems like the ...
1
vote
2answers
52 views

Poor SQL Server Performance With Nested “OR” Conditions In LEFT OUTER JOIN

I have a tricky bit of SQL that's giving me a bit of trouble. I'd like to introduce several new columns to a key table whose components may or may not be used based on user configuration (Dimension's ...
0
votes
1answer
40 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1answer
23 views

Why are bulk multi-column key queries so slow in MySQL?

(For this question, I am using AWS/Aurora MySQL with a reasonably-spec'd RDS instance) Consider the following schema: Table T: col0: the usual autoincrement primary key col1: varchar ...
1
vote
1answer
23 views

Better way to select a matching subset of rows?

DB Fiddle link. I have a many to many table like this: CREATE TABLE house_to_cats ( id SERIAL PRIMARY KEY, house_id INTEGER, cat_id INTEGER ); -- house with cats 1 and 2: too small INSERT INTO ...
3
votes
1answer
106 views

Execution Plan is different when Constant Variable is Null

I am working on a scenario in which I am providing a variable a value. When I pass Null value, the Query Engine didn't scan the Join tables. As per Logical Query Processing, first FROM Clause Executes,...
0
votes
2answers
29 views

How do I optimize this extremely simple MySQL query?

I am using MySQL 5.7 (can't change this), and I am kind of new to MySQL. I have a fair amount of experience with SQL sever so just trying to work through some of these quirks. The query I want to run ...
1
vote
3answers
70 views

Is it possible to optimize this query? Currently taking days (no sign of stopping) on ~11 million records

Table in question: CREATE TABLE `observations` ( `time_of_observation` datetime NOT NULL, `station` varchar(5) NOT NULL, `number` varchar(20) NOT NULL, `destination` varchar(100) NOT NULL, `...
0
votes
1answer
34 views

What is the best way to cross check the record in multiple collections

I am developing the application in which system will show the campaign content to users which he/s never seen. To keep the logs either the specific user seen the specific content or not, i am ...
2
votes
1answer
28 views

How query optimizer works in mongodb?

The MongoDB query optimizer processes queries and chooses the most efficient query plan for a query given the available indexes This is an excerpt from MongoDB documentation. I am looking for more ...
2
votes
2answers
39 views

Trouble Figuring out why My Query is so slow

I have a query that takes 15 seconds to get 350 results in a MySQL 5.6 Server and I am unable to diagnose why, I am still very new to database optimizing. U The EXPLAIN visual does show some non-...
0
votes
2answers
35 views

MySQL LEFT OUTER JOIN performance on a local column condition

Assume the following tables, indexed on the obvious keys: Table users ------------------------ id | name | has_cars ------------------------ 1 Dan 0 2 Bob 0 3 Jake 1 4 Jen 1 ...
2
votes
1answer
36 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 ...
1
vote
0answers
33 views

MySQL 8 more than doubles the size of MyISAM table after optimization

I recently upgraded from MySQL 5.7 to 8.0. I have noticed some changes from 5.7 to 8. But I have not been able to find any discussion on following situation that I have been puzzling for a while. I ...
0
votes
0answers
14 views

PostgreSQL - Optimize query with multiple subqueries

I have 2 tables, users and sessions. The tables look like this: users - id (int), name (varchar) sessions - id (int), user_id (int), ip (inet), cookie_identifier (varchar) All columns have an index....
0
votes
1answer
23 views

using tmpfs for mariadb tmp tables

My server is writing about 50% of temp tables to disk, even when max_heap_table_size and tmp_table_size are ridiculously high (500M). Yesterday I ran a script all day that monitored the tmp folder and ...
14
votes
1answer
251 views

When can SARGable predicates be pushed into a CTE or derived table?

Sandbag While working on Top Quality Blog Posts®, I came across some optimizer behavior I found really infuriating interesting. I don't immediately have an explanation, at least not one I'm happy ...
0
votes
1answer
35 views

is vertical scaling good idea for a mysql database server?

I operate a mariadb/mysql database server and i do not want to get into the problems with managing a multi-cluster database server i have access to huge CPU and RAM and STORAGE when needed so my ...
16
votes
7answers
1k views

Optimizing numeric range (interval) searches in SQL Server

This question is similar to Optimizing IP Range Search? but that one is restricted to SQL Server 2000. Suppose I have 10 million ranges provisionally stored in a table structured and populated as ...
0
votes
1answer
69 views

incorrect Estimated Number of Rows vs Actual number of rows

I'm seeing a huge difference in the estimated number of rows vs actual number of rows. I'm not sure if i'm reading this right, so does this mean that sql server estimated the number of rows returned ...
2
votes
1answer
412 views

Query speed optimization

I have several tables that I'm trying to combine with the below query. I'm using the following tables: LoanOrigination: This contains loan characteristics such as asset value, loan term, etc. There ...
4
votes
2answers
98 views

Optimizing table valued function SQL Server

I am trying to optimize this table-valued function. If I could, I would change it to the procedure but I cant. The problem is with two update statements. I kept only those two in the function because ...
2
votes
1answer
89 views

Why can vectorization reduce the use of virtual functions?

Vectorized Query can be very helpful to get a high-performance analytical systems. I know that vectorization can execute all operations on blocks of values (SIMD instructions) instead of interpreting ...
1
vote
1answer
55 views

Equivalent Join Conditions Yield Vastly Different Results

The following query aligns test data with the closest truth data equal to or earlier than the test data for all possible truth_id's. There are very few truth_ids and very few test ids, but each id has ...
3
votes
1answer
65 views

There's a long delay in this query that I need to resolve [closed]

This is a multi-tenant/portal DNN (formally DotNetNuke) database, and a core query as part of the registration process seems to be terribly slow. Here it is: declare @PortalID int = 0; SELECT R.*, ...
0
votes
0answers
15 views

Compare PostgreSQL Execution Plans

PostgreSQL newbie here. I'm trying to optimize a function that returns a table. Essentially it boils down to either creating a temp table and then selecting/joining on that table to return results, or ...
2
votes
1answer
38 views

Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 “Using index condition”

My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different). EXPLAIN SELECT * FROM `...
0
votes
0answers
7 views

NHibernate second level cache collection hydration takes too long

I have enabled query caching and entity caching in NHibernate to store large collections of objects, needed for repetitive operations. While I have minimized the number of hits to db, the performance ...
0
votes
0answers
53 views

Slow insert on very large tables, (even in transactions)

I have a table with about 50 million rows, the table itself is fairly simple CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE) I then do an insert INSERT OR IGNORE INTO Word (word) ...
6
votes
1answer
294 views

Why would a Clustered Index Seek return a higher “Actual Number of Rows” than there are rows in the underlying table?

I'm troubleshooting an issue related to SQL Server (Azure SQL Database technically) occasionally choosing a bad execution plan, presumably due to skewed stats. sp_updatestats fixes it every time, ...
0
votes
1answer
49 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?
2
votes
0answers
32 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
vote
1answer
49 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 ...
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
113 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
55 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
32 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
24 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 ) ...
-1
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
45 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
76 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
43 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
2answers
121 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
49 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
31 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
90 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
60 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 ...