PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs If I had a table with 3 columns - say A, B and D - and I had to introduce a new one - say C to replace the current position of D. I would use the following method:

Introduce 2 new columns as C and D2.
Copy the contents of D to D2.
Delete D.
Rename D2 to D.

The new order would be A, B, C and D.

I thought this was a legitimate practice as (so far) it produced no issues.

However, today I came across a problem when a function carrying out a statement on the same table returned the following error:

table row type and query-specified row type do not match

And the following detail:

Query provides a value for a dropped column at ordinal position 13

I tried restarting PostgreSQL, doing a VACUUM FULL and finally deleting and re-creating the function as suggested here and here but these solutions did not work (aside from the fact that they try tackling a situation where a system table has been altered).

Having the luxury of working with a very small database I exported it, deleted it and then re-imported it and that fixed the issue with my function.

I was aware of the fact that one should not mess around with the natural order of columns by modifying system tables (getting hands dirty with pg_attribute, etc.) as seen here:

Is it possible to change the natural order of columns in Postgres?

Judging by the error thrown by my function I now realize that shifting the order of columns with my method is also a no-no. Can anyone shine some light as to why what I am doing is also wrong?

Postgres version is 9.6.0.

Here is the function:

CREATE OR REPLACE FUNCTION "public"."__post_users" ("facebookid" text, "useremail" text, "username" text) RETURNS TABLE (authentication_code text, id integer, key text, stripe_id text) AS '

-- First, select the user:
WITH select_user AS
(SELECT
users.id
FROM
users
WHERE
useremail = users.email),

-- Second, update the user (if user exists):
update_user AS
(UPDATE
users
SET
authentication_code = GEN_RANDOM_UUID(),
authentication_date = current_timestamp,
facebook_id = facebookid
WHERE EXISTS (SELECT * FROM select_user)
AND
useremail = users.email
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id),

-- Third, insert the user (if user does not exist):
insert_user AS
(INSERT INTO
users (authentication_code, authentication_date, email, key, name, facebook_id)
SELECT
GEN_RANDOM_UUID(),
current_timestamp,
useremail,
GEN_RANDOM_UUID(),
COALESCE(username, SUBSTRING(useremail FROM ''([^@]+)'')),
facebookid
WHERE NOT EXISTS (SELECT * FROM select_user)
RETURNING
users.authentication_code,
users.id,
users.key,
users.stripe_id)

-- Finally, select the authentication code, ID, key and Stripe ID:
SELECT
*
FROM
update_user
UNION ALL
SELECT
*
FROM
insert_user' LANGUAGE "sql" COST 100 ROWS 1
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER

I performed the renaming/reordering on both column facebook_id and stripe_id (a new column was added before these, which is the reason for the renaming, but is not touched by this query).

Having the columns in a certain order is purely out of interest for order. However, the reason for asking this question is out of concern that a simple renaming and deleting of a column may trigger real issues for somebody using functions in production mode (as happened to myself).

Probable bug on 9.6 and 9.6.1

This completely looks like a bug to me...

I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0 and 9.6.1).

CREATE TABLE users
(
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    column_that_we_will_drop TEXT
) ;

-- Function that uses the previous table, and that has a CTE
CREATE OR REPLACE FUNCTION __post_users
    (_useremail text)
RETURNS integer AS
$$
-- Need a CTE to produce the error. A 'constant' one suffices.
WITH something_even_if_useless(a) AS
(
    VALUES (1)
)
UPDATE
    users
SET
    id = id
WHERE
    -- The CTE needs to be referenced, if the next
    -- condition were not in place, the problem is not reproduced
    EXISTS (SELECT * FROM something_even_if_useless)
    AND email = _useremail
RETURNING
    id
$$
LANGUAGE "sql" ;

After this setup, the next statement just works

SELECT * FROM __post_users('a@b.com');

At this point, we DROP one column:

ALTER TABLE users
    DROP COLUMN column_that_we_will_drop ;

This change makes the next statement to generate an error

SELECT * FROM __post_users('a@b.com');

which is the same as mentioned by @Andy:

ERROR: table row type and query-specified row type do not match
SQL state: 42804
Detail: Query provides a value for a dropped column at ordinal position 3.
Context: SQL function "__post_users" statement 1
    SELECT * FROM __post_users('a@b.com');

Dropping and recreating the function does NOT solve the problem.
VACUUM FULL (the table or the whole database) does not solve the problem.

The bug report was passed to the appropriate PostgreSQL mailing list and we had a very fast response:

  I can't reproduce this in HEAD or 9.6 branch tip.  I believe it was
  already fixed by this patch, which went in a bit after 9.6.1:

  https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f4d865f22

  But thanks for the report!

  regards, tom lane

Version 9.6.2

On 2017-03-06, I can confirm that I cannot reproduce this behaviour on version 9.6.2. That is, the bug seems to have been corrected on this release.

UPDATE

Per comment of @Jana: "I can confirm the bug is present in 9.6.1 and was fixed in 9.6.2. The fix is also listed on postgres release website: Fix spurious "query provides a value for a dropped column" errors during INSERT or UPDATE on a table with a dropped column"


MySQL format numbers with comma How can I print the integer part of a number with commas separating every three digits?

For example,

 12345    -> 12,345
 1234.567 -> 1,234.567

Use the FORMAT function, which works for floating point numbers as well as for integers:

Floating point numbers:

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'

Integers:

mysql> SELECT FORMAT(12334, 0);
        -> 12,334


SQL Server : create table within stored procedure I have a stored procedure that will be run by a user with public permissions.
When operational, it will be called from an Excel spreadsheet and the results presented on a given tab.

The procedure has many execution paths, one of which is to create a table - [temp].[xyz] (temp already exists as a schema - users with 'public' have alter permission on this schema)

This same s/p is later called many times and selects various results from [temp].[xyz]

Finally, the s/p is called to drop [temp].[xyz]

However, the problem starts when it tries to create [temp].[xyz] using

select ...
into [temp].[xyz]

Error: I don't have permission to create a table. (Note: The table does not exist already)

I think the solution may be something along the lines of : a sys admin creating a new login who has table create permissions.  When it comes to the create(select into) table, wrap this statement in an 'EXECUTE AS' statement this the login who has create privs.

I'm OK with SQL but very basic on sys admin. If you need more details, please ask. Any help as to how I can get round this is appreciated.

Thanks, J

You can add this before the select ... into ...:

If Exists(Select 1 From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = 'xyz' and TABLE_SCHEMA = 'test')
Begin
    EXEC sp_executesql N'Drop Table test.xyz'
End

It will drop the table if it already exists before the select into try to create it.


Exception during recovery in mongo I tried to recover from a sudden shutdown of mongo, but I am experiencing some problems. Here is the log file

***** SERVER RESTARTED *****
Thu Apr 25 01:09:17 [initandlisten] MongoDB starting : pid=19996 port=27017 dbpath=/var/lib/mongodb 64-bit host=Vault
Thu Apr 25 01:09:17 [initandlisten] db version v2.2.3, pdfile version 4.5
Thu Apr 25 01:09:17 [initandlisten] git version: f570771a5d8a3846eb7586eaffcf4c2f4a96bf08
Thu Apr 25 01:09:17 [initandlisten] build info: Linux ip-10-2-29-40 2.6.21.7-2.ec2.v1.2.fc8xen #1 SMP Fri Nov 20 17:48:28 EST 2009 x86_64 BOOST_LIB_VERSION=1_49
Thu Apr 25 01:09:17 [initandlisten] options: { config: "/etc/mongodb.conf", dbpath: "/var/lib/mongodb", logappend: "true", logpath: "/var/log/mongodb/mongodb.log", nohttpinterface: "true" }
Thu Apr 25 01:09:17 [initandlisten] journal dir=/var/lib/mongodb/journal
Thu Apr 25 01:09:17 [initandlisten] recover begin
Thu Apr 25 01:09:17 [initandlisten] recover lsn: 3778163987
Thu Apr 25 01:09:17 [initandlisten] recover /var/lib/mongodb/journal/j._0
Thu Apr 25 01:09:17 [initandlisten] dbexception during recovery: 13537 journal file header invalid. This could indicate corruption in a journal file, or perhaps a crash where sectors in file header were in flight written out of order at time of crash (unlikely but possible).
Thu Apr 25 01:09:17 [initandlisten] exception in initAndListen: 13537 journal file header invalid. This could indicate corruption in a journal file, or perhaps a crash where sectors in file header were in flight written out of order at time of crash (unlikely but possible)., terminating
Thu Apr 25 01:09:17 dbexit:
Thu Apr 25 01:09:17 [initandlisten] shutdown: going to close listening sockets...
Thu Apr 25 01:09:17 [initandlisten] shutdown: going to flush diaglog...
Thu Apr 25 01:09:17 [initandlisten] shutdown: going to close sockets...
Thu Apr 25 01:09:17 [initandlisten] shutdown: waiting for fs preallocator...
Thu Apr 25 01:09:17 [initandlisten] shutdown: lock for final commit...
Thu Apr 25 01:09:17 [initandlisten] shutdown: final commit...
Thu Apr 25 01:09:17 [initandlisten] shutdown: closing all files...
Thu Apr 25 01:09:17 [initandlisten] closeAllFiles() finished
Thu Apr 25 01:09:17 [initandlisten] shutdown: removing fs lock...
Thu Apr 25 01:09:17 dbexit: really exiting now

Based on your log file, you are using MongoDB version v2.2.3. This version is not officially supported by MongoDB, so I'd recommend you plan to upgrade to a newer version. The latest stable version, MongoDB 4.0.9.

As per the log message, the journal file j._0 in /var/lib/mongodb/journal/ folder is corrupted.

Try, removing the j._0 file and do the recovery process.

Disclaimer: you may lose some data that present in the journal file.


MySQL: Does the order of the fields in a table make a difference in performance? Specifically I am wondering if I should place all my fixed length fields like int, timestamp, char at the beginning of the CREATE TABLE and place all the variable length like varchar at the end.

Also, I am wondering if TEXT fields are treated differently.

I would venture to say no up front in terms of the table. However, the order of fields in an index and the number of fields in a WHERE matter a great deal.

Example : You have the following table :

