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.

2
votes
0answers
31 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
58 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 ...
0
votes
0answers
17 views

MariaDB: *.mad file causes full disc space [on hold]

I am using MariaDB 10.1 My hard disk has 200GB space but after a couple of minutes a file xxx.MAD causes full disc space. I don't know how these files were created. How can I solve this? Why causes ...
0
votes
0answers
16 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
28 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 ...
2
votes
1answer
264 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
37 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
101 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
42 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
27 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
31 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` ...
0
votes
0answers
34 views

Why two select statements are causing deadlock? S and IX locks

<deadlock> <victim-list> <victimProcess id="process516293848" /> </victim-list> <process-list> <process id="process516293848" taskpriority="0" logused="17436" ...
0
votes
1answer
21 views

Interpret DBT2 results

I'm testing experimental storage (so I expect the numbers to be bad / far off). I ran the test on the experimental storage and got this: Response Time (s) Transaction %...
0
votes
0answers
44 views

Availability group - HADR_SYNC_COMMIT - slow synchronous commit [closed]

We see intermittent slow transactions in our production database coupled with high HADR_SYNC_COMMIT waits. The database is part of a four replica AG with one remote synchronous-commit replica and two ...
0
votes
1answer
61 views

SQL Server 2016 - High page writes, but normal/low everything else

I am a sysadmin and I am not very well versed in SQL Server metrics. I have been having issues with one of our DB's and the DBA doesn't seem too knowledgeable about measuring performance, so I'm ...
0
votes
1answer
35 views

Does write stalls on separated MDF file affect performance

My user database MDF file resides on separated disk. There is nothing else on the disk than this MDF file. I can see average write stalls higher then 20ms on the file using appropriate DMV. I assume ...
1
vote
0answers
39 views

Validation within a transaction with all violations in response

I'm trying to solve a problem with some sort of offline integration where data occasionally (once in several days) comes in a form of csv files. It can be new rows or updates for existing ones. Total ...
0
votes
0answers
19 views

Multiple Ad-Hoc Statements taking longer than previous days [duplicate]

We have a Data Warehouse product which runs a number of ad-hoc (IE SQL Text, not stored procs or dynamic SQL) every night. The build took significantly longer last night than previous nights and ...
2
votes
1answer
26 views

High number of Checkpoint pages/sec and memory pressure

Recently I read a blog post on mssqltips.com about memory bottlenecks on SQL Server. In this article I read following: The following performance counters on SQL Server: Buffer Manager object can ...
0
votes
0answers
47 views

Consolidating SQL Server databases into a New Server vs. Existing Server

Currently I am working on a project that requires me to consolidate 3 servers into 1 server, so I am trying to find the best method at the moment. Right now my options are: Consolidate the 3 servers ...
0
votes
1answer
23 views

Conditional insert vs UNIQUE constraint

https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already I would like to check if something exists before inserting it, what should I rely on? UNIQUE constraint or ...
0
votes
1answer
38 views

Are these stats reasonable? If not, where should I start?

The below is the output result of mysqladmin for our mysql server which has been having consistent problems for months since we switched from a dedicated server to the cloud. In the switch, we ...
-2
votes
2answers
45 views

First query waits for loading the whole table in memory

I'm testing SQL Server 2017 performance. I've create a table like the following one: CREATE TABLE [dbo].[AssessmentResponses]( [ResponseID] [int] IDENTITY(1,1) NOT NULL, [AssessmentID] [int] ...
0
votes
0answers
13 views

MySQL query is slow - Need to understand EXPLAIN output

Dear Database Administrators community, I have a query which I would like to find out what causes it to be slow and what can I do to improve it. This is the query I am using: select ereturn1_....
0
votes
0answers
15 views

Ιndexed view after replication

I am trying to find a smart (no redundunt dataflow) way to aggregate data on views/tables to be used from our reporting tools. One given is that the reporting database is different from the oltp one, ...
0
votes
0answers
15 views

Weird results when querying PostgreSQL (lower performance when requesting a small string than requesting a larger string)

I’m developing an experiment where I want to analyze the impact of the database in a web application. The experiment consists of searching for 1 string on the database according to an ID and returning ...
-3
votes
1answer
26 views

Sql Server benchmarking - Clarifications [closed]

We are in process of enabling both TDE and EFS on our DBs. In process of performance benchmarking to understand how sql server is behaving on current hardware pre and post , we are using perfmon or ...
3
votes
3answers
107 views

Automatically have Nonclustered indexes in a Different Filegroup

Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/...
2
votes
1answer
29 views

How to modify values on INSERT based on referenced table, even if foreign key doesn't exist yet?

Summary I want to add rows to a (projected) very huge table, but switch 2 values for references in other tables. The catch is that the referenced tables should be updated automatically when new ...
0
votes
1answer
27 views

$X Tables Do Not Exist?

In troubleshooting why a database application is working infinitely better on test than production, I ran across this phenomenon: I cannot query the X$ tables in production. SQL> desc x$ksppi ...
5
votes
1answer
95 views

Columnstore index for Key/Value Pair table on SQL Server

I need to build a database for storing assessments. Each assessment can have an indefinite number of questions, and an indefinite number of responses. Responses for each assessment can grow up to ...
0
votes
0answers
11 views

Why influxDB is good to save visits of a site?

I'm implementation a database for a site, and i make the table visits(For analytics purpose), whenever a user visits a page, will be a INSERT in my database. A friend my tell me it's no a problem, ...
3
votes
1answer
94 views

How to benchmark disk performance from within SQL Server?

I know of tools such as SQLIO and diskspd to benchmark IO/disk performance, but need something that can be run from the SQL Server instance itself via T-SQL, as I'm working on a Cloud-hosted ...
1
vote
1answer
31 views

Should I use a RDBMS JSON field or a NoSQL database to store large JSON datasets that I don't need to query?

I am building a website that allows users to retrieve a bunch of open source chart (eg UN data). I don't need to be able to query the data itself. I will instead extract certain meta data (keywords, ...
-1
votes
1answer
46 views

How do columns with excessive NULL values hurt database performance?

I have a legacy system that store comments in the database. I created a new system to replace the legacy system, that also stores comments in the database. For reasons that it would take too long to ...
0
votes
1answer
60 views

Raid 1 or Raid 1+0 for SQL Server Architecture

We are looking at new servers. Today our SQL Servers are built as follows: C/D on RAID 1 , E : Tempdb on RAID 1, F:log files on RAID 1 , G : Catalog data on RAID 1 , H: model data on RAID 1 , ...
1
vote
1answer
55 views

Performance Issue after Truncate and Load of a table in Oracle [closed]

One of our clients has migrated from old to new (larger size/resources) servers. A process (X) is taking 4 hours extra whenever we are doing a truncate and full load of a specific table. If we do not ...
0
votes
2answers
58 views

First names database design

I have a large amount of data on users (about 8 million users). I want to mine this data to create a table with statistics about first names. It should also include as many variations of each name as ...
0
votes
1answer
30 views

performance issue on using ip2location for group of ips

In admin panel I want to show counting users by country. I have in database these tables CREATE TABLE `cities` ( `id` int(11) UNSIGNED NOT NULL, `country_id` int(11) UNSIGNED NOT NULL, `name` ...
2
votes
1answer
38 views

Worker threads with multiple instances

I'm trying to understand the whole subject of worker threads for multiple instances, there's lots of advice on configuring workers but nothing I can find which directly addresses when the server has ...
-1
votes
1answer
46 views

What are the advantages and disadvantages of using a embedded sql database? [closed]

What are the advantages and disadvantages of using an embedded SQL database instead of MySQL server? I am a web developer and I'm planning to develop an application for the shop, without investing my ...
0
votes
1answer
61 views

Very high (99%) insertion cost in estimated execution plan

I have four tables with 500,000 to 4,000,000 rows. The script joins them and insert into a temporary table with some where clause. It is taking long. The execution plan shows 99% cost only for the ...
0
votes
3answers
51 views

mysql simple update with 40 million and 128GB RAM taking too much time

We are having trouble with simple updates on a single table taking a long time. The table contains ~40 Million rows. and the job runs every day that truncates the table and inserts new data from ...
1
vote
2answers
68 views

Poor SQL Server Performance With Nested “OR” Conditions In LEFT OUTER JOIN

I have a tricky bit of SQL that's giving me a bit of trouble. I'd like to introduce several new columns to a key table whose components may or may not be used based on user configuration (Dimension's ...
0
votes
0answers
33 views

RAC DB Slowness - AWR Report

While going through the AWR report found that the row lock waits on indexes and buffer busy waits are more (rest of the stats look normal). Could someone tell me if we can interpret by the below if ...
0
votes
0answers
33 views

What does 'innodb_buffer_pool_reads' and 'innodb_buffer_pool_read_requests' actually mean?

Mysql version: 5.7.18 (Innodb) I have a table with the following specification Structure: CREATE TABLE `dummybigint10` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT 'A ...
-1
votes
1answer
27 views

Performance: storing list of thousands of items in postgresql's JSON field vs having separate table for items?

Let's say I have some table A which have json-field items: A: foo: 'foo' items: [ {a: 1, b:2}, {a: 3, b:4}, ... ] There could be couple of thousands of items in one A object. Is there any ...
0
votes
0answers
15 views

Still getting values for performance_schema.global_status after setting 'Performance_Schema' to OFF

In mysql version 5.7, I have turned off performance schema (performance_schema=OFF in /etc/my.cnf), but still getting values incremented for variables in 'performance_schema.global_status' table. Why ...
1
vote
0answers
77 views

Creating index on a big text column in postgres

We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE. The column is of type text and it is used ...
0
votes
0answers
16 views

Postgres - Storing Attributes in 1:0 related object, performance improvements?

In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated ...