Looking for performance improvements for this query This query is taking a long time to execute. We are expecting results in 0 sec, if possible.

Please help me to do any other following:

Rewrite the query
Suggest any indexes
Any other optimization that may be needed.

Also, please help isolate root cause.

Here is the Query And Explain Plan:

mysql> explain SELECT * FROM (`xgen_studentMLs`)
    LEFT JOIN `xgen_quizzes`
         ON xgen_studentMLs.quizid = xgen_quizzes.idnum
    WHERE
          `student` = 27126
      AND xgen_studentMLs.topic = 1829
      AND xgen_studentMLs.metatype = 'topic'
      AND (xgen_studentMLs.department='Nursing' OR xgen_studentMLs.department='Biology')
      AND (xgen_quizzes.book IS NULL OR xgen_quizzes.book=0)
    ORDER BY xgen_studentMLs.timestamp DESC LIMIT 100;

+----+-------------+-----------------+--------+---------------+---------+---------+------------------------------+------+-----------------------------+
| id | select_type | table           | type   | possible_keys | key     | key_len | ref                          | rows | Extra                       |
+----+-------------+-----------------+--------+---------------+---------+---------+------------------------------+------+-----------------------------+
|  1 | SIMPLE      | xgen_studentMLs | range  | student,mult  | mult    | 265     | NULL                         |   18 | Using where; Using filesort |
|  1 | SIMPLE      | xgen_quizzes    | eq_ref | PRIMARY       | PRIMARY | 4       | prepu.xgen_studentMLs.quizid |    1 | Using where                 |
+----+-------------+-----------------+--------+---------------+---------+---------+------------------------------+------+-----------------------------+

Here are table structures:

mysql> show create table xgen_quizzes\G
*************************** 1. row ***************************
       Table: xgen_quizzes