CREATE TABLE testtable
(
    a INT,
    b INT,
    c INT,
    KEY (a,b,c)
);

Note this query :

SELECT * FROM testtable WHERE a=1 AND b=2 AND c=3;

Since every column in the WHERE clause is mentioned as a eq_ref, aka equa-reference (using =), the index can be used to zero in on one column.

Note this query :

SELECT * FROM testtable WHERE a=1 AND b>2 AND c=3;

Column a is in front of the index as an eq_ref, but Column b is not. This is a range query. Depending on the cardinality of Column a (cardinality visible in SHOW INDEXES FROM testtable), an index scan if cardinality of a=1 is very low and the total number of rows with Column a=1 is less than 5% of the number of rows in testtable, otherwise a full table scan is chosen by any Query Optimizer (MySQL, Oracle, PostgreSQL, SQL Server, etc).

Note this query :

SELECT * FROM testtable WHERE b=>2 AND c=3;

This WHERE clause never mentions Column a. Result? automatic full table scan.

As far as order of columns in a table goes, defragging tables and making table formats with fixed row lengths could reduce any possible issues with table column order is that is a suspected concern.

If anyone knows of issues with Oracle, PostgreSQL, SQL Server, or other RDBMS's concerning table column order, please chime in.


SQL Server 2016 All Queries recompile all the time (plan cache corruption?) I have a SQL Server 2016 SP1 production server. It's been running without a restart for 330 days. Everything's been running fine. Until last week, one day, all of the sudden, every query on the server has been generating a compilation.

Usually when I check the Perfmon, I see a lot of Batch Requests/Sec but this day, I saw as much Requests/Sec as Compilations/Sec

When I tried to look at what's using my cache, I had nothing! I've never seen anything like that. It went on and on all day, the CPU was at 100% since it was compiling all the time.

Finally, the IT decided to restart the machine. Everything got fixed after a reboot. But I was wondering, have you ever seen something like that? Can the cache plan be corrupted or something? (Like maybe if I had ran a DBCC FREEPROCCACHE it would have fixed itself without a restart?)

Does that mean that I should reboot my server at least once every six months or so?

Here is a regular baseline for the same server.

You should enable Lock Pages in Memory (LPIM).

On SQL Server 2016+, I've encountered a similar scenario that I believe to be a bug. When the server experiences memory pressure, and LPIM is not be enabled, SQL Server will clear the plan cache as part of it's process to release memory back to the OS. This much in normal. SQL Server should only do this when a low memory condition exists.

In some cases, SQL Server will continue to clear the plan cache, even after the low memory condition is resolved. This results in the behavior you are seeing. On a busy server, you will see some plans entering the plan cache, only to get purged seconds later. This is not the proper behavior.

Enabling LPIM will resolve the issue. Enabling LPIM is a best practice for all SQL Server installations, so I would recommend enabling this for every SQL Server instance, regardless of version or whether you've experienced this problem.

To enable the lock pages in memory option

On the Start menu, click Run. In the Open box, type gpedit.msc.
On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.
Restart the SQL Server Service for this setting to take effect.


Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB We have been monitoring some SQLServer: Memory Manager's metrics, and noticed that after DBCC CheckDB job, metric

Database Cache Memory (KB)

drops down significantly. If to be exact, it dropped from 140 GB cached DB memory to 60 GB. And after that, slowly ramp up again during the week. (Amount of "Free Memory KB", went from 20 to 100 GB right after CheckDB)

DBCC CheckDB is run every Sunday, so Database Cache Memory has to ramp up back again each week

What is the behavior of this ? Why CheckDB pushes database pages out of memory ?

Second question is why "buffer cache hit ratio" did not change after DBCC CheckDB completes ?

It was 99.99% on average and after DBCC CheckDB job it drops to ~98.00%, and returns back to 99% pretty fast while I expected "buffer cache hit ratio" to drop significantly because database data has to be read from storage to RAM again ?

We have been monitoring some SQLServer: Memory Manager's metrics, and
  noticed that after DBCC CheckDB job, metric

  Database Cache Memory (KB) drops down significantly. If to be exact,
  it dropped from 140 GB cached DB memory to 60 GB

This is correct, you can clearly see this behaviour when this example DBCC CHECKDB command completes at 21h45

Why

This behaviour is due to the database snapshot created by the DBCC command being dropped, removing all of its objects in memory.

You can replicate the behaviour by creating a snapshot of a database, loading some data in memory, and then dropping that snapshot

  CREATE DATABASE MY_DATABASE
     GO
     USE MY_DATABASE
     GO
     CREATE TABLE dbo.bla(id int identity(1,1) PRIMARY KEY NOT NULL,
                          val int,
                          val2 char(100));

    INSERT INTO dbo.bla(val,val2)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),'bla'
    FROM master..spt_values spt
    CROSS APPLY master..spt_values spt2;
    GO

    CREATE DATABASE MY_DATABASE_SNAPSHOT
     ON
     (
     NAME ='MY_DATABASE',
     FILENAME ='D:\DATA\MY_DATABASE.ss'
     )
     AS SNAPSHOT OF MY_DATABASE;

     GO

    USE MY_DATABASE_SNAPSHOT
    GO
    SELECT * FROM dbo.bla;

     SELECT
      COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
    FROM sys.dm_os_buffer_descriptors;

BufferSize before dropping the snapshot

BufferSizeInMB
1061.70312  --before

Dropping the snapshot

USE master
GO
DROP DATABASE MY_DATABASE_SNAPSHOT ;

BufferSize after dropping the snapshot

BufferSizeInMB
824.179687 --after

  Second question is why "buffer cache hit ratio" did not change after
  DBCC CheckDB completes ?

This depends on how fast the data is loaded back in your buffer cache.

If your buffer pool fills up over a longer time, it should amount to this ratio staying higher on average.

This corresponds with this part of your question:

  ... It (Buffer pool datasize) dropped from 140 GB cached DB memory to
  60 GB. and after that, slowly ramp up again during the week...


Using Like with variables? I have an odd situation where I have a column called (TransmittalNumber) with datatype of VarChar (75) in a table named (Transmittal Sheet).

I want to query this table to find the transmittal numbers that are duplicated or the ones that starts with the same numbers but ends with a suffix of –W for (West division). Please check the TS# column below:

TSID    TS #    Facility
1    1234       Arizona
2    1234 -W      National
3    2222       Pacif.NW
4    2222 -W      National
5    4444       California
6    4444 -W      National
7    5555        Arizona
8    5555        Arizona

Would something like the following query works?:

declare @TSNumber varchar(75)

set @TSNumber = (select transmittalnumber from TransmittalSheet
)
-   Subquery above returned more than 1 value even with using the IN!
SELECT     TransmittalID, TransmittalNumber, Facility
FROM         TransmittalSheet ts
where  ts.TransmittalNumber like (@TSNumber)

union all

SELECT     TransmittalID, TransmittalNumber, Facility
FROM         TransmittalSheet ts1
where ts1.transmittalnumber like (@TSNumber + '-w')

Prior to 2014, the numbers entered to this column were only added from local regions. Now all TS numbers are entered from a National side, so the older similar local TSs will have –W in the new national entry after a migrate.

The reason of my query now is to clean up the table from the local ones that still have a current active entry in the national.

I've tried a self join with no luck still, any advice?

SELECT ts.TransmittalNumber from TransmittalSheet ts
Full Join TransmittalSheet ts2
ON ts.TransmittalID = ts2.TransmittalID
-- AND
WHERE ts.TransmittalNumber = ts2.TransmittalNumber + ' -W'

I’m using the SQL Server 2008R2

Your data follows a strict pattern:
If there is more than one entry like #TS%, then the first entry will be the one with #TS, the second will be the one with #TS - W. Obviously, if you sort descending, the order will be reversed.

Therefore the solution seems obvious: group by #TS, get those lines where row_number >1, when sorted descending.

SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
FROM         TransmittalSheet ts

To get only the multiples, you'd put that into a CTE:

With T1 as (SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
    FROM         TransmittalSheet ts)
select * from T1 where T1.RN>1

You could then simply inner join that CTE to your original table on TransmittalID to remove all duplicates.

If the TransmittalNumber has a variable length, you'd also have to use patindex(), in order to calculate your parameter for the left() function.


How to use SUBSTRING using REGEXP in MySQL I have the following situation. I have to substring regular expression from description using MySQL.
Description:

  Lorem D9801 ipsum dolor sit amet

Where D9801 is REGEXP. Every strong text description has different content but my regexp should looks like: REGEXP 'D[[:digit:]]{4}'

REGEXP always has "D" at the beginning and "xxxx" - 4 digits at the end: Dxxxx

I know the REGEXP returns only true/false value, but how can I make query to return only 'D9801' value?

I tried something like this:

SELECT SUBSTRING (description, LOCATE(REGEXP 'D[[:digit:]]{4}', description), 5)
FROM (
   SELECT "Lorem D9801 ipsum dolor sit amet" AS description
) temp

I know it's wrong, so I try with this:

SELECT
    id,
    SUM(description REGEXP 'D[[:digit:]]{4}') AS matches,
    CASE
        WHEN (SUM(description REGEXP 'D[[:digit:]]{4}') > 0) THEN
            SUBSTRING(description, LOCATE( /*POSITION_OF_REGEXP_IN_DESC*/ , description), 5)
        ELSE 'Brak schematu'
    END AS show_substr FROM ps_description GROUP BY id;

But how to find position of regexp?

I heard about UDF but I cannot use it, I use OVH hosting.

This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string.
The basic locate syntax you would need is explained here.
LOCATE (search str, str, [position])

search str = A string which will be searched for.
str =A string which is going to be searched.
position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here
SUBSTRING(str, pos, len)

str=  A string.
pos=  Starting position.
len=  Length in characters.

The easier way of viewing this is to think of substring as the following
SUBSTRING(str FROM pos FOR len)
The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.
declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)


Vastly different query run-times on different servers I've got two databases setup on two different SQL Servers. One is 2012 and one is 2016, but aside from that, they have the same CPUs, same core count, and same RAM. They're both VMs in the same location and all tests were run from the same client.

I created the tables and loaded them using the exact same method (SQLBulkCopy in .NET) and then applied the same CLUSTERED indexes to various fields on the tables (based on usage). I ran a query that is reasonably complex and returns about 200K records from a table that contains 4mil records and is JOINed to a few other tables that contain 10K (or so) records.

