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
2
votes
2answers
61 views

How to make DDL changes to large tables and cause minimum server and object contention?

I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
-3
votes
0answers
29 views

Optimise/Speed up query [closed]

Below query is used for inserting and updating the tables in the mssql database. INSERT INTO BalanceTable ([AccountID],[Type],[Date],[Balance],[TRansactionDr],[TRansactionCr]) VALUES (134,'month', '...
0
votes
2answers
49 views

How to store daily records in SQL Server 2017 database? [closed]

So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries. Example:...
2
votes
1answer
36 views

Strange performance of a top-N query with Postgres

I have a table transfer_event from which I need to find the latest n entries for a set of tokens; both n and the tokens are supplied by the user. I am using Postgres 9.6. The question is that if I ...
2
votes
1answer
39 views

Finding stored procedures with XACT ABORT ON OR OFF

I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog ...
1
vote
2answers
36 views

Are there side effects to Postgres 12's NOT MATERIALIZED directive?

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
0
votes
0answers
28 views

optimize query with fulltext index in mysql

I have a column inside my mysql table that has a fulltext index and I need to make a query in which I get all the rows that do not have a specific word or those that start with those letters, so I ...
0
votes
0answers
24 views

Small read cache on SSD (MySQL)

I have a question concerning performance settings/setup in MySQL DB/InnoDB. The server has SSD disks so general access to data is fast. Although there is enough of RAM, but enough is never enough and ...
0
votes
1answer
22 views

How to avoid index_merge in a query?

I have multiple queries using index_merge but for put an example, I show you the following query EXPLAIN select count(*) From Clientes Where id_campana='1849' and id_empresa='55' and (...
0
votes
1answer
16 views

Multiple query to columns indexed in MySql

I am trying to make a table with server-side page, so it can also apply filters for each of the columns in my table. This is its structure: +----------------+ | products | |----------------| | ...
-1
votes
0answers
51 views

Query optimization (Index spool)

I have a query that need to be optimized. You can see the query plan here There you can see that 85% of the cost belows to an Index Spool but I cannot find the way to optimize it. There is a ...
0
votes
1answer
22 views

Why is it important to carefully determine and select statistics for the query optimization process?

It's a general question rather than a db engine specific one. In popular db engines, we see the query optimization process taking statistics into consideration. I'm trying to understand what types of ...
0
votes
0answers
24 views

Mysql: extremely weird index behaviour on “SELECT WHERE IN”?

I've just been stuck for a bit on a couple of very weird index choices from mysql. I have a table with half a billion entries and primary key is an auto inc ID Original query: "select id from table ...
0
votes
1answer
19 views

Optimal Table Structure

We have a parent table, Journal and a child table, Stops. A Journal can have X amount of Stops. For a good 80% of our SQL queries, our customers are just interested in the first and last Stop for ...
-1
votes
2answers
44 views

MySQL Select Query not using Required Index

I have a Table which has 2 Million Records and No primary or Unique Keys. I added Two Indexes and I'm expecting an Index to be used for my Select Query. When I saw the Explain Plan , Optimizer is not ...
0
votes
0answers
10 views

Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial. Or in other words, does the query optimizer ...
2
votes
1answer
122 views

Mysql GROUP BY is really slow on a simple view

I have these tables. CREATE TABLE `movements` ( `movementId` mediumint(8) UNSIGNED NOT NULL, `movementType` tinyint(3) UNSIGNED NOT NULL, `deleted` tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB ...
1
vote
0answers
67 views

Database - limit of reading a table with a lot of concurrent batch read and writes

We have this table. It contains data which will be picked up by an hourly batch job for processing. Five times a day, there are also multiple batch ETL jobs which get data from other source table and ...
1
vote
1answer
100 views

How to optimize this Select MAX() SQL query?

In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this ...
2
votes
3answers
235 views

SQL Server 2017 Stored Proc & TVF Performance Problem

I have a SQL Server 2017 Enterprise Edition instance where a stored procedure was taking approx. five minutes to execute. After reviewing the stored procedure code, I could see that there was an ...
1
vote
2answers
27 views

Postgres doesn't use expression index for group by

I'm doing a simple group by on a single column with a matching index and it works fine: create index on contacts (home_city); select home_city, count(*) from contacts group by home_city limit 50; ...
0
votes
0answers
27 views

Union the same table. Optimize query

I'm coming with a question about merging two data sets. I am using STI (single table inheritance) in the products table, so one column defines what type of product the specific row stores. The ...
1
vote
0answers
28 views

Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?

I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
4
votes
1answer
234 views

SQL Server chooses unselective index

I was testing SQL Server indexes and found very strange behavior. Here is my code: DROP TABLE IF EXISTS dbo._Test DROP TABLE IF EXISTS dbo._Newtest GO CREATE TABLE _Test( ID INT NOT NULL, ...
1
vote
1answer
38 views

Explanation for second Compute Scalar

I noticed a peculiar thing. This query declare @t int = 3; select distinct top(@t) Number - Number + ABS(CHECKSUM( CAST(NEWID() as binary(16)) )) from Numbers as n; produces this plan dbo....
0
votes
0answers
23 views

SARGABLE predicate must be in DNF. Why?

This famous paper about finding Access Path in Relational Database Management System by Selinger et al. says the following about criteria tree and predicates (at the end of 3rd section). A sargable ...
-2
votes
1answer
53 views

Optimize query with nested inner query and case statements [closed]

I have below query written by someone targeting Microsoft SQL server 2017. I do not have access to database to create indexes etc., So only way I can do is optimize below query as efficient as ...
0
votes
1answer
53 views

Is a separate execution plan generated for each dynamic SQL query that is executed within a stored procedure?

If I have a stored procedure and I re-write the logic so that some of the queries are executed as dynamic SQL, will those queries now get their own individual execution plans in addition to the stored ...
2
votes
2answers
35 views

Postgresql expanding view multiple times instead of executing it once

I have a rahter long subquery which I store as a view CREATE VIEW scans_minmax AS SELECT tmp.source, value ->> 'Ok' AS "Ok", ...
0
votes
2answers
37 views

How to write SQL queries to populate the tables while synchronizing transactions and account balance?

I have two tables having the following schema. CREATE TABLE users( `id` INT AUTO_INCREMENT, `balance` DECIMAL(10, 2), PRIMARY KEY(`id`) ); CREATE TABLE user_transactions( `id` INT ...
1
vote
0answers
30 views

Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error [duplicate]

I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX) Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a ...
2
votes
2answers
73 views

Postgresql Query plan different on two servers

I'm having some strange issue of running the same query between prod and dev and receiving a bit of a different query plan and I'm not sure why. I'll try to cover everything I can, but if I forgot ...
0
votes
1answer
25 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
1
vote
0answers
60 views

Slow MySql with left join and Group by

Below is the Mysql 5.7 Table query which has a left join with the group by. When I execute it takes more execution time. line_items have around 35 Lakhs (3.5M) data and users have 1000 rows. Below is ...
1
vote
1answer
52 views

Is there a maximum IOPS for MySQL asynchronous replication on the slave server?

I have been struggling with this issue for a while now. I have a simple Master-Slave setup with MySQL 8.0.15 installed on both systems. In day-to-day operations the slave keeps up the Master with no ...
1
vote
1answer
122 views

How can Inner join be optimize? [closed]

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
53 views

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

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
2answers
114 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); ...
0
votes
1answer
30 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
33 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 ...
3
votes
3answers
130 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
44 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
36 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
472 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
228 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
43 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
114 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
72 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 ...