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.

learn more… | top users | synonyms

0
votes
2answers
151 views

Multiple WHERE IN on multiple columns

I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns. More specifically, i'm doing the following: SELECT a, b FROM ...
1
vote
3answers
78 views

Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

I've a table [order] where a simple select shows cardinal difference between Estimated Number of Rows and Actual Number of Rows and in a strange way. Generally Estimated Number of Rows skews as ...
0
votes
1answer
46 views

SQL Server data, logs, backups files in mounted foldersin C:\

I know that is a good practice to have data, logs and backups files of SQL Server in different disk, for example D:\ for Data files E:\ for Logs files F:\ for Backup files If I want to use this ...
1
vote
1answer
57 views

SSIS push and pull performance

After reading Which one is more efficient: select from linked server or insert into linked server? I wonder if this is true also for SSIS? For example, I have 2 SQL Servers (A and B) and SSIS ...
4
votes
1answer
201 views

MS SQL Server CPU load goes up dramatically when turning on 2014 features by setting compatibility level

I'm running some Microsoft SQL Server 2014 load testing on a decently spec'ed machine. I'm certainly not an expert on the SQL Server tuning specifics, but with the bits an pieces I've gathered from ...
0
votes
0answers
48 views

oracle : Effect of inserting new rows when row movement is enabled

I have a table which is partitioned by key. I want to move few records (which I require later) to another partition so that I can drop this partition and get some free space. I am enabling row ...
3
votes
1answer
122 views

Speeding up a GROUP BY, HAVING COUNT query

I'm trying to speed up this query in Postgres 9.4: SELECT "groupingsFrameHash", COUNT(*) AS nb FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876" GROUP BY "groupingsFrameHash" HAVING COUNT(*) ...
1
vote
3answers
202 views

MySQL database uses too much CPU

I have a MySQL database which uses too much CPU. I am actually performing benchmarking to the web servers which queries the database. Right now the database is being the bottleneck, it uses too much ...
0
votes
1answer
61 views

If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

We have an issue with MySQL InnoDB performance. We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts ...
6
votes
1answer
112 views

Why would a procedure sometimes hang executing SELECT…INTO #Temp2 FROM #Temp1 JOIN LocalTable…?

We have several stored procedures, called by jobs, that follow a specific pattern (designed to minimize blocking). Some of these procs tend to hang on occasion, running up the CPU, tempdb allocations, ...
0
votes
0answers
46 views

Performance drops after online backup

We are using DB2 Express-C 10.5.5 on Ubuntu Server 12.04.5 64-bit. Every night we do a full online backup: backup db MYDB online to /home/db2inst1/backup compress include logs after backup we do log ...
0
votes
1answer
81 views

Why Write IOPS,Write throughput,Queue depth decreased on AWS/RDS after changing innodb_flush_log_at_trx_commit and sync_binlog?

I changed values for my Mysql parameters 1. innodb_flush_log_at_trx_commit=2(before it was 1) 2. sync_binlog=0(before it was 1) This has changed some of my parameters on AW RDS drastically ,here ...
22
votes
1answer
150 views

Very strange performance with an XML index

My question is based on this: Improve Xquery performance in SQL Server To give an answer there I did the following test-scenario. Test scenario First I create a test table and fill it with 100.000 ...
6
votes
1answer
3k views

Why is my Azure SQL Server so slow?

We currently have a VM that is very underpowered and are proposing to move to an Azure VM with better specs. The problem is, the Azure VM is much slower than the original VM even though it is of a ...
11
votes
2answers
4k views

More CPU cores vs faster disks

I'm part of a small company so as usual covering a number of different roles. The latest of which is procuring a dedicated SQL Server box for our .NET web app. We've been quoted on a dual Xeon E5-2620 ...
8
votes
2answers
17k views

High CXPACKET and LATCH_EX waits

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...
42
votes
4answers
33k views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...
1
vote
0answers
41 views

SQL Server 2012 Express and Hardware Differences affecting performance [closed]

Here is the setup. I'm running SQL Server 2012 Express on the following two boxes: Box 1 (Server) : Dell T710 2x X5680 3.33Ghz Xeon 92GB Memory RAID 10 - 146GB Dell drives, w/ Cachecade SSD drive ...
2
votes
2answers
4k views

Finding reason behind high write IOPS on MySQL (RDS) server

I am having performance issues with an AWS RDS instance (running MySQL 5.6). This is the backend db to an XMPP server called Tigase. According to Tigase Inc. this type of db instance should be able ...
0
votes
0answers
24 views

Is joining two tables from the same DB faster than in separate DBs? [duplicate]

Would a query / view for joining two tables that reside in the same DB perform faster than one table from each DB in the same instance? I'm using SQL Server 2008 R2 and it's a simple inner join.
1
vote
2answers
89 views

Regarding difference in execution time of queries

These are two explain plans of two different queries that return the same results but at a drastically different speed (280 second vs 1-7 seconds). I want some help knowing what the fast query has so ...
3
votes
1answer
94 views

How do I get useful SQL Server database performance statistics?

We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We ...
1
vote
1answer
794 views

Temporarly disable cache in oracle 11g

I want to disable the caching for my session in oracle 11g to see how long a query takes. Is this possible or is there a better way for meassuring the time a query takes to run?
0
votes
0answers
26 views

select aggregates at each funnel step

