Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
1
vote
1answer
24 views

Can I tell mariadb which query plan to use for a given query?

I have a query which gets different query plans over time and one of them takes less then a minute to execute, the other one takes days. Can I tell the db to use a given plan (taken from explain or ...
0
votes
2answers
83 views

Why does a query becomes extremely slow independently from data amount?

I have a query running usually within few seconds/minutes which becomes extremely slow after a while (about a week) taking then days! to execute. The query just stays in 'Sending data' and CPU use is ...
1
vote
1answer
47 views

why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well?

I am running the following query on MS SQL Server 2014 (and also on 2014SP3) SET NOCOUNT ON CREATE TABLE #GUIDs( PartyNames_GUID UNIQUEIDENTIFIER NULL, Party_GUID UNIQUEIDENTIFIER NULL, ...
8
votes
1answer
182 views

Improve the poor performance of the sys.dm_db_stats_properties DMV

We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL ...
2
votes
1answer
26 views

How to fetch related rows on multiple condition with ranking

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | ----------------------------------------------------------...
0
votes
0answers
9 views

High cost on my query on partitioned table

I have a partitioned table as follows: create table daily_summary ( id uuid default uuid_generate_v4() not null constraint daily_summary_partition_pkey ...
0
votes
0answers
24 views

Which database for a web application with similar requirements like file sharing/storage services?

I am currently working on a web application that allows manufacturers to upload files (technical drawings of products), which they can then share with suppliers so that they can produce the ...
0
votes
1answer
49 views

Query performance discrepancy between Azure and on premise SQL Server

I have the same database on both SQL Azure and on premise. Most of my queries run in similar time on the two instances but I have a handful of queries where the performance difference is on the order ...
0
votes
1answer
67 views

Querying 500 million records in Oracle view takes forever

I really need professionals help now after wasting a week of time to solve my problem and searching on Stack Overflow. I have a view created on top of two different tables. This view has 500 million ...
0
votes
0answers
29 views

index isn't used, filter is too slow [duplicate]

I am analyzing the query which takes several minutes to execute and I want it to be a bit faster: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM filings AS filing WHERE (filing.sources_id != 10 OR filing....
0
votes
3answers
48 views

Do you think is necessary to create an auxiliary translate table for table that has only two elements (id and name)

I have a question. In my database I have an Allergen table that has two elements: Id and Name. I want that my Allergen table has translations, so what is better ? Create an auxiliary translate table ...
0
votes
1answer
50 views

Optimise XOR search on int list

I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int). Another large table (TableB) contains the same ID column with some other ...
3
votes
2answers
412 views

Replace a sequential set of numbers with special character

I have a varchar(200) column that contains entries such as, ABC123124_A12312 ABC123_A1212 ABC123124_B12312 AC123124_AD12312 A12312_123 etc.. I want to replace a sequence of numbers with a single * ...
0
votes
0answers
46 views

Quick question on Scalar UDF's

I am trying to browse online but can not get enough info on how to measure performance of scalar UDF's in SQL server 2014. Newer version yes i do see from plans we can get the same and newer dmv's ...
0
votes
2answers
153 views

Mysql optimize slow performing query with many to many table joins

I have the following query with joins to a number of many-to-many junction tables: profile_language profile_industry profile_contract_type profile_contract_hour profile_qualification It takes around ...
1
vote
0answers
63 views

How can I make this query faster without adding indices?

I have a read-only database, from which I have to report on all our Wuhan virus cases. The execution plan and query can be found here. I can't share the data, since it's health records, but this ...
0
votes
0answers
125 views

MongoDB Explain plan - elapsed time breakdown for executionStats

I have run an explain plan for a query, and am a bit confused with the timings of each stage, and how they aggregate to the total elapsed time. Following command was run to generate the statistics, ...
2
votes
3answers
42 views

Performance problem selecting large log table with one join

I've a Log-Tabelle with approximately 13 million rows. The table structure is (truncated) as follows: CREATE TABLE `dmsserver_status_log` ( `id` bigint(20) NOT NULL, `customer_id` int(...
1
vote
1answer
100 views

Why do update statements degrade when in a transaction?

I have a batch which updates a table based on its primary key SET NOCOUNT ON go IF OBJECT_ID('Tab1') IS NOT NULL BEGIN DROP TABLE Tab1 END go CREATE TABLE Tab1 ( Tab1_ID INT ...
0
votes
1answer
36 views

Best way to filter result set by secondary key

I have a table with a primary key and a secondary key, something like CREATE TABLE Entry ( PrimaryKey int NOT NULL, SecondaryKey int NOT NULL, Data nvarchar(100) NOT NULL, ...
16
votes
2answers
3k views

SQL estimates are way off on DELETE statement with Triggers on huge tables

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
0
votes
1answer
36 views

Ideas to improve query performance

SELECT SQL_CALC_FOUND_ROWS pn_posts.id FROM pn_posts LEFT JOIN pn_term_relationships ON ( pn_posts.id = pn_term_relationships.object_id ) INNER JOIN pn_postmeta ...
0
votes
0answers
31 views

Speed up query on large MySQL table

SELECT property, geo, stars FROM table WHERE SUBSTRING_INDEX(geo, '>', 3) LIKE '%$mylocation%' ...loop to show results geo column has values like - Spain>Canary Islands>Lanzarote>Some ...
0
votes
0answers
22 views

Designing a database for sorted SELECT query in the last X hours

I'm building a database (was thinking an RDS like mysql would be fine for this, but input here would be great here too) that has a table where the data from that table will be (I think) queried by ...
0
votes
1answer
57 views

Postgresql Lateral Left Join Query Performance Issues - Takes a Long Time

I am trying to run the query below on a database with hundreds of thousands of lines but it is taking over 12 hours so far. It is the lateral left join that is taking so long. The reason I need the ...
1
vote
3answers
23 views

mysql execution time , Somestimes fast,sometimes slow

The following SQL statement, execution time , Somestimes fast,sometimes slow. ※execut in Navicat Premium tab windows Information as below.Do you have any suggestions? SELECT device.xmbh AS ...
0
votes
1answer
27 views

MySQL: Use index in background query

I'm using MySQL 5.7.10 I'm checking a new query for an audit report. I'll execute it in a simple background Unix process, whick invoke mysql from the console. To check the query, I use a worksheet in ...
0
votes
3answers
68 views

Improve reading speed of MySQL table storing stock prices (100 Mil. entries)

I have a table which stores stock prices in a MySQL database with the following structure: CREATE TABLE IF NOT EXISTS `tbl_prices` ( `price_id` bigint(20) NOT NULL AUTO_INCREMENT, `stock_id` int(...
0
votes
0answers
42 views

KNN performance with conditions in SQL Server

I'm running K Nearest Neighbor queries on spatial point data in MSSQL. I'm using this answer as a template for my SQL: https://dba.stackexchange.com/a/141240/202189 My initial code looks like so: ...
1
vote
0answers
79 views

Postgres - slow timestamp query with <1 mln rows index

I have a table "ads" which has 300k rows and timestamptz field created_dttm. I also created an index CREATE INDEX idx_ads_created_dttm ON public.ads USING btree (created_dttm DESC) Postgres 11.5 ...
1
vote
2answers
113 views

Performance - Looking for Some Advice on Improving this Query

I've been looking into a performance issue for the last few days and although I've improved the query runtime from 48 seconds to 11 seconds, I feel that it can be improved even further. The issue is ...
0
votes
1answer
33 views

Imported database very slow on a simple query

A production database has been exported to a separate location for various reasons, however the dumped/imported database is performing much slower on some very specific queries. Here is 2 queries on ...
0
votes
0answers
31 views

How to optimize query? Taking too long on first load

I am facing an issue when opening the page, the loading time is around 20 seconds. I have two tables -------------------------------------------------------------------------------------- agreements ...
0
votes
0answers
38 views

Different execution plan - Delete TOP 10 vs Delete TOP 1000 [duplicate]

Was troubleshooting a delete statement that periodically delete old data from a monitoring table and noticed different behaviour with different TOP numbers and wanted to understand why. Delete ...
0
votes
0answers
47 views

Slow MySQL query speed

I have a problem with MySQL query performance. Please look at the details below. My question is if perhaps my schema could be simplified? This has to be done keeping generic device type support - ...
4
votes
4answers
274 views

Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR

I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database: SELECT * FROM Users u LEFT JOIN Comments c ON u.Id = c....
3
votes
0answers
178 views

PostgreSQL: filtering on array and ordering produces wrong plan / bad index choice

This is the story if a nightmare... I have this table: Table "subscriptions" Column | Type | Collation | Nullable | ...
0
votes
1answer
53 views

How significant is performance difference between “DATE(mydate) = CURDATE()” vs “mydate = CURDATE()”?

DATE(mydate) = CURDATE() vs mydate = CURDATE() I have a pretty performance sensitive situation with tens of millions of rows, and I'm wondering if storing only date in datetime field and time in a ...
3
votes
1answer
50 views

Query optimization issue on view with RANK on DateTime for most recent rows

I'm having a problem with a view, that is designed to store sync data, for an iOS app, that syncs data back and forth to a web service. The XML fields are used to store both structured data for ...
0
votes
1answer
18 views

What's the optimal way to query data using another table column as a between filter

sorry for the ambiguous question title. I don't know what's it called. The thing is let's say I've a order table storing uid , order_id and ip looks like this | uid | order_id | ip | 10000 | ...
1
vote
1answer
73 views

Query runs quicker when column calculation is done after initial query

I have always been led to believe that since SQL evals the SELECT after filtering that putting a calculation in the select of a query should be as fast or faster than running the query and then doing ...
6
votes
2answers
220 views

Optimize/Speedup query

Below query is used for inserting and updating the tables in the SQL Server database. The XQuery is slow while executing in SSMS for first time. Query insert new <ROW> Update BalanceTable set [...
0
votes
0answers
24 views

Read only database for time series event data

I have a time-series dataset (around 500 million rows, 4 columns) that stores public transport ticket validations. Not sure if it matters, but it's an event driven dataset, without constant timestamp ...
3
votes
3answers
46 views

Is there any performance issue with including the “COLLATE” as part of the query as opposed to setting it for the column/table?

PostgreSQL has this "collate" concept. You can either tell a table's column to always have a "collate", or you can do it in the query. If I do it in the query, does PG punish me somehow performance-...
0
votes
2answers
41 views

Inherit INNER JOIN fields in MySql SELECT QUERY

I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY. Here's what i got so far: In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC ...
0
votes
2answers
41 views

What happens at low level when I add a column to a table in 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 ...
1
vote
0answers
35 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
2answers
258 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 ...
2
votes
1answer
76 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 `...
1
vote
1answer
149 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 ...

1
3 4
5
6 7
48