Database design for multiple user attributes I am building an app with Laravel, and I have users which also have a profile.

I am unsure about the best way to set up the database for the user attributes.

For example:

user_ud  |   name  |  gender  |  haircolor
   1         josh       1           3

This quick example shows what my table for profiles currently looks like, as the vales are filled in from select boxes.

Is this the best way to map these attributes to values like this?

 id  |  gender
  1      male
  2      female

 id  | hairColor
  1      blonde
  2      brown
  3      black

and so on creating a new table for each attribute type? or is there a better way of doing this?

You could look at using Entity-Attribute-Value design.

This consists of a table with three columns, one for the user id, one for the type of attribute, and one for the attribute value.

With this design, you can have an unlimited number of attributes without needing to implement new columns for new attributes.

Many architects will say this design cannot scale well, and they'd mostly be right; however if implemented correctly without going overboard on the number of attributes, and with proper indexing and efficient column types, it can work well.

For instance:

+---------+------------+
| USER_ID |  USER_NAME |
+---------+------------+
| 1       | You        |
| 2       | Me         |
| 3       | Them       |
+---------+------------+

+--------------+-----------------+
| ATTRIBUTE_ID |  ATTRIBUTE_NAME |
+--------------+-----------------+
| 1            | Eye Color       |
| 2            | Hair Color      |
| 3            | Number of Legs  |
+--------------+-----------------+

+---------+--------------+--------+
| USER_ID | ATTRIBUTE_ID |  VALUE |
+---------+--------------+--------+
| 1       | 1            | Brown  |
| 1       | 2            | Blue   |
| 1       | 3            | 2      |
| 2       | 1            | Blonde |
| 2       | 2            | Green  |
| 2       | 3            | 27     |
| 3       | 1            | Black  |
| 3       | 2            | Black  |
| 3       | 3            | 42     |
+---------+--------------+--------+


Database Recovery Stuck on "Restoring" Scenario:

Sql Server Database routinely backed up with Backup Statement below.
The "Recovery Model" was set to "Full" (we usually have them set to "Simple").
The ldf file, which had gotten quite large, was deleted using Windows Explorer (after first stopping Sql Server).
ldf file deletion did not go into recycle bin because it was too large.

  BACKUP DATABASE MYDATABASE to disk = 'E:\BACKUPS\BACKUP.BAK' WITH
  FORMAT, MEDIANAME = 'MYDATABASE', NAME = ' FULL MYDATABASE BACKUP'

We are now trying restore one of our backups (200MB file size).  The restore progress proceeds at expected rate.  But it sticks at 100%, and the database status remains Restoring.  We notice that once the progress hits 100% the mdf file is approximately the right size.  And the ldf file shows 60GB filesize.  This was about the size of the file when it was deleted.  But obviously this file did not come out of the 200MB bak file.

Is there anything we can do?  I am not a Sql Server DBA, but I presume the problem was we were not backing up a "Full Recovery" database properly.

Yeah, you need that .ldf file.  In the future, don't delete that.

If your database is running in full recovery, you need to run log backups regularly.  "Regularly" is a term that depends on your particular environment, but hourly during business hours is a common choice.  Basically, it only purges the transactions out of the log file when it's backed up.  If it's never backed up, the log grows until it consumes the entire disk, as you've seen.

In the future, set the database to simple recovery, shrink the transaction log, and then back it up immediately, as NathanC suggested.

As for now:  I don't know what kind of backups you have beyond the one you restored from, but I agree with NathanC that you need to let it sit and wait for the restore to finish.  It's chugging through that log file.  Once it finishes, truncate the log and set it to simple like your other databases, or implement log backups.


Serialize a column based on sorting order? I have a column number_order and products_name I'm struggling to build a command that will sort by A-Z so that respectively:

A = 1
B = 2
C = 3

I'm using MariaDB. To be clear I want to run a command that will set the number_order starting from 1 to X, based on the products_name so that a product called A would have number_order = 1

UPDATE sourcetable
SET number_order = (@tmp := @tmp + 1)
WHERE NOT (@tmp := 0)
ORDER BY products_name ASC

fiddle


When running `make installcheck` I get can not connect to database On Ubuntu when I run make installcheck I'm getting

/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/9.5/bin'    --inputdir=test --load-language=plpgsql --dbname=contrib_regression base math equality
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
command failed: "/usr/lib/postgresql/9.5/bin/psql" -X -c "DROP DATABASE IF EXISTS \"contrib_regression\"" "postgres"
/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk:272: recipe for target 'installcheck' failed
make: *** [installcheck] Error 2

What's the reason for this?

This is on the pg-bignum extension.

If you're trying to build an extension on Ubuntu, you may have to set the port to isolate which version of PostgreSQL you wish to test the extension on with PGPORT. You can see here make install check is connecting on 5433 with the socket name .s.PGSQL.5432,

You likely want port 5432

make PGPORT=5433 installcheck


Select Multiple Values from Same Column; one sql statment I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same column (meta_value).

The database structure is as follows:

id | user_id | meta_key | meta_value

sample data is as follows:

1 | 3434 | first_name | Brandon
2 | 3434 | last_name  | Johnson
3 | 3434 | street_add | 123 main
4 | 3434 | city       | ocean beach
5 | 3434 | state      | Texas

I am trying to return the first name, last name, street_add, city, and state in one sql statement.

I'd like the output to look like:

Brandon, Johnson, 123 Main, Ocean Beach, Texas

Just use GROUP_CONCAT with a WHERE condition

SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id)
FROM t
WHERE meta_key  IN('first_name','last_name','street_add','city','state')
GROUP BY user_id


JSONB: Finding an element with a null attribute in array of objects With the following table in psql 10:

CREATE TABLE logs(id serial, data JSONB);
INSERT INTO logs VALUES
  (1, '[{"name":"test1","date":"2018-01-09"},{"name":"test2","date":"2018-01-10"}]'),
  (2, '[{"name":"test3","date":"2018-01-10"},{"name":"test4","date":null}]');

How do I find row #2 by querying the date=null value in the array?

I already know how to query by name to find row#2:

AND data @> '[{"name": "test4"}]'

But I don't know how you can query for rows with or without a null date attribute.


InnoDB storage for multiple BIT(M) columns? Let's say if I have a table with four BIT(1) columns. Will InnoDB use 4 bytes to store these columns, or will it be smart and use 1 byte?
(Referring to MySQL 8, if it matters.)

that has nothing to do with smart 4 columns are 4 columns. so it woud use 4 byte
Save your bit patterns as Integer.
Depending what you need you can use tinyint 1 Byte (8bits) till BIGINT 8 Byte (64 bits)
If you have or want to save space, you have no better solution.
if you want to store more bit you have to switch to BLOB and use that
For example

2 is 00000010
3 is 00000011

Any language can transform an integer too a byte array and back again


Pt-query-digest date ranges do not work as expected Analyse Queries with Pt-query-digest

I am using pt-query-digest to analyse queries and would like to analyse a specific time range only, in order to measure the effect of adding new indices and tuning the server.

The Query log is of course turned on (MySQL 5.6) and queries are written to the log files as expected. I transfer the log files to my local machine and concatenate all compressed log files into one file like this:

zcat -f $(ls -tr mysql-slow.log*) > MySQL-All-Slowlogs.log

When I then execute pt-query-digest without any parameters, I get the output as expected:

pt-query-digest MySQL-All-Slowlogs.log > output.log

The date range in this output file is the whole range that is covered within the log files and the header states this:

# 490ms user time, 10ms system time, 35.09M rss, 103.70M vsz
# Current date: Fri Apr  7 10:58:38 2017
# Hostname: LocalMachine
# Files: MySQL-All-Slowlogs.log
# Overall: 2.33k total, 125 unique, 0.00 QPS, 0.05x concurrency __________
# Time range: 2017-03-31 03:16:49 to 2017-04-07 06:00:10
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======

Thats fine. Now I would like to investigate on the queries from the last two days, for instance

Problem

Pt-query-digest does not respect date ranges I provide, I still get the full list starting end of march (2017-03-31)

# Gives all queries
pt-query-digest --since 2d MySQL-All-Slowlogs.log > output.log
# Also all queries
pt-query-digest --since '2017-04-05'  MySQL-All-Slowlogs.log > output.log

And specifying the until date gives no results at all

pt-query-digest --since '2017-04-05' --until '2017-04-07'  MySQL-All-Slowlogs.log > output.log

# No events processed.

I also provided a DSN with the same results while connecting via I an SSH tunnel

  pt-query-digest --since '2017-04-05' h=127.0.0.1,P=$PORT,u=$USER,p=$PASSWORD MySQL-All-Slowlogs.log > output.log

Expected Result

The documentation says this:

      --since
type: string

Parse only queries newer than this value (parse queries since this date).

This option allows you to ignore queries older than a certain value and parse only those queries which are more recent than the value. The value can be several types:

* Simple time value N with optional suffix: N[shmd], where
  s=seconds, h=hours, m=minutes, d=days (default s if no suffix
  given); this is like saying "since N[shmd] ago"
* Full date with optional hours:minutes:seconds:
  YYYY-MM-DD [HH:MM:SS]
* Short, MySQL-style date:
  YYMMDD [HH:MM:SS]
* Any time expression evaluated by MySQL:
  CURRENT_DATE - INTERVAL 7 DAY
If you give a MySQL time expression, and you have not also specified a DSN for --explain, --processlist, or --review, then you must specify a DSN on the command line so that pt-query-digest can connect to MySQL to evaluate the expression.

The MySQL time expression is wrapped inside a query like “SELECT UNIX_TIMESTAMP(<expression>)”, so be sure that the expression is valid inside this query. For example, do not use UNIX_TIMESTAMP() because UNIX_TIMESTAMP(UNIX_TIMESTAMP()) returns 0.

Events are assumed to be in chronological: older events at the beginning of the log and newer events at the end of the log. --since is strict: it ignores all queries until one is found that is new enough. Therefore, if the query events are not consistently timestamped, some may be ignored which are actually new enough.

What am I missing here?

Thanks!

Preserve the correct sequence

I finally figured out the problem. The way how I extract all the gzipped log files and add them to a consolidated log file did not preserve the correct sequence and therefore pt-query-digest did not find the queries in the order it was expecting them.

The manual even says:

  Events are assumed to be in chronological: older events at the beginning of the log and newer events at the end of the log

Solution

The solution is to process the logrotate files in reverse order in order to end up with a logfile that has the oldest logs first and the newest ones at the end of the file.

ls mysql-slow.log* |sort -nr -t . -k 3,3 | xargs zcat -f > MySQL-All-Slowlogs.log

The command above does the following things:

ls: list all files starting with the file name mysql-slow.log.*
sort -nr -t: sort based on a numeric literal (otherwise the sequence number of the file will be interpreted as string alphabetically, sorting 111 before 2. -r reverses the order. -t uses "." as field seperator. k specifies the key to sort on.
xargs: For each line cat the zipped or unzipped slow log file to the culmulative file.

Then you can use the following comand to obtain

 pt-query-digest --since '2017-04-06 12:00:00' --until '2017-04-09 12:00:00'  MySQL-All-Slowlogs.log  > output.txt


Select COUNT of days between two dates except weekends I am trying to get the number of days between two different dates, except the weekends.

I don't know how to achieve this result.

Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10'
                     , interval  '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;

You don't need an extra subquery level for generate_series(). SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause.
Note in particular that generate_series() includes the upper bound in the output, as long as a full interval (3rd parameter) fits. The upper bound is only excluded if the last interval would be truncated, which is not the case with full days.
With the pattern ISODOW for EXTRACT(), Sundays are reported as 7, according to the ISO standard. Allows for a simpler WHERE condition.
Rather call generate_series() with timestamp input. Here is why:

Generating time series between two dates in PostgreSQL

count(*) is slightly shorter and faster than count(the_day), doing the same in this case.

To exclude lower and / or upper bound, add / subtract 1 day accordingly. Typically, you might include the lower and exclude the upper bound:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10' - interval '1 day'
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) < 6;


create linked server to vertica I'm attempting to create a linked server from SQL Server to Vertica to pull over some data. I've attempted using the following ODBC found here.

The code I'm currently trying is:

EXEC dbo.sp_addlinkedserver
@server = N'VERTICA',
@srvproduct=N'',
-- SQL Server driver
@provider=N'MSDASQL',
-- Connection string
@provstr=N'Driver=Vertica ODBC Driver 4.1;
    Servername=Vertica;
    Port=5433;
    Database=Vertica;
    UserName=user;
    Password=pass'

The error message I get is:

  OLE DB provider "MSDASQL" for linked server "VERTICA" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

The driver in the connection string needs to be Vertica, not Vertica ODBC Driver 4.1.

EXEC dbo.sp_addlinkedserver
    @server = N'VERTICA',
    @srvproduct=N'',
    -- SQL Server driver
    @provider=N'MSDASQL',
    -- Connection string
    @provstr=N'Driver=Vertica;
            Servername=ServerName;
            Port=5433;
            Database=DatabaseName;
            UserName=user;
            Password=pass'


Mapping many-to-many relationship I have two tables:

Employee Table with columns employee_id (primary key) and employee_name.
Company Table with columns company_id (primary key) and company_name.

The companies allows its employee to work for other companies. So an employee can work in many companies and a company can have many employees(MANY TO MANY relation).

Say I have 3 employees and the companies they work for with the respective start and end time for a day.

employee_name | company_name | hours they work |
Akash            A               09:00 - 11:00
                 B               12:00 - 02:00
                 C               04:00 - 07:00

Sunny            D               09:00 - 11:00
                 C               12:00-  04:00
                 D               05:00 - 07:00

Vishal           B               09:00 - 12:00
                 A               12:00 - 05:00

How should I design the database?
How do I find the employee who worked the most hours for a given company?

You will have both employee table and company table to store employee and company info. But you need another table for the relation since it is a many-to-many relationship.

Also here, the work hours info is a relation attribute. It does not exist until an employee starts to work for a company.

The ER diagram will simply be as the following:

When you map this relation, you will have a table company_employee(employee_id, company_id, work_hours)

Your SQL code for the tables:

CREATE TABLE employee (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE company (
    company_id INTEGER PRIMARY KEY,
    company_name VARCHAR(300) NOT NULL
);

CREATE TABLE company_employee (
    employee_id INTEGER NOT NULL,
    company_id INTEGER NOT NULL,
    work_hour_start TIME NOT NULL,
    work_hour_end TIME NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employee (employee_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY (company_id) REFERENCES company (company_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (employee_id, company_id, work_hour_start, work_hour_end)
);

In the company_employee table, you can store work hours in a single column too, depending on your needs.

To view the columns,

SELECT e.employee_name, c.company_name, ec.work_hour_start, ec.work_hour_end
FROM employee e
INNER JOIN company_employee ec
ON e.employee_id = ec.employee_id
INNER JOIN company c
ON c.company_id = ec.company_id;

Lastly, this will show who worked how many hours for what company:

SELECT c.company_name, e.employee_name, MAX(ec.work_hour_end - ec.work_hour_start) AS max_hours
FROM employee e
INNER JOIN company_employee ec
ON e.employee_id = ec.employee_id
INNER JOIN company c
ON c.company_id = ec.company_id
GROUP BY c.company_name, e.employee_name
ORDER BY c.company_name;


Decode function in SQL Server Can some one help me to figure out the equivalent function to decode in SQL Server.

Here is the statement which I used for Oracle and I want it for SQL Server. I tried but not able to get it.

UPDATE [SCHEMA].TLS_FAC_PRC_ONG
SET FPO_RID_OWNER = decode(FPO_RID_OUTSTANDNG,'NONE',FPO_PID_FACILITY,NULL,FPO_PID_FACILITY,FPO_RID_OUTSTANDNG),
FPO_CDE_OWNER = decode(FPO_RID_OUTSTANDNG,'NONE','FAC',NULL,'FAC','OST');

You should be able to turn the decode into a case:

UPDATE
  [SCHEMA].TLS_FAC_PRC_ONG
SET
  FPO_RID_OWNER = CASE FPO_RID_OUTSTANDNG
                    WHEN 'NONE' THEN FPO_PID_FACILITY
                    WHEN NULL THEN FPO_PID_FACILITY
                    ELSE FPO_RID_OUTSTANDNG
  FPO_CDE_OWNER = CASE FPO_RID_OUTSTANDNG
                    WHEN 'NONE' THEN 'FAC'
                    WHEN NULL THEN 'FAC'
                    ELSE 'OST'
                  END


Query gets slow when bind peeking is turned on There is an old database that was upgraded from Oracle 10 to 11G R1. In Oracle 10 we had disabled bind peeking. Since Oracle 11 has adaptive cursor sharing, we want to turn bind peeking back on. When we did this we found that many queries went much much faster. Unfortunately, one critical query got very slow. As soon as we turn off bind peeking, the one query gets fast again but everything else goes back to being sluggish.

The question is: In Oracle 11, what would cause bind peeking to make a query slow? I thought adaptive cursor sharing was supposed to take care of the bad bind variable peeks problem.

Bind peeking is done in stages. First the query is be flagged as "bind sensitive" based on histograms, and then it may become "bind aware". The IS_BIND_SENSITIVE and IS_BIND_AWARE columns in V$SQL will show the state of the query. If IS_BIND_AWARE is not on, the query is not adapting.

This post helps to explains the process.


Error granting SELECT on view that uses ALL_OBJECTS I'm in the middle of testing Oracle 12c for our application. I have something similar to this view in a particular schema, which we'll call MY_OWNER_SCHEMA for this question:

CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
    OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
;

The view is a sort of "metadata" view, used to verify (programmatically) that all tables and views have a comment. Since MY_OWNER_SCHEMA is locked except during deployment, we grant permissions to MY_LOGIN_USER to SELECT from it:

GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;

(This GRANT is executed during deploy while logged in as MY_OWNER_SCHEMA.)

This worked just fine in Oracle 11g, and creating it works in 12c as well. However, in 12c only, the GRANT fails with the following message:

SQL Error: ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view or
           revoke existing grants on the view.

This is fairly odd, since ALL_OBJECTS is a public interface accessible to all users, and it only shows objects to which the user already has some access. Did something change with the permissions model or default permissions settings in Oracle 12c? Is it possible that the database was installed differently? Is there a way to get around this error without giving MY_OWNER_SCHEMA explicit permission to GRANT to this global system view?

Yes, use READ privilege (introduced in 12c) instead of SELECT.

SQL> create user MY_OWNER_SCHEMA identified by MY_OWNER_SCHEMA;

User created.

SQL> create user MY_LOGIN_USER identified by MY_LOGIN_USER;

User created.

SQL> grant create session, create view to MY_OWNER_SCHEMA;

Grant succeeded.

SQL> conn MY_OWNER_SCHEMA/MY_OWNER_SCHEMA
Connected.
SQL> CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
  2    3    4    5    6    7    8    9      OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
; 10   11   12

View created.

SQL> GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;
GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'

Above fails, but:

SQL> GRANT READ ON OBJECT_COMMENTS TO MY_LOGIN_USER;

Grant succeeded.


Print database_size from sp_spaceused Using SQL Server 2008, I have found that the database size can be obtained using

exec sp_spaceused

This procedure returns two tables:

database_name | database_size | unallocated space

reserved | data | index_size | unused

I would like to create a query where the only output of the SQL call is the value returned for database_size.  I do not want any extraneous output (no table headers, column names, etc).

Is there a simple way to do this?

Another way to accomplish this without the creating database objects is to use the below query taken from the sp_spaceused stored procedure itself:

declare @dbsize bigint
declare @logsize bigint

select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles

select ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + ' MB') as Database_Size


Combining multiple queries into a single query I've got checkbox group field with 4 days of the week - Thursday, Friday, Saturday & Sunday. I populate this field based on whether 4 separate text fields for each corresponding day of the week are populated or not.

At the moment, I'm doing this by running 15 separate queries! This is every combination of Thursday, Friday, Saturday & Sunday I can think of, which then updates the value for this checkbox group field.

The queries are listed below for info. My question is, can anyone think of a way to update this checkbox group field with a single query rather than 15 separate queries? Thanks for any help. Regards, Stephen.

UPDATE exp_channel_data SET field_id_285 = "Thursday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Saturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Sunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nFriday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSaturday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Thursday\nSunday" WHERE channel_id = 14 AND (field_id_151 <> "" AND field_id_152 = "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSaturday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 <> "" AND field_id_154 = "");
UPDATE exp_channel_data SET field_id_285 = "Friday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 <> "" AND field_id_153 = "" AND field_id_154 <> "");
UPDATE exp_channel_data SET field_id_285 = "Saturday\nSunday" WHERE channel_id = 14 AND (field_id_151 = "" AND field_id_152 = "" AND field_id_153 <> "" AND field_id_154 <> "");

Yes, you can update the group in one go using an UPDATE statement like this:

UPDATE
  exp_channel_data
SET
  field_id_285 =
    CONCAT_WS(
      '\n',
      CASE WHEN field_id_151 <> '' THEN 'Thursday' END,
      CASE WHEN field_id_152 <> '' THEN 'Friday'   END,
      CASE WHEN field_id_153 <> '' THEN 'Saturday' END,
      CASE WHEN field_id_154 <> '' THEN 'Sunday'   END
    )
WHERE
  channel_id = 14
  AND (field_id_151 <> ''
    OR field_id_152 <> ''
    OR field_id_153 <> ''
    OR field_id_154 <> ''
  )
;

Each of the CASE expressions produces either a week day name or null. The CONCAT_WS function concatenates the names using \n as the delimiter, omitting the nulls, if any.

However, even though you can do this with a single statement, the fact remains that this is a terrible design. Without more information it is hard to suggest a proper alternative but it seems likely that a junction table could be used here instead.


How do I execute a PL/SQL package from my database SQL> prompt? Sorry to ask such a basic question, but I am yet to do my Oracle course at work and I am already required to do some run PL/SQL processes.
I have installed a 'package' using SQL Developer, see it below (C_FIX_STUCK_COS).

But now I need to execute it from the SQL> prompt using SQLPlus (and finally from a Bash script).
So having added a 'stored procedure' previously and got that to run with the execute command I tried that again with my package.

execute C_FIX_STUCK_COS

But it fails with the following error:

BEGIN C_FIX_STUCK_COS; END;

  *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'C_FIX_STUCK_COS' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I guess that I am not using the correct method of execution, but haven't managed to find anything online yet that will help me.

The called procedure 'inside' doesn't have any parameters:

PROCEDURE FIX_STUCK_COS
-- Main procedure, called from external source
IS
  TYPE GEN_CURSOR       IS REF CURSOR;
  v_CO_CUR              GEN_CURSOR;

Please can someone just explain quickly (if that is possible) how a package differs from a 'stored procedure'?
But more importantly, can someone please tell me how I can execute the 'package'?
Thanks very much.

You cannot execute a PL/SQL package, as it is simply a container for one or more routines (stored procedures and functions). Typically you use packages to organize various related routines.

You execute (call) individual routines in a package by referencing them by their names, e.g.

exec PKG_NAME.SPROC_NAME

or in your case

exec C_FIX_STUCK_COS.FIX_STUCK_COS

as both package and SP have very similar names (which is not necessarily a good idea in itself).


Are there any good database management applications for JDBC/Java databases? I am working with an Apache Derby/JavaDB database via JDBC. Before have I been working with MySQL and used phpMyAdmin as a good Database Management tool.

Is there any good desktop application for managing databases over JDBC?

DbVisualiser could fulfill your requirements as it supports quite a number of RDBMS, including JavaDB/Derby. You can see it in action below:

The only question mark is if the free version has some limitations which are a stopper for you. You can check the matrix here.


Select users that are in the same team We have these 3 tables:

USERS

userid
name
email

TEAMS

teamid
name
options (0,1,2 or 3)

TEAMUSER

id
teamid
userid
isadmin (0 or 1, if they are administrator of team)

Let's say we have user 1, 2 and 3 inside users table. Lets also say we have 3 teams, team 1, 2 and 3.

User 1 and 2 are part of the same team, the team id 3. So there are rows in teamuser table also.
User 1 is also part of team 2, and user 3 is part of team 3, but it's not relevant here.

THE QUESTION IS THIS:

How do I get an SQL that says:
SHOW ME THE TEAMS WHERE USER 1 AND 2 (OR X AND Y) ARE PART OF THE SAME TEAM? (and isadmin to see if they are admin and the team options also)

So user 1 and 2 can be part of more than 1 team together, they can be together in several teams, and that's what I'm interested in. Not the teams that they are individually in, but only the teams that they are both in.

So it would be awesome to get a list of rows like this if it's possible in one query:

teamid | userid | isadmin | options
-----------------------------------
  3    |   1    |    1    |    1
  3    |   2    |    0    |    1

-- get all the unique team ids which have both user1 and user2
SELECT DISTINCT a.teamid
FROM
( SELECT teamid FROM `TEAMUSER` WHERE userid = user1 ) AS a
INNER JOIN
( SELECT teamid FROM `TEAMUSER` WHERE userid = user1 ) AS b
ON a.team_id = b.team_id


Any way to force "garbage collection" in TokuDB? Playing around with TokuDB I'm finding even after "optimize table" for things such as changing row compression or other DDLs it takes a non deterministic amount of time for freed space to be reflected in the file system.

Is there anyway to force this more immediately or otherwise view status of if the clean up is in progress or otherwise scheduled?

Have you simply tried "mysql> show processlist;"? TokuDB displays the progress of optimize operations in plain view.


Cascade primary key update to all referencing foreign keys in oracle Is it possible to update all referencing foreign keys in Oracle when updating primary key?

A procedure like this that gets the table name and primary key and finds all foreign keys in all tables and update that with the new value

  PROCEDURE cascade_update_primary(
                          table_name IN VARCHAR2,
                          primary_column IN Number,
                          new_value IN Number);

Oracle does not support ON UPDATE CASCADE.

With DEFERRABLE foreign key constraints:

create or replace procedure cascade_update_primary
(
  p_table_name in varchar2,
  p_old_value in number,
  p_new_value in number
)
as
  l_column_name varchar2(128 char);
begin
  select
    cc.column_name into l_column_name
  from
    user_cons_columns cc
    join user_constraints c on (c.constraint_name = cc.constraint_name)
  where
    c.constraint_type = 'P' and c.table_name = p_table_name
  ;

  execute immediate 'set constraints all deferred';

  execute immediate 'update "' || p_table_name || '" set "' || l_column_name ||
    '" = :B1 where "' || l_column_name || '" = :B2' using p_new_value, p_old_value;

  for c in (
    select
      cc2.table_name, cc2.column_name
    from
      user_cons_columns cc
      join user_constraints c on (c.constraint_name = cc.constraint_name)
      join user_constraints c2 on (c.constraint_name = c2.r_constraint_name)
      join user_cons_columns cc2 on (c2.constraint_name = cc2.constraint_name)
    where
      c.constraint_type = 'P' and c.table_name = p_table_name
      and cc.column_name = l_column_name
  )
  loop
    execute immediate 'update "' || c.table_name || '" set "' || c.column_name ||
      '" = :B1 where "' || l_column_name || '" = :B2' using p_new_value, p_old_value;
  end loop;
end;
/

In case of NOT DEFERRABLE foreign keys, with some modifications:

insert parent row with new PK value
update child tables to new value
delete parent row with old PK value


mysql emits BINLOG rows even though binlog_format=STATEMENT We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default)

According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in our binlogs, as the first statement in each binlog.

Why is that?

Whenever mysql writes to binary logs, it has to encode all the operations.

There exists a base64 constant at the head of every binary log : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number.

This value is registered in log_event.h in the mysql source code.

Other events are also encoded in log_event.h:

enum Log_event_type
{
  UNKNOWN_EVENT= 0, START_EVENT_V3= 1, QUERY_EVENT= 2, STOP_EVENT= 3,
  ROTATE_EVENT= 4, INTVAR_EVENT= 5, LOAD_EVENT= 6, SLAVE_EVENT= 7,
  CREATE_FILE_EVENT= 8, APPEND_BLOCK_EVENT= 9, EXEC_LOAD_EVENT= 10,
  DELETE_FILE_EVENT= 11, NEW_LOAD_EVENT= 12, RAND_EVENT= 13,
  USER_VAR_EVENT= 14, FORMAT_DESCRIPTION_EVENT= 15, XID_EVENT= 16,
  BEGIN_LOAD_QUERY_EVENT= 17, EXECUTE_LOAD_QUERY_EVENT= 18,
  TABLE_MAP_EVENT = 19, PRE_GA_WRITE_ROWS_EVENT = 20,
  PRE_GA_UPDATE_ROWS_EVENT = 21, PRE_GA_DELETE_ROWS_EVENT = 22,
  WRITE_ROWS_EVENT = 23, UPDATE_ROWS_EVENT = 24,
  DELETE_ROWS_EVENT = 25, INCIDENT_EVENT= 26,
  HEARTBEAT_LOG_EVENT= 27,
  ENUM_END_EVENT /* end marker */
};

Whenever mysqlbinlog dumps the contents of a binary logs, it has to see these event numbers, especially the binlog magic number. If this value is missing, that quickly reveals one of the following:

the file is not a binary log
the binary log is horrifically corrupt

If mysqlbinlog dumps the expression BINLOG, the file it is reading can be trusted to be a binary log because it encoutered the binlog magic number when parsing the file.

For more information on this: please see MySQL Internals on Binary Logs


AlwaysOn Availability Groups log_send_rate On all our AlwaysOn setups, running Windows 2012 and SQL Server 2012 in virtual machines and on bare-metal, I find that the log_send_rate in
sys.dm_hadr_database_replica_states is consistently returning incorrect values.

For example (for synchronous mode)

sys.dm_hadr_database_replica_states.log_send_rate (ave = 36,571 (kb/s listed in bol))

Perfmon - SQLServer:Availability Replica - Bytes Sent to Replica/sec (max = 486,000.000, avg = 259,000.000)

Perfmon - SQLServer:Databases - Log Bytes Flushed/sec  (max = 653,044.000, avg = 341,000.000)

I've not seen any posts about this but it doesn't appear to be working correctly. A correct log_send_rate value is useful for monitoring AlwaysOn.

Has anyone else experienced this?

Yes, this was fixed recently in Service Pack 2, Cumulative Update 3. Here's the KB article: http://support.microsoft.com/kb/3012182

"FIX: Log_Send_Rate column in sys.dm_hadr_database_replica_states cannot reflect the rate accurately in SQL Server 2012"


Problems of having DR as Synchronous Commit In our always on set up we have One Primary and  One Secondary as Synchronous Commit and the DR as asynchronous commit. If i want to change the DR to Synchronous Commit will there be any performance issues ?

If i want to change the DR to Synchronous Commit will there be any performance issues ?

Well, it will take longer to commit transactions, as the primary will wait for the log records to be saved on both sync replicas during each commit.  Whether that causes a "performance issue" depends on the transaction throughput, network bandwidth, distance to the DR location, and the tolerance of the application and users for slower commits.


Will exporting tables in one db server to another db server release space in server? Our production db server mount points are 95% full.There are a few tables which are not required. Hence if I export those tables to our test db server and drop those tables, will it release space in production db server?

Thanks

Short answer: probaply not.
Longer answer: It depends. If they have their own tablespace and you drop the tablespace also, then yes.

If your test tables are in a tablespace that holds other data and you can‘t drop it then no. The Space is freed in the tablespace itself, but not on disk. To do that you need to reorganize the tablespace.

It depends on the database version and edition if you can do this online . Another option would be to create a new tablespace, copy all objects from the old tablespace into the new one.


Access 2010-2016 Compatibility Error I was just updated to Access 2016, while my users are still at Access 2010.

I opened our database in MS Access 2016 once, didn't make any changes, and closed it out, and now all the users are having errors for ANY basic VBA code, including:

StrConv (Me.FormField, vbProperCase)
DoCmd.GoToRecord
DoCmd.RunCommand acCmdSaveRecord

etc.

However, it works fine on my end. Totally broken for all my users on Access 2010. Is there any way to fix this? I am thinking, even if I go back to Access 2010, if these are errors for code that would normally work fine in either version, how to I resolve these errors?

Tried a compact and repair, no effect.

Here's an example: In Access 2010,  when the AfterUpdate() event is triggered on a textbox that has the StrConv (Me.FormField, vbProperCase) function, it brings up the VBA debugger.
The error is: Compile error: cannot find  project or library.

Previously, the exact same function worked fine in access 2010, and there have been no changes to this code. It works fine in a backup copy from about 15 days ago.

Community wiki answer:

That particular error message is the key:

  Compile error: cannot find  project or library

Opening the office 2010 database in 2016 removed all of the VBA references to 14.0 Object Libraries in the References menu, and replaced them with 16.0, which is not present in Office 2010.

Broken references are to be expected when the .mdb is opened in an older version. It happens when there are references to Office libraries (Word, Excel, etc). Edit the references (in the VB Editor) in the oldest version (the one that doesn't run the code).


Can this query be optimized further? I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to page.

exec sp_executesql N'
set arithabort off;
set statistics time on;
set transaction isolation level read uncommitted;

With cte as
(
    Select
        peta_rn = ROW_NUMBER() OVER (ORDER BY  d.LastStatusChangedDateTime  desc),
        d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime,
        d.CreatedByAccountID, d.JurisdictionID,
        d.LastStatusChangedDateTime as LastStatusChangedDateTime,  d.IDate,
        d.InstrumentID, d.DocumentStatusID , u.Username, it.Abbreviation AS ITypeAbbreviation,
        ig.Abbreviation AS IGroupAbbreviation, d.DocumentDate
        From ITypes it
        Inner Join Documents d on it.ITypeID = d.ITypeID
        Inner Join Users u on d.UserID = u.UserID
        Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
        Where
            1=1 AND
            (  d.DocumentStatusID = 9  ) And
            (CreatedByAccountID = @0 Or DocumentStatusID = @1 Or DocumentStatusID = @2)  And
            (  d.IGroupID = @3   Or d.IGroupID = @4   ) And
            (d.JurisdictionID = @5 Or DocumentStatusID = @6 Or DocumentStatusID = @7)
)
Select
    cte.DocumentID, cte.IsReEfiled, cte.IGroupID, cte.ITypeID, cte.RecordingDateTime,
    cte.CreatedByAccountID, cte.JurisdictionID,
    cte.LastStatusChangedDateTime as LastStatusChangedDateTime, cte.IDate, cte.InstrumentID,
    cte.DocumentStatusID,cte.IGroupAbbreviation, cte.Username, j.JDAbbreviation, inf.DocumentName,
    cte.ITypeAbbreviation, cte.DocumentDate, ds.Abbreviation as DocumentStatusAbbreviation,
    ds.Name as DocumentStatusName, (SELECT COUNT(*) FROM CTE) AS TotalRecords
    FROM cte
    Inner Join DocumentStatuses ds On cte.DocumentStatusID = ds.DocumentStatusID
    Left Join InstrumentFiles inf On cte.DocumentID = inf.DocumentID
    Inner Join Jurisdictions j on j.JurisdictionID = cte.JurisdictionID
    Where
        1=1 And
        peta_rn>@8 AND
        peta_rn<=@9
    Order by peta_rn',
    N'@0 int,@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int',
    @0=44,@1=5,@2=9,@3=3,@4=4,@5=1,@6=5,@7=9,@8=90000,@9=90050

This is my statistics output:
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

(50 row(s) affected)

 SQL Server Execution Times:
   CPU time = 717 ms,  elapsed time = 727 ms.

and this is my query plan XML. All indexes are created properly. I am unable to post here due to body limit of 30K.

https://www.dropbox.com/s/vrx5ec6cpi8vn33/plan.txt

Any help would be appreciated. Thanks!

First of all, as you are getting sub-second performance how much time do you want to spend on this?  If you spend an hour on it, and you tune it to half a second, how long will it take to pay for itself?  If this query runs several times a second, then I'm with you, let's tune it.  Otherwise it's not really worth it.

Having said that, there are a number of warning signs in your query and plan which need closer attention, if you have time.  This is a strange looking query - is it dynamically generated?

1) Rowcount Discrepancy

The dbo.IGroupes table is showing Estimated Rows (7) * Estimated Executions (100.99), so ~707 rows, versus 630,346 Actual Rows.  Rowcounts which are more of an order of magnitude difference tend to be a bit of a warning sign.  So why do you think this is?  What is the relationship between the dbo.Documents table and the dbo.IGroupes table?  Should you be getting a distinct list from here instead?  This would also mean the estimated cost for this operator is way off too.  There are other rowcount discrepancies and Plan Explorer does a good job of showing this:

2) Optimizer Timeout

If you click the SELECT operator in the plan and look at the properties, you will see the property "Reason for Early Termination of Statement Optimization" marked as "Time Out".  This means the optimizer ran out of "time" (more like iterations than time) and returned with the lowest estimated cost plan it had at the time.  This is generally an indication the query is too complicated and the workaround is to simplify.  Looking at your query you have the dummy 1=1 clause and a number of other OR clauses.  Can this be simplified?

A few other points: you should be aware that if you have the "Include Actual Execution Plan" option on, this negatively affects the values from SET STATISTICS TIME ON.  This is the Observer Effect.  Are your values from a run with the plan on or off?

One of my normal approaches to query tuning is to break it up.  This helps identify true bottlenecks.  It may be that later on I put the query back together.  Here's one example, but it's worth noting in my test rig, there's no real performance difference between the original query and this one:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
      IDENTITY( INT, 1, 1 ) rn
    , CAST( d.DocumentID AS INT ) DocumentID
    , d.IsReEfiled
    , d.IGroupID
    , d.ITypeID
    , d.RecordingDateTime
    , d.CreatedByAccountID
    , d.JurisdictionID
    , d.LastStatusChangedDateTime
    , d.IDate
    , d.InstrumentID
    , d.DocumentStatusID
    , d.DocumentDate
    , d.UserId

INTO #tmp
FROM Documents d
WHERE ( d.DocumentStatusID = 9 )
  AND ( d.CreatedByAccountID = @0 OR d.DocumentStatusID = @1 OR d.DocumentStatusID = @2 )
  AND ( d.IGroupID = @3 Or d.IGroupID = @4 )
  AND (d.JurisdictionID = @5 Or d.DocumentStatusID = @6 Or d.DocumentStatusID = @7 )
ORDER BY d.LastStatusChangedDateTime DESC

SELECT
      t.DocumentID
    , t.IsReEfiled
    , t.IGroupID
    , t.ITypeID
    , t.RecordingDateTime
    , t.CreatedByAccountID
    , t.JurisdictionID
    , t.LastStatusChangedDateTime
    , t.IDate
    , t.InstrumentID
    , t.DocumentStatusID
    , ig.Abbreviation AS IGroupAbbreviation
    , u.Username
    , j.JDAbbreviation
    , inf.DocumentName
    , it.Abbreviation AS ITypeAbbreviation
    , t.DocumentDate
    , it.Abbreviation AS DocumentStatusAbbreviation
    , ds.Name AS DocumentStatusName
    , ( SELECT COUNT(*) FROM #tmp ) AS TotalRecords
FROM #tmp t
    INNER JOIN dbo.ITypes it ON t.ITypeID = it.ITypeID
    INNER JOIN dbo.Users u ON t.UserID = u.UserID
    INNER JOIN dbo.IGroupes ig ON t.IGroupID = ig.IGroupID
    INNER JOIN dbo.DocumentStatuses ds On t.DocumentStatusID = ds.DocumentStatusID
    INNER JOIN dbo.Jurisdictions j on t.JurisdictionID = j.JurisdictionID
    LEFT JOIN dbo.InstrumentFiles inf On t.DocumentID = inf.DocumentID
WHERE rn > @8 AND rn <= @9
ORDER BY rn
GO

If you want to carry on, please provide the schema and indexes from your tables, plus either the stats or an idea of the rowcounts.


Change Tracking Bad Plans in SQL Server 2016 Has anyone else experience bad plans when calling CHANGETABLE in SQL Server 2016?

I have an application that uses change tracking to update a cache. Application servers get the changes for multiple tables every second. The CHANGETABLE is called so often that usually returns just a few rows. This works great in 2008 R2 and 2012. When I tested 2016 I found that the CPU spiked and found that the plan is scanning change tracking internal tables where it used to do a seek.

If you want to see this behavior here are the steps to replicate it:

Create a database and setup change tracking.
Create a Table:

CREATE TABLE [dbo].[ChangeTable_Test](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Test_ChangeTable_Id] PRIMARY KEY CLUSTERED
(
     [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChangeTable_Test] ENABLE CHANGE_TRACKING
GO
INSERT INTO ChangeTable_Test (Name) VALUES ('Test')
GO 1000

Include the actual execution plan and run the following.

SET STATISTICS IO ON;
SET NOCOUNT OFF;
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 110
GO
DECLARE @CurrentVersion INT;
SELECT  @CurrentVersion=CHANGE_TRACKING_CURRENT_VERSION ()-1;
SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C
GO
ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 130
GO
DECLARE @CurrentVersion INT;
SELECT  @CurrentVersion=CHANGE_TRACKING_CURRENT_VERSION ()-1;
SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C

The above demo works for me on SQL Server 2016 SP1-CU4. I uploaded two plans to Paste the Plan. The first one is under COMPAT 110 and the second is under  130.

Microsoft support acknowledged the problem but do not plan to fix it. They suggested workarounds using a USE PLAN hint or Plan Guides. But it would make a maintenance nightmare for this product.

The funny thing is that 2014 and 2017 do not pick the bad plans and they use the new Cardinality Estimator.

Do you have any suggestions beside of the ones I mentioned?

Based on your question it sounds like you have a highly concurrent application that calls CHANGETABLE on a variety of tables in many different queries. Forcing each query plan to have a specific shape with a USE PLAN hint or a plan guide would be a Herculean effort. Changing your active database to have a compatibility level of 110 without query fixes may be a bad option for other reasons.

The suggestion in this answer could be difficult to implement but it feels like a better option than the ones listed above. The idea is to replace the call to CHANGETABLE with a stored procedure that inserts the data from CHANGETABLE into a temp table. Queries that need change data can use the temp table populated instead of CHANGETABLE directly. Your application code might look like this:

DECLARE @CurrentVersion INT = CHANGE_TRACKING_CURRENT_VERSION () - 1;

CREATE TABLE #changes (SYS_CHANGE_VERSION bigint NULL);

EXEC dbo.CHANGETABLE_API '#changes', 'TEST', 'dbo', 'ChangeTable_Test', @CurrentVersion;

-- run queries that need #changes temp table

DROP TABLE #changes;

Working around scoping issues requires a few tricks. The stored procedure has three parts:

Add static columns to the temp table: SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, and SYS_CHANGE_CONTEXT
Add dynamic primary key columns to the temp table. You can use sys.columns and other dmvs for this.
Insert into the temp table from CHANGETABLE under the context of an empty database with 2012 compatibility level and whatever other settings you need to get good performance.

Below is a quick and dirty implementation of most of the above. It doesn't have any kind of error checking or protection against SQL injection attacks. Feel free to do whatever you want with the code:

CREATE OR ALTER PROCEDURE dbo.CHANGETABLE_API (
    @temp_table_name SYSNAME,
    @changes_database_name SYSNAME,
    @changes_schema_name SYSNAME,
    @changes_table_name SYSNAME,
    @last_sync_version BIGINT
)
AS
BEGIN
    DECLARE @sql_to_add_static_cols NVARCHAR(4000),
    @sql_to_add_table_cols NVARCHAR(4000),
    @sql_to_insert_rows NVARCHAR(4000);

    -- okay to hardcode these depending on how you call CHANGETABLE
    SET @sql_to_add_static_cols = N'ALTER TABLE '
    + QUOTENAME(@temp_table_name)
    + N' ADD '
    + N'SYS_CHANGE_CREATION_VERSION bigint NULL, '
    + N'SYS_CHANGE_OPERATION nchar(1) NULL, '
    + N'SYS_CHANGE_COLUMNS varbinary(4100) NULL, '
    + N'SYS_CHANGE_CONTEXT varbinary(128) NULL';

    EXEC (@sql_to_add_static_cols);

    -- this should be dynamic based on sys.columns and other dmvs
    SET @sql_to_add_table_cols = N'ALTER TABLE '
    + QUOTENAME(@temp_table_name)
    + N' ADD id BIGINT NOT NULL';

    EXEC (@sql_to_add_table_cols);

    -- key is to run the insert in a database with the settings that you need for a good query plan
    SET @sql_to_insert_rows = N'USE DB_2012_COMPAT; '
    + N'INSERT INTO '
    + QUOTENAME(@temp_table_name)
    + N' SELECT * FROM CHANGETABLE(CHANGES '
    + QUOTENAME(@changes_database_name)
    + N'.' + QUOTENAME(@changes_schema_name)
    + N'.' + QUOTENAME(@changes_table_name)
    + N', ' + CAST(@last_sync_version AS NVARCHAR(30))
    + N') AS C';

    EXEC (@sql_to_insert_rows);
END;

When I call the stored procedure in a database with compatibility level 130 I get a query plan that only does seeks as desired:

In the same session if I run the following code:

DECLARE @CurrentVersion INT = CHANGE_TRACKING_CURRENT_VERSION ()-1;
SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C
OPTION (RECOMPILE);

I get a scan which you wanted to avoid:


Effect of Reorganizing and Rebuilding an index What are the effects of Index Rebuilding and Reorganizing on clustered and non-clustered indexes. How does these 2 operations affect the logical storage and physical storage of data in Hard disk?

The MSDN page says this about reorganizing and rebuilding indexes:

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.
You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

Doe that cover your question?


Postgres - window function rank and count I have some tables that track athletes time around a track:

competitions, competition_users and competition_user_sessions

I am constructing a query that extracts the current rank and total no of competitors for each supplied 'competition_user', I can get rank ok, but my count (totalUsers) doesn't count all competitors in the competition, it only seems to count up to the supplied user (eg. gives the same as the rank)

SELECT compUserId, rank, totalUsers
    FROM (
        SELECT cu.competition_user_id as compUserId, cu.user_id as userId,
    count(*) OVER w as totalUsers, rank() OVER w as rank
        FROM competition_users cu
        LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id
        LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id
        WHERE cu.left_competition = false
        AND cu.competition_id in (:compIds)
        WINDOW w AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC)
    ) as sub
WHERE compUserId in (:compUserIds)

My understanding was that the default frame for was the entire window, whereas this seems to be counting from frame start to current row?

Your issue appears to be that you are applying the same WINDOW (named w) for both your COUNT(*) and your rank().

When you use a WINDOW which contains an ORDER BY clause, and you then apply certain aggregations such as SUM or COUNT, it applies the aggregation continuously across the ordering, which is why your COUNT and rank() are identical.

If you modify your query have multiple windows as

SELECT compUserId, rank, totalUsers
FROM (
    SELECT cu.competition_user_id as compUserId, cu.user_id as userId,
    count(*) OVER (PARTITION BY cu.competition_id) as totalUsers,
    rank() OVER (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) as rank
    FROM competition_users cu
    LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id
    LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id
    WHERE cu.left_competition = false
    AND cu.competition_id in (:compIds)
) as sub
WHERE compUserId in (:compUserIds);

so that you are only applying the PARTITION BY to your COUNT(*) window, and have both PARTITION BY and ORDER BY clauses for your rank(), I believe you'll get the results you want.

Refer to this SQL FIDDLE as a reference, where I have a generic id field, a com_num field to represent the competition id, and a com_time field to represent a competitors time.


mysqldump with INSERT ... ON DUPLICATE I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate entries or something else) in this case.

But I do some merges for testing purposes and I'll do final merge later. So, I want to execute merge (data may be changed) a few times. Notice, my rows in my tables never deletes, only can be inserted or updated.

Can I create mysqldump with ON DUPLICATE option? Or may be I can merge dump that inserts new data and update modified data?

Sure, I can insert ON DUPLICATE in dump manually, but I want to automate merge process.

There are options to help you in this:

  --insert-ignore     Insert rows with INSERT IGNORE.
  --replace           Use REPLACE INTO instead of INSERT INTO.
  -t, --no-create-info
                      Don't write table creation info.

Keep this paradigm in mind

mysqldump everything from DB1 into DUMP1
load DUMP1 into DB3
mysqldump everything from DB2 using --replace (or --insert-ignore)
and --no-create-info into DUMP2
load DUMP2 into DB3


Update statement using Case statement, derived table, and ROW_NUMBER Running MS SQL 2008 R2. I am provided a CSV list of product-IDs which I parse into individual rows and normalize. I then need to take those product-IDs (anywhere from 1-10) and write them to their corresponding columns in a table. I'm using a combination of CASE statements, a derived table, along with ROW_NUMBER in order to assign the correct product-ID value to each corresponding column. For some reason, it only seems to update the product_id_1 column, which is not right since there are other values stored in the #contactProducts table Below please find the T-SQL to recreate my issue:

CREATE TABLE #contactProducts(
[contact_product_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[product_id] [varchar](50) NOT NULL,
[createDt] [datetime] NOT NULL,
CONSTRAINT [PK_#contactProducts] PRIMARY KEY CLUSTERED
(
[contact_product_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #contactProducts VALUES(3,'B6281-81',getDate())
INSERT INTO #contactProducts VALUES(3,'B100839-1',getDate())
INSERT INTO #contactProducts VALUES(3,'B101203-5',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())

SELECT * FROM #contactProducts

-- Temp_Import_2
CREATE TABLE #temp_import_2(
[temp_import_2_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[PRODUCT_IDS] [varchar](2000) NULL,
[PRODUCT_ID_1] [varchar](50) NULL,
[PRODUCT_ID_2] [varchar](50) NULL,
[PRODUCT_ID_3] [varchar](50) NULL,
[PRODUCT_ID_4] [varchar](50) NULL,
[PRODUCT_ID_5] [varchar](50) NULL,
[PRODUCT_ID_6] [varchar](50) NULL,
[PRODUCT_ID_7] [varchar](50) NULL,
[PRODUCT_ID_8] [varchar](50) NULL,
[PRODUCT_ID_9] [varchar](50) NULL,
[PRODUCT_ID_10] [varchar](50) NULL,
CONSTRAINT [temp_import_2] PRIMARY KEY CLUSTERED
(
[temp_import_2_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #temp_import_2 (temp_import_id, product_IDs)
VALUES(3, 'B6281-81,B100839-1,B101203-5,B101204-7,B102472-14')

SELECT * FROM [#temp_import_2]

-- update with derived table
-- even tho there are values for thisRowNum = 2,3,4, etc. they don't seem to update. only works for thisRowNum = 1

UPDATE #temp_import_2 SET
 #temp_import_2.PRODUCT_ID_1 = CASE WHEN derivedTbl.thisRowNum = 1 THEN derivedTbl.product_id END
,#temp_import_2.PRODUCT_ID_2 = CASE WHEN derivedTbl.thisRowNum = 2 THEN derivedTbl.product_id ELSE '2' END
,#temp_import_2.PRODUCT_ID_3 = CASE WHEN derivedTbl.thisRowNum = 3 THEN derivedTbl.product_id ELSE '3' END
,#temp_import_2.PRODUCT_ID_4 = CASE WHEN derivedTbl.thisRowNum = 4 THEN derivedTbl.product_id ELSE '4' END
,#temp_import_2.PRODUCT_ID_5 = CASE WHEN derivedTbl.thisRowNum = 5 THEN derivedTbl.product_id ELSE '5' END
,#temp_import_2.PRODUCT_ID_6 = CASE WHEN derivedTbl.thisRowNum = 6 THEN derivedTbl.product_id ELSE '6' END
,#temp_import_2.PRODUCT_ID_7 = CASE WHEN derivedTbl.thisRowNum = 7 THEN derivedTbl.product_id ELSE '7' END
,#temp_import_2.PRODUCT_ID_8 = CASE WHEN derivedTbl.thisRowNum = 8 THEN derivedTbl.product_id ELSE '8' END
,#temp_import_2.PRODUCT_ID_9 = CASE WHEN derivedTbl.thisRowNum = 9 THEN derivedTbl.product_id ELSE '9' END
,#temp_import_2.PRODUCT_ID_10 = CASE WHEN derivedTbl.thisRowNum = 10 THEN derivedTbl.product_id ELSE '10' END
FROM (
select temp_import_id, product_id, ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
from #contactProducts
) derivedTbl INNER JOIN #temp_import_2 ON derivedTbl.temp_import_id = #temp_import_2.temp_import_id

SELECT * FROM [#temp_import_2]

DROP TABLE #contactProducts

DROP TABLE [#temp_import_2]


Start mongod instance on replica set as background job I have setup a Mongodb replica set with 1 secondary node. When i execute on secondary

sudo mongod --replSet rs0 --dbpath /data --port 27017 --oplogSize 700

The primary and secondary nodes are linked and it works perfectly but the connection logs are running in open terminal. I would like to run the above task as background job. The option of -d doesn't seem to work here.

You can make mongod run in the background with --fork and you will also need to direct your logfiles somewhere with --logpath when you do so.  Before you go about doing all of that though, you should not run mongod as root (which you are doing by using sudo in the command you pasted).

Run it as a regular user, if you have installed MongoDB from one of the packages then you should have the mongod or mongodb user on the systems.  Make sure the dbpath (/data) has the appropriate permissions (as well as wherever you put your logs) and you should have no problems.  For reference, you would change the permissions on /data as follows (assuming a user and group of mongod):

sudo chown -R mongod:mongod /data


How to use a trigger to only insert one row I am doing a third year project using an intel galileo that reads an RFID card, and signs a student into a class, and I am using multiple tables.

I have nearly completed it as all reads are been recorded into a table called read_log. What I want to achieve is when a record is entered into this read_log, set a trigger to just insert the card number, which again is been sent this table read_log.

So what i have so far is :

DROP TRIGGER IF EXISTS `test`;

CREATE DEFINER=`root`@`localhost` TRIGGER `test` AFTER UPDATE ON `readlog`
FOR EACH ROW
BEGIN
INSERT into attendence(S_no)

SELECT s.Stu_no from student s
JOIN readLog r ON s.Stu_no=r.Cardid WHERE s.Stu_no=r.Cardid;

END

I can confirm the trigger is working, as once i scan the rfid card over the galileo it inserts into the database, and the trigger triggers but enters the number 29 times, so there is 29 records entered everytime i read.

Can someone provide a tip as to what to look for or how to go about writing a trigger that will only insert one row. I have tried update instead of insert but this does nothing.

Instead of INSERT, use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE.

The former (IGNORE) checks UNIQUE key(s) to avoid inserting a second time.  The latter (IODKU) will either insert a new row or update an existing row (based on any UNIQUE key(s)).


Can sp_executesql be configured / used by default? I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I tell it to give a good reason for me being not able (too stupid) to find things out myself... I can't see any code where the queries are wrapped with sp_executesql.

But when I trace, I can see a lot of queries coming in wrapped with sp_executesql. The whole application solution does not even contain the command sp_executesql at all.

So I wondered if there is kind of a configuration I do not know yet that forces the software to wrap queries with sp_executesql by default?

What could cause this behaviour?

The reason the SQL statements are getting wrapped with sp_executesql is the setting of the SqlCommand.Commandtype property and passing any Parameters to the command.

SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", 1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

The code above ends with this T-SQL:

exec proc1 @param1=1

SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@param1", 1);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

This code ends with execution of the following T-SQL:

exec sp_executesql N'proc1',N'@param1 int',@param1=1

Addition 23.12.15: Using a CommandType.Text command, the results are similar: As soon a parameter is added to the command object, .NET will  wrap the whole query into sp_executesql and pass the parameters to it.

Addition: After diving deeper into sp_executesql, parameter sniffing and plan caching this behavior of the .NET classes totally makes sense in order to avoid high frequent query compilation and number of plans. So it's basically designed to ensure a better SQL Server performance in general while it at the same time could lead to poor performance of some queries (parameter sniffing issue) that are used with different parameter values than the initial created query plan.

See:

https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/
http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql

http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different

http://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/
https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql

The above sample were created using .NET Framework 4.5 and SQL Server 2008 Developer Edition.


Quiet installation of MS SQL 2012 RC0 from command line using config file My problem is that I recently deployed Microsoft SQL server 2008 R2 Express to create a database for a locally hosted application to access.  To save myself time I used the /CONFIGURATIONFILE option to set it all up to run from the command line so I could stript the installation from soup to nuts.

I now am trying to use the same ini file with Micro SQL Server 2012 RC0 and the thing won't install quietly.  I continually get the Window to configure installation even if I use /Q="true", /QS="true", and I even tried /QUIET="true" but the window continues to open.

Is there a way to force MS SQL Server 2012 RC0 to install quietly or is that functionality that is not in this release?

Have you specified the rest of the required command line parameters for a quiet install for instance IACCEPTSQLSERVERLICENSETERMS?

This help page covers the required parameters for a quiet command line install.


Connection issues during a drop in CPU usage I have a challenging issue that I am facing and would like to lean on this community for advice and possibly answers to this issue.

We are running a dedicated server: Windows 2008 R2 64bit OS, 32 GB of RAM.
Our SQL Server version:  MS SQL Server 2012  (11.0.3000).

Intermittently our server will drop to 0-5% CPU usage, during that time our applications have a hard time (or just cannot) connect to the SQL Server.  Further, within the database logs I am reading this:

  FlushCache: cleaned up 12007 bufs with 100094 writes in 130307 ms (ovoided 667 new dirty bufs) for db 7:0
  last target outstanding: 4, avgWriteLatency 44
  average throughput: 0.41 MB/sec, I/O saturation: 12161, context switches 25796

The above logs are written several times per day, however a few of these instances occur when our CPU drops to an extremely low rate.  Our CPU for this server normally runs around 30-60% during business hours and I have never seen it drop to this level.

The only major changes have been an update in RAM from 16 GB to 32 GB.  There were some updates to the maintenance backups, but those fire at 12AM (PST).

I am available to add any further logs or information and am extremely grateful for any help concerning this issue.

It sounds somewhat like you are hitting a bit of memory pressure. Dirty Buffers are pages in memory that have changes in them that have not been written to disk yet. Normally they would stay in memory until one of the various processes clean them up and/or write them to disk.

There are some things you will want to look into but with only the information you have given here I have to ask, "How much ram have you reserved for the OS?"

You are running a 64 bit version of Windows. Now, the 32 bit kernel will use 4 GB of RAM. However, we have found (the hard way) that 64 bit versions of the OS will use 8 - 12 GB of RAM dependent on how busy the Sql server is. By that I mean are you doing a lot of reading and writing to disk. If the OS can't allocate the memory it needs for this (because the Sql server has sucked it all up) you will see the CPU being consumed for the additional context switching it needs to do to write those buffers out to disk. When you added the additional RAM to the server did you reset the Sql Server Max Memory setting? When you only had 16 GB of RAM your Sql server was constrained as much as the windows kernel was and so you were likely using virtual disk (hard drive space) quite a bit. When you added more RAM you opened things up a bit and now the Sql server is attempting to keep more of the database(s) in memory.


Delete duplicated rows by modified_date - PostgreSQL 9.2 I've got some duplicated rows on a table called ja_jobs:

To find those duplicated data, I'm running this Query:

select * from ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false AND time_job IS NOT NULL AND (time_job,recurrenceid) IN (
select time_job,recurrenceid FROM ja_jobs WHERE clientid = 33731 GROUP BY time_job,recurrenceid HAVING count(*) > 1
)

The query finds duplicated rows by time_job and recurrenceid.

On the following example:

You can see that the jobs are duplicated, but we have three versions of it (Just look on the modified_date column)

I need to delete the new jobs and only keep the OLDEST one.

DELETE from ja_jobs WHERE id IN (14754912,14792799);

How can I do that?
How can I select all the newest jobs and just delete them?

Here what I've got so far:

select min(id) over (partition by time_job,recurrenceid,time_arrival order by created_date) as min_id into junk.test_table FROM ja_jobs
WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false AND (time_job,recurrenceid) IN (
select time_job,recurrenceid FROM ja_jobs WHERE clientid = 33731 GROUP BY time_job,recurrenceid HAVING count(*) > 1
)

But on the junk.test_table table I got duplicated "min_id"

You're mixing grouping criteria twice while creating junk_test, first in the GROUP BY subselect by having fewer conditions in the WHERE, and then in the PARTITION BY by having one extra partitioning field (time arrival).

If you can assume that older ids are older jobs, then you can identify your duplicates by joining grouped table with itself, like this:

SELECT jd.dup_group_no, j.id=jd.id AS to_keep, j.id INTO junk.test_table
FROM (
  SELECT time_job, recurrenceid, client, creatortype, deleted, MIN(id) AS id, row_number() over () AS dup_group_no
  FROM ja_jobs
  WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false
  GROUP BY time_job, recurrenceid, client, creatortype, deleted
  HAVING count(*) > 1
  ) jd
JOIN ja_jobs j USING (time_job, recurrenceid, clientid, creatortype, deleted);

If there is no guaranteed correlation between older ids and older jobs by creation time, the query is trickier:

SELECT jm.dup_group_no, j.id=jd.id AS to_keep, j.id INTO junk.test_table
FROM (
  SELECT DISTINCT ON (jd.time_job, jd.recurrenceid, jd.clientid, jd.creatortype, jd.deleted) jd.time_job, jd.recurrenceid, jd.clientid, jd.creatortype, jd.deleted, jd.id, row_number() over () AS dup_group_no
  FROM (
    SELECT time_job, recurrenceid, client, creatortype, deleted
    FROM ja_jobs
    WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false
    GROUP BY time_job, recurrenceid, client, creatortype, deleted
    HAVING count(*) > 1
    ) jd
  JOIN ja_jobs jm USING (time_job, recurrenceid, clientid, creatortype, deleted)
  ORDER BY jd.time_job, jd.recurrenceid, jd.clientid, jd.creatortype, jd.deleted, jd.created_date, jd.id
  ) jm
JOIN ja_jobs j USING (time_job, recurrenceid, clientid, creatortype, deleted);


Indexing for group by + SUM query on mysql I have a table with the following schema:

    CREATE TABLE `wordtrend` (
      `oid` bigint(20) NOT NULL AUTO_INCREMENT,
      `monitorId` bigint(20) DEFAULT NULL,
      `nGram` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `nGramWord` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `negatives` bigint(20) DEFAULT NULL,
      `neutrals` bigint(20) DEFAULT NULL,
      `positives` bigint(20) DEFAULT NULL,
      `total` bigint(20) DEFAULT NULL,
      `trendCut` datetime DEFAULT NULL,
      PRIMARY KEY (`oid`)
    ) ENGINE=MyISAM AUTO_INCREMENT=358539 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Is it possible to create an index in order to run the following query efficiently?

SELECT nGram, nGramWord, SUM(total) AS sTotal, SUM(positives), SUM(negatives), SUM(neutrals) FROM WordTrend WHERE monitorId = 21751021 AND trendCut >= '2011-01-01 00:00:00' GROUP BY nGram, nGramWord ORDER BY sTotal DESC

We already tried the following:

KEY `RollupIndex` (`monitorId`,`trendCut`)
KEY `RollupIndex2` (`monitorId`,`nGram`,`trendCut`)

but we are getting "Using where; Using temporary; Using filesort" on the extra column.

I noticed something interesting about your query: you are getting these totals for this year. The trendCut date is not a static value. It is a very bad candidate for an index column since the column value changes and it is neither first nor last in the column order of the index.

The index you need is this

monitorID,trendCut,nGram,nGramWord,total

However, this would still be deficient.

Suggestion: If you do queries that require trendCut by year, you should have a trendCutYear column. Try the following:

Create New wordtrend Table
Create TrendCutYear Column in the New wordtrend Table
Create New Index in the New wordtrend Table
Load the New wordtrend Table from the Old wordtrend Table
Keep the Old wordtrend table under another name

Here are those steps:

CREATE TABLE wordtrendnew LIKE wordtrend;
ALTER TABLE wordtrendnew ADD COLUMN trendCutYear SMALLINT NOT NULL;
ALTER TABLE wordtrendnew ADD INDEX (monitorID,trendCutYear,nGram,nGramWord,total);
ALTER TABLE wordtrendnew DISABLE KEYS;
INSERT INTO wordtrendnew
SELECT oid,monitorId,nGram,nGramWord,negatives,
neutrals,positives,total,trendCut,YEAR(trendCut)
FROM wordtrend;
ALTER TABLE wordtrendnew ENABLE KEYS;
ALTER TABLE wordtrend RENAME wordtrendold;
ALTER TABLE wordtrendnew RENAME wordtrend;

Now, you have a static trendCutYear. You can now query using a static value in the middle of the index. The query can now be rewritten using trendCutYear instead of trendCut:

SELECT nGram, nGramWord, SUM(total) AS sTotal,
SUM(positives), SUM(negatives), SUM(neutrals) FROM WordTrend
WHERE monitorId = 21751021 AND trendCutYear = 2011
GROUP BY nGram, nGramWord ORDER BY sTotal DESC;

This should perform a whole lot better for you.

Give it a Try !!!


How to search a given date value in all tables of present in database I need a query to search for a particular date 2017-04-01 in every table in my database.

My database has many tables and I don't know in which table this value 2017-04-01 exists.

Please suggest a query which will help me to find the given date value in all the tables present in the database.

Datatype is DATETIME.

You'll need to construct some dynamic T-SQL in order to search all columns in your database for a specific value.
Since you're searching for a DATETIME value, I've constructed the following example code which should point you in the right direction:
SET NOCOUNT ON;
DECLARE @FindText NVARCHAR(MAX) = 'CONVERT(DATETIME, ''2017-04-01 00:00:00.000'')';
DECLARE @cmd NVARCHAR(MAX);
DECLARE @deets NVARCHAR(MAX);
DECLARE @Params NVARCHAR(MAX);
DECLARE @Name VARCHAR(1000);
DECLARE @Count INT;
DECLARE @RowNum INT;
SET @RowNum = 0;
SET @cmd = '';
SET @deets = '';
SET @Params = '@Name VARCHAR(1000) OUTPUT, @Count INT OUTPUT';

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT 'SELECT @Name = ''' + s.name + '.' + o.name + '.' + c.name + ''', @Count = COUNT(1) FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' c WHERE ' + QUOTENAME(c.name) + ' = ' + @FindText + ' HAVING COUNT(1) > 0;'
    , 'SELECT * FROM ' + s.name + '.' + o.name + ' WHERE ' + QUOTENAME(c.name) + ' = ' + @FindText + ';'
FROM sys.schemas s WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON s.schema_id = o.schema_id
    INNER JOIN sys.columns c WITH (NOLOCK) ON o.object_id = c.object_id
    INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.is_ms_shipped = 0
    AND o.type_desc = N'USER_TABLE'
    AND s.name <> N'sys' --ignore the sys schema
    AND t.name IN ( --could eliminate all but datetime if certain!
          'date'
        , 'datetime2'
        , 'datetimeoffset'
        , 'smalldatetime'
        , 'datetime'
    );

OPEN cur;
FETCH NEXT FROM cur INTO @cmd, @deets;
WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT N'RowNum:' + CONVERT(VARCHAR(50), @RowNum);
    SET @Name = NULL;
    SET @Count = NULL;
    EXEC sp_executesql @cmd, @Params, @Name = @Name OUT, @Count = @Count OUT;
    IF @Count > 0 PRINT @Name + ': ' + CONVERT(VARCHAR(50), @Count) + '                            ' + @deets;
    SET @RowNum = @RowNum + 1;
    FETCH NEXT FROM cur INTO @cmd, @deets;
END
CLOSE cur;
DEALLOCATE cur;

Since this type of code can be susceptible to code-injection vulnerabilities, I'd never recommend using this in a stored procedure, or any code that is called by code where a user can enter the value for @FindText.  See "little bobby tables" for details about that.


Browse for Servers does not display local instance I just installed SQL Server and SQL Server Management Studio but it doesn't show anything in servers list and I don't know how to connect to it.

This shows that I have installed SQL Server:

But in this image you can see that my Browse for Servers dialog is empty:

Network servers is empty too.

Your title says you have an installation problem, but I don't think this is the case (it seems like you have installed SQL Server okay, you just can't find it). Of course I don't know exactly what options you selected during setup, but from the programs applet, it seems like you should have at least one instance installed.

The Browse for Servers dialog is hokey and won't always find all instances, even on your own computer, even on the best days. I would rather use Configuration Manager to determine what's installed. Find the most recent version of this, since older versions of the Configuration Manager won't be able to see/manage newer versions of SQL Server (but the older versions of Config Manager don't get replaced or uninstalled).

If you can't find Configuration Manager, you can add it to any generic MMC console. Run mmc from the command line:

mmc.exe

(If you have that awful thing called UAC enabled, you may have to give the tool permission to run.)

Then choose file > add/remove snap-in:

Then pick SQL Server Configuration Manager and click Add (if there are multiple, they are from different versions, but not labeled correctly, so add all of them), then click OK.

Then open SQL Server Services, and you will see the list of instances on the right. If you had to add multiple nodes, make sure you check all of the nodes, since it is impossible to tell visually which node represents which version of Config Manager.

If that list is empty, you did not install SQL Server correctly.

To connect to one of those, first you need to make sure it's running; if not, right-click and select Start:

Now in Management Studio you should be able to connect by entering the right instance name. It will be in this form:

(local)\instance_name_from_within_brackets_in_the_list

So, once I have started that instance picture above, I would enter the following in the SSMS connect to server dialog:

(local)\SQL2016_407

Unless the instance name in brackets is MSSQLSERVER - this is a special display name reserved for the default instance. In this case you should be able to connect using just (local).

There are some other exceptions as well - for example if you only installed LocalDB, this is not going to show up as an active instance in Configuration Manager - this is by design (the whole point of LocalDB is to remove instance-level management). For more information on using LocalDB, see this tip.


Pre-filtering on right table in a LEFT JOIN I have the following schema/query where I basically need to filter parent table by some criteria and then collect aggregated data from its child table(s):

create table parent (id integer primary key, name text not null);
create table child (id integer primary key, pid integer not null references parent(id));

select parent.id, parent.name, q.cnt from parent
left join (
  select pid, count(*) cnt from child
-- where pid in (select id from parent where name like '%xyz%')
  group by pid
) q on parent.id = q.pid
where name like '%xyz%'

(Actual schema/query is more convoluted but the gist is the same.)

The issue is, unless I uncomment the WHERE clause on the right table the query takes significantly more time to execute. Having to specify the same filter in two places doesn't feel right. Am I doing something wrong? Should I transform the query somehow? Why is the inner filter even necessary; shouldn't it automatically discard records from the right table that cannot be joined? The database is PostgreSQL by the way.

For example you can do it this way:

select p.id, p.name,
COALESCE((select count(*) from child where p.id = pid), 0) AS cnt
from parent AS p
where name like '%xyz%'

or this:

select p.id, p.name,
count(c.pid) AS cnt
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id, p.name

if you need more columns from parent:

select p1.*, CTE.cnt
from parent p1
join (select p.id
count(c.pid) AS cnt
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id) as CTE on p1.id = CTE.id


Can I compress two rows with a difference in two columns into a single row with both columns? I have a query that returns customer product information that shows which region a product is sourced from.

Query:

SELECT
    prod.prod_id,
    prod.is_source,
    link.prod_link_disabled,
    loc.location_id,
    loc.customer_id,
    cust.customer_name
FROM
    product prod,
    product_link link,
    region region,
    location loc,
    customer cust
WHERE
    prod.prod_id = link.prod_id
    AND link.region_id = region.region_id
    AND region.loc_id = loc.loc_id
    AND link.prod_link_deleted = FALSE
    AND loc.customer_id = customer_id
    AND prod.prod_id = 5874;

This returns the following result:

| prod_id             | is_source | prod_link_disabled | location_id | customer_id | customer_name   |
| ------------------- | --------- | ------------------ | ----------- | ----------- | --------------- |
| Test Product - 5874 |         t |                  f |           2 |         332 | Test Playground |
| Test Product - 5874 |         f |                  f |           1 |         332 | Test Playground |

This is showing that product 5874 is coming from location 2 (because is_source is true).

I'd like to compress this information into a single row. I'd like a result that looks like this:

| prod_id             | prod_link_disabled | src_location_id | dest_location_id | customer_id | customer_name   |
| ------------------- | ------------------ | --------------- | ---------------- | ----------- | --------------- |
| Test Product - 5874 |                  f |               2 |                1 |         332 | Test Playground |

This compressed the two previous rows into a single row where src_location_id is 2 (again because that row had is_source as true) and dest_location_id is 1

How can I pivot the two row result into a single row?

It is safe to assume that there are always only two results in the first query.

Do I understand correctly and you need something like:

SELECT prod1.prod_id,
       link1.prod_link_disabled,
       loc1.location_id AS src_location_id,
       loc1.location_id AS dst_location_id,
       loc1.customer_id,
       cust1.customer_name
  FROM product AS prod1
  JOIN product_link AS link1 ON link1.prod_id = prod1.prod_id
  JOIN region AS region1 ON region1.region_id = link1.region_id
  JOIN location AS loc1 ON loc1.loc_id = region1.loc_id
  JOIN customer AS cust1 ON cust1.customer_id = loc1.customer_id
  JOIN product AS prod2 ON prod2.prod_id = prod1.prod_id
  JOIN product_link AS link2 ON link2.prod_id = prod2.prod_id
  JOIN region AS region2 ON region2.region_id = link2.region_id
  JOIN location AS loc2 ON loc2.loc_id = region2.loc_id
  JOIN customer AS cust2 ON cust2.customer_id = loc2.customer_id
 WHERE prod1.is_source AND
       NOT link1.prod_link_deleted AND
       NOT prod2.is_source AND
       NOT link2.prod_link_deleted AND
       prod1.prod_id = 5874;

Sure, you can remove excessive JOINs.


Is the renaming of a table logged? I can see when a table is created, modified or dropped in the Schema Change History report.  Or by reading the default trace.  But I have not been able to find any record when a table is renamed.  Where might this information be stored?

I did look at modify_date in sys.all_objects.  It returns a row for the renamed table.  My hope was to see some kind of log entry that indicated there was a name change.

The default trace does not capture this information. The rename operation is performed using sp_rename and the default trace captures CREATE, MODIFY, DELETE operations of objects (as in ALTER TABLE, CREATE TABLE, DROP TABLE) which doesn't occur when this procedure is run.

If you want to audit this action, you can create a trace for SQL:BatchCompleted events and filter the TextData looking for sp_rename.

As an FYI, here is the list of events captured by the default trace:

EventID  Event_Description
-------  ----------------------------------------------
18       Audit Server Starts And Stops
20       Audit Login Failed
22       ErrorLog
46       Object:Created
47       Object:Deleted
55       Hash Warning
69       Sort Warnings
79       Missing Column Statistics
80       Missing Join Predicate
81       Server Memory Change
92       Data File Auto Grow
93       Log File Auto Grow
94       Data File Auto Shrink
95       Log File Auto Shrink
102      Audit Database Scope GDR Event
103      Audit Schema Object GDR Event
104      Audit Addlogin Event
105      Audit Login GDR Event
106      Audit Login Change Property Event
108      Audit Add Login to Server Role Event
109      Audit Add DB User Event
110      Audit Add Member to DB Role Event
111      Audit Add Role Event
115      Audit Backup/Restore Event
116      Audit DBCC Event
117      Audit Change Audit Event
152      Audit Change Database Owner
153      Audit Schema Object Take Ownership Event
155      FT:Crawl Started
156      FT:Crawl Stopped
164      Object:Altered
167      Database Mirroring State Change
175      Audit Server Alter Trace Event
218      Plan Guide Unsuccessful


Database Tuning Issues I'm not a DBA so looking for some advice on tuning a MySQL (MyISAM) database. I've found that during peak hours we are getting pretty severe load spikes which are slowing down the web front-end.

I've also used JMeter to run tests against the server, and with 20 concurrent users, load spikes to 400%. This doesn't seem normal.

I've ran MySQL Tuner and the following have been flagged up:

[!!] Query cache prunes per day: 4351
[!!] Joins performed without indexes: 155460
[!!] Table cache hit rate: 0% (1K open / 11M opened)

The query cache prunes seems straight forward, and I think I could safely increase this to 100MB without any negative scalability isuses.

I'm confused by 'joins without indexes' as all my tables are indexed. Is this more likely to be an issue with queries using non-indexed columns?

The table cache hit rate is also something I'm not sure about. The number of Open_tables never decreases below 1000, suggesting the tables never close. 1000 is the value of table_open_cache in my config.

load spikes to 400%

That probably means that you are CPU-bound and not I/O-bound.  And hence, you have plenty of RAM, but the queries are inefficient.

  [!!] Query cache prunes per day: 4351

In most production systems, the write traffic is so high that the Query cache should be turned off.  There are other metrics to further demonstrate that.

  [!!] Joins performed without indexes: 155460

For tiny tables, that does not matter.  For even small tables, that could matter.

Turn on the slowlog with long_query_time = 1; wait a day; run pt-query-digest.  Then show us the first couple of naughty queries, together with SHOW CREATE TABLE and EXPLAIN SELECT ....  That is very likely to include the worst offenders of "non-indexed JOINs".

  [!!] Table cache hit rate: 0% (1K open / 11M opened)

You have more than 1000 tables?  And you are actively using more than 1000?  That sounds like a poor schema design.  Please elaborate on why there are so many tables.  (Meanwhile, increase table_open_cache to, say, 2000 and set table_open_cache_instances to 16 (assuming you are running at least 5.6.6).

Don't go past 100M for query_cache_size.  It gets inefficient as it gets bigger.

In my opinion, log-queries-not-using-indexes is more clutter than benefit.

For a head-start on fixing the indexes, see my cookbook.

If you would like a lot more tests (more than what that tool did), provide me with

RAM size
SHOW VARIABLES;
SHOW GLOBAL STATUS;

It will include an analysis of whether the QC is being useful.


Oracle 10g: same query, different plans on copies of the same DB I have a query joining a number of long tables (primary key in one table, indexed column in another) which results in different plans on different copies of the same database.

The structure of copies including indices is the same and the data is 99+% the same (copies are made within a week from each other so some of them hold a bit more or fewer rows, the difference is totally insignificant for that particular query).

The problem is that on some copies Oracle applies the indices as expected while on another ones there is always a full table scan even if you specify a hint.

What factors can be influencing the plan apart from the data structure itself? For example, are there any dynamic ones such available RAM or anything like that? In other words, what should I check after I make sure that the DB structure of the "good" copy (which uses the indices) is identical to the "bad" one (which uses full scans)?

Or is it nonsense and different plans mean differences in data structure which I simply haven't noticed for some reason?

There are other parameters that can influence execution plan, like PGA size, number of CPUs, disk I/O speed, different sample sizes when you are gathering statistics, index modifications, different system load, different block sizes and so on and on.

Just too many to check them all.

You could try sqlTXplain or sql Health Check Oracle's tools to find the root cause of this problem.

Run it for the same query on both databases and compare parameters that you will get(sqlTXplain compare method).


How to install UTF8 locale on PostgreSQL? I have a PostgreSQL 8.4 on Windows 7 machine, and my issue is that I have only the win1251 locale.

So when I create a database, I choose the UTF8 encoding and can select only lc_collate  = Russian_Russia.Win1251. And when I get any errors when I use JDBC I get unreadable logs.

How can I set a locale like en_US.UTF8 or ru_RU.UTF8?

PostgreSQL 8.4 supports only one locales that is selected in installation time. You need to rerun initdb statement with different locales (but only one can be used) or you can migrate to 9.x version, that supports more locales.


Best Practice for Oracle SGA and PGA Memory Components Size I have a Server that has 128GB RAM. I have read here and there that the size of SGA + PGA can bee 80% of the RAM.
I wonder if there is any best practice for this configuration that could allow Oracle Database to utilize hardware even better?
There is also this consumption that this server will be used only to serve the Oracle database.

What version of Oracle are you using?  Are you actually setting values for the SGA components and/ or the PGA separately?  If so, why?  If you're using a recent version of Oracle, the starting point would normally be to set memory_target and let Oracle size the various memory components automatically.  Unless, of course, you have some reason to need to set minimum values for certain memory components.

As for what to set memory_target to, you just need to leave enough memory for the OS and any other processes that need to run on the machine (including things like any backup manager you might be using, any monitoring software that runs on the server, etc.)  It seems unlikely to me that you'd need 20% of 128 GB for that software.  I'd guess that 8 GB would be more than enough leaving 120 GB for Oracle's memory_target.  You could do better than guessing by taking a look at the actual memory utilization on the server over time.


Is there any special configuration for SQL Server implementing Routing List in a heterogeneous enviroment (Client and Server in different domains)? I had implemented successfully SQL Server Routing List Feature many times... but I have a very special scenario where our Reporting Services Node is located in a different Domain than SQL Server Nodes.

Brief environment description:

Reporting Services/SQL2012 (connection success with other server environments using Application Intent)
Always On Nodes/SQL2014.
There are several security levels between then. Only require port for SQL Server Listener is open.
When I built a Reporting Services DataSource without Application Intent Property, the connection test run with success but as soon I activate ApplicationIntent=ReadOnly;MultiSubnetFailover=True the connection fails throwing the following error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)

Datasource is using SQL Auth, and the user exist in all participant nodes of the availability group.

I dig a little bit and found that adding the FQDN in the Reporting Services node the functionality works just fine!
I also request to Security to check those entries to make sure works fine.

Thanks Sean Gallardy your clue was key for success. Cheers.


data validation Datawarehouse ETL Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following kimball). We have had some incidents of worng wiring.

So I want to write few scripts that will do the data validation by doing the cross lookup and checking if each surrogate keys refer to the right record from appliaction database. I just wanted to ask you guys if you had to deal with any data validation in reports? My solution is not very efficient and very tedious to implement, wanted to check if any easier suggestion is there.
PS: I had this question on stackoverflow, but unfortunately got no response. So trying my luck here.

Usually surrogate keys are implemented based on dimension tables and external data sources in order to have coherent and valid data records; the ETL process, which in charge of extracting data from different sources, transforming and finally delivering data into the data warehouse, must ensure this condition (i.e., surrogate keys refer to the right record).
Personally, I did perform a data validation once by simply using a comparison between the OLAP(i.e., Data Warehouse) result and OLTP(i.e., DB sources) result for the same simple analysis query in order to see if there are any incoherent information.