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

7
votes
4answers
542 views

How do I best measure the query performance?

I have 2 stored procedures, where the second stored procedure is an improvement of the first one. I'm trying to measure by exactly how much that is an improvement. 1/ Measuring clock time doesn't ...
0
votes
0answers
44 views

Explain difference between insert as select and select

Can someone explain why there is plan difference? Same query, same time the explain plan taken. I understand what happen (different order of getting the data) and how to fix it(move to forall insert ...
2
votes
1answer
138 views

Disk Latency vs Throughput

I've searched the web for a little while and haven't found an answer I'm confortable with. I am testing a SQL Server on a VM that was presented new disc. If disk latency is reduced, shouldn't ...
3
votes
1answer
69 views

Why does this limit make the postgres planner use a much slower index scan instead of a much faster bitmap heap/index scan?

I have a table of about 700k rows in Postgres 9.3.3, which has the following structure: Columns: content_body - text publish_date - timestamp without time zone published ...
2
votes
1answer
61 views

In Oracle AWR report how to use “Latch statistics” section in analysis

Latch statistics in AWR report contain following sections 1.Latch Activity 2.Latch Sleep Breakdown 3.Latch Miss Sources 4.Parent Latch Statistics 5.Child Latch Statistics How can I use these ...
3
votes
1answer
89 views

Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted. My question is, how this will impact the performance? We are using SQL Server 2012. Table statistics (will ...
3
votes
2answers
174 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 ...
0
votes
0answers
43 views

Mysql profiler different duration information_schema.profiling

Hi! I have a problem and no idea how to resolve it. I'm trying to use mysql profiler. I'm using the command: set profiling=1; to turn it on. Later, i execute my query which is correlated ...
0
votes
1answer
46 views

Is there a table size above which SELECT performance degrades significantly?

In MySQL, does SELECT performance degrade significantly after a certain table size? I have a table with about 107 million rows and some queries seem stuck in "Sending data" for a long time. Of course, ...
0
votes
5answers
149 views

How to determine why a MySQL server has become slow and requests sometimes time out?

I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having. Most of the time, responses are returned quickly from the ...
8
votes
3answers
205 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 ...
2
votes
1answer
74 views

Debugging why postgres is snowballing with system cpu usage

I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database. ...
0
votes
1answer
99 views

performance difference between < and <=

I remember that there is a performance issue with SQLs that use "<" against "<=" because of statistics and/or histograms and/or the data itself, but I dont remember exactly the issue. I know ...
11
votes
1answer
506 views

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
4
votes
1answer
53 views

How can a connection for one DB block a connection to another DB in SQL Server?

We have an interesting scenario. For weeks, we have been experiencing 'outages' on SQL Server 2008 -- where our websites have slowed to a crawl and mass timeouts for all customers who have DB's ...
-1
votes
1answer
42 views

Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows ...
1
vote
0answers
69 views

Poor performance in my pgpool cluster

I've configured a postgres cluster with pgpool2 using the following link. The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for ...
1
vote
1answer
50 views

How can I improve performance of update query in PostgreSQL

I have a table with about 80 million records. I have added a new attribute (heigth_category) which I want to fill based on another attribute (heigth). If the heigth is between 0 and 6, I want to fill ...
0
votes
1answer
47 views

PostgreSQL - Return unique combinations of columns based on where clause

This is quite a long question, please bear with me. So I'd like to first explain I have a database of firewall logs created using the following command: CREATE TABLE firewall_logs_mapped ( ...
0
votes
0answers
31 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
6
votes
1answer
215 views

Estimating IO Requirements for Bursty Usage

We have an application that queries a SQL database periodically throughout the day. There are periods of zero or only light activity, interspersed with individual requests for relatively large ...
2
votes
1answer
78 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
0
votes
0answers
50 views

TempDB causing high IO writes - dependent on server's max memory setting [duplicate]

I have a mirrored SQL Server 2012 database. The first server has a hugh tempdb write IO load when the maximum memory limit is set above 8192 mb. These 8192 where the original setting for both the ...
0
votes
1answer
48 views

RAID0 vs multiple files in file group

I have a server that gets updated and backed up once a day and processes queries the rest of the day (selects). I'm trying to improve performance of the update process and the selects. I'm not worried ...
0
votes
0answers
31 views

H2 database binary file full of @^@^@^@^

I am investigating H2 database performance and was curious to why the database file was several megabytes long when only storing hundreds kilos of text, I therefore opened the database file with vim ...
6
votes
2answers
507 views

What is the fastest way to insert large numbers of rows?

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition ...
2
votes
1answer
97 views

Specifying columns in LEFT JOIN through subquery beneficial performance wise?

I'm trying to understand how JOINS are handled, and was wondering about the following: Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty ...
1
vote
1answer
55 views

Oracle views not using indexes [closed]

I am experiencing the unexplainable - at least to me. I have some tables in my production database which have some indexes on. We then have a number of views which merge the data across tables to ...
0
votes
1answer
128 views

How index work in oracle? [duplicate]

I want to understand : How index work in oracle. How they are stored in the disk. How they increase the efficiency of a query? By third point I means how Index contribute to the efficiency of ...
3
votes
1answer
58 views

What inefficiency does sharding & partitioning attempt to address? [closed]

I have never implemented sharding or partitioning, but from what I understand, multiple servers are used to distribute large amounts of data. Is this because of a limited hardware budget or to ...
0
votes
2answers
73 views

Tips on using SQL access advisor in Oracle

Please share how to use oracle SQL access adviser for single or multiple queries. you can assume that we know the sql ids for the queries we are interested in.
1
vote
1answer
74 views

What useful information can I get from ASH report which I cannot get from AWR report?

What useful information can I get from ASH report which I cannot get from AWR report? What are the common scenarios in which its better to get information from ASH than from AWR?
0
votes
1answer
36 views

MySQL: What points should we consider when create composite index

There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key. Beside this, we create composite index on the ...
0
votes
0answers
29 views

Slow exporting SAP Data Services to MySQL (Oracle is faster)

We've a MySQL 5.1 server and we are trying to import data from SAP Data Services from another server. It works but it takes 3h! We tried to setup an Oracle XE server to test the same operation and it ...
0
votes
0answers
28 views

How to optimize subselect join on large tables

There exists a need to migrate from one definition of a column to the next, so a backfill must occur. In order to get a grasp for the task at hand, I needed some background on the data being updated. ...
1
vote
0answers
112 views

Postgres database zero down time production deployment

I need some idea about how I can deploy a financial application in production environment using PostgreSQL DB with zero down time. I have two servers in Master-Slave design. Currently I am giving ...
4
votes
0answers
85 views

SQL Server CPU spikes - possibly non-query related - suggested indicators

I'm looking for suggestions for indicators I could monitor to discover what is causing these cpu "blunted spikes". Some known information: sqlserver.exe is confirmed as the process consuming the ...
2
votes
1answer
51 views

Does Windows Server File Compression slow Oracle DB performance?

I'm starting a new a new contract and I get to troubleshoot their Oracle instance... I'm more apps and sql, but I'll be trying to figure out their crashes and slow performance. Go figure. I can ...
3
votes
0answers
83 views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
0
votes
0answers
24 views

Mysql View Performance on Aggregate Functions

I've a table that contains weekly statistical infos. ex. Society - Week - Year - Data AAA Inc - 1 - 2014 - 10250 AAA Inc - 2 - 2014 - 5235 BBB Inc - 2 - 2014 - 3210 ... Over this table i need to ...
6
votes
2answers
169 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...
4
votes
1answer
109 views

Settings for a MySQL backup only slave instance

I have two instances of MySQL running one a server, set up as master and slave. It's all working smoothly together and I'm only using the slave instance for backups to avoid locking up the live ...
2
votes
2answers
128 views

MySQL on SSD - what are the disadvantages? [closed]

Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost. Given that 20% of all queries in my database are INSERTs and ...
1
vote
0answers
42 views

Fastest way to get the length of the intersection of two text arrays in PostgreSQL

This is what I am currently using: CREATE OR REPLACE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS $$ select array ( select unnest($1) intersect select unnest($2) order by 1 ...
3
votes
0answers
159 views

Database under heavier load — all SQL queries start timing out after a period of hours/days

I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it. You can see that everything seemed to be OK until about 3AM this ...
0
votes
1answer
136 views

What is the Difference between IN and Not IN performance wise?

I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise. When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
1
vote
1answer
137 views

InnoDB: very bad performance on INSERT/UPDATE

Lately I have really big problems with INSERTs and UPDATEs. On my website 80% of all queries are SELECTs but in query slow log there are only INSERTs and UPDATEs. My dedicated server has 32GB RAM, ...
5
votes
1answer
279 views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
0
votes
1answer
176 views

Improve Insert performance for 200 Million rows

How can the following insert statement be improved - which varies from 20mins on certain days to 70-140mins on most other days. SELECT DISTINCT od.SourceID, od.OrderID, od.OrderLineNo, ...
0
votes
2answers
50 views

How to change table/indexes structure for a query with two aggregated colums

Do you have any suggestion on how to improve a table structure or its indexes in order to have a single query run faster? The table has this structure: docid | character varying(17) | ...