New answers tagged optimization
0
Rate Per Second = RPS
Suggestions to consider for your my.cnf [mysqld] section
thread_cache_size=100 # from 9 to support your 86 max_used_connections (concurrent)
innodb_io_capacity=1900 # from 200 for higher IOPS with your SSD devices
read_buffer_size=256K # from 128K to reduce handler_read_next RPS of 402 for 6 days
innodb_buffer_pool_size=2G # from ...
1
SELECT username, email, achID, SUM(points)
FROM users
NATURAL JOIN userAchievements
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email, achID
WITH ROLLUP
HAVING GROUPING(username) + GROUPING(email) + GROUPING(achID) IN (0, 3)
fiddle
is there a way to get the output like this? 3 columns "username ...
1
you can join on table_C
SELECT nameC, nameA, nameB
FROM table_Ref r
INNER JOIN table_A a ON r.idA=a.idA
INNER JOIN table_B b ON r.idB=b.idB
INNER JOIN table_C c ON a.idC=c.idC;
0
In this case you can use FULL OUTER JOIN in INNER JOIN
what can happen is to have blanck fields in the final table due to data that are not in all tables
3
Use TYPE Directive in FOR XML Queries
SQL Server support for the xml (Transact-SQL) enables you to optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive.
-- 1st
DECLARE @AuditParameters XML = (
SELECT
1 AS AccountID,
2 AS CategoryID,
3 AS CategoryAttributeID,
...
4
Add TYPE to return XML from the inner query rather than a string
DECLARE @AuditParameters XML = (
SELECT
1 AS AccountID,
2 AS CategoryID,
3 AS CategoryAttributeID,
'4' AS SyncBatchGUID
FOR XML PATH(N'Parameters'), ELEMENTS XSINIL, TYPE
)
-1
I think you are computing Avg and count twice in select and having clause.
Instead, you can use efficacy and uses in having as
HAVINg eefficacy >0 AND uses>=10
3
You could try applying the filter
(WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104'))
Directly on the index instead of it being implemented as a filter further in the execution plan.
You can do this by not applying functions on columns in your where clause and adding an index. The group by is also ...
0
With the CTE row_number approach, try creating a nonclustered index on StationId, ParameterId, DateTime desc. I've found having an index with the proper sort order that my partition by order clause uses has improved performance for me.
0
If the performance is really critical and you are about frequently ask your table for the most recent date... Why not create lookup table of station and parameter as key with most recent time stamp. You need to update this table each time you modify the big one, but this way you have your results when you need it I milliseconds.
0
In addition to what Luuk says, observe how simple the combined query is:
SELECT name, address
FROM users
WHERE username = '...'
AND passwordHash = '...;
Then if you get a row (the 97% case), you are finished. If you get no row, it is either a bad username or bad pwd. (If you need to distinguish between those two cases, then it gets a little ...
2
Rule of Thumb: A disk hit on HDD takes about 10ms (100 hits/second). SSD access might be 10 times that fast.
"0.1-0.6" on HDD -- perhaps 10 to 60 blocks needed to be fetched. That might be only one for the index, then several for the data. Perhaps there are at least 60 rows in the resultset?
You say that the slow version is "remote". This might add a ...
23
If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this:
select StationID, ParameterID, m.DateTime LastDate
from StationParameter sp
cross apply
(
select top 1 DateTime
from MyTable
where StationID = sp.StationID
and ParameterID = sp.ParameterID
order by DateTime desc
) m
To enable ...
0
The answer We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3% is at softwareengineering.stackechange.com
2
There are two problems
PROBLEM #1 : MyISAM
MyISAM does not cache data (stored in the .MYD) which means data must be read for disk every time. Indexes may be cached (read once from .MYI), but not data.
See my old post in What are the main differences between InnoDB and MyISAM? under the MyISAM subheading
PROBLEM #2 : HDD vs SSD
An HDD must use spindles ...
1
In the plans you posted we can see it's transferring more records (more network I/O), and it's also converting both the arguments you deliver to it, as well as converting the output upon inserting.
By the looks of it the remote DB has it's field as a N(VAR)CHAR instead of (VAR)CHAR. So when you're quering it using VARCHAR expression, it first has to convert ...
0
Another possibility is to use an index you already have that is correlated with the StartDate.
For example, if you have an IDENTITY column as your primary key, and StartDate is filled in as GETDATE() when a new row is added and never updated, then you can get pretty much the same result with something like this:
SELECT
StartDate AS Expr1
FROM
[...
1
Without seeing the query plans we can't answer in detail. There is likely a query hint you can use to encourage the query planner to use the alternate plan.
Another option is to control it from the other side: create a view in the remote end that does the work and select directly from that at the local side.
Update Once Query Plans Presented:
The warnings ...
0
I used to use MySQL on a 256MB, non-dedicated, machine. But, thanks to OS bloat, more MySQL/MariaDB features, etc, 512MB may be the minimum these days.
If you set things too high in my.cnf, there will be swapping, which is terrible for performance.
When using a tiny VM, decrease, don't increase, things in my.cnf.
The main thing to decrease is ...
-4
Easiest way is to create an index that supports your query.
As you mentioned there is no supporting index. I suggest to use query hint option(maxdop noofcpucore) to make your query go parallel, where noofcpucore = numeric value, equal to the number of cpu cores that will be used by the query (for example 4).
1
It's a bit hard to tell how your tables are linked together, but I think the following should be more efficient:
WITH weeks AS (
SELECT make_interval(weeks => n) as week
from generate_series(0, 3) AS n
)
SELECT week,
count(x.user_email) as "Returning Users"
FROM weeks
LEFT JOIN (
SELECT act.user_email, age(act.timestamp, acc.timestamp)...
0
Overnormalized -- It is rarely worth it to split up an address and normalize each piece.
You have a company_address plus separate columns for city, state, etc. If these are redundant, that is a no-no.
(Style nitpick): Don't prefix column names with the table name; it clutters the queries.
INT is always a 4-byte quantity; the (100) in INT(100) means nothing....
0
Try Below Index :
CREATE INDEX IDX_CMB ON wp_posts(ID,post_type,post_date);
Basically, this index will used for indexing GROUP BY clause WHERE clause, create this index & check explain plan & execution time.
1
Looking at the execution plan, you can see that every query is done as a different query. Again, if you want to see if those query are using parallelism, you will see it in the execution plan.
If each of the query can go parallel, then they will.
Running this query agains StackOverFlow2013 DB:
create table #tbl (tbl varchar(30));
insert into #tbl ...
1
I think the Optimizer works something like this:
It can find the first row: MIN(name) WHERE foo=123
It can find the last row: MAX(name) WHERE foo=123
Those are done via a drill-down in the BTree, and assumed to be reasonably cheap. If there are a lot of rows for foo=123, then this is likely to skip over some blocks. Note: fetching blocks potentially ...
Top 50 recent answers are included
