Tag Info

Hot answers tagged

8

This is a frequent problem with poorly chosen clustered keys. Time series in general should be organized by time, since most queries ask for time ranges. Case in point, your query. If you have a correlation between id and timestamp then you can add an appropriate id based predicate: SELECT TOP 10 * FROM messages m INNER JOIN ...


5

I wrote a series on SQLServerCentral about baselines that might be of interest to you: http://www.sqlservercentral.com/Authors/Articles/Erin_Stellato/351331/ And as Shawn so kindly mentioned, I also have a Pluralsight course. If you have more questions, feel free to contact me (erin at sqlskills dot com). Erin


4

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run. Swapping should be a no-go for MySQL. Make sure to tune your ...


3

Here is a suggestion: add an index on (seriesName, retreivalTime) and try this query. It won't be super fast but probably more efficient than what you have: SELECT d.dbId, d.dlState, d.retreivalTime, d.seriesName, <snip irrelevant columns> FROM DataItems AS d JOIN ( SELECT seriesName, MAX(retreivalTime) ...


3

An index can be used to optimize the GROUP BY, but if the ORDER BY uses different columns, the sorting cannot use an index (because an index would help only if the database would be able to read the rows from the table in the sort order). A COLLATE NOCASE index does not help if you use a different collation in the query. Add a 'normal' index, or use GROUP ...


2

Assuming you have 300 Bytes per row, that makes a whopping 95GB per three weeks - that's not very much in today's terms - a 1TB disk would last 30 weeks - that's almost 1/2 of a year. If you compressed this data, I'm fairly sure that you could store at least a couple of years (possibly a lot more) on a single 1TB disk. I would keep the "live" data on one ...


2

Troubleshooting Performance It is all about the queries. You need only three bits of information about your queries: CPU, Duration & Reads. SELECT TOP 50 qs.creation_time , qs.execution_count , qs.total_worker_time as cpu , qs.total_elapsed_time as duration , qs.total_logical_reads as reads , t.[text] FROM sys.dm_exec_query_stats qs CROSS APPLY ...


2

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong). Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages: The optimizer has ...


1

You have 2 temporary tables being created. It is highly likely that the big difference comes from the fact that MEMORY, the engine used by MySQL to create temporary tables in, well, memory does not support TEXT/BLOB data types (independently of max_heap_table_size), so they are forced to be created on disk, having a great slow down . Profiling your query as ...


1

See this article about the performance problems and possible solutions using ORDER BY ... LIMIT. I would create an index on ord.ID DESC and remove the subquery. This is assuming that ID is not a primary key and indexed already. SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...



Only top voted, non community-wiki answers of a minimum length are eligible