On the 2012 Server, my run-times (in ms) are:

21772
22864
23209

This seems pretty much about what I'd expect. But on the 2016 Server, the run-times are:

132440
122569
120030

Removing the CLUSTERED index on the largest table and replacing it with a NONCLUSTERED index results in:

72096
71042
69958

I'm looking for ideas on where to start looking for why the run-times would be so vastly different on two servers that are so similar. The run-times on 2012 are well within reason, but on 2016 they're untenable.

Many reasons are affected to your result i try to explain some important of :

  Note

  At First it's important to know how config your VMs many options are important like : reserved CPU ? Or reserve Memory Or Share it.
  How many Vms in your host and when you run your queries how many workloads your host has ? and many other reason like hypervisor config hypervisor queue and etc ...
  Of course it's too complex for explaining by few rows you can read this:
  https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/techpaper/sql_server_virtual_bp.pdf

  Cardinality Estimation (CE)

  After SQL Server 2012 CE is Change and use new algorithm it's one of most factor you have to pay attention when you want to check SQL Server 2012 Vs SQL Server 2016

  https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver15

But I explain about some important factor in SQL Server

CPU

CPU is very important, and config Maxdop and Cost threshold for parallelism are affected to your result.

and many things related to cpu ...

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver15

Memory

The Other important factor you have to check is memory , are your queries cash , limitation of memory and etc

and many things related to RAM ...

Disk I/O

I/O is very important in different way you can look at :

One side is what your disk IOPS is ? Or how many VMDK you are? how you config Filegroups in Vmdks and ...

the other side is How many Page IO you need to do your work in each server (related to check execution paln and Statistics , i will explain it)

TempDB

