Is unique index better than unique constraint when an index with an operator class is required Postgres docs say:

  The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Based on this, if I want an expression index on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a lower case index?

As @Colin'tHart pointed out, these 2 cases aren't the same. I should have posted this question without the use of lower() expression. In that case, my understanding is that a CREATE UNIQUE INDEX would be better than a unique constraint and a simple index.

Based on this, if I want an index with an operator class (e.g. text_pattern_ops) on a column and also want that column to be unique, would case 2 below be better since it can accomplish the above with a single index. Whereas case 1 would have an index created automatically because of a unique constraint and another one because I need a different operator class?

Case 1:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   CONSTRAINT book_name_key UNIQUE (name)
);

CREATE INDEX book_name_like ON book (name text_pattern_ops);

Case 2:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE UNIQUE INDEX book_name_like ON book (name text_pattern_ops);

For a moment I thought one might be able to use a pre-existing  text_pattern_ops index with the USING INDEX clause when adding a UNIQUE CONSTRAINT. But that fails, because:

ERROR: index "book2_name_like" does not have default sorting behavior

Per documentation:

  The index cannot have expression columns nor be a partial index. Also,
  it must be a b-tree index with default sort ordering. These
  restrictions ensure that the index is equivalent to one that would be
  built by a regular ADD PRIMARY KEY or ADD UNIQUE command.

For instance, a unique index like that would allow a FK constraint referencing it, but perform terribly, because it does not support standard operators.
Per documentation:

  Note that you should also create an index with the default operator
  class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index.

So to answer the question:

If you need a UNIQUE CONSTRAINT (among other reasons: to reference it with a FK), your first variant with constraint and index is the only option. Additionally, the default operator class of the index created by the constraint supports more operations (like sorting in default sort order).

If you don't need any of that go with your second variant because, obviously, just one index is cheaper to maintain: just a UNIQUE text_pattern_ops index.

Differences between index and constraint:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

Alternative with COLLATE "C"

Instead of creating two indexes, there is another alternative for xxx_pattern_ops indexes that may be preferable. The documentation:

  The difference from the default operator classes is that the values
  are compared strictly character by character rather than according to
  the locale-specific collation rules. This makes these operator
  classes suitable for use by queries
  involving pattern matching expressions (LIKE or POSIX regular
  expressions) when the database does not use the standard "C" locale.

And:

  The index automatically uses the collation of the underlying column.

You can create the column without collation (using COLLATE "C"). Then the default operator class behaves the same way as text_pattern_ops would - plus the index can be used with all the standard operators.

CREATE TABLE book2 (
   book_id serial PRIMARY KEY,
   book    text NOT NULL COLLATE "C" UNIQUE  -- that's all!
);

Now, LIKE can use the index:

SELECT * FROM book2 WHERE book LIKE 'foo%';

But ILIKE still can't:

SELECT * FROM book2 WHERE book ILIKE 'foo%';

SQL Fiddle.

Consider a trigram index using the additional module pg_trgm for a more general solution.:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


4m Rows in MySQL, ~5 Minutes to Run Query I have 2 tables, a users table and a log table which literally logs each time a user signs in.

What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a year. That said, I have a working query, but it's taking ~5 minutes to execute.

Here's what I'm currently working with:
SQLFiddle

SELECT u.usrid, u.username, u.rank, u.extras, l.dtime
FROM users AS u
JOIN (
    SELECT userid, MAX(datetime) dtime
    FROM log
    GROUP BY userid
) AS l
ON u.usrid = l.userid
WHERE (u.rank = 'P' OR u.extras LIKE '%W%')
AND l.dtime < DATE_SUB(NOW(), INTERVAL 1 YEAR)
ORDER BY l.dtime DESC

It's a part of a very old and soon to be updated system... But until then, I'm trying to make the best of what I've been given to work with. That being said, I don't have access to actually alter the database schema so I'm hoping there is a way to optimize this query to work better.

Furthermore, the users table has ~500k rows while the log table has ~4m rows. With that many rows, I'm also wondering if it's a size issue. If so, what suggestions could I pass on to the DBA in order to improve it's ability to scale? I know I could simply store the last log in date somewhere separate for this particular use-case; but we use that log data for a lot of things so we need a way to be able to search it efficiently.

SELECT  u.usrid, u.username, u.rank, u.extras, x.dtime
    FROM
      ( SELECT  userid, MAX(datetime) dtime
            FROM  log
            GROUP BY  userid
            HAVING  dtime < NOW() - INTERVAL 1 YEAR
      ) AS x
    JOIN  users u ON u.usrid = x.userid
    WHERE  ( u.rank = 'P'
              OR  u.extras LIKE '%W%'
           )
    ORDER BY  x.dtime DESC

Plus

 INDEX(usrid, datetime) -- in `log`

(I assume usrid is the PRIMARY KEY in users.)

Is size an issue?  Yes and no.  With the right indexes and queries, etc, billion-row tables can work fine.  In your case, it is dubious...  The subquery will probably produce 500K distinct users from its GROUP BY.  Then the HAVING will shrink the number.  What's left needs to be looked up in users and further filtered.  Finally another sort (ORDER BY) before delivering the results.

I think that newer versions of MySQL will leapfrog through the INDEX I suggested, landing on only 500K index rows, not all 4M.

If you think the rank and extras do a better filtering than MAX(datetime), then a different approach might be faster.


Query to List Encryption Certificate for Databases What certificate is being used to encrypt each of the databases on the instances.

I can get the data using the following but how do I write the queries

USE master
GO

-- this provides the list of certificates
SELECT * FROM sys.certificates

-- this provides the list of databases (encryption_state = 3) is encrypted
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

I noticed that columns sys.certifcates.thumbprint and sys.dm_database_encryption_keys.encryptor_thumbprint contain the same data.

You can join on the certificate thumbprint:

use master;
go

select
    database_name = d.name,
    dek.encryptor_type,
    cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

My sample output:

database_name           encryptor_type    cert_name
=============           ==============    =========
tempdb                  ASYMMETRIC KEY    NULL
AdventureWorks2012TDE   CERTIFICATE       TdeCert


archive command repeatly failing for one particular file I am seeing many pg_log files full of following errors

2019-07-08 00:04:06 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST LOG:  archive command failed with exit code 1
2019-07-08 00:04:07 CEST DETAIL:  The failed archive command was: copy "pg_xlog\000000010000002100000025" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/000000010000002100000025"
2019-07-08 00:04:07 CEST WARNING:  transaction log file "000000010000002100000025" could not be archived: too many failures
2019-07-08 00:05:07 CEST LOG:  archive command failed with exit code 1**

postgres.conf having settings

checkpoint_segments = 256
wal_buffers = 256MB
wal_level = archive
archive_mode = on
port = 7112
work_mem = 128MB
shared_buffers = 512MB
effective_cache_size = 32719MB
listen_addresses = '*'
logging_collector = on
log_line_prefix = '%t '
archive_command = 'copy "%p" "D:/Programme/OmniBack/server/db80/pg/pg_xlog_archive/%f"'**

I have checked pg_xlog folder for this file. This file is not present in pg_xlog but in pg_xlog\archive_status with following permissions

pg/pg_xlog\archive_status\000000010000002100000025.ready

KDLF\adminkdlf:(I)(F)
VORDEFINIERT\Administratoren:(I)(F)
NT-AUTORITŽT\SYSTEM:(I)(F)
VORDEFINIERT\Benutzer:(I)(RX)

My disk is getting full. Please tell why this issue is coming because of this others logs are still not archived. I am not sure if I am missing something here.

If the file is not in pg_xlog, but there is a .ready file in archive_status, then somebody must have removed that file, which is why archival is failing.

Temporarily set archive_command = '/bin/true' until the view pg_stat_archiver indicates that PostgreSQL is happy again, then set the parameter back to its original value.

Perform a new base backup immediately after that.

You won't be able to recover across the point where you have no WAL archives.

Try to find out which of your administrators is stupid enough to delete files from the data directory and send him or her to a PostgreSQL course.


Effective dates, complex requirements I have a table with, say, preferred colors.
For every person, we store the favorite color and the effective date. If the person changes preference, we store the new one with a newer effective date.
Example:

name   color   eff_date
John   Green   2014-03-07
Luis   Red     2014-03-07
Luis   Yellow  2015-05-02
Nina   Blue    2015-07-06

