ACCESS_METHODS_DATASET_PARENT Problem We had an AlwaysOn Async replica
we had some problems so we deleted the availability group
After that the database and CPU seem to work abnormally
It's very slow and we have waittype = LATCH_EX , ACCESS_METHODS_DATASET_PARENT
We didn't have such a problem till today
Can anybody help ?????

Given the wait types of LATCH_EX and ACCESS_METHODS_DATASET_PARENT I highly doubt AlwaysOn was the culprit.

These two latch types may go hand in hand. You'll want to investigate where the latch_ex waits are actually happening - sys.dm_os_latch_stats may help here where the latch_class is not 'buffer'.

Given that removing AlwaysOn did not help with the situation, I highly doubt AlwaysOn was the problem. However, AlwaysOn may have contributed to some wait time though how much is impossible to say, though I doubt it would be noticeable against your current top waits.

Paul has some information on these:
https://www.sqlskills.com/help/latches/access_methods_dataset_parent/
https://www.sqlskills.com/help/waits/latch_ex/

Additionally this whitepaper on latch contention may be helpful:
http://download.microsoft.com/download/B/9/E/B9EDF2CD-1DBF-4954-B81E-82522880A2DC/SQLServerLatchContention.pdf


Is it necessary for rownum = 1 with Query.createQuery().getSingleResult()? I'm a newie and I'm not really sure if MIN(My_Date) returns just one row if 2 or more dates are identical.

Reference is a FOREIGN KEY.

so to limit query:

SELECT MIN(Shipping_Date)
FROM Com_Order
WHERE Reference = 'X' and rownum = 1
GROUP BY Reference;

This second query seems to have same results just one value..

SELECT MIN(Shipping_Date)
FROM Com_Order
WHERE Reference = 'X';

I'm using JPA, so is it redundant to use rownum = 1 and GROUP BY Reference if I use Query.creatQuery().getSingleResult()? Are the 2 queries the same?

Ty.

The only difference between the two queries is that the 2nd will always return 1 row. Even if the table is empty or just has 0 rows with reference=X, the result will be 1 row with null in the min(date) column.

The first query will return no row in this case. The and rownum=1 is useless and does not affect anything for the specific query.

Test at SQLfiddle.


How to restore SQL Server database to a particular time? Is it possible for me to restore a SQL Server 2019 database up to a particular datetime? The database backup is full backup.
The database is full recovery mode, and only one transaction log backup after the full backup.
Thanks.

You can do it through T-SQL this way:
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY,
STOPAT = 'Mar 23, 2009 05:31:00 PM'
GO

But don't forget you can use the GUI from SSMS. The graphical solution is easier to understand especially if you are 4 people around the same monitor and the manager is behind you with a stick in his hand:

Right click on the database > Tasks > Restore > Database

General > Timeline and setup the time for database restoration

Check in “Specific date and time” to set up the time for database restoration, press “OK” to save changes, and press “OK” button in the previous window


Why have SQL Server services disappeared from services.msc? I'm running an Always On Availability Group with 2 SQL Server 2014 database servers in an AD domain. Updates are supplied to all domain servers via WSUS. Also have a third MS Reporting Services server that is not part of the group.

This morning, with DB01 as primary and DB02 as secondary, I installed Microsoft SQL Server 2014 (SP2-CU12) (KB4130489) - 12.0.5589.7 (X64) on all three servers. Went in ok on the report server and DB02, Failed to go in on DB01 (error 86D80017). Instead of troubleshooting the error, I decided to manually install KB4130489. Manual install on DB01 went ok. AOHA Dashboard shows all is ok. What is weird is that on DB01, the Services Management Console UI does not show any of the expected SQL-Server related services (5 of them in my case). SQL Server Configuration Manager shows services (as expected - see screen shot below). They just don't show up in services.msc

Why have SQL Server services disappeared from services.msc on DB01?

SQL Server Configuration Manager shows services (as expected)

