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

6
votes
2answers
667 views

What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?

My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month ...
6
votes
3answers
297 views

Things to consider with a bigger table

I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently ...
6
votes
3answers
3k views

Too Many Connections

I'm using MATLAB to feed the database but after a time I'm getting a too many connections error. I always using opendb and closedb in pair. Is it to fast the feeding? Do I have to wait after I ...
6
votes
3answers
302 views

Query performance

Why SELECT Barraportfolioname FROM portfolio WHERE id IN (SELECT DISTINCT i1.portfolioid FROM Import i1 LEFT OUTER JOIN Import i2 ON ...
6
votes
2answers
5k views

SQL query slow-down from 1 second to 11 minutes - why?

Question: I'm porting the below query (listing tables by foreign key dependencies) to PostGreSql. WITH Fkeys AS ( SELECT DISTINCT OnTable = OnTable.name ,AgainstTable = ...
6
votes
4answers
801 views

Four db servers all identical, one is much slower than the others

The setup We have an application loading a lot of transaction data for later querying. It's sharded on 4 SQL Server 2008R2 Enterprise Edition on Windows Server 2008 64 bits. All servers are ...
6
votes
2answers
1k views

Displaying query plans in Server Management Studio

Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset: select top 10 sum(qs.total_worker_time) as total_cpu_time, ...
6
votes
4answers
856 views

When to use MySQL query_cache?

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a ...
6
votes
2answers
848 views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
6
votes
2answers
3k views

What are the differences between SQL I/O Sim and SQL IO tools?

I want to test my different SQL Server alternatives with a benchmark tool. I found two tools by Microsoft: SQLIO Disk Subsystem Benchmark Tool SQL IO Sim What are the differences between them? is ...
6
votes
3answers
4k views

Performance Hit using CAST() in TSQL (SQL Server)?

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField). So, if myField is of type NVARCHAR, we ...
6
votes
4answers
338 views

What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.] Question 1 What ...
6
votes
4answers
712 views

How can I tell if my SQL Server DB performance is hardware-limited?

Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test ...
6
votes
2answers
8k views

SQL Server gets slow over time until we have to restart it

We have a database with a mixed OLAP/OLTP workload. The queries are quite ad-hoc and are dynamically created in the mid-tier application server. When we start the server, the performance is quite ...
6
votes
2answers
680 views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
6
votes
1answer
826 views

Password hashes: Fixed-length binary fields or single string field?

I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are: Storing the hash and the ...
6
votes
2answers
524 views

Explanation requested for slow DELETE with SQL Server

I would like to get some extra insights/reasoning for SQL Server delete behavior. We have a fairly large database of over 1800 GB. In there are some very shallow tables (only a few integer columns) ...
6
votes
4answers
5k views

Select statement takes long remotely due to long “client processing time”, but short locally

While connected to our production server (SQL2008. very powerful machine), this select statement takes 2 seconds, spitting back all fields (4 MB of data in total). select top 30000 * from person ...
6
votes
2answers
13k views

MySQL creates temporary tables on disk. How do I stop it?

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...
6
votes
2answers
464 views

Is it possible to give the optimizer more or all time it needs?

Given that the optimizer cannot take al the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off. I was ...
6
votes
1answer
295 views

Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?

The sys.dm_os_performance_counters DMV introduced in SQL Server 2005 makes this task somewhat trivial. However, my Google-Fu has been blocked in finding a way to do this in SQL Server 2000. How can ...
6
votes
2answers
261 views

Row padding to get more performance

I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance: Because rows are small (many fit a page) multiple locks may compete for one ...
6
votes
1answer
260 views

Sql server snapshot isolation overhead when read_committed_snapshot is enabled

When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation? Snapshot ...
6
votes
1answer
10k views

High Buffer I/O SQL Server 2008 R2 causes?

Am trying to troubleshoot performance on an SQL server that we have for our document management server and today performance has hit an all time low. Looking at the MDW I setup recently I can see a ...
6
votes
1answer
171 views

How should we handle rows which won't be queried once they are old in PostgreSQL?

