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
6
votes
4answers
471 views

Counting occurrence of words in table is slow

Consider these simplified tables: CREATE TABLE dbo.words ( id bigint NOT NULL IDENTITY (1, 1), word varchar(32) NOT NULL, hits int NULL ) CREATE TABLE dbo.items ( id bigint NOT NULL ...
2
votes
1answer
197 views

Super poor UDF performance on only 1 server, not identical other server

I have the following code that takes 20 times longer on one production box (db35) than another identical box (db34). Both have: Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64)...
6
votes
1answer
144 views

Understanding Plan Choice When Query Store Doesn't Respect Forced Plan

I'm trying to figure out a situation that I encountered last week where, in an attempt to temporarily fix a plan regression during an incident, I forced an execution plan using Query Store, but found ...
0
votes
2answers
61 views

MySql GROUP BY, ORDER BY LIMIT query taking more than 5000 seconds

I have a simple table. In which one composite index is created. We have a job which hits select statement on slave server and which takes around 5000 seconds to execute query. Please check table and ...
2
votes
3answers
149 views

Optimizing slow query

For a few days now, i'm trying to optimize this query. But still I haven't found the right solution in how to fix this. The contents table contains about 20 million records. As the connect table ...
1
vote
2answers
74 views

How to Speed Up my SQL Query?

I have a query that is taking much longer than I expect it should, and I can not figure out how to speed it up. For a little bit of background on the table structure, these are audited records. There'...
0
votes
4answers
74 views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
1
vote
1answer
93 views

Awful performance on joined queries while great separately [closed]

