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.
5
votes
3answers
1k views
Database suggestion for a social network/ knowledge base community?
I am looking into various database types and DBMS's for a new project I am wanting to start in the summer.
I have built systems in MySQL and postgreSQL , now I am wanting to expand my knowledge and ...
5
votes
1answer
590 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 ...
5
votes
3answers
175 views
When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help
Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
5
votes
2answers
2k views
How do I create an index to speed up an aggregate LIKE query on an expression in postgres 8.4?
I may be asking the wrong question in the title. Here are the facts:
My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
5
votes
1answer
146 views
Is my function nondeterministic one
I have two functions: fn_Without_Param and fn_With_Param
CREATE FUNCTION [dbo].[fn_Without_Param]
(
)
...
INNER JOIN .. ON .. AND SubmitDate = CONVERT( varchar(10), GETUTCDATE(), 101 )
and
/*
...
5
votes
2answers
561 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 ...
5
votes
2answers
366 views
SQL Server performing slow
I have a Microsoft SQL Server running on an IBM server with 10 cores and 64 GB RAM.
I have from 100 to 300 users working concurrently on the server.
Users complain about slowness of the application. ...
5
votes
4answers
2k 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 ...
5
votes
2answers
613 views
The order of INNER JOIN's
I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000.
I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
5
votes
4answers
2k views
Optimising join on large table
I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
5
votes
2answers
229 views
Performance impact of waiting on a service broker message
I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the ...
5
votes
2answers
2k 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 = ...
5
votes
2answers
546 views
Speed up INSERTs
I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is :
Have no index on the table whatsoever
Switch to simple logging (from full logging)
...
5
votes
2answers
334 views
Unexpected extremely long query time (~5 minutes using nested WHEN-INs)
For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes).
The data in the database is highly (and not arbitrarily) normalized. It very efficient ...
5
votes
2answers
628 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)
, ...
5
votes
3answers
2k views
Why does this Entity Framework query perform so bad in MySQL?
Let me give you a little setup. We have a InnoDB table on MySQL 5.1 with close to 20 million records, no foreign keys, and proper indexes for queries we do. We are using the latest 6.3.5 MySQL ...
5
votes
1answer
597 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 ...
5
votes
1answer
278 views
Denormalized table designs in normalized database?
There was a discussion between the developers and DBA's last week on set of tables that the developers were creating with more than 400 columns in our OLTP environment with combinations of VARCHARs, ...
5
votes
3answers
596 views
CPU usage on RDS instance monotonically increasing with no change to query volume
I have two RDS instances: a R/W master and a read-only replica.
On 29th June, the replica stopped registering replication data - not sure if this is related.
On July 3rd, the master's CPU usage ...
5
votes
2answers
132 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 ...
5
votes
1answer
289 views
How can I benchmark the performance of UDFs to figure out which one is faster?
Basically I have two scalar UDFs that output the same data, one that uses a cursor and one that uses a recursive CTE. I want to determine which one should be used and discard the other; I'd prefer to ...
5
votes
1answer
68 views
RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise
Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?
The last two weeks one of our applications has had downtime due to ...
5
votes
1answer
275 views
Oracle aggregate functions slows query down massively
I have a table with ~12M rows of data in it. Here is the table structure:
SYSTEM_ID
BATCH_ID
MEASUREMENT_INDEX,
PARAMETER_ONE,
PARAMETER_TWO
The primary key is made up of the first three columns. I ...
5
votes
3answers
3k 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 ...
5
votes
3answers
138 views
How to optimize a query that's running slow on Nested Loops (Inner Join)
Consider the following simple query (only 3 tables involved)
SELECT
l.sku_id AS ProductId,
l.is_primary AS IsPrimary,
v1.category_name AS Category1,
...
5
votes
1answer
121 views
How do I find out who is the resource hog in SQL Server 2008?
We have a SQL Server 2008 installation on a machine with 24+GB of RAM and 16 cores. Plus fast drives, etc. A large machine by our standards.
Anyway, that machine is 100% devoted to SQL Server and ...
5
votes
1answer
621 views
Oracle's Merge v. Select->Update/Insert
What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?
I'm trying to optimize a database, but I haven't found any speed tests comparing the two.
I found ...
5
votes
2answers
252 views
Just how much is a query supposed to slow down the whole system?
This is similar to my earlier question but more generic. I have a system with 16GB of memory, 16 cores, supposedly quality hard drives, RHEL 5, MySQL 5.0.x. Yes, I know, we're working on upgrading ...
5
votes
1answer
3k views
Client times out, while MySQL query remains running?
We experienced an issue in which a read-only query, run through MySQL workbench, timed out from a user's UI perspective and remained running on the server (and apparently consuming more and more ...
5
votes
1answer
362 views
Indications from obscenely high Operator Costs in Query Plan?
I have a monthly ETL process that's too complicated to explain here, but basically we get a large amount of customer data, bulk load it into various tables, then run a series of build processes.
The ...
5
votes
3answers
312 views
MyISAM Performance: Join Decomposition
in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones:
Converting
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON ...
5
votes
2answers
372 views
Teradata: How to design table to be normalized with many foreign key columns?
I am designing a table in Teradata with about 30 columns. These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc. It is bad design to store ...
5
votes
2answers
349 views
Large accounting table, best way to partitioning by two dates
I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call ...
5
votes
2answers
1k views
What are the potential causes for a sudden MySQL slowdown?
A few days ago mysql became suddenly very slow with queries taking more than 10s to complete when they used to complete under 100ms... It lasted about an hour and then the speed was normal again. What ...
5
votes
1answer
3k views
MySQL INNODB SELECT query hanging on copying to tmp table
I have a query that is hanging (seemingly) randomly on a specific query. Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website ...
5
votes
1answer
292 views
MySQL and Solid State Disks on EMC
Since all writes and sequential reads are cached by the EMC storage system, the best use for solid state disk is to store data that has a random READ pattern. On Oracle, that is the temp tablespace ...
5
votes
1answer
96 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 ...
5
votes
1answer
361 views
NESTING_TRANSACTION_FULL latch
Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
5
votes
1answer
99 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 ...
5
votes
1answer
49 views
Sudden increase in log_file_sync waits
I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion.
Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
4
votes
3answers
330 views
Usage of SQL Server 2008 R2 Express. Please Advise
I'd like you all to give me hints and advise on the following scenario:
We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application.
The Web Application won't have ...
4
votes
3answers
703 views
For every insert statement I see sp_executesql in SQL Server Profiler
Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows.
I saw for every insert I called, a call was made to ...
4
votes
3answers
345 views
Some book suggestions for performance tuning
I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
4
votes
3answers
807 views
Is the ADO.NET Entity Framework known for generating poorly performing queries?
This question is inspired by the comment posted to the latest ServerFault blog post:
Are you guys still using LINQ to SQL?
I know I can use SQL Server Profiler and/or the ToTraceString method to ...
4
votes
5answers
1k views
Improve sort performance in PostgreSQL?
I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
4
votes
3answers
2k views
How to reduce the log file size without shrinking it in SQL server
In SQL server, how do you reduce the log files size without (DBCC) shrinking it. I know shrinking the log file will free up some space, but will also cause fragmentation. Doing a checkpoint in simple ...
4
votes
4answers
368 views
How much will using a varchar(15) PK affect my tables performance?
I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
4
votes
3answers
6k views
Optimizing InnoDB default settings
I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
4
votes
4answers
3k views
Mysql int vs varchar as primary key (InnoDB Storage Engine?
I am build a web application (project management system) and I have been wondering about this when it come to performance.
I have an Issues table an inside it there are 12 foreign keys linking to ...
4
votes
4answers
884 views
T-SQL INNER JOIN Optimisation
Well, I really have read so many articles, used different techniques and done some random things to do this.
My problem was that a have big tables (over one million rows) and some small (with few ...