Create Table: CREATE TABLE `xgen_quizzes` (
  `idnum` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0',
  `timestarted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `timefinished` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `questionlist` mediumtext NOT NULL,
  `topics` mediumtext NOT NULL,
  `totalnum` int(11) NOT NULL DEFAULT '0',
  `completednum` int(11) NOT NULL DEFAULT '0',
  `assignment` int(11) NOT NULL DEFAULT '0',
  `department` varchar(255) NOT NULL DEFAULT '',
  `book` int(11) NOT NULL DEFAULT '0',
  `cqs` mediumtext NOT NULL,
  `metatype` varchar(25) DEFAULT 'topic',
  PRIMARY KEY (`idnum`),
  KEY `userid` (`userid`),
  KEY `assignment` (`assignment`)
) ENGINE=InnoDB AUTO_INCREMENT=13547573 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table xgen_studentMLs\G
*************************** 1. row ***************************
       Table: xgen_studentMLs
Create Table: CREATE TABLE `xgen_studentMLs` (
  `student` int(11) NOT NULL,
  `topic` int(11) NOT NULL,
  `ML` float NOT NULL,
  `MI` int(3) NOT NULL DEFAULT '0',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `department` varchar(255) NOT NULL,
  `metatype` varchar(25) DEFAULT 'topic',
  `quizid` int(11) NOT NULL DEFAULT '0',
  KEY `student` (`student`),
  KEY `mult` (`topic`,`department`,`student`,`timestamp`,`ML`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Here is your query refactored

SELECT * FROM
(SELECT * FROM `xgen_studentMLs`
WHERE student = 27126 AND topic = 1829 AND metatype = 'topic'
AND department IN ('Nursing','Biology')
ORDER BY timestamp DESC LIMIT 100) A
LEFT JOIN xgen_quizzes Q ON A.quizid = Q.idnum
WHERE IFNULL(Q.book,0) = 0;

The subquery is meant to retrieve only 100 rows before doing the LEFT JOIN. You will need an index to assist the subquery

ALTER TABLE xgen_studentMLs ADD INDEX new_ndx (student,metatype,topic,department);

Give it a Try !!!


Launchctl in MongoDB I'm trying to configure MongoDB in OS X Mountain Lion. I've installed with Homebrew; I have followed lots of tutorials, but I have got an issue which I haven't been able to solved.

The issue is related with launchctl unload and launchctl load commands. My homebrew.mxcl.mongodb.plist is located at /usr/local/Cellar/mongodb/2.4.6/ and it contains the following code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>Label</key>
  <string>homebrew.mxcl.mongodb</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/Cellar/mongodb/2.4.6/mongod</string>
    <string>run</string>
    <string>--config</string>
    <string>/usr/local/Cellar/mongodb/2.4.6/mongod.conf</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>KeepAlive</key>
  <false/>
  <key>WorkingDirectory</key>
  <string>/usr/local</string>
  <key>StandardErrorPath</key>
  <string>/usr/local/Cellar/mongodb/2.4.6/data/log/mongod.log</string>
  <key>StandardOutPath</key>
  <string>/usr/local/Cellar/mongodb/2.4.6/data/log/mongod.log</string>
  <key>HardResourceLimits</key>
  <dict>
    <key>NumberOfFiles</key>
    <integer>1024</integer>
  </dict>
  <key>SoftResourceLimits</key>
  <dict>
    <key>NumberOfFiles</key>
    <integer>1024</integer>
  </dict>
</dict>
</plist>

If I executed sudo launchctl load -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist, in http://localhost:28017/ appears MongoDB's http diagnostic access information. But if I execute sudo launchctl unload -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist does not disconnected and appears the same in the http://localhost:28017/ url.

To shutdown Mongo I've need the following commands:

use admin
db.shutdownServer()

How can I do to shutdown Mongo automatically with launchctl unload command?

I tested this, first I installed MongoDB 2.4.6 with brew, and then used launchctl to load and unload.  In my testing, it sends a SIGTERM to the mongod process which then shuts down as expected.  Here are the commands I used as well as the logs for the mongod process:

Commands:

launchctl load -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist
launchctl unload -w /usr/local/Cellar/mongodb/2.4.6/homebrew.mxcl.mongodb.plist

Logs:

tail -f /usr/local/var/log/mongodb/mongo.log
Tue Oct 22 17:33:32.774 [initandlisten] MongoDB starting : pid=13192 port=27017 dbpath=/usr/local/var/mongodb 64-bit host=adamc-mbp.local
Tue Oct 22 17:33:32.774 [initandlisten] db version v2.4.6
Tue Oct 22 17:33:32.774 [initandlisten] git version: nogitversion
Tue Oct 22 17:33:32.774 [initandlisten] build info: Darwin minimountain.local 12.4.0 Darwin Kernel Version 12.4.0: Wed May  1 17:57:12 PDT 2013; root:xnu-2050.24.15~1/RELEASE_X86_64 x86_64 BOOST_LIB_VERSION=1_49
Tue Oct 22 17:33:32.774 [initandlisten] allocator: tcmalloc
Tue Oct 22 17:33:32.774 [initandlisten] options: { bind_ip: "127.0.0.1", command: [ "run" ], config: "/usr/local/etc/mongod.conf", dbpath: "/usr/local/var/mongodb", logappend: "true", logpath: "/usr/local/var/log/mongodb/mongo.log" }
Tue Oct 22 17:33:32.775 [initandlisten] journal dir=/usr/local/var/mongodb/journal
Tue Oct 22 17:33:32.775 [initandlisten] recover : no journal files present, no recovery needed
Tue Oct 22 17:33:32.806 [websvr] admin web console waiting for connections on port 28017
Tue Oct 22 17:33:32.806 [initandlisten] waiting for connections on port 27017
Tue Oct 22 17:34:21.682 [signalProcessingThread] got signal 15 (Terminated: 15), will terminate after current cmd ends
Tue Oct 22 17:34:21.682 [signalProcessingThread] now exiting
Tue Oct 22 17:34:21.682 dbexit:
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: going to close listening sockets...
Tue Oct 22 17:34:21.682 [signalProcessingThread] closing listening socket: 9
Tue Oct 22 17:34:21.682 [signalProcessingThread] closing listening socket: 10
Tue Oct 22 17:34:21.682 [signalProcessingThread] closing listening socket: 11
Tue Oct 22 17:34:21.682 [signalProcessingThread] removing socket file: /tmp/mongodb-27017.sock
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: going to flush diaglog...
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: going to close sockets...
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: waiting for fs preallocator...
Tue Oct 22 17:34:21.682 [signalProcessingThread] shutdown: lock for final commit...
Tue Oct 22 17:34:21.683 [signalProcessingThread] shutdown: final commit...
Tue Oct 22 17:34:21.692 [signalProcessingThread] shutdown: closing all files...
Tue Oct 22 17:34:21.692 [signalProcessingThread] closeAllFiles() finished
Tue Oct 22 17:34:21.692 [signalProcessingThread] journalCleanup...
Tue Oct 22 17:34:21.692 [signalProcessingThread] removeJournalFiles
Tue Oct 22 17:34:21.692 [signalProcessingThread] shutdown: removing fs lock...
Tue Oct 22 17:34:21.693 dbexit: really exiting now

I did this several times to confirm the behavior.  In Chrome at least the status page no longer responds and I receive an error (as expected) once it has been shut down.

The only difference between what I am doing and what you have posted is that I am not using sudo (in fact it refuses to load or unload due to dubious ownership of the file).  So, I changed the ownership of the plist file to root and tried sudo with the same results.

The only way I was able to recreate a failure to unload was as follows:

Start with sudo launchctl (root is the owner of the plist file)
Change ownership of the plist file back to my regular user
Try to unload without sudo

This fails with an error however:

launchctl: Error unloading: homebrew.mxcl.mongodb

Note: Subsequently changing the ownership back to the regular user made the unload successful

Similarly, this also produces the same error:

Start without sudo (regular user owns the plist file)
Change ownership of the plist file to root
Try to unload with sudo

I was unable to recreate the silent failure you seem to be having with any of the various combinations I tried.

Some information gathering tips which may give you a clue:

When you launch with launchctl, what does the output of this command say: launchctl list | grep mongodb? (it should list something like 13340  -   homebrew.mxcl.mongodb
If you run this same command after you run unload (without error), it should show the exit status in that middle column (-15)
Sometimes it can take a while for MongoDB to exit - so tail the log (see example above), see if the TERM signal is being received
Why is it you are using sudo?  Have you installed brew as root?  If so this might be at the core of the issue here - generally it is not recommended to run MongoDB as root.


clp appends bytes to output I am migrating a number of databases to UTF-8, and just discovered phenomena that I was not aware of. When selecting data out to the terminal, additional bytes are added to the output. Example:

~]$ x=$(db2 -x "values 'a'")
~]$ echo "${x}b"
a b

One additional space after a

~]$ x=$(db2 -x "values 'aa'")
echo "${x}b"
aa  b

Two additional space after aa

It does not seem to matter how many bytes a character occupies in utf8:

~]$ x=$(db2 -x "values ''")
~]$ echo "${x}b"
 b

One additional space after g-clef

~]$ x=$(db2 -x "values ''")
~]$ echo "${x}b"
  b

Two additional space after g-clef g-clef

db cfg:

Database territory                                      = SE
Database code page                                      = 1208
Database code set                                       = UTF8
Database country/region code                            = 46
Database collating sequence                             = SYSTEM_819_SE

The terminal has encoding UTF8 (tried terminator and gnome-terminal), and before connecting to the database I did:

export LC_CTYPE=sv_SE.utf8

The above is of course just silly examples, but I have a fair amount of tests in scripts similar to that:

dbtype=`db2 -x "values nya.get_db_type()"`
if [ "${dbtype}" = "N" ]; then
    ...

where I need to change the test one way or another.

Any thoughts on a configuration, that would get rid of the extra bytes?

~]$ uname -a
Linux nya-ladok3-release 3.10.0-1062.9.1.el7.x86_64 #1 SMP Mon Dec 2 08:31:54 EST 2019 x86_64 x86_64 x86_64 GNU/Linux
~]$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL11050" with level
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.5".

This is the explanation I got from IBM support, I have not tested it myself, but it seems reasonable. The workaround suggested will work for all situations that I can think of right now:
The extra spaces padded in CLP is the expected behavior. This is because there are some multiple-byte characters that take more than 1-physical space. See below example that demonstrates the same:
Say, U+FF2D FULLWIDTH LATIN CAPITAL LETTER M
$ db2 "select a, hex(a) from table(values('A' || u&'\FF2D' || 'B'),('A B')) t(a)"
A   2
----- ----------
AＭB 41EFBCAD42 —> Here wide-M is taking more space than a normal multi/single byte character.
A B  41204220202 record(s) selected.

If you are using a lot of functions like get_db_type() in your scripts, you can change the return value in these functions to modify it to OCTETS:
Something like the following:
CREATE OR REPLACE FUNCTION get_db_type()
RETURNS VARCHAR(1 OCTETS)
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
    RETURN 'N';

END @

I'll leave the current workaround as is below:
If nothing else shows up I'll probably do something along the lines of:
x=$(db2 -x "values ('')")
x="${x%"${x##*[![:space:]]}"}"
echo ${x}b
b

At first, I figured:
x=$(db2 -x "values ('')" | xargs echo)

would do, but the pipe introduces a subshell, so:
echo ${x}b
SQL1024N A database connection does not exist. SQLSTATE=08003b

it does not work.


Experience using ScaleArc in test or production? Has anyone had any experience using ScaleArc?

My CTO has asked my thoughts on it, and I have seen no information out there regarding real-world experiences.

HA HA HA HA What a coincidence. I was asked to evaluate it myself.

Here is what has been done so far...

My supervisor installed it
I created two EC2 instances
I have created Master/Slave status between them
I create the Cluster on ScaleArc EC2

I submitted the Master EC2 as a ScaleArc Master
I submitted the Slave EC2 as a ScaleArc Slave

I adjusted the Security Options for port 3306 on the Cluster

Here is what the rudimentary test I performed: I decided to connect to MySQL, not using the Master EC2, and not using the Slave EC2, but using only the ScaleArc EC2. Here is a script to wrote up to test connectivity:

[redwards@lw-lts-155 ec2]$ cat master_select
EC2_DNS=<ScaleArc-EC2-DNS Hidden>
MYSQL_USER=<<Username Hidden>
MYSQL_PASS=<Password Hidden>
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql -h${EC2_DNS} ${MYSQL_CONN} -ANe"show variables like 'hostname'"

Here is the result:

[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-205-21-233 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-205-21-233 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-205-21-233 |
+----------+------------------+
[redwards@lw-lts-155 ec2]$ ./master_select
+----------+------------------+
| hostname | ip-10-242-47-176 |
+----------+------------------+

Hey, not bad. Slave was reached 7 out of 10 times.

At the very least, that is what one would want: Using just one address, connect to MySQL and have some roving intelligence decide which DB Server to connect to. This has been the pipe dream of all avid prognosticators of MySQL Proxy:

Dec 17, 2012 : MySQL proxy to replication configuration
Apr 19, 2012 : What's the latest on MySQL Proxy?
Jan 24, 2012 : Using Replication for Scale-Out

Whether they implemented MySQL Proxy using LUA or scripted it in some proprietary way, the bottom line is this : ScaleArc has achieved this and has done the heavy lifting for us all (at least in the AWS Universe).

I would say : Go ahead and play with it as follows:

Setup one Master and three Slaves
Register the Master and Slaves in ScaleArc
Write up some benchmark against the Cluster
Use the charts provided and see if it intelligently does the load balancing

Sorry, I am no expert with ScaleArc and this not a full answer for you. Test drive it and kick the tires some.

UPDATE 2013-01-14 16:32 EDT

I downloaded the sakila database and loaded it into the Cluster.

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

Then, I ran this query

SELECT actor_id FROM sakila.actor
ORDER BY MD5(CONCAT(last_name,first_name))

and stored the results in actor_id.txt

Next, I constructed the following script to launch mysql many, many times, in testbeds..

EC2_DNS=<ScaleArc-EC2-DNS Hidden>
MYSQL_USER=<Username Hidden>
MYSQL_PASS=<Password Hidden>
MYSQL_CONN="-h${EC2_DNS} -u${MYSQL_USER} -p${MYSQL_PASS} -Dsakila"
NUMBER_OF_TESTBEDS=${1}
PARALLEL_TESTS=${2}
if [ "${1}" == "" ] ; then NUMBER_OF_TESTBEDS=1 ; fi
if [ "${2}" == "" ] ; then PARALLEL_TESTS=1     ; fi
ACTOR_LIST=""
for ACTOR_ID in `cat actor_id.txt`
do
        ACTOR_LIST="${ACTOR_LIST} ${ACTOR_ID}"
done
SQLPREFIX="SELECT F.film_id,F.title FROM actor A"
SQLPREFIX="${SQLPREFIX} INNER JOIN film_actor FA USING (actor_id)"
SQLPREFIX="${SQLPREFIX} INNER JOIN film F USING (film_id) WHERE A.actor_id="
for (( X = 1 ; X <= NUMBER_OF_TESTBEDS ; X++ ))
do
        echo -n "Launching TestBed #${X} ("
        for (( Y = 1 ; Y <= PARALLEL_TESTS ; Y++ ))
        do
                echo -n " ${Y}"
                for ACTOR_ID in `echo "${ACTOR_LIST}"`
                do
                        SQLSTMT="${SQLPREFIX}${ACTOR_ID}"
                        mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" /dev/null 2>/dev/null &
                done
        done
        echo -n ")"
        wait
        echo
done

Hope This Helps !!!


Using group by with left join and IN yields empty result I have two tables: invitees and invitee_information. invitees belong to events (third table event_id is a foreign key in invitees table).

I want to get the total count of invitees per event for which there is no information available yet (no record in the invitee_information table).

When I write the query using only one event_id in the where clause and anti-join pattern (IS NULL for invitee_id column to filter for rows having no information saved) the count is returned as expected for that given event_id:

SELECT COUNT(i.id)
FROM invitees i
LEFT JOIN invitee_information ii
ON ii.invitee_id = i.id
WHERE ii.invitee_id IS NULL AND i.`event_id` = 18571 AND i.`invitation_sent` = 1;

Now in order to optimize the query to get this count data at DB level for multiple event_ids instead of getting this in a loop for each event_id, I used IN and passed multiple event_ids and used GROUP_BY event_id.

Expected result should be (if there is no data saved in invitee_information table for event_id 18569 :

event_id | count(i.id)
18569    |          0

But query result is always empty. My updated query is:

SELECT i.`event_id`, COUNT(i.id)
FROM invitees i
LEFT JOIN invitee_information ii
ON i.id = ii.invitee_id
WHERE ii.invitee_id IS NULL AND i.`event_id` IN(18569,18571) AND i.`invitation_sent` = 1
GROUP BY i.`event_id`;

Not sure about your question but are you after this kind of result ?

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |

If yes, then you need to use a SUM(IF(ii.invitee_id IS NULL, 1,0))

SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
WHERE

  i.`event_id` IN(18569,18571,20000)
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Full answer :

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE invitees
    (`id` int, `event_id` int, `invitation_sent` int)
;

INSERT INTO invitees
    (`id`, `event_id`, `invitation_sent`)
VALUES
    (1, 18569, 1),
    (2, 18569, 1),
    (3, 18569, 1),
    (4, 18569, 1),
    (5, 18571, 1),
    (6, 18571, 1),
    (7, 10000, 0),
    (8, 10000, 0),
    (9, 20000, 1)
;

CREATE TABLE events
    (`id` int)
;

INSERT INTO events
    (`id`)
VALUES
    (18569),
    (18571),
    (10000),
    (20000)
;

CREATE TABLE invitee_information
    (`invitee_id` int)
;

INSERT INTO invitee_information
    (`invitee_id`)
VALUES
    (9),
    (4)
;

Query 1:

-- Your one by one query
SELECT i.`event_id`, COUNT(i.id) c
FROM invitees i
LEFT JOIN invitee_information ii
ON ii.invitee_id = i.id
WHERE ii.invitee_id IS NULL AND i.`event_id` = 18571 AND i.`invitation_sent` = 1

Results:

| event_id | c |
|----------|---|
|    18571 | 2 |

Query 2:

-- The IN only for count at least 1
SELECT i.`event_id`,  COUNT(i.id) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
WHERE
   ii.invitee_id IS NULL
   AND
  i.`event_id` IN(18569,18571,20000)
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |

Query 3:

-- The count for every event asked
SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
WHERE
  i.`event_id` IN(18569,18571,20000)
  AND i.`invitation_sent` = 1
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |

Query 4:

-- If you want the count for every event whatever invitation_sent
SELECT i.`event_id`,  SUM(IF(ii.invitee_id IS NULL, 1,0)) c
FROM invitees i
LEFT JOIN invitee_information ii
  ON i.id = ii.invitee_id
GROUP BY i.`event_id`

Results:

| event_id | c |
|----------|---|
|    10000 | 2 |
|    18569 | 3 |
|    18571 | 2 |
|    20000 | 0 |


How to format documentation in MS_Description extendedproperties? I'm using a 'MS_Description' extended property to document parameters in a stored procedure.  This parameter's documentation requires more structure than just plain text.  That is I would like to describe: "if value = x, then action A is taken; if value = y, then action B is taken, ...".

What is the best way to store this structure?

The following approaches occured to me:

Use html within the 'MS_Description', e.g.

EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'
        Selects the foobar.  Can have the following values:

x

does A

y

does B

'
    @level0type = N'SCHEMA',      @level0name = [dbo],
    @level1type = N'PROCEDURE',   @level1name = N'SE_DemoProc'
    @level2type = N'PARAMETER',   @level2name = N'DemoPar';

Would developpers DBAs automatically parse html in their heads?

Use visual studio xml document tags the extendedproperty, @value would become:

@value = N'
        Selects the foobar.  Can have the following values:
        <list type="bullet">
             <item><term>x</term><description>does A</description></item>
             <item><term>y</term><description>does B</description></item>
        </list>'

Does SSMS, visual studio, and/or other tools interpret Xml document tags in MS_Description extended properties?
Does extra semantics of '', '', and '' improve people's understanding more than the html?

Use a different extended property to document the values that a parameter excepts?

Which property is commonly used for this?
How to encode these multiple values?

As far as I know, an extended property can only occur once for a given (schema, procedure, parameter)-combination.

Using newlines, tabs, and '*' to format the plain text will not work since whitespace is collapsed from sql scripts.

does escaping newlines and tabs work?

Perhaps, I should avoid the mode parameter altogether and split this stored procedure into several distinct precedures.  (But that would be a different question and out of scope for this one.)

Why use extended properties at all here? They're second class citizens and not all that discoverable. The documentation you've described could just as easily be a block comment in the stored procedure body or, better yet, in your data dictionary and/or source control (since this stuff really should be documented in a safe location beyond the database anyway).

A block comment in a view, function, trigger or stored procedure is definitely stored in the database. Everything between the two GO batch separators is stored as the body (though I confess I don't know which visual editors might strip them, since I just use a standard query editor):

GO

/*
  this is a comment
*/

CREATE PROCEDURE dbo.whatever
  /* @A is for apple, @J is for jacks */
  @A int,
  @J int
AS
BEGIN
  SET NOCOUNT ON;
  -- do stuff;
END
GO

(I will also confess that comments stored before CREATE PROCEDURE or after the final END may be prone to inadvertent exclusion when someone modifies the procedure, so it may be safer to keep the comments inside the body.)

A little trickier to store documentation for tables and columns inside the database without using extended properties. But again, I'd argue these should be documented externally anyway. If you're going to bother writing documentation for these things, you may as well put it somewhere that will still exist if the database goes south.


Check Free Space in SQL Server (License Limit) I have a Microsoft SQL Server 2017 Express Edition those have a size limit of 10240MB (10Gigs) and I am not sure how I can check how much space I have left in this license. I have tried the following:
SELECT DB_NAME(database_id) AS database_name,
    type_desc,
    name AS FileName,
    size/128.0 AS CurrentSizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DemoDB' AND type IN (0,1);

This outputs the following:

database_name
type_desc
FileName
CurrentSizeMB

DemoDB
ROWS
DemoDB
10229.687500

DemoDB
LOG
DemoDB_log
8217.000000

Is it the upper value CurrentSizeMB 10229.687500 or do I need to use another query?

You can utilize the below to see how much free space is left in the data file(s) as well as how much you've already used. The limit for data files in Express edition is 10 GBs currently(https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15)
The code is based off of: https://am2.co/2016/04/shrink-database-4-easy-steps/
USE DemoDB
GO

SELECT
DB_NAME(),
dbf.name AS LogicalName,
dbf.type_desc AS FileType,
fg.name AS FileGroupName,
dbf.physical_name AS PhysicalFileLocation,
CAST(  (dbf.size / 128.0)  / 1024.0 AS DEC(10,2)) AS FileSizeGB,
CAST(  (FILEPROPERTY(dbf.name, 'SPACEUSED')  / 128.0)  /1024 AS DEC(10,2))  AS UsedSpaceGB,
CAST(   ((dbf.size / 128.0)  / 1024.0) - ((FILEPROPERTY(dbf.name, 'SPACEUSED')  / 128.0)  /1024) AS DEC(10,2)) AS FreeSpaceGB

FROM
sys.database_files dbf
LEFT JOIN sys.filegroups fg
ON dbf.data_space_id = fg.data_space_id

ORDER BY
FileType DESC,
FileGroupName,
LogicalName


Database Relational Design for Multiple Categories I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants within each study has a unique "study code" with different formats depending on the study is in. (i.e. Study A's code may be 10 characters with digits and alphabets, Study B's code may be only 6 characters digits only...etc)

I know that I need a many-to-many link table, and a look-up table for the studies, but other than that I am having a hard time figuring out where to incorporate the "study code" within the tables.

Here are the fields I have so far for my tables.

Participants: participantID (primary key), name, date-of-birth...etc (background demographics)
PatientInStudies: participantID, study, study_code
LookupStudies: study(listing all the studies A, B, C..etc.)

Basically, the PatientInStudies table will look something like:

1, A, A123456789

1, B, 123456

1, C, BB23-1-52

2, A, A295890100

2, B, 123908

3, C, KG19-7-23....etc

Will the PatientInStudies Table work? Is there a better way to organize the study code within the database?

Thanks!

I second @FrustratedWithFormsDesigner's suggestions for a unique constraint.  See http://office.microsoft.com/en-us/access-help/create-a-constraint-adp-HP003088257.aspx for how to do this in MS Access.

What a Unique Constraint does is state that no two rows can have duplicate data on some portion of the row.  So presumably you want each patient to be in a study only once, so you'd want a unique constraint on PatientsInStudy(participantID, study).

This way each individual can only be given one study id.  Similarly in that design your PatientsInStudy table would have a primary key spanning all three fields, and a second unique constraint on (StudyID, study_code).  This way even if two studies end up with similar semantics or overlapping spaces, the key cannot be reused in the same study.

Otherwise, good.


MySQL dump - how to order tables? It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running:

show tables;

I would like to reverse that order - is it possible to do without getting the list of all tables from database; reordering that list, and specifying all tables in cmdline to mysqldump?

What you are asking for can be done by getting the list of all tables from the database. However, you should use the INFORMATION_SCHEMA database. In your particular case, you can use the INFORMATION_SCHEMA.TABLES table.

TABLES IN DESCENDING TABLE_NAME ORDER (Database mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Choose Ordering
# Descending Order By Table Name
#
ORDER_BY="table_name DESC"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

TABLES IN DESCENDING SIZE ORDER (Database mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Choose Ordering
# Descending Order By Table Size
#
ORDER_BY="(data_length+index_length) DESC"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

GIVE IT A TRY !!!

I have suggested techniques like this before

Sep 09, 2011 : How can I optimize a mysqldump of a large database?
Dec 16, 2011 : How do you mysqldump specific table(s)?
Aug 23, 2012 : Creating dump file with use command

UPDATE 2014-12-20 21:09 EST

Your last comment

  It just seems inflexible to me that mysqldump doesn't offer any kind of table ordering. Reason is I have a table that I want it to be restored last, but in the dump order its among the first couple of tables. It's app dependence that this specific table is not present until the whole dump is fully restored.

TABLES IN ORDER BY tb1,tb2,tb3 BEING LAST (Database mydb)

Let's say you want tables tb1,tb2,tb3 to be last

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Order By List of Tables to Make Last
#
LAST_TABLES=tb1,tb2,tb3
ORDER_BY="IF(LOCATE(CONCAT(',',table_name,','),',${LAST_TABLES},')>0,1,0)"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

UPDATE 2014-12-21 23:02 EST

In your comment you said mysqldump doesn't offer any kind of ordering unless you specify table names as arguments, and there are not going to. Why ? Please keep in mind that the question MySQL dump - how to order tables? is very broad. There are many ways to ask for tables from a mysqldump.

In my old post How can I monitor the progress of an import of a large .sql file? (May 02, 2012) : When you execute a mysqldump of a single database, all tables are dumped in alphabetical order. Naturally, the reload of the mysqldump into a database would also be in alphabetical order.

The mysqldump client program sees a list of options and names names

  Syntax

  There are in general three ways to use mysqldump—in order to
  dump a set of one or more tables, a set of one or more complete
  databases, or an entire MySQL server—as shown here:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

Given the first example of syntax, MySQL (now Oracle) expect us developers and DBAs to give the database names and table names.

Since the mysqldump client program has the --databases (-B) option, we are given the opportunity to dump the databases in a specific order or with a specific list.

The point I am making is that it is left up to us to order the tables when running mysqldump. Since MySQL (now Oracle) uses storage engines that use plug-in frameworks, MySQL was not going to make mysqldump compliant to ordering anything for us. Imagine trying to use a storage engine whose physical files you cannot get access to. That leaves no choices but to use the INFORMATION_SCHEMA as I just demonstrated.

Unless someone downloads the source code for mysqldump and creates a version of mysqldump that can do custom ordering, this is all that mysqldump client program gives us to work with. We just have to do the ordering.


Large Fact table and partitioning key dilemma I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes.
The table has an identity column which is the primary key and has a clustered index on it. There are other non-clustered indexes on it but I won't go in the details much here. The column, I am trying to partition is not part of primary key but is not null and this is creating a slight dilemma for me. I have two options.

I add this column as part of primary key i.e. composite primary key. Since the first column is identity, the combination would always be unique which means I don't have to worry about the applications accessing the table. The clustered index will automatically be partition aligned and other indexes can also be partition aligned.
The seconds option is to remove the clustered index on the identity column and make it unique non clustered. This index cannot be partitioned aligned since partition key is not part of it and hence would have to sit on one drive. Then create a clustered index on the partition key column which can be partition aligned and so all the other non clustered indexes.

Our DBA is in favour of second option since he doesn't want to change primary key. I am worried about the performance hit in option 2 since the index is not partition aligned.

I would appreciate any feedback plus any other method you would have used in such situation.

I hate to state the obvious but I'd say test both scenarios and run your production queries against all 3 scenarios (scenario 1 being the current non partitioned one).  The reason I state this is because I don't know what your code is querying.  Do they actually have a benefit of being sorted in the base table by the identity column as opposed to the other column?  For example, are your queries actually looking for the row ID often?  If you're not sure, you might be surprised by getting some performance benefits.  The general idea of using ID as the clustered key was for range scans, but in our case, we scan by customerID and Date, so it worked out perfectly for us, and perhaps you.  Check out this article by Kim Tripp:
http://www.sqlskills.com/blogs/kimberly/post/The-Clustered-Index-Debate-Continues.aspx

"What is often cited as the “reason” for IDENTITY PRIMARY KEY clustered index definitions is its monotonic nature, thus minimizing page splits.  However, I argue that this is the only “reason” for defining the clustered index as such, and is the poorest reason in the list.  Page Splits are managed by proper FILLFACTOR not increasing INSERTS.  Range Scans are the most important “reason” when evaluating clustered index key definitions and IDENTITies do not solve this problem.Moreover, although clustering the IDENTITY surrogate key will minimize page splits and logical fragmentation due to its monotonic nature, it will not reduce EXTENT FRAGMENTATION, which can cause just as problematic query performance as page splitting.
In short, the argument runs shallow
"

Typically you want your clustered index to be as narrow as possible, unique, and non nullable as it get's carried into all other indexes.  I have a table with roughly 10 billion rows and we partition off the datetime column, which has worked out great.


Log error restoring old database to SQL Server 2017 I am trying to migrate a database from an old SQL Server 2008 R2 to SQL Server 2017. The database was originally 2000 and thus has a compatibility level of 80.  Initially I took a full database backup and tried to restore it to the new server, but got the following error:

  Msg 9014, Level 16, State 2, Line 32 An error occurred while
  processing the log for database 'nrf'. The log block version 2 is
  unsupported. This server supports log version 3 to 5.

I went back to the original server and restored the backup to a new name, nrf_temp, and then altered that database to compatibility level 100 (for 2008 R2). I took a new backup and again tried to restore to my new server, but got the same error again.  Then I went back to the original server, deleted my new name database, nrf_temp, and did a restore from that last backup that was taken after I put the compatibility level to 100 - thinking maybe a recreation of the physical files might help.  I took another backup and again tried the restore to my new server, but took the same error again.

Is there any way to get the restore to work on the 2017 server or will I have to define the database completely new and import data?  Note that I was able to successfully back up the other databases from that old server, that were already at compatibility 100, and restore those backups to my new 2017 build.

Make sure you've installed the minimum service pack level for 2008 on your source instance before making the backup. It appears to need to be on SP4 for it to be restorable to SQL Server 2017: Supported Version and Edition Upgrades for SQL Server 2017


Why does adding a simple CHECK constraint take so long on Postgres I'm using PostgreSQL 9.6.1 on Amazon RDS

I'm adding a CHECK constraint on an already indexed column named timestamp_ of a table called event_click that contains about 1 million rows.

ALTER TABLE event_click_2016_8
ADD CONSTRAINT click_2016_8_chk CHECK ( timestamp_ >= '2016-8-01' AND timestamp_ < '2016-9-01' );

The above takes 15 minutes to complete. Why is it so slow? What specifically is holding up this operation? There is nothing else running on the DB and no locks being held by other processes.

r3.large instance (2 vCPU, 15 GiB RAM)
maintenance_work_mem = 256147
work_mem = 1024000

when creating the constraint, check pg_locks for granted  is false or pg_stat_activity for wait_event_type and wait_event where pid = <pid that adds the check> (note that these two columns are new in 9.6 - in earlier versions you see only the fact that it is waiting).

Most probably your ALTER TABLE was waiting for other process to release a lock before it could acquire exclusive one.


How to get the SQL Server Activity Monitor's output using T-SQL? Is there any script to get the output just like Activity Monitor?

Or
Which DMV's can I use to get the output as Activity Monitor displays on the screen?

It should be able to display the stored procedure name as well as the statement from the stored procedure which is currently running and the bloking related info as well.

To get the exact output as Activity Monitor:

I have modified the given script as follows. You can play the activity monitor on one side and this script in another window and verify the output.

You can also view the currently running expensive queries by using this script and for that just need to do ORDER BY [Total CPU (ms)] desc.

This script will display the following things:

[Session ID]
[User Process]
[Login]
[Blocked By]
[Head Blocker]
[DatabaseName]
[Task State]
[Command]
[statement_text] --It will display the statement which is being executed presently and it can be from the SP or the normal T-sql .
[command_text] ----- It will display the Stored Procedure's Name.
[Total CPU (ms)]
'Elapsed Time (in Sec)'
[Wait Time (ms)]
[Wait Type]
[Wait Resource]
[Memory Use (KB)]
[Host Name]
[Net Address]
[Workload Group]
[Application]

You can also add or remove the columns whichever you need .

I have commented out some columns in the query, like: --[Open Transactions Count] = ISNULL(r.open_transaction_count,0), --[Login Time] = s.login_time, --[Last Request Start Time] = s.last_request_start_time, So if you want can also add or remove the columns as per your requirement and you can also filter out the data DatabaseName wise. I hope this script will help many of us.

    /* ACTIVITY MONITOR'S OUTPUT along with statement_text and command_text */ /* Processes */
SELECT [Session ID] = s.session_id,
       [User Process] = CONVERT(CHAR(1), s.is_user_process),
       [Login] = s.login_name,
       [Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
         [Head Blocker]  =
    CASE
        -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
        WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
        -- session is either not blocking someone, or is blocking someone but is blocked by another party
        ELSE ''
    END,
                        [DatabaseName] = ISNULL(db_name(r.database_id), N''),
                        [Task State] = ISNULL(t.task_state, N''),
                        [Command] = ISNULL(r.command, N''),
                        [statement_text] = Substring(st.TEXT, (r.statement_start_offset / 2) + 1,
                                            ( ( CASE r.statement_end_offset WHEN - 1 THEN Datalength(st.TEXT)
                                            ELSE r.statement_end_offset
                                            END - r.statement_start_offset ) / 2 ) + 1), ----It will display the statement which is being executed presently.

 [command_text] =Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), ''), -- It will display the Stored Procedure's Name.

 [Total CPU (ms)] = r.cpu_time,
 r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)',
                                 [Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
                                 [Wait Type] = ISNULL(w.wait_type, N''),
                                 [Wait Resource] = ISNULL(w.resource_description, N''),
                                 [Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
                                 [Memory Use (KB)] = s.memory_usage * 8192 / 1024,
 --[Open Transactions Count] = ISNULL(r.open_transaction_count,0),
 --[Login Time]    = s.login_time,
 --[Last Request Start Time] = s.last_request_start_time,

 [Host Name] = ISNULL(s.host_name, N''),
 [Net Address] = ISNULL(c.client_net_address, N''),

 -- [Execution Context ID] = ISNULL(t.exec_context_id, 0),
 -- [Request ID] = ISNULL(r.request_id, 0),
 [Workload Group] = N'',
                     [Application] = ISNULL(s.program_name, N'')
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id
                                      AND r.request_id = t.request_id)
LEFT OUTER JOIN
  ( -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
 -- waiting for several different threads.  This will cause that thread to show up in multiple rows
 -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,
 -- and use it as representative of the other wait relationships this thread is involved in.
 SELECT *,
        ROW_NUMBER() OVER (PARTITION BY waiting_task_address
                           ORDER BY wait_duration_ms DESC) AS row_num
   FROM sys.dm_os_waiting_tasks ) w ON (t.session_id = w.session_id)
AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id) OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE s.session_Id > 50 -- Ignore system spids.

ORDER BY s.session_id --,[Total CPU (ms)] desc ;


Which items are table entities and which are fields? I'm a mystery novelist with a series of three books that will end up at around 20 novels. I am having trouble getting the tables and fields sorted out.

I want to be able to:

create a list of all the people in the series
create a list of all the books in the series
search and find all the characters who have appeared in more than one book
which book a character first appeared
list a complete company or platoon by year
identify all characters with a serial number
Search for a  character in different companies, platoon, sections within the Battalion
Find the book where a character first appears

During the course of the series, people can be promoted or demoted (change their rank). They can be cross-posted within the Battalion from one Company to another (from a rifleman in Alpha Company to a Mortar man in Mortar platoon, Support Company). I have no idea how to represent this – or even if it can be done with one database.

Based on what I understand of normalization, I have reduced the data chunks into the following entities or fields:

Books by title
Years – 1964 -70
Months of the year (more than one book can appear in a year, no book appears in two years)
Battalion organization – the Companys
The platoons, sections
Character – last name, first name, nickname, where he came from in Canada, notes
Rank – (Which can be different depending on the year/book)

More information is available if required, please ask for any information regarding the Canadian Army of the time.

It sounds like the main issue here is that the attributes of certain entities (such as characters) can change over time, and you want to be able to track all the changes.

One way to do this might be to think of it as a data versioning problem.

One possible schema might look like this:

book
----
  ID
  name
  sequence_number (which book it is in the series)
  start_date (use this to track the dates that book covers)
  end_date (use this to track the dates that book covers)

character_base
--------------
  id (PK)
  first_book (FK to book.id)

character
--------
  id
  base_id (FK to character_base.id)
  version_number (in your case, this is probably optional)
  firstname
  lastname
  nickname
  hometown
  notes
  birthdate
  rank (fk to rank reference table: rank.ID)
  start_date (the begining of the time range for which this version is valid)
  end_date (the end of the time range for which this version is valid)

rank
----
  ID
  name

Now, whenever you create a new "version" of your character, you need to create a new record in character, but make sure you point the base_id to the same value as the previous value. Also remember to always populate start_date and end_date and ensure there are no gaps in the range.

To find out which version of a character is present in which books, you'd have to join character to book with a condition such that the character's version start and end date falls within a book's start and end date.

You can apply the versioning pattern for character to other entities that can be versioned, such as platoon or company if their attributes (such as name) need to be tracked over time.


SQL Server 2016 Job running forever I have created a SQL Server Job on SQL 2016. This is a job that runs a full backup from our backup utility DDBoost. When I execute the job, the backup completes in 5 mins for all databases. However, the job keeps running forever. Any ideas?

USE [master]
GO
DECLARE @returnCode int
EXEC   @returnCode = dbo.emc_run_backup
' -c <FullyQualifiedName> -k -S 4 -l full -y +90d
-a "NSR_DFA_SI=TRUE" -a "NSR_DFA_SI_USE_DD=TRUE"
-a "NSR_DFA_SI_DD_HOST=adc-dd-02" -a "NSR_DFA_SI_DD_USER=ddboost"
-a "NSR_DFA_SI_DEVICE_PATH=/sql-prod"
-a "NSR_DFA_SI_DD_LOCKBOX_PATH=D:\Program Files\EMC DD Boost Modules\DDBMA\config"
-a "NSR_SKIP_NON_BACKUPABLE_STATE_DB=TRUE"
-a "SKIP_BACKUP_PROMOTION=FALSE" "MSSQL$PRSQL01:"'

IF @returnCode <> 0
BEGIN
    RAISERROR ('Fail!', 16, 1)
END

Sounds like a bug. Try scripting from the GUI directly and see if your syntax is wrong somewhere.
Since the problem still occurs when running the script by itself, it doesn't have anything to do with the fact that it is running from a job. My guess is that the EMC application isn't properly reporting back to the dbo.emc_run_backup stored procedure that is has finished.
If that's the case, I'll bet scripting to CLI (ddbmsqlsv.exe) instead of T-SQL might behave differently. (You would need to change the job step to CmdExec instead of T-SQL.)
This may be a version problem, from what I can tell DDBoost didn't officially add support for SQL 2016 until version 3.5:

Changes in the Microsoft application agent 3.5
The following list provides the changes in the Microsoft application agent 3.5:

Added support for SQL Server 2016

Can't find a public link, but this is from the EMC® Data Domain® Boost for Enterprise Applications and EMC ProtectPoint™ Microsoft Application Agent Release Notes for Release 3.5.x.x (EMC document #81497).
Even if it worked before, a recent update of SQL 2016 may have broken it.
So my recommendation would be to upgrade to 3.5.0.1 or 4.0.
If you are unable to do that, all I can recommend is opening a support case with EMC.


Trigger needs dbms_alert declared A trigger has given me the following:

 ORA-04098: trigger 'adminuser.DB_ERROR_CHANGE_TRIG' is invalid and failed re-validation

and this is when working on altering schemas/triggers/etc from an import of a client database.

I ran

show errors trigger adminuser.db_error_change_trig

and the results were:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5      PL/SQL: Statement ignored
2/5      PLS-00201: identifier 'DBMS_ALERT' must be declared

Now, googling, I have found that I need to grant explicit execute privileges to the public user which I did with

grant execute on dms_public to public

However, this has not worked.
How can I determine what is going on, as well as how to fix this.  Thirdly, is dbms_alert schema specific? Meaning, my understanding is the the full name of the object is SYS.dbms_alert, is there another for another schema or is one global one?

I think you may be missing synonym for dbms_alert for adminuser schema. A object grant and synonym both are normally required (unless you own the object).

Try following as sys account, perhaps it will fix the problem.

create or replace synonym adminuser.dbms_alert for sys.dbms_alert;

If you don't want to create a synonym, you should use sys.dbms_alert, if your create a synonym as shown above, using just dbms_alert is sufficient in your code.


Get last X rows order by asc I want to select last X rows from a table, so what I did was:

SELECT * FROM table ORDER BY id DESC LIMIT 120

and it worked, but I want it to be ordered ascending, so I tried with:

SELECT * FROM table ORDER BY id DESC LIMIT 120 ORDER BY id ASC

expecting that it would reorder selected rows, but it didn't work.

So how can I achieve it?

You might be able to do something like this.

SELECT t.*
FROM (SELECT * FROM table ORDER BY id DESC LIMIT 120) AS t
ORDER BY t.id ASC ;

Another alternative would be to load the results into a temp table, then query that for the desired order.


How can FLOOR(3) equal 2? I'm trying to find a reliable, efficient expression to calculate how many decimal digits it takes to write a positive integer.

Mathematically, the number of decimal digits in an integer n is 1 + floor(log(n)), where log is the common logarithm (base 10).

There are several ways to construct an equivalent expression using built-in functions, but some of them give incorrect results. Can someone explain why?

Here is an example.

How to calculate the log?

The simplest way to calculate the common logarithm is to use the LOG10 function.

If you prefer one function for all logarithms you can use the LOG function and specify base 10 with the second parameter.

Prior to 2012, SQL Server's LOG function would calculate only the natural log (base e=2.71828...). You can calculate the log to an arbitrary base of a number by dividing the natural logarithm of the number by the natural logarithm of the base.

The following query calculates all three expressions for some example values:

SELECT
  Number,
  LOG(Number, 10) AS LogAB,
  LOG10(Number) AS LogTen,
  LOG(Number) / LOG(10) AS LogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Output:

Number      LogAB                  LogTen                 LogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2.99956548822598       2.99956548822598       2.99956548822598
1000        3                      3                      3
1001        3.00043407747932       3.00043407747932       3.00043407747932

I have chosen the values 999, 1000, and 1001 because 1000 is a point where the number of digits steps up. 999 has 3 digits, 1000 has 4.

The value of all three expressions is visibly the same, and looks correct.

Let's move on to the floor step.

How to calculate the floor?

You can take the floor of each log in the previous example using a query like this:

SELECT
  Number,
  FLOOR(LOG(Number, 10)) AS FloorLogAB,
  FLOOR(LOG10(Number)) AS FloorLogTen,
  FLOOR(LOG(Number) / LOG(10)) AS FloorLogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

Output:

Number      FloorLogAB             FloorLogTen            FloorLogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2                      2                      2
1000        2                      3                      2
1001        3                      3                      3

The values of each expression for 999 and 1001 are equal and correct. If we added 1 to each value, we would have a count of 3 digits in 999 and a count of 4 digits in 1001.

The values for 1000 are not the same! If we added 1 to each value, we would have a count of 4 digits in 1000 if we used the LOG10 function, and a count of 3 digits if we used the LOG function in either form.

There is an inconsistency here!

How can FLOOR(3) equal 2?

The implication is clear: using the LOG function would give me an incorrect count for some values, so I should use the LOG10 function.

But the value of each log expression itself is identical and correct. Why does the floor function produce different values from their input?

SELECT
  Number,
  CAST(LOG(Number, 10) AS VARBINARY) AS LogAB,
  CAST(LOG10(Number) AS VARBINARY) AS LogTen,
  CAST(LOG(Number) / LOG(10) AS VARBINARY) AS LogOverLog
FROM (
  VALUES (1000)
) AS Tally (Number);

Returns

Number      LogAB                   LogTen                  LogOverLog
----------- ----------------------- ----------------------- ----------------------
1000        0x4007FFFFFFFFFFFF      0x4008000000000000      0x4007FFFFFFFFFFFF

0x4008000000000000 is exactly 3.

0x4007FFFFFFFFFFFF is 2.99999999999999955591079014994.

If you are looking for an  efficient expression maybe a CASE expression with the 10 different cases would actually work out less CPU intensive than calculating logarithms (or possibly you could have nested case expressions to do a trinary search)


Restore RMAN Level 0 without any archivelogs? I have a level 0 backup, and control files, but I don't have the archivelogs. And I'm not using rman catalog.

I found ways to restore a L0 backup, if you have at least the first archivelog after the L0, or until the first missing archivelog, but yet I don't find how to restore the database as it was when the L0 was created.

Is it possible to restore just the L0?

Of course. A consistent full backup created in MOUNT mode does not require any archivelogs to be successfully restored.

An online backup is inconsistent and requires archivelogs for restoring a consistent state.


How can I find duplicate key violations in a different collation? I have a MySQL table that has the collation for one of its varchar columns in the primary key set to utf8_general_ci. I want to change the collation to utf8_unicode_ci so it stops treating "gmail.com" and "gmàil.com" like they're the same item. I tried running this:

alter table my_table change character set utf8 collate utf8_unicode_ci;

The alter fails with a duplicate key violation on one of the items.

  Error 1062 (23000): Duplicate entry 2013-07-15-119-comcast.net for key PRIMARY

I tried selecting the items out of the table that have date equal to 2013-07-15 and source_id equal to 119, but eyeballing the results I don't see two entries for "comcast.net" that might be causing the problem.

Can I construct a query into this table to determine which two rows are different under utf8_general_ci but the same under utf8_unicode_ci?

A query like this did the trick:

select date, thing_id, email_domain collate utf8_unicode_ci
from my_table
where date = '2013-07-15'
and thing_id = 119
and email_domain collate utf8_unicode_ci = 'comcast.net';


why from_utf8 does not exist in postgresql? I have the following sql code that uses from_utf8 function:
SELECT FROM_UTF8(name, ’?’) AS name, COUNT(DISTINCT target) AS cnt
FROM directory_entry_file GROUP BY name
ORDER BY cnt DESC
LIMIT 1;

When I execute this query in the postgresql, I get the following error:
ERROR:  function from_utf8(unix_path, unknown) does not exist
LINE 1: SELECT FROM_UTF8(name, '?') AS name, COUNT(DISTINCT target) ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How can I fix this error?

As documented in the the manual the function you are looking for is convert_from()

Converts a binary string representing text in encoding src_encoding to text in the database encoding

So the query should probably be:
SELECT convert_from(name, 'UTF8') AS name,
       COUNT(DISTINCT target) AS cnt
FROM directory_entry_file
GROUP BY convert_from(name, 'UTF-8')
ORDER BY cnt DESC
LIMIT 1;


stored proc to handle XML as input param for multiple record inserts i have updated my proc from taking typical data types to take a single XML param so that i can call it once instead of multiple times for inserting multiple records.

here is a sample of my proc that works well...

ALTER PROCEDURE [dbo].[ins_Attachment_xml]
    @XML XML
AS
BEGIN

        DECLARE @messageId INT
        SET @messageId = (SELECT n.c.value('@MessageId','int') FROM @XML.nodes('/Attachment') n(c))

        SELECT
            rr.item.value('@FileboxId','varchar(MAX)') AS FileboxId,
            rr.item.value('@FileName','varchar(MAX)') AS FileName
        INTO #tmp
        FROM @XML.nodes('/Attachment/item') AS rr(item)

        INSERT INTO Attachment (MessageId,FileboxId,FileName)
        SELECT @messageId, T.FileboxId, T.FileName
        FROM #tmp T
END

exec ins_Attachment_xml
@XML =
'<Attachment MessageId="2">
  <item FileboxId="FB1"  FileName="notes.doc" />
  <item FileboxId="FB2"  FileName="W2.jpeg" />
  <item FileboxId="FB3"  FileName="2011 Taxes.pdf" />
</Attachment>'

now i need to change the XML param being passed in to this but dont know how to update the proc to make it work with the new XML structure. this is the new xml:

<MessageRecipient>
<MessageId>52</MessageId>

<Recipient>
        <RecipientTypeId>2</RecipientTypeId>
        <EmailAddress>dig.s@mail.com</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>ash.k@mail.com</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>dee.v@mail.com</EmailAddress>
    <FriendlyName>dee v</FriendlyName>
    <UFID>dee123</UFID>
</Recipient>
</MessageRecipient>

I c an't speak for performance but this is one way of doing things.

declare @x xml

select @x = '<MessageRecipient>
<MessageId>52</MessageId>

<Recipient>
        <RecipientTypeId>2</RecipientTypeId>
        <EmailAddress>dig.s@mail.com</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>ash.k@mail.com</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>dee.v@mail.com</EmailAddress>
    <FriendlyName>dee v</FriendlyName>
    <UFID>dee123</UFID>
</Recipient>
</MessageRecipient>'

select
    Finaldata.R.value ('../MessageId[1]', 'NVARCHAR(10)') AS MessageID
    , Finaldata.R.value ('RecipientTypeId[1]', 'INT') AS RecipientTypeId
    , Finaldata.R.value ('EmailAddress[1]', 'NVARCHAR(200)') AS EmailAddress
    , Finaldata.R.value ('FriendlyName[1]', 'NVARCHAR(100)') AS FriendlyName
    , Finaldata.R.value ('UFID[1]', 'NVARCHAR(10)') AS UFID
from @x.nodes ('//MessageRecipient/Recipient') as Finaldata (R)


Does restoring a full backup bring it to the backup START time or the backup FINISH time? Backstory: I'm trying to restore copies of several SQL 2012 databases to a different environment, but the application needs them to be (roughly) "synchronized" in time.

(Of course the ideal way to do this is by using FULL recovery mode using full and log backups, and then do point-in time restores using the same timestamp. But in my case, I don't need them perfectly synchronized, just within ~10 minutes or so, and I'd rather deal with only full backups and restores. I do have control over when the full backups run, but the problem is that the databases are of very different sizes, so the full backups run for very different lengths of time.)

So my question: should I synchronize the start time of the full backups? Or the finish time?

In other words, when I restore a full backup, will I end up with how it looked when the backup started, or when it finished?

Check out this link

  A full database backup provides a complete copy of the database and
  provides a single point-in-time to which the database can be restored.
  Even though it may take many hours for the backup process to run, you
  can still only restore the backup to a single point (effectively at
  the end of the backup, but I'll discuss exactly what that point is
  later in this article). A full backup does not allow recovery to any
  point in time while the backup was running.


How can I insert procedure results from server 1 into table in server 2? The goal is to link our website to the database.  The database the website will connect to will be different then our production db.  In fact, a separate instance of SQL.  How can this be structure to execute the procedure in sql1 to populate table A in sql2 so the web query can pull from sql2 table A?

One option is to create a "linked server". This allows you to access another server tables. To do this you use the server name as a prefix.
Before addressing this option verifies with the DBAif  is an option allowed in the organization. Also check permissions issues.

This link from Microsoft has more information. The following example is taken from the same link:

How to create the link?

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'SRVR002\ACCTG',
    @srvproduct=N'SQL Server' ;
GO

How can reference a table from another server?

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins
FROM master.sys.server_principals AS local
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked
    ON local.name = linked.name ;
GO


Dropping a table with powershell Trying to delete an existing table using powershell

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server(".\CEF_2014_1")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("tempdb")

#drop the Table
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "listeningport")
$tb.Drop()

but getting an error

PS C:\Users\craig.efrein\Dropbox\Scripts\Powershell> .\drop_table.ps1
Exception calling "Drop" with "0" argument(s):
"Drop failed for Table 'dbo.listeningport'. "
At C:\Users\craig.efrein\Dropbox\Scripts\Powershell\create_table.ps1:11 char:11
+         $tb.Drop <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

If I create the table first, and then drop it, that works.

Am I filling the $tb object correctly?

$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "listeningport")

As a second question, how do I check for the existence of the table before deleting it in powershell?

A cleaner way to do this would be to get the table object from the database object directly, and then drop it if it returns non-null. This will ONLY run if the table exists.

#drop the Table
$tb = $db.Tables['listeningport']
IF ($tb)
    {$tb.Drop()}

You can use $tb.Tables['tablename', 'schemaname'] to use a non-dbo schema.

When I deal with things like this in SMO my preference is ALWAYS to get the child objects from the parent object directly. It's cleaner, it's more obvious to the next person reading the code what you're doing (you're getting the table directly from that table collection in the db object), and it's very easy to check for existence in a single step.

For your specific issue, it's possible that the table is not created with that name since you're looking at tempdb. Are you sure it's there with that specific name?


Creating SQL Server triggers to copy a record from one table and populate another I want to create a trigger that will copy a record from one table and populate another after an insert or update. Users will enter a date in an inspection table and I want that date inserted or updated into an asset table date column. I am a newb to SQL and T-SQL and have not created triggers before. Guidance on how to get this setup would be appreciated.

Here's a better explanation:

The table I want to create the trigger on is called [Inspections]. Users will add new records to this table and included a DateInspected value in that row. I want the trigger to take that new DateInspected value from [Inspections] and update an existing value in a table called [Hydrants].

The [Inspections] table and the [Hydrants] table have a primary key called [Id]. How do I ensure the trigger is updating the correct existing record in the [Hydrants] table?

OK, Here's my update based on your newly added info. The merge statement in the trigger will insure that newly added records will be inserted into hydrants. Otherwise, records that exist between inspections and hydrants will be updated in hydrants--when inspections is updated.

You can see when you run the insert and update (after the table and trigger creation below) that the row insert in the inspections table is inserted into hydrants whereas the row update to inspections is also updated in hydrants.

create table Inspections
(
    PK int identity(1,1),
    MyDate date
)
go

create table Hydrants
(
    PK int,
    MyDate date
)
Go
CREATE TRIGGER MyTrigger
   ON  Inspections
   AFTER  INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    MERGE INTO Hydrants as Target
    USING inserted as Source
    ON (TARGET.PK = SOURCE.PK)
    WHEN MATCHED  THEN
    UPDATE SET TARGET.MyDate = SOURCE.MyDate
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (PK, MyDate)
    VALUES (SOURCE.PK,SOURCE.MyDate);

END
GO
insert into Inspections(Mydate) values('01/10/2016')
select * from Hydrants
update Inspections set Mydate = '02/10/2016' where PK =1
select * from Hydrants


Three update query vs single update query performance Am trying to optimize a procedure. There are 3 different update queries present in the procedure.

update #ResultSet
set MajorSector = case
        when charindex('  ', Sector) > 2 then rtrim(ltrim(substring(Sector, 0, charindex('  ', Sector))))
            else ltrim(rtrim(sector))
        end

update #ResultSet
set MajorSector = substring(MajorSector, 5, len(MajorSector)-4)
where left(MajorSector,4) in ('(00)','(01)','(02)','(03)','(04)','(05)','(06)','(07)','(08)','(09)')

update #ResultSet
set MajorSector = substring(MajorSector, 4, len(MajorSector)-3)
where left(MajorSector,3) in ('(A)','(B)','(C)','(D)','(E)','(F)','(G)','(H)','(I)','(J)','(K)','(L)','(M)','(N)','(O)','(P)','(Q)','(R)','(S)','(T)','(U)','(V)','(W)','(X)','(Y)','(Z)')

To complete all three update queries it takes less than 10 seconds.

Execution plan for all three update queries.

https://www.brentozar.com/pastetheplan/?id=r11BLfq7b

What I planned is to change the three different update queries into one single update query, so that the I/O can be reduced.

;WITH ResultSet
     AS (SELECT CASE
                  WHEN LEFT(temp_MajorSector, 4) IN ( '(00)', '(01)', '(02)', '(03)', '(04)', '(05)', '(06)', '(07)', '(08)', '(09)' )
                      THEN Substring(temp_MajorSector, 5, Len(temp_MajorSector) - 4)
                  WHEN LEFT(temp_MajorSector, 3) IN ( '(A)', '(B)', '(C)', '(D)','(E)', '(F)', '(G)', '(H)','(I)', '(J)', '(K)', '(L)','(M)', '(N)', '(O)', '(P)','(Q)', '(R)', '(S)', '(T)','(U)', '(V)', '(W)', '(X)','(Y)', '(Z)' )
                      THEN Substring(temp_MajorSector, 4, Len(temp_MajorSector) - 3)
                  ELSE temp_MajorSector
                END AS temp_MajorSector,
                MajorSector
         FROM   (SELECT temp_MajorSector = CASE
                                             WHEN Charindex('  ', Sector) > 2 THEN Rtrim(Ltrim(Substring(Sector, 0, Charindex('  ', Sector))))
                                             ELSE Ltrim(Rtrim(sector))
                                           END,
                        MajorSector
                 FROM   #ResultSet)a)
UPDATE ResultSet
SET    MajorSector = temp_MajorSector

But this takes around 1 minute to complete. I checked the execution plan, it is identical as first update query.

Execution plan for above query:

https://www.brentozar.com/pastetheplan/?id=SJvttz9QW

Can somebody explain why it is slow?

Dummy data for testing:

If object_id('tempdb..#ResultSet') is not null
drop table #ResultSet

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT CONVERT(varchar(255), NEWID()) as Sector,cast('' as varchar(1000)) as MajorSector
into #ResultSet
FROM Tally
where  n <= 242906 -- my original table record count
ORDER BY n;

Note : Since this is not my original data the timings I have mentioned above could be little different. Still the single update query is much slower than the first three.

I tried executing the queries more than 10 times to make sure external factors should not affect the performance. All 10 times first three updates ran much faster than the last single update.

The first single update reads and writes every row from the table. The second and third then re-read and re-write a sub-set of those rows. Look at the Actual Number of Rows. When the three statements are combined into one, the optimizer figures that if it has to read everything to satisfy the first change then it can piggy-back off that for the second and third change.

Have a look at the XML version of the query plans, specifically the <ComputeScalar> operators and <ScalarOperator ScalarString=""> parts. In the original plan you'll see each is relatively simple and maps very closely to the SQL. For the all-in-one plan it's a monster. This is the optimizer re-writing the SQL into a logically equivalent form. A plan works1 by passing each row through the operators once. The optimizer's doing all the work it has to do to satisfy all three changes as that row passes through one time.

I'd expect the second query to be faster because the data is only read and written once whereas it is touched three times in the first.

As the second query has no predicates (no WHERE clause) the optimizer has no choice but read every single row and process it. I'm surprised the second form takes longer than the first. Are both starting from clean buffers? Is there other work happening on the system? Since it's reading and writing to a temp table the IO is happening in tempdb. Is there file growth or somesuch happening?

By one measure you have achieved your desired outcome. You say you want to make changes "so that the IO can be reduced." The all-in-one does less IO than the three separate statements do in total. I suspect what you really want, however, is reduced elapsed time, and this is obviously not happening.

1 more or less, lots of details omitted.

I ran your routine to generate test data then ran the three single-update statements and the all-in-one statement. Although there are some differences (no clustered index, no parallelism) I get more-or-less the same results. Specifically, the plans are about the same shape and the three individual queries complete in about two seconds and the one big query in about thirty to thirty five seconds.

I set

set nocount off;
set statistics io on;
set statistics time on;

With the plan in cache and the data in memory I get:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 1422 ms,  elapsed time = 1417 ms.

(242906 row(s) affected)

Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 344 ms,  elapsed time = 337 ms.

(0 row(s) affected)

Table '#ResultSet...'. Scan count 1, logical reads 125223, physical reads 0

 SQL Server Execution Times:
   CPU time = 734 ms,  elapsed time = 747 ms.

(0 row(s) affected)

I've removed some bits that aren't relevant. Since physical reads is zero for all three the table fits in memory. logical reads is the same for all three which makes sense. As there are no indexes the only approach is to scan every row of the table. The second and third query affect zero rows because I'd run them a few times already. CPU time and elapsed work out as 2500ms.

For the bigger query it is

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table '#ResultSet...'. Scan count 1, logical reads 125223

 SQL Server Execution Times:
   CPU time = 33093 ms,  elapsed time = 33137 ms.

(242906 row(s) affected)

The same number of pages are read, the same number of rows are updated. The huge differences is the CPU time. This is reflected in casual observation of Task Manager which shows 30% utilisation for the duration of query execution. The question is, why does it take so much?

The individual queries separately have simple calculations and two of the statements have predicates that greatly reduce the number of rows touched. The optimizer has good heuristics for processing these and finds a quick plan. The all-in-one query applies the monster Compute Scalar against every single row. My suggestion is that, for whatever reason, the optimizer cannot unravel the logic into a plan that's quick to run and ends up using a lot of CPU. The optimizer has to work with what its given, which in the second case is complex, nested SQL. Perhaps by refactoring the SQL the optimizer will follow different heuristics and achieve a better outcome? Perhaps some (filtered) indexes or (filtered) statistics will convince it to write a different plan. Maybe persisted computed columns would help it along. Perhaps you just need to give the optimizer what it needs and your first attempt really is the best that can be achieved and you need to find a way to run those three in parallel. Sorry I can't be more scientific.


IO Statistics for Index Seek Does Statistics IO (Logical/Physical Reads) include index pages that are not leaf-level?

The following example is lifted directly from this old post from Gail Shaw:

https://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/

--Setup
CREATE TABLE TestingSeeks (
    id int identity (1,1) not null,
    SomeStr char(6) default '' -- a filler
)

insert into TestingSeeks (SomeStr)
select top (500000) ''
from sys.columns c1 cross join sys.columns c2

CREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id)

--Example queries
--Query 1
select  id
from    TestingSeeks

--Query 2
select  id
from    TestingSeeks
where   id > 0

--Statistics IO output for Query 1

  (500000 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical
  reads 1120, --All other columns are zero

--Statistics IO output for Query 2

  (500000 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical
  reads 1120, --All other columns are zero

As you can see, the Logical Reads are the same for both queries (the Seek and the Scan) but I thought that the Seek would have more pages to read as it would read the Root page and pages from the intermediate level(s) of the index.

If I run the following query:

--Seek for one row
--Query 3
select  id
from    TestingSeeks
where   id = 400

--Statistics IO output for Query 3

  (1 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical reads
  3, --All other columns are zero

You can see that there are three reads (1. Root page, 2. Intermediate page, 3. Leaf level page), so why did the statistics for [Query 2] above not include two more rows than the Scan (Query 1)?

  SQL Server @@Version:  Microsoft SQL Server 2014 (SP2-GDR) (KB4057120)
  - 12.0.5214.6 (X64)   Jan  9 2018 15:03:12    Copyright (c) Microsoft Corporation     Developer Edition (64-bit) on Windows NT 6.1  (Build
  7601: Service Pack 1)

Yes, the logical reads include non-leaf level index pages.

For the index scan in the first query, the first logical page is located by traversing the b-tree to find the first index leaf page, followed by a scan of the entire index in logical order. These stats include the 2 non-leaf pages and all index leaf pages.

For the index seek in the second query, the first logical page satisfying the criteria is located by traversing the b-tree followed by a scan of the leaf pages. These stats include the same 2 non-leaf pages the full scan needed to locate the first page (assuming no rows exist before the key value zero or those are in the first page). The same number of leaf pages are touched because all pages qualify for the seek predicate.

The equality predicate in the third query touches the same number of non-leaf pages of the b-tree hierarchy as the first 2 queries, although maybe different non-root pages, Only a single leaf page is needed to satisfy the predicate for a total of 3 logical IOs.


Postgres \copy command into single column without delimiter I am trying to import big fixed length files into Postgres and I am using the copy command and a staging table following this guide.

The problem is that this files has a lot of different characters and i don't know which to use as delimiter. In fact I don't need a delimiter at all as I am importing files in a single column table.

You cannot use COPY or \copy if there is no character which is certain not to appear in the data. You will either need to pick a character to act as the delimiter, then escape or quote it and the escape/quote character in the input file (perhaps using PROGRAM).  Or, not use COPY or \copy, but rather use INSERT instead.

It isn't clear from your question if your files have newlines as record separators or if every file is one record and internal newlines are literal.  If the latter you do use COPY or \copy, you will have to escape/quote that as well.


Foreign key on split unique columns I have 3 tables
Vendors
Trucks
Fruits

Truck table has truck # and is foreign Keyed to Vendors with a split uniqueness constraint on both truck and Vendor (different vendors can have the same truck number but no vendor can have 2 trucks the same number)

Fruit tracks shipping history and vendor (has FK to Vendor as well)
I want to add a foreign Key on Fruit to Truck # but I am not sure how to do this

Found the answer.
I was Trying:

USE [FruitHistory]
GO

ALTER TABLE [dbo].[Fruit]  WITH CHECK ADD  CONSTRAINT [FK_Fruit_OnTruck] FOREIGN KEY([TruckNumber],[Vendor])
REFERENCES [dbo].[Trucks] ([TruckNumber],[Vendor])
GO

ALTER TABLE [dbo].[Fruit] CHECK CONSTRAINT [FK_Fruit_OnTruck]
GO
GO

But what actually worked was

USE [FruitHistory]
GO

ALTER TABLE [dbo].[Fruit]  WITH CHECK ADD  CONSTRAINT [FK_Fruit_OnTruck] FOREIGN KEY([Vendor],[TruckNumber])
REFERENCES [dbo].[Trucks] ([Vendor],[TruckNumber])
GO

ALTER TABLE [dbo].[Fruit] CHECK CONSTRAINT [FK_Fruit_OnTruck]
GO
GO

Not Completely sure why but reversing the columns allowed it to be set


alter database backup controlfile vs cp Linux file We have the following explanation in the documentation:

  Back up the control file to a binary file (duplicate of existing
  control file) using the following statement:

ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

My question is: why do I when modify control_files parameter to use the controlfile backed up with the alter database statement I need to do a recover the database and with cp command not? what is the difference between cp and the backup command?

If you just move/rename control files (cp, mv ...) and modify accordingly control_files parameter - no need to do any recovery.

You may use cp only when instance is down or in nomount state.
Otherwise, you can get inconsistent control file.
If we do:

  ALTER DATABASE BACKUP CONTROLFILE...

then we make "old", "not current" controlfile - backup controlfile.
With cp we make "current" copy of controlfile.

If we have current controlfile - no recovery is needed.
When controlfile is a backup controlfile then it requires recover and open resetlogs.

Here's a link to documentation -- old, but explains the concepts well.


How to model state change timeline I've got an app where users can place orders for recurring delivery of a particular service.

The orders table has columns service_id and state, where service_id links to the service the user has ordered and state can be active or inactive, reflecting whether the user currently wants to receive that service or not. Users can toggle orders on and off as they please.

When a user turns an order on or off (active/inactive) I need to track this, and I need to be able to query for whether an order was active or inactive on a given date.

My question is: What's the best way to to model something like this, specifically so that I can easily write a query like "all orders that were active on (date)"?

I've thought about making a orders_states table with order_id, state, and date, where each change create a row with the state that was set and the date it was set, but I'm not 100% sure about how I'd query it to get the past state on an arbitrary date, or if there's a better way to model this.

I'm testing / prototyping on this now, but I'm obviously not a SQL expert and would love some suggestions from the pros here. Thanks!

(I'm using Postgres 9.4.1.2 in case that matters, but I figure this is more of a generic SQL modeling question)

I guess you'll need an order state table. You can create a new row every time the state changes for an order (how you do this is up to you: triggers, SP or on the application layer). That table must have a non-null timestamp column to get the time the row was created.


Source of error on psql data load Upon importing a file derived from rails establishment, psql is hitting the error of foreign key constraints:

ERROR:  insert or update on table "documents" violates foreign key constraint "fk_rails_d4abdc7f58"
Key (typ_document_id)=(7) is not present in table "typ_documents".

Yet, querying afterwards

SELECT * from typ_documents;

getting a result

  7 | Test Internal | 2013-07-04 08:36:16.026295 | 2013-07-04 08:36:16.026295

The only assumption that I can follow is that, when the table documents is being loaded, the order of loading is such that typ_documents is happening after and the error is arising.  Yet, this error is not occurring consistently and the data dump is in alphabetical order of table names.  Thus, this is a weak assumption.

Removing the foreign key constraint from rails would overcome the problem, but that is a weak reaction.

Update
Data from the pg_dump file  (redacted to omit the long full text)

COPY documents (id, titolo, abstract, full_text, typ_document_id, idioma_id, competitor_id, created_at, updated_at) FROM stdin;
[...]
5   Conservazione Finocchio con lavaggio    La prova è stata [...redacted...] 3 ppm\r\n15\t15\r\n0,75\r\n30 sec\t60 sec\t90 sec 7   1   \N  2013-07-08 10:49:53.393598  2013-07-11 16:07:31.540986

COPY typ_documents (id, nome, created_at, updated_at) FROM stdin;
[...]
7   Test Internal   2013-07-04 08:36:16.026295  2013-07-04 08:36:16.026295

How can this issue be debugged/overcome?

I think your problem is (as a_horse.. says) that your foreign key is not deferrable. Maybe creating your foreign key according the to code below solves your issue.

    ALTER TABLE documents
      ADD FOREIGN KEY (typ_document_id)
      REFERENCES typ_documents(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      DEFERRABLE
      INITIALLY DEFERRED;


How to get capacity specifications for a particular Oracle release? How to get capacity specifications for new Oracle release(Oracle 11.2.0.4)?
ie, information like

How much schema we can create?,

How much tables a schema can accommodate?

How much rows total instance can have?

How can I get these information? Or from where I can get these information?

Thanks in advance!!

Physical Database Limits

Logical Database Limits

The maximum number of users (schemas): 2^31 = 2147483648

The official answer to your other questions: unlimited.

The practical answer is: as many as your hardware/operating system/design can handle.


Extract SYNONYM and SEQUENCE from Informix metadata We are migrating Informix database to oracle. Please help us to extract SYNONYM and SEQUENCE from Informix metadata using query.

Although you don't mention the version of the informix, try the ones above.

I believe that since V9.4 there is a system catalog table named SYSSEQUENCES where you can find the information about sequences on each database.

For the synonyms the one to be used is SYSSYNTABLE, if you have an older version try the SYSSYNONYMS.


SQL Server Pagination using CTE I have to apply paging on query as per requirement i need two extra columns other than query result
1. Total Records
2. Total Pages

but i am trying to confirm that this the right way to retrieve total number of pages

CASE WHEN TotalRec%@PageSize > 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage

WITH CTE
AS
(
        SELECT COUNT(*) OVER() TotalRec, ROW_Number() over(ORDER BY id) RowNo, ColumnOne, ColumnTwo
        FROM
        (
        SELECT ColumnOne, ColumnTwo
        FROM table1 ct LEFT OUTER JOIN table2 IR ON ct.code=IR.code
        WHERE table1.code=3
        Group By  ColumnOne, ColumnTwo

        )ImmCat
)

    SELECT TotalRec TotalRec,
    CASE WHEN TotalRec%@PageSize > 0 THEN TotalRec/@PageSize) +1 ELSE TotalRec/@PageSize) END  TotalPage
    ,ColumnOne, ColumnTwo
    FROM CTE
    WHERE RowNo BETWEEN (((@PageNo - 1)*@PageSize) + 1)) AND (@PageNo * @PageSize))

You can certainly avoid a conditional here, but you also do not need either CEILING or conversion to numeric to get the total number of pages if both @totalrec and @pagesize are integers (and it seems safe to assume that they are). The following formula will give you the number of pages to accommodate @totalrec rows given the page size of @pagesize:

(@totalrec - 1) / @pagesize + 1 AS TotalPages


Implementing "ANY" aggregate operator in T-SQL I have a database that contains department and employee tables, and I need to return the number of employees for each department. I write the following query:

SELECT d.ID, d.Name, COUNT(*) EmployeeCount
  FROM dbo.Departments d
       INNER JOIN dbo.Personnel p
       ON p.DepartmentID = d.ID
GROUP BY d.ID, d.Name;

The problem is, I'm worried that the GROUP BY operator is going to be slow when working on a VARCHAR column. I'd like to be able to write

SELECT d.ID, ANY(d.Name), COUNT(*)  EmployeeCount
  FROM dbo.Departments d
       INNER JOIN dbo.Personnel p
       ON p.DepartmentID = d.ID
GROUP BY d.ID;

I could probably use the MIN/MAX aggregate functions, but they would likely make the query even slower. I know that, in MySQL, you can select a column that doesn't appear in the GROUP BY clause, and the DB engine will return any row value at random for that column. Is something like that doable in T-SQL?

I think you are trying to optimize a performance problem that doesn't exist. I don't know why you think a GROUP BY is going to be problematic here. Conceptually there isn't really much difference between what you wrote and the following:

SELECT d.Id, d.Name, s.c
  FROM dbo.Departments AS d
  INNER JOIN
  (
    SELECT ID = DepartmentID, c = COUNT(*)
    FROM dbo.Personnel
    GROUP BY DepartmentID
  ) AS s
  ON d.ID = s.ID;

So this query doesn't do the GROUP BY on the Name column, but in my testing this is actually less efficient than your version unless you create a non-clustered index on Personnel.DepartmentID (in which case the plans are exactly the same). I suggest optimizing performance when you actually observe a performance problem, not based on hunches. In your case the grouping essentially comes along for free because it's part of the same clustered index scan that has to read all the data anyway. I'm not sure how forcing SQL Server to pick a random name (which is going to be in the same row as the ID you already read anyway) can possibly make anything faster...


Unable to delete secondary data file I wanted to create a second log file, but by mistake I created a data file.

Now I'm trying to remove it, but I get the error message "the file cannot be removed because it is not empty". The following query lists a lot of tables:

SELECT o.name AS TableOrIndex
FROM sysfiles f
JOIN dbo.sysfilegroups s
ON f.groupid = s.groupid
JOIN dbo.sysindexes i
ON i.groupid = s.groupid
JOIN dbo.sysobjects o
ON i.id = object_id(o.name)
AND i.indid in (0, 1)
WHERE f.name = 'DB_NDF_FILE'

UNION

SELECT i.name
FROM sysindexes i join sysfilegroups f ON i.groupid = f.groupid
JOIN sysfiles l ON f.groupid = l.groupid
WHERE l.name = 'DB_NDF_FILE'

What's the correct procedure to move these data to other data files so I can safely delete this newly created file?

Use:

DBCC SHRINKFILE('logicalname', EMPTYFILE);
ALTER DATABASE database_name REMOVE FILE logicalname;


store a file into table from TSQL Possible Duplicate:
  Files - in the database or not?

I Want to store a file into sql server 2008 from TSQL (Management Studio).
I Know the file saving into varbinary column can be done by using the .NET

But I want to do this from Management Studio.

In C# and VB.NET we saving file into varbinary column by using ReadAllBytes()

How I can achieve this in Management Studio

Something like this?
https://stackoverflow.com/questions/9291255/insert-image-into-database

Please read about File Stream too.


Full backup vs Simple TempDB usage What is the difference in terms of I/O disk usage between Simple Backup and Full Backup?

Does full backup incur more reads and writes?

How does full backup mode impact reads and writes to TempDB's transaction log?

Microsoft recommends setting it to SIMPLE to limit space utilisation. See here. From an IO perspective, SIMPLE mode will generally require the same IO because it has to retain enough transaction data to facilitate rollback of an active transaction. There is some good information on TLOG utilisation here.


How to merge two large mysql tables into one with similar data keeping the latest record I have two similar large tables with below table structure

CREATE TABLE tableA (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE tableB (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to create one large table merging above two tables with the latest record

CREATE TABLE combine_table (
  col1 varchar(20) NOT NULL,
  col2 varchar(40) NOT NULL,
  date date NOT NULL,
  time time NOT NULL,
  PRIMARY KEY (col1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Some data may duplicated in table A and B.
How can I achieve this.
Note that these tables are very large

Since you have very much data at hand, I suggest you merge your date and time columns first. Then you can use an index efficiently. If you don't, you will have to do something like

...WHERE CONCAT(date, ' ', time) = SELECT MAX(CONCAT(date, ' ', time)) ...

So, first do this for both tables.

ALTER TABLE tableA ADD COLUMN creation_date datetime; /*or whatever name, just make it meaningful and don't use keywords*/
UPDATE tableA SET creation_date = CONCAT(date, ' ', time);
ALTER TABLE tableA DROP COLUMN date, DROP COLUMN time;
CREATE INDEX idx_dt_tableA_creation ON tableA(creation_date);

Then you can insert both tables into your combine_table
(Note, left this for completeness, the second option is much better).

INSERT INTO combined_table
SELECT col1, col2, creation_date
FROM (
      SELECT col1, col2, creation_date
      FROM tableA
      UNION ALL
      SELECT col1, col2, creation_date
      FROM tableB
) sq /*subquery_alias*/
WHERE creation_date = (SELECT MAX(creation_date) FROM (
                                    SELECT col1, col2, creation_date
                                    FROM tableA
                                    UNION ALL
                                    SELECT col1, col2, creation_date
                                    FROM tableB
                        ) another_sq
                        WHERE sq.col1 = another_sq.col1
                       )
;

Nonetheless, this will be a heavy operation, if you really have that much data.

Now that I think of it, there's a better way of doing it.

First insert tableA

INSERT INTO combined_table
SELECT * FROM tableA;

Then do an insert on duplicate key update.

INSERT INTO combined_table c
SELECT * FROM tableB b
ON DUPLICATE KEY UPDATE
/*you can skip col1, since it's the identifying primary key here*/
col2 = IF(b.creation_date > c.creation_date, b.col2, c.col2),
creation_date = IF(b.creation_date > c.creation_date, b.creation_date, c.creation_date)
;