I'm trying to find a way to efficiently get aggregates at different stages of a funnel. For example, given documents that contain sections, I want to tell how many sections and documents an author has ...
2
votes
3answers
6k views

Select query takes more time than it should

I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure: Below is some of ...
1
vote
1answer
37 views

Strategy To Remove Old Records

We are running Oracle 11.2.0.4.0. It is a transactional database that has been online since 2011. We have been tasked to clean up old records. The table structure is (parent->child): A->B-C->D->E ...
0
votes
1answer
850 views

HOW does ROWNUM work with this “optimization”

I'm trying to optimize a query in which I have some requirements where I need to be able to arbitrarily select a row ROWX from a query. Why is it that SELECT XML_DATA FROM (Select ROWNUM AS ...
0
votes
1answer
58 views

PostgreSQL takes 99,9% I/O? [closed]

Please, look at this screenshot of iotop tool output (Linux). There is long-running batch update process on table with 20+ million rows. Does it mean that postgres is limited by I/O disk performance? ...
1
vote
0answers
58 views

Adding a Mongo Shard decreases the insert performance

For a mobile application which will hold quite some documents, we've chosen Mongo as a database. Let me explain the situation first: Every day, I have a set of documents that needs to be inserted, ...
5
votes
1answer
170 views

What is the best storing device for DB transaction log?

I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 ...
1
vote
0answers
45 views

How to compare different DBMS? [closed]

I'm planning to compare MS SQL Server and Teradata. I know, they are have completely different architecture, because they were created to achieve different goals. But here we are. I have generated ...
1
vote
0answers
80 views

Very slow MySQL performance

I have 4 million rows in MySQL. I have a count query to fetch from DB to Apache web server. The issue is first load (Count query) - when I click fetch button, it takes more than 5 mins. Second time ...
3
votes
0answers
56 views

Read READ COMMITED transaction advantage in MySQL, high performance object model

I am writing high performance object model with READ COMMITED locking. My question is if there is any reason to starting READ ONLY transaction for read only operations vs. standard SELECT not ...
5
votes
2answers
164 views

Total billions of rows and daily millions of row insert: What database system suits best [closed]

We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns. Operations are: Insert 455+ thousand rows per day ...
1
vote
1answer
126 views

Stored Procedure Performance Issue in Sharepoint Report

We have a report stored procedure that runs in about 1 minute in production; however, when executed from the Sharepoint it populates, it rus for over 20 minutes, with exactly the same parameters. ...
2
votes
1answer
76 views

MySQL best performance settings [closed]

I am going to do some performance tests on MySQL server as I want to evaluate how MySQL behaves in some situations. MySQL is the only (additional) software installed on my server (Windows Server ...
4
votes
1answer
93 views

Debug query on big table that is sometimes slow

I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries ...
3
votes
0answers
87 views

Sort operation SQL Server 2012

I am trying to optimize a query and the sort operation (91% cost) is the costliest operation. Any help will be appreciated. Query is : SELECT TOP 1 ArchiveType, CONVERT(NVARCHAR(30), ...
9
votes
3answers
14k views

Cache MySQL database in memory

I am having problems with a website with 600MB of MySQL database. The website is way too slow. I noticed the bigger the MySQL database gets, the slower it goes. When it was 5MB, the website was very ...
2
votes
3answers
211 views

Count is slow Oracle

this is my query : SELECT Count(last_upd) FROM S_CONTACT WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd + 1/24 > SYSDATE - (1 / (24 * 60)); It takes 84 seconds to return ...
0
votes
0answers
29 views

One bitmask column vs multiple bit columns [duplicate]

I have many tables with a column storing a set of days of week. I used to use MySQL and data type "set" worked well. For example: [worker] id weekday_mask (data type: ...
3
votes
1answer
112 views

Improve performance of query with multiple group by column and count function in select

Brief Background of issue We have a aggregate table to store activities of competitors and this table have currently 3 Million activities and we need to retrieve data from this table group by ...
7
votes
6answers
922 views

Fast reads without paying the price on writes

We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, ...
0
votes
1answer
66 views

Does innodb_buffer_pool_size accept “*GB” values?

I thinks innodb_buffer_pool_size is specified in bytes, would typing in values with unit (i.e 1024MB or 1GB) work? Has anyone already tried this? Specs: MySQL 5.6
1
vote
0answers
48 views

Disable/Remove Table Index for INSERT Performance

I am doing a database maintenance where I have to add new fields to a table with over 10GB of data about (20 million rows). To solve that I renamed tableA to be old_tableA and created a new table ...
1
vote
2answers
2k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
1
vote
2answers
157 views

novice-design: Are FOREIGN KEYs always necessary? Joins on columns which are not PK

I've been wondering if following approach is bad or really very bad:). Question#1: can we use indicator_id to combine data from indicator_values and indicator_details tables (without using FOREIGN ...
1
vote
0answers
98 views

MySQL 5.6 Performance Tuning Issue

I have read and try lots of things about MySQL optimization and tuning. I tried most of them one by one according to our needs but nearly I got nothing. I am doing my test using some simulation ...
17
votes
4answers
35k views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
0
votes
0answers
64 views

How can I restore a MySQL dump faster on Windows?

I am restoring some InnoDB and MyISAM .sql files that are on the order of GBs (ranging from 1 GB to ~ 10GBs) and they are taking a day or longer to restore. The command I am using currently is: use ...