Questions tagged [query-performance]

Use this tag for questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
0
votes
1answer
28 views

Query to filter a table based on a comparison applied to the same table?

Case & Problem: Consider a corporate building with some rooms and departments that need authorization to enter. Employees have to scan their badges to be authorized. If the employee is inside (...
1
vote
1answer
81 views

How to improve simple update statement causing blocking issues, inneficient index scan in query plan

Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, ...
3
votes
2answers
126 views

Index Seek scanning whole table dependent on parameter value

I have a query: SELECT Id, ColumnA, ColumnB FROM MyTable WHERE ColumnA = @varA OR ColumnB = @varB The table is defined as CREATE TABLE MyTable ( Id INT IDENTITY(...
0
votes
2answers
142 views

same query gets slower after other queries get called (using a lot of prepared statements inside stored procedures)

I have a query that, when i run it first after i restart mysql, takes 2 seconds, but when I run a sequence of other queries before it (this query belongs to a procedure between other procedures) ...
0
votes
1answer
49 views

SELECT optimization in Postgres 10

I want to select last unique rows based on time, so: SELECT DISTINCT ON (track) * FROM eco.tracks WHERE id > (SELECT id FROM eco.tracks WHERE time_track < ((SELECT time_track FROM eco....
2
votes
2answers
42 views

Postgres query very slow when using index

So I've run into this issue with a slow Postgres query. I've added an index but it doesn't seem to help. It takes 8 minutes to get 100 records from a table with 2822968 records. Here's the query ...
1
vote
1answer
70 views

How to make delete duplicates faster?

On a mysql table with about 1.7M rows, I tried to delete duplicates posts: delete a FROM comment a INNER JOIN comment a2 WHERE a.id < a2.id AND a.body = a2.body; The result was: ...
3
votes
1answer
224 views

Can low memory cause Table Spool operators in plan [duplicate]

I was trying to optimize a query and found that the plan had table spool(eager) in it. But when I ran it on another server which had much more memory(256GB compared to 64GB) than the other one the ...
3
votes
1answer
69 views

Reused Nested Decision Logic - CTE vs Copying Code

Question I have a query which outputs one column, created through a series of CASE statements. That same column is used as part of a CASE logic for a 2nd column in the same SELECT statement. If I ...
2
votes
2answers
116 views

How to optimize a MySQL joining to itself?

I need to optimize a MySQL (5.7.12) query. I've been reading the docs about optimization in MySQL docs but It's been hard for me to comprehend it. I have two tables: vehicles CREATE TABLE `vehicles` ...
8
votes
2answers
156 views

Very similar queries, vastly different performance

I have two very similar queries First query: SELECT count(*) FROM Audits a JOIN AuditRelatedIds ari ON a.Id = ari.AuditId WHERE ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1' ...
0
votes
1answer
48 views

Cannot find slow (frozen) running query in Query Store [closed]

I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes ...
5
votes
1answer
566 views

CASE, VIEW, CTE or JOIN LATERAL: which is the best approach?

As a follow-up to this question, I have one of my own. The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously ...
0
votes
2answers
34 views

MySQL compare values of two large tables

On MySQL 5.7 I want to get the records from table 1 that are not present in table 2. I'm using this simple LEFT JOIN query that is very slow (more than 2 minutes), I don't know why. SELECT * FROM t1 ...
6
votes
1answer
191 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
0
votes
1answer
42 views

Why does this MySQL query take 25+ seconds?

The query is: SELECT T.ID, T.A, T.T FROM table1 AS T WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T I have a BTREE index ...
0
votes
1answer
196 views

Loading 400 Million Records into MySQL Hangs on “Sending Data”

I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. ...
0
votes
1answer
24 views

PostgreSQL 9.6: Update on a single row table takes an unreasonable amount of time

I've got a table called source which has a primary key on its id and a “Unique Constraint” on the column name. This is the way the table looks: id int4 name ...
0
votes
0answers
67 views

How to calculate count of rows of Memory-Optimized Table?

Performance is important for me so I don't use select count(*) from MyTable. I use the following query to calculate rows count: SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [...
0
votes
2answers
33 views

large number of tables and postgres query performace

I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 ...
0
votes
0answers
70 views

How to get hit (number of possible results of a query) from a table with more than 100 million records in Postgres

We have a table X in postgres v11, where it has 5 columns like A B C D E. We all columns are indexed and there are all integer values. We have around, 100 milion records, but soon will turn into 800 ...
1
vote
2answers
100 views

Postgres: why did this trigram index slow down regexp queries?

I have a TEXT keyvalues column in Postgres: select * from test5 limit 5; id | keyvalues ----+------------------------------------------------------ 1 | ^ first 1 | second 3 ...
2
votes
1answer
843 views

Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS

We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are ...
1
vote
1answer
44 views

Random selection with a “complex” subset structure

This is my first question in the whole StackExchange world. I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question. In my ...
3
votes
2answers
263 views

Performance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))

I seem to be having a huge performance gap between using hard coded values for a SELECT IN, vs a STRING_SPLIT. The query plans are identical except for the last phase where the index seek is getting ...
1
vote
0answers
88 views

Optimize multiple JOINs with large data

I have 3 tables with this structure: +-----------+ +-----------+ +-----------+ | Files | | Programs | | Channels | |-----------| |-----------| |-----------| | ID | | ID ...
1
vote
1answer
69 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 ...
1
vote
1answer
35 views

30 days rolling sum as base for fee calculation. Faster solution

I need to calculate PayPal fees or an online shop to show an overview table with the fees per day. I came up with a solution playing this over several views, but it's dead slow. I takes more than a ...
3
votes
1answer
93 views

Hash Match Spills and Key Lookup causing major performance issue

I have updated the statistics on all the indexes but this query is still taking close to 40 seconds to execute. I need it to be under 20. I am not a DBA and very new at reading execution plans. I ...
27
votes
3answers
24k 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 ...
0
votes
1answer
39 views

Very slow JOIN + ORDER BY query only in dev environment

I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query. This query is taking 75 ...
1
vote
0answers
60 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
0
votes
1answer
27 views

MSSQL. Tree structure fetching with conditions

I need to make a tree structure Table: create table Dirs ( Id uniqueidentifier primary key not null default (newid()), Name nvarchar(30) not null, ...
1
vote
1answer
67 views

Is it possible to move mongodb indexes to another file system?

Is it possible to separate MongoDB files location so the indexes are on another file system? In the example below, I have database with 1.5TB of data + indexes of 330GB. I have a very fast storage ...
1
vote
1answer
62 views

What are some consequences of putting quotes in a WHERE clause for an integer column?

I have a table with a column some_column of data type integer. I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same: select * from my_table ...
1
vote
1answer
600 views

Would a date clustered index speed up queries with date ranges? (SQL Server) [closed]

I'm not the expert on indexes really - I'm trying to work my way through a few materials now. Anyway, let's say I have a table with 7 million records spanning 3 years. However the most common query ...
5
votes
1answer
290 views

SQL Server OR operator causing massive loop join

The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...). I used live execution to verify that there is a nested loop join ...
2
votes
1answer
343 views

Different plans on Readonly Secondary Replica

We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose. One of the reports intermittently runs for much longer than normal. I can see when it runs ...
2
votes
1answer
100 views

Understanding MAXDOP behaviour for queries going parallel

For my current server as below 4 Sockets with 10 cores per socket and 20 logical processors per socket. Total 80 per SQL licensing: 4 NUMA nodes with HT enabled I've set CTOP 50 and MAXDOP 8 on ...
0
votes
1answer
46 views

Speed up slow postgres query with indices (~5 minutes)

I have roughly 100 million rows in a table called 'bets' and a couple million in 'games'. The problem is that even with indices particular queries run very slow. The tables are structured as so: ...
2
votes
2answers
203 views

Should I split timestamp parts into separate columns?

I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later ...
0
votes
0answers
30 views

ORACLE : Query optimisation - How to optimize query by replacing “order by” and few row fetch after ordering

Below is the join query where in query takes time mostly for "order by" and fetching top few rows after ordering. SELECT * FROM ( SELECT q.* ,ROWNUM RN FROM ( SELECT T1.X,T2....
5
votes
2answers
6k views

MySQL slow select from a view

I would appreciate an approach with this problem, I have a lottery system in mysql, this system receives a lot of transaction per second.. a lot! I need to check the total amount for each the number ...
32
votes
5answers
34k views

UPDATE performance where no data changes

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to ...
1
vote
2answers
49 views

Inline function Plan Properties in SQL Server 2008

Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is ...
0
votes
0answers
47 views

Designing a database for a weekly time schedule - speed vs space

I need to "store" a weekly time schedule as compactly as possible. The schedules are in 15-minute increments. In a 24 hour period that would give me 96 time slots. Midnight is 00, 0600 is 24, 1800 ...
18
votes
1answer
1k views

Single-row INSERT…SELECT much slower than separate SELECT

Given the following heap table with 400 rows numbered from 1 to 400: DROP TABLE IF EXISTS dbo.N; GO SELECT SV.number INTO dbo.N FROM master.dbo.spt_values AS SV WHERE SV.[type] = N'P' ...
2
votes
2answers
2k views

oracle performance on date range query

I am trying to count the number of orders passed by a certain organization over a certain time range. But I found that the below query (time range of 2 days) is much much slower that doing two ...
1
vote
0answers
33 views

Different query plans for same In line Function [duplicate]

I am facing a potential parameter sniffing issue with an In Line function in sql server 2008. Basically Given the below three queries (same function): First Query: DECLARE @SQL NVARCHAR(1000) ...
0
votes
0answers
48 views

MYSQL 5.7 Online DDL (Alter) Statement causing Lock_Wait_Timeout : AWS RDS

We are not able to do updated code publish from application on MySQL 5.7 as it contains ALTER statement on the table which has more than 50 million records and application team escalated that most of ...