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.
9
votes
3answers
275 views
Query Performance Tuning
When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters? Do you run the query and view the actual execution plan? If so, what ...
9
votes
1answer
555 views
SQL Server Cache Flush and Disk I/O
We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are ...
9
votes
1answer
2k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
9
votes
2answers
630 views
Placing Transaction Log on Separate Volume [solid state]?
Transaction logs are often isolated on a separate volume. The rationale for this practice, as I understand it, is that the transaction log's data is written sequentially -- and hard drives can execute ...
9
votes
2answers
1k views
Updating 700 million rows to same value
I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.
I don't have admin access, or access to an admin, so this needs to be accomplished with ...
9
votes
2answers
3k views
Slow Performance Inserting Few Rows Into Huge Table
We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
8
votes
4answers
4k views
Methods of speeding up a huge DELETE FROM <table> with no clauses
Using SQL Server 2005.
I am performing a huge DELETE FROM with no where clauses. It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE. The problem is ...
8
votes
3answers
2k views
How do indices impact query performance?
Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
8
votes
3answers
278 views
What is retrieved from disk during a query?
Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...
Do the number of columns in a particular table affect the performance of a query, ...
8
votes
3answers
682 views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
8
votes
2answers
161 views
Postgres query plan of a UDF invocation written in pgpsql
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
8
votes
2answers
2k views
Configuring PostgreSQL for write performance
One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed ...
8
votes
1answer
983 views
SQL Server TempDB behaviour in large memory environment
Reading over In-memory SQL Server log file reminded me of a question I had a short while ago.
We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
8
votes
3answers
1k views
What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?
I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better:
delete rows and insert new ones, or
update already existing rows
For me ...
8
votes
4answers
923 views
How can I optimize this MySQL query further?
I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
3answers
2k views
How to boost performance of virgin queries in MS SQL Server?
I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
8
votes
2answers
311 views
Strategy to handle an SQL Server DB with too many files (BLOBs) in it?
Scenario:
SQL Server 2005 database servicing an ASP.NET application (on a separate web servers).
Database:
DB has about 5GB of "normal" data in it, and about 15GB of "files" (e.g.: a 200k PDF stored ...
8
votes
1answer
2k views
Reasons for occasionally slow queries?
We are running MySQL 5.1 on Windows Server 2008 R2.
We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
8
votes
1answer
877 views
Optimising BCP performance for BLOB data
I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between ...
8
votes
1answer
615 views
In Google App Engine, what is the most effective many-to-many join model?
The BigTable design rejects many of the philosophies of standard relational models, explicitly preferring denormalization to a big host of tiny tables.
One of the larger areas where this is a problem ...
7
votes
4answers
571 views
DDL contention on TempDB
I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
7
votes
5answers
9k views
How to efficiently check EXISTS on multiple columns?
This is an issue I come up against periodically and have not yet found a good solution for.
Supposing the following table structure
CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C ...
7
votes
4answers
1k views
Help me choose a RAID level combination for a SQL Server 2008 instance
I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.
I am going to make new RAID configuration. I have 6 SCSI ...
7
votes
4answers
294 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 ...
7
votes
3answers
274 views
Performance benefit of taking unused databases offline
I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to ...
7
votes
3answers
2k views
SQL Server performance sudden degradation
I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full ...
7
votes
2answers
3k views
When To Update Statistics?
I've inherited a Maintenance Plans that does the following:
Cleanup old data
Checks DB integrity
Performs Database and Transaction Log Backups
Reorganizes Our indexes
Updates Statistics
Delete old ...
7
votes
2answers
693 views
Is there a way to force an index to stay in memory with SQL Server 2008?
I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
7
votes
3answers
1k views
SQL Server - Multiple running totals
I've got a base table with transactions and I need to create a table with running totals. I need them to be per account and and also have a few running totals for each account (depending on the ...
7
votes
5answers
1k views
What could be causing strange query timeouts between PHP and MySQL?
I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On ...
7
votes
3answers
187 views
Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?
Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been ...
7
votes
4answers
2k views
TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately
I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters.
Here is my take on it:
If I am seeing a lot of SQl compilations, then ...
7
votes
2answers
547 views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
7
votes
2answers
449 views
How to implement paging in an effective way?
I have a database query which could result in a big result set. The client which displays the data receives the data over network, so the idea was to minimize the amount of transferred data by ...
7
votes
1answer
1k views
noexpand hint for non-enterpise edition and performance
I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says:
Indexed views can be created in any
...
7
votes
3answers
2k views
Multiple Oracle Instances — is this a good practice?
One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're ...
7
votes
1answer
740 views
PostgreSQL Index optimization with dates
I have a large table of objects (15M rows +), for which I want to query for outdated field. PostgreSQL (9.0.8)
I want to divide the query by millions, for scalability & concurrency purposes, and ...
7
votes
2answers
711 views
Is the overhead of frequent query cache invalidation ever worth it?
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
7
votes
2answers
162 views
High rise in SQL Server CPU after adding a DB
I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005).
My average CPU was on 48% ...
7
votes
2answers
1k views
INSERT performance difference between temporary tables and table variable
I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.
This is the code to insert ...
7
votes
1answer
280 views
Could Stack Overflow run on SQL Server Web Edition?
I am starting a new web venture that may need to scale to a high number of users.
I am confident with the SPLA licencing for SQL Server Web edition, but want to know if I will need to factor in ...
7
votes
1answer
5k views
Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?
During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions ...
6
votes
3answers
525 views
SQL Database Structure for RESTful API
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.
Initially, I though a table per resource would be a good way to go, but I'm ...
6
votes
4answers
3k views
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
I had to write a simple query where I go looking for people's name that start with a B or a D :
SELECT s.name from
spelers s
WHERE s.name LIKE 'B%' or s.name like 'D%'
order by 1
I was wondering ...
6
votes
5answers
551 views
Inclusion of ORDER BY on query that returns no rows drastically affects performance
Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant ...
6
votes
2answers
306 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
268 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
2k 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
4answers
3k views
Why using innodb_file_per_table?
There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
