Tag Info

Hot answers tagged

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 ...


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) ...


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 ...


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 ...


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 ...


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.


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 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 ...


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 ...


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


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. ...


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 ...


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: ...


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 ...



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