Questions tagged [query-performance]
For questions about improving the performance and/or efficiency of database queries.
2,404
questions
0
votes
1answer
29 views
Help with tuning this UPDATE Query or possible re-write
I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
Declare @status_D INT,
Declare @StatusIP INT,
Declare @Status_F INT,...
-3
votes
0answers
19 views
Query Optimization for Subquery
Please help to optimize below MySQL query
SELECT * FROM (
SELECT u.name,u.avatar,u.occupation,
(SELECT GROUP_CONCAT(revenue)FROM LOGS WHERE user_id = u.id AND TYPE ='conversion') AS ...
0
votes
0answers
22 views
Slow query / wrong query plan for SORT BY LIMIT LEFT JOIN
I have a following 3 tables a,b,c all with compound unique key (application_id, tid) however all those tables are not related with foreign key (it's a limitation of our solution). All tables has ...
0
votes
0answers
15 views
Slow query on WordPress site [closed]
I have an ongoing issues with slow queries killing my WordPress site.
# Query_time: 4066.175238 Lock_time: 0.000487 Rows_sent: 0 Rows_examined: 11814357687
# Rows_affected: 0 Bytes_sent: 0
SET ...
2
votes
4answers
744 views
Get first and last record of a selection without using min() max()
My energy-meter is writing the (accumulated) consumtion every minute to a db. In order to get my todays consumption I use:
SELECT
(MAX(energy_kwh) - MIN(energy_kwh)) AS kwh_today
FROM logging....
1
vote
1answer
23 views
PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table
I have a dilemma that makes for sort of curious experiment to be conducted but I was wondering if it already has been done.
I have a table:
create table test(action_date date, item_num int, <...
0
votes
0answers
18 views
MySQL compare multiple values in WHERE clause, find subset
I have a table segments with column comp_id, segment_id.
comp_id: 1
segment_id: 23,11,223,3331;
comp_id: 2
segment_id: 231,11,223,3331;
I have another table: comps with segment_id as well.
comp_id: 1
...
0
votes
0answers
4 views
MongoDB numerous slow query logs with zero keysExamined
using a MongoDB version 3.6 Replica Set, I am wondering why a query that resulted in no key examination and document examination would result in being slow. For example, I have numerous slow query ...
1
vote
1answer
39 views
Decrease Insert and Update performance on bigger database
Have two partitioned tables (32 partitions / 16 files / 8 file groups). First table with 56 columns and Second table with 5 columns. One of requirment of application is to test procedures for future ...
0
votes
0answers
26 views
Storing URL paths as unique identifiers in Postgres [closed]
I need to find a performant way for storing and looking up friendly paths for a multi-tenant SaaS, e.g. app.com/client. I also need to retrieve the paths from the database to generate human-readable ...
0
votes
1answer
41 views
Does ' /*+parallel(<Specific Table >,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?
I have three tables with below structure:
create table customer_info
(customer_num number,
customer_firstName varchar2(100),
customer_lastName varchar2(100),
branch_code varchar2(...
3
votes
1answer
46 views
Ordering CONNECT BY siblings
I have an hierarchical query that uses CONNECT BY:
select
wonum,
parent,
classification,
classstructureid,
division,
worktype,
status,
glaccount,
fircode,
...
1
vote
1answer
22 views
MySql Set Column on First Appearance of Value
I have a select that returns something that looks like the following:
PeopleId
Timestamp
FavoriteNumber
1
2020-11-04 17:40:33
14
2
2020-12-06 21:19:41
59
3
2020-12-10 18:46:11
14
4
2020-12-15 00:...
1
vote
0answers
46 views
SQL Query expensive when try to combine everything into one query
I'm trying to join two queries into one.
First
EXPLAIN SELECT * FROM list
WHERE plate_type = 3 and plate_letters = 'VH' and plate_numbers > 9949
ORDER BY plate_type, plate_letters, plate_numbers
...
1
vote
0answers
68 views
Why SQL Server's MERGE has such a huge Excessive grant when compared to INSERT + DELETE?
I am trying to optimize a security computation process for a Web application by trying to making it synchronous (compute on the fly for an entity) instead of asynchronous (large computation for all ...
4
votes
1answer
244 views
Fastest query to filter product by countries
I have a large Product table:
ProductID
ProductName
1
Product 1
2
Product 2
...
...
n
Product n
whereas n is around 2,000,000.
To limit product availability to a certain country, I have a second ...
0
votes
1answer
39 views
WHERE…LIMIT much slower than WHERE…ORDER BY…LIMIT in MySQL 5.5
I'm using MySQL 5.5 (but i see the same behavior on 5.7)
Here is the table schema:
CREATE TABLE `so_table` (
`p` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`c1` int(10) DEFAULT NULL,
...
0
votes
1answer
32 views
what's the best way to execute fast aggregation queries based on YEAR and MONTH filters in addition to dynamic filters
My SQL table is going to have millions of rows. Currently, with only 2M rows, my aggregation queries take anywhere between 20 seconds to a minute.
The filters in WHERE clause for aggregation queries ...
2
votes
1answer
104 views
Suboptimal plan when joining on an ID column with incorrect statistics
We are having an issue with a joining query between 2 tables (showing only relevant columns from the query):
CREATE TABLE IF NOT EXISTS payments (
id TEXT PRIMARY KEY,
consumer_id TEXT,
...
12
votes
1answer
279 views
Why Does the SQL Server Query Optimizer Not Convert an OUTER JOIN to an INNER JOIN When a Trusted Foreign Key Is Present?
It appears that the SQL Server query optimizer does not translate an OUTER JOIN into an INNER JOIN when the join column in the first table is defined as NOT NULL and has a trusted foreign key ...
3
votes
1answer
58 views
Why does a list of 10,000 IDs perform better in a complicated query with multiple CTEs compared to the equivalent SQL to select them?
I have a Rails application with a legacy query that I'd like to refurbish. The current implementation performs two SQL queries: one to get a large number of IDs and a second query that uses those IDs ...
0
votes
1answer
23 views
Bulk SQL INSERT into Azure SQL Database using spark causes blocking/contention?
I am running the following code using microsoft's sql sparkconnector to write a 1-2 Billion dataframe into Azure SQL Database.
df.write \
.format("com.microsoft.sqlserver.jdbc.spark") \
....
0
votes
1answer
22 views
How do I efficiently emulate JSON_TABLE in Mariadb?
I don't have MySql readily available, but from what I understand, the best way to achieve what I want in MySql is to make use of JSON_TABLE. Also, from what I understand, JSON_TABLE is being very ...
1
vote
2answers
146 views
Preventative measures to avoid wrong cardinality estimate statistics
This is a follow-up to Queries randomly getting slow, good preventive practices?, attempting to ask a more specific question about one of the ideas stated there.
A common cause of queries suddenly ...
0
votes
1answer
342 views
Good practices for proactively preventing queries from randomly becoming slow [duplicate]
We support several deployments of an in-house application based on SQL Server.
We've had a problem several times now where some query that used to be quick and small suddenly becomes very slow or ...
0
votes
1answer
21 views
Optimizing simple query that performs a large table migration
There are two tables trains and train_statuses.
train_statuses already has a column train_status which is an ENUM.
CREATE TYPE train_status AS ENUM('queued', 'running', 'succeeded', 'failed', '...
0
votes
1answer
27 views
How to improve poor performance with two joined CTE expressions on hierarchical data?
I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched ...
0
votes
1answer
22 views
String search is very slow with OR statement
I have the following tables:
product, product_stock, and product_offer
I need to optimize the search when looking for a product that is available in product_stock or that is currently on offer. First, ...
0
votes
0answers
27 views
How get the total count and number of distinct rows in a single query?
I have the following relation:
contract => company => item
The contract (root) entity has an owner_id column which indicates which user created the contract. When I am doing some stuff with a ...
0
votes
1answer
33 views
WITH-clause not pre-calculated, lineal resource wasting, why?
I'm trying to save resources, and shorten code, but getting lineal resource wasting with "WITH-clause".
This is my base example:
#!/bin/bash
echo "CREATE TABLE 'clients_table' ( id ...
0
votes
2answers
37 views
Do frequent DML operations on a table with indexes and huge data affect SELECT query performance?
If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where ...
3
votes
4answers
154 views
Significant query performance degradation over time for only certain conditions
UPDATE: All problems described in this question disappeared after upgrade to MariaDB 10.4.17.
I've got strange situation with queries becoming increasingly slow over time.
Table with data in question ...
1
vote
2answers
56 views
mysql - large table - index performance and query issue
I have simplified a much larger table below:
CREATE TABLE `core` (
`id` int NOT NULL,
`loc_country` enum('United States','Colombia','United Kingdom',
'Australia','India','Germany','...
0
votes
2answers
40 views
MariaDB 10.4 Query Optimizer Wrong Execution Plan
I have a very strange behavior with some tables and the query optimizer on a MariaDB server.
First of all we have our main table let's call it huge and we have on the same database another table a ...
0
votes
1answer
35 views
Improve sqlite mapping query
I have an SQLite DB that has couples of tables. one of them looks like this:
(id integer primary key, location integer, name text, desc text, version integer)
select id,other_colum from table where ...
8
votes
2answers
209 views
Sqlite: Finding the next or previous element in a table consisting of integer tuples
I have a sqlite table called tuples defined like
create table tuples
(
a INTEGER not null,
b INTEGER not null,
c INTEGER not null,
d INTEGER not null,
primary key (a, b, c, d)
) ...
5
votes
2answers
105 views
Why does a list of 10,000 IDs perform better than using the equivalent SQL to select them?
I have a Rails application with a legacy query that I'd like to refurbish. The current implementation performs two SQL queries: one to get a large number of IDs and a second query that uses those IDs ...
5
votes
2answers
177 views
SQL Server Indexed View and TOP
I'm struggling to persuade a query plan to behave as I think it should. The addition of a TOP clause when querying an indexed view is causing a sub-optimal plan, and I'm hoping for some help in ...
2
votes
2answers
142 views
WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance
Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
0
votes
1answer
70 views
How to rewrite slow CTE construction to match speed of temp tables
I think the general advice of this community is to avoid temp tables in favor of CTEs. However, I sometimes encounter situations in which CTE constructions are very slow, while their temp table ...
0
votes
3answers
33 views
Improve Slow Count Query
I have two tables on Mysql 8 Server. One of is products (InnoDB) other is product_filter (InnoDB). I would like to take count for pagination but it takes more than 10 seconds every time. I had lots of ...
0
votes
0answers
20 views
UPDATE vs SELECT returns different Execution Plan
In my DB table, where ID is the PK, if I use this:
EXPLAIN SELECT * FROM table WHERE ID = 3;
The type and the ref columns of the explain output table are both equal to const.
Now, when I use this:
...
0
votes
2answers
75 views
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Let me set the stage:
SQL Server > 2016
Several databases with the same schemas
Data is similar but not identical
The same indexes exist etc., all systems but one work as expected
Examining the ...
0
votes
2answers
84 views
Prevent SQL from apparently executing an expensive sub query
I have a stored procedure of the form:
CREATE PROCEDURE StoredProcedure
@SearchString NVARCHAR(MAX),
@ComparisonType INT
AS
BEGIN
SELECT A.Value1,
B.Value1,
C....
2
votes
2answers
278 views
Why is it faster to create index and run query than run query without it?
I have table with few million rows. It contains logs from external service, so I've decided not to index it (lot of inserts, sparse reads).
When I run query that reads from the table without index, it ...
1
vote
1answer
25 views
Optimizing multi-criteria sort query
I want to efficiently sort a table in PostgreSQL based off of two criteria. Here is my table structure:
CREATE TABLE shapes
(
shape_id character varying,
shape_pt_lat double precision,
...
0
votes
0answers
43 views
Postgresql multicolumn index on tsrange and text columns
I have a complex query on a very small device (ARM9 @ 400 MHz with 256MB of RAM)
the postgresql db table I am querying has up to 10M rows.
SELECT *
FROM records_list a
INNER JOIN records_list b
...
1
vote
1answer
68 views
Optimisation select with large IN and millions of records in result
I work with a table which consists of 1.5B records.
DB: RDS, PostgreSQL 12.4, 16GB RAM, 4vCPU
Schema:
CREATE TABLE public.trip (
id bigint NOT NULL,
cell_to character varying NOT NULL,
...
1
vote
4answers
91 views
High performance count with where clause and dynamic data
I am looking for a high-performance approach to solve this problem.
I have a table on a SQL-Server 2019 with 316 columns and several million rows.
For this query, there are only a few of those columns ...
2
votes
3answers
251 views
Query to update one column of a table based on a column of a different table
I have two tables with below structure :
Create table Customer_info
(Customer_num varchar(50),
Customer_Branch int);
Create table Customer_Transaction_Info
(Customer_num varchar(50),
...
