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

4
votes
2answers
2k views

Are many NULL columns harmful in mysql InnoDB?

As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance. Does the presence of excess ...
6
votes
1answer
151 views

Why is performance non-linear if I do many UPDATEs in a single transaction

An older question covers some reasons why the performance of multiple INSERTS within a single transaction is non-linear as the INSERT count grows. Following some of the advice there, I've been trying ...
1
vote
1answer
67 views

How do DTUs in Standard and Premium performance tiers in SQL Azure compare?

We recently observed a serious performance degradation in a SQL Azure database running in Standard3 performance tier - CPU utilization went from ten percent to fifty percent to almost one hundred ...
0
votes
1answer
34 views

Select records not associated to a specific tag in a many-to-many relation

as shown in this sql-fiddle I have a bridge table events_tags representing a many-to-many between events and tags. I need the fastest way to find all events not associated to a given tag t. The ...
4
votes
2answers
258 views

Creating MSSQL Performance Baseline Monitoring

In order to get an overview and compareable data my current task is to create a performance baseline to get some figures about the different productive sql instances. My thoughts are: I want to ...
3
votes
0answers
53 views

Isolate database performance on an instance

I have SQL Server 2014 Standard Edition installed on a machine with two different users and two catalogs, each of them accessed by a different user (each user is strictly tied to his catalog). Is it ...
1
vote
0answers
40 views

MySQL Slows Down after MYD file reaches 2GB (64 Bit OS)

MySQL seems to be writing rows OK (75,000 rows/min) until when the MYD file reaches 1.8 GB, and then the insert rate drops immensly (10,000 rows/min), and 1 hour later it's even slower (6k rows /min). ...
1
vote
0answers
30 views

Bypass stored procedure execution CPU consumption

We are using Microsoft SQL Server Agent Jobs to execute stored procedures on the other database systems (like MySQL or Oracle). Usually we run procedures via linked servers (over OLEDB and ODBC ...
0
votes
0answers
19 views

Load DATAINFILE extremely slow

I am using the code below to insert a 2.2MB file into a MySQL database in the cloud using Azure (the free version) with 36 thousand rows and 8 columns of doubles. MySqlCommand sqlCmd = new ...
5
votes
2answers
474 views

Should we create a schema per “project”?

This is a database where users create a 'project' and work on it. We have a table for project and various other tables that contain different attributes for project (multiple rows for each project ...
5
votes
1answer
195 views

SQL Server on SAN, same LUN: one logical drive vs multiple

First, let me start by saying that I did notice there are multiple similar questions to this, but neither of them is exactly what I want to ask, and not one of them has a definite answer. Second, let ...
2
votes
2answers
56 views

Is there much difference between having a key (intA, intB), or having key (longAB) where longAB is the concatenation of bytes of intA and intB?

This is a bit of an odd question. Let's go with MySQL for example. I don't know a ton about db internals. Compare two scenarios, in one, we have a table with a composite key of the from (intA, intB)...
0
votes
0answers
44 views

MySQL performance differences between two servers

I've a production server (CentOS 5.11) and a development one (Debian 8). Same MySQL version (but different architecture, i686 vs x86_64), same data, same config settings. If I execute a query with ...
1
vote
2answers
129 views

Performance improvement by placing tables on a virtual RAM disk

I am trying to improve performance of queries by placing the tables in a virtual RAM DISK. I am using in Oracle 11.2 Standard edition with 4G of SGA and 2G of PGA. I created a Virtual RAM DISK of ...
3
votes
4answers
124 views

Should I Denormalize Tables To Avoid Joins?

I'm in the process of developing a web application, and need some help with some basic db design. Currently, I am unsure of the following three tables: ### User ### - Id - DetailId - etc... (...
2
votes
1answer
83 views

Is this right decision to define 5-6 foreign key in a single table…?

If i define 5-6 foreign keys on a single table, are there any performance concerns? CREATE TABLE [dbo].[PS_CLASSROOM_MST]( [CLASS_ID] [int] NOT NULL, [CLASS_NAME] [varchar](50) NOT NULL, [...
0
votes
1answer
34 views

Which columns to index with LEFT JOIN and GROUP_CONCAT

So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching: SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, ...
6
votes
2answers
169 views

SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor

I have a 3rd party application which uses a cursor that updates rows in my SQL Server 2014 table. I am unable to modify this code and have therefore devised a scheme to point the cursor to a view ...
0
votes
2answers
44 views

Does subqueries “short-circuit” based on other conditions?

I have a slightly costly case when exists (...) then 1 else 0 end sub-query in the select clause. The sub-query is only really needed if another column in the main query has a specific value (can ...
5
votes
1answer
94 views

Sub-select takes “ages” - EXCEPT much faster

Script to create the tables DROP TABLE IF EXISTS history; CREATE TABLE history ( id integer NOT NULL, ticket_id integer NOT NULL); ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY ...
1
vote
0answers
33 views

Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library

I am writing a PostgreSQL extension using the C language. I would like to do the following tasks: Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in ...
1
vote
2answers
67 views

Why could query performance improve enormous after db restore on new server with worse hardware?

A asp.net page doing very slow to load a list from SQL Server has increased it's performance very much after the whole database has been restored on a new SQL Server. The asp.net app has moved from ...
1
vote
1answer
32 views

InnoDB slow query (few derived and dependent queries)

I have a select query which always worked pretty fast but data kept increasing which is why this query became very slow (2/3mins). I need to get all 0 values from those tables in the last WHERE ...
9
votes
4answers
18k views

How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
0
votes
1answer
51 views

How to quantify rollback events in SQL Server between 2 dates

I need to quantify rollback events and find theirs reasons, without change the application code. There is any way to collect this data? The reason to check the rollback occurrences have need to ...
3
votes
3answers
105 views

Improve Restore Speed

I have a ~3GB backup file from our QA database, and a daily diff (~500MB). For data complexity reasons, some of our developers like to use regular backups from this database, and so we have a process ...
0
votes
2answers
169 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
80 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
63 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
212 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
55 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
129 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
222 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
66 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
115 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
97 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
200 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 ...
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
18k 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
34k 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
43 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
100 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
821 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 ...