However, services.msc does NOT show services as expected (they should appear right after "Spot Verifier" service

Can't explain why this cured my problem, but while using services.msc on the computer that wasn't showing the SQl Server services, I clicked on Action->Connect To Another Computer. I connected to the other DB server (where SQl Server services appear ok on its services.msc) and then I connected back to  he local computer (DB01). The SQL Server services now appear as expected


What privileges are needed to run EXPLAIN PLAN FOR CREATE INDEX? My user can EXPLAIN PLAN FOR any query I like, but not for CREATE INDEX. I need to do that in order to estimate a suggested index's size, and using sysdba is out of question. What privileges should I ask for? This is Oracle 11.2.0.3.

SQL> explain plan for create index INDEXNAME on SOMEONE.SOMETABLE("FLD1","FLD2");
explain plan for create index INDEXNAME on SOMEONE.SOMETABLE("FLD1","FLD2")
                                                   *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> explain plan for select * from SOMEONE.SOMETABLE order by fld1 desc;

Explained.

SQL>

The problem is most likely in you not having sufficient privileges to create the index in question -- nothing to do with EXPLAIN PLAN itself.

To quote the manual, "To create an index in another schema, you must have the CREATE ANY INDEX system privilege".


Innodb_buffer_pool explanation The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process.

According to the mysql docs:

  On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size.

Now, how does the percentage approach makes sense here when we are talking about actual numbers?

Lets say that currently my db uses 80 gb of ram and 20 gb of ram is left for other tasks.
After a period of time, my database grows bigger and i decide to increase the buffer pool size. To keep the 80% proportion, i have to increase the total ram of the server, even though other processes did not require more ram memory.
Why not just add the additional ram while keeping the same 20gb of ram for the other tasks?

The 80% is just a general rule of thumb for helping people start with a more sensible value than the default.

Buffer pool size calculation can actually be more complicated. Ideally frequently accessed data pages should remain in the buffer pool and you don't want too much eviction to happen because of its impact on performance. This is again generic but a more realistic measurement to have the buffer pool large enough to accommodate hot data + enough space for avoiding unfrequent reads triggering too much eviction.

Just because you have a database 1TB big you don't have to have 1Tb of buffer pool if you only read the same 100MB all the time.

Keep in mind also that inserts and updates also affect buffer pool usage because the operation happens there. You should try to match the amount of data you can write into innodb logs (controlled by innodb_log_file_size) and the space required for this in buffer pool. Even if you have large innodb log files but you don't have enough space for insert than eviction will cause flushing data pages to tablespace which in essence have the same effect as checkpointing.

One empirical way to correctly size buffer pool is to set as high as your datasize (or as large as possible) and run normal production load on it while decreasing the size constantly while measuring transaction rate, response time and threads_running. When you see performance starting to drop that's the size that you need at least.


Friendship and Messages in a MySQL table relational schema problem (concerning big data manipulation) Before you start suggesting this question for duplicate read it, I have searched and i haven't found any proper solution.

So, i'm building a social networking site (I know that you are laughing right now)
and i want to save the friendship states and the messages that users send everyday.

Now let me explain the problem more clearly, I thought that if i use a table for the friendships and have 40K users (and the average user have 50 friendships) that means that the table is going to grow to 2 million rows!

This will result to slow data processing or even to a database failure.

What if I use the file system to store the friendships instead of a Database?
I mean, i can store friendships in files.  (every user could have a "buddy.dat" file in his personal subdirectory that contains his friendships)

I have already done this thing for the messages, every user has a personal subdirectory created with a "messageges.dat" file that contains his conversations.  (This means that I'm going to have a directory with 40K personal sub-directories! 1 for every user)

Are there any consequences if I use the file system for this purpose?
(store the friendships/messages to files and then retrieve the data from there)

What is your opinion / solution?

EDIT:

I have news guys!!
I made a script to generate 40K directories in the "messages" directory and another script that generated 2 million rows in the users table!!

So, in the 40K directories case, they didn't slow down my site's scripts, it only took me half a minute to list them if i wanted with the file browser in a Debian distro.
In the mysql 2 million rows case, the 2 million rows didn't slow down my database or the site either, everything was ok.

Check this out, and links within (1, 2). InnoDB has a limit of 4 billion tables. There is no compelling reason to use the file system to store data that is better stored in a database.

Take a look here to see that virtually every form of social networking software uses an underlying database - normally MySQL or PostgreSQL (which I would recommend). 2 million rows is very small in todays database terms - no need to have 40.000 tables (1 for each user either) - although you may beed to look at some form of partitioning - take a look at  the answer here.

Anyway, with 40.000 users, shouldn't you perhaps be thinking of using more than 1 machine?
1 guy here tried putting > 32.000 schemas onto a MySQL instance - and couldn't (follow links there also). With 40K users, put 20K in a separate schema on each server if you want to - see here also. Implicit assumption - not all of your schemas will be very active simultaneously.

Databases are designed for searching and sorting and aggregating data - file systems aren't - are you going to grep huge files? Not that it's impossible, but your time is probably better used doing other things.

Final suggestion, download some of the Open Source stuff out there and check to see what they've done - keep what you like and throw away the rest.

[EDIT in  response to OP's questions]

You asked:

  The private messages aren't going to be searched i guess, is it better
  to keep the file system for this job?

I would still hold them in the database - you never know when you're
going to need them - file systems aren't generally designed for lots
and lots of small files. Plus, in a file system scenario, how do you store who is replying to whom? By having yet more files? Or by putting in a header? Uggghh! A BLOB for the message with fields for replying_to_message_ID &c. - much easier!

You also asked:

  As for the friendship records i will use the database, will InnoDB
  make the difference regarding the maximum rows and the performance?

See the post by Bill Karwin here. Bill Karwin in training manager with Percona. Anybody with Percona is likely to know a thing or two about MySQL.

People argue about the performance of MyISAM v. InnoDB - Bill Karwin again.
MyISAM is old and will soon be deprecated. Don't use unless you have a compelling reason to do so (my guess is that you won't).


Logging procedure, package errors to OS's Syslog Good day,

Setup: Oracle 11G on Solaris 10 x86

We'd like to capture the errors/failures of our nightly scheduled procedures/ packages in the OS's syslog. That log will then be sent to a remote NMS managed by the NOC who'll alert the on-call personnel. Can and how can this be done?

Thanks.

Of course it can be done, the question is, do you really want this?

Scheduler jobs can raise events (put in a queue). You can capture (dequeue) those events using Advanced Queueing. For example:

Monitoring Job State with Events Raised by the Scheduler

How To Use DBMS_SCHEDULER To Publish Events For Oracle Advanced Queueing (Doc ID 871718.1)

Once you have the events and details, you need to write them to syslog. You can do this by using external procedures, for example:

write to syslog from pl/sql

Or, you could keep it nice and simple, and use the built-in e-mail notification support for Scheduler, for example:

BEGIN
 DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  job_name   =>  'EOD_JOB',
  recipients =>  'jsmith@example.com, rjones@example.com',
  sender     =>  'do_not_reply@example.com',
  subject    =>  'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
  body       =>   '%event_type% occurred at %event_timestamp%. %error_message%',
  events     =>  'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
END;
/

Monitoring Job State with E-mail Notifications


Escape characters in Oracle dump When I take a dump of a particular table which has CLOB data type, the resulting dump doesn't handle escaping of special characters.

I get ' un escaped, so insert statements won't work.

Any ways to fix it?

I'm right clicking on the query result and getting the dump.

I'm using SQL Developer and am new to Oracle.

As someone who spent 4 years of his life dealing with converting CLOB type data from legacy systems into Oracle, I feel your pain.

I highly recommend using an Oracle built function, such as REPLACE to go through and scrub all of the garbage characters from CLOB type fields. Additionally, remember that most systems consider a "return" to be a new line and carriage return in unison, so you might have to scrub ascii characters below 33. IIRC the new line/carriage return combo was char(10)char(13). You can easily google a list of of the "invisible" ascii characters, and hunt down the ones you want removed.

HTH.


MYSQL Select data from two different tables BUT represent the data in multiple rows I am needing to select all email addresses from two different tables with a single query.  The goal of this is to list out all email addresses a client has per row.

Both tables are structured differently from one another.

Table 1 Structure

ID    First Name    Last Name    Email
1     Mike          Smith        mikesmith@test.com
2     Jane          Doe          jane@doe.com

Table 2 Structure

ID    Name             Value
1     work_email       mikesmith@company.email
1     alt_work_email   mikesmith@second.email
2     school_email     jane@school.edu
2     address 1        123 west main street
2     city             Houston

Normally, If I knew the the exact Name for Table 2 would do a LEFT JOIN and be golden but this is not the case.

SELECT
tb1.id,
tb1.first_name,
tb1.last_name,
tb1.email as Value,
tb2.value as Value2,
FROM  table1 as tb1
LEFT JOIN table2 as tb2 ON tb2.name LIKE '%email%' AND tb2.value LIKE '%@%'
AND tb2.value != '' AND tb2.client_id = tb1.id

I know the above query is inaccurate but I needing something like this to collect all email addresses from Table 1 and where Name contains the word email from Table 2 and display those emails along with the Name value associated with the email address.  Table 1 Name value will be the column header of Email.  Desired output of what is needed is above.

Desired Output

ID    First Name    Last Name    Name           Value
1     Mike          Smith        Email          mikesmith@test.com
1     Mike          Smith        work_email     mikesmith@company.email
1     Mike          Smith        alt_work_email mikesmith@second.email
2     Jane          Doe          Email          jane@doe.com
2     Jane          Doe          school_email   jane@school.edu

Any help on this would be amazing and very much appreciated!

SELECT id, First_Name, Last_Name, 'Email' Name, Email Value
    FROM table1
UNION ALL /* or UNION DISTINCT */
    SELECT tb1.id, tb1.First_Name, tb1.Last_Name, tb2.Name, tb2.Value,
    FROM  table1 tb1
    LEFT JOIN table2 tb2
        ON tb2.Name LIKE '%email%'
       AND tb2.Value LIKE '%@%'
       AND tb2.client_id = tb1.id
/* ORDER BY 1 */

PS. AND tb2.value != '' condition is excess. If false, then AND tb2.Value LIKE '%@%' is false too.


"The SELECT permission was denied" only for some of my tables In my database there are about 100 tables on 2 tables I get the following error when accessing the database by a SP (all data access uses SP):

  The SELECT permission was denied on the object 'XXX' database 'XXX' schema 'dbo'. sql server 2008

I can fix the error by giving grant on the select:

GRANT SELECT
  ON OBJECT::[dbo].[XXX] TO [allsp_user]
  AS [dbo];
GO

when running

select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
 permission_name,
 state_desc
from sys.database_permissions

I see only for my 2 tables:

XXX allsp_user  dbo SELECT  GRANT

My problem is that the error comes only on 2 tables, all others are working fine with selects. None of them have a special permissions except the 2 I added the Select Grant. I do not want to Grant the user the select permissions because I do not understand why he needs them, in all other projects and on all other tables it works fine without the permissions. How to find what causes the error on those 2 tables?

The difference between the two tables and the other tables was not in the table it self, but in how the stored procedures where doing the select. In this case the two tables where the only 2 which had some dynamic SQL in them which causes the error. Replacing the dynamic sql fixes the need for Grant select on the tables.


MongoDB - slow remove query I have a collection in MongoDB with 800M+ records. I wanted to do some cleanup, but the remove operation is really really (unusably) slow - even deleting 1000 records can take 10+ minutes.

The structure of one record in the collection is something like this:

{
     'stID' : '00000001',
     'recordID' : '28-4',
     'date' : ISODate('2017-06-06 20:00:00'),
     ......
}

There is a compound index on {'stID' : 1, 'recordID' : 1, 'date' : 1} and my delete query is like this: db.coll.remove({ 'stID' : '00000001', 'recordID' : '28-4' }, { multi: true });

The database is part of  a replicaSet, with one secondary and one arbiter, and I do understand that the delete operation has to fill the oplog with the _ids of each record to delete, but still I find it hard to understand why this could be so slow, especially since:

there is no considerable system or IO activity on the primary or the secondary
the secondary is not lagging behind the primary

We have never experienced problems with find or write/update operations. Can you suggest what other parameters/commands/tools should I use to see what is going on with the query and the servers.

The storage engine we use is WiredTiger. The server has 128GB of RAM and fast SSD drives in a RAID 1 configuration.

I've had improved performance by using bulkWrite:

db.ig_posts.bulkWrite([
   { deleteMany: { "filter": { /* your query here */ } }
]);


Caveats of OUTER JOIN on nested JSON value I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I right join on that query to get what I need, and this works well:
select v.id from (
    select distinct json_data ->> 'elementId' as elementId
    from content
    and json_data->> 'elementId' in ('id1', 'id2', 'id3')
) as a
right join (values('id1'), ('id2'), ('id3')) as v(id)
on v.id = a.elementId
where a.elementId is null

The above query works perfect except for the fact that I want to I should be able to reduce the nested query to a regular select if I do the comparison on json_data ->> 'elementId' directly.
My attempt:
select v.id
from content a
right join (values('id1'), ('id2'), ('id3')) as v(id)
on json_data ->> 'elementId' = v.id

After some debugging I realized that this will never work because the content table will always contain a row even if json_data ->>'elementId' is null.
Edit: I had an extra WHERE statement which wasn't stated in the question, once I moved this after the ON my query was fixed
My question is; Is there a way to avoid using a nested query when wanted to do a left join or right join  on JSON data?

Use NOT EXISTS:
SELECT *
FROM  (VALUES ('id1'), ('id2'), ('id3')) AS v(id)
WHERE  NOT EXISTS (SELECT FROM content WHERE json_data ->> 'elementId' = v.id);

Or if you prefer a join:
SELECT v.id
FROM  (VALUES ('id1'), ('id2'), ('id3')) AS v(id)
LEFT   JOIN content c ON c.json_data ->> 'elementId' = v.id
WHERE  c.json_data IS NULL -- or use the PK column

Either is an "anti-join", technically; and both will probably result in the same query plan.
See:

Select rows which are not present in other table

Consider upgrading to a current version of Postgres. 9.4 has reached EOL in Feb 2020.
Index
But even Postgres 9.4 already supports jsonb which (unlike json) allows a GIN index to support your query. See:

How to get particular object from jsonb array in PostgreSQL?
What's the proper index for querying structures in arrays in Postgres jsonb?

Or if you are focused on this query exclusively, a plain btree on an expression should be the optimum:
CREATE INDEX ON content ((json_data ->> 'elementId'));

Related:

PostgreSQL operator uses index but underlying function does not


How is an Active Log different from MinLSN? MinLSN is the LSN of the oldest uncommitted transaction:

What exactly does Active Log hold?
Isn't it supposed to hold all the uncommitted transactions?
Does it hold any other log records which are already committed?
What happens to MinLSN and Active Log when a CHECKPOINT happens?

MinLSN is the LSN of the oldest uncommitted transaction.

This is not exactly true

LSN stands for Log Sequence Number.
Each log record has a LSN and each new log record is written to the logical end of the log with a higher LSN than the previous LSN.

Using this simplified Log Sequence:

LSN Log
80  Begin Tran 1
82  Begin Tran 2
83  Update Tran 2
84  Check Point
85  Update 1
86  Commit Tran 1
87  Check Point
88  Update Tran 2

Next LSNs will be 89, 90, ...

The Log Sequence Number of the first log record that is required to successfully perform a full recovery of the database is recorded in checkpoint records. This LSN is called Minimum recovery LSN or MinLSN.

MinLSN is the minimun of:

LSN of the beginning of the oldest active transaction
LSN of the start of the last checkpoint
LSN of the beginning of the oldest replication transaction that has not yet been replicated

If we look at the above LSN records, MinLSN candidates are:

Oldest active transaction = Begin Tran 2 with LSN 82
LSN of the last checkpoint = LSN 87

Therefore MinLSN is LSN 82.

  What exactly does Active Log hold?

The list of LSNs starting at MinLSN and up to the last recorded LSN is called the Active Log. Here Active log is composed of LSN 82 to 88. MinLSN is just the first LSN of the Active Log.

  Isn't it suppose to hold all the uncommitted transactions?
  Does it hold any other log records which are already committed?

You can see that the Active Log still contains already commited transactions (TRAN 1) because their LSNs are bigger than the MinLSN.

  What happens to MinLSN and Active Log when a CHECKPOINT happens?

When a new checkpoint occurs and is recorded:

LOG records linked to a Dirty page being written to disk are written from memory to disk before making any change to the page. This guarantees the successful recovery of the database before writing a page to disk. Since LOG are written sequentially, records in memory not related to the current page being written may have to be record before it.
Dirty pages are written to disk (ie. all pages in memory that have been changed since the last checkpoint or since they were read from disk, however the status of the transactions),
Checkpoint LOG record is created. Its LSN is compared to the LSN of the begining of the oldest active transaction:

If its LSN is smaller, it becomes the new MinLSN.
Otherwise, the LSN of the oldest trasaction remains the MinLSN.


What is the SQL command to get 20 closest points from some center location There are 1.6 million rows.

Must not compute distance for all rows.

Spatial index is available

This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added my spatial index. Then what? What's the SQL command?

If you can point me to a web it'll be great.

    BusinessID  varchar(250)    utf8_unicode_ci         No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
    Prominent   double          No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
    LatLong     point           No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
    FullTextSearch  varchar(600)    utf8_bin        No  None        Browse distinct values  Change  Drop    Primary     Unique  Index   Fulltext
With selected: Check All / Uncheck All With selected:
Print viewPrint view Propose table structurePropose table structureDocumentation
Add new fieldAdd field(s) At End of Table At Beginning of Table After
Indexes: Documentation
Action  Keyname Type    Unique  Packed  Field   Cardinality Collation   Null    Comment
Edit    Drop    PRIMARY BTREE   Yes No  BusinessID  1611454 A
Edit    Drop    Prominent   BTREE   No  No  Prominent   0   A
Edit    Drop    LatLong BTREE   No  No  LatLong (25)    0   A
Edit    Drop    sx_mytable_coords   SPATIAL No  No  LatLong (32)    0   A
Edit    Drop    FullTextSearch  FULLTEXT    No  No  FullTextSearch  0

Note:I am not dealing with lines or polygons, only points. I need more direct samples. Also I am not interested in points within a circle. I want 20 closest points. The fact that those points are within a circle or not is irrelevant. (Actually I will use filter, however, that's latter)

If a SPATIAL INDEX does not work for you, see
  http://mysql.rjweb.org/doc.php/latlng
It is a description of how to efficiently "find the nearest" on the globe.


Returning true or false in a custom function I have this mysql table schema

CREATE TABLE `nxt_records` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `tel_number` VARCHAR(50) NULL DEFAULT NULL,
    `nxt_status` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

and this function

DELIMITER $$
CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar
BEGIN
 if (exists(select tel_number from nxt_records where tel_number = in_number ))then
return 'true';
else
return 'false';
end if;
END$$
DELIMITER ;

which i want to use inside this stored procedure

DELIMITER //
CREATE PROCEDURE nxt()
BEGIN
if (the_nxt(44) = true)then

select 'found.';
else
select 'nothing found.';
end if;

END //
DELIMITER ;

when i run the function code,i get the error

  SQL Error (1064): You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near 'BEGIN   if (exists(select tel_number from
  nxt_records where tel_number = in_num' at line 2

What is causing this error?.

I found the error to have been my varchar

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar

which i changed to

CREATE FUNCTION the_nxt (in_number VARCHAR(255)) RETURNS varchar(255) and worked.


How can I improve this query in PostgreSQL? I've been using PostgreSQL for a little over a year. I am continually whacked by queries that have counts in them, such as:

SELECT COUNT(*) AS count_all, listings.id AS listings_id
FROM "listings" LEFT OUTER JOIN houses ON houses.id = listings.house_id
WHERE "listings"."subscription_id" = 123
  AND (exists (select 'x' FROM apps
               WHERE apps.listing_id = listings.id
                 AND apps.subscription_id = listings.subscription_id LIMIT 1))
                 AND "listings"."listing_status" = 'Active'
GROUP BY listings.id

How can I write this query in PostgreSQL where it will return in a reasonable amount of time?

My explain plan is the following. I admit I really don't know how to read it very well. But it looks bad:

HashAggregate  (cost=2579302.75..2579319.31 rows=1656 width=8)
  ->  Bitmap Heap Scan on listings  (cost=304056.70..2579294.47 rows=1656 width=8)
        Recheck Cond: ((subscription_id = 162) AND (listing_status = 'Active'::citext))
        Filter: (SubPlan 1)
        ->  BitmapAnd  (cost=304056.70..304056.70 rows=3313 width=0)
              ->  Bitmap Index Scan on listings_subscription_item_idx  (cost=0.00..570.84 rows=5637 width=0)
                    Index Cond: (subscription_id = 162)
              ->  Bitmap Index Scan on index_listings_on_listing_status  (cost=0.00..303484.78 rows=3936030 width=0)
                    Index Cond: (listing_status = 'Active'::citext)
        SubPlan 1
          ->  Limit  (cost=0.57..683.01 rows=1 width=0)
                ->  Index Scan using apps_listing_idx on apps  (cost=0.57..42312.00 rows=62 width=0)
                      Index Cond: (listing_id = listings.id)
                      Filter: (subscription_id = listings.subscription_id)

This query works fantastically on a laptop with the same database, but dies in production and takes over a minute to return. Does anything jump out at anybody for things I could rapidly improve?

The database has autovacuum turned on.

I immediately see a few things that confuse me here, which you should look into.

Unnecessary Join

In your query you have a portion

...  LEFT OUTER JOIN houses ON houses.id = listings.house_id ...

yet no values from the houses table are in your SELECT.  The good news is that this is not affecting your performance, because the query optimizer has wisely left the houses table out of the query entirely.  This does concern me a bit just in principle.

Odd Query Plan

In your query plan, it chooses to enforce the conditions of

subscription_id = ### AND listing_status = 'Active'

by performing two Bitmap Index Scans, followed by a subsequent BitmapAnd.  This seems like a highly inefficient way to perform this operation when the Bitmap Index Scan on listings_subscription_item_idx returns approximately 5637 rows, but the Bitmap Index Scan on index_listings_on_listing_status returns over 3 million!

Is there any chance that you have a very large number of entries in listings for which listing_status is not 'Active'?

In this case, I would think the optimal plan would be to use the Bitmap Index Scan on listings_subscription_item_idx to extract a set of candidate rows, and then simply filter this small set of results for listing_status = 'Active'.  I'm surprised the optimizer has chosen this plan, but I have a wild guess that it's because the listing_status = 'Active' is actually highly selective on that column (such that maybe only 10% or less of the listings are Active).

To try and address this, I suggest trying the following query:

WITH listing_by_subscription AS(
SELECT * FROM "listings"
WHERE "listings"."subscription_id" = 123)
SELECT COUNT(*) AS count_all, listing_by_subscription.id AS listings_id
FROM listing_by_subscription
WHERE listing_by_subscription."listing_status" = 'Active'
AND EXISTS (SELECT 1 FROM apps
            WHERE apps.listing_id = listing_by_subscription.id
            AND apps.subscription_id = listing_by_subscription.subscription_id LIMIT 1)
GROUP BY listing_by_subscription.id;

Optimization Barrier in CTE

Here, I've tried to make use of the optimization barrier found when using a Common Table Expression.  Any time you use the WITH() expression, the Postgres optimizer treats it as a separate block which it must calculate separately from the overall optimization, sort of like a 'forced materialization'.  In this case, I try to use that to force the most selective predicate, the subscription_id = ### portion, to whittle down the result set first.

Try out this query and see if it helps!

work_mem

Last but not least, there's been a lot of talk abou work_mem settings today.  As @CraigRinger mentioned, we really need to see an EXPLAIN (ANALYZE,BUFFERS) to get the complete picture, but I'm wondering if this setting is sufficient.

Check you postgresql.conf file, and report back your work_mem setting, along with that EXPLAIN (ANALYZE,BUFFERS), if you're still having trouble.

Hope this works!


How to convert binary string value from error log? I'm trying to investigate an issue with a query I'm currently debugging.

The error I get from my client code is the following:

Duplicate entry '\x9F\x98\x9Ern\x06\x8BN\x97\x8D\xB5A\xB5e\x12\xC9-\x9C\xE1\x1Ai\' for key 'PRIMARY'.

How do I convert this binary string value to its original value so I can find out which part of my SQL query it belongs to? My goal would be to take this string and be able to convert it back to its binary(16) equivalent.

I found this MySQL documentation that explains briefly how the conversion works, but I'm not sure how to proceed in SQL.

That error indicates that you are inserting a value in a column that is expected to be unique. The error translates any values in the range 0x20 to 0x7E into their easier-to-read form, and uses an \x prefix for others. So the value you are inserting probably looks like this:

0x9F989E726E068B4E978DB541B56512C92D9CE11A695C

(I got that by keeping any pair of values appearing immediately after an \x, and converting any other values (r, n, N, A, e, -, i, and \) to their ASCII hex values. I assume the \ is a literal, but I would have expected it to be escaped.)


Number of Worker threads available in PostgreSQL In SQL Server we can have many worker threads per CPU core. Does anyone know if the same is true in PostgreSQL and can point me at a reference? (assume version 11).

The background is that we have some OLTP workloads currently in SQL Server that are being migrated to PostgreSQL. I'm wondering if there is a risk of running out of threads if parallelism is enabled and connections being refused (which can happen on SQL Server when we run out of threads), and whether I should recommend that parallelism is disabled by default.

PostgreSQL does not use threads, it uses coordinated single-thread processes.  It will not use more than max_parallel_workers, system-wide, at one time.  If it would exceed that, the queries will not error out, they simply run with fewer worker processes than they "wanted".

If the number of connections you have trying to run queries at the same time is always greater than your number of CPUs, then you probably might as well turn parallelism off.  OLTP usually are not great candidates for parallel execution anyway.


Resetting a new archiving database I have an archiving question.

We have a History DB used for archiving old records from our main system and the application knows to query it

However, it becomes too big.

I would like to reinitialize the History DB.
My idea was - in a nutshell:

Close down this DB,
Make it READ-ONLY,
Rename it to *old
Create a similar history DB (same structure/tables).
With the help of views/synonyms, the application will be able to query the data for both DBs without a code change.

My question is about the last part

Will that work ?

Can I create view/synonym (the view will query both of table DBs with UNION ALL) that will be the default go-to object name instead of the actual original table name  ?

Our main problem is the backups, as the Archiving is done daily, we need also to back up this DB, and as it's huge DB, it's a waist of time and resources.

Thanks,
Roni.

The built-in way to reduce backup size for VLDBs with read-only data is to parition your large tables, placing older partitions on read-only filegroups, and then take infrequent backups of the read-only filegroups (or whole database), combined with regular backups of the read-write filegroups.  Then you can perform a Piecemeal Restore.

  Will [using UNION ALL views over multiple databases] work ?

Yes, you can typically replace a table with a view without changing the application, unless the application does something unusual.  The risk is in the performance, as the UNION ALL view across multiple databases doesn't really perform the same.

You might just try using columnstore compression on the large tables in the archive database.


What does "WSREP Aborter Idle" mean in a MariaDB Galera cluster? I've tried to Google WSREP Aborter Idle but there are literally 3 results. Does anyone know what this means?

Should I be concerned that the these processes have been opened for so long or is this just part of the normal cluster operation?

Thanks!

After extensive searching the only reference I can find is a bug report filed with Codership. Within that report the following quote makes reference to the process and seems to point to this being both required and part of the normal operation of the Galera cluster.

  wsrep aborter thread is missing in the process list in node carthusian. Checking by gdb, shows that aborter thread is alive and working well.
  Also whole cluster is healthy and works good under sqlgen test load

If anyone has further details about this I'd be interested to learn more!


Not using foreign key constraints in real practice. Is it OK? Not using FK constraints is my company's untold rule. FK constraints are used only when designing ERD and not used when creating tables.

According to my senior, in real practice, those are very time consuming obstacles when we are dealing with urgent issues. He says when we need to use INSERT/UPDATE/DELETE statements immediately, the constraints block those statements to be executed and writing statements while keeping the constraints is time consuming too. I even heard many other companies are doing this.

While I kinda understand the struggles, I'm not sure if this is a good way since it's quite opposite to my understanding to DB. This company is my first job too, so I don't know how other companies dealing with this.

In practical way, what is your opinion about this? Can this be justified? Are there better ways? How other companies are doing about this matters?

UPDATE: It seems it is a quite common approach for South Korean companies. I asked a few other seniors who worked for other companies, and most of them are saying they all doing like this. And even one of them worked for a financial company! Interesting...

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits.

The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there1. This way we can be sure we only ever capture orders for customers that actually exist, for products we actually produce and sell. A lot of people think this is a good idea.

The reason we declare them inside the DBMS is so it can take care of enforcing them. It will never ever allow in any data that breaks the rules. Also it will never allow you to get rid of data required to enforce the rules. By delegating this task to the machine we can have confidence in the integrity of the data, no matter what its source or when it was written or which application it came through. Of course this comes with a cost. The DBMS has to check the rules are being followed, for each and every row, for each and every query, all the time. This takes time and effort, which is a load on the server. It also requires the humans to submit DML in a sequence that respects the rules. No more slyly forcing in an Order, then catching up with the admin afterwards. Oh no naughty human, you must first create the Customer, then the Product (and all the prerequisite rows) and only then may you create an Order.

Without foreign keys we are much freer with what we can do, and the order in which we can do it. Problematical rows can be removed ad hoc to allow critical processes to complete. The data can be patched up afterwards, when the panic is over. INSERTs are generally a little quicker (which adds up over time) because no FKs checks are done. Arbitrary subsets of data can be pulled from the DB as desired without having to ensure all supporting data is included. And so on. This can be absolutely fine if the people involved know the system, take careful notes, have good reconciliation routines, understand the consequences and have the time to tidy up after themselves. The cost is that something, somewhere is missed one time and the database deteriorates into barely creditable junk.

Some teams put the checks in the application rather than in the database. Again, this can work. It seems to me, however, that the total server load will be much the same (or slightly higher) with this approach, but the risk of forgetting some check in a bit of code somewhere is much higher. With DRI the rule's communicated to the computer once and is enforced forever. In application code it has to be re-written with each new program.

For my two cents' worth, I'm all for foreign keys. Let the computers do what they're good at doing, like routine repetitive checking that column values match. We humans can concentrate on dreaming up new and interesting stuff. Having taken responsibility for a system a few months back I'm adding FKs to most tables. There are a few, however, where I will not be adding them. These are where we collect data from external sources. The cost of rejecting these rows is greater than the cost of accepting bad data and fixing it up later. So, for these few tables, there will be no foreign keys. I do this open-eyed, knowing we have monitoring and corrective procedures in place.

1I acknowledge the existence of multi-column foreign key constraints.


Dangers in dropping all tables in a schema with CASCADE The answer to this question suggests I can use the CASCADE option of DROP SCHEMA to remove all a schema and all its tables, but warns:

  CASCADE - Automatically drop objects (tables, functions, etc.) that are contained in the schema, and in turn all objects that depend on those objects

Being a novice to databases, I'm unsure about what objects may depend on objects within a schema. Is it possible that a table or some other object outside the schema that is dependent on an object inside the schema will be dropped as well? If I just want to eliminate a schema and all its contents without changing anything else, is using CASCADE a good idea?

run it in a transaction, you'll get warnings for every cascading drop, ROLLBACK if don't like what you see.


mysql import/drop very slow on pyshycal server I have mysql installed on a physical server (name it Mysql A) and importing 1 MB .sql (from mysqldump) Magento file could take more than 5 minutes, and dropping database slow as well.

whereas importing the same .sql file into a mysql server that is installed on a VMware Virtual machine (name it Mysql B), it just takes 3 seconds.

the disk write/read test results on both physical and vm machine are equal.

Here is the settings on MySQL A (physical):
http://pastebin.com/3qAxCJxz

and MySQL B (vm):
http://pastebin.com/PqZePH8Z

both settings are default ubuntu mysql installations.

Any help on why it takes dramatically slower when importing/dropping database on my physical server? It's a HP Proliant ML-10 with 16GB of RAM, and  Intel(R) Xeon(R) CPU E3-1220 V2 @ 3.10GHz and the VM's host machine specs and performance has the same benchmark results as the server #A

Thanks

It is important to know the load average in your MySQL server, in the VMWare server with Percona DB, the server (i assume) has no working tasks. but in the MySQL server you have some extra Work.
If your MySQL server is on a unix/linux server you can check your load average with the command top-i or simple use up time.

Another think you can check is how many connections do you have in your MySQL and your Connection Usage, the traffic, number of Queries, Query Caché,etc.

I hope this can help you to take a direction of way do you have this problem.s
Please update with more information if you still need more help.


How to combine Oracle RAC with in-memory database to isloate OLAP from OLTP I am going through a document
https://www.oracle.com/technetwork/database/options/database-in-memory-data-sheet-2338744.pdf
There is a thing written "Oracle Real Application Clusters (RAC) can be combined with Oracle Database InMemory to isolate analytic workloads from OLTP workloads by running them on different
servers of a cluster"
Can anyone explain how would it be possible as RAC has the same storage under it and how can different nodes take responsibility of OLAP and OLTP workload independently

RAC has shared storage, but workload can be segregated using different service names for each. Create an OLTP service name for that workload and only run the service on nodes to be used for OLTP processing. Same for analytic workloads.


Is Scale out deployment for SSRS needed in an Availability group? This is my first attempt at setting up SSRS, so I apologize if any question appears basic.  I've researched as much as I can and am confused about the below things.  I have my environment described below as well.

We have a 2 node AG in a WSFC.  lets call them sql1 and sql2, with listener of sql-listener.  SQL 2016 enterprise.  My intent is to have all application connections running reports sent to the secondary node to keep the load off the primary server.  Nodes are setup in synchronous commit, read/write primary mode, and readable secondary.  Questions:

is Scale out deployment required in a setup like this?  I can't seem to get Sql2 to show up on the scale-out screen on Sql1 for me to add.  Event viewer is giving errors on Sql2 like "report server windows service cannot connect to the report server database" and The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead."
Is the above error likely caused by using the built in virtual service account and not a domain account?  both nodes have the report server db's and are synced in the AG.  I have re-setup SSRS using the config manager on both nodes and they are identical in every way except the URL's reflect their local servernames   http://sql1:80/ReportServer and http://sql2:80/reportserver.  I had also added a new URL with the listenername on both servers, but the issue remains.
3.) the connection string must also be changed as I understand it.  I added the applicationintent = readonly and changed the data source to the sql listener.  Is that all that is required to change on the connection string to automatically point incoming connections to the secondary node given my AG setup above?

Configuring a Scale-Out SSRS instance on Always On is fairly straightforward. You follow the normal Scale-Out deployment steps but when you connect your first and second SSRS instances to the ReportServer database you simply point to the Always On listener.

There is a great blog article here that goes through the steps from start to finish.

You do not specify applicationintent = readonly in the connection string because you want both SSRS instances to connect to the primary ReportServer database. Use that connection string option in the connection strings for your report data sources (if they are in an AG) to redirect report workload to the secondary replica.

Lastly, using the Virtual Account is likely causing your issues as it won't have access to the RS databases. Use a domain service account instead.


Why is my PostgreSQL ORDER BY case-insensitive? I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior:

veure_test=# show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table
-----------------------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)

And uname -a:

Linux ---- 3.2.0-4-amd64 #1 SMP Debian 3.2.65-1 x86_64 GNU/Linux

However, on my iMac, with Postgres 9.3.4, I get the following:

veure_test=# show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table
-----------------------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)

And the uname -a:

Darwin ---- 14.4.0 Darwin Kernel Version 14.4.0: Thu May 28 11:35:04 PDT 2015; root:xnu-2782.30.5~1/RELEASE_X86_64 x86_64

I'm mystified by why the Debian version appears to be case-insensitive and the OS X version is not. What am I missing, or what other information do I need to provide?

Update: On my Mac, the pg_collation table shows I have an en_US.UTF-8 collation, but on Debian, I have an en_US.utf8 collation. Thus, on my Mac:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.UTF-8";
    bar
-----------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)