how do you config your Tempdb by how many files (it's can related to CPU) or where your TempDB is locate

https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/

Log File

how do you config your Log File How many VLF Generate What size of Log growing and ...

MDF/NDF File Config

how do you config your Data File growing ... , FileGroups and etc

Do you config Instant File Initialization
https://www.sqlshack.com/an-overview-of-instant-file-initialization-in-sql-server/

and many other reason of course ...

After ALL ...

Statistics

It's very important to check you Execution Plan (DO NOT COMPARE COST , CHECK WITCH OPERAND USE) in each server and how many page IO you need to complete your action.

check your statistics are update.

when you want to compare some scenario like yours, has baseline is very helpfull to get correct result ,test your senarios a lot and find a baseline to compare

Finally when you use Non-Cluster instead of Cluster Index
if i want to explain shortly : Cluster Index is structure of your data (actually is your data in B+Tree ...) when you remove it your table now is Heap and don't have Tree structure and don't need to sort , or other IO need , for this reason and many other reasons ... your bulk insert faster than before
https://www.sqlshack.com/clustered-index-vs-heap/


Expanding contractions/number ranges into separate records I am planning the migration of a directory of image files plus a FileMaker-database containing the corresponding metadata into an Imagic IMS database (formerly known as ImageAccess). The vendor provides a Java “Datasheet to xml conversion tool” which creates an XML file for importing image metadata from a tab-separated file. The images' filenames are made up by their image reference numbers listed in the FileMaker database.

In the source FileMaker-database the image reference numbers are often contracted, making a sample export record describing four images look something like this:

ref        subject                    photographer
123/3-6    Building A, Living room    Photographer A

For the program converting the tab separated file to XML, these four images should be listed as:

ref        subject                    photographer
123/3      Building A, Living room    Photographer A
123/4      Building A, Living room    Photographer A
123/5      Building A, Living room    Photographer A
123/6      Building A, Living room    Photographer A

How can I best expand these contracted records into separate lines?

In the process of ETL  (extract, transform, and load) you are in the transform stage.

Not being familiar with the vendor specific tools you mention my thoughts are that your going to have to custom build an intermediate step with your own tools or development environment to "transform" the xml file.

This would be possible with most any scripting/programming language. Do you have experience with .Net, Ruby, or other programming languages?

If I were doing this project I would write a .Net utility to scan through the XML file looking for hyphens in the ref column.  I'd then parse the numbers on each side adding additional records as you've described to take the original XML from what it is to a new one in the format you want.  Once done you can continue on with the "load" part of the ETL process.

Since you can conceptualize what your trying to accomplish if you can now select a framework such as .Net or Ruby to do it in, there's a lot of guys at www.stackoverflow.com who could help you get where you're needing to go.


Pivot information - sql table I was wondering if anyone could help me with pivoting data in sql? I am a newbies and need some help

I want to pivot data based on "LastLogOn" column. Is it possible to do in SQL?

From:

To:

You did not specify your RDBMS, but here is a solution that uses SQL Server and dynamic SQL.  If you are using a different RDBMS, you could probably get it to work with minimal changes.

Since you are generating column headings (Username1, Username2, etc.) based on how many UserName's have the same LastLogon value, I used an intermediate table (PivotTemp) to store both the original data as well as the generated column names.

I added a few more rows to the test data to verify the output.

--Demo setup
set nocount on
DECLARE @cols AS NVARCHAR(MAX),@colsCoalesce AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
drop table if exists Table1
create table Table1 (LastLogon datetime, UserId int, UserName1 varchar(30))

insert into table1(LastLogon,UserId, UserName1) values
('2018-06-14 21:00:00',1,'John'),
('2018-06-20 15:03:00',2,'Jack'),
('2018-06-14 15:31:00',3,'Mary'),
('2018-06-18 07:18:54',4,'Ben'),
('2018-06-18 07:18:54',5,'Simon'),
('2018-06-18 07:18:54',5,'Barry'),
('2018-06-20 17:03:54',6,'Andrew'),
('2018-06-20 17:03:54',6,'Stuart')

--The solution
drop table if exists PivotTemp
select * into PivotTemp from
(
select LastLogon, UserName1 as UserName, 'UserName' + convert(varchar(5),ROW_NUMBER() over(partition by LastLogon order by LastLogon)) as GeneratedUserNameHeading from table1
)a
--select * from PivotTemp
SElect @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.GeneratedUserNameHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
SElect @colsCoalesce = STUFF((SELECT distinct ',' + 'coalesce(' + QUOTENAME(c.GeneratedUserNameHeading) + ',''N/A'') as ' + QUOTENAME(c.GeneratedUserNameHeading)
            FROM PivotTemp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

print @cols
print @colsCoalesce
--select * from PivotTemp
set @query = 'SELECT LastLogon, ' + @colsCoalesce + ' from
            (
                select LastLogon
                    , UserName
                    , GeneratedUserNameHeading
                from PivotTemp
           ) x
            pivot
            (
                 max(UserName)
                for GeneratedUserNameHeading in (' + @cols + ')
            ) p '

execute(@query)

| LastLogon               | UserName1 | UserName2 | UserName3 |
|-------------------------|-----------|-----------|-----------|
| 2018-06-14 15:31:00.000 | Mary      | N/A       | N/A       |
| 2018-06-14 21:00:00.000 | John      | N/A       | N/A       |
| 2018-06-18 07:18:54.000 | Ben       | Simon     | Barry     |
| 2018-06-20 15:03:00.000 | Jack      | N/A       | N/A       |
| 2018-06-20 17:03:54.000 | Andrew    | Stuart    | N/A       |


Is there some sort of shortcut/feature in PostgreSQL to avoid horrible storage waste with duplicate values? Let's say that I have this table which keeps track of every page load on my website:

CREATE TABLE "example.com page loads"
(
    id                      bigserial,
    "URL"                   text NOT NULL,
    "IP address"            inet NOT NULL,
    "user agent"            text,
    "timestamp"             timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY             (id)
)

If the same person loads 100 pages, or many other people with the same exact user-agent string load 10,000 pages, the same long "user agent" string will be stored redundantly 100/10,000 times in my poor table, massively inflating it.

This was always a huge problem to me when I used to use plaintext webserver logs, and later when I did the exact same thing as I'm describing right now (a database table in PostgreSQL).

A very obvious and immediate thought that pops up in my head is: "Why can't the user agents be automatically stored just once, internally, and then automatically 'referenced' by PostgreSQL in whatever manner it is comfortable with, while never exposing this internal optimization to me?"

That is, I don't want to have to make a separate table like this:

CREATE TABLE "example.com unique user agents"
(
    id                      bigserial,
    "user agent"            text,
    PRIMARY KEY             (id),
    UNIQUE                  ("user agent")
)

... and then be forced to do expensive and annoying manual queries to look up whether the user-agent is already present in the table of unique user agents and then have a column called "unique user agent id" referencing this table from the "page loads" table instead of a nice, simple text column.

I'm sure you understand exactly what I mean. Basically, it's such a common/obvious thing that I'm 99% sure that this must already have been solved a looong time ago, only I have just never realized it.

There is probably some simple feature to do exactly this, such as (this is just my guess):

CREATE TABLE "example.com page loads"
(
    id                      bigserial,
    "URL"                   text NOT NULL,
    "IP address"            inet NOT NULL,
    "user agent"            text OPTIMIZE_UNIQUELY_INTERNALLY,
    "timestamp"             timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY             (id)
)

That would be lovely, if there is such a "OPTIMIZE_UNIQUELY_INTERNALLY" flag that I can just apply to columns when I want this to be done "under the hood", without me having to think about it!

If there is such a thing, that would save me enormous amounts of storage and headaches.

I don't think that this is the same thing as indexes. Making the "user agent" column into an index won't make PG store each unique value only once, would it? It would only create an additional "look-up" table for quicker queries?

There is no magical automatism for that. You will have to create the lookup table yourself.

This is the way relational databases are designed: you spread your data over several tables. For example, if you normalize your schema, you will end up with more tables than entities. In a way, this lookup table can be seen as a kind of normalization, since the user agent doesn't feel atomic to you.

Don't worry about having more than one table: inner joins are quite simple and readable in SQL, and databases are optimized to process them efficiently.


Different characters, same ASCII code? I have this query that throws two results:

SELECT id FROM table1 WHERE id like 'nm041033%'

nm0410331
nm0410331

And this slightly different query that throws only one result:

SELECT id FROM table1 WHERE id='nm0410331'

nm0410331

I tried to check the ASCII of the last character and got the same:

SELECT id,ascii(substr(id,9,1)) FROM table1 WHERE id like 'nm041033%'

nm0410331 49
nm0410331 49

I guess it is a rare encoding problem. How can I solve it?

PS: The field id is a primary key. The charset is latin1_general_ci, and the values were inserted using PHP utf8_decode().

UPDATE: I changed the charset to ascii_general_ci, and now this query gives me zero results:

SELECT id FROM table1 WHERE id='nm0410331'

However, those two ids are not the same yet. If I use SELECT DISTINCT or GROUP BY I get two rows.

PS: The last character isn't the number you can type with the keyboard.

Thanks to the insight of Akina, who suggested to use HEX() to check the field, I found an extra '0A' byte at the end of one of the values.

After removing the primary key constraing (to avoid the temporary duplicate id), I used:
 UPDATE table1 SET id = TRIM(TRAILING UNHEX('0A') FROM id);
And was able to solve it.

PS: For future googlers, using SELECT id FROM table1 WHERE id like 'nm0410331%' could make me note my silly problem too...


Low PLE on NUMA node 000, high on 001 I'm looking at PLE (Page Life Expectancy) across NUMA nodes on our SQL Servers, and came across a rather weird distribution. NUMA node 000 has a very low PLE compared to 001. I'm unsure why this is. I've checked on multiple other SQL Servers in our environment, and the other production servers do not have this behavior.

System is running SQL Server 2012 Enterprise Edition on Dell m620 with 256GB Ram. It's a 2 socket, 6 core (HT enabled) machine. MAXDOP is set at 6. AFAIK memory modules are installed evenly across the memory banks of the CPUs

Something tells me NUMA node 000 has other SQL tasks to perform, that other nodes, but i've forgotten where I heard/saw it.

@@Version shows: Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64)

If you have a read-intensive query running on one NUMA node (in this case, 0), then it can experience a lower page life expectancy relative to other NUMA nodes.

That's totally normal.

To see what queries are running right now, you can use Adam Machanic's excellent sp_WhoIsActive. It's totally free. Some folks even run it every X minutes and log the data into a table so they can go back to see what was running at the time PLE nosedived.


Grant Permissions Within Report SP Leaving aside why one would want to do such a thing:

In MS SQL Server 2008, Is it possible to have a report SP grant permissions to a column used in that report to a table in a database other than the one it's running in?

I'm looking at something like the following:

USE mydb
CREATE PROCEDURE dbo.report
AS
BEGIN
GRANT SELECT ON otherdb.dbo.table (column) TO [myuser]

SELECT * from otherdb.dbo.table
END

Execute cannot be used on objects in a db other than the current one and USE statements cannot be included within a stored procedure. Is there another way?

Assuming the same user exists in both databases:

EXEC otherdb.sys.sp_executesql N'GRANT ...';

Of course you grant SELECT on a column, not EXECUTE.


SSIS: Conditional operator in expression builder ignores previous text Consider the following simple expression in SQL Server SSIS, where a variable is built with the following expression containing a conditional operator:

"Preamble
"
+
@[$Package::myParameter] == ""  ? "" : "Middle Text"
+
"
The End"

The logic is simple. If myParameter has a value, the "Middle Text" bit will be shown. If there is no value, then it will show nothing. While the conditional seems to work fine, I noticed that any text/expression I have prior to the operator is simply ignored. In this case, the "Preamble" text is never shown regardless if the boolean check gets to TRUE or FALSE.

Any ideas?

The solution is to encapsulate the conditional operation with parenthesis:

"Preamble
"
+
(@[$Package::myParameter] == ""  ? "" : "Middle Text")
+
"
The End"


My availability group doesn't work correctly I recently created an "availability group" on sql server, I created my Listener, all is well the high availability group appears well in the roles of my cluster. However I encounter a small problem, the two replicas are not really synchronized

, then when I open my dashboard I have the error message /i.stack.imgur.com/Gf9wK.png . why it does not work ? I have already done everything but nothing done.

I use windows server 2012 R2 and SQL SERVER 2016 Enterprise.

Can you confirm you have the correct permissions on the endpoint?

USE master;
GO
SELECT SUSER_NAME(principal_id) AS endpoint_owner
    ,NAME AS endpoint_name
FROM sys.database_mirroring_endpoints;
GO

-- change the endpoint owner
USE master;
GO
ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;
GO

-- grant connect
GRANT CONNECT
    ON ENDPOINT::Hadr_endpoint
    TO [yourdomain\username]; -- account used for the SQL Service


Is it poor practice to aggregate data from different tables into one? Background

I write a lot of large reports for and generally maintain a large health records DB (write SPs, functions, jobs, etc.). The original schema, and software that uses it, is from a different vendor, so I can't change much about it structurally.  There are many records that require tracking such as labs, procedures, vaccines, etc. and they are scattered across dozens of tables, many of which are bloated and poorly indexed (I have been able to fix this somewhat).

The Problem

The issue is that because we have little control over the DB, and since it can change from any given update or patch, it makes writing and maintaining these reports difficult and tedious - especially when there's a large amount of overlap.  All it takes is one patch and I'm stuck rewriting large portions of a dozen reports.  Additionally, queries quickly become obfuscated and slow as the joins, nested selects, and applies pile up.

My "Solution"

My plan was to write all of these records to one "catch-all" table, and write triggers on the original tables to maintain the records in this aggregate table.  Of course I'd need to ensure my triggers were intact after updates, but this would be much easier from a maintainability standpoint, and just referencing the data.

The table would be thin and long, storing only the data required, something like this:

CREATE TABLE dbo.HCM_Event_Log (
    id INT IDENTITY,
    type_id INT NULL,
    orig_id VARCHAR(36) NULL,
    patient_id UNIQUEIDENTIFIER NOT NULL,
    visit_id UNIQUEIDENTIFIER NULL,
    lookup_id VARCHAR(50) NULL,
    status VARCHAR(15) NULL,
    ordered_datetime DATETIME NULL,
    completed_datetime DATETIME NULL,
    CONSTRAINT PK_HCM_Event_Log PRIMARY KEY CLUSTERED (id)
)

Then I'd have various relational tables for things like the type_id and item groupings.

I'm starting to second guess this idea as several of these tables are written to quite a bit, the SPs and reports I'd be writing would reference the data a lot as well.  So I'm concerned that this table would become a record locking and performance nightmare with so much I/O.

My Question

Is a bad or a good idea?  I realize every situation is different in SQL Server (2008 r2 Standard Edition BTW), and the "sometimes" rule, but I'm really just looking for general advice.

I started to consider using a service broker, but I'd only be performing simple updates/inserts (See the alternative to the accepted answer). The data in many cases needs to be realtime, so using a backup DB wouldn't really work.  Performance is already somewhat of an issue for us, but most of that is hardware related that'll be resolved soon.

If I understood you correctly,

you have a large third-party system,
you don't have much control over it,
you make complex reports that read data directly from this third-party database,
your queries depend on the internal structure of the third-party database.

I would approach it like this:

Set up my own separate database, which I have full control of.
Set up a sync process that reads data from relevant tables and columns from the third-party database and inserts/updates into mine.
Develop my complex reports based on the stable structure of my database.

In this case you can fine-tune structure and indexes of your database to improve performance of your reports, without affecting third-party system.
Unless the original data structure changes dramatically, the logic of your queries for your reports would not change if third-party database changes. You would have to adjust only the sync process.

The sync process is effectively the conversion process - you convert data from third-party database into the structure that you need. Part of this conversion process could be fixing any normalization problems that original third-party database may have. Only this part of the system has to know and depend on the internal structure of third-party system. Your main reports and main queries would depend only on your database.

So, the main point is - separate and limit the part of your system that depends on internals of third-party system.

update

Regarding real-time requirement. BTW, I always thought that definition of "real-time" is "guaranteed response time", not "some small response time". It depends on your application, of course. In my practice it is enough if I sync two databases within a minute of detected change. If a user sees a report on screen and some underlying data changes, the report has to be somehow re-run to reflect this change. You can poll for changes or listen to some event/message, still the report query has to be executed again to show the latest changes.

You already intend to write triggers to capture changes in the original tables and write these changes to one generic table. So, capture changes as you intended, but write them to properly normalized tables, not a single one.

So, this is extreme case - conversion of third-party data structure into your internal data structure is performed in the triggers that fire on INSERT/UPDATE/DELETE of third-party tables. It can be tricky. The code of triggers would depend on internal structure of both systems. If conversion is non-trivial it may delay original INSERT/UPDATE/DELETE to the point of their failure. If there is a bug in your trigger it may affect original transaction to the point of their failure. If third-party system changes it may break your trigger, which would cause transactions of third-party system to fail.

Less extreme case. To make the code of your triggers simpler and less prone to errors write all captured changes to some staging/audit/diff table(s), set some flag/send a message that there are changes pending and launch the main conversion process that would go through these intermediary tables and perform conversion. The main thing here is that potentially heavy conversion process should happen outside the scope of the original transaction.

At a second glance it looks pretty much like your original suggestion in the question. But, the difference is: the capture-all tables hold data only temporarily; the amount of data is small - just what has changed; it doesn't have to be a single table; eventually the data will be stored in separate properly normalized permanent tables, which you have full control of, which are independent from third-party system and which you can tune for your queries.


Azure SQL Database - Error Starting Extended Events Session I'm using workloadtools to try and capture a production extended events "replay trace" and have hit a problem. The tool is creating the extended events session but is unable to start it for one database (it works fine on another smaller db on the same server). I get this error message:

Operation failed. Operation will cause database event session memory to exceed allowed limit. Event session memory may be released by stopping active sessions or altering session memory options. Check sys.dm_xe_database_sessions for active sessions that can be stopped or altered. If no sessions are active on this database, please check sessions running on other databases under the same logical server. (Microsoft SQL Server, Error: 25747)

The tool uses a template file to define the extended events - see below, I've tried editing the max_memory value both up and down but without success.
max_memory in sys.dm_user_db_resource_governance shows 100 for the db in question, docs say this is a percentage. Looking at that dmv points out to me that the 2 dbs in question have different configurations and are actually in different elastic pools (although on the same server)
These values also seemed pertinent:

max_memory_grant 13
max_db_memory 26787738

Has anyone got a suggestion for what to try next?
CREATE EVENT SESSION [sqlworkload] ON {1}
ADD EVENT sqlserver.attention (
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id,
        sqlserver.sql_text
    )
    {0}
),
ADD EVENT sqlserver.rpc_completed (
    SET collect_data_stream = (0),
    collect_output_parameters = (1),
    collect_statement = (1)
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id
    )
    {0}
),
ADD EVENT sqlserver.sql_batch_completed (
    SET collect_batch_text = (1)
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id
    )
    {0}
),
ADD EVENT sqlserver.user_event(
    ACTION(
        package0.event_sequence,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.{2},
        sqlserver.session_id
    )
    WHERE [sqlserver].[like_i_sql_unicode_string]([user_info],N'WorkloadTools%')
)
WITH (
    MAX_MEMORY = 4960 KB,
    EVENT_RETENTION_MODE = NO_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = PER_CPU,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);

