Questions tagged [query-performance]
The query-performance tag has no usage guidance.
2,066
questions
0
votes
0answers
16 views
Read only database for time series event data
I have a time-series dataset (around 500 million rows, 4 columns) that stores public transport ticket validations.
Not sure if it matters, but it's an event driven dataset, without constant timestamp ...
3
votes
3answers
36 views
Is there any performance issue with including the “COLLATE” as part of the query as opposed to setting it for the column/table?
PostgreSQL has this "collate" concept. You can either tell a table's column to always have a "collate", or you can do it in the query.
If I do it in the query, does PG punish me somehow performance-...
0
votes
2answers
22 views
Inherit INNER JOIN fields in MySql SELECT QUERY
I want to know how can i inherit one field of an INNER JOIN table in a SELECT QUERY.
Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to inherit tableA and ...
0
votes
2answers
29 views
What happens at low level when I add a column to a table in PostgreSQL?
I have a table with number of rows in the order of billions. Let's say I want to add a column to that table. I would like to know what happens behind the scene to know the cost, expected time of this ...
1
vote
0answers
17 views
Why is PostgreSQL prefix/range search finicky?
Configuration
PostgreSQL 11.4
SET default_statistics_target=1000;
Data
The scenario is that there are "computers" each with "files", which each have path (ordered text list) unique to that ...
0
votes
1answer
150 views
Slow query compilation, lots of reads during compilation phase
I have a query of the following form
exec sp_executesql N' UPDATE table SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000'' WHERE (column5=@P1 AND column6=@P2 )',N'@P1 ...
0
votes
0answers
24 views
SQL Query assistance (plan link pasted) [closed]
I have a query that I was able to take down from 6 minutes to 3 minutes -
Just curious if anyone can see if there is anything additional I can do w/ this query or if I am missing something simple.
...
2
votes
1answer
68 views
Why does MySQL take so long to execute this query on a large dataset?
Been banging my head against the wall on this one for a while now and hoping someone can help me out.
The SQL query:
SELECT
`elements`.`id` AS `elementsId`,
`elements_sites`.`id` AS `...
1
vote
1answer
108 views
Funky cardinality estimate skews, high memory grants and missing statistics
I have a select statement within a procedure that is causing me some grief at the moment. The query estimates about 100 million rows and that in turn grants large chunks of memory (about 8 gigs per ...
0
votes
1answer
37 views
Optimal query pattern to count related entries in PostgreSQL
Assume I have the following table schema and data
create table categories(
id integer primary key,
parent_id integer
);
insert into categories
values
(1,0),
(2,0),
(3,1),
(4,...
1
vote
3answers
45 views
MAX(id) function in query with indexed condition
I have a table
create table test
(
id int auto_increment,
createdAt TIMESTAMP not null,
primary key (id)
);
create index createdAt
on test (createdAt);
And here is my query
SELECT MAX(id) ...
0
votes
1answer
30 views
How to run analysis on a 16M row table faster
I have two tables students with 6k records and transactions with 16M records.
I have a query similar to this:
select section, count(s.students) from students as s
where upper(name) in
(select ...
0
votes
1answer
22 views
accelerating a SQL quadruple self-join with a complex alternation in the WHERE clause
The following Sqlite query has a triple self-join on table t:
SELECT "update_variable",
lhs_acc.name_suffix || ":" || rhs_var.name_suffix,
op.span,
op.path
FROM t op
JOIN t lhs_acc ON (...
0
votes
0answers
24 views
Improve MySQL query efficiency for first row in a group
I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions.
Reading other similar posts about how to access the first row in a group, it seemed like the ...
0
votes
1answer
29 views
Query with left join not using index
The below query is taking about 1 second to run
SELECT `taxi_driver_mapping`.*,
concat("dpk00",
taxi_driver_mapping.mapping_payment_plan_id) AS package_text,
`people`.*,
...
0
votes
1answer
29 views
Need help with indexes on 2 slow wordpress query
Here are my my 2 slow query i want to improve.
SELECT object_id, term_taxonomy_id
FROM wp_term_relationships
INNER JOIN wp_posts
ON object_id = ID
WHERE term_taxonomy_id IN (525627,516360,525519,...
0
votes
1answer
38 views
How can I improve this slow query in my wordpress site?
SELECT object_id, term_taxonomy_id
FROM wp_term_relationships
INNER JOIN wp_posts
ON object_id = ID
WHERE term_taxonomy_id IN (525627,516360,525519,535782,517555,525186,517572,549564,1,517754,541497,...
1
vote
2answers
125 views
Bigint and SQL Server performance
I inherited a database in which many of the tables have bigint as a datatype for many of the fields, and when you see the content it does not requiere all the space that bigint offers. Does using ...
1
vote
0answers
23 views
MySQL SELECT .. WHERE .. ORDER BY … LIMIT 1000000,10 performance
I have following Innodb table with ~2.500.000 rows:
ID | param1 | param2 | param3 | param4 | sort_field1 | sort_field2 | sort_field3 | sort_field4
ID - int, PK
param1-4 - tinyint(1), INDEX (it has ...
0
votes
2answers
29 views
Need help in optimizing a slow Postgres Query
So, I need to calculate a rolling Monthly active users on everyday for last 100 days. In output, one column should represent the dates of last 100 days from today and against every single day, number ...
0
votes
0answers
49 views
If there is no end-user input, is it safe to un-parameterize a parameterized query to avoid parameter sniffing?
AFAIK, a parameterized query is commonly used to prevent SQL injection attacks. What if there is no end-user input that can cause SQL injection? In this case, is it safe to un-parameterize a ...
0
votes
0answers
19 views
Improve PostgresSQL aggregation query performance
I am aggregating data from a Postgres table, the query is taking approx 2 seconds which I want to reduce to less than a second.
Please find below the execution details:
Query
select
a....
-2
votes
3answers
109 views
Query sometimes take 25 seconds to run
After a set of usual data access queries run, SQL Server 2019 gets into a state where one specific query takes almost exactly 25 seconds to execute. Under normal conditions it takes 200 ms.
When I ...
0
votes
2answers
58 views
Microsoft SQL Server equivalent to Oracle Enterprise Manger Top Activity Interface?
My DB performance management experiences is primarily with Oracle and I am looking for an equivalent set of tools in MSSQL. In particular something to easily spot historical SQL peaks and identify the ...
0
votes
2answers
55 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
44 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
2k views
Query runs in parallel but it is showing as blocked by itself
I have a query which runs in parallel with a MAXDOP of 8.
When i looked at sp_who2 i see that the same session ID is being repeated several times(>8) with different connection id.
I used the below ...
0
votes
0answers
31 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
1answer
52 views
Same session ID(with same SQL text) but 3 different connection ids
I was listing out all sessions open in my sql server 2016 production server.
I have issues with slow performance and i have been investigating it.
I see that i have several same sessions(session_id) ...
0
votes
1answer
41 views
PostgreSQL not using varchar index when ordering by primary key
My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it ...
0
votes
1answer
18 views
How to perform query operation on another query result
How to get approved , unapproved , deleted count from the below result using sql query.
SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher`
WHERE company_id = 1 GROUP BY approved,...
0
votes
0answers
71 views
“Change user” command slow query
We have a back-end server using ASP Core 3.0, and we are using pomelo library to make a connection to our MySQL database.
Every now and then the server starts to fire a LOT of SQL exceptions saying ...
0
votes
1answer
40 views
Improving performance of mapping standardized values with non equi joins - LIKE operator
I have a large-ish data warehouse staging table (~2.5 million rows) that is using a mapping table to standardize a text column. I have an update statement that works perfectly, but has terrible ...
-1
votes
1answer
46 views
Can a query that executes quickly still be a poorly written query? [closed]
This is more of a theoretical question and here is some background. Our system uses MySQL and was experiencing some slowdowns late in the day. Running SHOW FULL PROCESSLIST showed me around 10 rows ...
2
votes
1answer
126 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 ...
0
votes
3answers
186 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 ...
4
votes
1answer
328 views
Optimize a slow nested loops join
I have this query running around 15 seconds on my system. The query plan is here. I believe the main problem is around here, where the optimizer chooses nested loops and is mistaken with cardinality:
...
1
vote
2answers
28 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;
...
1
vote
2answers
43 views
Alternative to slow [LEFT JOIN + OR] without UNION?
I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables.
The query using only left joins ...
1
vote
2answers
62 views
Postgresql query is very slow for my big table
My database version is postgresql 9.5.
create table if not exists request_log
(
id bigserial not null constraint app_requests_pkey primary key,
request_date timestamp not ...
1
vote
1answer
69 views
Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows
I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them.
Trying to insert this query in a table on destination server takes over 2 ...
9
votes
3answers
280 views
Help performance tuning master/detail (email like inbox) SQL query
I have spent the last couple days searching, watching videos, and I think I've gotten as far as I can get just fumbling my way through. I am looking for more specific direction given my example below.
...
1
vote
2answers
77 views
Overestimated query resulting in poor performance - TVF with xml processing
Today i asked question about bad query performance. That query had a lot of joins over a lot of views. I think i have found which view is a trouble for SQL server. Here is actuall query plan for ...
2
votes
3answers
56 views
MySQL query very slow with multiple concurrent users in a short time
I have a site with a MySQL 5.5 database where most of the traffic happens at a fixed time every single day when thousands of users are loading the site in a period of 2-3 hours.
My slow query log (...
0
votes
1answer
19 views
Optimal query for comparing table column vs. summary column
What is the most efficient way to stack a field on one table with a summary field summarizing child data in another table?
For example, I have an account table and a task table. Below is the query I'...
1
vote
2answers
91 views
Multiple instance of Update Query is causing very high Lck_M_U wait thus high CPU
My investigation found the following query is causing high Lck_M_U wait. I noticed there are multiple instance of this query running obviously competing for the same resource. I wonder if there is ...
2
votes
1answer
44 views
Get first part of result faster in sorting query on hundreds of millions of rows?
I have a single table containing around 280 million rows in MariaDB 10.4.10.
I need to process this full table in an external program, sorted by timestamp. The external program is fast; the largest ...
0
votes
1answer
61 views
Optimize query that accoridng to plan produces over 500 billions rows - Actual result is only 400k rows
I'm trying to improve DB performance. I'v noticed a query that according to the plan it produces a lot of rows but i know for fact there are no more than 400k rows in the table after that insert. Why ...
0
votes
2answers
37 views
Select records that have at least one link to other tables
I want to select all users that have either a post, a photo or a video. I came up with the following query.
select
users.username
from
users
where
exists (select * from posts where users.id = ...
-1
votes
1answer
77 views
Problem with indexing null values
There is a table 'test' with ~ 10M records with field 'finish'.
select count(*) from test where finish is null;
count
---------
2485009
(1 row)
I'd like to create index to speed up queries like ...
