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
0
votes
1answer
44 views

Is it possible to rewrite this Function for optimization?

I need help optimizing this function as it takes more than 2 minutes currently which is far from optimal. This function basically gives you the name of column, corresponding table and list of unique ...
1
vote
1answer
210 views

Adhoc queries memory consumption in SQL Server

I have a SQL Server 2012 and it has just one (1) database in it. When I generate a server dashboard report it shows that 50% of CPU usage is related to my database and the rest is associated to Adhoc ...
0
votes
0answers
29 views

Where each partition has only one value in the constraint column, can I avoid filtering?

I have partitioned a table in postgres 9.6 by date. Every date within a parition is identical. When I select data, a relatively expensive Seq Scan filter operation happens against every row in the ...
0
votes
4answers
89 views

MariaDB User Define Variable throws warning if it is empty

I have 2 queries: the first query result will be passed to the second query using a user-defined variable. (This implementation is because of a performance issue with the table after applying proper ...
0
votes
0answers
54 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
3answers
213 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
66 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
81 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
158 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
48 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
26 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
136 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
33 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
74 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
41 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
151 views

How does the MongoDB query optimizer evaluate candidate plans?

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
46 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
217 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
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 ...
1
vote
0answers
161 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
41 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
147 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 ...
15
votes
1answer
403 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
60 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 ...
18
votes
7answers
2k 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 ...
1
vote
1answer
600 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
427 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
740 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
106 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
68 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
72 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
26 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 ...
1
vote
2answers
128 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
73 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 ...
1
vote
0answers
74 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
608 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
53 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
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 ...
1
vote
1answer
60 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
52 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
114 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
61 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
33 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
28 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
1answer
57 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
695 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
105 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
504 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 ...