ALTER EVENT SESSION [sqlworkload] ON {1} STATE = START;

So the answer was to check every database in the elastic pool for extended event sessions and then stop and delete the few running ones I found. After that a retry worked.
The hint was in the error message :-

Check sys.dm_xe_database_sessions for active sessions that can be stopped or altered.

I'd like to claim I wrote some smart powershell to iterate through every database - but actually I did a LOT of clicking in SSMS (Powershell next time).


Hashed User concept in db2 Is there anything called "Hashed User" in the IBM DB2 version 10.1? Please help required urgently.

I guess I got the answer. Its not a part of DB2. But its a feature of SecureSphere. As SecureSphere captured the user name as Hashed User and its a security feature of SecureSphere that when SecureSphere encounters user names it cannot decrypt it will put it as "Hashed User".


Optimization issue with user defined function I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I was able to reduce the problem down to this:

select
    S.GROUPCODE,
    H.ORDERCATEGORY
from
    ORDERLINE L
    join ORDERHDR H on H.ORDERID = L.ORDERID
    join PRODUCT P  on P.PRODUCT = L.PRODUCT
    cross apply dbo.GetGroupCode (P.FACTORY) S
where
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

For this query, SQL Server decides to call GetGroupCode function for every single value that exists in PRODUCT Table, even though the estimate and actual number of rows returned from ORDERLINE is 1 (it's the primary key):

Same plan in plan explorer showing the row counts:

Tables:

ORDERLINE: 1.5M rows, primary key: ORDERNUMBER + ORDERLINE + RMPHASE (clustered)
ORDERHDR:  900k rows, primary key: ORDERID (clustered)
PRODUCT:   6655 rows, primary key: PRODUCT (clustered)

The index being used for the scan is:

create unique nonclustered index PRODUCT_FACTORY on PRODUCT (PRODUCT, FACTORY)

The function is actually slightly more complex, but the same thing happens with a dummy multi-statement function like this:

create function GetGroupCode (@FACTORY varchar(4))
returns @t table(
    TYPE        varchar(8),
    GROUPCODE   varchar(30)
)
as begin
    insert into @t (TYPE, GROUPCODE) values ('XX', 'YY')
    return
end

I was able to "fix" the performance by forcing SQL server to fetch the top 1 product, although 1 is max that can ever be found:

select
    S.GROUPCODE,
    H.ORDERCAT
from
    ORDERLINE L
    join ORDERHDR H
        on H.ORDERID = M.ORDERID
    cross apply (select top 1 P.FACTORY from PRODUCT P where P.PRODUCT = L.PRODUCT) P
    cross apply dbo.GetGroupCode (P.FACTORY) S
where
    L.ORDERNUMBER = 'XXX/YYY-123456' and
    L.RMPHASE = '0' and
    L.ORDERLINE = '01'

Then the plan shape also changes to be something I expected it to be originally:

I also though that the index PRODUCT_FACTORY being smaller than the clustered index PRODUCT_PK would have an affect, but even with forcing the query to use PRODUCT_PK, the plan is still the same as original, with 6655 calls to the function.

If I leave out ORDERHDR completely, then the plan starts with nested loop between ORDERLINE and PRODUCT first, and the function is called only once.

I would like to understand what could be the reason for this since all the operations are done using primary keys and how to fix it if it happens in a more complex query that can't be solved this easily.

Edit: Create table statements:

CREATE TABLE dbo.ORDERHDR(
    ORDERID varchar(8) NOT NULL,
    ORDERCATEGORY varchar(2) NULL,
    CONSTRAINT ORDERHDR_PK PRIMARY KEY CLUSTERED (ORDERID)
)

CREATE TABLE dbo.ORDERLINE(
    ORDERNUMBER varchar(16) NOT NULL,
    RMPHASE char(1) NOT NULL,
    ORDERLINE char(2) NOT NULL,
    ORDERID varchar(8) NOT NULL,
    PRODUCT varchar(8) NOT NULL,
    CONSTRAINT ORDERLINE_PK PRIMARY KEY CLUSTERED (ORDERNUMBER,ORDERLINE,RMPHASE)
)

CREATE TABLE dbo.PRODUCT(
    PRODUCT varchar(8) NOT NULL,
    FACTORY varchar(4) NULL,
    CONSTRAINT PRODUCT_PK PRIMARY KEY CLUSTERED (PRODUCT)
)

There are three main technical reasons you get the plan you do:

The optimizer's costing framework has no real support for non-inline functions. It does not make any attempt to look inside the function definition to see how expensive it might be, it just assigns a very small fixed cost, and estimates the function will produce 1 row of output each time it is called. Both these modelling assumptions are very often completely unsafe. The situation is very slightly improved in 2014 with the new cardinality estimator enabled since the fixed 1-row guess is replaced with a fixed 100-row guess. There is still no support for costing the content of non-inline functions, however.
SQL Server initially collapses joins and applies into a single internal n-ary logical join. This helps the optimizer reason about join orders later on. Expanding the single n-ary join into candidate join orders comes later, and is largely based on heuristics. For example, inner joins come before outer joins, small tables and selective joins before large tables and less selective joins, and so on.
When SQL Server performs cost-based optimization, it splits the effort into optional phases to minimize the chances of spending too long optimizing low-cost queries. There are three main phases, search 0, search 1, and search 2. Each phase has entry conditions, and later phases enable more optimizer explorations than earlier ones. Your query happens to qualify for the least-capable search phase, phase 0. A low enough cost plan is found there that later stages are not entered.

Given the small cardinality estimate assigned to the UDF apply, the n-ary join expansion heuristics unfortunately reposition it earlier in the tree than you would wish.

The query also qualifies for search 0 optimization by virtue of having at least three joins (including applies). The final physical plan you get, with the odd-looking scan, is based on that heuristically-deduced join order. It is costed low enough that the optimizer considers the plan "good enough". The low cost estimation and cardinality for the UDF contributes to this early finish.

Search 0 (also known as the Transaction Processing phase) targets low-cardinality OLTP-type queries, with final plans that usually feature nested loops joins. More importantly, search 0 runs only a relatively small subset of the optimizer's exploration abilities. This subset does not include pulling an apply up the query tree over a join (rule PullApplyOverJoin). This is exactly what is required in the test case to reposition the UDF apply above the joins, to appear last in the sequence of operations (as it were).

There is also an issue where the optimizer can decide between naive nested loops join (join predicate on the join itself) and a correlated indexed join (apply) where the correlated predicate is applied on the inner side of the join using an index seek. The latter is usually the desired plan shape, but the optimizer is capable of exploring both. With incorrect costing and cardinality estimates, it can choose the non-apply NL join, as in the submitted plans (explaining the scan).

So, there are multiple interacting reasons involving several general optimizer features that normally work well to find good plans in a short period of time without using excessive resources. Avoiding any one of the reasons is enough to produce the 'expected' plan shape for the sample query, even with empty tables:

There is no supported way to avoid search 0 plan selection, early optimizer termination, or to improve the costing of UDFs (aside from the limited enhancements in the SQL Server 2014 CE model for this). This leaves things like plan guides, manual query rewrites (including the TOP (1) idea or using intermediate temporary tables) and avoiding poorly-costed 'black boxes' (from a QO point of view) like non-inline functions.

Rewriting CROSS APPLY as OUTER APPLY can also work, as it currently prevents some of the early join-collapsing work, but you have to be careful to preserve the original query semantics (e.g. rejecting any NULL-extended rows that might be introduced, without the optimizer collapsing back to a cross apply). You need to be aware though that this behaviour is not guaranteed to remain stable, so you would need to remember to retest any such observed behaviours each time you patch or upgrade the SQL Server.

Overall, the right solution for you depends on a variety of factors that we cannot judge for you. I would, however, encourage you to consider solutions that are guaranteed to always work in future, and that work with (rather than against) the optimizer wherever possible.


Poor temporal table performance on older values I'm encountering a strange issue occurring when accessing historical records within a temporal table. Queries that access the older entries in the temporal table via the AS OF sub-clause take longer than queries on recent historical entries.

The historical table was generated by SQL Server (includes a clustered index on the date columns and uses page compression), I have added 50 million rows to the historical table, and my queries were retrieving about 25,000 rows.

I have tried to determine the root cause of the issue but have not been able to identify it. So far I have tested:

Creating a test table with 50 million rows with a clustered index to see if the slow down was simply due to volume. I was able to retrieve 25K rows at constant time (~400ms).
Removing page compression from the historical table. That had no effect on the retrieval time but did significantly increase the size of the table.
I tried accessing the rows of the history table directly using an ID column vs the date columns. This is where things were a bit more interesting. I could access older rows in the table at ~400ms where as with the AS OF sub clause it would take ~1200ms. I tried filtering on my test table on the date column and noticed a similar slowdown when compared to filtering on the ID column. This leads me to believe that the date comparisons are behind some of the slowdown.

I want to look at this more but I also want to make sure that I am not barking up the wrong tree. First, has anyone else experienced this same behavior when accessing older historical data in a temporal table (we only noticed slow downs passed 10 million rows)? Second, what are some strategies I can use to further isolate the root cause of the performance issue (I just started looking into execution plans but it is still a bit cryptic to me)?

Execution plans

These are simple retrieval queries: the first accesses older rows, the second accesses newer rows.

Older Rows ~1200ms execution time

Recent Rows ~350ms execution time

Table details

These are the columns in the temporal table. The history table has the same columns but does not have a primary key (as per the history table requirements):

Below are the indices on the history table:

In a comment from Zane on your question, he stated:

  ...It seems like part of your problem is you're reading 50 million rows in order to return 20K in the plan.

This is, indeed, the problem.  There's no index available to push some, or all, of the predicates down to the storage engine.  Microsoft recommends this baseline indexing strategy for temporal tables in the Docs article Temporal Table Considerations and Limitations:

  An optimal indexing strategy will include a clustered columns store index and / or a B-tree rowstore index on the current table and a clustered columnstore index on the history table for optimal storage size and performance. If you create / use your own history table, we strongly recommend that you create this type of index consisting of period columns starting with the end of period column to speed up temporal querying as well as speeding up the queries that are part of the data consistency check. The default history table has a clustered rowstore index created for you based on the period columns (end, start). At a minimum, a non-clustered rowstore index is recommended

The phrasing of that is a little confusing (to me, anyway).  But the takeaway is that you could create these indexes to improve performance some, if not quite a lot:

NC index on the current table, leading with SysEndTime:

CREATE NONCLUSTERED INDEX IX_SysEndTime_SysStartTime
ON dbo.Benefits (SysEndTime, SysStartTime)
/*INCLUDE (ideally, include your other important fields here)*/;

This will allow you to avoid reading some of the rows in the current table by seeking to the appropriate end time.

CCI on the history table

CREATE CLUSTERED COLUMNSTORE INDEX ix_BenefitsHistory
ON dbo.BenefitsHistory
WITH (DROP_EXISTING = ON);

This will let you get batch mode on the history table, which should make the scans much faster.

NC index on the current table, leading with SysStartTime:

See Paul's answer to the question Most Efficient Way to Retrieve Date Ranges for more details on why indexing for date range queries is hard.  Based on the logic there, it makes sense to add another NC index on the current table that leads with SysStartTime, so that the optimizer can choose which one to use based on statistics and the specific parameters of your query:

CREATE NONCLUSTERED INDEX IX_SysStartTime_SysEndTime
ON dbo.Benefits (SysStartTime, SysEndTime)
/*INCLUDE (ideally, include your other important fields here)*/;

Creating the 3 indexes outlined above made a significant difference in resource usage in my test cases.  I set up a test case which runs two queries that return 1.5 million total rows.  Both the history and current tables have 50 million rows).