We have a table in a PostgreSQL database which is growing on the order of millions of rows per day. Each row consists of: ID Foreign user ID Date and time Other data The date and time aren't ...
6
votes
2answers
976 views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
6
votes
1answer
138 views

Does tempDB disk perf matter if you have plenty of unused memory?

We have a SQL Server 2012 Enterprise setup with a large amount of RAM (256 GB), and at this time fairly small databases (about 17GB). Right now we are experiencing performance issues due to various ...
6
votes
1answer
5k views

What's the difference between POINT(X,Y) and GeomFromText(“POINT(X Y)”)?

I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table. ...
6
votes
1answer
3k views

Capabilities of InnoDB INSERT Performance

Hi I am running the most recent version of Percona Server. Server version: 5.5.24-55 Percona Server (GPL), Release 26.0 I have a 10 cpu box of these characteristics. processor : 0 vendor_id ...
6
votes
1answer
1k views

What should every DBA know? [closed]

As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like ...
6
votes
3answers
2k views

Why performance degradation with query hint FIRST_ROWS(1000)? [closed]

I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input. In case the user supplies very unselective arguments, the query takes a lot of ...
6
votes
2answers
1k views

Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
6
votes
1answer
709 views

Database design for handling 1 billion rows and counting

We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes ...
6
votes
1answer
1k views

MySQL table creation insanely slow

A simple table creation on one of my MySQL databases takes forever: mysql> CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY); Query OK, 0 rows affected (16.58 sec) The machine is quite ...
6
votes
1answer
215 views

Huge differences of performance of MySQL in two servers

We have a MySQL server installed in two different machines, a testing server and a production server, both windows, which is used by a web application. The problem is that there are HUGE performance ...
6
votes
1answer
78 views

Is there a performance hit by breaking out tables into different schemas in SQL Server?

Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server. For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...
6
votes
3answers
2k views

Performance issue with xpath in SQL Server 2008

I have a table with lots of large xml-documents. When I run xpath expressions to select data from those documents I run into a peculiar performance issue. My query is SELECT p.n.value('.', 'int') ...
6
votes
2answers
421 views

MySQL: Does the order of the fields in a table make a difference in performance?

Specifically I am wondering if I should place all my fixed length fields like int, timestamp, char at the beginning of the CREATE TABLE and place all the variable length like varchar at the end. ...
6
votes
2answers
332 views

Transaction speed benchmarks for mySQL v5.6 replication - seems very slow

I'm trying to select the best configuration for our new infrastructure but got a bit confused about the results. I used sysbench v0.5 for the tests: prepare data sysbench ...
6
votes
1answer
573 views

Extremely Slow StatMan query issue with #SP2010 farm

The issue I’m experiencing is explained in a blog-post I created for this, (easy to refer to and to manage the responses / solutions in 1 location) which can be found here: ...
6
votes
1answer
164 views

Start/Stop MySQL

I'm seeking help to understand what happens when the following command line is executed: root@prodn$ service mysqld stop Yes, it shuts down the MySQL server so access to it is no longer available ...
6
votes
1answer
303 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
6
votes
1answer
159 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
6
votes
1answer
1k views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...
6
votes
2answers
395 views

Major Discrepancy Between SQLIO IOPS/Throughput Stats and DBCC CHECKDB Operations

I'm conducting time trials on a new SSD array running both SQLIO tests and a real-world workload of DB restores and DBCC CHECKDB calls. I'm seeing a major discrepancy between the IOPS and throughput ...
6
votes
2answers
268 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 ...
6
votes
1answer
388 views

Backup to three drives suddenly slow

The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit ...
6
votes
3answers
564 views

Single query sending and retreiving duplicate data on MS SQL Server?

Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
6
votes
1answer
497 views

Using an RDBMS for querying tens of Terabytes of time-series data?

I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote: (...) opinions regarding best of breed data structures to be used for indexing time-series (aka ...
6
votes
1answer
176 views

What is the expected performance impact when dropping a large table

I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service. The table is no longer being written to, and needs to be ...