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
1
vote
0answers
28 views

Altering a column and cancelling the alter greatly improves SQL performance

We have a table that has roughly 5.5 million rows. We use Entity Framework and run a query on this table and get our row of interest based on 2 columns. One FK column that has an index (Type) and ...
0
votes
2answers
49 views

How Important is the buffer pool really?

I have been doing some testing of a new (virtual) server which is to replace an existing production server. We suspect the current production server is overspec'ed and therefore are tweaking the ...
0
votes
2answers
14 views

Improving performance for large volume of simultaneous reads/writes on Percona 8

A general description of the environment and the use case: Technical: We're running a distributed application with a single server and ~25 clients; each client is running ~75 threads. At one ...
2
votes
1answer
136 views

Performance difference between environments when adding column

We have 2 exact same Database Environments. The second environment contains a copy of the production database and hosts approx 11M records in the Invoice table. The goal of this environment is used to ...
0
votes
2answers
30 views

very high Temporary tables created on disk, how to fix them?

Hello we use nextcloud hand use as backend a mysql-db on Debian 10. I run ./mysqltuner.pl which said Temporary tables created on disk: 99% (48K on disk / 48K total) (I recently restart the mysql-...
3
votes
1answer
68 views

Why is FROM VALUES massively faster than visiting a table once?

Given this setup, CREATE TABLE t(foo) AS SELECT 'foobar'::text; Why is this query, EXPLAIN (ANALYZE, TIMING OFF) SELECT generate_series(1, 1000000), upper(foo) FROM ( VALUES ('foobar') ) AS t(foo)...
1
vote
1answer
111 views

Query Store internal queries being executed thousands of times per minute

I have an SQL Server 2016 Standard production server with more 59 clients Databases. Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) ...
1
vote
2answers
48 views

Execution time/cost of function in assembly

I have a query calling XmlTransform from Microsoft.MasterDataServices.DataQuality on about 1000 rows or so and it takes 30s to do the whole set (the XSL is in a fixed variable), is there any way to ...
-1
votes
0answers
22 views

Is there any software with which I can retrieve all personal information mentioned in the documents such as names, places etc.?

I am a property dispute lawyer and I am struggling with manual data retrieval from these voluminous property related documents. Is there any software with which I can retrieve all personal information ...
8
votes
1answer
484 views