If we want to take the current preferred color of Luis:

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date=( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' )

Now we decide that persons can change their favorite color anytime, but the change will only be effective from 1st January. So even if Luis entered a record that says that his favorite color is Yellow, the query must return the old value (Red) until 1st January 2016.

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date=( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' and eff_date < '1 jan 2015' )

This query seems ok, but will not return any record for Nina. The rules say that a newcomer can choose the favorite color immediately, without waiting for next 1st January.
In other words, "if possible, take a record from 1st January or earlier; if not possible, any record; take the most recent".
This is what I was able to write:

SELECT color
  FROM fav_colors
 WHERE name='Luis'
   AND eff_date= ISNULL(
       ( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' and eff_date < '1 jan 2015' ),
       ( SELECT max(eff_date) FROM fav_colors WHERE name='Luis' )
       )

Is there a more elegant, or more efficient, way to achieve this result?

I have no control on the tables structure, they are from PeopleSoft.

If Nina made two selections this year I want to show the most recent one.

I explain how to do this here: http://blogs.lobsterpot.com.au/2014/07/08/ssis-lookup-transformation-in-t-sql/

The basic concept is to use OUTER APPLY with TOP (1). In your case you may need to start with your list of people that you're looking up. Like this:

SELECT p.Person, ISNULL(c1.color, c2.color) AS color
FROM (VALUES ('Nina')) AS p(Person)
OUTER APPLY
    (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND f.eff_date <= @date
    ORDER BY f.eff_date DESC) c1
OUTER APPLY
   (SELECT TOP (1) f.color
    FROM fav_colors AS f
    WHERE f.name = p.Person
    AND c1.color IS NULL
    ORDER BY f.eff_date ASC) c2

The post details how the Query Optimizer will only do the second lookup if the first one fails. It's really very efficient with the right index in place (on (name, eff_date) include (color)).

Oh - if color can be NULL legitimately, then test a different column in the c2 sub-query.


Stored Procedure Performance Issue in Sharepoint Report We have a report stored procedure that runs in about 1 minute in production; however, when executed from the Sharepoint it populates, it rus for over 20 minutes, with exactly the same parameters.  We've done everything to avoid parameter sniffing problems: used WITH RECOMPILE on the procedure declaration; added OPTION OPTIMIZE FOR UNKNOWN on the parameters within queries; even created local variables and assigned the parameter values to them and used them throughout the procedure.  None of these has had any effect.  I know that the 1-base minute run time is a big red flag, and we're working on pre-aggregating this data into our BI platform, but that's months down the liine and this is needed for month-end close every month.

Other relevant information:
Sharepoint 2013 on SQL Server 2012
Data Source is a SQL Server 2008 R2 database
Report definition created in Visual Studio 2008

Your Sharepoint db instance will have a ReportingService_weirdsequenceofcharacters database. It stores report execution and performance metrics. There is a view called ExecutionLog2 in that database which has 3 Times.
Use them to find whether the bottleneck is sql, report processing, or rendering. You should also be able to compare these values to the values from your production report server (if you have one). A non-sharepoint report server database will typically be named ReportServer.

TimeDataRetrieval

The number of milliseconds spent interacting with data sources and data extensions for all data sets in the main report and all of its subreports. This value includes:

Time spent opening connections to the data source
Time spent reading data rows from the data extension

Note: If a report has multiple data sources/data sets that can be executed in parallel, TimeDataRetrieval contains the duration of the longest DataSet, not the sum of all DataSets durations.  If DataSets are executed sequentially, TimeDataRetrieval contains the sum of all DataSet durations.

TimeProcessing

The number of milliseconds spent in the processing engine for the request.  This value includes:

Report processing bootstrap time
Tablix processing time (e.g. grouping, sorting, filtering, aggregations, subreport processing), but excludes on-demand expression evaluations (e.g. TextBox.Value, Style.*)
ProcessingScalabilityTime (see AdditionalInfo column)

TimeRendering

The number of milliseconds spent after the Rendering Object Model is exposed to the rendering extension.  This value includes:

Time spent in renderer
Time spent in pagination modules
Time spent in on-demand expression evaluations (e.g. TextBox.Value, Style.*). This is different from prior releases, where TimeProcessing included all expression evaluation.
PaginationScalabilityTime (see AdditionalInfo column)
RenderingScalabilityTime (see AdditionalInfo column)

http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx


SSMA from oracle xe to sql server express PK violation I'm using SSMA for the first time trying to migrate an Oracle XE schema to SQL Server Express. The majority of the tables get created and their data migrated just fine. There are a handful of tables though that keep giving me primary key violation.

It tells me the duplicate key value, but when I query the Oracle table for that key there is only 1 record (which makes sense since it has the PK on it).

I'm trying to figure out what I could be missing here because this can't be the real error. I mean the oracle table has the PK on it so it can't, and doesn't, have a duplicate value. So why is SSMA giving me seemingly a false error on this when clearly the source oracle table doesn't have this duplicate key in it?

I figured it out. The difference was casing of the varchar PK. Oracle sees casing as different but SQL must not. There was some value with a capital 'S' and a lowercase 's' where everything else was the same. That's interesting.


Why is innodb strict mode not enabling? I tried adding this line in /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu)

innodb_strict_mode = 1

This is within the [mysqld] section, where I have seen this line in other examples. I know this config file is being read at startup because I get errors if I type in nonsense.

Nevertheless, SHOW VARIABLES lists this variable as OFF and apparently I can insert rows without specifying my non-default fields.

Why isn't this variable being set?

I have tried =on instead of =1, no difference. The config is otherwise unmodified from the installation default, AFAIK, full text here.

You can set this SESSION variable from the command line:

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | OFF   |
+--------------------+-------+
1 row in set (0.01 sec)

Then, you do this:

mysql> set innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

Then, to check:

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+

Then, on exiting and reconnecting, and rechecking the variable, it is again OFF - the default. Use SET GLOBAL innodb_strict_mode = ON if you don't want it to reset after logging off. But, setting it in the client will not make the change stick after a reboot of the server.

Edit my.cnf - I've never seen or touched mysqld.cnf.

Put the line(s) below in the [mysqld] section of my.cnf.

sql_mode    = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"
innodb_strict_mode = ON

(I recommend the sql_mode line also - otherwise, for example, certain GROUP BY queries will return (ahem...) anomalous results).

Bounce the server - and relog in.

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

It's all in the documentation here.

  You can turn innodb_strict_mode ON or OFF on the command line when you
  start mysqld, or in the configuration file my.cnf or my.ini. You can
  also enable or disable innodb_strict_mode at runtime with the
  statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is
  either ON or OFF. Changing the GLOBAL setting requires the SUPER
  privilege and affects the operation of all clients that subsequently
  connect. Any client can change the SESSION setting for
  innodb_strict_mode, and the setting affects only that client.

Maybe you don't have the SUPER privilege? Log in and run SHOW GRANTS to find out.


Connect to sql server with windows authentication in different domains I get we have two options while connecting to remote SQL Servers or other domains with Windows authentication:

runas /netonly method
adding windows credentials

But doesn't Windows authentication mean that the user who logged in to Windows has authentication to use SQL Server?

So since I am not an authorized login or user in remote servers, I mean I am not a Windows user on their machine, how could I connect to their servers?

So everyone who knows my domain name and instance name can connect to my machine.

What about security? Do we have to add other Windows users or machines to connect to our SQL Server?

I believe we must add other machines to our domain or we must add other domains as trusted to our domain.

Please help; what is the point I missed?

When you login to a computer you can do so in the following manner:

login to the computer with a user that is stored on the computer (e.g. COMPUTER_1\USER_3)
login to the computer with a user that is stored in the active directory of the domain you are using (e.g. DOMAIN_1\USER_1)

When the login is complete you have verified the authenticity of your account (USER_n) against either the computer (COMPUTER_1) or against the Active Directory domain (DOMAIN_1).

Environment

Now let's say we have the following information based on your questions:

first domain user account USER_1
second domain user account USER_2
first computer user account USER_3
second computer user account USER_4
first computer COMPUTER_1
second computer COMPUTER_2
first server SERVER_1
second server SERVER_2
first domain DOMAIN_1
second domain DOMAIN_2

Next I am assuming the following:

USER_1, COMPUTER_1 and SERVER_1 are members of the DOMAIN_1
USER_2, COMPUTER_2 and SERVER_2 are members of the DOMAIN_2
USER_3 is a user account on COMPUTER_1
USER_4 is a user account on COMPUTER_2

Scenario 1:

You login to COMPUTER_1 using the account USER_3 (computer user account). When you try to connect to the SERVER_1 using Windows Authentication you are denied access.

Why?

The COMPUTER_1\USER_3 local Windows account cannot be added to the SQL Server as a login on SERVER_1. Only SERVER_1\USER_n accounts could be added as SQL Server Logins to SERVER_1 or DOMAIN_1\USER_n accounts. SQL Server is unable to find COMPUTER_1\USER_n accounts when creating Windows Authenticated SQL Serer Logins.

Scenario 2

You login to COMPUTER_1 using the account DOMAIN_1\USER_1 (Domain user account). When you try to connect to the SQL Server SERVER_1 using Windows Authentication you are granted access.

Why?

DOMAIN_1\USER_n accounts can be added as Windows Authenticated SQL Server Logins to the SQL Server instance running on SERVER_1 and then granted access to the SQL Server. SQL Server will determine that you (DOMAIN_1\USER_1) are a valid Domain user and will allow you to connect to the SQL Server (assuming you have previously assigned these permissions to the account on your SQL Server).

Scenario 3

You login to COMPUTER_1 using the account DOMAIN_1\USER_1 (Domain user account). When you try to connect to the SERVER_2 (remember server is in DOMAIN_2) using Windows Authentication you are most likely denied access.

Why?

DOMAIN_1\USER_n accounts cannot be added as SQL Server Logins to the SQL Server instance running on SERVER_2. Unless you have a trust between the domain DOMAIN_1 and DOMAIN_2. The SQL Server instance on SERVER_2 will be unable to verify that you (DOMAIN_1\USER_1) is a valid Domain login and will deny you access SQL Server.

Summary

When you login to a computer you have only verified your authenticity towards either the computer or to the domain the computer belongs to. Any other permissions (SQL Server access, Windows Server access) depend on the rights assigned to your account either in the domain or on the target system. If a system is out of scope of the DOMAIN_1 or SERVER_1, then you will be unable to access these servers with Windows Authenticated SQL Server logins.

If a user is trying to connect to a SQL Server in a non-domain environment then you will possibly be better off with dedicated Native SQL Server Logins instead of Windows Authenticated SQL Server Logins. When you want to connect to the SQL Server you will have access via the SQL Server login.

This is a very short basic summary of how authentication works.


Stored procedure - problem with merge - Insert I am calling a stored procedure from my c# code . The procedure works fine for update, but fails for Insert. Is the following merge-insert procedure code correct?

Any help is appreciated.

Here is the procedure:

CREATE TYPE TrackType AS TABLE
(
t_Id uniqueidentifier,  t_JobID  nvarchar(50), t_CreatedDate datetime2(7),
t_ModifiedDate datetime2(7), t_DbDate    datetime2(7)
t_SubGUID nvarchar(MAX), t_eType nvarchar(MAX)
);
GO

ALTER PROCEDURE [dbo].[Process_NO_table]
@track       TrackType READONLY
AS
// i need to iterate all the rows of the table(over a million)

Declare @rows INT
Declare @i int = 0
Declare @count int = (SELECT COUNT(*) FROM @track)
DECLARE @is INT

  while (@i < @count)
BEGIN

// first i check modified date from the database table
   SELECT  @is = COUNT(*) FROM NO_table WHERE [JobID] IN (SELECT [t_JobID] FROM
@track)
MERGE [dbo].[NO_table] AS [Target]
USING @track AS [Source]

// if the database modifed date is less than the modifeid date from the
proceduretable(@track) then it updates the records
ON [Target].[ModifiedDate] < [Source].[t_ModifiedDate] AND JobID = t_JobID
WHEN MATCHED THEN
    UPDATE  SET [JobID] = [Source].[t_JobID],
        [CreatedDate] = [Source].[t_CreatedDate]
  [DbDate]= [Source].[t_DbDate]
 [ModifiedDate] = [Source].[t_ModifiedDate]
[SubGUID] = [Source].[t_SubGUID]
 [eType] = [Source].[t_eType]

// if there no records with the same jobid then it creates-insert a records
MERGE [dbo].[NO_table] AS [Target]
USING @track AS [Source]
ON (@is != 0)
WHEN NOT MATCHED THEN
   INSERT INTO [NO_table] ( [JobID], [CreatedDate], [ModifiedDate], [DbDate],
[SubGUID], [eType]  )
VALUES ( [Source].[t_JobID], [Source].[t_CreatedDate], [Source].[t_ModifiedDate],
[Source].[t_DbDate], [Source].[t_SubGUID], [Source].[t_eType] )
;

SET @i = @i + 1

END

GO

How about simplifying greatly:

UPDATE d SET
  [CreatedDate] = [Source].[t_CreatedDate],
  [DbDate]= [Source].[t_DbDate],
  [ModifiedDate] = [Source].[t_ModifiedDate],
  [SubGUID] = [Source].[t_SubGUID],
  [eType] = [Source].[t_eType]
FROM dbo.NO_table AS d
INNER JOIN @track AS [Source]
ON d.JobID = [Source].t_JobID
WHERE d.[ModifiedDate] < [Source].[t_ModifiedDate];

INSERT dbo.NO_Table(...cols...)
  SELECT ...cols...
  FROM @track AS t
  WHERE NOT EXISTS (SELECT 1 FROM dbo.NO_Table
    WHERE JobID = t.t_JobID
    AND [ModifiedDate] >= [Source].[t_ModifiedDate]);

Now you have two set-based operations instead of over a million singletons, and you avoid doing silly things like counting rows when you only care if it the result is zero or non-zero. And you avoid the overkill of MERGE for this simple scenario (and a bunch of other things that come with it).


Preserving whitespace in MongoDB values In a simple scenario of me inserting a key-value pair, when value contains some white space, how can I preserve it?

Example:

> > db.bar.insert({"name":"mike    "})
> > db.bar.find() { "_id" : ObjectId("5080c03b8410d443d9acb7f5"), "name" : "mike " }

A bit of testing shows that the data is being inserted correctly, but the shell is trimming the field when it displays it.  You can test this as follows:

mongos> db.test.insert({"name":"    adam    "})
mongos> db.test.find({"name":"    adam    "})
{ "_id" : ObjectId("50814e5c2a17d922cd25974f"), "name" : " adam " }
mongos> db.test.find({"name":" adam "})
mongos>

That is, the data is found when you query with multiple spaces but not found when you re-use what the shell displays. So this is just a display issue with the shell, the DB is behaving as it should behind the scenes.  Also tested this with a driver and it behaved correctly.

Even though this means that the data is the way you want, it is still technically a bug, because it could be confusing.  So I filed a bug here:

https://jira.mongodb.org/browse/SERVER-7424

Finally, to make this print out correctly, try this (assuming the same data I used in my test):

mongos> var fix = db.test.find({"name":"    adam    "}).next()
mongos> printjsononeline(fix)
{  "_id" : ObjectId("50814e5c2a17d922cd25974f"),  "name" : "    adam    " }


Maximum number of databases for single instance of PostgreSQL 9 Developing a multicustomer application we plan to use a different database for each customer.
But it could be more than 1000 customers (applications).

Will PostgreSQL handle it without any problems?

Has anybody tried something similar?

Note:  35 tables for each one, with up to 3000 records as an average, for each database.

I haven't tried it myself, but there are others around who have.
Here you can see that even 10,000 databases run without problem on a single instance.  You can even find some pratical aspects on ServerFault as well.

Since your databases are quite small, you will not run into any sort of file number limitation of the host OS.  The only problem I can think of is that when all these databases will be accessed concurrently, handling all those connections will be tricky.

And, as a last note: you are very welcome on this site.  We hope you will remain with us for a long time.


Failover Cluster Owners In Windows Server Failover Cluster Manager I can see "possible owners" under "Other Resource" and also under "Server Name".

I have a 3 node AlwaysOn Availability Group with 2 servers in synchronous mode and 1 in Asynchronous Mode for disaster recovery.

Under the possible owners under "Other Resource" I can see the ones in Synchronous mode is checked but under the 'Server Name' all are checked.

Will appreciate if any one can tell me the difference between possible owners listed under "Other Resource" versus those listed under "Server Name".

The names you're seeing under "Other Resources" are actually the SQL Server instance names, not the names of the nodes that could be possible owners.

"Possible Owners" are the machines that are eligible to run the Roles configured in the cluster.  For SQL Server, Roles are the SQL Server instance, the SQL Server Agent service, etc.

"Other Resources" are items required to run each role.

The official Microsoft Docs on Failover Clustering with SQL Server is a great resource for understanding how that all ties together.

This MSDN blog talks about preferred and possible owners, and shows how SQL Server manages them automatically.

This Microsoft Docs page talks about the relationship between AlwaysOn and Failover Clustering.  This section of the above page says:

  Do not use the Failover Cluster Manager to manipulate availability groups, for example:

Do not add or remove resources in the clustered service (resource group) for the availability group.
Do not change any availability group properties, such as the possible owners and preferred owners. These properties are set automatically by the availability group.
Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime. You must use Transact-SQL or SQL Server Management Studio.


Slow MySQL query with Order and Group by I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query:

SELECT `user_articles`.*
FROM `user_articles`
INNER JOIN `articles` ON (`user_articles`.`article_id` = `articles`.`id`)
GROUP BY `articles`.`id`
ORDER BY `user_articles`.`created_at` DESC

I have found that by removing the ORDER BY statement it speeds it up (0.003s). These are the results from the EXPLAIN

+----+-------------+---------------+--------+---------------+------------+---------+-------------+-------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys | key        | key_len | ref         | rows  | Extra                                        |
+----+-------------+---------------+--------+---------------+------------+---------+-------------+-------+----------------------------------------------+
| 1  | SIMPLE      | articles      | index  | PRIMARY       | PRIMARY    | 4       | NULL        | 22678 | Using index; Using temporary; Using filesort |
| 1  | SIMPLE      | user_articles | ref    | article_id    | article_id | 4       | articles.id | 1     | Using where                                  |
+----+-------------+---------------+--------+---------------+------------+---------+-------------+-------+----------------------------------------------+

Is there any way I can speed up my query?

As @Back in a Flash has suggested, an index on created_at in user_articles will probably help if the optimizer chooses to use it... but the optimizer might not catch on to the viability of that index, because you're doing something else that needs an explanation.

You're using GROUP BY but you're not aggregating anything that I can see.  If you're not looking for the MIN(), MAX(), SUM(), COUNT(), or the result of some similar aggregate function, then your use of GROUP BY here is incorrect.  By default, MySQL does not throw an error when GROUP BY is used like this.

It looks as if multiple rows in user_articles could reference the same row in articles.  Your query, as written, will only return one matching row from user_articles for each referenced row in articles, and the selection of that row will not be deterministic.

That doesn't seem like what you want to be doing.  If it is, you shouldn't be doing it this way. :)

If it isn't... or if each row in user_articles references a unique row in articles, then your query is unnecessarily burdening the optimizer by insisting that this is indeed what it wants the server to try to do.  As the query is written, the GROUP BY is superfluous at best, and possibly contributing to the performance problem, since, even if you had an index on created_at, that conflicts with the fact that the server also needs to GROUP BY (potentially) a different index.


Percona XtraDB Cluster: How to skip SST when starting? Is there a way to skip State Snapshot Transfer (SST) at startup on Percona XtraDB Cluster Server?

wsrep_sst_mode=skip doesn't work:

121003  6:49:55 Percona XtraDB (http://www.percona.com) 1.1.8-rel28.1 started;
    log sequence number 82208067084
121003  6:49:55 [ERROR] /usr/sbin/mysqld: unknown variable 'wsrep_sst_mode=skip'
121003  6:49:55 [ERROR] Aborting

I'm using Percona-XtraDB-Cluster-server-5.5.27-23.6.356.rhel5.

I think you want wsrep_sst_method=skip not wsrep_sst_mode=skip

mysql> show variables like 'wsrep_sst%';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| wsrep_sst_auth            |            |
| wsrep_sst_donor           |            |
| wsrep_sst_method          | xtrabackup |
| wsrep_sst_receive_address | AUTO       |
+---------------------------+------------+
4 rows in set (0.00 sec)

mysql>


MongoDB 3.0 release for Cent OS Can any one tell me when the MongoDB team will release 3.0 production release Also, will it be available for CentOS?

We are shifting our production boxes from RHEL 6 to CentOS and on the MongoDB download page 3.0.0 rc does not show CentOS in the distribution list.

At present ( meaning as of writing ) there really isn't an actual "official release date" for MongoDB 3.0 or is there "really" an official source for the repository of binary builds for any platform.

Results vary for each distribution, but in this case you are probably best following the official documentation links and changing the version in the required "drop-down" to "3.0 (upcoming)" (and again, as of writing). The "repo" location links have been consistently changed over most OS distributions as of the 3.0 pending release. Url's in the documentation likely need "massaging".

Right now the RHEL/CentOS repo for current is here: http://repo.mongodb.org/yum/redhat/7/mongodb-org/testing/x86_64/RPMS/

Following that you can install a "binary build" of the "current release candidate" for your own usage. Failing that then just go for something like: https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-rhel70-3.0.0-rc11.tgz and deploy according to your platform mapping of locations and configurations.

When it's released then it will be released. Contact your local MUG for more info if someone is prepared to give it to you. Otherwise just live with "Soon".


ssl with postgresql 12 I tried following the docs for the same.
It says postgres.conf will have service.crt for ssl_cert_file, but it has /etc/ssl/certs/ssl-cert-snakeoil.pem
and now I can't proceed with it.

Any insight or tutorial will be helpful.

Simple. Change the parameter ssl_cert_file to point to your server certificate and ssl_key_file to the private key that belongs to it.

If the private key requires a pass-phrase, you also have to set ssl_passphrase_command appropriately.

To validate client certificates, put the certification authority certificate in ssl_ca_file.


Restrict Postgres superusers to local only connections I have a database with several users: foo, bar, and super. I was looking at pg_hba.conf to lock down access.

I'd like to allow foo and bar to be able to connect from any IP with password authentication. Or locally with trust authentication.

I'd like to allow super to only be able to login locally with trust authentication. Never from a non-local address.

I started with this.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow everything if you're on the same machine.
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

But, then I got stuck because I'm not sure how to say "all users, except super". I'm looking for something like this.

host    all             not_super       all                     md5

One approach I thought I could take is to list foo and bar explicitly. But, I'm wondering if there is another way.

host    all             foo             all                     md5
host    all             bar             all                     md5

You'll have to enumerate the superusers:

host   all  superuser1   0.0.0.0/0   reject
host   all  superuser2   0.0.0.0/0   reject
host   all         all   0.0.0.0/0      md5

There should be a very limited, fairly constant set of superusers.


Architectural Question about Data analytics I understand that in many OLTP architectures there is some ELT stuff which creates a OLAP Data warehouse model and then you can run data analytics on this.  However, this can take some time to generate.  Suppose you need to run your data analytics immediately, is there another well known pattern or approach for this?

Many thanks.

Have your OLAP structures already in place (star schemas, fact and dimension tables).
Populate your OLAP  structures with a feed from your OLTP systems.  This can take quite a while the first time through but this feed is where the transformations happen.  Once all of the data has been transformed and loaded into the fact and dimension tables, you use the same logic to run but only bring in new or updated data.  This is a trickle update.
Once your data is in the OLAP structure, build your cubes.  Only process the updates if you can.

The biggest mistake I see is people relying on tools and wizards to accomplish this.  They end up importing all historical data every day and completely rebuilding their cubes from scratch.  They are the ones that also complain that it takes hours and hours for their data to be ready for reporting.

Wizards will only get you so far.  If you are planning on building a reliable robust reporting system, you have to get your hands dirty and start writing some SQL code.


Different Disk Drive Same Backend Storage I have a database running on a VM which is getting hammered during a large load, in particular I can see waits of WRITELOG happening.  My initial thought is to split the files out on to their own drives but the backend storage is the same as where the other DB files are sitting.

Basically it is SAN presented as a Cluster Shared Volume to a whole host of virtual machines.

Would there be a performance advantage in doing this?  Some memory in the depths of my brain is telling me something about the number of IO streams would be better potentially?

To update this I have now separated out the files and correctly sized the transaction log.  I have been collecting information from sys.dm_io_virtual_file_stats and can see that I now have extremely high readIOStalls but with a low latency of 13ms.  I also collected some memory information and PLE was in the thousands on average with this being a 32GB system I would expect that apart from in one 30min period where it drops right down to 30 before climbing sharply again, at this time lazy write/sec increases to 50 also before reducing to 0.   Could this period be the cause of the large number of read stalls I am seeing?  I would have expected to see with such high Read Stalls also high latency?

This will depend on your exact SAN. But in a former job we had a SAN with auto-tiering. In this case, in theory you can divide up your database into files and the most active files would automatically be moved to flash storage by the SAN


Configure sp_add_schedule to run on the first day of every X months/weeks I need to configure my jobs scheduling dynamically.

The jobs will always run on the first day of the week once every X weeks. Same for months.

Looking at the documentation,

I see that for weeks option I would need to use freq_type=8 (weekly) with freq_interval=1 (Sunday). But which parameter should be used to mark that the repetition should be once every X weeks?

I believe @freq_recurrence_factor is the parameter you are looking for.


Adding a ROW_NUMBER() with no column to ORDER BY? So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order.

Let's say my source data is:

SELECT value
FROM STRING_SPLIT('one,two,three,four,five', ',')

which returns the items in the original (desired) order:

value
-----
one
two
three
four
five

If I try to use ROW_NUMBER() or RANK() I'm forced to specify an ORDER BY, for which value is the only legal choice:

SELECT value, n = ROW_NUMBER() OVER(ORDER BY value)
FROM STRING_SPLIT('one,two,three,four,five',',')

But this (as expected) sorts value alphabetically instead of leaving it in the desired original order:

value   n
------ ---
five    1
four    2
one     3
three   4
two     5

Joining to a number table doesn't work, since without a WHERE clause I'll get a full outer join.

About the best I could come up with was using a temp table with an identity field:

CREATE TABLE #argg (n INT IDENTITY(1,1), v VARCHAR(99))

INSERT #argg
SELECT value v
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT *
FROM #argg

DROP TABLE #argg

but this is really long and annoying. Any better ideas?

The canonical way to do this is the following: ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). If you're golfing, you might try something like this:

SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT('one,two,three,four,five',',')

It works for the simple case you posted in the question:

I should say that there is not a documented guarantee that the ROW_NUMBER() value will be in the precise order that you expect. But it's code golf, so this seems good enough.


"SQLNCLI11" for linked server "linkedserver" returned message "The transaction manager has disabled its support for remote/network transactions." I have a database in a SQL Server 2014 AG group (AG1).- two nodes.

On both nodes, a linked server is set up.  I am selecting data from a table from AG group (AG1) database and inserting into a linked server (a database from another AG group(AG2)).  The only statement in the transaction is an insert statement.  It is not a distributed transaction.

Why do i get the following error?

  OLE DB provider "SQLNCLI11" for linked server "LinkedserverListener" returned message "The transaction manager has disabled its support for remote/network transactions.".
  Msg 7391, Level 16, State 2, Line 2
  The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "LinkedServerListener" was unable to begin a distributed transaction.

Sample code is a below.

Drop table #Myatran
Select 'ABC' name into #Myatran

DECLARE @pndstop_ps NVARCHAR(4000)
SET @pndstop_ps =
N'
INSERT INTO '+ 'linkedserver'+'.DB.dbo.MyaTEST(Name) SELECT name FROM #Myatran
'
SET XACT_ABORT ON
BEGIN TRANSACTION

EXEC sp_executeSQl @pndstop_ps

SET XACT_ABORT OFF

SQL Server 2014 Availability Groups does not support distributed transactions.

SQL Server 2016 has limited support for distributed transactions, however, SQL Server 2017 has the best support.

Consider using SQL Server Integration Services or bcp in/out, or some other tool for Extract-Transform-Load, or ETL, operations.

FYI, you may not be aware of this, but doing an INSERT INTO ... into a table on a linked server results in the local SQL Server performing a series of cursor calls for each row inserted into the target table.

To prove this, I've created a linked server and ran the following code to setup a simple test-bed.

First, we'll create a table in tempdb at the linked server:

DECLARE @cmd nvarchar(max);
SET @cmd = 'IF OBJECT_ID(''dbo.InsertTest'', N''U'') IS NOT NULL
DROP TABLE dbo.InsertTest;
CREATE TABLE dbo.InsertTest
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);';

