Stack Exchange Network

Stack Exchange network consists of 174 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.

0
votes
0answers
10 views

Poor query planning in Postgres 10

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join. Here's my ...
0
votes
1answer
45 views

powerful VPS but slow MS SQL- why? [on hold]

VPS (Contabo) : 10 cores (Xeon E5 2.2GHz), 60 GB RAM, SSD, MS SQL Web Edition (configured to use 10 CPUs), Windows Server 2016 When running SQL takes ~32GB of RAM, When I execute SQL a stored ...
1
vote
0answers
40 views

User permission businness logic

This question will also be posted in the stackoverflow, since dba and programmers may have different opinions. First of all sorry for the long question, but I wrote some extra code so that I can ...
-3
votes
0answers
40 views

Determining reason for slow running SQL query [on hold]

How can I find the root cause for slower execution of a SQL query compared to yesterday's execution time? Whether it is: Network problem Hardware problem Waits problem Optimizer statistics problem ...
0
votes
1answer
20 views

What index strategy to use to improve multi-condition SELECT?

As far as I understand, indexes should be selected on fields with high cardinality of data - e.g. sex only has two values ( male and female ) so low cardinality, whereas something like sha256sum field ...
0
votes
1answer
40 views

Index Seek Plan turns to Index Scan after adding another Join condition [closed]

