How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data? Context:

Out of curiosity, I'm doing load testing for my application. And then the result there's a lot of concurrent inserts happened.

After doing the load testing on create-endpoint, I'm trying to do load testing on the Fetch endpoint, including testing the pagination. For the pagination, I'm combining two columns, id (PK with UUID v4) and created_time. Also, I've added an index for faster sorting.
I'm following these solutions from here.

https://stackoverflow.com/a/58943917/4075313
https://dba.stackexchange.com/a/205385/117466

Problem:

Since the data was inserted concurrently, there are a few rows that have the same created_time, in my case up to 100(rows) in the same timestamp.

This is my table schema, an example

BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS "payment_with_uuid";

CREATE TABLE "payment_with_uuid" (
 id VARCHAR(255) PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
 amount integer NULL,
 name varchar(255) default NULL,
 created_time TIMESTAMPTZ NOT NULL DEFAULT (now() AT TIME ZONE 'utc')
);

CREATE INDEX idx_payment_pagination ON payment_with_uuid (created_time, id);

COMMIT;

This is my query,

SELECT  * from payment_with_uuid ORDER BY  created_time DESC, id DESC LIMIT 10;

It will return 10 rows of payment, assume the data will look like this, and assume the timestamp is same until the 100th row

+-------------------------------------+--------+------------+---------------------+
| id                                  | amount | name       | created_time        |
+-------------------------------------+--------+------------+---------------------+
| ffffa567-e95a-4c8b-826c-e2be6acaeb6d| 32003  | Allistair  | 2020-05-24 21:27:10 |
| ffff2dd6-3872-4acc-afec-7a568935f729| 32003  | James      | 2020-05-24 21:27:10 |
| fffe3477-1710-45c4-b554-b539a9ee8fa7| 32003  | Kane       | 2020-05-24 21:27:10 |

And for fetching the next page, this is my query looks like,

SELECT * FROM payment_with_uuid
WHERE
created_time <= '2020-05-24 21:27:10' :: timestamp
AND
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7'
ORDER BY created_time DESC, id DESC LIMIT 10;

And because of that, the pagination messed up, like some records that exist on the 1st page, may exist on 2nd, or 3rd, or any pages. And sometimes the records are missing.

Questions and Notes:

Is there any way to do this in a more elegant way?
I know using auto-increment will solve this, but choosing auto-increment id is not an option for us, because we're trying to make everything is consistent across microservice, many services already using UUID as the PK.
Using offset and limit will also solve this, but it's not a good practice as far as I know as this article explained https://use-the-index-luke.com/no-offset
I'm using Postgres 11.4

SELECT * FROM payment_with_uuid
WHERE
created_time <= '2020-05-24 21:27:10' :: timestamp
AND
id <'fffe3477-1710-45c4-b554-b539a9ee8fa7'
ORDER BY created_time DESC, id DESC LIMIT 10;

This is wrong, but it shouldn't lead to the problem you indicate of the same row showing up on page 1, 2, etc.  Rather it would result in most rows failing to show up at all, because the two filters are implemented independently.  You need to implement the id filter only within ties of the created_time filter.  Elegance I guess is a matter of opinion, but it seems to me that the most elegant solution is the tuple comparator similar to what you had attempted to include in your original question.

SELECT * FROM payment_with_uuid
WHERE
(created_time,id) < ('2020-05-24 21:27:10' :: timestamp, 'fffe3477-1710-45c4-b554-b539a9ee8fa7')
ORDER BY created_time DESC, id DESC LIMIT 10;

Now the timestamp should really be exact, it doesn't look like yours is.
How is it getting rounded to the nearest second?  In my hands it looks more like 2020-05-25 09:16:29.380925-04

If for some reason you don't want to use the tuple comparator, then you need to include the timestamp twice, once for less than and once for equal to:

WHERE
created_time < '2020-05-24 21:27:10' :: timestamp
OR
(
    created_time = '2020-05-24 21:27:10' :: timestamp
    AND
    id <'fffe3477-1710-45c4-b554-b539a9ee8fa7'
)

In addition to not being very elegant, this will probably not use the index very effectively.  You could use boolean reasoning to re-write it to avoid that top-level OR, so that it can use the index, but then it will get even harder to read and understand.


Cannot INSERT into database on a shared server I have launched an application on a shared server but I cannot INSERT any data. I thought maybe I don't have privileges so I did SHOW GRANTS in my application then die()

The result was [ EDITED ]:

  [Grants for gigfa_11863212@%] => GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES ON `gigfa_11863212_life_journal`.* TO 'gigfa_11863212'@'%'
  [Grants for gigfa_11863212@%] => GRANT USAGE ON *.* TO 'gigfa_11863212'@'%' IDENTIFIED BY PASSWORD '*78839FD7FF9639FB72CCB5E9D8BCD81A7A0A9E3D' WITH MAX_QUERIES_PER_HOUR 80000 MAX_UPDATES_PER_HOUR 60000 MAX_CONNECTIONS_PER_HOUR 20000

Why do I get the following error message when I issue an INSERT statement?

< INSERT command denied to user 'user'@'host' for table 'table' >

You have granted usage to user@% but all other things to usr@%.


Unable to fully drop a database snapshot I have a job that drops and re-creates a snapshot, it's currently failing when trying to create the database as it says the database already exists.

The database however does not have an entry in master.sys.databases, and is not visible in SSMS.

The [database].ss file does exist, and I can even run queries on tables in the database.

I'm not sure how to drop the database fully however, if I try to drop, I get the folowing:

drop database [database_snapshot]

Msg 3701, Level 11, State 11, Line 1
Cannot drop the database '[database_snapshot]', because it does not exist or you do not have permission.

(I know it's not a permissions issue)

Similarly I am unable to create the database:

CREATE DATABASE [database_snapshot] ON
(Name = N'[database]', FILENAME = N'[path to .ss]')
AS SNAPSHOT OF [database]

Msg 1801, Level 16, State 3, Line 2
Database '[database_snapshot]' already exists. Choose a different database name.

If anyone has ideas on how to proceed here, it would be most appreciated. :)

We ended up renaming the .ss file while the service was down. When it came back up again we could create a new snapshot. It's not ideal that services had to be down for us to resolve this, but it's better than nothing.


Group by to different columns I have customer data in a table structure like this:

customerID | status       |
xxx        | 1            |
xxx        | 2            |
yyy        | 3            |
xxx        | 1            |
yyy        | 4            |
yyy        | 4            |

I would now like to see the different status each customer have

So the result would be:

customerID | status1     |  status2      |  status3      |  status4
xxx        |  2          | 1             |  0            | 0
yyy        |  0          | 0             |  1            | 2

CREATE TABLE tbl (customerID varchar(10), status int);
INSERT INTO tbl VALUES
('XXX', 1),
('XXX', 2),
('XXX', 3),
('YYY', 2),
('YYY', 2),
('XXX', 1),
('XXX', 4),
('YYY', 4);

If there are a well-known different status values, you can use a PIVOT solution in this way:

SELECT customerID, [1], [2], [3], [4]
FROM
(
    SELECT customerID, status FROM tbl
) src
PIVOT
(
    COUNT(status) FOR status IN ([1], [2], [3], [4])
) pvt;

Result:

customerID |  1 |  2 |  3 |  4
:--------- | -: | -: | -: | -:
XXX        |  2 |  1 |  1 |  1
YYY        |  0 |  2 |  0 |  1

dbfiddle here

Or you can use SUM(CASE WHEN status = ...)

SELECT customerID,
       SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) Status1,
       SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) Status2,
       SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) Status3,
       SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END) Status4
FROM   tbl
GROUP BY customerID;

Result:

customerID | Status1 | Status2 | Status3 | Status4
:--------- | ------: | ------: | ------: | ------:
XXX        |       2 |       1 |       1 |       1
YYY        |       0 |       2 |       0 |       1

dbfiddle here


SQL Batches and the GO command: which parts of my script failed? I executed a script that had multiple sections, separated by the GO command, something like this:

Use DB1
GO

ALTER TABLE foo ADD bar VARCHAR(10) NULL
UPDATE foo SET bar = 'nananana'
ALTER TABLE foo ALTER COLUMN bar VARCHAR(10) NOT NULL
DROP TABLE oldtrash
GO

sp_rename 'zippidy.dodah', 'day', 'COLUMN';
GO

USE DB2
GO

sp_rename 'zing.bang', 'bong', 'COLUMN';
GO

ALTER TABLE whiz ADD dum INT NULL
DROP TABLE moretrash
GO

When I ran it (all at once), I got a mix of success and error statements, something like:

(red text)
Msg 207, Level 16, State 1, Line 6
Invalid column name 'bar'.

(black text)
Caution: Changing any part of an object name could break scripts and stored procedures.

(red text)
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238 [Batch Start Line 27]
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

I have some ideas about what went wrong here, but I have a few questions (hopefully these are more broadly applicable than just this one script):

