Optimizing intersect query in MySQL I'm facing a performances problem in our MariaDB server.

We have a table linking users to pages, a user belongs to 1+ pages.

I have a page that have users and I want to get the 10 best pages based on users they share with the current page (page_id).

Here is the schema :

CREATE TABLE IF NOT EXISTS `pages_users3` (
  `user_id` bigint(20) unsigned NOT NULL,
  `page_id` bigint(20) unsigned NOT NULL,
  `comments` int(11) NOT NULL DEFAULT '0',
  `likes` int(11) NOT NULL DEFAULT '0',
  `shares` int(11) NOT NULL DEFAULT '0',
  `posts` int(11) NOT NULL DEFAULT '0',
  `interactions` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`page_id`,`user_id`) `clustering`=yes,
  KEY `user_page` (`user_id`)
) ENGINE=TokuDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci `compression`='tokudb_zlib' AUTO_INCREMENT=30127543590 ;

I have a query that can retrieve similar pages of a page based on users intersections :

SELECT `pages_users`.`page_id` , COUNT(*) AS count
FROM `pages_users`
LEFT JOIN `pages_users` AS `pu2` ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE `pages_users`.`page_id` != *PAGE_ID*
AND `pu2`.`page_id` = *PAGE_ID* /* To avoid getting the current page in results */
GROUP BY `pages_users`.`page_id`
ORDER BY `count` DESC
LIMIT 10

Unfortunaly this query runs in about 1m30s, so I would like to optimize it...

Here is the result of an explain :

+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
| id   | select_type | table       | type   | possible_keys                                                                     | key                                            | key_len | ref                                    | rows      | Extra                                                     |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
|    1 | SIMPLE      | pages_users | index  | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_interactions_index | 20      | NULL                                   | 165354919 | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | pu2         | eq_ref | pages_users_user_id_page_id_unique,pages_users_user_id_page_id_interactions_index | pages_users_user_id_page_id_unique             | 16      | soprism_prod.pages_users.user_id,const |         1 | Using index                                               |
+------+-------------+-------------+--------+-----------------------------------------------------------------------------------+------------------------------------------------+---------+----------------------------------------+-----------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

I'm requesting your help because I spent many hours trying to optimize that query and I feel confused :(
I'm open to anything, from refactoring to changing server parameters...
Thank you !

Some additional insights :

SELECT COUNT(user_id) FROM `pages_users` WHERE `page_id` = *PAGE_ID*;
+----------------+
| COUNT(user_id) |
+----------------+
|         146132 |
+----------------+
1 row in set (0.08 sec)

SELECT COUNT(user_id) FROM `pages_users`;
+----------------+
| COUNT(user_id) |
+----------------+
|      165354918 |
+----------------+
1 row in set (1 min 0.98 sec)

Hardware : Quad Core Xeon, 32Gb RAM, Raid 1 2x2Tb

Please try:
(edited 2014-12-18: replaced Left with INNER join):

SELECT
    `pages_users`.`page_id` ,
    COUNT(`pu2`.`page_id`) AS `count`
FROM
    `pages_users`
    INNER JOIN `pages_users` AS `pu2`
        ON `pu2`.`user_id` = `pages_users`.`user_id`
           AND `pu2`.`page_id` = *PAGE_ID*
WHERE
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY
    `pages_users`.`page_id`
ORDER BY
    `count` DESC
LIMIT 10

or as an alternative

SELECT
    `pages_users`.`page_id` ,
    COUNT(`pu2`.`pu2_page_id`) AS `count`
FROM
    `pages_users`
    INNER JOIN
    (
        SELECT
            `pu2_intern`.`page_id` AS `pu2_page_id`
        FROM
            `pages_users` AS `pu2_intern`
        WHERE
            `pu2_intern`.`page_id` = *PAGE_ID*
    )  AS `pu2`
        ON `pu2`.`user_id` = `pages_users`.`user_id`
WHERE
    `pages_users`.`page_id` != *PAGE_ID*
GROUP BY
    `pages_users`.`page_id`
ORDER BY
    `count` DESC
LIMIT 10

You could also add another index just on the field page_id:

KEY `page` (`page_id`)

I hope it could help you.


How to duplicate huge postgres table? I have huge postgres table (10GB of data - 160M records). Table is static and there are no write operations on it performed. I want to duplicate it, perform writes, reindex it and then with single fast transaction delete the old one and rename the new one to original name.

What is the fastest way to duplicate such huge table?

Generally the fastest way to duplicate a table is simply:

CREATE TABLE table2 AS SELECT * FROM table1;

Parallel INSERTs may be faster, but only with a very fast disk subsystem (when data is interleaved on many drives). Otherwise this will be slower.

Once you're done with modifying table2, it can take the new name with:

BEGIN;
DROP TABLE table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;

The DROP TABLE command needs an exclusive lock, which affects concurrent readers in a way you may want to anticipate:

DROP will wait for any pending read on the table from other transactions to finish.
Any new transaction attempting to read that table in the meantime will be put it in wait, and then fail since the original table1 no longer exists. The error would look like "could not open relation with OID oid"

To avoid the second issue, you may rename table1  to old_table1  instead of dropping it, and then drop it only later outside of the transaction, when these readers are done with it. So the sequence above would become:

BEGIN;
ALTER TABLE table1 RENAME TO old_table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;
...
DROP TABLE old_table1;

You could you this open source deduplicator — uses JDBC: https://github.com/bmiller1009/deduper


optimize big sql query My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish and product (can be different).

SELECT count(*) as daySum,
    p_name,
    SUM(pp_price) as daySumPrice,
    o_day,
    o_time,
    s_id,
    s_name,
    t_name,
    t_id,
    SUM(ust10) as ust10,
    SUM(ust20) as ust20,
    SUM(sdUst10) as sdUst10,
    SUM(sdUst20) as sdUst20 ,
    fk_p_id,
    u_name
FROM
(
    SELECT tbl_orders.o_id,
        tbl_orders.o_comment,
        DATE_FORMAT( $dateField,  '$sqlDateFormat' ) AS o_day,
        tbl_orders.o_time,
        tbl_orders.o_t_name AS t_name,
        tbl_orders.fk_t_id AS t_id,
        tbl_orders.fk_u_id AS u_id,
        tbl_orders.fk_s_id AS s_id,
        tbl_orders.o_s_name AS s_name,
        tbl_orders.o_u_name AS u_name,
        tbl_orders.fk_p_id,
        CONCAT(tbl_orders.o_p_name,IF(sideDish.p_name IS NULL,'',' ('),IFNULL(GROUP_CONCAT(DISTINCT(sideDish.p_name) SEPARATOR ', '),''),IF(sideDish.p_name IS NULL,'',')'),IF(tbl_orders.o_comment='','',' - ".getLanguageKey('label_comment').": '),tbl_orders.o_comment,IF(tbl_orders.o_comment='','','')) as p_name,
        ROUND(IFNULL(tbl_orders.o_p_price,0)+IFNULL(sideDishPrice.pp_price,0),2) as pp_price,
        IF(tbl_orders.o_p_ust=10,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust10,
        IF(tbl_orders.o_p_ust=20,IFNULL(tbl_orders.o_p_price/(100+tbl_orders.o_p_ust)*tbl_orders.o_p_ust,0),0) AS ust20,
        sideDishPrice.ust10 AS sdUst10,
        sideDishPrice.ust20 AS sdUst20
    FROM tbl_orders
    INNER JOIN tbl_dailyReport dr1
        ON dr1.dr_id = tbl_orders.fk_dr_id
    LEFT JOIN
    (
        SELECT  o2.o_id,
            SUM(IFNULL(o2.o_p_price,0)) as pp_price,o2.o_p_name as p_name,o2.o_parent AS o_parent,
            SUM(IF(o2.o_p_ust=10,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust10,
            SUM(IF(o2.o_p_ust=20,IFNULL(o2.o_p_price/(100+o2.o_p_ust)*o2.o_p_ust,0),0)) AS ust20
        FROM tbl_orders o2
        GROUP BY o_parent
    ) sideDishPrice
        ON tbl_orders.o_id = sideDishPrice.o_parent
    LEFT JOIN
    (
        SELECT o_id,o_p_name AS p_name,o_parent
        FROM tbl_orders
        GROUP BY o_id

        ) sideDish
            ON tbl_orders.o_id = sideDish.o_parent
WHERE
                    tbl_orders.fk_c_id = '$c_id'
                    $stornoNull

                    AND tbl_orders.o_parent IS NULL
                    $onlyDayClosingData
                    $whereClauseTime
                    $whereClauseID
                    $whereClauseStorno
                        GROUP BY  tbl_orders.o_id,tbl_orders.o_p_name,sideDish.o_parent

                    ) products  $groupBy $orderBy;

The query is working and it gets the right numbers but it takes too long.

rows in database: 7000 orders in march: 3500 time to print 6 of these queries with different group by's (date, waiter, table, products, payment method, and cancellations,...): about 30-40 secs.

Imagine how long it would take if we have 10000000 of rows (which could be realistic in a few years?)

Is there any way to improve this performance?

EDIT:
I already solved it using a second table.
As the table tbl_orders is used for the orders itself (recursive, for orders with sidedishes) i just put it joined into a new table tbl_report. There it is possible now for me to group things like i want them with good speed :)

Thank you for your advices. Some of them were helpful though :)

how should i mark my question? solved?

All time we use lot of SQL queries. But we don’t consider about its performance. If we follow some tips then our query will be more efficient. Some of them are.

Use views and stored procedures instead of heavy-duty queries.
Try to use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Try to use UNION ALL statement instead of UNION

For more http://cybarlab.blogspot.com/2013/02/sql-queries-optimization-tips.html

Thanks n regard


How to manage millions of users? I am about to launch something really big. I need to prepare my server and database.

I would like to group each set of 100,000 users in separate user tables but I do not know how to associate one user trying to log in to the appropriate user table.

For instance, how would I know that user jay@mail.com is related to the user table #36?

Would it be the same to have 10 millions users in one user table or 100 of 100,000?

How does Facebook? I can't believe they would have one global user table with 950 million entries.

You're not going to have a billion users tomorrow and MySQL can handle several million rows without any problem. I have 5 million users in my user table and trust me, it isn't even on my radar of things to worry about.

Don't worry about sharding until you need to do it. You are attempting to optimize prematurely for a problem that may or may not ever exist and in the process, you will severely cripple the rate at which you can innovate. Be fast to launch and find the problems as they come. You cannot predict in advance what your scaling challenges will be.

When and if you ever reach this scale, you will then have quite a bit of money and resources to throw at this kind of problem.

why not divide based on the alphabetical range? If you will have millions of users, create a separate table for each letter or for pair of letters (table 'a' for users with username starting with 'a'). It will be much overhead at first but since you are expecting big database and want to be able to distinguish which table should be used for particular user - i guess the alphabetical order is the obvious and easiest choice.


Should I use data type SERIAL for table id columns? I found out that there is a mysql datatype called SERIAL, it appears to be designed for use as an id column. However some of my peers don't think that the real data type BIGINT implied by SERIAL is appropriate for tables that may never get big.

Is there any reason to avoid using SERIAL type for a table id column?

Are there any obvious caveats?

The only real concern would be size.

If a table is really small, why bloat it ? For example, if you have a table will never surpass 255 rows, use TINYINT UNSIGNED for id. No need to bloat the table's column up to 8 times bigger. This would also apply to the primary key.

After loading a table with data, you should run this

SELECT id FROM tablename PROCEDURE ANALYSE();

For any table < 4,294,967,296 rows, PROCEDURE ANALYSE() will never recommend a BIGINT. In terms of byte width, smaller numeric keys are always faster to process than larger numeric keys.

What will be further victimized would be indexes. Why ?

In the Clustered Index (a.k.a. gen_clust_index), row data and BTREE for Primary Key occupy the same space
InnoDB has a fixed size for an InnoDB Page

In light of these two things, the larger the Primary Key, the more space is needed and the more likely extra InnoDB pages are needed.

Therefore, for a populated table, my suggestion would be to let PROCEDURE ANALYSE() tell the correct dataytpe. For an empty table, try to forecast the number of value to expect and this set the data type to the following:

id < POWER(256,1) (256), TINYINT UNSIGNED
id < POWER(256,2) (65536), SMALLINT UNSIGNED
id < POWER(256,3) (16777216), MEDIUMINT UNSIGNED
id < POWER(256,4) (4294967296), INT UNSIGNED

IMHO don't ever use 'serial'.  The reason is that serial doesn't produce incremental increases.  The first serial may be '1', but the second is likely '4' and the third may be '11'.  If that doesn't matter, have at it.  There is no downside (anymore) to using indices which are significantly too large.  We don't run on machines with memory measured in K anymore.  Don't adopt old axioms which aren't applicable anymore.


Get @@SERVERNAME from linked server This seems like a basic question but I can't find any answers out there - I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things:

select <linked server>.@@SERVERNAME;
select <linked server>.SERVERPROPERTY('ServerName');

... but no joy. Any ideas?

This is SQL 2008 R2 & 2014 (2008R2 is the linked server)

EDIT: Errors are:

  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near
  '@@SERVERNAME'.

Some slightly shorter (and more natural, IMHO) approaches:

EXEC LinkedServer.[master].sys.sp_executesql N'SELECT @@VERSION;';

Or:

EXEC('SELECT @@VERSION;') AT LinkedServer;

I prefer the sp_executesql route because:

It will still allow for strongly-typed parameters, instead of dealing with all kinds of string concatenation, escaping of single quotes, etc.
It is easy to specify a particular database in that path, instead of having to put database prefixes on all the objects referenced in the query. And yes, you can define the database dynamically:

DECLARE @db SYSNAME, @exec NVARCHAR(1024);
SET @db = N'tempdb';
SET @exec = N'LinkedServer.' + QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @exec N'SELECT @@VERSION, DB_NAME();';

And the linked server name, too, if need be:

DECLARE @srv SYSNAME, @db SYSNAME, @exec NVARCHAR(1024);
SET @srv = N'LinkedServer';
SET @db = N'tempdb';
SET @exec = QUOTENAME(@srv) + N'.' + QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @exec N'SELECT @@VERSION, DB_NAME();';

Easiest Way (SSMS)

You can't script it through openquery if you don't have permissions on the master database (nor should you).

From the object explorer

+Linked Objects

++Linked Servers

+++[Linked Server Name]

Left click the linked server name Script Linked Server As > Create To > New Query Editor Window

Look for @datasrc='linked server source here'

Voila!


Import data problem in SQL Server 2008 I want to import data from one database to another.  On the destination database, I don't have permission to restore a backup file to it, so I have only one option which is to import data.

I used the wizard and I set the option of allow identity insert, but after it ran I faced a problem with foreign keys insertion. Is there anything I missed or shall I drop the foreign keys and recreate it after import?

I really wonder why Microsoft made it complicated.

You could drop the foreign keys, but if the tables are large and/or you have busy environment, be sure to test your insert times before you do anything on production.
In regard to new keys, it shouldn't throw a problem, but if you're trying to insert duplicate keys then you'll get errors.
Also, be sure to insert parent entities first (if you don't drop foreign constraints first!) then child entities.

**Insert the parent entity first then their child otherwise it will conflict the  **FOREIGN** KEY Constraint.**


How to get data from three different tables This question is continuing of this issue: How to get data from two different tables?

Now I need to connect the table Sessions with 2 tables:

Input
Downloads

When I use this query with real data:

SELECT sessions.ip
    , COUNT(sessions.id)
    , COUNT(input.input) as TotalInputs
    , COUNT(DISTINCT input.input) as UniqInputs
    , COUNT(downloads.shasum) as files
    , COUNT(DISTINCT download.shasum) as Uniqfiles
FROM sessions, input, downloads
WHERE sessions.id = input.session
    AND sessions.session = downloads.session
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01"
GROUP BY sessions.ip
ORDER BY COUNT(sessions.id) DESC LIMIT 5;

I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | Uniqfiles
IP1 |              11145    |       11145   | 15            | 11145 |         8
IP2 |               9125    |        9125   | 71            |     0 |         0
IP3 |               7882    |        7882   | 56            |  7882 |        19

But numbers for count(sessions.id), TotalInputs and Files are not accurate. For example if I use this query:

SELECT downloads.shasum
FROM sessions, downloads
WHERE sessions.id = downloads.session
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01"
    AND sessions.ip = "IP3";

I find that Files count for IP3 have proper value of 752 (not 7882). Real value for TotalInputs is less than COUNT(sessions.id).

How can I fix my query?

Sample data is available on this SQL Fiddle.

Using the above query and below sample data, I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 3                     | 3             | 2             | 3     | 1
IP3 | 8                     | 8             | 4             | 8     | 2

I need this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 1                     | 3             | 2             | 1     | 1
IP3 | 3                     | 5             | 4             | 3     | 2

How can I update my query ?

Sample Sessions data:

id      | starttime             | endtime               | sensor    | ip    | termsize  | client
id1     | 2015-05-07 11:01:20   | 2015-05-07 18:01:32   | 10        | IP3   | 80x50     | 3
id2     | 2015-05-07 18:03:20   | 2015-03-07 18:11:32   | 2         | IP2   | 80x50     | 1
id3     | 2015-05-07 23:05:20   | 2015-06-07 18:10:32   | 10        | IP3   | 80x70     | 3
id4     | 2015-05-07 13:05:20   | 2015-05-09 20:05:32   | 7         | IP3   | 60x30     | 5

Sample Input data:

id  | session   | timestamp             | realm | success   | input
1   | id1       | 2015-07-13 10:29:18   | NULL  | 1         | date
2   | id3       | 2015-08-13 10:11:18   | NULL  | 0         | aaa
3   | id1       | 2015-03-13 10:11:18   | NULL  | 0         | aaa
4   | id1       | 2015-07-14 10:33:15   | NULL  | 1         | uname
5   | id3       | 2015-05-19 20:33:11   | NULL  | 1         | netstat
6   | id2       | 2015-09-22 10:53:21   | NULL  | 1         | pwd
7   | id2       | 2015-09-22 10:58:11   | NULL  | 1         | pwd
8   | id2       | 2015-11-03 09:53:07   | NULL  | 0         | bbb

Sample Downloads data:

id  | session   | timestamp             | url           | outfile   | shasum
1   | id1       | 2014-07-13 12:15:47   | http://xxx    | xxx       | SHA1
2   | id2       | 2014-09-13 12:18:50   | http://xxx2   | xxx2      | SHA2
3   | id1       | 2015-09-11 13:20:50   | http://xxx3   | xxx3      | SHA1
4   | id3       | 2016-01-19 18:21:30   | http://xxx4   | xxx4      | SHA3

This query:

Uses ANSI JOIN (LEFT, INNER, ...)
Uses LEFT JOIN for each table in order to count Sessions IPs without Input or Downloads
Uses DISTINCT for each COUNT in order to remove duplicates added by the JOIN between tables
Counts values for totals and counts ids for unique counts

Query:

SELECT s.ip
    , COUNT(DISTINCT s.id)
    , COUNT(DISTINCT i.id) as TotalInputs
    , COUNT(DISTINCT i.input) as UniqInputs
    , COUNT(DISTINCT d.id) as files
    , COUNT(DISTINCT d.shasum) as Uniqfiles
FROM sessions s
LEFT JOIN input i
    ON s.id = i.session
LEFT JOIN downloads d
    ON s.id = d.session
GROUP BY s.ip;

SQL Fiddle

Output:

ip  | COUNT(DISTINCT s.id)  | TotalInputs   | UniqInputs    | files | Uniqfiles
IP2 |       1               |       3       |   2           | 1     |   1
IP3 |       3               |       5       |   4           | 3     |   2

@JulienVavasseur is correct in that you need to format your queries using Ansi92 join syntax.  If nothing else, it will make it easier for us to read and help you with your questions.
However, looking at the problem, it seems as easy as adding a Distinct to the count of session.id to reduce it from 7882 to 752.  When you ask for a count, you're going to get a count of the rows, not a distinct count of the sessionid (unless you ask for distinct).


Only one administrator can connect at this time (MS SQL Server error 18461) I was experimenting the effect of giving SQL Server a small amount of memory I thought it was going to recover.

I configured SQL Server to use 200MB of memory now it does not want to start, I did some searches on the internet and I was advised to start SQL Server in single-user mode. however, I get the error

  Login Failed for user A. Reason: Server is in single user mode. Only
  one administrator can connect at this time (Microsoft SQL Server,
  Error: 18461)

I have stopped the SQL Server Agent.

If you start SQL Server in single user mode only one administrator can connect at the same time.

What's probably happening here is that some service is using a sysadmin login to connect such as Reporting services or SQL Server agent.

When starting SQL Server in single user mode you have the option to specify a client application so only that application can connect.

Have a look at the command line options where it lists:

  -m "Client Application Name"

  When you use the -m option with SQLCMD or SQL Server Management
  Studio, you can limit the connections to a specified client
  application. For example, -m"SQLCMD" limits connections to a single
  connection and that connection must identify itself as the SQLCMD
  client program. Use this option when you are starting SQL Server in
  single-user mode and an unknown client application is taking the only
  available connection. To connect through the Query Editor in
  Management Studio, use -m"Microsoft SQL Server Management Studio -
  Query".

  Client Application Name is case sensitive.

If you are able to connect this way, change the maximum server memory to something reasonable. I suppose you will be able to connect because otherwise you would probably receive something as "there is no process on the other end of the pipe" so I would assume your server is running.

If however you can't log in to SQL Server because your memory configuration doesn't allow you, you can start SQL Server in minimal configuration using the -f option.

  -f

  Starts an instance of SQL Server with minimal configuration. This is
  useful if the setting of a configuration value (for example,
  over-committing memory) has prevented the server from starting.
  Starting SQL Server in minimal configuration mode places SQL Server in
  single-user mode. For more information, see the description for -m
  that follows.

For reference: SQL Server can’t start after accidently set the "max server memory" to 0

Was able to open query editor window in SSMS after starting SQL Server in single-user mode.  See: https://www.sqlservercentral.com/forums/topic/unable-to-open-ssms-in-single-user-mode


Eliminate duplicates in ListAgg (Oracle) Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row.  11.2 Added the LISTAGG function, so I am trying to use that instead.  My problem is that I need to eliminate duplicates in the results and don't seem to be able to do that.

Here is an example.

CREATE TABLE ListAggTest AS (
  SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual
     CONNECT BY rownum<=6
  );
SELECT * FROM ListAggTest;

      NUM1 NUM2
---------- ---------------------
         1 2
         2 2                    << Duplicate 2
         3 3
         4 4
         5 5
         6 6

What I want to see is this:

      NUM1 NUM2S
---------- --------------------
         1 2-3-4-5-6
         2 2-3-4-5-6
         3 2-3-4-5-6
         4 2-3-4-5-6
         5 2-3-4-5-6
         6 2-3-4-5-6

Here is a listagg version that is close, but doesn't eliminate duplicates.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s
FROM ListAggTest;

I have a solution, but it's worse than continuing to use the custom aggregate function.

You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:

SELECT Num1,
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()),
           '([^-]*)(-\1)+($|-)',
           '\1\3'),
         '-') Num2s
FROM ListAggTest;

This could be tidier if Oracle's regex flavour supported lookahead or non-capturing groups, but it doesn't.

However this solution does avoid scanning the source more than once.

DBFiddle here

SQL was designed as simple language, very close to english. So why don't you write it as in english?

eliminate duplicates on num2 & use listagg as aggregate function - not analytic,  to compute concat on string
join in to original, as you want one result row for one input

select num1, num2s
  from (select num2,
               listagg(num2, '-') within group(order by num2) over() num2s
          from listaggtest
         group by num2
       )
  join listaggtest using (num2);


Hyper-V Dynamic Memory and SQL Server I will probably get voted down for this, but does anyone have a good place to start other than the Best Practices Documentation for using Dynamic memory with SQL server.

We are experiencing issues and keep having to set to static for it to work.  The best practices guide basically says it should all work, but I am looking for some real world issues anyone might have faced.

Dynamic memory with SQL is good for letting the memory expand as required and taking the pressure off the disk subsystem as SQL can hold more in memory.  It's a bit of a swizz though in the real world as you are supposed to set the "Lock Pages In Memory" (LPIM) setting to stop the OS from taking memory back from SQL, which can be bad, so you end up creating a one-way street.  You set the max server memory setting (in SQL) to be higher than the startup memory (Hyper-V setting), ideally you match it to the Max mem setting (Hyper-V) of the VM.  Thus SQL starts and takes whatever you set at startup and then it grows over time to reach the maximum setting.  The thing is it always does this.  It's not like a safety net, having the extra headroom "just in case" SQL needs it, it will always take it eventually and then because of LPIM it won't give it back.  You might as well have set the memory to static at the max memory level because that's what it's going to get anyway.

I'm still not sure of the benefit of Dynamic memory for SQL and I've read a lot of white papers and articles, everyone affiliated with MS seems to recommend it but with LPIM and my own real-world observations, I can't see the point of it.  The behaviour with SQL is not "dynamic" it just takes and keeps.

SQL Server with VMware has limited support from Microsoft Support. Keeping that in Mind, the startup memory is the key and it needs to be optimized. See more on SQL Server Best Practices with Dynamic Memory in Hyper-V.


mysql replication successful but slave not replicating I have created a mysql master-slave configuration and things look fine .
the  " show master status;" on slave doesnt show any error . this is the output

 Slave_IO_State: Waiting for master to send event
                  Master_Host: 109.123.100.58
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            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: 106
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

However on replication on slave doesnt seem to be happening when master undergoes change

On master . SHOW MASTER STATUS

+------------------+----------+--------------+-------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB        |
+------------------+----------+--------------+-------------------------+
| mysql-bin.000001 |   639495 |              | mysql,informationschema |
+------------------+----------+--------------+-------------------------+

on slave SHOW PROCESSLIST yields

+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| Id  | User        | Host      | db   | Command | Time  | State                                                                 | Info             |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| 174 | system user |           | NULL | Connect | 25777 | Waiting for master to send event                                      | NULL             |
| 175 | system user |           | NULL | Connect | 25777 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 199 | root        | localhost | NULL | Query   |     0 | NULL                                                                  | show processlist |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Am i missing something

The output you presented is very confusing. On one hand, the master shows:

mysql-bin.000001   639495

The slave's PROCESSLIST indicates that replication works (the IO thread is connected to the master, the SQL thread is waiting).

The slave's SHOW SLAVE STATUS again claims all is well, but the master's position is 106.

Are all these outputs issued at roughly the same time? If not, then they are not valuable.

I would check the following:

The obvious: is 109.123.100.58 really your master?
Does SHOW PROCESSLIST on master list the connection made by the slave under the replica user?
Do you have different server-id settings for master and slave?
Do you have any replicate-do-* or replicate-ignore-* settings in your mysql.cnf file?

You didn't tell the slave which DB to replicate: the variable Replicate_Do_DB is empty.

Try to add the line

replicate-do-db=name_of_db (where "name_of_db" is the name of the database to replicate)

in your my.cnf (on slave) and restart the slave-server.

EDIT:
I don't think my answer is the right solution, meanwhile ;-)

Perhaps have a look a this page instead:
http://dev.mysql.com/doc/refman/5.1/en/replication-problems.html


Creating roles and granting rights to the role I wanted to create a role called "user_updates" and this role should have the rights "table_note_updates".
The rights called "table_note_updates" is already available in my database. How to grant the rights to role that i create.

A role can be granted to another role.
create role user_updates;
grant table_note_updates to user_updates;

create role user_updates;
grant table_note_updates to user_updates;

Grant created role to user.
grant user_updates to myuser;


Is it safe to delete mysql-bin files? I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those files like mysql-bin.000123, mysql-bin.000223 etc. I have checked the mysql replication by doing show master status and show slave status they are using some mysql-bin files at certain positions, but I guess all the other bin files are leftovers which will not be used anymore. In this case is it safe to delete all those mysql-bin files except the ones that replication is currently pointing at ?

If it is safe to delete, then is there anything I could do to automatically delete those files once they are not in use ?

Please do not just delete them in the OS.
You need to let mysqld do that for you. Here is how mysqld manages it:
The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are:
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';

These will clear all binary logs before the binlog or timestamp you just specified.
For example, if you run
PURGE BINARY LOGS TO 'mysql-bin.000223';

