We’re rewarding the question askers & reputations are being recalculated! Read more.

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
1
vote
1answer
36 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
29 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
157 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
31 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
33 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
26 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 ...
4
votes
2answers
110 views

How to optimize IN query on indexed column

I have a table with over 50M records. One of the fields is COLOR_CODE. I've set an index on the column COLOR_CODE like this: "mytable_colorcode_idx" btree (color_code) I'm noticing that when I run ...
3
votes
2answers
152 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
1
vote
1answer
192 views

What causes temporary slow (fulltext) queries?

I logged this slow query on a 400k rows MySQL 5.6 InnoDB table: SELECT SQL_NO_CACHE topic_id FROM topics_search WHERE host_id = 1 AND forum_id IN (23, 50) AND MATCH(topic_text)...
1
vote
1answer
38 views

selecting data after filtering out

I am using RDBMS MySQL 5.7. I have 3 tables with the following structures: Table A - primary table - products: +----+-----------+-------------+------------+--------+ | id | title | description |...
0
votes
1answer
47 views

slow count queries - what are we doing wrong?

Using Postgresql 9.6.14, running the following query: SELECT COUNT(*) AS "__count" FROM "crm_client" INNER JOIN "crm_role" ON ("crm_client"."role_ptr_id" = "crm_role"."id") INNER ...
0
votes
2answers
114 views

This simple mysql query is taking 2-3 seconds

I'm using sqlalchemy and trying to do a query on an m2m table. I have a tag and want find all the events that match that tag: SELECT * FROM events WHERE EXISTS ( SELECT 1 FROM events_tags, tags ...
-2
votes
1answer
47 views

Performance using a jsonb column in PostgreSQL? [closed]

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
0
votes
0answers
33 views

Performance Problems with MongoDB Atlas M0 with Larger Documents

Problem Description Our team is developing a small web application for an academic research project, which is currently hosted on Heroku and connects to a MongoDB Atlas M0 (Free Tier) database. The ...
0
votes
0answers
64 views

Reproduce Deadlock in SQL Server

I have identified a deadlock by using Solarwinds, I have noticed that it is happening in a specific table, that table only has a primary key, which tells me that the deadlock makes sense since every ...
0
votes
1answer
107 views

how to minimize the data scanned on my database using index?

Is there anyone how to minimize the data scanned on my database?? please show me.. using index? query SELECT IF (e.channel LIKE 'IAX2%', e.calldate, IF(d.channel LIKE '...
2
votes
2answers
152 views

Usage of NON Clustered Index in SQL Server

I have 2 Index: CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA] ( [Column1] ASC, [Column2] ASC ) CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA] ( [Column3] asc ) Now I have 2 ...
2
votes
1answer
91 views

Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit

We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
2
votes
1answer
96 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
0
votes
1answer
15 views

Slow Query not using index in one of the tables

My Rails table structure is like this: a User has_many Contacts, which have one Reservation each, which have many ReservationNotes each. I'm trying to get all the ReservationNotes for the contacts ...
1
vote
1answer
137 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
3
votes
2answers
108 views

Optmizing ORDER BY

I'm new to MySQL and have written the following query (see below). PROBLEM: The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes ...
1
vote
1answer
19 views

How to skip prefix column check in multi column index in MySQL?