If an error refers to Line 6, is that from the top of the script? Or from the top of the batch? (after the prior GO)
If an error is encountered, does it roll back only the statements in that batch? (between the GOs)? So, if the UPDATE foo fails (because the column doesn't exist when the statement is evaluated), then none of the other statements in that batch commit, including the DROP TABLE oldtrash?
Would the order of the statements within the batch have mattered, if one of them errored out? For example, if my DROP TABLE had come before the UPDATE foo, would it have run?
The order of the output leads me to believe that the first sp_rename succeeded (with the "caution" output), and the second failed with the error. I'll check the tables, of course, but is that what you'd assume here? Or could the first sp_rename have both issued the caution and thrown the error?
The failure of the prior batches doesn't prevent later batches from running, correct? So regardless of what happened above, my final ALTER TABLE whiz and DROP TABLE moretrash likely executed (and due to the lack of output, succeeded)?

After sufficient testing, I think I've answered all my questions:

  If an error refers to Line 6, is that from the top of the script? Or from the top of the batch? (after the prior GO)

Errors are reported from the top of the entire script, not from the top of the batch.

One caveat: errors point to the line that starts the statement containing the error, even if the error itself is on a later line. For example, if badTableName doesn't exist, the following will report an error on line 12, not line 13:

SELECT *                 --line 12
FROM badTableName        --line 13

  If an error is encountered, does it roll back only the statements in that batch? (between the GOs)?

Yes. If an error is encountered, that entire batch (between the GOs) is rolled back.

  Would the order of the statements within the batch have mattered, if one of them errored out?

No, the order of commands within that block would not have mattered.

  The order of the output leads me to believe that the first sp_rename succeeded (with the "caution" output), and the second failed with the error.

After some testing, this appears to be correct. The Caution is only output when sp_rename completes successfully.

  The failure of the prior batches doesn't prevent later batches from running, correct?

Correct. Other batches are treated independently, and will be run.


Remote linked servers I have a SQL Server 2008R2, and I want to connect remotely to a SQL Server 2012SP1, for which I am given:

IP: 12.34.56.78
Login: john
Password: pw1
Database: TESTDB
Table: test_table

The remote database is outside my LAN, but firewall is configured on both sides, so I can query it by opening SSMS directly from my database server.
I perform the following:

EXEC sp_addlinkedserver '12.34.56.78', 'SQL Server';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = [12.34.56.78], @locallogin = NULL , @useself = N'False', @rmtuser = 'john', @rmtpassword = 'pw1'

Up to now, no error, and I can see the linked server in SSMS, "Test Connection" is successful but I cannot query it.

select * from [12.34.56.78].TESTDB.test_table

Returns this error:

Msg 208, Level 16, State 1, Line 1
Invalid object name '12.34.56.78.TESTDB.test_table'.

What is the proper way to define a linked server from the Internet, and if possible assigning a nice name to it (rather than referring to it by IP)?
Should I be using SQLNCLI as a provider?

You are missing the schema name in your query. It should be [LINKED_SERVER].[DB_NAME].[SCHEMA_NAME].[OBJECT_NAME]. So in your case, [12.34.56.78].TESTDB.[HERE SHOULD BE YOUR MISSING SCHEMA].test_table

It's a public IP address. Hopefully it is a firewall's address that routes the traffic to a SQL Server! If it has a DNS name assigned to it, then you could use it. Or you could create a SYNONYM and forget about typing this long name.

CREATE SYNONYM [schema].[synonym_name] FOR [LINKED_SERVER].[DB_NAME].[SCHEMA_NAME].[OBJECT_NAME]


Oracle database silent install failing on shared pool/sga size I am running the following command:

dbca -silent -createDatabase -templateName foobar.dbt -responseFile NO_VALUE -gdbname foo1 -sid foo1 -emConfiguration NONE -automaticMemoryManagement true -initParams memory_max_target='2048M',memory_target='1638M',sga_max_size='1638M',sga_target='0M',pga_aggregate_target='0M',pga_aggregate_limit='0M'

This fails and I receive the following in the log:

The specified shared pool size or SGA size "80MB" does not meet the recommended minimum size requirement "331MB". This will make database creation fail. Do you want to continue?

Unique database identifier check passed.

/apps/oradata/ has enough space. Required space is 1775 MB , available space is 2507 MB.
File Validations Successful.
Cleaning up failed steps
DBCA_PROGRESS : 5%
Creating and starting Oracle instance
DBCA_PROGRESS : 6%
DBCA_PROGRESS : 8%
DBCA_PROGRESS : 9%
ORA-01078: failure in processing system parameters

DBCA_PROGRESS : 10%
ORA-01034: ORACLE not available

ORA-01034: ORACLE not available

DBCA_PROGRESS : DBCA Operation failed.

The parameters specified are our standard. (1638M is 80% of MAX_MEMORY_TARGET). Usually we set the memory parameters after the database is created, I'm trying to do it during creation instead.

I do not know where the log is grabbing "80MB" from. To my knowledge, it should be set to 0 because I am setting SGA_TARGET to 0.

I suspect that maybe I am doing something contradicting, or maybe I should be considering other parameters to set manually. Could it be that by setting SGA_TARGET to 0 and MEMORY_TARGET to 0, that I am confusing Oracle on whether or not it should be using Automatic Memory Management, and it tries using it anyway which is setting the shared pool to 80MB?

After testing your code, found the issue and modified the command which works fine for me.

[oracle@oracle templates]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE -gdbname oracdb -sid oracdb -emConfiguration NONE -storageType ASM -asmsnmpPassword grid -diskGroupName +DATA  -initParams memory_max_target='1300M',memory_target='1300M',sga_max_size='1300M',sga_target='0M',pga_aggregate_target='0M',pga_aggregate_limit='0M'
Enter SYS user password:

Enter SYSTEM user password:

Registering database with Oracle Restart
5% complete
Copying database files
7% complete
8% complete
9% complete
10% complete
DBCA Operation failed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oracdb/oracdb12.log" for further details.

The error log.

[oracle@oracle templates]$ cat /u01/app/oracle/cfgtoollogs/dbca/oracdb/oracdb12.log
The specified shared pool size or SGA size "80MB" does not meet the recommended minimum size requirement "331MB". This will make database creation fail. Do you want to continue?

Unique database identifier check passed.

+DATA has enough space. Required space is 6262 MB , available space is 10195 MB.
File Validations Successful.
Registering database with Oracle Restart
DBCA_PROGRESS : 5%
Copying database files
DBCA_PROGRESS : 7%
DBCA_PROGRESS : 8%
ORA-01078: failure in processing system parameters

DBCA_PROGRESS : 9%
DBCA_PROGRESS : 10%
ORA-01034: ORACLE not available

ORA-01034: ORACLE not available

DBCA_PROGRESS : DBCA Operation failed

I faced the same error as you have.

[oracle@oracle admin]$ oerr ora 01078
01078, 00000, "failure in processing system parameters"
// *Cause:  Failure during processing of INIT.ORA parameters during
//        system startup.
// *Action:  Further diagnostic information should be in the error stack.

Further information is the error message ORACLE not available, which means instance creation is failed.

During the database creation, DBCA creates a dummy init.ora files under the ORACLE_BASE/cfgtoollogs/dbca/<oracle_SID>/ directory.

Let's see what parameters were used in the init files for that instance.

[oracle@oracle oracdb]$ cat initoracdbTempOMF.ora
##############################################################################
# Copyright (c) 1991, 2013 by Oracle Corporation
##############################################################################

###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name="seeddata"

###########################################
# File Configuration
###########################################
db_create_file_dest="+DATA"
db_recovery_file_dest="+DATA"
db_recovery_file_dest_size=4560m

###########################################
# Miscellaneous
###########################################
compatible=12.1.0.2.0
diagnostic_dest=/u01/app/oracle
memory_max_target=1300M
memory_target=Unexpected error!!!
pga_aggregate_limit=0M

###########################################
# Network Registration
###########################################
local_listener=LISTENER_ORACDB

###########################################
# Processes and Sessions
###########################################
processes=300

###########################################
# SGA Memory
###########################################
sga_max_size=1300M
sga_target=0M

###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/oracdb/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=oracdbXDB)"

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=0M

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1

db_unique_name="oracdb"
_diag_hm_rc_enabled=false
control_files="/u01/app/oracle/cfgtoollogs/dbca/oracdb/tempControl.ctl"
enable_pluggable_database=true

  MEMORY_TARGET=Unexpected Error!

There was the main problem. Then I tried to edit the command and removed that size delimiter M then ran the command once again.

[oracle@oracle templates]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE -gdbname oracdb -sid oracdb -emConfiguration NONE -storageType ASM -asmsnmpPassword grid -diskGroupName +Data  -initParams memory_max_target='1300M',memory_target='1300'
Enter SYS user password:

Enter SYSTEM user password:

Cleaning up failed steps
5% complete
Registering database with Oracle Restart
11% complete
Copying database files
12% complete
14% complete
21% complete
27% complete
34% complete
44% complete
Creating and starting Oracle instance
46% complete
51% complete
52% complete
53% complete
58% complete
62% complete
63% complete
66% complete
Completing Database Creation
70% complete
73% complete
76% complete
86% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oracdb/oracdb13.log" for further details.
[oracle@oracle templates]$

And it has completed successfully.

The memory target value appeared as memory_target=1300m.

I don't know why the DBCA has made that error. Same time other parameters are using same values(with the character M) as valid value.

If you wish to use automatic memory management and provide memory related parameters with templates in DBCA silent mlde, you can use -automaticMemoryManagement true(which forces to use memory target hence its not necessary to use memory parameters)  along with -totalMemory <memory allocated for Oracle in MB>.

Example: ...-automaticMemoryManagement true -totalMemory 1650 is equivalent to ... -initParams memory_target = '1650'. Size is expressed in MB.

sga_target and sga_max_target parameters are not supported on command line.
Details are in MOS document(2212857.1)


COUNT(*) on InnoDB whenever phpMyAdmin loads It's a known issue that innodb is slow in

SELECT count(*) FROM Table

Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb table with 19k rows, which can take up to a minute to execute. Yet it doesn't initiate such count(*) for the other innodb table with 4 million rows.

Another clue, in phpmyadmin, under the Records column, the number of records for the 19k-row table is showing the exact value whereas the 4-million-row table is showing an approx.

I understand innodb table gives an estimation of the row count. But it seems that for smaller table, in this case with 19k rows, phpmyadmin decides to do a count(*) instead of just giving an estimation.

Question is, is there a way to stop phpmyadmin from doing such count on my innodb table? It's okay to just give an estimation instead of doing an expensive row count whenever I refresh my phpmyadmin screen.

Edit config.inc.php, and change the setting for MaxExactCount.

$cfg['MaxExactCount']
    Type:   integer
    Default value:  500000

For InnoDB tables, determines for how large tables phpMyAdmin should get the
exact row count using SELECT COUNT. If the approximate row count as returned
by SHOW TABLE STATUS is smaller than this value, SELECT COUNT will be used,
otherwise the approximate count will be used.


How to select columns in SQL server 2008 using WHERE and IN, having 2 columns in the where clause How to select columns in SQL Server 2008 using WHERE and IN, having 2 columns in the where clause.
Here is what I have, however I am  getting some error message.

select * from tbl
where (col1, col2) in (VALUES ('x1','y1'),('x2','y2'),....);

  Msg 4145, Level 15, State 1, Line 2
  An expression of non-boolean type specified in a context where a condition is expected, near ','.

Please advise.

I would do something along the lines of

select * from tbl where col 1 in (x1,x2) and col 2 in (y1,y2)

Unless you are looking for specific combinations such as xy and y1 together, you didn't specify enough in your question.


Posgres Row Security Policy - Why Array faster than Bitstring? I have a question:
I have two row security policies to restrict the access to a table.
The first policy uses an Array-Column "rights".
The "rights" column is type 16-bit bit/integer array and each user is assigned an index.
If the array has the value 1 at an index, the user with that index can read the line. The policy for looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING (rights[(select array_pos from account where username = current_user)] > 0);

The second policy uses bitstrings with the same logic so for example:

Value in the table: 1001010

User1 bit string: 0001000

User2 bit string: 0000100

1001010 & 0001000 = 0001000 -> User1 has access

1001010 & 0000100 = 0000000 -> User2 has no access

The policy looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING ((bitstring & (select user_bitstring from account where username = current_user)) <> B'0'::BIT(16));

Intuitively, I would argue that the second option is more performant as a bit string does not consume a lot of memory and binary operations don't cost much, but if one compares the performance, the array version is even a little faster. How come?
Here are the two evaluation plans with the same test data (2 million lines and user has access to half):

Array:
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000526114.16 rows=666667 width=1160) (actual time=0.025..2733.524 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000526106.00 rows=666667 width=1160) (actual time=0.025..2660.927 rows=1000000 loops=1)
         Filter: (rights[$0] > 0)
         Rows Removed by Filter: 1000000
 Planning Time: 0.181 ms
 Execution Time: 2768.561 ms
