Questions tagged [query-performance]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
2answers
16 views

What happens at low level when I add a column to a table in MySQL/ 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 ...
0
votes
1answer
413 views

Database ExecuteSQL(MySQL client run out of memory)

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that ...
0
votes
1answer
206 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
1
vote
1answer
99 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
132 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 ...
1
vote
1answer
555 views

Query taking more than 5 minutes to execute

I've a query that runs in 2 identical environments: Production and QA. In QA server, it takes around 6 seconds and in production it takes 5 minutes or more! Query is as follows: mysql> SELECT ...
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
3answers
371 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
2
votes
0answers
57 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 `...
0
votes
0answers
23 views

SQL Query assistance (plan link pasted)

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. ...
7
votes
1answer
1k views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers | ...
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 (...
1
vote
2answers
35 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
35 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,...
2
votes
1answer
66 views

Why does elimination of an if-statement cause notable speed up in my function?

I have the following table: CREATE TABLE [dbo].[StateChanges] ( [RecordId] uniqueidentifier NOT NULL, [LinkedObjectId] uniqueidentifier NOT NULL, [ChangeType] TINYINT not null, [...
2
votes
2answers
303 views

SQL Query optimization - Long running query

I have a database with the following tables : CREATE TABLE [Document]( [IdDocument] [int] NOT NULL, [DocumentType] [int] NOT NULL CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)...
0
votes
1answer
156 views

mysql speed multiple OR WHERE or multiple single queries, what is going to be fastest [closed]

I have an application at the minutes that loops through a multidimensional array and builds a query from it, currently the query looks like this, SELECT id FROM TABLE WHERE col_1 IN ("1,2,3") AND ...
0
votes
3answers
437 views

MySQL Connections slows down my website

I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database. I ran these queries show status like 'Connections'; +-----...
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 ...
1
vote
1answer
248 views

Intermittently Slow Query @ MySQL

Problem statement In a nutshell, the problem is that a query which has been smoothly running for months has started behaving weird. Particularly, the exact same query runs fast most of the times, but ...
0
votes
3answers
178 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 ...
1
vote
2answers
122 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 ...
0
votes
1answer
128 views

SQL Server : Memory pressure,Spiked user connections and CPU usage

I have the below sql server environment. SQL Server 2016 standard edition. 128 GB RAM 96 GB alllocated to SQL Server 8 tempdb's with 1 GB each. Have a major database with 2.5 TB size. Reporting also ...
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
187 views

Fastest way to check if record is related to a particular user in postgres

While implementing a sharelikes table I added a procedure to add a new record for a like. I wanted to add a check before insert that the item being liked does not belong to the users who submitted ...
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,...
2
votes
1answer
563 views

Why does postgres choose to sort instead of scanning the index?

I have the following table: # \d service Table "public.service" Column | Type | Modifiers -------------+----------+----------- customer_id | integer | not null date | date ...
1
vote
1answer
87 views

Neo4j import performance

I have a Neo4j 3.5.3 installation on my Ubuntu laptop (which is Intel i5, 4 GB RAM, SSD disk) and I'm trying to import a moderate-sized dataset from CSV files into the graph. Here is the full Cypher ...
28
votes
3answers
25k views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
4
votes
1answer
1k views

Slow postgres query that uses quick sort instead of hash-aggregate

I'm on postgres 9.5 and working with a web-analytics database that records visitor traffic. I'm trying to optimize a very slow query gives me a count of the unique people that visited a given page ...
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
28 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
2answers
154 views

PostgreSQL 10 Optimizing slow query performance

I have the following query: select i.id as id, i.ts_updated as updated, i."name" as name, cast(attr.data_norm as text) as data_norm, cast(_59.json_agg as text) as _59, ...
-2
votes
3answers
108 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
56 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
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 ...
1
vote
2answers
1k views

Performance of a big INSERT…SELECT with multiple JOIN

I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production ...
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....
0
votes
1answer
482 views

Slow stored procedure performance

I have a stored procedure that used to run fine without any issues before we made this change last evening and now the performance is unacceptable. The stored proc pulls data based on information in ...
1
vote
2answers
154 views

How to improve query performance?

I have a query that executes very often on our system and there is no option to change the query text. Are there any suggestions on what could be done? Any help would be appreciated. The query: ...
4
votes
1answer
355 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
0
votes
2answers
54 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:...
0
votes
1answer
486 views

Query goes in Copying to tmp table stauts in MySQL and never returns

I am getting some strange behaviors for my MySQL query status. Here is my query code: SELECT a.id as art_id, b.album_artist_id, s.song_title,s.song_seo,a.artist_seo,a.artist_name, b.album_title, b....
2
votes
1answer
43 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 ...
3
votes
1answer
827 views

What's the (Big O) computational complexity of a PostgreSQL recursive common table expression?

For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to ...
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
30 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
50 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) ...

1 2 3 4 5 42