Questions tagged [query-performance]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ...

1 2 3 4 5 42