Note: To reduce SSMS overhead, I ran the test with "Discard results after execution" option enabled.

Execution Plan - Default Indexes

Logical reads: 1,330,612
CPU time: 00:00:14.718
Elapsed time: 00:00:06.198

Execution Plan - With Indexes Described Above

Logical reads: 27,656 (8,111 row store + 19,545 columnstore)
CPU time: 00:00:01.828
Elapsed time: 00:00:01.150

As you can see, all 3 measures dropped significantly - including total elapsed time, from 6 seconds to 1 second.

The other option presented by the Docs article is to forgo the two NC indexes on the current table in favor of a clustered columnstore index.  In my test, performance was very similar to the indexing solution described above.


Long/complex INSERT and UPDATE query parser or visual editor/query builder I have long INSERT queries that insert  values to table with over 50 columns. Is there any tool to parse this long query to key-value representation?

like:

INSERT INTO sometable(col1, col2, col3) VALUES (1,2,3)

parse to:

col1: 1
col2: 2
col3: 3

So it would be easier to analyze.

One method is with the Microsoft.SqlServer.TransactSql.ScriptDom. This can be used from a .NET application or PowerShell script. The assembly is available as a NuGet package or, if you already have SQL Server tools installed, you can reference an existing assembly in the Program Files (x86) folder.

Below is a PowerShell example that uses the assembly installed by SSMS.

Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

$query = "INSERT INTO sometable(col1, col2, col3) VALUES (1,2,3)"

# use the appropriate TSqlParser version for the scripts being parsed
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

$scriptReader = New-Object System.IO.StringReader($query)

$fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
if($parseErrors.Count -eq 0) {
    for($i = 0; $i -lt $fragment.Batches[0].Statements[0].InsertSpecification.Columns.Count; ++$i) {
        $columnName = $fragment.Batches[0].Statements[0].InsertSpecification.Columns[$i].MultiPartIdentifier[0].Value
        $columnValue = $fragment.Batches[0].Statements[0].InsertSpecification.InsertSource.RowValues[0].ColumnValues[$i].Value
        Write-Host "$columnName`: $columnValue"
    }
}
else {
    Write-Host "$($parseErrors.Count) parsing errors. First error: $($parseErrors[0].Message)" -ForegroundColor Red
}


Make a DBMS-enforced multi-column "UNIQUE" constraint, with a "match all" wildcard Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour.

I have multiple columns. Now I wish to make sure each entry into the database has a unique column. Normally this would be a simple

ALTER TABLE test
ADD UNIQUE(X, Y)

However in our application "NULL" means not "no data" but rather "wildcard". (unless you think there's a better general purpose to specify this). What this means is that for unique constraint when one of the columns is not filled, it "matches everything", ie the following tables would violate unique constraints (in addition to normal constraints):

X    | Y
'a'  | 'B'
'a'  | null

X    | Y
'a'  | 'B'
null | 'B'

While this would be obviously "ok":

X    | Y
'a'  | 'B'
null | 'C'

X    | Y
'a'  | 'B'
'b'  | null

Can I put such a constraint on database level? Or do I have to guarantee this on server level?

ps: this is not the primary key, there is always a column id for primary keys. Also in this example I use strings, but actually the datatypes are different and can be anything.

I suggest you fix your design. Using NULL as a wildcard is not the best in my opinion. Splitting the table into 2 or 3 so the wildcard information is stored in separate tables would be better.

If you keep this design, it will need some complex exclusion constraints, besides the UNIQUE one:

CREATE TABLE test (
    test_id serial NOT NULL PRIMARY KEY,
    x int NULL,
    y int NULL,

-- unique constraint for non-null (x,y) values
CONSTRAINT x_y_unique
    UNIQUE (x,y),

    -- when x IS NULL, do not allow a non-null x value (with same y)
    CONSTRAINT unique_with_wildcards_x_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE 1 END) WITH <>,
          y with =
        ),

    -- do not allow 2 NULL x values (with same y)
    CONSTRAINT unique_with_wildcards_x_one_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE NULL END) WITH =,
          y with =
        ),

    -- when y IS NULL, do not allow a non-null y value (with same x)
    CONSTRAINT unique_with_wildcards_y_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE 1 END) WITH <>
        ),

    -- do not allow 2 NULL y values (with same x)
    CONSTRAINT unique_with_wildcards_y_one_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE NULL END) WITH =
        )
) ;

Tested in dbfiddle.uk.

