Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
51 views

What are some consequences of putting quotes in a WHERE clause for an integer column?

I have a table with a column some_column of data type integer. I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same: select * from my_table ...
1
vote
2answers
27 views

How to determine if it is necessary to increase the sga and the pga in Oracle database

How to determine if it is necessary to increase the sga and the pga in a database oracle 12c, is there any procedure, any specific view where can I be guided that indicates to me that it is necessary ...
-2
votes
0answers
10 views

Setting up Performance Monitor for several VMs [closed]

Does anyone have a script to complete automation of collecting Performance counters for several VMs to be stopped and started on a daily basis.
-3
votes
0answers
23 views

How to configure settings to release SQL server allocated RAM memory when it is unnecessary [duplicate]

I hosted application and database in a same server. And i have one of large execution process by job in mid night. After that server RAM memory fully allocated to SQL server and not going down. And ...
0
votes
0answers
26 views

MySQL: High CPU/Load after linux OOM killer hit

We have a single VM running MySQL 5.7.20 with 64GB memory and 4 vCPUs (Xeon E5-2690 v3 @ 2.6GHz). This MySQL node is dedicated to our Atlassian Confluence application so it's not having any other apps ...
2
votes
1answer
24 views

SQL Azure “hot” table query slowdown over time

I have an Azure SQL Database table that is acting as a "prioritized queue". As such, it gets heavily written to and deleted from over time. What's happening is that after a week or two, the DB size ...
0
votes
0answers
28 views

Does keyset/cursor pagination scale query performance better than offset pagination?

I am seeing behavior where the query that powers my infinite scrolling list seems to be slowing down as the number of rows grow. Naturally, I used offset pagination because the library I am using ...
8
votes
1answer
252 views

What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?

So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate ...
7
votes
3answers
219 views

Long Running Query on Read-Only Replica that takes moments on the Primary

I've got a 4 node AG setup as follows: VM Hardware Configuration of all nodes: Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710) 16 vCPUs 356 GB RAM (long story to this one...) max ...
-2
votes
1answer
16 views

Slow performance - join dbo.StringList Sql Server [duplicate]

