Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

Why?
For a LIMIT 1, Postgres may estimate it to be faster to traverse the index supporting the ORDER BY and just keep filtering until the first row is found. This is fast as long as more than a few rows qualify and one of those pops up early according to ORDER BY. But it is (very) slow if no qualifying row pops up early, or even a worst case scenario if no row ends up qualifying at all. Similar for any small LIMIT.
Postgres collects statistics about the most common values (MCV list), but not for the least common ones - for obvious reasons, that would be far too many to be useful. And it has no statistics for correlations between columns by default. (While that can be created manually it won't fit your use case anyway, as ID numbers are typically uncorrelated.)
So Postgres has to base its decision on generic estimates. It's very hard to identify the sweet spot where to switch from one index to the other. This gets harder, yet, for a predicate like image_id IN (123, ... ) with many items, and most are typically rare or very rare or even non-existent. But if you put enough numbers into the list, Postgres will eventually expect that traversing the other index will find the first hit faster.
Solutions?
You may be able to improve the situation somewhat with a larger statistics target:
ALTER TABLE mcqueen_base_imagemeta2 ALTER image_id SET STATISTICS 2000;

That (among other things) increases the size of the MCV list for the column and help identify more (less) common values. But it's not a general solution for the problem, and makes ANALYZE and query planning a bit more expensive. Related:

Check statistics targets in PostgreSQL

Upgrading to the latest version (soon to be Postgres 12) also helps as general performance got better and the planner smarter.
There are various techniques for a workaround, depending on cardinalities, value frequencies, access patterns, ... Completely disabling the ORDER BY index like Laurenz demonstrated is one radical workaround - which can backfire for long lists or very common image_id, where the ORDER BY index would, in fact, be much faster.
Related:

Can spatial index help a "range - order by - limit" query

Workaround for your case
Should work well for the given numbers: 5 billion rows, around 20 image_id in the filter list, small LIMIT. Most efficient for LIMIT 1 and a short list, but good for any small LIMIT and manageable list size:
SELECT m.*
FROM   unnest( '{123, ...}'::bigint[]) i(image_id)
CROSS  JOIN LATERAL (
   SELECT m.id
   FROM   mcqueen_base_imagemeta2 m
   WHERE  m.image_id = i.image_id
   ORDER  BY m.id DESC
   LIMIT  1  -- or N
   ) m
ORDER  BY id DESC
LIMIT  1;  -- or N

Provide your list as array and unnest(). Or use a VALUES expression. Related:

Optimizing a Postgres query with a large IN

It's essential to support this with a multicolumn index on (image_id, id DESC)!
You might then delete the existing index mcqueen_base_imagemeta2_image_id_616fe89c on just (image_id). See:

Is a composite index also good for queries on the first field?

This should result in one very fast index(-only) scan per image_id. And a final, (very) cheap sort step.
Fetching N rows for each image_id guarantees that we have all rows needed in the outer query. If you have meta-knowledge that only fewer rows per single image_id can be in the result, you can decrease the nested LIMIT accordingly.
Aside

(a common pattern in Django pagination)

Pagination with LIMIT and OFFSET? OK for the first page, but after that it's just a bad idea.

Efficient pagination for big tables
What is the recommended way to join junction tables for efficient ordering/pagination?


MySQL 5.5 - view all queries executed on certain table

The "general log", if turned on, records all queries touching all tables.
The "slow log", if turned on with long_query_time=0, records all queries, plus performance-related info, for all tables.

In either case, you would need to filter down to the desired table.  This cannot be done with a simple grep since the log formats involve multiple lines of text.  Still, it is better than nothing.

(There is no feature of MySQL to monitor a single table or database or user.  This Answer applies to all(?) versions of MySQL.)


How to initialize a PostGIS database in PostgreSQL single-user mode?

Someone previously ask a very similar question on the lists. Anyway, as told in the thread, reclassarg is a composite type.

  RhodiumToad: Guest78493: in single-user, doing CREATE TYPE ... AS ...;
  doesn't create the array type

  RhodiumToad: Guest78493: it creates only the non-array one, but I have no idea yet why

  RhodiumToad: Guest78493: so for now, any extension with a CREATE TYPE AS won't load correctly in single-user

  RhodiumToad: Guest78493: arguably if it doesn't work it should error rather than quietly getting it wrong, so I think you're justified in calling it a postgres bug

Further, from Tom Lane himself!

    [ Can't create postgis extension in single-user mode ]

  Why in the world would you think that's a good thing to do?

  Single-user mode is a barely-documented disaster recovery aid.
  It's not meant for routine activity.  There are a whole lot
  of behaviors you want that are turned off in single-user mode.

  The specific reason why this doesn't work is this bit in
  heap_create_with_catalog:

/*
 * Decide whether to create an array type over the relation's rowtype. We
 * do not create any array types for system catalogs (ie, those made
 * during initdb). We do not create them where the use of a relation as
 * such is an implementation detail: toast tables, sequences and indexes.
 */
if (IsUnderPostmaster && (relkind == RELKIND_RELATION ||
                          relkind == RELKIND_VIEW ||
                          relkind == RELKIND_MATVIEW ||
                          relkind == RELKIND_FOREIGN_TABLE ||
                          relkind == RELKIND_COMPOSITE_TYPE))
    new_array_oid = AssignTypeArrayOid();

  We could possibly develop some other mechanism for detecting whether
  we're within the initdb sequence, but I can't get very excited about
  treating this as a bug.  Single-user mode hasn't been considered a
  standard user environment since maybe the early 90s.

If it was you, please update us in the future and self-answer your questions when you get word from the horse's mouth.

UPDATE.

  So maybe there is a way to reformulate the PostGIS creation script so it achieves the same thing without requiring those language features, i.e. to make it "more portable".

No, there is not. ST_Reclass requires it. The method you're using is not supported. Initializing a database is currently viewed as a different thing than CREATE EXTENSION on one. I don't think PostgreSQL intends -single to be used in that way, and certainly PostGIS doesn't intent to rewrite working code so it functions in PostgreSQL's unsupported modes, with outdated single-user contexts.


Is it possible to migrate a postgresql database without downtime?

If you need to migrate to the same version of postgres, then you can setup slave of your db on new server with streaming replication and then just promote it to master. There will be no downtime while doing pg_basebackup, maybe just some load on disks and network caused by your whole cluster moving to new server.


I see what your problem is you are duplicating the interactions because you are basically just trying to join those two tables together in your query. What you want to do is a correlated subquery to get the answer of how many interactions each mentor has had.

Something like this.

select a.mentor_id, count(distinct(a.mentee_id)) as UniqueMenteeCt,b.InterCt
from mentee_table a inner join
(select a.mentor_id, count(a.mentee_id) as InterCt
 from INTER a
 group by a.mentor_id
)b on a.mentor_id = b.mentor_id
group by a.mentor_id,InterCt


db connection uses an anonymous logon instead of the domain user

Reading the manual I found something relevant:

  For example IIS 7, in its default configuration, has anonymous
  authentication enabled with built-in user account IUSR used as a
  default identity. This means that in order for IIS to execute PHP
  scripts, it is necessary to grant IUSR account read permission on
  those scripts. If PHP applications need to perform write operations on
  certain files or write files into some folders then IUSR account
  should have write permission to those.

And iis.net has the following relevant information:

  This built-in account does not need a password and will be the default
  identity that is used when anonymous authentication is enabled. If you
  look in the applicationHost.config file you will see the following
  definition:

  <anonymousAuthentication enabled="true" userName="IUSR" defaultLogonDomain="" />

  This tells IIS to use the new built-in
  account for all anonymous authentication requests.

Hope it helps


LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

If your table is a MyISAM engine you can try to specify CONCURRENT in your statement.

Load data ref.

Concurrent insert ref.

  With LOAD DATA INFILE, if you specify CONCURRENT with a MyISAM table
  that satisfies the condition for concurrent inserts (that is, it
  contains no free blocks in the middle), other sessions can retrieve
  data from the table while LOAD DATA is executing. Use of the
  CONCURRENT option affects the performance of LOAD DATA a bit, even if
  no other session is using the table at the same time.

UPDATE FOR FREE BLOCK EXPLAIN

When you delete a row a free block can occur, if you have free blocks in the "middle" of your insert, for example if you replace deleted rows(index and/or primary key) with your load data, concurrent insert do not work, when free blocks are filled in future insert become concurrent again.


Benefit to additional index with key column moved to includes

IMO, first we have to understand

How optimizer work ?
Optimizer will choose choose which index and WHY ?

As we know Optimizer make Cost base query plan. if query plan is not Trivial or Simple then it must be Cost based Optimization.

Example,

CREATE TABLE Test (
   id int identity(1,1) primary key
    A VARCHAR(50)
    ,B VARCHAR(50)
    ,C VARCHAR(50)
    ,D VARCHAR(100)
    ,E VARCHAR(100)
    )

INSERT INTO Test
WITH (TABLOCK) (
        A
        ,B
        ,C
        ,D
        ,E
        )
SELECT CONCAT (
        REPLICATE('A', 30)
        ,(rn / 5)
        )
    ,CONCAT (
        REPLICATE('B', 30)
        ,(rn / 5)
        )
    ,CONCAT (
        REPLICATE('C', 30)
        ,(rn / 5)
        )
    ,REPLICATE('D', 100)
    ,REPLICATE('E', 100)
FROM (
    SELECT TOP (1000000) ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) rn
    FROM sys.objects A
        ,sys.objects B
        ,sys.objects C
    ) t4

CREATE NONCLUSTERED INDEX nc1_ABC ON Test (
    A
    ,B
    ,C
    ) include (
    D
    ,E
    )

CREATE NONCLUSTERED INDEX nc2_AB ON Test (
    A
    ,B
    ) include (
    C
    ,D
    ,E
    )

In my example Index size of nc1_ABC will be more than nc2_AB. WHY ?

So if my query is,

SELECT A
        ,B
        ,C
        ,D
        ,E
    FROM test
    WHERE A = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA14372'
        AND B = 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB14372'
        and c='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC14372'

Optimizer will choose nc1_ABC index.It will not use nc2_AB.

But if my query is

SELECT A
        ,B
        ,C
        ,D
        ,E
    FROM test
    WHERE A = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA14372'
        AND B = 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB14372'
        --and c='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC14372'

Optimizer will choose nc2_AB. WHY ?

Because optimizer will compare the price of choosing nc2_AB and nc1_ABC.Optmizer will see that it has to find all record based on filter A and B, that is can do with both index  but index size of nc2_AB is less than nc1_ABC.So it will be use.

  Is there any performance reason to have an additional index Bar with:

So it depend upon example and how frequently the query is use and how important it is to get those record quickly.

If all those are correct then additional index Bar is require,
else index Foo is good enough for both query.

It really depend upon example and situation.


how to remove all permissions in a database?

This should help drop all users after restore.
declare @sql nvarchar(max)

set @sql = ''

select @sql = @sql + '
print ''Dropping ' + name + '''drop user ' + name + ''
from dbo.sysusers
where name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'public') and LEFT(name, 3) <> 'db_'
order by name

