A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [performance]

An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.

Filter by
Sorted by
Tagged with
0
votes
0answers
38 views

Improve query performance: add indexes

I have a query that performs unacceptable. It runs more than 60'000 times within a business critical operation and I have problems tuning it. Will greatly appreciate help. Please, if possible, ...
0
votes
0answers
19 views

Why doesn't MySQL use indexes for subqueries?

This query takes forever to run (30+m). select date, sc, ( select count(fingerprint_id) from stats where hit_date >= t.date and hit_date < ...
1
vote
1answer
56 views

Why only few of expected tasks are processed although many cores are available?

I push some data from one database to another on the same instance using Service Broker. 20 cores are online, 6 Service Broker tasks are activated, every taks is working on indepentent piece of data. ...
0
votes
0answers
11 views

MongoDB Embedded vs References Performance [closed]

MongoDB supports two different type of relationships (not really relationships!) Embedded and References The 1st way is not good for my situation for some reasons. but i want to know about references....
-3
votes
0answers
47 views

SQL Server 2016 sp_blitzfirst results - anything sticks out as concern for performance issues?

Performance issues random, not able to pin point issues using my normal baselines. Maybe I am overlooking something in the results in the screenshots.
1
vote
1answer
35 views

Is it better to have a CASE expression or a JOIN with a dimensional table?

I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this: SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday' WHEN date_part(...
0
votes
0answers
11 views

Poor performance between connection c# desktop app and cloud mysql database [closed]

I have a c# app desktop app that works with a mysql database, with net framework 4.6.1, using net connector 8.0. On my developer environment i use mysql 5.7.11, and on my cloud mysql server i use the ...
1
vote
1answer
26 views

Slow bitmap heap scan on group/count query in a large table

I run an app with a database of PostgreSQL 10.10 I am optimizing a bunch of queries in our application, with great results so far, but there is one specific query that takes about 1 minute if not ...
1
vote
2answers
40 views

Optimize INSERT performance with a UNIQUE constraint on md5(JSONB_COLUMN::text)

I have the following table and indices defined: CREATE TABLE IF NOT EXISTS data ( id serial, job_id bigint NOT NULL, payload jsonb NOT NULL, tags jsonb NULL ); CREATE UNIQUE INDEX IF ...
0
votes
1answer
46 views

SQL Server 2016 - unexpected privileged time bursts

MS SQL 2016 SP2 CU8 + Always On. 1 CLR module (occasional usage, no correlation with the issue) I'm looking for methods to find the reason for such behavior. DBCC FREEPROCCACHE solves. But after ...
0
votes
2answers
56 views

TINYINT vs Nullable BIT performance in MS SQL Server [closed]

We need three modes to display the status of users in the database! We can do it in two ways Nullable BIT: Null: Not checked 0: Not accepted 1: accepted TINYINT 0: Not checked 1: accepted 2: ...
2
votes
1answer
2k views

Query runs in parallel but it is showing as blocked by itself

I have a query which runs in parallel with a MAXDOP of 8. When i looked at sp_who2 i see that the same session ID is being repeated several times(>8) with different connection id. I used the below ...
0
votes
0answers
28 views

optimize query with fulltext index in mysql

I have a column inside my mysql table that has a fulltext index and I need to make a query in which I get all the rows that do not have a specific word or those that start with those letters, so I ...
0
votes
0answers
24 views

pg_restore idling during second half of restore

We're migrating from PostgreSQL hosted on our VM to Azure Database for PostgreSQL managed database. One of the first problems we hit has been time to restore database dump. It took 20-30 min on our VM ...
0
votes
1answer
36 views

Improve CASE select performance on view creation

I am new to SQL, and the below view query is taking a long time to return values because of the case statement, can someone help in identifying how I can speed things up? CREATE OR REPLACE FORCE VIEW ...
0
votes
1answer
29 views

Performance impact of a group by function call

I have the following query, which for simplicity sake I use the simple example format below: SELECT col1, col2, dbo.fun3(col3) GROUP BY col1, col2, dbo.fun3(col3) This took 30 seconds to run and ...
0
votes
1answer
34 views

Can I modify a column's data type against a live table in Postgres?

I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields. I want to change these columns back to varchar. Can I do this against a live ...
0
votes
0answers
35 views

Is incrementing/decrementing a column from a large set of rows bad practice?

I'm designing a queue system and trying to decide between decrementing a column of a large set of rows or giving the row a priority and calculating the queue position with this. That said, I'm ...
1
vote
2answers
95 views

How to compare write speed before and after adding index?

I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on ...
1
vote
1answer
35 views

Does “alter table … set data type” functionally differ from “alter table … type”?

The docs show [SET DATA] TYPE which implies the SET DATA part is optional, but the 9.1 release notes also imply that SET DATA TYPE may be different: Allow ALTER TABLE ... SET DATA TYPE to avoid ...
0
votes
1answer
18 views

Cassandra query response time degradation

I am experimenting with Cassandra on 4 sites with 2 nodes on each one. Have small network latency between them. I always query the data in DC where I inserted it. I see very strange behavior: when I ...
-2
votes
1answer
43 views

Does SQL Server 2019 honor OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' )) [closed]

We had several pieces of code that when the 2014 Cardinality Optimizer came out performed very poorly. By adding this oip
1
vote
1answer
41 views

Postgres: How can I force index usage for view with latest rates?

We import currency rates into DB: CREATE TABLE currency_rate ( id int8 NOT NULL, date date NOT NULL, currency varchar(3) NOT NULL, rate numeric(12,6) NOT NULL, CONSTRAINT ...
0
votes
1answer
15 views

How to perform query operation on another query result

How to get approved , unapproved , deleted count from the below result using sql query. SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher` WHERE company_id = 1 GROUP BY approved,...
0
votes
0answers
46 views

SQL query tune to replace UNION - Oracle

SELECT FN_ENQDATA_STRING (col1) FROM tab1 WHERE tab1.col2 = tab2.col2 AND tab1.col3 = ? I also have tab2 which has same columns as tab1 and i need to call function for that too. What I can do is ( ...
0
votes
1answer
27 views

which command to execute to run multiple OLAP queries?

I have 10 sql files that contains different OLAP queries. I want to run them as a batch. Meaning by batch, Postgres will consider each file as a batch and execute the queries at once (depends on its ...
0
votes
1answer
26 views

What to expect from a benchmark?

I'm using pgbench to test a cloud SAN (similar to EBS). The problem is, I don't really know what to expect given any system configuration. Meaning, that even before I start tuning PostgreSQL ...
0
votes
1answer
63 views

Very slow query despite index being used

I have this table, created to hold tons of modbus measurements that comes from my devices: CREATE TABLE public.tv_smartdevicemeasurement_modbus ( measurement_id serial NOT NULL, insert_time ...
0
votes
0answers
50 views

Exporting SQL query very slow in Oracle SQL Developer

I have written a query and I'm able to run it successfully. When I try to export it, its exporting speed is very slow i.e. 50 rows per 10-15 mins. I managed to export it around 50k data in 10 hours....
0
votes
1answer
33 views

Table with huge data takes long time to query

I have a table in Postgres that is having over 50million records in production. It’s a table that I have to query it often for debugging and data extraction because it is heavily used by the ...
0
votes
1answer
30 views

in SQL databases does an update rewrite all columns in the row or only those in the SET tuple?

i was wondering how a sql update works in two scenarios: table with 3 columns: a, b, c say the data value for a has to be changed. 1: update on a row setting all columns a, b, c (where b and c have ...
5
votes
0answers
69 views

What are the downsides of running DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') hourly?

I have been seeing some concerning behavior in the SQL Server memory caches on one of our production servers. We are on SQL Server 2014 SP2 Enterprise Edition, 16 cores, 400GB RAM (300 GB dedicated to ...
-1
votes
2answers
45 views

MySQL Select Query not using Required Index

I have a Table which has 2 Million Records and No primary or Unique Keys. I added Two Indexes and I'm expecting an Index to be used for my Select Query. When I saw the Explain Plan , Optimizer is not ...
0
votes
0answers
68 views

“Change user” command slow query

We have a back-end server using ASP Core 3.0, and we are using pomelo library to make a connection to our MySQL database. Every now and then the server starts to fire a LOT of SQL exceptions saying ...
2
votes
1answer
29 views

Why EXPLAIN doesn't show heap fetches for index scan

I tried to compare potential perfomance difference between covering b-tree index and simple b-tree index and was confused with EXPLAIN(ANALYZE,BUFFERS) output. Test environment -- function to fill ...
0
votes
1answer
36 views

Improving performance of mapping standardized values with non equi joins - LIKE operator

I have a large-ish data warehouse staging table (~2.5 million rows) that is using a mapping table to standardize a text column. I have an update statement that works perfectly, but has terrible ...
-1
votes
1answer
46 views

Can a query that executes quickly still be a poorly written query? [closed]

This is more of a theoretical question and here is some background. Our system uses MySQL and was experiencing some slowdowns late in the day. Running SHOW FULL PROCESSLIST showed me around 10 rows ...
0
votes
1answer
54 views

Slow SQL Queries

I'm having problems with my application performance. The tables in the database have the following records: "issues" has more than 334823 "custom_values​​" has over 1350243 "users" has 1350 "projects"...
0
votes
1answer
35 views

Profiler or MDW?

Our application support people have stated that something is failing in one of the applications presumably due to some unusual activity/load in the database during specific hours of the day - ...
0
votes
1answer
61 views

Trying to find out what is consuming all the CPU

SQL Server Specs : SQL Server 2016 Standard 128 GB RAM for the SQL Server dedicated server. 108 GB allocated to SQL Server 40 logical CPU All SQL Queries run in parallel mode and queries are pretty ...
2
votes
1answer
122 views

Mysql GROUP BY is really slow on a simple view

I have these tables. CREATE TABLE `movements` ( `movementId` mediumint(8) UNSIGNED NOT NULL, `movementType` tinyint(3) UNSIGNED NOT NULL, `deleted` tinyint(1) UNSIGNED NOT NULL ) ENGINE=InnoDB ...
0
votes
1answer
25 views

Postgres EXPLAIN ANALYZE is super slow

there is Postgres 9.6 on Redhat I want to EXPLAIN ANALYZE slow query (quite complex one) and it takes too much time. My session with EXPLAIN ANALYZE consume 100% of one CPU and doesn't finish even ...
0
votes
1answer
74 views

MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin

I am a developer so was hoping someone with DBA experience could help me out here. I am using a MySQL database (MYSQL 5.6). I came across a situation where a particular column stores Ids generated ...
1
vote
0answers
69 views

Database - limit of reading a table with a lot of concurrent batch read and writes

We have this table. It contains data which will be picked up by an hourly batch job for processing. Five times a day, there are also multiple batch ETL jobs which get data from other source table and ...
1
vote
1answer
100 views

How to optimize this Select MAX() SQL query?

In the following select query, it take almost 1 min to return the result, seems the table has over 2 millions record and growing. There are no index i can use from the table. How do I optimize this ...
2
votes
1answer
56 views

Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

I'm getting the following error when I try to use the FORCESEEK hint with the query below: Msg 8622, Level 16, State 1, Line 96 Query processor could not produce a query plan because of the hints ...
4
votes
1answer
319 views

Optimize a slow nested loops join

I have this query running around 15 seconds on my system. The query plan is here. I believe the main problem is around here, where the optimizer chooses nested loops and is mistaken with cardinality: ...
1
vote
2answers
27 views

Postgres doesn't use expression index for group by

I'm doing a simple group by on a single column with a matching index and it works fine: create index on contacts (home_city); select home_city, count(*) from contacts group by home_city limit 50; ...
1
vote
2answers
40 views

Alternative to slow [LEFT JOIN + OR] without UNION?

I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables. The query using only left joins ...
1
vote
2answers
55 views

Postgresql query is very slow for my big table

My database version is postgresql 9.5. create table if not exists request_log ( id bigserial not null constraint app_requests_pkey primary key, request_date timestamp not ...