Notice that the (NULL, NULL) combination is allowed. If you want this to be treated as a super-wildcard, (so if it's in the table, no other rows would be allowed), you'll need some more exclusion constraints.


Need help on a database model design I am creating a small booking application with Laravel. It lists some centers. Each center has its own unique rooms. Each room may be booked several times (with dates). From my application, I post the 'from' and 'to' dates. I need to add the date range as booking dates of a particular room. Remember, a room can be booked more than once, but to a limited number of times. I need this because I want to mark the booked days with different colors in a calendar in the front-end.

Just in case you want to know, I'm more concerned about the booked dates more than everything else.

Thanks.

UPDATE:

I want advice on how to setup the database tables and columns. Then, how to set the booked dates of a room. Is it enough if I set a Start and End date to the booking, or do I need a row for each day that is booked?

Thanks.

Your question is not very clear on this point but I'm assuming you want to find overlaps with a proposed, new booking.

Let the values for the proposed booking be held in the variables @StartDate, @EndDate and @RoomID.  The SQL will be

select
    RoomID,
    StartDate,
    EndDate
from TheBookingTable as t
where t.RoomID =  @RoomID
and T.StartDate <= @EndDate
and T.EndDate >= @StartDate;

This query will return all current bookings which overlap your proposed booking, or an empty set if none exist.


access columns returned by psql from bash I want to use value returned by psql in bash. This is the script:

psql "connection parameters" -c "SELECT pg_database_size('dbname');"

The output is like this:

 pg_database_size
------------------
          5773072
(1 row)

But I only want the 5773072 so I can use it in logging. Can anyone help?

In addition to what @Milen already provided, you may want -A (--no-align) to remove leading white space:

psql "con params" -tAc "SELECT pg_size_pretty(pg_database_size('mydb'))"

As an aside: pg_size_pretty() may or may not be of interest.


Docker for Oracle Timestamp out of sync I have created an Oracle Database Docker Image with the help of this link

SYSTIMESTAMP shows UTC time and not my local machine's time.

kaushik:~ knayak$ date
Fri Jun  8 17:46:35 IST 2018

select current_timestamp,systimestamp from dual;

CURRENT_TIMESTAMP                           SYSTIMESTAMP
------------------------------------------- ----------------------------------
08-06-18 05:47:51.742216000 PM ASIA/KOLKATA 08-06-18 12:17:51.742211000 PM GMT

How can I change the container's time to match with my time zone?

The problem is not with your database, it is the problem of the docker.

Docker has notorious problems with the time synchronization between the host machine and the virtual machines ("containers"...).

The simplest solution for the problem if you start the docker container with admin privileges (--privileged flag to docker run), and then use a time synchron tool inside the container to fix its time (ntpdate -s your.country.clock).


Dropped an index and recreated it in PostgreSQL 9.2, not used anymore I have a table with 15M rows, with two indexes; one of them is a very simple BTREE on a string column.

While trying to optimize some queries, I created a new partial index on the same column. Using EXPLAIN I noticed that the index wasn't used, so I tried to drop the original full index.

The partial index was still not used (a sequential scan was used instead). After some other tests, I dropped the new partial index and restored the old one. So the situation should be exactly the same as at the start. But the re-created index is not used, not even with the simplest of queries:

SELECT * FROM table WHERE status = 'status'

It still insists to do a full scan. I tried analyze, then vacuum analyze, nothing.
Before recreating it, the same identical index was used. Why did this happen? How can I make PostgreSQL use the index again?

This is the explain:

"Seq Scan on table  (cost=0.00..350340.22 rows=246 width=579)"
"  Filter: ((status)::text = 'partial'::text)"

This is the index:

CREATE INDEX index_table_on_status
  ON table
  USING btree
  (status COLLATE pg_catalog."default");

I solved the mystery on pg irc channel - I had a long running query (idle in transaction) and they told me that a new index doesn't get used until all transactions that started before its creation don't finish.

Killed the transaction, and now the indexes are back to normal.


How does the MySQL Query Optimizer react to a SELECT COUNT sentence? Let's say we have a table called "customers" with these columns ("id", "name", "country", "created_at).

There's an index by ("country", "created_at").

If

SELECT COUNT(*)
FROM customers
WHERE ID > 10000
AND country = "US"
ORDER BY country, created_at

is executed, does the Query Optimizer select the same path as if no COUNT were requested, as in

SELECT * FROM ...//same conditions

?

Rephrasing, does MySQL Query Optimizer take into account sort orders when resolving SELECT COUNT?

The two queries have a very big difference:

----- query 1
SELECT COUNT(*)
FROM customers
WHERE ID > 10000
  AND country = 'US' ;

----- query 2
SELECT *
FROM customers
WHERE ID > 10000
  AND country = 'US' ;

While the second query returns all rows that match the WHERE conditions, the first one has an aggregate function (COUNT()) in the SELECT list, so it does an aggregation, a collapsing of rows that match the conditions into one row and returns only one number, the number of rows that match the conditions.

So, for the first query, there is no sensible reason to have an ORDER BY. The result is one row only. Even more, it should produce an error as the rows (that have been collapsed into one) may have different values in the country and created_at columns. So, which one should be used for the ordering (say in a case where you had a GROUP BY and the result set was more than one rows)?

You can test at SQL-Fiddle that SQL-Server, when you add ORDER BY country, created_at, it produces the error:

  Column "customers.country" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

An error is produced in Postgres, too.

But even in MySQL that may allow such non-standard syntax, to add ORDER BY in the first query, the optimizer is smart enough to not take that into account for the execution plan. There is nothing to order. One row will be returned anyway. You can check that by viewing the execution plans with EXPLAIN. Simple test at SQL-Fiddle: Mysql-test

Oracle (version 11g2) seems to allow such nonsense too. You can see the execution plan here: Oracle-test. Not sure how the plan should be interpreted but it seems that Oracle at least knows that it's one row only so the "sorting" operation is not costly.


MySQL PHPMyAdmin Autocommit Off I have global autocommits off but I was wondering if it were possible to set autocommit on for PHPMyAdmin during login without doing so manually. Turning off autocommit makes the PHPMyAdmin GUI not work properly (clicking 'delete' on a row won't actually delete it since there isn't a commit). Since PHPMyAdmin creates a new session on each page load, setting autocommit on doesn't do anything since it immediately gets 'wiped out'. I have to manually type in queries followed by a commit.

You could set a trigger to watch for a session coming in from phpmyadmin or a specific user. That trigger could fire once the session is created and enable autocommit. Since MySQL 5.7 still doesn't support all the DDL command you could use init_connect to drive the procedure when a user connects.

Here is a document outlining the init_connect operation. Logon Init_connect operation


How to preserve key when joining to a preference table in Oracle I have a table of customers, and a table of customer preferences, where:

create table customer (
  customerId number,
  name varchar2(200),
  primary key (customerId)
);

create table pref (
  preftype number,
  customerId number,
  prefval varchar2(200),
  primary key (preftype, customerId)
);

So basically the pref table records the customer's preference based on each preference type. customerId is unique for each preftype.

Theoretically the following join should not lose the key-preserved property, but I am getting "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table." Anyway I can rewrite the query to maintain the key-preservedness?

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;

Restrictions / requirements:

In our case a query engine is building that outer query (the one with rowid) around the inner query, so we don't have as much control.

We'd like to have a query that can be wrapped around with something like:

select rowid, v.*
from
    (
    -- query
    ) v ;

I don't see the reason for having doubly nested subqueries when a simple LEFT JOIN can give the same result:

select c.customerid, c.name, p.prefval
       -- , c.rowid                        -- this errs
from customer c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Now, when we add c.rowid in the SELECT list, Oracle parser/optimizer is not able to identify that every row of customers is going to be joined with - at maximum - 1 row from pref. So it throws an error.

We can convert the LEFT JOIN to a subquery though - and no error is thrown:

-- query 2 --
select c.customerid, c.name,
       ( select p.prefval
         from pref p
         where c.customerid = p.customerid
           and p.preftype = 1
       ) as prefval,
       c.rowid                               -- but works here
from customer c ;

or putting the customers in a derived table and then left join to pref:

-- query 3 --
select c.customerid, c.name, p.prefval, c.rowid
from
    (select c.*, c.rowid from customer c) c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Tested at rextester.com


MySQL DELETE becomes exceptionaly slow with a large number of rows When performing a DELETE against a large number of rows in the Prices table, the DELETE becomes progressively slower.  If deleting 15,000 rows it runs in about 15 seconds.  20K rows takes 3 or 4 minutes.  40,000 rows takes 15 minutes, 100,000 rows runs for well over an hour.

The below After DELETE trigger updates the Items table with a count of the available price types.  This is used when calculating prices to speed up that process in production.

Both tables are InnoDB, and I have updated innodb_buffer_pool_size to 4G, with no effect.

I have verified that all SQL statements are using indexes. There is a PriceType + ItemID index on the Prices table, and ItemID is the primary key for the Items table.

Unfortunately this schema is determined by another application and I can't modify the table structure. I can modify the triggers, indexes, etc.  That application cannot be changed to update the counts in the Items table directly.

BEGIN

    DECLARE iPriceTypeA INT;
    DECLARE iPriceTypeB INT;
    DECLARE iPriceTypeC INT;

    SET iPriceTypeA = (SELECT COUNT(*) FROM Prices WHERE PriceType='A' AND ItemID=OLD.ItemID),
        iPriceTypeB = (SELECT COUNT(*) FROM Prices WHERE PriceType='B' AND ItemID=OLD.ItemID),
        iPriceTypeC = (SELECT COUNT(*) FROM Prices WHERE PriceType='C' AND ItemID=OLD.ItemID);

    UPDATE Items
        SET PriceTypeA = iPriceTypeA,
            PriceTypeB = iPriceTypeB,
            PriceTypeC = iPriceTypeC,
        WHERE ItemID = OLD.ItemID;

END

This doesn't seem like it ought to create a problem, but when deleting 120K rows the server becomes unusable for a couple of hours. Why would this query become essentially exponentially slower with more rows?

Edit: updating with Prices table schema

ItemCode varchar(30)
PriceType char(1)
Method char(1)
Factor decimal(7,2)

Edit:

I am still mystified why this trigger causes the delete to become slower the greater the number of records.  As I said if I remove the trigger call the delete is very quick.  I would really like to get to the bottom of the performance issue.

TO get things working for now my solution was to switch to MyISAM since this table is only used for selects in our application.

With InnoDB, deleted rows must be held in case there is a crash or other reason to rollback the action.  This is costly, and may get more costly with larger counts, as you are seeing.

Plan A:  Delete in smaller chunks and COMMIT after each chunk.  (Similarly, chunking should be applied to Updates.)

Plan B, C, D, ...  See more tips

If no WHERE

No WHERE?  It would be much better to create a new table, then use RENAME TABLE to atomically swap the new one into place.  And finish with DROP TABLE.  No down time; no costly delete.

Query rewrite

If you are running 5.7, there is a new feature there which lets you "rewrite" queries to accommodate various kinds of nasties.  More discussion.  You could probably turn a delete without a where into DROP and CREATE.  (This is not quite as good as the previous suggestion.)


SELECT every second row without auto_increment How can I select every second row in MySQL, without using any data of the table or some auto_increment?
Something like

SELECT EVERY SECOND ROW FROM `table`;

Thank you!

There is no SQL mechanism for select every other row.

You will have to use an user-defined auto increment variable as follows:

ODD

SET @inc = 0;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;

EVEN

SET @inc = 1;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;

Give it a Try !!!

MORE INSIGHTS

If the table is very large, try using a LEFT JOIN of id

ODD

SET @inc = 0;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;

EVEN

SET @inc = 1;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;

PAGINATION

ODD

SET @inc = 0;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id LIMIT 0,20) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;

EVEN

SET @inc = 1;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id LIMIT 0,20) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;


Suddenly have to rebuild indexes to prevent site from going down I have been having this problem for a while:
https://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column

It seems all is good and when I suddenly get a lot of writes to the table the index seems to die and queries take up to 2 seconds. This always happens when I've got a lot of traffic so the knock on effects pretty much bring my database server down.

If I rebuild the index all is good again. What could be causing these sudden 'breaks' in the index? How can it be prevented?

table def:

+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | int(11) unsigned      | NO   | PRI | NULL    | auto_increment |
| user_id       | mediumint(8) unsigned | YES  | MUL | NULL    |                |
| created       | int(11) unsigned      | YES  | MUL | NULL    |                |
| track         | int(11) unsigned      | YES  | MUL | NULL    |                |
| prop          | int(11) unsigned      | YES  | MUL | NULL    |                |
| sequence      | text                  | YES  |     | NULL    |                |
| location      | varchar(255)          | YES  |     | NULL    |                |
| type          | tinyint(1) unsigned   | YES  | MUL | NULL    |                |
| flagged       | int(11) unsigned      | YES  | MUL | 0       |                |
| status        | tinyint(1) unsigned   | YES  | MUL | 0       |                |
| featured      | tinyint(1) unsigned   | YES  | MUL | 0       |                |
| recommended   | tinyint(1) unsigned   | YES  |     | 0       |                |
| rendered      | tinyint(1) unsigned   | YES  |     | NULL    |                |
| fms_id        | varchar(32)           | YES  | MUL | NULL    |                |
| comments      | text                  | YES  |     | NULL    |                |
| tv            | tinyint(1) unsigned   | YES  |     | 0       |                |
| performers    | varchar(255)          | YES  |     | NULL    |                |
| comp_34_votes | int(11) unsigned      | YES  |     | 0       |                |
| comp_35_votes | int(11)               | YES  |     | 0       |                |
| comp_36_votes | int(11)               | YES  |     | 0       |                |
+---------------+-----------------------+------+-----+---------+----------------+

and the query that suddenly gets slow (if i remove the orderby it's not slow):

SELECT sql_no_cache `p`.`id` as performance_id, `p`.`performers`, `t`.`name` as track_name, `p`.`location`, `p`.`fms_id`
FROM (`performances` p)
JOIN `tracks` t ON `p`.`track` = `t`.`id`
WHERE (p.status = 1 OR (p.status != 2 && p.flagged < 3))
AND `p`.`prop` IN ('1', '2', '3', '4', '5', '6', '8', '10', '11', '13')
AND `p`.`track` IN ('17', '9', '5', '15', '2', '3', '8', '6', '12', '4', '1')
AND `p`.`type` IN ('1', '0', '2')
ORDER BY `p`.`created` desc
LIMIT 11, 12

and the explain:

+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                  | key          | key_len | ref                            | rows | Extra       |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | p     | index  | track,prop,flagged,status,type | created_desc | 5       | NULL                           |   45 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                        | PRIMARY      | 4       | db_123.p.track |    1 | Using where |
+----+-------------+-------+--------+--------------------------------+--------------+---------+--------------------------------+------+-------------+

The problem is not really index fragmentation. It is more associated with the index statistics. If you are using MyISAM, sudden spikes of INSERTs would throw the statistics off in the eyes of the MySQL Query Optimizer. This would cause the MySQL Query Optimizer to take horrific guesses within the EXPLAIN plans of SELECT queries.

If you are using InnoDB, ANALYZE TABLE becomes completely useless.

As long as the table is reasonably small, ANALYZE TABLE is all you can really do for MyISAM. Rebuilding indexes may help InnoDB tables periodically.

As long as tables are not heavily written, index statistics will remain stable and query EXPLAIN plans will remain more consistent.

Just remember: once you have tons of INSERTs, UPDATEs, and DELETEs, all bets are off for trustworthy index statistics until the next rebuild or ANALYZE TABLE.


Oracle DB 11g Restore Points I am posting here on the forum for the first time... Please feel free to point me to the correct section of the website, if "DBA" is the wrong one.

So I work as a Business Intelligence Consultant, but my current project role focuses more on various database development tasks. One of these (it is more of a housekeeping task) is removing all restore points from the database that are older than 30 days.

I am not very familiar with databases in general.

Could someone point me in the right direction?
Is there some way to achieve this using PLSQL/SQL?
For example, a loop that runs every day at a specific time and gets rid of too old restore points in the DB?

That depends:

Restore Points are nothing more than pointers to flashback logs in the FRA.  If you are creating these pointers via:create restore point <rpname>, then you can do something like:

begin
  for rp in (SELECT name FROM V$RESTORE_POINT where restore_point_time < sysdate - 30)
  loop
    execute immediate 'drop restore point ' || rp.name;
  end loop;
end;

However, if you are not explictly creating these restore points, and you are just concerned about space consumption and reclamation, then take a look at the FRA:

show parameter db_flashback_retention_target;
show parameter db_recovery_file_dest;
show parameter db_recovery_file_dest_size;

The relationship between these parameters is as follows:

The flashback logs will be stored in db_recovery_file_dest.

db_flashback_retention_target is how long (in minutes) you want to keep these flashback logs for.

However, the logs will only consume space until the size specified in db_recovery_file_dest_size is reached.

You can estimate how large to make db_recovery_file_dest_size with the following query:

SELECT ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 --in gigabytes
FROM V$FLASHBACK_DATABASE_LOG;

Therefore, if you want to keep flashback logs for 30 days, you would:

alter system set db_flashback_retention_target = 43200 scope=both;
--43200 = 1440 minutes in a day times 30

After some amount of time, check the estimated_flash_back_size in v$flashback_database_log, and size db_recovery_file_dest_size appropriately.  The flashback logs will be cleaned up automatically without the need for any sort of cleanup script.  When sizing the FRA, keep in mind that you may be storing other things in the FRA (like RMAN backups), which you can check with:

select * from  V$FLASH_RECOVERY_AREA_USAGE;

You can read up on restore points, flashback database, and the FRA from the Oracle docs here.


Max number of sequence objects At my job we have a lot of objects that we track versions of.  We are tentatively looking at creating a rather large number of sequence objects in order to keep track of these version.  My concern is that sql server may have a max number of these that we can create, but I am unable to find any such documentation.

So, is there a max number of sequences that can be created in SQL Server 2014 and 2016?

What is a Sequence?

  A sequence is a user-defined schema bound object that generates a
  sequence of numeric values according to the specification with which
  the sequence was created.

MSDN on Sequence definition reference.

Ok, it's a schema bound user defined object. What does SQL say about how many User Defined Objects we can have?

  Database objects include objects such as tables, views, stored
  procedures, user-defined functions, triggers, rules, defaults, and
  constraints. The sum of the number of all objects in a database cannot
  exceed 2,147,483,647.

MSDN on maximum UDF reference.

Summary answer: You can have 2,147,483,647 objects in a database. One of those objects referenced would be Sequence Objects which Sequences fall into that category. This is not explicitly stated, but it can be contrived from the wording "... the sum of the number of all objects in a database cannot exceed 2,147,483,647."


Error log flooding with aborted connections (Aurora 2.03 - MySQL 5.7.12) Here's some contextual information: I have a multi-tenant, WordPress MU installation running on Elastic Beanstalk, scaled up to 2 instances minimum. These are connected to the RDS database and are otherwise functioning correctly. Each WordPress install (tenant) has a database on a shared RDS cluster.

This doesn't happen in Aurora MySQL 5.6, which may be a clue, I just haven't been able to find an answer yet. A couple days ago I began upgrading to Aurora MySQL 5.7. I created a new cluster from scratch, and am importing individual databases for a gradual rollout in case there was any problems.

The RDS cluster has 1 primary instance and 1 read replica, and the error is occurring on both the primary and replica.

Here is a sample of the log:

2018-10-17T11:12:55.418509Z 34783 [Note] Aborted connection 34783 to db: 'unconnected' user: 'unauthenticated' host: '172.31.8.238' (Got an error reading communication packets)
2018-10-17T11:12:59.939953Z 34785 [Note] Aborted connection 34785 to db: 'unconnected' user: 'unauthenticated' host: '172.31.21.224' (Got an error reading communication packets)
2018-10-17T11:13:10.715438Z 34791 [Note] Aborted connection 34791 to db: 'unconnected' user: 'unauthenticated' host: '172.31.8.238' (Got an error reading communication packets)

The IPs are the EC2 instances so the connections aren't malicious as far as I can tell. I have the security group setup to only allow connections from the Elastic Beanstalk application.

I find db: 'unconnected' user: 'unauthenticated' to be pretty odd though.

The databases seem to be working fine but I'd like to resolve this before moving the rest of our clients over.

Please let me know if there's any other information I can provide, and thank you in advance for spending the time.

The aborted connections log messages are controlled by the log_warnings setting. In MySQL 5.6 it is set to 1 by default whereas in 5.7 the default is 2 (which is more verbose and shows connection messages)

You can compare the description of the settings for 5.6 vs 5.7

https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-warnings

https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-warnings

  Print out warnings such as Aborted connection... to the error log. This option is enabled (1) by default. To disable it, use --log-warnings=0. Specifying the option without a level value increments the current value by 1. Enabling this option by setting it greater than 0 is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. See Section B.5.2.11, “Communication Errors and Aborted Connections”.

As long as the defaults weren't changed I wouldn't expect to see the connection errors on the 5.6 server. You could try setting it to 2 and checking the log again: set global log_warnings=2;


Need to filter dimension where two attributes are the same I've obfuscated the scenario but the general idea still applies, so if the schema seems retarded, it probably is in this example...

Let's say I have a Student dimension, and in it are two attributes - Major and Minor (subjects).

I want to be able to create a Named Set in SSAS that gives me Students who have somehow been recorded as having the same subject for Major and Minor...

Dimension looks a little like this:

Student
  - Major
  - Minor

The underlying table looks something like this:

StudentID  |  MajorKey  | MinorKey
1             50          51
2             45          101
3             58          56
4             45          45

So, in effect, I want an MDX expression like this:

FILTER (
  [Student].allmembers,
  [Student].[Major] = [Student].[Minor]
)

... but I can't quite figure out the syntax. Any ideas?

UPDATE:

If I use this syntax...

FILTER (
  [Student].allmembers,
  [Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)

Then it slows down the ENTIRE cube for some reason. Every Measure (natural or calculated) takes several minutes as opposed to 1-2 seconds pre-cache.

I got it!

Basically the filter expression was correct, but I needed to Crossjoin the set what I wanted to filter against...

FILTER (
  CROSSJOIN([Student].[Major].[Major], [Student].[Minor].[Minor]),
  [Student].[Major].Properties("Key") = [Student].[Minor].Properties("Key")
)

I know I answered this quick but I've spent the best part of 10 hours on this today :P


SQL Server 2014 Express has reached database size limit. What is the quickest solution? My SQL Server 2014 Express database has reached database size limit.

What is the quickest solution to get back up and running? Is Express software the same as the full version, with just some limitations, or is it a complete different software?

i.e. Can I just go to Microsoft store online, order a key for full version, and type it in to the current Express to activate full features, or do I need to do full uninstall of Express, reinstall of full version, copy database and such?

My SQL Server 2014 Express database has reached database size limit.

Correct solution would be to upgrade it to version which does not have limitation on data file like developer, Standard and Enterprise depending on your requirement. If there is space in your database try shrinking it and see if that helps. But this is temporary solution in no matter of time you would again face space issue.

  Is Express software the same as the full version, with just some limitations, or is it a complete different software?

No its is not same as full version there are many differences and all is documented in Features Supported by the Editions of SQL Server 2014.

  Can I just go to Microsoft store online, order a key for full version, and type it in to the current Express to activate full features, or do I need to do full uninstall of Express, reinstall of full version, copy database and such?

You can perform an in place upgrade,you don't need to uninstall the express edition. Please see Supported Version and Editon Upgrade. You need license key mostly its embedded in new installation setup you get.