--print @sql
execute (@sql)


In SQL Server, can user X be granted ability to change permissions for all users except Y?

You can grant the permission with the ability to grant to others but you would have to put something else in place to restrict that user from granting it to a specific user. It is grantable or not.


Moving from SQL 2005 [SQL_Latin1_General_CP1_CI_AS] to 2008 - will I lose any features by using 'backwards compatibility'

First of all, apologies for such a long answer, as I feel that still there is a lot of confusion when people talk about terms like collation, sort order, code page, etc.

From BOL :

  Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data that you will be working with.

This means that Collation is very important as it specifies rules on how character strings of the data are sorted and compared.

Note: More info on COLLATIONPROPERTY

Now Lets first understand the differences ......

Running below T-SQL :

SELECT *
FROM::fn_helpcollations()
WHERE NAME IN (
        'SQL_Latin1_General_CP1_CI_AS'
        ,'Latin1_General_CI_AS'
        )
GO

SELECT 'SQL_Latin1_General_CP1_CI_AS' AS 'Collation'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage') AS 'CodePage'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'LCID') AS 'LCID'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'Version') AS 'Version'

UNION ALL

SELECT 'Latin1_General_CI_AS' AS 'Collation'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage') AS 'CodePage'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'LCID') AS 'LCID'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'Version') AS 'Version'
GO

The results would be :

Looking at above results, the only difference is the Sort Order between the 2 collations.But that is not true, which you can see why as below :

Test 1 :

--Clean up previous query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_SQL_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_SQL_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('Kin_Tester1')

-- Create second table using collation SQL_Latin1_General_CP1_CI_AS
CREATE TABLE Table_SQL_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

-- add some data to it
INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('Kin_Tester1')

--Now try to join both tables
SELECT *
FROM Table_Latin1_General_CI_AS LG
INNER JOIN Table_SQL_Latin1_General_CP1_CI_AS SLG ON LG.Comments = SLG.Comments
GO

Results of Test 1:

Msg 468, Level 16, State 9, Line 35
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

From above results we can see that the we cannot directly compare values on columns with different collations, you have to use COLLATE to compare the column values.

TEST 2 :

The major difference is performance, as Erland Sommarskog points out at this discussion on msdn.

--Clean up previous query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_SQL_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_SQL_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_tester1')

-- Create second table using collation SQL_Latin1_General_CP1_CI_AS
CREATE TABLE Table_SQL_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

-- add some data to it
INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_tester1')

--- Create Indexes on both tables

CREATE INDEX IX_LG_Comments ON  Table_Latin1_General_CI_AS(Comments)
go
CREATE INDEX IX_SLG_Comments ON  Table_SQL_Latin1_General_CP1_CI_AS(Comments)

--- Run the queries

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments = 'kin_test1'
GO

--- This will have IMPLICIT Conversion

--- Run the queries

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_SQL_Latin1_General_CP1_CI_AS WHERE Comments = 'kin_test1'
GO

--- This will NOT have IMPLICIT Conversion

The reason for Implicit conversion is because, I have my database & Server collation both as SQL_Latin1_General_CP1_CI_AS and the table Table_Latin1_General_CI_AS has column Comments defined as VARCHAR(50) with COLLATE Latin1_General_CI_AS, so during the lookup SQL Server has to do an IMPLICIT conversion.

Test 3:

With the same set up, now we will compare the varchar columns with nvarchar values to see the changes in the execution plans.

-- run the query

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments =  (SELECT N'kin_test1' COLLATE Latin1_General_CI_AS)
GO

-- run the query

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_SQL_Latin1_General_CP1_CI_AS WHERE Comments = N'kin_test1'
GO

Note that the first query is able to do Index seek but has to do Implicit conversion while the second one does an Index scan which prove to be inefficient in terms of performance when it will scan large tables.

Conclusion :

All of the above tests shows that having right collation is very important for your database server instance.
SQL_Latin1_General_CP1_CI_AS is a SQL collation with the rules that allow you to sort data for unicode and non-unicode are different.
SQL collation wont be able to use Index when comparing unicode and non-unicode data as seen in above tests that when comparing nvarchar data to varchar data, it does Index scan and not seek.
Latin1_General_CI_AS is a Windows collation with the rules that allow you to sort data for unicode and non-unicode are same.
Windows collation can still use Index (Index seek in above example) when comparing unicode and non-unicode data but you see a slight performance penalty.
Highly recommend to read Erland Sommarskog answer + the connect items that he has pointed to.

  This will allow me to not have problems with #temp tables, but are there pitfalls?

See my answer above.

  Would I lose any functionality or features of any kind by not using a "current" collation of SQL 2008?

It all depends on what functionality/features you are referring to. Collation is storing and sorting of data.

  What about when we move (e.g. in 2 years ) from 2008 to SQL 2012? Will I have problems then? Would I at some point be forced to go to Latin1_General_CI_AS?

Cant vouch ! As things might change in and its always good to be inline with Microsoft's suggestion + you need to understand your data and the pitfalls that I mentioned above. Also refer to this and this connect items.

  I read that some DBA's script complete the rows of complete databases, and then run the insert script into the database with the new collation - I'm very scared and wary of this - would you recommend doing this?

When you want to change collation, then such scripts are useful. I have found myself changing collation of databases to match server collation many times and I have some scripts that does it pretty neat. Let me know if you need it.

References :

Storage and Performance Effects of Unicode
Working with Collations
Collation and Unicode Support
Code Page Architecture


Pg install: "The database cluster initialisation failed"

It appears that the root cause of this issue may have been that @Celeritas's computer had an incorrect value for the COMSPEC environment variable. It had a trailing semicolon, so instead of the normal:

C:\Windows\system32\cmd.exe

it was:

C:\Windows\system32\cmd.exe;

This one-character difference is enough. The above isn't a valid command prompt path, so popen() calls were failing. Unfortunately, instead of something useful like:

'"C:\Windows\system32\cmd.exe;"' is not recognized as an internal or external command, operable program or batch file

it instead reports the delightfully useful error No error:

initdb: could not execute command ""C:/Program Files/PostgreSQL/9.2/bin/postgres.exe" --boot -x1 -F ": No error

See related:

http://forums.enterprisedb.com/posts/list/2125.page
http://postgresql.1045698.n5.nabble.com/initdb-failure-td2083455.html

I've reported this to the installer team and written a wiki entry to describe it. See blog post.

The issue was eventually resolved by:

Editing the COMSPEC environment variable
Uninstalling PostgreSQL
Rebooting
Reinstalling PostgreSQL

though there was a lot more before that which shouldn't actually be necessary to resolve this, including doing a total manual clean uninstall of PostgreSQL by hand.

Now I just want to find the people who wrote the program that modified this environment variable.


Cannot execute as the database principal because the principal "dbo" does not exist

You need to give the database a valid owner. Use the ALTER AUTHORIZATION command to do this:

ALTER AUTHORIZATION
ON DATABASE::[DatabaseName]
TO [A Suitable Login];

Related Knowledge Base article

Note the sp_changedbowner system stored procedure has been deprecated in favour of ALTER AUTHORIZATION.


Wordcount in a field (all and unique) - is there a more elegant/optimal way?

First step obviously is to create the table and data (as per the question mentioned):

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ($$What a great day$$);
INSERT INTO wordcount (description) VALUES ($$This is a product. It is useful$$);
INSERT INTO wordcount (description) VALUES ($$['a', ' ', ' ', 'b']$$);

The first "lifesaver" was the dollar quoting ($$) - a really neat
PostgreSQL feature. I was really floundering before I came across
this - couldn't even get the data into the table (trying
backslashes, double quotes &c.)

My final SQL looks like this (fiddle here):

