Tag Info

New answers tagged

0

Your expression is too complex. There are many severe limitations of what can go into BY RANGE. Use simply UNIX_TIMESTAMP(time). Even if it would work, FLOOR does not add anything to the expression. BTW, the trailing hh:mm:ss is optional in this situation. These are identical: UNIX_TIMESTAMP('2015-08-01 00:00:00') UNIX_TIMESTAMP('2015-08-01') Now, on ...


0

Don't know enough about SSDs to comment, but I have studied columnar stores and how traditional disk technology affect/are affected by this model. It's perfectly true that records in "OldSQL" (Google Michael Stonebraker and OldSQL) are not adjacent - this is entirely by design - the RDBMS hands placement of files over to the OS - hence fragmentation. (I'm ...


1

You can try this: SELECT m.vlanID, v.vlan_name, m.interfaceName, count(1) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType FROM macs m INNER JOIN network_devices n ON n.nd_ip_address = m.ipAddr INNER JOIN vlans v ON m.vlanID = v.vlan_id INNER JOIN ...


0

If you stop it (disable it), the index won't be available for queries using it. However you can set bchange tracking to manual: ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING MANUAL GO Update... ALTER FULLTEXT INDEX schema.table START UPDATE POPULATION GO ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING AUTO GO You can check the ...


1

Based on the explain image above, the query is doing a table scan. (possible key and key are Null). Rows_examined: 1,088,312,551 ( 1 billion rows examined, that's quite a lot, look like a cross join to me). The second query (not exist) might not be right as it will exclude all data FROM bot_sessions_statistics WHERE date_active >= date(NOW()) compare to ...


1

As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not. At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows. I guess your query will at least require ...


0

Create a table with a select * from a large table and where 1=0. Gather stats, save them using dbms_stats and then apply those stats to the large table. You will essentially tell Oracle that the large table has no data, which would favor full table scans. You can also try switching the optimizer mode to first_rows_1 or invalidating some indexes.


6

The semantics of the two queries are not the same - UNION removes duplicates, whereas the FULL OUTER JOIN will not: DECLARE @T1 AS table (id bigint NULL, val integer NULL); DECLARE @T2 AS table (id bigint NULL, val integer NULL); INSERT @T1 (id, val) VALUES (1, 1); INSERT @T1 (id, val) VALUES (1, 1); INSERT @T2 (id, val) VALUES (1, 1); INSERT @T2 (id, val) ...


1

work_mem Obviously, the sort operation spills to disk: Sort Method: external merge Disk: 36224kB More work_mem can help the query, like @Kassandry already suggested. Increase the setting until you see Memory instead of Disk in the EXPLAIN output. But it's probably a bad idea to increase the general setting based on one query. Proper setting depends on ...


1

One of the easiest and most effective ways to increase performance on this sort of query is to execute SET work_mem=40MB (because you have ~32MB of temp file for sorting, and a little extra often helps) then run your query, and see if the EXPLAIN ANALYZE plan changes from disk to an in-memory sort. Afterwards, run RESET work_mem to put the value back to the ...


1

Depending on how selective the combined predicates are, I would imagine a good index for this particular query would be: CREATE INDEX index_name ON products (above_revenue_average ASC, start_date DESC) WHERE status > 100 AND category_id <> 5; The SELECT * is potentially problematic because the index above does not contain all columns. ...


0

Create the temp table as follows # # Make Temp Table # CREATE TABLE mydiff SELECT name,type FROM AA WHERE 1=2; ALTER TABLE mydiff ADD COLUMN diffcount INT DEFAULT 1; ALTER TABLE mydiff ADD PRIMARY KEY (user,type); # # Load Unique Keys From Table AA # INSERT IGNORE INTO mydiff (user,type) SELECT user,type FROM AA; # # Load Keys From Table BB # INSERT INTO ...


0

Create the result table, my_diff, without index, and when it is populated, add the desired index. Make sure you have an index on (name, type) in both tables. i.e. a composite index It is not a good idea to have primary key on all fields. This is especially true if you are using InnoDB engine. Instead, alter the table to add an integer auto increment field ...


0

1M/year is 2 per minute. You can do a lot of updating in 30 seconds. 1M/day (12/second) would be more exciting. Do you need to rerun the summary queries? Can't you just do a subtract via an UPDATE? Possibly fast enough to be synchronous. How often do you need to post a correction? If it is rare, consider adding a row to the summary table with a ...


2

If Shanooooon's suggestion is not enough, then SELECT (stuff from x, plus picture stuff) FROM ( SELECT ... (everything except `picture` stuff) LIMIT 30 ) x LEFT JOIN pictures ON x.picture_id = pictures.id This helps because it will reach into pictures only 30 times, not 1000 times.


0

It is more efficient to do a table scan rather than use an index in the case where you need to touch more than 20% of the rows. The optimizer knows this and uses it to good advantage. (The "20%" varies with the phase of the moon.) Don't use a 4-byte INT for a flag; use a TINYINT UNSIGNED. In product_has_category, don't have an id, instead use PRIMARY ...


2

Instead of using a sub query you could use a LEFT JOIN. That way you negate the need for a sub query which is being looked at for each image. LEFT JOIN seen ON seen.image_id = images.id AND seen.user_id = $user_id Then only get rows that haven't been joined. WHERE seen.id IS NULL


1

You can create a plan guide as sysadmin for the query, which would then allow the user to run the pre-existing plan. Otherwise, rewriting the query is about all you can do. Using QUERYTRACEON in plan guides


2

You can use Adam Machanic's SP_WHOISACTIVE to get the details tracked. One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval. This usage is documented. Please see on how to collect ...


1

You need to use the explain facilities in DB2 to answer this question, it's highly specific to the actual queries you are executing, and you can't get a general answer. IBM i 7.2 (and previous versions) have Visual Explain. If you are restricted from doing this, then you need to speak to the system or database administrator for your environment.


1

If you are still inserting data for 2014, then you risk problems with this method, because rows inserted between steps 2 and 5 are going to end up getting dropped rather than moved. If you are not still inserting data for 2014, then I think you should change step 5 to "rewrite the trigger to throw an error upon insertion of 2014 data" and move it up to be ...


3

How can I check whether the process is actually doing something? For the session 65, process is suspended because its waiting on a process to complete. That wait info itself is highlighted there as PAGEIOLATCH_SH.. This means from MSDN Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in ...


0

CREATE TABLE `genescorrelation` ( `correlation` FLOAT NOT NULL, `gene1_id` SMALLINT unsigned NOT NULL, `gene2_id` SMALLINT unsigned NOT NULL, PRIMARY KEY (gene1_id, gene2_id), KEY (gene2_id, gene1_id, correlation) ) ENGINE=InnoDB; and reload. I am assuming that FLOAT, with about 7 significant digits is more than enough for a ...


1

The problem as shown is transforming relational calculus, of which SQL is a variant, into relational algebra, which consists of the original operators Codd defined on relations. I will assume that the EMP, ASG, and PROJ represent employees, projects, and the assignment of employees to projects. The query, as stated in relational calculus, is asking for the ...


1

Looking at the query SELECT gene1_id, AVG(correlation) AS avg FROM genescorrelation WHERE gene2_id IN (176829, 176519, 176230) GROUP BY gene1_id ORDER BY NULL I have four(4) suggestions SUGGESTION #1 I see three(3) columns. I recommend making a covering index ALTER TABLE genescorrelation ADD INDEX gene2_gene1_correlation_idx1 ...


1

Why don't you call a stored procedure via NHibernate instead of asking it to query the view? That would allow you to write your own query and tune as needed.... I don't know NHibernate very well but this blog post looks like a reasonable resource. In case that link dies here are the basic steps: Write your stored procedure to select the data in the most ...


0

You can make use of the dmv's sys.dm_exec_query_stats and sys.dm_exec_requests to analyse the resource consuming queries along with their duration for what long: Use the query below to capture the same: -- Execute the query inside target database SELECT TOP 10 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, ...


0

You can split the query by each <table>.lastmodifieddate > '2015-07-01' and then use UNION to run them all at once. Regarding some comments about indexing - indexes are crucial to fast query execution, it is not about complexity of a table but about number of rows - small table can be easily kept in memory but large one cannot so index can help to ...


0

I suggest you to try with the APPLY join operator. It is useful when you have to join tables where one table has much more rows than the other tables. it's taking an eternity because the large size of the resultsets Which indexes are involved on the execution plan of the query? Can you post the execution plan used for the original query? Thanks


4

You can create a function like this; CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp ( time_a timestamp, time_b timestamp ) RETURNS TABLE ( recipient varchar, "timestamp" timestamp ) AS $$ BEGIN RETURN QUERY SELECT DISTINCT ON (m.recipient) m.recipient, m."timestamp" FROM messages m LEFT JOIN ...


9

This query: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; says: For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and ...


0

Remove the 4 left joins. You are not using their data and they are not needed. It slows everything down, especially if you don't have proper PK and indexes (tell us more) parameter_table.id should be a PK and/or have 1 or 2 indexes on it such as (it really depends on your model): create index idx_parameter_table_id0 on parameter_table(id) where col1 = 0 ...


1

From your query plans, it looks like you're comparing ints to ints in the first query plan, and int to numeric in the second plan. Your first compare: Index Cond: (("timestamp" >= 1431100800) AND ("timestamp" <= 1431108000)) and timestamp >= 1431100800 and timestamp <= 1431108000 In the second query, it's numeric values: Filter: ...


2

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query. Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event. UPDATE: To prove the query ...


5

Which option from above will perform better? Best case, both will produce exactly the same execution plan, with the same runtime performance. This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from ...


2

I show some important things about views in my talk at http://bit.ly/Simplification - the key thing would be to make sure that you're not doing needless joins, that they get optimised out when you don't need those columns. My talk generally covers the idea of modularisation for an interface for developers, so it's probably quite useful.



Top 50 recent answers are included