this will erase all binary logs before mysql-bin.000223.
If you run
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.
If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:
mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf
[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you
SHOW SLAVE STATUS\G
This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

Master_Log_File
Relay_Master_Log_File

When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run
PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

That way, replication is not interrupted.

Yes, it is safe. For mysql 8, here's how you can do it.
We can set binlog_expire_logs_seconds as a option or as a system variable or as a command line parameter too, but I will show how to use it as configuration option.
Using as a options, you have to edit configurations files. You can look for windows and linux files here. For linux, I went to /etc/mysql/my.cnf file:
$ cat /etc/mysql/my.cnf
...
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Let's see what's inside /etc/mysql/mysql.conf.d/:
$ ls /etc/mysql/mysql.conf.d/
mysql.cnf  mysqld.cnf

For example, to keep only 3 days of logs, you have to calculate total of seconds (246060*3). Now, all we have to add binlog_expire_logs_seconds option to a mysqld section. My solution was to create a file with this content:
$ cat /etc/mysql/mysql.conf.d/binlog_expiration.cnf
[mysqld]

# Three days: 24*60*60*3
binlog_expire_logs_seconds=259200

Since the file was inside a directory included by /etc/mysql/my.cnf it will be loaded with the service.
And then, restart the service:
sudo service mysql restart

It will erase the log for you and will keep deleting them.
NOTE: For reference, expire_logs_days was deprecated.


Question about 'mysql error 1064' ALTER TABLE sample62 CHANGE newcol c;

This code makes me mad. I just followed the book I had.

Program said

  You have an error in your SQL syntax; check the manual that corresponds
  to your MySQL server version for the right syntax to use near '' at line 1.

My MySQL version is 5.7. Can you please help me?

mysql error 1064

The error that gets thrown if the syntax is wrong. You did not clearly state what you are doing.

If you are changing a column

ALTER TABLE table_name MODIFY COLUMN column_name datatype

If you are trying to change column definition

ALTER TABLE table_name CHANGE old_column_name new_column_name data_type(size)

If you are adding a new column

ALTER TABLE table_name ADD column_name datatype

Use this syntax:

Alter table tablename CHANGE [COLUMN] old_col_name new_col_name column_definition


PostgresSQL dynamic execute with argument values in array I'm wondering if this is possible in Postgres:

Best explained using a contrived example:

create or replace function test_function(filter_param1 varchar default null
                                       , filter_param2 varchar default null)
  returns integer as
$$
declare
  stmt text;
  args varchar[];
  wher varchar[];
  retid integer;
begin
  if filter_param1 is not null then
    array_append(args, filter_param1);
    array_append(wher, 'parameter_name = $1');
  end if;
  if filter_param2 is not null then
    array_append(args, filter_param2);
    array_append(wher, 'parameter_name = $2');
  end if;

  stmt := 'select id from mytable where ' || array_to_string(wher, ' or ');
  execute stmt into retid using args;

  return retid;
end;
$$ language plpgsql;

In Python there is *args - perhaps PostgreSQL has a similar mechanism?

EDIT for Erwin Brandstetter questions:

All filter parameters will be applied to different columns, but should be AND'ed.
Returning setof makes much more sense here.
All parameters can be of the same column type (ie. varchar).

Either way, that's totally possible, given that all your parameters are of the same data type.
EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts.
create or replace function test_function(_filter1 text = null
                                       , _filter2 text = null
                                       , OUT retid int) as
$func$
declare
   _args text[] := ARRAY[_filter1, _filter2];
   _wher text[];
begin
   if _filter1 is not null then
      _wher := _wher || 'parameter_name = $1[1]'; -- note array subscript
   end if;

   if _filter2 is not null then
      _wher := _wher || 'parameter_name = $1[2]'; -- assign the result!
   end if;

   IF _args  IS NULL         -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   execute 'select id from mytable where ' -- cover case with all params NULL
         || array_to_string(_wher, ' or ')
         || ' ORDER BY id LIMIT 1';   -- For a single value (???)
   into  retid
   using _args;
end
$func$  language plpgsql;
This is just a proof of concept and needlessly complicated. It would be an interesting option for actual array input, for instance with a [VARIADIC function][2]. Example:

How to do WHERE x IN (val1, val2,...) in plpgsql

For the case at hand, use instead:
CREATE OR REPLACE FUNCTION test_function(_filter1 text = null
                                       , _filter2 text = null)
  RETURNS SETOF int AS
$func$
DECLARE
   _wher text := concat_ws(' OR '
             , CASE WHEN _filter1 IS NOT NULL THEN 'parameter_name = $1' END
             , CASE WHEN _filter2 IS NOT NULL THEN 'parameter_name = $2' END);
BEGIN
   IF _wher = ''   -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   RETURN QUERY EXECUTE 'SELECT id FROM mytable WHERE ' || _wher
   USING  $1, $2;
   -- USING  _filter1 , filter2; -- alternatively use func param names
END
$func$  LANGUAGE plpgsql;
Explain

List all values that can possibly be referenced in the dynamic query in the USING clause in their order of appearance. If not all of them will be referenced in the dynamic query, there's no harm in that. But we need to keep ordinal positions intact.

Note in particular that $n inside the dynamic query references given values of the USING clause by ordinal number, while $n in the USING clause references function parameters. Same syntax, different scope!
In my example $2 references $2 for simplicity. But one could reorder values in the USING clause in any fashion, so that (for instance) $2 in the dynamic query references $1 at the 2nd position in the USING clause, which references the 1st function parameter.

This allows for any number of parameters with any (heterogeneous) data types.

Returning a set of integer in this example(RETURNS SETOF int), which better fits the example - using RETURN QUERY EXECUTE accordingly.

concat_ws() is particularly handy to assemble a list of OR'ed or AND'ed predicates conditionally.

RETURN QUERY EXECUTE SELECT
        TotalBookingAmount,
        TotalQuantity,
        TotalActualWeight,
        TotalChargedWeight,
        TotalLR,
        TotalDeliveryAmount,
        GrandTotalAmount,
        waybilltypeid,
        bookingmonth,
        bookingyear,
        sourcebranchid,
        destinationbranchid
                FROM '|| tableName || '
            where
                accountgroupid          = ' || accountGropIdCol     :: numeric || '
            AND sourcebranchid          = ' || SourceBranchIdCol    :: numeric || '
            AND bookingmonth            = ' || bookingMonthCol      :: numeric || '
            AND bookingyear             = ' || bookingYearCol       :: numeric || '
            AND destinationbranchid     =  any (string_to_array(' || DestinationBranchIdCol ||',' || ',' || ')' :: numeric[] ||')';


SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows Brief on application:

This is audio fingerprinting application, being developed in Java with Microsoft SQL Server 2005 database.

I have one application to create fingerprints of original songs and put these fingerprints in database. To store fingerprint in database I have table:

CREATE TABLE [dbo].[fp_core](
    [hashkey] [bigint] NOT NULL,
    [note_id] [int] NOT NULL,
    [timeoffset] [int] NOT NULL
) ON [PRIMARY]

The application processes song and takes 100 sample per second, so around 15000 samples for complete song. These sample values are stored in database, 1 row for each sample as {HASHKEY, NOTE_ID, TIMEOFFSET}. For fingerprint of complete song, I may have around 15000 rows in fp_core table. I am planning to put fingerprints of 50000 songs in database, so around 750 million rows will be in fp_core table.

I have other application to process recordings and detect songs played in it. Process is, create set of HASHKEY from recording audio, same as for creating fingerprint of original song. Recording audio will generate around 20000-30000 HASHKEYs. Then application retrieves rows from fp_core table for all matching HASHKEYs generated by recording audio.

To retrieve data from fp_core table by processing recording, I am doing is, filling these all HASHKEYs of recording in one more table, table is:

CREATE TABLE [dbo].[fp_core_keys](
    [hashkey] [bigint] NOT NULL
) ON [PRIMARY]

then I am joining these two tables to retrieve all matching rows, the query is:

select fp.hashkey, fp.note_id, fp.timeoffset
from dbo.fp_core fp
INNER JOIN dbo.fp_core_keys keys ON fp.hashkey = keys.hashkey

I have following indexes:

CREATE CLUSTERED INDEX [index_fp_core] ON [dbo].[fp_core]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_fp_core_keys] ON [dbo].[fp_core_keys]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Problem:

Retrieving data using above query is so slow, taking time around 40 seconds.

Right now, here is stats:

Query:

select count(hashkey) from fp_core
go
select count(distinct(hashkey)) from fp_core

Result:

57177764
13675633

Plan:

Can anybody help me?

If you are determined to retain your existing table structure, the best indexes will be something like:

create index ix1 on fp_core(hashkey, note_id, timeoffset);
create index ix1 on fp_core_keys(hashkey);

You mention that the hashkeys can be different for the same song, but it sounds like you have some mechanism for detecting similar songs - if possible, add an additional column called 'songid' to your fp_core table and then query on this single value using a WHERE clause.


SQL Server 2012 - Deadlock happening daily We have a performance issue with a 3rd-Party application using SQL Server 2012: deadlock happen at least 10 times an hour.

The server is virtual server on VMWare 5.5 with PVSCSI adapter in persisted mode for SQL Server disks, 4 vCPU (that are seen by SQL Server as 2 CPU with 2 cores).
It runs Windows Server 2012.

As we already had the issue, our supplier asked to set MAXDOP to 1 as they experienced problems with parallelism. It didn't solve the situation.

I ran the DBCC TRACEON (1222, -1) to get extended informations about deadlocks and transmit it to the supplier.

Edit: Example deadlock graph

In XML Format: (I was not able to add it to the post)
XML Format

Edit2: Index Maintenance

The following Job runs Ola Hallengren MaintenanceSolution (IndexOptimize) :

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d SAIDBA -Q "EXECUTE maintenance.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationLevel1 = 3,@FragmentationLevel2 = 30,@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y',@LogToTable = 'Y'" -b

Edit 3: update on the situation

I did some mining on the error log to get an overview of the time line for deadlock occurrences. It happened three to four times more frequently after a software update (120 per day), but there were already 30-40 per day previously.

As the application supplier doubts about our indexing strategy, he wants us to do it.. We will rebuild all the indices..
We will see if it has positive effects...

What else can I do to investigate/troubleshoot/Reduce the number of deadlocks ?

Short answer

Check ERRORLOG for deadlock frequency (is it regular or very new ?)
Ensure Index Maintenance exists and has run successfully.
Setup a trace either with SQL Server Profiler, an Extended Events solution or via Trace Flag 1222.
Analyze the results of the trace:

What queries were involved ?
Which tables/indices are accessed when it happens and how frequently ?

Anything else ?

Deadlocks are "simply" application logic errors. In SQL Server I think you can do the following:

review the application logic;
You can use transaction isolation in snapshot mode SET TRANSACTION ISOLATION LEVEL

See also:

How to Track Down Deadlocks Using SQL Server 2005 Profiler

written by Brad McGehee. I think that he is more authoritative than me. He states:

  ...Deadlocks have been the bane of many a DBA. While rare for a well-designed and written application, deadlocks can be a major problem for—how can I say this delicately?—“less efficient” application code. What is even more frustrating is there is not much the DBA can to do prevent deadlocks, as the burden of preventing them in the first place is on the developers of the application...."


Preferred database solution for storing sensor data As a hobby project I am developing a system which stores data from different kind of sensors around the house. Currently it is implemented using ASP.Net MVC 6 and MongoDB for the database. (I choose MongoDB because I was curious about how it works) Every sensor peroidically posts data to the server on it's own.

For the sake of not having to store the same information possibly hundreds of times I am using references for redundant data. This data includes the type of a sensor, the data fields used by the sensor(and information about fields like it's name, etc).

Here is an example in JSON format:

// Sensors collection
{
    _id : 1,
    TypeId : 1
}

// SensorTypes
{
    _id : 1,
    GenericName : "Test sensor",
    FieldIds : [1, 2]
}

// Fields
[{
    _id : 1,
    Name : "field1",
    DecimalPlaces : 2
},
{
    _id : 2,
    Name : "field2",
    DecimalPlaces : 3
}]

Questions:

Is this good practice? Am I using correct and preferred way to avoid redundancy in my database?
"Schema" of my documents can be displayed like a graph. Does a graph database like Neo4j would be more suitable for my problem?

Thank you for your help!

In general you do not want to normalize the data you are stuffing into whichever NoSQL solution you are using, in other words, you want to leave the redundant information there. Otherwise you should be using a relational database system like MySQL,Postgres,SQLite, or MSSQL (Express edition is free, but has database size limits). If you use any of those systems, reducing redundant information would be beneficial.

Honestly, if you are looking to grab a bunch of sensor data, then display it as a graph at a later point, you might want to look into setting up a box with Elasticsearch + Kibana on it. You can get something up and running in a matter of minutes with VERY minimal amounts of configuration: https://www.elastic.co/products/kibana

When Neo4j calls itself a "graphing database" it is not graphs in the visual sense of the word, it is graphs in the relationship sense: https://en.wikipedia.org/wiki/Graph_database

Hope some of this helps.

i assume its bit outdated but i would like to share my experience with MongoDB and Sensor data. Development went really easy and fast but some drawbacks during project created too much problems that we had to change to MSSQL.

First of the problems was that Data got lost - more corrupted. We could see the data, but amount of stored documents was totally different than amount of Iterable documents. Amount of lost data was about 1/3. This is something that happens in MOngoDB and as i heard in a lot of NoSQL DBSs. However, if you are in  need of all information that was aceppted by the server, than this could be a major drawback

Second was Agreggation lookup. Its basically a left outer join and it took me ages untill i was able to make any join. Couldnt place all the data in one document as the packets sizes would have been way over 16MB (when all different sensors values are writen in one document)

Third was lack of scheme: some values of field devices (sensors) got corupted during concertion from degrees Celsius to Kelvins due to Controller bug. MongoDB had no validator and i got bunch of strange values in DBS. That had arround 5 days cost, as we blieved that mistake was in DBS,

Placing validators has a large cost, firstly it is almost impossible to create it properly if you have more than 50 Key value pairs. Not to talk about costs of performance

Why we went for MSSQL instead of Mongo is basically described above. For reporting purposes Grafana has MSSQL driver and that worked just fine

Coming back to your question: if you stored redundancies in the way you did it allready, there is no quarantee, that this sensor type exists (which is something that you could easily change with relational DBS by using PK and FK)

If you are using it in such way, you will end up by using Lookup - that could take a lot of processing time, and changes of one document referenced, could cause you loosing data as mongoDB doesnt check it automatically as relational DBSs do.

BR
HK


Tips on improving/refactoring slow query? Let's play lotto!

Ok. I have a numbers table with 80 rows (numbers 1-80):

create table dbo.numbers (knum tinyint primary key identity);

I have a table with the drawid, and each number drawn.

create table dbo.draws (drawid int, drawnumber tinyint);

Now, given that the draws table may have data like:

drawid   drawnumber
  1         10
  1         36
  1         54
  1         75
  2          9
  2         45
  2         46
  2         72

I want to find out when was the last time every possible three-number permutation occurred.

I'm using this:

declare @curdraw int
select @curdraw = max( drawid)-100 from draws;

select TOP 10 K1,K2,K3, @curdraw-max(d1.drawid)  from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID < @CURDRAW

GROUP  BY K1, K2, K3
ORDER BY @curdraw-max(d1.drawid) DESC

(Oh, and table Threes is a table with all 492,000+ three-number combinations from 1-80)

Is there a better way to do what I'm trying to do here? This particular query is just very slow and I'm sure someone with better math/grouping skills could do better.

Answers to comments - clarifications:

It's for a keno game. Every draw has 20 numbers out of the 80. I just showed 4 each as an example.
Two years of accumulated draws, at 254 draws a day with 20 numbers per draw... Drawnumber table is currently 3.7 mil rows. Also, what I'm looking for specifically is "out of every 3-number combination possible can you give me a sorted list of how long it has been since any one of them has hit, ordered by that length".
The top 10 is merely because I don't need to see all 490K results.

The primary keys:

USE [OKD]
GO

ALTER TABLE [dbo].[DrawNumbers]
  ADD CONSTRAINT [PK_DrawNumbers]
  PRIMARY KEY CLUSTERED
    ( [DrawID] ASC, [DrawNumber] ASC )
  WITH
   (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
    ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
GO

USE [OKD]
GO

ALTER TABLE [dbo].[threes]
  ADD PRIMARY KEY CLUSTERED
    ( [THREEID] ASC )
  WITH
    (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
     SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
     ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
GO

Changing it to use a CTE will be faster probably - they normally give the optimizer a better chance at optimising the query.

with currentdraw as (
select k1,k2,k3 from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID = @CURDRAW
), previousdraws as (
select d1.drawid, k1,k2,k3 from THREES
inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER
INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID
INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID
WHERE D1.DRAWID < @CURDRAW
)
select previousdraws.*
from previousdraws p
inner join currentdraw c
on p.k1 = c.k1 and p.k2 = c.k2 and p.k3 = c.k3
order by drawid desc

Your best indexes will probably be:

create index ix1 on THREES (k1,k2,k3);
create index ix1 on DrawNumbers (drawid, drawnumber);


Why is this MySQL proc using cursors failing to retrieve results? I am importing data from a third-party vendor. I have already imported their tables into my existing database. Now, I just need to iterate through each of their person records, munge it a little bit, and insert it into a few tables on my end. My cursor seems to only be iterating through once, though - here is the code.

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE;

    -- Declare Person variables --
    DECLARE person_id INT;
    DECLARE era_username VARCHAR(100);
    DECLARE last_name VARCHAR(50);
    DECLARE first_name VARCHAR(50);
    DECLARE email VARCHAR(100);
    DECLARE email_primary VARCHAR(50);
    DECLARE degree_id_1 INT;
    DECLARE degree_id_2 INT;
    DECLARE member_status INT;

    DECLARE user_id INT;
    DECLARE user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR
        SELECT person_id, era_username, last_name, first_name, TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2, member_status
        FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor
        INTO person_id, era_username, last_name, first_name, email, email_primary, degree_id_1, degree_id_2, member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET user_id = SELECT (MAX(uid) + 1) FROM users;
        IF email_primary IS NOT NULL AND email_primary NOT LIKE '%null%' THEN
            SET user_email = email_primary;
        ELSE
            SET user_email = email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (user_id, era_username, SHA1(RAND()), user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`, `member_category_id`, `subscription_weekly_email`, `subscription_monthly_newsletter`)
        VALUES (user_id, first_name, last_name, phone_num, member_status, weekly_com, monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (user_id, degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

It's as if the SELECT statement used to populate the cursor is returning no results. I've tested that independently, and it DOES return results correctly. I've tried putting a SELECT person_id directly below the first FETCH, and the returns NULL. So...although this query should return results, they're not making it into the cursor's result set. Am I missing something?

Try tagging all local variables with something like my_

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE;

    -- Declare Person variables --
    DECLARE my_person_id INT;
    DECLARE my_era_username VARCHAR(100);
    DECLARE my_last_name VARCHAR(50);
    DECLARE my_first_name VARCHAR(50);
    DECLARE my_email VARCHAR(100);
    DECLARE my_email_primary VARCHAR(50);
    DECLARE my_degree_id_1 INT;
    DECLARE my_degree_id_2 INT;
    DECLARE my_member_status INT;

    DECLARE my_user_id INT;
    DECLARE my_user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR
        SELECT person_id, era_username, last_name, first_name,
              TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2,
              member_status FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor
        INTO my_person_id, my_era_username, my_last_name, my_first_name,
              my_email, my_email_primary, my_degree_id_1, my_degree_id_2,
              my_member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET my_user_id = SELECT (MAX(uid) + 1) FROM users;
        IF my_email_primary IS NOT NULL AND my_email_primary NOT LIKE '%null%' THEN
            SET my_user_email = my_email_primary;
        ELSE
            SET my_user_email = my_email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (my_user_id, my_era_username, SHA1(RAND()), my_user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`,
              `member_category_id`, `subscription_weekly_email`,
              `subscription_monthly_newsletter`)
        VALUES (my_user_id, my_first_name, my_last_name, my_phone_num,
              my_member_status, my_weekly_com, my_monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = my_person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

In the book MySQL Stored Procedure Programming, all cursor examples give the local variable a unique name from its corresponding table column (if you have the book, see page 108 Example 5-15).

Sidenote : Where are weekly_com and monthly_com coming from ???

I too faced similar problem and tried investigating , Yes as many people replied this looks like mysql bug explained here.

alternative solution to the problem can be found here.click here


How do I write a query that inserts values while AND gets values from another table? I have a table (Tbl2) that needs to be populated with values from another table (Tbl1).

Tbl1 has columns: id (uuid), name, address, gender.

Tbl2 has columns: id (uuid), org, name, address, gender, createdAt, updatedAt.

I want to copy id, name, address, gender from tbl1 into tbl2. How Do I write a query that inserts those values into Tbl2 while also providing new ids, org, createdAt, and updatedAt values?

You need to look at the DEFAULT clause. I think you want something like the following as an example:

CREATE TABLE tbl2
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  org TEXT NOT NULL DEFAULT SUBSTRING(MD5(RANDOM()::TEXT) FROM 1 FOR 4),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  update_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT tbl2_pk PRIMARY KEY (id)
);

Note that I am using

  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT))

as the PRIMARY KEY. This is close enough to a UUID for the purposes of this answer - I'm using dbfiddle (see this link) and can't install the UUID extension. I'm also using a SUBSTRING of the same function to generate the org field values, since I don't know exactly what that is - I would have thought that a reference table to things like the UN, EU, NATO, ASEAN... &c. would be more appropriate for this, but then, only you know that - see @Philᵀᴹ 's com

Then populate it with some values:

INSERT INTO tbl2 (name, address, gender)
VALUES
('Name_23', 'Address_23', 'M'),
('Name_24', 'Address_24', 'F'),
('Name_25', 'Address_25', 'M');

Notice how I don't have to specify the id or the org fields since there's a DEFAULT in place. Now, we do the same for tbl1.

CREATE TABLE tbl1
(
  id TEXT NOT NULL DEFAULT(MD5(RANDOM()::TEXT)),
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  gender CHAR(1) NOT NULL,
  CONSTRAINT tbl1_pk PRIMARY KEY (id)
);

and:

INSERT INTO tbl1 (name, address, gender)
VALUES
('Name1', 'Address_1', 'M'),
('Name2', 'Address_2', 'F'),
('Name3', 'Address_3', 'M');

And then the INSERT is performed:

INSERT INTO tbl2 (name, address, gender)
SELECT name, address, gender
FROM tbl1
-- WHERE tbl.id NOT IN (SELECT id FROM tbl1) -- MD5 should ensure uniqueness
RETURNING name, address, gender;  -- this is optional - check it out.

And then we check our tbl2:

SELECT * FROM tbl2;

Result:

id  org name    address gender  created_at  update_at
1feaceed5c2d8e37f5565d222b5bd31c    28a1    Name_23 Address_23  M   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
508c0eecc16e305687b355c684cd5d56    d057    Name_24 Address_24  F   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
247d6d7bdcef078c4b4f7bef33e91d01    78a5    Name_25 Address_25  M   2020-02-26 05:43:28.366588+00   2020-02-26 05:43:28.366588+00
07bcc6cf6a3a3b79fcc05bafe9ebc8c2    05f8    Name1   Address_1   M   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00
029ef1b1c21df2a0fe25da352114aa1f    48c2    Name2   Address_2   F   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00
f4f51eccf9d4bfa7e011bdc4b2c135b8    8723    Name3   Address_3   M   2020-02-26 05:43:28.395899+00   2020-02-26 05:43:28.395899+00

A simple inspection shows that our INSERT has worked. Finally, notice that our created_at and updated_at fields have also been included - as the default is NOW(). In order for the updated_at field to be modified automatically, you'll need to use a TRIGGER - if you do a search, there are millions on the all-singing, all-dancing web. If this doesn't answer your question, let me know and I'll see what I can do. p.s. welcome to the forum!

Create a table mapping tbl1.id to the new values, then just insert into tbl2 a SELECT that joins those tables. Here's an example:

https://www.db-fiddle.com/f/eSRqTAdH9b2cwfuJfwrJ1B/0


The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION Below is an excerpt of an 800 line stored procedure that is failing with this error now. I say now because this is not new code. This script is scheduled to run every few minutes and has worked for a year.

ALTER PROCEDURE [dbo].[bsi_transfer_reallocate] @transfer_no int
AS
BEGIN
BEGIN TRY

[a few lines of code]

  BEGIN TRANSACTION REALLOCATE;

(over 700 lines of code)

  COMMIT TRANSACTION REALLOCATE;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION REALLOCATE;
    SET @dbg_msg = 'Transfer ' + CAST(@transfer_no AS varchar(8)) + ' aborted due to error condition with error number ' +
       CAST(ERROR_NUMBER() AS varchar(200)) + ' and message ' + ERROR_MESSAGE()
    INSERT INTO BATTERY_debug VALUES ('REALLOCATION Failure', GETDATE(), @dbg_msg)
    select  @dbg_msg
    RETURN (1)
END CATCH
END
return(0)

Some errors automatically rollback the transaction.  See generally
Error and Transaction Handling in SQL Server

And if your error already rolled back the transaction, the first line of your catch block will throw this error.

So replace

  ROLLBACK TRANSACTION REALLOCATE;

with

 if @@trancount > 0 ROLLBACK TRANSACTION;

(Remove the 'REALLOCATE' label because this will NOT roll back only the nested transaction.  A Nested Transaction can have a name, but cannot be individually rolled back.  For that you would need a savepoint, but that's probably not the issue here).

I'd hazard a guess that an exception is being thrown by one of those "liones" ... causing the CATCH block to be executed before the BEGIN TRANSACTION statement occurred.


Query to find customers that have deposit in all branches located in a specific city I have 3 tables as follow:

Customer (CustomerName , CustomerCity , CustomerStreet)
Branch   (BranchName , BranchCity)
Deposit  (CustomerName , BranchName , AccountNumber , Balance)

Here I show you sample data of the above tables :

Customer ('John','City1','Street1')
         ('Lili','City2','street2')

Branch   ('Branch1','Kaj')
         ('Branch2','Bahar')
         ('Branch3','Bahar')
         ('Branch4','Bahar')

Deposit  ('John','Branch1',12,1000)
         ('Lili','Branch1',123,1222)
         ('Lili','Branch2',124,45233)
         ('Lili','Branch3',345,6000)
         ('Lili','Branch4',567,5600)

So based on the information above, I need a query to give me CustomerName
that have a deposit in all branches that are in Bahar.

I should see 'Lili' in the output because it has deposits for all branches that are in Bahar but I should not see John cause it has a deposit only in one branch of Bahar.

I did my best to write the query but I could not. Could you help me with this please?

I have two solutions for your question :
solution 1 , I use Not Exists , Except

Select distinct C.CustomerName
From customers C
where NOT EXISTS ((Select BranchName
                   From Branch
                   Where BranchCity='Bahar')

                      EXCEPT

                   (Select BranchName
                    From Deposit a
                    where A.CustomerName = C.CustomerName))

In solution 2 I use Not Exists ,

Select Distinct C.CustomerName
From Customers C
Where NOT EXISTS  (Select BranchName
                   From Branch b
                   Where BranchCity='Bahar'
                   and NOT EXISTS (select BranchName
                                   from Deposit a
                                   where a.CustomerName=C.CustomerName
                                   and a.BranchName=b.branchName))

Each one of the query above will answer your query and give you the desired result.

SELECT c.CustomerName
FROM Customer c
CROSS JOIN Branch b
LEFT JOIN Deposit d
ON d.CustomerName = c.CustomerName
AND d.BranchName = b.BranchName
WHERE b.BranchCity = 'Bahar'
GROUP BY c.CustomerName
HAVING SUM(CASE WHEN d.AccountNumber IS NULL THEN 1 ELSE 0 END) = 0


How to merge multiple query results into one 1.

SELECT COUNT( * )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

2.

SELECT SUM( inscan )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

3.

SELECT SUM( is_deliver )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

4.

SELECT SUM( is_rto )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

How To merge above 4 query into one?

I tried below query but it gives Error
"Commands out of sync; you can't run this command now"

SELECT prtner_name, (

SELECT COUNT( * )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS total, (

SELECT SUM( inscan )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS inscan, (

SELECT SUM( is_deliver )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS deliver, (

SELECT SUM( is_rto )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name
) AS rto

FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name

Since the FROM, WHERE and GROUP BY clause are identical, combine them as follows:

SELECT
    prtner_name,
    COUNT(*)        shipment_count,
    SUM(inscan)     inscan_count,
    SUM(is_deliver) is_deliver_count,
    SUM(is_rto)     is_rto_count
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name;

You can do with subselects.

Example, with your queries:

SELECT

(SELECT COUNT(*)
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name) AS total,

(SELECT SUM( inscan )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name) AS inscan,

(SELECT SUM( is_deliver )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name) AS deliver,

(SELECT SUM( is_rto )
FROM tbl_shipment
WHERE  `awdno` !=  '' AND  `prtner_name` !=  ''
GROUP BY prtner_name) AS rto


What is contained in the controlfile of oracle database? As we all know that controlfile contains datafile locations and redo log locations along with their names.It also contains dbname,timestamp,checkpoint info,log sequence number,rman backup info.In addition to this,what more a controlfile contains? Does it contain user backup info?

Lets define the controlfile in oracle: it is a small binary file that records the physical structure of the database and its include:

1) The database name.

2) Names and locations of associated datafiles and redo log files.

3) The timestamp of the database creation

4) The current log sequence number

5) Checkpoint information


How to Add AFTER Delete Trigger in My case? I am Making a Database Management System of Hospital in MySQL WorkBench, and i Want to Make a After Delete Trigger in my case. The scenario is i have two Tables

1) The Patients which are currently admitted in the Hospital, that Table is represented as resident

and

2) This is The Table where i keep Records of the Outgoing Patient (i.e the Patient who successfully Undergoes treatment and walks out of Hospital) as outpatient.

Now, in the Resident table i have a column called residentID which distinguishes the Patients who are there by Just there Unique Number, and In outpatient i keep the records by OutpatientID of which patient who left Hospital.

So I want to make a Trigger in Such a way that Whenever i Write a DELETE FROM resident where residentID = 1 than the Person's ID who just got Deleted from resident table comes into Outpatient Table. So how do we make Such Trigger which does this, I am pretty much quizzed here. This is what i want, any Inputs would be highly appreciated.

You should take the vbalues of OLD and use them in INSERT command.

Something like this:

CREATE TRIGGER triger_name_after_delete
AFTER DELETE
   ON resident FOR EACH ROW
BEGIN
   -- Insert record into Outpatient table
   INSERT INTO Outpatient (residentID, [rest columns names...] )
   VALUES ( OLD.residentID, OLD.[rest columns names...])
END;

Maybe you can create DELETE after INSERT. So, if like you case, you can use this trigger.

CREATE TRIGGER `delete_after_insert`
  AFTER INSERT
  ON  `outpatient`
  FOR EACH ROW
  BEGIN
  DELETE FROM `resident` WHERE `residentID` = ''
END;

You don't need old values for insert to outpatient. because you insert first and get the id for delete on resident table.


How to get the name of the current database from within PostgreSQL? Using \c <database_name> in PostgreSQL will connect to the named database.

How can the name of the current database be determined?

Entering:

my_db> current_database();

produces:

ERROR:  syntax error at or near "current_database"
LINE 1: current_database();

The function current_database() returns the name of the current database:

 SELECT current_database();

It's an SQL function, so you must call it as part of an SQL statement. PostgreSQL doesn't support running functions as standalone queries, and has no CALL statement like some other SQL engines, so you just use SELECT to call a function.

PostgreSQL List Databases - To list all the databases created within PostgreSQL Server.
postgres=# \l
postgres=# \list

To Check Current database you are connected to.
SELECT current_database();


Oracle 11g database structural change doesn't autobackup controlfile/spfile I have configured controlfile autobackup:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/%F';

Then added a test tablesapce into the database:

create tablespace test datafile '+DATA01' size 10m;

And there is no controlfile autobackup, and also no entry in alert log file about backing it up.

Why?

I found the answer as I think:

"Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database."

After about(not sure how much) 10-15 minutes I run the following command from the RMAN:

list backup;

and now it shows me the result. So it waited for a time and then backed up the controlfile.

_controlfile_autobackup_delay=300

Oracle 12v Automatic Control File Backup


Taking MySQL dump from another machine I have a MySQL db on Machine A and I want to access the mysqldump from machine B.

Both machines are running on Linux and I also have the credentials of both, but I can't access the mysqldump. What can I try?

In General if you have the credentials for accessing MySql from Machine B.

You should have MySQL user name , host name and Password.Then You can write the Command for taking backup

On Machine B Write

mysqldump -h Your_host_name -u user_name -p password --all-databases > backup.sql

If You Face issues there may be some network issue try some things as

Use ping with ip address to check that DNS isn't broken. eg. ping Machine A
Use mysql client to connect from B to A. eg mysql -u user -pPASS --host=Host_Name --port=3306 (substitute whatever port you are connecting to the master on)

I put it like this and it works perfectly

mysqldump -P 7999 -h '192.168.1.25' -vv -u'user' -p'password' base_dados | gzip > base_dados.sql.gz


How to get the current date without the time part In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0

The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Two different and excellent answers on StackOverflow bear this out: One, Two

Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.

This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions

This technique is also extendable.

yesterday: DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
start of month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
end of last month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
start of next month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Edit:

As I mentioned about determinism, varchar methods are not safe unless you use style 112.

Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.

The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.

You have to convert it to varchar specifying a format pattern (110 in this case) then convert (or cast) back to datetime.

select getdate(), cast(convert(varchar(10), getdate(), 110) as datetime)


Get the nth distinct column value ordered by another column I want to get the row with the nth distinct value in a column, as ordered by another column.

In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the email_address column does not have a unique constraint.

I believe I have done this correctly with the following query:

SELECT `email_address`
    FROM footable
    GROUP BY `email_address`
    ORDER BY `created_at` ASC
    LIMIT 1
    OFFSET 1000

But I am not totally confident that is giving me the right value.

Is the above query correct for this purpose? If not, how should I solve this?

You need two changes:

a major one: Using an non-aggregated column in a GROUP BY query will yield unpredictable result - it's a pity that default setting s in MySQL allow this type of query, and good that it is corrected in 5.7 version.
The problem with your query is that an email address can appear in many rows - with many different created_at values - and the question is: Which of these different values will MySQL use for the ORDER BY? The answer is any one of the them. It's usually the first it will find but "first" here can mean different things, depending on the execution plans and available indexes. So, the results will not be determinate and may change from one execution to another.
You can use MIN(created_at) instead for the ORDER BY and the issue  is solved.
a minor one: OFFSET x means "skip x rows". Replace 1000 with OFFSET 999:

SELECT email_address
    FROM footable
    GROUP BY email_address
    ORDER BY MIN(created_at) ASC
    LIMIT 1
    OFFSET 999 ;

Yes you can run those queries:

    SELECT
      email_address
    FROM footable
    GROUP BY email_address
    ORDER BY created_at ASC
    LIMIT 0,1000;

    SELECT
      DISTINCT email_address
    FROM footable
    ORDER BY created_at ASC
    LIMIT 0,1000;


#Deleted as values when using MS Access to query a db2 database through ODBC When trying to query certain db2 tables I get #Deleted for all values in the table. But for other tables it works fine.
The row count of this query is equal to the row count when using the data studio client so it is actually querying the same table, just the fields don't display correctly.
This data studio result looks like this

and the ms access result looks like this

Update
After checking other tables I have found a similarity. Tables with a lot of records give me the results (but the values are '#Deleted'). The other tables display the results correctly.

The cause of this problem most likely is the primary key of the table in DB2.

Access can not handle some data types as primary keys well.

Notoriously bad for PKs are all floating point numeric types. They may be rounded differently on the server and in Access. If Access uses a floating point value to query a single record from the server, there will be no results because the actual value on the server is different.
This is usually the cause for the #Deleted displayed in table cells.

Good data types for Primary keys are integer types (max int32) and string/varchar types (max length 255 characters).

Dates and fixed-decimal-point data types may be causing problems as well. That is mainly dependent on the actual backend database system and the used ODBC Driver. - I have no experience in that regard with DB2.

Here are some options to solve or work around this issue

If the problem is caused by date or decimal data types in the primary key column, you should check if there are settings in you ODBC-Driver that change its behaviour for those types. If not, you could research if there is another ODBC-Driver available that is more suitable for the use with Access.
Be aware that there will be data types that Access simply cannot support properly.
Lacking experience with DB2 I cannot give more detailed advice on this.
The best way to solve this issue is obviously changing the structure of the base table in the server database. If you only use unproblematic data types in all the primary key columns will make the problem disappear.
If Access is the primary frontend application for the database, I would strongly recommend going this way. Unfortunately there will be many scenarios where this is just not feasible.
If option 1 and 2 fail or cannot be used in your scenario, the workaround would be to create views on the server that wrap the problematic tables. You then link the views to the Access application instead of the base tables.

If you only need read only access to the data, this is a simple solution. When linking views you can (but don’t have to!) select the unique key of the view. If you don’t select any, the view will be read-only, but the data should be displayed properly.

If you do need direct write access to the data it becomes more complicated. You will need to create an artificial unique column in the view, by converting the data in the primary key column of the table to a varchar representation of the keys data. When linking the view to Access you select your artificial column as primary key. Now the data in the table should be displayed properly and should be editable (except for the key column of course).
This approach will incur performance penalties for the calculated PK-column is not indexed itself. You should investigate if the database supports persisting and indexing the artificial key column in the view.
If option 1-3 cannot be used, there is another option. Do not use a linked the table (or view) at all, but create a Pass-Through-Query in Access to query the data from DB2. PT-Queries are read-only though.

If you need to edit data returned by the PT-Query, you need to send custom SQL statements to the database to update the data. So in this case this will take considerably more effort than the other options.

The error occurring due to large amount of data in SharePoint list, maintain the data below 1000. The error will be resolved.

Note: For permanent data backup above mentioned method not useful. I am using access for only to upload the data into the SP list.


Changing sa password I'm trying to change the password of the sa account using SQL Server Management Studio 2012. I've followed the steps below but the password is still the same.

Login into the SQL Server Management Studio, Select Database Engine, \SBSmonitoring, Windows Authentication.
Go to Object Explorer--Security folder--Logins folder.
Right click on SA account and select the Properties option.
In General Page, change the SA password and confirm it.
In Status Page, change Login to Enabled. Click OK to save the change.
Restart the SQL Server and all its services.

Any idea ?

use this query it will change sa password

GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'newpassword'
GO

My understanding is that you should always change the password through the SQL Server Configuration Manager. Otherwise it may not correctly change throughout the system.


How do shrink *right* when required? I've been reading all the articles (and comments) linked from Stop Shrinking Your Database Files. Seriously. Now. I understand that shrinking is bad.

What I have a hard time figuring out is how to do it right when its actually required.

The backstory is that I have inherited this archive DB with a handful of tables containing PDF's that should have been expired after 5 years, but the procedures to do this on a schedule was never set up. I don't have the row counts at the moment but it's a few million, and about 3TB. I guesstimate about half of this will be deleted and never come back.

I've been talking to our infrastructure providers DBA, but I find the advice they give (delete, reindex then shrink) to be highly dubious. I'm a system consultant not a DBA, but it seems I get to play one this time :-/

Paul Randal recommends two approaches

1

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup
  using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

Unfortunately I don't have access to the actual server just SSMS, and this is quite a bit outside my comfort zone anyway. I'm not actually a DBA..

2

  DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.

Yes but how.. there are quite a few parameters to choose from, and this seems optimized to get the size absolutely minimal, and not what I need which is to get rid of the bloat while keeping the DB as healthy as possible.

In other words I don't care if the DB grows some, it needs space to operate and it will grow some as document production increases year by year, but the nightly, weekly or monthly purging will now keep this in check.

I should also add that I can take this DB offline at night without much trouble.

The recommendation from your Providers DBA is sufficient for your needs.  I would add the additional step of checking to make sure you don't have any heaps, however.  When you delete records from Heaps the space is not always made available and you won't have a good way of reorganizing it.  If you can, add a clustered index to the heap before deleting (or after, dealers choice).  If you cannot, I would delete, add a clustered index and then remove it.

SELECT T.name AS TableName
    , I.* FROM sys.indexes AS I
    INNER JOIN sys.tables AS T ON T.object_id = I.object_id
WHERE I.type_desc = 'HEAP'

So, my steps would be:

Check for Heaps and decide on course of action.
purge data (preferably in chunks), check-pointing or backing up the log as
applicable.
defragment indexes (Ola Hallengren's scripts will help
here if you don't have your own).  Do this first so that pages can be readjusted to the correct fill factor.  Otherwise, space used estimate will be off since a partially used page is counted as a full page and you will probably have lots of partially full pages that can be consolidated without impact.
Look at current database file size
used.  Shrink to appropriate size (I like to leave at least 20% empty
space in the file.
Defragment your indexes again since the shrink moved everything around.
Take the time to evaluate file growth settings
to make sure they make sense for your environment.  Usually growing
in fixed size rather than as a percentage and make sure you have
instant file initialization turned on.

This will tell you how much space is actually being used in the file.

SELECT DB_NAME() AS DatabaseName
, F.name
, F.type_desc
, F.state_desc
, F.file_id
, F.growth
, F.max_size
, F.is_percent_growth
, F.physical_name
, F.size/128.0 AS CurrentSize_MB
, FILEPROPERTY(F.name, 'SpaceUsed')/128.0 AS SpaceUsed_MB
 FROM sys.database_files F

I think you need to start facing the real problem here:

  archive DB with a handful of tables containing PDF's

While compression won't do much for you, is there any way to get rid of storing the PDF's in the DB and replace the blob with a link to an on-disk location?


Insert and Update with AES_ENCRYPT is mangling character set on double byte script DB and fields are UTF8_unicode_ci collation - MySql v 5.6

HTML charset is utf 8

Double byte script was entered successfully prior to this issue (Chinese, Japanese, plus Cyrillic etc) when using plain non encrypted input.

But trying to use AES_ENCRYPT on input mangles all non-english script.
DB field is BLOB.

 UPDATE mytable SET bodytext=AES_ENCRYPT('任車心是，防少劇和的；子反華不就','aeskey') WHERE t_id=123

produces strings of question marks - English is fine though

I'm not sure if I should try to CONVERT the input to UTF8 first - but when I try that I get an error.
Any suggestions of what to review?

Forcing utf8mb4 does the trick. Run a query against MySQL immediately after making the connection with:

SET NAMES'utf8mb4';

Alternatively, use these settings in your my.cnf file to get the same effect:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

(Note: These settings are defaults (for the most part) in MySQL 8.0 and MariaDB 10.3, so this probably isn't a problem there.)

Chinese (and other) scripts are multibyte - standard utf8 allows 3 bytes per character - fine for plain text but using AES_ENCRYPT ON 3 bytes requires another byte - thus setting utf8mb4 allows the required 4 bytes per character.


MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group A company is having a Microsoft SQL Server 2017 Enterprise cluster (RTM-GDR, 64-bit,  14.0.2027.2, KB4505224) consisting of one Availability Group (AG) with two servers, a primary and a secondary.
A closed source application used by this company seems to work fine with the exception of one piece of functionality. When this functionality is used, the following error can be found in its log file:
Cannot use SAVE TRANSACTION within a distributed transaction

A possible workaround, according to an unrelated website, is to disable distributed transactions by:
ALTER AVAILABILITY GROUP MyaAG
   SET ( DTC_SUPPORT = NONE );

After testing, this seems to solve the issue. However, I am uncertain about the impact of this change.

What impact does this change have?
Is the cluster still active?
Is the data still replicated to the secondary server?
Does it have a positive or negative impact on the performance?
Has a safety feature just been disabled?

So unfortunate news for you is it sounds like this closed source application uses a potentially deprecated feature of SQL Server if it's using SAVE TRANSACTION, and was not designed to run for a database that's part of an Availability Group (or any kind of clustering for that matter). Even more unfortunate is your only three options are:

Thoroughly verify it isn't breaking anything when you use that functionality of the application. That should involve testing that functionality and measuring the results on your primary, and then failing over to your secondary and verifying the same results did in fact commit to that node as well, and the application is still working as expected on the secondary. If all's well, then just live with the error.

Reach out to the vendor and communicate to them that you're trying to run their application on a clustered server with an Availability Group and are receiving an error for a specific function of their application that appears to use a deprecated feature of SQL Server, and that you need their confirmation if this is ok, and/or does their application support Availability Groups. You'll probably want do this anyway, if you pick option 1.

Disable distributed transactions with SET ( DTC_SUPPORT = NONE ); as you've seen this removes the error, but it does make your Availability Group less reliable which I'll answer directly to your questions below.

Out of these options, your best bet is #2, so that you can work with the vendor and let them know the full situation in which you're trying to use their application. It sounds like Availability Groups aren't directly supported by it and they'd be able to confirm that. Unfortunately for you, that's just a constraint from the vendor, and there's not much else you can do other than the options I provided above, and/or don't use Availability Groups / clustering, rather have an alternative high availability plan in place such as maintaining a completely decoupled, second server, which you regularly restore backups to. (Obviously this is less than ideal.)
To directly answer your questions on disabling distributed transactions:

What impact does this change have?

There is a risk of data loss from unhardened (uncommitted) transactions when a failover event occurs from the primary to the secondary. While this risk is potentially small, and I believe only affects your distributed transactions themselves (such as this application functionality with the SAVE TRANSACTION code) it's still a risk. This is discussed in the Microsoft Books Online for Configure distributed transactions for an Always On availability group, specifically when they say:

SQL Server does not prevent distributed transactions for databases in an availability group - even when the availability group is not configured for distributed transactions. However when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions.

Is the cluster still active?

Yes the cluster is still active and you should be able to easily test that by making a minor data change on the primary and watching it update on the secondary.

Is the data still replicated to the secondary server?

Yes, same answer as above.

Does it have a positive or negative impact on the performance?

Not relevant to the change, and probably measurably no difference.

Has a safety feature just been disabled?

Yes, your distributed transactions are at risk for data loss now if a failover were to occur when they were still active transactions (i.e. the secondary now becoming the primary wouldn't know if it's correct to rollback those transactions, rollforward or commit them), as discussed in my first answer to your questions. How much risk this is to you will depend on how important that data is for the feature that uses the SAVE TRANSACTION code, and anywhere else you may be explicitly using distributed transactions.

I think you can ignore the error, Save Transaction is used to create a savepoint in a transaction (but this is not allowed for distributed transactions), you lose the ability to do a rollback to the savepoint. The only one who will really know is your vendor.
But I would not disable distributed transactions - If you disable the distributed transaction then you won't enlist the 2 parties into a transaction at all. Your first task is to work out which is the other party in your distributed transaction - it could be another SQL Server, Oracle, MSMQ (unlikely but that's the last time I worked with MSDTC in earnest). Then you can assess if the distributed transaction is really required.
When you get this error what else happens ?
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-ver15