EXEC [LinkedServer].tempdb.sys.sp_executesql @cmd;

Here, I'm creating a local table in tempdb to hold some rows:

IF OBJECT_ID('dbo.InsertTest', N'U') IS NOT NULL
DROP TABLE dbo.InsertTest;CREATE TABLE dbo.InsertTest
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.InsertTest (id)
SELECT ROW_NUMBER() OVER (ORDER BY c.object_id, c.column_id)
FROM sys.columns c;

a quick check to see if we have rows in the source table:

SELECT CountOfRows = COUNT(1)
FROM dbo.InsertTest;

╔═════════════╗
║ CountOfRows ║
╠═════════════╣
║         990 ║
╚═════════════╝

Here, we'll insert rows from the local source table into the remote linked-server table.  I've setup a trace running on the target server so we can see the T-SQL statements issued against it.

INSERT INTO [LinkedServer].tempdb.dbo.InsertTest (ID)
SELECT it.ID
FROM dbo.InsertTest it;

The output:

╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                             SET XACT_ABORT OFF                                              ║
╠═════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ declare @p1 int                                                                                ║
║ set @p1=NULL                                                                                ║
║ declare @p2 bigint                                                                                ║
║ set @p2=NULL                                                                                ║
║ exec [sys].sp_getschemalock @p1 output,@p2 output,N'"tempdb"."dbo"."InsertTest"'                            ║
║ select @p1, @p2                                                                                ║
║ declare @p1 int                                                                                ║
║ set @p1=NULL                                                                                ║
║ declare @p3 int                                                                                ║
║ set @p3=229378                                                                                ║
║ declare @p4 int                                                                                ║
║ set @p4=294916                                                                                ║
║ declare @p5 int                                                                                ║
║ set @p5=NULL                                                                                ║
║ exec sp_cursoropen @p1 output,N'select * from "tempdb"."dbo"."InsertTest"',@p3 output,@p4 output,@p5 output ║
║ select @p1, @p3, @p4, @p5                                                                                ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=1                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=2                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=3                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=4                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=5
.
.
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=987                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=988                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=989                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=990                                         ║
║ exec [sys].sp_releaseschemalock 1                                                                           ║
║ exec sp_cursorclose 180150027                                                                               ║
║ exec sp_reset_connection                                                                                ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

