Hot answers tagged

61

"I'm more wondering why the query optimizer would ever use the plan it currently does." To put it another way, the question is why the following plan looks cheapest to the optimizer, compared with the alternatives (of which there are many). The inner side of the join is essentially running a query of the following form for each correlated value of ...


38

The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction: SELECT key, value FROM tableX GROUP BY key ; In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7: SELECT key, ANY_VALUE(value) AS value FROM tableX ...


29

Boolean logic - or Three valued logic IN is shorthand for a series of OR conditions x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL) ... is the same as x <> 1 AND x <> 2 AND x <> NULL ... is the same as true AND true AND unknown ** ... = unknown ** ... which is almost the same as false in this case as it will not pass ...


22

When I run your script to create a statistics only database and the query in the question I get the following plan. The Table Cardinalities shown in the plan are tblFEStatsPaperHits: 48063400 tblFEStatsBrowsers : 339 So it estimates that it will need to perform the scan on tblFEStatsPaperHits 339 times. Each scan has the correlated predicate ...


20

In my book even one scan of 50M rows is unacceptable... My usual trick is to materialize the distinct values and delegate the engine with keeping it up to date: create view [dbo].[vwFEStatsPaperHitsBrowserID] with schemabinding as select BrowserID, COUNT_BIG(*) as big_count from [dbo].[tblFEStatsPaperHits] group by [BrowserID]; go create unique clustered ...


20

To get a count for each of those you can try SELECT COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1, COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2, COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3 FROM `table1`;


18

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this: DECLARE @x TABLE ( id INT, row_num INT, customer_code VARCHAR(32), ...


17

select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0), sum(SnapShotValue) from YourTable group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0) SE-Data datediff(minute, 0, CaptureTime) gives you the number of minutes since 1900-01-01T00:00:00. dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0) adds the number of minutes ...


16

Similar to Aaron's solutio, shorter syntax: SELECT SUM(col1 LIKE '%something%') AS count1, SUM(col1 LIKE '%another%') AS count2, SUM(col1 LIKE '%word%') AS count3 FROM `table1` The LIKE expression makes for a boolean result. TRUE is 1, FALSE is 0, so the CASE is redundant here.


15

Whether the two queries you posted are logically equivalent is irrelevant; you shouldn't use either of them. I will try to steer you away from a couple of things: Whenever possible, try to avoid applying functions to columns. It's always as good, and mostly better, to keep those calculations against constants and not columns - this can destroy SARGability ...


13

A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the page already existed in the buffer cache. Consequentially, logical reads is a measure of how many times pages were actually touched in memory during query ...


12

After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution: SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLS.COLUMN_NAME, CONS.R_CONSTRAINT_NAME, CONS_R.TABLE_NAME R_TABLE_NAME, ...


12

SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary. It is also an interval with two ends rather than one. Writing BETWEEN is the same as writing two separate predicates with >= and <=. Interval with ...


11

No, you don't need to gave the columns in the same order. Not least, table order may not reflect actual on-disk order (this is 100% true for SQL Server, and I'm sure MySQL is the same) Unless your OCD itch needs scratched


11

I believe you're looking for the tool tablediff which lets you do exactly that - compare two replicated tables for differences. You might find this article useful, to get started. Here is a GUI for tablediff


11

The two queries are logically identical and do produce the same plan. The simplification phase of the Query Optimizer handles this. They're identical because of the constraints that are on the tables - foreign keys, uniqueness, nullability...


11

To find gaps in a number range: Test table and data: mysql> CREATE TABLE wp_blogs -> ( -> blog_id INTEGER -> ); mysql> insert into wp_blogs values(1); mysql> insert into wp_blogs values(2); mysql> insert into wp_blogs values(4); mysql> insert into wp_blogs values(6); mysql> insert into wp_blogs values(7); mysql> ...


11

One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join: SELECT * FROM user_branch T1 LEFT JOIN dimcustomer2 T2 ON T1.BRANCH_CODE = T2.BRANCH_CODE INNER JOIN customer_guarantee T3 ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM Let us assume that some rows in T1 have no matches in T2. More ...


10

The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g. SELECT ListA.*, ListB.* FROM ListA LEFT JOIN ListB ON ListA....


10

Rowstore The sort spill itself can probably be addressed by enabling trace flag 7470. See FIX: Sort operator spills to tempdb when estimated number of rows and row size are correct. This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to ...


9

CONNECT BY is the correct way to handle data that is naturally recursive. I don't know what your table looks like but maybe something like: SELECT * FROM some_table st START WITH st.location = 'BLDG-01' CONNECT BY PRIOR st.location = st.parent; This should get nodes under "BLDG-01". The START WITH clause is your base case. Another explanation (aside ...


9

Similar to @ypercubes but to get one row without 3 separate queries select count(CASE WHEN pub_id = '1389' THEN title_id END) as algodata, count(CASE WHEN pub_id = '0877' THEN title_id END) as binnet, count(CASE WHEN pub_id = '0736' THEN title_id END) as newmoon from titles where pub_id IN ('1389', '0877', '0736') Also, decide if your ...


9

For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters. select a.myVal, b.myVal from MyTable a join MyTable b on b.id = a.id where a.OtherVal = 100 and b.Otherval = 200 For performance, ensure you have proper indexes. In this limited example, ideally you ...


9

There might be other better ways but does following work for you? select client_id ,service_date ,other_stuff from ( select client_id ,service_date ,other_stuff ,row_number() over (partition by client_id order by service_date) rn_min ,row_number() over (partition by client_id ...


9

You can not select aggregates across a field if you don't include the field in the group by list. If you want the totals per year you should write SELECT year,sum(mark),sum(maxmark) FROM table1 GROUP BY year If you want the totals per parameterno it should be SELECT parameterno,sum(mark),sum(maxmark) FROM table1 GROUP BY parameterno From the error ...


9

It is not clear to me what you are asking but I believe that GROUP BY is one of the most misunderstood concepts in SQL, so I'll add this answer anyhow. It may or may not help with the understanding of the concept GROUP BY. Assume we have a table like: CREATE TABLE T ( YEAR INT NOT NULL , PARAMETERNO INT NOT NULL , MARK INT NOT NULL , PRIMARY KEY (YEAR, ...


9

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value. Consider changing the execution of sp_send_dbmail to this: SET @usage_data += ' end of data' exec msdb.dbo.sp_send_dbmail @profile_name='some_mail_profile', @recipients=@admin_list, @subject='Mail from sql server', @body=@usage_data, @importance=...


9

I'm afraid the phrase "logical execution" does not make much sense; query execution by definition is physical materialization of a result set. I think what you mean by "logical execution" is the query compilation, the phase where the query syntax and semantic meaning is analyzed and the query plan is prepared to implement said semantic meaning. Joined ...


8

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply: SELECT p.* FROM products AS p INNER JOIN tag_ties AS tt ON tt.ref_id = p.id AND tt.tag_id IN (10, 11, 12) GROUP BY p.id HAVING COUNT(p.id)=3 Extending this idea, we can also query based on the tag ...


8

LISTEN / NOTIFY for PostgreSQL http://www.postgresql.org/docs/current/static/sql-notify.html in the database... NOTIFY static_channel_name, 'static-message'; or in a function/trigger: perform pg_notify('dynamic-channel-name', 'dynamic-message'); in the database client: LISTEN some_channel_name; --note the lack of quotes The LISTEN client will receive ...


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