I had a query passing from Entity Framework which is a very expensive query with a terrible performance. while optimizing this query I found the problem in this part: SELECT TOP 20 * FROM [...
2
votes
3answers
30 views

Linking Waits to a process/statement

I'm starting to take on more of a DBA role and trying to get tooled up with scripts and knowledge to help with this. I have been trying the sp_blizt procs in a dev environment. A key area I'm trying ...
1
vote
1answer
39 views

Does an index improve the querying performance in just two ways? [closed]

As far as I know, an index improves the querying performance in the following two ways: The index only contains the values of the column that it was created for and pointers to the rows in the ...
2
votes
2answers
114 views

Clustered Sequential GUID Primary Key vs Non-Clustered GUID and Clustered Sequential ID Primary Keys

I'm trying to figure out what the optimal solution is in regards to size and performance when using GUIDs as a primary key. The main reason for wanting GUIDs is that it's more appropriate in our micro-...
1
vote
1answer
64 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 ...
1
vote
1answer
42 views

Mongodb as a data ware house?

Will mongodb a good choice as a data warehouse considering following situation? We are building analytics, report generation platform for our client and a typical table growth in terms of rows will ...
0
votes
0answers
20 views

Optimal way to store json

I would like to store JSON in a database. I know that most DBs offer some sort of indexing in case of querying based on a value in JSON but I will only rarely need this. What is the optimal way to ...
7
votes
1answer
78 views

PostgreSQL ANALYZE execution time over 24h (still running)

I upgraded a Postgres DB 9.3.2-->10.5 using pg_upgrade (in place). I did everything according to the documentation and the instructions given by pg_upgrade. Everything went fine but then I realized ...
0
votes
1answer
44 views

BETWEEN query on two indexed columns exponentially takes longer on minimal changes to LIMIT

This is a minimal example of my problem, but I reproduced the problem with this structure. I have the following table: | column name | type | |----------------+-------------| | id ...
1
vote
2answers
29 views

Mp3 file directly in a Database Without using BLOB [closed]

I need to store Mp3 files in a Database. Problem is that the files are going to be accesed with a smartphone. As the Smartphone is going to need to decode the Blob it would be very slow. I tried ...
0
votes
1answer
33 views

Loading 400 Million Records into MySQL Hangs on “Sending Data”

I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. ...
0
votes
0answers
28 views

LDF file size is very huge even after doing Transaction log backup [duplicate]

I work on a SQL Server 2016 Database and in our production environment we have primary SQL server and a fail over SQL server. All the transactions are occurring on the primary SQL server. In primary , ...
0
votes
0answers
26 views

Database for product catalog with multiple stores, hierarchical categories [closed]

I'm seeking for assistance on the decision of the best database, be that relational one or not, as well as the best schema for the given task. The idea is the following: there is an offline store ...
0
votes
1answer
42 views

REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

Is it necessary to REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it? I have a table with approximately 3.5 million rows in Oracle. Every week new data is ...
0
votes
1answer
33 views

Database schema tracking patient stats over time

I want to create a database which allows to record patient reading data points like: weight, height, pressure, etc., over time. I found a database schema like the one discussed in this Q & A ...
0
votes
2answers
25 views

MariaDB instance in cloud hit 95% CPU utilisation for 30 mins

I've got MariaDB instance in the cloud which I put live about 2 months ago. I'm new to the DBA business and I got the impression the people at the company who I run it through are learning on the hoof ...
1
vote
1answer
30 views

Disk block size for mongodb deployment

Why does Mongodb production notes have no recommendations about the disk block size? AFAIU, disk block size would have a considerable impact on a throughput intensive application. Does disk block size ...
0
votes
1answer
23 views

SAP BO Freehand SQL date formatting (Oracle db)

My original query worked perfectly via SQL Developer: select * from [table] where DT_CURRENT_DT = '15-OCT-18' However when ran via freehand SQL in SAP BO I got no results. After running the query ...
0
votes
1answer
29 views

What happen when a transaction is committed and SNAPSHOT isolation is enabled?

I am actually considering using SNAPSHOT isolation for one WCF service. Before doing so, I would like to know how it works. AFAIK, when it's enabled, updated rows are maintained in tempDB (instead of ...
-2
votes
0answers
12 views

Measure Database performance on localhost

i have enabled query cache and set cache size, settings shown in the picture below. how do i measure the performance of the database and if the speed of my PHP web application improved after enabling ...
0
votes
1answer
17 views

Does number of backup tables hinder performance

Our main database (MySQL) has about 50 tables with about 2 million records each, many of the columns are indexed. Before doing any major change, we either copy the entire DB under a new name like "...
1
vote
1answer
39 views

What database to choose for 1TB of time series? [closed]

I'm saving about 3GB per day of financial orderbook data to a database (currently Mysql). In the coming weeks, I'm going to save trade and quote (bids/asks) data too. Data is structured, but I'm ...
-1
votes
2answers
77 views

SQL Server performance issue

I set up an online dedicated windows server in Godaddy hosting. The server has 32GB RAM, 2TB Storage, and 4 CPU Cores @ 3.1 GHz. I installed SQL Server 2017 Enterprise Edition. Problem is this the ...
0
votes
1answer
46 views

Performance difference between Text and Varchar in Mysql

I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below CREATE TABLE small_table ( pk int(11) NOT NULL AUTO_INCREMENT, varc varchar(255) DEFAULT NULL, txt ...
0
votes
1answer
46 views

Performance: CTE in an “IN”-Operator

I've got a query with a CTE (getting all children of a parent) followed by a main statement using the "IN"-Operator to filter for the CTE's output. Roughly looks like this: WITH cte_table AS (...) ...
4
votes
1answer
44 views

Postgres jsonb column or standard normalized table?

I'm implementing an app with a payment system, and I need to record transactions that are made through the app. Also I need to use some of the information about the transaction for rendering some KPIs....
0
votes
3answers
25 views

Reducing I/O operations for often-updating data in MariaDB

I have a big table where I update something like 10k rows per second on average, but just one column. Currently it looks to me that MariaDB writes those changes rather instantly onto the disk creating ...
0
votes
1answer
38 views

Should I use bigint or varchar(20) as primary key, performance-wise, for Postgres?

I have 18 character long IDs I get from an API, and I currently store them as varchar(20). They are used as primary and foreign keys. Is it better in my case, to use bigint instead of a string type, ...
0
votes
0answers
40 views

Restore time on dev servers

We have 4 test environments running SQL Server 2014 Developer edition. Every night, we refresh one of those environments by restoring a SQL Backup file stored on a separate server, so that it's in a ...
0
votes
0answers
30 views

Is it better to use IN(ids) instead of many single queries by id

I am wandering what is better to do, when you have big amount of connections with big amount of queries that are being executed by application. Is it better to execute 100 queries where x goes from 1 ...
2
votes
0answers
33 views

How costly is changing column datatype in PostgreSQL?

I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
1
vote
0answers
13 views

AWS RDS Performance insights - Do DBAs would need a DB user? [closed]

This question is more to clarify or expand my knowledge. Currently, on my work, we're migrating all our systems to AWS. Regarding to databases, we start using performance insights but I'm not really ...
0
votes
2answers
32 views

Index columns on pre-production?

If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed. Say your app is new and you have identified columns which are good candidates for indexing (based on ...
2
votes
1answer
38 views

Regularly updating table with huge amount of rows

I have a table containing (in addition to some metadata) a score and a boolean flag: CREATE TABLE scores ( score_id SERIAL NOT NULL CONSTRAINT score_pkey PRIMARY KEY, user_id ...
0
votes
2answers
48 views

Optimising MySQL database with read only tables

I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd ...
3
votes
1answer
154 views

MySQL randomly slowing done due to context switching [closed]

I've been tracking down a problem with my config for the last 2 weeks and I've hit a wall. Hopefully somebody will have a clue and be able to point me in the right direction. Randomly throughout the ...
0
votes
2answers
31 views

mysql v5.5 index performance

On a 5.5.23 server (that needs to go to 5.7 at least, I know) I have an InnoDB table with 3 columns (there are a few more that are not relevant here): TimeC (bigint, unix timestamp) id (integer) ...
0
votes
0answers
35 views

Estimate cost of adding lot of data

We've got an application that will be replaced by a new one. Data of the old one will be migrate in the database of the new one by a migration process (applicative process). Half of the old data is ...
0
votes
0answers
53 views

Problem with the table structure

I have two tables in the database. 1st table size is 150 GB and 2nd table size is 5 GB. Whenever I run an ALTER... query on the 1st table the query will execute in seconds. On the 2nd table, the ALTER ...
4
votes
1answer
74 views

PostgreSQL UDF (User Defined Functions) overhead

Disclaimer The task may seem esoteric, but nevertheless I want to create a POC of some sorts. The goal My goal is to make PostgreSQL database (version 10) expose an API to an application that uses ...
1
vote
1answer
44 views

Deadlock - Due to update causing Index page movement within an index

I am still troubleshooting deadlock mentioned in this questions. In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also. Now I ...
2
votes
2answers
60 views

MySQL Performance vs. Memory

We are running MySQL Ver 5.7.18 on an Amazon Lightsail Linux instance (2GHz Single Core CPU, 2GB RAM, 40GB SSD). Besides MySQL, we also run some Java services on this machine, which access the ...
0
votes
1answer
21 views

DB2 Workfile scan

We just migrate from LUW to zos and we're finding that in z/os the accesspath explains show new node named WFSCAN (Workfile scan) and this rises when there is a join, group by or any kind of sort. ...
0
votes
1answer
40 views

Why should I save both markdown and the converted HTML in my database?

First of all, forgive me if this is the wrong place to ask this or if this has been answered somewhere and I just can't seem to find it. So you have a scenario where you have an article/news web app ...
0
votes
0answers
32 views

Enabling TDE on PRODUCTION Servers - Guidance , Suggestions and Testing TDE ways

First of all just want to say , I am completely new to Transparent Data Encryption. Can I get some guidance on things to keep in mind while I am enabling TDE on Critical PRODUCTION Servers. I have ...