Query time of fetching a particular, single row id by PK is extremely slow

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT * FROM contract WHERE contract.id = 33129; Index Scan using contract_pkey on public.contract (cost=0.29..8.31 rows=1 width=305) (actual time=0....
0
votes
0answers
49 views

Excessive TempDB Usage When Using Pivot + Temp Tables

I have a stored procedure that is acting quite strangely, it basically works in this way: --Populate #tmpActualsVertical with data(in this example 84,652,788 rows) SELECT ...
0
votes
2answers
43 views

Understanding SQL aggregation efficiency

How would the first query compete in terms of performance against the second alternative: Query 1: select count(*) page_views, count(distinct(session_id)) sessions, (count(*) / count(distinct(...
0
votes
1answer
38 views

Why the JOIN doesn't use index?

I have a query like this: EXPLAIN SELECT dictionary.id, dictionary.word, lwd.grammatical_role, lwd.phonetics FROM loghatnameh_dehkhoda dictionary LEFT JOIN loghatnameh_words_details lwd ON lwd.word = ...
0
votes
1answer
63 views

SQL Server select into vs. insert into performance with embedded cross apply openjson

Note: updated 11/15/2021 to address comment feedback. It seems SQL Server 2016 has some odd little corners when it comes to performance. Version: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13....
0
votes
3answers
37 views

Does using "qb_name()" hint (query block name) in a query have significant effect on performance?

I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query: select /*+ qb_name(main)...
0
votes
2answers
37 views

Performance of implementing product images in an e-commerce store as a separate table vs. as columns of the Product table?

I am creating an e-commerce store in Django and am currently trying to decide between implementing product images as an entity of their own then linking to the product by a foreign key or putting ...
0
votes
1answer
25 views

Does changing boot disk to SSD affects performance?

We recently changed our production database disk to SSD. To clarify, all data paths, caching, tmp, ... directory references in the MySQL configuration point to that database disk, now SSD. The boot ...
2
votes
1answer
19 views

Is using a set-returning function as an intermediate step bad for performance?

Say I have two queries that do the same thing, such as filtering something out via a complicated join, but the one queries then joins it with one table, and the other query just orders it. Can I use a ...
0
votes
1answer
50 views

What replacement algorithm does this ALTER TABLE use

Question I am trying to figure out what kind of replacement algorithm a command like the following would use ALTER TABLE `catalog_category_product_index_store1` COMMENT='Catalog Category Product Index ...
1
vote
2answers
58 views

Different execution time for same query on different Postgres databases

I have two identical databases, development and live. I run this query on both of them.... select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device) On the dev box the ...
-1
votes
0answers
49 views

Should I use many to many instead of one to many for performance reasons?

Consider two tables Parent and child. 1B Child rows. 10 Parent rows Only 1K children have one parent each. The relation here conceptually is OneToMany. Is it better for performance reasons to ...
2
votes
2answers
66 views

improve performance of large scan calculation in PostgreSQL table

I have a table flags that collects user contributions for each distinct polygon (flag_value, in this case a 'YES') during their usersession. The idea is to count how many users said YES out of the ...
1
vote
1answer
66 views

Performance of temp db files on SAN

It is generally recommended to put the tempdb database on disks that differ from those that are used by user databases. I have 4 databases on my sql server, ranging from 5GB to 300GB in size. D: drive ...
3
votes
1answer
355 views

Best practices for updating data on a "live" table

Scenario: I am running a user application hooked to a SQL Server as the data backend. During a normal day, the data in the table associated with the application can be subject to frequent change, ...
0
votes
0answers
37 views

Can related queries be executed in a single plan?

I'm experimenting with a web app. It executes the exact same set of closely related queries each time to update the fields on the page. There are about 25 of them, and many share subqueries. ...
0
votes
2answers
34 views

What is the best way to measure INDEX impact on production?

We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have ...
0
votes
1answer
26 views

How properly create index for a referenced table

I have a table with thousands of records with vehicles data, and this table is referenced by the table, where images are stored for these vehicles. This second table about millions of records because ...
0
votes
1answer
80 views

Table Lock Escalation - Performance Questions

I have few questions on table lock escalations, please confirm or disprove: Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ? When Shared lock is ...
0
votes
2answers
70 views

High SQL Server Usage

I run SQL Server 2019 with CU13 in Hyper-V vm and it seems that my hosting company is saying my sql server is doing too much disk io. I am not a dba so i did poke around and found some queries that I ...
1
vote
0answers
63 views

Many sessions AWAITING COMMAND

I need some guidance please. An application is going through migration and the processes seem to be quite cpu intensive. CPU utilisation on the server is very high - in the 90's. The headblocker ...
1
vote
1answer
51 views

Speeding up SELECT FROM a large table

I have a large table (500M rows with ibd size of 1.5TB) CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, ChildID int(11) unsigned NOT NULL, Number smallint(5) unsigned, Title varchar(255)...
1
vote
1answer
58 views

What's the best practices for storing visits? [closed]

I would like to save visits in a table. I am using MySQL but I am not sure how I should save the visits as the records may increase considerably and this might cause performance issues and I am also ...
0
votes
1answer
56 views

Slow query Mysql with Order By and JOIN

I've been investigating an issue with a slow query when trying to get the last 50 comments of a user. To give some context the database size is 9 TiB and the COMMENT table has about 7 billon of rows. ...
0
votes
0answers
29 views

High use of CPU on AWS RDS mysql

I am experiencing trouble with a database, I am pretty sure there are some troublesome queries created by the programmers, but I need to find the culprit. So usually I dont have a problem with the ...
0
votes
0answers
60 views

How to speed up restore database from snapshot in SQL Server? [duplicate]

To revert a test database to an initial state (after running a test), I would like to restore the database from snapshot. I'm using the following script to achieve that. However, the script execution ...
0
votes
2answers
50 views

Choice between two antipatterns: Overdesign versus Over-optimization

Sometimes I need to keep state, like an IsVerified flag. There are two options: boolean (or bit) Advantage: smallest and fastest ...but in theory "over-optimization" and thus a bad argument. ...
0
votes
1answer
76 views

Speeding up DBCC CHECKDB on a large database

I have 16 GB RAM, 12 GB allocated for SQL Server, total size of all databases on an instance 5TB. DBCC CheckDB with PhysicalOnly checking on the biggest database ~ 3TB. 1 socket, 2 virtual processors. ...
0
votes
2answers
53 views

Query with CASE in subquery join clause is very slow on PostgreSQL but fast on Oracle and MariaDB

We have an attribute-oriented data management system with bitemporality and flexible tenant priorization logic. Deletion of records is done by inserting the same row in DELETED status. Tenants may ...
2
votes
2answers
1k views

Join condition vs. WHERE...does it make a performance difference?

I've developed a habit of separating join conditions from other additional conditions. However I understand the logical execution order is: FROM WHERE Can it be detrimental to performance if I add ...
0
votes
0answers
33 views

Is there any alternative to Sage Evolution's way to backup & restore databases

I am currently working on a handful of web apps that work in coherence with our Sage 200 (Evolution) database. However, one problem that we have noticed at the moment is the ability to backup and ...
0
votes
0answers
14 views

Postgresql POD Master/Slave don't clean the cache when Client stopped sending data

I'm running a Performance Stress test on our backend (NODE JS) using a client(JAVASCRIPT) that generates the stress , with a Postgres DB. I've stopped the client from sending data after 5-6 hours , ...
1
vote
2answers
68 views

Select * - Why or Why Not?

Is there really any difference between these two queries? SELECT TOP 1000 [COL_1], [COL_2], [...], [COL_n] FROM [database].[schema].[table] SELECT TOP 1000 * FROM [database].[schema].[table] ...
1
vote
0answers
70 views

How can I confirm why a SQL Server query runs faster on development laptop than production server?

I am finding that queries are taking slightly more than twice as long to complete on the production server compared to my development laptop. My assumption (possibly incorrect) is that the query ...
2
votes
1answer
245 views

What perfmon metric will help me to monitor whether the SQL server memory is under pressure and resulting in usage of page file on disk?

SQL server is assigned 110GB memory. It is consuming the entire memory. I want to understand whether there is memory pressure or not. Usually SQL server will remove old pages from the memory (that it ...
0
votes
1answer
44 views

Why insert statements get slow when having too many indexes?

I am having a slowness in my insert statements when working with MongoDB, and when I did a quick investigation, I though this might happened because the table has 11 indexes. Plus, I know that when I ...
0
votes
0answers
47 views

Oracle multithreaded JDBC bulk select high-performance data export

The goal of our solution is to export as fast as possible CLOB data from an events table. The concept is to logically partition the data with a partition_id column such that every thread from the ...
0
votes
1answer
37 views

How does proportional fill algorithm help with tempdb performance once the files are full?

Having about 8 tempdb data files is a good practice because of large number of object creation and deletions that happen in the tempdb. So sql server can use the data files in parallel. Given this ...
0
votes
1answer
38 views

Is this horrendously low row estimate causing a slow insert?

reference: https://www.brentozar.com/pastetheplan/?id=Bklc3fGNt I have a clustered index on both #LargerRange and #SmallerRange. These indexes contain 5 key columns: Name, InOrOut, LocCode, VisitID, ...
0
votes
0answers
45 views

Working with index-key sizes which are close to the DBMS's index-key size limit

I might need to work with relatively large (non-clustered) index keys (between 1KB to 4KB). I found that Spanner and DB2 support 8KB index-key size, MSSQL allows 1.7KB and Postgress around 2.6KB (1/3 ...
1
vote
1answer
38 views

What are the reasons to have an API back end if everything required can be handled securely by direct Database connection?

Today's database allow strict user restrictions, such as readonly on specific tables. If a client application is very simple and only needs to read a database, is it a better idea to allow the client ...

1
2 3 4 5
124