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
1answer
95 views

How can Inner join be optimize?

I have a table where there are in some cases twin records. In the table, I have the following columns: id (identity column) action_id session_id row_id timestamp ...and more records. I need to ...
4
votes
0answers
42 views

How to set up a solid MySQL/MariaDB Server for +3000 active connections? [on hold]

Background. I have an App connecting to an RDS instance on Amazon (r3.Large) and it's working smoothly. However, the boss does not like to spend ~ $1,000 bucks a month on it so we are moving In-house....
6
votes
1answer
90 views

Why do we have Top N Sort in this example?

In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top? use tempdb; go create table dbo.t1 (id int identity primary key, v int); ...
-1
votes
0answers
26 views

Like '%' search for value withing longblob column in MySql is slow

MySQL version is 5.7.25 I have table like CREATE TABLE `applications` ( `id` varchar(25) NOT NULL, `application` longblob NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; '...
0
votes
0answers
21 views

Is there a way to figure out an executing query's plan handle by the query text?

Is there a way to determine the plan handle for the query plan of an executing query by that query's text? Perhaps in any of the DMVs? (I'm trying to find the query stats on an ad-hoc query.)
1
vote
2answers
34 views

Infix search on large table when majority of rows can be filtered out

We have a Postgres table, which looks like this: +-----------+---------+---------+---------+ | CLIENT_ID | VALUE_1 | VALUE_2 | VALUE_3 | +-----------+---------+---------+---------+ | 11234 | ...
1
vote
1answer
32 views

When SELECTing an expression that is an expression based index does the expression get processed? [closed]

I am asking this mainly for postgres expression based indexes but would be interested to know for other SQL implementations that have expression based indexes. Example: CREATE INDEX people_names ON ...
2
votes
3answers
49 views

PostgreSQL NOT IN array slow query

I have a large table with millions of rows. Each row has an array field tags. I also have the proper GIN index on tags. Counting the rows that have a tag is fast (~7s): SELECT COUNT(*) FROM "...
0
votes
2answers
39 views

Postgres using seq scan with filter on indexed column + EXISTS on related table

I've following query which takes about 25-35 seconds to execute: EXPLAIN (analyze, buffers, format text) SELECT booking.* FROM booking WHERE booking.reference_number = '9999999999' OR booking....
2
votes
1answer
27 views

How to get more physical details when executing an SQL query?

I want to get some physical features about a SQL query execution with PostgreSQL, like Read/write page accesses I/O operations Response time Sequential I/O throughput rate Deadlocks Pages scanned ...
5
votes
1answer
1k views

Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table? If I have a table where some of the rows will be ...
7
votes
1answer
468 views

Is there a reliable way to determine how long it took the optimizer to generate a query plan?

I'm not sure where I'd start, but is there a way to see how long it took the optimizer to generate a query plan for a query? Is it stored in any of the DMVs or something that's part of one of the ...
9
votes
1answer
145 views

Is it possible to re-use a query plan from the plan cache from one database in another database?

For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query ...
3
votes
1answer
40 views

SQL query with Like operator with Leading Wildcards

I have a query as follows: select top (30000) * from table1 where licenseplate like '%as43bv%' I have an index on licenseplate but the query will not hit the index due to leading wildcard. Since ...
2
votes
2answers
95 views

'Row Goal' different behavior - why?

This is a heap with 400+ Million rows and 16 non clustered indexes, 146 columns. Please do not focus on why I cannot create a clustered index. That is not in my control and not in scope to my question....
1
vote
1answer
69 views

Optimizing a query with many permutations (using Postgresql 11)

I'm trying to figure out the optimal solution for filtering a result set by every permutation of a given input criteria. We're using Postgresql 11 on AWS RDS I've created a sql fiddle here that ...
2
votes
0answers
25 views

Improve split large table algorithm in MySQL

I have a very large table ~1TB of history data in MySQL 5.6. I want to split the data into many smaller tables per sites. My current idea is to iterate in chunks of 10'000 records and inside this loop ...
6
votes
2answers
1k views

How SQL Server caches data in memory

I have couple of questions on how caching of data works Imagine we have below situation: Server restarted or we just ran DBCC DROPCLEANBUFFERS We have a Table1 that is 50 GB and has columns A, B, C, ...
2
votes
2answers
85 views

The difference between filtering a specific column in 'On Clause ' of a join statement and 'Where clause'

I have two tables with these structures: Table1(Date ,Code ,Name) Table2(Date ,Code) I need to filter a specific date for both tables. For instance I need data only for '10/28/2019' from each table....
1
vote
1answer
38 views

Query plan for a dynamically built `query_int` using `format()`

To make things simple here is a statically built version of the query that runs perfectly fast: SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20; The catch ...
0
votes
1answer
30 views

Mysql - query optimization

I have a DB schema as such, SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='...
0
votes
0answers
28 views

Why query mysql 8.0 response too slow with respect to mysql 5.5

I have the following query which in mysql 5.5, original server runs in approximately 3 seconds but all the opposite with respect to mysql 8.0, which runs in about 3 minutes, a big difference!!!. This ...
0
votes
1answer
74 views

MySQL JOIN query takes too much time to get the result

I have a MySQL query as follows which JOINs 8 tables. When I use 3 tables to get the data the result is getting within 10 seconds. But whenever I add one more table the fetching time goes upto 1 ...
1
vote
2answers
68 views

Mysql: Is it possible to handle 1000 concurrent while having low cpu? [closed]

We're running Centos 7, we're in the process Mariadb 5.5, upgrading to 10.3 today. PHP 5. The queries are bad, and there are far too many of them to optimize, but I'm trying what I can, the database ...
0
votes
1answer
53 views