(9 rows)

Bitstring:
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000266895.60 rows=1000000 width=1160) (actual time=0.026..2759.098 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=7) (actual time=0.013..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000531106.00 rows=1990000 width=1160) (actual time=0.025..2685.145 rows=1000000 loops=1)
         Filter: ((bitstring & $0) <> '0000000000000000'::bit(16))
         Rows Removed by Filter: 1000000
 Planning Time: 0.167 ms
 Execution Time: 2794.719 ms
(9 rows)

Some comparison between the two versions (yellow and purple - blue without any policy) x-Axis: Count of Rows in thousand, y-Axis: Execution Time of the query - averaged:

And another question: is there a better way to filter access. It should be possible that the rights to a line are changeable.
Would it be possible, for example, to build an index that speeds up the filtering process?

It doesn't matter very much how big the data structure is, as long as the row is small enough not to get TOASTed (stored out of line, in which case access becomes more expensive).

The performance difference you measure might as well be the cost of the different operators (& vs. array element access).

I don't believe that you can do that more efficiently than you did. Row level security comes with a performance hit that is ideally proportional to the number of rows checked, and the performance impact will be most visible with a cheap access path like a sequential scan.


Will a specific denormalization increase performance in a specific scenario? Suppose I have a table with fields | a | b | c | d | ... | z | which contains millions of records and which will serve millions of queries a day. And the field z holds the id of some parameter which can be one of the five possible values. This parameter will never get new values.

What I need is the maximum possible speed and performance of select from this table. All other query types performance does not matter. Disk space doesn't matter much too.

Very important point is that in each select query I will always specify the value of z to only one value ( e. g. where z = 1 ) Also ib the select query I will always filter record by other fields and also I will make some joins.

The question is: will it boost the performance of select to split the table into five tables each corresponding to one of the five possible values of z? I am going to use MySQL database but I would like to hear the general answer if one exists.

The question is: will it boost the performance of select to split the
  table into five tables each corresponding to one of the five possible
  values of z?

Yes, you could use partitioning here. Partition elimination will kick in. The partitioning will act as a leading index column.

Which brings me to the following point: Just add z as the leading column to all indexes. As you always filter on z (using an equality predicate) this will only help. (Except, if the index was unique anyway like an index on an ID column).

I'm generalizing a bit here like always with databases, but make sure to add filtering on z to your indexes.

Other techniques apply as well, of course, like making indexes covering.

I don't see the necessity for creating 5 tables, or using partitioning here. I can see how that might improve performance by a few percent, though, because z no longer needs to be stored.

If your app is generating enough load that a few percent of load matter, then your server is overloaded anyway by my definition. Still, it is worth a try if you don't care about increased development and maintenance costs.


Oracle sqlldr convert date field with different NLS_DATE_FORMAT I want to load data file which contains date fields in this format:

10-AUG-16

but, my oracle NLS_LANG is "Simplified Chinese_China.ZH16GBK", if I specify date format in control file like this:

dd-Month-YY

It will fail, as it expect data like this:

10-八月 -16 // 八月 means august in Chinese

how can I load this data?

TO_DATE functions accepts the NLS language as an optional parameter .. have you tried pulling it in via SQL*Loader as a CHAR, then do a TO_DATE conversion on it inline?

so your SQL*loader syntax might look like:

  ...
    my_date     CHAR  "TO_DATE(:my_date, 'dd-MON-RR', 'NLS_DATE_LANGUAGE=American')"
  ...

See if that works any better ??


Query Returns 3 Million records The following Query Returns 3 Million records, I want to display only Unique Assets_ID, how to modify this query, so there are no duplicate records?

Select DISTINCT
(a.Assets_ID),
   a.Offices_OfficesID,
   a.AssetInformation_AssetInformationID,
   a.Serial,
   a.AddQuantity,
   a.MEI,
   a.ICCID,
   a.AssetStatus,
   b.AssetType,
   c.Category,
   d.AssetCategories_AssetCategoriesName,
   m.ManufacturerName,
   x.Vehicle_ID,
   x.Emp_Id,
   x.Phone,
   x.QuantityforIssue as qty,
   x.AssignedStatus,
   x.AssignmentDate as stat_date,
   d.AssetCategories_AssetCategoriesName as asst_name,
   u.FirstName,
   u.LastName
FROM
   Assets as a
   JOIN
      AssetInformation as b
      ON a.AssetInformation_AssetInformationID = b.AssetInformation_ID
   JOIN
      AssetCategories as c
      ON c.AssetCategories_ID = b.Category
   JOIN
      AssetStatus as d
      ON d.AssetStatus_ID = a.AssetStatus
   JOIN
      Manufacturer as m
      ON m.Manufacturer_ID = a.Manufacturer
   JOIN
      AssetAssignment as x
      ON (x.Asset_ID = a.Assets_ID
      AND a.Offices_OfficesID = x.Offices_OfficesID)
   JOIN
      Users as u
      ON (u.UserID = x.Emp_Id
      or x.Emp_Id = 0)
WHERE
   a.Offices_OfficesID IN
   (
      1
   )

Assets table

DESCRIBE Assets
Assets_ID   int(11) NO  PRI NULL    auto_increment
Offices_OfficesID   int(11) YES MUL 0
AssetInformation_AssetInformationID int(11) YES MUL 0
ToolKits_ToolKitsID int(11) YES     0
Serial  varchar(255)    YES     NULL
qued    int(11) NO      NULL
MEI varchar(255)    YES MUL NULL
ICCID   varchar(255)    YES     NULL
AddQuantity int(11) YES     1
AssetCategories_AssetCategoriesID   int(11) YES     0
User    varchar(50) YES     NULL
Manufacturer    varchar(50) YES     NULL
AssetStatus varchar(50) YES     NULL
DateAdded   datetime    YES     NULL
Comments    text    YES     NULL