And on Debian:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.utf8";
    bar
-----------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)

So en_US.UTF-8 and en_US.utf8 have different sort orders?

So en_US.UTF-8 and en_US.utf8 have different sort orders?

No, these both are the same, just a different naming convention.

  I'm mystified by why the Debian version appears to be case-insensitive and the OS X version is not.

Yes, you are correct. This is the default behavior on Mac. Collations don't work on any BSD-ish OS (incl. OSX) for UTF8 encoding.

Here is a reference to prove that:

Problems with sort order (UTF8 locales don't work

As a_horse_with_no_name said, Postgres uses the collation implementation from the OS. There is no way to get the same result on both operating systems.

In your case you may(I said maybe)do like this: ORDER BY lower(fieldname).


Try to define appropiate memory size for this postgres database I have a site with around 150.000 daily page views using a 180Mb postgres database size running on a 800 mb ram db on Heroku.

This site does not do many updates, queries are mostly select that are cached on the page. SELECT count(*) FROM pg_stat_activity; always show 30 connections to the database (that should correspond to at least one connection active per worker).

By looking into new_relic I see queries to the database seem to be around 10ms but I have the impression that even If the 800 mb may be ram. Those queries could eventually run noticeable faster.

Is there any relationship in this scenario to giving more ram to process the queries faster or it doesn't matter since the db size is below 200mb?

So long as the DB fits in RAM used for disk cache and/or shared_buffers there is very little benefit to adding more RAM.

Focus your performance efforts elsewhere - eliminate unnecessary queries, cache frequently read results that don't need to be perfectly fresh, replace repeated tiny queries with more efficient joins over sets, etc.


Many-to-many tag filter query with a junction table get slower with more tags. Can it be faster? Part 1

I have this query to find some videos containing all given tags. Here with tags 1,9,27,13,67 as an example.

SELECT *
FROM video v
WHERE v.id IN (
    SELECT tr1.vid as video_id FROM (SELECT video_id vid FROM tag_rel WHERE tag_id=1) tr1
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=9) tr2 USING (vid)
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=27) tr3 USING (vid)
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=13) tr4 USING (vid)
    INNER JOIN (SELECT video_id vid FROM tag_rel WHERE tag_id=67) tr5 USING (vid)
    -- more joins if more tags --
)
AND is_x=0
AND is_y=0
AND deleted IS NULL
LIMIT 0, 60

