Right outer join seems to not work because of where clause filter I have the following query, I need to modify it, so that it gives me the result for originDocForCurrency.id = 68 if there is no matching for commission.referenzid = 68. I tried the RIGHT OUTER JOIN ON commission.referenzid = originDocForCurrency.id, but it don't give results, if no record is found with commission.referenzid = 68.

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
 (SELECT originDoc.poswert FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCommission,
 (SELECT originDoc.r_art FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocClass,
 (SELECT originDoc.waehrung FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
RIGHT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
    ON commission.referenzid = originDocForCurrency.id
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68
    AND
    ( commission.referenzid = 68
        AND commission.btyp = 7
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )
    OR
    ( commission.referenzid = 68
        AND commission.btyp = 8
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )

How can I modify the query, to get the desired result? Can I achieve the desired result maybe with a CASE in the WHERE clause?

To simplify your query slightly you have basically the following:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
RIGHT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
    ON commission.referenzid = originDocForCurrency.id
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

Where you have no match in [src_boss_entwicklung].[dbo].[tckopf] the column commission.referenzid will be NULL, meaning this condition:

AND (commission.referenzid = 68 OR commission.referenzid = 68);

will not be true for these rows (because NULL = 68 evaluates to NULL). This effectively makes your RIGHT OUTER JOIN an INNER JOIN

I have never been a big fan of RIGHT JOIN especially when LEFT JOIN appears in the same query. I would rearrange the query to make all the outer joins left outer joins for continuity purposes:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68
AND (commission.referenzid = 68 OR commission.referenzid = 68);

This does not solve the problem though since you still have the possibility of commission.referenzid being NULL and turning the LEFT OUTER JOIN into an INNER JOIN. You can get around this by moving the predicates to the JOIN conditiion:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND
    ( commission.referenzid = 68
        AND commission.btyp = 7
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )
    OR
    ( commission.referenzid = 68
        AND commission.btyp = 8
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL )
    )

I am also fairly certain this condition can be simplified to:

SELECT *
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1

Making your final query:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
 (SELECT originDoc.poswert FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCommission,
 (SELECT originDoc.r_art FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocClass,
 (SELECT originDoc.waehrung FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.referenzid = 68
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;

ADDENDUM

As pointed out in a comment, you don't need the three subselects, you can just use the columns in the table aliased originDocForCurrency:

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
    originDocForCurrency.poswert AS originDocCommission,
    originDocForCurrency.r_art AS originDocClass,
    originDocForCurrency.waehrung AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
    ON commission.referenzid = originDocForCurrency.id
    AND commission.btyp in (7, 8)
    AND ISNULL(commission.storno, 0) <> 1
    AND ISNULL(commission.proforma, 0) <> 1
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68;


A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad? I have the following tables in my postgres database

- power_relations        - (id, properties)
- power_relation_members - (id, power_relation_id, member_id, member_type)
- power_lines            - (id, geom)
- power_nodes            - (id, geom)

A power_relation can have multiple members which can either be power_nodes or power_lines. I use power_relation_members to track this polymorphic many-to-many relation.

The query I need to write has to return all the power_lines that belong to power_relations that contains power_lines matching a condition.

For example, if I have a

powerlines:

id, geom
----------
1, abc
2, d
3, g
4, j
5, m
6, abc

power_relation_members:

member_id, power_relation_id
----------------------------
1, r_1
2, r_1
3, r_1
4, r_2
5, r_2

power_relations:

id, properties
--------------
r_1, "relation1"
r_2, "relation2"

In the above data, line 1 and 6 have the geom = 'abc'. Lines 1 - 3 are related to relation 1, lines 4-5 to relation 2 and no relation for line 6.
I would want to get all the power_lines that are related (via power_relation to the power_line whose geom is abc. That means, I need lines 1, 2, 3 as results from my query.

I could achieve my result in two steps:

Get the distinct id's of power_relations that have power_lines that match my condition
Get all the power_lines that are related to the power_relations whose id is in the ids fetched from step 1.

Instead, I wanted to do this all in one query by doing the step 1 in a subquery. This includes joining the same tables in the subquery and its outer query. This is how my query looks and it gives me the results as I expected:

SELECT DISTINCT l.id FROM powerline l
JOIN power_relation_members m
ON (l.id = m.member_id AND m.member_type = 'way')
JOIN power_relations r
ON (m.power_relation_id = r.id
    AND r.id in (
        SELECT DISTINCT s_r.id FROM powerline s_l
        JOIN power_relation_members s_m
        ON (s_l.id = s_m.member_id AND s_m.member_type = 'way')
        JOIN power_relations s_r
        on (s_m.power_relation_id = s_r.id)
        WHERE s_l.geom = 'somestring'
    )
);

You may see the simulation in the fiddle here http://sqlfiddle.com/#!15/2493b

I'm not very familiar with how the database's perform and I would like to know if this single query is optimal compared to the two step individual queries or if there is a better and optimal way to achieve the same.

Thank you.

I'm not sure why you need to join all three tables every time. For your specific example, what about the following query:

WITH rel AS (
  SELECT prm.power_relation_id
  FROM power_relation_members prm
  JOIN power_lines pl ON prm.member_id = pl.id
  WHERE pl.geom = :BIND_VAR_HERE -- in this case, 'abc'
  GROUP BY prm.power_relation_id
) SELECT
  pl.id, pl.geom
FROM power_relation_members prm
JOIN rel r ON prm.power_relation_id = r.power_relation_id
JOIN power_lines pl on prm.member_id = pl.id;

You reference member_type and power_relation_members.id in your SQL, but nowhere are they mentioned in your post apart from the DDL.. Is this important logically, or just a filter?


Review my simple database tables Hey trying to create a simple database table for a small beer review project, it been awhile since i have created db's so could anyone just tell me if i am totally wrong here?

Especially my use of unique identifiers, as i see it they would always be unique?

Project description: A simple asp.net site containing beer data, and information on specific key elements like brewery etc. people should be able to search by name and read about the beer.

Without knowing more about your data I'm not sure about your table structure, but I will say that GUIDs/uniqueidentifiers are controversial for clustered indexes.  Traditional logic says that your clustered index should be unique, small, and sequential.  GUIDs are unique, large, and random.  (There is a newsequentialID() function that you could use as a default value to get around the random bit, however.)

An article against random GUIDs as PK/clustering keys:  http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/  Summary:  Your data can end up a scrambled mess, and selects of ranges that should be next to each other can be slowed down.  Also, it takes up too much space in large tables and indexes.

An article for random GUIDs as PK/clustering keys:  http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/  Summary:  If you have a LOT of inserts, it might be faster to spread them out instead of having them all be "at the end."

Personally, I think that if you have a specific business/logic reason to use GUIDs, then use them.  Otherwise, you might want to consider using an autonumber column instead.


Sharding the Existing MongoDB I've got 3 dedicated non-virtual servers (srv1-3). On one of them (srv1) i've got MongoDB in production, that already have got really big amount of data. At the present time there is no sharding or replica processes, it's just a standalone server.

So, I want this server (srv1) to be as a shrad and config server at the same time. The second server (srv2) is going to be a second shard. And the third one (srv3) is going to be a replica server.

I guess, that I have to run the mongod process on srv1 in two instances: one is the regular in --fork, and the second is --configsrv localhost:27019 (and previously create a separate directory for the config server, like /data/confDB).

After that, I should add sh.addShard("srv1:27017") and sh.addShard("srv2:27017") in mongo console and then just sh.enableSharding(myDBname).

Is that correct? Or it's necessary to create an additional server, that will be the config server?

So, I want this server (srv1) to be as a shrad and config server at
  the same time. The second server (srv2) is going to be a second shard.
  And the third one (srv3) is going to be a replica server.

  Is that correct? Or it's necessary to create an additional server,
  that will be the config server?

Yes, you can, As per MongoDB documentation here Changed in version 3.4. Replica Set Config Servers.

Starting in MongoDB 3.2, config servers for sharded clusters can be deployed as a replica set (CSRS) instead of three mirrored config servers (SCCC). Using a replica set for the config servers improves consistency across the config servers, since MongoDB can take advantage of the standard replica set read and write protocols for the config data. In addition, using a replica set for config servers allows a sharded cluster to have more than 3 config servers since a replica set can have up to 50 members. To deploy config servers as a replica set, the config servers must run the WiredTiger storage engine.

In version 3.4, MongoDB removes support for SCCC config servers.

The following restrictions apply to a replica set configuration when used for config servers:

Must have zero arbiters.
Must have no delayed
members.
Must build indexes (i.e. no member should have buildIndexes setting
set to false).

Examples

The following command adds a replica set as a shard:

use admin
db.runCommand( { addShard: "repl0/mongodb3.example.net:27327"} )

  WARNING Do not use localhost for the hostname unless your config server is also running on localhost.


What is the different between resource pools and workload groups in sql server? In SQL server one can use resource pools and workload groups, what is the difference between them? Why should I use one over the other?

Resource Governor Concepts

  Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL
  Server instance inside of a SQL Server instance.
  Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload
  allows for aggregate monitoring of the sessions, and defines policies
  for the sessions. Each workload group is in a resource pool.


problem to join several tables without relation My simplified database scheme:

CREATE TABLE `stores` (
  `idStr` int(11) NOT NULL,
  `nameStr` varchar(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `channelProvider` (
  `idCP` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `mount` int(11) NOT NULL,
  `dateCP` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Expenses` (
  `idExp` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `amountExp` int(10) NOT NULL,
  `dateExp` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `products` (
  `idProd` int(11) NOT NULL,
  `idStr` int(11) NOT NULL,
  `nameProd` varchar(255) NOT NULL,
  `priceSale` int(11) NOT NULL,
  `priceBuy` int(11) NOT NULL,
  `qAvailable` int(11) NOT NULL,
  `dateAddProd` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I need to select and get a result as an example:

BENEFIT | Turnover | Total profit of Product | Total profit | Total of Expenses | Total Amount Channel Provider | Month

I tried with JOINS but I don't get good results.
Appreciate any hints!

While there isn't enough information to be certain this is your problem, it seems likely since you mention joining many tables.

When you have joins to a number of tables, you can wind up with redundant data in your results. Here's how it works.

Sometimes, a number of joins may be fundamentally linear. For example:

SELECT cust.Name, SUM(prod.Price * oline.Qty) as totalPriceCharged
  FROM Customer cust
         INNER JOIN Order ohead ON (cust.custNo = ohead.custNo)
           INNER JOIN OrderLine line ON (ohead.orderNo = oline.orderNo)
             INNER JOIN Product prod ON (oline.prodID = prod.prodID)
 WHERE ohead.orderDate >= '2018-10-01'
   AND ohead.orderDate <  '2018-11-01'

This query would be intended to show the total price of goods ordered by each customer who placed an order in October of 2018. Each Order ties to a Customer; each OrderLine to an Order; and each Product to an OrderLine entry. Each of the four tables is a part of each row of the output; this is simple, because every row returned from Product is tied to a specific row from OrderLine, and so on. In effect, every table involved has a specific relationship with every other table involved, through that chain of joins:

However, you also have non-linear joins:

SELECT cust.Name
      ,SUM(empTime.Hours) as totalHrs
      ,SUM(inv.Total) as totalBilled
  FROM Customer cust
         INNER JOIN EmployeeTime empTime ON (cust.custNo = empTime.custNo)
         INNER JOIN Invoice inv ON (cust.custNo = inv.custNo)

Presumably, the goal of this statement would be to see the total amount charged to each customer, versus the total hours employees worked for that customer (yes, we've just changed from provided products to services, apparently).

However, this query won't give the person who wrote it back what they want.

Unlike our previous query, the only connection between EmployeeTime and Invoice is the custNo that links those tables to Customer. Each row returned has to represent one row from Customer, one from EmployeeTime, and one from Invoice. Let's assume our company is just starting out:

Customer:
 custNo |     Name
--------+--------------
    1   | AAA Chemical
    2   | ZZZ Cleaning

EmpTime:
 custNo |  Hours
--------+---------
    1   |   2.0
    1   |   2.5
    2   |   5.9
    2   |   1.1
    2   |   3.0

Invoice:
 custNo |   Total
--------+-----------
    1   |   100.00
    1   |   125.00
    2   |   295.00
    2   |   205.00

So, the expected results would be:

 custNo |  Hours  |   Total
--------+---------+-----------
    1   |   4.5   |   225.00
    2   |  10.0   |   500.00

What you'd actually get would be:

 custNo |  Hours  |   Total
--------+---------+-----------
    1   |   9.0   |   450.00
    2   |  20.0   |  1500.00

Why?

Again, each row from our result set (before we do our SUMs) has to contain one row from each of the three tables. And, the only way that EmployeeTime and Invoice are connected are through their connection to custNo.

So, how does the database engine know which EmployeeTime rows to connect to which Invoice rows? By the custNo, of course.

Giving us:

 custNo |     Name     |  Hours  |   Total
--------+--------------+---------+-----------
    1   | AAA Chemical |   2.0   |   100.00
    1   | AAA Chemical |   2.0   |   125.00
    1   | AAA Chemical |   2.5   |   100.00
    1   | AAA Chemical |   2.5   |   125.00
    2   | ZZZ Cleaning |   5.9   |   295.00
    2   | ZZZ Cleaning |   5.9   |   205.00
    2   | ZZZ Cleaning |   1.1   |   295.00
    2   | ZZZ Cleaning |   1.1   |   205.00
    2   | ZZZ Cleaning |   3.0   |   295.00
    2   | ZZZ Cleaning |   3.0   |   205.00

as our underlying data to feed to the SUM functions.

Each row from EmployeeTime for a given Customer has been matched against each row from Invoice for the same customer. This is called a cross-product.

How do you avoid this?

You make linear joins, where the relationship between each table is well-defined, and where we don't need cross-products to fill things out (not an option, for this query);
You prevent the tables that don't have well-defined connections from "joining" more than one row; or
you return a simple set of calculated values, instead of doing joins.

The last two options tend to look very similar; they'll both rely on subqueries to get our values.

Here's our previous query, where we're just joining single rows:

SELECT cust.Name
      ,hours.totalHrs
      ,inv.totalBilled
  FROM Customer cust
         INNER JOIN (SELECT custNo, SUM(Hours)
                       FROM employeeTime
                      GROUP BY custNo
                    ) hours
         INNER JOIN (SELECT custNo, SUM(Total)
                       FROM Invoice
                      GROUP BY custNo
                    ) inv

Technically, this still does a cross-product; however, 1 * 1 = 1

This should work just as well if you're using LEFT OUTER JOIN (or RIGHT...) instead of INNER JOIN.

Or, we just avoid the join altogether, and calculate the totals using suBqueries in the SELECT list:

SELECT cust.Name
      ,(SELECT SUM(Hours) FROM EmployeeTime WHERE custNo = cust.custNo) as totalHrs
      ,(SELECT SUM(Total) FROM Invoice WHERE custNo = cust.custNo) as totalBilled
  FROM Customer cust

Either method should work, if this is indeed the root of your problem.


dependant subquery processed despite Extra Impossible WHERE noticed after reading const tables in this query https://www.db-fiddle.com/f/snzSLd8y1oRNRqdsFpNdHa/0

SELECT test0.id,
(SELECT GROUP_CONCAT(test2.one_info) FROM test2 WHERE test2.one_id = test1.id) AS GC
FROM test0
INNER JOIN test1 ON test1.zero_id = test0.id
WHERE test0.id = 1 AND test1.id = 1 AND test1.rand = 5;

test1.rand=5 exists so the execution plan is

| id  | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
| --- | ------------------ | ----- | ---------- | ----- | ------------- | ------- | ------- | ----- | ---- | -------- | ----------- |
| 1   | PRIMARY            | test0 |            | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      | Using index |
| 1   | PRIMARY            | test1 |            | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      |             |
| 2   | DEPENDENT SUBQUERY | test2 |            | ref   | IND           | IND     | 5       | const | 4    | 100      | Using index |

but when i change it to something that does not exist like test1.rand=10 it give this exectuion plan

| id  | select_type        | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra                                               |
| --- | ------------------ | ----- | ---------- | ---- | ------------- | --- | ------- | ----- | ---- | -------- | --------------------------------------------------- |
| 1   | PRIMARY            |       |            |      |               |     |         |       |      |          | Impossible WHERE noticed after reading const tables |
| 2   | DEPENDENT SUBQUERY | test2 |            | ref  | IND           | IND | 5       | const | 4    | 100      | Using index                                         |

and despite Extra: Impossible WHERE noticed after reading const tables and it returning no results it still used select_type: DEPENDANT SUBQUERY and got rows: 4

how to fix this problem and make it not return anything at all to save performance?

Look at the Handler counts to see if it is actually touching any rows:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

And use EXPLAIN FORMAT=JSON SELECT ... to get more details.

Anyway, since it is a DEPENDENT subquery, it will be asked to run as often as needed.  But that is zero times.

Also, EXPLAIN, is not perfect.  The subquery did not get 4 rows; EXPLAIN is estimating that it will touch 4 rows each time it the subquery is invoked.  Again, 0*4 = 0.


Database design for small startup For a small startup that doesn't have much expected traction in the very beginning. We are currently working with only one postgres database without replications and at risk of failures. I've done some research and realized that the primary methods everyone uses nowadays are either master-master or master-slave replications. My question is in general for small guys like us, what would be the urgency and need to have these replications in place for the additional cost of servers? Would we be able to survive on a single server? As well, would master-master or master-slave suit us better?

Thanks!

There are really several different things in play here:

Recovery to the current state if the DB server is lost. Synchronous replication is your only option here, but it comes with some risks - if the replica goes down or fails the master will stall, and if there's much latency then performance will suffer due to the need for the replica to confirm before commits are accepted. Setting synchronous_commit = off for transactions that aren't crucial can help.
Recovery to a recent state if the DB server is lost. Asynchronous replication and is what you want here. It doesn't guarantee to be up to date to the latest committed transaction, but has less performance impact.
Recovery to some previous point in time to cope with accidental deletions, corruption, etc. Your two tools here are dumps and PITR (wal archiving), with different advantages. PITR lets you recover to a finer-grained point in time ("what was in my customer table just before Bob dropped it?"), but doesn't protect as effectively against corruption of database storage and can require more disk space. PgBarman helps automate PITR, as does the tool WAL-E. Dumps can be more compact and are portable between PostgreSQL versions and architectures, but are coarser grained snapshots of the DB state - you can't have an incremental dump in Pg.
High availability. HA isn't so much for protection against data loss as to maintain uptime. In PostgreSQL it's implemented with replication and failover. repmgr is one tool that can help you manage this, in combination with something like PgBouncer or HAProxy for connection routing.

Personally I think your minimum requirement is to use PgBarman or WAL-E to archive WAL for PITR, and do nightly dumps as additional redundancy for backup purposes.

Look into synchronous replication if you cannot afford to possibly risk losing a transaction (e.g. if you're processing financials/payments).

Investigate HA if you reach the point where downtime becomes unacceptable.


How can I represent the entity types depicted in an ERD as relations with attribute names? I have created the entity-relationship diagram (ERD) shown below (for brevity, each individual bubble-shaped box portrays multiple attributes):

Considering such an ERD, I have to convert each entity type or associative entity type depicted there as a relation with its corresponding attribute names, but I run into some problems.

I usually represent a relation as follows:

X = (a, b, c)*

* Where X is the name of the relation and a, b and c are the names of its attributes.

Then, in the business domain that I am working on, a Course depends on zero-to-many (0:M) other Courses (as portrayed in the ERD). This means that a Course should be connected somehow to the Courses it is dependent on.

So, how can I represent this situation as a (mathematical) relation?

Option 1

I have tried the following:

Course = (course_number, course_name, course_dependencies?)

Option 2

I think it is possible to depict the desired aspect in another way, e.g.:

Depends = (course_name)

and then

Course = (course_number, course_name, depends)

Option 3

But I thought about, maybe, arranging the elements as follows:

Course = (course_number, course_name)
Depends = (course_name_depender, course_name_dependee)

In this way, each pair of attribute names in the relation called Depends represents the association (or relationship) between two occurrences of the relation entitled Course.

Questions

Which of these options is the right way to do it? Perhaps there is a better approach?

Considering the Course entity type that you describe, I have made the following assumptions:

A Course is primarily identified by its CourseNumber
A Course is alternately identified by its Name
A Course is registered at a CreatedDateTime

So I reformulated the situation as follows:

A Course is the dependent in zero-one-or-many CourseDependences
A Course is the determinant in zero-one-or-many CourseDependences

Then, I supposed that:

A CourseDependence is primarily identified by its DependentCourseNumber and DeterminantCourseNumber
A CourseDependence is registered at a CreatedDateTime

This means, yes, that there is a conceptual-level many-to-many (M:N) association (or relationship) that may involve distinct instances of the entity type called Course. Such an association implies the existence of an associative entity type that, as noted, I denominated CourseDependence.

Consequently, from the set of formulations described above, I have derived the IDEF1X1 diagram shown in Figure 1:

In this way, employing the notation that you use to express relations along with their corresponding attribute names, I would represent (a) the Course entity type and (b) the CourseDependence associative entity type as two distinct relations in the following way:

Course = (CourseNumber, Name, CreatedDateTime)
CourseDependence = (DependentCourseNumber, DeterminantCourseNumber, CreatedDateTime)

Therefore, the Option 3 exposed in your question would be very close to an accurate possibility to express the relevant aspects.

Endnote

1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by the sole originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


Copy SQL Server replication configuration from production to test environment Where I'm currently working we have an SQL Server's instance for a billing database and another instance for customer database. Both databases keep some tables synchronized using replication mechanism. I'm trying to set up some automated way to get fresh backups from production into QA/DEV environments, but in order for our platform to be kind of a mirror of production environment we need this replication thing between customer and billing databases to work. I know very few about these kind of things, I'm only a developer by unfortunately no one else is willing to do it.

My question is, Is it possible to copy the replication setup in production to QA without having to go along all the process of setting up replication step by step in QA/DEV environments? Maybe generating some scripts of this setup from production and with some small changes enable it in QA/DEV environments?

Additional notes:

We use an instance for QA/Dev customer databases.
Another instance for QA/DEV billing databases.
Both instances run in the same server.

SQL Server version:

  Microsoft SQL Server 2016 (RTM-GDR) (KB3210111) - 13.0.1728.2 (X64)
      Dec 13 2016 04:40:28
      Copyright (c) Microsoft Corporation
      Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

This is probably a dumb question, but I'd appreciate any help about it.

Thanks.

From the Microsoft Docs on Replication:

If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must recreate all publications and subscriptions after backups are restored.
In the event of server failure or the need to set up a test environment, you can modify the script by changing server name references, and it can be used to help recreate your replication settings. In addition to scripting your current replication settings, you should script the enabling and disabling of replication. For information about scripting replication objects, see Scripting Replication.

You can script the replication objects, then modify the server names to suit the test/dev environment via SQL Server Management Studio:

Connect to the Distributor, Publisher, or Subscriber in Management Studio, and then expand the server node.

Right-click the Replication folder, and then click Generate Scripts.

Specify options in the Generate SQL Script dialog box.

Click Script to File.

Enter a file name in the Script File Location dialog box, and then click Save. A status message is displayed.

Click OK, and then click Close.

The resulting script can then be ran against the test/dev environment to automatically setup replication, just ensure you modify all server names as needed.  You really don't want the test/dev environment to interfere with the production environment.


SQL Query for cumulative sum I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple database structure consisting of two tables.

Table users:

PK_User, uniqueidentifier
ID, bigint
Username, nvarchar(128)
CreationTimestamp, datetime

Table data:

PK_Data, uniqueidentifier
FK_User, uniqueidentifier
FK_Reporter, uniqueidentifier
CreationTimestamp, datetime

I am currently working with the following SQL Statement:

SELECT u.Username, COUNT(d.FK_User) AS 'Count', CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime) AS 'Date'
FROM data d INNER JOIN users u ON u.PK_User = d.FK_User
GROUP BY CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime), u.Username
ORDER BY CAST(FLOOR(CAST(d.CreationTimestamp AS float)) AS datetime)

which delivers something like this:

User1   5   %Date1%
User2   3   %Date1%
User1   7   %Date2%
User2   1   %Date2%

So I am able to get the sum for each user for each specific day. I would like to sum this sum up, so that User1 gets for the second day 5+7=12 and User2 3+1=4.

How can I achieve this?

You need a "window" aggregate, i.e. an OVER clause in the aggregate. And because the query already has a GROUP BY, the aggregate needed is the SUM() over the COUNT(d.FK_User) you already have:

SELECT
    u.Username,
    -- COUNT(d.FK_User) AS UserCount,     -- if you need both counts
    SUM(COUNT(d.FK_User)) OVER (PARTITION BY u.Username
                                ORDER BY CAST(d.CreationTimestamp AS DATE)
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND CURRENT ROW)
        AS CumulativeUserCount,
    CAST(d.CreationTimestamp AS DATE) AS CreationDate
FROM data d
    INNER JOIN users u
    ON u.PK_User = d.FK_User
GROUP BY
    CAST(d.CreationTimestamp AS DATE), u.Username
ORDER BY
    CreationDate ;

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is not strictly needed. The default frame for window aggregates when there is an ORDER BY inside OVER () is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which would produce the same results as ROWS but is likely to generate a suboptimal execution plan. See Aaron Bertrand's blog article Best approaches for running totals – updated for SQL Server 2012 for details.
I've simplified the complicated expression that seems to just strip the time part form the datetime column.
Removed the single quotes around the aliases. Aliases and identifiers better not be reserved keywords and not have special characters or spaces. If you do need any of these, then it's preferred to quote them with double quotes "Count" or square brackets [Count].


Given the data:

CREATE TABLE #Demo
(
    Val integer NOT NULL,
    TS datetime2(3) NOT NULL
);

INSERT #Demo
    (Val, TS)
VALUES
(1, '2019-08-12 00:00:00.013'),
(3, '2019-08-12 00:10:00.013'),
(2, '2019-08-12 00:20:00.013'),
(2, '2019-08-12 00:30:00.013'),
(0, '2019-08-12 00:40:00.013'),
(0, '2019-08-12 00:50:00.013'),
(1, '2019-08-12 01:00:00.013'),
(7, '2019-08-12 01:10:00.013'),
(0, '2019-08-12 01:20:00.013'),
(1, '2019-08-12 01:30:00.013'),
(2, '2019-08-12 01:40:00.013'),
(0, '2019-08-12 01:50:00.013'),
(2, '2019-08-12 02:00:00.013'),
(0, '2019-08-12 02:10:00.013'),
(0, '2019-08-12 02:20:00.013'),
(0, '2019-08-12 02:30:00.013'),
(1, '2019-08-12 02:40:00.013'),
(0, '2019-08-12 02:50:00.013'),
(4, '2019-08-12 03:00:00.013'),
(3, '2019-08-12 03:20:00.013'),
(1, '2019-08-12 03:30:00.013');

One approach is:

Calculate the number of minutes from a fixed date (with no time component)
Add 30 minutes to each time to align half-hour slots to hours
Round to the hour (divide then multiply minutes by 60 using integer math)
Subtract the 30 minutes
Add the minutes to the same fixed date

This is shown in the code below:

SELECT
    TS = CONVERT(char(19), Timeslot.TS, 120),
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS JOIN (VALUES(CONVERT(datetime2(3), '20190101', 112))) AS Base(FixedDate)
CROSS APPLY(VALUES((DATEDIFF(MINUTE, Base.FixedDate, D.TS) + 30) / 60 * 60)) AS Slot(InMins)
CROSS APPLY(VALUES(DATEADD(MINUTE, Slot.InMins - 30, Base.FixedDate))) AS Timeslot(TS)
GROUP BY
    Timeslot.TS
ORDER BY
    Timeslot.TS;

Output:

╔═════════════════════╦═════╗
║         TS          ║ Val ║
╠═════════════════════╬═════╣
║ 2019-08-11 23:30:00 ║   6 ║
║ 2019-08-12 00:30:00 ║  10 ║
║ 2019-08-12 01:30:00 ║   5 ║
║ 2019-08-12 02:30:00 ║   8 ║
║ 2019-08-12 03:30:00 ║   1 ║
╚═════════════════════╩═════╝

db<>fiddle demo

This produces a couple of extra periods compared with the output shown in the question, but it nevertheless looks correct to me.

If you prefer the (somewhat slow) FORMAT function, the following is equivalent:

SELECT
    TS = Slot.TS,
    Val = SUM(D.Val)
FROM #Demo AS D
CROSS APPLY
(
    VALUES
    (
        -- Remove 30 minutes
        DATEADD(MINUTE, -30,
            CONVERT(datetime2(3),
                FORMAT(
                    -- Add 30 minutes
                    DATEADD(MINUTE, 30, D.TS),
                    -- Truncate to hour
                    N'yyyy-MM-dd HH:00:00',
                    N'en-us'
                ), 120
            )
        )
    )
) AS Slot (TS)
GROUP BY
    Slot.TS
ORDER BY
    Slot.TS;


Using RowLock to enforce Once-And-Only-Once I have a Web Service that can fire off a chain of events for an order.  It does this when it gets a message telling it to (max of about 2 per second).

The problem I have is that I have more than one instance of the Web Service, and sometimes different parts of the order can reach each instance at the exact same time.  This causes the chain of events to fire off more than once for the order (bad).

So, I was thinking, I could make a table that is just the ID of the order and a flag saying if the "chain of events" has been fired off yet.

I could then start a transaction in my code.  Something like this:

mySqlConnection.BeginTransaction(); // C# Code

I could select the row for the order in question like this:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT  OrderId, IsChainFired
FROM    OrderedChainFlag WITH ( ROWLOCK, XLOCK, HOLDLOCK )
WHERE   OrderId = 123456

Then, if IsChainFired is false, fire off the "Chain of Events".  I would then run:

UPDATE  OrderedChainFlag
SET     IsChainFired = 1
WHERE   OrderId = 123456

and then run (even if IsChainFired is true):

myConnection.CommitTransaction(); // C# Code

This would prevent more than one instance of the service from reading that the chain is free to be run at the same time.

My worry (and my question) is lock escalation.  If this stays on row locks then it will be a fantastic solution to my problem.  But if the locks escalate to page or table, then I will have just created a bottleneck in my system.

So, is there anything I can do to ensure that this stays at the row lock level?  (or is it even a good idea to use the database locking system as a semaphore like this?)

NOTE:
OrderId would be the Primary Key, Clustered Index and Partitioning Index of the table.

You can use the OUTPUT clause to see if the chain of events should be "fired":

My test-bed:

CREATE TABLE dbo.OrderedChainFlag
(
    IsChainFired BIT NOT NULL
        CONSTRAINT DF_OrderedChainFlag_IsChainFired
        DEFAULT ((0))
    , OrderID INT NOT NULL
);

CREATE INDEX IX_OrderedChainFlag_Firing
ON dbo.OrderedChainFlag(OrderID, IsChainFired);

GO

INSERT INTO dbo.OrderedChainFlag (OrderID)
VALUES (123456);

Run this to see the output:

UPDATE OrderedChainFlag
SET IsChainFired = 1
OUTPUT inserted.IsChainFired, inserted.OrderID
WHERE OrderId = 123456
    and IsChainFired = 0;

Use a DataReader to read the results of the UPDATE statement; if a row is returned by .Read() you know the table was updated, and effectively, you can run the chain of events.

Running the UPDATE statement above will only ever provide output if it actually updated the IsChainFired column to 1.  You can prove that by running the update statement twice.  The first one shows output, the second one does not.

Since this one statement is atomic, one-and-only-one update will succeed, without affecting lock escalation, assuming you have a good index on OrderId and IsChainFired, and that index is configured WITH (ALLOW_ROW_LOCKS = ON).

Second option, which is a slight variation on the above, would be to not specify the next OrderID to be processed; instead simply select the next one that has IsChainFired = 0:

CREATE INDEX IX_OrderedChainFlag_IsFired
ON dbo.OrderedChainFlag(IsChainFired);

UPDATE TOP(1) OrderedChainFlag
SET IsChainFired = 1
OUTPUT inserted.IsChainFired, inserted.OrderID
WHERE IsChainFired = 0;

The UPDATE statement in this case returns the OrderID for a single row directly from the IX_OrderedChainFlag_IsFired index.

I inserted over 750,000 rows into the OrderedChainFlag table in my test-bed, and ran the above UPDATE TOP(1) statement, with the index I mention in place, and got the following plan:

A (arguable) bonus here is you could potentially return more than TOP(1) rows for processing, perhaps using multiple threads in your Web Service.

You should probably also read this question, along with the excellent answers on concurrency.


How to find the sum and overpayment of three table values I need to find the each client sum of TotalAmount in Invoice_TBL and sum of TotalPaid in Payment_TBL. The below i have mentioned three tables.

Retrieve query answer should like below image from the above 3 tables.

Probably easiest to do with sub-selects:

select clientno
     , coalesce(totalamount,0) as totalamount
     , coalesce(totalpaid,0) as totalpaid
     , least(coalesce(totalamount,0)-coalesce(totalpaid,0),0) as overpaid
from (
    select c.clientno
        , (select sum(i.totalamount)
           from invoice i
           where c.clientno = i.clientno) as totalamount
        , (select sum(p.totalpaid)
           from payment p
           where c.clientno = p.clientno) as totalpaid
    from client c
)

If least is not availible in your DBMS something like:

case when totalamount-totalpaid > 0
     then 0
     else totalamount-totalpaid
end as overpaid

should do

A slightly different approach is to use LATERAL. I think this is called CROSS APPLY in sql-server. I cannot verify this at the moment so you will probably have to modify this:

select clientno
    , coalesce(totalamount,0) as totalamount
    , coalesce(totalpaid,0) as totalpaid
    , case when coalesce(totalamount,0)-coalesce(totalpaid,0) > 0
           then 0
           else coalesce(totalamount,0)-coalesce(totalpaid,0)
      end as overpaid
from (
    select c.clientno, i.totalamount, p.totalpaid
    from client c
    outer apply (
        select sum(totalamount) as totalamount
        from invoice
        where clientno = c.clientno
    ) as i
    outer apply (
        select sum(totalpaid) as totalpaid
        from payment
        where clientno = c.clientno
    ) as p
) as x;


Different execution plans and joins using synonym or table Edit:

We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all).

We have a very strange issue using Oracle 12.1.0.2.

This is the query:

SELECT * FROM OWNER1.TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

This results in a cartesian product of the two tables, returning a wrong dataset and thousands of rows.

This is the same query, but this time using the public synonym OWNER1TABLE1 for the table OWNER1.TABLE1:

SELECT * FROM OWNER1TABLE1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

This results in the correct data being returned (only 16 rows).

Oracle is also using two quite different execution plans:

Using the table name:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------
Plan hash value: 3737086414
-------------------------------------------------------------
| Id  | Operation                           | Name          |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |
|   1 |  MERGE JOIN CARTESIAN               |               |
|   2 |   PARTITION RANGE SINGLE            |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| OWNER2.TABLE2 |
|   4 |     INDEX RANGE SCAN                | O2.IDX        |
|   5 |   BUFFER SORT                       |               |
|   6 |    PARTITION RANGE ALL              |               |
|   7 |     TABLE ACCESS FULL               | OWNER1.TABLE1 |
-------------------------------------------------------------

Using the public synonym:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1141852644

---------------------------------------------------------------------
| Id  | Operation                                   | Name          |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |               |
|   1 |  HASH JOIN                                  |               |
|   2 |   PARTITION RANGE SINGLE                    |               |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER1.TABLE1 |
|   4 |     INDEX RANGE SCAN                        | OWNER1.PKY$T1 |
|   5 |   PARTITION RANGE SINGLE                    |               |
|   6 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| OWNER2.TABLE2 |
|   7 |     INDEX RANGE SCAN                        | O2.IDX        |
---------------------------------------------------------------------

The synonym is defined like this:

CREATE public synonym OWNER1TABLE1 FOR OWNER1.TABLE1;

And maybe the strangest thing is, if we define an alias (e.g. T1) for OWNER1.TABLE1 in the query like

SELECT * FROM OWNER1.TABLE1 T1 JOIN OWNER2.TABLE2 USING (COLUMN1, COLUMN2, COLUMN3, COLUMN4) WHERE COLUMN1=10 AND COLUMN2='string' AND COLUMN3='01.04.2018' ORDER BY COLUMN3;

...it returns the correct data, too.

Anyone knows about a bug or something? Can't really be expected behaviour, can it?

Bug 18856999 - Wrong results (wrong cursor) with schema table masking public synonym (Doc ID 18856999.8)

There is a one-off patch for this with the same number for 12.1.0.2. The fix is in some later PSUs and starting with 12.2.0.1.

As usual, the above requires an active support subscription.


MAX NUMA/ "Affinity" in SQL Server Is there now, or possibly in the works a way to tell SQL Server to stay inside one NUMA node, either server wide or at the query level?   Basically the same functionality as MAXDOP, but MAXNUMA?

Right now, unless I am totally missing it, when a query goes parallel it can use all of the processors it can see.   Just wondering if there is a way to restrict it to one or two or 'x' NUMA nodes.  I may not really want my queries spread out over 80 logical processors :)

This is mostly a curiosity rather than a "help me break my broken thing" question.

You could use SQL Server's "processor affinity" to limit processing to a single NUMA node.  See the <process affinity> section of the ALTER SERVER CONFIGURATION command on this MSDN page for more details.

By default, SQL Server attempts to keep query processing contained inside a given NUMA node if the query goes parallel where MAXDOP <= number of cores per NUMA node.  The indication here is to configure MAXDOP to a reasonable value; certainly not more than the number of cores per NUMA node.  There are a load of resources to help with calculating a reasonable MAXDOP; including the answers on my question.


How to find missing numbers? I have a table containing a list of numbers. How do I find numbers that are missing from the table?

For instance:

numbers   id
 1         1
 5         2
 3         3
 6         4
 7         5
 8         6
 9         7
 20        8
 ....

How can I find missing numbers?  Like 4 and there are no numbers between 9 and 20.

I have tried nothing, but want to know.

Assuming you want all missing integer numbers between the minimum and maximum existing id in your table - in a current Postgres 9.5 installation like you commented (or at least 9.3):

@Seb3W already suggested generate_series(). It's more efficient to retrieve min and max in a single query, though. It's also preferable to use set-returning-functions like generate_series() in the FROM list instead of the SELECT list (conforming to standard SQL and less error prone).

SELECT id
FROM  (SELECT min(id) AS a, max(id) AS z FROM numbers) x, generate_series(a, z) id
LEFT   JOIN numbers n1 USING (id)
WHERE  n1.id IS NULL;

Once you have the complete set of candidate numbers, use a run-of-the-mill technique to ...

Select rows which are not present in other table

About the LATERAL join:

What is the difference between LATERAL and a subquery in PostgreSQL?

If you don't care about standard SQL and want to squeeze out the last drop of performance (or in Postgres 9.2 or older without LATERAL joins) you can use generate_series() in the SELECT list, but still make it a single SELECT:

SELECT id
FROM  (SELECT generate_series(min(id), max(id)) FROM numbers) n(id)
LEFT   JOIN numbers n1 USING (id)
WHERE  n1.id IS NULL;

If you are after performance, you should have an index on numbers.id, of course:

CREATE INDEX numbers_id_idx ON numbers (id);


Is it possible to mysqldump a subset of a database required to reproduce a query? Background

I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).

Question

Is there a way that I can incorporate this select statement into a script that dumps the queried data into a new database, such that the database could be installed on a new mysql server, and the statement would work with the new database. The new database should not contain records in addition to those that have been used in the query.

Update:
For clarification, I am not interested in a csv dump of query results. What I need to be able to do is to dump the database subset so that it can be installed on another machine, and then the query itself can be reproducible (and modifiable with respect to the same dataset).

Example

For example, my analysis might query a subset of data that requires records from multiple (in this example 3) tables:

select table1.id, table1.level, table2.name, table2.level
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

mysqldump has the --where option to execute a WHERE clause for a given table.

Although it is not possible to mysqldump a join query, you can export specific rows from each table so that every row fetched from each table will be involved in the join later on.

For your given query, you would need to mysqldump three times:

First, mysqldump all table3 rows with name in ('fee','fi','fo','fum'):

mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql

Next, mysqldump all table2 rows that have matching table3_id values from the first mysqldump:

mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql

Then, mysqldump all table1 rows that have matching table1_id values from the second mysqldump:

mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql

Note: Since the second and third mysqldumps require using more than one table, --lock-all-tables must be used.

Create your new database:

mysqladmin -u... -p... mysqladmin create newdb

Finally, load the three mysqldumps into another database and attempt the join there in the new database.

mysql -u... -p... -D newdb < table1.sql
mysql -u... -p... -D newdb < table2.sql
mysql -u... -p... -D newdb < table3.sql

In mysql client, run your join query

mysql> use newdb
mysql> select table1.id, table1.level, table2.name, table2.level
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

Give it a Try !!!

WARNING : If not indexed correctly, the second and third mysqldumps may take forever !!!

Just in case, index the following columns:

ALTER TABLE table2 ADD INDEX (table1_id);
ALTER TABLE table2 ADD INDEX (table3_id);
ALTER TABLE table3 ADD INDEX (name,id);

I'll assume id is the primary key of table3.


How do I connect to Oracle Server without installing Oracle Client on client machines? I am using Microsoft VB.Net to create a Windows application, and would like some suggestions on how to connect to an Oracle database.

use jdbc JDBC Driver Downloads or maybe
ODAC for Windows


How to read Mongodb entries in plaintext? I'm testing mongodb on a local machine for the first time, and I just want to see the actual plaintext contents of a database entry.

Started the mongo console:

$ mongo

Set dbs:

> use <my dbs name>

Searching entries:

> db.<collection name>.find({"_id": "486b4ca55b144b119f4551417c5798c8"})

{ "_id" : "486b4ca55b144b119f4551417c5798c8", "date" : ISODate("2017-04-20T05:47:06.176Z"), "content" : ObjectId("58f84b5a0318762cb6e230a6"), "digest" : "d064a874c872d75f6ab67c04d8b79b26f65f9b4f", "short" : "5f9b4f", "size" : 2374694 }

This is as far as I've gotten. The entries are pastebin uploads. I'm able to locate entries with various search queries but it doesn't actually show me what was uploaded. Is this possible? Am I going about this the hard way?

You are seeing "plain text" documents right now, in json format. Command db.coll.find() shows all documents (in batch of 20 documents). If you want those word wrapped (easier to read) add .pretty() to end of line

db.coll.find().pretty()

of course all these documents are in bson (binary) format in database and if you want to see that, use mongoexport.

When you insert document, if you don't specify _id, it is added automatically. Rest of document is just like you inserted it.


ER Schema "Optimisation" I am trying to make an ER Schema for a person. The person will have Current Address and Permanent Address. So far I have it so that both Current and Permanent Address are Composite Attributes and they have the same Attributes.

If you look at the picture you see that it is pretty much a copy of the same attributes. How can I combine them/make this better?

I'm with @sa555. but to exemplify better, you can adda Person_Has_Address entity, that has a relation to a new AddressType table.

It would be like:

AddressType would have two records: "CurrentAddress" and "PermanetAddress". In the case more address type wouldd come up (like mailing address, or work address), your are covered-up


Is there a way to figure out an executing query's plan handle by the query text? Is there a way to determine the plan handle for the query plan of an executing query by that query's text?

Perhaps in any of the DMVs?

(I'm trying to find the query stats on an ad-hoc query.)

Derived from https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

DECLARE @SearchForSql AS NVARCHAR(MAX) = 'SQL TEXT'

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE Text LIKE CONCAT('%' ,@SearchForSql,'%')
    AND Text NOT LIKE '%-- Self Reference Marker --%'

Note: The "NOT LIKE" is there to ensure that you don't find the statement you are running, which is inherently self referencing.


PostgreSQL Bloat Warning: Vaccuum does not help We use check_postgres to monitor our DB. We get this warning:

POSTGRES_BLOAT WARNING:
DB "foo" (db foo) index foo_text_index_id_uniq rows:?
pages:485435 shouldbe:330673 (1.5X) wasted bytes:1267810304 (1 GB)

According to the docs a vacuum should help, but the autovacuum does not help.

What can I do to get rid of the bloat?

Vacuuming will not remove the existing bloat.  It will free up that space for future use.  Until that future use has happened, it will still look bloated.

To free it up now, you would have to do something drastic like VACUUM FULL, CLUSTER, or use some external tool like pg_repack.  That is a lot of churn to put your database through just to fix a problem that is not actually a problem.

I would just do as @KookieMonster said, and increase the threshold.  Then if things start to get worse, eventually you will start getting warnings again.  (Actually, I wouldn't run check_postgres in the first place, but if I were, then...see above.)


how do I insert a default row? If I create a table with an identity column as primary key, and all the other columns have default values, for example

create table rr (id int identity(1,1) primary key, dt datetime default getdate())

To insert a single row

INSERT INTO RR DEFAULT VALUES;

It is possible to insert multiple rows of default values by (ab)using MERGE

MERGE INTO RR
USING (SELECT TOP 1000 *
       FROM   master..spt_values) T
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT
  DEFAULT VALUES;


Custom backup deletion Ola Scripts I am trying to find a way to delete backups being taken by Ola's native SQL backups. I am trying to keep only 14 days of transaction logs, 1 month of diffs, and 12 months of full weekend backups. Is there a way to do it in Ola's scripts?

You can use the CleanupTime  parameter for each backup job (log, diff, full)

  Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

So for example:

@Cleanuptime = 336  --2 weeks for transaction logs
@Cleanuptime = 744  --1 month for diff backups
@Cleanuptime = 8760 --1 year for full backups

Example A from Ola

  A. Back up all user databases, using checksums and compression; verify
  the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24


In MongoDB, $setIsSubset does not make efficient use of index Data:
student        class
s1          ["english"]
s2          ["maths", "geography"]
...
sN          ["history", "english"]

The field "class" is indexed, and values of it are all in array.
Query1:
db.students.explain('executionStats').aggregate([
  { $match: { $expr: { $setIsSubset:  [ "$class", [ "maths" ] ] } } }
])

,
Query2:
db.students.explain('executionStats').aggregate([
  { $match: { "$class": "maths" } }
])

The first one does a COLLSCAN or IXSCAN with indexBounds class : [MinKey, MaxKey], depending on some conditions (e.g. the number of documents in the collection), while the second does a IXSCAN with indexBounds class : ["maths", "maths"].
Given that the array of the "class" field can have multiple values, I need the $setIsSubset semantics here. I found this. However, the double negation does a COLLSCAN instead of using the index as well. The corresponding query is as follows:
Query3:
db.students.explain('executionStats').aggregate([
  { $match: { class: { $not: { $elemMatch: { $nin: ["maths" ] } } } } }
])

It seems like MongoDB only does simple matching during the IXSCAN. Otherwise, it should be able to avoid doing a IXSCAN with [MIN, MAX] which essentially fetches all keys in the index. It can be possibly even slower than the COLLSCAN.
Wondering if there is anyway to achieve the $setIsSubset functionality, which can utilize the index efficiently.

An index is basically a mapping from a field value to a document.  In MongoDB each element of the array is listed in the index separately.
Using that example data, the index might look something like:
"geography" => s2
"english" => s1
"english" => sN
"history" => sN
"maths" => s2

This lends itself well to equality and inclusion searches.  In a query for{ $match: { "$class": "maths" } }, it would simply start at the first occurrence of  "maths", scan through the index entries until it reach a value that is not "maths", and pass the list of matching on to the next part of the query.  The same works for an $in search with a separate scanned range for each value being tested.
This can also work for $nin by scanning the ranges of the index that fall between each value in the query.
The query for {$setIsSubset: {"$class", [input]}} is essentially
${and:[
   {class:{$in:[input]}},
   {class:{$not:{$nin:[input]}}}
]}

In order to efficiently use an index with the curren structure, the MongoDB would need to scan all of the matching ranges to find documents that match, and scan all non-matching ranges for documents that do not match, sort one or both lists, and then compare the two lists to eliminate documents that have both matching and non-matching values.
I am not sure if that would perform better or worse than just scanning all of the documents.
It may be worth submitting a feature request to see if that can be implemented.
Also node that the following from the $expr documentation:

$expr does not support multikey indexes.

In MongoDB "multikey" means at least one of the fields in one of the indexed documents contains an array.
As a workaround, you might try doing separating that test into multiple aggregation stages, first test the array with $in which will use and index to reduce the number of documents examined, then test the with $setIsSubset will be able examine only the documents that have at least one matching value.
I would be very curious how this query performs compared with the two you have tried already:
db.students.explain('executionStats').aggregate([
  { $match: { class: { $in: [ "maths" ] }} },
  { $match: { $expr: { $setIsSubset:  [ "$class", [ "maths" ] ] } } }
])


How can I get this query to use it's index? Queries matching this query below shows up in the slow log (with different contentVersionId &  modifiedDateTime).

As I don't really know what I'm doing I tried adding indexes to all columns in the WHERE clause (on at a time) but it the explain still showed Extra: Using where not Extra: Using index as I was expecting.

Am I wrong in assuming Extra: Using where is bad? If I'm not wrong how should I proceed?

Table is innodb, primarily read from and contains 600000+ rows

The Query:

SELECT cmContentVersion.contentVersionId,
       cmContentVersion.stateId,
       cmContentVersion.modifiedDateTime,
       cmContentVersion.versionComment,
       cmContentVersion.isCheckedOut,
       cmContentVersion.isActive,
       cmContentVersion.contentId,
       cmContentVersion.languageId,
       cmContentVersion.versionModifier
FROM   cmContentVersion
WHERE  cmContentVersion.languageId = 3 AND
       cmContentVersion.isActive   = 1 AND
        (cmContentVersion.contentVersionId > 1207494 OR
         cmContentVersion.modifiedDateTime > '2013-05-05 23:00:00.0' )
ORDER BY cmContentVersion.contentVersionId;

Explain extended gives:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cmContentVersion
         type: ref
possible_keys: PRIMARY,ixlanguageId,ixisActive,IX_DateTime
          key: ixisActive
      key_len: 1
          ref: const
         rows: 318270
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

And here are the indexes on this table:

+------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------
| Table            | Non_unique | Key_name     | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| cmContentVersion |          0 | PRIMARY      |            1 | contentVersionId | A         |      636541 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | contentId    |            1 | contentId        | A         |      127308 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixlanguageId |            1 | languageId       | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixstateId    |            1 | stateId          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixisActive   |            1 | isActive         | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            1 | contentId        | A         |       63654 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            2 | languageId       | A         |       79567 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            3 | stateId          | A         |      159135 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            4 | isActive         | A         |      212180 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

EDIT:

new explain with the indexes suggested here

           id: 1
  select_type: SIMPLE
        table: cmContentVersion
         type: ref
possible_keys: PRIMARY,ixlanguageId,ixisActive,IX_foo,active_lang_contentversion_IX,active_lang_modified_IX
          key: ixlanguageId
      key_len: 4
          ref: const
         rows: 356055
     filtered: 100.00
        Extra: Using where

Conditions with OR are harder for the optimizer than conditions with AND only.

Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range.

Your query has both the above difficulties. Noticing that it is equivalent to this rewriting:

WHERE ( languageId = 3 AND
        isActive   = 1 AND
        contentVersionId > 1207494
      )
   OR ( languageId = 3 AND
        isActive   = 1 AND
        modifiedDateTime > '2013-05-05 23:00:00.0'
      )

your best option is to have an index that can be used for the first part (equalities and one of the 2 range conditions) and another index for the second part (equalities and the other range):

ALTER TABLE cmContentVersion
  ADD INDEX active_lang_contentversion_IX
    (isActive, languageId, contentVersionId),
  ADD INDEX active_lang_modified_IX
    (isActive, languageId, modifiedDateTime) ;

The optimizer might then choose to use both indexes and combine them with an Index-Merge access algorithm (the Sort-Union variation).

You can "force" the index-merge to be used by rewriting the query with derived tables.

SELECT cv.contentVersionId,
       cv.stateId,
       cv.modifiedDateTime,
       cv.versionComment,
       cv.isCheckedOut,
       cv.isActive,
       cv.contentId,
       cv.languageId,
       cv.versionModifier
FROM   cmContentVersion AS cv
  JOIN
    (   SELECT contentVersionId
        FROM cmContentVersion
        WHERE languageId = 3 AND
              isActive   = 1 AND
              contentVersionId > 1207494
      UNION
        SELECT contentVersionId
        FROM cmContentVersion
        WHERE languageId = 3 AND
              isActive   = 1 AND
              modifiedDateTime > '2013-05-05 23:00:00.0'
    ) AS cvi
      ON cvi.contentVersionId = cv.contentVersionId
ORDER BY cv.contentVersionId;

However, and since you have the ORDER BY, a combined index might be better for efficiency. Try adding this index:

(isActive, languageId, contentVersionId, modifiedDateTime)

and running the query as:

SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE ( languageId = 3 AND
        isActive   = 1 AND
        contentVersionId <= 1207494 AND
        modifiedDateTime > '2013-05-05 23:00:00.0'
      )
   OR ( languageId = 3 AND
        isActive   = 1 AND
        contentVersionId > 1207494
      )
ORDER BY cv.contentVersionId;

and as:

SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE   languageId = 3 AND
        isActive   = 1 AND
        contentVersionId <= 1207494 AND
        modifiedDateTime > '2013-05-05 23:00:00.0'
UNION ALL
SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE   languageId = 3 AND
        isActive   = 1 AND
        contentVersionId > 1207494
ORDER BY contentVersionId;


Dividing DBCC CHECKDB over multiple days I'm working on implementing Paul Randal's method of manually spreading DBCC CHECKDB over several days for very large databases, which basically consists of:

Dividing the tables in the database roughly equally between 7 buckets
Running a DBCC CHECKALLOC twice a week
Running a DBCC CHECKCATALOG once a week
Running a DBCC CHECKTABLE on one bucket each day of the week

Has anyone used this technique? Any existing scripts out there?

I'm concerned this may not actually cover everything that CHECKDB does; the Books Online documentation for CHECKDB says that in addition to CHECKALLOC, CHECKCATALOG and CHECKTABLE, it also:

Validates the contents of every indexed view in the database.
Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM. (SQL 2008 only)
Validates the Service Broker data in the database.

So here are my questions:

Are these additional checks necessary/important? (Indexed views are probably a bit more concerning to me, I don't think we are using Service Broker or FILESTREAM yet.)
If so, are there ways to perform these additional checks separately?
CHECKALLOC and CHECKCATALOG seem to run very quickly, even on large dbs. Any reason not to run these every day?

(Note: this will be a standard routine for thousands of existing databases across hundreds of servers, or at least every database over a certain size. This means that options like restructuring all databases to use CHECKFILEGROUP aren't really practical for us.)

DBCC CHECKDB is vital for SQL Server databases to be 100% sure that there is no corruption. However, due to databases growing massive in size, its very hard to find a maintenance window when you claim to be 24x7 up. Over the years, SQL Server team has implemented various mechanisms that will detect most common forms of corruptions especially related to Physical corruption caused by hardware.

SQL Server 2005 and up has PAGE_VERIFY = CHECKSUM which can help you proactively detect physical corruption in database pages thereby adding a checksum to each page as it is written to the I/O system and validates the checksum as it is read from the disk.

Also, backup (full or differential) with CHECKSUM will guarantee to detect any I/O corruption caused by hardware.

Hence, from the hardware side of corruption, SQL Server does a good job of detecting it and reporting it. (Make sure to set important corruption related Alerts as well).

That being said, still logical corruption, scribbler induced errors - where in-memory pages are corrupted either by third-party code running inside the SQL Server process or by drivers or other software with sufficient privileges executing in Windows kernel mode and/or SQL Server Bugs, etc are undetectable using above methods and hence CHECKDB comes into picture.

DBCC CHECKDB performs a more thorough checks that includes checking page headers for possible corruption that are not detectable by any other means.

  Any existing scripts out there?

Instead of reinventing the wheel, I would highly recommend you to take a look at Ola's SQL Server Integrity Check solution

Efficiently running DBCC CHECKDB :

You just need to be creative when you are tight in maintenance window having huge databases or high number of databases to run CHECKDB on.

After attending SQLSkills training, what I have implemented in my environment is :

prioritize on what tables are critical to check.
separate the tables into groups with different priorities and then run DBCC CHECKTABLE along with running DBCC CHECKALLOC and DBCC CHECKCATALOG
Create a worker table that will store the table names with priorities. Just make sure that all high prioritie tables (which are massively big) are not in one group else your CHECKDB will not complete at all.
You can even have a timeout column in your worker table that will orchestrate when your CHECKDB will get killed once it has passed the maintenance window
Add how long it took per table to run DBCC CHECKTABLE,  DBCC CHECKALLOC and DBCC CHECKCATALOG. So that you can get a feel on how long it is usually taking for your checks to run.
You can even run with NOINDEX option as it will speed up the operation as it does not check the Non-Clustered Indexes on user tables. This has some advantage as it is not that critical as Data corruption since no data is lost and you can drop and recreate the Index if necessary.

Obviously, Enterprise edition can take advantage of Parallel execution of DBCC statements, but look out for MAXDOP setting as it might end up taking all your CPU. This can be hard limited by Resource Governor.

Note: If you are having SPARSE column, then your CHECKDB will be dead slow as described here.

Finally, its how to prevent database corruption by utilizing all available tool set + your faith in your database server hardware system and most importantly the value of your data.

Some excellent references :

Is CHECKDB A Necessity?
DBCC Checks and Terabyte-Scale Databases
SQLU VLDB Week – Integrity Checks
Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
SQL Server 2008 Database Checking


How to know which process is causing high memory usage on DB? I've got one Onesight alert mail stating below:

Short Description: Onesight Alert: ZEUS DB Monitor (zeus04)

Description:
Solaris 5.8: vmstat memory(free) - Free Memory for zeus04 2065112   8:06:02 AM IST    2 min
  Free Memory below 4GB: Value was 3,965,488 , threshold is 4194304.0 - 12:36:02 AM IST

Upon issuing OS level commands below I got the following understanding:

prstat

This command returned me the top process ID(3578) which is using more CPU. then,

prstat -Lm -p 3578

this command returned me the following output:

PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWPID
3578 oracle 0.7 2.3 1.6 0.0 0.0 90 4.4 1.5 483 235 1K 0 java/838694
3578 oracle 0.3 0.0 0.2 0.0 0.0 95 1.5 0.1 52 8 41 0 java/839336

I thought some Java process would be running so asked the Apps Support team and the said:

we believe this process is related to the Oracle OEM agent Java process

Now how do i drill down the issue and resolve because free memory is going below 2GB almost everyday.

DB environment details:
OS:Solaris 5.8(Unix)
DB version: 10.2.0.5
Memory size:64 GB

Regards,

Ritu

The software you use is out of date. An advantage of that is that there could very well be a solution provided in My Oracle Support. The Oracle OEM agent have lot's of problems in many versions. Prepare for patching the agents or use a daily restart agent procedure. The Oracle OEM agent is part of the Grid Control monitoring infra structure so you could take it down without harming the application that is using the database[s] that the agent is monitoring.


Is it possible to automate the configuration of dataguard broker? We deploy a number of oracle databases every year and a good percentage of them require a dataguard configuration.

I've developed a script which is able to perform the database synchronization very well:

modify init.ora / generate spfile
generate listener.ora
generate tnsnames.ora
create standby control file
create standby redo log
gather all files in a tar ball ready to be shipped to the standby server.

On the standby server another script:

Dispatch the files different data files, control files, redo logs to where they must be
Modifies the init.ora and generate the spfile
generate listener.ora
generate tnsnames.ora
run the recover managed standby database command

Then I hit a wall on data guard manager :( How can I automate its configuration.

It would be really great if I could do something like this:

dgmgrl sys/manager -e "create configuration broker1 as primary database is 'primary' connect identifier is to_primary;"

I haven't seen anything like that.

Anybody has an idea of what could be done to perform this automatically?

Actually, never mind, I've just found the answer ^^

I just had to google the right thing!
http://docs.oracle.com/cd/B19306_01/server.102/b14230/dgmgrl.htm

8.1.1 DGMGRL Optional Parameters

DGMGRL sys/knl_test7@primary "show database 'North_Sales'"


Handling duplicate values using trigger I have a database where tables are like:

tblCustomer(UserID [Primary Key],Facebook,Twitter,PhoneNum);
tblSales(InvoiceID [Primary Key],CustomerID [Foreign Key],ProductID [Foreign Key]);

I'm importing some paper-based records and they're in chronological (time) order, with following columns:

(Customer's)FaceBook,Twitter,PhoneNum,ProductID;

For some reason we don't have existing ID systems for customers so UserID will be auto-generated at import-time. In my scenario, any of Facebook, Twitter or Phone number can uniquely identify a customer so I have unique index for each of them to enforce uniqueness constraint.

I created a view to facilitate data import:

viewDataEntry(FaceBook,Twitter,PhoneNum,ProductID);

A common case is that customer's Facebook (or other contact method) appears in multiple sales records. A trigger is created to handle such cases:

CREATE TRIGGER
ON dbo.viewDataEntry
INSTEAD OF INSERT
AS

BEGIN TRY
INSERT INTO dbo.tblCustomer(Facebook,Twitter,PhoneNum)
SELECT Facebook,Twitter,PhoneNum FROM inserted;
END TRY

BEGIN CATCH
IF ERROR_NUMBER() != 2601 --To ignore uniqueness violation exception
THROW;
END CATCH

DECLARE @UserID INT;
SET @UserID = (SELECT UserID FROM dbo.tblCustomer AS O,inserted AS I WHERE (O.PhoneNum = I.PhoneNum OR O.Facebook = I.Facebook OR O.Twitter = I.Twitter));

INSERT INTO dbo.tblSales(CustomerID,ProductID)
SELECT @UserID,ProductID FROM inserted;

GO

Intended outcome is:

If duplicate Customer records are imported, drop duplicate, insert into sales table only;
If new Customer record is imported, create record for both customer and sales table.

However whenever duplicate values are entered I run into Error 3910 or 3616, which means the transaction is un-committable. I think it's because insertion into customer table need to be rolled back and I know that I cannot rollback part of the transaction while keeping the remaining part (which is, unfortunately, the intended outcome).

I found MERGE statement but it has too many restrictions (like WHEN MATCHED must be followed by UPDATE and DELETE).

Please kindly provide any working solution.

It's generally best to use stored procedures rather than triggers for business rules. Triggers are more appropriate to enforce data integrity rules in cases where declarative constraints can't be used.

Below is an example that uses a stored procedure instead of a trigger. See inline comments for considerations.

CREATE TABLE dbo.tblCustomer(
      UserID int NOT NULL IDENTITY
        CONSTRAINT PK_tblCustomer PRIMARY KEY CLUSTERED
    , Facebook varchar(100) NULL
    , Twitter varchar(100) NULL
    , PhoneNum varchar(20) NULL
    , CONSTRAINT CK_tblCustomer_not_all_null CHECK (COALESCE(Facebook,Twitter,PhoneNum) IS NOT NULL)
    );
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_Facebook ON dbo.tblCustomer(Facebook) WHERE Facebook IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_Twitter ON dbo.tblCustomer(Twitter) WHERE Twitter IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_tblCustomer_PhoneNum ON dbo.tblCustomer(PhoneNum) WHERE PhoneNum IS NOT NULL;

CREATE TABLE dbo.tblSales(
      InvoiceID int NOT NULL IDENTITY
        CONSTRAINT PK_tblSales PRIMARY KEY
    , CustomerID int NOT NULL
        CONSTRAINT FK_tblSales_tblCustomer
        FOREIGN KEY (CustomerID) REFERENCES dbo.tblCustomer(UserID)
    , ProductID int NOT NULL
--      CONSTRAINT FK_tblSales_tblProduct
--      FOREIGN KEY REFERENCES dbo.tblProduct(ProductID)
    );
GO

CREATE OR ALTER PROCEDURE dbo.usp_InsertCustomerSale
      @ProductID int
    , @Facebook varchar(100)
    , @Twitter varchar(100)
    , @PhoneNum varchar(100)
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    --This statement will error with a 'subquery returned more than one value' if more than one customer already has these alternate keys.
    DECLARE @UserID int = (
        SELECT UserID
        FROM dbo.tblCustomer WITH(UPDLOCK, HOLDLOCK) --locking hint to serialize concurrent insert attempts
        WHERE
               Facebook = @Facebook
            OR Twitter = @Twitter
            OR PhoneNum = @PhoneNum
        );

    IF @UserID IS NULL
    BEGIN
            --insert new customer row and get assigned identity value
        INSERT INTO dbo.tblCustomer(Facebook, Twitter, PhoneNum)
            VALUES(@Facebook, @Twitter, @PhoneNum);
        SET @UserID = SCOPE_IDENTITY();
    END;

    --insert sales row
    INSERT INTO dbo.tblSales(CustomerID,ProductID)
        VALUES (@UserID, @ProductID);

    COMMIT;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO

--insert customer and sale for customer 1
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 1
    , @Facebook = 'fb1'
    , @Twitter = 'tw1'
    , @PhoneNum = '(111)111-1111';
--insert customer and sale for customer 2
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 2
    , @Facebook = 'fb2'
    , @Twitter = 'tw2'
    , @PhoneNum = '(222)222-2222';

--insert sale only for customer 2 (with no natural key on sales table, nothing will prevent dup sales)
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 2
    , @Facebook = 'fb2'
    , @Twitter = 'tw2'
    , @PhoneNum = '(222)222-2222';

--this will err due to ambiguous customer (customer 1 has phone and customer 2 has facebook and twitter
EXEC dbo.usp_InsertCustomerSale
      @ProductID = 3
    , @Facebook = 'fb2'
    , @Twitter = 'tw2'
    , @PhoneNum = '(111)111-1111';


Update reps table with right values in a trigger after insert on userterritory I two DBs veeva and pdone as shown below in the pics

As you can see veeva is simple and hasn't no constraints which relies on duplicate and wrong values. The problem happen exactly on userterritory where a user is assigned to one or more territory. In the new DB pdone that is not allowed so I need to find - from veeva - those reps that has only one territory and I got that through this query:

    SELECT
        count(*) AS reps,
        userid,
        lastmodifieddate,
        territoryid
    FROM
        (
            SELECT
                userid,
                territoryid,
                count(*) AS territories
            FROM
                userterritory
            GROUP BY
                userid
            HAVING
                territories = 1
        ) T1
    INNER JOIN (
        SELECT
            id AS userid,
            lastmodifieddate
        FROM
            `user`
        WHERE
            `user`.`id` IN (
                SELECT
                    userterritory.`userid`
                FROM
                    userterritory
            )
    ) T2 USING (userid)
    GROUP BY
        territoryid
    HAVING
        reps = 1

After each INSERT on userterritory I execute a trigger that update reps table by setting up the right territories_id based on relationship from userterritory. This is the code for the trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
    # [ VARIABLES DECLARATION ]
    DECLARE vUserID VARCHAR(18) DEFAULT '';
    DECLARE vTerritoriRep VARCHAR(45) DEFAULT '';
    DECLARE vTerrytoriLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
    # [ VARIABLES ASIGNATION ]
    SET vUserID         =NEW.userid;
    SET vTerritoriRep   =(select IFNULL(reps.territories_id,'N/A') from pdone.reps where reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerrytoriLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);

IF (NEW.territoryid != vTerritoriRep) THEN
    UPDATE pdone.reps
    SET reps.territories_id = NEW.territoryid,
        reps.lastSyncAt = IF(NOW()>vTerrytoriLastMod,NOW(),reps.lastSyncAt),
        reps.updatedAt=now()
    WHERE reps.veeva_rep_id=vUserID;
END IF;
END

All is working perfectly, but I am getting reps with territories_id nulled and that is not allowed since each reps has to be assigned to one territories. See this pic:

So, how do I apply my query to the trigger in order to get right results? Can any give me some help?

I edited your TRIGGER to do this:

I added the vTerritoryCount variable.
If you have more than 1 rows with the same userid it wont update the reps.territories_id.

Here's the TRIGGER:

USE `veeva_new`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW
BEGIN
    # [ VARIABLES DECLARATION ]
    DECLARE vUserID VARCHAR(18) DEFAULT '';
    DECLARE vTerritoryRep VARCHAR(45) DEFAULT '';
    DECLARE vTerritoryLastMod DATETIME DEFAULT '0000-00-00 00:00:00';
    DECLARE vTerritoryCount INT(7) DEFAULT 0; #[060715.01]
    # [ VARIABLES ASIGNATION ]
    SET vUserID         =NEW.userid;
    SET vTerritoryRep   =(SELECT IFNULL(reps.territories_id,'N/A') FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerritoryLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=vUserID LIMIT 0,1);
    SET vTerritoryCount =(SELECT COUNT(*) FROM veeva_new.userterritory WHERE userterritory.userid=vUserID);
IF (NEW.territoryid != vTerritoryRep) THEN # [ IF THE NEW TERRITORYID IS DIFFERENT FROM TERRIROTYID IN reps TABLE ]
    IF (vTerritoryCount>1) THEN
        SET @Blah='There\'s more than 1 rows, I\'m not going to do anything.';
    ELSE
        UPDATE pdone.reps
        SET reps.territories_id = NEW.territoryid,
            reps.lastSyncAt = IF(NOW()>vTerritoryLastMod,NOW(),reps.lastSyncAt),
            reps.updatedAt=now()
        WHERE reps.veeva_rep_id=vUserID;
    END IF;
END IF;
END


Is it possible to increase query performance on a narrow table with millions of rows? I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as it gets?

The Query

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

The Table

CREATE TABLE [dbo].[Heartbeats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [IsPUp] [bit] NOT NULL,
    [IsWebUp] [bit] NOT NULL,
    [IsPingUp] [bit] NOT NULL,
    [DateEntered] [datetime] NOT NULL,
 CONSTRAINT [PK_Heartbeats] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The Index

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats]
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Would adding additional indexes help? If so, what would they look like? The current performance is acceptable, because the query is only run occasionally, but I'm wondering as a learning exercise, is there anything I can do to make this faster?

UPDATE

When I change the query to use a force index hint, the query executes in 50ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats] WITH(INDEX(CommonQueryIndex))
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'

Adding a correctly selective DeviceID clause also hits the 50ms range:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31' AND DeviceID = 4;

If I add ORDER BY [DateEntered], [DeviceID] to the original query, I am in the 50ms range:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'
ORDER BY [DateEntered], [DeviceID];

These all use the index I was expecting (CommonQueryIndex) so, I suppose my question is now, is there a way to force this index to be used on queries like this? Or is the size of my table throwing off the optimizer too much and I must just use an ORDER BY or a hint?

Why the the optimiser doesn't go for your your first index:

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats]
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Is a matter of selectivity of the [DateEntered] Column.

You told us that your table has 44 million rows. the row size is:

4 bytes, for the ID, 4 bytes for the Device ID, 8 bytes for the date, and 1 byte for the 4 bit columns. that's 17 bytes + 7 bytes overhead for (tags, Null bitmap, var col offset,,col count) totals 24 Bytes per row.

That would rougly translate to 140k pages. To store those 44 million rows.

Now the optimiser can do two things:

It could scan the table (clustered index scan)
Or it could use your index. For every row in your index, it would
then need to do a bookmark lookup in the clustered index.

Now at a certain point it just becomes more expensive to do all these single lookups in the clustered index for every index entry found in your non clustered index. The threshold for that is generally the total count of lookups should exceed 25% tot 33% of the total table page count.

So in this case: 140k/25%=35000 rows 140k/33%=46666 rows.

(@RBarryYoung, 35k is 0.08% of the total rows and 46666 is 0.10 %, so I think that is where the confusion was)

So if your where clause will result in somewhere between 35000 and 46666 rows.(this is underneath the top clause!) It's very likely that your non clustered will not be used and that the clustered index scan will be used.

The only two ways to change this are:

Make your where clause more selective. (if possible)
Drop the * and select only a few columns so you can use a covering
index.

now sure you could create a covering index even when you use a select *. Hoever that just creates a massive overhead for your inserts/updates/deletes. We would have to know more about your work load (read vs write) to make sure if that's the best solution.

Changing from datetime to smalldatetime is a 16% reducion in size on clustered index and a 24% reduction in size on your non clustered index.


Using sp_send_dbmail with SQL authentication to send attachments from UNC path I'm trying to send emails with attachments using a SQL authentication account that I have granted credentials to.

The login used in the stored Credentials is a domain account that has permissions to read from the share.

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'me@abc.xyz.nz',
    @profile_name = 'Alarms',
    @subject = 'Test with Attachment',
    @body = '*** Alarms ***' ,
    @file_attachments = '\\Servername\Share\Images\TestImage.png'

But when I use the SQL login I get the following error:

  Msg 22051, Level 16, State 1, Line 26 The client connection security
  context could not be impersonated. Attaching files require an
  integrated client login

What have I missed? how can I test that the credentials have been applied correctly? Does this only work for a local share?

Edit: I granted my SQL login Sysadmin privs and it now works with both a local share and with a remote share. So what privs does sysadmin have that allows this to work?

I managed to get this working eventually in my Dev environment.

Enable xp_cmdshell
Add proxy account DomainLogin
grant execute on xp_cmdshell to SQLLogin
Grant logon as a batch job on Server
On server open Secpol. > Security Settings > Local Policies – User Rights Assignment node. Add DomainLogin
Create SQL Credential "CredForMail" using DomainLogin
Grant Credential to : ALTER LOGIN SQLLogin ADD CREDENTIAL CredForMail
ALTER DATABASE DatabaseName SET TRUSTWORTHY ON

The end result is that I have a non-sysadmin user that can send emails with attachments it has picked up from a share on a remote server.

I think the proxy account and Credential may be overlapping and both may not be necesary. I have tried so many things I am no-longer sure of the bare minimum required to make this work. (and that bothers me)

Because of the complexity to set this up, enabling features that could be seen as a security risk, I am going to back all of this out, and instead will separate out this application function and give it it's own domain account.


Big lists in a relational DB I need to store a list of items and that list belongs to a single user. The lists are going to be huge, so this has to be done as efficiently as possible. The lists also have to be modifiable to a small degree (able to add new items).

This is my (possible) solution:
Have a table items, where we store the data of our items. Table lists contains info about our lists (name, user_id etc). Table lists_lengths contains our lists lengths, e.g:

list_id | item_id_start | item_id_end
------------------------------------
1       | 1             | 200
2       | 201           | 220
1       | 221           | 300

If the lists get a lot of modifications, this might end up being a poor design though. Is this good or is the blatant list_id in the items table just the way to go?

Once the table gets really big, you will find that looking up an item to find out what list it is in becomes painfully slow.

I discuss that, plus provide efficient code, in my blog on ip-ranges.  Your use case (non-overlapping ranges of items "owned" by various "lists") seems to be the same problem.


how to remove/deal with locks in a MariaDB Galera cluster I have a cluster of 3 nodes running MariaDB Galera cluster, cluster works as expected, I can reboot a single node at a time and in the best case, IST will be used or SST(using rsync) to sync back the node, all good so far.

I noticed that some DELETES or DDL's as described here: http://galeracluster.com/documentation-webpages/schemaupgrades.html, can lock the full cluster, therefore when possible and having access to the 3 nodes, I started to follow this procedure:

SET wsrep_OSU_method='RSU';
Run the ALTER/DELETE statement.
SET wsrep_OSU_method='TOI';

The problem with this approach is that I need to run the procedure in all the 3 nodes and in some cases this is not possible since the full cluster is exposed using an HAproxy.

The more the database grows and if "accidentally" someone runs a DELETE or ALTER instead of trying to follow the "RSU/TOI" procedure, the whole database locks/blocks (no read/writes) and start affecting other databases within the cluster. An ugly but quick solution after "guessing" the node where the query that is locking is by running this query:

show open tables where in_use > 0;

Rebooting the node in question helps, and hopefully, the node will resync using IST like mentioned before and worst case SST. Restarting the mysqld process works but takes to much time that is better to just reboot the full VM since is faster to have the cluster unlocked and usable again.

Is not possible also to kill the query:

ERROR 1095 (HY000): You are not owner of thread 70760372

This "workaround" has become a bad habit, therefore, wondering what other techniques or procedures can be used to unlock the cluster in a graceful, clean and pragmatically way (something that could be automated) or even better what options/settings could help to prevent against the locks?

A long term solution could be to use a Master-slave setup, in where the "RSU/TOI" could be avoided, but before going that further, I would like to fine tune better the current cluster setup.

The only way that I found to fix this issue is to restart the whole cluster in the original order as restarting a single node just fails. I'm not sure why this is a thing in Galera, it seems to be very sensitive to errors like this. In my opinion a database cluster should just work no matter what queries you throw at it.

I guess it's something of a free add-on when you go the Galera road, unfortunately.