However, if you run the insert from the target server, like this:

INSERT INTO dbo.InsertTest (ID)
SELECT it.ID
FROM [LinkedServer].tempdb.dbo.InsertTest it;

You see this in the trace:

╔════════════════════════╦════════════════════════════════════════════════╗
║        TextData        ║                                                ║
╠════════════════════════╬════════════════════════════════════════════════╣
║ SET STATISTICS XML ON  ║                                                ║
║                        ║ INSERT INTO dbo.InsertTest (ID)                ║
║                        ║ SELECT it.ID                                   ║
║                        ║ FROM [CP708-D377\MV].tempdb.dbo.InsertTest it; ║
║ SET STATISTICS XML OFF ║                                                ║
╚════════════════════════╩════════════════════════════════════════════════╝

Clearly, it pays to run the insert at the target server, instead of the source server.


SQL Server setting up logins for backup site I am setting up a second SQL Server for a backup site.
I am using SSIS to transfer user logins from production to site2.
I was wondering whether I should transfer the NT Service,NT AUTHORITY and ###MS****### logins from one server to the other or set those up separately on site 2 ?

I was wondering whether I should transfer the NT Service,NT AUTHORITY and ###MS****### logins from one server to the other

No need to transfer those logins, because they are created automatically when you install new SQL Server instance
By the way, easiest way to transfer other logins from one server to another, is to use sp_help_revlogin procedure:
How to transfer logins and passwords between instances of SQL Server


