Appropriate multi-language schema, or overkill? UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/

I am building a web app, and I want it to support multiple languages. This structure has two components:

Connecting locale ('english','Deutch', etc) with terms, and having a rosetta stone connecting terms, and terms in specific language.
Grouping terms by page. I don't want to say, SELECT term1,term2,etc through the 30+ terms I might need on a page. I want to ask by the page they're connected to.

Here is my proposed table structure (note all the id's have relationships/indexes among them to make very efficient queries):

  * locale
      * id
      * value //English, Deutch, etc//
  * terms
    * id
    * value //In English//
  * page
    * id
    * value //Think add entry, menu//
  * page_group //group all terms to a page, for easy pulling//
    * id
    * page.id
    * term.id
  * rosetta
    * id
    * locale.id
    * term.id
    * value //french word for amount, description, etc//

This will allow queries like:

SELECT localization.value,
        terms.value
FROM localization
INNER JOIN terms ON terms.id=localization.termid
INNER JOIN page_group ON page_group.termid=localization.termid
INNER JOIN page ON page.id=page_group.pageid
INNER JOIN locale ON locale.id=localization.localeid
WHERE page.value='add_entry' AND locale.id=custlangid
ORDER BY terms.id

I only have to ask for two items; the language id that I need, and the page I need. It'll serve up all the terms, in the specified language, that are a part of the group of terms for that page.

I think this is a really good structure, but I would love some feedback.

UPDATE: To clarify, we are just talking about localization of the UI components. (labels, navigation, helpful text) All the info the user enters will be stored in unicode, not in this schema.

UPDATE 2: I've actually ended up using this, and it's great. Here's my post on it's actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/

We've done a lot of this, and (administrative) users were allowed to fix the translations live.  (You still might want a caching layer, but I'm totally down with driving this with a real database and not resource files - it gives you a ton of power to query and find things which need to be translated, etc).  I think your schema is probably fine, so I'll just pass on some stuff we learned in the hope that it's useful.

One thing you have left out is phrases with insertion points.  In the example below, the order is reversed and the language is still English, but this could very easily be two different languages - pretend this is just two languages who normally put things in a different order.

Hello, <username> you have <x> points!

You've got <x> points to spend, <username>!

In our pre-.NET, we had a routine which did the insertion so the phrases would look like this:

Hello, {0:username} you have {1:points} points!

You've got {1:points} points to spend, {0:username}!

This would obviously simply be used in your code as <%= String.Format(phrase, username, points); %> or similar

Which helped the translator a bit.  But .NET String.FOrmat doesn't support commenting within the format string, unfortunately.

As you say, you would not want to handle that in your php with locale awareness or meta phrases.

So what we had was a master phrase table:

phraseid, english, supplemental info

and a localized table:

phraseid, localeid, translation

You've also assumed with INNER JOINS that the localized versions exist - we tended to leave them out until they were translated, so that query of yours would end up returning nothing at first (not even the default)

If a translation didn't exist, ours defaulted to English, then fellback to code-provided (in case the database didn't have the ID, and it was also clear from the code what phrase identifier "TXT_LNG_WRNNG_INV_LOW" was actually trying to get) - so the equivalent of this query is what we used:

SELECT COALESCE(localized.translation, phrase.english, @fallback)
FROM DUAL
LEFT JOIN phrase
    ON phrase.phraseid = @phraseid
LEFT JOIN localized
    ON localized.phraseid = phrase.phraseid
    AND localized.localeid = @localeid

Obviously, you might get all the things at one time using your page system.

We tended not to link things to the page because they were re-used a lot between pages (and not just in page-fragments or controls), but that's certainly fine.

In the case of our Windows-native apps, we used reflection and a mapping file from control to translation tag so that translation did not require re-compiles (in pre-.NET apps we had to tag the controls using the Tag or other special properties).  This is probably a little more problematic in PHP or ASP.NET MVC, but possible in ASP.NET where there is a full-blown server-side page model.

For testing, you can obviously query to find missing translations very easily.  To find places which need to be tagged, translate the entire phrase dictionary using pig-latin or Klingon or something like replace every non-space character with ? - the English should stand out and let you know that some naked plaintext has crept into your HTML.


MySQL: How to troubleshoot when simple queries are not returning anymore? This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again.

When it happens, I can use a client to login but when I run simple queries they run forever and don't return, or would eventually return but I dont know after how much time.

mysqld.log shows nothing important and when I kill my SQL query I get: 160712  7:41:36 [ERROR] /usr/libexec/mysqld: Sort aborted, which I think is normal.

Disk space is fine, plenty of space. CPUs are fine too.

Basic mysqlcheck on all databases all returned OK.

mysqltuner results are the following one hour after being restarted:

[OK] Currently running supported MySQL version 5.1.71-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4K (Tables: 2)
[--] Data in InnoDB tables: 327M (Tables: 222)
[!!] Total fragmented tables: 12

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 8m 4s (12K q [2.990 qps], 2K conn, TX: 4M, RX: 842K)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 6.2G global + 14.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 13.2G (84% of installed RAM)
[OK] Slow queries: 0% (0/12K)
[OK] Highest usage of available connections: 1% (8/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/102.0K
[!!] Key buffer hit rate: 81.2% (48 cached / 9 reads)
[OK] Query cache efficiency: 91.0% (4K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 139 sorts)
[!!] Joins performed without indexes: 16
[OK] Temporary tables created on disk: 0% (0 on disk / 9 total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 97% (230 open / 237 opened)
[OK] Open file limit used: 0% (25/65K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB buffer pool / data size: 6.0G/327.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 2.0M, or always use indexes with joins)

I see some tables are fragmented, is it something I should be checking?
What else should I be doing/looking for?

Thank you in advance

You are hardly using MyISAM, so ignore the issues about MyISAM.

Ignore the recommendation to OPTIMIZE and 'fragmented'; that tool always says that, even if it is of no use (which is usually the case).

Joins without indexes -- you are better off looking at any 'slow' queries.

Find the slow queries.  First try SHOW FULL PROCESSLIST when it "hangs" to see if you can catch a query.  Another thing to do is to turn on the Slowlog, run for a day, then use pt-query-digest to summarize the slowlog.  Let's look at the first couple of queries.

"Sort aborted" sounds like a poorly written query; the PROCESSLIST should show it before killing it.  Perhaps it is a JOIN without an ON.


Why is my SQL Server database file so large? I have a relatively simple database that is over 300MB on disk.  When I look at the table data it adds up to just over 5MB.  Please see the screenshot below:

This screenshot can also be seen here.
If I add several more devices, I will run out of disk space at this rate! Why is this so large and how do I shrink it and keep it under control automatically?
Query results from the "answer" in this related thread say that reserved MB should be 7.66, which is still far more reasonable than 300+.

It is the log file.  I've backed it up, told it to truncate, shrunk the log file and still no luck. I've tried shrinking both database and the log file directly.  I should probably give up and move to simple mode, but I would prefer full recovery if I could control the log size.

Simple recovery doesn't make it easier to shrink a log file. It will just empty the log for you (when checkpoint occurs), same thing as you do when you perform a log backup. The thing with a log file, regarding shrink, is that it cannot be shrunk beyond the last used Virtual Log File (VLF). So, examine the VLF layout if you are interested. If just do a log backup and then shrink several times and the last VLF will eventually move towards beginning of the file and the shrink will happen. More info: https://karaszi.com/large-transaction-log-file


Adding columns & index to a huge table I'm working with a Rails app backed by a MySQL database. One of the more-frequently used models is backed by a table with over 200,000,000 rows (and growing). We've been aware for a while that the way it's set up isn't scalable.

The feature I'm working on at the moment requires me to add a couple new columns to that table, as well as a compound index. My coworker did a test-run on her local dev machine and it took about an hour per column (didn't even test adding the index yet).

We're considering taking a different approach, building a new table with the needed columns, dumping the data from the old table to the new one, then pointing the Rails model at the new table instead. Feels like a short-term solution for a large looming problem, but I'm curious if anyone has insight on how to navigate this problem.

We've also talked about archiving the table's old records. It's a table that gets written to and read from a lot, but only with recent records (the app probably never needs to do anything with records more than a month old).

First of all, use one alter statement to do all of adding two columns and the compound index. ALTER TABLE table1 ADD col1 INT, ADD col2 CHAR(2), ADD INDEX (col1, col5);. More about ALTER
Archiving old records is a good idea. If it is very rarely needed, then some slowness is affordable (I think).
Depending on your engine that you use, dumping the data and importing it to a new table maybe slow if you can't disable the keys. If you drop all indexes and recreate them, that is slow again. If you can afford some down time, any method is ok.
If the tables keeps growing, and you don't want to archive, you may consider "Sharding" the table, but that would require some extra efforts at development level
Last idea, which I don't strongly recommend unless you can't afford downtime, is to create a new table with a primary key and the new column that you want to add. Use this PK to join the original table.


Reg: Default Privileges on PostgreSQL Schemas I have a situation where many users can create database objects across all the available schema.

There is a generic user pg_dump_user used for by all the developers to dump the whole database to their local db whenever required. So this user should have read access to all the database objects.

To achieve this, I have tried setting the default privilege at schema level as well as at the user level.

--Default privileges set for each schema

ALTER DEFAULT PRIVILEGES IN SCHEMA xyz GRANT SELECT ON TABLES to ro_user';

--Default privileges set for each user

ALTER DEFAULT PRIVILEGES FOR ROLE abc_user GRANT SELECT ON TABLES to ro_user' ;

Even though above setup is there, after certain times these are getting vanished as the new objects created are not accessible by ro_user.

Any pointer on this would help, my aim is just to achieve select access for all the objects current and future for a user so as to do pg_dump.

The documentation about ALTER DEFAULT PRIVILEGES tells you a possible reason - it is not that clearly described, though.

Let's see, what is said:

  You can change default privileges only for objects that will be
  created by yourself or by roles that you are a member of.

This means that the default privileges defined by this statement applies only to objects that are created by you (or the role you are a member of).  Let's see this in action!

alice's schema

First, we are logged in as alice.  Then, in a newly created schema, we create a table and grant some rights to bob:

SELECT current_user;
 current_user
──────────────
 alice

SHOW search_path ;
 search_path
──────────────
 test, public

CREATE SCHEMA alicetest;

ALTER DEFAULT PRIVILEGES
    FOR ROLE alice
    IN SCHEMA alicetest
    GRANT ALL ON TABLES TO alice;

GRANT SELECT ON alicetest.a TO bob;
-- this I do only for showing the privileges -
-- the owner has by default ALL and is not shown by \dp

\dp alicetest.a
                             Access privileges
  Schema   │ Name │ Type  │  Access privileges  │ Column access privileges
───────────┼──────┼───────┼─────────────────────┼──────────────────────────
 alicetest │ a    │ table │ alice=arwdDxt/alice↵│
           │      │       │ bob=r/alice         │

alice now has all rights on her table, as expected.

bob' table in the same schema

Now, after obtaining access to this schema, bob tries to create a table:

SELECT current_user;
 current_user
──────────────
 bob

CREATE TABLE alicetest.b (id integer);

GRANT SELECT ON alicetest.b TO alice;

\dp alicetest.b
                            Access privileges
  Schema   │ Name │ Type  │ Access privileges │ Column access privileges
───────────┼──────┼───────┼───────────────────┼──────────────────────────
 alicetest │ b    │ table │ bob=arwdDxt/bob  ↵│
           │      │       │ alice=r/bob       │

As you can see, despite creating the table in alice's schema where she set the default privileges, bob's table doesn't have all those permissions.  This happens because alice is not a member of bob.

Let's check this membership thing, too, and try to define default privileges by alice again, this time for another role:

ALTER DEFAULT PRIVILEGES
    FOR ROLE charlie
    IN SCHEMA alicetest
    GRANT ALL ON TABLES TO alice;
ERROR:  must be member of role "charlie"

So, some mighty enough user grants her a membership in charlie, then she tries again, with success:

ALTER DEFAULT PRIVILEGES
    FOR ROLE charlie
    IN SCHEMA alicetest
    GRANT ALL ON TABLES TO alice;
ALTER DEFAULT PRIVILEGES

charlie's round

Then charlie creates a new table:

CREATE TABLE alicetest.c (id integer);

And the privileges:

\dp alicetest.c
                               Access privileges
  Schema   │ Name │ Type  │    Access privileges    │ Column access privileges
───────────┼──────┼───────┼─────────────────────────┼──────────────────────────
 alicetest │ c    │ table │ charlie=arwdDxt/charlie↵│
           │      │       │ alice=arwdDxt/charlie   │

As you see, alice, as a member of charlie, gets her access to this table.

To answer your question,

I guess you defined the default privileges for yourself (alice in the example), but you developers act as a bunch of bobs here, not getting the necessary privileges.  One way to get around this (as we do it at work) is to do a

SET ROLE TO schema_owner;

every time before creating a new object in the schema.  This should be a role that all developers are a member of (otherwise you'd get an error).

NOTE that \dp is a psql command.


Can the 'returning' clause return source columns that are not inserted? Here's a minimal example of my real-world problem:

create table t(id serial primary key, rnd double precision);

of course you can return the inserted columns with a returning clause:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID |            RND |
|----|----------------|
|  9 | 0.203221440315 |
*/

you can also return a literal:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID |            RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 |     1 |
*/

but you can't return the source columns:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/

Is there any way I can get w.rnd out of the final returning clause?

db<>fiddle here

The documentation on the RETURNING clause says:

  An expression to be computed and returned by the INSERT command after
  each row is inserted. The expression can use any column names of the
  table named by table_name. Write * to return all columns of the
  inserted row(s).

This clearly does not apply for columns from an other table.

Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:

WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
     x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
  FROM w, x;

That is, you can put more than one writable CTE to the beginning of a query.  Please see this in action on dbfiddle.


How to SELECT cells from mysql for a HTML table? I have data for individual cells of a HTML (or excel) table as

CREATE TABLE table_cols
(
column_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
column_number tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(column_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE table_rows
(
row_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
row_number tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE table_cells
(
row_id smallint(5) unsigned,
column_id smallint(5) unsigned,
content varchar(255),
PRIMARY KEY(row_id,column_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

What is the best SELECT query to catch data to be displayed in a HTML table as

 columns titles

row title	 cells

I don't expert to make the entire table by one query. I get the results by  PHP.

MySql has no XML data type so it's hardly can help ( more help then app layer code) to generate HTML markup.
It can help to create ordered stream of cells. Kind of

select r.row_number, h.column_number, c.content
from table_cells c
join table_rows r on c.row_id = r.row_d
join table_cols h on c.column_id = h.column_id
order by r.row_number, h.column_number

Or may be to convet sparse array of cells to regular one as directed by table_cols and table_rows, if that is the case.


UPDATE n:m relation in view as array (operations) Environment

I came up with a view and trigger based solution to modify rows in multiple underlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The next task I have to solve is mapping n:m relations which refer to the mentioned objects.

Tables

CREATE TABLE art (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40),
  -- more columns, irrelevant to the problem
);

CREATE TABLE tag (
  id bigserial NOT NULL PRIMARY KEY,
  name character varying(40) NOT NULL UNIQUE,
  useable boolean NOT NULL DEFAULT false
);

CREATE TABLE art_tag (
  article_ref bigint NOT NULL,
  tag_ref bigint NOT NULL,
  CONSTRAINT art_tag_pk PRIMARY KEY (article_ref, tag_ref),
  CONSTRAINT art_tag_article_fk FOREIGN KEY (article_ref)
      REFERENCES art (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT art_tag_tag_fk FOREIGN KEY (tag_ref)
      REFERENCES tag (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

The view is populated with every relevant column of the main table art (articles) and another column with all tag IDs as array - aggregated with array_agg(<column>) AS <alias>.

Whenever I edit a view row I have to handle the array which resolves to the surrogate key table.

Required Operation

I was looking for a kind of array subtraction which can be explained by the following examples.

{1,2,4} - {1,2,3} = {4}
{1,2,3} - {1,2,4} = {3}

Solution

This code could be used inside the triggers (remove the clutter beforehand) to calculate all elements which have to be deleted from or inserted into the surrogate key table.

DO
$do$
DECLARE
  n bigint[] := array[1,2,3];
  o bigint[] := array[1,2,4];
  i integer;
BEGIN
  FOREACH i IN ARRAY n
  LOOP
    IF NOT (SELECT i = ANY (o)) THEN
      RAISE NOTICE 'insert: %', i; -- execute insert query
    END IF;
  END LOOP;
  FOREACH i IN ARRAY o
  LOOP
    IF NOT (SELECT i = ANY (n)) THEN
      RAISE NOTICE 'delete: %', i; -- execute delete query
    END IF;
  END LOOP;
END
$do$

resulting in:

NOTICE:  insert: 3
NOTICE:  delete: 4

which perfectly fits the expected output. Replacing the NOTICE with a suitable query should suffice. As the trigger always handles exactly one view row the second referenced id, not contained in the array, is known to the trigger function. So following the example code I got two pairs

(1,3) and (1,4)

where 1 represents the article id. The first pair is the one to insert into the surrogate key table and the second one is the one to delete.

Question

Will that just work or is it even an appropriate solution to the task? Is there any other, maybe better way to achieve this? (Concurrency should be assured.)

Qt's model view programming works pretty simple if exactly one table is affected. Otherwise I have to code more stuff. The chosen solution suites me because I like to put as much as possible into the database.

I'll assume a single given art.id at a time.

Since there is no provision to explicitly lock not-yet-existing rows ("value locking") in Postgres, it's hard to defend against concurrent INSERT commands in the default isolation level READ COMMITTED. The more expensive isolation level SERIALIZABLE should solve this, I didn't take a closer look, I think I have a better idea.

A "brute force" approach in READ COMMITTED would be to lock the whole table art_tag in SHARE ROW EXCLUSIVE mode. The manual:

  This mode protects a table against concurrent data changes, and is
  self-exclusive so that only one session can hold it at a time.

If you have lots of concurrent transactions writing to the table, performance will deteriorate quickly either way.

To only defend against concurrent UPDATE / DELETE commands in the default isolation level READ COMMITTED, lock all rows of the given article_ref in art_tag and wrap the whole operation into a single transaction.

Solution

There is a smarter alternative: take a FOR NO KEY UPDATE lock on the one row in the master table art. (FOR NO KEY UPDATE is a bit less restrictive than the common FOR UPDATE and good enough for our purpose.)

This is assuming that you have no other, possibly racing, concurrent write access to art_tag that ignores locks on art. It's your responsibility to make sure of it.

BEGIN;

SELECT * FROM art WHERE id = 1      -- we do not actually retrieve rows
FOR NO KEY UPDATE;                  -- only to lock the master row exclusively

DELETE FROM art_tag
WHERE  tag_ref <> ALL ('{1,2,3}'::bigint[]);

INSERT INTO art_tag (article_ref, tag_ref)
SELECT 1, unnest('{1,2,3}'::bigint[])
ON CONFLICT DO NOTHING;             -- requires Postgres 9.5 or later

COMMIT;                             -- locks released at end of transaction

To fine-tune behavior in case of conflicts consider the NOWAIT or SKIP LOCKED modifiers.

Related:

Postgres UPDATE ... LIMIT 1
How do I insert a row which contains a foreign key?

Function

You can wrap this into a function to simplify parameter passing and have an automatic transaction wrapper:

CREATE OR REPLACE FUNCTION f_set_tags(_article bigint, VARIADIC _tags bigint[])
  RETURNS void AS
$func$
BEGIN
   PERFORM * FROM art WHERE id = _article
   FOR NO KEY UPDATE;

   DELETE FROM art_tag
   WHERE  tag_ref <> ALL (_tags);

   INSERT INTO art_tag (article_ref, tag_ref)
   SELECT _article, unnest(_tags)
   ON CONFLICT DO NOTHING;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_set_tags(1,1,2,3);  -- 1st param is article_ref, rest are tag_ref

Or, if you have an actual array to pass:

SELECT f_set_tags(1, VARIADIC '{1,2,3}');
SELECT f_set_tags(1, VARIADIC your_array_variable);

For small sets of tags (max. 100), a VARIADIC parameter should be particularly convenient, to pass a list of values for the array. You can still pass an actual ARRAY (with any number of elements). Details:

Return rows matching elements of input array in plpgsql function

Could be a plain SQL function as well (switch back SELECT for PERFORM). I expect PL/pgSQL to be a bit faster for repeated calls in the same session (prepared statements).

Caution! Locks are only released at the end of a transaction, so it might be unwise to call this function in the context of a bigger outer transaction!


Auditing data changes in SQL Server I am tasked with implementing auditing of data changes on our database.  I am aware that there are commercial products for this, but I wanted to know which design would be better.  For any given row we will want to know what values were changed, who changed it, and when the change took place.

We have Enterprise Level data (possibly millions of rows), and we are using MS SQL Server Web (ver.13).

A suggested design used histid as the unique primary key on every table; What would normally be the unique primary key would be repeated (once for each version of the row):

-- primary key is histid.
-- the current programAssignmentId is what I am  typically searching for.

SELECT
    [histid] -- column is primary key.
    ,[isCurrent]
    ,[lastupdate] -- column is indexed.
    ,[programAssignmentId] -- column is indexed.
    ,[programid] -- foreign key, column is indexed.
    ,[entityid]  -- foreign key, column is indexed.
    ,[lastUpdatedById] -- foreign key, column is indexed.
    ,[effectiveDate]
    ,[orderno]
    ,[status]
    ,[deleted]
    ,[created]
FROM [dbo].[mem_programAssignments]
WHERE [programAssignmentId] = 'a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2'
ORDER BY [programAssignmentId], [lastupdate] DESC

Would yield the following results:

histid                                isCurrent   lastupdate                programAssignmentId                    programid
===================================   =========   =======================   ====================================   ====================================
7330075F-B076-113D-B548CDDD0BF5D4B8   1           2017-08-17 13:37:56.950   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661
73291EC6-F168-E817-EB42EB46A5AFB08C   0           2017-08-17 13:37:11.670   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661
56F73DE0-0137-4CBF-969AEDFE55229E3B   0           2017-04-24 18:49:11.000   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661

A competing design suggested one or more history tables that would record the changes.

SELECT
   [id]
    ,[reference_class_name]
    ,[reference_table]
    ,[reference_id]
    ,[column_name]
    ,[old_value]
    ,[new_value]
    ,[created_at]
    ,[user_id]
    ,[user_name]
    ,[message]
FROM [dbo].[history]

Would output something like:

id   reference_class_name         reference_table          reference_id                            column_name   old_value    new_value
==   =========================    ======================   =====================================   ===========   ==========   ===========
24   Models\ProgramAssignement    mem_programAssignments   b40b2d80-99d3-11e8-a0ad-29cac47b46c7    startDate     2017-01-01   2018-07-11
25   Models\ProgramAssignement    mem_programAssignments   b40b2d80-99d3-11e8-a0ad-29cac47b46c7    deductible    15000000     30000000

The reference_id in this case would be referencing the id value of the primary key of the table listed in the reference_table column (mem_programAssignments in this case).

The advantage to the second design is it would separate the historical values from the current values, but the obvious drawback would be that the history table(s) could get extremely large.

Is one design better than the other -- or I should say, what are the advantages, disadvantages, or risks with these designs?  Are there other design patterns I should consider?

For a relatively immutable tracking of history, System Versioned Temporal Tables may be the way to go as you are using a recent enough edition of SQL Server (similar support is available in some other databases). They have some gotchas that mean they are not suitable for all circumstances but if they fit your model they'll make life easier by implementing chunks of it for you "for free" and allowing you to use some useful time-travelling syntax such as SELECT <stuff> FROM <table> AS OF <date_time> and BETWEEN <date_time> AND <date_time> instead of more manually querying the history.

For an audit you need extra though, as well as what happened when you need to track who made the change. One option would be to add a user identifier column to each table being tracked, which would be included in the history too. If you are tracking users that all have their own SQL Server login then you can populate that by trigger, reading the value from SYSTEM_USER/SUSER_NAME(), though in the more common case of tracking users of an application that always logs into SQL Server using the same application account you will need to implement the population that column in your application logic.

  Is one design better than the other

Your second suggestion is essentially an extended property bag (where each property is actually property+time) which makes me wary of it. It is potentially flexible, but has its own significant issues which, for instance, make the model inefficient to report from. Lookup "Entity Attribute Value model" (the more technical term that "property bag" is usually a synonym for), on DBA.SE and elsewhere, for detailed discussions as to the pros and cons of this. It is commonly considered to be an anti-pattern.

isCurrent

If you do roll your own history structure instead of using the built-in support (because the built-ins are unsuitable for some reason, or you need to implement something more cross-platform compatible, for instance) then I would recommend against keeping all the data in your core tables and instead keeping current data on its own and a separate history. This will make querying current data, likely the most common thing you application will do, less error prone. If you keep all versions1 in your history structure you may still have an isCurrent flag in those structures.

1 SQL Server's temporal tables don't keep the latest version of a row in the history table (unless it has been deleted, depending on if you conceptually consider the last existing version to be the latest or not) only copying the previous version in as it is modified. Some history/audit implementations do hold the latest version of each row as well as the past ones so that the history table contains everything.


Precomputing expensive aggregations I'm looking for a tool that will let me precompute expensive aggregations some time after rows have been inserted into a MySQL database.  For example, if I often run a SUM() on a column, I'd like to instead store a running sum in a separate table that can be queried instead of the raw data.

My use case is for heavily-read live stat data (graphs, etc.).

Are there more extensive options beyond materialized views?  The thought behind finding a system like this is to reduce the amount of raw sql required to create materialized views as the users of the system would like to specify aggregations without SQL (as new aggregations and stats are being created constantly).

Another option would be to put the data into an OLAP cube.  OLAP cubes are designed for doing aggregates.


Select statements during long update I've a table

MESSAGES
id (long) | attach_data (image) | ins_date | flag

this table contains all messages of my application. During night, my job cleans old attach_data with query

update messages set attach_data=0x
where ins_date< DATEADD(DAY, -120, GETDATE())
and datalength(image)> 0

this is very long query (about 2 hours)

Other processes are always querying same table with

select id FROM MESSAGES WHERE Flag = 0 ORDER BY ins_date ASC

But during this 2 night hours, this query is not function (timeout expired).
There are a tricks?
I'm running Sql server 2005

[Note: I got to the end of writing this and noticed that sometimes you refer to the data field as image and sometimes as attach_data.  You'll need to take that into account as you read this.]

You might try breaking up the update statement into multiple batches with TOP:

UPDATE TOP (5) messages SET attach_data = 0x
WHERE ins_date < DATEADD(DAY, -120, GETDATE())
AND DATALENGTH(image) > 0;

Then you'll need to modify your maintenance window to execute this query repeatedly until no records are returned.  You also might want to use a static date parameter specified by the application since the dates will create a moving target as you re-run this query.

Note that this method will cause the maintenance process to take longer to process overall, but each batch should complete more quickly and therefore you shouldn't be blocking reads as long.  You can make the whole process faster by increasing the size of the TOP, but each transaction will lock longer.  It's a tradeoff you'll have to play with.

Note also that you can't easily specify an order the records will be updated here since UPDATE doesn't support ORDER BY; you'll just have to keep repeating it until it finishes.

If that doesn't work for you, then there are some optimizations you could make.  I would guess that part of the issue is that DATALENGTH() is not a very fast function since it has to read the data.  Do you really need to test data length here?  Is what you're doing significantly different than this:

UPDATE messages SET attach_data = 0x
WHERE ins_date < DATEADD(DAY, -120, GETDATE())
AND attach_data <> 0x;

If you must use DATALENGTH() or the above doesn't work, its worth noticing that it is deterministic.  That means you can index it.  It may be worthwhile to create an indexed view, or a computed column in the table which has DATALENGTH() that you can then index.

Something like this:

CREATE VIEW messages_maintenance
WITH SCHEMABINDING
AS
SELECT id,
    ins_date,
    DATALENGTH(image) AS "image_datalength"
FROM messages;

Followed by a unique clustered index on id, and then a nonclustered index on image_datalength, ins_date.  Then you can run:

UPDATE messages SET attach_data = 0x
WHERE EXISTS (SELECT 1 FROM messages_maintenance
        WHERE messages_maintenance.id = messages.id
        AND messages_maintenance.image_datalength > 0
        AND messages_maintenance.ins_date < DATEADD(DAY, -120, GETDATE()));

Adding a computed column and an index on that would be similar.

Note that this method does add a number of indexes that now have to be maintained by the DB, so it isn't free.


Postgres 9.1 statistics in pg_stat_database I've been searching for some information about Postgres 9.1 stats which are stored in pg_stat_database - on the web and on IRC. I found SOME information for 9.2 but even that doesn't seem to be accurate. What I'm after is to get proper transaction stats ie commits and rollbacks. Also I've no clue what tup_fetched and tup_returned mean.

I made a few tests on a dummy database. I created a database called statistics and one simple table called films in it. Inserted a few values into it and here is what I found out:

statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_database where datname='statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
          94 |             0 |       182 |     3259 |        18345 |        1293 |           27 |           0 |           0
(1 row)

statistics=# INSERT INTO films VALUES ('UA509', 'Bananas', 105, '1971-04-13', 'Comedy', '85 minutes');
INSERT 0 1
statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_database where datname='statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
          97 |             1 |       182 |     3261 |        18345 |        1293 |           28 |           0 |           0
(1 row)

statistics=# select * from films;
 code  |  title   | did | date_prod  |  kind  |   len
-------+----------+-----+------------+--------+----------
 UA502 | Bananas  | 105 | 1971-07-13 | Comedy | 01:22:00
 UA503 | BanaAAas | 106 | 1971-07-13 | Comedy | 01:26:00
 UA504 | BanaAAas | 106 | 1971-07-13 | Comedy | 01:26:00
 UA509 | Bananas  | 105 | 1971-04-13 | Comedy | 01:25:00
(4 rows)

statistics=# select xact_commit, xact_rollback, blks_read, blks_hit,tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted from pg_stat_database where datname='statistics';
 xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted
-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------
          99 |             1 |       182 |     3262 |        18349 |        1293 |           28 |           0 |           0
(1 row)

What this shows is that xact_commit increases by 3 after just one row insert. tup_inserted does seem to report correct value.
After simple SELECT, xact_commit increases by 2 - kind of strange as SELECT shouldn't really be increasing xact_commit ?
What I'm trying to do is to gather some reasonable data which would give me an idea about how many transactions/s particular Database is doing.
Could any of the gurus shed some light on this ? I take it that tup_fetched and tup_returned are some really damn random values which only Postgres developers understand :)
Thanks in advance!

It seems that pg_stat_database is thoroughly described in the manual.

As to the increasing xact_commit. You might be have AUTOCOMMIT=on. PostgreSQL will always run all your queries in a transaction. In case AUTOCOMMIT=on, COMMIT will issued after each statement executed.

In your case you have 2 statements:

previous SELECT * FROM pg_stat_database;
your test statement, like SELECT 1

You might try the following scenario:

open 2 sessions, S1 and S2;
S1: SET autocommit TO off;
S1: SELECT * FROM pg_stat_database;
S1: SELECT * FROM pg_stat_database;
S2: SELECT 1;
S1: SELECT * FROM pg_stat_database;
S2: SET autocommit TO off;
S2: SELECT 1;
S1: SELECT * FROM pg_stat_database;

You can continue playing around yourself.


PostgreSQL How to optimize a query with ORDER BY and LIMIT 1? I have the following PostgreSQL schema:

CREATE TABLE User (
    ID INTEGER PRIMARY KEY
);

CREATE TABLE BOX (
    ID INTEGER PRIMARY KEY
);

CREATE SEQUENCE seq_item;

CREATE TABLE Item (
    ID INTEGER PRIMARY KEY DEFAULT nextval('seq_item'),
    SENDER INTEGER REFERENCES User(id),
    RECEIVER INTEGER REFERENCES User(id),
    INFO TEXT,
    BOX_ID INTEGER REFERENCES Box(id) NOT NULL,
    ARRIVAL TIMESTAMP
);

Its main use case is a typical producer/consumer scenario. Different users may insert an item in the database in a particular box for a particular user and each user can retrieve the topmost(this means the oldest) item in a box that is addressed to her/him. It more or less mimics the functionality of a queue on a database level.

More precisely, the most common operations are the following:

INSERT INTO ITEM(SENDER, RECEIVER, INFO, BOX_ID, ARRIVAL)
VALUES (nsid, nrid, ncontent, nqid, ntime);

And retrieve commands based on a combination of either RECEIVER+SENDER or RECEIVER+BOX_ID:

SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND
(i.BOX_ID=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;

and

SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND
(i.SENDER=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;

The last two snippets are packed within a stored procedure.

I was wondering how to achieve best performance given this use case and knowing that the users will insert and retrieve somewhere between 50,000 and 500,000 items (however, the database is never expected to contain more than 100,000 items at a given point)?

EDIT

This is the EXPLAIN I get with for the SELECT statements no indexes:

Limit (cost=23.07..23.07 rows=1 width=35)
   -> Sort (cost=23.07..25.07 rows=799 width=35)
      Sort Key: ARRIVAL
      -> Seq Scan on Item i (cost=0.00..19.07 rows=799 width=35)
         Filter: (((RECEIVER = 1) OR (RECEIVER IS NULL)) AND (SENDER = 1))

The best EXPLAIN I get based on my understanding is when I put an index on the time(CREATE INDEX ind ON Item(ARRIVAL);):

Limit (cost=0.42..2.88 rows=1 width=35)
   -> Index Scan using ti on Item i (cost=0.42..5899.42 rows=2397 width=35)
      Filter: (((receiver = 2) OR (RECEIVER IS NULL)) AND (SENDER = 2))

In all of the cases without index on ARRIVAL I have to sort the table which seems to my inefficient. If I try to combine an index on ARRIVAL and RECEIVER/SENDER I get the same explanation, but slightly slower.

Is it correct to assume that a single index on ARRIVAL is the most efficient option?

A btree index on (sender,arrival) could help.  That would allow it to jump directly to the first-arrived message for a given sender.

One on (arrival,sender) is less likely to help.  That allows you to jump to the first-sent message globally, but then you still have to walk along those messages until you hit one from the specified sender.  If that particular sender is new, or only sends messages to people who keep up on their inbox, then you might have to walk through most of the index  before you find a qualifying message.  It might help in that you only have to walk the index, not the index and table combined, but that would still be a much smaller win than the proper index of (sender,arrival).

Similarly, you would want another index on (box_id, arrival), not on (arrival, box_id).

Also, performance testing on a table with 800 rows is useless if the real table will have 100,000 rows.


Fastest way to check if record is related to a particular user in postgres While implementing a sharelikes table I added a procedure to add a new record for a like.

I wanted to add a check before insert that the item being liked does not belong to the users who submitted the like request. Given function parameters Pshareid and Puserid I used the following conditional in the procedure

SELECT INTO checkuser true FROM ks.shares
WHERE id = Pshareid AND userid = Puserid limit 1;
IF checkuser THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

In the select described above the record will almost always not exist.

Is that more efficient than a SELECT on the share id to return the record (if it exists which is very likely at the point of select) and then to check the userid of the returned record?

SELECT userid INTO checkuser FROM ks.shares
WHERE id = Pshareid limit 1;
IF checkuser = Puserid THEN
    -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

You can use PERFORM 1 test with a control statement using FOUND special variable. Your query will get like this;

PERFORM 1
    FROM ks.shares
    WHERE
        id = Pshareid AND userid = Puserid
    LIMIT 1;

IF FOUND THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

Referenced sections;

Executing a Command With No Result

Available Diagnostics Items (special variable named FOUND)


Can I use RMAN to backup and restore data in a certain point in time? I have an Oracle database over which I have full control. What I am doing is performing load testing on our application, which uses Oracle as a database. To do this, I initially set the database up with some dummy data. I'm calling this "Point A".

The load testing will consume this data, and I will have to restore the data so it looks exactly like it was in Point A before I can re-run them. The hard way to do this is using Data Pump, which takes a long time and seeing how I need to backup several schemas it's rather complex.

I was wondering if it was possible just to issue a BACKUP DATABASE on RMAN after I have the data set up. Then I would run my tests, and restore it using something like RESTORE DATABASE FROM TAG <somedate>, and that would be all I need to get my tests going again. There should be no changes to the control file or anything like that.

Is this a safe way to go about doing this?

There is a feature specifically for that:

 CREATE RESTORE POINT before_test1 GUARANTEE FLASHBACK DATABASE;
 select NAME,SCN,TIME from v$restore_point;
 exec perform_my_test1;
 SHUTDOWN IMMEDIATE;
 STARTUP MOUNT;
 FLASHBACK DATABASE TO RESTORE POINT before_test1;
 DROP RESTORE POINT before_test1 ;

This is a database's feature, not RMAN's (commands are for sqlplus), see doc for the full setup and disclaimers.

To more precisely answer your question, RMAN can also be used for the task. The general script is:

run {
  set until scn 123123123 ;
  restore database /* from tag ... */;
  recover database;
}

Don't use bare RESTORE DATABASE FROM TAG because without SET UNTIL it doesn't work as you'd expect in some scenarios.

I wouldn't say any of these two solutions are inherently safe, both have as many pitfalls as any other Oracle's advanced features, so you need to do some research upfront.


Where to save user data? I hope this is the right stackexchange place to ask.

Im really new using databases and I have a little question: Where to save the user data? I mean, if Im logged as "Foxandxss" on my app, and I want the list of my... questions... How it knows the questions that are mine?

I ended with a "Owner" column in the table with the data. I filter the rows comparing Owner with the current logged user. For one table maybe is OK but if I have like 5, is quite overkilling.

So, how is the typical database that manage user data?

Thank you.

You'll need to describe the other 5 tables. How is the data related? You should only ever have to filter for owner once if the data is already naturally related. For example, if I want all the questions that are mine, and their related answers, I should be able to do something like this (please ignore syntax, I have no idea what platform you're using):

SELECT Q.QuestionID, Q.Text, ..., A.AnswerID, a.Text, ...
  FROM Questions AS Q
  INNER JOIN Answers AS A
  ON Q.QuestionID = A.QuestionID
  WHERE Q.Owner = 'Foxandxss';

The point: You should only need to check in one place for the owner. If you're storing the owner in the Answers table too, and for any given QuestionID that value is the same, this is wasteful and counter-productive.

(You probably wouldn't actually repeat the question data every time, since there may be 500 answers, and you would ideally have the username mapped to a userID of some kind so that you're not repeating that wide data throughout your indexes and data. But hopefully you get the drift.)


Why do we need listener for Oracle EBS application server? I'm using Oracle Application EBS R12 which connects to Oracle DB 10.2.0.4
Why do we need Listener.ora in Oracle EBS Application ?
If so, can anyone explain why EBS Application needs it ?

The ICM starts up a Service Manager on each node that is enabled for concurrent processing, by instructing the node's Applications Listener (which is dedicated to Concurrent Processing) to spawn a process running the Service Manager executable (FNDSM).

The Applications Listener must be configured to source the Oracle E-Business Suite environment file before FNDSM is spawned.

Following startup, the Service Manager acts as an agent of the ICM to start and stop concurrent managers on that node, according to their defined work shifts.

For more info Click here to Oracle Docs


Sybase Database : least permission/roles to extract ddl I am currently using Sybase Central 15.x. In which I have a database, a login and a User.
Here I need to give permission(s)/role(s) to a user by which it able to extract ddl of database.
There I found that if I give sa_role then user can extract ddl. But as I know sa_role make a user as a super user(sa). So I need to know that there exist any other permission/role to do the same task.

From the ASE Utility Guide; Section: ddlgen, regarding Permissions required to use ddlgen:

Users must have either sa_role or sso_role to generate DDL for:
- Encryption keys
- Logins
- Roles
For all other objects, users do not need any specific permissions or roles to generate DDL

Is your user receiving errors when they attempt to use ddlgen (and if so, what errors)?


Is tsvector the right choice for searching exact match of two words with variations like plural and singular? I'm developing a search application that takes a string and then break it down into a combination of two words and then search a table for matching patterns. An example:

'My cat ate a rat'

search patterns: 'my cat' 'cat ate' 'ate a' 'a rat'

At the same time, this should match with plurals and singulars of the combination. So 'my cat' should match 'my cats' and also "my cat's". Stop words should not be ignored and the two words must be in the same order with no words between them.

My question is, is tsvector the right tool for this or can this be done using the 'LIKE' operator alone? I'm asking this to decide whether I need to spend time diving deep into tsvector to build my queries.

Thanks!

note: this is my first time here, please let me know if this is not the right place to ask this type of question.

Short answer; yes, a tsvector column used as an index will allow you to return matching documents with stemming and pluralization handled by the default text search configuration.

The documentation is very complete and easy to read and will give you a solid grounding in how full-text search works and a bit in managing updates to the corpus and likely issue.

There are some subtleties you need to look at, but the defaults are pretty good for most languages. Things that might give issues are mostly to do with your documents structure and quirks that might need to be addressed with tweaks to the dictionaries or stop words list ( stop words are ignored for search purposes ).


How to estimate database size needed? I work on a transaction banking service project, and we need to have a central database to keeping client's info, account info, transaction details, etc.

So, how can I estimate the size of database needed?
Or, is there any industry benchmark for database size?

Thank you!

A rough estimate for bare tables is pretty simple. The number of bytes in a row is roughly

  Total number of bytes for all columns
+ dbms overhead

dbms overhead might include a fixed or variable number of bytes per row or per column. The number of bytes of overhead often depends on the datatype of the column. The dbms might also apply padding to make bit columns, for example, align with a machine word boundary.

Multiply the bytes per row by the expected number of rows one year, two years, or five years from now. Repeat for every table, or for only a statistically representative sample of the tables.

You can often find leads to the details for your dbms by searching for "your_dbms row format". If you searched for

sql server row format

you'd probably find this blog article.

Your dbms might require you to take into account compression, compact storage formats, and indexes.  Compression and compact storage formats reduce the amount of storage space you need; indexes usually increase it.

If you want a really good estimate, build a testbed. Fill it with statistically representative sample data. Do the arithmetic based on what that tells you.  Include estimates for new tables to implement new features.


Phantom SQL Server database backups. How can I find the cause? I've noticed from the event log, that something is backing up SQL Server databases, separately to our maintenance plan backups. We can see something running SQL backups at 03:00. Our intended backups only kick off at 04:05.

The backup logs look like this:

  Database backed up. Database: master, creation date(time):
  2015/11/15(09:39:37), pages dumped: 490, first LSN: 743:441:73, last
  LSN: 743:472:1, number of dump devices: 1, device information:
  (FILE=1, TYPE=VIRTUAL_DEVICE:
  {'{9BCE7BA5-E8AB-4459-8655-365988420F35}12'}).

There seems to be no other backup of the database files themselves which might trigger a volume shadow copy (VSS writer) that we can see, so it's a mystery what's causing these.

The worry is of course, that these backups will interfere with the restore process for point-in-time restore as we have no access to these full backups, yet they are presumably resetting the transaction log.

The server is a Virtual Machine and I originally theorised that perhaps something was backing up the entire VM at this time, and perhaps that was causing VSS to create the backups, but apparently there is no machine imaging backup set up on this server - only file based backups of certain folders and the main SQL data folder is not backed up.

Is there anything I can set up which could provide any clues as to what's running these backups?

This looks like Veeam backups or some other third-party backup tool. Ask your systems guys if they take VM snapshots and backup the SQL server. Make sure you are VERY careful as Veeam can take FULL backups and break your differential base and possibly hose up your restore chain. I believe in Veeam they can do COPY_ONLY backups or set it to not truncate the transaction log, you'll want to make sure they aren't causing your well-planned restore strategy to go down the toilet in the event of disaster.


Change transaction isolation level inside running transaction I'm studying transaction isolation levels in SQL Server, and I'm trying to figure out how SQL Server behaves when the isolation level changes during the lifetime of the transaction.

It seems that something like this is possible in SQL Server:

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* (some selects/inserts/updates/deletes) */

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

/* (some selects/inserts/updates/deletes) */

COMMIT TRANSACTION;

To be honest, I cannot think of an example when a decrease in the isolation level would make sense, I can only think of some scenarios where a part of the transaction would require serializable isolation while other parts wouldn't. I have a feeling that mixing isolation levels using snapshots and row-versioning with other kinds of isolation levels won't work well, but I cannot find much information to back this up.

Is it a thing that happens in practice that a single transaction switches between multiple isolation levels during its lifetime? Are there some caveats and details to know about?

The syntax you are using is valid, all statements executed after the SET TRANSACTION ISOLATION LEVEL command will use the specified isolation level.

Consider the following table:

CREATE TABLE dbo.test
(
    ID int identity(1,1) Primary key
    , Field nvarchar(10)
)
GO

INSERT INTO dbo.test(Field)
Values ('XXXXXXXXXX')
GO 100

Next, start a transaction and execute 2 statements.
The first statement will use REPEATABLE READ (which won't release its shared locks when the statement completes), the second statement will use SERIALIZABLE (which will take and hold range locks)

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT *
FROM dbo.test
WHERE id = 100

-- This will show a shared lock on key 100, the lock is kept during the transaction because repeatable read is used
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

-- Isolation level 3 = repeatable read
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM dbo.test
WHERE id >= 0 and id < 10

-- This will show the previous shared lock on key 100 + additional RangeS-S key locks taken by the serializable statement
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

-- Isolation level 4 = Serializable
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

COMMIT TRANSACTION

Looking at the output of sys.dm_tran_locks and sys.dm_exec_sessions you can see that the two statements issue different locks and use different isolation levels within the same transaction.
Although it's technically possible, I tend to use a single isolation level within a transaction.

When combining SNAPSHOT isolation with pessimistic locking you could get some unexpected results.

Imagine starting a transaction in SNAPSHOT isolation and reading some data.
Next, a second session updates the data you just read.
The first transaction switches to READ COMMITTED and reads the data again, SQL Server will now return the updated values.
Finally, you switch to SNAPSHOT isolation again and read the data. You'll use the  previously created snapshot to read from and the old values will be returned.

-- Note: you have to set your isolation level to snapshot before starting your transaction otherwise you will get an error

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

-- Returns XXXXXXXXXX
SELECT *
FROM dbo.test
WHERE id = 1

-- Run "UPDATE dbo.test SET Field = 'YYYYYYYYYY' WHERE id = 1" in a separate session

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Returns YYYYYYYYYY
SELECT *
FROM dbo.test
WHERE id = 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

-- Returns XXXXXXXXXX
SELECT *
FROM dbo.test
WHERE id = 1

COMMIT


MongoDB on Raspberry Pi --dbpath not setting I installed MongoDB using this tutorial. I have tried to set --dbpath in /etc/mongodb.conf like this:

dbpath=/path/that/iwant

#where to log
logpath=/var/log/mongodb/mongodb.log

logappend=true

When I mongod I get:

ERROR: dbpath (/data/db/) does not exist.

Further more, if I declare mongodb --dbpath /path/that/iwant i get:

exception in initAndListen: 10309 Unable to create/open lock file
errno:13 Permission denied Is a mongod instance already running?

Q: How do I get mongod to reference the proper path in the config file without specifying.

Bonus: Why is permission being denied from mongo. (I have tried sudo chmod -R 775 /path/that/iwant and sudo chown -R mongodb /path/that/iwant)

I am new to mongo and database admin exchange. As a brother from Stack Overflow I am humbled.

Edit: I added the path /data/db to the pie and chown mongodb on it. If I sudo mongod the server starts. If I try to start it with my user it will not start getting an error Unable to create/open lock file which exists.

Edit: Here is what my /etc/init.d/mongd looks like:

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
DAEMON=/usr/bin/mongod
DESC=database

# Default defaults.  Can be overridden by the /etc/default/$NAME
NAME=mongodb
CONF=/etc/mongodb.conf
DATA=/path/that/iwant
LOGDIR=/var/log/mongodb
PIDFILE=/var/run/$NAME.pid
LOGFILE=$LOGDIR/$NAME.log  # Server logfile
ENABLE_MONGODB=yes

...further down

DAEMONUSER=${DAEMONUSER:-mongodb}
DAEMON_OPTS=${DAEMON_OPTS:-"--dbpath $DATA --logpath $LOGFILE run"}
DAEMON_OPTS="$DAEMON_OPTS --config $CONF --rest"

This is what ps aux | grep mongo looks like after i start the server with sudo mongod:

root     10747  0.1  0.3   5124  1612 pts/0    S+   10:41   0:00 sudo mongod
root     10748  1.8  3.8  84896 17416 pts/0    Sl+  10:41   0:00 mongod
1001     10823  0.0  0.1   3544   808 pts/1    S+   10:42   0:00 grep --color=auto mongo

Firstly instead of installing mongodb through apt just get a tar or zip of it.

1)Extract it to your home folder or wherever you would like.

2)Mark the mongod,mongo,mongos executable.

3)make a conf file say mongod.conf and add

 dbpath=conf/data/db
 logpath=conf/log/mongod.log
 logappend=true
 port = 27001
 fork = true

4)Start mongod by

 path/bin/mongod --config path/mongod.conf

"bin" because you will find mongod in bin folder after extract


MySQL Master not sending events to another Master We have Master-Master Replication and it is configured correctly as it transmit data for certain period of time and after that it does not send further events till we stop/start slave on both nodes.

When I run Show Slave Status\G, it displays

Seconds_Behind_Master as 0
Slave_SQL_Running as Yes
Slave_IO_Running as Yes
server_id is also different for both nodes.

My Server Info

MySQL Version is 5.6.23
Windows Server 2012

Any help would be great.

This could be one of two things

Slave I/O Thread is encountering Network Issues
Slave I/O is Playing Possum (pretending to be dead)

Derek Downey and I have written posts about the flaky behavior of the Slave's I/O Thread

Oct 17, 2011 : Master port is disabled from Public IP, MySQL log is not getting refreshed
Sep 30, 2011 : Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL)

According to the Bug Reports on this phenomenon, it was supposedly fixed.

See (Bug #30703 and Bug #51089)

If this bug is happening to you, someone missed a patch making MySQL 5.6.23.

I have three suggestions

Drop back to MySQL 5.6.22 or 5.6.21 or last version of MySQL 5.6 where replication was working
Use MySQL 5.6's heartbeat option when running CHANGE MASTER TO.
Implement semisynchronous replication

As for the Network

Check Network For Dropped Packets
Check the NIC Card
Use separate NIC card for Replication Traffic

Other that what I have just said, sorry I don't have a silver bullet for this one :-(


Five tables or a single table with an identifier column? Do I go with:
Multiple Tables Method
   table5up             table10up                 table15up                 table20up             table25up
[id   -   name] [id   -   name   -   age] [id   -   name   -   age] [id   -   name   -   age] [id   -   name ]
[1    -   jhon] [1    -   mike   -   14 ] [1    -   alex   -   17 ] [1    -   mark   -   22 ] [1    -   felix]

Or
Single Table Method
[id  -  name  -  age   - up ]
[1   -  john  -  NULL  - 5  ]
[1   -  mike  -  14    - 10 ]
[1   -  alex  -  17    - 15 ]
[1   -  mark  -  22    - 20 ]
[1   -  felix -  NULL  - 25 ]

Knowing that I may have NULL values, which scheme, using 5 separate queries for multiple scheme:
SELECT id, name FROM table5up
SELECT id, name, age FROM table10up
SELECT id, name, age FROM table15up
SELECT id, name, age FROM table20up
SELECT id, name FROM table25up

Or one query defined by up column value for single scheme:
SELECT id, name, age FROM tableups WHERE up > 4 AND up < 26

I will not call all tables all the time, only one per time as example for multiple tables:
SELECT id, name FROM table5up;

and for single-table:
SELECT id, name, age FROM tableups WHERE up = 5

Props:
id INT(11), name VARCHAR(48), age TINYINT(3)

Tables with the same structure but different sets of rows are sometimes called orthogonal tables. I don't believe in universal truths in the database world (which means: your use case could have peculiarities that I am not considering), but in general I consider orthogonal tables a bad idea.

From a theoretic perspective I can tell you that I especially don't like using metadata (table names) as data (age range). But what does it mean?

Tables designed in this way prevent you from running easily multi-table queries, like these:

SELECT AVG(age) FROM big_table;
SELECT up, COUNT(*) FROM big_table GROUP BY up;

Of course you will be able to obtain the same information with multiple queries, or with UNION. But it seems to me less clean and, should you ever need to add or remove a table, or change the usage of up (from 20,25,30 to 20,30,40), you'll need to make much more changes.

There are even more obvious case when the "one table" solution is better: maybe one day you'll need a query which returns people whose age is less than 23.

Flexible designs are usually better. For specific use cases, you can always create views, if really needed.


Is it safe to kill ANALYZE TABLE query on a MyISAM table? I have a huge MyISAM table that is blocking my application. I frequently find ANALYZE TABLE queries running on that table. I guess that's because a join is being performed on the table or at least an index is being used, I'm not sure.

So I'm thinking to run a OPTIMIZE TABLE query on the table to speed things up. But there are currently multiple ANALAYZE TABLE queries locking the table, so is it safe to kill such queries ? I couldn't find enough information on mysql's documentation for that matter..

Thanks.

First thing to know is what ANALYZE TABLE does.
For a MyISAM table mydb.mytable, ANALYZE TABLE traverses all the indexes in the mytable.MYI file, reads all BTREE pages, computes statistics, and stores in them in the information_schema, specifically STATISTICS. You can see it when you run one of the following:

SHOW INDEXES FROM mydb.mytable;
SELECT * FROM information_schema.statistics WHERE table_schema='mydb' AND table_name='mytable';

ANALYZE TABLE is a read-only operation and does issue a table-wide read lock.
ANSWER TO YOUR QUESTION
Yes, you can kill ANALYZE TABLE and wait briefly for the read lock to be released. However, there is a significant penalty. Because the index statistics will not be completely computed, the MySQL Query Optimizer will take rather bad guesses at generating EXPLAIN plans of queries against that table.
SUGGESTIONS
SUGGESTION #1 : Delay ANALYZE TABLE
If you want to run OPTIMIZE TABLE mydb.mytable without doing the ANALYZE TABLE operation, do the following:
ALTER TABLE mydb.mytable RENAME mydb.mytable_opt;
ALTER TABLE mydb.mytable_opt ENGINE=MyISAM;
ALTER TABLE mydb.mytable_opt RENAME mydb.mytable;

This will rename the table, perform the defragmentation, and rename it back.
You could do it in one line as well
ALTER TABLE mydb.mytable ENGINE=MyISAM;

If you want to run ANALYZE TABLE later on, set up a cron job to run it in every midnight or perhaps once a week. You will have to do this inevitably if you expect queries against your table to perform well after an optimization.
SUGGESTION #2 : Change the MyISAM table to InnoDB
During the generation of an EXPLAIN plan, InnoDB will perform reads against the index pages of a table by traversing 2-3 levels of nonleaf nodes and literally approximate (or really guess) the cardinality of all column levels. You can tell this is happening to an InnoDB table when you run SHOW INDEXES FROM mydb.mytable; because the cardinality changes with each run.
In light of this, you can simply run
ALTER TABLE mydb.mytable ENGINE=InnoDB;

and that's it. Even if you ran ANALYZE TABLE on an InnoDB table, the statistics can get rather stale. By default, innodb_stats_on_metadata is already disabled in MySQL 5.6. If you have MySQL 5.5 and prior, you can disable the update of the statistics by doing this:
SET GLOBAL innodb_stats_on_metadata = 0;

You will still have to bite the bullet and run ANALYZE TABLE sometime downstream if the InnoDB table experiences high INSERTs, UPDATEs, and DELETEs. Otherwise, the MySQL Query Optimizer will take bad guesses at EXPLAIN plans.


How to convert mysql database charset to utf-8 I have a vb forum with a huge amount of data

I need a way to convert this forum database tables charset with thier data from

latin1_swedish_ci to utf8-general-ci

How to do this ?

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

  If you want to change the table default character set and all
  character columns (CHAR, VARCHAR, TEXT) to a new character set, use a
  statement like this:

  ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Follow the link for more details.

Be sure to do this in a test enviornment first.  You probably wont run into many issues going from latin1 => utf8 but in general you might run into collation "weirdness" when converting charsets.  This is especially true if you have any kind of unique constraints on char-ish columns.

Also keep in mind if you do have any char or varchar columns indexed moving to utf-8 will triple the amount of memory needed


Guidance on strategy for serving files remotely using SQL Server Thanks for the help.  "I" am the IT department for a small business and my ignorance may keep me from asking the right questions so please bear with me.
Currently, we are using MS Access front and back - transitioning to SQL Server Express.  For now, we'll be keeping MS Access as the front-end, keeping in mind that eventually, the data will be consumed in various front-ends such as tablets and cell-phones.  We have all kinds of documents "attached" or associated with various entities such as Projects, Invoices, Suppliers, Employees, etc.  Currently, I simply store the documents on a share and refer to them by building a path using a simple naming convention based upon the entity name and key for each record.  The files are simply opened on demand.  This has worked great in a LAN environment.  Now that we will be accessing the data via SQL Server remotely, I have looked at FILESTREAMs and FILETABLEs and my head is spinning a bit.  I'm not sure if that's the way to go.  Also, FTP and/or VPN.  I'm not sure if a VPN will work for us in the future because I don't know about setting that up for mobile devices or if that's even a viable option.  I have the same concerns using FTP.  We are a small company and I'm attempting to keep expenses low and manage this myself if possible.  Let me know if you need more details.  What are your thoughts?

Putting files inside your database tables is almost always a bad idea. Your database server is not a file server.
Store the files on a file server of some sort, and then store the location of the files along with other metadata you might need in the database. Which you're already doing - so keep doing that, you're just going to change where/how you store the files (maybe).
And please, for the love of all you find precious, do not use FTP. It's broken and insecure. Use SFTP, Azure blob storage, or other secure, sustainable, scalable file storage.


The Cluster service failed to bring clustered service or application 'SQL Server (MSSQLSERVER)' completely online or offline SQL Server service restarted/stopped, which cause the production database down n find this errors in System log.

  ERROR5:The Cluster service failed to bring clustered service or
  application 'SQL Server (MSSQLSERVER)' completely online or offline.
  One or more resources may be in a failed state. This may impact the
  availability of the clustered service or application.

  ERROR4:Cluster resource 'SQL Network Name (tcsdb)' in clustered
  service or application 'SQL Server (MSSQLSERVER)' failed.

  ERROR3:Cluster network name resource 'SQL Network Name (tcsdb)' cannot
  be brought online. The computer object associated with the resource
  could not be updated in domain 'ABC.local' for the following reason:
  Unable to get Computer Object using GUID.

  ERROR2:Cluster resource 'SQL Network Name (tcsdb)' in clustered
  service or application 'SQL Server (MSSQLSERVER)' failed.

  ERROR1:Cluster network name resource 'SQL Network Name (tcsdb)' cannot
  be brought online. The computer object associated with the resource
  could not be updated in domain 'ABC.local' for the following reason:
  Unable to get Computer Object using GUID. how we can fix this issue

This means the cluster network name (tcsdb) can't be registered with Active Directory at the moment. It could be that your newly primary node can't see a domain controller, or can't see a writeable domain controller, may have a network interface down, may be having routing problems, etc.


MySQL - Get id of the department? I need to find the department that has the oldest employee. My plan is to merge everything into one big table and try to sort the employees by age, then find the department that has the oldest employee, and find the manager for that department. Currently this is my query:

SELECT *
  FROM employees emp
  JOIN dept_emp de       ON emp.emp_no=de.emp_no
  JOIN departments dep   ON de.dept_no=dep.dept_no
  JOIN dept_manager depm ON dep.dept_no=depm.dept_no
  JOIN employees emp2    ON depm.emp_no=emp2.emp_no
-- some part of query is lost here --
           DESC
 LIMIT 0, 1

;

Unfortunately, when I run that, it's saying that column 'birth_date' is ambiguous to sort - which makes sense bc I'll have 2 columns with the same date.

I've rid of the redundant second subselect so now it is the single one that fetch employee(s) having earliest date of birth (highest age), their IDs, names, IDs of their managers and names of managers.

SELECT ds.dept_no
     , ol.emp_no     AS oldest_emp_no
     , ol.birth_date AS oldest_bd
     , e1.first_name AS o_first_name
     , e1.last_name  AS o_last_name
     , dm.emp_no     AS req_manager_no
     , e2.first_name AS m_first_name
     , e2.last_name  AS m_last_name

  FROM departments  AS ds
  JOIN dept_emp     AS de ON de.dept_no = ds.dept_no
  JOIN employees    AS e1 ON e1.emp_no = de.emp_no
  JOIN ( SELECT MIN(birth_date) AS birth_date FROM employees )
         AS ol ON ol.birth_date = e1.birth_date
  JOIN dept_manager AS dm ON dm.dept_no = ds.dept_no
  JOIN employees    AS e2 ON e2.emp_no = dm.emp_no
;


Generate insert statements for table AND related data I'm looking to move data from tables and related tables between 2 databases. The trouble is the primary keys are out of sync.

Is anyone aware of a tool which can generate insert statements for a given table and it's related data (perhaps walking foreign keys)?

I've been looking at SSMS toolpack, RedGate's tools, Database project data compare, various open source data diff tools, etc etc, but not found anything.

I'm aware of being able to walk foreign keys, so perhaps some amazingly clever SQL script might be able to do it?

Seeing as I've looked at so many tools, I'm guessing it's probably blooming difficult / impossible to do it fully automated. But perhaps a technique that needs some manual intervention as well?

Or perhaps anyone has hints on how they handle this sort of task. I don't really fancy writing lots of manual sql to handle the insert - as it's bound to be error prone.

What I'm really hoping for is a way of generating SQL to tie up the PK and FK's whilst doing the inserts in the new database.

Say for example we had:

+--------------+--------------+
|     personid |     name     |
+--------------+--------------+
|            1 |         bob  |
|            2 |         fred |
+--------------+--------------+

+----------+----------------+----------+
| animalid |     animal     | PersonID |
+----------+----------------+----------+
|        1 |         cat    |        1 |
|        2 |         dog    |        1 |
|        3 |         lizard |        2 |
+----------+----------------+----------+

It would be awesome to copy bob to the new database and bob's cat and dog. Then fred and fred's lizard.

The SSMS Tools Pack allows you to generate insert scripts for a particular table.  On the Features page, it's the "Generate Insert statements from resultsets, tables or databases."  That won't get all child tables automatically, however.

In an older Stack Overflow post, they mention a few methods for generating inserts.  The first method (using SSMS's Generate Scripts functionality) will work if you have SQL 2005 or better.  The second method, a downloaded stored procedure, could do the job if you're using 2000.

Assuming you have 2005 or later, right-click on the database and go to Tasks --> Generate Scripts.  From there, you can select specific database objects.  You'll select all of the necessary tables (so this is the manual process).

To get the list of tables, you are correct about walking the foreign key list.  That's at sys.foreign_keys.  The following script will get a list of all foreign key dependencies from a central point.  Note that this does not get the child records, but only the "parent" records.  For example, suppose T3 has a foreign key (T2ID) to T2, and T2 has a foreign key (T1ID) to T1.  If we run this script on T3, it will return three tables:  T3, T2, and T1.  If we run it on T2, it will return two tables:  T2 and T1.  If we run it on T1, it will return only T1.

declare @SchemaName sysname = 'dbo';
declare @TableName sysname = 't2';

with foreignkeys as
(
    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName,
        convert(varchar(8000), tbls.name + '.' + tbl.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
    where
        tbls.name = @SchemaName
        and tbl.name = @TableName

    UNION ALL

    select
        ref.object_id as ObjectID,
        refs.name as SchemaName,
        ref.name as TableName,
        convert(varchar(8000), fks.[Path] + '/' + refs.name + '.' + ref.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables ref on fk.referenced_object_id = ref.object_id
        inner join sys.schemas refs on ref.schema_id = refs.schema_id
        inner join foreignkeys fks on fks.objectid = fk.parent_object_id
    where
        fks.objectid <> ref.object_id
        AND fks.[Path] NOT LIKE '%' + refs.name + '.' + ref.name + '%'
)
select
    SchemaName + '.' + TableName
from
    foreignkeys

UNION

select @SchemaName + '.' + @TableName;

If you want to get the child tables as well (i.e., getting the entire foreign key dependency chain), another CTE will do it:

declare @SchemaName sysname = 'dbo';
declare @TableName sysname = 't2';

with foreignkeys as
(
    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName,
        convert(varchar(8000), tbls.name + '.' + tbl.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
    where
        tbls.name = @SchemaName
        and tbl.name = @TableName

    UNION ALL

    select
        ref.object_id as ObjectID,
        refs.name as SchemaName,
        ref.name as TableName,
        convert(varchar(8000), fks.[Path] + '/' + refs.name + '.' + ref.name) as [Path]
    from
        sys.foreign_keys fk
        inner join sys.tables ref on fk.referenced_object_id = ref.object_id
        inner join sys.schemas refs on ref.schema_id = refs.schema_id
        inner join foreignkeys fks on fks.objectid = fk.parent_object_id
    where
        fks.objectid <> ref.object_id
        AND fks.[Path] NOT LIKE '%' + refs.name + '.' + ref.name + '%'
),
parentsandchildren as
(
    select
        ObjectID,
        SchemaName,
        TableName
    from
        foreignkeys

    UNION ALL

    select
        tbl.object_id as ObjectID,
        tbls.name as SchemaName,
        tbl.name as TableName
    from
        sys.foreign_keys fk
        inner join sys.tables tbl on fk.parent_object_id = tbl.object_id
        inner join sys.schemas tbls on tbl.schema_id = tbls.schema_id
        inner join parentsandchildren fks on fks.objectid = fk.referenced_object_id
    where
        not exists (select * from foreignkeys fk where fk.objectid = tbl.object_id)

)
select
    SchemaName + '.' + TableName
from
    parentsandchildren

UNION

select @SchemaName + '.' + @TableName;

If you run this script on T1, T2, or T3, it will return the list of all three tables (because it traverses the chain in both directions).  Both of these scripts are also smart enough to not get caught up in self-joins and loops, should your table structure have either of those.

Running one of these two scripts would get you the list of tables that you could then select in the Generate Scripts menu.  Just make sure to set the Type of data to script option to Schema and Data, or else you'll only get the table structure.

EDIT

I don't know of any tool which will script out individual records the way you're asking for them.  Even writing this would be difficult at first glance, because you would need to know which records you need to insert, all records which depend upon that, all records which depend upon those, etc.  That's a fairly straightforward recursion problem by itself.  But then you'd need to maintain a structure telling when each row gets inserted so you don't double-insert records.  For an arbitrary table structure (especially one with loops), this becomes a challenge.

But based strictly on your example, I'm not sure if there's actually any value to doing what you want regarding specialized insertion.  As far as SQL Server is concerned, there's no difference between the two scenarios:

Scenario A:

Insert Bob's Person record
Insert Animals related to Bob
Insert Fred's Person record
Insert Animals related to Fred

Scenario B:

Insert Bob and Fred's Person records
Insert all Animal records related to Bob and Fred

After the insertions are complete, all select statements will return the same results regardless of which path you choose.  Even if you have a trigger or some other complex logic that depends upon inserting in a particular order, I'd have a hard time seeing a case in which A works but B doesn't.  I could see this making sense if we were discussing a 1980s RDBMS and you used interleaving parent and child records in a file to improve performance, but that's not really applicable here.

What you might look at instead is getting the order of insertions correct:  insert into reference data tables first and go on down the chain.  In your example, that's inserting into Person and then into Animal.  That way, you won't have any foreign key constraint violations and all records would be up to date.  Or, using a tool like Atlantis Interactive's Data Inspector, you can have it disable foreign keys during the load and then re-enable them afterward.


Count childs of parent with sql or save in column? In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this:

WITH RECURSIVE cte (id, message, author, path, parent_id, depth)  AS (
    SELECT  id,
        message,
        author,
        array[id] AS path,
        parent_id,
        1 AS depth
    FROM comments
    WHERE parent_id IS NULL

    UNION ALL

    SELECT comments.id,
        comments.message,
        comments.author,
        cte.path || comments.id,
        comments.parent_id,
        cte.depth + 1 AS depth
    FROM comments
    JOIN cte
        ON comments.parent_id = cte.id
)
SELECT id, message, author, path, depth
FROM cte
ORDER BY path;`

Is it better to count the child, or have a column that keeps track when a row is insert an increment and pull that column with the query?

If counting from sql, how would I implement that?

I assume you want to count the number of immediate childrent for each node in the tree. One easy solution is to do that with a sub-select:

WITH RECURSIVE cte (id, message, author, path, parent_id, depth)  AS (
    SELECT  id,
        message,
        author,
        array[id] AS path,
        parent_id,
        1 AS depth
    FROM comments
    WHERE parent_id IS NULL

    UNION ALL

    SELECT comments.id,
        comments.message,
        comments.author,
        cte.path || comments.id,
        comments.parent_id,
        cte.depth + 1 AS depth
    FROM comments
    JOIN cte
        ON comments.parent_id = cte.id
)
SELECT id, message, author, path, depth
     , (select count(1) from cte as cte2
        where cte2.parent_id = cte.id) as num_children
FROM cte
ORDER BY path;

An alternative is to use comments instead of cte:

     , (select count(1) from comments as c
        where c.parent_id = cte.id) as num_children

Personally I would stay away from storing this number.

If all you are interested in is the number of children for all nodes there is no need to recursively traverse the tree, a self join or a sub-select is sufficient:

select id, message, author
     , (select count(1) from comments as c2
        where c2.parent_id = c1.id) as num_children
from comments as c1

The transformation to use a join instead is straight forward.

If you are interested in alternatives to recursively traverse the tree there are options such as NESTED SETS. You can google for the details, but the idea is to store an upper bound and a lower bound for each node. A node is an ancestor to another node if it subsumes that node. Main drawback with this approach is that it is difficult to maintain the bounds.

Yet another approach is to store a MATERIALIZED PATH for each node (much like your array).

None of these have a parent_id attribute. A third approach that do use the parent_id attribute is to have a separate ancestor table. This is known as the TRANSITIVE CLOSURE of parent_id. It is suitible to maintain this relation with triggers.

Finally, all of these models (including the recursive one you mention) have benefits as well as drawbacks, choose the one that fits your scenario best.


Optimizing queries for 25+ million rows I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). This took a lot of time (around 2 minutes).

There are duplicates in data rows, and all indexes are NON-CLUSTERED. I am only interested in 4 columns for my criteria and the result should output the count only, for all queries.

columns needed: TABLE, FIELD, AFTER, DATE, and there is an index on each of DATE and TABLE.

After creating a temp table with only the fields I need, it went down to a 1:40 minutes, which is still very bad.

CREATE TABLE #TEMP
(
    TABLE VARCHAR(30) NULL,
    FIELD VARCHAR(30) NULL,
    AFTER VARCHAR(1000) NULL,
    DATE DATETIME,
    SORT_ID INT IDENTITY(1,1)
)
CREATE CLUSTERED INDEX IX_ADT ON #TEMP(SORT_ID)

INSERT INTO #TEMP (TABLE, FIELD, AFTER, DATE)
    SELECT TABLE, FIELD, AFTER, DATE
    FROM mytbl WITH (NOLOCK)
    WHERE TABLE = 'OTB' AND
    FIELD = 'STATUS'

Runnig this -> (216598 row(s) affected)

Since not all queries rely on date range, I didn't include it in the query. The problem is that it's taking well above 1 minute to insert only. The above insert took 1:19 mins

I want to run something like this for several queries:

SELECT COUNT(*) AS COUNT
FROM #TEMP
WHERE AFTER = 'R' AND
DATE >= '2014-01-01' AND
DATE <= '2015-01-01'

It's a problem with the insert more than that of the selection, but the temp has way less rows than the original table which could be better than going through the table several times.

How can I optimize this?

EDIT

I have removed the sort ID, I thought the problem was with mainly the select and not insert. It was a guess.

I can't create a unique on any index since there is no unique field or rows.

I am using SQL Server 2012.

Table Info: It's a heap and has the following space usage:

name    rows        reserved    data        index_size  unused
mytbl   24869658    9204568 KB  3017952 KB  5816232 KB  370384 KB

The question is mainly about how to optimize the select statement:

SELECT [TABLE], [FIELD], [AFTER], [DATE]
FROM mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB' AND
[FIELD] = 'STATUS'

Removing the redundant projections and adding the presumed dbo schema:

SELECT [AFTER], [DATE]
FROM dbo.mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB'
AND FIELD = 'STATUS';

Without an index like ([TABLE],[FIELD]) INCLUDE ([AFTER],[DATE]) SQL Server has two main options:

Scan the heap entirely (3GB+); or
Locate rows matching [TABLE] = 'OTB' and [FIELD] = 'STATUS' (using IDX6), then perform a heap (RID) lookup per row to retrieve the [AFTER] and [DATE] columns.

Whether the optimizer chooses a heap scan or index seek with RID lookup depends on the estimated selectivity of the [TABLE] = 'OTB' and [FIELD] = 'STATUS'predicates. Check to see if the estimated number of rows from the seek matches reality. If not, update your statistics. Test the query with an table hint forcing the use of the index, if that condition is reasonably selective. If the optimizer is currently choosing the index seek, test performance with an INDEX(0) or FORCESCAN hint to scan the heap.

Beyond that, you could look to improve the scan of the heap a little by removing some of the unused space (370MB). In SQL Server 2008 this can be done by rebuilding the heap. Unused space in heaps often results from deletes performed without a table lock being taken (without a table lock, empty pages are not deallocated from a heap). Tables that experience frequent deletions are often better stored as a clustered table for this reason.

The performance of the heap scan depends on how much of the table is stored in memory, how much must be read from disk, how full the pages are, the speed of the persistent storage, whether the scan is I/O or CPU bound (parallelism can help).

If performance is still unacceptable after you have investigated all of the above, try to make the case for a new index. If available on your version of SQL Server, a possible filtered index for the given query would be:

CREATE INDEX index_name
ON dbo.mytbl ([DATE],[AFTER])
WHERE [TABLE] = 'OTB'
AND [FIELD] = 'STATUS';

Also consider index compression, if that is available and beneficial. Without a new index of some kind, there's relatively little you can do to improve the performance of the given query.


Update old database with SQL dump I had to recover a database with a some months old full backup. I have however in my possession a most recent but unfortunately incomplete SQL dump for this same database done with mysqldump.

Is there any tool out there that will allow me to compare my SQL dump with the live database and import any new data?

import your dump with INSERT IGNORE OPTION (skip duplicates)

on your dump file (assuming you're using linux OS)

cat /path/dir/to/dumpfile.sql |
  sed s/"^INSERT"/"INSERT IGNORE"/g > /path/dir/to/newdump.sql

then issue import

mysql -u username -p databasename < /path/dir/to/newdump.sql


Can SQLServer implicitly combine clustered and nonclustered index for queries that hit the NCI with a WHERE and the CI with an ORDERBY? I recently asked a question about the best indexing strategy for a table that must record events, in order, against a GUID. The GUID is a nuisance but it's unavoidable because it's an FK to elsewhere.
In terms of the queries I want to do they are essentially of the form SELECT TOP N SomeColumn FROM t WHERE GuidColumn = 'someguid' ORDER BY AutoIncInt DESC
I've done some experimentation and a good tradeoff approach seems to be:

Create the AutoIncInt column on the table as the unique clustering key
Create a nonunique nonclustered index on GuidColumn, and INCLUDE SomeColumn

At first I had the nonclustering index on SomeGuid ASC, AutoIncInt DESC, thinking it would help the ORDER BY AutoIncInt DESC part of the query but the plan remained the same when I removed it - no sort operation was needed and the query just hit the NC index, did a top and a select; it seems that SQLServer is somehow smart enough to know, even when AutoIncInt is not cited in the nonclustering index at all, there's a way to easily get the data out in order / easily calculate which last N rows are needed for the TOP
How does this mechanism work in practice? Does the nonclustering index have an implicit order thanks to referring to the clustering index, such that SQLS can quickly and easily deduce which N index nodes I want the SomeColumn from?

Each leaf node row in a SQL Server b-tree index uniquely identifies a single row in the table by a unique key. In a non-clustered index, this unique key consists of the declared key columns plus the row locator. The row locator is the clustered index key (including uniqueifier for when the CI key value is not unique). With a heap (no clustered index on the table), the row locator is the physical location of the row (file,page,slot).
The DBCC PAGE output of this demo script shows both GuidColumn and AutoIncInt as index keys even though AutoIncInt was not specified:.
DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t (
      AutoIncInt int IDENTITY NOT NULL CONSTRAINT pk_t PRIMARY KEY CLUSTERED
    , GuidColumn uniqueidentifier NOT NULL
    , SomeColumn char(5000) NOT NULL
)
CREATE INDEX idx ON t(GuidColumn) INCLUDE (SomeColumn);
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','3')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','1')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','2')

DECLARE @dbid int, @fileid int, @pageid int;
SELECT TOP (1)
      @dbid = DB_ID()
    , @fileid = allocated_page_file_id
    , @pageid = allocated_page_page_id
FROM sys.dm_db_database_page_allocations( DB_ID(), OBJECT_ID(N'dbo.t','U'), 2, 1, 'DETAILED')
WHERE
    page_type_DESC = 'INDEX_PAGE'
ORDER BY
      allocated_page_file_id
    , allocated_page_page_id;

DBCC PAGE(@dbid,@fileid,@pageid,3);
GO

Results:
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
| FileId | PageId | Row | Level |           GuidColumn (key)           | AutoIncInt (key) | SomeColumn |  KeyHashValue  | Row Size |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
|      1 |    424 |   0 |     0 | 00000000-0000-0000-0000-000000000001 |                1 |          3 | (63f9557c63d5) |     5038 |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+

This index architecture has positive performance implications for your query because the AutoIncInt column is not only implicitly included in the non-clustered index as the row locator, it is also part of the unique key and thus ordered. This allows the plan for your query to use an index seek for the GuidColumn WHERE predicate plus a backwards scan for the ORDER BY...DESC clause.
Below is an excerpt from the actual query plan shows a backwards scan of the index. The resultant non-clustered index will be the same regardless of whether AutoIncInt is explicitly specified as a key column.
<IndexScan Ordered="true" ScanDirection="BACKWARD"