MYSQL: Stored function to replace category ids with names I have a stored function which should replace category ids in column path with names from name column. Then store result string into a new column named path_long.

I use Debian 8, MySQL v5.5.

Example

I have a column named path with content like '/426/427/428'. I would like to replace category id numbers with category names. Result would be like '/Computers/Other accessories/Laser printers'.

I have this stored function:

CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
    `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM @path_input)), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp
    GROUP BY catid
    ORDER BY
       n
);

INSERT INTO t2
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;

RETURN (SELECT path_long FROM t2 limit 1);

END

Here is the test DDL:

CREATE TABLE `category` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `path` VARCHAR(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=429
;

Also test data:

INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');

Unfortunately I can't change the design. It is given in the software. Horrible schema or not, horrible database or not, this is what I have. The framework uses this schema, and the database is MySQL. I have to do the query on this system and I have to get the desired result.

Using query:

SELECT decode_path(category.path) as decoded FROM category

Problem

The query results the following:

decoded
A
A
A
A
A
A
A
A
A
A
A
A

Source column looks like this (showing undecoded paths):

path
/426/427/428
/1/2/3
/4/5/6
/7/8/9

Desired result column should be like this (showing decoded paths):

path_long
/Computers/Other accessories/Laser printers
/A/B/C
/D/E/F
/G/H/I

Basically it should decode a path having category ids to readable path format using category names.

How to fix the stored function to make it work?

Does this work? I wrote this up in a hurry on MariaDB so may not be 100% correct, but hoping it should work on MySQL 5.5 as well, or at least only require minor tweaks. It seems to work on your test data.

DELIMITER //
CREATE FUNCTION decode_path (
    `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
  BEGIN
    DECLARE cnt INT UNSIGNED DEFAULT 2;
    DECLARE cat_id INT UNSIGNED;
    DECLARE cat_name VARCHAR(200) DEFAULT '';
    DECLARE decoded_path MEDIUMTEXT;
    SET decoded_path = '';

    WHILE cnt < LENGTH(path_input) - LENGTH(REPLACE(path_input, '/', '')) + 2 DO
      SET cat_id = SUBSTRING_INDEX(SUBSTRING_INDEX(path_input,'/',cnt), '/', -1);
      SELECT `name` INTO cat_name FROM category WHERE id = cat_id;
      SET decoded_path := CONCAT(decoded_path, '/', cat_name);
      SET cnt := cnt + 1;
    END WHILE;
    RETURN decoded_path;
  END
//
DELIMITER ;

Using this, I get:

SELECT path, `name`, decode_path(path) FROM category;
+--------------+-------------------+---------------------------------------------+
| path         | name              | decode_path(path)                           |
+--------------+-------------------+---------------------------------------------+
| /1           | A                 | /A                                          |
| /1/2         | B                 | /A/B                                        |
| /1/2/3       | C                 | /A/B/C                                      |
| /4           | D                 | /D                                          |
| /4/5         | E                 | /D/E                                        |
| /4/5/6       | F                 | /D/E/F                                      |
| /7           | G                 | /G                                          |
| /7/8         | H                 | /G/H                                        |
| /7/8/9       | I                 | /G/H/I                                      |
| /426         | Computers         | /Computers                                  |
| /426/427     | Other accessories | /Computers/Other accessories                |
| /426/427/428 | Laser printers    | /Computers/Other accessories/Laser printers |
+--------------+-------------------+---------------------------------------------+

So to store the result string from the function into a new column named path_long:

UPDATE category SET path_long = decode_path(path);

Tested with MySQL 5.5 on db-fiddle.com here.


Nvarchar to varchar to nvarchar characters conversion I had a table filled with some greek text rows as nvarchar(2000).

Lately, I altered the type of the column to varchar(4000) but I realized that some greek characters were shown as question marks.

So, I tried to change back to nvarchar(4000) in order to fix it as I suppose that the characters' unicode still remains the same.

I am just wondering, is there any way to fix this instead of restoring the backup I created before altering the table?

No, there is no way to "fix" the data because the data is no longer there. When you converted to VARCHAR, the underlying values for each character were changed into the ASCII value for ?. This is not a display issue, those characters are now physically a regular question mark. You will need to do a restore from a backup, unfortunately.

The following example code shows that once a Unicode character is converted to VARCHAR (assuming the Code Page denoted by the Collation does not support that character), that it becomes a regular 'ol question mark, and forever shall it remain as such:

DECLARE @Character NCHAR(1) = NCHAR(0x3525);
SELECT @Character AS [TheCharacter],
       UNICODE(@Character) AS [DecimalCodePoint],
       ASCII(@Character) AS [DecimalValueOfVarchar],
       UNICODE(CONVERT(VARCHAR(5), @Character)) AS [ConvertToVarchar],
       UNICODE(CONVERT(NVARCHAR(5), CONVERT(VARCHAR(5), @Character)))
             AS [ConvertToVarcharAndBack],
       ASCII('?') AS [VarcharQuestionMark],
       UNICODE(N'?') AS [UnicodeQuestionMark];

-- 㔥    13605   63  63  63  63  63

The following example shows an instance of a Unicode character that is highly doubtful (at least at this time) to be supported in most fonts, hence it appears as a square box, but the UNICODE built-n function shows that the underlying code is still the correct Unicode Code Point:

SELECT NCHAR(0xABBF), N'ꮿ', UNICODE(N'ꮿ');

-- ꮿ    ꮿ   43967

The actual character can be seen here: Cherokee Small Letter YA U+ABBF. This is a display issue, and many characters that are not represented in various fonts will display in the same manner without altering the actual value of the character, but they are still distinct characters.


Distribute records on different MySQL databases - MySQL Proxy alternative My scenario is the following:

Right now I am using one big MySQL database with multiple tables to store user data. Many tables contain auto increment columns.

I would like to split this into 2 or more databases. The distribution should be done by user_id and is determined (cannot be randomized). E.g. user 1 and 2 should be on database1, user 3 on database2, user 4 on database3.
Since I don't want to change my whole frontend, I would like to still use one db adapter and kind of add a layer between the query generation (frontend) and the query execution (on the right database). This layer should distribute the queries to the right database based on the user_id.

I have found MySQL Proxy which sounds exactly like what I need. Unfortunately, it's in alpha and not recommended to be used in a production environment.
For php there is MySQL Native Driver Plugin API which sounds promising but then I need a layer that supports at least php and java.

Is there any other way I can achieve my objectives? Thanks!

You can take a look into MySQL Fabric (Official Doc) but it requires more db server

I have tried this tool only in R&D env for testing a basic HA

It supports some sharding scenarios

Here some high level pros and cons

Pros:

setup
sharding
write/read
basic HA

Cons:

fabric node is the SPOF
no multi master
rewrite software in order to use the fabric driver


Postgres replication views I see some discrepancies between two views in postgres as to replication and lag time.

By this query, clearly replication is up:

9.5.6.11

postgres=# select client_addr, sent_location, replay_location from pg_stat_replication where application_name = 'walreceiver';
  client_addr   | sent_location | replay_location
----------------+---------------+-----------------
 172.yyy.yyy.213  | 1C/2B5732F8   | 1C/2B5732F8
 10.xxx.xxx.195 | 1C/2B5732F8   | 1C/2B5732F8
 10.xxx.xxx.196 | 1C/2B5732F8   | 1C/2B5732F8

10.1

postgres=# select client_addr, sent_lsn, replay_lsn from pg_stat_replication where application_name = 'walreceiver';
 client_addr  |  sent_lsn  | replay_lsn
--------------+------------+------------
 10.qqq.qqq.44 | 1/8C000140 | 1/8C000140

But when look for the lag time:

9.5.6.11

postgres-# SELECT pg_last_xlog_receive_location() ,  pg_last_xlog_replay_location() ,  now() ,  pg_last_xact_replay_timestamp();
 pg_last_xlog_receive_location | pg_last_xlog_replay_location |              now              | pg_last_xact_replay_timestamp
-------------------------------+------------------------------+-------------------------------+-------------------------------
                               |                              | 2018-05-10 04:19:38.079161-04 |

10.1

postgres=# SELECT pg_last_wal_receive_lsn() ,  pg_last_wal_replay_lsn() ,  now() ,  pg_last_xact_replay_timestamp();
 pg_last_wal_receive_lsn | pg_last_wal_replay_lsn |              now              | pg_last_xact_replay_timestamp
-------------------------+------------------------+-------------------------------+-------------------------------
                         |                        | 2018-05-10 04:29:21.730089-04 |

There are no info..
What am I missing?

It looks like you are running the 2nd set of queries on the master.  Those functions need to be run on the replica, not the master.


How can I get the last second of the last month as a default parameter value? I am trying to get the last second of the last month as a default value for a parameter. I can get the last day of the last month from this query on social.msdn.microsoft.com

  last month last day expression: DateValue(DateAdd("D",-1,DateAdd("D",-(Day(Now)-1),Now)))

The about would return 12am of the last day of the month. Starting with that I made one change so that I can get the last second of the last month.

=DateValue(DateAdd("S",-1,DateAdd("D",-(Day(Now)-1),Now)))

That does not work though as the time is still set to midnight.

How do I get the last second of the last month as a datetime value?

This has no affect on underlying queries that deal with report data. This is purely about showing an end user the start and end dates in a report header. "9/1/2018 00:00:00 to 9/30/2018 11:59:59"   "9/1/2018 00:00:00 to 10/1/2018 00:00:00". The former example to my end users is clearer that the data is in the month where as seeing number for the next month (like in the second example) is ... unsettling

They key is the placement of DateValue() which:

  Returns a Date value containing the date information represented by a string, with the time information set to midnight (00:00:00).

The problem with where you are removing seconds is inside the datevalue() function which is dropping the time portion.

It is still important as you want time removed from the initial calculation for getting the last day of the month.

A little shifting around get the desired output.