database design for song position under play-list I have a music streaming application where I have playlists. Each playlist has maximum 100 songs. I have a mapping table called PlaylistSongMapping which has the following schema

+------+------------+--------+
|SongId| PlaylistId |Position|
+------+------------+--------+
|   1  |     10     |    2   |
|   2  |     10     |    1   |
|   3  |     10     |    3   |
|   5  |     10     |    4   |
|   6  |     11     |    1   |
+------+------------+--------+

The position of each song inside playlist is managed using the Position column. Inside the playlist I need the order change functionality. The current logic is to update Position column. The problem is if i need to move the song with SongId: 3 to first position I need to update rows of SongId 1,2,3. This number would be a big one when playlist has more number of songs. Is there a better logic so that number of update queries are very minimum.

Instead of Position field you can use a field as LastSongId where you can add the SongId of the previous song in the play list.

So when you want to change you order you only need to update only three rows.

The first row: Your song with new order

The second row / the third row: The song before and after your your changed position song

Your new schema:

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    NULL  |
|   2  |     10     |    1     |
|   3  |     10     |    2     |
|   5  |     10     |    3     |
|   6  |     11     |    NULL  |
+------+------------+----------+

If you change the SongId 3 as position 1, you'll have:

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    3     |
|   2  |     10     |    1     |
|   3  |     10     |    NULL  |
|   5  |     10     |    2     |
|   6  |     11     |    NULL  |
+------+------------+----------+


Integrity Issues in this structure? Imagine the situation. There are three tables,

Student table[PK Student ID]
Enrollment Table [PK EnrollID, FK Student ID]

Fees Paid [PK Fee ID, FK EnrollID, FK StudentID]

Edit: Fee_Paid Table references the PK of both ENROLL TABLE and STUDENT Table.
ENROLL Table References the PK of STUDENT Table.

Given this situation which I have, I was informed that student ID should not be a FK in the Fees table and it causes "Integrity Issues".

While It can be done that way (linking fees paid to student ID and not needing student id in Fees_Paid Table).

Are there any negative ramifications to be using the foreign keys the way that I am cause I honestly seen none. The integrity rule I'm aware of is not letting students be deleted when their ID exist in Fees table but any further clarification will be appreciated.

Your tables mean something like

Student /* [StudentID] is a student AND ... */
Enrollment /* enrollment [EnrollID] is of student [StudentID] AND ... */
Payment /* payment [PaymentID] is enrollment [EnrollID]
    AND enrollment [EnrollID] is of student [StudentID]
    AND ... */

You must decide exactly what these mean and what situations can arise. Together those determines the constraints (PKs, FKs etc). You must give the meanings to users.

In the case of payments, purchases, orders, prices etc finalized transactions are usually recorded with the data from that time, even if a customer ceases to be a customer or prices changed. Does this matter to you? Is there is a FK Payment (StudentID) to Enrollment (StudentID)? I gave Payment a meaning that would lead to that FK.

The integrity problem is that your design allows Enrollment and Payment to disagree about EnrollID and StudentID even when the DBMS is enforcing the constraints. That can never arise, and the database would describe an impossible situation, so it's an error, but the DBMS doesn't know that, so it can't reject it.

That problem is gone with FK Payment (EnrollID, StudentID) to Enrollment (EnrollID, StudentID). Then the DBMS catches those errors.

But then of you update one of those tables then you have to update the other. That is called an update anomaly. (This one doesn't lead to errors when you have the 2-column FK; it's just inconvenient.)

You can see that the table meanings are simpler when you just have

Payment /* payment [PaymentID] is enrollment [EnrollID] AND ... */

If you do want to involve the previous Payment meaning in a query then you use JOIN to get the table whose meaning is the AND of the simpler meanings.

PS You don't need to declare a FK Payment (StudentID) to Student (StudentID) because it will be enforced by the combination of FKs in Payment and Enrollment.


Tuning performance of finding entities with fully matching attribute values How to tackle performance tuning of finding matches of entities with the same attributes and values, in an OLTP entity-attribute-value table?

The query needs to run hundreds of times per minute from several application threads in parallel, and the performance needs to be quick and without bringing down the whole instance (ideally under 50ms duration, with accordingly low CPU and IO) -- but calculating all the matches is aggregating across at least hundreds of thousands of rows.

What approaches exist to tackle this type of problem?

Repro to test:

Simplified repro

Right now the query that you have looks pretty good. It seems like performance scales with the number of attributes on the entity that you're checking against. For example, if I test with the following parameters:

DECLARE @search_entity_id INT = 2896182;
DECLARE @current_count INTEGER = 8;

The query that you have with MAXDOP 1 takes about 188 ms CPU time and 188 ms elapsed time on my desktop. If I change the parameters to these:

DECLARE @search_entity_id INT = 1324226;
DECLARE @current_count INTEGER = 4;

Then CPU time drops to 93 ms CPU and elapsed time drops to 90 ms.

One trick that can improve performance is to take advantage of batch mode execution. Batch mode execution processes rows up in batches up to 900 rows instead of row by row to cut down on metadata and access costs. It was designed to work with CCIs but can also work with data from rowstore tables. There are a few different tricks to get batch mode with rowstore queries. Here, I'll just create an empty NCCI against the temp table:

CREATE NONCLUSTERED COLUMNSTORE INDEX X_CCI ON #base (entity_id)
WHERE entity_id = -1 AND entity_id = -2;

As of SQL Server 2016, batch mode processing is supported for MAXDOP 1 queries and for the sort operator. Both of those enhancements are helpful for this query. After creating the index we can see the difference in the query plan:

The previous 8 row example now executes in about 110 ms of CPU time and 115 ms of elapsed time. The 4 row example executes in about 62 ms of CPU time and 60 ms of elapsed time. I got a savings of around 33% of my machine, but I don't know if that's good enough for your workload's requirements.


Execute system commands in postgresql My requirement is to execute a system command like (ls) or C program when a trigger executes. Is there any way to create a trigger function to solve this problem.

You can easily do what @a_horse_with_no_name suggests in his comment.  But there is also an interesting way to do it, using PL/pgSQL as the function language.

This uses a feature of the COPY command, introduced in PostgreSQL 9.3.  It can now take a command as target/source, exactly where you'd use a filename or STDIN/STDOUT in normal cases:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

Obviously, you need a table to put the output, but you can ignore it, if you want.

See a small example:

CREATE TABLE trigger_test (
    tt_id serial PRIMARY KEY,
    command_output text
);

CREATE OR REPLACE FUNCTION trigger_test_execute_command()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $BODY$
BEGIN
    COPY trigger_test (command_output) FROM PROGRAM 'echo 123';
    RETURN NULL;
END;
$BODY$;

CREATE TABLE trigger_test_source (
    s_id integer PRIMARY KEY
);

CREATE TRIGGER tr_trigger_test_execute_command
    AFTER INSERT
    ON trigger_test_source
    FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_test_execute_command();

INSERT INTO trigger_test_source VALUES (2);

TABLE trigger_test;
 tt_id │ command_output
───────┼────────────────
     1 │ 123

Note: the function needs to run with superuser rights - that is, either do the INSERT as a superuser, or define the function with SECURITY DEFINER.  In any other case, you'll get an error:

ERROR:  must be superuser to COPY to or from an external program
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.


Why does Oracle still not support Multi-Row insert? According to Wikipedia's article about INSERT in SQL, multi-row inserts are standard since SQL-92. Oracle (up to 11g at least) does not support this.
Why?

-- Valid
insert into some_table (magic) values (42);

-- Invalid
insert into some_table (magic) values (42), (666);
-- --> SQL Error: ORA-00933: SQL command not properly ended

The plain answer is that not all RDBMSes support all of the various ANSI standards. Oracle just happens to not support this particular part of it.

Oracle does however support INSERT ALL INTO.

By the way, an example of insert into dual is terribad - You can break older versions of Oracle by doing an INSERT into DUAL.


How to insert multiple returns of the select statement? I have 3 tables. ClassList, Student & Faculty. I'm trying to assign the faculty to a specific class, e.g. I'm assigning "John Doe" to the section "Section1" where this "Section1" exists in the Student table column.

Outcome:

Student table

 StudentID       Name    Section
    1            user1    Section1
    2            user2    Section1
    3            user3    Section2

Faculty table

 FacultyID       Name       Subject
    1            Faculty1   Subject ABC
    2            Faculty2   Subject DEF

ClassList table

  ClassListID        StudentID      FacultyID    ModifiedDate

My insert statement is like this:

INSERT INTO ClassList
VALUES
(
    (
        SELECT Student.StudentID
        FROM Student
        WHERE Student.Section = 'Section1'
    ),
    (
        SELECT Faculty.FacultyID
        FROM Faculty
        WHERE Faculty.FirstName = @FacultyName
        OR Faculty.LastName = @FacultyName
    ),
    GETDATE()
);

I know that the select statement in the Student table returning a lot so I'm wondering if there's a way to add all the return query of it into ClassList table?

Since you don't appear to have any common information in these two tables, I'd recommend doing a distinct cross apply:

INSERT INTO ClassList (StudentID, FacultyID, ModifiedDate)
SELECT DISTINCT student.studentID
    ,faculty.FacultyID
    ,getdate()