It executes in ~1.6 seconds.

If I run the inner select alone, it executes in ~0.8 seconds, and the result is 77 video ids.

Now, if I replace the inner select with these (example) video ids:

SELECT *
FROM video v
WHERE v.id IN (
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77
)
AND is_x=0
AND is_y=0
AND deleted IS NULL
LIMIT 0, 60

This query executes in ~0.06 seconds.

Question 1: Why is this the case, and why doesn't the full query run in ~0.86 seconds, which is the sum of the two operations (get video ids by tags and get videos by video ids)?

Part 2

For my application, I can't inject the ids because the inner select might return millions of ids. So I need them in one query, together with the support of checking is_x, is_y and deleted, and offsetting the limit for pagination.

I have spent months going back and forth, and this is the fastest query for most cases.

Question 2: Can this whole query be done faster/better with other techniques?

Question 3: By adding more tags, the query gets drastically slower, but isn't more joins just filtering down the number of possible results for the engine, so it should be faster with more tags?

Info

tag_rel contains about half a billion rows with about .5 million unique tag ids. The video table has around 30 million rows.

Full query explain:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table   | partitions | type   | possible_keys            | key     | key_len | ref                           | rows    | filtered | Extra       |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  1 | SIMPLE      | tag_rel | NULL       | ref    | tag_id,video_id          | tag_id  | 4       | const                         | 1847596 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | tag_rel | NULL       | eq_ref | tag_id,video_id          | tag_id  | 8       | const,db.tag_rel.video_id     |       1 |   100.00 | Using index |
|  1 | SIMPLE      | v       | NULL       | eq_ref | PRIMARY,is_x_2,deleted   | PRIMARY | 4       | db.tag_rel.video_id           |       1 |    50.00 | Using where |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Indexes:

