Migrate on-premise SQL Server databases to Azure SQL Database We're in the process of a server migration from an on-premise server (Win2008R2) to Azure PaaS.

To move the databases, we used the Microsoft Data Migration Assistant (DMA) tool, which worked great and we can connect to the migrated Azure DB via SQL Server Management Studio.

Considering:

Made quite a few changes to the migrated Azure database (tables, SPs, indexes) to work with the apps in Azure
Combined multiple DBs from the on-prem server into one DB in Azure via DMA to save costs
One on-premise database is continually being modified by insert and update operations (multiple tables) during the migration process
When fully switching hosting in Azure we need to make sure all data is up-to-date. This will be done very early in the AM during which on-prem IIS websites are stopped assuring no DB operations are made, giving us a short window to migrate missing data

Question: What is the best and fastest way to migrate data (all vs missing/updated) considering the above?

Can this be achieved by following the below steps?

Create a new empty Azure SQL database (to later hold all the tables of 5 on-prem databases)
Via Microsoft Data Migration Assistant (DMA), migrate only the schema of that one on-prem database which is continually being modified by insert/update operations
Via SQL Data Sync Agent, set up auto data sync (e.g. every 30 minutes) between the on-prem and Azure database
After data sync up and running, via Microsoft Data Migration Assistant (DMA)- migrate schema and data of the rest of the on-prem databases (no data changes during migration process) into the single Azure database previously created and used for syncing

EDIT:

After enabling Data Sync in the Azure portal, the sync was unsuccessful due to the below logged errors:

Member Database: MyAzureDb -> Tombstone cleanup failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed.
Member Database: MyAzureDb -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:18456, Message: Login failed.

To fix 1 and 2 above tried: ALTER ROLE DataSync_executor ADD MEMBER [myUser]; but got Cannot add the principal 'myUser', because it does not exist or you do not have permission.

Member Database:MyOnPremDb: -> Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:325, Message: Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. SqlError Number:156, Message: Incorrect syntax near the keyword 'AS'.

The current compatibility level of the on-premise database is 80. Not sure what impact setting it to 100 will have to the production databse/apps.

I would recommend you to migrate first only the schema of your on-premises databases to Azure SQL Databases and then let Azure SQL Data Sync to migrate the data to Azure and keep it updated on Azure SQL Database.

My suggestion to start with an empty schema on the Azure SQL Database side is because when SQL data Sync finds data on-premises and on Azure it start comparing both databases and that consumes a lot of resources.

On the initial sync SQL Data Sync may consume a lot of resources on the on-premises database server even when having an empty schema on the Azure side, for that you can use SQL Server Resource Governor to cap the CPU used by the data sync sessions in your on premises SQL Server, and this way avoid big performance impact possibly affecting database users.

When you are ready, you can switch your users (gradually or not if SQL Data Sync is on bi-directional mode) to Azure. Once your users have been migrated, you can then remove the member database (the on-premises database) from the SQL Data Sync configuration and stop SQL Data Sync operation.


Optimizing SELECT query consistently showing up in slow-log In a app called 'Links', users post links and photos of interesting content they've discovered recently (and others comment on the said postings).

These posted comments under the photos are saved in a links_photocomment table in my postgresql 9.6.5 DB.

One SELECT query on the links_photocomment table is consistently showing up in slow_log. It's taking longer than 500ms, and is ~10X slower than what I'm experiencing in most other postgresql operations.

Here's an example of the corresponding SQL from my slow log:

LOG:  duration: 5071.112 ms  statement:

SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM   "links_photocomment"
       INNER JOIN "auth_user"
               ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_photocomment"."which_photo_id" = 3115087
ORDER  BY "links_photocomment"."id" DESC
LIMIT  25;

See the the explain analyze results: https://explain.depesz.com/s/UuCk

The query ends up filtering 19,100,179 rows according to that!

What I've tried:

My gut feel is that Postgres bases this query plan on misleading statistics. Thus, I've run VACUUM ANALYZE on the said table. However this hasn't changed anything.

Being an accidental DBA of sorts, I'm looking for some quick expert guidance on the subject. Thanks in advance and apologies for the noob question (if it is).

Addendum:

Here's the entire output for \d links_photocomment:

                                      Table "public.links_photocomment"
     Column      |           Type           |                            Modifiers
-----------------+--------------------------+-----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_photocomment_id_seq'::regclass)
 which_photo_id  | integer                  | not null
 text            | text                     | not null
 device          | character varying(10)    | not null
 submitted_by_id | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 image_comment   | character varying(100)   | not null
 has_image       | boolean                  | not null
 abuse           | boolean                  | default false
Indexes:
    "links_photocomment_pkey" PRIMARY KEY, btree (id)
    "links_photocomment_submitted_by_id" btree (submitted_by_id)
    "links_photocomment_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photocomment_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photo" CONSTRAINT "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photocomment_id_fkey" FOREIGN KEY (which_photocomment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo" CONSTRAINT "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED

The plan is not using the index on (which_photo_id) but the PK (id) index instead, so it has to read a very large part of the index (and all of it if there are less than 25 rows that match the filter). This takes about 4.4 seconds in the specific execution (and finds the 25 rows after reading and rejecting 19M rows):

-> Index Scan Backward using links_photocomment_pkey on links_photocomment
   (cost=0.57..2,819,246.22 rows=7,195 width=41)
   (actual time=555.830..4,929.154 rows=25 loops=1)

   Filter: (which_photo_id = 3115087)
   Rows Removed by Filter: 19100179

I would try these:

replacing the index on (which_photo_id) with an index on (which_photo_id, id).

rewrite the INNER join to a LEFT join (there is a FOREIGN KEY constraint that ensures that the two queries will produce the same results.)

rewrite with a subquery (derived table or CTE) moving the WHERE filter inside) in order to first get the 25 ids (hopefully with an index only scan) and then join the other 2 tables.

Query (with derived table):
SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       ) AS lim
       INNER JOIN "links_photocomment"
           ON ( "links_photocomment"."id" = lim.id )
       LEFT OUTER JOIN "auth_user"
           ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
           ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;

Query (with CTE):
WITH lim AS
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       )
SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM
       lim
       INNER JOIN "links_photocomment"
           ON ( "links_photocomment"."id" = lim.id )
       LEFT OUTER JOIN "auth_user"
           ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
           ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;


Simple update takes forever On SQL Server I want to update a 0/1 interger value for a specific set of records. For some reason this takes very long (let it run for 2 hours, still not finished). I can't figure out what is wrong or what to tweak.

The query:

 update workorder set INCTASKSINSCHED=0 where siteid = 'RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;

There are 282320 workorder records, only 103565 are for the RAILINFRA site. So it should be very, very fast.

In SQL Management studio I have this as an estimated execution plan. You can see a huge cost at the workorder_ndx1 index. I'm not sure why that is...

I tried to count the number of records, this works very fast:

SELECT COUNT(*) FROM  workorder WHERE   siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;

1 row: 5430
Execution time 0sec

The select statement took just under 1 sec, and has a different execution plan:

SELECT * FROM  workorder WHERE   siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;

Gives the 5430 rows

This is the information of the indices used:

workorder_ndx1 (clustered)

SITEID
WONUM

workorder_ndx24 (non-clustered)

HISTORYFLAG
ISTASK
WONUM

There is only one index with the to be updated field INCTASKINSCHED

workorder_ndx38 (non-clustered)

WONUM
INCTASKINSCHED
SITEID

I do have the XML's of the executionplans (slqplan-files), let me know if you need more information from them.

I suspect there is somekind of a loop that it uses an index that needs updating, but not sure why that should be the workorder_ndx1 index, because it doesn't include the INCTASKINSCHED field.

It turns out there was a trigger on the samen table that also updates it, that forces an update for the index used. It works near instant with the trigger disabled.


Why selecting rowversion using outer join never returns null Using SQL Server 2014.

Given table with rowversion column, other table to join with and a select like this:

 CREATE TABLE dbo.FooTable(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RowVersion] [rowversion] NULL,
 CONSTRAINT [PK_FooTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
))

 CREATE TABLE dbo.BarTable(
    [Id] [int] IDENTITY(1,1) NOT NULL
 CONSTRAINT [PK_BarTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
))

Insert into BarTable default values
GO

SELECT *
  FROM FooTable ft
  FULL OUTER JOIN BarTable bt on ft.Id = Bt.Id

Result of select is:

Id  RowVersion  Id
NULL    0x      1

Where there was no match with BarTable the RowVersion column is not null, but 0x.

This confuses Dapper, which we use to deserialize result on app server, into thinking, it should construct an object for FooTable part of row - an invalid one, containing just an empty byte array.

Is there any reason for this rather unexpected behaviour?

Are there any smarter solutions than translating the value back to null using CASE in the sql statement or dealing with it in application code?

This appears to be a bug in .NET's SqlClient.  The column is being returned as an empty byte[] even though it's marked as null in the result.

EG

using System;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;

namespace ConsoleApp14
{
    class Program
    {

        static void Main(string[] args)
        {

            using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select cast(null as rowversion) rv";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();
                    var allowDbNull = rdr.GetColumnSchema()[0].AllowDBNull;
                    var isNull = rdr.IsDBNull(0);
                    var val = rdr[0];

                    Console.WriteLine($"SqlClient: AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");

                }
            }

            using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select @val = cast(null as rowversion) ";

                var p = cmd.Parameters.Add(new SqlParameter("@val", System.Data.SqlDbType.Timestamp));
                p.Direction = System.Data.ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                {

                   SqlBinary val = (SqlBinary) p.SqlValue;
                   Console.WriteLine($"SqlClient (parameter): IsDbNull: {val.IsNull} {val.GetType().Name} {val}");

                }
            }

            using (var con = new OdbcConnection("Driver={ODBC Driver 17 for SQL Server};Server=localhost;Trusted_Connection=yes"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select cast(null as rowversion) rv";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();
                    var allowDbNull = rdr.GetSchemaTable().Rows[0]["AllowDBNull"];
                    var isNull = rdr.IsDBNull(0);
                    var val = rdr[0];

                    Console.WriteLine($"ODBC:      AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");

                }

            }

        }
    }
}

Outputs

SqlClient: AllowDbNull True IsDbNull: False Byte[] System.Byte[]
SqlClient (parameter): IsDbNull: True SqlBinary Null
ODBC:      AllowDbNull True IsDbNull: True DBNull

