Hot answers tagged optimization
17
You are falling into the "Catch-All Query" trap, which is explained very well by Gail Shaw here.
To summarize the problem: SQL Server optimizes away the significant overhead of query compilation by caching a query plan after compilation, and then later checking the cache for a matching query plan before a later compilation. The "matching" that occurs here ...
10
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
consider every possible plan and select the best from all
alternatives?
There is, but I don't publicise it because it would be misunderstood and ...
8
There is no knob or trace flag that I know of to coerce this behavior in any way (though Paul White mentions some trace flags here that provide more visibility and allow you to coax some behavior deltas).
Microsoft provides plenty of weapons but this one would almost be guaranteed to be pointing squarely at your own feet 100% of the time. When running a ...
6
There is no guaranteed way to force SQL server to execute your clause conditions in a specific sequence. The optimizer will always evaluate them in the order it sees fit.
What you can do is something like this:
IF @LinkMode IS NULL
BEGIN
select ...
from ...
WHERE (myColumn IN (...very long time exeted query...))
...
...
END
...
5
Use correct ANSI group by (not the MySQL abomination extension) and see what happens
select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by name,gender,dob,country
having sum(score)>=1000
order by sum(score) desc limit 50
Why?
GROUP BY in ...
4
You might want to consider moving the data outside the database if:
You are having problems with backup durations
It causes disk or network throughput problems on your database server
You have a requirement to access the data without using the database
In general, I'd suggest keeping it in the database unless you can come up with a good reason not to.
...
3
Things to try:
Adding an index on (user_id, date, score)
Group by only on scores table and then join to users:
SELECT s.total, u.name, u.gender, u.dob, u.country
FROM users AS u
JOIN
( SELECT user_id, SUM(score) AS total
FROM scores
WHERE date >= '2012-01-01' AND date < '2012-02-01'
GROUP BY user_id
HAVING SUM(score) >= 1000
...
3
I got consistent results in my repeated tests with various versions over the last years:
count(*) is slightly faster count(pk). It is also shorter and most of the time it better fits what is tested (the existence of a row).
Concerning:
Is Postgres smart enough to pick up that a SERIAL PRIMARY KEY is going
to exist in every row and never be false
The ...
2
I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column.
From the manual
Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper ...
2
Two hints for you:
The KEY uid is redundant, because it is covered by the PRIMARY KEY
40,000 rows at a time might make for too large a transaction. Although these are very small rows (two INTs) this may cause the transaction to go to disk, depending on your settings. I usually go with around 1,000 rows at a time (I go as low as 100 and as high as 10,000).
...
2
OPTIMIZE TABLE basically does three(3) things
Shrinks the data pages
Shrinks index pages
Computes Fresh Index Statistics
Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable
USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp ...
2
I have recently discovered a fantastic free script from the people at BrentOzar Unltd
http://www.brentozar.com/blitzindex/
This does some good analysis of which indexes exist, how often they are used and how often the query engine is looking for an index that doesn't exist.
It's guidance is generally good. Sometimes it gets a bit over-suggestive of ideas. ...
2
The best index for this query is (u1_id, t)
My initial guess was right, that you have indexes on (t) alone and on (u1_id) alone. You haven't told us the exact EXPLAIN output (which index is used), so the most probable explanation is that mysql is choosing to use one of these existing indexes or none at all (doing a full scan of the table), which yields the ...
1
Yes, it will make a difference. How big a difference will depend both on how many rows the table has and how many columns as well as how frequently the table is read from and updated. Depending on the database, the block size and how many rows are updated at the same time could also be factors.
Keep in mind the overhead you are introducing by splitting ...
1
Here are some of my inputs to stand "FOR OUTSIDE DB"
It purely depends upon your need. If your business wants to keep them always available and recoverable with no down time then you might have to think a proper option to keep them available 24/7, but keeping them in Database the capacity management of DB server will be in question.
If no priority for your ...
1
Dynamic SQL would probably work too, since in that case the query optimizer should get the actual values at run-time (do correct me if I'm wrong, I'm actually not sure but seem to remember using it for similar situations). But I'm with the others in this one, in that an IF / ELSE clause would serve you best, as it's the simplest and easiest solution that ...
Only top voted, non community-wiki answers of a minimum length are eligible