tag_rel.tag_id      = unique(tag_id, video_id)
tag_rel.video_id    = unique(video_id, tag_id)
video.is_x_2        = (is_x, is_y, deleted)

Let's compare your two queries in part 1. Assume you have 100 videos and 10 tags evenly distributed. Every:

SELECT video_id vid FROM tag_rel WHERE tag_id=...

then produces 10 rows. If you join n of those the amount of rows that have to be examined is 10^n. In your example, n is 5 so 100000 rows. The result is then filtered down to whatever amount of videos that have all those tags, and compared with the videos that satisfy the other predicates. This comparison once again multiplies the numbers of rows that have to be compared.

For second query it is sufficient to examine 1000 rows given the assumptions above. This is of course greatly simplified but should give you an idea of the amount of extra work that has to be done in the first case.

What you want to do is to find the videos that have all tags, i.e.:

FORALL x:p(x)

FORALL is the universal quantifier (see for example: https://en.wikipedia.org/wiki/Quantifier_(logic)). Unfortenate, there is no operator in SQL that directly implements this. One way to overcome this is to translate it to an existential quantifier:

FORALL x:p(x) <=> NOT EXISTS x:NOT p(x)

This can be implemented in SQL, however most people find it more intuitive to count the distinct number of tags per movie and compare it to the length of tag list. Your query would then become:

SELECT video_id vid
FROM tag_rel
WHERE tag_id in (1,9,27,13,67)
GROUP BY video_id
HAVING COUNT(tag_id) = 5 -- 5 is the number of tags

Now we can simply join this back with videos:

SELECT v.* -- dont use * in your code
FROM video v
JOIN (
    SELECT video_id vid
    FROM tag_rel
    WHERE tag_id in (1,9,27,13,67)
    GROUP BY video_id
    HAVING COUNT(tag_id) = 5 -- 5 is the number of tags
) t
    ON t.video_id = v.video_id
WHERE is_x=0
  AND is_y=0
  AND deleted IS NULL;

The list of tags can easily be replaced by a select.

Apparently this question is not as efficient as the original, but I'll still add a possible optimization:

SELECT v.* -- dont use * in your code
FROM video v
JOIN (
    SELECT video_id vid
    FROM tag_rel
    WHERE tag_id in (1,9,27,13,67)
    GROUP BY video_id
    HAVING COUNT(tag_id) = 5 -- 5 is the number of tags
    LIMIT 0, 60
) t
    ON t.video_id = v.video_id
WHERE is_x=0
  AND is_y=0
  AND deleted IS NULL
LIMIT 0, 60;


Policies RE database triggers in well-designed applications? I've heard all kinds of horror stories around gremlins living in database triggers, and--worse--systems being brought down by the addition of a trigger that caused a chain of cascading ones.

I'm considering implementing a strict policy about the use of database triggers. For separation of concerns, the initial thought is to say:

  "Database triggers shall be used only for the purpose of capturing and maintaining the audit trail."

Does anyone have similar policies?
Are there any strong arguments against such a policy?

Sounds like we hold similar views on trigger usage but I'm very interested to hear the opposite opinions from other DBAs. Personally, I view them as a last resort and have only implemented triggers where there is no other option. The exception being INSTEAD OF triggers against views.

Triggers are often the only option when you're enhancing a 3rd party solution and the extension points are not exposed by the systems API. Where I have full access to source, I've not yet encountered a situation where a trigger was my first thought.

Audit is a good example. In a 3rd party application, triggers may be the only choice. If you have access to source, it is certainly not the only option, though it may be lowest risk/cheapest/easiest (delete as applicable) option. If the database API is well designed, there will be other entry points to add audit.

Despite my thoughts on triggers, I wouldn't impose the strict policy you are suggesting. In my opinion, the DBA vs Developer mentality that persists in many organisations arises from the way this type of guidance is perceived. Try to encourage consultation, rather than dictating.


Use NEW in FROM clause in Postgres trigger? I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.

SELECT
unnest(something)
FROM NEW

However, this seems to lead to an error:

  relation 'new' does not exist

How can I use the NEW row inside of the trigger function, in such a way that allows me to unnest an array field for further processing?

CREATE TABLE statements

Below are examples of the table structures:

CREATE TABLE parent_table (
    id uuid NOT NULL,
    jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],
    CONSTRAINT "parent_table_pkey" PRIMARY KEY (id),
);

CREATE TABLE many_to_one_table (
  id serial primary key,
  parent_table_id uuid references parent_table(id),
  subfield_a text,
  subfield_a text
);

TRIGGER function