Below is my demo table structure: CREATE TABLE `table1` ( `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent', `postLocation` smallint(6) NOT NULL, `postArea` smallint(6) ...
6
votes
0answers
94 views

Can oscillating number of fragment decrease FTS query performance?

I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly. Reading ...
1
vote
1answer
103 views

Slow join performance SQL Server

I am struggling a bit to improve performance on one of my queries. What I had is basically all of those 4 tables for reporting without any indexes, PKs, etc. I was thinking that if I copy them (...
0
votes
0answers
103 views

SQL Server Query Timeout with NOT IN clause but IN works fine

So were using SQL Server 2016 Enterprise and suddenly have an issue with a query on the Production server. Works fine on Test and Dev and if we backup and restore the production DB to either of ...
0
votes
0answers
24 views

Read performance of SQL Server data files data from Azure BLOB

I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here. The interesting ...
0
votes
1answer
18 views

three tables relation order is correct but values are false

i have those tables table1 | id | name | | 1 | axe | | 2 | bow | | 3 | car | | 4 | dart | table2 table3 | t1_id | ...
0
votes
1answer
29 views

Proc runinng slow once in a while [duplicate]

I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min. For past couple of weeks it happens once in ...
0
votes
1answer
137 views

Is it advisable to asynchronously update the summary table every time a transaction is deleted?

Given three tables: (a) Product (b) Transaction Head (c) Transaction Detail We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction ...
1
vote
1answer
117 views

Insert statement performance in SQL Server [duplicate]

I have the below Insert statement query as part of an stored procedure that is taking some time to be complete (20-30) minutes, when normally in another server with same hardware specifications takes ...
0
votes
0answers
36 views

Select query taking too much time in postgresql

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU): create table stat_data ( name varchar NOT NULL, the_date date NOT NULL, uopen numeric, uhigh ...
1
vote
1answer
28 views

Predict how much hard drive space a query might cost?

Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My ...
0
votes
0answers
19 views

How does the MongoDB $filter (aggregation) stage work internally?

I was asked a question by a colleague today whether the MongoDB $filter aggregation stage is implemented efficiently. The aggregation stage in question: $project: { absences: { $filter: {...
0
votes
0answers
23 views

How can i remove filesort for this subqueires?

This is the Query: SELECT A.* FROM messages_thread_user mtu, LATERAL (SELECT m.*, u.userName FROM messages m JOIN ...
1
vote
1answer
40 views

MySQL order of execution

I am running a very long query described below. It fetches the next needed action for each account on an automation system. I.e. SELECT Account.id, (IFNULL(**Should send message query**, ...
1
vote
1answer
29 views

Why does performing a query on a second environment, it takes forever to run, and scans hundreds of millions of rows?

I have a query, it is fairly big, but not THAT big. One the baseline environment, it runs in less than 1/3 of a second. When we moved the database to RDS in Amazon, it takes 10-15 minutes to run. I ...
2
votes
2answers
42 views

How to find exact string from a sentence

I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
0
votes
1answer
52 views

Why the difference in query time for these two queries?

I have a MySQL 5.7 database with a large table that has about 70,000,000 rows. There are 4 columns: Id, Time, Value, Monitor_Id. Id is the primary key, Time is indexed and Monitor_Id is a foreign key. ...
0
votes
1answer
59 views

Is IF ELSE better in this stored procedure than a Parameter? [duplicate]

I found a stored procedure in our sql server 2016 db Create PROCEDURE XXX @complicatedQuery bit AS BEGIN SELECT some fields FROM Table A join Table B on something JOIN Giant Table C ...
0
votes
1answer
53 views

Part of parallel execution plan has serial portion

I have a big linq to sql query which runs in parallel mode. It is returning results in 8 seconds and it is pretty good. I noticed in the execution plan that the extreme right part(from where execution ...
1
vote
1answer
406 views

Improve Performance on GROUP BY - large table PostgreSQL

I have a Postgres table containing the following three columns: origin (INT, b tree index) destination (INT, b tree index) minutes (SMALLINT, b tree index) The table has ~ 1.4 billion rows. Each row ...
0
votes
0answers
31 views

How can I obtain a list of our applications' SQL statements that do not make use of indexes?

We have a system that processes thousands of statements a second, all running quickly. With so many statements we are looking to fine tune with small improvements to have a knock on large improvement ...
1
vote
2answers
42 views

Why is this query sometimes slow, and what can I do to make it faster?

I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database. ...
3
votes
1answer
3k views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ,...
3
votes
2answers
4k views

Amazon Redshift - The difference between Query Slots, Concurrency and Queues?

We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and ...
1
vote
1answer
73 views

Why is parallelism not good in this query?

I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias: Finding Key Lookups inside the Plan Cache and from there I borrowed the ...
0
votes
1answer
48 views

`cube` query performance issues with ordering

I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the But ...
16
votes
3answers
4k views

Wrapping query in IF EXISTS makes it very slow

I have the below query : select databasename from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt' and not exists(select 1 from dbo.smalltable c where c.source=l.source) ...
0
votes
1answer
32 views

How to increase timing performace of a SQL query

I'm using postgresql 9.6 on the 16GB Ubuntu-PC with 4 cores & SSD with following params: max_connections = 200 shared_buffers = 6GB work_mem = 256MB ...