I've opened an issue at https://github.com/dotnet/SqlClient/issues/255  But it's likely to be closed as WontFix.  According to notes in the source

// Dev10 Bug #479607 - this should have been the same as SqlDbType.Binary, but it's a rejected breaking change

The issue has been raised, but not fixed as a breaking change.  It may get fixed in .NET Core, which is full of breaking changes anyway, and left as-is in .NET Framework.


Best practice for a Table with private columns I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ideas.

Extra field for each field

My first idea was to just add a boolean to each fiend to mark if the previos column was public.

---------------------------------------------------------------------------------------
| user_id | user_data_1 | user_data_public_1 | user_data_2 | user_data_public_2 | ... |
|---------|-------------|--------------------|-------------|--------------------|-----|
| int     | string      | boolean            | int         | boolean            | ... |
---------------------------------------------------------------------------------------

In this case I have to use IF(user_data_public_1, user_data_1, NULL) on the SELECT query.

Related table to mark public fields

Another idea was to have the table with the fields and a related table with to mark the public fields:

---------------------------------------
| user_id | user_data_1 | user_data_2 |
|---------|-------------|-------------|
| int     | string      | string      |
---------------------------------------

---------------------------------
| user_id | filed_name | public |
|---------|------------|--------|
| int     | string     | string |
---------------------------------

In this case I'd need to do the JOIN and check if the field is public on the related table.

I don't like so much any of the solutions, so is there some elegant and hopefully efficient solution to solve this type of design.

Thanks in advance.

A slight variation on Rolandos answer.  Rather than a TEXT column, you could use an INT column as a mask value.  Using a bitwise OR to test the mask would give you way to determine if the field is shown.

select id,
    case when 1 | mask = mask then col1 end col1,
    case when 2 | mask = mask then col2 end col2,
    case when 4 | mask = mask then col3 end col3,
    case when 8 | mask = mask then col4 end col4,
    ...
from table

So if you want to show columns 1 and 3 then the mask would be 5.
Editing the mask is just a matter of adding and subtracting the appropriate values.
For example if you want to make column 3 public for a user

update table
set mask = mask + 4
where id = 1
    and 4 | mask <> mask -- Make sure it isn't already set

Edit By @RolandoMySQLDBA

Perhaps to apply IF function to your idea

SELECT
    IF(POWER(2,0) | mask = mask,fld1,'Private') fld1,
    IF(POWER(2,1) | mask = mask,fld2,'Private') fld2,
    IF(POWER(2,2) | mask = mask,fld3,'Private') fld3,
    IF(POWER(2,3) | mask = mask,fld4,'Private') fld4,
    ...
    IF(POWER(2,n) | mask = mask,fldn,'Private') fldn
FROM user_table;

Maybe, this might work too

SET @xp = -1;
SELECT
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld1,'Private') fld1,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld2,'Private') fld2,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld3,'Private') fld3,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld4,'Private') fld4,
    ...
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fldn,'Private') fldn
FROM user_table;

so you don't have to hardcode the powers of 2,

This would only work for n <= 32. Don't know what would happen if there would be 33+ columns.


How should I interpret the "lock time" within mysql slow query log? I'm trying to understand how to best interpret the lock time for the queries which show up on our MySQL slow query log.

For instance if an UPDATE query has 10 second lock time. I presume it is the total time after the update query acquires the lock. Even if it is waiting for previous select queries to complete but not executing the UPDATE action itself, the clock should be ticking since it is locking all the SELECT queries which have lined up after the UPDATE query.

And how about the SELECT query locks. How come some select queries have lock times? Is it because there is an UPDATE query following up hence they are locking a table together.

The lock_time in the slow query log is actually the amount of time the query spent waiting to acquire the lock it needs to run. For example, UPDATES queries need a write lock.

The locking also depends on the storage engine you are using in the table. When writing, InnoDB will use row-level locking and only lock the rows that are being changed. MyISAM will lock the entire table until the update/insert/delete is complete.

The Locking of the entire table for MyISAM is a big reason SELECT queries will have a lock_time in your slow query log.


Unable to install 64-bit Oracle 10g Release 2 ODAC 10.2.0.3 for Windows x64 on 64 bit Windows Server 2008 R2 I need to connect an application to Oracle. In the installation guide for that application it says that it requires 64 bit version of Oracle Data Access Component,more specifically 10.2.0.3 . I have tried installing it but I am continuously getting the error attached in the image when ever I try running the installer. The crash details are given in the image attached. Has some one ever tried installing it? If yes then would it be possible to give some details or point me in the right direction so that I can complete this installation.

Thanks

I think That version of ODAC is not certified on Windows 2008 R2. Please use the regular Oracle DB Client that supports Windows 2008 R2. If you need to use ODP.NET 10.2, then go here:
http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html

It can be very helpful to You.


Optimize extremely slow SQL query SELECT j.job_id,j.last_updated,j.job_title,j.status,
(SELECT COUNT(*) FROM candidates WHERE job_id=j.job_id) resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=1) auto_resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=0) self_resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=-1) paging_resumes_count,
(SELECT SUM(views) FROM job_views WHERE job_id=j.job_id) job_views,
(SELECT posts FROM autopost_jobs WHERE job_id=j.job_id) autopost_count
FROM jobs j WHERE j.user_id={$userId} ORDER BY j.last_updated DESC,STATUS

Table structure:

| jobs  | CREATE TABLE `jobs` (
  `job_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(20) NOT NULL,
  `job_title` varchar(450) NOT NULL,
  `job_desc` varchar(5000) NOT NULL,
  `sjob_data` text NOT NULL,
  `job_requirements` varchar(5000) DEFAULT NULL,
  `years_of_experience` varchar(100) NOT NULL,
  `location_id` int(11) unsigned NOT NULL,
  `department_id` int(11) NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  `job_company_name_alt` varchar(100) DEFAULT NULL,
  `is_show_company` tinyint(1) NOT NULL DEFAULT '1',
  `status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0=inactive,1=active,2=deleted',
  `job_reward` varchar(128) DEFAULT NULL,
  `job_lang` int(11) NOT NULL DEFAULT '0' COMMENT '0 - Ru 1 - En',
  `job_photo` varchar(100) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `is_hot_job` tinyint(1) NOT NULL DEFAULT '0',
  `job_internal_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1794 DEFAULT CHARSET=utf8 |

| candidates | CREATE TABLE `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `hr_id` int(11) DEFAULT NULL,
  `job_id` int(10) unsigned NOT NULL DEFAULT '0',
  `through_user_id` int(11) NOT NULL,
  `ref_type` tinyint(4) NOT NULL COMMENT '0 = personal , 1 = automatic , 2 = file uploaded , 3 = lead sent',
  `media_id` tinyint(2) NOT NULL DEFAULT '-1',
  `status_id` int(11) DEFAULT NULL,
  `cv_filename` varchar(250) DEFAULT NULL,
  `lead_url` varchar(256) DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `is_viewed` tinyint(1) NOT NULL DEFAULT '0',
  `is_hired` tinyint(1) NOT NULL DEFAULT '0',
  `is_employee_note` tinyint(4) NOT NULL DEFAULT '0',
  `note` varchar(1000) NOT NULL,
  `last_updated` datetime NOT NULL,
  `device_type` int(1) NOT NULL DEFAULT '0' COMMENT '0 - web, 1 - mobile',
  `third_party_approval` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `setIndex` (`user_id`,`job_id`,`through_user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=20012 DEFAULT CHARSET=utf8 |
| autopost_jobs | CREATE TABLE `autopost_jobs` (
  `job_id` int(11) NOT NULL,
  `hr_id` int(11) NOT NULL,
  `posts` bigint(20) DEFAULT '0',
  `media_id` int(11) NOT NULL,
  `last_user_id` int(11) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `inserted_date` datetime DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
| job_views | CREATE TABLE `job_views` (
  `user_id` int(11) NOT NULL,
  `hr_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `media_id` int(11) NOT NULL,
  `post_type_id` int(11) NOT NULL COMMENT '0 - self, 1 - auto, -1 - undefined ',
  `date` date NOT NULL,
  `views` bigint(20) NOT NULL DEFAULT '0',
  `last_time` time NOT NULL,
  PRIMARY KEY (`user_id`,`hr_id`,`job_id`,`media_id`,`date`,`post_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

This query just hangs, what could be the reason?, how can I optimize it

Indexes to add:

Table: candidates
  (job_id, ref_type)

Table: job_views
  (job_id, views)

Table: autopost_jobs
  (job_id, posts)

Then run again the queries and post the updated EXPLAIN


How to list all the inherited tables from the parent table? I have 3 tables (janitor, security & manager) which inherited from the user table.

What is the query can be used to produce the following result(list all the name of the child tables)?

Thank you. Cheer :)

Try this:

SELECT
table_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE table_schema = 'database_name'
AND referenced_table_name = 'user';

This will list of referenced tables under table 'user'.


Clone a database from SQL Server 2019 to 2017 I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says:

System.Data.SqlClient.SqlError: The database was backed up on a server
running version 15.00.4083. That version is incompatible with this
server, which is running version 14.00.2037. Either restore the
database on a server that supports the backup, or use a backup that is
compatible with this server. (Microsoft.SqlServer.SmoExtended)

Is there a "compatibility" backup mode on the 14-15 Sql Server versions?
local (on docker, Linux):

Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2
(X64)   Nov  2 2020 18:35:09   Copyright (C) 2019 Microsoft
Corporation  Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS)

distant (on Windows Server)

Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64)
Nov  2 2020 19:19:59   Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0
(Build 17763: ) (Hypervisor)

Just succeeded to clone the Database, using on local: In the SSMS, right click on the database to export, then Tasks => Export Data-tier Application it creates a bacpac file.

Copy the backpac to the distant server, and there in SSMS, right click on the "Databases" folder and then Import Data-tier Application, use the copied backpac file.
It copies the schema and the data as well...


Confusing Query: Mixing Aggregates and Non-Aggregates Hope someone can clarify how to do this query from SQL Server 2012, or maybe what it is asking;
it seems I am being asked to do a query that combines aggregates with non-aggregates, leading to what seems like an impossible query, since one can only group by fields that appear in the Select clause, which messes up
the query.

I am asked to do a query on the table Invoices:

Invoices (InvoiceID, VendorID,InvoiceDate, InvoiceTotal,...)

I am asked to do a query that produces 6 columns:
3  columns already appear "as is" in the table: VendorID, InvoiceDate, InvoiceTotal

And 3 other columns are aggregates:

4) Sum(InvoiceTotal)  AS VendorTotal: Sum of invoices for each VendorID

5) Count(InvoiceID) AS VendorCount: Count of Invoices for each VendorId

6) Avg(InvoiceTotal) AS VendorAvg: Average of Invoices per VendorId

Now, problem is that one cannot combine in the same query (with exceptions I cannot use here) aggregates and non-aggregates unless one groups by the non-
aggregates. So I can do:

Select
    VendorId, InvoiceDate, InvoiceTotal,
    Sum(InvoiceTotal) AS VendorTotal,
    Count(InvoiceId) AS VendorCount,
    Avg(InvoiceTotal) AS VendorAvg
GROUP BY
    VendorID

Now, I have no trouble if I only group by VendorID. But in order to get a
valid query, if I select either InvoiceDate or InvoiceTotal -- both non-aggregates -- I must group by each of them in order to get a valid query.

But grouping by these last two basically undoes the other aggregates: If
I group by invoice date, I lose the aggregation by Vendor, since each vendor
has different invoice dates.

I have tried doing self joins, using Invoices AS I1 join Invoices AS I2
and doing aggregates in I1 and non-aggregates on I2, but this does not seem to work.

Am I missing something obvious here? Any Ideas?

Edit: The answer was found to be using OVER (PARTITION BY VendorId) , like so:

Select
VendorId, InvoiceDate, InvoiceTotal,
    Sum(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorTotal,
    Count(InvoiceId) OVER (PARTITION BY VendorId) AS VendorCount,
    Avg(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorAvg
GROUP BY
    VendorID

The solution is what you already found, in DBMS like SQL Server 2005+ that have implemented window functions, we can use them to get aggregates over each partition while keeping the original table, so not collapsing the table as a group by does. I'm not writing the query here, please edit your answer.

In older versions (or other DBMS) that don't have the OVER () clause syntax available, we could use GROUP BY in a derived table (or a CTE) and then a "self" join back to the original table, like this:

SELECT
    i.VendorID, i.InvoiceDate, i.InvoiceTotal,
    grp.VendorTotal,
    grp.VendorCount,
    grp.VendorAvg
FROM
      dbo.Invoices AS i             -- our original table
  JOIN
      ( SELECT
            VendorID,
            Sum(InvoiceTotal) AS VendorTotal,
            Count(InvoiceId) AS VendorCount,
            Avg(InvoiceTotal) AS VendorAvg
        FROM
            dbo.Invoices
        GROUP BY
            VendorID
      ) AS grp                      -- the aggregated table
  ON
      grp.VendorID = i.VendorID ;


Replication with Repmgr + native streaming replication I have an environment where the old DBA used the repmgr. And now I need to add a second replica to use as a "dump server".

But my question is regarding using the native mode of PostgreSQL via stream replication. When I perform base_backup and a second replica is added, can it cause any side effects on repmgr?

I've never used repmgr in prod and I just want to make sure that a simple replica can ruin the whole cluster.

As per my experience, NO. it does not affect on repmgr.

i never tested on Production server. So please be careful when applying on Prodn server.


How to improve estimate of 1 row in a View constrained by DateAdd() against an index Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

Given a table and index:

create table [User].[Session]
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)

Actual Rows for each of the following queries is 3.1M, the estimated rows are shown as comments.

When these queries feed another query in a View, the optimizer chooses a loop join because of the 1 row estimates.  How to improve the estimate at this ground level to avoid overriding the parent query join hint or resorting to an SP?

Using a hardcoded date works great:

 select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc > '04/08/2015'  -- but hardcoded

These equivalent queries are view compatible but all estimate 1 row:

select distinct SessionId from [User].Session -- 1
 where CreatedUtc > dateadd(day, -365, sysutcdatetime())

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) > sysutcdatetime();

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

Try some hints (but N/A to View):

 select distinct SessionId from [User].Session -- 1
  where CreatedUtc > dateadd(day, -365, sysutcdatetime())
 option (recompile);

select distinct SessionId from [User].Session  -- 1
 where CreatedUtc > (select dateadd(day, -365, sysutcdatetime()))
 option (recompile, optimize for unknown);

select distinct SessionId                     -- 1
  from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 inner loop join [User].Session s
    on s.CreatedUtc > d.MinCreatedUtc
option (recompile);

Try using Parameter/Hints (but N/A to View):

declare
    @minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate;

select distinct SessionId from [User].Session  -- 2.96M (great)
 where CreatedUtc > @minDate
option (recompile);

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate
option (optimize for unknown);

The statistics are up to date.

DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;

The last few rows of the histogram (189 rows total) are shown:

A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type:
Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression
One workaround is to use GETUTCDATE (which returns datetime):
WHERE CreatedUtc > CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE()))

Note the conversion to datetime2 must be outside the DATEADD to avoid the bug.
An incorrect cardinality estimation reproduces for me in all versions of SQL Server up to and including 2019 CU8 GDR (build 15.0.4083) when the 70 model cardinality estimator is used.
Aaron Bertrand has written an article about this for SQLPerformance.com:

Performance Surprises and Assumptions : DATEADD()


NonClustered Index Scan Problem I had an issue with a Clustered Index Scan. So I got all columns in the Output list of the Index Scan in SSMS and I created a NonClustered Index for these columns.

Now however, I do not see any performance difference even though it is using the new NonClustered Index. Instead it is now doing a NonClustered Index Scan.

How do I get it to do a seek?

This is the Predicate shown in the plan

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]

How do I get it to do a seek?

Given the predicate on the table you have shared with us:

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]

It seems likely you are using an OPTION (RECOMPILE) hint on the query in a version of SQL Server that does not support the Parameter Embedding Optimization (PEO) fully. This requires at least SQL Server 2008 SP1 CU5.

Without that, the recompile hint is not an effective way for dynamic search queries to seek on an index. This type of query is characterized by expressions like:

column = @variable OR @variable IS NULL
column = COALESCE(@variable, column)

If you are not using one of those constructions, it would be something broadly similar. If full parameter embedding were available, the predicate you have would be simplified to:

'SGD' = [UserTbl].[Name]

...which could use a nonclustered index with [Name] as the leading key to perform a seek.

You need to do one or more of the following:

Upgrade to a more recent version of SQL Server (adding an OPTION (RECOMPILE) hint if not already present)
Rewrite the query, perhaps using dynamic SQL, so that the expression is seekable using an index with Name as the leading key
Ensure Name is the leading column in the index

Further reading:

Parameter Sniffing, Embedding, and the Recompile Options
Dynamic Search Conditions in T-SQL


How do I abstract a WHERE clause condition? I'm wondering how a condition in a commonly used WHERE clause can be abstracted away so that it can be safely changed.

Say I have data like

ColA | ColB | ColC
==================
  1  |   2  | ABC
  1  |   3  | ABD
  1  |   4  | ABE
  2  |   2  | ACC
  3  |   2  | ACD
  4  |   2  | ACE

I have an example query something like

SELECT ColA, ColB FROM MyTable WHERE ColC LIKE 'AB%'

This clause appears in many selections of varying complexity.

The problem I have is that the ColC codes are going to be changed incrementally into different values, e.g. ABE may be changed to ZBE.Throughout this period, the existing query results have to remain the same - i.e. ABC, ABD and ABE would have to become ABC, ABD and ZBE. Then ABD may change to ZBF and so on. Oh, and the database must have minimal down-time.

My gut instinct would be to change all occurrences of this clause into a function now when time-pressure is low, so that when data starts being transformed, I can change that function into whatever hideous thing it needs to be without having to worry about making lots of complex, messy changes quickly.

However, I've been reading up and come across many recommendations that I shouldn't be doing this as it will be inefficient (which I can totally see).

So, what would be the best way to do this? If I have to accept a period of inefficiency during this transition period, then so be it, but I'd rather not.

Thanks.

OK 5 years later, here's a possible solution.
If this is your database and you are allowed to change its structure, I would

Create 2 new fields; OldC and NewC.
Update all OldC values with the value of ColC.
When ready to calculate, update all NewC values with the new value.
When ready to migrate, update all ColC with NewC.

If during this time, you need to know whether ColC is the old or new value, compare it to the 2 new fields.


WiredTiger internal cache It is stated in the MongoDB documentation FAQ (https://docs.mongodb.com/manual/faq/storage/#wiredtiger-storage-engine) :

  Avoid increasing the WiredTiger internal cache size above its default value.

which should be 50 % ou 60 % of RAM by default

and

  The storage.wiredTiger.engineConfig.cacheSizeGB limits the size of
  the WiredTiger internal cache. The operating system will use the
  available free memory for filesystem cache, which allows the
  compressed MongoDB data files to stay in memory.

I don't understand this statement. Does the WT cache contains the working set in terms of compressed data and index ? If so, do we need to keep extra memory for some kind of memory mapped files for MongoDB data files ?

My db.serverStatus().mem shows this :

{
    "bits" : 64,
    "resident" : 10518,
    "virtual" : 12131,
    "supported" : true,
    "mapped" : 0,
    "mappedWithJournal" : 0
}

So I would guess I've no memory mapped bytes, then I can increase de WT cache ?

Thanks !

Do we need to keep extra memory for some kind of memory mapped files for MongoDB data files

The mem.mapped and mem.mappedWithJournal metrics in serverStatus output are only applicable to the MMAPv1 storage engine, so are expected to be 0 with WiredTiger. These metrics predate the storage engine API (and WiredTiger) so will likely be moved to a storage-engine specific section of serverStatus at some point.

  Does the WT cache contains the working set in terms of compressed data and index ?

The WiredTiger internal cache is used for uncompressed data which is a different representation from the compressed data format on disk. Memory outside the WiredTiger cache is available for filesystem cache (which caches the on-disk representation, including compression) as well as other temporary RAM requirements such as per-connection overhead (1MB per connection), in-memory sorts, aggregations, and JavaScript contexts.

Your working set is the total set of data and indexes that your application frequently accesses. For best performance this should ideally fit into RAM. A larger WiredTiger cache will allow for more uncompressed data in RAM at the expense of memory available for other purposes. If your data compresses significantly, more free memory for the filesystem cache will allow for a larger total working set in RAM (uncompressed + compressed data).

  So I would guess I've no memory mapped bytes, then I can increase de WT cache ?

As per the MongoDB documentation, you should generally leave the WiredTiger cache at the default size (or possibly even decrease this). You can always try to experiment with tuning the cache size for your workload and deployment, but setting this too large may adversely affect performance.


Is there a way to install both tabular and multidimensional modes? I have started studying for the MCSE BI exam. I would like to have both the multidimensional and tabular modes available for studying/practice purposes. I have a SQL Server 2014 Developer Edition with tabular mode installed.

Is it simply a matter of installing another named instance? Seems like there's a gotcha somewhere in there, but I have not found it in the research I have done so far.

You answered your own question. You simply install another instance of SSAS.


Rebuild Indexes on SQL Server Standard edition We have SQL Server 2008 R2 Standard on a Windows Server cluster. I want to defrag all indexes on a database.

What is the best approach to disable all connections and allow the rebuild of indexes?

I might not be able to shutdown web servers. I doubt single user mode would work as my connection might be kicked out. I also doubt restricted user mode would work as the application logs in as dbo.

One solution that comes to mind is disabling the SQL Server port but not quite sure. Any suggestions?

Edit: I think of setting the database as single_user\restricted_user and connect via DAC and run the defrag job from the command line. Do you think this would work?

Depending on your version of SQL Server, you can do index rebuilds as an ONLINE operation. This is an Enterprise feature.

Failing that (ie Standard Edition etc), look at re-organizing indexes - these will keep them online. Microsoft best practice says that you should generally not do a rebuild operation unless fragmentation is over 30%. You should always do some form of intelligent rebuilding - i.e check fragmentation and take an appropriate action. I can't recommend Ola Hallengren's maintenance solution enough for dealing with this.

https://ola.hallengren.com/

If you are worried about the blocking effect of index rebuilds, I would recommend this sort of approach.


How to read and process xml data from stored procedure in sql server? I am calling a stored procedure like this:

usp_ReportResults @query = '759,905,1048,170,725,80129',
                  @ReportName = 'GenRepot'

where usp_ReportResult is defined as

CREATE PROCEDURE [dbo].[usp_ReportResults]
     @query VARCHAR(MAX),
     @ReportName VARCHAR(100),
     @AutoSelectXML BIT = 1,
     @XMLResult XML = NULL OUTPUT

I am trying to get result in xml variable to make further process as:

DECLARE @XMLRESULT xml

SET @XMLRESULT = exec usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName = 'GenRepot'

But I'm not able to get result in @XMLRESULT and unable to read and store data result from xml to table.

You don't need to SET the XMLRESULT.

You should pass it in as an OUTPUT parameter.

https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

DECLARE @XMLRESULT xml;
EXEC usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName ='GenRepot', @XMLResult = @XMLRESULT OUTPUT;

You can then use the @XMLRESULT parameter, which has been populated with the result of the Stored Procedure.

INSERT INTO dbo.my_table(xml_field)
SELECT @XMLRESULT;


Backup of Postgres database I want to create a backup of my database in Postgres.
I'm using pgAdmin.

on my database I have clicked right button of mouse
after that I've selected the "Create a backup of database"

After that I've got a sequence of messages:

C:\Program Files\pgAdmin III\1.12\pg_dump.exe --host localhost --port 5432 --username "postgres" --role "postgres" --no-password  --verbose --file "E:\wfr.backup" "WFR"
pg_dump: server version: 9.1.1; pg_dump version: 9.0.3
pg_dump: aborting because of server version mismatch
pg_dump: *** aborted because of error

Why didn't it create a backup and return the error instead?

pgAdmin III 1.12 does not support PostgreSQL 9.1. So better upgrade your pgAdmin III to version 1.14 or newer.


MongoDB - can read cause write locks? I have a MongoDB database with ~100GB data size. I ran a test with 300 threads and a total 10,000 'read' queries. I notice on the MMS lock% it is showing 0.6 ~ 1% of write lock. My question is, the queries are purely 'read', why is it showing write lock?

First off, to correct the comment: the profiler does not write to the local database, it writes to whatever database you are profiling in the system.profile collection.  So if you were reading from database foo and had profiling turned on, the collection would be foo.system.profile.  You could turn on profiling for the local database, and hence have the profiler write there of course, but it is not a default of any kind.

Then, if you have the profiler enabled, regardless of where it writes too, all writes require a write lock whether they are to the system.profile collection or anywhere else. Just because it is getting input internally does not exclude the system.profile collection from following the same rules as any other collection on a database.

If you turn the profiler off I would expect the lock to disappear.  There may still be the occasional blip in the global lock percentage in the stats but that is essentially just background noise.


Sql Server 2016 Cannot drop database (it is really, really, really stuck) I have a database that was involved in a mirroring session and something crashed hard and the database is stuck 'in recovery' and will not allow me to drop it because it thinks the database is in use although I can't see what.

I run this and the affected database is not listed:

SELECT DB_NAME(dbid) as 'DBName'
     , loginame as 'Login'
     , COUNT(dbid) as 'Connections'
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

I also run EXEC sp_who2 and the database is not listed either

I run

ALTER database careplans set offline with ROLLBACK IMMEDIATE;
DROP database careplans;

and I get an error "ALTER DATABASE failed because a lock could not be placed on database 'careplans'. Try again later."

This brings back no results:

select * from sys.sysprocesses where dbid = DB_ID( 'careplans')

This gives me the 'lock could not be placed' error:

ALTER DATABASE careplans
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

This gives me an "Exclusive access could not be obtained because the database is in use." error:

RESTORE DATABASE CarePlans WITH RECOVERY

Any of the following give me the 'lock could not be placed' error:

ALTER DATABASE careplans SET  SINGLE_USER WITH NO_WAIT
ALTER DATABASE careplans SET EMERGENCY;
DBCC CHECKDB (careplans, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE careplans SET online;
ALTER DATABASE careplans SET  Multi_USER WITH NO_WAIT

I have no idea what else I can try...?

This issue is pretty tricky to reproduce so I can suggest to try two more solutions, but both require SQL Server to be restarted:

Start SQL Server in single user mode (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode)
(just add SQL Server service startup parameter -m and restart the service). Then using SQL CMD connect to the SQL instance using local administrator account and try to drop database using DROP DATABASE statement. Perhaps this time you won't get an error related to exclusive access acquisition.
Another way (pretty primitive I know! But it might be effective either!) is to stop SQL service and delete (or change location) of database files. Then when you start SQL Server back this database should come up in 'recovery pending' status and you should be able to remove it.


How can I identify queries that are producing cache misses in Postgres? I have a Postgres database with a total data size of 115GB. The server has ~60GB of memory.  The index cache hit rate is holding at 99%+ but the table cache hit rate has fallen to ~97%.

I am trying to identify if there are particular queries or access patterns we are making that are contributing to the drop.  We may be able to optimize the app if so.

I have used the below query to identify tables that have a low hit rate...

SELECT relname,
  CASE (sum(heap_blks_hit) + sum(heap_blks_read))
    WHEN 0 THEN 1
    ELSE sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))
  END as hitrate,
  pg_size_pretty(sum(heap_blks_hit) + sum(heap_blks_read)) AS total_read,
  pg_size_pretty(sum(heap_blks_read)) AS total_miss
  FROM pg_statio_user_tables
  GROUP BY relname
  ORDER BY hitrate

I am not sure where to go from here though.  Is there a way to track if certain queries are commonly producing misses for the tables I know are low?

The pg_stat_statements extension does exactly what you want, giving the block hits and misses for each statement.

However, I usually don't find this information all that useful.  Many of the block misses are actually served by the file system cache and not actually read from disk.  PostgreSQL provides no direct way to discern these types of misses.

I think the best thing to do would be to turn on track_io_timing and then look at the time spent servicing those misses, rather than the raw number of misses.


Normalisation of practise database I am a newbie to database design, and I'm a bit confused about how to create a database with minimum redundancy. Suppose I have a table abc with repetition of data in it and no two rows are same. Now to reduce repetition? I follow normalisation rules and break the table abc into other tables with relations to table abc. Now is it a fair practise to add a primary key in table abc explictly which will identify each row uniquely, or we can ignore adding primary key?

If primary key is added in table abc and table abc contain a column authid (int). then it collides with the primary key value in it.

Update

To elaborate a flat db abc suppose i have a library table with columns

ISBN   Title   Auid   Auname   Auphone   Pubid  Pubname   pubphone   price
-----------------------------------------------------------------------------
11111  C++      4     Roman    2147483647  1   Big House    1234567890  20
9999   Emma     1     Austen   1111111111  1   Big House    1234567890  20
9133   Faeri    7     Spenser  2147483647  1   Big House    1234567890  15
9104   Hamlet   5  Shakespeare 2147483647  2   Alpha Press  2147483647  20
1034   Iliad    3    Homer     2147483647  1   Big House    1234567890  25
1234   Jane     1    Austen    1111111111  3   Small House  2147483647  49
9977   King     5  Shakespeare 2147483647  2   Alpha press  2147483647  49
5555   Macbeth  5  Shakespeare 2147483647  2   Alpha press  2147483647  12

here in this lib table we may have same ISBNin two rows but any two rows are unique.the repeatations of data is obvious as shown and to reduce that we can break the table and follow normaliation

Books

Title
ISBN
Price
ISBN primary key

Authors

AuName
AuPhone
AuID
Auid primary key

Publishers

PubName
PubPhone
PubID
Pubid primary

and:

book/author

ISBN
Auid

to maintain book/author relation

So can I add primary key explicitly in flat library table or how to acheive normalisation? I think I should add primary key else no relation. Is this breaking enough to achieve normalisation or any other concept or review is always welcome?
Following example from Access Database Design & Programming, 3rd Edition by Steven Roman

Your breakup of the table is largely correct.  There is three tangible objects, a book, the author, and the publisher.  Each of these needs a table, we also need a table to store the relationship between Authors and Books and Publishers and Books as these relationships are many to many.

Books

ISBN (primary key)
Title
Price

Authors

AuId (primary key)
AuName
AuPhone

Publishers

PubId (primary key)
PubName
PubPhone

Authors to Books

AuBId (primary key)
AuId (composite key)
ISBN (composite key)

Publishers to Books

PubBId (primary key)
PubId (composite key)
ISBN (composite key)

You could just use the composite keys instead of having an explicit primary key on the last two tables that define the many-to-many relationships.

Edit:

When I say composite key I mean if a primary key exists that it is an alternate unique key made of multiple columns.  If there is no other primary key defined then the primary key would be defined as both columns, i.e. PubId and ISBN.  There are both pros and cons for doing using a composite key as the primary key, people have different takes on the best option.

There is no reason to explicitly store the libary table with the table structure above, all the information is already stored.  If absolutely necessary I would think that the ISBN is unique, if not you could as mentioned add a primary key column like serialno which maintain integrity.


ALTER TABLE command with no default value taking forever? I'm attempting to add a NOT NULL constraint to a table I have (so I can use it as part of a primary key, so I can create a spatial index...) but it is taking a really long time - 1 hour 31 minutes and counting.

I had read this post where the answer seemed to be that it was taking so long because the person was adding a default value but i'm not doing that:

ALTER TABLE unacastVisitRaw ALTER COLUMN timestamp varchar(64) NOT NULL

The table is rather large (290 million records) but it still seems like a long time for just adding a constraint and not changing any values.

Is there anything I can do to speed up this operation?  Why would it be taking so long if I'm not updating the existing records?

UPDATE: Here are the wait stats for the session

Why would it be taking so long?

You can look at the Session Wait Stats to see. eg

declare @session_id int = 121

select top 10 *
from sys.dm_exec_session_wait_stats
where session_id = @session_id
order by wait_time_ms desc

select command, wait_type, wait_time, last_wait_type, cpu_time, writes, total_elapsed_time
from sys.dm_exec_requests
where session_id = @session_id

where 121 is the session id of the session running the query.

  Why would it be taking so long if I'm not updating the existing records?

It probably wouldn't.  Changing the nullability is a change to the data type, not a constraint.  And it appears that it requires a rewrite of your table.


Convert hierarchy in an ordered list of rows I tried to make a simple list that represents hierarchy of items.

Tags have simple structure like
   [Id]
  ,[Name]
  ,[ParentTagId]

I try to make the follwing result table:
ID, ParentID, Name, Lev, LevelDynamic, RowNumber
LevelDyanmic is for rendering. It contains a delta of level changes. The order of returned rows is important.

WITH cte (ParentID, ID, Name, lev, shift)
              AS(SELECT   ParentTagID,
                            ID,
                            Name,
                            1,
                            ID * 10000
                   FROM     dbo.Tag
                   where ParentTagID is null
                   UNION ALL
                   SELECT   e.ParentTagId,
                            e.ID,
                            e.Name,
                            lev + 1,
                            shift + lev * 100 + 1
                   FROM     cte
                            INNER JOIN dbo.Tag AS e ON e.ParentTagId = cte.ID
                 )
                  select cte.ID,
                     cte.ParentID,
                  cte.Name,
                  cte.lev,
                  cte.shift,
                  CAST(cte.lev - (LAG(cte.lev, 1, 0) OVER(ORDER BY cte.shift)) as int) as LevelDynamic,
                  CAST(ROW_NUMBER() OVER(ORDER BY cte.shift) as int) AS RowNumber
                  from CTE
                 order by shift asc

But that query returns me not correct results, the order of sibling elements is incorrect, it related to incorrect shift calculation. Could you help me with it ?

Probably you need tree nodes in depth-first  order

with tag as (
    select * from (values
     (1,'.Net',cast(null as int)),
     (2,'EF',1),
     (3,'NHib',1),
     (4,'CF',2),
     (5,'Java',null),
     (6,'JRE',3)) t([Id],[Name],[ParentTagId])
),
cte (ParentID, ID, Name, lev, shift)AS(
  SELECT   ParentTagID,
                ID,
                Name,
                1,
                row_number() over(order by id)
       FROM     Tag
       where ParentTagID is null
       UNION ALL
       SELECT   e.ParentTagId,
                e.ID,
                e.Name,
                lev + 1,
                shift * 100 + row_number() over(order by e.id)
       FROM     cte
       INNER JOIN Tag AS e ON e.ParentTagId = cte.ID
)
select *,
   CAST(cte.lev - LAG(cte.lev, 1, 0) OVER(ORDER BY cast(shift as varchar(50))) as int) as LevelDynamic,
   CAST(ROW_NUMBER() OVER(ORDER BY cast(shift as varchar(50))) as int) AS RowNumber
from cte
order by cast(shift as varchar(50))


Why does my statement result in a syntax error? I tried to run this SQL statement in MySQL 5.5 and 5.7.1 but always got error.

DELIMITER //
CREATE PROCEDURE one_off_update_user_info_view_type()
  BEGIN
    DECLARE @batchsize BIGINT DEFAULT 1;
  END
//

You could check the script in local MySQL 5.5+ since it does not contain any business logic or inner-company databases. Could anyone tell me why the error arose and how to fix it?

You use DECLARE in compound-statement syntax to declare a local variable, cursor, condition or handler. However, your variable @batchsize is a session global variable due to the presence of the @ prefix, and it is an error to attempt to redeclare a global variable.

Change the name of your variable from @batchsize to batchsize, and then read up on MySQL variables.


Recommending movies customers haven't rented yet based on their favorite genres, according to their rental history Disclaimer

I do NOT want any explicit queries to my question. I want suggestions regarding how I can best approach this problem. I am eager to learn SQL by experimentation, but for my sake, I'd prefer not to have my hand held. Thanks!

Database/ER model

I am using the DVD Rental database found on the PostgreSQLTutorial website. Here's the ER diagram they've provided (the zip is provided on the site):

Preliminary problem leading up to question

For my own practice, I wanted to see if I could come up with a list of customers (customer) and the genres (film_category) from which they've rented (payment and rental) movies, as well as counts for each of those genres per customer. Here is the query I wrote, which works correctly:

SELECT customer.first_name || ' ' || customer.last_name AS customer, category.name AS movie_genre, COUNT(category.name) AS num_rented

FROM customer JOIN payment ON customer.customer_id = payment.customer_id
JOIN rental ON payment.rental_id = rental.rental_id
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film_category ON inventory.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id

GROUP BY customer, movie_genre
ORDER BY customer, num_rented DESC;

Problem

To challenge myself further, I want to see if I can list each customer's name along with the names of movies they have not yet rented that belong to the genre from which they've purchased the most movies (or the first one, if there's a tie). For example, here's a snapshot of some of the data I get when running the above query:

And here's a preview of the first 19 rows of the film table (not joined in the above query, but it will need to be joined for this problem):

So for example, we see that Aaron Selby really likes Travel movies, so we want to list her name alongside all movies belonging to the Travel category, but only those movies she has not yet rented.

Question

Could someone please offer some guidance/hints as to where I can begin? I know I'll need to join the film table, but here are some challenges:

limiting the output for each customer to only the most common genre they've rented
based on that information, retrieving all movies from films corresponding to that genre that the customer has not yet rented

Most common ...

In statistics, this is called mode.

Yes, PostgreSQL has an aggregate called mode.  I haven't messed with it in PostgreSQL.

Retrieves those not wanted...

You want the resulting set of all movies for a genre minus the data set containing all movies rented by a customer for that genre

In PostgreSQL, you'll want to say EXCEPT instead of MINUS.


How can you create a ROLE which isn't allowed to do anything? I want to create a role which by default isn't allowed to do anything. It has basically zero permissions. Starting from such role, I want to grant the minimal set of permissions.

How can you make a role without any kind of permissions?

Just use

CREATE ROLE foo;

Be aware of default privileges (https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html).


Oracle transactions deadlock How to rollback all active transactions on Oracle DB?

I execute query and see 4 transcations in ACTIVE status.

SELECT *
FROM V$TRANSACTION

Deadlock (ORA-00060) are automatically resolved by Oracle and signalled in the alter.log.
Transactional locks have to solved manually.
You can kill session from sql command line using:

SQL> alter system kill session 'sid, serial#';

This will mark session for being killed whener it's possible. Sometime kill is not immediate. IF you want and immediate kill you can, on a unix system, run

kill -9 processid

This will kill immediatly the session forcing the PMON to rollback any uncommitted data. To retrieve processid (SPID) run:

SQL> SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

A final note: in windows process is slightly different. To kill a session you have to run:

C:\> orakill <SID> <spid>

where <SID>  = the Oracle instance name (ORACLE_SID)
      <spid> = the thread id of the thread to kill


What are options for super volatile tables? I've inherited a very volatile table which is a map of who holds what resource in the system.  At any given moment, there could be a dozen inserts/deletes/reads going against that table.  However, there are never any more than 30-40 rows in the system.

The system was written in the SQL 2000 era and the access to the table is serialized via sp_getapplock/sp_releaseapplock system sprocs, so that only 1 request is modifying the table. In addition, the INSERT & DELETE statements execute WITH (TABLOCK). Reading the notes from a decade ago, it states that without these restrictions, the system would experience non-stop deadlocks.

I've ported the database to SQL Server 2016 Enterprise Edition.  Now that the throughput of the system has increased 10 fold, this table is easily the biggest bottleneck.

What are my options for a table as volatile as this with SQL 2016?

I am looking for fast (hopefully concurrent) access and no deadlocks.

In my experience, Sql Server sometimes wants to scan small tables (even when they have indexes that you'd think would be used) which can lead to deadlocks on a hot table.

It might be a kludge solution, but I've had tables like this and was able to solve my problem by actually adding lots of 'bogus' rows to 'widen' the table and force Sql Server (optimizer) to seek instead of scan.  I've also had to resort to using the ROWLOCK hint to get around the deadlock problem (your mileage may vary)


Ms sql MERGE INTO locks whole table for updates I have a table for statistic values, it holds millions of records, which is defined like this:

CREATE TABLE [dbo].[Statistic]
(
    [Id] [INT] IDENTITY(1, 1) NOT NULL
  , [EntityId] [INT] NULL
  , [EntityTypeId] [UNIQUEIDENTIFIER] NOT NULL
  , [ValueTypeId] [UNIQUEIDENTIFIER] NOT NULL
  , [Value] [DECIMAL](19, 5) NOT NULL
  , [Date] [DATETIME2](7) NULL
  , [AggregateTypeId] [INT] NOT NULL
  , [JsonData] [NVARCHAR](MAX) NULL
  , [WeekDay] AS (DATEDIFF(DAY, CONVERT([DATETIME], '19000101', (112)), [Date]) % (7) + (1)) PERSISTED
  , CONSTRAINT [PK_Statistic]
        PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [IX_Statistic_EntityId_EntityTypeId_ValueTypeId_AggregateTypeId_Date]
ON [dbo].[Statistic] (
                         [EntityId] ASC
                       , [EntityTypeId] ASC
                       , [ValueTypeId] ASC
                       , [AggregateTypeId] ASC
                       , [Date] ASC
                     );

CREATE NONCLUSTERED INDEX [IX_Date] ON [dbo].[Statistic] ([Date] ASC);

CREATE NONCLUSTERED INDEX [IX_EntityId]
ON [dbo].[Statistic] ([EntityId] ASC)
INCLUDE ([Id]);

CREATE NONCLUSTERED INDEX [IX_EntityType_Agg_Date]
ON [dbo].[Statistic] ([EntityTypeId] ASC, [AggregateTypeId] ASC, [Date] ASC)
INCLUDE ([Id], [EntityId], [ValueTypeId]);

CREATE NONCLUSTERED INDEX [IX_Statistic_ValueTypeId]
ON [dbo].[Statistic] ([ValueTypeId] ASC)
INCLUDE ([Id]);

CREATE NONCLUSTERED INDEX [IX_WeekDay]
ON [dbo].[Statistic] ([AggregateTypeId] ASC, [WeekDay] ASC, [Date] ASC)
INCLUDE ([Id]);

ALTER TABLE [dbo].[Statistic]
ADD CONSTRAINT [PK_Statistic]
    PRIMARY KEY NONCLUSTERED ([Id] ASC);

During updates with merge, sql server locks the whole table instead of pages/rows, @inTbl is a key/value datatable passed as parameter

MERGE INTO Statistic AS stat
USING
    (SELECT inTbl.EntityId, inTbl.Value FROM @p0 AS inTbl) AS src
ON src.EntityId = stat.EntityId
   AND stat.EntityTypeId = @p1
   AND stat.ValueTypeId = @p2
   AND stat.Date IS NULL
   AND stat.AggregateTypeId = @p3
WHEN MATCHED THEN
    UPDATE SET stat.Value = src.value
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EntityTypeId, ValueTypeId, Date, AggregateTypeId, EntityId, Value)
    VALUES
    (@p4, @p5, @p6, @p7, src.entityId, src.value);

So, I have 2 problems:
1) the merge sometimes takes forever to finish

2) updates like this wait for merge to finish:

UPDATE [dbo].[Statistic]
SET [Value] = @p0, [JsonData] = @p1
WHERE [EntityTypeId] = @p2
      AND [ValueTypeId] = @p3
      AND [Date] = @p4
      AND [EntityId] = @p5
      AND [AggregateTypeId] = @p6;

I have plans/locks files for the queries, but they are rather big, so here they are

before index rebuid: https://www.brentozar.com/pastetheplan/?id=S19EgxYIB

after index rebuild: https://www.brentozar.com/pastetheplan/?id=SyjexxtLH

What can be the problem? This happens occasionally and may sometimes go away after clustered index rebuild.

The clustered index goes fragmeted to 90+% in a day or so. How can I prevent this fragmentation?

I have a table for statistic values, it holds millions of records

  ...

  The clustered index goes fragmeted to 90+% in a day or so.

Look at your clustered index, its key is 48 bytes long, it's not a good choice because your table is big enough and you have also 5 nonclustered indexes. All of them have these 48 bytes at every index level,
so every nonclustered index occupies at least twice of space it needs.

IMHO, the first thing to do is, if possible, to change clustered index key, your clustered index can be defined on identity, it will be unique, always increasing, narrow, and this will reduce yor clustered index fragmentation, and in case when JsonData field is never updated clustered index fragmentation will be 0.

This will also decrease your insert time: now too much time is spent to log page slits caused by insert into clustered index.

To your second problem: lock escalation.
As you said, every batch contains 2000 rows in the source table, but they cause 3402 rows to be inserted(according to estimated plan), and this is only for clustered index. You have 5 nonclustered indexes, so in one statement you insert at least 6 * 2000 = 12000 rows, or maybe all 20412 rows if the estimations are correct.

Lock escalation triggers on 5000 locks per statement:

  In addition to escalating locks when an instance-wide threshold is
  crossed, SQL Server will also escalate locks when any individual
  session acquires more than 5,000 locks in a single statement. In this
  case, there is no randomness in choosing which session will get its
  locks escalated; it is the session that acquired the locks.

and in your case they very probably are row locks, this is because of your clustered index key that is random.
It could take page locks in case of insertion into always increasing key, but your clustered key is really random. And in any case insertions into nonclustered indexes are random too, so it's normal that server chose row locks.

So you can disable lock escalation on your table or split your batches in 1000 rows per batch or even less, this should be tested.

Here is a small repro in response on this comment:

  inserts can't take locks (can't lock a resource that doesn't exist)

if object_id('dbo.t') is not null drop table dbo.t;
create table dbo.t(id int identity primary key, col1 varchar(10), col2 varchar(10));
create index ix_col1 on dbo.t(col1);
create index ix_col2 on dbo.t(col2);

begin tran
insert into dbo.t (col1, col2)
select top 1000 'aaa', 'bbb'
from sys.columns c1 cross join sys.columns c2;

select *
from sys.dm_tran_locks
where resource_type <> 'DATABASE'
      and request_session_id = @@spid
order by resource_associated_entity_id,
         resource_type;

rollback tran;


/usr/libexec/mysqld: Normal shutdown, but my team doesn't do that? One of my databases has been shutting down abnormally, but the log said "normal":

130422 13:23:01 [Note] /usr/libexec/mysqld: Normal shutdown

130422 13:23:01 [Note] Event Scheduler: Killing the scheduler thread, thread id 379021
130422 13:23:01 [Note] Event Scheduler: Waiting for the scheduler thread to reply
130422 13:23:01 [Note] Event Scheduler: Stopped
130422 13:23:01 [Note] Event Scheduler: Purging the queue. 41 events
130422 13:23:01 [Note] Error reading relay log event: slave SQL thread was killed
130422 13:23:01 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
130422 13:23:01 [Note] Slave I/O thread killed while reading event
130422 13:23:01 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002426', position 107
...
130422 13:23:04  InnoDB: Starting shutdown...
130422 13:23:04  InnoDB: Waiting for 201 pages to be flushed
130422 13:23:08  InnoDB: Shutdown completed; log sequence number 6942784551767
130422 13:23:08 [Note] /usr/libexec/mysqld: Shutdown complete

There is no related error or stack trace in the mysqld.log, just some errors:

No data - zero rows fetched, selected, or processed

from Event Scheduler.

I'm pretty sure my team doesn't stop it (via init script or mysqladmin shutdown, ...). grep through the last binlog file, I didn't see any SHUTDOWN statement.

Is there any other causes? Let me know if you need more information.

PS: mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1

UPDATE at Mon Apr 22 18:23:33 ICT 2013

Searching through the source code, I found this:

$ grep -lir 'normal shutdown' mysql-5.5.28
mysql-5.5.28/scripts/mysqld_safe.sh
mysql-5.5.28/Docs/mysql.info
mysql-5.5.28/sql/share/errmsg-utf8.txt

$ grep -lr 'ER_NORMAL_SHUTDOWN' mysql-5.5.28
mysql-5.5.28/Docs/mysql.info
mysql-5.5.28/sql/mysqld.cc
mysql-5.5.28/sql/share/errmsg-utf8.txt

mysql-5.5.28/sql/mysqld.cc

  if (sig != 0) // 0 is not a valid signal number
    my_sigset(sig, SIG_IGN);                    /* purify inspected */
  if (sig == MYSQL_KILL_SIGNAL || sig == 0)
    sql_print_information(ER_DEFAULT(ER_NORMAL_SHUTDOWN),my_progname);
  else
    sql_print_error(ER_DEFAULT(ER_GOT_SIGNAL),my_progname,sig); /* purecov: inspected */

Looks like that there is no other reason for this message can be logged into the log file. One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and close the db connection.

PS: In this bug report, there is one person also got this problem on 5.5.24.

Linux's Viewpoint
You should run this Linux command
history | grep mysqladmin

This will let you see if anyone ran a shutdown from within the server. Note that this will not let you see remote mysqladmin shutdowns. Perhaps running tcpdump and locating the word mysqladmin or shutdown might help.
MySQL's Viewpoint
Shutdown commands do not exist from command line utilities. Therefore, binary logs will not have shutdowns recorded. You are on the right track when you said;

One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and turned it on again.

Look over the users with that privilege. Run this query:
SELECT user,host FROM mysql.user WHERE shutdown_priv='Y';

You may see something like this:
mysql> select user,host from mysql.user where shutdown_priv='Y';
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | localhost    |
| root  | 127.0.0.1    |
| root  | ::1          |
| root  | 10.240.163.% |
| root  | 10.48.19.%   |
| user1 | %            |
+-------+--------------+
6 rows in set (0.00 sec)

mysql>

You will see all the users that have shutdown privilege. SUPER privilege does not have the shutdown privilege. There is a separate privilege called SHUTDOWN.
You can quickly revoke that privilege. For example, note the user user1@'%'. It has remote shutdown privileges. You can yank it with
REVOKE SHUTDOWN ON *.* FROM user1.'%';

or
UPDATE mysql.user SET shutdown_priv='N'
WHERE user='user1' AND host='%';
FLUSH PRIVILEGES;

That takes care of the remote shutdown privileges.
You should also address securing user connections by removing SHUTDOWN privilege from every user that does not need to have it. Perhaps you can do this:
UPDATE mysql.user SET shutdown_priv='N'
WHERE CONCAT(user,'@',host) NOT IN
('root@localhost','root@127.0.0.1');
FLUSH PRIVILEGES;

That way, only root@localhost and root@127.0.0.1 can do mysqladmin shutdown within the localhost. I would definitely have the root@127.0.0.1 on hand because there are times when mysqld loses connection to the mysql.sock file and prevents root@localhost from connecting to issue a shutdown. With the root@127.0.0.1, you can issue this:
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp

to issue a mysqladmin shutdown without needed the socket file.
Give it a Try !!!


Postgressql - could not fork new process for connection: Cannot allocate memory Postgres instance restarted multiple times with out of memory errors. could not able to get what process would have consumed all memory.
2020-09-03 04:54:14 PDT LOG:  could not fork new process for connection: Cannot allocate memory
TopMemoryContext: 799496 total in 13 blocks; 63080 free (17 chunks); 736416 used
  TopTransactionContext: 8192 total in 1 blocks; 7960 free (0 chunks); 232 used
Here are the current settings. Postgres version is 10.5.12

effective_cache_size                | 16GB
maintenance_work_mem                | 1GB
max_wal_size                        | 4GB
max_worker_processes                | 8
temp_buffers                        | 8GB
work_mem                            | 256MB
shared_buffers                      | 32GB
max_connections                     | 3000

vm.overcommit_memory = 2
vm.overcommit_ratio = 80

-sh-4.2$ free -g
              total        used        free      shared  buff/cache   available
Mem:             62           9           0          36          52          16
Swap:             0           0           0

Can you please suggest if any configuration changes required.

If each of these 3000 connections used work_mem only once (this limit is per node of the query plan, so it can be used several times by one database session), that could use almost 1TB RAM. I am not surprised that you are going OOM.
max_connections = 3000 is an insane setting. Do you have 3000 cores? Can your storage subsystem handle 3000 concurrent I/O requests?
I recommend that you use pgBouncer or another connection pool to reduce the number of database sessions.
By the way vm.overcommit_ratio should be 100 if you have no swap, otherwise you cannot allocate more than 80% of the available RAM (but perhaps that's on purpose).


MySQL performance issues when saving Bitcoin data I have set a cron job to a php script which periodically checks Bitcoin for new blocks, and if there are any, add the blocks and transactions to a database. After a few million records have been added, I have noticed that the table I am saving transactions to is suffering an incredible slowdown. This issue had to do with not having set up my search term (in this case address affected and/or txid) as an index, after adding the index it has become considerably faster.

This leads me to believe that there may be some best practices I am unaware of when saving Bitcoin data to a database, possibly breaking tables down to a set size, so that I can search my database later at optimal speeds.

My question is this: When saving a large data set to MySQL that constantly gets updated, such as the Bitcoin data, what do I need to do to improve the performance for database lookups?

My primary goal is to be able to retrieve data as quickly as possible. Thank you! :)

Edit: Here is the table structure before adding indices.

vin (Hex TEXT, Txid VARCHAR(64), Version VARCHAR(1), Blockhash VARCHAR(64), Locktime VARCHAR(1), Time VARCHAR(10), Vincoinbase BLOB, Vintxid VARCHAR(64), Vinvout VARCHAR(4), Vinscriptsigasm TEXT, Vinscriptsighex TEXT, Vinsequence VARCHAR(12))

vout (Hex TEXT, Txid VARCHAR(64), Version VARCHAR(1), Blockhash VARCHAR(64), Locktime VARCHAR(1), Time VARCHAR(10), Voutvalue DECIMAL(20,8), Voutn VARCHAR(4), Voutscriptpubkeyasm TEXT, Voutscriptpubkeyhex TEXT, Voutscriptpubkeyreqsigs VARCHAR(4), Voutscriptpubkeytype VARCHAR(10), Voutscriptpubkeyaddresses TEXT)

I am not aware of any Bitcoin-specific best database practices. I also see no need for them, as just general good database design will help you.

The key to achieving decent database performance is to define indices in a way that every access you every need can take advantage of them. Where you do not succeed in this, just about any query will revert to iterating over every record in the table, which obviously becomes slow when you reach gigabytes of stored data.

A good place to start will be to define a (ideally integer, i.e. BIGINT) unique index, also known as primary key. If you do not do that yourself, mysql will just invent one anyways---and if you do, you automatically have a fast way to refer to transactions (in database parlance: records in the vin and vout tables). If you invent a new field for it---as I would suggest---you may want to call it id and use AUTO_INCREMENT to have mysql take care of inventing new unique values for new records.

You may also want to have a close look at your VARCHAR entries. These are essentially just (very big) integers, and if you define them as such (check out the DECIMAL and NUMERIC types), handling indices on them becomes computationally much cheaper because complications in string sorting such as collation do not arise. If you prefer to work with the hexadecimal representations, then that can be achieved with suitable VIEWs into your tables, but be warned that using these is a frequent source of new performance problems since optimization to index-usage after querying a view is a complicated subject. To get this right, it may be safer to do all conversions between hex and large-integer representation outside the database.

To increase your chances at getting better and more detailled answers than I have just given, I very strongly suggest you

Look to a developer or database community, because none of the relevant issues are very Bitcoin-specific, at best they are specific to database applications involving very large integers.
Provide relevant information such as what you've already done---or do you expect someone to completely re-invent an indexing scheme neither knowing what queries you like to run, nor what you have already managed to get right youself?

Good luck!


PostgreSQL 9.6 error: invalid resource manager ID I am encountering error messages like this in a PostgreSQL 9.6 server log file (while experimenting with pg_rewind):

LOG: invalid resource manager ID 119 at 0/910CE68

What is a (PostgreSQL) resource manager and why does it have to by identified in a WAL file?

I have by now concluded that this is a symptom of PostgreSQL encountering malformed WALs. These error messages also seem related:

LOG: invalid record length ...
LOG: record with incorrect prev-link ...

The root cause was apparently that a call to pg_basebackup -X stream did not terminate. (This was apparently an issue titled "pg_basebackup -x stream from the standby gets stuck if there is no traffic in the database" that was resolved in PostgreSQL 9.2, but may have cropped up again.) When I had to interrupt the call with CTRL-C, this may have left malformed WALs.

I am now using pg_basebackup -c fast -X fetch instead, and this works fine. Given that and for my current purposes, it is no longer necessary to understand details around (PostgreSQL) resource managers.


SQL Server Order SELECT List with condition I want order the select list with condition. For example; I have t1 table and it has 3 columns and 6 rows

Table t1:
+----+---+---+
| id | x | y |
+----+---+---+
| 1  | a | 1 |
| 2  | b | 3 |
| 3  | c | 2 |
| 4  | d | 2 |
| 5  | e | 1 |
| 6  | f | 3 |
+----+---+---+

Ordering the select list without condition, that select query is easy:
SELECT y FROM t1 ORDER BY y result: 1,1,2,2,3,3
SELECT y FROM t1 ORDER BY y DESC result: 3,3,2,2,1,1

If I want that result 2,2,1,1,3,3 or 2,2,3,3,1,1, how can i do it with select query or c# entity framework?

For a simple solution based on just the one 'y=2' value (as per comments), the suggested order by case ... construct should suffice.

However, if the requirement is to be able to sort by a varying number of 'y' values, or perhaps order by a combination of columns ...

One idea that comes to mind ... use a table value constructor (TVC) to build an 'inline' table of your desired sort order; we'll then join this TVC back to your original data set to provide the desired ordering sequence.

We'll start by creating our table and populating with some sample data:

drop table if exists t1;

create table t1
(id     int
,x      varchar(30)
,y      int);

insert into t1 (id,x,y) values
(1,'a',1),
(2,'b',3),
(3,'c',2),
(4,'d',2),
(5,'e',1),
(6,'f',3);

For our TVC we'll generate a single-column derived table called dt(y) that includes the y values listed in their desired sort order.  We'll add the row_number() function to provide the column that we'll actually order by.  Finally, we'll wrap all of this in a common table expression (CTE) for ease of reference.

-- order by 'y' values 2, 1, 3:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(1),(3) ) as dt(y)  -- desired sort order is 2, 1, 3
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  1
 3       |  3

-- order by 'y' values 2, 3, 1:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)    -- desired sort order is 2, 3, 1
)
select *
from   myorder
order by orderid;

 orderid |  y
 ------- | --
 1       |  2
 2       |  3
 3       |  1

We can now join our TVC with the original data set (table t1 in this case) to generate the desired sort order:

-- sort solely on the TVC's 'y' column:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y

from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid;

 id | x  |  y
 -- | -- | --
  3 | c  |  2
  4 | d  |  2
  2 | b  |  3
  6 | f  |  3
  1 | a  |  1
  5 | e  |  1

-- sort by TVC's 'y' column (ascending order), then by t1's 'x' column (descending order):

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2),(3),(1) ) as dt(y)
)

select t1.id,
       t1.x,
       t1.y

from   t1
join   myorder mo
on     mo.y = t1.y

order by mo.orderid,
         t1.x desc

 id | x  |  y
 -- | -- | --
  4 | d  |  2
  3 | c  |  2
  6 | f  |  3
  2 | b  |  3
  5 | e  |  1
  1 | a  |  1

The key item to remember is to make sure the TVC's records are listed in an order that matches the desired sort order.

One potential issue is that if you don't know how many 'y' values you have, or perhaps you only need to sort by a handful of known 'y' values (and don't care about ordering of other 'y' values), you could:

limit the TVC to just those 'y' values you're interested in and ...
join the main data set via an outer join to the TVC and ...
then default the the missing TVC's 'orderid' columns to some sufficiently large number to ensure their location in the sort order does not affect the 'y' values of interest

For example, assume we want 'y=2' values to be listed first, and we don't care about the rest of the 'y' values:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        y
 from   (values (2) ) as dt(y)        -- only interested in 'y=2' records
)

select t1.id,
       t1.x,
       t1.y

from   t1                            -- left join w/ TVC/CTE
left
join   myorder mo
on     mo.y = t1.y

order by isnull(mo.orderid, 100),    -- default missing TVC.orderid's to 100
         t1.x desc

 id | x  |  y
 -- | -- | --
  4 | d  |  2   -- 'y=2' records show up
  3 | c  |  2   -- first in our output

  6 | f  |  3   -- while we let the database engine
  5 | e  |  1   -- determine the order of the rest
  2 | b  |  3   -- of the result set; granted, for this
  1 | a  |  1   -- example we also had 't1.x desc'

Obviously you could still add other columns to the 'order by' clause to affect the final ordering.

Here's a dbfiddle for all of the above.

While the example queries (above) are based on hard coding the sort order for the 'y' column, this method could be expanded to allow for hard coding the sort order for any combination of columns.

Let's say for example you want a hard coded sort order based on 'y=2' and 'x in ('a','b','c')', the CTE/TVC would look like:

with myorder as
(select row_number() over (order by (select 1)) orderid,
        x
        y
 from   (values (2,'a'),(2,'b'),(2,'c') ) as dt(x,y)
)

The main query could then be (outer) joined to myorder on the x and y columns:

with myorder as(...)

select ...

from   t1
left
join   myorder mo
on     mo.x = t1.x
and    mo.y = t1.y

order by isnull(mo.orderid, 100);

Again, if you don't need the flexibility then the more simple order by case ... construct should be sufficient.


oracle DB connection - hostdef extension doesn't exist I am trying to connect to an Oracle DB through ERwin, yet I keep getting this error message:

I know that the DB exists, as my coworkers are accessing it. The Username and Password are also correct, and I've had a few other coworkers try their credentials too and it didn't work.

The possible error might be with the connection string,but I've tried several options, and none seem to work. These are the options I've used:

jdbc:oracle:thin:@DomainName.DN.DN.com:1521:DatabaseName

Of course, I didn't actually type DomainName, I changed those from the company name. This was a direct copy of the connection string that was used in Squirrel SQL for this DB.

I also tried, based on a friend's suggestion:

jdbc:tibcosoftwareinc:oracle://DomainName.DN.DN.com:1521;SID=DatabaseName

This is the connection page that I'm using:

The correct format for the connection string is:

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = "HOST name")(PORT = "PORT number")) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = "SERVICE Name") ) )


Index help for bad Query on fairly big table (2m) I am looking for some assistance please. I have a query that is running against a fairly big table (2 million records).
I have been trying to get the indexes working efficiently. There are a few other queries against this table, but this is by far the most frequent one. I am seriously struggling to get it to execute in under 1 second, and often see it running in 3 to 5 seconds using profiler.
It might be as fast as can be, but I would appreciate some input to confirm/deny that please.
Just a note: Dev wont change the query or schema at all. Optimizations can be made in the database only, and no schema changes.
The Table:
CREATE TABLE [dbo].[Notifications](
[ntID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[NotificationID] [int] NOT NULL,
[NotificationType] [nvarchar](50) NOT NULL,
[UserName] [nvarchar](50) NULL,
[CreatedBy] [nvarchar](50) NULL,
[CreatedOn] [datetime] NULL,
[Status] [nvarchar](50) NOT NULL,
[Result] [nvarchar](50) NULL,
[Extension] [nvarchar](50) NULL,
[ShiftRate] [nvarchar](255) NULL,
[ResponseMinutes] [int] NULL,
[ResponseWindow] [datetime] NULL,
[caNotificationID] [int] NULL,
[AwardedBy] [nvarchar](50) NULL,
[AwardedOn] [datetime] NULL,
[CancelledBy] [nvarchar](50) NULL,
[CancelledOn] [datetime] NULL,
[CancelledReasonID] [int] NULL,
[CancelledReasonText] [nvarchar](255) NULL,
[AwardingDate] [datetime] NULL,
[ScheduledLaunchDate] [datetime] NULL,
[CustomMessage] [nvarchar](160) NULL,
[SystemName] [nvarchar](4000) NULL,
[AutoClose] [bit] NOT NULL,
 CONSTRAINT [PK_ESP_Notifications_ntID] PRIMARY KEY CLUSTERED
(
    [ntID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ESP_Notifications] ADD  DEFAULT ((0)) FOR [AutoClose]
GO

Table Data snapshot:

The Query:
Update Notifications
  set Status = 'Awarding' OUTPUT deleted.*
where ntID = (
    select top(1) ntID
    from Notifications
    where NotificationType = 'Shift'
      and (Status = 'Done')
      and ResponseWindow < '2019-02-04 10:40:03'
    order by ntID)

Attempted Indexes :
CREATE INDEX [IX_Notifications_Status_NotificationType_ResponseWindow_ntID]
ON [dbo].[Notifications](
   [Status] ASC,[NotificationType] ASC,[ResponseWindow] DESC,[ntID] DESC
)

CREATE INDEX [IX_Notifications_Status_ScheduledLaunchDate_ntID]
ON [dbo].[Notifications](
    [ScheduledLaunchDate] DESC,[Status] ASC,[ntID] ASC
)

CREATE INDEX [IX_Notifications_NotificationType_caNotificationID_NotificationID_ntID]
ON [dbo].[Notifications](
   [NotificationType] DESC, [caNotificationID] DESC, [NotificationID] DESC, [ntID] DESC
);

NotificationType contains 3 different types, of which 70% are type 'Shift'
Status has 10 types, but the 'In Flight' records are only about 100 to 200, split over 4 Status's
Thank you for your assistance.

If the subquery in that update consistently uses those two predicate values, a filtered index should help a lot.  Something like this (which Erik Darling kindly provided as a comment):

CREATE INDEX IX_ntID_ResponseWindow_Includes ON dbo.Notifications (ntID, ResponseWindow)
INCLUDE (NotificationType, Status)
WHERE (Status = 'Done' AND NotificationType = 'Shift');

This will let the subquery find the relevant group of IDs that match Status and NotificationType (as they are the only rows in the index) and avoid sorting the data (since it's already ordered by ntID as the leading column in the index).

It will still have to check the ResponseWindow value on each row.  In the worst case, if there are no rows that meet the date requirement for the query (or if the first match has a very high ntID), most or all of the index will be read.
  Which makes the effectiveness of this approach limited depending on data distribution.


Why Are My Mirrored Databases Changing States on Their Own? Setup:

SQL Server 2005 w/SP4 64-bit Standard for all three servers
Windows Server 2008 R2 Enterprise w/SP1 64-bit for all three servers
01 is designated as the Principal
02 is designated as the Mirror
03 is designated as the Witness

My issue is that there are Principal and Mirrored DBs on both servers and it is causing Maintenance Plans and SQL scripts to fail.

My questions:

Why are the DBs failing from one to the other when 01 should have all Principal DBs?
How do I stop this behavior
What is the least invasive process of changing the Principal DBs on 02 back to 01?

Can you please look at SQL Server errorlog and see if you could find some relevant information on both Principal and mirror. Generally if SQL Server CPU utilization goes high above 50 % there can be failover. This is specifically caused by heavy load on SQL Server this is documented in Below Microsoft Web resource See 'recommendation for Configuring partner Server' in link : http://msdn.microsoft.com/en-us/library/ms366349%28v=sql.100%29.aspx

This can be avoided by Alter partner time out

  Alter database db_name set partner timeout 20

Edit: After User provided more details

  Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.

As per this message its clear that network connecting principal and mirror flipped I dont know for how much time but this caused the failover. What happens is Mirror server keeps sending pings to Principal whether it is alive or not and suppose Principal goes down and now mirror will send 10 pings to principal(By default) and if it does not gets any response it will initiate failover with help of Witness( its little more complex with witness ,it involves quorum)

If it was for moment you can increase partner timeout( not a good practice though) but you should Immediately speak to network team and ask them RCA for this glitch as this caused failover. My course of action would be to meet them personally ask them to run bunch of commands to check response time


sql server instance using maximum cpu usage I have a SQL server 2005 instance installed on the server machine with two vm

Server Specification are
*Processor:* Xeon
Ram : 6GB
OS: window server on vm ware

It is running asp.net application deployed on the IIS and sql server database instance . This application is used by around 500 users daily. after every 22 days the processors CPU Usage Reaches to 100% to 95% . and the most used process (in task manager) is the instance of the sql server that is taking the maximum(all) the processing of the processor. Ram is only used up to 3 - 4 GB

I executed the below task but of no use

Recreated all the table indexes but of no use
Restarted the sql server database engine instance but of no use

But when the server(machine) was restarted. The sql server was working fine and CPU Usage was back to normal.

WE DO NOT WANT TO RESTART THE SERVER MACHINE. is this possible to performance tune the sql server so that after 22 days it does not choke up the server processing or what can i do to avoid it.

Try the CPU related DMV queries from Glenn Berry's SQL Diagnostic Queries. In fact, I suggest you run all the diagnostic queries because the high CPU utilization can be a result of some problem with the way the instance is configured, the way queries were written etc.,

Here is another article on SQL Server Performance site, that might help you troubleshoot.


Analyzing an Oracle dump I have a full Oracle dump, I know I have to imp the dump with the fromuser touser clause.

However, I don't know the names of the schemas included in the dump.  How can I open the dump created with Oracle 10g just to check and analyze the content?

Use the SHOW=Y option for imp

From the manual at http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2498

  When SHOW=y, the contents of the export dump file are listed to the display and not imported.
  The SQL statements contained in the export are displayed in the order in which Import will execute them.
  The SHOW parameter can be used only with the FULL=y, FROMUSER, TOUSER, or TABLES parameter.


SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea? Let me set the stage:

SQL Server > 2016
Several databases with the same schemas
Data is similar but not identical
The same indexes exist etc., all systems but one work as expected
Examining the execution plan recommends the creation of an index with ALL of the columns as INCLUDE. The same base column already exists without the includes.

The problem:

On ONE of the databases, the query engine does not utilize an existing index and uses a full table scan
Manually executing the query (which uses a parameter) - full table scan!
Manually executing the query without a parameter (used fixed date) - uses the index.
Manually executing the query with a parameter and adding WITH (INDEX(update_ts_INDEX)) - uses the index. The query goes from 2 minutes to less than a second.

Hypothesis:

I believe that the query cache might be corrupt - but I don't know if that is possible. By trade I am a java developer who has spent the last 3 years digging into SQL server performance tuning, etc.
The fact that manually executing the query without a parameter - the query engine picks the right index - blows my mind. Ideas?

Notes:

On the other servers, which run the same code and queries, I can find NO evidence of this same query taking any time. They must be using the index.
I just executed the command manually, in another region with a parameter, and it used the index so the issue is indeed in ONLY one region - one database.

Example of query:
DECLARE @P1 DATETIME = GETDATE() - .1;

SELECT value_1, update_ts, value_2 FROM PRODUCTION_TABLE
WHERE (PRODUCTION_TABLE.update_ts > @P1);

If I remove the variable and handcode a date, it uses the index.
Question:
Can I surgically remove a bad cached query plan for this particular query?
--- UPDATE ---
Using OPTION (RECOMPILE) causes the query engine to pick the correct index.

You can!
Run this to find your plan's ID:
select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

Then place that ID in the stored procedure called DBCC FREEPROCCACHE
DBCC FREEPROCCACHE (plan_handle_id_goes_here)

You could always just free the whole thing though. It'll add a few seconds to the first time each query is ran, but no more than what a server reset (or an IISRESEt in a web server) would do anyways.
This is how you clear it all:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Check here for more info https://serverfault.com/questions/91285/how-do-i-remove-a-specific-bad-plan-from-the-sql-server-query-cache