I have a simple queries: SELECT t1.[IntField], CONVERT(varchar,t1.[KhDateTimeField],121) AS 'TDateTimeField' , t1.[FloatField1], t1.[FloatField2] , t2.[FloatField3] FROM ( SELECT t.[...
1
vote
1answer
425 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
3
votes
1answer
942 views

speed up PostgreSQL calculations via OLAP warehouse?

So I have asked these three questions: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5 https://quant.stackexchange.com/questions/29572/building-financial-data-...
3
votes
3answers
849 views

Optimise Large Record Deletion Procedure

I'm creating a database that mirrors our production database, but is lighter and anonymised - for local development purposes. Ensuring we've enough data to operate on as an engineering team, I'm ...
14
votes
2answers
7k views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
1
vote
1answer
258 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
0
votes
2answers
57 views

usage percentage of Mongodb internal cache

I've ran two performance test on a MongoDB server with identical environment/settings AFAIK. I found that the throughput was 10% apart. When I inspected the mongostat logs, I found that Mongodb in ...
0
votes
1answer
29 views

Speed up simple MySQL query on large database

I have a database with 1 million records and I frequently run this very simple query: select id from my_table limit 1000000; It takes around 5 seconds to execute. And if I add more columns to the ...
0
votes
2answers
822 views

How to make MySQL use indexes in an integer range select query

I'm doing a select on a table looking for rows that match between an upper and lower bound SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ; the ( simplified ) create ...
1
vote
3answers
144 views

Slow query in production, bad execution plan or bad index?

I've just fixed a production performance issue by dropping an index and recreate it. I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad. ...
1
vote
1answer
102 views

MySQL DB performance is degrading on connecting using multiple clients

I am facing weird behavior in our MySQL DB and need your expert guidance on this. We have a query (simple select without join, usages index as well) which is executes in 5.8 seconds However, when we ...
5
votes
1answer
499 views

Storing prices with price history and different specificity (global, user group, single user) in MySQL database

I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). ...
7
votes
3answers
13k views

how to test if XML = '' in sql server?

I am working with sql server 2005 and I had a situation where many values can be passed in a parameter. Based on this: Passing multiple values for one SQL parameter this procedure uses XML as a ...
5
votes
1answer
475 views

MySQL Partitions with Partition limit

I have a partitioned table on the basis of the Primary key having 1.5 Billion rows in it. But when I check the .ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know ...
0
votes
1answer
45 views

How to make PG query faster even when it is using indices

I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question ...
1
vote
1answer
33 views

Optimize postgresql query with multiple subqueries

I'm trying to optimize this query taking between 3-4 minutes (it's running on hdd right now and it will go as low as 10s on sdd when going to production I guess but it's still very high). Table ...
2
votes
1answer
9k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
2
votes
0answers
67 views

What's the more efficient way of selecting with multi left joins?

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
1
vote
2answers
58 views

Unable to retrieve query plan of running query because of blocking

I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes. Probably some plan caching issue. But the problem is ...
2
votes
0answers
37 views

Performance test against identical tables (including data) with very different results [closed]

I created 2 identical schemas and a jmeter performance test to highlight the differences between an indexed and non-indexed table. All tables have clustered indexes from their primary keys. Before ...
0
votes
0answers
15 views

Modeling tabular structure in MSSQL to store data like Excel sheet applying Column inheritance

I've created this database structure to store tabular information (it's a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different ...
1
vote
1answer
498 views

Window aggregate resulting in slow query on postgres 9.6

I'm using Postgresql 9.6 and I'm running into a performance issue. I have a relatively simple query: explain analyze SELECT * FROM ( SELECT A.person_id, O.session_id, timestamp_ AS ...
16
votes
2answers
21k views

Most Efficient Way to Retrieve Date Ranges

What's the most efficient way to retrieve date ranges with a table structure like this? create table SomeDateTable ( id int identity(1, 1) not null, StartDate datetime not null, EndDate ...
13
votes
5answers
2k views

Does SentryOne Plan Explorer work?

Does SentryOne Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about? It looks like it shows the hot path in color commpared to SSMS's ...
1
vote
2answers
3k views

Creating Sort Index takes 96% of query time even without any ORDER BY

I have this query: SELECT itr.recipe_id, SUM(itr.weight), SUM(aprice_weight), SUM(itr.weight+aprice_weight) AS score FROM (SELECT itfin.id AS flyer_item_id, itfin.flyer_id, ...
0
votes
0answers
17 views

Is querying by a PRIMARY KEY in MySQL faster than other queries?

I have two queries that return on a dataset of 200,000 records with apparently the same performance. The first looks up a primary key, and the second a varchar(32) with no special constraints. Am I ...
2
votes
1answer
103 views

SELECT operation with four joins results in bad performance

I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places. Resources ...
0
votes
1answer
20 views

Optimize query between two MySql tables

I currently have a query that works on two tables of expense and income. This is an example of what the structure of both tables looks like since they have the same columns: | id| date | ...
1
vote
1answer
91 views

Changing stored procedure parameter results slow stored procedure performance

I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to ...
0
votes
1answer
89 views

SQL Server - Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014

I have 2 databases that contain nearly identical data. The first has compatibility level 100 and is restored on a sql2012 instance. The second has compatibility level 110 and is restored on a sql2014 ...
3
votes
0answers
35 views

How to optimize this recursive postgresql query?

I've been searching a lot on this topic but I'm stuck at optimizing a query. I've created a complete dbfiddle to see the problem, if you increase the amount of inserts for the comments table you'll ...
0
votes
2answers
62 views

Table design and query Optimization: Query to find the suitable work from a list of work items

I have table with jsonb column as below CREATE TABLE work ( id SERIAL NOT NULL, work_data JSONB ); sample data is as follows: 100 {"work_id": [7245, 3991, 3358, 1028]} ...
0
votes
1answer
126 views

Query sometime not using index

Starting in the middle of last night (of course) I have a query that stops using an index and when that happens, it takes over an hour to complete vs. about 3 seconds when it uses the index. This ...
0
votes
3answers
813 views

Percent of total: Is there a more efficient way than using a subselect?

I have an ASSET_SUMMARY_VW view: +-----------------------+---------------+ | ASSET_TYPE | COST | +-----------------------+---------------+ | TRANSPORTATION | 1,000,000,000 |...
1
vote
1answer
46 views

Postgres query plan shows sequential scan instead of a index scan with on the fly data

My use case is I am trying to select rows based on a list of known text values. For example, I have known text values as 'Frasier' and 'Cheers' and I want to select some rows that match the values. ...
1
vote
1answer
91 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
0
votes
1answer
86 views

MySQL Huge DELETE

I'm trying to delete 10000 rows from my database on MySQL 5.7 but it's been stuck for 7 hours. Query: DELETE FROM article WHERE id in (52109548,52109558,52109560,52109564,....). This are my buffer ...
3
votes
3answers
222 views

How to optimize an ordered date range query with several OR filters (for streaming)?

I have a table of data which is be outputted into a stream, so the upper bound of the cost doesn't matter as much as the lower bound. The table contains about 200M rows. The question is how to ...
2
votes
0answers
54 views

Optimizing a paginated query with various filters and dynamic sorting

Introduction Imagine a (fairly advanced) ToDo app in which items are catogorized into lists (which can be temporarily deactivated), have custom additional attributes, can be assigned to a user and ...
3
votes
2answers
209 views

SQL Server expensive nested loops join and lazy table spool

I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is: select distinct d.documentpath as path, d.documentname as name, d....
0
votes
2answers
88 views

MySQL - two tables with relation one to many - retrieve “n” fields from table1 WHERE SUM of a field on table2 is less/equal/greater than “value”

Tables structure: CREATE TABLE IF NOT EXISTS `A` ( `id` int(11) NOT NULL AUTO_INCREMENT, `num` int(6) NOT NULL, `docnr` int(6) NOT NULL, `docy` varchar(3) NOT NULL, `docd` date NOT NULL, `ent` int(3) ...
0
votes
0answers
61 views

MySQL Explain. How to improve type ref extra NULL

I'm struggling to find the right index to the query shown below (I have improved it from minutes to 20 seconds but it needs to be faster): explain extended SELECT cta.NRO_CUENTA , (IF(aut.Reversada=...
0
votes
2answers
42 views

Storing the groupwise most recent related record

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the "...