Here is the essence of the TRIGGER function:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json() RETURNS TRIGGER AS
$body$
begin
    if NEW.jsonb_array_field is null then
        raise exception 'jsonb_array_field is null';
    else
        insert into
            many_to_one_table(subfield_a, subfield_b)
            select
                parent_table_id, -- this is to be the ForeignKey
                json_data->>'subfieldA' as subfield_a,
                json_data->>'subfieldB' as subfield_b
            from (
                select
                    id, -- need ID for ForeignKey relationship
                    unnest(jsonb_array_field) as json_data
                    from new
            ) as unnested_json;
    end if;
    RETURN new;
end;
$body$
LANGUAGE plpgsql;

TRIGGER statement

The trigger statement can be run either before or after INSERT and UPDATE, so long as the data are mirrored in the 'migration' table.

CREATE TRIGGER unnest_and_normalize_json_on_insert AFTER INSERT ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

CREATE TRIGGER unnest_and_normalize_json_on_update AFTER UPDATE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

ForeignKey clarification
We are trying to transition this part of our data model to use a ForeignKey relationship rather than JSON field. The trigger is intended as a temporary step to ensure the normalized_table has data going forward, while we backfill/migrate data from older records.

NEW and OLDin trigger functions are records - each only defined where applicable. (So no OLD in an INSERT trigger, for instance.) You can perform ROW operations on them. Reference nested columns with dot notation (like table-qualified column names) like they were tables included in the FROM list. NEW and OLD are visible almost everywhere in SQL DML statements in a trigger function. (Except dynamic SQL with EXECUTE.)

To treat NEW as actual table you'd have to transform it first - which is typically not necessary. Like: (SELECT NEW.*). For a meaningful use case see chapter 3. in this related answer:

PostgreSQL use NEW in query for INSTEAD OF trigger

Your basic trigger function burns down to:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO many_to_one_table (parent_table_id, subfield_a, subfield_b)
   SELECT NEW.id
        , ja->>'subfieldA' AS subfield_a  -- column alias only for documentation
        , ja->>'subfieldB'
   FROM   unnest(NEW.jsonb_array_field) ja;  -- produces a derived table

   RETURN NEW; -- optional for AFTER trigger
END
$func$  LANGUAGE plpgsql;

This was just noise:

if NEW.jsonb_array_field is null then ...

The column jsonb_array_field is defined as:

jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],

It's an AFTER trigger, the NOT NULL constraint would already have raised an exception for NULL in jsonb_array_field at this point.

You can merge your two triggers into one:

CREATE TRIGGER unnest_and_normalize_json_on_insert
AFTER INSERT OR UPDATE ON parent_table  -- !
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

That said, you'd have to do a lot more to cover UPDATE and DELETE properly. Similar to this one (chapter The dark side):

Can PostgreSQL have a uniqueness constraint on array elements?


Why does a query on a sparse index perform much faster than a query on a partial index in MongoDB? I created a sparse index in my MongoDB Atlas like so:

db.detection_events.createIndex({"emp_id": 1}, {sparse: true})

When I performed this operation:

db.detection_events.count({"emp_id": {$exists: true}})

I got the result extremely quickly. Upon readon the docs for sparse indeces, MongoDB recommends to use partial indeces instead. I created a partial index like so:

db.detection_events.createIndex({"emp_id": 1}, {partialFilterExpression: {emp_id: true}})

And the same query takes a long time to execute. I got the partial index code from here.

Why does the partial index have a much higher query time than the sparse one? Is it because I created the index incorrectly?

Turns out I created the partial index incorrectly.

If you want to speed up an $exists query, you need to add the partialFilterExpression option like so:

db.detection_events.createIndex({"thingToQueryOn": 1}, {partialFilterExpression: {thingToQueryOn: {$exists: true}}})

Note the lack of quotation marks in the second document


Creating new database and insert values in Oracle 10g xe hr account? I am new to Oracle 10g xe. When I log into hr account, how do I start creating new database and insert values? My case study is a five aside football league, where the organizer wants to know following questions:

alphabetical list of all the teams and their players.
how many time players have been awarded player of the match?
list different types of penalty points which can be awarded?
produce a list of all teams playing all other teams indicating who is at home and who is away.

and lot more.

How do I start creating a new database from scratch?

You can create a new user/schema using create user clause and then start creating database tables and other objects into this schema. I would not recommend you to exploit hr schema, I would keep that schema intact and use it for testing purposes.


SQL: What is slowing down INSERTs if not CPU or IO? We have a database for a product that is write-heavy. We just bought a new server machine with a SSD to help. To our surprise, the insertions were not faster than on our old machine with much slower storage. During benchmarking we noticed that the IO rate exhibited by the SQL Server process was very low.

For example, I ran the script found on this page, except that I added a BEGIN TRAN and COMMIT around the loop. At best I could see the disk usage reach 7Mb/s, while CPU barely touched 5%. The server has 64Gb installed and is using 10. The total run time was 2 minutes 15 seconds for the first call down to around 1 minute for subsequent calls. The database is on simple recovery and was idle during the test. I dropped the table between each call.

Why is such a simple script so slow? The hardware is barely being used at all. Both dedicated disk benchmarking tools and SQLIO indicate that the SSD performs correctly with speeds upward of 500Mb/s for both reading and writing. I understand that random writes are slower than sequential writes, but I would expect a simple insert like this, to a table without clustered indexing, to be much faster.

Ultimately our scenario is much more complex, but I feel that I need to understand a simple case first. In a nutshell our application deletes old data, then uses SqlBulkCopy to copy new data to staging tables, performs some filtering, and finally uses MERGE and/or INSERT INTO depending on cases to copy the data to the final tables.

--> EDIT 1: I followed the procedure linked by Martin Smith, and I got the following result:

[Wait Type]  [Wait Count] [Total Wait (ms)] [T. Resource Wait (ms)] [T. Signal Wait (ms)]
NETWORK_IO          5008              46735                 46587        148
LOGBUFFER           901               5994                  5977         17
PAGELATCH_UP        40                866                   865          1
SOS_SCHEDULER_YIELD 53279             219                   121          98
WRITELOG            5                 145                   145          0
PAGEIOLATCH_UP      4                 58                    58           0
LATCH_SH            5                 0                     0            0

I find it weird NETWORK_IO takes most of the time, considering there are no result to display and no data to transfer anywhere other than to the SQL files. Does the NETWORK_IO type includes all IO?

--> EDIT 2: I created a 20Gb RAM disk and mounted a database from there. The best time I had on the SSD is 48s, with the RAM disk it went down to 37 seconds. NETWORK_IO is still the biggest wait. The maximum write speed to the RAM disk was about 250Mb/s while it's able to do multi gigabytes per second. It still wasn't using much CPU, so what's holding up SQL?

I know it's an old Question but this might still help searchers and it's a problem that pops up every now and then.

The main reason why you are hitting a performance ceiling without you seeing any resource bottleneck is because you've reached the limit of what is possible to process within one session single thread. The loop isn't processed in parallel, but all inserts are done serially.

In my case, it takes 36 seconds to insert 3 million rows. That means 36/30000000 = 0.000012 seconds per row. That's pretty fast. On my system, it simply takes 0.000012 to go through all the steps that are necessary.

The only way to get it done faster is start up a second session in parallel.

If I start 2 sessions in parallel both doing 15 million inserts. Both of them finish in 18 seconds. I could scale out more, but my current test setup is hitting 95% cpu with two parallel session, so doing 3 would skew the results since I would hit a CPU bottleneck.

If I start 2 parallel session both inserting 3 million rows, they both finish in 39 seconds. so that is now 6 million rows in 39 seconds.

Okay, that still leaves us with the NETWORK_IO wait showing up.

The NETWORK_IO waits are added by the fact that you are using extended events to trace them. In my case the insert takes 36 seconds (on avg). When using the extended event way (from the link above in the very first comment) this is what is registered:

Wait Type             Wait Count  Total Wait Time (ms) Total Resource Wait Time (ms) Total Signal Wait Time (ms)
NETWORK_IO            3455        68808                68802                         6
PAGEIOLATCH_SH        3           64                   64                            0
PAGEIOLATCH_UP        12          58                   58                            0
WRITE_COMPLETION      8           15                   15                            0
WRITELOG              3           9                    9                             0
PAGELATCH_UP          2           4                    4                             0
SOS_SCHEDULER_YIELD   32277       1                    0                             1
IO_COMPLETION         8           0                    0                             0
LATCH_SH              3           0                    0                             0
LOGBUFFER             1           0                    0                             0

You can see that 68 seconds of NETWORK_IO is registered. But since the insert loop is a single threaded action that took 36 seconds, this can't be. (Yes, multiple threads are used, but the operations are serial, never in parallel, so you can't acummulate more wait time than the total duration of the query)

If I don't use extended events but just the wait stats DMVs on a quiet instance (with just me running the insert) I get this:

Wait Type                   Wait Count  Total Wait Time (ms)  Total Resource Wait Time (ms) Signal Resource Wait Time (ms)
SOS_SCHEDULER_YIELD             8873                 0.21                                    0.01                                    0.20
PAGEIOLATCH_UP                  3                    0.02                                    0.02                                    0.00
PREEMPTIVE_OS_AUTHENTICATIONOPS 17                   0.02                                    0.02                                    0.00
PAGEIOLATCH_SH                  1                    0.00                                    0.00                                    0.00

So the NETWORK_IO you were seeing in the extended events log, wasn't related to your insert loop. (If you wouldn't turn nocount on, you would have massive async network IO waits, +1 Martin)

However I don't know why the NETWORK_IO show up in the extended event trace. Sure the writing out to a async file target of the events accumulates ASYNC_NETWORK_IO, but surely this is all done on a differenent SPID then the one we are filtering on. I might ask this as a new question myself)


Save different account types in a database I have the following issue. In one application (imagine a messaging/e-mail app on your smartphone), the user enters its accounts and gets information from the server displayed. I need to store the accounts and there are different types of accounts. Something like this:

All account types: name of the account, account type, ...
Additional fields for account type A: user, password, URL
Additional fields for account type B: user, password, server specific info

I expect to have 1-5 accounts per user, so really small table(s). What I need to consider is that account types may be added/removed in the future, so I want to avoid referencing the account types or their specific fields in the main DB handler.

The best idea that I have right now is to store everything in the same table. This will contain the fields that are common to all account types and an additional text field with the remaining information (e.g. in JSON format).

What is the general/best way for solving the issue? Is my approach the correct one?

You can reasonably use both approaches. The majority of contemporary SQL databases (mySQL, PostgreSQL, Oracle, SQL Server) will recognize JSON as a data type, and will let you store it as such (that is: it is not just a string, but a string following a certain number of rules).

Things to consider to make your choice:

How the different parts of the data are accessed: If, whenever you need to check the main table, you also need to access the surrogate one (to get the remaining information, regardless of which one it might be), then it is a good idea to have all the information together. If most of the accesses to the main table don't need that remaining information, having it in several separate tables makes more sense.
Access permissions (privileges): If anyone having access to the main table doesn't need or shouldn't have access to the reamining information, then, it feels more natural (and is probably easier) to have it in two (or more) different tables, and deal with table permissions, instead of column permissions.
Programming language ease of handling the JSON data: You can access JSON data from JavaScript, Python, C#, Java, Ruby, PERL... and most (if not all) programming languages. But is not equally easy with all of them. So, consider if the programming language for your project handles JSON easily, or you prefer to get always atomic data (columns with ints, floats, texts, dates and times, ...) from database rows. If handling JSON is not straightforward, choose surrogate tables.
Modularity: if you plan to work with different modules (or classes, or ...) for different types of accounts, it may make sense that each module gets a user_id, and it takes care to check/update one table extra_user_info_X by itself (where X is the type of account). This approach would better match the modularity of the application design.

If your database allows for inheritance (like PostgreSQL or Oracle), that might also be a pattern/paradigm that fits your needs, and a complement to using a multi-table approach.


tempdb usage in SQL Server I have a SELECT INTO statement writing to a database which is in SIMPLE recovery mode, and is not tempdb. Will this use tempdb at all?

The SQL is something like

USE TGTDB
GO

SELECT * INTO DestinationTable FROM SRCDB.dbo.SourceTable
GO

Will this use tempdb at all? We are running into contention issues.

First, run your query and make a note of your SESSION_ID.

Then, run these two queries to see how much TempDB space your original query is using. Be sure to update them with your SESSION_ID.

-- TempDB session usage
select *
from sys.dm_db_session_space_usage
where session_id = 123
go

-- TempDB task usage
select *
from sys.dm_db_task_space_usage t
where session_id = 123
go

Also, run this query to show us the layout of your TempDB. Please post the results.

use tempdb
go
exec sp_helpfile
go


Using SQL Server 2012 Standard with Bitlocker We're trying to use Bitlocker to secure an SQL Server 2012 database. We don't have any issue getting bitlocker to work... the issue we have is that once the drive is locked, SQL Server can no longer read the data.

We'd love to use TDE, obviously, but since that's limited to the Enterprise edition, that's a no-go. We have TPM activated on the server. This is in a Windows Domain and we use Windows Authentication... but once the drive is encrypted, we cannot access the DB with anything.

What am I missing here?

You need to use the account that the SQL Server runs as to enable Bitlocker.


PostgreSQL Streaming Replication - Slave remains in recovery mode PostgesSQL 9.3.x

I cannot run some commands on slave because:
"ERROR:  recovery is in progress"

I set up streaming without archive. Initialized with pg_basebackup. Dataset has about 180GB .
This is what I get on slave when looking at relevant processes:

23378 ?        Ss     0:12 postgres: startup process   recovering 000000010000026000000083
23379 ?        Ss     0:02 postgres: checkpointer process
23380 ?        Ss     0:00 postgres: writer process
23382 ?        Ss     0:00 postgres: stats collector process
23383 ?        Ss     0:14 postgres: wal receiver process   streaming 260/837D8000

Byte lag (as queried on master) is between 0 and 2000 bytes.

recovery.conf content:

standby_mode = 'on'
primary_conninfo = 'host=SOME_IP port=5432 user=postgres sslmode=require'

What am I missing?

This is what "streaming replication" means.  You stay in recovery, so that you can stream changes from the master.

That means you cannot run things that change the database on the replica.


Finding interval overlap between two tables I have two tables that have top_depth, bottom_depth, lith_type.  One defines the different layers found in a borehole and the other define where samples are extracted in each of the layers.

I would like to find all samples that intersects the layer boundaries. Although, I was able to solve my problem, I was hoping there was a more elegant solution.  This is what I did:

CREATE TABLE lithology ( official_name TEXT, top_depth FLOAT, bottom_depth FLOAT, lith_type TEXT );
INSERT INTO lithology VALUES('B1', 0, 3,'SC');
INSERT INTO lithology VALUES('B1', 3, 11,'SS');
INSERT INTO lithology VALUES('B1', 11, 20,'STS');
INSERT INTO lithology VALUES('B1', 1, 21, 'SS');

CREATE TABLE sample_data ( official_name TEXT, sample_id TEXT, top_depth FLOAT, bottom_depth FLOAT, lith_type TEXT );
INSERT INTO sample_data VALUES('B1', 1, 1, 2,'SC');
INSERT INTO sample_data VALUES('B1', 2, 3, 4,'SS');
INSERT INTO sample_data VALUES('B1', 3, 6, 7,'SS');
INSERT INTO sample_data VALUES('B1', 4, 10.5, 11.5,'SS');
INSERT INTO sample_data VALUES('B1', 5, 14, 15,'SS');
INSERT INTO sample_data VALUES('B1', 6, 19, 20,'SS');

select
    L.official_name, S.sample_id,
    L.top_depth, L.bottom_depth, L.lith_type,
    S.top_depth, S.bottom_depth, S.lith_type
from
    lithology L, sample_data S
where
    L.official_name = S.official_name
and
    (S.bottom_depth > L.top_depth and L.top_depth > S.top_depth or
    S.bottom_depth > L.bottom_depth and L.bottom_depth > S.top_depth or
    S.top_depth < L.top_depth and S.bottom_depth > L.bottom_depth )
order by L.official_name,S.sample_id

This should show that sample_id=4 is the sample to check.

The simplest way to work on this is to follow Martin Fowler's (and many others as well) technique: http://c2.com/cgi/wiki?TestIfDateRangesOverlap

Note: SQL Server syntax

create table Layers (layer_top_depth int, layer_bottom_depth int, layer_lith_type int);
create table Samples (sample_top_depth int, sample_bottom_depth int, sample_lith_type int);
go
insert into layers  values(4, 7, 1) ;
insert into layers  values(6, 8, 2);
insert into layers  values(5, 6, 3);
insert into Samples values(6, 7, 1);
insert into Samples values(6, 7, 2);
insert into Samples values(3, 8, 3);
go
SELECT L.layer_top_depth, L.layer_bottom_depth, L.layer_lith_type,
       S.sample_top_depth, S.sample_bottom_depth, S.sample_lith_type
 FROM Layers L
   JOIN Samples S ON
       (L.layer_top_depth <= s.sample_bottom_depth AND
        S.sample_top_depth <= L.layer_bottom_depth);
go
drop table Layers;
drop table samples;

If the samples at boundary layer should not be reported, replace the comparison of <= with < as mentioned by ypercubeᵀᴹ and is shown below:

SELECT L.layer_top_depth, L.layer_bottom_depth, L.layer_lith_type,
       S.sample_top_depth, S.sample_bottom_depth, S.sample_lith_type
 FROM Layers L
   JOIN Samples S ON
       (L.layer_top_depth < s.sample_bottom_depth AND
        S.sample_top_depth < L.layer_bottom_depth);

The lyth_type is just riding along in this since you did not mention how to use it.


can I use select statement for ifnull function? I need to select a value from a table according to its date. If the record does not exists in the target table, I want to replace it with another value as defualt value instead of Null.
Can I do this with ifnull() function?

I have written this piece of code in a procedure, but I only get Null value for this field.

     CREATE PROCEDURE `test`(IN report_date DATE)
     BEGIN
        DECLARE initial_stock INT UNSIGNED;

        SET initial_stock = (select DISTINCT gs_fuel_type.fuel_initial_stock
                             from gs_fuel_type
                             WHERE gs_fuel_type.fuel_type_id = 2 );

        drop TEMPORARY table if EXISTS temp_daily_fuel;
        create TEMPORARY TABLE temp_daily_fuel (
           fuel_stock INT UNSIGNED DEFAULT 0
         );

        insert into  temp_daily_fuel (fuel_stock)
        select distinct
        (select ifnull(gs_fuel_stock.fuel_remainder , initial_stock)
            from gs_fuel_stock
            where   gs_fuel_stock.fuel_type_id = 2
                and gs_fuel_stock.shift_date = date_sub(report_date , INTERVAL 1 day)
        ) as fuel_stock

        FROM shifts_view
        where   shifts_view.shift_date = report_date
        AND shifts_view.fuel_type_id = 2
        GROUP BY  shifts_view.fuel_type_id;

     select  * from temp_daily_fuel ;

    END

Why { select ifnull(gs_fuel_stock.fuel_remainder , initial_stock) ....  } still returns null and doesn't replace the initial_stock value for this statement?

and I am quite sure that initial_stock value is not null.

The simplified Version of this code:

    CREATE PROCEDURE `test`(IN report_date DATE)
        BEGIN
           DECLARE i INT UNSIGNED;

           SET i = (select DISTINCT g.i from g WHERE g.id = 2 );

           drop TEMPORARY table if EXISTS t;
            create TEMPORARY TABLE t (
                f INT UNSIGNED DEFAULT 0
                );

           insert into  t (f)
                select distinct
            (select ifnull(gs.r , i) from gs
                where  gs.id = 2 and gs.date = date_sub(report_date , INTERVAL 1 day) ) as f
             FROM a
               where a.date = report_date AND a.id = 2
                  GROUP BY  a.id;

               select  * from t ;
            END

