Group by's in materialized views and query planning In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

Can indexed views still help me in this scenario somehow?
Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

JOYOUS ADDENDUM: Now that I got it working, I successfully tested that it even works in cases where the query contains a left join in those cases where the left join can in fact be optimized away (ie. the on-clause covers a unique index in the joined table).

That is really awesome, because it means that even in cases of a query with left joins one can design the schema in such a way that getting a total row count of everything or specific groupings is fast.

The feature is called 'indexed view matching' and it can be tricky to get right.  There are a number of prerequisites outlined here: Resolving Indexes On Views, plus the indexed view needs to be able to answer the question that your query is asking.  I would expect it to work in Developer/Enterprise Edition for straightforward scenarios, and I got it to work with a simple schema and query:

-- Indexed view matching
USE tempdb
GO

IF OBJECT_ID('dbo.BaseTable') IS NOT NULL DROP TABLE dbo.BaseTable
GO

CREATE TABLE dbo.BaseTable (

    rowId       INT IDENTITY PRIMARY KEY,

    slot        INT NOT NULL,
    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
)
GO

-- Add dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.BaseTable ( slot, someDate, someData )
SELECT
    rn % 999,
    DATEADD( day, rn % 333, '1 Jan 2016' ),
    NEWID()
FROM cte

CHECKPOINT
GO --10

DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

CREATE VIEW dbo.IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;
GO

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);
GO

-- Indexed view matching
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

-- Indexed view not used as it can't 'cover' the query
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.BaseTable
WHERE Slot = @slot
AND rowId = 42
OPTION ( RECOMPILE );
GO

-- Query Indexed view directly with NOEXPAND hint
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView WITH ( NOEXPAND )
WHERE Slot = @slot
OPTION ( RECOMPILE );
GO

-- EXPAND VIEWS hint ensures Indexed view is NOT used.
DECLARE @slot INT = 42

SELECT COUNT(*)
FROM dbo.IndexedView
WHERE Slot = @slot
OPTION ( RECOMPILE, EXPAND VIEWS );
GO

Execution Plan:

There are a few other queries in my test script above and you can see the indexed view is not used for some of them.

Here are some quotes about indexed views from one of the architects of the query optimizer, Conor Cunningham:

  The Query Optimizer contains logic to use this index both in cases when
  the original query text referenced the view explicitly as well as in cases when the user submits a
  query that uses the same components as the view (in any equivalent order).

but ...

  [complexity] ... makes it difficult for the Query Optimizer to consider plans that perform
  the view evaluation first, then process the rest of the query. Arbitrary tree matching is a
  computationally complex problem, and the feature set of views is too large to perform this operation
  efficiently.

'Microsoft SQL Server 2008 Internals',
Chapter 8: The Query Optimizer - Conor Cunningham

  ...indexed views work for basic SPJG (SELECT-PROJECT-JOIN-GROUPBY) queries.  The joins must be inner joins, not self joins, and join on a key.   Each operator has a series of restrictions...

source

I think those quotes are quite telling.  You may also wish to consider filtered indexes.


How to change all connections to SQL Server without investigating each hard-coded connection to serverName? We've got a server upgrade scheduled for the near-future and we're looking to move to a new server. Our main issue is that we cannot have two servers with the same name on our domain. So is there a command which corrects all of my connections to '08Server1' from '08Server2' to instead use '16Server1' from '16Server2'?

All our applications also reference '08Serverx' - other than a mass find&replace - is there a better method available?

For our system we set up a DNS Alias and have all the connection strings point at that  i.e. sqlSERVERAlias\Database where SQLSERVERAlias points at 08Serverx or 08Servery

Then all you've got to do is change the Alias when you change the servers


Query that ranks students order by marks and give the position of student even when there are students with equal marks I need an sql that will rank students order by marks scored in a specific examtyp e.g CAT1 and give exact position even if the students have equal marks e.g in this sample data admNo 2525 and admNo 4093 scored equal marks in CAT1, so i need an sql that will fetch the positions as the same and skip give the next student number 3. the following sql that i tried does not consider a scenario like this.

$sql = "SELECT q1.rownum
FROM
    (
        SELECT *, @rownum:=@rownum + 1 AS rownum
        FROM $table t, (SELECT @rownum:=0) r
        WHERE examtyp =? AND termId=? AND year=?
        ORDER BY points DESC
    ) q1
WHERE q1.admNo=$admNo";

resid  admNo streamIdexamtyptermIdmarks gradepointyear
1       2129    0   CAT1    1   525     C   62  2013
2       4093    0   CAT1    1   569     B+  69  2013
3       2129    0   CAT2    1   550     B+  67  2013
4       4093    0   CAT2    1   556     B+  68  2013
6       2129    0   FINAL   1   559     B+  68  2013
7       2129    0   AVERAGE 1   545     B   66  2013
7       4093    0   FINAL   1   581     B+  70  2013
8       4093    0   AVERAGE 1   569     B+  69  2013
9       2525    9   CAT1    1   569     B+  69  2013

SELECT t1.id, mark,
(SELECT COUNT(*) FROM marktable t2 WHERE t2.mark > t1.mark) +1
AS rnk
FROM marktable t1 order by rnk;

SQLFIDDLE


Postgresql tries to connect to fe80::1, permission denied I upgraded a couple of days ago to IPv6 on my local test machine and since then I've been experiencing some issue with connecting to my local PostgreSQL server.
I set the host to 'localhost', which worked fine using IPv4. While using IPv6, there is a 1:150 chance that the connection fails (here is a testcase for all unix systems, and here is another one for all other systems (requires psycopg2 and python3)).
As it turns out it is because the loopback interface (lo0) is configured like this:

lo0: flags=8049<UP,LOOPBACK,RUNNING,MULTICAST> mtu 16384
    options=3<RXCSUM,TXCSUM>
    inet6 ::1 prefixlen 128
    inet 127.0.0.1 netmask 0xff000000
    inet6 fe80::1%lo0 prefixlen 64 scopeid 0x1  # line 5
    nd6 options=1<PERFORMNUD>

Line 5 is especially interesting. It says use IP address fe80::1 for connections (and which is actually used about every 150th time).
Is it safe to allow all connections (without password) from fe80::1 or did my PC just happen to be number one on the local network? Can my IP change when I'm on another network (I used to 10.0.0.1 on IPv4 but that changed occasionally depending on where I am).
If other computers can also use fe80::1, how can I prevent postgresql from connecting to my server using that address? If I change the host to "::1", will postgresql even work on networks (routers) that don't support IPv6 / haven't IPv6 enabled? Or do I need to use IPv4 (i.e. 127.0.0.1) for 100% stable connection?

Additional tags: IPv6, loopback
(Sorry I can't create tags on my own, yet).

So this is almost certainly a bad configuration in ip address resolution.  As you mention, you have a line resolving localhost to your external ipv6 address.  Yes, this should be removed, or changed from fe80::1 to ::1


Optimization paramters read-only mysql 5.7 database? What's the best way to configure a MySQL database that is only read-only, and will never have any modifications made to it?

1) I have increased my innodb_buffer_pool_size to 70% of available RAM

2) Increase query cache size to 40mb

Running against a load generator, after some time the database starts bottlenecking to only 1000 statements per second.

The configuration is 2-cores, 8GB ram 50GB SSD. The database contains a single InnoDB 13 GB table with the same exact query running on two indexed columns.

We are using a vertx server to hit the database. The symptom we see is that the web service performance degrades after 15-20 minutes of heavy load.

Where can the bottleneck be? What parameters should we optimize?

I can think of several things that might help.  But I can't be specific without seeing that query, plus SHOW CREATE TABLE.

If we can shrink the table/index size, there might be less I/O.  If the index involves a UUID, then I will have lots to say.

Sometimes rearranging the index(es) helps.

If you are not using InnoDB, we need to discuss that.

Let's see SHOW GLOBAL STATUS; and SHOW VARIABLES; -- there are some metrics that will say how well the Query cache is doing, info about connections, whether the buffer_pool is adequately big, plus other things that might give clues.

In general, "you can't tune your way out of a performance problem."

For "readonly", add this to the config file:

read_only = ON

but I doubt if it will impact the performance.


Allowing end users to kill their own queries I am an end user (programmer/analyst) without permissions to kill my own queries in our datawarehouse.  This causes problems when I need one killed (like right now - 5:30 on a Friday afternoon) and there is no-one around to do it for me.

I'm looking for a solution that would allow me to see a list of my currently running queries and then specify a query to kill.  I would only have permission to kill my own queries and no others.

All of my queries are read only - I have no permissions to write to the DB.  We access the DB via ODBC from various 3rd party apps, and sometimes from MS SQL Svr Mgmt Studio. Not 100% sure of the server version, but let me know if it's necessary and I can find out.

Is this possible?  If so, what downsides exist.  Ideally I would like to be able to sell the idea to the DBA team and have them implement it.

Your DBAs can grant you elevated permissions so that you can do this.  Normally this wouldn't be needed however.  If you are in SQL Server Management Studio you can simply click on the stop button and 99% of the time the query will simply stop (the amount of time that it takes to stop will depend on the amount of data which has been changed by the query as the transaction will need to be rolled back).  Failing that you could kill the application on your side which would then terminate the query on the SQL Server when the application is closed.


PostgreSQL - PostGIS ST_SetValues - int array cast to double and back I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][].

If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to integer?

Or is there some limited precission for integer part of the double. I want to store "packed" RGBA into a single 32bit integer there and "unpack" single color channels after output in my app.

As from documentation, double should be 64bit, so in my oppinion, this should be possible, but maybe I am missing something.

You're dong it wrong. I assume you're wanting a 32bit integer so you can bit shift around for RGBA (8bits per channel.) What you really want is a raster with 4 channels, 8 bits each. Because life will be easier.

ST_MakeEmptyRaster
ST_AddBand

Adapted from official docs...

SELECT
    *
FROM ST_BandMetadata(
    ST_AddBand(
        ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0),
        ARRAY[
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0),
            ROW(NULL, '8BUI', 255, 0)
        ]::addbandarg[]
    ),
    ARRAY[]::integer[]
);


Speeding up Postgresql manual index reset? I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records).

To solve this, I run the following (as pseudocode):

last=1
for db in databases:
   ALTER TABLE table1 DROP CONSTRAINT table1_pkey
   CREATE SEQUENCE temp_seq START last
   UPDATE table1 SET table1_pk = nextval('temp_seq')
   ALTER TABLE table1 ADD PRIMARY KEY table1_pk
   last = nextval('temp_seq')
   DROP SEQUENCE temp_seq

This goes through all the databases and resets their indices starting from 1. First database has indices 1-50, second 51-125, third 126-223 and so on.. After this I dump and restore them to one database and there is no overlap anymore.

Everything works well, other than the index reset part is extremely slow with bigger databases (many GBs of data). I found out that postgres is running VACUUM ANALYZE after every UPDATE table1 SET table1_pk = nextval('temp_seq'). This makes everything even slower than before. According to my understanding, this is done to prevent transaction ID wraparound? From the PostgreSQL docs: https://www.postgresql.org/docs/9.3/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

  PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers: a row version with an insertion XID greater than the current transaction's XID is "in the future" and should not be visible to the current transaction. But since transaction IDs have limited size (32 bits) a cluster that runs for a long time (more than 4 billion transactions) would suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future — which means their output become invisible. In short, catastrophic data loss. (Actually the data is still there, but that's cold comfort if you cannot get at it.) To avoid this, it is necessary to vacuum every table in every database at least once every two billion transactions.

Is this the correct reason? Is there a faster way to do primary key reset? Or to tune the vacuuming parameters without any data loss?

Are these databases in the same Postgres Cluster?

If so, you might consider a Foreign Data Wrapper, to create a "connection" to each source database and insert the data directly into the new table:

CREATE EXTENSION POSTGRES_FDW ;

CREATE SERVER foreign_db
FOREIGN DATA WRAPPER POSTGRES_FDW
OPTIONS
( HOST       'host_name'
, DBNAME     'database_name'
, UPDATABLE  'false'
);

CREATE USER MAPPING FOR fred
SERVER foreign_db
OPTIONS ( USER 'fred', PASSWORD 'password' );

CREATE FOREIGN TABLE foreign_old_table
(
   ...
)
SERVER foreign_db
OPTIONS ( TABLE_NAME 'old_table' );

Then; finally

INSERT INTO new_table ( everything, except, pk )
SELECT ( everything, except, id )
FROM foreign_old_table ;


Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects' I was trialing Apex source control - trial ended. Uninstalled Apex (well I thought I did). Today I deleted the Apex database. Now I get

Msg 208, Level 16, State 1, Procedure ApexSQL_SourceControl_DDLTrigger, Line 72 [Batch Start Line 7]
Invalid object name 'ApexSQL.ApexSQL_SourceControl.Objects'.

Can not find any triggers at all on the server - how can I get rid of this error. Won't let me alter any procedures.

Found it  - under linked servers triggers.


SQL Server Index Scan when using 'OR' operator We implemented a Google-style search where SQL queries are run after a debounce is triggered from the front-end. (We know SQL is probably the wrong technology for this, but I'm knee-deep in startup-chaos here.) The query:
SELECT
    TOP(50) [Name], [Surname]
FROM
    [dbo].[Clients]
WHERE
    [Name] LIKE @SearchTerm + '%' OR
    [Surname] LIKE @SearchTerm + '%'

It is a sizable table, so I added two non-clustered indexes on both columns to help speed things up:
CREATE NONCLUSTERED INDEX [IX_Patients_Name] ON [dbo].[Clients]
(
    [Name] ASC
)
INCLUDE([Surname]);

CREATE NONCLUSTERED INDEX [IX_Patients_Surname] ON [dbo].[Clients]
(
    [Surname] ASC
)
INCLUDE([Name]);

My thinking was that SQL would do an index seek on both columns, but it seems the query optimizer decides to use an index scan.

This might not be a real issue for this simple use-case, but we have more complex versions of this with multiple joins, etc.
Is there any way to optimize this query to uses seeks?

As you mentioned in your question, this kind of Google-style query isn't really what SQL Server is "good at."  Erik Darling talked about this exact query anti-pattern in his post The Only Thing Worse Than Optional Parameters….
All that aside.
It's possible to naturally get a seek with that type of query, but it's much more common to get the scan as you noticed.  Here's an example from the StackOverflow2010 sample database.
First I'll create these two helpful indexes:
CREATE NONCLUSTERED INDEX IX_DisplayName ON dbo.Users (DisplayName) INCLUDE ([Location]);
CREATE NONCLUSTERED INDEX IX_Location ON dbo.Users ([Location]) INCLUDE (DisplayName);
GO

Then I'll create a procedure similar to the one you have:
CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName,
        [Location]
    FROM
        dbo.Users
    WHERE
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

If I run that procedure with a fairly selective parameter, I'll end up with an index union plan.  If the parameter is less selective, a scan of one of the covering indexes is used instead.
DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'Josh';
GO
DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'S';
GO

Execution plans are here.
Note that this is true even if you write this as separate UNION queries directly.
One way to reliably get the index union plan, as mentioned in the linked post, is to add a FORCESEEK hint to the table you'd like to union on.
If I change the proc to this, I don't get scans on either plan:
CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName,
        [Location]
    FROM
        dbo.Users WITH (FORCESEEK)
    WHERE
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

The bigger issue with the query, as simplified in the question anyway, is that you are using TOP without an ORDER BY, which is likely to produce drastically different search results depending on which index is used.  Make sure your real query has an ORDER BY, or that this problem is accounted for in some way.


GROUP BY uninterrupted sequence of logs for same location I'm having a hard time describing my problem or finding it via Google.

I want to be able to group rows based on location and employee, but the group should be split if a row with the same location occurs.

Basically a way to determine shifts. If another employee logs a row at the same location then the last created_at should be the end date of the previous employee.

Starting from this (employee_logs):

location | employee | created_at
---------+----------+--------------------
A        | Bart     | 2015-09-01 09:00:00
B        | Homer    | 2015-09-01 09:05:00
A        | Bart     | 2015-09-01 09:30:00
A        | Bart     | 2015-09-01 10:00:00
A        | Homer    | 2015-09-01 10:05:00
A        | Homer    | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00
A        | Bart     | 2015-09-01 14:00:00

The desired result is (shifts):

location | employee | start               | end
---------+----------+---------------------+---------------------
A        | Bart     | 2015-09-01 09:00:00 | 2015-09-01 10:00:00
B        | Homer    | 2015-09-01 09:05:00 | 2015-09-01 09:05:00
A        | Homer    | 2015-09-01 10:05:00 | 2015-09-01 11:05:00
A        | Bart     | 2015-09-01 12:00:00 | 2015-09-01 14:00:00

The hard part is that the sequence should be interrupted when another person creates a log at the same location. I can't seem to solve this with the usual techniques.

SELECT location, employee, min(created_at) AS start_ts, max(created_at) AS end_ts
FROM  (
   SELECT location, employee, created_at
        , row_number() OVER (PARTITION BY location ORDER BY created_at)
        - row_number() OVER (PARTITION BY location, employee ORDER BY created_at) AS grp
   FROM   employee_logs
   ) sub
GROUP  BY location, employee, grp
ORDER  BY min(created_at), max(created_at);

This interrupts a sequence when a different employee logs at the same location. It does not interrupt when the same employee logs at a different location (which might also make sense, but you didn't mention that).

In the subquery, have an running number over all rows for the same location and subtract the running number over rows for the same location with the same employee. The result grp is a number without meaning, that denotes each group of rows forming a sequence together.

Aggregate in the outer SELECT. It is important to remember that grp is only unique per (location, employee), so you have to group by all three columns.

SQL Fiddle.

Related, with ample explanation:

Select longest continuous sequence


How do I change where database diagram relationship lines attach in MySQL Workbench? Sometimes, MySQL Workbench makes some strange choices about where table relationship lines get attached.  For example, two vertical lines would be better in the following:

For a while, these boxes (representing tables) had two vertical lines between them, and the crossover happened when other boxes were added elsewhere in the diagram, and things got moved around a bit.

Is there a way to say something like "this line should go to this attachment point"?

No, it's not possible to specify attachment points. That layout algorithm has a few flaws.


Recreate partition in mysql I have a table having datetime column partitioned by RANGE

  (DAYOFYEAR(datetime_col))

By this I want to drop oldest of 4th week partition data and recreate immediately for next year similar week inserts.

I.E. I use Mysql 5.6.19-67.0, the partitions are by week

(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (71) ENGINE = InnoDB,
...
....
 PARTITION p49 VALUES LESS THAN (344) ENGINE = InnoDB,
 PARTITION p50 VALUES LESS THAN (351) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Now I want to retain the structure by removing 4th week prior DATA ONLY . But I want to see the structure there WITHOUT data for that partition.

  1st Option: alter table t1 truncate partition p4

  2nd Option: Drop the partition and add partition to recreate it.

Issue in 1st Option: I'm afraid to issue this, as this doesn't drop the partition and recreate it. It goes for delete from partition where datetime_col < '2013-11-12 00:00:00'. Not sure how much CPU or undo buffers this will create in a 500G partition table.

Issue in 2nd Option: No issues, but it just won't work.

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

Anyother recommendations are most welcome!!

Running TRUNCATE PARTITION works fine

DROP DATABASE IF EXISTS partest;
CREATE DATABASE partest;
USE partest
CREATE TABLE rolando (
  id int not null auto_increment,
  LogDate DATE,
  PRIMARY KEY (id,LogDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
ALTER TABLE rolando TRUNCATE PARTITION p4;

Here is the output

mysql> DROP DATABASE IF EXISTS partest;
Query OK, 1 row affected (1.55 sec)

mysql> CREATE DATABASE partest;
Query OK, 1 row affected (0.00 sec)

mysql> USE partest
Database changed
mysql> CREATE TABLE rolando (
    ->   id int not null auto_increment,
    ->   LogDate DATE,
    ->   PRIMARY KEY (id,LogDate)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
    -> (PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
    ->  PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
    ->  PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
    ->  PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
    ->  PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
    ->  PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
    ->  PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
    ->  PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
    ->  PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
    ->  PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
    ->  PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (2.60 sec)

mysql> ALTER TABLE rolando TRUNCATE PARTITION p4;
Query OK, 0 rows affected (0.25 sec)

mysql>

OK, fine. It works. However, doing this is logically unsafe. Why ? The partitioned table is based on DAYOFYEAR(datetime_col)). Since each partition contains a week for multiple years, running alter table t1 truncate partition p4; would wipe out week 4 from multiple years.

OK, let's try dropping and adding the partition

mysql> ALTER TABLE rolando DROP PARTITION p4;
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE rolando ADD PARTITION
    -> (PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>

This makes sense since MAXVALUE already exists in the definition. This prevents you from adding (appending) a partition if MAXVALUE is present.

While you could play games with ALTER TABLE ... REORGANIZE PARTITION, the complexity isn't worth it and you won't achieve the result of wedging in a partition.

Rather that explanining further partition machinations, just do the following

Make sure an index exists on the date field
Perform the DELETE SQL already mentioned

An alternative would be to create a table with a weekly partition then drop the exact week.


Mysql MyIsam tables get corrupted I am facing the myisam tables get Corrupted frequently but I don't know exact root-cause for this problem. However I will do Repair/MyIsamchk --auto-repair whenever the table gets corrupted. Please see the below actions,

mysql> check table tableName;
+--------------------+-------+----------+----------------------------------------------------------+
| Table              | Op    | Msg_type | Msg_text                                                 |
+--------------------+-------+----------+----------------------------------------------------------+
| database.tableName | check | warning  | 2 clients are using or haven't closed the table properly |
| database.tableName | check | warning  | Size of datafile is: 13540475       Should be: 13540450  |
| database.tableName | check | error    | Found 541619 keys of 541618                              |
| database.tableName | check | error    | Corrupt                                                  |
+--------------------+-------+----------+----------------------------------------------------------+
4 rows in set (3.83 sec)

mysql> repair table tableName;
+--------------------+--------+----------+----------------------------------------------+
| Table              | Op     | Msg_type | Msg_text                                     |
+--------------------+--------+----------+----------------------------------------------+
| database.tableName | repair | warning  | Number of rows changed from 541618 to 541619 |
| database.tableName | repair | status   | OK                                           |
+--------------------+--------+----------+----------------------------------------------+
2 rows in set (15.28 sec)

Server configurations are,

CentOS - 64 Bit (Linux - ext3)
RAM - 2GB
Hard disk - 79GB (Please see the disk free space details below)

Shell# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       79G   57G   18G  76% /
tmpfs          1000M     0 1000M   0% /dev/shm

There will be free space is available on the Hard disk but why the table gets corrupted? and also kindly provide the solution to prevent this problem?

Thanks in advance.

MyISAM indexes tend to get corrupted when the server crashes (eg, power failure) or is otherwise rudely terminated (kill -9).  You did the 'right' thing by running CHECK and REPAIR.

The datafile size is a bit worrisome; that is worse than the usual (just index issue).  The crash may have happened between "writing" and "flushing", since MyISAM is not careful about syncing.

Changing to InnoDB is the 'right' thing to do.  For each of your tables, do

ALTER TABLE table_name ENGINE=InnoDB;

Caution:  The disk footprint for your tables will double or triple; check that you have enough disk space.

InnoDB automatically recovers after power failure and virtually any other crash.


What does it mean when a table is listed with fragmentation? With this query I can list index fragmentation:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

But sometimes, it's listed like this:

What does this mean? is the table fragmented?

And, while I'm here, I would like to ask another question.

I'm using a job to reorganize indexes. All of them are just being reorganized, because this database is running at full speed 24/7 and I can't use rebuild because of locks.

What is the best way to use rebuild\reorganize in this situation? is Hallengren a better solution in this case? if im not mistaken, if the index can't be locked, it will just reorganize it.

Thanks

Dont try to reinvent the wheel as there is a cost to reinventing it.

Use Ola's maintenance scripts - they are tested well enough and follow best practice. You can even use LockTimeout in Ola's solution.

I would not worry on the fragmentation levels where the page count is less than 5K.

  What is the best way to use rebuild\reorganize in this situation?

Do the index maintenance during less activity or maintenance time.


Multidimensional aggregates Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is simple to keep the aggregate-table up to date (let's assume an aggregate like SUM()).

However, I am wondering if there is a method to keep/cache aggregate data, over different attributes from the original table.

For example.

games(username text, win bool, t timestamp);

--- attribute, grouping
aggregate_games(wins int, username text);

SELECT * FROM aggregate_games
WHERE username=<...>

But it gets more complicated when I want to keep the aggregate data for many different possible attributes. For example.

games(win bool, username text, map text, revision int, mode int, team int, server inet);

--- attribute, group-combinations
aggregate_games(wins int, username text, map text, revision int, mode int, team int, server inet);

Ultimately, what I want to be able to do is something like:

SELECT sum(wins)
WHERE username={*,{username}}
  AND map={*,{mapname}}
  AND revision={*,{revision}}
  AND mode={*,{mode}}
  AND team={*,{team}}
  AND server={*,{server}}

Is there a good way to do this without:

re-aggregating all the data
without keeping an insane unscalable number of combinations of aggregate data around

To extend it a bit further, I want to actually keep the aggregate data itself over time, so essentially my aggregate data would have a timestamp (and so, for example, it could be graphed over time).

MS Analysis Services (Multidimensional)'s [almost] whole reason for being is to abstract away aggregations so you can simply query tour OLAP data without having to worry about how the aggregate value is being retrieved.  In fact, in Analysis Services Multidimensional, you point and click to create your aggregation combinations.

I'm not sure what database server you're running, but if you're running SQL Server 2014 you also have the ability to build a clustered columnstore index on your fact table.  Doing this will, in a most peoples' cases, essentially eliminate the need for you to perform aggregations entirely.  I'd definitely look into that as an option if you're on SQL Server and your data and resources are a good fit.

In that similar vein, if you're not running SQL Server, there are a lot of columnar databases out there which service OLAP workloads quite well, such as Tableau ($$$).  I'm not sure what the open-source space has to offer, but that's worth checking out as well.


Some queries slower in MySQL 5.5 than 5.0? We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran.

We recently updated one of our slaves to MySQL 5.5, from MySQL 5.0, as a test run before we do it on our master. We're still running everything in MyISAM because we needed it up and running before attempting to do InnoDB optimization. The thing seems to be working fast enough, except for this process. The other slave, with identical hardware, finished it virtually in real time; this slave, however, is running over 2 hours behind because of it.

I'm curious, does anyone know if this type of thing (tons of tiny, simple updates) is slower in MySQL 5.5 for MyISAM in general? The settings between the two servers are as close as settings between a 5.0 and 5.5 can be, so it doesn't necessarily appear to be configuration. So while I can fix it by not having the horrible process, I'm worried that this is indicative of a deeper problem with our server. Should I be, or is it all kosher?

Edit: The problem appears to only be with large numbers of small queries. I just ran one and it was in the "query end" state for .133 seconds, or 89% of the query time. Only being able to run ten queries per second on a single thread seems pretty bad. Does 5.5 change things such that I need to modify my cnf file?

Disabling the binary log on the slave fixed it. This makes me worry about when we upgrade the master to 5.5, but for now things are working much much better.

Edit: The issue was sync_binlog; now that it's set to zero, everything's happy.


How to change rows to columns in mysql? I have data as following(it is just an sample, I have 100+ different types.):

advertiser_id activity_type counts
1               1            2
1               2            5
2               1            3
2               3            9

I would like to change it into

advertiser_id activity_type1 activity_type2 activity_type3
1              2                 5           0
2              3                 0           9

I wrote code as

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(`activity_type` = ', activity_type, ',counts,NULL)) AS counts', activity_type)) INTO @sql FROM ceciliadb.tbltmp_act
;

SET @sql = CONCAT('SELECT advertiser_id, ', @sql, 'FROM ceciliadb.tbltmp_act GROUP BY advertiser_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But there is error as

mysql> PREPARE stmt FROM @sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ceciliadb.tbltmp_act GROUP BY advertiser_id' at line 1

Is there anyone can help?

As for your original question, you need an extra space before FROM in the line before your PREPARE

SET @sql = CONCAT('SELECT advertiser_id, ', @sql, ' FROM ceciliadb.tbltmp_act GROUP BY advertiser_id');

SUGGESTION

At first glance, the query you need generated looks like this

SELECT
    advertiser_id,
    IF(activity_type=1,counts,0) activity_type1,
    IF(activity_type=2,counts,0) activity_type2,
    IF(activity_type=3,counts,0) activity_type3
FROM ceciliadb.tbl.tmp_act
GROUP BY advertiser_id;

I would craft it like this:

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since you have 100 types, you nned to wedge those 100 type IDs in the subquery

SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

Since the generated query may be too long, set the GROUP_CONCAT length to 10 MB beforehand

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT 1 num UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 100) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;

If your columns are not sequential in nature, then use the actual values as headers

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(CONCAT('IF(activity_type=',num,',counts,0) activity_type',num)) cmd
INTO @column_list FROM (SELECT DISTINCT activity_type FROM ceciliadb.tbl.tmp_act) A;
SET @sql = CONCAT('SELECT advertiser_id,',@column_list,' FROM ceciliadb.tbl.tmp_act');
SET @sql = CONCAT(@sql,' GROUP BY advertiser_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPEARE s;


Restore ALL databases with sqlcmd in SQL Server (Express, Docker, 2017) These documentation articles

https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-2017

and

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database?view=sql-server-2017

Tell us how to restore individual databases. I want to restore all databases in the file list. Is there a way to do this programmatically from within the sqlcmd without involving bash?

Based on what you want I would do the following:

Create two files - List.txt (with the list of DBs you want to
restore) and DBRestore.bat with sqlcmd command to run a stored
procedure 'DBRestore' inside SQL Server
Create a stored procedure 'DBRestore' which loads into SQL Server temp table the list from the LIST.txt and processes it - reads DB name one by one and restores them


How are Postgres database files updated? I'm curious how the /base files are updated for a Postgres database. Do the segments correspond to segments of each table, so that only a few segment files are updated after an update to subset of the table? Or will the update to the table cause changes to all or a majority of the segment files?

Basically, I'm interested in the reliability of these updates to the /base files for backup purposes. Given the usage of WAL for PITR, I have a feeling that the files are not updated in any coherent way for my purposes.

According to the documentation:

  When a table or index exceeds 1 GB, it is divided into gigabyte-sized
  segments. The first segment's file name is the same as the filenode;
  subsequent segments are named filenode.1, filenode.2, etc. This
  arrangement avoids problems on platforms that have file size
  limitations. (Actually, 1 GB is just the default segment size. The
  segment size can be adjusted using the configuration option
  --with-segsize when building PostgreSQL.) In principle, free space map and visibility map forks could require multiple segments as well,
  though this is unlikely to happen in practice.

The linked documentation page says:

  Each table and index is stored in a separate file. […]
  each table and index has a free space map … stored in a file named with the filenode number plus the suffix _fsm. Tables also have a visibility map, stored in a fork with the suffix _vm

So the actual table data ends up in a single file, or in multiple segments all belonging to the same 'virtual' file.

However, a table update can also affect indexes, or statistical information in the system tables.

In any case, the WAL tracks all changes to all tables, and at the end of each transaction, all files are in a coherent state.


Innodb full table Scan mytable is an InnoDB table with (a, b) as the primary key.

Does this query check all table?

Select *
from    mytable
where   ( b='2' and c='3')

I want to find out if 'cluster' structure helps this query not check all table. (when I don't have all part of primary key in my where clause)

Would it be faster if I used MyISAM and a secondary index? Because In full table scan, It seems that Innodb caches a page for every record and so its not fast.
I've read about designing composite primary keys in an  article:
".....For the sake of example let’s assume two rows were added for a user whose user_id is 50 and they have been assigned with id=10000000 and id=10001000, where id is the primary key in this table. What sort of work the following query has to perform in order to return results?

SELECT * FROM messages WHERE user_id = 50

It finds index entires where user_id is 50. With the primary key values from the secondary index, it can then locate the physical rows. So InnoDB first seeks a row where id is 10000000. But since it does not operate on individual rows at this point, but rather on the blocks of records called pages, it locates the proper page on disk and caches it in the buffer pool. Only then it can extract the actual row, return it, and move on to the next one where id is 10001000. Since the distance between the two records is significant, it turns out that it is not on the same page that was just pulled into the cache and which holds the first row. InnoDB has to go back to disk to fetch another page, cache it, extract the row and return it.

In the above example at least two I/Os were issued. In a pessimistic case for a few hundred matching rows, when a user has that many messages, that could result in a few hundred I/O operations. Random I/O. Assuming as little as a few milliseconds for each operation, the query may need as much as one second to execute. It would use index properly, it would only pull a few hundred rows, but would still perform poorly. With the exception for flash technology any typical storage is rather slow, and also very difficult to scale, so such scenario should be avoided. ......"

It will do a full table scan.

The columns in an index are ordered.  Think about finding a person in a list of people where the list is sorted by last name, but all you know is the first name.

PRIMARY KEY(b,a)

would be somewhat good for WHERE b=1 AND c=2 -- it could at least narrow it down to all the rows where b=1.  That is, it would do a "range scan" of only part of the table.

INDEX(b,c)

would (probably) work better (given that you leave the PK at (a,b)).  This because it would drill down that secondary index's BTree to exactly the row(s) with (1,2), then reach over into Data+PK to find the rest of the columns needed.

No, don't even consider MyISAM these days.  There are more drawbacks than this one advantage -- if it even is an advantage.

See Cookbook for Creating Indexes.

More

The "left" part (b,a in this example) of the index can be directly used.

PRIMARY KEY(b,a,c,d) would benefit where b=1 and a=2 and d=3, but only because of b and a.  The BTree (actually a B+Tree) can efficiently be scanned.  The code would drill down to where b=1 and a=2 starts, then scan forward until b=1 and a=2 no longer applies.  c is in the way of making use of d=3, though it will be checked.

An InnoDB BTree are composed of 16KB block(s).  If you have only a few rows, every thing will be in one block.  If you have a billion rows, there may be many blocks for the b=1 entries, and the BTree may be about 5 physical levels deep of nodes pointing to sub-nodes.  The B+Tree means that it can scan one leaf node, then step right to the next leaf node.  A rule of thumb:  a block has 100 entries in it.  (In reality, it is anywhere from 1 to at least 1000, depending on a lot of things.)

One thing to remember in dealing with a MySQL PRIMARY KEY -- it is UNIQUE.  So don't randomly add/subtract columns from the definition; you will change what it is testing for uniqueness.


Mysql columns mutual exclusion I want to create a table which has 2 columns that would only allow one of them to have value in the same row.

I tried with CHECK constraint but it not working correctly

Ex: SQL Fiddle schema example

thanks in advance

The CHECK clause is parsed but ignored by all storage engines.

http://dev.mysql.com/doc/refman/5.7/en/create-table.html
Use trigger instead.


How fast does SQL Server start moving data if you add another Rows Data File? Our sql server migration was delayed (naturally) and it is going to run out of space before the new cut over date. In order to keep the database up, I need to give it another database file to start writing to.

I've got space allocated and ready to go, but when I create it, it SQL Server going to immediately start moving data over to the new file?

Question: Would I be better off creating two new files, then running

    dbcc shrinkfile (livendb, emptyfile)

on the old one? Ensuring they become equal right away?

SQL Server uses a proportional fill algorithm when determining where to write data.

If you have one file that is 90% full, and add a similarly sized new, empty file to the same filegroup, the new file will get approximately 90% of the writes (because it is nearly empty, and the existing file is 90% full).

Set the new file to the same size as the existing file (naturally, this would be on a different drive, if the existing drive is out of space).  Preallocating a good amount of file space is required for the proportional fill algorithm to actually write most of it's data to the new file.

Once you have the filegroups configured the way you want them, you can re-balance the data between the files by rebuilding tables and indexes, ensuring you specify the correct filegroup in the ON .... clause for each table and index being rebuilt.

As an aside, get in the habit of always specifying the maximum file size, and managing that purposefully. Setup alerts in SQL Server Agent so you get alerted as files fill up.  When you're thinking about maximum file sizes, evaluate a sensible file growth increment; one that balances time required to grow the file against the desire to not be continuously growing files.  Currently, if I think a filegroup needs 10 GB of disk, and I decide to split that into two files of 5GB each, I'd probably set the initial file size at 3GB for each file, with filegrowth increment set to 250MB or 500MB, and I'd set the maximum file size for each file at, say, 6GB just to give a bit of leeway.   Then I'd setup an event to warn when any file goes over 80% full, to give you time to provision more disk space.  My answer here shows an example of setting up an alert to monitor log file growth.


Azure SQL Server External Data Sources Let me preface this what what I'd like to accomplish and see if this is inline with the expectation of using Azure SQL DB.

Currently I use a couple Virtual Machines in Azure that use a linked server to execute some stored procedures to hit a database outside of Azure (the external database is in AWS)

I'd like to move to use the Azure SQL Server to query an external database but I'm not seeing documentation around setting up an external data source that's just a SQL DB - only elastic within your own Azure network. Is this outside the scope of the product?

Currently Azure SQL Database only allows to query other Azure SQL Databases or Azure SQL Data Warehouse databases by using elastic queries. Linked servers are not supported on Azure SQL Database. Currently is not possible to query SQL Server instances or other RDBMS from Azure SQL Database.

The new PaaS product named Azure Managed Instance allows to create linked servers but for a limited number of targets. Supported targets: SQL Server and SQL Database. Not supported targets: files, Analysis Services, and other RDBMS. For more information, please read this blog.


Oracle Exadata datafile distribution I'm new to Oracle and curious of how Oracle Exadata handles database files. Do we have to assign the disk to a database file as we do with Microsoft or is this handled by Exadata?

I do not know what you mean by the "Microsoft" way, but Exadata uses ASM.

Administering Oracle ASM Disk Groups on Oracle Exadata Storage Servers

http://www.oracle.com/technetwork/database/availability/maa-exadata-upgrade-asm-2339100.pdf

ASM is not specific to Exadata or any hardware, you can use it on any supported platform, even on Windows, so you handle database files just as in any other environment with ASM.


Nested SQL Query (Query a subset of data) Assume I have the following structure...

Structures
- ID
- Name

Fields
- ID
- StructureID
- Key

These table hold simplistic 'structures' (Think very simplistic tables).

There is then an additional table

DataValues
- ID
- FieldID
- Value
- RowID

Example data inside of the table...

ID | FieldID | Value             | RowID
1  | 5       | Blog Post Title   | 1
2  | 6       | Blog Post Body    | 1
3  | 5       | Another Post H1   | 2
4  | 6       | Another Body      | 2

Now I want the ability to do dynamic queries to each data set, so grab all the RowID's that follow a certain pattern.

SELECT DV.RowID FROM DataValues as DV
    INNER JOIN Fields as F ON DV.FieldID = F.ID
    INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')

The issue is then when we want to do multiple conditions

SELECT DV.RowID FROM DataValues as DV
    INNER JOIN Fields as F ON DV.FieldID = F.ID
    INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')
AND (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')

This obviously wont work, is there a way to query a subset of the data?

If I understand what you mean correctly (and I'm not sure I do), you should return 2 rows per RowID for a 'matching' result.

Therefore you can do an OR on the key data you are searching for, GROUP BY RowID and count that there are matching results for each subset.

SELECT
  DV.RowID,
  MIN(CASE
        WHEN F.[Key] = 'Title' THEN 1
        WHEN F.[Key] = 'Body' THEN 2
      END
    ) AS Ordinal
FROM DataValues as DV,
INNER JOIN Fields as F ON DV.FieldID = F.ID
INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
AND (
     (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')
OR   (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')
    )
GROUP BY DV.RowID
HAVING COUNT(*) = 2
ORDER BY Ordinal ASC;

Alternatively, you could do an INTERSECT of the two result sets for all of the RowID values that satisfy both conditions:

SELECT DV.RowID, 1 as Ordinal
FROM DataValues as DV
INNER JOIN Fields as F ON DV.FieldID = F.ID
INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
  AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')
INTERSECT
SELECT DV.RowID, 2 AS Ordinal
FROM DataValues as DV
INNER JOIN Fields as F ON DV.FieldID = F.ID
INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
  AND (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')
ORDER BY Ordinal ASC;


Optimize Row Level Security expression in Postgres I have a table which contains millions of rows, categorized into a few categories (lets say numbers 1-5). An application that accesses the database uses one single database account. However, the application has its own user accounts and each app user is allowed to access only certain categories. Therefore the list of allowed categories is passed to the database by a session variable:

SET SESSION mydb.allowed_categories = '1,3,5';

I use RLS to filter the rows according to the session variable:

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (ARRAY[category] && string_to_array(current_setting('mydb.allowed_categories'),',')::int[]));

The problem is that with this approach the RLS row filtering takes some significant amount of time.
On the other hand, when I experimented by doing:

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (category = 1 OR category = 3 OR category = 5);

the RLS filtering was almost 10 times faster. Of course, such hardcoding is a no go, since I want to change the list of allowed categories dynamically within the app.

The category column has a btree index, however since the number of categories is rather small, the query planner always favors the sequential scan for the RLS filter.

So my question is - is there a way to optimize the RLS expression so it gets at least closer to the hardcoded approach? Would you suggest a different solution to the problem? The application has a lot of users, so I don't want to create a database account for every single one.

At first glance, it might appear that since current_setting and string_to_array functions are stable and immutable respectively and there is an index on the category column, the following condition could do the trick.

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (category = ANY(string_to_array(current_setting('mydb.allowed_categories'),',')::int[])));

However, the real problem has nothing to do with the form of the condition. If you take a look at the PostgreSQL system catalog and find those two functions that were used in the condition, you may notice that both functions have their cost parameter set to 1. This makes the optimizer assume that calling those functions to recheck the condition while doing Bitmap Heap Scan is cheap.

In order to illustrate this, consider the big_table table shown below.

CREATE TABLE big_table AS
SELECT c AS category
FROM generate_series(1, 1000000) g,
     generate_series(1, 10) c;

CREATE INDEX big_table_category_idx ON big_table (category);

ANALYZE big_table;

Querying the table with the the query below results in the following plan (look at the number of rows removed by Index Recheck).

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(string_to_array(current_setting('mydb.allowed_categories'), ',')::int[]);

"Bitmap Heap Scan on big_table  (cost=52478.56..195418.17 rows=3036665 width=4) (actual time=166.613..9273.010 rows=3000000 loops=1)"
"  Recheck Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"  Rows Removed by Index Recheck: 5209365"
"  ->  Bitmap Index Scan on big_table_category_idx  (cost=0.00..51719.39 rows=3036665 width=0) (actual time=164.782..164.782 rows=3000000 loops=1)"
"        Index Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"Total runtime: 9341.568 ms"

To avoid that, you have two options. The first is to increase the work_mem parameter in your server config. This will allow the server to store the complete bitmap in memory and save a great deal of time while rechecking the condition. The plan below was obtained when the work_mem parameter was set to 1000M.

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(string_to_array(current_setting('mydb.allowed_categories'), ',')::int[]);

"Bitmap Heap Scan on big_table  (cost=52478.56..195418.17 rows=3036665 width=4) (actual time=193.613..449.385 rows=3000000 loops=1)"
"  Recheck Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"  ->  Bitmap Index Scan on big_table_category_idx  (cost=0.00..51719.39 rows=3036665 width=0) (actual time=184.858..184.858 rows=3000000 loops=1)"
"        Index Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"Total runtime: 513.528 ms"

The second option is to wrap the query condition in a "costly" function.

CREATE OR REPLACE FUNCTION my_categories()
RETURNS int[] AS $$
BEGIN
  RETURN string_to_array(current_setting('mydb.allowed_categories'), ',')::int[];
END;
$$ LANGUAGE plpgsql STABLE COST 100000;

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(my_categories());

"Index Only Scan using big_table_category_idx on big_table  (cost=250.44..9363945.19 rows=3036665 width=4) (actual time=0.035..577.094 rows=3000000 loops=1)"
"  Index Cond: (category = ANY (my_categories()))"
"  Heap Fetches: 3000000"
"Total runtime: 642.522 ms"

This option will also make the optimizer end up performing Index Scan instead of Bitmap Scan which may turn out to be a bit slower.


Does cross-database permission chaining work for objects owned by sa/dbo? I have two databases, both owned by sa. All objects in both databases are in schema dbo.

I have a user bob in dbSafe who needs to select from a view that joins tables from both dbSafe and dbRestricted. I don't want to grant bob direct permission to those tables in dbRestricted, since they contain columns I don't want bob to see.

I've granted bob SELECT permission on the view, and SET DB_CHAINING ON for both databases, but I'm still getting an error:

  The server principal "bob" is not able to access the     database "dbRestricted" under the current security context.

Am I misunderstanding what cross-database chaining does?

  If the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.

Can database chaining work in this scenario, with database owner sa and schema dbo? Or does it have to be an explicit login account/non-default schema?

The accepted answer to this question suggests creating a view in dbRestricted and assigning direct permission to bob, would that work in this case? What if adding a view to that database isn't an option for me (due to developer/vendor restrictions)? Isn't that what cross-database chaining is for?

You need to create a user for bob in dbRestricted.  You do not need to grant bob access to the table in dbRestricted.

This minimally complete verifiable example shows this in action:

USE master;
CREATE DATABASE db_a;
ALTER DATABASE db_a SET DB_CHAINING ON;
CREATE DATABASE db_b;
ALTER DATABASE db_b SET DB_CHAINING ON;
CREATE LOGIN db_a_login WITH PASSWORD = 'aasdfasdfasdf78723%';
GO

USE db_a;
CREATE TABLE dbo.t
(
    someval varchar(10) NOT NULL
);
INSERT INTO dbo.t (someval) VALUES ('db_a');

CREATE USER db_a_login FOR LOGIN db_a_login;

USE db_b;
CREATE TABLE dbo.t
(
    someval varchar(10) NOT NULL
);
INSERT INTO dbo.t (someval) VALUES ('db_b');
CREATE USER db_a_login FOR LOGIN db_a_login;

USE db_a;
GO
CREATE VIEW dbo.both_t
AS
SELECT *
FROM db_a.dbo.t
UNION ALL
SELECT *
FROM db_b.dbo.t;
GO
GRANT SELECT ON dbo.both_t TO db_a_login;
GRANT SELECT ON dbo.t TO db_a_login;
GO

EXECUTE AS LOGIN = 'db_a_login';
SELECT *
FROM dbo.both_t;
REVERT

╔═════════╗
║ someval ║
╠═════════╣
║ db_a    ║
║ db_b    ║
╚═════════╝

If we remove cross-database-ownership-chaining, we see this fails:

ALTER DATABASE db_a SET DB_CHAINING OFF;
ALTER DATABASE db_b SET DB_CHAINING OFF;

EXECUTE AS LOGIN = 'db_a_login';
SELECT *
FROM dbo.both_t;
REVERT

  Msg 229, Level 14, State 5, Line 50
  The SELECT permission was denied on the object 't', database 'db_b', schema 'dbo'.

Use this to clean up afterwards:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_a')
DROP DATABASE db_a;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'db_b')
DROP DATABASE db_b;
IF EXISTS (SELECT 1 FROM sys.server_principals sp WHERE sp.name = 'db_a_login')
DROP LOGIN db_a_login;
GO


LIKE uses index, CHARINDEX does not? This question is related to my old question. The below query was taking 10 to 15 seconds to execute:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0)

In some articles I saw that using CAST and CHARINDEX will not benefit from indexing. There are also some articles that say using LIKE '%abc%' will not benefit from indexing while LIKE 'abc%' will:

http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where
https://stackoverflow.com/questions/803783/sql-server-index-any-improvement-for-like-queries
http://www.sqlservercentral.com/Forums/Topic186262-8-1.aspx#bm186568

In my case I can rewrite the query as:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'

This query gives the same output as the previous one. I have created a nonclustered index for column Phone no. When I execute this query it runs in just 1 second. This is a huge change compared with 14 seconds previously.

How does LIKE '%123456789%' benefit from indexing?

Why do the listed articles state that it will not improve performance?

I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?

Query using CHARINDEX:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 )

Execution plan:

Query using LIKE:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'

Execution plan:

How does LIKE '%123456789%' benefit from indexing?

Only a little bit. The query processor can scan the whole nonclustered index looking for matches instead of the entire table (the clustered index). Nonclustered indexes are generally smaller than the table they are built on, so scanning the nonclustered index may be faster.

The downside, is that any columns needed by the query that are not included in the nonclustered index definition must be looked up in the base table, per row.

The optimizer makes a decision between scanning the table (clustered index) and scanning the nonclustered index with lookups, based on cost estimates. The estimated costs depend to a great extent on how many rows the optimizer expects your LIKE or CHARINDEX predicate to select.

  Why do the listed articles state that it will not improve performance?

For a LIKE condition that does not start with a wildcard, SQL Server can perform a partial scan of the index instead of scanning the whole thing. For example, LIKE 'A% can be correctly evaluated by testing only index records >= 'A' and < 'B' (the exact boundary values depend on collation).

This sort of query can use the seeking ability of b-tree indexes: we can go straight to the first record >= 'A' using the b-tree, then scan forward in index key order until we reach a record that fails the < 'B' test. Since we only need to apply the LIKE test to a smaller number of rows, performance is generally better.

By contrast, LIKE '%A cannot be turned into a partial scan because we don't know where to start or end; any record could end in 'A', so we cannot improve on scanning the whole index and testing every row individually.

  I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?

The query optimizer has the same choice between scanning the table (clustered index) and scanning the nonclustered index (with lookups) in both cases.

The choice is made between the two based on cost estimation. It so happens that SQL Server may produce a different estimate for the two methods. For the LIKE form of the query, the estimate may be able to use special string statistics to produce a reasonably accurate estimate. The CHARINDEX > 0 form produces an estimate based on a guess.

The different estimates are enough to make the optimizer choose a Clustered Index Scan for CHARINDEX and a NonClustered Index Scan with Lookups for the LIKE. If you force the CHARINDEX query to use the nonclustered index with a hint, you will get the same plan as for LIKE, and performance will be about the same:

SELECT
    [Customer name],
    [Sl_No],
    [Id]
FROM dbo.customer WITH (INDEX (f))
WHERE
    CHARINDEX('9000413237', [Phone no]) >0;

The number of rows processed at runtime will be the same for both methods, it's just that the LIKE form produces a more accurate estimation in this case, so the query optimizer chooses a better plan.

If you find yourself needing LIKE %thing% searches often, you might want to consider a technique I wrote about in Trigram Wildcard String Search in SQL Server.


Issue patching SQL Server 2016 to SP2 CU 15 Trying to update all our SQL 2016 servers to SP2 CU 15, but am running into:
The version of SQL Server instance MSSQLSERVER does not match the version expected
by the SQL Server update. The installed SQL Server product version is 13.1.4001.0,
and the expected SQL Server version is 13.2.5026.0.

SSMS shows the server to be at 13.0.4259.0
@@Version shows:
Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64)   Jun 15 2019 19:20:12
   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016
   Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

What do I need to do to get the SP2 CU 15 patch applied to this server?

Thanks to SqlWorldWide:

You will need to install SP2 first in order to apply any CU that was released after SP2.

Applying the base SP2 patch allowed me to then apply the CU 15 patch.
Being forced into the DBA role, I tried this CU on our test server which was at the same patch level and CU 15 worked fine, hence my confusion.


Error when trying to add a shard set in Mongo db I'm trying to add a shard set in mongos I got this error and . Kindly help.

mongos>db.runCommand( { addshard : " firstset/ip_1:port_1,ip_2:port_2,ip_3:port_3" } );

{
      "ok" : 0,
      "errmsg" : "failed listing   firstset/ip_1:port_1,ip_2:port_2,ip_3:port_3's databases{ ok: 0.0, errmsg: \"not authorized on admin to execute command {listDatabases : 1 }\" ,code : 13 } "
}

You need to have at least the role of clusterManager to perform this action in 2.6 . If you are a dbadmin you can grantRole clusterManager to current user following this tutorial http://docs.mongodb.org/manual/tutorial/assign-role-to-user/


sp_who returns more rows than the value of "max degree of parallelism" sp_configure returns the following values on a server.

name                      minimum   maximum config_value    run_value
max degree of parallelism       0     32767            8    8

However, sp_who1 and sp_WhoIsActive show that some spids have more than 8 rows (like 18 or so). Shouldn't it be at most 8 rows in this case?

This query returns a lot of rows from time to time:

SELECT *
FROM sys.sysprocesses sp
where exists
(
    select spid
    from sys.sysprocesses
    where spid =sp.spid
    and sp.waitresource = ''
    group by spid
    having count(*)>9
)

Shouldn't it be at most 8 rows in this case?

No, because a single execution plan can use more threads than specified by the effective degree of parallelism (DOP).

Only a maximum of DOP threads will be active on a scheduler at a given moment in time. SQL Server (2005 onward) limits a parallel query to DOP schedulers, not DOP threads.

Since a scheduler abstracts a logical processor, this effectively limits the parallelism as desired.

DOP threads are assigned to each parallel branch, where a branch is delimited by Parallelism (exchange) operators.

For more information, see my Simple Talk article:

Understanding and Using Parallelism in SQL Server

and/or my PASS Summit 2013 session recording:

Parallel Query Execution


What's the maximum number of tables that can be referenced in a query in MySQL? In the days of SQL Server 2000 and 2005, the maximum number of tables in a query was 256.

Does MySQL have a similar limitation?

I know that such query of such size means that either there's something wrong with database design or some data warehousing solution should be used, but let's forget about this for a while and focus on theoretical capabilities of MySQL.

Limitations of Joins

In MySQL (4.0, 5.0, 5.5), the maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.


How to split a MySQL table file? I have a table in a MySQL database for which innodb_file_per_table is enabled. One of my tables is about 20GB and growing. How can I partition the table into separate files?

Suppose that I have a table named tab1 that is stored on /var/lib/mysql/db_name/tab1.frm and the size is about 20GB. I want to partition that table to split into 2 files tab1-1.frm and tab1-2.frm each one sized at 10GB.

Table definition:

CREATE TABLE cdr (
     gid bigint(20) NOT NULL AUTO_INCREMENT,
     id bigint(20) NOT NULL,
     start datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
     clid varchar(80) NOT NULL DEFAULT '',
     src varchar(80) NOT NULL DEFAULT '',
     channel varchar(80) NOT NULL DEFAULT '',
     duration int(11) NOT NULL DEFAULT '0',
     uniqueid varchar(32) NOT NULL DEFAULT '',
     dnid varchar(20) NOT NULL DEFAULT '',
     service varchar(100) NOT NULL DEFAULT '',
     cost int(11) NOT NULL DEFAULT '0',
     PRIMARY KEY (gid),
     UNIQUE KEY id (id,prefix),
     KEY start (start),
     KEY clid (clid),
     KEY service (service)
) ENGINE=InnoDB AUTO_INCREMENT=37024605 DEFAULT CHARSET=latin1

I assume it is the .ibd file that is 20GB, not the .frm?

If you want two physically separate tables you could copy tab1-1 into tab1-2 (so you have two copies) then Delete half the data from each. You'd still need to Optimize both tables afterwards to shrink them.

Using the MySQL partition option you would have one table tab1, which you would partition by some range (e.g. all ID's below 10000 in partition 1, 10001 - 20000 in partition 2 etc.). This would then show up on your filesystem as two (or more) separate files (in the form of tab1.frm and various files like tab1#p#partition1.ibd, tab1#p#partition2.ibd).
It wouldn't give you control over the filesizes however.

In the above example, if all your ID's were below 10000 for instance, it would make no difference to your filesizes.

If you provide the table structure, and mysql version it would help.

UPDATE: 2015-04-13 11:44 (GMT+1):

Looking at your table structure, the first thing I see is that you are going to have problems because you have a Primary key and a Unique Key.

  All columns used in the partitioning expression for a partitioned
  table must be part of every unique key that the table may have.

  In other words, every unique key on the table must use every column in
  the table's partitioning expression. (This also includes the table's
  primary key, since it is by definition a unique key.

http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html

At present, any attempt at partitioning will result in an error:

e.g.

Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function

or

Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function

I would recomend looking at some of the following first:

http://dev.mysql.com/doc/refman/5.6/en/partitioning.html
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html
http://mysql.rjweb.org/doc.php/partitionmaint (credit to Rick James for this one)

Also, test this on a non-live server, as it may well take quite some time to build the partitions, which you wouldn't want to do on a Live database.

UPDATE: 2015-04-27 09:52 (GMT+1):

It seems you have some decisions to make. . .

In order to use the Start column, you would need to add start to both the Primary Key (PK) and the Unique Key.

    ALTER TABLE `test`.`cdr`
    DROP PRIMARY KEY
    ,DROP INDEX `id`
    ,ADD PRIMARY KEY (`gid`,`start`)
    ,ADD UNIQUE INDEX `id` (`id` ASC, `prefix` ASC, `start` ASC);

The affect on your PK would be negligible (as long as you DON'T write directly to it - which would defeat the point of the auto-increment anyway).

It would however, potentially cause a problem for your Unique Key, as it would now only be unique across id,prefix,start.

e.g.
insert into test.cdr (id,prefix,start) values (100,'abc','2015-04-27 09:19:00'),(100,'abc','2015-04-27 09:19:01');

will become valid, whereas previously it would have generated a duplicate key error.

so you would need to consider how important that uniqueness is.

If it IS very important, you may want to consider partitioning by id instead, but that depends on what your data looks like as to how easy that would be (and using id,prefix as your PK, and converting gid to a standard (not unique) key.

If it isn't very important, then you could convert the Unique Key to a standard key, and make gid,start your PK.


Read data with group by 100000 distinct values on 36 million rows I am looking for sub second solution for reading data from a billion row table with 8 different columns.

I tried using mysql but it gives sub-par performance. My machine config is :12 core cpu 1.4 Ghz xeon 64GB RAM.

I tried using vertica, citus with postgres, scyllaDB and spark but nothing worked.

Query:

select
    bd,
    p_s,
    sum(v2) v2,
    sum(v1) v1,
    sum(case WHEN tf='N' THEN v2 ELSE 0 end) ty_nv,
    sum(case WHEN tf='E' THEN v1+v2 ELSE 0 end) ly_ev,
    sum(case WHEN tf='S' AND v2>0 AND v1<=0 THEN v2 ELSE 0 end) ty_NBLY_Bv2,
    sum(case WHEN tf='S' AND v2<0 THEN v2 ELSE 0 end) ty_same_ntv,
    sum(case WHEN tf='S' AND v2<=0 AND v1>0 THEN v1 ELSE 0 end) ly_BLY_NBv2,
    sum(case WHEN tf='S' AND v1<0 THEN v1 ELSE 0 end) ly_same_ntv,
    sum(case WHEN tf='S' THEN v2 ELSE 0 end) ty_sv,
    sum(case WHEN tf='S' THEN v1 ELSE 0 end) ly_sv,
    sum(case WHEN tf='S' AND v2>0 AND v1>0 THEN v2 ELSE 0 end) AS ty_BLY_Bv2,
    sum(case WHEN tf='S' AND v2>0 AND v1>0 THEN v1 ELSE 0 end) AS ly_BLY_Bv2,
    sum(case WHEN tf='S' AND v2>0 AND v1>0 THEN (v2-v1) ELSE 0 end) ty_lysiv
FROM  (
    select
        month,
        bd,
        p_s,
        si,
        sum(v2) v2,
        sum(v1) v1,
        (case when sum(sf) > 0 then 'S' else (case when sum(nf) > 0 then 'N' else  (case when sum(ef) > 0 then 'E' else null end)end)end) as tf,
        (case when sum(ly_sf) > 0 then 'S' else (case when sum(ly_nf) > 0 then 'N' else  (case when sum(ly_ef) > 0 then 'E' else null end)end)end) as ly_f
    from
        temp
    group by month, si,p_s,bd
    ) as a
group by p_s,bd

Table Structure is:

CREATE TABLE `temp` (
    `year` int(11) DEFAULT NULL,
    `month` varchar(3) DEFAULT NULL,
    `si` bigint(20) DEFAULT NULL,
    `category` varchar(100) DEFAULT NULL,
    `bd` varchar(100) DEFAULT NULL,
    `pack_type` varchar(100) DEFAULT NULL,
    `pack_segment` varchar(100) DEFAULT NULL,
    `p_s` int(11) DEFAULT NULL,
    `v2` double DEFAULT NULL,
    `v1` double DEFAULT NULL,
    `sf` int(11) DEFAULT NULL,
    `nf` int(11) DEFAULT NULL,
    `ef` int(11) DEFAULT NULL,
    `ly_sf` int(11) DEFAULT NULL,
    `ly_nf` int(11) DEFAULT NULL,
    `ly_ef` int(11) DEFAULT NULL )

Explain Plan of the Query:

"GroupAggregate  (cost=14717923.03..15097409.03 rows=40000 width=60)"
"  ->  Sort  (cost=14717923.03..14727169.03 rows=3698400 width=60)"
"        Sort Key: a.p_s, a.bd”
"        ->  Subquery Scan on a  (cost=12565877.39..13897301.39 rows=3698400 width=60)"
"              ->  GroupAggregate  (cost=12565877.39..13860317.39 rows=3698400 width=64)"
"                    ->  Sort  (cost=12565877.39..12658337.39 rows=36984000 width=64)"
"                          Sort Key: temp.month, temp.si, temp.p_s, temp.bd”
"                          ->  Seq Scan on temp  (cost=0.00..964340.00 rows=36984000 width=64)"

Shrink datatypes --

INT takes 4 bytes; probably overkill for your data.
It is usually ill-advised to split a DATE into multiple columns.
Normalize any repeated VARCHARs.

You have nothing limiting the number of rows you need to look at, so the query must read the entire table.  Unless you have magical disks, you must have enough RAM to hold the entire table, do you?

Since you have nested GROUP BYs, there will need to be one or two temp tables, each with lots of rows, plus one or two sorts.

Your only hope to avoid "sub-par" performance by any database engine is to reformulate your query.

Since you are grouping by month, are the previous month's data static?  That is, could you summarize each month at the beginning of the next month, then use the Summary table?

It might be better to summarize daily, thereby avoiding the moderately large monthly query.

Such a summary table would probably look very much like the output of the derived table (in the from cause).  Probably this would be best:  PRIMARY KEY(p_s, bd, month, si).

Bug?  You are grouping by month.  Does that mean you are combining last July with this July?  Or did you mean to include year in that?  At which point, it is even more compelling to use DATE instead of two columns.


Concurrent Update On Primary Key Column Making Deadlock I have simple table T which have Col1, Col2,Col3 and Col4. columns. Col1 is primary key. Now I am running some concurrent code,
Parallel.For(0, list.Count, new ParallelOptions { MaxDegreeOfParallelism = -1 }, j =>
{
   var obj= list[j];
   // Do some work
   // UPDATE T SET Col2, Col3, Col4 WHERE Col1 =@Col1

Each value of col1 is different. But when I run this code I am getting too much deadlocks. Since Col1 is PK(Clustered Index), I am now confuse why this is happening?
In the deadlock vistim. I see Owner Mode: U
Whenever I run,
USE master
GO
DROP TABLE temp_sp_who2
GO
CREATE TABLE temp_sp_who2 ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL,DBName SYSNAME NULL,Command VARCHAR(1000) NULL, CPUTime INT NULL,DiskIO BIGINT NULL,  LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL,SPID2 INT , RequestId INT NULL )
GO

INSERT  INTO temp_sp_who2
EXEC sp_who2

SELECT  BlkBy ,count(*) FROM
temp_sp_who2 WHERE CAST(BlkBy AS NVARCHAR(MAX)) <> CAST(SPID AS NVARCHAR(MAX))
GROUP By BlkBy
ORDER BY Count(*) DESC
GO

I see BlkBy is different with high numbers. But BlkBy is always high when app is running.

Without the deadlock trace, we can only guess based on symptoms.
Long-term blocking and deadlocks suggest the singleton row update query is performing a full scan rather than locating the row by the PK index. This will happen if the primary key parameter data type is different than the referenced column and results in a non-sargable expression that can't use the PK index efficiently.
Attention to detail is important in declaring parameters in app code or stored procedure to ensure the parameter definition matches the referenced column type. A common cause of mismatched types (not necessarily yours) in ADO.NET is the AddWithValue method, which infers the SQL type based on the supplied value. Strings in .NET are Unicode so the resultant parameter type of a string value is nvarchar. Comparing an nvarchar parameter with a legacy SQL collation varchar column precludes using the primary key index to locate the row, resulting in a full scan that leads to blocking and deadlocks on on active system.


PostgreSQL, UPDATE CASE statement uses huge amount of space I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well.

I am using PostgreSQL 9.3 64Bit on a windows 2012 server. I have tweaked the postgresql.conf using advice on the wiki. So it is as optimised as I can make it.

The table is large (>150million rows) which I need to add an extra column and populate it based on the contents of 3 other columns. The size of this table is 53Gb without indexes.

Having tested lots of approaches I am using a CASE statement. Two of the columns are arrays so I have used GIN indexes and a standard index.

A sample of my CASE statement looks like this, the rules go up to 38

UPDATE osmm.topographicarea
    SET fcode = (CASE
        --building CASE statements
        WHEN (descriptivegroup @> '{Building}' and descriptiveterm @> '{Archway}') then 1
        WHEN (descriptivegroup @> '{Building}') then 2

        WHEN (descriptiveterm @> '{Step}') then 3
        WHEN (descriptivegroup @> '{Glasshouse}') then 4
        WHEN (descriptivegroup @> '{Historic Interest}') then 5
        WHEN (descriptivegroup @> '{Inland Water}') then 6
      ELSE 99
   END);
COMMIT;

The process takes over 5hrs but adds a huge 180Gb to the table!!

Adding an integer column to this table surely should not do this?

How can I fix this please?

Size of the table is 53Gb without indexes. Obviously you have indexes, one of them a GIN index, which is typically multiple times the size of a plain table storing only the indexed column. I expect you get around 180 GB for
SELECT pg_total_relation_size(osmm.topographicarea);

Details on measuring sizes:

Measure the size of a PostgreSQL table row

As has been commented, due to the MVCC model of Postgres, an UPDATE in place effectively writes a new row version of every changed row (and also for affected indexes), which roughly duplicates the size of the table in your operation.
If you don't have to worry about concurrent access, you could go a different route:

Optimizing bulk update performance in PostgreSQL

60 GB (size of table, rounded up) will be too much for a temp table in memory. It would spill to disk, which voids the intended effect. You could just create a new regular table, drop the old one and rename the new - if you don't have concurrent access or depending objects.
To reclaim space you need
VACUUM FULL osmm.topographicarea;
While being at it, make that:
VACUUM FULL ANALYZE osmm.topographicarea;

Or run CLUSTER or use pg_repack or pg_squeeze if you cannot afford an exclusive lock on the big table.
Details:

How to reclaim disk space after delete without rebuilding table?
VACUUM returning disk space to operating system

Optimize UPDATE
Indexes are not helping for this UPDATE, on the contrary. Since you are updating every row and all involved columns are in the same row, indexes are of no use whatsoever here. They still have to be kept up to date at all times, though. It will be much cheaper to delete all indexes and add them back later - especially the comparatively expensive GIN index. And more ... Details:

How to speed up a slow UPDATE query

Also, you are updating every row unconditionally. If some of the rows already have the right value in fcode, it would be cheaper not to touch those at all. Details (last chapter):

How do I (or can I) SELECT DISTINCT on multiple columns?

But it sounds like you are adding a new column. In this case there is nothing to gain here.
Of course, all the usual advice for performance optimization applies as well.


Any advantage of having a second index with included columns on a primary key? I'm reviewing our database and noticed that on a particular table we have the following indexes:

Table:

Col1    INT IDENTITY(1,1) Primary Key
Col2    INT
...
about 15 more columns
....
ColN   VARCHAR(50)
....
another 10 more columns

In addition to the standard primary key clustered index on Col1, we also have the following index:

create nonclustered index [iTable-Col1] ON [dbo].[Table1]
(
    Col1
)
include ( ColN )

We regularly search by Col1 and want to only retrieve ColN. This index does get used as it is the smallest index that contains all of the data required to satisfy the query.

My question is, does this index add any benefit to SQL Server? Would we be better off dropping it and just doing a search on the clustered index?

My only thought is that this index is much smaller than the clustered index (25mb vs 300mb) which may make searching and/or caching faster.

Server is SQL Server 2012 if that makes any difference.

Most of the time, this type of index is only useful for index scans against those columns.

The index tree structure above the leaf level will usually be very similar in terms of tree levels, so if that's the case, there's little or no performance difference in navigating the tree for seek operations (that don't also scan). If you want to learn the basics of index internals, I have a video here that will help you.

Anyways, back to the scans. The reasons why this index would be useful are much less about the index itself, and more about why not to scan the clustered index (or keep those data pages in memory). If the other columns make the table relatively wide (based on the sizes you gave, I would say they do, although it's unclear how large this table is in the context of the entire database), the narrow index may be of benefit in some cases. Frequent scans (or seeks on many different key values) will tend to keep the entire index in memory, so the larger the index, the less memory space there will be for other objects, and of course this will incur more I/O if the buffer pool is cold.

Although it's somewhat of a rare use case (I've done it once or maybe twice that I can remember), the biggest benefit of this type of index is it will greatly help queries that want to use a merge join to join this table. (Note: optimize the query first if an index scan isn't appropriate!) In the query plan, a scan of the clustered index, or a scan and sort of a nonclustered index before a merge join can give an indication of when this might be a good idea. But again, this is rare. The query should be executed frequently enough, or the base table expensive enough to keep stuck in memory (i.e., you don't need it there all the time), to justify the extra storage and maintenance for the index.

If you're unsure how this index is being used right now, check the DMV sys.dm_db_index_usage_stats. Bear in mind that the counts are reset on server restart, so if you're considering dropping the index, make sure it isn't useful during the entire business cycle.


Mariadb - Variable 'innodb_log_file_size' is a read only variable Im using mariadb 10.0.29, I need to change the innodb file size to 256M, default size 50M,

My config file is located on

/etc/mysql/mariadb.conf.d/50-server.cnf

I have added the below line, but it won't work.

[mysqld]
innodb_log_file_size=256M

Also I tried set innodb_log_file_size=5033164800;

But it shows

ERROR 1238 (HY000): Variable 'innodb_log_file_size' is a read only variable

You might have to consider two things before doing this.

Ensure innodb_fast_shutdown SHOULDN'T BE 2. If it is? change it
to 1 globally.
Verify  step 1, query: show variables like 'innodb_fast_shutdown';
Shutdown Mysql.
Ensure you copy the iblogfile0&1 into a safer place and then
remove from data dir.
Make changes to my.cnf for innodb_log_file_size as per your
desired option.
Start Mysql

Rollback: If it doesn't start for some reason put back the iblogfiles and start.

Please see link https://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html for detailed explanation

  UPDATE: In our chat we narrowed down that my.cnf was pointed to a
  different file or picked default ones. We forced mysql to pick the
  mentioned my.cnf via mysqld_safe --defaults-file. And it worked.


What happens when checkpoint_segments reached? I'm a bit confused with checkpoint_segments definition in PostgreSQL documentation. What happens when checkpoint_segments reached?

The dirty pages in shared_buffers written to WAL. Then what is the
usage of background writer?
WAL is merged to actual data files.

Combing through the docs, I see the following (accidental incorrect details will hopefully (or surely) be pointed out by others):

  There is a separate server process called the background writer, whose
  function is to issue writes of "dirty" (new or modified) shared
  buffers.

BW does its work according to its config parameters.  It writes at most bgwriter_lru_maxpages dirty shared buffers, then has a bgwriter_delay millisecond rest.  Now, when it comes to checkpoint_segments log segments (or checkpoint_timeout seconds, if it comes first), it flushes all (remaining) dirty segments to data files.  Now the database data has a consistent state on disk, with a corresponding entry in the log as well.  It is a good starting point for a subsequent REDO if necessary.

And who does make the checkpoints?

  The server's background writer process automatically performs a
  checkpoint [...]

As for your 2nd point, WAL is WAL (stored in log segment files) and data files are data files.  The two will be consistent with each other but not merged.


FlushCache messages appearing in log at specific times We've been having a lot of database performance issues lately, and I've been trying to see if I can figure out why. We don't have a DBA (I'm a software developer) so I'm sort of just winging it, and much of what I find online reads like a foreign language to me.

We've been restarted SQL Server every morning because that's the only way it's operational during the working day. I noticed that every morning around 5 am we are started to get this message every two minutes in the log:

  FlushCache: cleaned up 11848 bufs with 7432 writes in 97168 ms
  (avoided 8139 new dirty bufs) for db 9:0

  last target outstanding: 4, avgWriteLatency 32

  average throughput: 0.72 MB/sec, I/O saturation:
  11635, context switches 18849

The numbers differ each time of course, but it's the same message over and over in that pattern until I restart the server. I'm not sure how to interpret this, I've been trying to Google about it and all I've gathered is that it means there might be something wrong with I/O and that something is taking longer than it's supposed to. We recently switched to using SSD so I didn't think it should be a write problem.

Could anyone shed some light on this?

The FlushCache message in the error log is caused by checkpoint logging, and in this case by a long checkpoint (which is defined as a checkpoint that is taking longer than the recovery interval).  Whether it's logged or not, the behavior is different in pre-2012 and 2012+.  Before SQL Server 2012, to get checkpoint logging you'd have to turn on a trace flag (T3504).  But starting in SQL Server 2012 that message is logged by default when a long checkpoint is encountered.

Now as for the question of "is this actually bad?", you really need to start looking at these numbers given their context.  It took you 97+ seconds to flush only about 93 MB of dirty buffers.  This looks like this could potentially be a mixture of a lot of data churn (during the actual checkpoint itself, about 64 MB worth of buffers were also dirtied) and potentially storage that isn't keeping up with the data modification and/or the rest of the I/O workload.

What I would do is verify the health of your storage subsystem, look at waits, and just get an overall performance picture of the instance.  Take a look at logical disk perfmon counters and see what the overall I/O churn is with throughput, latency, and IOps.  It will help you paint a more vivid picture of how the disks are performing.  If you have the ability to benchmark your storage, if you haven't already baselined it, you should see what these volumes in question are capable of (SQLIO is a great utility for that) and what they are doing right now (it's nice to have a benchmark baseline when the volumes were stood up to compare to a current benchmark).

Here is a great article explaining this message - How It Works: When is the FlushCache message added to SQL Server Error Log?

EDIT:  Re-reading your question, I must've missed this comment:

  I noticed that every morning around 5 am we are started to get this message

See what is happening on your storage at this time as per the guidance above.  That sounds like textbook scheduled operation that is taking a toll on the storage causing the checkpoint performance to suffer and to be "long".


Grant select on tables with a specific naming pattern How to grant select on all tables starting with VVC_?

grant select on VVC_* to user1

There is no inbuilt method for granting permissions on tables matching a pattern. You need to grant it to the individual tables.

It would be quite easy to generate the required script with a query against sys.tables for names like 'VVC[_]%' though.

Instead of using a VVC prefix you could create a VVC schema. Then you can grant select permissions on the schema. This would also cater for tables added in the future to the same schema.


Postgres Logical Replication For Specific tables I'm using logical replication to move data from postgres to a search engine via some background process, but I am only concerned with a small set of tables. Is there a way to specify which tables a replication slot is concerned with

I have a replication user set up and am able to receive the changes just fine.
I've implemented little script to handle the binary protocol and forward to my search engine with:

START_REPLICATION SLOT <SLOT NAME> LOGICAL 0/00000000

But this gives me every change for every table.
I see that this is possible between postgres servers via publications and subscriptions, but this doesn't seem to work from an application client. Maybe I am missing something?

Is there a way to whitelist tables for replication slots this way?

Have you tried specifing the list of tables to be logically replicated while creating the publisher?

Create publisher pub_1 for table table_a;
Create subscription sub_1 publication pub_1;

This will send the changes only for table_a to the subscriber.
This is the way to whitelist a selected set of tables to replicate.

ALTER PUBLICATION name ADD TABLE table_b;

This Alter publication command can be used to add/remove any tables after creating a publication.

I dont think there is a way to do this with replication slots.


MySQL Query doesn't work but works some time, whats wrong with it? Below Query doesn't work most of the time and giving error Getting error #1048 - Column 'StateStarttime' cannot be null but work sometimes, what am I doing wrong here

I think DATE_FORMAT( NOW()-60 , '%H:%i' ) causing the issue if it is what will be the right syntax?

INSERT INTO time (
EmpID,
Date,
Username,
Computername,
State,
MinutesatState,
StateStarttime,
StateEndtime
) VALUES (
'303016',
CURDATE(),
'yatendra',
'NIPL885',
'Active',
'1',
DATE_FORMAT( NOW()-60 , '%H:%i' ),
DATE_FORMAT( NOW() , '%H:%i' )
)

NOW() in number context is treated as de-delimitered textual datetime in short format (see Conversion Between Date and Time Types, the last demo). So substraction can cause invalid datetime value.

To obtain correct result perform calculations in datetime context. The best (and simplest) way is to use

NOW() - INTERVAL 60 SECOND

or use some datetime function.


SQL Server RLS vs Oracle FGAC What is the difference between SQL Server RLS and Oracle FGAC. How to decide which one to go for? We already have both the products in place and teams to support the products.

I don't know Oracle, but these the seems to do different things.

RLS is row-level security. I.e., you see this row, but not that row.

FGAC (fine-grained access control) seems to be about auditing, i.e., "I can see that you did this modification yesterday". That would be more comparable to the Server Audit feature in SQL Server. (Please correct me if I'm wrong, those who know about FGAC.)


SQL Log Shipping - What is the date time format used by transaction log file name? Looking at my SQL log shipping file names (following is an example)
Q:\Backups\LogShipping\MyAudit\MyAudit_20201027165400.trn
What date time format is the transaction log expressed in (local military time, UTC time)?

What date time format is the transaction log expressed

Yes, it is UTC time with the format dbname_YYYYMMDDHHMMSS (time is backup start time in 24 hours format). I think, following screenshot (copied from..) can help for easy validation.
Left side: Highlighted is Log Shipping backup name with UTC datetime
Right side: Date modified column indicating the time in +4 time-zone