How to rewrite slow CTE construction to match speed of temp tables I think the general advice of this community is to avoid temp tables in favor of CTEs.  However, I sometimes encounter situations in which CTE constructions are very slow, while their temp table equivalents are very fast.
For example, this spins for hours and never seems to yield results.  The query plan is full of nested loops.
CREATE TABLE #TRIANGLES
(
    NODE_A VARCHAR(22),
    NODE_B VARCHAR(22),
    NODE_C VARCHAR(22)
)
;

INSERT INTO #TRIANGLES VALUES
/*  150,000 ROWS  */
;

CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES (NODE_C);

WITH
TRIANGLES_FILTERED AS
(
    -- **** FILTERING OF THE TRIANGLE TABLE OCCURS IN A CTE ****
    SELECT   *
    FROM     #TRIANGLES AS T
    WHERE    LEN(T.NODE_A) = 2  AND
             LEN(T.NODE_B) = 2  AND
             LEN(T.NODE_C) = 2
),
CONNECTABLE_NODES AS
(
    SELECT   DISTINCT T1.NODE_C AS [NODE]
    FROM     TRIANGLES_FILTERED AS T1

             INNER JOIN
             TRIANGLES_FILTERED AS T2
             ON T1.NODE_B = T2.NODE_A  AND
                T1.NODE_C = T2.NODE_B

             INNER JOIN
             TRIANGLES_FILTERED AS T3
             ON T2.NODE_B = T3.NODE_A  AND
                T2.NODE_C = T3.NODE_B

    WHERE    T1.NODE_A <> T2.NODE_C  AND
             T1.NODE_A <> T3.NODE_C  AND
             T2.NODE_A <> T3.NODE_C
)
SELECT   *
FROM     #TRIANGLES AS T1
WHERE    T1.NODE_A IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_B IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_C IN (SELECT * FROM CONNECTABLE_NODES)
;

Query plan:
https://www.brentozar.com/pastetheplan/?id=rk_5TaiiP

Whereas, the query plan for this uses hash matches and it runs in a flash:
CREATE TABLE #TRIANGLES
(
    NODE_A VARCHAR(22),
    NODE_B VARCHAR(22),
    NODE_C VARCHAR(22)
)
;

INSERT INTO #TRIANGLES VALUES
/*  150,000 ROWS  */
;

CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES (NODE_C);

-- **** FILTERING OF THE TRIANGLE TABLE SAVED INTO A TEMP TABLE ****
SELECT   *
INTO     #TRIANGLES_FILTERED
FROM     #TRIANGLES AS T
WHERE    LEN(T.NODE_A) = 2  AND
         LEN(T.NODE_B) = 2  AND
         LEN(T.NODE_C) = 2
;

CREATE NONCLUSTERED INDEX IDX_A ON #TRIANGLES_FILTERED (NODE_A);

CREATE NONCLUSTERED INDEX IDX_B ON #TRIANGLES_FILTERED (NODE_B);

CREATE NONCLUSTERED INDEX IDX_C ON #TRIANGLES_FILTERED (NODE_C);

WITH
CONNECTABLE_NODES AS
(
    SELECT   DISTINCT T1.NODE_C AS [NODE]
    FROM     #TRIANGLES_FILTERED AS T1

             INNER JOIN
             #TRIANGLES_FILTERED AS T2
             ON T1.NODE_B = T2.NODE_A  AND
                T1.NODE_C = T2.NODE_B

             INNER JOIN
             #TRIANGLES_FILTERED AS T3
             ON T2.NODE_B = T3.NODE_A  AND
                T2.NODE_C = T3.NODE_B

    WHERE    T1.NODE_A <> T2.NODE_C  AND
             T1.NODE_A <> T3.NODE_C  AND
             T2.NODE_A <> T3.NODE_C
)
SELECT   *
FROM     #TRIANGLES AS T1
WHERE    T1.NODE_A IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_B IN (SELECT * FROM CONNECTABLE_NODES)  AND
         T1.NODE_C IN (SELECT * FROM CONNECTABLE_NODES)
;

Query plan:
https://www.brentozar.com/pastetheplan/?id=B1cZC6isD
How would I rewrite the first one to be as fast as a second one?

BTW, if you're wondering what all the geometry/topology is about, I needed to know how all the triangles connect with each other in the creation of this puzzle:
https://puzzling.stackexchange.com/questions/105275/dragon-summoning-spell

Sometimes CTE has got the wrong estimation. The temp table is good at it.
So, the CTE uses those indexes because they think fewer rows are there. The reason for the slowness of the first one is RID Lookup. If you drop your indexes or add your output column as include on your index. It will faster.
There is an awesome blog post here.
I think there is no win between them. You should use them to depends on the situation. And try them both in the same situation. In this way you can see the costs.


Changes to Oracle EM 11G Database Control "Availability >> Backup Settings" don't stick Trying to configure Enterprise Manager 11G Database Control for backups for the first time. Whenever I navigate to

Availability >> Backup Settings >> Policy tab

and try to make a change, for example, enable the checkbox for Automatically backup the control file and server parameter file (SPFILE) with every backup and database structural change, then click OK button, the GUI says the settings were saved successfully, but when I revisit the page, it's always showing the default settings. (I make sure to set the correct Host Credentials at the bottom of the tab each time)

If I do it enough times, occasionally, the changes will stick the next time, but upon a 2nd (and subsequent) visits (without changing anything, just viewing the page), the settings go back to default again.

Anyone see this behavior before? The Home page doesn't show any problems.

Enterprise Manager 11.2.0.4 Database Control Backup Settings are not Retained, Scheduled Backups Fail to Run (Doc ID 2185136.1)

  SOLUTION

  Download and apply one-off Patch 19316728 to the 11.2.0.4 database
  Oracle Home according to the instructions in the associated README
  file.

If you want to set the above setting manually, just log in to the server, and:

$ rman target /
RMAN> configure controlfile autobackup on;


MySQL Import CSV with Multiple Field Separators I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator.

I am stuck doing this entire process in SQL as it is part of a larger program- so scripting is out of the question.

Example of the data:

   reportdata, commission, total, chargeback, company
   ",123,""$116.00 "",""$604.00 "",""($88.00)"", foo

I'm stumped apart from attempting to do a bunch of string operations on a temp table.

Any ideas?

Since you are not allowed to script but SQL try the following:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\"');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

There you go, another text file that has the doubled double quotes stripped.

If you want all doubled double quotes replaced with single quotes, do this UPDATE:

CREATE TABLE importweirddata( txt varchar(255) ) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'whatever_importfile.txt'
INTO TABLE importweirddata
LINES TERMINATED BY '\r\n';
UPDATE importweirddata SET txt = REPLACE(txt,'\"\"','\'');
SELECT * FROM importweirddata INTO 'improved_importfile.txt';
DROP TABLE importweirddata;

Now, go import that file (improved_importfile.txt) and have fun with it.

Give it a Try !!!


Not able to connect to Mongodb vie mongo shell I am no able to connect to mongodb via mongo shell.
command:
sudo mongo --tls --tlsCertificateKeyFile server.pem --tlsCAFile CA_INTERMEDIATE --host "mongodb://xxx:xxx@some-url:27020/data_base?ssl=true"

error:
MongoDB shell version v4.4.3
connecting to: mongodb://some-url:27020/data_base?compressors=disabled&gssapiServiceName=mongodb&ssl=true
{"t":{"$date":"2021-01-08T14:55:17.158Z"},"s":"E",  "c":"NETWORK",  "id":23256,   "ctx":"js","msg":"SSL peer certificate validation failed","attr":{"error":"SSL peer certificate validation failed: unable to verify the first certificate"}}
Error: couldn't connect to server some-url:27020, connection attempt failed: SSLHandshakeFailed: SSL peer certificate validation failed: unable to verify the first certificate :
connect@src/mongo/shell/mongo.js:374:17
@(connect):2:6
exception: connect failed
exiting with code 1

However, this works when I am using the same keys, certificate and parameters with mongoDB Compas.

can someone please tell me what I am doing wrong?

I suspect Compass is falling back on the system trust store while the command line tool is not.
Capture the certificate presented by the server:
openssl s_client -connect some-url:27020 </dev/null > retrieved_cert.pem

And then validate using the CA certificate:
openssl verify -CAfile CA_INTERMEDIATE retrieved_cert.pem

If validation succeeds, you should see
retrieved_cert.pem: OK

If that doesn't work, take a look at the subject and issuer of each certificate, and ensure that the file passed to --tlsCAFile contains the self-signed root certificate at the head of the chain.


Why is MySQL Server software so huge? How come MySQL Server software is bigger (in size) than SQLite? What makes it so big? Isn't it just about some network protocols and code that comes to save the tables and its entries?

SQLite, with the same language as MySQL, comes with only a 300kb library, but the MySQL Server is around 600mb in size.

Consider that MySQL has a 31MB source code download. PostgreSQL has a 16MB source code download. SQLite has a 1.45MB source code download - please at least start by comparing like with like - comparing 300K with 600 MB isn't fair!

While it's true that SQLite can be made smaller, it's also possible to do the same with MySQL - there is an embedded version (I've never used it, so can't speak to its size).

MySQL also comes with a bunch of utilities (for backing up and checking integrity for example and there are many more - don't have a running system handy to go through them all).

Furthermore, MySQL supports a bunch of storage engines - SQLite does not.

And now the biggie - MySQL supports concurrent updates - SQLite does not. As D. Richard Hipp himself says, "SQLite is not designed to compete with Oracle. SQLite is designed to compete with fopen()".

I think he's being modest - his achievement with SQLite is impressive - but it is not a multi-user RDBMS. MySQL can certainly compete in many spheres that Oracle moves in which SQLite could never do - can you imagine running a telecoms system without concurrent updates? Just my 0.02c.

[EDIT in response to the comment of @jynus]

Yes, indeed, those figures are very interesting.

SQLite 212,680 lines of code
PostgreSQL 727,260 lines of code
MySQL 2,380,079 lines of code

Seeing as all of them are written in C/C++, it is a fairer comparison of size than MB of download.

It would be even more interesting to tease out what, exactly, these "lines of code" figures correspond to.

I.e. one could ask why SQLite has the broad functionality it does on 1/10 of the MySQL code base?

Or how come PostgreSQL is so much more competent an RDBMS than MySQL with 1/3 of the code?

And/or how many of the client tools and other assorted "fluff" are included in the MySQL source code line count?

Also, given that C++ should encapsulate more functionality per line than C, this would suggest that MySQL code is even less "efficient" (by the crude measure of lines of code).

(BTW, I'm putting on fire-retardant clothing as we speak! :-) )

Great topic for discussion - not sure how long it would last here though, more's the pity!


Creating databases - no access to folder - MS SQL I am merging a few instances together which are for separate customers. They need to be separated by account access so customer X can't view customer Y's databases.

I have created a login (we'll call it Bob). Bob has the server roles dbcreator and public. Under user mapping, I have given access to the required databases.

If I use these account details in SSMS, I can see all of the databases (as If I were SA). I can only open the database I have access to but I still don't want Bob to be able to see other customers' database names. Any ideas?

Also, when using a script in my application to create a new database, it complains that I do not have access to C:\Program Files\Microsoft SQL Server\MSSQL12.IDENTITYACCESS\MSSQL\DATA

My instance has access to this folder but I do not want to give Bob the sysadmin server role. Is there a way to give Bob access to that folder without giving the sysadmin server role?

SQL Server Login

When you create a SQL Server login (be it either a native SQL Server login with password or a Windows Authenticated login without password), then this user is assigned certain default permissions/privileges. Let's have a step-by-step look at how we can achieve what you want.

1. Create SQL Server Login

USE [master]
GO
CREATE LOGIN [Bob]
    WITH PASSWORD=N'BobsPass4567#',
    DEFAULT_DATABASE=[master],
    CHECK_EXPIRATION=ON, CHECK_POLICY=ON

Reference: CREATE LOGIN (Transact-SQL) (Microsoft | Docs | SQL)

The login has been create for [Bob] and he can already connect to the SQL Server instance.

You may ask yourself: How so?

When a login is created the login receives some default permissions/privileges. Let's see what they are.

2. Check [Bob]'s Permissions/Privileges

In a SQL Server query window execute the following statements/commands:

EXECUTE AS LOGIN = 'Bob'
GO

Reference: EXECUTE AS (Transact-SQL) (Microsoft | Docs | SQL)

With the above step we are posing as [Bob] and are then going to check the default permissions/privileges. Next step:

SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL, 'LOGIN') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL,'ROLE') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL, 'USER') AS fmp

Reference: sys.fn_my_permissions (Transact-SQL) (Microsoft | Docs | SQL)

We are checking the default permissions of [Bob] for various so called securable classes (securable_class). We only receive return values for the first statement. These are:

entity_name | subentity_name | permission_name
------------+----------------+-------------------
server      |                | CONNECT SQL
server      |                | VIEW ANY DATABASE

These basic permissions seem reasonable. Allowing the login to connect to the SQL Server instance is a good thing and letting the login view any database seems pretty adequate.

Oh, but you don't want the login [Bob] to be able to view any database? Well let's DENY [Bob] that privilege.

3. DENY Privilege to [Bob]

The following command will deny [Bob] the ability to view any database on the server:

You will have to open a new Query Window as your current Query is running as [Bob] and [Bob] can't revoke his own permissions/privileges. Try it....

DENY VIEW ANY DATABASE TO [Bob]
GO

Reference: DENY Server Permissions (Transact-SQL) (Microsoft | Docs | SQL)

...and because you shouldn't trust anything you read on the internet, you should go ahead and verify that [Bob] can't view any databases:

EXECUTE AS LOGIN = 'Bob'
GO
SELECT * FROM sys.databases
GO

Which returns:

name   | database_id | source_database_id | owner_sid | ...
-------+-------------+--------------------+-----------+-------
master | 1           | NULL               | 0x01      | ...
tempdb | 2           | NULL               | 0x01      | ...

Even though you have denied the VIEW ANY DATABASE privilege to [Bob], he'll still have the ability to view the master and tempdb database. He can't view the msdb or model databases. Why? Well, this topic is worth a separate discussion...

4. GRANT Privileges to Create Databases

[Bob] is now able to connect to the SQL Server instance, but unable to view any database (other than master and tempdb) or perform any other task. But he needs to be able to create databases. Let's assign the right privilege:

GRANT CREATE ANY DATABASE TO [Bob]
GO

Hmmm. Let's see if [Bob] can create a database now:

EXECUTE AS LOGIN = 'Bob'
GO
CREATE DATABASE BobsDatabase
GO

And let's see if [Bob] can view his own database:

SELECT * FROM sys.databases
GO

This returns:

name         | database_id | source_database_id | owner_sid                          | ...
-------------+-------------+--------------------+------------------------------------+---------
master       |  1          | NULL               | 0x01                               | ...
tempdb       |  2          | NULL               | 0x01                               | ...
BobsDatabase | 19          | NULL               | 0x9C426DE3F33E5340A71D96D8635900F0 | ...

So we have proven that [Bob] can create his own databases, but not view all databases.

  All these settings and checks will work if you open up a separate query window with [Bob]'s credentials and perform [Bob]'s action in that window, except when modifying his own permissions/privileges.

Folder Access

The folder access issue is a bit more complex. Needless to say, [Bob] doesn't have access to my local C:\SQL\Data\... folder, because [Bob] is a native SQL Server login. However, he does have the CREATE ANY DATABASE privilege, so he doesn't actually require access to the folder itself; the SQL Server instance's service account will do that for him.

Your issue with the folder access might have to do with how you are executing the script in your application. Is it [Bob] that is executing the statement or some unknown application pool account? Does that account have privileges to create any database? Or could you pose as [Bob]?

  Hint: EXECUTE AS LOGIN = 'Bob'

This does require that the application pool account have adequate SQL Server privileges. You might have to rewrite that part in your code or seek further advice on how to pose as somebody else.


Objective, SQL Server 2005 and exporting data using **bcp** I was tasked w/ getting data out of an obsolete document management system running on top of an instance of MS SQL Server.

Trying to export > 30000 documents through the app is not an option, as it shoots itself in the foot if I pick more than 10 items.

Trying to export the database (or individual tables) from SQL Server Management Studio renders a variety of errors, namely Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Destination - documents" (that happened when trying to export to either Excel or CSV).

Given the fickle state of the machine and my utter lack of knowledge in all things Microsoft I don't want to try and mess with the SQL Server installation by adding components.

The only way I managed to extract ANY data from tables is via bcp.
That, however, produced an other interesting complication.  Having explicitly chosen TAB as a delimiter I get e.g. for a table w/ 61 (or 65  respectively) columns output that has a variety of column numbers for each row:

awk -F"\t" '{print NF}' folders.txt | sort | uniq -c | sort -n
    3 0
    5 1
    9 17
    9 45
15277 61

awk -F"\t" '{print NF}' documents.txt | sort | uniq -c | sort -n
    1 4
    1 62
   14 0
   24 1
   41 29
   41 37
31291 65

Does anyone have advice on how to get sensible results out of bcp?

Here's a sample query:

bcp "select * from objProd.dbo.documents" queryout z:\test.txt -c -T

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000
1000 rows successfully bulk-copied to host-file. Total received: 7000
1000 rows successfully bulk-copied to host-file. Total received: 8000
1000 rows successfully bulk-copied to host-file. Total received: 9000
1000 rows successfully bulk-copied to host-file. Total received: 10000
1000 rows successfully bulk-copied to host-file. Total received: 11000
1000 rows successfully bulk-copied to host-file. Total received: 12000
1000 rows successfully bulk-copied to host-file. Total received: 13000
1000 rows successfully bulk-copied to host-file. Total received: 14000
1000 rows successfully bulk-copied to host-file. Total received: 15000
1000 rows successfully bulk-copied to host-file. Total received: 16000
1000 rows successfully bulk-copied to host-file. Total received: 17000
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
1000 rows successfully bulk-copied to host-file. Total received: 20000
1000 rows successfully bulk-copied to host-file. Total received: 21000
1000 rows successfully bulk-copied to host-file. Total received: 22000
1000 rows successfully bulk-copied to host-file. Total received: 23000
1000 rows successfully bulk-copied to host-file. Total received: 24000
1000 rows successfully bulk-copied to host-file. Total received: 25000
1000 rows successfully bulk-copied to host-file. Total received: 26000
1000 rows successfully bulk-copied to host-file. Total received: 27000
1000 rows successfully bulk-copied to host-file. Total received: 28000
1000 rows successfully bulk-copied to host-file. Total received: 29000
1000 rows successfully bulk-copied to host-file. Total received: 30000
1000 rows successfully bulk-copied to host-file. Total received: 31000

31333 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 3094   Average : (10127.02 rows per sec.)

Note: This is my first time ever using SQL Server, I'm a Linux admin, and had good exposure to Postgres and some to MySQL.  The state of the windows instance and the DB have not changed since long before I joined my current employer.

Right.

It would appear that free-form text fields in several rows (and varied columns) in two of the tables had embedded newline/carriage return characters, which then messed up the field-count (obviously).

I now am just exporting 5 (and 3, respectively) columns from those tables, and the problem is gone away.  Feeling kind of silly after this.

Thanks everyone for trying to help.


Why did MySQL remove the query cache feature after version 8.0? Why did MySQL remove the query cache feature after version 8.0?

There is a detailed blog from the MySQL server team about this, where Matt Lord says:

The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines.
We considered what improvements we could make to query cache versus optimizations that we could make which provide improvements to all workloads.
While these choices themselves are orthogonal, engineering resources are finite.  That is to say that we are shifting strategy to invest in improvements that are more generally applicable to all workloads.


How to handle divide by zero in GENERATED columns in MySQL I am using GENERATED AS columns in MySQL 5.7 as follows:
ALTER TABLE report_user
MODIFY avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/(trans_out_count))) STORED;

but sometimes the trans_out_count could be 0, then the GENERATED AS column will give a divide by zero error. So, what I did was:
ALTER TABLE report_user
MODIFY avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/(trans_out_count + 0.00001))) STORED;

but can be a little problematic - for example for 100,000/1.00001, the right value is 100,000, but the actual value it not correct 9,999.90. What should I do to handle this problem? This is the table DML:
CREATE TABLE `report_user` (
  `trans_into_count` int(11) NOT NULL DEFAULT '0' COMMENT '转入次数',
  `trans_out_count` int(11) NOT NULL DEFAULT '0' COMMENT '转出次数',
  `trans_into_amount` bigint(11) NOT NULL DEFAULT '0' COMMENT '转入金额',
  `trans_out_amount` bigint(11) NOT NULL DEFAULT '0' COMMENT '转出金额',
  `avg_trans_into_amount` bigint(20) GENERATED ALWAYS AS (round((`trans_into_amount` / (`trans_into_count` + 0.00001)),0)) STORED,
  `avg_trans_out_amount` bigint(20) GENERATED ALWAYS AS (round((`trans_out_amount` / (`trans_out_count` + 0.00001)),0)) STORED,
  `user_name` varchar(256) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '用户名',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID'
) ENGINE=InnoDB AUTO_INCREMENT=36149 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='提转统计';

There are three ways to handle DIVIDE BY ZERO error in your case :

1) USING IF() :

ALTER TABLE report_user
modify avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/if(trans_out_count,0,1))) STORED;

2) USING NULLIF() :

ALTER TABLE report_user
modify avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/nullif(trans_out_count,1))) STORED;

3) USING GREATEST() :

ALTER TABLE report_user
MODIFY avg_trans_out_amount bigint
GENERATED ALWAYS AS (round(trans_out_amount/greatest(trans_out_count,1))) STORED;


Get list of students having actions in Oracle I have a table of student actions (id, student_id, type, and quarter). I need to get a list of all the students, along w/ a flag of whether they had a particular action in the current quarter, and the past quarter but I'm not sure how to do this in Oracle SQL.

student_actions table

Assuming that the current quarter is 3, and that I'm only interested in the Expulsion and Probation actions how would I get a result set like the one I've mocked up below?

expected results

This is simply an aggregation problem.

select
  student_id,
  count(case when type in ('Expulsion','Probation') and quarter=3-1 then 1 end) as previous,
  count(case when type in ('Expulsion','Probation') and quarter=3 then 1 end) as "CURRENT"
from student_actions
group by student_id;

See http://sqlfiddle.com/#!4/f4263/10/0


SQL Server Linked Server - Timeout Error I am trying to create a linked server between two SQL Servers (ServerA and ServerB).  ServerA is hosted by Windows Server and ServerB is hosted on a local Windows desktop.  I want to create a linked server from ServerA to ServerB.  I'm doing this using SSMS GUI.  ServerB is a named instance (ie ServerB\MyInstance).  Under "Security", I am using "Be made under this security context" and I enter the "sa" account.  However, I'm getting this error "Login timeout expired".  The weird thing is, I can establish the connection using c:>sqlcmd -SServerB\MyInstance -Umylogin -Pmypw from my desktop.

The credential is the same in both cases.  Can you someone tell me what I'm doing wrong?  Would it be a firewall issue?  Not sure about this since I can connect via sqlcmd.

Thanks

It could be a firewall issue.
You have to be able to connect to Server B from Server A. You said that you can connect to Server B from your desktop, but this only proves there is no problem between your desktop and Server B.
For a fair test, you have to log in to Server A and attempt to log in to Server B from Server A.


Migrating MS Access application I'm advising a small non-tech company that outsources their software, hardware, and support services to a local IT provider. This company is preparing to migrate several of their internal applications from an in-house managed server to a cloud-based provider.

One of these applications is a business-critical MS Access-based inventory management tool. This tool is unfortunately built and maintained by one person located overseas, so we expect very little support for any custom development and have to treat the application as a black box.

Sales employees are currently accessing the in-house Access application via VPN and virtual terminals, connections to which have been EXTREMELY unstable. Additionally, the application can hang for up to half an hour intermittently, which has gotten worse over the 2 years they have been using it. One of the primary goals of this cloud migration are to solve these performance/stability issues.

Final note: their IT provider is looking to use Anchor as a cloud vendor.

My question: How can we best resolve these performance issues by moving "to the cloud?"

The "best" solution which we found was:
1. migrate the Access tables to an SQL tables
2. Migrate view & stored procedures to the SQL server
3. make the MS-Access run from a network location. This way there are two Ms-Access: one for the development work, the other for everyday use.
This way ms-access can scale.
As the developer who has done this I can tell you this:
Migrating the tables is pretty strait-forward (there's even an option for that in Access). Then you need to check that the data types have stayed the way the are supposed to. SQL might use a data size which is bigger then it should be (currency shouldn't be money, it should be decimal (18,2); avoid floats if you don't need to; use the smallest data type possible; strings have migrated correctly; etc).  Also: when Access uses SQL as it's back end it can only have one column as a primary key, otherwise it doesn't link properly. So if you had a primary key which was composed of two columns, you need to add to that table an identity column.
Once that is established you have a split Access application which means better availability. Yet, you need to migrate the views and stored proc: basically copy & paste the code form Access to SQL. Since the syntax is different you need to check for the following pitfalls:
(1) for example, now there's IIF in SQL so that sort that part out.
(2) parameters which are incorporated in the stored proc are used in a different syntax in SQL so that's also something to be aware of.
(3) Avoid ORDER BY in views. (don't use the SQL option, just don't use it and do the sorting in Access)
(4) Last but not least: in Access the "true" is -1, in SQL it is 1. It is very important to check that the WHERE clause says WHERE flag <> 0, and not flag = -1.
Good luck!


Distrubution1 Database Offline - I have no clue what this is I have a SQL Server 2012 installation with a hosting provider that is an active/passive high-availability setup. (Exact version 11.0.5058)

We recently migrated to this server from a 2008 install in a different data center.

There is a database that now shows up in SSMS called "distribution1".  It appears to be offline.

I know there is a System Database called distribution, but I have no idea what this distribution1 database is.

My hosting provider said they couldn't provide any advice about it and to contact the developer and well... that's me.

This may be a very dumb question, but is it safe to assume that this database is not doing anything since it is offline anyway?

Offline is offline; the database can't be "doing anything" unless there is application code somewhere that changes it from offline to online and then back again.

As to whether you can drop it, well, we have no idea. It may be cruft, but it may be important. (Think about me asking if this cable I found in my basement goes to anything important - I wasn't actively using it, obviously, but I may need to charge some device tomorrow that needs it.)

To get a better idea of what this database is, you can try to get some clues or trigger your memory in various ways:

Take a look at revealing properties, such as create date / owner.
Bring the database online temporarily, and inspect the names of tables and the data inside them. If you are afraid to bring it online long enough to inspect it, you can bring it online just long enough to set it to single_user and back it up, then take it back offline, then restore a copy as a different name, and inspect the copy.
Review the default trace (see some examples here) to see if there are any significant events (but the default trace rolls over, so it may not go back far enough).


Instant File Initialization | Lock Pages in Memory Issues I have a server that I'm coming onto performance tune and for the life of me I can'g get these rights enabled.  So I ran

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami
nofault\administrator

Then

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami /priv

PRIVILEGES INFORMATION
----------------------

Privilege Name                  Description                               State
=============================== ========================================= ========
SeLockMemoryPrivilege           Lock pages in memory                      Disabled
SeIncreaseQuotaPrivilege        Adjust memory quotas for a process        Enabled
SeSecurityPrivilege             Manage auditing and security log          Disabled
SeTakeOwnershipPrivilege        Take ownership of files or other objects  Disabled
SeLoadDriverPrivilege           Load and unload device drivers            Disabled
SeSystemProfilePrivilege        Profile system performance                Disabled
SeSystemtimePrivilege           Change the system time                    Disabled
SeProfileSingleProcessPrivilege Profile single process                    Disabled
SeIncreaseBasePriorityPrivilege Increase scheduling priority              Disabled
SeCreatePagefilePrivilege       Create a pagefile                         Disabled
SeBackupPrivilege               Back up files and directories             Disabled
SeRestorePrivilege              Restore files and directories             Disabled
SeShutdownPrivilege             Shut down the system                      Enabled
SeDebugPrivilege                Debug programs                            Enabled
SeSystemEnvironmentPrivilege    Modify firmware environment values        Disabled
SeChangeNotifyPrivilege         Bypass traverse checking                  Enabled
SeRemoteShutdownPrivilege       Force shutdown from a remote system       Disabled
SeUndockPrivilege               Remove computer from docking station      Disabled
SeManageVolumePrivilege         Perform volume maintenance tasks          Disabled
SeImpersonatePrivilege          Impersonate a client after authentication Enabled
SeCreateGlobalPrivilege         Create global objects                     Enabled
SeIncreaseWorkingSetPrivilege   Increase a process working set            Disabled
SeTimeZonePrivilege             Change the time zone                      Disabled
SeCreateSymbolicLinkPrivilege   Create symbolic links                     Disabled

No prob I go Local Security > Local Policies > User Rights Assignment grant the LockPages in Memory to Network Administrator and perfom volume maintenance tasks.  No Good.

I tried giving the privileges to everyone ( I know bad Idea but just to test )  still no good.  And yes I restarted SQL server after each try.  Has anyone had any similiar issues with this, real cheap performance gain and I usually have no problem with this.

Thanks

Try setting the services to run as domain/machine account, and give the permissions to that account


Pre- and Post- Snapshot Replication Scripts Not Executing and No Errors I have a Snapshot Replication Publication setup on Server 1.
Server 2 subscribes to the publication on Server 1.
Data is replicating successfully.
The Distributor Agent and Agent job for the Subscriber of Server 2 runs on Server 1 (where the Distributor database lives).
I created a stored procedure on Server 2 to drop (pre replication) and create (post replication) schemabound entities on Server 2's subscriber database. (I've done this successfully in the past because Snapshot Replication can stall if there's entities in the subscriber database that are schema bound to the replicated entities.)
I also created a script that executes this stored procedure from Server 1 via a linked server to Server 2. (I've verified the linked server is setup correctly and I can query data from and manually execute the stored procedure via the linked server from Server 1 to Server 2.)
I've added this script to the Snapshot Publication's "Snapshot" Properties:

Initially I received an error in the Snapshot Agent job because my Snapshot Agent didn't have file share access to the path in the scripts, but I added that account and then the error went away and the Snapshot job finishes successfully now.
But the entities in the SQL script don't get dropped or created, and I don't see an errors anywhere. I've also tried moving the before and after script file to a folder share local to Server 1 and referencing it with a UNC path (as opposed to the network path in the screenshot) and still no change in behavior.
I've updated the TEST.sql script with a really simple test with the following code:
INSERT INTO DatabaseA.dbo.Table1 (Field1) -- DatabaseA is on Server1, so it should insert to the local table
SELECT 'Test' AS Field1

When I re-run the Snapshot Agent, it still runs successfully, but the table doesn't get inserted into (on Server 1).
Why could my pre- and post- scripts not be running but I'm not receiving any errors either?
(I also have another Publication for Transactional Replication on Server 1 that is syncing data just fine to Server 2 in the same database as the Snapshot Replication. But even when I set the pre- and post- scripts on the "Snapshot" Properties of the Transactional Publication on Server 1, I still get no errors and the SQL doesn't execute.)
Edit: I also just tried moving the script file to a local folder of Server 1, giving the Snapshot Agent read access to that folder and script file, and re-running the Snapshot Agent, and still nothing. No execution of the script, and no errors.
BUT interestingly, if I manually modify the Subscription Agent job itself and add a step to execute the same exact SQL query, it DOES work.
I also noticed the SQL Agent job runs under a different SQL account "sqlmin" then I expected for certain steps (new steps I created, or the first step of the job called "Snapshot Agent startup message" - I guess because these steps aren't of type "REPL-Distributor"). But the other pre-existing steps execute as the SQL Agent I designated, which is "DBReplication". The "sqlmin" account I know has very minimal SQL permissions on both Server 1 and Server 2, could this be why the pre- and post- replication scripts aren't executing (but I'm also not receiving an error anywhere) when I set the scripts on the pre- and post- fields of the Snapshot Publication - Snapshot properties?
Also additional details about the distributor account (who is also my Snapshot Agent account):
The distributor is a Windows AD account that has a SQL LOGIN on both Server 1 and Server 2 and with the SQL role of db_owner in both the source and target replicated databases as well as db_owner on the distribution database (which lives on Server 1).

Just a quick summary of some of the things I would try, when analysing the various options....

Have you tried passing the four part object identifier in the scripts?
Instead of using:
... Database.Schema.Table ...

Try using:
... Server.Database.Schema.Table ...

Or in your case where you are using the Linked Server it would be:
... LinkedServerName.Database.Schema.Table ...

Where you define the values as:
LinkedServerName : The name of the linked server as you defined in the Linked Server connection.
Database ........: The name of the database
Schema ..........: The name of the schema
Table ...........: The name of the table

It depends on where the script has to modify data. That's not quite clear in your description, but seeing as you did mention Linked Servers I thought I'd mention it.

The script is executed on the Subscriber as is explained in:

You can specify scripts to execute at the Subscriber before or after the snapshot is applied. Scripts can be used for a variety of reasons, such as creating logins and schemas (object owners) at each Subscriber.

And the additional information provided here is pretty important:

The script is run by launching the sqlcmd utility. Before deploying a script, run it with sqlcmd to ensure it executes as expected. The contents of scripts that are executed before and after the snapshot is applied must be repeatable. For example, if you create a table in the script, you should first check for its existence and take appropriate action if it exists. The script must be repeatable because if you need to reinitialize a subscription for which the script has already been applied, the script will be applied again when the new snapshot is applied during reinitialization.

Reference: Modify Snapshot Initialization Options for SQL Replication (Microsoft | SQL Docs)
If the script doesn't work together with the account that is used for the subscription agent and in conjunction with sqlcmd, then it isn't going to work in the subscription.

Hint
Use ; at the end of every line in the scripts.
Add GO in between lines.

Then you might want to have a look at the article Troubleshooter: Find errors with SQL Server transactional replication. I'm not saying you havent't done that yet, but you didn't say you had. Who knows, maybe you can find the issue in the replication agent's job history...


Is there a way to prevent a database from being deleted from the SSMS GUI? Using SQL Server Management Studio, it is possible to right-click a database in the Object Explorer and delete it. (You can also delete a database using T-SQL, not demonstrated here.) I accidentally deleted the wrong database (thankfully, I was able to restore from a recent backup).

Still, it got me thinking. Is there a way to configure a database against accidental deletion -- by GUI or by T-SQL Script? If I actually need to remove a specific database, then I should have to take a few deliberate steps to enable database deletion. Otherwise the database cannot be deleted.

I searched for "prevent database deletion" and all I found were how-to articles about how to prevent table row data from being deleted -- not accidental deletion of the entire database.

You cannot prevent it directly from the GUI, as far as I know.  You can create a Server-scoped trigger to monitor for DROP_DATABASE events and throw an error to the user if invoked.

USE [master]
GO
CREATE DATABASE [CannotDropMe];
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    RAISERROR('You cannot drop databases on this server.', 16,1);
    ROLLBACK;
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO

-- DB still exists
SELECT * FROM sys.databases WHERE name = 'CannotDropMe';
GO

Also if you want to log the statements (attempted DROP DATABASE) along with any other info you want, you can alter the trigger to something like this.

/* If logging we need a log table */
CREATE TABLE [SomeDB].dbo.[DropDatabaseLog] (Command VARCHAR(100), DBUser NVARCHAR(100), ServerName NVARCHAR(100), EventTime DATETIME DEFAULT(GETDATE()));
GO

CREATE TRIGGER TRG_PreventDropDB ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    DECLARE @CommandText NVARCHAR(MAX);
    SELECT @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
    ROLLBACK;

    SET NOCOUNT ON;
    INSERT INTO [SomeDB].dbo.[DropDatabaseLog] (Command, DBUser, ServerName)
    SELECT @CommandText,
        CURRENT_USER,
        @@SERVERNAME
    RAISERROR('You cannot drop databases on this server.', 16,1);
END
GO

-- ERROR
DROP DATABASE [CannotDropMe];
GO

SELECT * FROM [DropDatabaseLog];

The database(s) will still exist, and you get some info on potentially who to yell at :)

Edit:  I blindly answered this without seeing Shaulinator's answer and links.  Check out both links, the first shows how you can filter on specific databases, which may be useful.


Negative number of rows inserted I just ran a rather simple INSERT statement (that I think processed a lot of rows), and I got the message:

  -1,311,750,183 rows inserted

Should I be concerned about the fact that it's negative?

It is possible that the number of rows exceed the value of PLS_INTEGER. Here is the quote from Oracle on %ROWCOUNT.

  %ROWCOUNT Attribute: How Many Rows Were Fetched? %ROWCOUNT returns:
      Zero after the explicit cursor is opened but before the first fetch
      Otherwise, the number of rows fetched (a PLS_INTEGER)
      Note:
      If the number of rows exceeds the maximum value for a PLS_INTEGER, then SQL%ROWCOUNT returns a negative value. For information about
  PLS_INTEGER, see "PLS_INTEGER and BINARY_INTEGER Data Types".


How to make a mysql statement for sent log I have a table called "make_sent_log"

CREATE TABLE IF NOT EXISTS `make_sent_log` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `transaction_id` varchar(255) NOT NULL,
    `total_base` int(11) NOT NULL,
    `delivered` int(11) NOT NULL,
    `pending` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `transaction_id` (`transaction_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

My goal is store delivery status of mobile numbers

For ex: sent bulk sms to 5 mobile Numbers

one unique transaction id will generated then I store it to the transaction_id column ex:- transaction_id = trans_1

I sent sms to 5 Mobile Numbers so I store it to the total_base column
ex:- total_base = 5

If sms sent successfully to the only 3 Mobiles, delivered = 3

So other numbers in pending stage so pending = 2

So My table looks like

INSERT INTO `make_sent_log` (`id`, `transaction_id`, `total_base`, `delivered`, `pending`) VALUES
(1, 'trans_1', 5, 3, 2);

My doubt is after some 1 or 2 Minutes, 2 pending numbers can be delivered so How to set

delivered = 5

And pending = 0

See below image

In next iteration, you may need to check if there are pending numbers to send sms. If pending count is greater than zero then need to send sms to those pending numbers.

track success_count after resending sms to pending numbers.

Then, need to increment delivered count and decrement pending count with this success_count.

So,

delivered  = delivered + success_count
pending = pending - success_count

then you may need to execute update on make_sent_log table with this updated delivered and pending counts.

update `make_sent_log` set delivered = delivered, pending = pending where transaction_id = 'trans_1';

Continue this iteration cycle until pending count = zero.


Modification to GEQO (Genetic Query Optimization) of PostgreSQL I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these possible join sequences at random. Source :
http://www.postgresql.org/docs/9.3/static/geqo-pg-intro.html

My question: how to modify the GEQO function if I definitively know the right join sequence, so that I don't have to search different join sequences. For ex., if I knew that the best way of joining the 4 relations is 4-1-3-2, I needn't have to check for other permutations.

There aren't any good materials on how GEQO is implemented in PostgreSQL. PostgreSQL only gives the overall view of the GEQO functionality but doesn't explain much.

Or could I achieve this functionality in the standard_join_search() itself without using GEQO?

One way you can do this without the need to mess around with GEKO is by using CTE.

CTE are optimisation barriers, hence you could wrap the joins inside CTEs in the order you want and PG will be forced to do so.

For example if we want to force the DB to first join t1 with t2 and only then with t4 we could run something like:

explain
with j1 as (select *,t1.c4 as t1c4 from t1 join t2 on (t1.c2=t2.id))
    ,j2 as (select * from j1 join t4 on (t1c4=t4.id))
select * from j2;

This will result in:

                                  QUERY PLAN
-------------------------------------------------------------------------------
CTE Scan on j2  (cost=51485.00..67785.00 rows=815000 width=64)
CTE j1
 ->  Hash Join  (cost=3473.00..14521.00 rows=815000 width=40)
       Hash Cond: (t2.id = t1.c2)
       ->  Seq Scan on t2  (cost=0.00..26.30 rows=1630 width=20)
       ->  Hash  (cost=1637.00..1637.00 rows=100000 width=20)
             ->  Seq Scan on t1  (cost=0.00..1637.00 rows=100000 width=20)
CTE j2
 ->  Hash Join  (cost=289.00..36964.00 rows=815000 width=64)
       Hash Cond: (j1.t1c4 = t4.id)
       ->  CTE Scan on j1  (cost=0.00..16300.00 rows=815000 width=44)
       ->  Hash  (cost=164.00..164.00 rows=10000 width=20)
             ->  Seq Scan on t4  (cost=0.00..164.00 rows=10000 width=20)
(13 rows)

This is just an example, you can change it around as needed - in any case PG can't change the order between the different CTEs.

Hope it helps :)


MySQL - Select when last entry before timestamp has specific value I can find rows in a table where the last row has a specific value. However, I'm having a hard time finding them when I need to limit the max, or min, timestamp.

Let's say I have table 'people'.

id   name   value   date
a    last   jones   2018-04-03 01:00:00
a    last   smith   2018-04-03 03:03:03
a    last   johns   2018-04-04 02:02:02
b    last   johns   2018-04-03 12:12:12
b    last   smith   2018-04-04 03:03:03
c    last   smith   2018-04-03 11:11:11
c    last   johns   2018-04-03 17:17:17
c    last   jones   2018-04-05 01:01:01

I want to get the id's where the last row has name='last' and value='johns'

SELECT  distinct a.id
FROM    people a
left JOIN people b
   ON (a.id = b.id and a.name=b.name and a.date < b.date)
where b.date is null
  and a.name = 'last'
  and a.value = 'johns';

This gives me

id
--
a

Great. However, now I want to limit my search where date < '2018-04-04 00:00:00', which should return

id
--
b
c

I've tried adding a.date < and b.date < in the LEFT JOIN ON, tried adding a.date < to the WHERE but I'm not getting the expected results. Thanks!

First, always post with create table statements and insert statements. You will get much more attention if people dont have to do a lot of work to be able to help you. After all, your time is not more valuable than everyone else time:

create table people
( id char(1) not null
, name char(4) not null
, value varchar(20) not null
, ts timestamp not null);

insert into people (id, name, value, ts)
values ('a',    'last',   'jones',   '2018-04-03 01:00:00')
     , ('a',    'last',   'smith',   '2018-04-03 03:03:03')
     , ('a',    'last',   'johns',   '2018-04-04 02:02:02')
     , ('b',    'last',   'johns',   '2018-04-03 12:12:12')
     , ('b',    'last',   'smith',   '2018-04-04 03:03:03')
     , ('c',    'last',   'smith',   '2018-04-03 11:11:11')
     , ('c',    'last',   'johns',   '2018-04-03 17:17:17')
     , ('c',    'last',   'jones',   '2018-04-05 01:01:01');

It might be easier to grasp your query if you instead of a left join uses a NOT EXISTS predicate (rows where it does not exists a row after ..., i.e. last row):

select id
from people a
where not exists (
    select 1
    from people b
    where a.id = b.id
      and a.name=b.name
      and a.ts < b.ts
)
and a.name = 'last'
and a.value = 'johns';

If you are interested in the situation before a certain point in time, add that predicate to both inner and outer select:

select id
from people a
where not exists (
    select 1
    from people b
    where a.id = b.id
      and a.name=b.name
      and a.ts < b.ts
      and b.ts < '2018-04-04 00:00:00'
)
and a.name = 'last'
and a.value = 'johns'
and a.ts < '2018-04-04 00:00:00';


Can we use subquery in GRANT statement in postgres I want to grant select privilege on few selected tables.

Can I use sub-query in GRANT statement????

I have tried the following query:

GRANT SELECT ON TABLE
(SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' ) as  flttab
FROM information_schema.tables
where table_schema = 'try3' AND table_name like 'ph1_part_03%') a TO xyz;

But I have got the below error:

  ERROR:  syntax error at or near "("
  LINE 5: GRANT SELECT ON TABLE  (SELECT array_to_string( array_agg(ta...
                                  ^

  ********** Error **********

  ERROR: syntax error at or near "("
  SQL state: 42601
  Character: 282

No that's not possible.

The typical solution for this kind of problems is to either use dynamic SQL, or to use a query that generates the desired statement. Then spool that to a script and run the script.

To use dynamic SQL you can use a DO block

do
$body$
declare
  table_list text;
begin
  SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' )
    into table_list
  FROM information_schema.tables
  where table_schema = 'try3'
  AND table_name like 'ph1_part_03%';

  if table_list is not null then
    execute 'GRANT SELECT ON '||table_list||' TO xyz';
  end if;
end;
$body$


Speed Up Cross Apply Without Index Hint I have a very small table with 12 rows in it that can be created with the following statement:

CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY,
                            ScoreGoal float NOT NULL
                           );

I have another table with ≈100M rows in it that can be created with the following statments:

CREATE TABLE dbo.SlowCrossApply(RecordKey nvarchar(12) NOT NULL,
                                Score1 decimal(3, 2) NOT NULL,
                                Score2 decimal(3, 2) NOT NULL,
                                Score3 decimal(3, 2) NOT NULL,
                                Score4 decimal(3, 2) NOT NULL,
                                Score5 decimal(3, 2) NOT NULL,
                                Score6 decimal(3, 2) NOT NULL,
                                FromToday bit NOT NULL
                               );

ALTER TABLE dbo.SlowCrossApply ADD CONSTRAINT i01PK PRIMARY KEY CLUSTERED(RecordKey ASC)
    WITH(FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

CREATE NONCLUSTERED INDEX i02TodayRecords ON dbo.SlowCrossApply(FromToday)
    INCLUDE (Score1, Score2, Score3, Score4, Score5, Score6)
    WHERE FromToday = 1
    WITH(FILLFACTOR = 100, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

i02TodayRecords has ≈1M rows in it. When I run the following query—I struggled formatting it to both look clean and prevent a horizontal scrollbar—it takes over 5 minutes to finish:

SELECT b.RecordKey,
       COALESCE(NULLIF(ROUND(((0.95 * (ROW_NUMBER() OVER(PARTITION BY a.Prefix
                                                         ORDER BY b.Score6 ASC
                                                        ) - 1
                                      )
                              )
                              / COALESCE(NULLIF(COUNT(*) OVER(PARTITION BY a.Prefix) - 1, 0
                                               ), 1
                                        )
                             ) + 0.005, 2
                            ), 0.96
                      ), 0.95
               ) AS NewScore
FROM (SELECT LEFT(s.RecordKey, 2) AS Prefix,
             CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS int) AS Quant
      FROM dbo.SlowCrossApply AS s
      CROSS JOIN dbo.SmallTable AS sm
      WHERE s.FromToday = 1 AND sm.ScoreMonth = MONTH(GETDATE())
      GROUP BY LEFT(s.RecordKey, 2), sm.ScoreGoal
     ) AS a
CROSS APPLY (SELECT TOP(a.Quant) s2.RecordKey, s2.Score6
             FROM dbo.SlowCrossApply AS s2
             WHERE s2.FromToday = 1 AND s2.Score6 > 0 AND LEFT(s2.RecordKey, 2) = a.Prefix
             ORDER BY s2.Score6 DESC
            ) AS b;

The outer subquery returns only 10 rows; and if I supply a hint to use i02TodayRecords or put the results of the outer subquery in a table variable, it takes less than 1 second. The final result returns just over 8000 rows.

The execution plan shows that 64% of the cost is due to an eager index spool on the clustered index in the Cross Apply portion.

I know the index hint works (at least for now), but I'm hoping to avoid using one. Ideally, I wouldn't go the table variable route either. Is there something I can do to get the query optimizer to "know" to utilize i02TodayRecords? I realize there is a lot more information that is probably important, and I'll do my best to supply said information if requested.

Some potentially useful information: the indexes have less than 1% fragmentation. The statistics for both indexes have been updated via a FULLSCAN, and the database is set to have simple parameterization and parameter sniffing—unfortunately, I can't changes those settings. In regards to the latter, the query optimizer did not replace any values with parameters unlike other simple queries I have run where I was forced to use a hint to utilize a particular filtered index.

The problem you're likely facing is around SARGability, namely using the LEFT function in your WHERE clause:

LEFT(s2.RecordKey, 2) = a.Prefix

With that in there, you're stuck running the function for every row and then comparing it. You can't index for that, as-is. Putting the transformation into a CTE, view, or derived table wouldn't help, nor would writing a function to perform the manipulation.

One way around that is to create and index a computed column:

ALTER TABLE dbo.SlowCrossApply ADD LeftTwoPrefix AS LEFT(RecordKey, 2);

Which can be indexed. I'm also changing your index definition a bit:

CREATE NONCLUSTERED INDEX TodayRecords_Filtered ON
dbo.SlowCrossApply(LeftTwoPrefix, FromToday, Score6)
INCLUDE (Score1, Score2, Score3, Score4, Score5)
WHERE FromToday = 1

Another alternative would be to dump the results of your CROSS JOIN into a temp table:

SELECT     LEFT(s.RecordKey, 2) AS Prefix, CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS INT) AS Quant
INTO       #yourmom
FROM       dbo.SlowCrossApply AS s
CROSS JOIN dbo.SmallTable AS sm
WHERE      s.FromToday = 1
AND        sm.ScoreMonth = MONTH(GETDATE())
GROUP BY   LEFT(s.RecordKey, 2), sm.ScoreGoal;

You followed up with:

  ... do you agree with the FromToday [column] being the index key in
  i02TodayRecords instead of RecordKey, ignoring the specifics of the
  query I used here?

In this case, it doesn't matter much. Non-unique nonclustered indexes store clustered index key columns in all levels of the nonclustered index. See my post here: Where Clustered Index Keys Dare.

And:

  Furthermore, since RecordKey is "random", there would likely be a
  great deal of fragmentation that would occur if it were the index key
  as opposed to FromToday which will always be 1. Do those points sound
  valid?

I generally don't worry about index fragmentation, so no. I aim to create indexes to help queries. A fragmented index is much more helpful than a non-existent index, and in many circumstances you'll never notice the fragmentation.

Hope this helps!


Optimize this SQL query for performance I'm trying to optimize the following statement:

'VI'+CAST(month(GETDATE()) AS NVARCHAR)+'/'+CAST(year(GETDATE()) AS NVARCHAR)
+'/00000' +CAST(@number+1 AS VARCHAR)

The statement produces a value like VI1/2011/000002 if the @number parameter is 1.

I would like to optimize this in terms of removing redundant cast statements and providing an efficient way to concatenate the strings and integers.

No shortcut at all, there is no elegant string concatention in SQL Server

You are mixing varchar and nvarchar though: datatype precedence means the entire expression will be nvarchar.


Plpgsql (Postgres) function for sanity check While starting up my app, I need to do a sanity check within my database.
In a table sensor, there's a column seconds. Now the function should take an argument id.

It then should simply do something like:

select STARTTIME,SECONDS from sensors where SECONDS = 0;

The rows are not allowed to be 0, which only could happen if the app crashed. So if this function finds some rows, it needs to repair them by checking another table:

select "timestamp" from secure_sanity where id = *id*;

and then it should calculate STARTTIME - TIMESTAMP (from secure_sanity) and set this value to seconds.

I have no idea how to do this as I am completely new to PL/pgSQL. I would rather do this from within the app, but it feels like it would be much more efficient to do this within a function.

Could you give me a clue?

It seems you just need the query:

UPDATE sensors
 SET seconds=(SELECT sensors.starttime-"timestamp" FROM secure_sanity
              WHERE id=*id*)
   WHERE seconds=0;

For that, it's overkill to write a plpgsql function.

Or if you really want one, just replace this *id* by a function parameter put the above UPDATE query inside a CREATE FUNCTION / BEGIN / END block.


Why select statement requests exclusive locks? In postgres (9.5) select requests exclusive lock. Why?

play=# \x on
Expanded display is on.
play=#   SELECT * FROM pg_locks pl
  LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid
    where
    pl.mode = 'ExclusiveLock'
;

-[ RECORD 1 ]------+---------------------------------
locktype           | virtualxid
database           |
relation           |
page               |
tuple              |
virtualxid         | 7/16
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 7/16
pid                | 17376
mode               | ExclusiveLock
granted            | t
fastpath           | t
datid              | 1700431
datname            | play
pid                | 17376
usesysid           | 10
usename            | postgres
application_name   | psql
client_addr        | 127.0.0.1
client_hostname    |
client_port        | 54308
backend_start      | 2018-05-07 15:12:30.682069+02
xact_start         | 2018-05-07 15:12:59.513311+02
query_start        | 2018-05-07 15:12:59.513311+02
state_change       | 2018-05-07 15:12:59.513313+02
wait_event_type    |
wait_event         |
state              | active
backend_xid        |
backend_xmin       | 7152428
query              | SELECT * FROM pg_locks pl       +
                   |   LEFT JOIN pg_stat_activity psa+
                   |     ON pl.pid = psa.pid         +
                   |     where                       +
                   |     pl.mode = 'ExclusiveLock'   +
                   | ;

Always consider locktype when analyzing locks.

In this case locktype = virtualxid, i. e. virtual transaction id. This is an identifier that is local for a backend. PostgreSQL uses such identifiers for read-only transactions to save "normal" transaction ids, that are global to all backends. (You can read why it is doing so, although it is not relevant to the question.) For read-write transaction you will have locktype = transactionid instead.

So, this row in pg_locks basically says that a backend process has an exclusive lock on transaction id of the transaction it is currently executing. PostgreSQL uses this lock to allow other transactions to wait for ours transaction to finish. Citing the doc:

  When a process finds it necessary to wait specifically for another
  transaction to end, it does so by attempting to acquire share lock on
  the other transaction's ID (either virtual or permanent ID depending
  on the situation). That will succeed only when the other transaction
  terminates and releases its locks.


View delete statements in the Transaction log Is it possible to view delete statements that have recently occurred in the Transaction Log?

You can view the individual rows that were deleted by looking for LOP_DELETE_ROWS operations still in the log:

select * from fn_dblog(NULL, NULL)
where Operation = 'LOP_DELETE_ROWS'

If the log was recycled (in simple recovery model) or truncated by backup (in full or bulk recovery model) then you will only be able to see the log operations still available in the log.

Understanding the log operations is quite complex, you need to be aware of things like undo or compensating operations to make sense of some log patterns you can encounter, but straight forward committed DELETEs are fairly easy to comprehend.


Postgresql: Difference between pg_dump and backup/restore? Don't they essentially do the same thing by storing all the data in a file and dumping it into another database? Is it that pg_dump just adds the data on to whatever is already there and restore deletes everything and then dumps it in?

pg_dump is the basic official tool of the project to create backups of PostgreSQL databases (or selected objects in it) in the file system.

To restore, you can either use pg_restore or simply psql, depending on the format of the backup.

The "Backup" and "Restore" options of pgAdmin are just fronts to the above mentioned tools.

There is no (official) tool "backup" or "restore" like you seem to be implying. You are probably confusing terms.


CASE Statement - 1st case is being ignored I have a table (usr_t_user_reg) in which I register all the users of the database. Now I want to create a view (usr_v_user_not_reg) which shows me all the users that I haven't registered yet or users that are no longer on the database.

The table looks like this:

| username | db_instance | forename | surname | ...... |
|----------|-------------|----------|---------|--------|
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB2      |   xxxxx  |   xxxx  | ...... |

Here is the statement to create the view:

CREATE OR REPLACE FORCE VIEW usr_v_user_not_reg AS
 SELECT username "User", db_instance "Instance",
 (
  CASE
    WHEN username IN
    (
      SELECT username
      FROM SYS.dba_users
      MINUS
      SELECT username
      FROM usr_t_user_reg
    ) THEN 'not registered'
    WHEN username IN
    (
      SELECT username
      FROM usr_t_user_reg
      MINUS
      SELECT username
      FROM SYS.dba_users
    ) THEN 'no longer present'
  END
 ) "Status"
 FROM usr_t_user_reg
 WHERE db_instance = 'DB1'
 AND username NOT IN
 (
  SELECT username
  FROM usr_t_user_reg
  INTERSECT
  SELECT username
  FROM SYS.dba_users
 );

At first I thought it would work fine and then I noticed that the first case is kind of being ignored. I have some users who are not registered yet and they are not displayed in the output. If I use the first case as a standalone query

SELECT username
FROM SYS.dba_users
MINUS
SELECT username
FROM usr_t_user_reg
WHERE db_instance = 'DB1';

then it works. As soon as I want to use it in the whole query (even without the CREATE command) it doesn't work. The second case works without any problems.

I think I'm missing something, but I don't know what.

Additional considerations

I think I forgot to mention something. On the database are multiple instances and with the view I want to know on which instance there is a user who isn't registered yet or who isn't available anymore. The view I want to create should have an output similiar to this:

| username | instance |     status     |
|----------|----------|----------------|
|   XYA    |   DB1    | not registered |
|   XYB    |   DB2    | not registered |
|   XYC    |   DB3    | non-existend   |

To me it looks much like you're asking only the registered users, as the SELECT acting as the basis for the view is:

SELECT
[...]
 FROM usr_t_user_reg

Thus having "some users who are not registered yet and they are not displayed in the output" is what should be expected.

Having something like this would show the not (yet) registered users:

  SELECT
    u.username "User",
    r.db_instance "Instance",
    NVL(r.db_instance,'not registered yet') "Status"
  FROM
    SYS.dba_users u LEFT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

That will, however, not give those users that are still in the "registered" table but are not anymore DB users, for that you could use the parallel:

  SELECT
    r.username "User",
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
  FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

The SQL above is probably possible to combine nicely, and is likely to be sub-optimal, but it did work on quick testing...

Edit:
Full outer join version combining the two queries above:

  SELECT
    NVL(r.username, u.username) "User",
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE
      CASE NVL(r.db_instance,'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END
    END "Status"
  FROM SYS.dba_users u FULL OUTER JOIN usr_t_user_reg r ON u.username = r.username;

Edit 2:
A version providing also the name of the instance(s) the user is not registered, the name of the instance(s) the non-existent user is registered to, and filtering out all of the cases where status is OK:

SELECT *
FROM (
  WITH t1 AS (
    SELECT
      u.username,
      i.db_instance
    FROM SYS.dba_users u, ( SELECT DISTINCT db_instance FROM usr_t_user_reg ) i
  )
  SELECT
    t1.username "User",
    t1.db_instance "Instance",
    CASE NVL(r.db_instance, 'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END "Status"
  FROM t1 LEFT OUTER JOIN usr_t_user_reg r ON t1.username = r.username AND t1.db_instance = NVL(r.db_instance, t1.db_instance)
  union
  SELECT
    r.username "User",
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
  FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username
)
WHERE "Status"!='OK';

It starts getting uglier and even less optimal, but again that did work on the test setup.


Why can't I view the plan for view creation...? mysql> EXPLAIN EXTENDED
    create algorithm =temptable view fisgen_temptable(inter,phone)
    as select intercom1,phone from fisgen where fid='f105';

I am getting an error like this:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use near 'create
  algorithm =temptable view fisgen_temptable(inter,phone)
  as select interc' at line 2

EXPLAIN shows an execution plan for SELECT queries. In version 5.6, it was extended to include plans for INSERT, UPDATE, DELETE and REPLACE statements.

No version of MySQL (not even the still in development, 5.7) has EXPLAIN for CREATE TABLE or CREATE VIEW.

You can of course run the explain for the select (that defines the view):

EXPLAIN EXTENDED
    select intercom1,phone from fisgen where fid='f105';

or as @jynus suggested (thank you), the more useful:

EXPLAIN EXTENDED
    select * from fisgen_temptable;


How do I retrieve the loop record in the connect by clause? I have a employee-->Manager hierarchy and using the following query:

SELECT  EMP_ID, FIRST_NAME CONNECT_BY_ISCYCLE "CYCLE" FROM  MYTABLE CONNECT by NOCYCLE PRIOR MANAGER_EMP_ID = EMP_ID START WITH EMP_ID = '12345'

We are getting the following as the output

12345   Nan 0
A12345  John 0
B12345  Dumble 0
C12345 Mark 0
D12345 Shady 0
E12345 Yenn 1

The problem here is because the CEO (F12345) is his owner manager, a LOOP occurs and hence , I am not getting the CEO record at the end of the table.  My expected output is:

12345   Nan 0
A12345  John 0
B12345  Dumble 0
C12345 Mark 0
D12345 Shady 0
E12345 Yenn 1
*F1345 Don*

Is there a way I can retrieve the LOOP record too? If there is no other way, I have to probably do two query and fetch the CEO is the second one.

Any guidance is appreciated.

You could use what your query returns to get the extra row that have CONNECT_BY_ISCYCLE=1

WITH a AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE
   CONNECT BY NOCYCLE
    PRIOR MANAGER_EMP_ID = EMP_ID
    START WITH EMP_ID = '12345'
)
SELECT EMP_ID, FIRST_NAME
FROM a

UNION ALL

SELECT m.EMP_ID, m.FIRST_NAME
FROM MYTABLE m
  JOIN a
    ON a.MANAGER_EMP_ID = m.EMP_ID
WHERE a.CONNECT_BY_ISCYCLE = 1 ;

Or use a recursive CTE:

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE
WHERE EMP_ID = '12345'

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
    AND a.MANAGER_EMP_ID <> a.EMP_ID   -- to avoid the cycles
)
SELECT EMP_ID, FIRST_NAME
FROM a ;

Another way to avoid cycles in a recursive CTE is to use the CYCLE clause (available only in Oracle):

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE
WHERE EMP_ID = 1

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
)
CYCLE MANAGER_EMP_ID
  SET IS_CYCLE TO 'Y' DEFAULT 'N'
SELECT EMP_ID, FIRST_NAME, IS_CYCLE
FROM a ;

Test at SQLfiddle

Just realized that the extra row can be retrieved with a simple change in the original query:

SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE
   CONNECT BY NOCYCLE
     PRIOR MANAGER_EMP_ID = EMP_ID
     AND PRIOR MANAGER_EMP_ID <> PRIOR EMP_ID
                                 -- if she's a manager
                                 -- don't try to connect her with superiors
   START WITH EMP_ID = '12345' ;


How can I move backups location? I have a seemingly easy question. How can I move a xxx.bak file to another location with T-sql?

I have a job that takes a copy from database a to c:mycopy/copy1/a.bak and I want to create a step that moves the file to c:mycopy/copy2/a.bak
I tried:

 MOVE N'c:mycopy/copy1/a.bak'  TO N'c:mycopy/copy2/a.bak'

Sorry for the stupid question and thanks for your help

'(There's) no such thing as a stupid question' is a popular phrase that has had a long history. It suggests that the quest for knowledge includes failure, and that just because one person may know less than others they should not be afraid to ask rather than pretend they already know. :)

I am using below code to transfer full backups to another location using XCOPY:

similarly you can modify and transfer the file from required path to another server.

 DECLARE @RowCnt INT

 SELECT bs.database_name AS DatabaseName,
 MAX(bms.physical_device_name) AS FullBackupName
 INTO #Backups
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bms
 ON bs.media_set_id = bms.media_set_id
 INNER JOIN master.dbo.sysdatabases s
 ON bs.database_name = s.name
 where datepart(yyyy,backup_start_date)=datepart(yyyy,getdate())
 and datepart(dd,backup_start_date)=datepart(dd,getdate())
 and datepart(mm,backup_start_date)=datepart(mm,getdate())
 and bs.type='D'
  GROUP BY bs.database_name

 SET @RowCnt = @@ROWCOUNT

 WHILE @RowCnt <> 0
 BEGIN

  SELECT  TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName
  FROM #Backups
  ORDER BY DatabaseName

  SET @RowCnt = @@ROWCOUNT

  IF @RowCnt <> 0
  BEGIN

        SELECT @SQL = 'xcopy ' + @BkpFileName +SPACE(1)+ '\\your\path\tobackup\'

     EXEC master.dbo.xp_cmdshell @SQL

    --print @sql

        DELETE FROM #Backups
        WHERE DatabaseName = @DBName

   END

   END

  DROP TABLE #Backups

 SET NOCOUNT OFF

Ps note: You have to make sure XP_Cmdshell is enabled for above to work!


Postgres 10.0 upsert syntax error not sure what to google for Postgres 10.0

table:

pcr=> \d tableFindOld
                               Table "public.tablefindold"
  Column  |            Type             | Collation | Nullable |         Default
----------+-----------------------------+-----------+----------+-------------------------
 hostname | character varying(16)       |           | not null |
 lastmiss | timestamp without time zone |           |          |
 lastfind | timestamp without time zone |           |          |
 uname    | character varying(64)       |           |          | NULL::character varying
Indexes:
    "tablefindold_pkey" PRIMARY KEY, btree (hostname)

upsert statement:

INSERT INTO tableFindOld(hostname, lastMiss, lastFind, uname)
VALUES('pcname', DEFAULT, '2019-03-04 11:38:04', 'billy')
ON CONFLICT (tableFindOld.hostname)
    DO UPDATE SET lastFind='2019-03-04 11:38:04', uname='billy'
    WHERE tableFindOld.hostname='pcname';

error:

ERROR:  syntax error at or near ")"
LINE 1: ...8:04', 'billy') ON CONFLICT (tableFindOld.hostname) DO UPDAT...
                                                             ^

I have tried googling this to no avail. A few questions:

What is the specific error here? (Really asking Why am I getting this error?)
In upsert statements, for a column conflict condition (in this example it is the primary key conflict) is the column name supposed to be enclosed in parantheses or not? I have seen some inconsistent info on this.
In an upsert statment with an update, are we supposed to include the DO keyword or not? Also have seen inconsistent info on this.

Also, I created the table with DEFAULT NULL on all columns except the primary key, so I'm not sure why that's not reflected in the description.

Thanks in advance for your help.

EDIT:
Here is my table creation code:

CREATE TABLE tableFindOld(
    hostname VARCHAR(16) PRIMARY KEY,
    lastMiss TIMESTAMP DEFAULT NULL,
    lastFind TIMESTAMP DEFAULT NULL,
    uname VARCHAR(64) DEFAULT NULL
);

SOLUTION:
Thanks to McNets for this answer. For reference, the working statement:

INSERT INTO tableFindOld(hostname, lastMiss, lastFind, uname) VALUES('pcname', DEFAULT, '2019-03-04 12:30:04', 'billy') ON CONFLICT (hostname) DO UPDATE SET lastFind='2019-03-04 12:30:04', uname='billy' WHERE tableFindOld.hostname='pcname';

Remove table name in:

ON CONFLICT (hostname)

db<>fiddle here

Quoted from docs:

ON CONFLICT Clause

[ ON CONFLICT [ conflict_target ] conflict_action ]

  conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes.


Is there any impact to changing IP address of MySQL Server? We are changing the network and the IP addresses will be changing.
Will there be any impact to MySQL running on the server?

I don't use the IP address to reference the server.  Instead I use the server name.

For example:

Server          IP
MYPROD01        10.1.3.30 -> becomes -> 192.1.30.90

When you use the server name (DNS name) then a change of IP address will be used by your clients as soon as all DNS servers have this new address. Since you change your subnet you must be able to 'reach' this subnet from your clients. The only 'problem' can be that you loose the open connections.


Strategies for "checking out" records for processing I'm not sure if there is a named pattern for this, or if there isn't because it's a terrible idea. But I need my service to operate in an active/active load balanced environment. This is the applicaiton server only. The database will be on a separate server. I have a service that will need to run through a process for each record in a table. This process can take a minute or two, and will repeat every n minutes (configurable, usually 15 minutes).

With a table of 1000 records that needs this processing, and two services running against this same data set, I would like to have each service "check out" a record to process. I need to make sure that only one service/thread is processing each record at a time.

I have colleagues that have used a "lock table" in the past. Where a record is written to this table to logically lock the record in the other table (that other table is pretty static btw, and with a very occassional new record added), and then deleted to release the lock.

I'm wondering if it wouldn't be better for the new table have a column that indicates when it was locked, and that it is currently locked, instead of inserting an deleting constantly.

Does anyone have an tips for this kind of thing? Is there an established pattern for long(ish) term logical locking? Any tips for how to ensure only one service grabs the lock at a time? (My colleague uses TABLOCKX to lock the entire table.)

I am not a huge fan of either the extra "lock" table or the idea of locking the whole table to grab the next record. I get why it is being done, but that also hurts concurrency for operations that are updating to release a locked record (surely two processes can't be fighting over that when it is not possible for two processes to have locked the same record at the same time).

My preference would be to add a ProcessStatusID (typically TINYINT) column to the table with the data being processed. And is there a field for LastModifiedDate? If not, then it should be added. If yes, then do these records get updated outside of this processing? If records can be updated outside of this particular process, then another field should be added to track StatusModifiedDate (or something like that). For the rest of this answer I will just use "StatusModifiedDate" as it is clear in its meaning (and in fact, could be used as the field name even if there is currently no "LastModifiedDate" field).

The values for ProcessStatusID (which should be placed into a new lookup table called "ProcessStatus" and Foreign Keyed to this table) could be:

Completed (or even "Pending" in this case as both mean "ready to be processed")
In Process (or "Processing")
Error (or "WTF?")

At this point it seems safe to assume that from the application, it just wants to grab the next record to process and won't be passing anything in to help make that decision. So we want to grab the oldest (at least in terms of StatusModifiedDate) record that is set to "Completed" / "Pending". Something along the lines of:

SELECT TOP 1 pt.RecordID
FROM   ProcessTable pt
WHERE  pt.StatusID = 1
ORDER BY pt.StatusModifiedDate ASC;

We also want to update that record to "In Process" at the same time to prevent the other process from grabbing it. We could use the OUTPUT clause to let us do the UPDATE and SELECT in the same transaction:

UPDATE TOP (1) pt
SET    pt.StatusID = 2,
       pt.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
OUTPUT INSERTED.RecordID
FROM   ProcessTable pt
WHERE  pt.StatusID = 1;

The main problem here is that while we can do a TOP (1) in an UPDATE operation, there is no way to do an ORDER BY. But, we can wrap it in a CTE to combine those two concepts:

;WITH cte AS
(
   SELECT TOP 1 pt.RecordID
   FROM   ProcessTable pt (READPAST, ROWLOCK, UPDLOCK)
   WHERE  pt.StatusID = 1
   ORDER BY pt.StatusModifiedDate ASC;
)
UPDATE cte
SET    cte.StatusID = 2,
       cte.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
OUTPUT INSERTED.RecordID;

The obvious question is whether or not two processes doing the SELECT at the same time can grab the same record. I am pretty sure that the UPDATE with OUTPUT clause, especially combined with the READPAST and UPDLOCK hints (see below for more details), will be fine. However, I have not tested this exact scenario. If for some reason the above query does not take care of the race condition, then adding the following will: application locks.

The CTE query above can be wrapped in sp_getapplock and sp_releaseapplock to create a "gate keeper" for the process. In doing so, only one process at a time will be able to enter in order to run the query above. The other process(es) will be blocked until the process with the applock releases it. And since this step of the overall process is just to grab the RecordID, it is fairly quick and won't be blocking the other process(es) for very long. And, just as with the CTE query, we are not blocking the entire table, thereby allowing other updates to other rows (to set their status to either "Completed" or "Error"). Essentially:

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'GetNextRecordToProcess', @LockMode = 'Exclusive';

   {CTE UPDATE query shown above}

EXEC sp_releaseapplock @Resource = 'GetNextRecordToProcess';
COMMIT TRANSACTION;

Application locks are very nice but should be used sparingly.

Lastly, you just need a stored procedure to handle setting the status to either "Completed" or "Error". And that can be a simple:

CREATE PROCEDURE ProcessTable_SetProcessStatusID
(
   @RecordID INT,
   @ProcessStatusID TINYINT
)
AS
SET NOCOUNT ON;

UPDATE pt
SET    pt.ProcessStatusID = @ProcessStatusID,
       pt.StatusModifiedDate = GETDATE() -- or GETUTCDATE()
FROM   ProcessTable pt
WHERE  pt.RecordID = @RecordID;

Table Hints (found at Hints (Transact-SQL) - Table):

READPAST (seems to fit this exact scenario)

  Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released...READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

ROWLOCK (just to be safe)

  Specifies that row locks are taken when page or table locks are ordinarily taken.

UPDLOCK

  Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level.


Google Datastore model User and Messages efficently I have two kinds: USER and MESSAGES. A user can send/receive many messages to/from another user. Thinking in a relational way the relationship would suggest a user can have many messages, and a message can only be created by one user. So if user A sends a message to User B, both user A should be able to see the message they sent, and user B should be able to see the message they received. I was thinking first of storing all sent/received messages inside of each user object, but since there is a 1mb limit for entities this is not an option as some users can retrieve many more messages than this. Second I was thinking of creating two kinds, USER and MESSAGES.The messages kind would have a to and from property both which would be indexed so that I can get all the messages a user has sent and received. As a result each message would be its own entity and this is where it is problematic. Lets say a User is deleted, I know have to delete all the message sent and received by this user. If a user has sent though sands of messages, the cost of removing each message will be very expensive. I was wondering what would be an efficient way to model this. I am be to Google Datatore and would be open to any ideas as I have nothing implemented yet.

Your second approach seem to be the best. Having a Message entity with key relations to sender/reciever.

As for you deleting a user dilema, you should consider not removing the entity when a user is deleted as this would break your db consistency. You should probably just have a logic deletion (eg a boolean value changed when a user is deleted), that way, messages sent to/from that user will stll make sense for other users.


Postgresql query slowed with table growth I have a very simple table in Postgresql, and need some guidance to speed up my queries. Recent large expansion of the data in the table has made everything grind to halt.

Table body_feature:

    Column     |       Type       | Modifiers
    -----------+------------------+-----------
     feature   | text             | not null
     body      | text             | not null
     start     | integer          | not null
     stop      | integer          | not null
Indexes:
    "t_feature_ind" btree (feature)

In the old version of the database, there were ~32 million rows in the database.

Here is a simple query:

explain analyze select * from body_feature where feature='ABC';

       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t_feature_ind on body_feature  (cost=0.00..49544.92 rows=15986 width=69) (actual time=175.967..261.175 rows=7285 loops=1)
   Index Cond: (feature = 'ABC'::text)
 Total runtime: 261.555 ms

With the recent expansion, number of rows has grown to more than 110 million, and the same query gives:

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on body_feature  (cost=944.33..155543.90 rows=42307 width=73) (actual time=1118.598..204052.203 rows=23132 loops=1)
   Recheck Cond: (feature = 'ABC'::text)
   ->  Bitmap Index Scan on t_feature_ind  (cost=0.00..933.75 rows=42307 width=0) (actual time=952.948..952.948 rows=23132 loops=1)
         Index Cond: (feature = 'ABC'::text)
 Total runtime: 204065.405 ms
(5 rows)

The original machine running the server is quite old and has 8 Gb of RAM, but I have access to a 1Tb RAM machine for testing.

What would you suggest I change in the server configuration to make such simple query revert back to the simple index scan?

I tried extracting feature and body values (which are text) into separate tables, and using simple integer IDs in the body_feature table (which cuts the raw size down significantly), but I still get the same results.

Thank you for any suggestions/help.

You are assuming that the problem is the switch to the bitmap scan.  That is very unlikely.

The likely problem is that your data had to be read from disk, either because it has grown too large to fit in cache (in which case more RAM would help), or the cache was cold when you happened to run the test.

The switch to bitmap scan is likely a response to the same thing that caused the problem, the growth in data.

You could cluster the table on the index t_feature_ind, which would make it more efficient to pull out all of the data with any given feature.


After dropping the clustered index, it is still attempting to do a clustered insert I am trying to convert data from one database system to another. One of the tables I need to transfer and format contains over 10 million rows.

I am running the following script to do it:

USE [Cvti101687]
go
truncate table [IDAT_MR_NOTEHISTORY]
go
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IDAT_MR_NOTEHISTORY]') AND name = N'PK_iMR_NOTES')
    ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] DROP CONSTRAINT [PK_iMR_NOTES]
GO
USE [101687_test2]
GO

declare @ChunkCounter int
declare @ChunkSize int
set @ChunkCounter = 0
set @ChunkSize = 50000

--hack to do a do-while loop in sql
while 1 = 1
begin
    insert into cvti101639..IDAT_MR_NOTEHISTORY with (tablock)
    SELECT newid(), '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000'
    ,'00000000-0000-0000-0000-000000000000',    ISNULL(TargetClientAccountNum, [REC_CLINUM]), ISNULL(TargetPetAccountNum, '     ')
    ,ISNULL(pck_desc, 'Converted Medical Record'), isnull([REC_DOCTOR], '99999'), isnull([REC_ENTRY],''), isnull(REC_ENTRY,''),[rec_dattim], 0
    FROM [RECORD]
    left join [WVSSPCK] on pck_code = 99 and pck_link = [REC_TAG]
    left join cvti101639..idat_patients p on p.oldAccountNum = REC_CASE
    where rec_deleted = 0 and (rec_rtype = 0 or rec_rtype is null)
        and (TargetClientAccountNum is not null or [REC_CLINUM] is not null)
        and rec_id >= @ChunkCounter and rec_id < @ChunkCounter + @ChunkSize

    --If no rows inserted, break out of the loop
    if(@@rowcount = 0)
        break;

    set @ChunkCounter = @ChunkCounter + @ChunkSize
end

go
USE [Cvti101687]
go
ALTER TABLE [dbo].[IDAT_MR_NOTEHISTORY] ADD  CONSTRAINT [PK_iMR_NOTES] PRIMARY KEY CLUSTERED
(
    [cvtGUID] ASC
)

To speed things up, I attempted to drop the clustered index on the destination table and rebuild it at the end (Yes, I know it's a clustered index on a GUID, I am just the conversions guy, I don't get a say in how the database is designed).

However, when looking at the actual execution plan from one of the iterations of the while loop of the insert, I see that it is performing a Clustered Index Insert on my index that I already dropped. Furthermore, the sort it is performing before the clustered index insert costs 69% of each chunk.

  |--Clustered Index Insert(OBJECT:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[PK_iMR_NOTES]), SET:([Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtGUID] = RaiseIfNull([Expr1012]),[Cvti101639].[dbo].[IDAT_MR_NOTEHISTORY].[cvtClientGUID] = [Expr1013],[Cvti
        |--Parallelism(Gather Streams, ORDER BY:([Expr1012] ASC))
             |--Sort(ORDER BY:([Expr1012] ASC))
                  |--Compute Scalar(DEFINE:([Expr1012]=newid(), [Expr1013]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1014]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1015]={guid'00000000-0000-0000-0000-000000000000'}, [Expr1016]={guid'
(snip)

Here is a copy of the actual execution plan after it has completed the first few iterations of the chunking loop. Just take the xml and save it as a .sqlplan file and it should be able to be opened in Management Studio.

What am I doing wrong that is causing this not doing a heap insert?

You are removing the primary key in database Cvti101687 and inserting to a table in database cvti101639.


Preferred design to avoid circular/multiple update paths Consider the following design:
CREATE TABLE dbo.Farmers
(
    FarmerName varchar(10) NOT NULL
        PRIMARY KEY
);

CREATE TABLE dbo.FarmEquipment
(
    FarmEquipmentName varchar(10) NOT NULL
        PRIMARY KEY
    , CreatorFarmer varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE dbo.Fields
(
    FieldName varchar(10) NOT NULL
    , OwnerFarmer varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.Farmers(FarmerName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
    , FarmEquipment varchar(10) NOT NULL
        FOREIGN KEY REFERENCES dbo.FarmEquipment(FarmEquipmentName)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Use case:

Farmer Joe owns land.
Farmer Ted owns land.
Farmer Joe builds a combine-harvester, and loans it to Farmer Ted for use on one of Farmer Ted's fields.
If Farmer Joe changes his name to Farmer Joseph, I want that to reflect in the Fields table, so Farmer Ted knows who owns that combine harvester he's been using.

I realize this is the exact use-case for surrogate keys, but I'm trying to determine how this would work if you're using only natural keys.
In SQL Server, you cannot actually create this structure, since the ON UPDATE CASCADE and ON UPDATE DELETE clauses in dbo.Fields produce the following error message:

Msg 1785, Level 16, State 0, Line 21
Introducing FOREIGN KEY constraint 'FK__Fields__FarmEqui__3AD6B8E2' on table 'Fields' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 21
Could not create constraint. See previous errors.

How should this model be expressed in SQL Server?
I realize I can just remove the ON UPDATE clauses, but that's not really the point. I want updates and deletes to cascade.

The solution I've seen has been to remove the foreign key constraint, and to maintain relational integrity via triggers.

On the foreign (or "child") table, you need INSERT and UPDATE triggers. if the "foreign key" column(s) are inserted/updated, then you have to make sure the value(s) exists in the primary key of the "parent" table.

On the "parent" table, on UPDATE of the primary key column(s), you have to locate all rows in any "child" tables (FarmEquipment and Fields would both be "children" of Farmers) with the old value, and update them to the new value.

On the "parent" table, on DELETE, you have to check the "child" tables to see if any rows use the primary key(s) in question, and delete them all.

Having worked with a vendor-supplied system that worked this way, I can't recommend it.

As noted above, you must maintain three chunks of code for each foreign key relationship, across a minimum of two triggers (let's leave self-referential situations out of the discussion, for the moment at least).
A "parent" table that tied to multiple "child" tables would need a check for each "child" table. While the code should be very similar for each "child" table, that can actually make modifications more difficult, as it would be easy to make a change meant to affect FarmEquipment to Fields by mistake. (In the real-world example I mentioned, there was at least one parent table with at least 20 "FK" relationships - sometimes having multiple links in the same child table, to different fields.
Documentation of the relationship is no longer built-in. While you can't (to the best of my knowledge) right-click a table and find all foreign key relationships that point to it, it's easy to find a query to retrieve this information from SQL Server's structural tables. When the relationship is maintained via triggers, you pretty much have to go through the triggers manually to identify all the relationships, and the precise definition (the "parent" table's DELETE trigger is where you can determine if the relationship is meant to be CASCADE, NO ACTION, SET NULL, or SET DEFAULT - with foreign key constraints, this can be seen from the "child" table).

There are workarounds (the trigger code does exist in a queryable form), but they require that the developers follow strict rules regarding naming conventions, the construction of the query, and possibly even the formatting of the code. Even if one person is maintaining the code, and is using a template to set everything up, keeping everything exactly so as changes are made and bugs are found and fixed becomes much more difficult.

The system I worked with did work, with this method, for more than 10 years; they'd started their app in an environment that didn't have effective foreign keys. But even they had gotten to the point where they were using foreign keys for new tables, and starting to put them in place on existing tables where possible. It's just that working with it wasn't easy.

I suspect this wouldn't meet your criteria; however, as it is a viable solution, I thought it was at least worth covering.


SQL Server 2008 posibilities of having views from one database in another database? I have a vendor database from I want to extract and transform certain information. As I dont want to change the structure of the original database due to licensing I therefor wondering if I can put together views from the vendor database but storing those in a second sql server 2008 database together with some other replicated and transformed data. The second database is supposed to live beside the first one on the same server.

Is it possible to have views like this and if so, do I loose a lot of performance when querying the views?

Yes it possible, that's what three part naming is for.

Use the form: [DatabaseName].[DatabaseOwner].[TableName] when referencing your vendor tables.
ie:

CREATE View [ViewName]
AS
SELECT * FROM [VendorDB].[dbo].[TableName]

Regarding performance, there's no big performance impacts, if the table has appropriate index and statistics these will be used by your queries in your DB.

Note: If you specify an index hint in your query, these will be ignored unless you bypass the view and refer to the table directly.

Location of your DB
As Stan noted, the above assumes that the two databases are residing on the same SQL Server instance, if you want to have a view residing in a different SQL instance (and I do not recommend this), you can:

Setup a linked server to the Vendor
DB
Use four part naming in your view definition: [LinkedServerName].[DatabaseName].[DatabaseOwner].[TableName]

ie:

CREATE View [ViewName]
AS
SELECT * FROM [VendorDBInstance].[VendorDB].[dbo].[TableName]