FROM Student
CROSS APPLY faculty


Recommended distributor architecture for transactional replication? We are planning to create a Transactional replication on 2 nodes of SQL Server 2008 R2, each node will be a publisher and a subscriber.

What is the best practice in order to have this scenario done? Microsoft always recommends that the distributor should be independent from the publisher or the subscriber, so it is obvious that a 3rd node is to be involved in the scenario. But, as each node will be at the same time publisher and subscriber, can the same (3rd node) be the distributor for the 2 publishers? Should I create 2 distributors, or can it still be on the same architecture, using only 2 nodes, each one acting as distributor as well as publisher?

What is the best practice in order to have this scenario done?

It depends on how much data you are replicating i.e.

Are you replicating selected columns/tables, entire database (all tables), etc ?
Are both nodes located in the same domain or different domains ?
Are you replicating in same region or cross region (US-UK, etc) ?

I have implemented T-Rep where I have used same server as publisher and distributor as the data that was needed to replicate was less and also, have implemented separate distribution database on separate server that does all the heavy lifting of publishing the data to subscribers where we had massive data to push down to subscribers.

You have to consider factors like -

time taken to perform snapshot and applying that snapshot to subscribers
Feasibility of re-initializing the articles when a major (e.g. schema) change occurs to the tables that are involved in replication.

should I create 2 distributors?
You can use the same distribution database. Though, for ease of maintenance and better performance [reducing contention - both writing to and reading from the distribution database] I would highly recommend you use separate Distribution databases.

Remember that distribution database is the heart of replication. So it requires proper maintenance, backups, etc. Now if you have just 1 distribution database that supports multiple publishers and a DISASTER happened, then restoring it from a previous backup will impact ALL publishers.

From BOL :

  In many cases, a single distribution database is sufficient. However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher. Doing so ensures that the data flowing through each distribution database is distinct.

Lastly some good references that will help you :

Deep Dive on Initialize from Backup for Transactional Replication

Replicating Non-Clustered Indexes Improves Subscriber Query Performance

Follow the Data in Transactional Replication - Whitepaper

Troubleshooting Transactional Replication

Scaling Out the Distribution Database


Stored procedure performance metrics I am taking over a new project which is having ~2500 SPs! and would like to know at current state what is the maximum / minimum time taken by each of the stored procs. Also IO, Logical Reads used by each of the SPs. Would like to benchmark it that way. After each new drop would use this benchmark to compare whether the changes done has helped or not.

Can DMVs or Extended Events be of help here? or do i need to run a trace covering my complete work load to capture this?

This will get you the top 100 with the highest average duration since the last service restart. This DMV is reset when SQL Server restarts, so if you need to go beyond that, you'll need to trace, use extended events, use auditing of some kind (built-in or manual), or invest in a 3rd party monitoring tool.

You can adjust that obviously, or go after different metrics such as physical or logical reads...

USE [Your Database Name];
GO

SELECT TOP (100)
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id]),
    type_desc,
    cached_time,
    last_execution_time,
    execution_count,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes,
    total_logical_reads,
    last_logical_reads,
    min_logical_reads,
    max_logical_reads,
    total_elapsed_time,
    last_elapsed_time,
    min_elapsed_time,
    max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) = N'Your Database Name'
ORDER BY total_elapsed_time / execution_count DESC;


Database schema for a variable-level hierarchy with variable types? A client is asking for a database structure for a medical translation app that involves narrowing down a health issue and then asking specific questions on it.  The tricky part of representing this in a database is that there may be anywhere from 2-5 hierarchical "filters" to narrow down the health issue, then a hierarchy of questions to ask the patient.  The same filters and questions can be re-used in any level of the hierarchy.  The questions must also be translated into several languages if dealing with a non-English speaking patient.  For example:

-> Symptoms

 -->Cardiovascular

    -> Chest Pain

      "Have you experienced any chest pain recently?"
        "On a scale of one to ten, how severe is the pain?"
        "Is the onset of the pain sudden or gradual?"
        "Is the pain related to exertion?"

      "Do you ever suffer from shortness of breath?"

    -> Palpitations

      "Are you ever aware of your heartbeat? What is it like?"
        "Can you tap out the rhythm with your finger?"

-> Discharge

  --> Chest Pain

    "We recommend the following lifestyle changes to improve your condition."
      "Avoid taking too much aspirin for pain."

I can use a string hack to make this work, but am looking for something that can scale better and is searchable.  Also it will be maintained by a non-technical person in the future, so preference to something simple to comprehend vs. better performance like nested sets.  (Sorry for the formatting, everything I tried kept auto-block quoting stuff.)  Thank you to anybody who can assist!

A good way to do this in a relational database is with statement, category, and category_structure tables. The statement represents the line of text. Assume that each statement resides in a single category.  A group of statements in a category have to appear in a certain order. Categories include the text groupings including Cardiovascular and Palpitations. The category_structure relates the hierarchy of categories. Each structure has a child_Category, parent_category, and sort_order. A structure is a unique combination of a child and a parent. Here are the table definitions.

statement
PK   statement_ID         number   An autogenerated sequence
FK   category_ID          number   A foreign key to category table
     statement_text       varchar  The long description On a scale of one...
     statement_sort_order number   The order within a category

category
PK   category_ID          number   An autogenerated sequence
  UK category_unique      varchar  Uniquely identifies the purpose of a category.
                                   Values could be Discharge. Also, Chest Pain Symptom,
                                   Chest Pain Discharge.
     category_description varchar  The visible text description of a category. The
              values would be Symptoms, Cardiovascular,
              Discharge, Chest Pain, Palpitations.

category_structure
PK   structure_ID         number  An autogenerated sequence
FKUK child_category_ID    number  foreign key to category
FKUK parent_category_ID   number  foreign key to category. Optional.
     category_sort_order  number  The order of categories or the order within
                                  categories

Note the Symptoms category would be the parent of all symptoms. The Discharge category would be the parent of all discharge. The structure table does not prevent a category from having both symptom and discharge as parents.

I see from the reformatting edit that Chest Pain is both a symptom category and a discharge category. This would still work fine. One option is to make two categories for Chest Pain, one as a symptom, one as a discharge. They just happen to have the same name, but they contain different sets of statements. The other option (reflected in the table design) is to add a field to the statement table describing whether the statement is a symptom-style statement or a discharge-style statement. So the Chest Pain category has both symptom statements and discharge statements. The query would only show the appropriate subset based on the need.


T-SQL (SQL Server 2012) Update based on lookup table I have the following tables related to this question:

CREATE TABLE tblTitan(
titanId int IDENTITY (15483,1) PRIMARY KEY,
userId int UNIQUE NOT NULL,
titanName varchar(15) NOT NULL,
titanExperience int DEFAULT 0,
titanLevel int DEFAULT 1,
titanStep int DEFAULT 1,
titanBattleCount int DEFAULT 0,
titanWinCount int DEFAULT 0,
titanLossCount int DEFAULT 0,
titanDateCreated date DEFAULT GetDate(),
titanDateLegend date DEFAULT NULL,
titanNpcFlag bit DEFAULT 0,
titanActiveFlag bit DEFAULT 1,
titanImage varchar(150) DEFAULT 'basicTitan.png',
titanElementType int NOT NULL UNIQUE,
FOREIGN KEY (userId) REFERENCES tblUser (userId),
FOREIGN KEY (titanElementType) REFERENCES tblElement (elementId),
);

CREATE TABLE tblLevel(
levelId int IDENTITY (1,1) PRIMARY KEY,
levelNo int NOT NULL,
levelStep int NOT NULL,
levelExperienceMin int NOT NULL,
levelExperienceMax int NOT NULL,
);

Scenario

A titan has battles with other titans, during which an algorithm calculates a winner, and xp is awarded to the titan. After each battle, I want to check if a titan's experience crosses a threshold from the tblLevel table, which is a lookup table.

tblLevel has values such as the following:

levelId 1

levelNo 1
levelStep 1
levelExperienceMin 0
levelExperienceMax 100

levelId 2

levelNo 1
levelStep 2
levelExperienceMin 101
levelExperienceMax 200

levelId 3

levelNo 2
levelStep 1
levelExperienceMin 201
levelExperienceMax 300

Question

Using t-sql code, how may I update the tblTitan level and step values based upon the experience the titan has? eg: if a titan has 110 experience, I want to update the titanLevel and titanStep values from (1, 1) to (1, 2), as dictated by the tblLevel lookup table.

I have tried fiddling around with the update function, but I am not too sure how to use a subquery or other method to be able to check if the experience is between the min and max ranges.

An example attempt of my code is:

UPDATE tblTitan t, tblLevel l
AS
SET t.titanStep = l.levelStep
WHERE t.titanExperience = >= l.levelExperienceMin AND <= l.levelExperienceMax

This sort of command SEEMS to make sense in my head, but is completely invalid in T-SQL. If somebody could please help me with the code or point me in the right direction that would be greatly appreciated!

My first question is:

  How would you write a query (SELECT) with a similar pattern?

I can see several problems with the UPDATE statement:

UPDATE on 2 tables (tblTitan t, tblLevel l)
No FROM clause
Old JOIN syntax (TableA, TableB VS FROM TableA INNER JOIN TableB ON ...)
Invalid (missing) expression in the WHERE clause (usage of < and > operators like a BETWEEN operator: A > B AND < C)

A SELECT statement would be like this:

SELECT titanId, titanName
    , t.titanLevel, t.titanStep
    , l.levelNo , l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience >= l.levelExperienceMin
    AND t.titanExperience <= l.levelExperienceMax ;

tblTitan t, tblLevel l is replaced by a proper INNER JOIN clause
the WHERE clause is replaced by the ON clause in the INNER JOIN
the invalid BETWEEN like WHERE clause is replace by 2 expresions with < and >

From this valid query, the FROM ... JOIN ... WHERE part can be kept and the SELECT can be replaced by an UPDATE t and a SET clauses.

The UPDATE statement can be writen like this:

UPDATE t
    SET t.titanLevel = l.levelNo
        , t.titanStep = l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience >= l.levelExperienceMin
    AND t.titanExperience <= l.levelExperienceMax