WITH cte1 AS
(
  SELECT id,
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT id,
       COUNT(word),
       COUNT(DISTINCT(word)),
       description
FROM cte1
WHERE LENGTH(word) > 0
GROUP BY id, description
ORDER BY id;

Result:

id  Word_count  Distinct_count  description
 1           4               4    What a great day
 2           7               6    This is a product. It is useful
 3           2               2    ['a', ' ', ' ', 'b']

Logic explained:

I decided not to bother with capitalisation - i.e. "It" and "it" are different words in this case - if this is an issue, the simple addition of an UPPER() function would solve that - it's not core to the question.

Step 1:

SELECT id, REGEXP_SPLIT_TO_TABLE(description, ',') FROM wordcount;
-- Keeping the id field helps clarity, even if superfluous.

Result:

id  regexp_split_to_table
1   What a great day
2   This is a product. It is useful
3   ['a'
3    ' '
3    ' '
3    'b']

Step 2 (remove all non-space, non-alpha)

SELECT id, REGEXP_REPLACE(REGEXP_SPLIT_TO_TABLE(description, ','), '[^a-zA-Z\s]', '', 'g')
FROM wordcount;

-- Remove all non-alpha, non-spaces. Otherwise the words "product" and "product." would
-- be counted as different! Again, keeping the id field makes things clearer,
-- even if not strictly necessary for purists

Result:

id  regexp_replace
1   What a great day
2   This is a product It is useful
3   a
3
3
3    b

Step 3 (put the strings into an array):

SELECT id, STRING_TO_ARRAY(REGEXP_REPLACE(
          REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')
FROM wordcount;
--              id again - not strictly necessary at this step.

Result:

id  string_to_array
1   {What,a,great,day}
2   {This,is,a,product,It,is,useful}
3   {a}
3   {"","",""}
3   {"","",""}
3   {"",b}

Finally, the answer itself - UNNEST and then select those words LENGTH > 0 grouping by id and description.

i.e. SELECT the necessary from the following cte (Common Table Expression) - the cte isn't strictly necessary - I could have used the UNNEST... throughout my final query, but that would have been horrible to read and debug. It's the reason Common Table Expressions were invented!

WITH cte1 AS
(
  SELECT id,
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT blah... (see above)


MySQL: how to implement a text search without delimiter

Digrams (or trigrams).

Take each pair (or triple) of letters in the text, hash it down to 0..63, set a bit in a BIGINT UNSIGNED.  The "OR" of a few dozen bits together like that will usually lead to about half the bits being on.  (If most of the bits are on, the method is less effective; if too few bits are on, it wastes space.

Do that as you store the data, and store the bigints in extra columns.  When testing, do likewise to the data coming in -- get a bigint representing the query.

WHERE (big3 & test3) = test3

Where big3 is the bigint for field3 and test3 is the bigint for "str3", that clause will check that all of the letter pairs of "str3" are probably in field3.

For the combined test of field1/2/3 against "str1",

AND ((big1 | big2 | big3) & test1) = test1

Better yet is to precompute (big1 | big2 | big3) into a combined big123 initially.

I said "probably".  That means you will get some false positives and need to double check the 'hard' way, as you already discussed.

Note:  If there is some meaning you would like to apply for "word boundary", that can easily be done in the building of the bigints.

Hash

One such hash:

SELECT HEX(1 << (CONV(MID(MD5(LOWER('th')), 5,2), 16, 10) & 0x3F));
+-------------------------------------------------------------+
| HEX(1 << (CONV(MID(MD5(LOWER('th')), 5,2), 16, 10) & 0x3F)) |
+-------------------------------------------------------------+
| 8000                                                        |
+-------------------------------------------------------------+

Translate this into your programming language; it would probably be easier there, not in SQL.

Dissecting it:

HEX(1 << (CONV(MID(MD5(LOWER('th')), 5,2), 16, 10) & 0x3F))
                              ^^  sample digraph
                       ^^^^^  fold case unless you need to differentiate
                   ^^^  a readily available hashing function
               ^^^                   ^^^  -- pick some hex digits out of it
          ^^^^                             ^^^^^^  -- hex -> decimal
                                                   ^^^^^^  -- only 6 bits
    ^^^^  -- shift "1" to the bit position given by the 6-bit number
^^^  -- (just for displaying the result; not part of the algorithm)


How do I query to pull all data in row based MIN and MAX service date?

There might be other better ways but does following work for you?

select client_id
      ,service_date
      ,other_stuff
  from (
        select client_id
              ,service_date
              ,other_stuff
              ,row_number() over (partition by client_id order by service_date) rn_min
              ,row_number() over (partition by client_id order by service_date desc) rn_max
          from tbl
       )
 where (rn_min = 1 OR rn_max = 1) ;


Configuring SQL Replication subscriber to a publisher in a different DNS zone

I can set up the subscription if I add the server name and ip into the hosts file.

Not pretty, but it works.


SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn

This answer has been copied from the accepted answer on this question of Stack Ovweflow:
How to determine the SQL Server replication type by TSQL?

Run the following query on the distribution database:
SELECT
     P.Publication
    ,P.Publication_type
    ,S.Subscriber_ID
    ,S.Update_Mode
FROM
    MSPublications P
INNER JOIN
    MSSubscriptions S
    ON P.Publication_ID = S.Publication_ID;

Publication_type:

0 = Transactional
1 = Snapshot
2 = Merge

Update_mode

0 = Read only
1 = Immediate update
2 = Queued update with message queue
3 = Immediate update with queued update as fail-over using message queue
4 = Queued update with SQL Server queue
5 = Immediate update with queued update as fail-over using SQL Server queue


Create LOG files when creating multiple DATA files for tempdb?

No, SQL Server only writes to 1 LOG file at a time. So it doesn't help to create multiple log files for better IO performance. It will use the first file and only when that one is full and no VLFs are cleared it will continue in the second file, etc. etc.

You might want to create a second log file if for example you run into an emergency situation where you run out of disk space on disk X and you want SQL server to continue logging in a second file on disk Y.


load spikes to 400%

That probably means that you are CPU-bound and not I/O-bound.  And hence, you have plenty of RAM, but the queries are inefficient.

  [!!] Query cache prunes per day: 4351

In most production systems, the write traffic is so high that the Query cache should be turned off.  There are other metrics to further demonstrate that.

  [!!] Joins performed without indexes: 155460

For tiny tables, that does not matter.  For even small tables, that could matter.

Turn on the slowlog with long_query_time = 1; wait a day; run pt-query-digest.  Then show us the first couple of naughty queries, together with SHOW CREATE TABLE and EXPLAIN SELECT ....  That is very likely to include the worst offenders of "non-indexed JOINs".

  [!!] Table cache hit rate: 0% (1K open / 11M opened)

You have more than 1000 tables?  And you are actively using more than 1000?  That sounds like a poor schema design.  Please elaborate on why there are so many tables.  (Meanwhile, increase table_open_cache to, say, 2000 and set table_open_cache_instances to 16 (assuming you are running at least 5.6.6).

Don't go past 100M for query_cache_size.  It gets inefficient as it gets bigger.

In my opinion, log-queries-not-using-indexes is more clutter than benefit.

For a head-start on fixing the indexes, see my cookbook.

If you would like a lot more tests (more than what that tool did), provide me with

RAM size
SHOW VARIABLES;
SHOW GLOBAL STATUS;

It will include an analysis of whether the QC is being useful.


High Availability Options for 2008 R2 or 2014 to 2016 "Mirror" for Reporting?

Your only valid options would be transactional replication. No changes since your downstream version is 2008 R2. When dealing with features like AGs, it generally is same MAJOR version of SQL Server.


One solution would be this:
SELECT DISTINCT
(SELECT COUNT(t2.f_id) FROM t t2 WHERE t2.reg_date <= dates.d) AS running_total
, dates.d
FROM t
RIGHT JOIN dates ON t.reg_date = dates.d
ORDER BY dates.d;

- see it working live in this sqlfiddle
As you can see, you need to provide the dates for which there is no data in your table in some way, if you really want to include them in your result. The easiest way is to have a table with some dates in it. Another way could be some views with fancy math.
Anyway, what you have to look out for in this solution is that you need to specify a column in the COUNT() function. With COUNT(*) it would count the NULL values, too.
There are other ways to solve this as well, especially with MySQL 8.0. Unfortunately I don't have time right now, but maybe I will find the time later. Or someone else adds another answer.
EDIT: to create for example 1000 dates starting from an arbitrary date (in this example the current date) on the fly, you could for example use this:
SELECT CURDATE() + INTERVAL c*100+b*10+a DAY AS thousand_days
FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3
ORDER BY thousand_days;


Is this Dedicated Server good enough to Run MySQL on Linux Ubuntu?

It's hard to know / to figure out:

Even some simple reads, could be IO hungry.
And the amount of users doesn't tell us how many request ( read / write).

Without more data, it hard to tell, but that seem to be a good server. I believe that it'll be good enough, but that's just a opinion.


You are saying:

EXEC @SQLString;

This is like saying:

EXEC N'SELECT 1;';

Which is probably something that looks like it will work on first glance, but go ahead and try it, it fails. You want:

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT ...;';
EXEC sys.sp_executesql @sql;

I wrote a pretty detailed article on dynamic pivot here:

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

But as for dynamic SQL, please see:

https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql
https://www.mssqltips.com/sqlservertip/3637/protecting-yourself-from-sql-injection-in-sql-server--part-1/
https://www.mssqltips.com/sqlservertip/3638/protecting-yourself-from-sql-injection-in-sql-server--part-2/


SQL Linked Server Passing Date Parameters

For an ad hoc batch, you need to type the date values and assign them to the variables:

DECLARE @fromperiod varchar(8),
        @toperiod   varchar(8);

SELECT @fromperiod = '20130101',
       @toperiod = '20130131';

An ad hoc T-SQL batch can't prompt you; it's not interactive like VB in a Windows Form. You could do this by using the Debug button on the toolbar, and then editing the values in the Locals tab, but this seems to be a pretty cumbersome way to avoid a little typing:

There are probably also ways you could use templates or snippets to have a slightly easier way to input values, but I don't find the above that much of a blocker in the first place.

For Reporting Services, I know there are ways to allow users to enter data and have those passed at parameters, but someone else is going to have to give you the skinny on that (the documentation might be helpful as well). I can spell Reporting Services but not much more than that. If this is a stored procedure it probably makes things a lot easier.


It should work with:

SET FOREIGN_KEY_CHECKS=0

I tried the following:

create table parent (x int not null primary key) engine = innodb;
create table child (x int not null primary key, constraint aaa foreign key (x) references parent (x) on delete restrict) engine = innodb;
insert into parent (x) values (1),(2);

-- test if f.k is active
insert into child (x) values (1),(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ("test"."child", CONSTRAINT "aaa" FOREIGN KEY ("x") REFERENCES "parent" ("x"))

insert into child (x) values (1);
SET FOREIGN_KEY_CHECKS=0;
delete from parent;
select * from parent;
Empty set (0.00 sec)

select * from child;
+---+
| x |
+---+
| 1 |
+---+

select @@version
-> ;
+-----------------+
| @@version       |
+-----------------+
| 10.1.16-MariaDB |
+-----------------+

So the problem is not with MariaDB, but with PhpMyAdmin. It has a checkbox on the pages where you can execute SQL, that overrides SET FOREIGN_KEY_CHECKS=. One must uncheck it if one wants to disable foreign key validation.


Each job step has a setting that allows it's output to be sent to a text file. This can be cumbersome because it's a text file located in the file system and not in a table and might be hard to get to. There is also an option to send the output of a job step to a table. You can query the table, but it's still clunky. (It is hard to write a query to pick the "X rows affected" string out of a blob of text, then pull the number out so that you can aggregate it, etc.)

I would recommend that you build a simple table in your user database (not in msdb or master) to keep that information. IMO, this is the best way to keep close track of the number of rows affected by each command, particularly if you want to be able to write simple queries to do historical reporting. This way, you can decide to log at the job step level, or if the step calls a stored procedure with multiple commands, you can log a audit record after each command. As always, @@ROWCOUNT is your friend.


Is it possible to switch queries based on an input in mySQL?

This should be possible with basic logical operations (IF, THEN, ELSE etc)

In this situation you'd want to get the INPUT value and check it against your conditions (pseudocode):

IF INPUT = 'YEARLY' THEN
BEGIN
INSERT QUERY HERE
END
END IF

IF INPUT = 'MONTHLY' THEN
BEGIN
INSERT QUERY HERE
END
END IF

For more information on this check the manual:

https://dev.mysql.com/doc/refman/5.7/en/if.html


'bsondump --pretty --type=debug' use case and output explanation

The bsondump utility converts BSON data (which is binary) into human-readable formats. Common usage would be bsondump --pretty to inspect data formatted as MongoDB Extended JSON. This can be useful for previewing data contained in BSON files as created by mongodump, replica set rollbacks, and other features (eg. auditing in MongoDB Enterprise as per your example).

The --type=debug output is generally only relevant for a developer who wants to see an annotated view of the BSON (as per the BSON spec).

  What's --- new object ---? It seems not to be a JSON document.

This indicates the start of a new BSON object, which would be represented as an opening curly brace ( { ) in JSON. You will see this at the top level for each new document and for every embedded document/array.

  What's type and the number? Is size characters or other measurement?

This indicates the BSON type and size (in bytes) of that object including nested fields/objects.

Potential use cases for confirming underlying data types might be checking for consistency (eg. a given field has the expected type) or  confirming underlying data types in order to do a query using the  $type operator in MongoDB.

You can perform a similar size calculation for a document/object in the mongo shell using Object.bsonsize(object).


What's the most efficient way to reclaim unused DB hard drive space?

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP NewFG;
GO

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = FG2,
    FILENAME = 'G:\DATA\FG2.ndf',
    SIZE = 500MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 250MB
)
TO FILEGROUP NewFG;
GO

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID)
WITH (DROP_EXISTING = ON) ON [NewFG]
GO


Figured out the issue.

Variable should not be the same as any column in the query.

DECLARE HOSTID INT;

declare cur1 cursor for SELECT itemid  FROM items WHERE hostid = 10579;

Since the cur1 query has a column named hostid it will trigger the not found continue handler which sets finished variable to true.

Not only it triggers continue handler but in some cases it can produce syntax error. Source - https://stackoverflow.com/a/60988686/13109839

Solution rename the HOSTID variable to _HOSTID.

DECLARE _HOSTID INT;


Thanks for the clarification. The first proposal is the infamous EAV, Entity Attribute Value. It is my daily pain as it is the model used by a 3rd party tool. If you want to have a fine list of why it is usually such a problematic model, please have a look at Bill Karvin's "SQL Antipatterns". You will have problems with data types, multiple values, enforcing integrity...

Therefore, I'm all in favor of the second model. If the volume of your data is really important, you can consider partionning, especially vertical partionning.


What is the fate of VODKA Indexing, is it dead?

I asked this question when I last saw Alexander (who is Vodka's developper).
To him, Vodka was more a Proof of Concept than an actual thing suitable for production. He needs to rewrite it all to achieve production-ready code.

But, he has a lot of things to do and so the project is frozen for now.

Cheers,

Arkena


Your question appears to be a duplicate of this question, but that doesn't have a formal answer which means it technically can't be a duplicate :) so I'll synthesize an answer here.  As you can see in this response on StackOverflow the latest version of the Portal should allow you to set the server name. I copied the picture over from that answer for convenience.

If that doesn't work you can go to this blog post as a fall back.  The meat of that blog post is here:

To use a meaningful alias, what you need to do is:

Using SQL Server Configuration Manager, in the SQL Native Client 11.0 Configuration\Aliases node, create an alias. Pick a meaningful name (ie; HRServer) for the Alias Name, leave the port at the default (ie: 1433), leave the protocol at the default (ie: TCP/IP) and provide your real server name (ie yy2195dk1k.database.windows.net) in the Server field.
Create the same alias in the SQL Native Client 11.0 Configuration (32bit)\Aliases node.
When connecting to the server in SSMS, enter your new alias (ie: HRServer) for the Server name, SQL Server Authentication for the Authentication method, and for the login, specify your login name followed by an @ symbol, followed by the first segment of the real server name (ie: mylogin@yy2195dk1k), then enter your password.
If the user isn't an admin user that has the ability to connect to the master database, you'll also need to manually set the database name using the Options button. (Note that you won't be able to browse for database names).

Later in the blog's comments they state:

After all, an alias is just a registry entry and so it can be pushed out by group policy like any other registry entry.

So you should be able to push out a group policy to any other clients which should allow them to use the alias if the Azure portal method doesn't work.
Please post an answer or comment on my answer if you find this doesn't work.  My answer is based on research not field experience.


What index should I use for a read-only database?

You said, that TimeElapsed is not really filtering much, so there is no point in indexing it.

Just looking at the query structure, I'd add an index to Parameters table on
(ExogDexCurve, ControlRange, SensorError). The order of columns is important.

I would also make sure that all indexes have Fill Factor=100, because the data is static.

But, overall, the main bottleneck is reading all Xa values for the needed ControlRange and SensorError.

The server has to read all this data. Storing it in columnstore index should help.

I would also consider denormalizing the data and making just one table (persisting the join). In this case I'd create a covering index. The order of columns is important.

(ExogDexCurve, ControlRange, SensorError, Xa) INCLUDE (TimeElapsed)

Xa is part of an index rather than included, because you order by Xa.

With one joined table and this covering index the query should scan the index without extra sorts.

Also, to "hide" the persisted joined table it may be possible to create an indexed view of the join instead of explicit table, but I'm not sure.


you will need something like this.

CREATE DATABASE QA
GO
USE QA
GO
CREATE TABLE Users
(
UserID int identity(-2147483648,1),
Name varchar(150),
fname varchar(150),
isadmin bit,
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserID),
)

GO
CREATE TABLE Question
(
QuestionID int identity(-2147483648,1),
UserID int,
Question varchar(max),
CONSTRAINT PK_Question PRIMARY KEY CLUSTERED (QuestionID),
CONSTRAINT FK_UserQuestion FOREIGN KEY (UserID)
    REFERENCES dbo.Users (UserID)
)
GO
CREATE TABLE Answer
(
AnswerID int identity(-2147483648,1),
QuestionID int,
UserID int,
Answer varchar(max),
IsSolver bit,
CONSTRAINT PK_Answer PRIMARY KEY CLUSTERED (AnswerID),
CONSTRAINT FK_UserAnswer FOREIGN KEY (UserID)
    REFERENCES dbo.Users (UserID) ,
CONSTRAINT FK_AnswerQuestion FOREIGN KEY (UserID)
    REFERENCES dbo.Question (QuestionID)
)
GO

This will generate this diagram.

You will need to add extra fields for other items you need, but the diagram should be what you need to accomplish your task.


Sql not exist, returning 0 rows and should return one

I don't know table schema of both tables, but assuming they have the same schema you can do something like this:
SELECT
    *
FROM
    xptoPrimary.dbo.CcArtigos AS tPrimary
WHERE
    Tipo_Doc in ('INE','INS')
    AND Armazem=2
    AND data_mov >='01-07-2018'
WHERE NOT EXISTS (SELECT
                      1
                  FROM
                      xptoSecondary.dbo.CcArtigos as tSecondary
                  WHERE
                      tPrimary.cod_art = tSecondary.cod_art
                      AND tPrimary.Tipo_Doc = tSecondary.Tipo_Doc
                      AND tPrimary.Armazem = tSecondary.Armazem
                      AND tPrimary.data_mov = tSecondary.data_mov);

That is, for each row of tPrimary table, there isn't a row in tSecondary that match the same criteria.


Are Views optimized when I add a WHERE clause to them?

You should see absolutely no difference in the plans or the performance between these two choices. When the view is queried, it is expanded out to a query against the base table, which means the same seek or scan will be used.

Now, depending on the data type and selectivity of MyColumn, if you wanted to create a filtered index on the base table (when you move to SQL Server 2008+), you might get better performance, but this again will not be different via the view or without.


Set in dynamic query value of variable declared outside dynamic query

Three things worth mentioning:

Always use PRINT to view the resulting dynamic SQL whenever you work with dynamic SQL. You will see that the SQL variable is actually holding NULL.

DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET ' + CAST(@variable1 AS VARCHAR) + ' = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'

PRINT(@SQL)

-- EXEC(@SQL)

The reason because the dynamic SQL is NULL is because you are concatenating a NULL value which is the @variable1 contents. I believe you wanted to write down the text '@variable1' as literal:

DECLARE @variable1 INT

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT

    SET @variable2 = 1
    SET @variable1 = @variable2

    SELECT @variable1 as V1, @variable2 as V2
'

PRINT(@SQL)

Whenever you use EXEC, the scope changes and variables declared outside can't be accessed anymore. So inside the dynamic SQL, you won't be able to read @variable1 since it's not declare anywhere. If we execute the dynamic SQL:

The way you can set variables values inside a dynamic execution and be able to read them from the outside is by supplying parameters via the OUTPUT option. This will require to use the SP sp_executesql rathen than a direct EXEC:

DECLARE @externalVariable INT

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = '
    DECLARE @variable2 INT = 1
    SET @resultVariable = @variable2'

EXEC sp_executesql
    @stmt = @SQL,
    @params = N'@resultVariable INT OUTPUT',    -- Declare the "input" parameters for the dynamic SQL
    @resultVariable = @externalVariable OUTPUT  -- Supply the "input" parameters for the dynamic SQL

SELECT
    Result = @externalVariable -- Read the updated value

Note that I changed data types to NVARCHAR since sp_executesql works with unicode inputs.

Another example with more parameters:

DECLARE @firstNumber INT = 15
DECLARE @secondNumber INT = 3
DECLARE @result INT

DECLARE @SQL NVARCHAR(MAX) = '
    SET @multiplicationResult = @inputFactor1 * @inputFactor2'

EXEC sp_executesql
    @stmt = @SQL,
    @params = N'
        @multiplicationResult INT OUTPUT,
        @inputFactor1 INT,
        @inputFactor2 INT',
    @multiplicationResult = @result OUTPUT,
    @inputFactor1 = @firstNumber,
    @inputFactor2 = @secondNumber

SELECT
    Result = @result -- 45!

If you don't have to read back results from variables, you can build your dynamic SQL by "hard-coding" the variables values directly into the script. Make sure to correctly use data type conversions inside the script and also escape NULL and literal values:

DECLARE @DateVariable DATETIME = GETDATE()
DECLARE @StringVariable VARCHAR(100) = NULL
DECLARE @FloatVariable FLOAT = 15.14

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        DateVariableContents = CONVERT(DATETIME, ''' + ISNULL(CONVERT(VARCHAR(100), @DateVariable), '') + '''),
        StringVariableContents = ' + ISNULL('''' + @StringVariable + '''', '''''') + ',
        FloatVariableContents = CONVERT(FLOAT, ''' + ISNULL(CONVERT(VARCHAR(100), @FloatVariable), '') + ''') '

PRINT(@DynamicSQL)

EXEC(@DynamicSQL)

Printed:

SELECT
    DateVariableContents = CONVERT(DATETIME, 'Mar 21 2019  3:27PM'),
    StringVariableContents = '',
    FloatVariableContents = CONVERT(FLOAT, '15.124')

Result:

DateVariableContents        StringVariableContents  FloatVariableContents
2019-03-21 15:28:00.000                             15.124


How to get the average value of each second? MySQL

SELECT  LEFT(RealTime, 19) AS to_the_second,
        ROUND(AVG(AcX))  AS Average_AcX
    FROM some_table
    GROUP BY 1;

Notes:

Often it is practical to treat a datetime (or timestamp) as a string, as I am doing with the LEFT.
You may want to add the number of decimal places to the ROUND() function call.
The 1 in GROUP BY 1 is shorthand for 1st column (the LEFT...).


fetch all data having a Price BETWEEN 100000 AND 10000000

Storing numeric data in a varchar column is causing your difficulty.

You can see this effect with a very simple test.

First a table with two columns where we'll store identical data; one column is a varchar, the other is an integer.

CREATE TABLE test
(
    v varchar(100) NOT NULL
    , n int NOT NULL
);

Insert some sample data:

INSERT INTO test (v, n)
VALUES (0, '0')
    , (1, '1')
    , (2, '2')
    , (100, '100')
    , (99, '99');

Show the data, ordered by the varchar column:

SELECT *
FROM test
ORDER BY v;

Results:

╔═════╦═════╗
║  v  ║  n  ║
╠═════╬═════╣
║   0 ║   0 ║
║   1 ║   1 ║
║ 100 ║ 100 ║
║   2 ║   2 ║
║  99 ║  99 ║
╚═════╩═════╝

Show the data, ordered by the integer column:

SELECT *
FROM test
ORDER BY n;

╔═════╦═════╗
║  v  ║  n  ║
╠═════╬═════╣
║   0 ║   0 ║
║   1 ║   1 ║
║   2 ║   2 ║
║  99 ║  99 ║
║ 100 ║ 100 ║
╚═════╩═════╝

The varchar column is sorted alphabetically, whereas the integer column is sorted numerically.

You should consider altering the price column to a numeric data type that supports the precision and magnitude you require.  Refer to the MySQL developer guide for fixed-point data types for more details.  I'd suggest considering decimal(18, 4) as a starting point for prices.  18 digits is the precision, i.e. the total number of digits that can be stored.  4 is the scale, which is the number of digits to the right of the decimal point.  If the price column was defined as decimal(18, 4), it would be able to store values such as 99999999999999.9999, and allow them to be sorted correctly.  Defining the column as a varchar is problematic since it will allow you to store hedgehog as well as 10000, which will lead to a variety of problems, not least of which will be the inability to obtain a total price of articles in a group.


Oracle service names with a minus sign (?)

Oracle said no, only [a...z] [A...Z] [0...9] _


While importing 150 GB file on Amazon RDS, why connection is failing?

It's the file size that's the problem.

Essentially there's a timeout expiring somewhere, the server logs will tell you where.

When the timeout expires, no more data is produced, and after a while with no data the connection gets closed as per normal.

The last time I had to tackle something like this (Which is a long time ago now), I wrote a small program that basically pulled the data out of my server in chunks using various SQL criteria.

This is absolutely NOT the best way of doing it, but when you have a table that's so big and takes so long that things start to time out, it's one of the only ways I found to make it work.

The other approach you have is to use an ETL tool such as Pentaho and do a server to server transfer, again using a custom query to do it a chunk at a time.

If you MUST do it using PSQL, then try to break it up.  Instead of one massive 150gb file, try doing the xfer a table at a time or similar.

Also, think about local resources.  You might get better traction if for example, you can dump the file to a server where you have file system access that's inside amazons network, such as an AWS instance.  If you have a local resource, set up an AT job, dump the DB to that instance (optionally G-zipping it in the process) then transfer that file to your PC using a web browser of FTP connection.

Tackling the initial problem head on, your not going to know which approach you have to take until you can figure out why it's timing out.


Azure Readable Secondary - How to: Different Access Policy to Primary?

The only way to have different access policies. e.g A reader (by whatever principal - AAD group user etc) can only access the secondary-

Make the 'Active Directory admin' of the logical server hosting the secondary replica an AAD group.
Make the user or group that needs to read from the secondary replica is a member of the group mentioned in step 1.
As a pre-requisite you need to set the properties of secondary to read-only or all.


How to repair Microsoft.SqlServer.Types assembly

After searching online a lot, I see the only option to fix that type of corruption is to recreate the database objects and move all the data.

(or restore a backup if available)


Oracle server connection reset by peer socket write error

The windows error 54 is associated with a port busy or problems with the network communication, try editing the listener.ora file and change the port, then restart the listener service (or if you're able to restart the machine, do so).

It looks like that, for some reason, the port 1521 hungs and there's no longer a TNS Listener service listening there.

P.D. if you didn't configured a TNS_ADMIN folder, the listener.ora file must be in the %ORACLE_HOME%\network\admin folder, where the %ORACLE_HOME% is where the product was installed

The Windows error reference is in: http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx


when running SSIS package with Windows Auth it works but with SQL Auth it fails

I believe your connection string is off based on what you added, I would try looking at the following website. It looks like your string is designed for a CE device...

https://www.connectionstrings.com/sql-server-2008/


Running sum total over 14 days for each date

This is a bit tricky but very fast query that counts how many days customer spent idle up to the date. You can use it as subquery to refine by additional conditions. I hope it is selfexplanatory enough.
Unfortunately dbfiddle works pretty bad with UDVs so I can't provide a working sample.
SELECT cust_id
     , date
     , new_transactions
     , total_transactions
     , IF( @curr_cust_id <=> @curr_cust_id := cust_id,
       /*THEN*/ IF( new_transactions = 0,
                /*THEN*/ @idle4 := @idle4 + 1,
                /*ELSE*/ @idle4 := 0
                /*FI*/ ),
       /*ELSE*/ @idle4 := 0
       /*FI*/ ) AS idle_for
  FROM customer_tbl
 WHERE ( TRUE OR @idle4 := 0 )       -- UDV initialization
 ORDER BY cust_id ASC
        , date ASC
;

+---------+------------+------------------+--------------------+----------+
| cust_id |       date | new_transactions | total_transactions | idle_for |
+---------+------------+------------------+--------------------+----------+
|   10001 | 2020-02-01 |                1 |                  1 |        0 |
|   10001 | 2020-02-02 |                1 |                  2 |        0 |
|   10001 | 2020-02-03 |                0 |                  2 |        1 |
|   10001 | 2020-02-03 |                0 |                  2 |        2 |
|   10001 | 2020-02-04 |                0 |                  2 |        3 |
|   10001 | 2020-02-05 |                0 |                  2 |        4 |
|   10001 | 2020-02-06 |                1 |                  3 |        0 |


Is there any slowdown in casting from varchar to text?

All tests done on PostgreSQL 9.5. On the matter of the cast,

Sample Data

We first create a table with 100,000 rows with 3 varchar columns.

CREATE TABLE foo AS
  SELECT x::varchar AS x1, x::varchar AS x2, x::varchar AS x3
  FROM generate_series(1,1e6) AS gs(x);

In SELECT

Then we try to the casts,

SELECT x1::text, x2::text, x3::text FROM foo;
Time: 476.825 ms

SELECT x1::text, x2::text, x3::text FROM foo;
Time: 501.092 ms

SELECT x1, x2, x3 FROM foo;
Time: 360.493 ms

SELECT x1, x2, x3 FROM foo;
Time: 373.647 ms

Conclusion: substantial slowdown it's not a compile type coercion.

In WHERE clause

However, in the where conditional they seem to act effectively the same,

SELECT * FROM foo
WHERE x1::varchar = '5'::varchar;

Performs as well as,

SELECT * FROM foo
WHERE x1::text = '5';

And if I now run,

SELECT *
FROM foo
WHERE array_position(ARRAY['5', '6', '9'], x1::text) IS NOT NULL;

SELECT *
FROM foo
WHERE array_position(ARRAY['5', '6', '9']::varchar[], x1) IS NOT NULL;

Both of them perform the same, so there is an optimization in the WHERE clause.


Implementing older version of mySQL on RH9

Personally, I would not risk moving the installed binaries laterally to a newer OS because you may have to drag along needed libraries WHICH MAY NOT WORK IN THE NEWER OS !!!

You should thoroughly look through MySQL's Archives, which goes back to MySQL 5.0.15.

SkySQL also tries to fully accommodate users of older versions of MySQL, MariaDB, and more. Please go to their repository and find the version you need. They go back to MySQL 4.1.

You should make sure SkySQL has RHEL6 builds, at least.

You could also try compiling MySQL from source within RH9. DISCLAIMER : Never Built MySQL From Source


It has to do with the way VARCHAT/TEXT/BLOB field are handled.
I personally observed this in an RDS instance
I had taken a table with 25 million rows which had a TEXT field in it and ran this
SELECT DISTINCT LENGTH(colname) FROM tblname;

and I saw just 2 numbers, 3 and 10.
I converted the field from TEXT to VARCHAR(10). What this did was increase the amount of RAM occupied in the InnoDB Buffer Pool. I verified this from a CloudWatch graph, see Free RAM dip right after doing this and the graph stayed that way. The number of free buffer pool pages dropped accordingly.
I am not the only to have seen this phenomenon. Someone once posted a blog entitled "Drain your InnoDB Buffer Pool by moving large fields out of MySQL" who had observed similar Buffer Pool weirdness.
What is interesting is that Bill Karwin had commented on this in a DBA StackExchange post (MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?). He mentioned that BLOBs will occupy InnoDB Buffer Pool Pages. Shrinking them can make data fit in the InnoDB Buffer Pool better could work for you or against you depending on the size of the data.
In your particular case, you only have 269 rows. This should let you know that handling TEXT fields requires some extra I/O in RAM or on disk. You are using MEDIUMTEXT, which can hold up to 16M. Given innodb_page_size = 16384 (16k), a MEDUIMTEXT value can occupy up to 1024 pages. This potential means you are dragging a lot of space from disk based on how much data is in each MEDIUMTEXT field. You may have to live with it.
SUGGESTIONS

If you keep MEDUIMTEXT, You may have to increase innodb_buffer_pool_size to accommodate
If you would like to change from MEDUIMTEXT, please run SELECT LocalDescription,LocalFacilitiesDescription FROM test.hotels where Id > 0 PROCEDURE ANALYSE();. This will suggest the proper datatype for those columns. (BTW I have discussed the use of PROCEDURE ANALYSE()).


we have a bunch of utility scripts and views for this kind of thing on GitHub. This one is for table privileges: v_get_tbl_priv_by_user .

A couple of things to keep in mind to simplify your admin tasks:

Use late binding views to minimize drop-recreate scenarios: WITH NO SCHEMA BINDING
Use ALTER APPEND to move a large volume of rows between tables. Again avoiding expensive drop-rename or insert scenarios.


How to fix postgres stuck in recovery mode?

If you can re import your data, you can uninstall postgresql and reinstall it. And then re import your data. I had a similar issue and this worked for me.

for ubuntu : sudo apt-get --purge remove postgresql-YOUR VERSION


How to restart MS SQL server right, what is best practice to do that?

You almost certainly have a very large number of virtual log files (VLFs) in the problematic log file of the database in question.  Shutting down and restarting SQL Server causes the recovery process to run on all databases on the instance.  For databases that have a very large number of VLFs this process can be agonizingly slow.

You can see the VLFs via this DBCC command:

DBCC LOGINFO;

Each row represents one VLF.  How many do you have?  I'm betting you have a very high number of VLFs (rows).

You can rebuild the log file to remove the excess VLFs; simply shrink the log file, then grow it in sensible increments to the prior size.

I wrote a blog post showing how to do that here.

If you're concerned about shutting down the SQL Server instance without causing recovery to run, you can issue this command before you shut down SQL Server:

shutdown;

However, if there are existing connections, the command will wait until all connections are closed.  See the Microsoft docs for more detail.  SHUTDOWN WITH NOWAIT; will cause SQL Server to immediately shutdown, without waiting for existing connections to close, however this will trigger recovery at the next startup, and is exactly what happens when you shutdown the instance via the control panel, or via shutting down the server itself.


How do I setup a foreign key relationship based on two columns?

Yes, by adding a UNIQUE constraint (or a unique index) on the company table, using these 2 columns:

ALTER TABLE company
  ADD CONSTRAINT CompanyID_DepartmentID_UQ
    UNIQUE (CompanyID, DepartmentID) ;

Then you can add the foreign key:

ALTER TABLE [user]
  ADD CONSTRAINT Company_User_FK
    FOREIGN KEY (CompanyID, DepartmentID)
    REFERENCES company (CompanyID, DepartmentID)  ;

Test at SQL-Fiddle

Minor comment: Since the table company has DepartmentID as the primary key, shouldn't it be called department table? (How do you call the table where the CompanyID is the primary key?)


DB2 BOOLEAN type, EXISTS predicate and nullability strange behavior

An interesting question with the answer that is not so interesting: in this trivial example your EXISTS is optimized away, so an attempt to reference this predicate evaluation result is undefined. If you run db2exfmt (or another tool that can retrieve the "optimized" version of the statement), you will see that this is what gets executed in the end:
SELECT 'exists' AS "STMT",
   $INTERNAL_FUNC$() AS "X",
   $INTERNAL_FUNC$() AS "x is null",
   $INTERNAL_FUNC$() AS "NOT x",
   $INTERNAL_FUNC$() AS "x = FALSE",
   SMALLINT($INTERNAL_FUNC$()) AS "smallint(x)",
   CHAR($INTERNAL_FUNC$()) AS "char(x)",
   $INTERNAL_FUNC$() AS "Y",
   $INTERNAL_FUNC$() AS "y is null",
   $INTERNAL_FUNC$() AS "NOT y",
   $INTERNAL_FUNC$() AS "y = FALSE",
   SMALLINT($INTERNAL_FUNC$()) AS "smallint(y)",
   CHAR($INTERNAL_FUNC$()) AS "char(y)"
FROM (SELECT NULL
FROM (VALUES ) AS Q1
WHERE ('Y' = 'N')) AS Q2

This is likely Working As Designed™, because predicates, of which EXISTS is one, are not meant to be used in place of expressions. Db2 tries to do its best to turn predicate evaluation outcomes into values, but the optimizer has an upper hand.
Things work as you expect when you use expressions and predicates where they respectively belong:
with boolstmt as
(
  select  'exists' stmt,
    case when exists(select 1 from sysibm.sysdummy1 where ibmreqd = 'N') then true else false end as x
  from sysibm.sysdummy1
 )


When binary logs do not reach the limit set by max_binlog_size, there are two(2) major reasons why

mysqld closes the currently open binlog and opens a new binlog

Running service mysql restart
Running service mysql top followed by service mysql start
mysqld crashes and mysqld_safe restarts it if the mysqld crash was not severe

Running FLUSH LOGS will do the following

Close and Reopen the Error Log
Close and Reopen the General Log
Close and Reopen the Slow Log
Close and Reopen Engine Logs
Close the Currently Open Binary Log and Open a New Binary Log

It says the following the MySQL Documentation under FLUSH LOGS

  With no log_type option, FLUSH LOGS closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file.

SUGGESTIONS

Look in your error log to see if mysqld crashes and restarts. The timestamp should match the binlog that was closed. The error log will also show the reason for the crash, or at least leave you a trail to attempt to troubleshoot
If your code or some cronjob is running FLUSH LOGS, do one of the following:

Replace it with FLUSH ENGINE LOGS;
Stop using FLUSH LOGS altogether
Live with it. Having 2M or 3M binary logs is not all that bad


Suppose your date range is 2014-12-08 to 2014-12-11

This would be the Query you need

SELECT B.*,A.quantity
FROM
(
    SELECT
        AA.id,SUM(BB.quantity) quantity
    FROM
    (
        SELECT id FROM orders
        WHERE date >= '2014-12-08' AND date <= '2014-12-11'
    ) AA INNER JOIN orderdetails BB ON AA.id = BB.order_id
    GROUP BY AA.id
) A INNER JOIN orders B USING (id);


How can I query matching MongoDB documents and return a subset of fields from an array of embedded documents?

If you want to return a subset of matching array elements in a MongoDB document, the best approach to do so is using the Aggregation Framework. The Aggregation Framework processes data similar to a Unix pipeline: the output of each aggregation stage is passed as input to the next:

db.etudiants.aggregate([
    // Find relevant documents: this can take advantage of an index, if applicable
    { $match: {
        'cours_reussis.nom_ecole': 'ISFCE',
    }},

    // Expand cours_reussis array into a stream of documents
    { $unwind: '$cours_reussis' },

    // Filter based on matching array elements
    { $match: {
        'cours_reussis.nom_ecole': 'ISFCE',
    }},

    // Project desired subset of fields
    { $project: {
        '_id': "$_id",
        'nom': '$nom',
        'prenom': '$prenom',
        'cours_reussis' : { 'nom_ecole': 1, date_reussite: 1 }
    }},

    // Regroup data as per the original document _id
    { $group: {
        _id: "$_id",
        nom: { $first: "$nom" },
        prenom: { $first: "$prenom" },
        cours_reussis: { $push: "$cours_reussis" }
    }}
])

You can debug the aggregation pipeline by starting with a single stage and then adding on additional stages one at a time assuming you are seeing the expected output.

Sample output from MongoDB 3.2:

{
  "_id": ObjectId("58462f40f8475aba9f391216"),
  "nom": "emile",
  "prenom": "zola",
  "cours_reussis": [
    {
      "date_reussite": 2015,
      "nom_ecole": "ISFCE"
    },
    {
      "date_reussite": 2015,
      "nom_ecole": "ISFCE"
    }
  ]
}


Log Reader replication agents are constantly restarting

It sounds like you just need to reinitialize the subscription which will recreate all the objects on the subscriber.

EDIT (Added by asker):

After re-initialization, if there are errors to the effect that it couldn't find a CRUD stored procedure, fire up SQL Profiler and look for errors surrounding that procedure. Make sure you filter the text based on the procedure name from Replication Monitor (It turns out that the command being executed was the stored procedure with a ;2 after it).

Then run EXEC sp_scriptpublicationcustomprocs 'PublicationName' on the publisher to get scripts to create the procedure. Create that procedure on the subscriber db including the group number that the process is attempting to run after the group name (e.g. [sp_MSins_dboTABLE];2). It feels hacky, but it will get replication functioning healthily again.


Approach to Analyzing Database Requirements

You could have a base table to store the common "person" attributes, and then specialized tables for the more specific fields. Example:

Person
------
  id
  ref_num
  reg_dt
  addrs_line1
  addrs_line2
  postal_code
  phone_num

th_person
--------
  id
  person_id (FK to person.id)
  max_accepted_at_one_time

animals
-------
  id
  accepted_by_th_person (FK to th_person.id)

ph_person
---------
  id
  person_id (FK to person.id)
  animal_ref_num

cs_person
---------
  id
  person_id (FK to person.id)

donations
---------
  id
  date
  amount
  donor_id (FK to cs_person.id)

This might be a good starting point.


How can Amazon Redshift be built based on PostgreSQL if Postgres is row-based?

A Postgres server is a lot of things, not just row-oriented access methods:

(The image above is from the Postgres documentation.)
Postgres source code is available for anyone to use under a very permissive license. To implement a DBMS that is "based on PostgreSQL" you don't have to rewrite it from scratch. Say, if you want to introduce a different layout for storing your data you can likely reuse with little or no changes the main server process, client APIs, query parser and rewriter, most of utility and security functions. You might need to modify the plan generator and executor, while adding new access methods and replacing parts of the page storage manager. Given the resources at Amazon's disposal, this doesn't look like an impossible undertaking.
Amazon basically say as much:

the specialized data storage schema and query execution engine that Amazon Redshift uses are completely different from the PostgreSQL implementation. [...] Amazon Redshift stores data in columns, using specialized data compression encodings for optimum memory usage and disk I/O. Some PostgreSQL features that are suited to smaller-scale OLTP processing, such as secondary indexes and efficient single-row data manipulation operations, have been omitted [...]


What are the difference between a "dimension" table in a star-schema and a "lookup" table in a relational database?

If you only have those tables there will be little difference between your datawarehouse star schema and your actual schema.

You probably have a much more complex relational schema though, where you also have customer groups or item types and your schema looks more like

fact table -> customer table -> customer group table
fact table -> item table -> item type table

When you are working towards a star schema you would then denormalize your schema so you include the customer group description in your customer dimension table and your item type description in your item dimension table.

The basis of a star schema is that you have a fact table, and every dimension is a single table just a single step away from the fact table.

The way kimball describes it:

  Star schemas characteristically consist of fact tables linked to
  associated dimension tables via primary/foreign key relationships.

If you don't denormalize your dimensions into single dimension tables you end up with a snowflake schema. Snowflakes may seem like a good idea, but they often result in poorer query performance and can get you in all kinds of trouble later on, especially if you plan to build a cube on top of your data warehouse.

Again Kimball has a post on it:

  We generally encourage you to handle many-to-one hierarchical
  relationships in a single dimension table rather than snowflaking.
  Snowflakes may appear optimal to an experienced OLTP data modeler, but
  they’re suboptimal for DW/BI query performance. The linked snowflaked
  tables create complexity and confusion for users directly exposed to
  the table structures; even if users are buffered from the tables,
  snowflaking increases complexity for the optimizer which must link
  hundreds of tables together to resolve queries. Snowflakes also put
  burden on the ETL system to manage the keys linking the normalized
  tables which can become grossly complex when the linked hierarchical
  relationships are subject to change. While snowflaking may save some
  space by replacing repeated text strings with codes, the savings are
  negligible, especially in light of the price paid for the extra ETL
  burden and query complexity.

With regards to your question about changing the customer data and losing history, in a datawarehouse that's solved by using "slowly changing dimensions".

You basically have several types of slowly changing dimensions, but you are probably looking for "type 2" which largely comes down to storing a valid_from and valid_to date on your dimension row so you can select the row that was valid at the time of the transaction.

Type 2 SCD's

  In addition to the primary surrogate key, I recommend adding five
  additional fields to a dimension that is undergoing Type 2 processing.
  These fields are shown in Figure 1. The datetimes are full time stamps
  that represent the span of time between when the change became
  effective and when the next change becomes effective. The
  end-effective-datetime of a Type 2 dimension record must be exactly
  equal to the begin-effective-datetime of the next change for that
  dimension member.

It's a broad subject to cover but I hope this sets you on the right track for the basics and you can use the correct terminology in the future.

The kimball site is a great resource (and many call it the reference) for dimensional modeling so I suggest you browse the glossary and the design tips section


Your suggested configuration still allows a split-brain situation to occur. If the link between the two datacentres goes down, both arbiters will nominate their local nodes as the primary. The two DCs will continue independently, and will not be able to reform the cluster when the connection is re-established.

The ideal configuration has a single arbiter in a 3rd location. We put ours in the cloud. It's a really good use-case for that.


Oracle materialized view refresh schedule

Your expression can be as complicated as you'd like so you can certainly specify that the refresh should happen every day at 2 AM, i.e.

NEXT trunc(sysdate+1) + interval '2' hour

will specify that the refresh happens every day at 2 AM.  The expression is evaluated at the conclusion of each refresh so you just need to ensure that the expression evaluates to whatever time you want at that particular instant in time.


You might look into MySQL Proxy. Features include:

load balancing
failover
query analysis
query filtering and modification

I haven't run it myself, but it seems to cover what you want.

UPDATE

Apparently the Read/Write splitting is a proof of concept and not for production use


In this case I think you don't need a recursive query, simply get those records that do not have two child records.

select *
from   mytable
where  id not in(select   parent_id
                 from     mytable
                 group by parent_id
                 having   count(*) > 1);

id | parent_id
-: | --------:
 4 |         2
 5 |         2
 6 |         3
 7 |         4
 8 |         5
 9 |         6
10 |         3

dbfiddle here


timeout on updating nvarchar(max) after converting datatype

I'm not sure exactly what the purpose is of your entire-table-update, but perhaps the timeout is because your query is causing:

stats to be created
triggers to be fired
massive writes to the log (and potentially you are waiting on log file auto-grow - instant file initialization doesn't help you here)

If this table is replicated, the database is mirrored, or you've added CDC/Change Tracking then I think it is going to be much worse. If the table is participating in replication, you should read this topic.

What is the update supposed to do? It's too late now for this case but in the future an alternative way to do this for other tables might be:

add a new column with the new data type
populate it in batches with values from the old column, and use transactions or checkpoints to minimize impact to the log (and subsequent features that use the log)
once done, rename or drop the old column, then rename the new column to the old name

Of course this still has to be done in a maintenance window. You don't want users contending for writes during 2.


Postgresql MVIEW refresh from Oracle Materialized View Log

The implementation of materialized views in Oracle and Postgres is different. What you're asking would require a level of integration of Postgres into Oracle's proprietary API - to register as a known client MV so that the log correctly updates itself - that I don't believe exists. It would also require a functioning database link to the Oracle database.

This extension looks like it can help Postgres connect to Oracle; I haven't used it myself, but it looks like it should work (although it also appears to have some limitations): https://pgxn.org/dist/oracle_fdw/

At that point, you would need to query the source tables directly, rather than existing MV Logs. Oracle maintains those logs automatically, truncating them or removing rows when it determines that all registered MVs have refreshed a certain set of changes. If there are no registered MVs, then the logs will grow out of control forever. It won't be long before they severely impact the performance of your updates and the Oracle DB (storage requirements, backups, memory, etc.). There are also situations in Oracle where an entire MV log can become invalid and require a complete refresh of the MV to reset. My point is that without the proper client registration, the data in those logs is pretty much useless, or worse than useless: harmful to maintain.


How can I append new data from a particular mySQL table to an excel spreadsheet?

I've changed my plan, instead of outputting to an excel file I'm going to build a web app that presents the requested data to the user, provides the ability to modify it if required along with the functionality to export to excel or csv format.


Strange behaviour with sample sizes for statistics updates

Background

Data for the statistics object are gathered using a statement of the form:

SELECT
    StatMan([SC0], [SC1], [SB0000])
FROM
(
    SELECT TOP 100 PERCENT
        [SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000]
    FROM
    (
        SELECT
            [TextValue] AS [SC0],
            [Id] AS [SC1]
        FROM [dbo].[Test]
            TABLESAMPLE SYSTEM (2.223684e+001 PERCENT)
            WITH (READUNCOMMITTED)
    ) AS _MS_UPDSTATS_TBL_HELPER
    ORDER BY
        [SC0],
        [SC1],
        [SB0000]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

You can collect this statement with Extended Events or Profiler (SP:StmtCompleted).

Statistics generation queries often access the base table (rather than a nonclustered index) to avoid the clustering of values that naturally occurs on nonclustered index pages.

The number of rows sampled depends on the number of whole pages selected for sampling. Each page of the table is either selected or it is not. All rows on selected pages contribute to the statistics.

Random numbers

SQL Server uses a random number generator to decide if a page qualifies or not. The generator used in this instance is the Lehmer random number generator with parameter values as shown below:

Xnext = Xseed * 75 mod (231 - 1)

The value of Xseed is computed as the sum of:

The low integer part of the (bigint) base table's partition_id e.g.

SELECT
    P.[partition_id] & 0xFFFFFFFF
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND P.index_id = 1;

The value specified in the REPEATABLE clause

For sampled UPDATE STATISTICS, the REPEATABLE value is 1.
This value is exposed in the m_randomSeed element of the access method's internal debugging information shown in execution plans when trace flag 8666 is enabled, for example <Field FieldName="m_randomSeed" FieldValue="1" />

For SQL Server 2012, this calculation occurs in sqlmin!UnOrderPageScanner::StartScan:

mov     edx,dword ptr [rcx+30h]
add     edx,dword ptr [rcx+2Ch]

where memory at [rcx+30h] contains the low 32 bits of the partition id and memory at [rcx+2Ch] contains the REPEATABLE value in use.

The random number generator is initialized later in the same method, calling sqlmin!RandomNumGenerator::Init, where the instruction:

imul    r9d,r9d,41A7h

...multiplies the seed by 41A7 hex (16807 decimal = 75) as shown in the equation above.

Later random numbers (for individual pages) are generated using the same basic code inlined into sqlmin!UnOrderPageScanner::SetupSubScanner.

StatMan

For the example StatMan query shown above, the same pages will be collected as for the T-SQL statement:

SELECT
    COUNT_BIG(*)
FROM dbo.Test AS T
    TABLESAMPLE SYSTEM (2.223684e+001 PERCENT)  -- Same sample %
    REPEATABLE (1)                              -- Always 1 for statman
    WITH (INDEX(0));                            -- Scan base object

This will match the output of:

SELECT
    DDSP.rows_sampled
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.[object_id], S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND S.[name] = N'IX_Test_TextValue';

Edge case

One consequence of using the MINSTD Lehmer random number generator is that seed values zero and int.max should not be used as this will result in the algorithm producing a sequence of zeroes (selecting every page).

The code detects zero, and uses a value from the system 'clock' as the seed in that case. It does not do the same if the seed is int.max (0x7FFFFFFF = 231 - 1).

We can engineer this scenario since the initial seed is calculated as the sum of the low 32 bits of the partition id and the REPEATABLE value. The REPEATABLE value that will result in the seed being int.max and therefore every page being selected for sample is:

SELECT
    0x7FFFFFFF - (P.[partition_id] & 0xFFFFFFFF)
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
    AND P.index_id = 1;

Working that into a complete example:

DECLARE @SQL nvarchar(4000) =
    N'
    SELECT
        COUNT_BIG(*)
    FROM dbo.Test AS T
        TABLESAMPLE (0 PERCENT)
        REPEATABLE (' +
        (
            SELECT TOP (1)
                CONVERT(nvarchar(11), 0x7FFFFFFF - P.[partition_id] & 0xFFFFFFFF)
            FROM sys.partitions AS P
            WHERE
                P.[object_id] = OBJECT_ID(N'dbo.Test', N'U')
                AND P.index_id = 1
        ) + ')
        WITH (INDEX(0));';

PRINT @SQL;
--EXECUTE (@SQL);

That will select every row on every page whatever the TABLESAMPLE clause says (even zero percent).


MongoVUE connect to mongodb with authencation failed!

Your error looks like a standard "auth failed" exception... If all the parameters of your connection are correct for your setup, I'd suggest you try to logon from the mongo shell directly:

$>mongo 1.2.3.4:40000/genbank  -u genBankReader -p passwd1234 --authenticationDatabase admin --verbose

Use the --verbose flag to log on as both the "read-only" user and the "admin" user and see if you can tell more of what's wrong (if any).

Alternative you can use another mongo client GUI like Robomongo and see if that works.
(I used to use MongoVue but switched to Robomongo as it was less confusing and more shell-oriented)

Otherwise, you could have setup/added the users incorrectly. Use the admin user to check db.system.users collection of the proper database just to make sure. Perhaps take another look at http://docs.mongodb.org/manual/reference/privilege-documents/

Hope this helps, good luck


MySQL replication - how to read from slaves? manually?

A good solution is to define two datasources for the applications. One datasource pointing to master is for writing. One datasouce pointing to slave node is for reading. The applications must be aware that  data can be delayed with respect to the master. There are applications that could tolerate a slight delay on data, and other applications that must be sure that read data are fresh. For example data that are updated infrequently can be always read from slave like bills of the previous month. Data that have just been inserted must be read from the master.

A Virtual IP is assigned to the master. Another vip is assigned to the slave. The applications connect to the master and slave using vip. So if master node changes, the vip can be assigned to the new master, and the application don't have to be reconfigured. Using vips is usefull if slave is promoted to master, and master demoted to slave.

If you have more than one slave, a load balancer (aka haproxy) could be put before slaves. The vip for the slave can be assigned to the load balancer. When a connection toward slave is opened the load balancer hijacks the connection toward one of slaves.


Can I refactor this query to get it to run in parallel?

The UDF is preventing parallelism. It also is causing that spool.

You could use CLR and a compiled regex to do your search and replace. It doesn't block parallelism as long as the required attributes are present and will likely be significantly faster than performing 300 TSQL REPLACE operations per function call.

Example code is below.

DECLARE @X XML =
(
    SELECT Names AS [@find],
           Replacement  AS [@replace]
    FROM  dbo.NamesMultiWord
    ORDER BY [WordLength] DESC
    FOR XML PATH('x'), ROOT('spec')
);

UPDATE dbo.Deidentified WITH (TABLOCK)
SET    IndexedXml = dbo.ReplaceMultiWord(IndexedXml, @X),
       DE461 = dbo.ReplaceMultiWord(DE461, @X),
       DE87 = dbo.ReplaceMultiWord(DE87, @X),
       DE15 = dbo.ReplaceMultiWord(DE15, @X)
WHERE  InProcess = 1;

This depends on the existence of a CLR UDF as below (the DataAccessKind.None should mean the spool disappears as well as that is there for Halloween protection and isn't needed as this doesn't access the target table).

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Xml;

public partial class UserDefinedFunctions
{
    //TODO: Concurrency?
    private static readonly Dictionary<string, ReplaceSpecification> cachedSpecs =
                        new Dictionary<string, ReplaceSpecification>();

    [SqlFunction(IsDeterministic = true,
                 IsPrecise = true,
                 DataAccess = DataAccessKind.None,
                 SystemDataAccess = SystemDataAccessKind.None)]
    public static SqlString ReplaceMultiWord(SqlString inputString, SqlXml replacementSpec)
    {
        //TODO: Implement something to drop things from the cache and use a shorter key.
        string s = replacementSpec.Value;
        ReplaceSpecification rs;

        if (!cachedSpecs.TryGetValue(s, out rs))
        {
            var doc = new XmlDocument();
            doc.LoadXml(s);
            rs = new ReplaceSpecification(doc);
            cachedSpecs[s] = rs;
        }

        string result = rs.GetResult(inputString.ToString());
        return new SqlString(result);
    }

    internal class ReplaceSpecification
    {
        internal ReplaceSpecification(XmlDocument doc)
        {
            Replacements = new Dictionary<string, string>();

            XmlElement root = doc.DocumentElement;
            XmlNodeList nodes = root.SelectNodes("x");

            string pattern = null;
            foreach (XmlNode node in nodes)
            {
                if (pattern != null)
                    pattern = pattern + "|";

                string find = node.Attributes["find"].Value.ToLowerInvariant();
                string replace = node.Attributes["replace"].Value;
                 //TODO: Escape any special characters in the regex syntax
                pattern = pattern + find;
                Replacements[find] = replace;
            }

            if (pattern != null)
            {
                pattern = "(?:" + pattern + ")";
                Regex = new Regex(pattern, RegexOptions.IgnoreCase | RegexOptions.Compiled);
            }

        }
        private Regex Regex { get; set; }

        private Dictionary<string, string> Replacements { get; set; }

        internal string GetResult(string inputString)
        {
            if (Regex == null)
                return inputString;

            return Regex.Replace(inputString,
                                 (Match m) =>
                                 {
                                     string s;
                                     if (Replacements.TryGetValue(m.Value.ToLowerInvariant(), out s))
                                     {
                                         return s;
                                     }
                                     else
                                     {
                                         throw new Exception("Missing replacement definition for " + m.Value);
                                     }
                                 });
        }
    }
}


How do I find the creation date of a user in MongoDB?

As at MongoDB 3.2, you are correct that the admin.system.users collection does not maintain metadata such as created or modified timestamps.

You can add metadata manually using customData, but as you noted this requires coordination with any applications or tools that are allowed to create users in your MongoDB deployment:

use admin
db.createUser({
     user: "bobbytables",
     pwd: "CorrectHorseBatteryStaple",
     customData: { created: new Date() },
     roles: [
        { role: "readWrite", db: "accounts" }
     ]
  })

Ideally this sort of metadata would be maintained by the database server in a standard format that tools could also choose to interpret. There is a relevant open feature request that you can watch/upvote: SERVER-10021: Add timestamps to user documents.

If you are using MongoDB Enterprise there are a few extra features that may help you with a workaround in the interim:

You can use an external authentication mechanism that maintains metadata for users, such as Kerberos or LDAP.
You could potentially enable auditing to capture createUser and updateUser events, and write an admin script to update the customData accordingly.


cannot connect to sql server in single user mode

Another alternative to recovering access to a SQL Server is outlined in this article, and doesn't require restarting in single user mode:

http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

Essentially, download PSExec, then run it with the following command line (fill in your location of SSMS.exe):

PsExec -s -i "C:\Program Files (x86)\...\ssms.exe"

You will be prompted to connect - the authentication mode will be Windows auth and hard-coded to NT AUTHORITY\SYSTEM. Once connected, you will be able to go into security and fix the sa password, add yourself as admin, add other admins, etc.

The article above has several caveats you should read before trying this, and you may also want to review the comments at the bottom.


How can I verify that a full database restore reflects the exact source database in SQL Server?

When you take the backup of database, the last LSN on the source will be X. If any activity will occur (including, say, an automated checkpoint), the source LSN will progress forward to X+n. If there is any activity that occurred on the source and was not captured on in the backup it would leave an imprint in the source log, somewhere between LSN X and X+n. Using fn_dblog one can look at the log and see if any such activity occurred.

An easy way to ensure no such activity occurs after backup is to set the database to read_only immediately after taking the backup. To prevent any activity sneaking in between the backup and the change to read_only you can disable connections or set the database in single_user mode, even start the server in single user mode.

So a procedure like following should be pretty much bulletproof:

restart source server in single user mode (-m)
connect and take the backup
change database to read_only
restore the database on the new server
run any tests you have on the new server
connect applications to new server
decommission or restart old server


Why can vectorization reduce the use of virtual functions?

Glancing through the paper, it sounds like the answer is as simple as this:

for "row-based" (non vectorized) operations, the (virtual) functions used to process query results have to be called once per row.
for "batch-based" (vectorized) operations, the (virtual) functions are called once per batch, and then optimized loops can be run inside the virtual function implementation (reducing virtual function overhead by virtue of just calling those functions less times)

The is mostly covered in the quote you mentioned

  ...virtual functions implementing block-wise operations handle thousands of tuples per function call...

Calling a function once, to handle thousands of rows, is going to much cheaper than calling the function thousands of times, once for each row.

In any program, there is overhead to calling functions, especially virtual functions (looking up the actual function implementation's address in a lookup table, missing out on certain compile-time optimizations because the actual implementation function isn't known until runtime, etc - see In C++ why and how are virtual functions slower? for more discussion on virtual function overhead).

Looking to SQL Server's implementation of batch-based query processing (I realize the paper is about HyPer, but the general principles hold), you can find other sources discussing the per-row vs per-batch cost savings in terms of CPU.  Consider this post:

Columnstore Index Performance: BatchMode Execution

It compares row based processing:

  Query was executed in ROW mode. The predicate was applied to 3580 rows, one row at a time, to get 1200 qualified rows
  ...The actual CPU cost was 15 ms...

To batch based processing:

  The query (specifically SCAN operator) was executed in BatchMode. There were two batches, each containing 900 rows, were executed. The predicate was applied to 900 rows at one time
  ...the actual execution time is < 1 ms (the minimal time that can be measured)...

Processing query results in batches thus reduces CPU costs in SQL Server for a number of reasons, but virtual function call overhead is likely one of them (although it's probably less significant than, say, operating on compressed data, applying predicates per batch, calculating aggregates per batch, etc).


I managed to find the problems.

One was I had an installed PostgreSQL server on my machine that I was unaware of. It seems I installed it with my pgAdmin install. So every time I connected to my server, I would establish a connection to my localhost server and not my remote server. I just uninstalled the server and installed only the pgAdmin client.

The second problem I had was that the file /etc/postgresql/9.5/main/pg_hba.conf had to be changed. So I ran:

sudo vi /etc/postgresql/9.5/main/pg_hba.conf

and changed the line

# Database administrative login by Unix domain socket
local    all    postgres    peer

to

# Database administrative login by Unix domain socket
local   all     postgres     md5

Once that was changed, I had two restart the configuration by executing:

sudo /etc/init.d/postgresql reload

I would also point out that it is important to have the postgres user as a unix and DB user with same passwords. I found all this information here.