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
57 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
93 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
460 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
12k 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
451 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
39 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
23 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 ...
0
votes
1answer
19 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 ...
2
votes
1answer
8k 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
48 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
36 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
13 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
447 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 ...
14
votes
2answers
20k 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
15 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 ...
1
vote
1answer
103 views

What could lead to excesive PAGEIOLATCH_EX

I'm having some delays when insert into a table. The funny thing is that it doesn't always happen. Basically it's part of a load test that I created to compare some new features. I run the store ...
0
votes
1answer
19 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
2answers
120 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: ...
1
vote
1answer
83 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
88 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 ...
1
vote
1answer
38 views

MY query getting slow when use sub query and exist [closed]

CREATE TABLE `temp_numbers` ( `win_digit` varchar(10) NOT NULL, `amount` int(11) NOT NULL, `session` varchar(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `...
3
votes
0answers
30 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
56 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
114 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
605 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
40 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
90 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
85 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
220 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
49 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
149 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
80 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
25 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
38 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 "...
2
votes
1answer
2k views

Why MongoDB is so slow on some queries while using IXSCAN as a winning plan?

I have DB with about 30M records in a collection with about 100GB collection size (total documents and indexes). I have a compound index that filters data based on user_id and some other fields like: ...
0
votes
0answers
13 views

Comparing Across Tables to Find Most Recently Modified Row

all. Ive been working with some Sales Force data to solve some data synchronization problems we have between two instances of SFDC. Quick Background We have two instances of SFDC (SalesForce) that ...
1
vote
2answers
45 views

MySQL 5.6 users index instead of range type select on a big table and simple query

We have recently migrated from MySQL 5.5 to 5.6 (5.6.44-86.0-log Percona Server to be precise) and we have a huge problem. Seemingly simple queries run for minutes instead of milliseconds on a table ...
2
votes
2answers
194 views

On Duplicate Key Update - million times per day optimization?

I am using the following query with different variables millions of times per day, and I am wondering if there is any way to optimize it: INSERT INTO campaign_stats (user_id, date, $country) ...
0
votes
1answer
91 views

Track database name in anemometer queries

Hello i have installed anemometer on our server in order to see the queries that need optimization. From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is ...
1
vote
1answer
60 views

Is there any reasonable way to determine what executed a query?

I have an S3 SQL Server database in Azure, and I'm having an issue with a specific Entity Framework generated query and how often it is being run. As the numbers below show, I'm getting 160K+ ...
1
vote
1answer
33 views

T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER

I have a table with below structure : FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int) based on this table I wrote this query SELECT ...
0
votes
2answers
28 views

Update with rolling sum grouped by another column

I'm trying to build a query to update a MySQL 8.0 table that looks like this ----------------------- user_id | total | delta ----------------------- 1 | 0 | 30 1 | 0 | -10 ...
0
votes
1answer
135 views

Postgres 11 declarative partitioning by relative time

I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but ...
1
vote
1answer
30 views

How to query for address “predictions” while applying constraints

Let's say I have this simplified database scheme. The scheme consists of three tables, street, city and zipcode that all have a many-to-many relationship to each other. What I want to do is a ...
0
votes
1answer
26 views

How to implement complex search functionality?

Below is my table structure: CREATE TABLE `post` ( `postId` bigint(20) NOT NULL AUTO_INCREMENT, `userId` mediumint(8) unsigned NOT NULL, `areaId` smallint(5) unsigned DEFAULT NULL, `postTitle`...
1
vote
1answer
22 views

Sybase query: How can I sort the extraction (by well formed columns)

community I built a Sybase query statement in a script (using the isql command/line ) below is the code: My script contains: isql -U databasename_dba -P password <<EOF! select ...