-- WHERE titanId = ...;

Sample data with 3 levels and 3 titans:

DECLARE @tblTitan TABLE (titanId int, titanName varchar(15) , titanExperience int, titanLevel int, titanStep int)
INSERT INTO @tblTitan(titanId, titanName, titanExperience, titanLevel, titanStep) VALUES
    (1, 'Bob', 110, 55, 66)
    , (2, 'Sam', 220, 77, 88)
    , (3, 'Isa', 50, 22, 33)

DECLARE @tblLevel TABLE(levelId int, levelNo int, levelStep int, levelExperienceMin int, levelExperienceMax int)
INSERT INTO @tblLevel(levelId, levelNo, levelStep, levelExperienceMin, levelExperienceMax) VALUES
    (1, 1, 1, 0, 100)
    , (2, 1, 2, 101, 200)
    , (3, 2, 1, 201, 300)

Output of SELECT before UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 22            | 33        | 1         | 1
1       | Bob       | 55            | 66        | 1         | 2
2       | Sam       | 77            | 88        | 2         | 1

Output of SELECT after UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 1             | 1         | 1         | 1
1       | Bob       | 1             | 2         | 1         | 2
2       | Sam       | 2             | 1         | 2         | 1


Are semicolon-delimited Postgres commands run serial or parallel? INSERT INTO ...; UPDATE ...; are these commands run sequentially (to completion) left-to-right, or in parallel? Or "it's not that simple"?

TL;DR I'm trying to circumvent 9.4.4's lacking UPSERT by inserting first if missing, then updating both new and existing after:

-- First bulk create any missing tags
INSERT INTO user_tags (user_id, tag_id, score)
SELECT :user_id, t.tag_id, 0
FROM (
    SELECT tag_id
    FROM post_tags
    WHERE post_id=:post_id
    EXCEPT
    SELECT tag_id
    FROM user_tags
    WHERE user_id=:user_id
    ) t;
-- Then increment their score
UPDATE user_tags
SET score = score + 1
WHERE tag_id IN (
    SELECT tag_id
    FROM post_tags
    WHERE post_id=:post_id
    )
    AND user_id=:user_id;

All SQL commands in a single batch are executed linearly; that is the first statement is executed, then the second is executed after the first completes.

The only way to get multiple statements to run in parallel is to execute each statement on a separate connection.

So, to confirm, the two statements in your batch will always execute linearly, i.e. the insert will run first, then the update will run.


MS SQL Server 2008 R2 duplicate key row in object 'sys.syscommittab' I have a server that is failing to backup due to an error with the syscommittab and have gone through everything in this question: SQL Server 2008 unable to backup due to Failed to flush the commit table to disk in dbid 11 due to error 2601 but am still having the same issue

Running a DBCC CHECKDB returns the following:

Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
    Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (491219680).
Msg 3999, Level 17, State 1, Line 1
    Failed to flush the commit table to disk in dbid 9 due to error 2601. Check the errorlog for more information.

Change tracking is enabled on this database with retention of 30 days and auto-cleanup on but it is not cleaning any records since this error occurred several months ago

I've seen suggestions to run sp_flush_commit_table_on_demand but that does not exist on this installation (SQL Server 2008 R2 SP3 - now, was SP1 but installed latest SP to address known issues with change tracking related to this error)

I can see the problem rows by running:

select * FROM sys.dm_tran_commit_table where xdes_id='491219680'

Both of them are from when the problem occurred (within 20 mins of each other) and are well outside of the retention period.  I'm wary of trying to manually delete these but can't really find anything to confirm what the risks of doing this is

Any suggestions on next steps would be appreciated

Eventually I found a Microsoft article on how to do this.  You can't simply delete the rows as they are in protected system tables so you need to put SQL into single user mode and run the script provided in the article from a DAC connection in order to delete the duplicate rows

https://support.microsoft.com/en-gb/help/3083381/duplicate-key-rows-from-the-sys-syscommittab-table-in-sql-server

Transact-SQL script from above article:

--Create a temporary database to store the necessary rows required to remove the duplicate data
if exists(select 1 from sys.databases where name = 'dbChangeTrackingMetadata')
begin
drop database dbChangeTrackingMetadata
end
go
create database dbChangeTrackingMetadata
go

--Table to store the contents of the SYSCOMMITTABLE
use dbChangeTrackingMetadata
go
create table dbo.t_SYSCOMMITTABLE (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
)
go

--Table to store the duplicate rows to be removed from the sys.syscommittab table
create table dbo.t_syscommittab (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
,dbfragid int
)
go

--Enable the usage of OPENROWSET
exec sys.sp_setbuildresource 1
go

--Change <AFFECTED_DB> to the database that contains the duplicate values
USE <AFFECTED DB>
go
declare @rowcount bigint
SET @rowcount = 0

--Copy all rows from the SYSCOMMITTABLE into the temporary database
insert into dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE
SELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_time
FROM OpenRowset (table SYSCOMMITTABLE, db_id (), 0, 0)

--Save the duplicate values into the temporary database
insert into dbChangeTrackingMetadata.dbo.t_syscommittab
select ondisk_ct.* from sys.syscommittab as ondisk_ct
join dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_ct
on ondisk_ct.xdes_id = inmem_ct.xdes_id

--Delete the duplicate values
delete from sys.syscommittab
where xdes_id in ( select xdes_id from dbChangeTrackingMetadata.dbo.t_syscommittab )
set @rowcount = @@rowcount
if (@rowcount > 0)
begin
print ''
print 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'
print ''
end
else
begin
print ''
print 'Failed to DELETE DUP rows from sys.syscommittab'
print ''
end
exec sys.sp_setbuildresource 0
go


Efficiently DROP huge table with cascading replication in PostgreSQL What I have:
Database: PostgreSQL 9.3
Table T,

Structure: 10 integers/bools and 1 text field
Size: Table 89 GB / Toast 1046 GB
Usage: about 10 inserts / minute
Other: reltuples 59913608 / relpages 11681783

Running cascading replication: Master -> Slave 1 -> Slave 2

Replication Master -> Slave 1 is quite fast, a good channel.
Replication Slave 1 -> Slave 2 is slow, cross-continent, about 10 Mbit/s.

This is a live, used database with about 1.5TB more data in it.

What's needed to be done:

Drop all data to start with a fresh setup (to do constant cleanups and not allow it to grow this big).

Question:
What would be the most efficient way to achieve this:

without causing huge lags between Master and Slave 1
without causing Slave 2 to get irreversibly lagged to a state where catching up is not possible

As I see it:

Safe way - do a copy, swap places, DELETE data constantly watching lag
Other way - do a copy, swap places, DROP table - but this would cause enormous amounts of data at once and Slave 2 would get lost?

DROP TABLE sends very little information over the WAL stream, and the information it does send is independent of how many rows the table has in it.


Can MySQL Handle Geo Location Say I want 20 closest businesses to a location.

Innodb doesn't support that. So what does?

Is it possible to have some tables in some engine and other tables on different engines?

Yes you can use MyISAM for storing the geo location as they support the spatial data types.For more information please look Saptial Datatypes in MySQL

And You can also use the mix of engines there is no issues on that..


Designing a database I am designing a MySQL db for a forms program we use for tracking the steps our techs take when working on a machine. Currently I use integers to store the answers to the questions. We have the question and then they hit complete or hit see notes and enter notes below. The issue is that the forms change constantly. Sometimes they add tasks, and take them away. Which messes with the order because the column headers are like Question 1 through question however many. What is the best way to approach designing this especially if we need to be able to see and create reports based off of these entries.

Based on what you provided: generic form with drop downs. Have predefined spaces to fill in. A dropdown with the question that can be chosen and then a box for text. Then include a "Add another section". Which will simply add another section to the bottom. This allows the form to expand and contract based on what they did. It is endlessly expandable. You don't have to keep redesigning the whole each time. Not sure that will work for you but it is one possibility.


SQL0204N "DB2ADMIN.TA" is an undefined name. SQLSTATE=42704 There is a table called ta with schema db2admin.
When I ran db2 select tabname,tabschema from syscat.tables wheretabname='ta' and tabschema='db2admin', it returned the row, which means the table was there.

However, when I ran the query db2 select * From db2admin.ta under the same user account, this error was raised:

  SQL0204N  "DB2ADMIN.TA" is an undefined name.  SQLSTATE=42704

What is the reason for this?

DB2 (as other standard-compliant SQL databases) converts unquoted identifiers to uppercase, so when you say select * from db2admin.ta DB2 will look for the table named 'TA' in the schema 'DB2ADMIN'. That's exactly what the error message tells you.

According to the SYSCAT.TABLES query results, you have created your table and schema with lowercase names. To access them you will need to quote both identifiers (separately):

select * from "db2admin"."ta"

Note the double quotes.


Effective way to migrate from SQL Server to MySQL I have a 20GB Microsoft SQL Server database that needs to be migrated to MySQL.

Right now I only have the schema for the source db. I tried the migration wizard on the schema in MySQL Workbench and it worked out fine (with some minor varchar issues that was easily solved)

As I am waiting for the data I am wondering if MySQL Workbench is the way to go here as the source is 20GB.

Can the Workbench handle it? Is it effective?
Should I investigate other tools than the Workbench?

Any advice and pointers is appreciated.

If you're looking for an easy-to-use, open-sourced tool, checkout etlalchemy.

You can carry out your SQL Server to MySQL database migration with 4 lines of Python code:

To Install:

pip install etlalchemy

(On El Capitan you may have to run pip install --ignore-installed etlalchemy)

To Run:

from etlalchemy import ETLAlchemySource, ETLAlchemyTarget

mssql_db_source = ETLAlchemySource("mssql+pyodbc://username:password@DSN_NAME")

mysql_db_target = ETLAlchemyTarget("mysql://username:password@hostname/db_name",
              drop_database=True)

mysql_db_target.addSource(mssql_db_source)
mysql_db_target.migrate()

If you want to learn more about etlalchemy, check out this article.


Access to folder is denied (log shipping) I am trying to log ship from server A to B. I have done everything to configure log shipping but when I try to run the job at Server B while copying a backup from server A, ikt gives me an error:

  can not access the path (Server A backup folder name) access is denied.

I have shared backup folder at server A with admin account moreover with Everyone. I also tried to change SQL Server agent config and use option (this account) in properties and fill my system account.

Please help me to fix this problem and explain a little like:

Is there any problem with sharing the account at server A
Do I need to change my SQL Server Agent service account and how?

Let's say at server A there is a folder backup in E drive and there is a folder restore at server B in E drive. I am trying to login to SQL Server through Windows service credential in server A.

Public ip for my two servers is 10.201.4.111 and 10.201.4.112 and my service accounts are local system, which are built in accounts.

It would be useful to see the output of the actual job itself.

You mentioned you are using local system, if so then you need to grant permissions to the computer account on the folder.  The account you give permissions to would be in the format:

<DomainName>\<ServerName>$

So for a computer called ServerA on the Contoso domain you would give permissions to the folder for this account

Contoso\ServerA$

The $ on the end means this is a computer account.

If this works you may still run into issues with logging  if the computer accounts don't have any permissions in SQL Server.


Updating records from the past with MySQL query I've built an application that fetches reports from an external source. It downloads report data once a day. Each report covers the time from now to 30 days ago.

E.g. let's say it's 1 May and report is downloaded for the last 30 days and we end up with 30 rows in the table (let's have each day be represented by one row). Then on 2 May we fetch the report and we end up with 31 rows in the table - 1 row from the report fetched on 1 May, and 30 rows from 2 May. 29 older rows that overlap are discarded. The rows from the latest report replace them. Then on 3 May we end up with 32 rows in the table, 4 May, 33 rows, etc.

Now the query that does the clean-up of the older data in my application looks like this (records with the same date that have a lower ID are deleted):

DELETE FROM $tableName WHERE id NOT IN (SELECT MAX(id) FROM (SELECT * FROM $tableName) AS sth GROUP BY $date

The problem is that this query is very inefficient. As the amount of data in the table grows, it's starting to kill the database server as each the select statement is applied to the whole dataset. Aslo NOT IN and MAX become less and less efficient as number of rows grows.

The system has to update rows from the past as the external source normalizes data backwords in time so records from the past will change occasionally, and I'm always interested in the last 30 days.

I'd be grateful for advice on how I can optimize this part of the application, how I can overcome the performance problem of this SELECT and DELETE statement.

Define an unique key in your table such that each day can only be represented by one row (UNIQUE KEY(date) would be enough for the example you show). Then use

INSERT INTO $tablename(...) VALUES(...)
    ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2);

Alternatively you might use REPLACE INTO, but that does the delete and insert, "burning" through autoincrement IDs which you seem to have.

You might even use the date column as a PRIMARY KEY with this setup.


Upgrading SQL 2016 AGs to SQL 2019 question I am doing a rolling upgrade of my production SQL 2016 Servers to SQL 2019.  I have availability groups.  My current structure is 3 DR servers (secondaries), 3 HQ (on prem) secondaries and 3 HQ primaries.  I have updated 2 of the DR servers as per the upgrade instructions that I've found.  The instructions say the 2019 DBs should be in synchronized mode, but my 2019 DBs are in Synchronized/In Recovery mode.  I hope the "In Recovery" part is because the DRs are now SQL 2019 and the rest of the AG is SQL 2016 and when I failover from the primaries to the secondaries, there won't be any trouble.  Is this the case?
I am using this reference during the upgrade.

"Synchronized/In Recovery" means the database is synchronized with the primary and applying log records continuously from the primary.  This keeps the database up-to-date against the source 2016 database.  The database cannot be used while in this state, not even for read-only queries.
If you were to failover to a database in that state, the server would run recovery on the database once it has applied all the outstanding log records in the redo-queue.  At that point the database comes online, and can be used by clients.  However, once recovery runs, and the database engine upgrades the database to SQL Server 2019, it cannot be restored onto prior SQL Server versions - i.e. you couldn't fallback to a 2016 server.
Once you complete the upgrade of the last node to SQL Server 2019, database status should return to normal on the readonly secondaries.


PostgreSQL crashes during count row query Currently I have only two tables - small one that works fine and another presumably large one.
When I try to find out row count in pgAdmin I get the following error:

The application has lost the database connection:
⁃ If the connection was idle it may have been forcibly disconnected.
⁃ The application server or database server may have been restarted.
⁃ The user session may have timed out.
Do you want to continue and establish a new session?

I also checked the Postgres server logs:

2020-03-30 03:06:06.764 MSK [37356] LOG:  server process (PID 30712) was terminated by signal 9: Killed: 9
2020-03-30 03:06:06.764 MSK [37356] DETAIL:  Failed process was running: SELECT COUNT(*)::text FROM ios.task_info;
2020-03-30 03:06:06.764 MSK [37356] LOG:  terminating any other active server processes
2020-03-30 03:06:06.765 MSK [30810] WARNING:  terminating connection because of crash of another server process
2020-03-30 03:06:06.765 MSK [30810] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-03-30 03:06:06.765 MSK [30810] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-03-30 03:06:06.765 MSK [30709] WARNING:  terminating connection because of crash of another server process
2020-03-30 03:06:06.765 MSK [30709] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-03-30 03:06:06.765 MSK [30709] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-03-30 03:06:06.767 MSK [37356] LOG:  all server processes terminated; reinitializing
2020-03-30 03:06:06.776 MSK [30816] LOG:  database system was interrupted; last known up at 2020-03-30 02:51:14 MSK
2020-03-30 03:06:06.818 MSK [30817] FATAL:  the database system is in recovery mode
2020-03-30 03:06:06.853 MSK [30816] LOG:  database system was not properly shut down; automatic recovery in progress
2020-03-30 03:06:06.855 MSK [30816] LOG:  redo starts at 0/4F4DE850
2020-03-30 03:06:06.855 MSK [30816] LOG:  invalid record length at 0/4F4DE888: wanted 24, got 0
2020-03-30 03:06:06.855 MSK [30816] LOG:  redo done at 0/4F4DE850
2020-03-30 03:06:06.862 MSK [37356] LOG:  database system is ready to accept connections

I run everything on my OS X Catalina laptop and use PostgreSQL 12 EnterpriseDB.

Just in case if it is important - I created the large table sending several large CSV COPY queries in my Python script.
What can be the reason of these errors?

I run everything on my OS X Catalina laptop and use PostgreSQL 12
  EnterpriseDB.

There is a JIT-related problem on this platform and version that leads to the kind of crash you're reporting. It's explained on mailing lists:

Re: PG v12.2 - Setting jit_above_cost is causing the server to crash

At the moment, the workaround seeems to be to set jit = off until EDB releases a new version with a fix, hopefully in PostgreSQL 12.3


How do you partition an events table by a time based off the datediff(Lag,time)? I am not sure how really to ask this question as i have never come across such grouping/ partitioning before.

Basically i have an events table with 50 million rows that give me a timestamp, user, video and a event. A super basic example of my current query is:

Activity as (
Select
Min(Timestamp) as start_Of_Activity,
Max(Timestamp) as End_Of_Activity
Session,
User,
Video,
Sum(Event_Count)
Group by User,Video,Session
)

I have just found out that the session ID is not accurate and wont be fixed so i have to change my business rules for defining an "Activity". Basically i have to run a Lag/Lead partition over each row to find out 'if The difference between a rows timestamp and the previous rows timestamp is over 2 hours for that user/video' then consider it another Row. Does anybody know how i should go about grouping this event data.

Example

Raw data example

Session ID:   User:  Video:   Activity_Start_Time   Event
10             1       2        12/2/19T12.05.05     play
10             1       2        12/2/19T01.05.05     stop
10             1       2        14/2/19T12.05.05     play
10             1       2        14/2/19T01.05.05     Watching
10             1       2        16/2/19T02.10.10     Stop

Current Grouping:

Session ID:   User:  Video:   Activity_Start_Time   Activity_End_Time
10             1       2        12/2/19T12.05.05     16/2/19T05.10.10

What the grouping needs to be

Session ID:   User:  Video:   Activity_Start_Time   Activity_End_Time
10             1       2        12/2/19T12.05.05     12/2/19T01.05.05
10             1       2        14/2/19T12.05.05     16/2/19T02.10.10

You can look at the following:  (Notice I changed your sample date formats for this example)
This uses a CTE to get your dataset with the 'LAG'ged value for [Activity_Start_Time].  The LAG is performed as partitioned by User and ordered by the date

Table:

Create Table #events
(
SessionID Int,
[User] Int,
Video Int,
Activity_Start_Time SmallDateTime,
[Event] VarChar(20)
)
Insert Into #events Values
(10,1,2,'2019-02-12 12:05:05','play'),
(10,1,2,'2019-02-12 13:05:05','stop'),
(10,1,2,'2019-02-14 12:05:05','play'),
(10,1,2,'2019-02-14 13:05:05','Watching'),
(10,1,2,'2019-02-16 14:10:10','Stop')

Query:

With cte As
(
Select
       Activity_Start = Lag(Activity_Start_Time,1,0)
                        Over (Partition By [User] Order By Activity_Start_Time),
       Case When IsNull(Lag(Activity_Start_Time,1,0)
                        Over (Partition By [User] Order By Activity_Start_Time),0) = 0
            Then 0
            Else DateDiff(Hour,Lag(Activity_Start_Time,1,0)
                        Over (Partition By [User] Order By Activity_Start_Time),Activity_Start_Time)
       End As Diff_Hours,
       *
From
       #events
)
Select
      SessionID,
      [User],
      Video,
      Diff_Hours,
      Activity_Start,
      Activity_Start_Time As Activity_End
From cte
Where Diff_Hours > 2

Drop Table #events

Result:

SessionID   User    Video   Diff_Hours  Activity_Start      Activity_End
10          1       2       47          2019-02-12 13:05:00 2019-02-14 12:05:00
10          1       2       49          2019-02-14 13:05:00 2019-02-16 14:10:00