In the context of a database, optimisation refers to the process of the query optimiser selecting the best query plan to execute.
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
10
votes
3answers
2k views
Does “WHERE 1=1” usually have an impact on query performance?
I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).
...
9
votes
5answers
371 views
Force sql server to run query conditions as written?
Im using Sql server 2008 R2 And I have this pseudo query (SP) :
select ...
from ...
WHERE @LinkMode IS NULL
AND (myColumn IN (...very long time exeted query...))
...
...
The ...
9
votes
4answers
796 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
9
votes
5answers
1k views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
9
votes
6answers
1k views
Suddenly Slow Execution Plan for Stored Proc
I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, ...
9
votes
2answers
223 views
How many Sproc Parameters is too many?
I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
9
votes
4answers
5k views
Logical operators OR AND in condition and order of conditions in WHERE
Let's examine these two statements:
IF (CONDITION 1) OR (CONDITION 2)
...
IF (CONDITION 3) AND (CONDITION 4)
...
If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will ...
8
votes
2answers
494 views
Optimization: Moving variable declarations to the top of your procedure
While working on optimizing some stored procedures, I sat down with the DBA and went through some sprocs with high blocking and/or high read/write activity.
One thing the DBA mentioned was I should ...
8
votes
4answers
925 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
1answer
668 views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application ...
8
votes
1answer
795 views
Why is Clustered Index Scan Number of Executions so high?
I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time.
The only difference between ...
7
votes
2answers
136 views
Is there any way to prevent the memo structure from being pruned?
We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just ...
7
votes
2answers
116 views
Optimization problem: compound clustered keys, flag conditions and index-merge
Three tables:
product: with columns: ( a, g, ...a_lot_more... )
a: PK, clustered
g: bit-column
main: with columns: ( c, f, a, b, ...a_lot_more... )
c: PK, clustered
f: bit-column
(a, b): UQ
...
6
votes
2answers
301 views
Data Compression Through Base36 Encoding
I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type.
I'm thinking about using Base36 encoding to accomplish this. Are ...
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
3k views
Avoiding temporary tables while sorting by a column in a different table
So I have this MySQL InnoDB query here:
SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt
FROM stories s
LEFT JOIN votes v ON s.vid = v.vid
WHERE s.lv = 1
AND s.status = 1
GROUP BY s.vid
...
6
votes
2answers
789 views
How do Application Like Yelp Retrieve distance information from data base efficiently
For example, say I have a table:
Business(BusinessID, Lattitude, Longitude)
All are indexed of course. Also there are 1 million records
Say I want to find businesses closest to 106,5, for example, ...
6
votes
2answers
111 views
Is there a good “rule of thumb” for translating EXPLAIN cost to (wall-clock) runtime?
From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have ...
6
votes
2answers
268 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
2answers
6k views
When to use views in MySQL?
When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?
One reason that I would prefer to use views is that the database schema ...
6
votes
1answer
146 views
On-the-fly query/command modification
I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly ...
6
votes
2answers
215 views
How should I Optimize storage for this table?
I'm using MySql to store a basic table of this format:
id int(11) //Auto-Incrementing ID
data varchar(5120) //Random input data, compressed by a program, not mysql.
...
5
votes
5answers
401 views
Does SQL Sentry Plan Explorer work?
Does SQL Sentry Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about?
It looks like it shows the hot path in color commpared to SSMS's ...
5
votes
3answers
396 views
Does SQL server optimize or pre-parse stored procedures?
Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored ...
5
votes
4answers
982 views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
5
votes
1answer
594 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
2answers
549 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
1k views
Insert-heavy InnoDB table won't use all my CPU
I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
5
votes
2answers
1k views
REBUILD - Clustered Index, TABLE, or both?
I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here.
I have a very large table that we had to add a column to. The clustered index is ...
5
votes
2answers
289 views
How recording of every change of a row in a database is generaly stored?
In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
5
votes
1answer
2k views
Benefits of running the OPTIMIZE TABLE Query in MySQL DB Server
I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server.
I checked this once and found that after this is run, ...
5
votes
3answers
165 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
5
votes
1answer
135 views
Walking a BTREE index as far as possible in MySQL
Suppose one has a column of words on which one builds a BTREE index:
CREATE TABLE myTable (
words VARCHAR(25),
INDEX USING BTREE (words)
);
LOAD DATA LOCAL INFILE '/usr/share/dict/words' INTO ...
5
votes
2answers
248 views
How to use merge hints to isolate complex queries in SQL Server
I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
5
votes
1answer
95 views
Determining which isolation level is appropriate
This is a homework question.
For the following transactions state the isolation level that will
maximize throughput without lowering the integrity of the database.
Explain the answer.
...
5
votes
1answer
251 views
Query performance while using sqlcmd [duplicate]
Possible Duplicate:
Running queries with SQLCMD vs. Running queries with SSMS
I have a number of scripts that query the data from different tables and consequently update them. I have ...
4
votes
4answers
587 views
Where to look for a bottleneck after elimitating CPU, memory, and disk
I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run ...
4
votes
2answers
542 views
Is there a way to hint to query optimizer to MySQL which constraints should be done first?
This is my current query:
SELECT BusinessID as ID,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
(
SELECT *
FROM
...
4
votes
4answers
251 views
SQL Server Optimization/Configuration for Inflexible Application
I'm responsible for overseeing an application which uses SQL Server as its backend storage solution. Unfortunately, the application has no concept of how a RDBMS should be utilized and seems to use it ...
4
votes
2answers
938 views
Why is this query not using my nonclustered index, and how can I make it?
As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default.
This query runs in about 2.5 seconds:
SELECT TOP 1000 * FROM ...
4
votes
1answer
106 views
Does combining columns into 1 column helps to optimize a large table?
I have user, items and user_items tables, and as usual user has many items.
Tables contains:
User: ~50K
Items: ~3.5M
UserItems: ~5M
I am using MySQL and most of my queries are selecting items of a ...
4
votes
4answers
259 views
Do cross-joins get optimised by the client library?
Note This is deliberately database agnostic, I'm interested in how different implementations, er, differ.
I came across Joins are for lazy people on SO, and it's made me question an assumption I'd ...
4
votes
1answer
88 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
4
votes
2answers
120 views
Optimizing my update sequence
Whenever a user updates his profile, we conduct a set of queries like so:
DELETE FROM `user_likes` WHERE `ID` = $id
INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name)
Is ...
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
4
votes
1answer
128 views
250 billion rows - computing interconnectedness on a really huge scale
I have a table of approximately 700k rows, each identified by a unique itemnumber.
Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical ...
4
votes
2answers
233 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
4
votes
1answer
752 views
MySQL reads / writes per table
I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives.
Is there a way ...
4
votes
2answers
228 views
Bulk Data Loading and Transaction Log
im currently working on a project which bulk import data from flat files(csv) about 18 differents files each linking to a specific table through some store procedure.
I followed the steps as advised ...