I need to update table from a certain population. The code: @IdentityNumbers dbo.StringList readonly update d set DeliveryNationalInsuranceBatchLogId = @BatchLogId, LastUpdateDate=getdate(...
0
votes
0answers
32 views

SQL Server query extremely slow. It fixes itself after rebooting [duplicate]

I have an SQL Server instance running on AWS RDS that is performing badly. A query that usually takes milliseconds to run, is taking minutes, causing my application to time out. This particular query ...
11
votes
2answers
471 views

Displaying an Estimated Execution Plan generates CXPACKET, PAGELATCH_SH, and LATCH_EX [ACCESS_METHODS_DATASET_PARENT] waits

I'm running Microsoft SQL Server 2016 SP2-CU6 (13.0.5292.0) on a 4 vCPU VM with max degree of parallelism set to 2 and cost threshold for parallelism set to 50. In the mornings, when trying to ...
3
votes
1answer
25 views

Can I apply production statistics to an empty local database to test PostgreSQL planner hypotheses?

I'm toying with the idea of exporting a database's statistics from production and applying them to a test database (same structure, small dataset). The goal is to test software for changes that ...
0
votes
0answers
34 views

Slow insert (trigger/indexes) processing

Whenever an insert is done in a table these tasks are executed: One Trigger (see down). Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have ...
2
votes
0answers
37 views

the cost decreased but the time was practically the same, why?

Table definition: CREATE TABLE schema.mylogoperation ( id_mylogoperation serial, data DATE, myschema VARCHAR(255), column_var_2 VARCHAR(255), user VARCHAR(255), action TEXT, ...
0
votes
1answer
14 views

mongodb read write locks

I'm currently investigating the possibility of using mongodb. This would contain the a collection of 100million+ documents. I need a process that will be constantly running deleting documents older ...
0
votes
2answers
48 views

MySQL Function work very slow

I have writed this mysql function, it's worked perfekt but very slowly, what can I do for better performance, have you any idea? note:i need all created virtual columns DROP FUNCTION IF EXISTS ...
0
votes
0answers
39 views

SQL Server query auditing in production without drastically affecting performance

Has anyone successfully turned on SQL Server auditing (extended events) to keep track of queries being run in a production environment while having the database still perform well? If you did, I'm ...
-3
votes
1answer
63 views

SQL Performance Issues [closed]

I just recently configured a brand-new Windows SBS 2008 Server with SQL 2008. The box is a HP Proliant DL 380 G5 with 32GB RAM and a 250GB SSD. I have Access97 as a front-end and using ODBC I ...
0
votes
2answers
36 views

What is going on with my user and max date query?

OK query below. Hitting about a half million rows and I am trying to grab the last time a user (uid) touched a page on our system denoted by LogDate. I have used this query quite a few times but ...
2
votes
2answers
58 views

updating statistics using STATS_STREAM or WITH FULLSCAN?

the syntax for creating statistics is: -- Syntax for SQL Server and Azure SQL Database UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name ...
0
votes
1answer
51 views

Performance Issues on MSSQL Server Web Edition [closed]

I am facing some performance issues on my MSSQL Server which is a Web Edition. My database size is around 3 TB and there are continuous inserts that happen through various processes. We also have ...
-3
votes
1answer
32 views

Definition of extended event session [closed]

Is there any complex EE session that can be used to collect performance data on SQL server on daily basis (Queries and their plans , waitings , blockings ,lockings ) , I want to create such session ...
2
votes
1answer
79 views

Improving performance of count where only result > 0 matters

I have following query: select count(*) -- rp.tag from submission inner join post rp on submission.id = rp.submission_id where user_name = ?1 and lower(rp.tag) like ('%verified%') and not lower(rp....
0
votes
0answers
45 views

TimescaleDB performance

I am trying to optimize some queries on a huge table but I can't manage to make then faster. I was wondering is it possible to make timescaleDB run any faster? Test results: tracker=> select count(*)...
2
votes
1answer
45 views

How can you have a Buffer cache hit ratio of 100% with PLE of 103? [duplicate]

I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE ...
2
votes
1answer
48 views

Accurately simulate SQL Server work load with diskspd

We were building a new node in an alwayson availibility group and this new server was on a different storage than the other nodes in the cluster. Since it was a separate storage we wanted to make ...
0
votes
0answers
15 views

Converting SalesForce IDs for Performance Reasons

I'm writing code for a data warehouse that extracts information from SalesForce. The format of the SalesForce IDs is like so: 001n000000UELLJAA5 Where each digit is a base-62 value denoted by: a-z ...
-3
votes
1answer
44 views

Fastest Aggregate function when you have series of duplicate values

Frequently, I'll need to crunch down a table into a series of aggregates. And frequently, it will look like this. John, Smith, Houston, Texas, Order 55, Balloon, 99 dollars John, Smith, Houston, ...
0
votes
0answers
43 views

postgres: join tables, order by GREATEST

Having the following table structure table1 id: BIGSERIAL updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now() ... CREATE INDEX table1_updated_at ON table2(updated_at); table2 table1_id: ...
0
votes
2answers
65 views

MySQL performance tuning related connection

I am running openstack cloud and it use mysql database which is MariaDB 10.1.30 I am sometime seeing connection reset from multiple clients, to trying to find out what is going on in database, I am ...
0
votes
0answers
32 views

What is the best compaction strategy for overwritable/mutable/upsert-able data?

Suppose I have a Cassandra table like this: CREATE TABLE keyspace.last_modified ( category text, subcategory text, id text, created_at bigint, PRIMARY KEY ((category, subcategory, ...
2
votes
0answers
125 views

SQL Server buffer cache smaller after reboot with same workload [closed]

We are hosting a SQL Server 2014 SP2 CU16 in a Windows Server 2016 64-bit environment for a customer. Performance has been fine for a long while. On March 31 at midnight, the database server ...
1
vote
2answers
145 views

Very slow simple INSERT in transaction

I have I problematic query on my SQL Server where I could not find the problem. I have two simple tables, one for the "Headers" and one for the "Items" (each with an PK column, total of five fields ...
0
votes
1answer
50 views

Overhead of triggers and foreign key constraints?

I have a sqlite database which is accessed by two programs. There are a number of invariants/constraints which should always hold. In addition to the usual foreign key constrains between tables, ...
1
vote
1answer
52 views

32-bit Enterprise SQL 2005 on 64 bit Windows 2008 Server Standard. Memory configuration

Working on a project we need to first stabilize and then upgrade. We have a 32bit Enterprise edition SQL Server 2005 installed on a 64 bit Windows 2008 standard Server. The server has 64GB RAM ...
2
votes
1answer
63 views

Auto generated statistics by query optimiser

Can we consider removing the auto generated statistics if the update statistic within a given database is taking too long? We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but often ...
1
vote
0answers
40 views

Background index creation resource provisioning

I have a long running background index creation running in MongoDB. However, the CPU and RAM usage is below 30%. Is there a way to allocate more resources to the index creation process or is it ...
5
votes
2answers
374 views

Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries?

Consider this table: create table Books ( Id bigint not null primary key identity(1, 1), UniqueToken varchar(100) not null, [Text] nvarchar(max) not null ) Let's imagine that we have ...
0
votes
1answer
48 views

Perfomances, slow load of query with multiples left join

I'm struggling a lot with a big doctrine query, there is 17000 contacts in the database and near 1 millions rows for statistics and orders. As you can see there is a lot of calculated values, i ...
2
votes
0answers
75 views

What's the more performant database design for query speed and write overhead when storing many boolean columns?

Setting the scene I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. ...
1
vote
1answer
80 views

Optimize queries that need to be 100% ACID compliant but can tolerate latency in persistence

Introduction I'm writing a trading program in C++. The program involves having user balances and orderbooks. The orderbook is basically a set of buy and sell orders. When submitting an order, say a ...
1
vote
1answer
106 views

mysql 8.0, updates are sometimes stalling

Recently I moved one of my production servers from MySQL 5.7 to 8.0. Immediately after that I noticed that update statements are sometimes (not always) taking more time (enormously) and tend to stack ...
2
votes
1answer
60 views

Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan

Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan -- create objects CREATE DATABASE ConvertTest GO USE ConvertTest GO CREATE ...
3
votes
1answer
273 views

How to determine the cost of index creation?

I have a table that I want to create an index on to improve the performance of a couple of SELECT queries that use the table. How can I test whether the index has had any significant detrimental ...
1
vote
0answers
231 views

What is the best option for a large slowly changing dimension lookup table

This question is an extension of a previously asked question Index Strategy and Query optimization, which further extends this question: Search between IP string addresses I implemented the two new ...
3
votes
1answer
124 views

What is the best index strategy or query SELECT when performing a search/lookup BETWEEN IP address (IPv4 and IPv6) ranges?

Question: Is there a better indexing strategy or query SELECT that I can use for looking up one large data set against another large data set? Or, should I look at placing the lookup dimension table ...
3
votes
0answers
69 views

how does CXPACKETS relates to CXCONSUMER wait types?

when running a delete query to a table that has many foreign keys I get the following waits. I noticed that I have both CXPACKETS and CXCONSUMER wait types at the same time during of the execution ...
2
votes
1answer
49 views

How to query all entities associated with all children (recursive) of a node in SQL?

This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary. Ok, I’m having trouble putting this into words… so bear with me. Say I have a tree of ...
0
votes
1answer
101 views

CASE in WHERE clause in MySQL

I'm working in a project with MySQL and using stored procedures to make my SELECT queries, so all of them have this same structure: DELIMITER $$ CREATE PROCEDURE `case_in_where`(IN `column_selector` ...