=DateAdd(DateInterval.Second,-1,DateValue(DateAdd(DateInterval.Day,-(Day(Now)-1),Now)))

or

=DateAdd("s",-1,DateValue(DateAdd("d",-(Day(Now)-1),Now)))


How do I return a json object of my database table I'm creating a web client that uses data from my database to plot markers on a google map. So my collumns are id, name, address, lat, lon, description. My client gets the data from a route in my restful server.

I am currently using flask in my python server.  This is the current code on my route on my server.

@testRestServer.route("/dbMap", methods=['GET', 'POST'])
def getData():
    cursor.execute("SELECT * FROM markers ")
    return jsonify(cursor.fetchall())

This is the output of the json i get, however i'm unable to manipulate it correclty to work with my google maps code as it has none of the headers as reference.

[
  [
    1,
    "Test1",
    "york",
    53.95734,
    -1.086833,
    "test1"
  ],
  [
    2,
    "Test2",
    "york",
    53.961533,
    -1.085244,
    "test2"
  ],
  [
    3,
    "Test3",
    "york",
    53.963051,
    -1.078202,
    "test3"
  ],
  [
    4,
    "Test4",
    "york",
    53.963104,
    -1.082185,
    "test4"
  ]
   ]

I'm not sure why its not outputting like this and not with the column names like this which i believe is what i need to be doing.

[
{"name": "test1"
 "address": "york"
  "lat" : "53.963104"}
]

I still need to work out if its best to return the column header as a string or key but if i can try either option and see which works best.
Any assistance on how I could return an output like the latter would be greatly appreciated.

Many thanks

This is the latest code with the error TypeError: 'int' object has no attribute 'getitem'

@testRestServer.route("/dbMap", methods=['GET', 'POST'])
def getData():
    #    cursor.execute("SELECT * FROM locations ")
    #    return jsonify(cursor.fetchall())

    records = cursor.execute("SELECT * FROM locations ")
    payload = []
    content = {}
    for result in range(records):
        content = {'id': result[0], 'name': result[1], 'address': result[2], 'lat': result[3], 'lon': result[4], 'description': result[5]}
        payload.append(content)
        return jsonify(payload)

So with a bit of hunting and adapting, i resolved the issue with a different method.  I'll post it here incase someone else has an issue similar to this in the future.

The completed route in the server to get the json working correctly is

@testRestServer.route("/dbMap", methods=['GET', 'POST'])
def getData():

cursor.execute('''SELECT * FROM locations ''')
row_headers = [x[0] for x in cursor.description]  # this will extract row headers
rv = cursor.fetchall()
json_data = []
for result in rv:
    json_data.append(dict(zip(row_headers, result)))
return json.dumps(json_data)


Replication on MySQL server I had to stop the slave server to test something. After I started the server again there is a problem with replication on MySQL server

On the problematic server

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: servera
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: servera-bin.000024
          Read_Master_Log_Pos: 808459481
               Relay_Log_File: serverb-relay-bin.000071
                Relay_Log_Pos: 4
        Relay_Master_Log_File: servera-bin.000024
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 808459481
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1129
                Last_IO_Error: error connecting to master 'replica@servera:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:

on the problematic server:

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| root    | serverb   |
| root    | 127.0.0.1 |
| replica | servera   |
| replica | serverb   |
+---------+-----------+

on the main server:

SELECT user, host FROM mysql.user WHERE Repl_slave_priv = 'Y';
+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| root    | servera   |
| root    | 127.0.0.1 |
| replica | servera   |
| replica | serverb   |
+---------+-----------+

according to what I've read, there is a need to execute the following command om the main server:

mysql> FLUSH HOSTS;

What will happen then? if there is any application connected to it - will it disconnect it also?

If your error is

ERROR 1129 (HY000): Host 'serverb' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

you can run a FLUSH HOSTS on master, it will flush the list of blocked host which exceed the number or fail connections attempts to your mysql master

It won't affect any running connection, it will only clean the list of blocked hosts.


Get distinct results based on column and satisfy condition I have the below select which is selecting from a table with the columns:

id, project_id, keyword_id, position,
Data example:

i, p_id, k_id, p
1,  1,    1,   4
2,  1,    1,   5
3,  1,    1,   6
5,  1,    2,   7
6,  1,    2,   8
7,  1,    3,   5
8,  1,    3,   7

QUERY

SELECT
  pr.project_id,
  COUNT(DISTINCT pr.keyword_id) as total_keywords,
  COUNT(CASE WHEN position BETWEEN 4 AND 10 THEN position ELSE NULL END ) AS pos4,
  date(pr.created_at) AS created_at
FROM
  project_reports pr
GROUP BY pr.project_id, date(created_at)

I only want to get the number of distinct keyword_id in pos 4-10. But using my SELECT I get all of them.

Expected Data example:

p_id, total_keywords, pos4
1,      3,            3

My current Data output

p_id, total_keywords, pos4
1,      3,             7

If you want the distinct keywords then you need to count the keyword_id, not the position column.

So your expression:

COUNT(CASE WHEN position BETWEEN 4 AND 10 THEN position ELSE NULL END )

becomes:

COUNT(distinct CASE WHEN position BETWEEN 4 AND 10 THEN keyword_id ELSE NULL END )

So the final statement is:

SELECT
  pr.project_id,
  COUNT(DISTINCT pr.keyword_id) as total_keywords,
  count(distinct CASE WHEN position BETWEEN 4 AND 10 THEN keyword_id ELSE NULL END ) AS pos4
FROM
  project_reports pr
GROUP BY pr.project_id;

SQLFiddle example: http://sqlfiddle.com/#!15/b1015/1


convert date to timestamp and store into table using trigger mysql Using trigger I convert DateTime into a timestamp and then store into another table. But somehow convert date into timestamp always affected by  timezone so that's why I had use convert_tz of date and then try to store in the database but it's not working for me.

TRIGGER :

DELIMITER $$
CREATE TRIGGER `insert_cdrs_data` AFTER INSERT ON `cdrs` FOR EACH ROW
BEGIN
        insert into cdrs_data1(`id`,`timestamp`,`accountid`,`type`,`calldate`) VALUES (new.id,UNIX_TIMESTAMP(CONVERT_TZ(new.calldate, @@global.time_zone, '+0:00')),new.accountid,new.type,new.calldate);
    insert into cdrs_data2(`id`,`timestamp`,`accountid`,`type`,`calldate`) VALUES (new.id,UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,'+0:00',@@global.time_zone)),new.accountid,new.type,new.calldate);
END;
$$
DELIMITER ;

Here we can see that for table cdrs_data1
 UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,@@global.time_zone,'+0:00')

For the table: cdrs_data2

UNIX_TIMESTAMP(CONVERT_TZ(new.calldate,'+0:00',@@global.time_zone)

OUTPUT :

cdr_data 1 :

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='44';
    +---------------------+------------+--------------------+---------------------+
    | calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
    +---------------------+------------+--------------------+---------------------+
    | 2019-05-25 07:49:53 | 1558763393 | +00:00             | +00:00              |
    +---------------------+------------+--------------------+---------------------+

cdr_data2:

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='44';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 07:49:53 | 1558763393 | +00:00             | +00:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.01 sec)

Setting global and session timezone into +03+00.

mysql> SET @@session.time_zone='+03:00';SET @@global.time_zone='+03:00';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data1 where id ='45';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 08:26:04 | 1558761964 | +03:00             | +03:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.01 sec)

mysql> select calldate,timestamp, @@global.time_zone, @@session.time_zone from cdrs_data2 where id ='45';
+---------------------+------------+--------------------+---------------------+
| calldate            | timestamp  | @@global.time_zone | @@session.time_zone |
+---------------------+------------+--------------------+---------------------+
| 2019-05-25 08:26:04 | 1558783564 | +03:00             | +03:00              |
+---------------------+------------+--------------------+---------------------+
1 row in set (0.00 sec)

After change database session and global timezone value timestamp value is not correct showing in above queries.

Question :
How to store date into timestamp using trigger in GMT+0:00 without change timezone or in other terms its should not affect by timezone.

Note :
Right now its not possible to restart mysql so i can't set timezone into the my.cnf file.

@@global.time_zone variable

To see what value they are set to

SELECT @@global.time_zone;

To set a value for it use either one:

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone='+00:00';

@@session.time_zone variable

SELECT @@session.time_zone;

To set it use either one:

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

both "@@global.time_zone variable" and "@@session.time_zone variable" might return "SYSTEM" which means that they use the timezone set in "my.cnf".

For timezone names to work (even for default-time-zone) you must setup your timezone information tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

Note: you can not do this as it will return NULL:

SELECT
CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime`
FROM `table_name`


What's the maximum number of characters you can copy from a cell If you ctrl + c a cell how many characters do you actually copy?
I'm getting 43679 I was wondering if this is the limit of ctrl c? is this configurable? the real length of the cell is around 160k

varchar(max) column
MS SQL 2016 ver 13.0.16106.4
Query Results -> SQL Server -> Results to Grid -> default 65k and 2mb xml

The maximum number of characters you can render in most SSMS GUI elements is 43,679 (as this search reveals). If you want to copy more, you'll need to either:

extract it to a file
use one of the workarounds here
display it in the output grid in a different way, e.g. by first converting it to XML.


Searching in Array performance? We have a table of

id|school_id|parent_ids

where parent_ids is an array of ids.

If we don't have the school_id and only parent_id to search for, then the query will search through all the table rows in parent_ids array, there might be thousands of rows, and parent_id might actually be within just few of them.

Does using IN in query for the array column could be a performance barrier in this case?

EDIT

Here is the dump of table structure:

-- ----------------------------
-- Table structure for schools_messages
-- ----------------------------
DROP TABLE IF EXISTS "public"."schools_messages";
CREATE TABLE "public"."schools_messages" (
  "id" int4 NOT NULL DEFAULT nextval('schools_messages_id_seq'::regclass),
  "message" jsonb NOT NULL DEFAULT '[]'::jsonb,
  "details" jsonb NOT NULL DEFAULT '[]'::jsonb,
  "school_id" int4 NOT NULL,
  "created_at" timestamp(0),
  "updated_at" timestamp(0),
  "parents_ids" int4[] DEFAULT ARRAY[]::integer[]
)
;
ALTER TABLE "public"."schools_messages" OWNER TO "prod_schools";

-- ----------------------------
-- Primary Key structure for table schools_messages
-- ----------------------------
ALTER TABLE "public"."schools_messages" ADD CONSTRAINT "schools_messages_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table schools_messages
-- ----------------------------
ALTER TABLE "public"."schools_messages" ADD CONSTRAINT "schools_messages_school_id_foreign" FOREIGN KEY ("school_id") REFERENCES "public"."trk_schools" ("id") ON DELETE CASCADE ON UPDATE NO ACTION;

I agree with Jack that your schema needs help. But you can still do this. Here we do this with one index lookup, using two core extensions intarray and btree_gist

CREATE EXTENSION intarray;
CREATE EXTENSION btree_gist;

CREATE INDEX ON public.schools_messages
  USING gist(school_id, parents_ids gist__int_ops);

VACUUM ANALYZE public.schools_messages;

SELECT *
FROM public.schools_messages
WHERE school_id = 42
  OR parent_id @> ARRAY[42];


Create table group sub-folders in Management Studio When I was exploring the master database in SSMS I noticed that under the Tables folder there is another folder called Systems Tables that houses a bunch of tables. Is it possible for us to create a structure akin to Systems Tables within our database?

I am looking to organize tables and stored procedures into project specific folders. Under the new setup - when I am referring to my table object I would have to use the following syntax (I am guessing here):

[dbName].[projectName].[dbo].[tableName]

Also, apart from clearing up the clutter, do anybody foresee any performance improvement/degradation because of this re-organization? I use Microsoft SQL Server 2008 R2.

There is no way to create custom folders directly in SQL Server Management Studio.

Here are a few common strategies:

Group database objects by schema to help one-dimensional organization
Name objects from broad to specific areas

For the 1st option:

billing.Product
billing.Receipt
internal.Employee
internal.Office

Schemas are most primarily used to logical grouping of database objects, and can also provide an extra layer of security when using more than just the out-of-the-box configuration.  And the secondary benefit is, of course, the grouping of these items within SSMS Object Explorer.

For the 2nd route (name objects from broad to specific), it'd be something like this:

dbo.Animal
dbo.AnimalDiet
dbo.AnimalDietFootType
dbo.AnimalHabitat
dbo.Person
dbo.PersonAddress
dbo.PersonAutomobile
dbo.PersonAutomobileArchive

So on an so forth.  You can see that these objects alphabetically order themselves.  It's not just good for visual organization, but a great naming convention in my opinion.


High usage of CPU Without use I'm trying to figure out what is happening but at this point I cannot troubleshoot this issue...

I have 2 servers with same specs, actually also with almost same users connected (Only me)...

One of the servers is stable at 20% CPU usage while the other is at 1%... Same Databases, same Processes...

How I can know what is actually consuming this high amount of CPU on SQL Server compared to the other server if both of them are idle?

First of all you should find out, which service/process consuming maximum CPU.

You can you Process Monitor tool provided by Microsoft, which give details information.

You can find it here https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx

If it's SQL Server, then

1st I would recommend to use master.dbo.sysprocess as

select * from master.dbo.sysprocesses order by cpu desc

if it returns spid < 50 on top means, it's SQL Server own process not user process. So, on the basis of lastwaittype column value you can change recommended (according the wait type) SQL Server configuration or tune your HW.

If it returns process id >50 means it's user process and you can use below query to find details about the process and tune your query.

if the session is active (running, runable, suspanded)

select
            db_name(sp.dbid),sp.spid,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
            ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
            ,es.status,es.cpu_time,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
            ,es.reads,es.writes,es.logical_reads

from
                sys.dm_exec_requests er
inner join      master.dbo.sysprocesses sp
on              er.session_id=sp.spid
inner join      sys.dm_exec_connections ec
on              er.session_id=ec.session_id
inner join      sys.dm_exec_sessions es
on              ec.session_id=es.session_id
inner join      sys.dm_exec_query_memory_grants qmg
on er.session_id=qmg.session_id
cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp

If the session is not active (sleeping).

select a.spid,db_name(b.dbid) [DB Name],b.text [Query],a.cpu
from master.dbo.sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle) b where status='sleeping'

Thanks


Convert date yyyy-mm-dd to integer YYYYMM How can I convert @dateb:

SET @dateb = dateadd(month, datediff(month, 0, getdate()) - 3, 0)

that returns 2014-04-04 as date to an integer of 201404

Thanks

On version 2012 or higher you can use the format function to get just year and month, then cast it as an int.

On versions prior to 2012 you can do the formatting with the convert function, then cast as int.

declare @dateb datetime
set @dateb = getdate()

select cast(format(@dateb,'yyyyMM') as int) --2012 or higher
select cast(convert(varchar(6),@dateb,112) as int) -- all versions


Design rules to make database easier to maintain I'm designing a small system that includes an API and a Database.  On the API side I'm familiar with versioning the functions (Endpoints) to allow older clients to continue working.

I'm looking for advice to achieve the same effect for the database.  Is there a way to "version" the database tables (or schemas), so to speak, to allow older applications to continue working?  In this case the that will be the older versions of the API service that I'm maintaining to support older clients.

One thought that occurred to me is "create a versioned view for every table", so for example if the table is called account I would have a view called account_v1.

I obviously don't have a problem with adding new tables or even adding columns to tables (as long as they have default values or allow Null)

The biggest problem comes when splitting a table into multiple tables or when dropping columns.

A simple example would be taking a user's email address out of the user table because in version 2 you want to allow the user to have multiple email addresses.  I'm quite new to views and triggers but I think you can set it up so that the older application still sees the email address in the table, and writes the email address to the new separate table.

I'm finding it terribly inconvenient to maintain views for every table, but this may be because I'm new to it and it will get easier?  Or are there perhaps some other rules or methods that makes it easier to upgrade and maintain older versions so that older version of the application doesn't break?  Is there an easy way to generate the views and triggers automatically because the initial View would basically mirror the table at that time.  Later when the table is modified, I would then have to hand-fix the view(s) associated with that table to maintain a consistent view.  And it seems to me that View version N-2 should refer to View version N-1 as it's base, with View version N-1 referring to View version N.  View version N refers initially to the raw table but when the table is changed that view will be re-written.

FWIW I'm using Postgres 9.5

It really is terribly inconvenient.

Some RDBMSes make it a bit easier with things like synonyms, more flexible changes to views, etc. But it's still a massive pain to do the sort of thing you're describing.

PostgreSQL doesn't let you do much to the underlying tables without dropping and re-creating the views and every object that depends on them, which makes them very inconvenient.

Your model of nested views will also quickly get a bit costly in query planner time. Plus you'll land up with a big search_path and lots of schemas quite quickly if you use a versioned schema approach. Or if you create name-versioned wrapper views for every DB object with every schema change you'll land up with huge numbers of database objects.

Not only that, but dealing with multiple values, upserts, concurrency and locking correctly when you're going via updatable views with rules can get hairy. Take your email address example: you have a row with an email address already set. A v1 client tries to clear it (set it to null); meanwhile a v2 client tries INSERT a second email address into the separate addresses table. What do you do? There are related problems with upserts and similar. It gets ugly fast.

Not to mention that you'll land up with kludges a lot, like having to aggregate lists of email addrs into a comma separated field, then de-aggregate them when UPDATEd, compare to the aggregated current table contents, and do a merge operation to add/delete email addresses. All while ensuring no concurrent operations occur on the same data. Doing this with triggers and rules is fun at all and very prone to deadlocks.

So personally ... I wouldn't bother. The consumer of the database is your API service. You can handle the change abstraction and versioning there to a large extent, and that's just what I suggest you do. You probably have a data access layer in the application that can help abstract some of it, but to a large extent it's about updating things so that the new code exposes the old APIs remapped onto the new backend, so you're not maintaining multiple versions of everything.

People who do try to maintain a consistent database "API" usually do so via stored procedures (or in PostgreSQL, functions). This has some ugly performance consequences, especially since in PostgreSQL non-inlined function results are fully materialised before being returned. It's basically the same approach as a web API anyway; expressing operations on the data as the interface rather than manipulating the data structure directly. If you have a web API there's no need to duplicate this at the DBMS level as well.


Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit? In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,

CREATE TABLE foo ( a int );
INSERT INTO foo VALUES (1),(2),(3);

Followed by,

BEGIN;
  ALTER TABLE foo ADD COLUMN b varchar;
  UPDATE foo SET b = CAST(a AS varchar);
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

However, that same thing in Microsoft's SQL Server seems to generate an error. Compare this working db fiddle, where the ADD (column) command is outside of the transaction,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
COMMIT;

-- txn2
BEGIN TRANSACTION;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

to this db fiddle which doesn't work,

-- txn1
BEGIN TRANSACTION;
  ALTER TABLE foo ADD b varchar;
  UPDATE foo SET b = CAST( a AS varchar );
  ALTER TABLE foo DROP COLUMN a;
COMMIT;

But instead errors

Msg 207 Level 16 State 1 Line 2
Invalid column name 'b'.

Is there anyway to make this transaction visible, with regard to DDL, behave like PostgreSQL?

Generally speaking, no. SQL Server compiles the whole batch at the current scope before execution so referenced entities have to exist (statement-level recompilations may also happen later). The main exception is Deferred Name Resolution but that applies to tables, not columns:

  Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

Common workarounds involve dynamic code (as in Joe's answer), or separating the DML and DDL into separate batches.

For this specific case you could also write:

BEGIN TRANSACTION;

    ALTER TABLE dbo.foo
        ALTER COLUMN a varchar(11) NOT NULL
        WITH (ONLINE = ON);

    EXECUTE sys.sp_rename
        @objname = N'dbo.foo.a',
        @newname = N'b',
        @objtype = 'COLUMN';

COMMIT TRANSACTION;

You still will not be able to access the renamed column b in the same batch and scope, but it does get the job done.

With regard to SQL Server, there is a school of thought that says mixing DDL and DML in a transaction is not a great idea. There have been bugs in the past where doing this has resulted in incorrect logging, and an unrecoverable database. Nevertheless, people do it, especially with temporary tables. It can result in some quite hard-to-follow code.


What is the safe way to update production data if data found inconsistent I am in a small company and require to fix a bunch of data in production which are inconsistent.  I have written the script to handle the fix.

I understand that writing sql to fix data is way more risky than working on fixes normal front end or backend code. For front end or back end, we can write thousands of test code to automatically test result or even little bug the affect might not be as dramatic.

How do you guys fix the data in production normally if many data was inconsistent? Are there any method or strategy which can reduce the risk

How do you guys fix the data in production

Very very carefully!

This may be too general a question: there are many variables involved regarding data size, error severity, how active that data is, what service level agreements you have with the client, ...

Some tips:

Test the scripts in your dev/test environments as extensively as time allows.
If you can, take a copy of the production system to test the scripts on. You probably need to do this on production services if your application handles PPI or other sensitive data.
If you have an upcoming maintenance window when you can offline the production environment to test and apply the change (and rollback the DB if something goes wrong).
If there is time in that window take a fresh copy of production database to retest before applying to production proper.
Take a backup of production, so you can rollback if the update fails and makes more mess.
Apply the change to production and test that the result is as desired.
If all is well, drop the extra test copies and turn production back on, if not rollback to your backups and try again...

Of course if the data fix needs to be made more urgently, you may need to be much less cautious than the above list.


Modify table constraint in PostgreSQL Say I have a constraint

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

that needs to be modified so that zipcode length is 6.

Do I have the drop the constraint zipchk first and then recreate it ?

ALTER TABLE distributors DROP CONSTRAINT zipchk;
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);

or is there a MODIFY (like) command :

ALTER TABLE distributors MODIFY CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);

To sum up the comments:

Like @ypercube hinted, you can do it in a single command, which is cheaper and safer:

ALTER TABLE distributors
  DROP CONSTRAINT zipchk
, ADD  CONSTRAINT zipchk CHECK (length(zipcode) = 6);

ALTER CONSTRAINT in Postgres 9.4 or later (like you found) can only change the "deferability" of a FK constraints. So not what you are looking for. Other than that, there is no "MODIFY (like) command" for constraints (referring to MySQL's MODIFY I assume).

Details in the manual for ALTER TABLE, like @a_horse mentioned.


PostgreSQL: 8.3/8.4 interface chages? I would like to upgrade a PostgreSQL 8.3 server to PostrgreSQL 8.4. There are many software components that interact with the server over the network, and I am concerned with compatibility issues.

Is there any interface change between the different versions of PostgreSQL, as far as an external component is concerned?

Unfortunately, without knowing your system (inside and out), it's going to be difficult for anyone to tell you how an upgrade is going to impact you.

I'd recommend checking the release notes to verify that the upgrade path won't mess up the application.

Here are a couple changes that I noticed that you might need to know about:

Remove ipcclean utility command
Changes in the TRUNCATE and LOCK statements
SELECT DISTINCT and UNION/INTERSECT/EXCEPT no longer always produce sorted output
Disallow LOCK TABLE outside a transaction block
Remove support for the (insecure) crypt authentication method
Modify the ALTER TABLE syntax to allow all reasonable combinations for tables, indexes, sequences, and views

I'd recommend trying this on a test machine first (if possible).


Would Mongo's ObjectID work well in InnoDB's clustered index? Mongo's ObjectID is defined like this:

  ObjectId is a 12-byte BSON type, constructed using:

  a 4-byte value representing the seconds since the Unix epoch,

  a 3-byte machine identifier,

  a 2-byte process id,

  and a 3-byte counter, starting with a random value.

Assuming the PRIMARY key was BINARY(12), will this work as well as an auto incremented INT? Does it count as sequential for InnoDB's clustered index? Would gaps in the timestamp be a problem?

I would believe that it won't work as well and here's why.

it's larger then a int (4 bytes) or bigint (8 bytes). Remember, every other index references the primary key and so it bloats the other indexes. This might not be a concern, but it's something to be aware of.
While the timestamp is sequential, the machine id, process id, and random value are not always going to be, so anything that happens in the same second isn't going to always be in ascending order.

So in conclusion, I don't believe that the Mongo ObjectID is going to perform as well as a smaller, ascending key.

That said, you still can use it as a primary key. It doesn't need to be ascending, it just means that you might have index leaf splits on inserts, which if they're close enough to the end, won't be overly performance impacting. And you can always rebuild the table to compact the indexs.

So, more about the leaf splits. By default, innodb likes a 15/16 fill, leaving 1/16th free for future changes. When the data is inserted in a sequential order, the indexes end up always filled to 15/16.

When the data is inserted randomly/out of order, innodb keeps the pages between 1/2 to 15/16 full, depending on what it believes the end fill rate of the index page will be. This obviously wastes disk space and memory when the index is loaded into memory.


Left outer join to itself to find outliers? I have a table of item types and the coordinates of the items, like this:

    ItemType | X
    --------------
    1        | 1
    1        | 5
    2        | 5
    1        | 6
    2        | 6
    1        | 7
    2        | 7
    2        | 10

I need to find the count of outliers - the items that are not in the vicinity of the items of another type (there may be more than two types, I want to count the outliers for each pair of item types). The vicinity is defined, say, as abs(X1-X2)<=2. That way, the first and the last rows of the table are the outliers.

I tried to run the following query but did not get the expected answer (I expected to get 1 for the pair of 1 and 2 and another one for the pair of 2 and 1).

select t1.ItemType, t2.ItemType, count(t1.ItemType)
from Tab as t1
left outer join Tab as t2 on abs(t1.X-t2.X)<=2
where t1.ItemType!=t2.ItemType
and t2.ItemType is null
group by t1.ItemType, t2.ItemType;

What am I doing wrong? I am using MySQL.

Try the following query.

SELECT t1.ItemType, t2.Itemtype, COUNT(t1.X)
FROM Tab t1, Tab t2
WHERE
    t1.ItemType < t2.ItemType
    AND ABS(t1.X - t2.X) > 2
GROUP BY t1.ItemType, t2.Itemtype
;

It should produce the following results.

+----------+----------+-------------+
| ItemType | ItemType | COUNT(t1.X) |
+----------+----------+-------------+
|     1    |     2    |      7      |
+----------+----------+-------------+


Is this an appropriate development environment MySQL backup strategy? I'm a web developer and I use primarily my own laptop at work. We use an SVN repository for our project codebases, and deploy our changes to an internal development server for testing.

Our system administrator has also implemented an on-site NAS backup, onto which we mirror our most up-to-date sources every few hours automatically.

Given this scenario, is it appropriate for me to simply copy the binary database files from my filesystem to the backup media, rather than to create mysql dumps for each database? The most likely restore scenario would be a complete (rather than partial) reconstruction of my local development environment, anyway.

I would also recommend MySQL dump with an additional twist, place the dump file under version control, at regular intervals, I would recommend when a change is complete. That way you can also track the changes to the database and make reconstruction easier


'ERROR: could not access file "$libdir/postgis-2.5": No such file or directory' Dealing with PostGIS has been a nightmare from day one. Every single time I have to touch it in any way, or update PostgreSQL, it turns into a broken mess. The only reason I use it is because I need to be able to use "geography" columns and a few "st_" functions.

Until a few days ago, I had EnterpriseDB's PostgreSQL 11.5 installed on Windows 10. It had PostGIS installed.
Then I installed EnterpriseDB's PostgreSQL 11.7 to update it. Now it somehow lost PostGIS, so I re-installed that as well. Everything has to be done manually for some reason.

Now, every query related to PostGIS gives this error:

ERROR:  could not access file "$libdir/postgis-2.5": No such file or directory

Even when I try to run:

ALTER EXTENSION postgis UPDATE;

I just get this nonsense:

WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade(); to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;

ERROR:  could not access file "$libdir/postgis-2.5": No such file or directory
CONTEXT:  PL/pgSQL function _postgis_drop_function_if_needed(text,text) line 6 at FOR over SELECT rows
SQL state: 58P01

I don't know why it says 2.5 when I installed postgis-bundle-pg11x64-setup-3.0.1-2.exe.

This doesn't work at all (doesn't exist):

SELECT postgis_extensions_upgrade();

What am I supposed to do?

I've seriously spent so much of my life just fighting with PostGIS that I truly hate it, and wish I could get rid of it forever. Or that they at least could get their act together and integrate it correctly into PostgreSQL so it doesn't lead to all these stupid problems all the time. I don't understand why it has to require so much manual work and fiddling. Why can't it just do these steps automatically?

This was "solved" by installing the 2.5 branch of PostGIS. But everything I said still stands.


Have aggregate function group results in one row I have a table that has the following columns:

debit
credit
payment_method
class

Now I will perform this query:

 select sum(debit - credit), payment_method, class
 from my_table
 group by payment_method, class

And since I have various payment methods and various classes, I will have several rows of the result set.

Now I want to be able to aggregate this result and have the output grouped by class only, for example for a certain class 001 I want to have:
payment_method1, sumofpayment_method1, payment_method2, sumofpayment_method2, payment_method3, sumofpayment_method3 in one row.

Is it possible in Postgres 9.1?

Update:

The table definition is as follows:-

debit numeric(10,2)
credit numeric(10,2)
payment_method varchar(20)
class varchar(25)

You are looking for the crosstab() function provided by the additional module tablefunc.

Then your function could look like this:

SELECT * FROM crosstab(
   'SELECT class, payment_method, sum(debit - credit) AS saldo
    FROM   tbl
    GROUP  BY 1,2
    ORDER  BY 1,2'

   ,$$VALUES
        ('payment_method1'::text), ('sumofpayment_method1')
      , ('payment_method2'), ('sumofpayment_method2')
      , ('payment_method3'), ('sumofpayment_method3')$$
   )
AS ct (class text
     , payment_method1 numeric, sumofpayment_method1 numeric
     , payment_method2 numeric, sumofpayment_method2 numeric
     , payment_method3 numeric, sumofpayment_method3 numeric);

I am using all 6 "types" you mentioned. Well, I see that only half of them are actually intended as type. Either way, substitute your actual types.
More details, links and explanations in this related answer on SO.


Adding restaurants in database I wanna store some vendors (like McDonald ,etc) and it's branches in a table.
a vendor may,or may not have branches.

I have created two tables:

1- A vendors table which have name,logo, etc.

2-A venues table which have location_id, vendor_id, etc.

relationship between venue and vendor is many to one.

suppose I have added a vendor name TastyBurger which is a vendor with lots of branches in all over country.

the other day another store comes up with name TastyBurger which isn't a branch of above vendor. and we create another vendor record for it as well.

the other day the first vendor comes up to save another branch.Now here is the question: How can I determine which vendor record was associated with this?

I made effort to make a real life example to make sense. But if not,just ask to explain more.

As you cannot use the vendor name as identifier, since it may be duplicated (as it happens with the name of a person), you need a true identifier to be set upon creation. When someone identifies themselves in your system, they cannot identify by their name, since it is not enough to uniquely identify them. Thus you don't have any other option than assigning them any proper id when you create the record on your vendors table and identify them by that id.

For this purpose you can create an ad-hoc column (filled in its most simple approach with an auto incremental series), or use a contact email, Tax identifier or any other key that you know is unique, instead of the name.


Blocking Extended events I have very basic table

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)
INSERT INTO [dbo].[Table_1]([Test]) Select 1

sp_configure'block process threshold', 1  --> 1 sec
go

To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session

select * from [dbo].[Table_1]

when i setup extended events, i got below

<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help

This query is run by Management Studio. I have checked this Microsoft SQL Server Management Studio(13.0.11000.78).

In recent releases ability to connect to Azure SQL Database been added to SSMS and looks like SSMS executes this query when ever we change focus to any tab or click in SSMS tab.

So now when we switch table from session 1 (which creates blocking) to session 2, below query is getting executed for the session 1. You can check this by running DBCC InputBudder(<>).

  DECLARE @edition sysname; SET @edition =
  cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition
  = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'

And hence when blocked process report captures event it get this query from Session 1.

Now we may think that why this is not the case for Session 2. But this is not case as while query in session 2 is executing, SSMS not fire above query.


What are the implications of constraint locality in Postgres? I'm a bit confused as to how constraints are managed in Postgres.

The source of this confusion is a diff between the output from two runs of pg_dump (on different boxes) that should be identical. The lone difference between the dumps is a change in default tablespace prior to setting up a number of constraints. To be extra clear here, no tables are created after the change in tablespace, only constraints.

I'm trying to determine if this actually affects anything about the constraints themselves. My main question:

pg_constraint has a conislocal boolean attribute. Does this indicate whether the constraint is stored in the same tablespace as the table with which it is associated?

Some followup questions:

Assuming the prior question is true, if all constraints are local, and the affected dumpfile only changes the tablespace prior to creating the constraints (ie all of the actual tables are in the 'correct' tablespace) will there be any difference between the layout of the database when restoring the two dumpfiles?
Any hazardous guesses as to what could account for the change in default tablespace between the two instances?

Constraint locality has nothing to do with tablespaces; a local constraint is one that is defined on a partition of a partitioned parent table that  itself has no identical constraint -- it is a logical locality.

You don't say what types of constraints you're talking about. PKs and unique constraints will cause creation of missing underlying indexes, which can obviously be affected by the change of the default tablespace.


Combine tables only if no results We have a table FOO that has a huge amount of data in it, most of which is only used infrequently and by (slow) user-interaction.

We would like to move all data that is more than 12 months old to an archive table, FOO_ARCHIVE. That will make the frequent reads and writes into FOO fast enough.

There are many complex stored procedures in our application(s) that currently query against FOO, so we need a way to sensibly combine the two tables when reporting.

I have considered using a union, but I'd rather only look in the archive table if I need to, which is when nothing is in the live table.

SELECT Field1, Field2, Field3, FieldN FROM FOO
UNION ALL
SELECT Field1, Field2, Field3, FieldN FROM FOO_ARCHIVE

However, I'd rather only hit archive if I have to, so perhaps create a stored procedure for this:

SELECT Field1, Field2, Field3, FieldN FROM FOO
IF @@ROWCOUNT = 0
BEGIN
    SELECT Field1, Field2, Field3, FieldN FROM FOO_ARCHIVE
END

But I only want one result set returned, and there can be many conditions applied to the query.

The solution must be supportable on SQL Server 2005, due to client deployments.

You can't say "UNION ALL ONLY IF SOME CONDITION" but you could do:

IF EXISTS (SELECT 1 FROM dbo.FOO)
BEGIN
  SELECT Column1, Column2, ColumnN FROM dbo.FOO;
END
ELSE
BEGIN
  SELECT Column1, Column2, Column3 FROM dbo.FOO_ARCHIVE;
END


SQL Always On COPY ONLY backups - what's the point if I cant restore the AG from these backups? Think I already know the answer to this but I'll ask anyway.

Situation is:

SQL 2012 Always On - 2 synchronous replicas.
7 TB worth of Sharepoint DBs in 1 AG.
Before patching - I want a Full DB backup.
Backup share is on Secondary server B.
Backup from server A to backup share on server B = 12 hours.
Backup from server B to backup share on server B = 6 hours, BUT the backups need to be COPY ONLY.

If I try and restore these and create an AG - it fails.
I need to take ANOTHER Full Db backup = another 6 hours, before I can recreate the AG.

So COPY ONLY backups are useless. Unless I'm missing something?
What's the point of these?

Copy only backups are generally used for situations where you need to restore the data for an unusual reason without disrupting the current backup chain (assuming there is one). Typical reasons are restores to dev boxes to test code or look at an irregularity in the data. You get the idea.

As a side note, taking backups from AG secondaries is kind of a donkey. If the AG is behind at all, your backups are behind. If you're not already using the secondary as a readable replica, they make it an expensive behind-backup device since now you have to fully license it (assuming you have software assurance, and all that).


Best practices when storing logging information to table A lot of applications store logging information in a log table.
These tables are special because you will only do inserts. You will never do updates. If you do deletes it will be a nightly job to get rid of old data.
The table has a datetime field which is typically strictly increasing. It is possible that the calling application will lose its connection to the db, and will stack up inserts, and at the point when it gets the connection it can't guarantee that it will do inserts in strict order. But in general I expect that it will be cheap to sort the table on insert if the datetime field is the clustered index.
Most queries will query on datetime, but these will be inequality queries.
With these special properties it feels like there should be a way to optimize it.
What are the best practices?
An example of a log table could be:
CREATE TABLE logMessages (
    logTime datetime2(6) NOT NULL,
    logSeverity varchar(10) NOT NULL,
    logStatus varchar(10) NOT NULL,
    logText varchar(255),
    processID bigint,
    processUser varchar(25)
)

A typical query:
SELECT logTime, logSeverity, logText
FROM logMessages
WHERE logTime >= '2020-10-01'
AND logTime < '2021-11-01'
AND logSeverity IN ('WARNING','ERROR','FATAL')

Unique id?
The datetime field is not unique, do we need a  unique identifier?
Option 1: add a logId BIGINT IDENTITY column
If we add a unique logId, it will not be used as a foreign key in any other tables.
If we use this as a clustered index alone and SQL Server decides that our query will retrieve too many rows for it to use a non-clustered index, it will do a full table scan.
Would it make sense to set the clustered index to (logTime, logId)? The reason I want this is that when the query optimizer will not use a non-clustered index on logTime because it expects too many rows, falling back on a good clustered index will reduce the number of rows to scan.
Option 2: not adding a logId
Set the clustered index to (logTime). Since logTime is not unique SQL Server will have to uniquify it by adding an invisible column. Is this a problem? At least I get a good clustered index that will help most of my queries.
Lookup tables
logSeverity is a small int with predefined values (DEBUG,INFO,WARNING,ERROR,FATAL), would it be better to move these to a different table and represent them with a tinyint?
Query hint: WITH (NOLOCK)
Experts typically say that you can use WITH (NOLOCK) if you don't need accurate data. Are there any situation where you can use it and still get accurate data? In this case we will not do UPDATE or DELETE, only INSERT. The inserts will also typically be at the end of the table (the datetime field will be increasing). A query that will not look at the last few seconds should not be affected by an insert.
To sum it up:

What should we do about unique key?
How should our clustered index be?
Should we use lookup tables?
Can we use WITH (NOLOCK)?
Any other best practices?

Thank you.

To directly answer each of your questions:
Q: What should we do about unique key?
Q: How should our clustered index be?
A: In most cases it's important to have a primary key so you can guarantee uniqueness (including this case). Only in a staging table it could possibly be an exception to not use one. It may even serve useful for adhoc debugging later on and is cheap to use an INT or BIGINT auto-increment field as one. But you can (and should) still make your logTime column the clustered index on the table for performance reasons, since it's what will be used as a predicate in a lot of your queries, and the uniqifier step SQL Server does is super negligible from a performance perspective. If you do add a LogId as the primary key, you can add a nonclustered index to it. That way if you need to do any adhoc maintenance (e.g. deleting / updating specific records in the table, you can efficiently seek them by the primary key).
Q: Should we use lookup tables?
A: In this case I would recommend using a lookup / "enums" table. Not so much from a space saving or performance perspective, but mainly because if one of those values change you can very efficiently maintain it by only updating the lookup table as opposed to having to update every record referencing that value in the main table.
Q: Can we use WITH (NOLOCK)?
A: It is not possible for you to use the WITH (NO LOCK) query hint here and be guaranteed to always have 100% accurate data, even if you're selecting from a different set of rows than the ones being INSERTED or UPDATED. I've maintained queries in the past that do use it and very rarely have had an issue, but my personal choice (unless data accuracy is not a concern) is to avoid it. There are much better performance improvement alternatives for query tuning. By the way, I once thought the same way about the NOLOCK hint and you can see Microsoft's David Browne's answer to my question for a conclusive reason why the data accuracy isn't guaranteed. In short, the rows you're selecting against may be part of a changing data structure because of the other further away rows that are being INSERTED or UPDATED also being part of that same data structure, e.g. B-Tree for a particular index both rows are a part of.
Q: Any other best practices?
A: Maybe consider other possible ways logs might need to be queried, e.g. all logs by a specific user, specific process, or both (even irrespective of when they occurred)? If so, adding a couple extra nonclustered indexes on those fields could help you later on too. E.g. nonclustered index on processUser, processID and then a separate nonclustered index just on processID (the first index would be covering for predicates that only use processUser, or use processUser and processID which is why you don't need a third nonclustered index that's just on processUser here).


Optimization of a big MySQL weather archive table I have a table that has an archive of daily weather conditions around the globe. There are around 150 million rows and the table is 1.7Gb excluding indexes.

The table is an archive so there are no writes to it. It is a MyISAM table.

Queries are showing up in my slow log as taking up to 45 seconds. These are well cached by the website, but this seems excessive.

CREATE TABLE `archive` (
  `latitude` float NOT NULL,
  `longitude` float NOT NULL,
  `condition` decimal(3,1) NOT NULL,
  `month` tinyint(4) NOT NULL DEFAULT '0',
  `day` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `archive`
  ADD KEY `longitude` (`longitude`),
  ADD KEY `month` (`month`);

I have indexes on longitude and month (as I usually run a monthly average for a location). Just to note - I can't have a primary index on just longitude and latitude as these are all repeated for each day and month.

Here is an example of one of the slow queries (actually the only query type):

SELECT AVG(condition) AS average, MIN(condition) AS min_temp, MAX(condition) AS max_temp FROM `archive` WHERE `latitude`=42.75 AND `longitude`=132.75 AND `month`=4;

Server info:

64Gb RAM
key_buffer_size= 256M

Any thoughts on how I can speed things up? I have considered breaking the table up but am not sure on the best way forward.

Thanks.

Too slow and there is a better index?  No brainer.  Add the index.  But...  If you already have INDEX(x) drop it as you add any index starting with x.  (I'm thinking of longitude and RMathis' comment.

You are likely to have trouble with things like latitude=42.75.  latitude is FLOAT; 42.75 is (I think) DOUBLE.  In this case, 42.75 can be represented exactly in both FLOAT and DOUBLE.  But 42.76 cannot, hence it may not find a match due to roundoff.

A pair of floats consumes 8 bytes.  See this for other representation choices.  For weather stations that are no closer than a mile, DECIMAL(4,2)/(5,2) would save space (5 bytes instead of 8) and avoid the potential roundoff problem.

You mentioned "one of the slow queries".  To best serve you, let's see all the frequent queries, even those that are not slow now.  Then we can suggest a set of indexes to best handle all of them.

(When discussing performance issues, please provide SHOW CREATE TABLE; there are several details missing from what you provided.)

How much RAM do you have?  What is the value of key_buffer_size?  (Recommend 20% of available RAM.)  If you have a tiny RAM, then, I will impart some other advice.


Postgresql: How to select records by column and jsonb array column I have table that look like this:
id           device_name      user_id     device_subname
(varchar)    (varchar)        (int)       (jsonb)
---          -------------    ---------   -----------------
X2           wall lamp        1235        [{"name": "rear light", "commands":"light_v1"}]
Z6           rear light       1235        []
H4           rear light       1235        []
1C           speakers         1476        []
V4           wall socket      1523        [{"name": "socket", "commands":"socket_v2"}]

How to get records with user_id = 1235 AND (device_name = 'rear light' OR device_subname->>name = 'rear light')?
I've come to this solution:
SELECT DISTINCT
  device.id, device.device_name, device.device_subname
FROM
  devices AS device,
  jsonb_array_elements(device.device_subname) AS "deviceSubname"
WHERE
  device.user_id = '1235'
  AND (
    device.device_name = 'rear light'
    OR
    "deviceSubname"->>'name' = 'rear light'
  )

Above query is resulting:
id      device_name      user_id     device_subname
---     -------------    ---------   -----------------
X2      wall lamp        1235        [{"name": "rear light", "commands":"light_v1"}]

What i want is to get this:
id      device_name      user_id     device_subname
---     -------------    ---------   -----------------
X2      wall lamp        1235        [{"name": "rear light", "commands":"light_v1"}]
Z6      rear light       1235        []
H4      rear light       1235        []

It's look like jsonb_array_elemenst() is skip records if the device_subname column is an empty array. How to do this query?

You can use the @> operator to check if the array contains a specific element. There is no need to use jsonb_array_elements
SELECT dv.id, dv.device_name, dv.device_subname
FROM devices AS dv
WHERE dv.user_id = 1235
  and (dv.device_name = 'rear light'
       or dv.device_subname @> '[{"name": "rear light"}]')

Your cross join is essentially an "inner join" so for rows that have no array elements, the row from the base table is not included.
You would have to use an outer join against the array elements if you really wanted to filter in the WHERE clause
SELECT dv.id, dv.device_name, dv.device_subname
FROM devices AS dv
  LEFT JOIN jsonb_array_elements(dv.device_subname) as sub(item) on true
WHERE dv.user_id = 1235
  and (dv.device_name = 'rear light' or sub.item ->> 'name' = 'rear light')

The @> is more efficient though.


Basic Access database help I am trying to create a basic database using access but I'm just learning at the minute.

I have a text file which contains, customer account code (contains duplicates), customer address type, customer name and telephone number.

I have created a table by linking to this source data.  (If I refresh this data in the future will I lose any linked data, if that makes sense?).

I want to be able to add through a form, company registration number and email address.

How do I do this? I know a bit about basic tables and forms but I am struggling to understand relationships.

Thanks in advance,

Dave

I don't have Microsoft Access, but I created a test in MySQL.

My table (freddy) - don't pay attention to the name:

mysql> select * from freddy;
+-------+----------------+
| regno | email          |
+-------+----------------+
|     2 | blah@blah1.com |
|     2 | blah@ah1.com   |
|     2 | blah@ah1.com   |
|     4 | blah@ah1.com   |
+-------+----------------+
4 rows in set (0.00 sec)

Then run the following query:

SELECT regno, email FROM freddy GROUP BY regno, email;

+-------+----------------+
| regno | email          |
+-------+----------------+
|     2 | blah@ah1.com   |
|     2 | blah@blah1.com |
|     4 | blah@ah1.com   |
+-------+----------------+
3 rows in set (0.00 sec)

So, as you can see, the 2 rows 2, blah@ah1.com have been deduplicated. So, you run a similar query on your customer_account_code (GROUP BY - or since you're only selecting on 1 field, you can use DISTINCT) e.g.

mysql> SELECT DISTINCT(regno) FROM freddy;
+-------+
| regno |
+-------+
|     2 |
|     4 |
+-------+
2 rows in set (0.00 sec)

Then set up your form to bring these up and allow your user to add a company_reg and company_email.

However, this is very close to a programming (and not a DBA) question. If you have more trouble with the SQL, then come back here, but otherwise, I think you need an Access and/or Programming forum - StackOverflow perhaps?


Is execution plan cached “better” for stored procedures than for a non-dynamic query? Reading different explanations about execution plan caching by Microsoft SQL Server, I'm confused about the benefits of using stored procedures instead of non-dynamic queries.

By a non-dynamic query I mean a fully parametrized query string which doesn't change through multiple invocations.

As I understand it:

The execution plan is cached both for a stored procedure and an ordinary query.
For a stored procedure, the execution plan is precomputed, which leads to a slight benefit over ordinary queries the first time the stored procedure is invoked.

The sources look rather contradictory to me:

Execution Plan Caching and Reuse article on MSDN doesn't make difference between parametrized queries and stored procedures. The subsections emphasize the importance of parametrized queries in order to make it easy for SQL Server to cache the execution plan.
SQL Server query execution plans – Basics claims the opposite (emphasis mine):

  When it comes to executing ad hoc queries, query plans are created based on complete code, so different parameters or any change in code will prevent reuse of the existing plan.

On DBA.StackExchange, the comment on an answer related to the benefits of stored procedures indicates that parametrized queries have exactly the same effect than stored procedures.

So, in the context where the execution plan is not thrown out of the cache and when, for the sake of experiment, I want to run billions of times a rather complicated query which would benefit from an execution plan and which takes one parameter which changes every time, would there be any benefit in terms of execution plan caching¹ to use stored procedures instead of an ordinary parametrized query?

¹ Outside the scope of the execution plan, there would be minor performance benefits of using a stored procedure, for example in terms of network footprint: passing the name of the stored procedure and its parameters is slightly better than passing the whole query. Those benefits are outside the scope of my question, which is purely about execution plan cache.

The answer is also available as a standalone blog article.

In order to find it out, I did some tests. The goal is to have the same parametrized query executed either directly from C# or by calling a stored procedure and to compare the runtime performance.

I started to create a stored procedure which does a sample query using Adventure Works database:

create procedure Demo
    @minPrice int
as
begin
    set nocount on;

    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[StartDate]
        from [Production].[ProductListPriceHistory] ph2
        where [ph2].[ProductID] = [p].[ProductID]
        order by [ph2].[StartDate] desc
    )
    where [p].[ListPrice] > @minPrice
end

Then, I use the following piece of code to compare the performances:

long RunQuery(SqlConnection connection, int minPrice)
{
    const string Query = @"
    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[StartDate]
        from [Production].[ProductListPriceHistory] ph2
        where [ph2].[ProductID] = [p].[ProductID]
        order by [ph2].[StartDate] desc
    )
    where [p].[ListPrice] > @minPrice
    option (recompile)";

    using (var command = new SqlCommand(Query, connection))
    {
        command.Parameters.AddWithValue("@minPrice", minPrice);
        var stopwatch = Stopwatch.StartNew();
        command.ExecuteNonQuery();
        stopwatch.Stop();
        return stopwatch.ElapsedMilliseconds;
    }
}

long RunStoredProcedure(SqlConnection connection, int minPrice)
{
    using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection))
    {
        command.Parameters.AddWithValue("@minPrice", minPrice);
        var stopwatch = Stopwatch.StartNew();
        command.ExecuteNonQuery();
        stopwatch.Stop();
        return stopwatch.ElapsedMilliseconds;
    }
}

ICollection<long> Execute(Func<SqlConnection, int, long> action)
{
    using (var connection = new SqlConnection("Server=.;Database=AdventureWorks2014;Trusted_Connection=True;"))
    {
        connection.Open();
        using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection))
        {
            command.ExecuteNonQuery();
        }

        return Enumerable.Range(0, 100).Select(i => action(connection, i)).ToList();
    }
}

void Main()
{
    var queries = Execute(RunQuery);
    var storedProcedures = Execute(RunStoredProcedure);

    Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures));
    Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries));
}

Notice option (recompile) and with recompile. This will force SQL Server to discard previously cached execution plans.

Each query is run une hundred times with a different parameter every time. The time spent by the server is measured at client side.

By running DBCC FreeProcCache; DBCC DropCleanbuffers; before gathering metrics, I make sure that all previously cached execution plans are removed.

Running this code gives the following output:

  Stored procedures: 786 ms. Details: 12, 7, 7, 9, 7, 7, 9, 8, 8, 6, 8, 9, 8, 8, 14, 8, 7, 8, 7, 10, 10, 7, 9, 6, 9, 8, 8, 7, 7, 10, 8, 7, 7, 6, 7, 8, 8, 7, 7, 7, 14, 8, 8, 8, 7, 9, 8, 8, 7, 6, 6, 12, 7, 7, 8, 7, 8, 7, 8, 6, 7, 7, 7, 12, 8, 6, 6, 7, 8, 7, 8, 8, 7, 11, 8, 7, 8, 8, 7, 9, 8, 9, 10, 8, 7, 7, 8, 8, 7, 9, 7, 6, 9, 7, 6, 9, 8, 6, 6, 6.
  Queries: 799 ms. Details: 21, 8, 8, 7, 6, 6, 11, 7, 6, 6, 9, 8, 8, 7, 9, 8, 7, 7, 7, 7, 7, 7, 10, 8, 8, 7, 8, 7, 6, 11, 19, 10, 8, 7, 8, 7, 7, 7, 6, 9, 7, 9, 7, 7, 8, 7, 12, 9, 7, 7, 7, 8, 7, 7, 8, 7, 7, 7, 9, 8, 7, 7, 7, 6, 7, 7, 16, 7, 7, 7, 8, 8, 9, 8, 7, 9, 8, 7, 8, 7, 7, 6, 7, 7, 7, 7, 12, 7, 9, 9, 7, 7, 7, 7, 9, 8, 7, 8, 11, 8.

Let's run it again:

  Stored procedures: 763 ms. Details: 11, 8, 10, 8, 8, 14, 10, 6, 7, 7, 6, 7, 7, 9, 6, 6, 6, 8, 6, 6, 7, 6, 8, 7, 16, 8, 7, 8, 9, 7, 7, 8, 7, 7, 11, 10, 7, 6, 7, 8, 7, 7, 7, 7, 7, 7, 10, 9, 9, 7, 6, 7, 6, 7, 7, 6, 6, 6, 6, 6, 10, 9, 10, 7, 6, 6, 6, 6, 6, 8, 7, 6, 6, 7, 8, 9, 7, 8, 7, 10, 7, 7, 7, 6, 7, 6, 7, 11, 13, 8, 7, 10, 9, 8, 8, 7, 8, 7, 7, 7.
  Queries: 752 ms. Details: 25, 10, 8, 8, 12, 8, 7, 9, 9, 8, 6, 7, 7, 6, 8, 6, 7, 7, 8, 9, 7, 7, 7, 7, 6, 10, 8, 7, 7, 7, 7, 7, 7, 7, 8, 9, 7, 6, 6, 6, 7, 13, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 6, 10, 7, 7, 8, 9, 8, 7, 6, 6, 7, 7, 9, 7, 8, 6, 9, 7, 7, 8, 7, 6, 6, 7, 7, 7, 7, 6, 7, 7, 8, 7, 7, 6, 7, 9, 8, 7, 7, 7, 7, 6, 7, 6, 6, 9, 7, 7.

It seems that the performance is very close between stored procedures and direct queries. Running the code a dozen times, I notice that stored procedures seem to be slightly fast, but the gap is very narrow. Possibly passing around the whole query creates this additional cost, which may increase if SQL Server is hosted on a dedicated machine with a slow LAN between it and the application server.

Let's now turn execution plan caching on and see what happens. To do this, I remove option (recompile) and with recompile from the code. Here's the new output:

  Stored procedures: 26 ms. Details: 23, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
  Queries: 15 ms. Details: 14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.

It becomes clear that caching has exactly the same effect for both direct queries and stored procedures. In both cases, it reduces the time to nearly zero milliseconds, and the most expensive query is the first one—the one which runs after the removal of cached execution plans.

Running the same code again shows a similar pattern. Sometimes, queries are faster, and sometimes stored procedures are. But every time, the first query is the most expensive one, and all others are close to zero milliseconds.

Reopening SQL connection

If the SQL connection is opened for every query, such as in this slightly modified code:

long RunQuery(string connectionString, int minPrice)
{
    const string Query = @"
    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[StartDate]
        from [Production].[ProductListPriceHistory] ph2
        where [ph2].[ProductID] = [p].[ProductID]
        order by [ph2].[StartDate] desc
    )
    where [p].[ListPrice] > @minPrice
    option (recompile)";

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(Query, connection))
        {
            command.Parameters.AddWithValue("@minPrice", minPrice);
            var stopwatch = Stopwatch.StartNew();
            command.ExecuteNonQuery();
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }
    }
}

long RunStoredProcedure(string connectionString, int minPrice)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand("exec Demo @minPrice with recompile", connection))
        {
            command.Parameters.AddWithValue("@minPrice", minPrice);
            var stopwatch = Stopwatch.StartNew();
            command.ExecuteNonQuery();
            stopwatch.Stop();
            return stopwatch.ElapsedMilliseconds;
        }
    }
}

ICollection<long> Execute(Func<string, int, long> action)
{
    var connectionString = "Server=.;Database=AdventureWorks2014;Trusted_Connection=True;";
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand("DBCC FreeProcCache; DBCC DropCleanbuffers;", connection))
        {
            command.ExecuteNonQuery();
        }
    }

    return Enumerable.Range(0, 100).Select(i => action(connectionString, i)).ToList();
}

void Main()
{
    var queries = Execute(RunQuery);
    var storedProcedures = Execute(RunStoredProcedure);

    Console.WriteLine("Stored procedures: {0} ms. Details: {1}.", storedProcedures.Sum(), string.Join(", ", storedProcedures));
    Console.WriteLine("Queries: {0} ms. Details: {1}.", queries.Sum(), string.Join(", ", queries));
}

the observed metrics are very similar:

  Stored procedures: 748 ms. Details: 11, 8, 6, 6, 8, 9, 9, 8, 8, 7, 6, 8, 7, 9, 6, 6, 6, 6, 6, 6, 7, 7, 6, 9, 6, 6, 7, 6, 6, 7, 8, 6, 7, 7, 7, 13, 7, 7, 8, 7, 8, 8, 7, 7, 7, 7, 6, 7, 8, 8, 8, 9, 7, 6, 8, 7, 6, 7, 6, 6, 6, 6, 8, 12, 7, 9, 9, 6, 7, 7, 7, 8, 10, 12, 8, 7, 6, 9, 8, 7, 6, 6, 7, 8, 6, 6, 12, 7, 8, 10, 10, 7, 8, 7, 8, 10, 8, 7, 8, 7.
  Queries: 761 ms. Details: 31, 9, 7, 6, 6, 8, 7, 7, 7, 7, 7, 6, 8, 7, 6, 6, 7, 10, 8, 10, 9, 7, 7, 7, 7, 10, 13, 7, 10, 7, 6, 6, 6, 8, 7, 7, 7, 7, 7, 7, 7, 9, 7, 7, 7, 6, 6, 6, 9, 7, 7, 7, 7, 7, 6, 8, 10, 7, 7, 7, 7, 7, 7, 7, 8, 6, 10, 10, 7, 8, 8, 7, 7, 7, 7, 7, 6, 6, 7, 6, 8, 7, 7, 7, 7, 7, 7, 7, 8, 7, 8, 7, 9, 7, 6, 6, 12, 10, 7, 6.

with option (recompile) and with recompile and:

  Stored procedures: 15 ms. Details: 14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.
  Queries: 32 ms. Details: 26, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0.

without.

Under the hood

Let's see what happens under the hood. The following query shows cached execution plans:

select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc

When running this query after executing the stored procedures one hundred times, the result of the query looks like this:

usecounts   size_in_bytes cacheobjtype                                       objtype              text
----------- ------------- -------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100         90112         Compiled Plan                                      Proc                 create procedure Demo
    @minPrice int
as
begin
    set nocount on;

    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[Product
100         16384         Compiled Plan                                      Prepared             (@minPrice int)exec Demo @minPrice --with recompile
1           49152         Compiled Plan                                      Adhoc                --DBCC FreeProcCache
--DBCC DropCleanbuffers

select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc

(3 row(s) affected)

When running the query directly one hundred times, the result is:

usecounts   size_in_bytes cacheobjtype                                       objtype              text
----------- ------------- -------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100         73728         Compiled Plan                                      Prepared             (@minPrice int)
    select top 1 [p].[Name], [p].[ProductNumber], [ph].[ListPrice]
    from [Production].[Product] p
    inner join [Production].[ProductListPriceHistory] ph
    on [p].[ProductID] = ph.[ProductID]
    and ph.[StartDate] =
    (
        select top 1 [ph2].[
1           49152         Compiled Plan                                      Adhoc                --DBCC FreeProcCache
--DBCC DropCleanbuffers

select usecounts, size_in_bytes, cacheobjtype, objtype, text
from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = 'Compiled Plan'
order by usecounts desc

(2 row(s) affected)

Conclusion

The execution plan is cached for stored procedures and direct queries.
The performance between stored procedures and direct queries is very similar when the SQL Server and the application are hosted on the same machine. When SQL Server is hosted on a dedicated server accessed through LAN, using stored procedures may result in better performance.


What is Page Life Expectancy precisely? What is the value returned by Page Life Expectancy, precisely?

The use of the word "will" in this documentation

  Indicates the number of seconds a page will stay in the buffer pool without references.

suggests that the PLE value is a prediction of some kind. Is the prediction based on the actual computed average of the length of time pages now in the buffer pool have been in the buffer pool?

Is the algorithm used for computing PLE available?

PLE is a measure of how long (in seconds), pages are staying in memory.  It is more a measure of the age of the current pages, than a prediction.  But if the current load stays the same you should expect the PLE to stay the same.

In reality, it changes, the value grows every second, until there is a big flush then it shrinks.

Related:

Page Life Expectancy (PLE), where to start?
SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy
How can you have a Buffer cache hit ratio of 100% with PLE of 103?


Compare dates in Oracle SQL I have a table LoanDeadline that has a column called deadline, it's simple text and not Date and it's in the format of

July 25, 2014

How do I compare this with sysdate ?
So far I have

SELECT 'YOUR LOAN IS APPROVED'
FROM LOANDEADLINE LD
WHERE SYSDATE < LD.DEADLINE;

But this doesnt work because my SYSDATE and LD.DEADLINE are different  data types.

WHERE SYSDATE < to_date( LD.DEADLINE, 'Month DD, YYYY' );

would work assuming all your data is perfect and that all your session are configured to use English month names.  I would strongly recommend, however, that you fix the data model.  Storing strings that represent dates rather than actual dates is a terrible practice.  Inevitably, you end up with at least a handful of columns where the data is invalid which would cause this query to fail.  You could work around that by creating a function that attempts to convert the string to a date and catches the exception if there is an error and then use that function in your query, i.e.

CREATE OR REPLACE FUNCTION my_to_date( p_str IN VARCHAR2, p_format IN VARCHAR2 )
  RETURN date
IS
BEGIN
  RETURN to_date( p_str, p_format );
EXCEPTION
  WHEN others
  THEN
    RETURN NULL;
END;

and then

WHERE SYSDATE < my_to_date( LD.DEADLINE, 'Month DD, YYYY' );


MySQL: all stored procedures deleted? Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be truncated as well.

What's even more interesting is that this isn't the case on the replicated slave.

What would cause this?
Why wouldn't it show up on the slave server?
If I copy the files /mysql/proc* from the slave back to the master - will this restore the procedures?

Nearly every file in the /mysql folder was touched at the same time. Some were changed while many were not. My first thought is that it's a failure in the file system but it appears to be localized to this one folder.

EDIT: Pertinent details:

RHEL 5.x
MySQL 5.5

I brought down both MySQL instances and am attempting to get the MySQL folder off of tape. I would run fsck on the volume but the man page has some pretty scary warnings.

The safest course of action is to stop MySQL on both machines and copy the table files you need from the mysql schema from slave to master, saving copies of the files.  As long as they are MyISAM tables (not InnoDB), this is a legitimate operation.

If you can't shut down the machines, then connect to both of them and run FLUSH TABLES WITH READ LOCK; wait for the prompt to return, and then leave the connections open (to hold the locks) while copying the files.  Then UNLOCK TABLES; to release the global read lock.  MyISAM tables are pretty flexible this way... you need to copy the MYD and MYI files for the tables, together in pairs, and you should probably copy the matching .frm files on the off chance that they aren't identical (though they should be)... just save copies of anything you overwrite, and don't try this with InnoDB tables.

This should restore the procedures; you might need to restart the server for them to be recognized by all threads (but I don't think so).

If changes to tables in mysql normally replicate in your setup, that tends to suggest underlying system trouble ... or someone else with access to the system not quite knowing with they are doing ... or a potential security breach, and, unfortunately, none of these things really jumps out to me as more likely than any other, though my instinct is to be "suspicious of malicious."

Since you do have binary logging enabled, you should review the binlogs from around the period of time the timestamps suggest using mysqlbinlog --verbose --base64-output=decode-rows, since even though the slave didn't change its tables, that doesn't mean there couldn't be something interesting in the binlog that changed something else that you haven't yet discovered, or that did not have the same impact on the slave for any number of other reasons, like replicate-ignore-db.

I would check all of the grant table contents along with everything else in the mysql schema.  I would also reconfigure the servers with log_warnings = 2, which logs access denied errors, and I might even be tempted to turn on the general query log for a while if your system has the I/O capacity to support that without performance penalty.  I would further configure the local machine to forward a copy of its syslog messages to another system with tightly restricted access, and thoroughly audit the firewall... all of this is a little bit alarmist-sounding, but without some other evidence of filesystem problems, that certainly can't be considered the most likely thing to have happened.


Transaction on delete is committed, but for a brief time the entries can still be read? I have a Java application that uses Spring JDBC and HikariCP for pooling MySQL connections. There is a high level of concurrency where a thread creates a unique entry for a given task in a single table, and then deletes it when it is finished. Each time a thread is assigned a task it first checks the table to see if that task is already running. It looks to be working, and I'm not getting any deadlocks, but after the application completely exits I noticed I still see entries coming and going from the table (using MySQL Workbench). Eventually they are all deleted successfully. The isolation level is READ_COMMITTED, so I'm not sure why I would be seeing dirty reads.

(I can't answer your question, but maybe the following will eliminate the problem you are seeing.)

For long-running tasks (more than a few seconds), consider this technique:

SET autocommit=1;
UPDATE Tasks SET owner='me' WHERE task='use_car' AND owner IS NULL;
if ROWS_AFFECTED == 0  THEN
     Do something else, or wait, or whatever

START TRANSACTION;
...
COMMIT;

UPDATE Tasks SET owner=NULL WHERE task='use_car';

Notes:

The 'task' stuff is kept outside the transaction.
Each UPDATE is atomic, so a simple autocommit suffices.


SQL Server: Exporting data objects While exporting Database. Say I want to copy a database from one place to another. I can use, e.g.

Insert * From DB1 Into DB2

Where DB2 is the target database. Question: will this insert query also copy all

the data objects: Indexes, triggers, etc.  from DB1 into Db2 ? If not, will

the export Wizard to the job? Anything else?

Thanks.

there is no script that does

Insert * From DB1 Into DB2

The best way to copy objects is through SQL Server Export Wizard and select all the objects you wan to move.

Also another way is through script each object and run the script in new database.


"Sniff" SQL queries being sent out from my computer I'm a student working on pulling certain information from an SQL server here at work. I don't have sysadmin privileges and my attempts at receiving them have not been met with success. It's a big company and the bureaucracy surrounding these things is nothing short of ridiculous, so that isn't an option.

I want to run a query that is already being executed by one of the programs on this computer, but with slightly different parameters (joining a few more tables, etc). The database is vast and incredibly complicated, and I've therefore been unable to understand how this program gets the information it does.

So my question is this: is there any way to monitor the queries being sent from my computer, specifically the query text? It would be tremendously helpful and save me time, energy, and a hell of a lot of red tape.

(I recognize this may seem like a stupid question or that I'm asking the impossible - if it can't be done it's no big deal - I can get by without it. It'd be nice, though.)

You do not require sysadmin privileges for what you want. You can use SQL Profiler and for that the required privilege is ALTER TRACE, see Permissions Required to Run SQL Server Profiler. Ask for the minimum required permission and perhaps will be granted.


Best way to async data inserts into timescaledb Having 3 files with one data set of daily data inside:

2019-09-11.txt
2019-09-12.txt
2019-09-13.txt

Having lines like:

2019-09-11 15:59:37.459802,1
2019-09-11 15:59:38.959802,1
[...]

The files are sorted by a timestamp, which is the main index in the hypertable:

CREATE TABLE IF NOT EXISTS l1(
    timestamp TIMESTAMP(6) NOT NULL,
    data INT

So it is a lot of data...
Is there a way to reach a higher insert performance rate by asyncing the INSERT INTO commands to one insert process per input file/date?

If the files are comma separated or can be converted into CVS, then use Timescale tool to insert data from CVS file in parallel: timescaledb-parallel-copy

A manual approach to insert data into hypertable can be to create several sessions of PostgreSQL, e.g., by executing psql my_database in several command prompts and insert data from different files into the same hyperatble. It is important that parallel loading will insert data into different chunks. For example, if hypertable is created with default chunks size, one chunk will fit one week of data. Thus different parallel sessions should read data from files belonging to different weeks. Otherwise, parallel execution will not perform well due to conflicts. Also you want to fit active chunks into memory.

In both cases it is good that the data are already sorted by timestamp as mentioned in the question.