Aggregate function on subquery

I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order: Offer -> Order -> Delivery -> Invoice. For each new row their ...
1
vote
2answers
56 views

Optimizing a query by adding an index

Here are my tables: accounts_company: - id ... accounts_companyview: - account_id: FK to company - viewer_id: FK to company shipments_address: - companyview_id: FK to companyview - is_shipper:...
0
votes
1answer
70 views

How to optimize stored procedure so it won't end with “Out of memory exception”

I have created a stored procedure that check against 2 and then delete from the 3. When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: ...
0
votes
1answer
72 views

Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints?

I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right ...
-1
votes
0answers
22 views

Indexing strategy for optional predicates

Let me ask a similar question i asked before. I believe there is more scope of explanation on this one. CREATE TABLE [dbo].[SalesOrder]( [Id] [uniqueidentifier] NOT NULL, [PageId] [...
0
votes
1answer
50 views

Column as an Included column or as a key column

I have a query which has an optional where condition on column classID(not unique). This same classID is being displayed as a select column in various other queries. Currently i have classID as an ...
0
votes
3answers
68 views

Query takes too much time to execute

I add the Query Plan of my query. And this takes too much time to execute and give data. https://explain.depesz.com/s/4JtZ I want to reduce the execution time of this query. So require to optimize ...
0
votes
0answers
62 views

ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1. ...
2
votes
0answers
27 views

Why can't outer join simplification happen for embedding outer join?

The MySQL documentation here suggests the following: Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
0
votes
2answers
119 views

Horribly performing query, how to fix

select user_id, count(id) as unread_count from notifications where is_read = false and user_id in(select(unnest('{200 user IDs}' :: bigint[]))) group by user_id; Problem is, ...
0
votes
0answers
46 views

Mariadb query optimization - Procedure

I have a complicated procedure and I have 200 clients. When these 200 clients run the procedure simultaneously, the CPU value suddenly increases to 70%. Then it's 6% again. How can I optimize these ...
0
votes
1answer
30 views

PostgreSQL planner not using PK index

I have a query: select F.DVALUE as f1, VF.VAL as f2, f_strlen( VF.VAL ) as f3 from PR_STR_TO_LIST( '37593' ) L left join DUALS U on U.NUM >= 0 and U.NUM < 2 left join ...
1
vote
1answer
33 views

WHERE IN subquery too slow

I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k ...
7
votes
0answers
124 views

Constant Scan applying

This question is continuation of optimizer behavior exploration in regard to VALUES construct started here and here. I want to ask about VALUES and APPLY this time. Using CROSS APPLY to alias an ...
0
votes
0answers
20 views

How does MySQL optimizer compute join cost when indexed?

So, The standard formula in theory is C(R1.R2) = C(R1) + T(R1)*C(R2) where, C(R1.R2) - Cost of joining two relations (tables) R1 and R2 C(R1) - Cost of reading relation R1 C(R2) - ...
0
votes
1answer
31 views

Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL

PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype. I am importing lots of data, includes a timestamp column, but ...
1
vote
0answers
33 views

MySQL query optimization for a slow stored procedure

Edit: I am using MySQL 5.7.22 I have a stored procedure, when it is running it will have up to 1 millions rows examined. Which cause the 99% cpu usage. I want to optimize the query but I have no ...
0
votes
1answer
26 views

Mariadb query time extremely increased after upgrade from 10.2 to 10.4

After upgrading from Mariadb 10.2 to 10.4 extremely increased query time. On 10.2 query time is 0.5s. On 10.4 query time is 14s. Also query plan has changed too. 10.2: -------------------------...
3
votes
2answers
92 views

Why max(id) with join not working or how to optimize it with join?

Below is my MESSAGES_THREAD table: CREATE TABLE `messages_thread` ( `id` varchar(64) NOT NULL, `creator` mediumint(9) NOT NULL, `other` mediumint(9) NOT NULL, `time` datetime DEFAULT ...
0
votes
1answer
65 views

Determine least number of columns required for unique (clustered) index

I'm in the situation where I am encouraged to tune an existing database and in the process either introduce or remove indexes as seem best fit. I was looking at a certain table and noticed that the ...
1
vote
3answers
88 views

View: Ignore a left join if it is not used?

I have a web service (GIS feature service) that references a view called gis_sidewalks_vw. create table gis_sidewalks ( id number(10,0), last_edited_date date ); insert into gis_sidewalks ...
2
votes
1answer
57 views

Slow Postgres Aggregate Query

I need to calculate values for a series of measurement data. The measurement data is divided into 1000 groups of consecutive rows as each call of the query must output a series of 1000 values. Typical ...
-3
votes
1answer
39 views

Why we can't have more than one inequality condition in MySQL indexing? [closed]

I am recently studying about database indexing in MySQL. I read some articles and found a great web page: http://mysql.rjweb.org/doc.php/index_cookbook_mysql I read in this page and some others that ...
0
votes
0answers
10 views

Storage technique used in meet in the middle attacks

When a block cypher is used twice in a row, it can be broken in 2^{k+1} key searches (k denotes the number of bits used in key). This computational advantage comes at a price of storing 2^k pairs of ...
2
votes
2answers
67 views

Performance optimization: Alternative to LEFT JOIN the same table twice

I would like to extend the following query with the commented out LEFT JOIN. However, those lines make the query noticeably slower. The query is used for an API to lookup user IDs by phone number and ...
0
votes
0answers
23 views

Trying to identify if the query is long running or long waiting

I have a sql query which has multiple joins and uses a view which has multiple joins.I captured the query in sql profiler and i see few information in there. I am trying to understand whether the ...