in this code, I want the value of "i" to be replaced instead of showing NULL.

Start with this simplification:

CREATE TEMPORARY TABLE t
INSERT INTO t
    SELECT ... -- Keep this; toss the other parts
SELECT FROM t

Then look around for other simplifications.

Read up on INSERT ... ON DUPLICATE KEY UPDATE ... -- it may be the ultimate simplification.

After all that, I may be able to see what you are asking about (IFNULL).


Is it possible that records are changed while a read query is still executing? Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records?

We didn't take any locks on the table (or to be read records).

I think it is obvious that reading single records (reading every record) is an atomic operation, but i am not sure about reading several (possibly millions) records. Is it an atomic operation? Do we need to take a read lock when reading multiple records (in one query) if it is possible that a write query be run in the meanwhile and we want a consistent snapshot of all of the records at the time the read query starts to be executed?

I am using MySQL.

Yes, it is possible and this may or may not be desirable. The database theory has a concept of isolation, which is about transaction visibility to other processes. MySql's documentation about set transaction has a discussion about product specific syntax.


Optimized date compare in WHERE clause, convert or datediff=0? In my query I am comparing two dates in the WHERE clause
once using CONVERT:
CONVERT(day,InsertedOn) = CONVERT(day,GETDATE())

and another using DATEDIFF:
DATEDIFF(day,InsertedOn,GETDATE()) = 0

Here are the execution plans
For 1st one using CONVERT

For 2nd one using DATEDIFF

The datatype of InsertedOn is datetime.
Which one is more optimized?

Instead, try

WHERE InsertedOn>=CAST(GETDATE() AS date) AND
      InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date))

This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the query are included in the index. If you have lots of columns in the query, using the table's clustered index is probably best. Read up on covering indexes.


SQL Server 2019 using Linked server to Oracle 19c (19.9) using Oracle 19c client on Windows - DBCOLUMNFLAGS_ISNULLABLE error Having issues selected data for some Oracle tables over the linked server from SQL Server:
select type, count(*) from RMTDB..JOB1308.HOST_INTERFACE group by type order by 1 desc;

Msg 7356, Level 16, State 1, Line 13
The OLE DB provider "OraOLEDB.Oracle" for linked server "RMTDB" supplied inconsistent metadata for a column. The column "CREATE_TIME" (compile-time ordinal 3) of object ""JOB1308"."HOST_INTERFACE"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.

Open query version of it works:
select * from OPENQUERY( RMTDB, 'select type, count(*) from HOST_INTERFACE group by type order by 1 desc')

result
TYPE      COUNT(*)
10          10450
8            6543
6            21

Anyone run into this?

I found a support.oracle.com Doc ID 1519958.1 that describes the issue.
For solution it says: Use the relatively simple work around of linking to a view that is based upon the table, instead of directly to the table.
Apparently this issue has been around since 2012.


Okay to merge included columns on otherwise identical indexes? I have two indexes that index the same field on the same table. The only difference between them is the included columns.

There's no reason I shouldn't combine the included columns and just have one index, right?

Yes, you should merge these indexes and only keep one,  as long as you verify that:

Neither of them are marked as UNIQUE (in which case, preserve that one)
Neither of them have a filter condition
Neither of them are expressly referenced in an index hint


Add host to existing user @ Mysql with the same permission I would like to add another host for existing users at my DB. I want this new record to have a same privileges with the existing user as well. I saw this thread https://stackoverflow.com/questions/19281305/adding-new-host-entries-to-existing-mysql-users but it seems it does not copy the whole privileges that the existing users have. Any programmatic solution instead of manually adding the user one by one?

If you can install or already have the MySQL Utilities, then you can use the mysqluserclone script.

Reference: 5.28 mysqluserclone — Clone Existing User to Create New User

The command is then a simple:

shell> mysqluserclone --source=root@localhost --destination=root@localhost \
      source_user@localhost target_user:secret1@192.168.1.1

In the above example you are connecting to root@localhost (as the source) and root@localhost (as the target) and are then copying the permissions from source_user@localhost to target_user@192.168.1.1.

So you could use:

shell> mysqluserclone --source=root@localhost --destination=root@localhost \
      Reynaldi@localhost Reynaldi:secret1@192.168.1.1

Further details can be found in the rather limited documentation.


How do I skip an offline database over the network in MySQL import? I am doing a MySQL import from an *.sql file to multiple database location over the network. I'm using cmd batch file to run the command and it's working as I expect it to be. However I notice that the import took longer times on any IP that is offline compared to the ones that is online. Below are the duration it took for both processes:

If online : 7-8 secs average/process
If offline : 40 secs average/process

Each of the process is performing two command, one is to import an *.sql file and the other is referring a *.txt file to update a table in the same location I imported the *.sql file.

Command 1:
mysql.exe -h 192.xxx.xxx.xxx -P 3306 -u root -pxxxx db < C:\xxx\data\xxx.sql

Command 2:
mysql.exe -h 192.xxx.xxx.xxx -P 3306 -u root -pxxxx db < update.txt

These commands is repeated about 20 times in one batch file and the possibility of getting an offline database is high. If I'm lucky, I'll get only one or two offline location but to get all database online at once is highly unlikely. The *.sql file is just about 45MB in size so import is actually quick.

My question is, how do I skip the offline location?

With some suggestion from Akina, I'm able to find one post in SO about how to determine if an IP is online or not through the network before executing the MySQL import command. Here is the link to the answer : https://stackoverflow.com/a/21252613/10910692

Below is the code I'm using in my batch file:

ping -n 1 192.xxx.xxx.xx | find "TTL=" >nul
if errorlevel 1 (
    echo host is offline..
    ) else (
    echo Updating host...
    mysql.exe -h 192.xxx.xxx.xx -P -u -p inventory < C:\xxx\data\xxx.sql
)

I also took Akina's suggestion to concatenate both the import and update query into one file and only need to call that one file for the update. As I mention in my question, the code above is repeated for about 20 times.

If I stick to my previous method and assuming that all IP is offline, it will take about 800 secs = 13-14 mins for the batch file to finish processing while with the method above, it reduces to 1-2 mins only! (based on assumption that each command took 5 secs max while in reality its less than 5 secs per command).


dm_db_index_operational_stats showing more blocking than extended event report I’ve implemented the XE to capture blocking on my instance (as per this great article https://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/#comment-2470072. )

I did this because I kept an eye on the blocking states, using this beauty of a query:

select
   db_name(database_id) DB,
   object_name(object_id) Obj,
   row_lock_count + page_lock_count No_Of_Locks,
   row_lock_wait_count + page_lock_wait_count No_Of_Blocks,
   row_lock_wait_in_ms + page_lock_wait_in_ms Block_Wait_Time_in_ms,
   index_id
from
   sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
order by
   Block_Wait_Time_in_ms desc

Occasionally some blocking gets logged to the file, but not as many as I would expect. My expectation being that the number in No_Of_blocks from the query, would correspond with the number of blocks logged to the file as per the XE.

Is there anything the XE might not capture versus the query? Any ideas? Thanks for your feedback!

You are comparing totally different things which are not comparable.

XE trace you are capturing an event called sqlserver.blocked_process_report which is described here.

Here you are capturing all kind of blocking exceeding a fixed amount of time.  If it is less than that duration (set up by sp_configure 'blocked process threshold') it will not be captured by this event.

  The Blocked Process Report event class indicates that a task has been
  blocked for more than a specified amount of time. This event class
  does not include system tasks or tasks that are waiting on non
  deadlock-detectable resources.

Your second collection using dm_db_index_operational_stats only counting waits for row and page lock. It is also cumulative since metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache.

Details here: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql

row_lock_wait_count: Cumulative number of times the Database Engine
waited on a row lock.
page_lock_wait_count: Cumulative number of times the Database
Engine waited on a page lock.


MySQL replication working fine, but still shows "Waiting for master to send event" at 280000 seconds To repeat the title: MySQL replication working fine, but mytop still shows a thread "Waiting for master to send event". It has been like this for a few days, and is now at 280000 seconds. But replication is just fine.

I can see another thread which is doing the replication work, right now it is sitting on "Slave has read all relay log; waiting for the slave I/O thread to update it" but it has been running queries, and I can verify that the slave is up to date just by viewing the data.

Can I kill this process?

I recently had to re-set up replication, and that involved creating a new replication user, so I'm not sure if this is what has caused this "ghost replication process".

Don't you dare kill that process. Why ?

Run this query

SELECT COUNT(1) FROM information_schema.processlist WHERE user='system user';

The answer has to be two. Why ?

Standard MySQL Replication functions on 2 threads: 1) IO Thread, 2) SQL Thread. In my old post Does MySQL support replicating all databases?, I explain the symbiosis between the two threads.

When you run SHOW SLAVE STATUS\G, you will see three lines:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

This is the sign of a healthy replication process.If either Slave_IO_Running or Slave_IO_Running is No, then Replication is not running for one of six(6)reasons:

Somebody ran STOP SLAVE;, which turns off both the IO Thread and SQL Thread
Somebody ran STOP SLAVE IO_THREAD;, which turns off the IO Thread
Somebody ran STOP SLAVE SQL_THREAD;, which turns off the SQL Thread
There was a replication user authentication error, which turns off the IO Thread
There was a network error, which turns off the IO Thread
There was an SQL Error, which turns off the SQL Thread

When any of these cases show up, Seconds_Behind_Master is NULL.
What about the number 280000 ? That is simply the length of time (over 82 hours) that the IO Thread has been running. That's OK. It is not a ghost process. Replication requires an IO Thread to read binlog events from a Master's binary logs.

In conclusion, your IO Thread has been up for over 82 hours. Do not mess with replication.