Assuming that Asset_ID is thePRIMARY KEY, you will not have any dups until you do theJOINs.  And theDISTINCT` is unnecessary.

Now, let's check the JOINs.  What is the mapping between Assets and each other table?  Is it 1:1?  Or 1:many?  Let's say, for discussion, that an Asset is in multiple "categories".  At this point, what do you want with SELECT ... c.Category?  One solution is

SELECT ..., GROUP_CONCAT(c.Category), ...
    FROM Assets
    JOIN Categories
    GROUP BY Asset_ID  -- this replaces DISTINCT

This avoids exploding the number of rows, and does it by turning that putting a list in the "category" column.


Select distinct values in column with condition I have the products table as follows:

ProductID | ItemId | Color
---------------------------
1           X        5N
2           X        10N
3           X        2N
4           Y        12S

and a second one joined by ItemId

ID | ItemId | Group | Color | Value
-------------------------------------
1    X        T       null    5
2    X        T       5N      7
3    X        M       2N      9
4    X        M       null    6
5    Y        M       null    12

I'd like to write a select that will return distinct Group values. As you can see there two groups with T value and ItemId is X, so in this case if color is the same in product and in other table then value with same color must be used.

If there is no such color, then take null value. What is important is that all distinct groups for each product must be fetched. The only thing is the priority for the same groups if color match.

Desired result:

ProductID | ItemId | Group | Color | Value
-------------------------------------------
1           X        T       5N      7
1           X        M       5N      6
2           X        T       10N     5
2           X        M       10N     6
3           X        T       2N      5
3           X        M       2N      9
4           Y        M       12S     12

DDL:

create table ProductPrices
(
  Id bigint not null
    constraint pk_product_prices
      primary key,
  ItemId varchar(50) not null,
  Group varchar(10) not null,
  Value decimal(18,4) not null,
  Color varchar(50)
)
go

create table Products
(
  ProductId nvarchar(70) not null
    constraint PK_table_4
      primary key,
  ItemId nvarchar(50) not null,
  Color nvarchar(50) not null
)
go

Solution 1

The idea is to find distinct groups for each product, then the highest priority match calculated using a CASE expression:

SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    V.[Value]
FROM dbo.Products AS P
CROSS APPLY
(
    -- Find groups for the current ItemId
    SELECT DISTINCT
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = P.ItemId
) AS G
CROSS APPLY
(
    -- Find the highest-priority Value
    SELECT TOP (1)
        PP2.[Value]
    FROM dbo.ProductPrices AS PP2
    WHERE
        PP2.ItemId = P.ItemId
        AND PP2.[Group] = G.[Group]
    ORDER BY
        CASE
            -- Colour match is best
            WHEN PP2.Color = P.Color THEN 1
            -- Otherwise accept NULL
            WHEN PP2.Color IS NULL THEN 2
            ELSE 3
        END ASC
) AS V
ORDER BY
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;

Solution 2

This joins the tables in the natural way, then uses a window function to find the highest priority match for each group. This code only accesses each base table once:

WITH
    Joined AS
    (
        SELECT
            P.ProductId,
            P.ItemId,
            PP.[Group],
            Color = ISNULL(PP.Color, P.Color),
            PP.[Value],
            rnk =
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END
        FROM dbo.Products AS P
        JOIN dbo.ProductPrices AS PP
            ON PP.ItemId = P.ItemId
    ),
    Ranked AS
    (
        SELECT
            *,
            rn =
                ROW_NUMBER() OVER (
                    PARTITION BY Joined.ItemId, Joined.[Group], Joined.Color
                    ORDER BY Joined.rnk ASC)
        FROM Joined
    )
SELECT
    R.ProductId,
    R.ItemId,
    R.[Group],
    R.Color,
    R.[Value]
FROM Ranked AS R
WHERE
    R.rn = 1
ORDER BY
    R.ProductId,
    R.ItemId,
    R.[Group] DESC;

Solution 3

This is the same logic as solution 2, but uses a subquery and a more compact syntax:

SELECT
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group],
    Q1.Color,
    Q1.[Value]
FROM
(
    SELECT
        P.ProductId,
        P.ItemId,
        PP.[Group],
        Color = ISNULL(PP.Color, P.Color),
        PP.Value,
        rn = ROW_NUMBER() OVER (
            PARTITION BY P.ItemId, PP.[Group], ISNULL(PP.Color, P.Color)
            ORDER BY
                CASE
                    WHEN PP.Color = P.Color THEN 1
                    WHEN PP.Color IS NULL THEN 2
                    ELSE 3
                END)
    FROM dbo.Products AS P
    JOIN dbo.ProductPrices AS PP
        ON PP.ItemId = P.ItemId
) AS Q1
WHERE
    Q1.rn = 1
ORDER BY
    Q1.ProductId,
    Q1.ItemId,
    Q1.[Group] DESC;

Solution 4

With the following index on dbo.ProductPrices:

CREATE INDEX [IX dbo.ProductPrices ItemId, Group (Color)]
ON dbo.ProductPrices
(
    ItemId,
    [Group],
    Color
)
INCLUDE
(
    [Value]
);

The following solution can perform well:

SELECT
    P.ProductId,
    P.ItemId,
    G.[Group],
    P.Color,
    [Value] =
        IIF
        (
            EXISTS
            (
                SELECT
                    1
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color = CONVERT(varchar(50), P.Color)
            ),
            (
                SELECT TOP (1)
                    PP.[Value]
                FROM dbo.ProductPrices AS PP
                WHERE
                    PP.ItemId = CONVERT(varchar(50), P.ItemId)
                    AND PP.[Group] = G.[Group]
                    AND PP.Color IS NULL
            )
        )
FROM dbo.Products AS P
CROSS APPLY
(
    SELECT DISTINCT TOP (9223372036854775807)
        PP.ItemId,
        PP.[Group]
    FROM dbo.ProductPrices AS PP
    WHERE
        PP.ItemId = CONVERT(varchar(50), P.ItemId)
) AS G
ORDER BY
    P.ProductId,
    P.ItemId,
    G.[Group] DESC;

Demos

db<>fiddle demo

As a side note, ItemId is defined as nvarchar(50) in one table, and varchar(50) in the other. Same with the Color column. These problems should be fixed, if possible.


Sneaky rollback in SQL Server 2008 R2 Is it possible for an entire set of committed transactions to be rolled back in SQL Server 2008 R2 without any notification or logging anywhere?

We had a set of data apparently get rolled back this weekend on an HA cluster SQL Server 2008 R2 environment. Data existed and now it doesn't. Nothing in the SQL Server error logs, nothing in the cluster logs. SQL Server agent job that originally loaded the data ran successfully (including progress emails sent from the processes themselves).

The only thing that wasn't rolled back was the bulk inserted data. The database is set to full recovery, so bulk inserted data SHOULD have been rolled back as well, correct?

Sorry for the lack of details but I am THOROUGHLY stumped. Can't even think where else to LOOK.

As Remus succinctly puts it, there is no secret feature to rollback a transaction without logging. It is also not possible to rollback a committed transaction.

Your code is broken, not SQL Server.

From the scant details the only scenario I can envisage is the now missing data having been inserted as one long running distributed transaction, someone sighting the data via an uncommitted read and the transaction subsequently being rolled back.

Most likely is an error in the logic or analysis of the failure. Human error and cover-up perhaps even more so.


SQL Log Shipping Firewall settings I'm attempting to setup log shipping between two differnet domains.  When I attempt to connect to the secondary server instance the primary server fails to connect.  If I turn off the windows firewall on the secondary server then I'm able to connect.

I have opened up the following tcp ports on the secondary server windows firewall
1433, 1434, 137, 139, 445

I have opened up the following udp ports on the secondary server windows firewall
137, 138

Are there any other ports or firewall settings that I've missed?

I was trying to connect to a secondary instance on the server.  If I use the primary instance this appears to work.  Is there a way to use the secondary instance?  I have the secondary instance port open but it is not of course using 1433.


In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array? In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index?

SET enable_seqscan to FALSE;
EXPLAIN ANALYZE SELECT * FROM items WHERE int_array_cast(metadata->>'types') @> '{52, 53}'

Output of EXPLAIN

Seq Scan on items  (cost=10000000000.00..10000000016.07 rows=1 width=2391) (actual time=0.073..0.117 rows=1 loops=1)
  Filter: (int_array_cast((metadata ->> 'types'::text)) @> '{10,14}'::integer[])
  Rows Removed by Filter: 37
Planning Time: 0.201 ms
Execution Time: 0.197 ms

Table Structure

CREATE TABLE "items" (
    "item_uuid" UUid NOT NULL,
    "metadata" JSONB,
    PRIMARY KEY ("item_uuid")
);

int_array_cast function defintion

CREATE OR REPLACE FUNCTION int_array_cast(TEXT) RETURNS INT[]
AS
$$
  SELECT CAST($1 AS INT[])
$$
IMMUTABLE
LANGUAGE SQL
RETURNS NULL ON NULL INPUT

Index created using GIN

CREATE INDEX IF NOT EXISTS items_metadata_dok_index ON items USING GIN(int_array_cast(metadata->>'types'))

Sample items in the table

item_uuid | metadata
--------------------------------------------------
        1 | {"types":"{1,2}", "name": "item_name1"}
        2 | {"types":"{10,11}", "name": "item_name2"}
        3 | {"types":"12", "name": "item_name3"}
        3 | {"name": "item_name4"}

If you want an index to be used for searching in an array, you need to first store the values as a proper array, not as a string with comma separated values that are enclosed with curly braces, e.g: {"types": [1,2], "name": "item_name1"}

Then you need to access the value as JSONB, not as text. So you need to use -> not ->>.

SELECT *
FROM items
WHERE metadata -> 'types'  @> '[52,53]'::jsonb

And finally you need to index the types array to make that work, not the complete content of the metadata column:

create index idx_metadata_types on items using gin  ((metadata -> 'types'));

To test the index usage, you need a substantially larger table than the one you have with just 38 rows.

Using this query:

explain (analyze, buffers)
SELECT *
FROM items
WHERE metadata -> 'types' @> '[52,53]'::jsonb

On a table with 100000 rows I then get the following plan (using Postgres 11 on my Windows 10 laptop):

Bitmap Heap Scan on items  (cost=5.38..103.08 rows=100 width=78) (actual time=0.315..0.422 rows=54 loops=1)
  Recheck Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
  Heap Blocks: exact=51
  Buffers: shared hit=57
  ->  Bitmap Index Scan on idx_metadata_types  (cost=0.00..5.35 rows=100 width=0) (actual time=0.298..0.298 rows=54 loops=1)
        Index Cond: ((metadata -> 'types'::text) @> '[52, 53]'::jsonb)
        Buffers: shared hit=6
Planning Time: 0.081 ms
Execution Time: 0.453 ms


user based trace enable in Cassandra i have created user and role in Cassandra and tracing is enabled.
but sessions and event table does not capture user info.
below tables gives information about what query has been executed and from which host, but i need user information as well. how we can find that ??

select * from system_traces.events;
 select * from system_traces.sessions;

The system_traces.events and system_traces.sessions tables are used to troubleshoot performance problems.

What you are after is audit capacity. In Apache Cassandra, this is still work in progress, you can follow the work with the following JIRA ticket.


ROLLBACK after COMMIT I have multiple database sessions from which one has been blocking others because of a frozen application. There are transactions running on multiple sessions. I need to kill the head blocker because of Lock request timeouts on other sessions. After killing that session other user have reported data loss. I am pretty sure they have committed their transactions but they still seem to have been rolled back. They can even proof it because some documents have been printed which are missing data afterwards.

Is it possible that committed transactions are being rolled back because of a rollback on another session? I would think not, but after reading the COMMIT documentation I have doubts. It says:

  If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements
  @@TRANCOUNT only by 1 and the transaction stays active.

Reading the TRANCOUNT documentation it says:

  Returns the number of BEGIN TRANSACTION statements that have occurred
  on the current connection.

So is the current connection the same thing as a session or can multiple sessions share the same connection (maybe by pooling)? And if they can share, how does this relate to transactions? Can it be that these active transactions are being rolled back later on and even after they have been committed?

[edit] To be more clear, some users didn't get a Lock timeout because their data didn't conflict with the head blocker. They committed transactions but those transactions seemed to have been rolled back after killing the head blocker.

[Update 2018-03-29] After a new incident I had the opportunity to investigate the problem while it happened. The transaction logs concluded that the problem was a nested transaction that never committed. The application that caused the problem wasn't frozen so the user never noticed it until he closed the application. At that point he lost his data because the transaction was rolled back. I accepted the best answer possible and that was the one telling me it wasn't possible to roll back a committed transaction. I guess it really isn't possible, it's just hard to find the real problem.

Generally, a transaction is exactly one of

committed
rolled back

Committed transactions are never rolled back.
It's how all RDBMS operate, on ACID principles

Now, there are some different cases where it may look like this rule has been broken. But it hasn't.

Before we look at these cases though, different user sessions do not share a connection. Each user/client has one connection to the SQL and all are isolated from each other. Connection pooling does not affect this.

Savepoints

You can SAVE a transaction and rollback to this savepoin,

That is, you can partially commit/rollback if you use savepoints but I've never seen anyone do this in real life code. I won't expand more.

Nested transactions

You can nest transactions but they don't really mean anything
Simply put, SQL Server does not really have nested transations even if @@TRANCOUNT can be higher than one.

A begin increments @@TRANCOUNT by one
A rollback will set @@TRANCOUNT to zero
A commit decrements @@TRANCOUNT by one

A longer explanation is in this SO answer


How to prevent redundant relationships in data? If I want to link users with other users. Would this be a good way to do it:

I'm asking because I probably get double data like this if i don't do specific checks:

+------+-------------+
|userId|relatedUserId|
+------+-------------+
|   1  |      2      |
|   1  |      3      |
|   2  |      1      |
|   2  |      3      |
|   3  |      1      |
|   3  |      2      |
+------+-------------+

It sounds like you are describing a mutual relationship such that there is no difference between (1,2) and (2,1).  In this case you should design your code to always insert the lower userId as the first value and the higher as the second.  Then you can use AlexKuznetsov's suggestion and add a CHECK constraint of (userId<relatedUserId).  The code can then catch the duplicate exceptions and ignore them since the relationship already exists.

Note: I flipped the comparison from > to < because the lower number being first makes more sense to me.  It works either way and may make more sense the other way in your environment.


Posts, comments, replies, and likes database schema I have a site where a users can comment on posts or reply to a comment. The user can also like replies or comments. Replies are not nested, so a reply doesn't have a reply. However, there is another field called reply_to within the reply table. Here's my current schema:

Comment
id
user (foreign key)
post (foreign key)
comment

Reply
id
user (foreign key)
comment (foreign key)
reply

CommentLike (Table that shows which user liked which comments)
id
comment (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

ReplyLike (Table that shows which user liked which replies)
id
reply (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

Does this seem like a good schema to use, or is there a better way to create this sort of structure?

Your question would be easier to read, if you included the references for your foreign keys.

I'm guessing that user (foreign key) refers to field id in users table (not displayed), and similarly with post.

With those two, as well as with the other foreign keys, I would attach _id to the name, so that it's clear they are ids and not names.

You don't specify primary keys. Did you already designate id in each table to be the primary key, and has auto-increment? If not, make sure you do.

Other than that, I think it's pretty good.


postgres Poor performance on ORDER BY "id" DESC LIMIT 1 I have table items with following schema (in postgres v9.3.5):

  Column   | Type   |                         Modifiers                  | Storage
-----------+--------+----------------------------------------------------+----------
 id        | bigint | not null default nextval('items_id_seq'::regclass) | plain
 data      | text   | not null                                           | extended
 object_id | bigint | not null                                           | plain
Indexes:
    "items_pkey" PRIMARY KEY, btree (id)
    "items_object_id_idx" btree (object_id)
Has OIDs: no

When I execute query it hangs for a very long time:

SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;

After VACUUM ANALYZE the query execution improved a lot, but still not perfect.

# EXPLAIN ANALYZE SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..1269.14 rows=1 width=63) (actual time=873796.061..873796.061 rows=0 loops=1)
   ->  Index Scan Backward using items_pkey on items  (cost=0.44..1164670.11 rows=918 width=63) (actual time=873796.059..873796.059 rows=0 loops=1)
         Filter: (object_id = 123::bigint)
         Rows Removed by Filter: 27942522
 Total runtime: 873796.113 ms
(5 rows)

The strange thing is that when I execute

SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1;

it returns 0 rows and I can do it in my code to optimize my web application's performance, but why it can be done by Postgres itself? I came to Postgres from MySQL and never seen such strange things there.

=====

I have found that it uses different query plan, different index, but why?

# EXPLAIN ANALYZE SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.34 rows=1 width=63) (actual time=0.014..0.014 rows=0 loops=1)
   ->  Index Scan using items_object_id_operation_idx on items  (cost=0.56..2579.16 rows=929 width=63) (actual time=0.013..0.013 rows=0 loops=1)
         Index Cond: (object_id = 123::bigint)
 Total runtime: 0.029 ms
(4 rows)

Trying to explain why there is difference in performance between the two queries.

This one: SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1 is satisfied by any one row with the matching object_id, so the index on object_id is a natural choice. The query requires minimal I/O: index scan to find the first matching value plus one heap read to fetch the entire row.

The alternative: SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1 requires all rows with the matching object_id be sorted by another column, id, then the row with the maximum value of id be returned. If you were to use the index on object_id you would need to perform the following operations: scan the index to find every matching object_id; for every match go fetch the actual row; then sort all fetched rows by id and return the one with the largest id.

The alternative chosen by the optimizer, presumably based on the object_id histogram, is: scan the index on id backwards, in its entirety; for each value go fetch the row and check if the value of object_id matches; return the first matching row, which will have the maximum possible id value. This alternative avoids sorting the rows, so I guess the optimizer prefers it to using the index on object_id.

The presence of an index on (object_id asc, id desc) allows for yet another alternative: scan this new index for the first entry matching the supplied object_id value, which by definition will have the highest id value; go fetch one matching row and return. Obviously, this is the most efficient approach.


"Test-SqlAvailabilityGroup" takes too much time Can anyone explain me why the command Test-SqlAvailabilityGroup takes too much time ?
Is there a fastest way to check if a an availability group exists or not (preferably on PowerShell) ?
Thanks.

I guess if you only want to see if the Availability group exists, you could query the DMV:
select name from sys.availability_groups

That will return all the name of the AG(s) on that specific instance.
It could be done from PowerShell too with a "invoke-sqlcmd" pretty easilly.


How do I add databases from a crashed MySQL 5.1 server to a new MySQL 5.6 server I was running MySQL 5.1 on a personal web server that recently died.  The system drive is non-operational, but I have my second hard disk where I had installed all of my programs.  This drive has my MySQL 5.1 installation directory, and in the data subdirectory appears to have all of my databases.  Databases are both MyISAM and InnoDB, mostly the latter.

I just purchased a new PC to use as a server and installed MySQL 5.6 on it.  I would like to move these databases onto this new server.  I had hoped it would be as easy as stopping the service, copying the subfolders within [installDirectory]/data, and restarting the service.  However, when I tested that with a small InnoDB database MySQL started okay and I could log into it and see the database, but all tables reported no rows and gave errors when trying to access them.  MyISAM databases seem to copy over just fine.

How can I get these old databases onto the new server?  I do have sql dump backups, but they're a month old (again, this was a personal web server) so I'd rather not go back to them if I can help it.  All of the resources I can find in a web search detail moving files from a functional 5.1 server to a functional 5.6 server.

I have resolved this issue using the instructions posted here: http://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html

I was unaware that by default all data in InnoDB tables across all databases on the server are stored in one single file at the root of the data directory: ibdata1.  That file, and I believe the log files, must also be copied along with the folders containing the FRM files (which apparently just store the table structure).  The side effect of this, of course, is that any InnoDB tables already defined on your new server will be rendered unusable.


Query NOT Workinng in OPEN OFFICE BASE select *  from "StudentMaster" where "PhoneNo" = NULL;

Syntax error!

i want to see all record which don't Have Phone number !

I have try this all thing.

select *  from "StudentMaster" where "PhoneNo" NOT LIKE ('%');

select *  from "StudentMaster" where "PhoneNo" NOT IN ('%');

But it show all record which have Phone Numbers but NOT NULL! Why

Adding my comment as an answer since it solved the problem:

Try IS NULL instead of = NULL -- I forget that myself sometimes!

I think someone that knows about mathematics would say the NULL is nothing so an equals comparison does not work as expected.


Finding all joins required to programmatically join a table Given a SourceTable and a TargetTable, I would like to programmatically create a string with all joins required.

In short, I am trying to find a way to create a string like this:

FROM SourceTable t
JOIN IntermediateTable t1 on t1.keycolumn = t.keycolumn
JOIN TargetTable t2 on t2.keycolumn = t1.keycolumn

I have a query that returns all foreign keys for a given table, but am running into limitations in trying to run through all this recursively to find the optimal join path and make the string.

SELECT
    p.name AS ParentTable
    ,pc.name AS ParentColumn
    ,r.name AS ChildTable
    ,rc.name AS ChildColumn
FROM sys.foreign_key_columns fk
JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id
JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
JOIN sys.tables p ON p.object_id = fk.parent_object_id
JOIN sys.tables r ON r.object_id = fk.referenced_object_id
WHERE fk.parent_object_id = OBJECT_ID('aTable')
ORDER BY ChildTable, fk.referenced_column_id

I am sure this has been done before, but I can't seem to find an example.

I had a script that does a rudimentary version of foreign key traversal.  I adapted it quickly (see below), and you might be able to use it as a starting point.

Given a target table, the script attempts to print the join string for the shortest path (or one of them in the case of ties) for all possible source tables such that single-column foreign keys can be traversed to reach the target table.  The script seems to be working well on the database with a couple thousand tables and many FK connections that I tried it on.

As others mention in the comments, you'd need to make this more complex if you need to handle multi-column foreign keys.  Also, please be aware that this is not by any means production-ready, fully-tested code.  Hope it's a helpful starting point if you do decide to build out this functionality!

-- Drop temp tables that will be used below
IF OBJECT_ID('tempdb..#paths') IS NOT NULL
    DROP TABLE #paths
GO
IF OBJECT_ID('tempdb..#shortestPaths') IS NOT NULL
    DROP TABLE #shortestPaths
GO

-- The table (e.g. "TargetTable") to start from (or end at, depending on your point of view)
DECLARE @targetObjectName SYSNAME = 'TargetTable'

-- Identify all paths from TargetTable to any other table on the database,
-- counting all single-column foreign keys as a valid connection from one table to the next
;WITH singleColumnFkColumns AS (
    -- We limit the scope of this exercise to single column foreign keys
    -- We explicitly filter out any multi-column foreign keys to ensure that they aren't misinterpreted below
    SELECT fk1.*
    FROM sys.foreign_key_columns fk1
    LEFT JOIN sys.foreign_key_columns fk2 ON fk2.constraint_object_id = fk1.constraint_object_id AND fk2.constraint_column_id = 2
    WHERE fk1.constraint_column_id = 1
        AND fk2.constraint_object_id IS NULL
)
, parentCTE AS (
    -- Base case: Find all outgoing (pointing into another table) foreign keys for the specified table
    SELECT
        p.object_id AS ParentId
        ,OBJECT_SCHEMA_NAME(p.object_id) + '.' + p.name AS ParentTable
        ,pc.column_id AS ParentColumnId
        ,pc.name AS ParentColumn
        ,r.object_id AS ChildId
        ,OBJECT_SCHEMA_NAME(r.object_id) + '.' + r.name AS ChildTable
        ,rc.column_id AS ChildColumnId
        ,rc.name AS ChildColumn
        ,1 AS depth
        -- Maintain the full traversal path that has been taken thus far
        -- We use "," to delimit each table, and each entry then has a
        -- "<object_id>_<parent_column_id>_<child_column_id>" format
        ,   ',' + CONVERT(VARCHAR(MAX), p.object_id) + '_NULL_' + CONVERT(VARCHAR(MAX), pc.column_id) +
            ',' + CONVERT(VARCHAR(MAX), r.object_id) + '_' + CONVERT(VARCHAR(MAX), pc.column_id) + '_' + CONVERT(VARCHAR(MAX), rc.column_id) AS TraversalPath
    FROM sys.foreign_key_columns fk
    JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id
    JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
    JOIN sys.tables p ON p.object_id = fk.parent_object_id
    JOIN sys.tables r ON r.object_id = fk.referenced_object_id
    WHERE fk.parent_object_id = OBJECT_ID(@targetObjectName)
        AND p.object_id <> r.object_id -- Ignore FKs from one column in the table to another

    UNION ALL

    -- Recursive case: Find all outgoing foreign keys for all tables
    -- on the current fringe of the recursion
    SELECT
        p.object_id AS ParentId
        ,OBJECT_SCHEMA_NAME(p.object_id) + '.' + p.name AS ParentTable
        ,pc.column_id AS ParentColumnId
        ,pc.name AS ParentColumn
        ,r.object_id AS ChildId
        ,OBJECT_SCHEMA_NAME(r.object_id) + '.' + r.name AS ChildTable
        ,rc.column_id AS ChildColumnId
        ,rc.name AS ChildColumn
        ,cte.depth + 1 AS depth
        ,cte.TraversalPath + ',' + CONVERT(VARCHAR(MAX), r.object_id) + '_' + CONVERT(VARCHAR(MAX), pc.column_id) + '_' + CONVERT(VARCHAR(MAX), rc.column_id) AS TraversalPath
    FROM parentCTE cte
    JOIN singleColumnFkColumns fk
        ON fk.parent_object_id = cte.ChildId
        -- Optionally consider only a traversal of the same foreign key
        -- With this commented out, we can reach table A via column A1
        -- and leave table A via column A2.  If uncommented, we can only
        -- enter and leave a table via the same column
        --AND fk.parent_column_id = cte.ChildColumnId
    JOIN sys.columns pc ON pc.object_id = fk.parent_object_id AND pc.column_id = fk.parent_column_id
    JOIN sys.columns rc ON rc.object_id = fk.referenced_object_id AND rc.column_id = fk.referenced_column_id
    JOIN sys.tables p ON p.object_id = fk.parent_object_id
    JOIN sys.tables r ON r.object_id = fk.referenced_object_id
    WHERE p.object_id <> r.object_id -- Ignore FKs from one column in the table to another
        -- If our path has already taken us to this table, avoid the cycle that would be created by returning to the same table
        AND cte.TraversalPath NOT LIKE ('%_' + CONVERT(VARCHAR(MAX), r.object_id) + '%')
)
SELECT *
INTO #paths
FROM parentCTE
ORDER BY depth, ParentTable, ChildTable
GO

-- For each distinct table that can be reached by traversing foreign keys,
-- record the shortest path to that table (or one of the shortest paths in
-- case there are multiple paths of the same length)
SELECT *
INTO #shortestPaths
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ChildTable ORDER BY depth ASC) AS rankToThisChild
    FROM #paths
) x
WHERE rankToThisChild = 1
ORDER BY ChildTable
GO

-- Traverse the shortest path, starting from the source the full path and working backwards,
-- building up the desired join string as we go
WITH joinCTE AS (
    -- Base case: Start with the from clause to the child table at the end of the traversal
    -- Note that the first step of the recursion will re-process this same row, but adding
    -- the ParentTable => ChildTable join
    SELECT p.ChildTable
        , p.TraversalPath AS ParentTraversalPath
        , NULL AS depth
        , CONVERT(VARCHAR(MAX), 'FROM ' + p.ChildTable + ' t' + CONVERT(VARCHAR(MAX), p.depth+1)) AS JoinString
    FROM #shortestPaths p

    UNION ALL

    -- Recursive case: Process the ParentTable => ChildTable join, then recurse to the
    -- previous table in the full traversal.  We'll end once we reach the root and the
    -- "ParentTraversalPath" is the empty string
    SELECT cte.ChildTable
        , REPLACE(p.TraversalPath, ',' + CONVERT(VARCHAR, p.ChildId) + '_' + CONVERT(VARCHAR, p.ParentColumnId)+ '_' + CONVERT(VARCHAR, p.ChildColumnId), '') AS TraversalPath
        , p.depth
        , cte.JoinString + '
' + CONVERT(VARCHAR(MAX), 'JOIN ' + p.ParentTable + ' t' + CONVERT(VARCHAR(MAX), p.depth) + ' ON t' + CONVERT(VARCHAR(MAX), p.depth) + '.' + p.ParentColumn + ' = t' + CONVERT(VARCHAR(MAX), p.depth+1) + '.' + p.ChildColumn) AS JoinString
    FROM joinCTE cte
    JOIN #paths p
        ON p.TraversalPath = cte.ParentTraversalPath
)
-- Select only the fully built strings that end at the root of the traversal
-- (which should always be the specific table name, e.g. "TargetTable")
SELECT ChildTable, 'SELECT TOP 100 *
' +JoinString
FROM joinCTE
WHERE depth = 1
ORDER BY ChildTable
GO


View Job Properties Fails for Job Owner Domain user MyDomain\UserA has a login with a server role of public and is a user of the msdb database with membership in the SQLAgentUserRole.

The user owns a job, Daily Job. When logged in as the user, if I try to view the job properties, I get this error:

  Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft Server, Error: 15517)

If I right click on Jobs and Select New Job... I get the exact same error.

I have even given the user membership in SQLAgentOperatorRole but get the same error.

In all my searching online, the only similar issue I've found is this question on MSDN, which is unanswered.

Any ideas? I'm at a loss here.

I had the same exact problem - I had a new user which wanted to add jobs. I added this user to msdb db and added him to sqlAgentOperatorRole and db_datareader, db_datawriter. Still, when trying to add a new job, the user was getting the same error:

  Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft Server, Error: 15517)

I traced it down and it looks like when trying to add a new job (or doing many other things...) the following query is run:

exec master.dbo.sp_Msdbuserpriv 'serv'

So, obviously I had to add the new user to Master as well:

use master
create user [youNewUser] for login [yourNewUserLogin]
alter user [youNewUser] with default_schema=[dbo]

And now it works like charm...


SQL Transactional Replication and Table level compression At the moment, I have my production server with several large databases and I use a second SQL server for reporting purposes. I maintain the reporting copies of the databases using transactional replication.

As we've recently upgraded to EE, I want to enable data compression on several of the large tables on the reporting server to save on disk space (I'm currently not ready to enable it on the production server)

Has anyone tried this and if so are there any hidden pitfalls?

The only pitfalls I can see is if the table is re-snapshotted, the replicated table would lose its compression. As all of the additional reporting indexes would also be lost, its not something I would undertake lightly.

There's no real pitfalls from doing this. You just need to be aware that there will be a slight increase in CPU on the subscriber, so if you're already CPU bound it might not be the best path forward.

You could consider using either pre or post snapshot script in order to help ensure that index compression is maintained in the event you need to run another snapshot (and to add any additional indexes that you require).

When performing the compression be aware that you will probably experience transaction log growth, much like you would if you rebuilt any other index. Sorting in tempdb is generally a good practice for something like that.


query doesnt use column dedicated index after a certain amount of rows I have the following table:

create schema test;
CREATE TABLE test.foo (
    info_datetime timestamp NOT NULL,
    asset_id int4 NOT NULL,
    price float8 NULL,
    CONSTRAINT minute_prices_pkey PRIMARY KEY (info_datetime, asset_id)
);
CREATE INDEX foo_asset_id_idx ON test.foo USING btree (asset_id);

when i run this with a small amount of rows the query uses the dedicated index:

explain
select *
from test.foo mp
where mp.asset_id = 1

Bitmap Heap Scan on foo mp  (cost=4.21..14.37 rows=8 width=20) (actual time=0.005..0.006 rows=0 loops=1)
  Recheck Cond: (asset_id = 1)
  Buffers: shared hit=1
  ->  Bitmap Index Scan on foo_asset_id_idx  (cost=0.00..4.21 rows=8 width=0) (actual time=0.003..0.003 rows=0 loops=1)
        Index Cond: (asset_id = 1)
        Buffers: shared hit=1
Planning Time: 0.077 ms
Execution Time: 0.026 ms

however when I insert about 3000 rows, the same query doesn't use the index

Seq Scan on foo mp  (cost=0.00..42.56 rows=1332 width=20) (actual time=0.012..0.435 rows=1332 loops=1)
  Filter: (asset_id = 1)
  Rows Removed by Filter: 873
  Buffers: shared hit=15
Planning Time: 0.074 ms
Execution Time: 0.707 ms

In this plan:

Seq Scan on foo mp  (cost=0.00..42.56 rows=1332 width=20) (actual time=0.012..0.435 rows=1332 loops=1)
  Filter: (asset_id = 1)
  Rows Removed by Filter: 873
  Buffers: shared hit=15
Planning Time: 0.074 ms
Execution Time: 0.707 ms

You can see that roughly 60% (1332 of 2205) of the rows from that table are returned. Doing that with a Seq Scan is much more efficient that doing an index lookup for each of those rows.

In the first query Postgres only expected 8 rows to be returned (in reality no row was returned) and for that it is more efficient to probe the index.

See this related answer


restoring db getting "no files are ready to rollforward" error When I try to restore a DB file I am getting following error:

 The log or differential backup cannot be restored because no files are ready to rollforward.

My Back up SQL

BACKUP LOG [ICT_IdealComputer]
TO DISK = 'C:\ICT\ICTBackup.bak'
WITH NAME = 'ICT-Transaction Log Backup',
SKIP,
NOUNLOAD,
STATS = 10

BACKUP DATABASE [ICT_IdealComputer]
TO DISK = 'C:\ICT\ICTBackup.bak'
WITH NAME = 'ICT-Full Database Backup',
SKIP,
NOUNLOAD,
STATS = 10

Restore statement:

USE [master]
GO

RESTORE DATABASE ICT_IdealComputer
FROM DISK = 'C:\ICT\ICTBackup.bak'
WITH REPLACE, NORECOVERY,
MOVE 'ICT_IdealComputer' TO   'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer.mdf',
MOVE 'ICT_IdealComputer_log' TO 'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer_log.ldf';

I did tried with different option, googled around but no luck. Any idea what is going wrong here?

Appreciate your help

I recommend you to use different backup file names when you do log backup and full backup, so you can do backup like the following

-- first do the full backup
BACKUP DATABASE [ICT_IdealComputer]
TO DISK = 'C:\ICT\full_ICTBackup.bak'
WITH NAME = 'ICT-Full Database Backup',
SKIP, init
NOUNLOAD,
STATS = 10

-- 2nd do the log backup
BACKUP LOG [ICT_IdealComputer]
TO DISK = 'C:\ICT\log_ICTBackup.bak'
WITH NAME = 'ICT-Transaction Log Backup',
SKIP, init,
NOUNLOAD,
STATS = 10

Then you do restore as follows:

USE [master]
GO
-- first restore from full bkup
RESTORE DATABASE ICT_IdealComputer
FROM DISK = 'C:\ICT\full_ICTBackup.bak'
WITH REPLACE, NORECOVERY,
MOVE 'ICT_IdealComputer' TO   'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer.mdf',
MOVE 'ICT_IdealComputer_log' TO 'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer_log.ldf';

-- then restore from log backup

RESTORE LOG ICT_IdealComputer
FROM DISK = 'C:\ICT\log_ICTBackup.bak'
WITH RECOVERY;


Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly? Every few days my SQL Server Instance terminates unexpectedly and restarts.
In the logs, see:

  A fatal error occurred in the .NET Framework common language runtime. SQL Server is shutting down. (Event ID 6536).

Prior to the termination I see several messages saying:

  AppDomain XXX is marked for unload due to memory pressure.

I have 60 CLR Stored Procedures, which I've inherited from my predecessor. All are supposed to be short stored procedures using a small amount of data. How do I find which CLR Stored Procedure is causing the memory pressure?

I'm running:

Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64)   Jul 16 2017 18:08:49
Standard Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: ) (Hypervisor)

Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly?

SAFE CLR procedures do not crash SQL Server, so looking at the managed memory usage of CLR is unlikely to lead you to the root cause.

It's more likely that you have unsafe CLR code interacting with native code that has a memory leak.  So look at any the CLR projects for any use of 3rd party native components (like OleDB or ODBC drivers), or any use of P/Invoke or COM Interop.


Check constraint on nullable data, how to approach? This is one of the tables in my database. Constraints omitted for clarity.

CREATE TABLE [Person].[Person]
(
    [ID]                     INT           NOT NULL IDENTITY,
    [Forename]               VARCHAR(16)   NOT NULL,
    [Surname]                VARCHAR(32)   NOT NULL,
    [Gender]                 CHAR(1)       NOT NULL DEFAULT 'U',
    [DateOfBirth]            DATETIME      NULL,
    [HobbiesAndInterests]    VARCHAR(256)  NULL,
    [AdditionalInformation]  VARCHAR(512)  NULL,
    [LocalCentreID]          INT           NOT NULL DEFAULT 0,
    [EmergencyContactID]     INT           NULL,
)

The problem I have involves the date of birth field. If a value is provided, I need to run a check against the date. If a value is not provided, the check does not need to run.

I basically need to check if the person is 18 or older based on the date of birth. If a date of birth isn't provided, then there's no need for it to be checked.

I've thought a few different ways I can address this:

The first is obviously to place a check constraint on the date of birth field but I think this fails if the date is null.

Another option is to use a scalar UDF inside the check constraint.

The final option I've thought of is to use an AFTER INSERT/UPDATE trigger to check the date and rollback if the date is invalid.

I've spoken to a colleague and he says technically I'm checking a business rule in the database, so this check should be pushed off to the application.

Is there a 'right' way to approach this problem or is it basically what the developer thinks is best?

Use a CHECK constraint.

CHECK constraints do not fail when the value is null. If the value is null, the condition of the check constraints usually evaluates to UNKNOWN and the row is accepted. Only when the condition evaluates to FALSE, the row is rejected.

  Another option is to use a scalar UDF inside the check constraint.

No, no, no. User defined functions are allowed inside CHECK constraints but there are several problems, often because they are used to implement complex behaviour/constraints, like searching a whole other table. In your case, a UDF would not do anything that can't be done with standard date functions. So don't use a UDF.

  The final option I've thought of is to use an AFTER INSERT/UPDATE trigger to check the date and rollback if the date is invalid.

You could but a CHECK constraint is simpler. Triggers are far more difficult be tested.

  I've spoken to a colleague and he says technically I'm checking a business rule in the database, so this check should be pushed off to the application.

This is a rather "hot" debate about whether business rules should be in the database or in the applications. I find the approach "all business rules in the application" a bit naive. Primary and foreign key constraints implement business rules for example, should we abolish them from our database, too? Another reason for keeping business rules closer to the database is that we may have more than one application using it. We then find implementing the same rules again and again, in possibly different languages/frameworks when it could have been done once, in the database.

One problem exists though with the specific constraint, see below*.

You case could be something like below. Tested at rextester.com:

CREATE TABLE  person
( person_id INT NOT NULL PRIMARY KEY,
  birthdate DATE NULL,
  CONSTRAINT is_adult
    CHECK (birthdate <= DATEADD(year, -18, GETDATE()))
) ;

Inserting null and valid dates succeeds:

INSERT INTO person
    (person_id, birthdate)
VALUES
    (1, '19500101'),
    (2, NULL) ;

SELECT * FROM person ;

person_id  birthdate
1          01.01.1950
2          NULL

But invalid dates fail:

INSERT INTO person
    (person_id, birthdate)
VALUES
    (3, '20010101') ;

  The INSERT statement conflicted with the CHECK constraint "is_adult". The conflict occurred in database "rextester", table "dbo.person", column 'birthdate'.

*: One problem with this particular case/constraint - and not because the column is nullable: one of the function used - GETDATE() - is not deterministic. It won't - obviously - yield the same value if it is run a day or a year later. It's unlikely that you'll have a problem because once one is over 18, one stays over 18. Age increases by time.

Weird scenario: Someone changes the date of the system running the database and puts it in, say 1986. Suddenly the previously valid rows become invalid. What will happen then? (Frankly I've no idea, have to test that!)

Consider the above and it might be more appropriate to handle this constraint in a procedural way, either in the applications or by handling the table only through INSERT and UPDATE procedures that enforce the constraint.


SQL Server Deadlock issue I'm newbie for SQL DBA , everyday at least once I've got a deadlock issue in SQL server 2012 server which is using Merge statement. There are no clause like NOLOCK, UPDLOCK, HOLDLOCK has been used in the merge statment. Its a multi user environment where the Biztalk reads the xml and save data into SQL Server. Per Minute Biztalk reads 300 xml messages. Since its a production server i can't implement anything just like that without doing research, but i haven't got any idea on how to resolve this issue. Recently i got an issue like Two xml messages are trying to update data to a table and trying to use the same index and error-ed out. Could anyone help me how to get away with this issue?

Things you can do to reduce (you can never eliminate) deadlocks in a multi-user relational database:

Reduce the duration of transactions.

Any deadlock is the result of two processes competing for the same resources, so the fewer simultaneous processes are running, the less risk of a deadlock there is. This does not just apply to hour-long ETL jobs, it could very well apply to millisecond OLTP transactions if there are many enough.

Performance tune your long-running queries or tweak your server infrastructure for better throughput.

Also, I've seen examples where a process can hold a transaction unneccessarily. For instance, in some ETL processes, there's often just a single process loading data and you may not need transactional integrity at all - if it fails, just truncate everything and reload it again.

Remember that a regular SQL statement, even without a BEGIN/COMMIT TRANSACTION also implicitly creates a transaction (which commits as soon as the statement completes).

Try placing locks in the same order.

The textbook example on how to create a lock is:

Process 1 places a lock on object A
Process 2 places a lock on object B
Process 1 requests a lock on object B, but has to wait on process 2
Process 2 requests a lock on object A. Now process 1 and 2 are waiting on each other - a deadlock.

If every process accesses all the objects in the same order, the example above doesn't result in a deadlock:

Process 1 places a lock on object A
Process 2 requests a lock on object A, and has to wait for process 1
Process 1 places a lock on object B
Process 1 completes, releasing its locks on A and B.
Process 2 can now lock object A
Process 2 places a lock on object B
Process 2 completes.

Try different isolation levels

The isolation level determines how aggressively SQL Server takes out and holds locks. Obviously, the more aggressive locks are taken, the higher the risk of a deadlock.

Add retry logic to the application

By definition, you can't avoid deadlocks with 100% certainty. The application needs to have a built-in retry logic that re-runs the transaction if it is the victim of a deadlock.

Further reading

Some blog posts I've written on the subject:

How to create, find and eliminate deadlocks
On isolation levels


Book for Learning RAC What is a good book to read to learn about RAC?  I am most interested in the practical end rather than the theory behind it.

Check ronr.blogspot.com for a plug of and link to a book by Ben Prusinsky and Syed Jaffar Hussain that is very practical. It explains lots of things, and how to do them in great detail. There is lots of hands on experience in it. Very - VERY - nice.


Upgrading Microsoft SQL 2014 and Always On AG Configurations I'm getting ready to perform an upgrade from SQL 2014 to 2017 on two servers that are configured for Always On. The concern on the upgrade is how to break Always On to stop replication and to keep any sort of systems from failing over during the upgrade.

Microsoft's documentation for upgrading says to

  Remove automatic failover on all synchronous-commit replicas

But there's no mention of a best practice on how to do this. Microsoft's documentation talks about disabling it in the Server Configuration Manager, then bringing back on once the upgrade is done prior to starting on the second one. But I also see the documentation mention removing the secondary from the availability group.

It looks like the upgrade process is straightforward, but the Always On portion is the confusing side. Can anyone recommend a best practice on this that's worked for them while maintaining data integrity?

For reference, this is the link from Microsoft I've been working from for the upgrade.

What you're reading is referring to changing the failover mode of an availability group replica:

  To change the failover mode of an availability replica

  In Object Explorer, connect to the server instance that hosts the
  primary replica, and expand the server tree.
  Expand the Always On High Availability node and the Availability
  Groups node.
  Click the availability group whose replica you want to change.
  Right-click the replica, and click Properties.
  In the Availability Replica Properties dialog box, use the Failover
  mode drop list to change the failover mode of this replica.

At this point you'll want to set the failover mode to manual. Voila!

I assume you're doing an in place upgrade by the nature of the question, so to answer your other concerns:

Removing the secondaries from the availability group would effectively do the same thing (can't fail over when there's nothing to fail over to...) but only changing the failover mode lets you keep more of the existing architecture in place without having to re-seed every replica all over again.

You also could disable the entire availability group feature in Configuration Manager, but I think that requires a restart of the service and requires you to completely setup availability groups from scratch.


Easier way to change every user in every database to default schema = dbo? There was recently a domain migration where our SQL Server is hosted.

I have two servers and about 30 DBs on each. Every user was changed to the new domain but in doing so, their default schema changed from to their domain when we want in defaulted to DBO.

I'm currently changing them manually by clicking on a DB -> Security -> Users then in the general properties tab, manually typing dbo in the default schema box.

Is there a way to write SQL code to run and adjust the default schemas for all of my users across all DBs?

So what you need to end up with is something like this, for all users in any database that don't already have a default schema of dbo:

USE db1;
ALTER USER user1 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user2 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user3 WITH DEFAULT_SCHEMA = dbo;

USE db2;
ALTER USER user4 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user5 WITH DEFAULT_SCHEMA = dbo;
ALTER USER user6 WITH DEFAULT_SCHEMA = dbo;

Here is one way to do this, using aggregate concatenation and nested dynamic SQL, that gets something very close (the USE command is repeated for each user in any database, something you can probably resolve through more tweaking, but not sure it's necessary):

DECLARE @sql NVARCHAR(MAX) = N'', @innersql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT @innersql += ''USE ' + QUOTENAME(name) + ';
  ALTER USER '' + QUOTENAME(name) + '' WITH DEFAULT_SCHEMA = dbo;
  '' FROM ' + QUOTENAME(name) + '.sys.database_principals
  WHERE type_desc IN (N''SQL_USER'',N''WINDOWS_USER'')
  AND default_schema_name <> ''dbo''
  AND name NOT IN (N''dbo'',N''guest'');'
FROM sys.databases WHERE state = 0;

EXEC sp_executesql @sql, N'@innersql NVARCHAR(MAX) OUTPUT', @innersql OUTPUT;

-- you can just print the command to preview it
-- (though it may be too long to review the whole thing):

PRINT @innersql;

-- once you're happy with it, uncomment this and run it all again:
-- EXEC sp_executesql @innersql;


Restore database from script created by the "Generate and Publish Scripts" wizard I have this database I need to move to a test server. The only way I can do this according to my searches is to create a script. The database is on SQL Server 2014, I am using 2012). I can get the script to generate (1.3 gigs), I am using sqlcmd to run it, but I have run into issues.

The first issue was that it was complaining about the database not existing, so I created a database with the same name and hoped I got the settings right. This solved that issue, but now it is complaining about the users not existing or me not having permission (I ran the script as the 'sa' user), and not all stored procedures and assemblies get created.

I just need a copy of the database to test against so I can find an issue without breaking the live database. Could someone help me with this, please?

I am unable to upgrade the database. The only thing I have permission to do is copy/backup the database, so long as it does not disrupt it. On the test-server side, it is a virtual machine that I restored from backup with Windows Server 2012 and SQL Server 2012 already installed on it.

I seem to have misunderstood part of what the script does. It does not re-create the database, but sets it up again. I was able to make it work when I create a new database, set up the database so that everything would be able to be recreated (set unsafe assemblies on and such). The users do not get recreated, and must be created, given permissions, etc. manually, as the script will try to log into the windows users with the old server name. All the script does is set the tables, stored procedures, assemblies, etc. and fill them with data. I wish they would have been more clear on that.

You simply need to create a full database backup and restore that to your new server.

How to backup a database using SSMS

How to restore a database using SSMS


Transferring IBD Files from one MySQL database to another in Windows Without going into loads of historical detail I'll just get straight to the point.

I am trying to move IBD files from one Windows server to another. MySQL is corrupt on the source server but I have a backup of the entire data directory. However, that's all I have. I do not have a mysqldump or anything of the sort, I can't generate one, so the reality is that all I have are the files.

I installed MySQL fresh on another server and copied the old schema into the new MySQL instance. All files moved over just fine with exception of the InnoDB storage engine (IBD) files, which is not a surprise.

I've tried all the suggestions I can find on the web and none of them are working. There's a nice writeup here but it does not work for me: http://www.chriscalender.com/tag/innodb-error-tablespace-id-in-file/

How can I import the IBD files into MySQL? I have all the files and I have a new MySQL instance. There must be a way to accomplish this. The above writeup seems like its sort of on the right track but it just simply does not work for my particular setup.

Any help would be greatly appreciated. Thank you!

No.

A .ibd is not transportable.  There are two alternatives:

Read about "transportable tablespaces".
Use mysqldump (or other) to create INSERT statements to recreate the table.

The .ibd is missing two things... the .frm file that gives the schema, plus some meta info in ibdata1, along with lots of other stuff for the other tables, etc.


Which database should I look at for analyzing ~30gb, 700M records dataset? I'm looking at analyzing a kaggle dataset which is pretty normalized, specifically joining, grouping, slicing and dicing the 3 tables patients(2M) <-> diagnosis(700M) <-> doctors(2M).

I have 30GB RAM, 300GB SSD machine, so I'm able to analyze using a good old c program (I practically malloc and load everything to memory...), but I want to employ the power of ad-hoc SQL queries.

I tried different MySql, SQLLite and even mini-hadoop configurations, both on my machine and on google compute cloud and amazon EC (just to make sure that its not a config/env problem).

I can't find anything that will give me results in less than a couple of minutes to any ad-hoc query.. and if I didn't go thru the hassle of building indexes ad-hoc performance was atrocious (hours or so).

Am I missing something obvious? If its possible for my c program to fit everything in memory and do crude analysis, isn't there a database which loads everything to the buffer cache and gives lightning-fast performance for all the wonderful SQL analytics?

I know this might some a bit naive, but please go easy on me - I'm used to huge Teradata/Hadoop clusters doing heavylifting of many TB of data in very good timings, so I expected this to be very easy to handle...

(Too long for a comment...)

MySQL...

SHOW VARIABLES LIKE '%buffer%'; -- the main configuraton is the cache allocation.  But it depends on which Engine you are using and how much RAM you have.

  pretty normalized, specifically joining, grouping, slicing and dicing the 3 tables

Sorry, but we need to check the details.  Please provide SHOW CREATE TABLE, some slow queries plus their EXPLAINs.

Are you CPU-bound?  Then the queries and/or indexes need attending to.

Are you I/O-bound?  Then probably indexes.

Edit 1

Glancing at the "schema", I see something naughty:  diagnosis_code is NUMBER in one table and VARCHAR(30) in another.  This makes it impossible to use an index when JOINing.

So, I repeat, let's see the MySQL version of CREATE TABLE and the queries.


Null instead '0000-00-00 00:00:00' in mysql 5.7 Before: mysql 5.6. One of the columns in the table is created as follows:

`f_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

When i view this field in mysql client or using DataGrip, this field is displayed correctly.

Now: mysql 5.7. The field is not displayed correctly (more precisely, it is causes en error). I delete from mysql config following flags: NO_ZERO_DATE, NP_ZERO_IN_DATE and STRICT_TRANS_TABLES. And now, looking at this field i see:

Using mysql-client: 0000-00-00 00:00:00
DataGrip: null

How to change server's option for support this non-standard datetime value, initialize by '0000-00-00 00:00:00' ?

UPD: https://youtrack.jetbrains.com/issue/DBE-3377

It is the JDBC driver option we changed in DataGrip by default. But you can configure it the way you like: