Speed up COUNT(*) when using an index I have a table similar to this (simplified):

CREATE TABLE books (
    id INT AUTO_INCREMENT,
    category INT NOT NULL,
    PRIMARY KEY (id),
    KEY (category)
);

This table has over 10,000,000 rows, at around 12 categories. So each category has an average of 833,333 books.

When querying for count:

SELECT COUNT(*) FROM books WHERE category=1;

Even though it's using an index when querying, this takes quite a long time to complete (several seconds.) How would you optimize this?

Previously, I had increased a number every time I inserted to books (into a table that relates category -> book count.) But our code is complicated, and many places insert or delete books. I know it's possible to solve this with EVENTS, but I'm asking maybe there's a MySQL feature I missed.

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query.

Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event.

UPDATE: To prove the query will partially read index category

mysql> select count(*) from books;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select category, count(*) from books group by 1;
+----------+----------+
| category | count(*) |
+----------+----------+
|        0 |       50 |
|        1 |       77 |
|        2 |       88 |
|        3 |       84 |
|        4 |      102 |
|        5 |       79 |
|        6 |       79 |
|        7 |       73 |
|        8 |       84 |
|        9 |       76 |
|       10 |       87 |
|       11 |       83 |
|       12 |       38 |
+----------+----------+
13 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from books where category = 6;
+----------+
| count(*) |
+----------+
|       79 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 79    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.01 sec)

my trick is, make temporary table with field category and units then u make schedule at night to counting it n save to your temporary table, then ur just read that table with just 12 row in it, very faster, but ur count H-1, u need liitle trick againt to add current day ...


Postgres startup fail error I was running some code when my laptop shut down. Now everytime I connect to the server, I get an error message as follows:

ambangra@access1:~$ LOG:  database system shutdown was interrupted; last known up at 2014-04-03 04:27:57 SGT
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/1864240
LOG:  record with zero length at 0/18668CC
LOG:  redo done at 0/18668A4
LOG:  last completed transaction was at log time 2014-04-03 04:19:34.929757+08
FATAL:  xlog flush request 0/18B02DC is not satisfied --- flushed only to 0/18668CC
CONTEXT:  writing block 0 of relation base/16384/12547_vm
LOG:  startup process (PID 14919) exited with exit code 1
LOG:  aborting startup due to startup process failure

Does it mean the code is still running? I am unable to connect at all. Any help would be greatly appreciated. Thanks!

Hi managed to clear it up using:

pg_dump data -f

I am able to connect to the server now.


Oracle 11g setup closes without errors on pre-requisite check I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check.

I have administrative access
UAC is disabled
IPv6 is diabled
There is more than enough free space
Windows 7 64bit
Oracle Administrator 64bit
No error in Event Viewer
No error in Installation log in Inventory\logs folder
Tried Run as Administrator also but didn't help

Any ideas?

I assume you are using the 64 bit version of Oracle to install.

I assume you have Windows 7 x64 - Professional, Enterprise, or Ultimate    edition

In the case of unusual errors it would not be impossible for something silly like the language pack in use on Windows to be the cause. Try starting with a new Windows install that has not been customized or has had any other Oracle products installed or removed

Check the    install logs.  With all the versions and folder name changes I am not  100% sure where but you should look in the c:/Windows/oracle or in  the install folder if located on hard disk

On 10g version, i use -debug. Ex

D:\SW_Imgs\Oracle\10201_database_win32\database>setup.exe -debug


Create MySQL CHILD and PARENT records at the same time and set CHILD.PARENT_ID by using MySQL Triggers I want to create relationships between parent and child when booth doesn't exist and when new child is created. This somehow can be done with Triggers but i don't know how to work with SQL.

After some googling i came up with Trigger concept bellow.

Create child
Create parent
Get last parent id
Update CURRENT child record with parent_id

Could somebody help me to achieve this?

DELIMITER $$
CREATE
    TRIGGER `database_name`.`trigger_name` AFTER INSERT
    ON `database_name`.`child_table`
    FOR EACH ROW BEGIN

    /*Step 1 - Create Parent record.*/
    INSERT INTO  `database_name`.`parent_table`(id) VALUES (``);

    /*Step 2 - Get last inserted ID of just created Parent record
      and assign to the variable*/
    DECLARE last_id int;

    SELECT LAST_INSERT_ID INTO last_id;
    FROM information_schema.tables
    WHERE table_name = `parent_table`
    AND table_schema = `database_name`;

    /*Step 3 - Update current record child_table.parent_id with last_id variable*/
    UPDATE NEW.parent_id = last_id;
END$$
DELIMITER ;

Or something like this, but it has errors.

DELIMITER $$

DROP TRIGGER IF EXISTS create_new_child; $$

CREATE
    TRIGGER create_new_child BEFORE INSERT
    ON child
    FOR EACH ROW BEGIN
    DECLARE last_id INT;
        SELECT AUTO_INCREMENT INTO last_id
        FROM information_schema.TABLES
        WHERE TABLE_NAME = parent
        AND TABLE_SCHEMA = DATABASE();
    INSERT INTO parent (title) VALUES (`TEST`);
    SET NEW.parent_id = last_id;
    END$$
DELIMITER ;

Unknown column 'parent' in 'where clause'

I think you have to perform this trigger before insert child row and there is a lot of errors in your code.

You must declare variables in the beginning of the script.
You give values to variables with SET
Only LAST_INSERT_ID() function is necessary and if you perform the trigger before update, you only have to set NEW value, and that's it.

DELIMITER $$
DROP TRIGGER `database_name `.`trigger_name` $$
CREATE
    TRIGGER `database_name `.`trigger_name` BEFORE INSERT
    ON `database_name `.`child_table`
    FOR EACH ROW BEGIN
    DECLARE last_id int;
    INSERT INTO  `database_name`.`parent_table`(id) VALUES (0);

    SET NEW.parent_id = LAST_INSERT_ID();
END $$
DELIMITER;

Finally got it working!

DELIMITER $$

DROP TRIGGER IF EXISTS create_new_child; $$

CREATE
    TRIGGER create_new_child BEFORE INSERT
    ON child
    FOR EACH ROW BEGIN
    DECLARE last_id INT;
        SELECT AUTO_INCREMENT INTO last_id
        FROM information_schema.TABLES
        WHERE TABLE_NAME = 'parent'
        AND TABLE_SCHEMA = DATABASE();
    INSERT INTO parent (title) VALUES ('TEST');
    SET NEW.parent_id = last_id;
    END$$
DELIMITER ;


SQL Server 2008 R2 Auditing I am new to database administration, and I need to implement auditing of all users of multiple databases.

Can we audit multiple users of a multiple database by creating just one audit specification?

I tried to create the audit specification, but I get the error message in the screenshot below.

The error message is telling you exactly what the problem is:

  You can only create audit actions on objects in the current database.
  (Microsoft SQL Server, Error: 33221)

The audit can only be on objects in the current database. Your screenshot shows that you are trying to audit two different databases: audit_test and db_aiudit

You will need to create two database audit specificiations - one for each of the two databases listed above a the respective database level

SQL Server Audit can only be done on Enterprise and Datacenter editions of SQL Server 2008 R2. With SQL server 2008 and 2008 R2 standard edition you couldn't perform auditing. But you can do the default trace for the basic auditing or you can write custom script for the auditing purpose. I advice you to use trigger is the best and a powerful way to detect the date and time of a specific query and details, also you can use the logon trigger ect...

For More details please have a look on Microsoft Documentation


Retrieving row count without using Count function I wrote a query below which shoots me a syntax error why would it do so,

SELECT MAX('Row') FROM

(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)

Error Desc:

Incorrect syntax near ')'.

I don't get it :(

If you want to get the exact count of rows in an efficient manner, then COUNT(*) is it. The ANSI standard (look for "Scalar expressions   125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start.

  If COUNT(*) is specified, then the result is the cardinality of T.

A ROW_NUMBER() function isn't a practical option: it isn't a counting function (it's  "ROW_NUMBER") and it will run badly as you add rows: a few 1000 will show how bad this it
SUM(1) may be optimised to COUNT(*) internally but I'd never use it
@@ROWCOUNT will require all rows to be returned by the first SELECT, which is a huge unnecessary overhead.

If you can live with approximate for SQL Server then use sys.dm_db_partition_stats. Marian's answer is out of date now since SQL Server 2005 added dmvs

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'Mytable' AND (index_id < 2)

See this on SO too for some more info: https://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table/6069288#6069288

In summary, there is exactly one useful way of getting the number of rows in a table. COUNT(*)

This is faster

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('Users')
AND (index_id<2);


Mysql daemon failed to start: unknown variable password=xxx Mysqld don't restart anymore...In the log, the apparently obvious reason:

mysqld started
[Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
[ERROR] /usr/libexec/mysqld: unknown variable 'password=mypassword'
mysqld ended

/etc/my.cnf there is nothing relevant and there is no such variable, which by the way has the correct mysql root password value in the message (!!!?):

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Mysql 5.0.95 on Centos 5.9. It worked ok before restarting!!

Tried also

 /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --no-defaults
Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld ended

I followed the last suggestion here

cd /usr
./mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --ledir=/usr/libexec &

Now it started, but don't understand it...not very nice!!!

I'v got same problem.
Just deleted password line from /etc/my.conf and mysqld started
Have no idea what was that...


Quickly appending data to text-column I'm using a statement similar to the following to append data to a column of type mediumtext to a bunch of rows:

INSERT INTO myTable (myKey,myVal)
ON DUPLICATE KEY UPDATE myVal=CONCAT(myVal,VALUES(myVal))
VALUES (1,'foo'),(69,'bar'),(1337,'baz')

At first this is really fast. But the more data there already is the slower it gets. It seems that when appending data, the whole field is read, merged with the new bit and then inserted again.

This mysql bugreport brings up exactly this issue:
https://bugs.mysql.com/bug.php?id=47937

Is there any way of making this faster?

My guess is the answer is NO. You won't make this any faster.

A database (at least all the ones I've worked with) doesn't treat text as you would with a Java StringBuilder, but rather like a Java String, which will suffer exactly the same problem (as would strings in most programming languages, except, maybe the V8 implementation of JavaScript). I wouldn't consider that a bug. It's a design decision. A database is not normally used in this fashion.

A StringBuilder has extra room for extra text, which is appended at the end of the already used space. When it actually runs out of space, it allocates a big chunk for further filling. A String is immutable and does not have extra room to add new text "at the end".

Perhaps what you should do is not modify your original row, and have instead a secondary related table where you store every string associated with myKey, together win an string_order column (could be an auto_increment, or a current timestamp) and, when needed, retrieve everything together with a GROUP_CONCAT of them. Your related table would just work as a kind of log, where you would be inserting the new "events" in order, a piece at a time.

At first this is really fast. But the more data there already is the slower it gets. It seems that when appending data, the whole field is read, merged with the new bit and then inserted again.

MySQL is an MVCC database. It's one the results of an MVCC that rows must be rewritten entirely on update. It may be even more surprising to you, but it's considered an optimization if the index does not need to be written to when you're rewriting a row.

  Assuming that mysql already knows the length of the field as part of the record storage, it should be possible to append data to a field without having to read it, so while the original query reads and writes the whole existing string, it should be possible to optimise that by recognising that source and destination fields are the same and simply appending to the existing value and increasing the stored length. This would return the complexity to a more manageable O(n), and thus give reasonable performance.

That would leave others vulnerable to a "dirty read." Likely there are too many real issues facing developers of MySQL to worry about this non-issue. You can't just write to a tuple. What if it's in someone else's snapshot? What if you want to ROLLBACK later? This is just simply not the way MVCC works. You copy and modify. Then you commit and flush.


Update a table using summed values from another table in MySQL/mariaDB I was trying to use the query below to update the ORDERS table by making the O_TOTALPRICE column up-to-date when the LINEITEM table is modified. And I was using a standard TPC-H database.

UPDATE ORDERS O, LINEITEM L
  SET
    O.O_TOTALPRICE = SUM(L.L_EXTENDEDPRICE * (1 - L.L_DISCOUNT/100) * (1 + L.L_TAX/100))
  WHERE
    L.L_ORDERKEY = O.O_ORDERKEY;

However, this results in 1111 - Invalid use of group function.

The operation is like this:

-----------------LINEITEM----------------       ---------ORDERS---------
L_ORDERKEY  L_EXTPRICE  L_DISCOUNT  L_TAX       O_ORDERKEY  O_TOTALPRICE
==========  ==========  ==========  =====       ==========  ============
1           12.99       20          8.5     ┬   1           <TO CALC>
1           8.99        30          11      ┘
2           99.99       10          10      ┬   2           <TO CALC>
2           49.99       0           8.5     ┤
2           3.99        50          14.7    ┘

Since there are multiple rows in LINEITEM corresponding to the record in ORDERS, I tried to add GROUP BY L.L_ORDERKEY, but that just came to a syntax error.

Is this possible to accomplish in the UPDATE statement, or does it have to be split to multiple statements? Thanks!!

Assumptions:

L.L_EXTENDEDPRICE should be L.L_EXTPRICE
L_DISCOUNT and L_TAX are percentages, in which case the values stored in LINEITEM need to first be divided by 100.0 before performing calculations

Some sample data:

drop table if exists LINEITEM;
drop table if exists ORDERS;

create table ORDERS
(O_ORDERKEY      int
,O_TOTALPRICE    decimal(10,2));

insert into ORDERS values
(1,0.0),
(2,0.0);

create table LINEITEM
(L_ORDERKEY      int
,L_EXTPRICE      decimal(10,2)
,L_DISCOUNT      decimal(10,2)
,L_TAX           decimal(10,2));

insert into LINEITEM values
(1,12.99,20, 8.5),
(1, 8.99,30,11),
(2,99.99,10,10),
(2,49.99, 0, 8.5),
(2, 3.99,50,14.7);

Contents of ORDERS before the update:

select * from ORDERS;

O_ORDERKEY  O_TOTALPRICE
----------  ------------
         1          0.00
         2          0.00

Proposed update statement:

update ORDERS O
set    O.O_TOTALPRICE  = (select sum(L.L_EXTPRICE * (1 - L.L_DISCOUNT/100.0) * (1 + L.L_TAX/100.0))
                         from   LINEITEM L
                         where  L.L_ORDERKEY = O.O_ORDERKEY);

Contents of ORDERS after the update:

select * from ORDERS;

O_ORDERKEY  O_TOTALPRICE
----------  ------------
         1         18.26
         2        155.52

Here's a rextester fiddle of the above.

UPDATE  ORDERS
    JOIN
        ( SELECT  L_ORDERKEY,
                  SUM(L.L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS ep
            FROM  LINEITEM
            GROUP BY L_ORDERKEY
        ) AS j  ON j.L_ORDERKEY = ORDERS.O_ORDERKEY
    SET ORDERS.O_TOTALPRICE = j.ep;

If that fails, try this syntax:

UPDATE  ORDERS
    SET O_TOTALPRICE =
        ( SELECT  SUM(L.L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX))
            FROM  LINEITEM
            WHERE  L_ORDERKEY = ORDERS.O_ORDERKEY
        )


Is it safe to pg_dump and pg_restore a new postgres database that has malware? I'm pretty sure there is a crypto bot eating up my CPU through a postgres script. I would like to create an entirely new VM, and move my database with it using pg_dump and pg_restore. I already checked my postgres for new users, tables, databases; couldn't find anything odd there which could comprise me if I move my data. I'm a little worried however because the bot is some how getting access to my postgres, and nothing else on my VM.
Thank you for the help.
EDIT:
I managed to move my db with pg-dump and pg-dumpall. It's been a few days since I destroyed my last VM and made a new db with better security. It looks like I'm in the clear at least for this same attack.

Probably.  But if you just recreate the same vulnerable setup, you will just get infected again from scratch.  They probably got in by guessing the weak password on a super-user account.  So set up all new passwords on the new system and make then strong ones. (pg_dump will not carry over your old passwords anyway, but pg_dumpall will.). It would also be good to lock down your new pg_hba.conf so that superusers can only connect locally, or at least only from white-listed IP addresses.
They probably created a new function which gives them shell access, and pg_dump will carry over that function definition.  But if they can't log in in the first place they can't use it anymore; and it probably relies on a dropped binary .so file, which pg_dump will not carry over.

I don't know what kind of crypto bot you're talking about but if you're going to start up with a new VM, you can safely use pg_dump plain text output (default Fp format) and restore it by using psql. If you're experiencing an issue infecting data structure of your PG data directory, I strictly recommend plain text backup since it's strictly SQL sentences. If you need to move users/roles you can also back them up with pg_dumpall.


How to change a script task version in SSIS? I added a script task to an SSIS project in VS2015. When I deployed to SQL Server 2016 I got an error message that the version 15.0 script is not supported.

Where does this version 15 come from? Reading other, similar questions on Stack Overflow I see that you can set the target version of the project to SQL Server 2012 - which I did (the eventual deployment target is SQL Server 2012).

I also tried deleting and recreating the script task. And in the information of the script it says it's using V10 of C#.

How can I resolve this?

  Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task ""ST_a1ad9dc5972c42b68c12a13155f10b6d"" uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
         at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)"

I have also opened the project in SSDT 2012 and rebuilt with a different name. Same error. It seems that there must be a reference that wasn't deleted or something.

None of the solutions on this question (https://stackoverflow.com/questions/34893267/ssis-script-task-vs15-not-work-when-deploy-on-sql-server-2014) worked.

Looking at the XML in the package where the script is, I can easily find that task, and there is no reference to version 15 anywhere.

=========== EDIT

After copying the project to the machine that hosts the database, opening VS2015 and deploying from there, the package executes.

And then when going back to my machine and building there, it doesn't.

Is this a bug? Or am I doing something stupid by expecting the build to produce the same deployment wizard as using the wizard from VS...

I have SQL Server 2016 (13.0.4411.0), ssisdb has the schema version (13.0.1601.5).

I am using an integration services package created in visual studio 2015. The script component has the path: C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\VSTA14_IS_ST_CS_Template.vstax It won't let me execute the package through the integration services catalog (due to the message experienced by Zach). However it appears that it will let me execute it through the file system (using SQL Agent). Unsure if this is working, will update this once the package has completed.

I believe I have the same issue, and here is the work around that got over my issue.

Details first:

I have sql server 2016 (13.0.4411.0)
SSISDB has the schema version (13.0.1601.5)
I am using an integration services package created in visual studio
The script component (Which states it is 2015 C#) has the path:
C:\Program Files (x86)\Microsoft SQL
Server\130\DTS\Binn\VSTA14_IS_ST_CS_Template.vstax

If I deploy to the catalog through SSMS, it won't let me execute the package through the integration services catalog (due to the message experienced by Zach - version 15.0 not supported).

The workaround:

If I deploy the package through Visual Studio to the 'Integration Services Catalog' on the required instance, this message goes away and the project runs successfully. This is not ideal as we should be able to deploy through SSMS, but meant that the project can progress.

I thought I would add to this, as I was getting the same error.  I am running VS 2017 and deploying to a 2016 SQL Server.  I read quite a few articles, then realized just how easy it was for me to fix this.  VS 2017 has great backwards compatibility.

This article on changing the version of an SSIS package to match the version of your target server helped me resolve the error in my case.


SQL Server 2016 Force Encryption Option We are using SQL Server 2016 and want to use the Force Encryption option.  From what I read, is it just installing the certificate on the Server itself and then setting the ForceEncyption to "Yes"?  Is there anything needed on the client side like updating the connection string that it uses to connect?  Or does setting the ForceEncryption take care of it?

Sorry, I'm new to encryption and we want to implement that on our SQL servers and it seems straight forward but I'm getting conflicting instructions as to whether the data connection string needs to be modified or not.
We are also using AAG as well so will implementation be different as well?

According to this StackOverflow post, if you have the server-level settings (referenced below) configured with ForceEncryption set to Yes, then that will enforce an encrypted connection regardless of the SSMS GUI Encrypt connection option being checked or not prior to making connections to that server. This may be a sufficient workaround for people where the "encrypted connection" is of more importance than the actual option being checked within the SSMS GUI.

According to Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager), you do this as such:

  To configure the server to accept encrypted connections

  In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then
  select Properties.
  In the Protocols for Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for
  the Certificate box, and then click OK.
  On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
  Restart the SQL Server service.

  To configure the client to request encrypted connections

  Copy either the original certificate or the exported certificate file to the client computer.
  On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.
  In the console pane, right-click SQL Server Native Client Configuration, and then click Properties.
  On the Flags page, in the Force protocol encryption box, click Yes.

  source

The server certificate doesn’t get exported to the client computer but the client does need to have its own certificate. Both certificates need to be descended from a recognized and trusted root authority.

If the SQL Server service is running under an account that’s not a local administrator, you’ll need to grant Read permission on the certificate to the service account.


how to remove Mariadb temp files I have a /mariadb_tmp directory (as specified in my mariadb-server.cnf) which is filled with files dating back to last November.

-rw-rw---- 1 mysql mysql   1032192 Nov 19 07:47 #sql899_5d5_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ab8f_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ad17_0.ibd
-rw-rw---- 1 mysql mysql    638976 Nov 19 09:32 #sql899_baf3_0.ibd
-rw-rw---- 1 mysql mysql    409600 Nov 19 09:32 #sql899_bbec_0.ibd
-rw-rw---- 1 mysql mysql    262144 Nov 19 09:35 #sql899_c96a_0.ibd
...
-rw-rw---- 1 mysql mysql    704512 Jan 16 10:08 #sql899_603c0c4_1.ibd
-rw-rw---- 1 mysql mysql    540672 Jan 16 10:08 #sql899_603c123_0.ibd
-rw-rw---- 1 mysql mysql 494927872 Jan 16 10:20 #sql899_603eb7b_0.ibd
-rw-rw---- 1 mysql mysql    606208 Jan 16 10:27 #sql899_60470ff_1.ibd

Googling on 'delete old mariadb temp files' leads to advice such as:

drop table `#sql899_5d5_0`;

which results in the error 'no database selected.'  Of course, I don't know what database each tmp files comes from, so I don't know if that would work.

There are no *.frm files to go with these, and they are in the specified tmpdir directory, not the datadir.

Any idea how to clean these up?  They are taking up a bunch of space on a disk that we really can't expand (for non-technical reasons) nor should we have to keep these around that long.

Thanks,

Ed Greenberg

After checking they aren't attached to an active process, ls -la /proc/$(pidof mysqld)/fd, or fuser {filename}, they can be removed with rm.

From MariaDB-10.4 onwards you shouldn't see these created on disk again ( MDEV-15584).

I often use the following commands:

sudo find /tmp -type f -atime +10 -delete

OR

find /tmp/* -type d -mtime +5 -print0 | xargs -0 rm -rf


Bulk Delete for Large Table in MySQL I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month.

If I do DELETE FROM NOTIFICATION WHERE CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day);, all the IOPS will be taken, the process will take hours, and a lot of new entries cannot be inserted due to "Lock wait timeout exceeded; try restarting transaction".

I was trying to do the way describe in here:http://mysql.rjweb.org/doc.php/deletebig
However, I am using UUID instead of increment ID.

What is the correct and efficient way to delete those rows while not affect new data being insert/update?

Make a temp table, switch it in and out, and copy the last 30 days data into it.

#
# Make empty temp table
#
CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION;
#
# Switch in new empty temp table
#
RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION;
#
# Retrieve last 30 days data
#
INSERT INTO NOTIFICATION SELECT * FROM NOTIFICATION_OLD
WHERE CreatedAt >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

In your off hours, drop the old table

DROP TABLE NOTIFICATION_OLD;

Here are the Advantages to doing DELETEs like this

NOTIFICATION is emptied fast by means switching in an empty table.
NOTIFICATION is immediately available for new INSERTs
The remaining 30 days are added back into NOTIFICATION while new INSERTs can take place.
Dropping the old version of NOTIFICATION does not interfere with new INSERTs
NOTE : I have recommended doing bait-and-switch for table DELETEs before : (See my July 19, 2012 post : Optimizing DELETE Query on MySQL MEMORY Table)

Give it a Try !!!

create table notification_temp as select * from notification where CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day) ;

drop table notification;

RENAME notification_temp TO NOTIFICATION;


Tools and best practice for reporting/presentation database Currently my employer has a team that creates tableau dashboards and another very large team that creates ad hoc and regular reporting for external clients.
Both of these teams pull data from many databases and other sources.  For each report they either create a script that lives on their machine, a stored proc that lives in a source database, sometimes a scheduled job on a jenkins instance, or manually copy and paste the results of a query.  It's a badly documented mess.

My solution, that will have the least impact on how they work, and cost to implement, is to create a database on which all of the extracted data will live, so the reports and dashboards only access this single source.  A task scheduler to run regular extraction jobs, and a fairly simple custom software application that tracks changes to the source databases and alerts the project owners (of the jobs) but can also be used by the database owners to determine which downstream jobs will be impacted by changes to their databases.

I was wondering if what I have described already exists as a concept (is a presentation database a thing?) or if there exists software that could do the bulk of the heavy lifting for me? I after any tools that I might look into as alternatives, or concepts that might help my better design this solution.

So data needs to be extracted mainly from mssql, jira, jenkins and few custom sources.  I'd be storing the extracts on RDS and running the jobs once migrated on Jenkins.  I'd be building a custom web based UI for some very specific interactions, but monitoring would all be tableau dashboards.

The RDS instance wouldn't be a true data warehouse or vault, more akin to a lake I guess, as the data stored there would be aggregated from the disparate systems for a very specific purpose per table and would not be linked to each other in anyway.  This is basically replacing a huge number of regular extracts living all over the various systems and disks and instead storing them in a single database.

So I'm wondering in particular if this setup is common? and if there are any tools I should be looking at that might do some of this already.

It seems you are referring to a concept called a data warehouse.  Data Warehousing is a huge topic, with a few main types of architecture: star schema, snowflake, and data vault. Googling these might give you some ideas on how to proceed.

May be it is helpful if you provide the names of the source systems you are extracting the data from .and also what platform do you have access to? where is this new database is going to be hosted/housed? DWman has already told you the perfect place to store this data-in the data warehouse.

If you have access to cloud services like aws or azure and if you are planning to house the database in azure ,you will get free tools for data extraction from the sources , transforming the data into a final format , that is how you want to see it in your reports and store it in a single place (the data warehouse). your solutions is a prefect answer to the problem!. you just have to put together the right tools to achieve it.

Well basically a Data warehouse is like any other database except that the data is in a different Star or snowflake schema  ,though i am not very expert with RDS systems , i know that it houses Microsoft sql server . so what that means for you is you can house the database(data warehouse in RDS and you will get ETL tools for free i.e Sql server data tools(SSDT) and with SSDT you should be able to leverage existing code your report developers were using to extract data from the different source systems  with little or no modification . you can also get free report authoring and distribution services such as Microsoft sql server reporting services or power bi for free with the same license. but this all set up and management is going to require some skill in several Microsoft business intelligence tools ([Microsoft BI stack]


Query to back up a PostgreSQL database I made a CRUD web application that uses a small PostgreSQL database. It is a donors database with the columns name, telephone, blood type, etc.

I have some SELECT and INSERT INTO queries, but now I need a query to back up the database. Nobody has asked about it on this site before, so I think it can't be done, or else I have to make a script using pg_dump or a maintenance plan).

Is it possible to write a SQL query to perform a PostgreSQL database backup?

PostgreSQL does not generate a database backup using a query as SQL Server does (ex: backup database [db_name] to disk...)! It is a different RDBMS; you can't simply do something that other one does, just because you want!

There are 2 methods to take backups from Postgres as commented before. Using pg_dump, you can produce a full sql file including all DDL and DML statements to recreate your database in another place (or restore):

pg_dump -Fp -v -h YOUR_HOST -U YOUR_LOGIN -d DATABASE_NAME -f your_file_name.sql

PostgreSQL pg_dump documentation

Or you can take physical backups by copying the pg_data folder.

It's up to you decide!

Use pg_dump from command line. the pg_dump exe is in your bin folder inside the Postgres installation directory.

sudo pg_dump -F c -b -v --dbname=postgresql://myuser:mypassword@lcoalhost:5432/mydbname > /home/ubuntu/mydatabase.backup (or mydatabase.sql)


Why does ORDER BY not belong in a View? I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)

I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the SELECT statement querying the view.

But being relatively new to practical SQL and the usages of views, I would like to understand why this is done so by design. If I've followed the history correctly, this was once possible and was explicitly removed from SQL Server 2008 and so on (don't quote me on the exact version).

However, the best reason I can come up with as to why Microsoft removed this feature is because "a view is an unsorted collection of data".

I am assuming there is a good, logical reason as to why a View should be unsorted. Why can't a view just be a flattened out collection of data? Why specifically un-sorted?  It doesn't seem that hard to come up with situations where (at least to me / IMHO) it seems perfectly intuitive to have a sorted view.

(Indexed views aside, of course.)

A view is not materialized - the data isn't stored, so how could it be sorted? A view is kind of like a stored procedure that just contains a SELECT with no parameters... it doesn't hold data, it just holds the definition of the query. Since different references to the view could need data sorted in different ways, the way that you do this - just like selecting from a table, which is also an unsorted collection of rows, by definition - is to include the order by on the outer query.

Also to give a little insight into the history. You could never put ORDER BY in a view, without also including TOP. And in this case the ORDER BY dictated which rows were included by TOP, not how they would be presented. It just so happened that in SQL Server 2000, if TOP was 100 PERCENT or {some number >= number of rows in the table}, the optimizer was fairly simplistic and it ended up producing a plan with a sort that matched the TOP/ORDER BY. But this behavior was never guaranteed or documented - it was just relied upon based on observation, which is a bad habit. When SQL Server 2005 came out, this behavior started "breaking" because of changes in the optimizer that led to different plans and operators being used - among other things, the TOP / ORDER BY would be ignored completely if it was TOP 100 PERCENT. Some customers complained about this so loudly that Microsoft issued a trace flag to reinstate the old behavior. I'm not going to tell you what the flag is because I don't want you to use it and I want to make sure that the intent is correct - if you want a predictable sort order, use ORDER BY on the outer query.

To summarize and just as much to clarify a point you made: Microsoft didn't remove anything. They made the product better, and as a side effect this undocumented, non-guaranteed behavior became less reliable. Overall, I think the product is better for it.

you can create a view which has order by and preserves the order by when queried after:

select top 99.999999999999 percent * from ..... order by


Lots of Indexes - MySQL vs MongoDB - Migrating So, I've never used MongoDB, I just read a lot about it and I think it's going to be good for my project. Also, I do not have lots of experience with MySQL and to be even more honest I have no clue of what I'm about to ask.

Scenario:

MySQL table profile:

id = [pk, auto_increment, smallint]
user_id = [pk, fk, varchar]
category_id = [pk, fk, smallint]
role_id = [pk, fk, tinyint]
country_id = [pk, fk, smallint]
state_id = [pk, fk, smallint]
legal_document = [pk, varchar, ?unique]
name = [pk, varchar, ?unique]
type = [pk, boolean]
last_activity = [pk, date]

All the fk you see there are MySQL tables of course. Then I'm thinking to use MongoDB to store the profile info, profile_info collection should contain documents like:

{
  '_id' : 1 (profile_id),
  'address': 'Some street in some state of some country :P',
  'phone': [5555555, 5555555],
  'email': 'example@example.com',
   etc...
}

I'm planning to use mongodb because my project needs to be public ASAP and we may add several new profile info and other things and I do not want to do alter table and migrate from tools with lots of rows.

With that said, we may have to add a few more pks to the MySQL table, so I was thinking of migrating all the project to MongoDB and I do not know if that is a good move.

Questions:

It's better to keep the pks in MySQL and trivial info in MongoDB, or would it be fine if I move all to MongoDB?
May MongoDB be faster if I just migrate the whole project but I keep the structure like that? I mean like having profile and profile_info collections instead of just profile.
I'm worried about how many resources MongoDB could use for a 'table/collection' with that many indexes, I want to keep disk space and ram at minimum use. Is there a critical difference between MySQL and MongoDB?

PS: SSDs are going to be used in the system.

PS II: All tables are just planned, nothing is written yet. I'm a bit of plan very ahead person so please be patient with me.

Given your description I strongly suggest against using MongoDB. Not because it would necessarily a bad choice (although I believe it is in your case for reasons other than pure technical ones).

Here are the points that caught my eye.

Data modeling

Trying to use MongoDB with relational data model with no adaptations almost always leads to tears and misery except for the most trivial use cases. And that is the better end of the story. The worse end is loosing money, potentially big time.

The reason for this is that with SQL, you identify your entities and their attributes and relations and then bang your head against the wall for a few hours to get your upper left above and beyond JOINS right to get your questions derived from your use cases answered. All while avoiding data redundancy like the devil holy water.

Data modeling with MongoDB works different. You identify the use cases and the questions derived from them and model your data in a way so that those questions can be answered in the most efficient way.

Since this is a bit abstract, let me give you an

Example

Let us assume you have a web application called "chirper", with users doing chirps. With SQL you would now model your data, coming up with something like a user table and a chirp table.

The first use case you encounter is that you want the latest 10 chirps to be displayed on the applications home page, together with the username of the chirper. With SQL it is easy enough, you do a join on chirps and users, sort the result descending by data and limit to 10 records.

With MongoDB, you'd have a careful look at what you need. For various reasons you do not want to embed the chirps into the user collection. But since you do need to relate the chirps to a user anyway, you decide to do so by username and come up with a "schema" for your chirps collection like this:

{
  _id: ObjectId("570b87a56931b8f21a8bf25c"),
  user: "jdoe",
  date: ISODate("2016-04-11T11:17:08.415Z"),
  text: "Chirp!!!"
}

and, miraculously enough, all you have to do is to do a

db.chirps.find({}).sort({date:-1}).limit(10)

which gives you the same result without a JOINS at the expense of relatively cheap disk space.

Resource limitation

  I want to keep disk space and ram at minimum use

MongoDB is a lot, but surely not resource friendly. It was never meant to be a replacement for MySQL – which by the way was specifically designed to be a relatively lightweight general purpose RDBMS. I strongly advice against running MongoDB and anything else on the same server for production purposes. The reasons are manifold, but here are the most important ones:

MongoDB will take up to 85% - 90% of your available, physical RAM. This is because the working set (the indices and a subset of the data) are kept in RAM.
Depending on the storage engine and its configuration you use for MongoDB, even CPU utilization can be considerable.
A heavily loaded MongoDB needs a lot of disk IO. I have seen systems with SSDs in a RAID0 in which the IO rate was at its limit.

Leaving out the petty details for the moment, this would mean that during situations where you do not want it the most (your application is taking off and has a lot of users), MongoDB would battle with the other parts of the application over resources.

Do not get me wrong: MongoDB is not a resource eating monster per se. But if it does what it is intended to do, namely dealing with huge amounts of data and data changes, you do not want to have the parts of your application battle for resources.

That being said: There are ways to limit MongoDBs resource allocation and make sure those limits are obeyed. Probably the most well known as of today is Docker. You have to decide wether it is worth it to run MongoDB in a Docker container.

As for the data file size: There is transparent compression available for the default wiredTiger storage engine. You can choose from either snappy(default) or the better known zlib compression. Both come at the expense of a higher CPU utilization, as mentioned above.

Time to market

Forgive me, but you demonstrate a lack of basic knowledge about MongoDB. Since you are not sure what to do, the best you can do is to research every step carefully, make a decision, rinse and repeat. And I am not even taking into account that you would need to remodel your data and most likely make massive changes to your applications persistence layer. If I were you and wanted/needed a fast time to market, I'd go with what I have as of now. In case problems occur during production, I'd get myself a consultant specializing on NoSQL to find out wether one of the various NoSQL databases suits your needs, identify said DBMS (which may or may not be MongoDB), get myself a specialist for that and do the migration only then. With a specialist on your side.

Administration

A mistake commonly made by people is that they think it is as easy to manage a MongoDB deployment as it is to getting it to work. It is not. Any DBA, Sysadmin or even DevOps (even when used in the wrongest sense of the word) should be able to get a sharded cluster running. Choose the proper dimensions, identify problems, decide when to scale, deal with non-trivial problems and errors? Not so much. And note that the latter is highly subjective and prone to the skills of the DBA in question. Do you really want to store your production data on a system you barely understand?

Conclusion

Imho, MongoDB does not fit the requirements you stated. Changing now will most likely increase your time to market, and very much so since you do not understand the intricacies and pitfalls of MongoDB. In the worst case you built up technical debts while simultaneously diminishing the performance of your application.

MongoDB is less well suited for your resource limitation requirements, and finding a sweet spot for resource limitations that give you an acceptable performance requires an experienced DBA and quite some time, during which your application might run with sub-par performance.

Let me put it in a bit of context: MongoDB might very well be suitable for your use cases, and even excel. But I do not think it is feasible to change your persistence technology as of now, given your level of knowledge and the stated requirements.

hth

Fill the SQL tables with the worst case, a looooooot of happy users! Then see how performant it is with a lot of queries and then try to copy the data into Mongo. Might help you decide when the best for switching is. I usually go with NOW! ;-)

Also not sure what SQL offers these days, but in Mongo you can do sharding across multiple servers if the collections get too full, replica sets if one server cannot handle all the queries, ... best talk with your doctor or someone who does this for a living.


Read-Scale Availability Group between a Transactional subscriber and another machine I am trying to configure Read-Scale replica between a transactional subscriber and a 3rd machine. I could create the group. But, when i attempted to add the DB (which is a transactional - subscriber) to be part of my read scale replica group, I am getting the following message and not able to complete my read scale replica. My replication is already configured and working fine. Configured the AG as well. All i am doing is, trying to add the DB to the AG.
Here is the message:
Special considerations exist when adding a replication Subscriber database to an availability group. For more information, see "Replication and Availability Groups" in SQL Server Books Online.
But the BOL is not saying anything about AG on a subscriber. Any help on this would be highly appreciated.

what sql server version are you using?
The distribution database cannot be placed in an availability group with SQL Server 2012 and SQL Server 2014. Placing the distribution database into an availability group is supported with SQL 2016 and greater, except for distribution databases used in merge, bidirectional, or peer-to-peer replication topologies


Creating an admin user in Oracle How can I create a "root" (like on MySQL) in Oracle?

I installed Oracle database on my system, for homework, for school (faculty) but it didn't give me the option to make a root user. Well at least I don't think so.

It asked me on preparation for install to create a password but when I tried connecting with the id and password it failed. (So that is why I presume it did not make a root user)

Connected on the databse with connect /as sysdba but don't know how to create a user with admin privileges.

Tried

create user "user" identified by "password" with grant option;

(error)

create user "user" identified by "password";

(succes)

grant all on "user" with grant option;

(error)

I want to have all privileges on all tables, etc.

you first need to connect as root to the database, then you create the admin user.

console

connect /as sysdba

sql

create user admin identified by secret
grant dba to admin
conn admin/secret

create user Rokon identified by Rokon;

grant create session, grant any privilege to Rokon;
connect Rokon/Rokon@dlicl


Shrinking the log file does not reduce size I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL.

When I try to shrink the log file, it's not shrinking.

I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file.

When I ran

DBCC SQLPerf(logspace)

I found that the log size is 4932 MB and Log space used is 98.76%!

Then I tried this command

USE <databasename>;
DBCC loginfo;

Now almost all VLFs are "status 2" which means all are in use.

I tried to take a log backup and then shrink the log file. Shrinking didn't reduce the size.

I changed the recovery model to SIMPLE and tried shrinking again, but this also didn't help.

I checked for open transactions

DBCC opentran (database);

and found that no transaction is open now.

What is stopping me from shrinking the log file? How can I solve this?

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.

My work around for the Replication that is blocking shrinking log file is:

Set DB Recovery Model to Simple
Take DB offline
Create backup of log file (just in case)
Delete log file
Bring DB online

In my case it worked. After bringing DB online log was created automatically and it's size was 512kb instead of 70GB. But this is only a workaround. The root problem is not resolved. In my case we are using replication.


Granting a user account permission to create databases in PostgreSQL How do I give a user account in PostgreSQL the ability to create and drop databases?  Is there a way to do this with GRANT?

It's done with ALTER USER username CREATEDB;

See ALTER USER in the doc.

To drop a database, either you're superuser (which can be granted with ALTER USER too) or you must own the database.

Create user from the start with CREATEROLE and CREATEDB permissions

After you've logged in to the PG server with the command line client, with a user that has the appropriate rights to create users (like the postgres user, which by default on UNIXes can be impersonated easily by the system super user with $ sudo -u postgres psql postgres):

CREATE ROLE user_name PASSWORD 'tYPe_YoUr_PaSSwOrD' NOSUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

NOSUPERUSER - the user being created does not have superuser rights (like the postgres user, recommended).
CREATEDB - the user being created can create databases which it will own.
CREATEROLE - the user being created can create roles (logins) for objects it owns or has specific access to (like databases it has created).
INHERIT - the user being created inherits some default options, optional.
LOGIN - the user being created has the right to log in.

The PG binaries (applications) can also be used. For example, the above SQL is equivalent to running:

sudo -u postgres createuser --createdb --createrole --pwprompt user_name

Give existing user rights to create DB

After you've logged in to the PG server with the command line client, with a user that has the appropriate rights to alter users (like the postgres user, which by default on UNIXes can be impersonated easily by the system super user with $ sudo -u postgres psql postgres):

ALTER USER user_name CREATEDB;

or, to give the same rights as the examples above:

ALTER USER user_name CREATEDB CREATEROLE LOGIN;


SQL Server 2012 - login mapping to user I'm still struggling a bit in moving from Oracle to SQL Server, especially with the login-vs-user thing.  I've restored a database from a client site so we can have a development copy. I want to set up a login with that new database as the default db, and map it to a user of that database. But everything I've tried results in a "user or role already exists" error. I'd like for this login to only have access to this one particular database.  I've read about users and logins, but I'm obviously missing something here.  Can the login have the same name as the database user?  What I've read seems to imply it can, but I get an error.

Harry

It is pretty simple, you just do the following two steps

1st: in sql server, create a new login using the user name in that database, like

use master;

create login [MyUserName] with password='mypassowrd123$', check_policy=off;
go

2nd: map the login with the user name inside that particular database, let's call it [xyz]

use [xyz]
alter user [MyUserName] with login = [MyUserName];
go


How do you set the pagesize, codeset and table space when creating a database in DB2? I want to create a database in DB2.

I found some information on  this page

I want to use only some options which are explained on the page:

PAGESIZE
CODESET
TABLESPACE

Then, I want to add/configure the buffer pools.

How should the create SQL look ?

CRAETE DATABASE myDatabase
USING CODESET UTF-8 TERRITORY US
COLLATE USING SYSTEM PAGESIZE 8192

CATALOG TABLESPACE 16K  -- Is the 16K ok?
USER TABLESPACE 16K  -- Is the 16K ok?
TEMPORARY TABLESPACE 16K;  -- Is the 16K ok?

To answer your question, yes you would specify the codeset, territory, collation, and pagesize like you do above. (And as a note, your link is off, you probably mean something like this which is the page for DB2 LUW 9.7 as an example.). By what you are setting up, you are specifying a 8K default pagesize. If you wanted a 16K default pagesize you would need to specify that.

And yes, 16K tablespaces are fine; it just depends on what you are doing.

But I will note that you can't catalog tablespaces. I think you meant to say that you want the CREATE TABLESPACE statement. And yes, if you do not specify a pagesize in your create tablespace statement, then it takes the default as specified by your CREATE DATABASE command.

Personally, I do not think there is anything wrong with specifying exactly what options you want with any statement, even if they are the defaults. For the following two reasons:

You know exactly what settings you are choosing to use and (hopefully) why.
In case IBM ever changes the default behavior, you are not left wondering why your scripts broke. You can continue to use the settings you choose and migrate to IBM's new defaults when you choose to (again, by explicit settings).

You should have just this and leave the rest to defaults:-

 CREATE DATABASE myDatabase


CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table @@Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a Geography datatype?

DECLARE @xPolygon NVARCHAR(MAX)
     = '53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670'

CREATE TABLE #xSpatialTable (
     id         INT IDENTITY (1,1)
    ,GeogCol1   geography
);

INSERT INTO #xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

The Second Compute Scalar operator in the actual execution plan for Expr1005 shows this

Scalar Operator(CONVERT_IMPLICIT(geography,[geography]::STPolyFromText((N'POLYGON(( '+[@xPolygon])+N'))',(4326)),0))

There is no such CONVERT_IMPLICIT if I change to using a user table instead of a temp table.

CREATE TABLE dbo.xSpatialTable (
     id         INT IDENTITY (1,1)
    ,GeogCol1   geography
);

INSERT INTO dbo.xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

Here's the actual execution plan

If I try the same test on a SQL 2014 database on the same machine, I don't get a convert_implicit whether I use a temp table or a user table.

Why the difference on 2008R2? and how do I get rid of the Convert_Implicit when using a temp table on 2008R2?

I don't think this has anything to do with the spatial extensions. I think this is this problem here,

From that post, all these work without generating CONVERT_IMPLICIT

SELECT * FROM [Table] WHERE IsSomething = 'TRUE';

SELECT * FROM [Table] WHERE IsSomething = Cast(1 as bit);

DECLARE @TRUE bit;
SELECT @TRUE = 1;
SELECT * FROM [Table] WHERE IsSomething = @TRUE;

You can read about the reasons for this problem here.

If it is the spatial type you may try a CAST to VARBINARY(max).. CAST( geography::STPolyFromText('') AS VARBINARY(max))

If you can't afford to upgrade to a newer version of SQL Server, PostgreSQL with PostGIS is perhaps an option as it is free and open source and has the best GIS implementation.

CREATE TEMP TABLE xSpatialTable (
     id         int           PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
     GeogCol1   geography
);

INSERT INTO xSpatialTable (GeogCol1)
VALUES ( 'POLYGON((53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670))' );


Can we use FILESTREAM in case of SQL Server cluster I saw this point on Technet article about RBS.

  The local FILESTREAM provider is supported only when it is used on
  local hard disk drives or an attached Internet Small Computer System
  Interface (iSCSI) device. You cannot use the local RBS FILESTREAM
  provider on remote storage devices such as network attached storage
  (NAS).

Does it mean we cannot use FILESTREAM in case of SQL Failover Cluster because Failover Cluster only works on NAS? But the thing is NAS is made available locally when setting Failover Cluster so may be FILESTREAM will work but I am not sure about it.

Yes, you can.  See: Set Up FILESTREAM on a Failover Cluster, https://msdn.microsoft.com/en-us/library/cc645886(v=sql.110).aspx


Consistent query performance over time We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of inserts and updates.
DB performance is generally quite OK, but periodically we get query performance problems; rather simple queries that previously worked just fine may take 10-100x the time all of a sudden.
This appears to be related to table/index statistics and the query optimiser - most times a statistics update will fix the problem, then again other times an update of the statistics will make the situation worse (re-running the statistics update will then typically solve the problem eventually).
What appears to be happening is that the optimiser decides to use objectively wrong indexes for some queries; all of a sudden, after having used the correct one for days and weeks.
My question is: Why is this happening and what can we do about it?
This database has been running for years with basically the same load, pretty much the same queries, and the same amount of updates. For 99.995% of the queries, there should be no reason to ever decide on different index strategies over time, regardless of the input (and - indeed - doing so will demonstrably completely destroy query performance).
As indicated above, automatically updating statistics on a schedule will often generate horrible problems - if the statistics sample gets skewed (which appears to happen at least 5% of the times) we end up in a world of pain.
Is there any way to tell SQL Server (on certain tables) that the statistics histogram and density will not change over time, so please just continue using the same query plan for queries involving this table? If not, how can we ensure a predictable result of statistics update over time (avoiding the skewed statistics problem described above)?
No stored procedures. We do have control over the SQL, so it can potentially be changed but it's a LOT of code so it would be unfortunate if we had to change every single query (e.g. adding in an additional clause).
A follow-up question: parameter sniffing only appears to be relevant for stored procedures, is that correct?

I suggest you first determine whether its the stats or if its parameter sniffing that is hurting you.
Regardless of above I suggest you read Erland's article on the subject.
What to do about it is difficult to say. We don't know whether it is stats or sniffing.
But possibly adding OPTIMIZE FOR can be "the" solution. It is cheaper than RECOMPILE since you don't have to take the plan production hit on each execution. And it gives you predictability. This, of course, assumes that you don't have the case where the stats differ so much so the same parameter input yield different plans because of stats reasons.
Try to identify one query. See if you have one or many plans for the query. Test with OPTIMIZE FOR and/or RECOMPILE. The one "global" option at database scale you have is to disable parameter sniffing for the database. This means the optimizer optimizes as it doesn't have a clue of the value. All this and more in Erland's article.
Parameter sniffing does not only apply to stored procedures. It also applies to parameterized SQL (typically executed using sp_executesql), which likely is far more common nowadays than stored procedures.

Are you checking the index fragmentation over time?  Having many updates on a table will always cause index fragmentation so having a schedules maintenance plan to rebuild indexes is definitely recommended, or even after a large data import.
I have dropped indexes before large data imports and then rebuilt them afterwards, speeding up the import, and maintaining index fragmentation.
Also, confirming your ISOLATION LEVEL configuration.  Using row versioning is more optimistic than READ COMMITTED with regards to record and table locks, and can make a huge difference to query performance when reading from heavily used tables.  Helps prevent deadlocks and reduces query waiting time without the threat of dirty data being presented.
Finding out which queries and which tables are concerned with the go slow would obviously be useful.  Sometimes parallelism causing an huge increase in wait times especially when stats and index frag is apparent. Forcing MAXDOP 1 can stabalise query times and sometimes improve the overall performance.  Minimally it will make query times consistent,  even if it reduces performance overall, it helps reduce the risk of them falling off the edge.


How come website be random slow? Open sleep processes? My website is randomly very slow by high MYSQL CPU, and I have no idea what the problem is. Hopefully I will come closer to the solution with this post.

If I check the mysql processes, I see a lot of open sleep commands when the website is slow. When there are none open sleep commands, there is no problem. So probably the problem lies there.

Curiously, this problem only happens sometimes. Sometimes I have no problems for 4 weeks, now the website is already 3 days in a row slow.

I already set the time_out mysql settings to 30 secs, but that does not help to solve the problem. So now I try to figure out what the problem is. Here are the alert status variables. Perhaps that gives some extra insights.

Could it be spambots/brute-force attacks?
Heavy/slow query's in the website?
Bad server settings?
Sql injections?

Other things that this problem can cause? What is the best way to tackle this problem now? Sending any direction will already help.

IMHO,reason can be one of the following

i) Inline query :It is prone to sql injection attack.but not necessarily website will be slow.It is Advisable to use parameterise proc.

ii) Unpotimize query : Yes.shortlist those page and related query/proc.

iii) CSRF Attack : your pages can be attack by malware or web crawler.There are various way to overcome it,one of them is Captcha, but not always.google it.

iv) Network/server configuration :this should point i).check with your admin.

v) check for infinite and uncessary loop in pages.Infact identiyfy pages which are slow then examine both code and query.


SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without any issue. He imported the package to the integration services under MSDB for testing. If he 'Run package' from integration services he gets an error stating that "To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher".
If I tried to execute by clicking 'Run package' from integration services (MSDB) which he imported; I can able to execute it without any issues.

We have verified all the installation and everything looks similar. Can you guys help me to resolve this issue?

When you run the package from msdb it executes on the machine that you are running it from.

(Unlike packages stored in the 2012+ SSISDB catalog where executing them spawns an execution process on the Server itself).

You must have installed SQL Server Integration Services onto your own machine from the SQL Server installation media, whereas your colleague hasn't. If Integration Services isn't installed you are restricted to running packages in SSDT. Executing packages that run locally with dtexecui will fail.


Copy complete structure of a table Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:

select * into dbo.table2 from dbo.table1;

This is just a simple copy of the table; all of the indexes / constraints are missing. How can I copy a table structure (without using a backup)?

I am primarily looking to do this manually, but if that's not possible I'll accept any solution.

You can script a table relatively easy using the UI of course:

This will output a CREATE TABLE script and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist).

But if you're trying to automate this (e.g. generate the create table script in code), it is a little more cumbersome. The above scripting option doesn't just pull the entire CREATE TABLE DDL from a single location in the metadata; it does a whole bunch of magic behind the scenes in the code to generate the eventual CREATE TABLE script (you can use Profiler to see where it gets its data, but you can't see how it assembles it). I suggested an option for this:

http://connect.microsoft.com/SQLServer/feedback/details/273934

However this was met with very few votes and was quickly shot down by Microsoft. You may find it much more worthwhile to use a 3rd party tool for generating schema (I've blogged about this).

In SQL Server 2012 there are new metadata functions that allow you to get much closer than the work you have to do in 2005, 2008 and 2008 R2, piecing together column information from the metadata (which has a lot of caveats, for example if it's decimal you have to add the precision/scale, if [n[var[char]] you have to add the length specification, if n[var]char you have to cut the max_length in half, if it is a MAX you have to change -1 to MAX, etc etc). In SQL Server 2012 this part is a little easier:

SELECT name, system_type_name, is_nullable FROM
  sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)

Results:

name                   system_type_name  is_nullable
--------------------   ----------------  -----------
name                   nvarchar(128)     0
object_id              int               0
principal_id           int               1
schema_id              int               0
parent_object_id       int               0
type                   char(2)           0
type_desc              nvarchar(60)      1
create_date            datetime          0
modify_date            datetime          0
is_ms_shipped          bit               0
is_published           bit               0
is_schema_published    bit               0

I've blogged about this, too.

Arguably this is much closer to your targeted CREATE TABLE statement than a convoluted approach using sys.columns, but there is still a lot of work to do. Keys, constraints, text in row options, filegroup information, compression settings, indexes, etc. It's a very long list and I'll once again suggest you look at a 3rd party tool for this instead of, at the risk of repeating an over-used analogy, re-inventing the wheel.

That all said, if you need to do this through code but you can do it outside of SQL Server, you can consider SMO/PowerShell. See this tip and the Scripter.Script() method.

Select *
into [new table name]
from [table to be copied name]
where 0=1


Testing postgresql 9.4 streaming replication I have setup the replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps.

And I see the slave is in streaming replication mode.

But I do have a question that, I have a bunch of databases in my master. Now should I create the same databases/tables and have the existing data in master, in slave, before testing to ensure my replication works?

Any suggestion highly appreciated. Thanks.

Simply put, if the slave can be started up, both contents of master and slave are perfectly same.

I'll explain the details.
The slave is restored from the base backup which is the physical database back up of the master by replaying WAL log (transaction log).  So, the slave is a perfect replica of master and the difference between master and slave never occurs if slave can be ran.  In other words, if there are any differences between master and slave, the slave cannot be ran.

The simplest test is to create a small table at the master and to confirm the table at the slave, which is mentioned at that document.

Master:

postgres=# create table tbl (data text); insert into tbl values ('test');

Slave:

postgres=# select * from tbl;

If you can find the new table at the slave, the databases of slave is a perfect replica of master data.

Additionally,  this is a very rare case,  but the piece of  data of slave may be corrupted  by a failure of the copy tool.
If you want to avoid this failure, you should do VACUUM FULL statement for all tables at the master server.
If you do VACUUM FULL, all tables will be reconstructed at the slave as well as the master. Therefore, the corrupted data of the slave will be restored. (It's a dirty trick.)


WiredTiger - "too many open files error" during resync of a secondary replica set member I'm upgrading a secondary replica set member to wiredTiger. I have upgraded it from MongoDB 2.6.3 to 3.0.4 and changed the storage engine to wiredTiger. Now it is resyncing all data from the primary.
At some point the following error is received, and the process starts all over again:

  2015-07-22T13:18:55.658+0000 I INDEX    [rsSync]         building index using bulk method

  2015-07-22T13:18:55.664+0000 I INDEX    [rsSync] build index done.  scanned 1591 total records. 0 secs

  2015-07-22T13:18:56.397+0000 E STORAGE  [rsSync] WiredTiger (24) [1437571136:397083][20413:0x7f3d9ed29700], file:WiredTiger.wt, session.create: WiredTiger.turtle: fopen:
  Too many open files

  2015-07-22T13:18:56.463+0000 E REPL     [rsSync] 8 24: Too many open files

  2015-07-22T13:18:56.463+0000 E REPL     [rsSync] initial sync attempt failed, 9 attempts remaining

The same machine was previously running 2.6.3 version without any open file limits issues. I'm aware that wiredTiger might be creating much more files, so it must be it, but does it keep them all open simultaneously ?

For reference:

  cat /proc/sys/fs/file-max

  10747371

In /etc/init.d/mongod the configuration is:

  ulimit -n 64000

According to the documentation it seems that mongo holds a file descriptor for every data file. As in wiredTiger this results in a file for each collection + a file for each index, according to a calculation for our usecase, can add up to over 700K.

So I can change the ulimit to 700000 or higher, but I'm wondering whether this is the most correct solution, and what alternatives exist.

checkout your /etc/security/limits.conf file and change soft and hard limits according to your need.

* soft nofile 4096

* hard nofile 4096

in the first column * means this limit is applied for all users except root user.
you also need to edit /etc/pam.d/common-session* file add the following line in the end :

session required pam_limits.so

or else

  ulimit -n 65535 (and no reboot required)

Finally, logout/reboot .

For those using Mongodb on Ubuntu 16.04 with systemd make sure to follow @sumit but you should also edit /etc/systemd/system/multi-user.target.wants/mongodb-01.service file and adding the following lines beneath [Service] directive:

# (file size)
LimitFSIZE=infinity
# (cpu time)
LimitCPU=infinity
# (virtual memory size)
LimitAS=infinity
# (open files)
LimitNOFILE=990000
# (processes/threads)
LimitNPROC=495000

Remember to restart the service or the server.


Logins aren't syncing across Availability Groups We have 2 servers in an AlwaysOn group.

While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie DBINSTANCE->Security->Logins are missing on one server.

Therefore when there is a failover, I get login failures on the second server (which doesn't have the corresponding instance level logins).

How do I overcome this issue? Was I supposed to set up the user account in a special way?

My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually.
Something like the script from SQLSoldier, originally posted on the article Transferring Logins to a Database Mirror, should do the trick.

You must use Windows Domain logins and create them in every instance, As the SID is manage by active directory you will be able to access  in all replicas members of availability group if the login exist in the primary replica. another option you must use a certificate.


MYSQL wont use index in "IN(SELECT ..)" sub clause I have two tables in MYSQL with target fields set as index / primary index already. Then I ran the below sql query.

  EXPLAIN
  SELECT charge FROM ec_provider_account_option
  WHERE accountPermissionId='4'
  AND accountTypeId
      IN (select accountType from ec_provider where id = 3)

According to the result no index ref used obviously. The in clause "select accountType from ec_provider where id = 3" uses index by querying alone.

Plus, if I replace the subsquery in(select .. from ..) with in(1,2,3), it will return the same result, and using index ref.

 EXPLAIN
    SELECT charge FROM ec_provider_account_option
    WHERE accountPermissionId='4' and accountTypeId
          IN(1,2,3)

How comes that ?

That's right, so just use a join - MUCH faster:

SELECT charge FROM ec_provider_account_option a,
                   ec_provider b
  WHERE a.accountTypeId = b.accountType
  AND a.accountPermissionId = '4'
  AND b.id = 3;

If your AccountType in the subquery is a varchar and the AccountTypeID is an Integer - this scenario might happen. MySQL would do a Full Table Scan


SQL Server 2008 query planner failing after index drop & recreate Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now after this one the SQL Server query plan cache is behaving oddly.

Execution planner reveals that wrong indexes are being used wrongly that return millions of rows, when only a few should match. When the indexes are correct, the execution plan shows that SQL Server resolves to use index Scan as opposed to index Seek providing much less than optimal results. With tablehints like WITH(INDEX(indexname)) or WITH(FORCESEEK) in the proper places, this can be corrected. INNER LOOP JOIN also fixes some of these.

However, the problem is that even when these new, filtered indexes are dropped and recreated as they were before, the query plan remains the same. Query plan cache has been cleared, DB has been restored to a different environment, statistics have been updated and obviously the indexes have been rebuilt so they're not fragmented.

This is currently a critical issue that nobody has any idea how to fix. While we can force SQL Server to use the correct plans, it's simply not a solution to the multitude of software that would have to be updated with it, and obviously a DB where you need to manually point out how to handle queries is not an option.

So any help would be greatly appreciated.

Edit:
We managed to fix one query by dropping the indexes, recreating them again as filtered, then running an UPDATE STATISTICS tablename WITH FULLSCAN. This fixed some of the problem and two joins were working correctly. After this we had to do a separate alter to a multi-column index that was in no way involved in the original index script, to include one of the columns used in the join. These two changes together enabled the query planner to resolve to use the correct indexes with Seek rather than Scan.

The theory right now is that due to an earlier crash, the DB was apparently restored by deleting the underlying DB and then creating a new one from backup, rather than just using REPLACE as before. This would have somehow disconnected masterdb's metadata such as execution plans, all cache and whatnot from the DB resulting in a new massive database with no existing plans to handle queries. This, grouped with an apparently failed statistics update on the newly created indexes would have produced a scenario where SQL Server had no idea how to resolve the various queries it was being bombarded with.

I'm not convinced however that this will still be enough, since some of the behavior such as having to alter the multi-column index, and the fact that no statistics update was ever necessary in any of the prior testing environments in the past 2 months of testing, seems to let on that there's something else that has gone wrong.

Well, the issue is now resolved:

While it would seem logical to use filtered indexes (NOT NULL), to reduce database size and as so many sources on the web say, increase performance, the reality it seems is something else entirely.

In layman's terms, SQL Server query planner resolves even your basic inner joins without making any assumptions as to the content of the columns. Even though NULL values do not form a join, they must be included in the column index in order for query planner to use it, unless otherwise specified with predicates such as WHERE joinCol_ID IS NOT NULL. Basically, SQL Server does not use filtered indexes for joins at all, unless the queries themselves are modified to account for the filter value. Instead, it will create new statistics on these columns and / or use a clustered index scan or other indexes including the column, whichever it deems most effective. Using filtered indexes on foreign keys is therefore an absolutely horrid idea.

We still have no idea how months worth of testing this in multiple other environments never produced the same results outside of this one, single DB, but this is the way it's supposed to work. Apparently something that as far as we know is not related to cache, statistics or configurations, caused the production DB to behave differently and correctly detect and use the filtered indexes, while all of the testing environments simply used the old indexes (seeing as the indexes were dropped and recreated with the same name, this seems a valid theory even if there is no real proof).

So the lesson of the story: The web is filled with examples of how underused filtered indexes are, how awesome they can be. But this serious downside never popped up except as a nagging thought in the back of my head saying "if these are so great, then why aren't NULL values filtered out of indexes by default, since they only take up space and only serve a purpose in special circumstances"? Well, now I know why. :)


Add array table constraints, empty or null if other column is false I'm adding constraint on an existing table jobs:

DROP TABLE IF EXISTS jobs CASCADE;
DROP TYPE IF EXISTS job_state;
CREATE TYPE job_state AS ENUM ( 'UNSTARTED', 'STARTED', 'FAILED', 'COMPLETED' );
CREATE TABLE jobs(
status job_state NOT NULL DEFAULT 'UNSTARTED',
error_message text[]
);

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false
   CHECK ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED') AND ( error_message IS NULL OR error_message = '{}' ));

What I want is

INSERT INTO jobs VALUES ('UNSTARTED', null);
INSERT INTO jobs VALUES ('STARTED', null);
INSERT INTO jobs VALUES ('FAILED', null); -- I would like to be able to do this

I know this error_message != '{}' OR error_message IS NOT NULL is not written properly. But I'm stuck finding a way to write it.

The constraint should be written using an OR like (NOT A) OR B

ALTER TABLE jobs
ADD CONSTRAINT error_message_is_null_if_failed_is_false
   CHECK ( NOT ( status IN ('UNSTARTED', 'STARTED', 'COMPLETED')) OR ( error_message IS NULL OR error_message = '{}' ));

INSERT INTO jobs VALUES ('UNSTARTED', null); -- no error
INSERT INTO jobs VALUES ('STARTED', null); -- no error
INSERT INTO jobs VALUES ('FAILED', null); -- no error
INSERT INTO jobs VALUES ('COMPLETED', null); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('STARTED', '{}'); -- no error
INSERT INTO jobs VALUES ('FAILED', '{}'); -- no error
INSERT INTO jobs VALUES ('COMPLETED', '{}'); -- no error

INSERT INTO jobs VALUES ('UNSTARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('COMPLETED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('STARTED', '{"error message"}'); -- error
INSERT INTO jobs VALUES ('FAILED', '{"error message"}'); -- no error

Why are you doing

CHECK ( NOT (failed IS FALSE AND (error_message != '{}' OR error_message IS NOT NULL)) );

And not

CHECK (
  failed IS FALSE
  AND (error_message = '{}' OR error_message IS NULL )
);

Or, even better rather than keeping state across multiple booleans like failed and error_message use a single ENUM.

CREATE TYPE terminal_job_state AS ENUM ( 'FAILED', 'COMPLETED' )


Best practice for "recycling" rows on PostgreSQL (UPDATE vs INSERT + DELETE) I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected.
My question is centered on a somewhat niche use case. Let's say you have a table of fairly high-frequency ephemeral data. For example, modelling notifications on a social network, those are created very often and you always see the latest N.
Would it make sense to keep the latest N (say 20) and when one comes in, the Nth row is UPDATED into the new one (ID included). Rather than inserting a new one and then trimming old rows (maybe in bulk on an interval)?
Any thoughts?

Postgres is MVCC. Every write produces a new row-version irrespective of whether the operation is insert or update. So I would not expect the proposed ring buffer style to give performance benefits.
With single-value systems, and fixed length rows I would expect faster performance. I'd like to see some thorough testing before I believed it was worthwhile, though.

The question of performance between an UPDATE statement or a similar INSERT and DELETE statement is too broad of a question to say which one is faster. It'll depend on the specific query used in both cases among other factors such as the schema structure and architecture, including how the table is indexed.
To address your specific example, I'm going to assume you're actually referring to the feed, e.g. the StatusFeed in Facebook, which is only the top N items, and is very ephemeral i.e. it changes on every refresh of the page (since I consider Notifications not very ephemeral and always expect the same Notifications to be waiting for me to view, regardless of how many times I refresh the page).
In such an example a traditional RDBMS could be used, like PostgreSQL, but might be a little overly architected of a choice for something that doesn't require much persistence. As Caius Jard points out, you would likely be better suited to use something designed for high-frequency and low-persistence like an in-memory solution, or possibly even a document data store since the schema is simple and there's not much relational logic needed to support such a use case.
If you were still fixed on (for the theory of it) trying to implement this with a traditional RDBMS then you'll likely generate less transactions and therefor have some slightly less overhead by doing only UPDATES as opposed to INSERTS and DELETES. But again, as far as which methodology will be faster overall or cause the least amount of contention, will very much dependent on how the schema is architected.


Why straight SQL query run faster than stored procedure? I am facing-out a very surprising problem in SQL Server.

The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will execute in ~57 sec.

For example :

DECLARE @ip_RequestId As Int
SET @ip_RequestId = 3287

SELECT
   Tbl1.SurveyResponseId,
   Tbl1.ReportLabelID,
   Tbl1.EntityId AS FeedbackByEntityId,
   (SELECT Candidate + ' ' + LastName
    FROM Tbl2
    WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,
   (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
FROM Tbl1

When I run the above query in SSMS as a query, it execute in ~1 seconds.

But after creating the stored procedure:

CREATE PROCEDURE [dbo].[spGetResponse]
   @ip_RequestId As Int
AS
BEGIN
    SELECT
       Tbl1.SurveyResponseId,
       Tbl1.ReportLabelID,
       Tbl1.EntityId AS FeedbackByEntityId,
       (SELECT Candidate + ' ' + LastName
        FROM Tbl2
        WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName,
       (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3
                       WHERE MultiRaterId = @ip_RequestId), '')) As ReportTitle
    FROM Tbl1
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~57 seconds.

After some googling, I found it's because of "parameter sniffing". But I am not really understand the "parameter sniffing".

**Updated: One more thing **

When the Sp run with search query, i will also fast for me. Means if I convert the SP following format, this will also return result in ~1 sec:

CREATE PROCEDURE [dbo].[spGetResponse]
    @ip_RequestId As Int
AS
BEGIN
     DECLARE @strQuery As VARCHAR(MAX)
     SET @strQuery = '(SELECT Tbl1.SurveyResponseId, Tbl1.ReportLabelID, Tbl1.EntityId AS FeedbackByEntityId, (SELECT Candidate + '' '' + LastName FROM Tbl2 WHERE Tbl2.ProposalId = Tbl1.EntityId) AS FeedbackByName, (SELECT ISNULL((SELECT MRReportTitle FROM Tbl3 WHERE MultiRaterId = ' + @ip_RequestId + '), '''')) As ReportTitle FROM Tbl1)'
     EXECUTE (@strQuery)
END

When I execute the above stored procedure with same parameter value (i.e @ip_RequestId = 3287) in SSMS window (exec spGetResponse 3287), it executes in ~1 seconds.

Is there any solution exist to overcome the "parameter sniffing" problem?

Assign the value of parameter to the local variable in the procedure then used that variable.

I had the similar issue. Added the OPTIMIZE FOR UNKNOWN at the end of the SP to resolve the problem.


Are cross-database queries expensive in SQL Server? Are cross-database queries expensive in SQL Server? All of the databases are in the same instance.

Sorry, I don't have enough reputation to comment on the question, but from my experience if the client application raises a transaction for a query which uses cross database joins then it would promote the transaction to distributed and have the overhead of a DTC transaction.

The DTC overhead in this case could be viewed as a negative to performance. Generally the difference would be negligible although Microsoft describe DTC transactions as such:

  Distributed transactions typically consume significant system resources

  Transaction Promotion

...which would suggest a performance degradation if your server cannot offer the resources it requires.

Just to clarify, the article above describes local transactions being promoted when remote systems are introduced, but I have seen this become the case for transactions on the same server when using cross database queries.

As Thomas Stringer points out in his comment, there will be extra overhead in authentication although I think as this will be SID-driven there will be minimal overhead there unless you have to use separate credentials to access the other database.

If there were difference in database settings which caused additional overhead in the join that could impact larger than the previous suggestions - for example database collation. Database collation could manifest as a functional difference, not just a performance difference.

I think Aaron has the strongest argument for performance with the optimizer not having the advantage of using relationships for cross-database queries whereas self-contained within a database you could use relationships to your advantage.

In SQL Server 2014, memory-optimized tables do not support cross-database transactions. You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. You cannot easily copy data from a table in one database, to a memory-optimized table in another database.http://msdn.microsoft.com/en-us/library/dn584627(v=sql.120).aspx Distributed and cross-database transactions are not supported for database mirroring/AOAG. http://technet.microsoft.com/en-us/library/hh393530.aspx.  If you are going to use ownership chaining, be aware of the security risks http://msdn.microsoft.com/en-us/library/ms188676.aspx


Set indexes for database used for login and register actions I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?In this type of databases what are the methods to implement performance?

I think you don't need to have any indexes (Rather than primary key) for user table which is mainly used for authentication, because most of the fields in user table has unique rows (correct me if i am wrong), so Cardinality is very high. If you index high cardinality field it might be a performance hit. You can go through this article for more information about cardinality and indexing


Firebird linked tables in Access 2010 I have multiple data sources in multiple databases that I have been tasked with correlating for business analytics.  I currently have ODBC connections to two (one Pervasive, one Firebird) that I can use successfully in Excel.  A DBA friend suggested I use Microsoft Access to create linked tables and perhaps make creating lookup tables between the data sets easier.

I've run into a problem.  Although Excel uses the ODBC connection to Firebird fine, Access can't create the linked table through the Create Linked Table dialogue.  I get the following error message:

ODBC--call failed

[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 30, column 13
when (#-104)

I take this to mean that whatever query Access is using to attempt to create the linked table is failing to be interpreted correctly by the Firebird database, but I don't get to see any of the code because Access is handling it.  I seem to recall reading that Access tend to fall back to SQL query structure when it can't figure out how to talk to certain databases, but I can't find the source.

I did see a post by Max Vernon with regards to linked tables in SQL server 2008.  He suggested using Microsoft OLE DB Provider for ODBC Drivers, but I have been unable to locate the driver.

I guess my question is this: Is it possible to manually configure linked tables in Access, do I need to get the OLE DB interface working (I have yet to figure out where it is to install it), or is there another method I should be exploring?

It is very easy to connect, just configure ODBC. However, when you install Firebird Server, you must select the option to copy files to the Windows directory.


When are computed columns computed? When are the values for computed columns determined?

When the value is retrieved?
When the value is changed?
Some other time?

I'm guessing this is a novice question since I'm not finding anything in my searches.

This is very easy to prove on your own. We can create a table with a computed column that uses a scalar user-defined function, and then check plans and function stats before and after both an update and select, and see when an execution gets recorded.
Let's say we have this function:
CREATE FUNCTION dbo.mask(@x varchar(32))
RETURNS varchar(32) WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT 'XX' + SUBSTRING(@x, 3, LEN(@x)-4) + 'XXXX');
END
GO

And this table:
CREATE TABLE dbo.Floobs
(
  FloobID int IDENTITY(1,1),
  Name varchar(32),
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)),
  CONSTRAINT pk_Floobs PRIMARY KEY(FloobID),
  CONSTRAINT ck_Name CHECK (LEN(Name)>=8)
);
GO

Let's check sys.dm_exec_function_stats (new in SQL Server 2016 and Azure SQL Database) before and after an insert, and then after a select:
SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

INSERT dbo.Floobs(Name) VALUES('FrankieC');

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

SELECT * FROM dbo.Floobs;

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

I see no function call on the insert, only on the select.
Now, drop the tables and do it again, this time changing the column to PERSISTED:
DROP TABLE dbo.Floobs;
GO
DROP FUNCTION dbo.mask;
GO

...
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)) PERSISTED,
...

And I see the opposite happening: I get an execution logged on the insert, but not on the select.
Don't have a modern enough version of SQL Server to use sys.dm_exec_function_stats? No worries, this is captured in the execution plans, too.
For the non-persisted version, we can see the function referenced only in the select:

While the persisted version only shows the computation happening on insert:

Now, Martin brings up a great point in a comment: this isn't always going to be true. Let's create an index that doesn't cover the persisted computed column, and run a query that uses that index, and see if the lookup gets the data from the existing persisted data, or computes the data at runtime (drop and re-create function and table here):
CREATE INDEX x ON dbo.Floobs(Name);
GO

INSERT dbo.Floobs(name)
  SELECT LEFT(name, 32)
  FROM sys.all_columns
  WHERE LEN(name) >= 8;

Now, we'll run a query that uses the index (actually it uses the index by default in this specific case anyway, even without a where clause):
SELECT * FROM dbo.Floobs WITH (INDEX(x))
  WHERE Name LIKE 'S%';

I see additional executions in function stats, and the plan doesn't lie:

So, the answer is IT DEPENDS. In this case, SQL Server thought it would be cheaper to re-compute the values than to perform lookups. This could change due to a variety of factors, so don't rely on it. And this can happen in either direction whether or not a user-defined function is used; I only used it here because it made it that much easier to illustrate.

The answer to this question truly is "it depends."  I have just run across an example where SQL Server is using the index on the persisted computed column but it is still performing the function, as if the values were never persisted to begin with.  It may have to do with the data type of the column (nvarchar(37)) or possibly the size of the table (about 7 million rows), but SQL Server decided to ignore the persisted keyword, it appears, in this particular instance.

In this case, the primary key on the table is TransactionID which is also a computed and persisted column.  The execution plan is generating an index scan and in a table with only 7 million rows this simple query is taking upwards of 2-3 minutes to run because the function is run again over every row and the values don't appear to be persisted in the index.