Will subquery (using IN) run multiple times for update statement (SQL Server 2017) Where an update statement is updating the value returned by a subquery - will the subquery be run for each row?

UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA WHERE DM = 1)

Column ID is not unique in the table so we can have multiple rows for an ID

Will it always update all rows for the ID to DM = 1 or sometime just one?

Will it always update all rows for the ID to DM = 1 or sometime just
  one?

If you don't have NULL values in the ID column then either all DM's for one type of ID != 1 and as a result all ID's that have the same value will be updated or none.

But as said previously, depending on nullability of the ID column you can have suprising results and performance implications.

More on NOT IN and NOT EXISTS here.

An example of spools being added & multiple table accesses as a result of using NOT IN

A cleaner way to write your query:

UPDATE a
SET a.DM = 1
FROM TableA a
WHERE NOT EXISTS
(
SELECT ID FROM TableA  b
WHERE b.DM = 1
AND b.ID = a.id );

Do note that NULL values will be updated as well, you would have to add extra logic such as adding AND a.id IS NOT NULL;.

Resulting cleaner join path:

Example of null values resulting in 0 updates happening:

CREATE TABLE dbo.TableA (id int,DM int);

INSERT INTO dbo.TableA(id,DM)

SELECT TOP 500
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) %200, 1
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.TableA(id,DM)

SELECT TOP 500
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) +200  , 0
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.TableA(id,DM)
VALUES(NULL,1),(NULL,0);

UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA WHERE DM = 1) ;

--> 0 rows affected

UPDATE a
SET a.DM = 1
FROM TableA a
WHERE NOT EXISTS
(
SELECT ID FROM TableA  b
WHERE b.DM = 1
AND b.ID = a.id )
AND a.id IS NOT NULL; --If you do not want to update null id's

--> (500 rows affected)


Drop an index in postgresql I had created an index wrongly and now I am trying to drop that index. Since the table is large, dropping the index is taking lot of time. Is there any other way to drop the index quickly?

You could try DROP INDEX [ CONCURRENTLY ] name

CONCURRENTLY

Drop the index without locking out concurrent selects, inserts,
updates, and deletes on the index's table. A normal DROP INDEX
acquires exclusive lock on the table, blocking other accesses until
the index drop can be completed. With this option, the command
instead waits until conflicting transactions have completed.
Here's the documentation for postgres 9.2:
http://www.postgresql.org/docs/9.2/static/sql-dropindex.html

Note: This feature is not available before postgres 9.2.

Here's the documentation for postgres 9.1:
http://www.postgresql.org/docs/9.1/static/sql-dropindex.html
This is similar to ONLINE = ON option in SQL Server:
http://technet.microsoft.com/en-us/library/ms176118.aspx


SQL Server Script to replicate recent inserted data to subscriber and to remove same data from publisher I want to replicate newly added data from publisher DB to subscriber DB.
When newly added data is replicated in subscriber dB the same data will be removed from publisher DB.
How to do this?

You can use a Temporal Table at the subscriber for this, although you need to use some custom initialization scripts to preserve the history table at the subscriber.
Then insert at the publisher and delete whenever you want.  Both the insert and delete will be replicated to the subscriber, and the history table will have the data after it's deleted from the main subscriber table.


PostgreSQL import CSV File causes Syntax Error I'm attempting to import a CSV file into a database via the "COPY" command; however, I get the (what seems common) error that I need to be a superuser and that I should use "\copy" instead. However, when using \copy, I get a syntax error:

ERROR:  syntax error at or near "\"
LINE 1: \copy

With the caret pointing to "\". Here's my query:

\copy tablename(column2, column3, column4, column5) from '/home/uploads/data.csv' WITH DELIMITER ',' CSV HEADER'

I tried both "copy" and "\copy". The first giving me a superuser error the latter giving me that syntax error. Any idea on how to fix it? make it work?

I'm executing the command via myPgAdmin's sql input field.

The only other question I have is concerning the importing of the columns via tablename(column2, column3 and so on. Is that the correct syntax for that?

\copy is a psql (commandline) command. It is not a regular SQL command.

You will need to use COPY instead (but that requires the file to be present on the database server)


Restoring database with less data than the backup file I'm not really experienced with DBA and SQL, so I need some recommendations from you.

I have a production database backup file, however it is very large, so when I'm executing restore, for example, making a copy of data in testing environment - it takes ages to restore.

I read this: Is a partial restore of data from backup possible?

It is similar to what I want to do, but not exactly equivalent.

To be clear, I need an answer to the following question: is there any possibility to restore database with fewer records than in the backup file?

For instance, if the backup file table contains 1 million records, I just want to restore only 1k records.

No, it is not possible to only restore n number of rows from a given table.  The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.

To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.


How does one create a Matrix Report to convert sql columns as rows? I'm trying to create a product listing. Naturally Sql prints the data vertically forming rows after rows like this:

Rowa. product_img#1|Product_name|Product_price|Product_short_description
Rowb. product_img#2|Product_name|Product_price|Product_short_description
Rowc. product_img#3|Product_name|Product_price|Product_short_description
Rowd. product_img#4|Product_name|Product_price|Product_short_description

and so on...

I'm trying to create a query so that I can display 3 products per rows like this:

Product_img#1|             Product_img#2|            Product_img#3|
Product_name|              Product_name|             Product_name|
Product_price|             Product_price|            Product_price|
Product_description|       Product_description|      Product_description|

Looking up the web I found that what i'm trying to do is called pivot table. It was suggested to me to use a service report like SQL Server Reporting Services and create a Matrix Report to print the data the way I want it.

To be honest, I have no idea how to use those services and where to start... This is totally gibberish to me. I'm using Netbeans joined with Mysql. Where do I install those services and how do I use them?

Is there such kind of service integrated in Workbench? Any help/tricks/tips on how to achieve this would be gladly appreciated!

The functionality you are looking for goes under the name UNPIVOT.  I'd suggest you have a good search on that subject and post a new question if you have further, specific question.


Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server This is driving me bananas.
According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and in the second it takes 35 SECONDS.
Here's the details.
I created test table named TEST with only two columns - LastName and FirstName.  It's a heap with no indexes of any kind.  It has exactly 1 Million rows and no nulls.
(I run DBCC DROPCLEANBUFFERS before each query)
When I execute "SELECT FirstName FROM Test WHERE LastName = 'Smith'" the execution plan shows a Table Scan and Statistics IO shows the total logical reads as 25,923.  The query takes less than 1 second to execute.
Then, I execute "SELECT * FROM Test" and again the execution plan shows a Table Scan and Statistics IO shows logical reads of 25,923.  BUT, this query takes 35 seconds to complete.
I've tried reversing the order of the queries, restarting the whole server in between queries, etc.  But, the results are always the same.
Unless those execution plans or statistics are lying in one of the cases, it makes no sense that there should be a 35x times difference.
How can I figure out what accounts for this difference?

Logical reads is only an indicator of how much data SQL Server reads from disk (or the buffer pool). There are a bunch of other factors that can contribute to a long-running query.
The most obvious suspect is how much time it takes to move the data from your server to Management Studio and/or the time it takes to render the data in a table. To eliminate those two, try inserting the results in a new temp table:
SELECT a, b INTO #new_table FROM #temp_table;

You can also set
SET STATISTICS TIME ON;

to see the amount of CPU and Elapsed times respectively.


Data files half empty in SQL Server. Is that a problem? I have a multiple TB database and I have been doing some clean-up and dropped many tables. So now the data files are half empty. If I don't care about releasing the space to the operating system, is there any other reason to shrink the files?
I am thinking that now at least I do not need to worry about auto-growth settings, which might slow things down if I add a large table with ETL.

No. Unless you need the space for something else it is not a problem at all. The unused space won't cause any extra CPU or memory use, empty pages won't consume space in backups, etc... As data grows in future it will use the empty allocated space instead of requesting more from the filesystem.
In fact shrinking can cause issues such as extra fragmentation, that you may end up spending time rebuilding indexes to resolve, so should be avoided unless needed.


How to make a function in postgresql with two parameters to return count from column table? I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year.
The table is something like this:
CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    status CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

I was able to return the count with the function below but when I give values to year and code in which there is no count it returns NULL but zero should be returned, how can I do that?:
CREATE OR REPLACE FUNCTION yearly_orders
    (year INT, order_cat orders.code%type) RETURNS INT AS $$

DECLARE
    total INT DEFAULT 0;

BEGIN
    SELECT COUNT(orders.code) INTO total
    FROM orders
    WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = year::INT AND orders.code = order_cat
    GROUP BY year, orders.code;
    RETURN total;
END;
$$LANGUAGE plpgsql;

The data is something like:
INSERT INTO orders
(id, code, service_id, status, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);

This would work:
CREATE OR REPLACE FUNCTION yearly_orders(_year int, _order_cat int)
  RETURNS int
  LANGUAGE sql STABLE AS
$func$
SELECT count(*)::int
FROM   orders o
WHERE  o.status = 'Requested'
AND    o.creation >= to_timestamp(_year::text, 'YYYY')
AND    o.creation <  to_timestamp((_year + 1)::text, 'YYYY')
AND    o.code = _order_cat;
$func$;

The conversion from integer to timestamp really depends on what you pass exactly and what that's supposed to mean. You might want to_timestamp(_year::text, 'YY') instead ...
Consider the list in Akina's comment.
Start reading the manual about about SQL functions.
Have a look at PL/pgSQL functions after that.
Overview:

Difference between language sql and language plpgsql in PostgreSQL functions


Slow insert - 300+ Million Records The following query takes over an hour on the table to insert.

There are no indexes. There was one which was deleted but still takes an hour after deletion.

Any pointers on improving this appreciated.

INSERT  INTO SAPECC_Full.dbo.[vbep]
    ( [MANDT] ,
      [VBELN] ,
      [VBELN_oco] ,
      [POSNR] ,
      [POSNR_oco] ,
      [ETENR] ,
      [ETTYP] ,
      [LFREL] ,
      [EDATU] ,
      [EZEIT] ,
      [WMENG] ,
      [WMENG_oco] ,
      [BMENG] ,
      [BMENG_oco] ,
      [VRKME] ,
      [LMENG] ,
      [MEINS] ,
      [BDDAT] ,
      [BDART] ,
      [PLART] ,
      [VBELE] ,
      [POSNE] ,
      [ETENE] ,
      [RSDAT] ,
      [IDNNR] ,
      [BANFN] ,
      [BANFN_oco] ,
      [BSART] ,
      [BSTYP] ,
      [WEPOS] ,
      [REPOS] ,
      [LRGDT] ,
      [PRGRS] ,
      [TDDAT] ,
      [MBDAT] ,
      [LDDAT] ,
      [LDDAT_oco] ,
      [WADAT] ,
      [CMENG] ,
      [LIFSP] ,
      [GRSTR] ,
      [ABART] ,
      [ABRUF] ,
      [ROMS1] ,
      [ROMS2] ,
      [ROMS3] ,
      [ROMEI] ,
      [RFORM] ,
      [UMVKZ] ,
      [UMVKN] ,
      [VERFP] ,
      [BWART] ,
      [BNFPO] ,
      [ETART] ,
      [AUFNR] ,
      [PLNUM] ,
      [SERNR] ,
      [AESKD] ,
      [ABGES] ,
      [MBUHR] ,
      [TDUHR] ,
      [LDUHR] ,
      [WAUHR] ,
      [AULWE] ,
      OcoDateChanged
    )
    SELECT  RTRIM([MANDT]) ,
            RTRIM([VBELN]) ,
            dbo.SAPLeading0s(VBELN) ,
            RTRIM([POSNR]) ,
            dbo.SAPLeading0s(POSNR) ,
            RTRIM([ETENR]) ,
            RTRIM([ETTYP]) ,
            RTRIM([LFREL]) ,
            RTRIM([EDATU]) ,
            RTRIM([EZEIT]) ,
            RTRIM([WMENG]) ,
            dbo.SAPNumeric(WMENG) ,
            RTRIM([BMENG]) ,
            dbo.SAPNumeric(BMENG) ,
            RTRIM([VRKME]) ,
            RTRIM([LMENG]) ,
            RTRIM([MEINS]) ,
            RTRIM([BDDAT]) ,
            RTRIM([BDART]) ,
            RTRIM([PLART]) ,
            RTRIM([VBELE]) ,
            RTRIM([POSNE]) ,
            RTRIM([ETENE]) ,
            RTRIM([RSDAT]) ,
            RTRIM([IDNNR]) ,
            RTRIM([BANFN]) ,
            dbo.SAPLeading0s(BANFN) ,
            RTRIM([BSART]) ,
            RTRIM([BSTYP]) ,
            RTRIM([WEPOS]) ,
            RTRIM([REPOS]) ,
            RTRIM([LRGDT]) ,
            RTRIM([PRGRS]) ,
            RTRIM([TDDAT]) ,
            RTRIM([MBDAT]) ,
            RTRIM([LDDAT]) ,
            dbo.SAPDate(LDDAT) ,
            RTRIM([WADAT]) ,
            RTRIM([CMENG]) ,
            RTRIM([LIFSP]) ,
            RTRIM([GRSTR]) ,
            RTRIM([ABART]) ,
            RTRIM([ABRUF]) ,
            RTRIM([ROMS1]) ,
            RTRIM([ROMS2]) ,
            RTRIM([ROMS3]) ,
            RTRIM([ROMEI]) ,
            RTRIM([RFORM]) ,
            RTRIM([UMVKZ]) ,
            RTRIM([UMVKN]) ,
            RTRIM([VERFP]) ,
            RTRIM([BWART]) ,
            RTRIM([BNFPO]) ,
            RTRIM([ETART]) ,
            RTRIM([AUFNR]) ,
            RTRIM([PLNUM]) ,
            RTRIM([SERNR]) ,
            RTRIM([AESKD]) ,
            RTRIM([ABGES]) ,
            RTRIM([MBUHR]) ,
            RTRIM([TDUHR]) ,
            RTRIM([LDUHR]) ,
            RTRIM([WAUHR]) ,
            RTRIM([AULWE]) ,
            GETDATE()
    FROM    vbep_full

You are asking SQL Server to perform 19.5 Billion string manipulations (300,000,000 rows X 65 columns), all in one giant transaction.  That is assuming vbep_Full is a regular table with simple column definitions (not a view with joins to other tables and/or with computed comlumns).

If your CPU is not maxed out, you could try breaking the work into multiple parts, process each part in parallel.

I would also recommend working in small batches with implicit transactions.  That way if one of the batches fails, you could start that batch over again without having to start the other batches over.


How to model a 1..1 Composition Relationship We have a couple of entities/relationships of the following kind:

Entity Event has a TimePoint
The TimePoint contains time information about the event. It belongs to that event and cannot be used by any other entity. Without the corresponding Event, the TimePoint has no meaning, it is orphaned. In short, it looks like a composition to me.
The Event, in turn, must have a TimePoint. There is no event without a time. It depends on the TimePoint.

The question I am facing is, which entity has the foreign key to the other entity?

Option 1:

FOREIGN KEY (Event.TimePointID) references TimePoint.ID

where as Event.TimePointID NOT NULL

Problem: When the Event is deleted, the TimePoint is left orphaned.

Option 2:

FOREIGN KEY (TimePoint.EventID) references Event.ID

where as TimePoint.EventID NOT NULL

Problem: When the TimePoint is deleted, the Event becomes invalid and makes the application crash.

I think this is a pretty common issue and there must be a good old DBA's advice to how to model such a relationship...?

Update: The example above is simplified to only represent the relationship in question. One reason why we need two separate entities is because a TimePoint can be owned by a record from a different table instead of an Event too. But it must always be owned by exactly one record from exactly one of either table.

Personally I would put the TimePoint attributs into the event table. But if you prefer two tables, you should be able to achieve that with foreign keys together with NOT NULL columns.

But that will only work if you have a database that supports deferrable constraints (Oracle and PostgreSQL come to mind), otherwise there is no way of inserting new values (unless you want to have some "dummy" TimePoint row - which is really a hack - I'd definitely prefer a single table solution over a "magic row").

I don't think you need separate columns for the FK columns, as both tables are so tightly coupled.

Something like:

create table event
(
   event_id integer not null primary key,
   ... other columns ...
);

create table timepoint
(
   event_id integer not null primary key,
   ... other columns ...
);

alter table event
    add foreign key (event_id)
    references timepoint (event_id)
    deferrable initially deferred;

alter table timepoint
    add foreign key (event_id)
    references event (event_id)
    on delete cascade
    deferrable initially deferred;

insert into event (event_id) values (1);
insert into timepoint (event_id)  values (1);
commit;

insert into event (event_id) values (2);
insert into timepoint (event_id)  values (2);
commit;

-- This will delete the timepoint as well!
delete from event
where event_id = 1;

The inserts work because the FK constraints are deferred. The ON DELETE cascade for the FK between timepoint and event ensures that the timepoint is deleted when the event is deleted. Deleting a timepoint is not possible because that constraint is not defined as cascading. If you want to delete the associated event when the timepoint is deleted, define that constraint as cascading as well.

(The above example works in PostgreSQL and should work in Oracle too)

But again: I'd put everything into the event table.


MongoDB Non Sharded Environment We are planning to setup non-sharded MongoDB Instance on Amazon AWS. As per my understanding we need MongoD setup on Primary and Secondary to forms as a replica set. Please advice if we need MongoS,Config Server & Arbiters

Please read the replication documentation before you go into production. Make sure you have understood them.

As per your questions:

You don't need config servers, as they are only needed for sharded environments
You don't need mongos instances, as they are only needed for accessing shards.
You only need an arbiter if you have an even number of data bearing replica set members, as the elections for a primary need a qualified majority with a quorum of 50% to elect a new primary.

Again, please read the docs. As you are obviously inexperienced with running MongoDB, it might be worth to have a look at the a MongoDB provider such as MongoLab or Compose (former MongoHQ). They are not necessarily more expensive (quite the contrary in some use cases) and you don't need to administer and monitor MongoDB yourself. Disclaimer: As a MongoDB DBA, I do not have first hand experience with either of them.

Some notes on running a non sharded environment: Please keep in mind that the space an EBS volume can provide is limited. So if there is any chance that you exceed 1TB of data (including indices, oplog and journal), you should think of using a sharded cluster from the beginning in order to prevent the need of interrupting your services when the need of scaling arises.


Send email when certain Data is being inserted in MS SQL Server I realize that there is an integrated email notification system in SQLServer, where I can configure it to send an email using my organization's Exchange server.

I want to use that to send email on the event that certain data is added, for example if I have a table with a column called 'error no' in it; I want to be notified via email when a certain error no. is being added.

e.g. if error no is 5, I want to be sent an email via the notification system. If the error no is 4, then insert it to table with no notification.

I would love to be able to do it without writing any stored procedure or T-SQL, but if I have to, then I have no problem doing it.

Thank you guys.

The quickest solution I can think of would be to create on your specific table an after insert trigger where, based on your conditions (the error no..etc), you'd call the system procedure msdb.dbo.sp_send_dbmail to send a custom email.

You'd have first to configure the mail (create a profile and test it). I don't really think there's a simpler way.

I think that you could create an alert based on a custom error number. But this means that you'll have to modify your current code that inserts data in that table to throw this custom error no, to be able to see the alert.


Machines using the SQL Authentication I have a requirement. The leadership wants an email list of all the users hitting the database and last access date. I have created the script that has the username and the time accessed. The issue is with SQL authentication. SQL authentication is only given to applications and the application accesses the database. The leadership wants me to design something that goes in and finds out the Active directory username for this SQL authentications hitting the database. I personally do not think this is achievable in SQL however, I would like to take the opinion of the community. Please advise.

As you identified in your question, it is not possible to reliably discern which Windows user is logged into a SQL Server using a SQL Server login.  You could track the name of the workstation where the user connects from, but that doesn't guarantee anything - the workstation name is easily spoofed.

If you require that level of auditing, you'd need to build it into all applications accessing the database.


Update statistics more often and stop defragmenting indexes I have several vendor databases that have most (99%) of the tables clustered on GUIDs. Currently we are rebuilding the tables every weekend. Most tables are 80% or more fragmented by this time.

Instead of this endless cycle, would we be better off just not rebuilding?

Would it be more beneficial to update statics more often instead of rebuilding the indexes?

I have read Stop Worrying About SQL Server Fragmentation by Brent Ozar, but it doesn't really give an answer to my question. I was hoping for a more complete article and possibly one that was updated more recently.

I am trying not to do this as I know it is not necessary. If it helps to understand my situation better please see the beginning part of this article again from Brent's blog. I would like to have a formal plan with articles and use cases for this to propose to management and go through the proper documentation before making the change.

I am working to design a new maintenance plan for our production databases. Our only maintenance is the weekly rebuild. There are several (30+) databases that are all about 700 GB-1.2 TB. The tables range from 100-300 GB in size for the largest.

I would like to have a better maintenance plan that provides better performance in the end.

Statistics are used by the Query Optimizer to help estimate row counts for operations so that it can determine the most efficient means of accomplishing that operation.

Since you are using GUIDs as the Primary Key in these tables, it is highly doubtful that you are doing any range operations / multi-row operations. Since GUIDs have no inherent order, you are really only ever doing single-row operations. And since these are Primary Keys, they are "unique" and any value only exists once, or not at all, hence your operations are either going to affect 1 row when filtering on the PK, or all rows if not filtering on anything (I am ignoring other filters for the moment since those would use statistics for another index, or an auto-created statistic on a column, neither of which are impacted by this particular issue). In this sense, merely updating statistics isn't going to help much (or at least shouldn't).

At the same time, since operations against these PKs are singleton operations (i.e. seeks), those are not greatly impacted by fragmentation. In this sense, rebuilding the Clustered PKs is not going to have a terribly noticeable affect on those operations. HOWEVER, rebuilding (at least occassionally) still has merit.

So my advice is this:

The easier but less overall effective path:

Since your system is currently suffering from a rampant page-split debacle, you need to free up that wasted / unused space as there is a lot of wasted space across many data pages that are not only filling up the Buffer Pool, but its also making backup/restore operations take longer. So, try doing index REORGANIZE operations for a few weeks and then a REBUILD. I'm not sure what Edition of SQL Server you are using, but if not using Enterprise then REBUILD operations are offline, yet REORGANIZE operations are online.

Doing online operations not only reduces the need for the maintenance window, but they can be done more often throughout the week. You can even stagger which indexes get rebuilt when and perhaps pick the top N index, based on fragmentation level, and REORGANIZE a certain number of them each night.
The harder but much more effective path:

Even if the REORGANIZE and occasional REBUILD plan helps, you are still really just using a bucket to scoop water out of a boat that has a hole in it. But your system is growing and so that "hole" is getting bigger. All of this REBUILD / REORG stuff is merely an effort to live with a bad situation. It might work for a while, it might even work forever, but there could easily come a point when it does not work. The absolutely better approach is to actually fix the bad situation, not just sweep it under the rug.

That absolutely better approach is to remodel the tables to use INT / BIGINT columns for the Clustered PKs. If the app code uses the GUIDs, or if the GUIDs are known by external systems and hence referenced by external systems, then you can still do this major modification: just keep the GUID values in one table, create a NonClustered Index on the column, and then do a lookup on those values at the beginning of operations to translate them to the internal INT / BIGINT values. This will shrink the size of all but a few tables (especially when considering that NonClustered Indexes have the Clustered Index keys duplicated in them, hence most of your NonClustered Indexes are 16 bytes larger than the sum of the index keys, when they could be just 4 or 8 bytes larger if the Clustered Key was INT or BIGINT, respectively). Smaller tables take up less space in the Buffer Pool, hence pages stay cached for longer and queries are more efficient. Backup and restore operations are also faster. Index REORG and REBUILD operations are also faster.

I have suggested approach #2 before to you in this answer:   Best fill factor for GUID clustering key. So, while approach #1 might help, I don't think your situation will drastically improve until moving forward with approach #2. And I do fully understand that it is much easier said than done since I don't have to deal with time / resource constraints and other complicating factors. BUT, I will say that if management is not keen on pursuing such a large project, they need to consider the value-proposition of having a faster system, spending less money on disk space (data files, log files, and backups), reduced down-time / maintenance window, reduced recurring support time due to you needing to "fix" this ever-worsening situation at least once per year, etc, etc, etc. In the long-term, this approach is actually cheaper. This is your best "maintenance plan" that provides for the best performance :-). And it can be accomplished in phases, so it doesn't have to be done in one massive release.


How to deal with multiple error columns in data flow I'm writing a simple package to load data into tables and a I have multiple destinations within the same package. I'm attempting to first attempt batch inserts but want to fall back to row by row in case an error is encountered. When the error is encountered in the row by row insert, I'd like to kick that out to an error table. My architecture looks like this:

However, I get an error in my Union component because there are multiple ErrorCode and ErrorColumns coming from each flow, one from the batch insert and one from the row by row insert.

I'm attempting to union the error output together in order to not have to duplicate any logic I want to do with my error handling.

What's the best solution for dealing with this? Is there a way to remove columns from a data flow?

Figured it out shortly after posting.

Although SSIS doesn't allow you to delete the error columns in the error output of the first insert, it does allow you to change the names. I was able to modify the names of these columns and then the Union does not throw any errors as the names are not in conflict anymore.


mysql - insert multiple values sharing same foreign key I have the following tables:

create table countries (
    country_id tinyint unsigned,
    iso_alpha3_code varchar(4)
);

create table provinces (
    province_id bigint unsigned,
    province_name varchar(50),
    country_id tinyint unsigned,
    foreign key (country_id) references countries(country_id) on delete restrict on update cascade
);

For inserting records into provinces, the corresponding country_id is needed:

insert into provinces (province_name, country_id) VALUES
    ('Alabama', (select country_id from countries where iso_alpha3_code = 'USA')),
    ('California', (select country_id from countries where iso_alpha3_code = 'USA')),
    ('Alaska', (select country_id from countries where iso_alpha3_code = 'USA'));

How to insert these records without repeated-select statements to get the foreign key? Can something like CTE be used?

PS: I found this, but it's for postgreSQL.

insert into provinces (province_name, country_id)
SELECT t1.province_name, t2.country_id
FROM ( SELECT 'Alabama' province_name UNION SELECT 'California' UNION SELECT 'Alaska') t1
JOIN (select country_id from countries where iso_alpha3_code = 'USA') t2;


TFS2010 for SSMS 2012 SSMS 2012 does not work with VSS 6. We installed modern source control, TFS 2010 server.

Then I Installed Team Foundation Server MSSCCI Provider 2010.
However the only choice for Source control  plug-in in SSMS 2012 is still  VSS6.
My VS2010 works just fine, I could connect to TFS in a team explorer.

How to add TFS as a second source code control plugin in SSMS 2012?

To use SQL Server Management Studio 2012 with Team Foundation Server (TFS) source control, you need to first install the Team Foundation Server MSSCCI Provider 2010 32-bit or Team Foundation Server MSSCCI Provider 2012 32-bit.

Then you will be able to select TFS as one of the source control providers under: Tools > Options > Source Control > Plug-In Selection.

Note that one of the pre-requisites to using the MSSCCI Provider is that you also have Visual Studio 2010 Team Explorer (the TFS client) installed on your machine. This is because it installs the TFS Object Model assemblies that the MSSCCI Provider uses to talk to TFS.


Providing an ad-hoc report facility for business using in MySQL and PHP? We are using MySQL 5.2 CE. We need to provide the business users a way to create their own ad-hoc report.

Could you give a suggestion and an example, if this can de done using PHP?

I suppose you could wire up some gnarly PHP code that queries INFORMATION_SCHEMA for database objects and then presents them to your end-users using some glamorous jquery and HTML5 elements. Then you'd have to generate SQL from the user's input.

Or, you could go through a bit of effort to implement/deploy BIRT to your user base.

However, my personal preference for doing ad-hoc querying would probably be Pentaho. There's still the overhead of deploying this to your users, but I find their documentation and implementation to be straightforward and easy-to-follow. Here's a link to using Ad-Hoc reporting in the Pentaho suite.


How to use 'Bind Variables' in a 'Dynamic Query ' when the exact number of variables are not known I have a procedure in which I'm using Dynamic SQL(variable actual_query) and I'm generating this query based on the input parameter i_tables which is a concatenation of the name of some tables. It has one of these forms:

All tables `test_table1,test_table2.

Nothing. So NULL will pass to the procedure.

I've read about Bind variable and the significant role it has in preventing injection and improving performance and I want to use it in my procedure.The problem is that I do not know how exactly I should change my procedure to use them specially in this situation when number of variables are not known. When you know the exact number of variables, you write execute immediate actual_query using var1,var2 and you know you will exactly have 2 variables.
create or replace procedure bind_variable_test(i_tables varchar2,
                                               i_cid    number,
                                               o_result out sys_refcursor) is
actual_query varchar2(1000) := '';
begin

-- this is the base query
actual_query := 'select *
            from z_test_a t1
              inner join z_test_b t2
               on t1.id = t2.id';

-- check input parameter " i_tables "
if i_tables like '%test_table1%' then
  actual_query := actual_query || ' ' || 'inner join test_table1 t3 on ' ||
                     't3.id = t1.id ' || 'and t3.cid = ' || i_cid;
end if;

if i_tables like '%test_table2%' then
  actual_query := actual_query || ' ' || 'inner join test_table2 t4 on ' ||
                     't4.id = t1.id ' || 'and t4.cid = ' || i_cid;
end if;

-- debug results
dbms_output.put_line(actual_query);

-- execute
open o_result for actual_query;
end;

After reading DBMS_SQL manual , I tried to edit my code and I did this:
create or replace procedure z_bind_variable_test(i_tables varchar2,
                                             i_cid    number,
                                             o_result out sys_refcursor)       is
actual_query varchar2(1000) := '';
c            pls_integer;
begin
-- this is the base query
actual_query := 'select *
              from z_test_a t1
               inner join z_test_b t2
            on t1.c_num = t2.c_num ';

-- check input parameter " i_tables "
if i_tables like '%test_table1%' then
  actual_query := actual_query || ' ' || 'inner join test_table1 t3 ' ||
                'on t3.C_NUM = t1.C_NUM ' || 'and t3.cid = :c_id';
end if;

if i_tables like '%test_table2%' then
  actual_query := actual_query || ' ' || 'inner join test_table2 t4 ' ||
                'on t4.C_NUM = t1.C_NUM ' || 'and t4.cid = :c_id ';
end if;

-- get cursor
c := dbms_sql.open_cursor();

-- parse the SQL
dbms_sql.parse(c, actual_query, DBMS_SQL.NATIVE);

-- using bind variables
dbms_sql.bind_variable(c, ':c_id', i_cid);

-- execute
o_result := dbms_sql.execute(c);

-- close the cursor
dbms_sql.close_cursor(c);
end;

But it just won't work! I want to learn how to use bind variables correctly but don't know whether I'm in the right path.
Update : The error I get is PLS-00382:Expression is of wrong type. And the problem is for this part o_result := dbms_sql.execute(c);

split the variable and fetch each element. That is, use a cursor


SQL Server 2016 Express, need to add account with Administrator access to a database Working with a new SQL Server 2016 Express installation. I loaded SSMS and have Administrator access to the instance. The vendor supplied a database for a new app that has only a couple internal accounts for their app defined in the database's Security. I only have public access to the database at this point- I can look at properties, but cannot add accounts to the database.

Is there a way to add my administrator account to this database's Security to gain admin access? SQL downtime is not an issue. I did some searching and found a handful of hits for regaining access to the instance, but haven't found any that address adding a new account at the database level.

If you are a Windows Administrator, follow the procedure documented here.

Or simply run this script from an elevated command prompt on the SQL Server:

net stop mssql$sqlexpress
net start mssql$sqlexpress /mSQLCMD
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'"
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

Source


Calculate ROI for date range I have a SELECT with calculated columns and Im doing some filtering on it.

The issue Im having with ROI where Im calculating average for the date range, which is incorrect as ROI needs to be calculated from actual revenue and costs (ie. ROI = (revenue-costs/costs)*100 )

So the question is, how I can calculate the correct ROI?

SELECT
                 c.campaign_name,
                 c.traffic_source_name,
                 cvr.custom_variable,
                 SUM(cvr.visits) as visits,
                 SUM(cvr.clicks) as clicks,
                 AVG(cvr.ctr) as ctr,
                 SUM(cvr.conversions) as conversions,
                 SUM(cvr.cost) as cost,
                 SUM(cvr.revenue) as revenue,
                 SUM(cvr.profit) as profit,
                 ROUND(AVG(cvr.roi), 2) as roi,
                 AVG(cvr.ap) as ap
                 FROM custom_variable_report as cvr
                 INNER JOIN campaigns as c
                 ON c.campaign_id = cvr.campaign_id AND c.date = cvr.date
                 WHERE cvr.campaign_id = 'b881eb19-8a47-4a60-9373-b0981006733a'
                 AND (cvr.date BETWEEN '2017-03-03' AND '2017-03-09')
                 GROUP BY cvr.custom_variable
                 HAVING ABS(profit) > 2.51*1
                 AND roi < -25
                 ORDER BY cvr.profit ASC

In this case if I have 2 data points: -20% and 100% the average is: 40% which is incorrect as ROI needs to be reflected from the revenue and costs instead for each row.

Unless I'm missing something, wouldn't it be:

ROUND((SUM(revenue) - SUM(cost)) * 100.0 / SUM(cost), 2) as roi


Exported CSV from MySQL doesn't appear in specified directory I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying:

 SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv'
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '\"'
 LINES TERMINATED BY '\r\n'
 from <table> limit 5;

The query itself works fine, but the file doesn't appear on the desktop. What should I change to get it to show up?

In windows use \\ or / while using in path of file. your command should be as follow

  SELECT * INTO OUTFILE 'C:\\Users\\Jim Smith\\Desktop\\zyzyz.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\r\n'
  from  limit 5;

or

  SELECT * INTO OUTFILE 'C:/Users/Jim Smith/Desktop/zyzyz.csv'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\r\n'
  from  limit 5;

Hope it helps


Processing Dimension completed but error occurs I have problem while processing cube in SSAS, everytime I have an error:

I tried to process every dimension separately and it looks like something bad was going on after processing 'Date' dimension.

Any ideas why?

Best regards,
Ana

If you take a look at your dimension table and run a query like the following:

Select count(suspect_column) as count_suspect_column, suspect_column
from Dimension_Date
GROUP BY suspect_Column
HAVING COUNT(suspect_column) > 1

It is likely that there is a duplicate value causing the process to fail. These values in that column need to be unique for the process to work. Also worth noting is that SSAS treats nulls as empty strings according to this excellent article:
https://www.sqlservercentral.com/articles/ssas-error-duplicate-attribute-key-found-when-processing


How to alter primary key constraint for 2 columns in same table in SQL Server? In SQL Server, I already created a table called tblDatabase with some columns. I want to add a primary key on the two columns DatabaseName and Servername.

I tried to add:

ALTER TABLE tblDatabase ADD CONSTRAINT pk_DatabaseServer
                            PRIMARY KEY (DatabaseName, ServerName)

in check constraints but I get pop up error that say

  error validating constraint 'ck_tblDatabase'.

Did I miss something?

I also have ServerName as PK in tblServer that already relationship with tblDatabase. Is that the reason why?

Don't enter this into the check constraint dialogue box. That only allows you to enter expressions for check constraints.

To create your primary key constraint you should just execute your TSQL ALTER TABLE script in a new query window.

To create this using the visual tools you would need to use the indexes/keys dialog or in the table designer view select the two columns and choose "Set Primary Key" from the shortcut menu.

I would avoid this last approach for a composite PK however as AFAIK it gives you no way of controlling the order that the index keys will be defined in.


Why does Cassandra recommend against creating an index on high-cardinality columns? The Cassandra documentation states,

  Do not use an index in these situations:

  On high-cardinality columns because you then query a huge volume of records for a small number of results. See Problems using a high-cardinality column index below.

It goes on,

  If you create an index on a high-cardinality column, which has many distinct values, a query between the fields will incur many seeks for very few results. In the table with a billion songs, looking up songs by writer (a value that is typically unique for each song) instead of by their artist, is likely to be very inefficient. It would probably be more efficient to manually maintain the table as a form of an index instead of using the Cassandra built-in index. For columns containing unique data, it is sometimes fine performance-wise to use an index for convenience, as long as the query volume to the table having an indexed column is moderate and not under constant load.

But never really answers the question: why is it inefficient? I have no idea what "manually maintaining the table as a form of an index" means. But then it somewhat contradicts itself with "…it is sometimes fine performance-wise to use an index for convenience as long as the query volume is moderate…"

Is this just trying to tell me to use the PK when and where I can? What's the inefficiency? My understanding is that a query that would hit an index would need to query every¹ node in the cluster, and then each node would do a lookup in its local index and the results would then get aggregated. This is not necessarily expensive (each index lookup should be fairly cheap) except that we pay in network latency, as we must wait for the slowest node of the lot. Am I missing anything here?

But if I have a collection that has a bajillion items that — on rare occasion — needs to be looked up by a different but almost unique attribute … this is an appropriate use, right?

¹Every? IDK if replication means that this can hit 1/3 of the cluster for a replication factor of 3 or not?

With a Cassandra index (i.e. a "secondary index", as opposed to primary keys), each node has to query its own local data for responding to a query (see the Cassandra secondary indexexes FAQ).  These index are also built using a background process.  This backgrounding means that the index may return false negatives in terms of hits (or false positives in terms of misses).

This means that in a high-cardinality column, the rate of change (i.e. additions/deletions) from that column can be quite high.  And thus if that rate of change is faster than the updating of the index via the background process, then using an index is "inefficient" (the index is performing more work than is needed by the application, which might often get the wrong answer).

A more efficient approach, in terms of query accuracy, might be to maintain a second table, rather than a secondary index.  Tables, as opposed to indexes, are treated just like any other table.  They are more likely to give your application the query results it expects.  The downside are that maintaining a table as an index, versus a Cassandra "secondary index", are now application constraints (i.e. your application code now has to know to insert/delete rows from that "index" table, and to keep the two tables in sync via application-level "reconciliation").

Hope this helps!


Database structure setup We're currently in the process of moving our hosting services to something which will allow us to scale our infrastructure and requirements.

Essentially we're using MySQL to:

Run our websites.
Run our blog
Store millions of rows which will require some heavy queries, but will be cached afterwards.

Our current idea is to setup two MySQL servers, on a master to master replication, with HAProxy on top to load-balance the requests.

As we've never dealt with replication before our main concerns are the following:

How quickly does replication work?
What happens if one of the servers go down? Would we change HAProxy to use just the one server?
How does replication deal with loading and replicating millions of rows each day? This would happen once a day.

Thanks for any help!

In general, actively writing to multiple masters in MySQL is a bad idea.

MySQL Replication is asynchronous, which means there's no guarantee the changes make it to the slave before the changes are committed on the master.

The implication is that something™ (node dies, slave lag, etc) can/will go wrong with one of the masters at any time. If your application and business rules can't handle this appropriately, you are in for some pain.

To answer your bullet points:

"How quickly does replication work?"

As fast as your server resources allow: network/IO/cpu/memory.

"What happens if one of the servers go down? Would we change HAProxy to use just the one server?"

Really depends on your HAproxy config, but a typical config would handle it automatically.

"How does replication deal with loading and replicating millions of rows each day? This would happen once a day."

Depends mainly on your hardware and configuration, and the answer here is: try it out. I've seen many GB loaded with minimal lag, and I've seen it cause hours of lag to apply on the slave. The real question is: can your application handle lag that arises from this, whether it's seconds or hours? Remember, slave applying is serial (until 5.6 with multiple slave threads), so lag would affect all tables until it's caught up.


Product attribute schema design, mixing solutions I am working on a product database in MySQL and the products can be multiple types with different attributes. The number of attributes and there datatype vary. I read about some design patterns (post) used in this situation, but none of them looks promising. I am thinking about mixing them somehow.

Lets say, I have these product types (D - dimension, but could be attribute or entity...):

Product_type1: D1 - int, D2 - int, D3 - string, D4 - date, D5 - string

Product_type2: D1 - int, D2 - string, D3 - date, D4 - int, D5 - string, D6 - int

Solution 1:

I store a fix number of attributes of every datatype for a product, let's say 3 int field, 2 date, 2 string. The most important attributes stored here (important regarding indexing and searching). The rest of the attributes are stored in a JSON field or an array format.

Solution 2:

Store everything in indexed JSON field and search in the table with 'LIKE' queries.

How fast is a Like query in MySQL when you have a big product database (20 000 - 30 000 products). Is solution 1 better or not?

Put common things (price, SKU) in MySQL columns; index them if needed.

Put the rest in a JSON column.  Do not try to use LIKE to search that.

Instead, first filter on what you can from the other columns, then use your application language to finish the filtering.

To save on space and bandwidth, compress (in the client) the JSON string and put it into a BLOB column.

Write your user-interface to encourage them to filter on the real columns, only reluctantly let them specify the other things.

See also MariaDB's "Dynamic Columns; it's been available for several releases.  In the future, 5.7 promises to have a bunch of JSON tools.


MySQL locking in Duplicate Key Error From the docs:

  If a duplicate-key error occurs, a shared lock on the duplicate index
  record is set. This use of a shared lock can result in deadlock should
  there be multiple sessions trying to insert the same row if another
  session already has an exclusive lock. This can occur if another
  session deletes the row.

Going  with the example in the docs,

Suppose that an InnoDB table t1 has the following structure:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations in order:

Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

  The first operation by session 1 acquires an exclusive lock for the
  row. The operations by sessions 2 and 3 both result in a duplicate-key
  error and they both request a shared lock for the row. When session 1
  rolls back, it releases its exclusive lock on the row and the queued
  shared lock requests for sessions 2 and 3 are granted. At this point,
  sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for
  the row because of the shared lock held by the other.

I have some questions :

1) The insert query takes an exclusive lock on the row it is inserting. So, suppose T1 is inserting on row 1, it will lock row 1. Now when T2 comes to write, will INNODB evaluate the query before executing it and find out that it is going to write the same PK (row with i = 1) and make T2 wait? Or will it start execution of T2 and find that it gives duplicate key error or PK violation.

2) Why are T2 and T3 taking shared locks? How do shared locks come into picture during insert?

I did a small simulation of that situation, in a simple bash script that does that:

➜  rsandbox_5_6_30 cat test.sh
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select  sleep(3); rollback;" test 2> tx1 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx2 &
./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx3 &

./m -e "SHOW ENGINE INNODB STATUS\G" test > istatus

Result:

➜  rsandbox_5_6_30 cat tx1
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx2
ERROR 1062 (23000) at line 1: Duplicate entry '6' for key 'PRIMARY'
➜  rsandbox_5_6_30 cat tx3

Transaction status:

---TRANSACTION 265035, not started
MySQL thread id 4, OS thread handle 0x700000b0b000, query id 204 localhost msandbox cleaning up
---TRANSACTION 265017, not started
MySQL thread id 3, OS thread handle 0x700000ac7000, query id 171 localhost msandbox cleaning up
---TRANSACTION 265041, ACTIVE 0 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 31, OS thread handle 0x700000ca3000, query id 210 localhost msandbox User sleep
select sleep(5)

If you execute the first transaction within certain delay, you can incur on a frame where one of the subsequent transactions is killed by deadlock detection, making the other transaction to insert the value.

The status of the alive transaction reflects that still needs an explicit commit (undo log entry 1).

-- Edited 2nd

"Neither can acquire an exclusive lock for the row because of the shared lock held by the other."  Actually the deadlock kills only 1 transaction, not both. So the EL is acquired by the 2nd session.

Due to how gap locking works we see the difference between executions. The deadlock happens as an intention of the 3rd transaction to acquire the shared lock when a duplicate key error is detected. When this happens, the 2nd transactions has the shared lock acquired, producing a deadlock and killing the 3rd transaction.  This is a limitation in the amount of concurrent writes over the same record.


Query for a table with paging and filtering vs. CTE (common table expression) Intro

In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed information, some information from associated tables (eg. past purchases) etc. A lot of data is being fetched, and in the simplest form the query looks like this:

SELECT
  customer.name,
  customer.surname,
  (SELECT aggregate_whatever(foo) FROM bar) past_purchases,
  baz.aaa bbb,
  gaz.ccc ddd,
  ...
FROM
  customers customer
LEFT JOIN
  baz ON ...
LEFT JOIN
  gaz ON ...

There are several thousand of customers and the additional data being fetched comes from most of the system.

Filtering, paging and sorting

In the end we will have to put past_purchases into the WHERE clause when building the query according to the filter. For this reason, the whole query is encapsulated as a CTE (common table expression), just like in this SO question. It looks like this:

WITH encapsulated AS (
  SELECT
    customer.name,
    customer.surname,
    (SELECT aggregate_whatever(foo) FROM bar) past_purchases,
    baz.aaa bbb,
    gaz.ccc ddd,
    ...
  FROM
    customers customer
  LEFT JOIN
    baz ON ...
  LEFT JOIN
    gaz ON ...
)
SELECT * FROM encapsulated
WHERE
  past_purchases = 5
  AND <other conditions>

That ultimate SELECT will also have an ORDER BY clause for the necessary columns. For paging purposes, a LIMIT is added at the end, and the number of total rows is calculated as follows:

...
SELECT *, COUNT(*) OVER () as total_row_count
FROM encapsulated
WHERE
  past_purchases = 5
  AND <other conditions>
ORDER BY
  surname, name
LIMIT 0, 10

Problem

This solution works but quickly runs into performance problems. All the tables have proper indices and EXPLAIN (ANALYZE) shows a nice yet costly plan with indexed accesses, yet the whole query takes almost 20 seconds to finish.

My suspicion is that the CTE is basically built in full at first, meaning the server fetches all the data for all the thousands of customers, and only then it applies the WHERE filtering, ordering and LIMITing.

Question 1

Are the WHERE conditions outside of the CTE propagated into the CTE?

Question 2

Am I using the CTE correctly? Basically I am simplifying my life: my final WHERE clause is trivial, because it does not need to repeat the expressions that form my columns, but did I break the performance in that act?

Things I have tried

Made sure all the table accesses are on an index (this helped a bit, but still almost 20 seconds in total to fetch).
Tried replacing all the views referenced from the query by their materialized versions (no improvement).
Tried removing JOINs and sub-SELECTs from the query to see if the performance improves (only a very little).
Set shared_buffers to 1/4 of the available RAM (1 GB of 4 GB, no effect).
VACUUMed the whole database (FULL FREEZE ANALYZE, no effect).

Question 3

Am I correct in thinking that my approach requires to scan basically the whole DB, build a specialized view of it (that is that CTE) and only then apply some filtering? Is there any better way to do this?

We have a similar issue with CTEs. From what I gather researching the question, and from actually testing on our own queries, indexes which would have been used to filter the results in the CTE when used in a WHERE clause outside of the CTE aren't used because, as mentioned here, the CTE acts as an optimization fence. This means that, for performance reasons, you will want to refactor queries using CTEs to use subqueries instead.

We had a bunch of queries that used CTEs where we gained an order or two of magnitude of performance when we refactored them to subqueries — in one case, we dropped query time from approx. 2 minutes to just under a second. So keep that in mind when building queries.

So, in your example, you would use the query inside the CTE as a subquery instead:

SELECT * FROM (
    SELECT
      customer.name,
      customer.surname,
      (SELECT aggregate_whatever(foo) FROM bar) past_purchases,
      baz.aaa bbb,
      gaz.ccc ddd,
      ...
    FROM
      customers customer
    LEFT JOIN
      baz ON ...
    LEFT JOIN
      gaz ON ...
) encapsulated
WHERE
  past_purchases = 5
  AND <other conditions>

(In our case, we had relatively abysmal performance on some of our queries due to us using XPath column expressions, combined with scanning upwards of 30k rows in a table, caused us to spend seconds on calculating those XPaths on rows which would ultimately be discarded anyway. Removing the CTEs and using subqueries sped things up considerably, as the XPath columns would be calculated only for the actual rows returned).


Is MariaDB easier and better suited for a WordPress website? I've been having problems with MySQL 8.0.20 on Ubuntu 18.04, and because I don't have a lot of knowledge on databases, I was thinking on switching to MariaDB as I heard it has better settings and performance out of the box.

So:

Is MariaDB a better solution for someone who is still learning database?
Can I just bring the MySQL database to MariaDB without any conversion or anything else (just drag and drop)?
Can I run mariadb with apache 2.4 and php 7.4?

1) There is very little difference between MySQL and MariaDB, especially for a beginner. Stick with whatever ships with your preferred Linux distribution.

2) You cannot do this even between different versions of MySQL and MariaDB.

2.1) You can only upgrade in-place, and not downgrade. If you need to downgrade, you will have to mysqldump and import on the new version.

2.2) If you are upgrading, MySQL and MariaDB versioning differs, so choose carefully if you intend to switch in-place. In some cases, upgrading requires special preparation, e.g. between MariaDB 10.1 and earlier / MySQL 5.7 and earlier to MariaDB 10.2 and later or very recent MySQL 8.0.x.

Bottom line - don't version hop. Pick one and stick with it until you know exactly what you are doing.

3) Yes, MariaDB speaks the same wire protocol as MySQL so anything that will run against one at a low level will run against the other.


MySQL 5.7 - Disable use of SSL connection For some reason when setting up a server with MySQL 5.7, it was created to use SSL connections:

have_ssl=YES,
have_openssl =YES

There is no SSL configured in my.cnf.

How can I disable this option? It's for a development machine and we don't need this on it. We get an SSL authentication error, and for some reason this feature is enabled on the server while it was not configured and there is no SSL reference in the my.cnf.

I was using 5.7 and could not find a way to disable the SSL. I needed to downgrade to 5.5 (due to timestamp rounding bug on 5.6/7) and on 5.5 SSL is disabled. I just want to know if there is an option/command to change this option to disabled. In 5.5 its disabled by default.

I would block the SSL port using your machine's software firewall (iptables, etc). This avoids having to restart mysqld.

e.g. if your SSL port is 3307:

iptables -I INPUT -i eth0 -p tcp --dport 3307 -j DROP

Otherwise, simply add a hash at the beginning of each line containing 'ssl' in your /etc/my.cnf, then restart mysqld.

e.g.

# have_ssl...
# anything_ssl...

If you're running mysqld < 5.7.x, then you can use the ssl=0 configuration option here. That option is deprecated and will be removed in a future release, though.

However, your best bet is to block all other ports than your non-ssl port and allow ssl clients to fail attempting to connect to that port.


Can a foreign key have a default value equal to 0? I have a column whose default value is 0 .Can I make that column as foreign_key

Yes, you can define a column with a default value of 0 as a Foreign Key.  However, for the constraint to work, you would need to have a row in the source table with a value of 0 as well.  Example:

CREATE TABLE dbo.Master
(MasterID INT Primary Key,
 Name NVARCHAR(20));

INSERT into dbo.Master values (0,NULL);
INSERT into dbo.Master values (1,'Full');
INSERT into dbo.Master values (2,'Partial');

CREATE TABLE dbo.Slave
(SlaveID INT Primary Key,
 Description NVARCHAR(20),
 MasterID INT FOREIGN KEY REFERENCES dbo.Master(MasterID) DEFAULT (0));

 INSERT  INTO dbo.SLAVE (SlaveID, Description) VALUES(10,'Quickly');

This works fine.  However, what if Master does not have a 0 MasterID.  Then you would get:

Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Slave__MasterID__3943762B".
The conflict occurred in database "TEST", table "dbo.Master", column 'MasterID'.

There is a WITH NOCHECK option (in SQL Server) which allows you to create a Foreign Key without the required data existing in the master table.  Likewise, Foreign Key constraints can be disabled and enabled, but I would recommend against doing that for any normal circumstances.  This is defined at:

http://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx


Non Equi-Join on a time series table I have two tables a and b. A contains start dates and end dates whereas table b contains 10 min bins with values associated to each bin:

a
StartDate            EndDate
2018-03-06 10:13:00  2018-03-06 10:45:00

b
TimeStamp / Amount
2018-03-06 10:10:00 / 100
2018-03-06 10:20:00 / 89
2018-03-06 10:30:00 / 50
2018-03-06 10:40:00 / 78

The result I'd like to acheive is this:

a.StartDate / a.EndDate / b.Amount
2018-03-06 10:13:00 / 2018-03-06 10:45:00 / 248.0

where b.Amount is equal to = 0.7 * 100 + 89 + 50 + 0.5 * 78

I've started the below query but it's not giving me any results:

SELECT a.StartDate, a.EndDate, b.Amount
FROM a
JOIN b ON a.StartDate > b.TimeStamp
       AND a.EndDate < DATEADD(mm,10,b.TimeStamp)

Your join is incorrect.  It is excluding all rows since TimeStamp can never be before the start date and after the end date.  Change it to:

JOIN b ON a.StartDate < b.TimeStamp
       AND a.EndDate > DATEADD(mm,10,b.TimeStamp)

Also, don't use the contraction, mm to indicate MINUTE (it actually indicates MONTH!).  See this Microsoft Docs page for details.  Use MINUTE:

JOIN b ON a.StartDate < b.TimeStamp
       AND a.EndDate > DATEADD(MINUTE, 10, b.TimeStamp)

To get a calculated total amount, you'll need to use the SUM() aggregate:

SELECT a.StartDate
    , a.EndDate
    , TotalAmount = SUM(b.Amount)
FROM a
JOIN b ON a.StartDate < b.TimeStamp
       AND a.EndDate > DATEADD(MINUTE, 10, b.TimeStamp)
GROUP BY a.StartDate
    , a.EndDate;

Pro-rating the start and end amounts, as you appear to indicate in your question, might be accomplished like this:

SELECT a.StartDate
    , a.EndDate
    , b.TimeStamp
    , b.Amount
    , ProRatedAmount = CASE WHEN DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) > -10
            THEN b.Amount - (DATEDIFF(MINUTE, b.TimeStamp, a.StartDate) / 10.0 * b.Amount)
        WHEN DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) > -10
            THEN DATEDIFF(MINUTE, b.TimeStamp, a.EndDate) / 10.0 * b.Amount
        ELSE b.Amount
        END
FROM a
    JOIN b ON DATEADD(MINUTE, -10, a.StartDate) < b.TimeStamp
       AND b.TimeStamp < DATEADD(MINUTE, 10, a.EndDate);

╔═════════════════════════╦═════════════════════════╦═════════════════════════╦════════╦════════════════╗
║        StartDate        ║         EndDate         ║        TimeStamp        ║ Amount ║ ProRatedAmount ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╬════════╬════════════════╣
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:10:00.000 ║    100 ║      70.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:20:00.000 ║     89 ║      89.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:30:00.000 ║     50 ║      50.000000 ║
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 2018-03-06 10:40:00.000 ║     78 ║      39.000000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╩════════╩════════════════╝

Aggregating those amounts into a total might be accomplished with a CTE:

;WITH src AS
(
SELECT a.StartDate
    , a.EndDate
    , Amount = CASE WHEN DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.StartDate, b.TimeStamp) > -10
            THEN b.Amount - (DATEDIFF(MINUTE, b.TimeStamp, a.StartDate) / 10.0 * b.Amount)
        WHEN DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) < 0 AND DATEDIFF(MINUTE, a.EndDate, b.TimeStamp) > -10
            THEN DATEDIFF(MINUTE, b.TimeStamp, a.EndDate) / 10.0 * b.Amount
        ELSE b.Amount
        END
FROM a
    JOIN b ON DATEADD(MINUTE, -10, a.StartDate) < b.TimeStamp
       AND b.TimeStamp < DATEADD(MINUTE, 10, a.EndDate)
)
SELECT src.StartDate
    , src.EndDate
    , Amount = SUM(src.Amount)
FROM src
GROUP BY src.StartDate
    , src.EndDate
ORDER BY src.StartDate
    , src.EndDate;

╔═════════════════════════╦═════════════════════════╦════════════╗
║        StartDate        ║         EndDate         ║   Amount   ║
╠═════════════════════════╬═════════════════════════╬════════════╣
║ 2018-03-06 10:13:00.000 ║ 2018-03-06 10:45:00.000 ║ 248.000000 ║
╚═════════════════════════╩═════════════════════════╩════════════╝


Unify case when changing fields values I have the following code:

$sql=
    'select *,'.
    'case when fisicas_cpf is not null then null else razao_social end as razao_social,'.
    'case when fisicas_cpf is not null then null else nome_fant end as nome_fant '.
    'from pessoas p join fisicas f join juridicas j '.
    'on p.fisicas_cpf=f.cpf or p.juridicas_cnpj=j.cnpj '.
    'group by p.id'
;

I want to unify the case statements because they verify the same thing.

I want something like this:

'case when fisicas_cpf is not null then null else razao_social,nome_fant end as razao_social,nome_fant '.

Here, @AaronB says that the case just return one value. This applicate to my situation, or there is a way of doing it?

CASE always returns a single scalar value. You could get to a single-test solution by left joining to a subquery returning a single row with the values only if the condition is met.

However, that would be fairly messy SQL Code. In this simple case you are better off just repeating the case statement. Even though this violates the DRY principle, it is in the end easier to read and maintain.

Correction: MySQL currently does not support correlated subqueries in a JOIN. Therefore the messy solution I mentioned above is not even possible in MySQL.

Just for completeness, here is the SQL Server solution:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.test(id INT, show_ind INT, col1 VARCHAR(10),col2 VARCHAR(10));

INSERT dbo.test VALUES(1,1,'c11','c21');
INSERT dbo.test VALUES(2,0,'c12','c22');
INSERT dbo.test VALUES(3,1,'c13','c23');
INSERT dbo.test VALUES(4,0,'c14','c24');

Query 1:

SELECT T.id,T.show_ind, XX.col1, XX.col2
  FROM dbo.test T
  CROSS APPLY(
    SELECT TOP(1) X.col1,X.col2
    FROM(
      SELECT 1 x,T.col1,T.col2 WHERE T.show_ind = 1
      UNION ALL
      SELECT 2 x,'',''
    )X
    ORDER BY X.x
  )XX

Results:

| ID | SHOW_IND | COL1 | COL2 |
|----|----------|------|------|
|  1 |        1 |  c11 |  c21 |
|  2 |        0 |      |      |
|  3 |        1 |  c13 |  c23 |
|  4 |        0 |      |      |

You probably agree that it is a lot harder to read than the CASE solution:

SELECT T.id,T.show_ind,
  CASE WHEN T.show_ind = 1 THEN T.col1 ELSE '' END AS col1,
  CASE WHEN T.show_ind = 1 THEN T.col2 ELSE '' END AS col2
FROM dbo.test T;

And these two examples do not even contain the JOINs that are part of your query.


How to merge partitions in Postgres? I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed frequently, so I am considering merging the rows from 2014 into a single partition. My assumption is that this will improve the query optimizer and help prevent too many partitions from being created over time. The steps I was planning to do that are:

Create a new partition called xyz_2014
Copy the data from 2014 into xyz_2014
Alter table xyz_2014 to inherit from xyz
Alter old tables xyz_2014_01, xyz_2014_02, etc. to not inherit from xyz
Update my INSERT trigger for xyz to reference xyz_2014
Confirm that everything is working correctly
Delete old tables

Is this approach typically how merging tables in Postgres is done?

If you are still inserting data for 2014, then you risk problems with this method, because rows inserted between steps 2 and 5 are going to end up getting dropped rather than moved.

If you are not still inserting data for 2014, then I think you should change step 5 to "rewrite the trigger to throw an error upon insertion of 2014 data" and move it up to be step 0.  That would remove the doubt.

But it does seem like you are doing a lot of tinkering.  If you want to remove the partitioning for 2014, why would you want to keep it for 2015?  Why did you implement partitioning in the first place and why is that reason no longer valid for 2014 (but still valid for 2015)?  Getting rid of the partitioning might speed up the queries, but it might not.  Reorganizing partitioned tables isn't something you should do on a hunch.  Do you have a QA system you can use to time the queries and see if they get faster?  And wouldn't you want faster queries for the newer data at least as much as for the older data?


Transaction log is full (due to NOTHING)... but this database is in simple recovery mode I'm supporting an antedeluvian webapp (soon to be retired) that still uses "aspnetdb" for its auth system.  I was doing some work in prep for its retirement on my test environment, when I found my test server complaining with the following error:

  The transaction log for database 'aspnetdb' is full due to 'NOTHING'.

Now, normally I'd assume the problem came from the database transaction log... but this database was recently switched into simple recovery mode (our admin got sick of us complaining that the test-SQL-server was out of space and so she switched it to simple recovery).

I've tried a few experiments with no luck, and done a fair bit of googling.  Other answers talk about growing the size of the transaction log... but all options related to the transaction log files and autogrowth are greyed out in SSMS - does the transaction log file even exist?  I've tried setting the transaction log to ulimited size through

alter database aspnetdb modify file (NAME = 'aspnetdb_log', maxsize = UNLIMITED, FILEGROWTH = 100MB)

but that just fails with the same error.

Anybody seen this error before?  Full transaction log on a database in simple recovery mode?

It's on SQL Server 2016, running in 2008 compatibility mode because aspnetdb is that old.

Run the below script to check the size, max size and growth increments of your log files.

SELECT
    @@SERVERNAME AS [Server],
    db.name AS [Database],
    mf.name AS [File],
    CASE mf.[type_desc]
        WHEN 'ROWS' THEN 'Data File'
        WHEN 'LOG' THEN 'Log File'
    END AS [FileType],
    CAST(mf.[size] AS BIGINT)*8/1024 AS [SizeMB],
    CASE
        WHEN mf.[max_size] = -1 THEN 'Unlimited'
        WHEN mf.[max_size] = 268435456 THEN 'Unlimited'
        ELSE CAST(mf.[max_size]*8/1024 AS NVARCHAR(25)) + ' MB'
    END AS [MaxSize],
    CASE [is_percent_growth]
        WHEN 0 THEN CONVERT(VARCHAR(6), CAST(mf.growth*8/1024 AS BIGINT)) + ' MB'
        WHEN 1 THEN CONVERT(VARCHAR(6), CAST(mf.growth AS BIGINT)) + '%'
    END AS [GrowthIncrement]
FROM sys.databases db
LEFT JOIN sys.master_files mf ON mf.database_id = db.database_id

If your max and current size are equal this could be causing your issue. If so, try this command:

ALTER DATABASE [db_name] MODIFY FILE (NAME = 'file name', MAXSIZE = <new max size> GB);


A query that lists all mapped users for a given login When looking at the properties of a particular login, it's possible to see a list of users mapped to that login:

I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every database one at a time and retrieves information from sys.database_permissions

Is it possible to write a single query that retrieves the user mapping information shown above or am I forced to use a cursor or sp_MSforeachdb or something like that?

Here's one way using dynamic SQL. There's not really any way to do this without iterating, but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb (background here and here).
This will get a list of all online databases, the mapped user (if it exists), along with the default schema name, and a comma-separated list of the roles they belong to.
DECLARE @name sysname = N'your login name'; -- input param, presumably

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
    p.name                 COLLATE SQL_Latin1_General_CP1_CI_AS,
    p.default_schema_name  COLLATE SQL_Latin1_General_CP1_CI_AS,
    STUFF((SELECT N'','' + r.name
      FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
      INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
      ON r.principal_id = rm.role_principal_id
      WHERE rm.member_principal_id = p.principal_id
      FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''')
    FROM sys.server_principals AS sp
    LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
    ON sp.sid = p.sid
    WHERE sp.name = @name '
  FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@name sysname', @name;

On more modern versions (2017+), I would still use dynamic SQL, but I would use STRING_AGG() instead of FOR XML PATH, probably something like this:
DECLARE @login sysname = N'your login name';

DECLARE @sql  nvarchar(max),
        @sid  varbinary(85),
        @coll nvarchar(64) = N'COLLATE SQL_Latin1_General_CP1_CI_AS';

SELECT @sid = [sid] FROM sys.server_principals AS dp WHERE name = @login;

;WITH d AS
(
  SELECT dbid = CONVERT(varchar(11), database_id),
         qn = QUOTENAME(name)
    FROM sys.databases WHERE [state] = 0
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max),
        N'SELECT db = d.name, username = dp.name ' + @coll + ',
        schemaname = dp.default_schema_name ' + @coll + ',
        roles = STRING_AGG(rp.name ' + @coll + ', N'','')
        FROM sys.databases AS d
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS dp ON dp.sid = @sid
        LEFT OUTER JOIN ' + qn + '.sys.database_role_members AS rm
        ON dp.principal_id = rm.member_principal_id
        LEFT OUTER JOIN ' + qn + '.sys.database_principals AS rp
        ON rp.principal_id = rm.role_principal_id
        WHERE d.database_id = ' + dbid + N'
        GROUP BY d.name, dp.name, dp.default_schema_name'
    ), char(13) + char(10) + N' UNION ALL ')
FROM d;

PRINT @sql;
EXEC master.sys.sp_executesql @sql, N'@sid varbinary(85)', @sid;

In this latter example, if you only want the databases with a user mapped to the named login, just change the first left join to an inner join.


How can I get the measures of the last month with MDX? I would like to get the values of the measures of the previous month.

That's what I have:

SELECT
   [Measures].[Recuent distinct] ON ROWS,
   TAIL(NonEmptyCrossJoin([Dim Date].[Calendar].[Month Name].Members,1),1) ON     COLUMNS
  FROM [BDID]
  WHERE [Dim Canal].[Canal].&[Data]

Result:

October 2015
    Recuent Customers 10344

That's what I would like to have:

September 2015
Recuent Customers 20754

The thing is that my MDX takes the values of the current month, but I want to take the values of the last month. It has to be valid for each month, I mean I would like to have an automatic query for using on Power BI. It means, this month It has to gave me the values of the last month (September) and next month the values of October, in order to avoid the modification of the query each month.

Let me know if you need more information.

Without sample data or structure it would be hard to provide the exact code, but here goes.

You need to look into the PARALLELPERIOD function. That function takes a Level from a hierarchy and navigates back a number of steps in that level.

Also, I'm using the assumption here that you want to use the TAIL() function to get the last month in your dimension with an actual value in your measure.

I whipped up an example in the AdventureWorks database that I think is getting you what you want (which still isn't entirely clear)

WITH MEMBER [Measures].[LastmonhtValue]
as
sum(PARALLELPERIOD([Date].[Calendar].[Month],1),[Measures].[Customer Count])

SELECT
    [Measures].[LastmonhtValue] ON 0,
 TAIL(NonEmptyCrossJoin([Date].[Calendar].[Month].Members,[Measures].[Customer Count],1),1) on 1
  FROM [Adventure Works]

The way this works:
- the parallelperiod function moves back one month
- the tail function uses the regular measure to get you the last nonempty month
- the result is 1 month with previous month's data

This is how the data looks when you browse the cube using 'month' and 'customer count'

This is the result of the code:

Barring any additional information from your side I'm afraid that is the best I can do.

Be sure to design your attribute relationships in your time dimension to make sure this code has acceptable performance


Ports to use for mongodb sharding + replication When deploying a replica set or a sharded cluster (any situation with several mongod's really) - how does one know what port numbers to use?

Up until now I have used all the port no's I've found in examples and tutorials, but there doesnt seem to be any obvious system or way to go about it.
For example, one example uses ports 27010 - 27014. Another example uses 27001-27003, 27020-22, 26050 etc.

The only information I found on the docs of mongodb, was the one of the default ports.

As a student (so not production deployment) who is just using a single machine, what should I use? The default ones are only 4 and I probably need atleast 8 mongod's running (for a small sharded cluster with replica sets).
Can more than 1 shardserver be using the same port?

(I am sure this is a naive question as this has not been asked about other places nor addressed in any of the mongodb books I have found, but I want to know.)

Thankful in advance.

You can use any ports you like. Particularly on a toy system, it makes no practical difference what number port you choose; it works just as well with port 29589 as port 27017.

Some things to keep in mind:

on a single machine, two different processes must listen on different ports. So no, you can't run two shard servers on the same port.
you need to administer all these processes; if you pick a system where different sets of ports are used for different purposes, that will make it easier to keep track. For example, you might choose to run configsvr processes on ports 21001-21003, shard mongod processes on ports 22001-22999, and mongos processes on ports 23001 upwards.
the default port is 27017, and that is public knowledge; so if your system is open to the internet, be aware that malicious ransomers using port scanners will be looking for an exposed mongod system on port 27017 they can access and exploit. So you might prefer to avoid using that port, to add an extra layer to your security.


Oracle Performance - Varchar2 (x char) x Varchar2 (x byte) I know the definition diference between Varchar2 (x char) x Varchar2 (x byte).

  varchar2(20 char) means you can store 20 characters -- whereas varchar2(20) means you can store 20 bytes.

What about the performance?
Is there a problem if all my database is using Varchar2 (x char), even though my whole database is just using english?

Thanks!

Assuming that your database character set is AL32UTF8 (if there is a difference between the two, this is almost certainly your character set) and that all the data is characters that exist in the US7ASCII character set ("english" might constitute more than that depending on the definitions we're using) there won't be any noticable performance differences between a varchar2(n CHAR) and a varchar2(n BYTE).

Based on your database character set, Oracle has to do a tiny bit more work reading a string to figure out that every byte represents a single character rather than being part of a multi-byte character.  But checking the length of a string in either characters or bytes is a pretty trivial operation.  Unless you're doing something like running a TPC benchmark where you're already doing a crazy number of things that you'd never do in reality to get tiny fractions of performance improvement, it's not something worth worrying about.

If you were going to go crazy with a TPC benchmark level of effort to get every last thousandth of a percent, it would probably be slightly more efficient to use a database character set of US7ASCII and declare everything using byte semantics given that you only had to store English.  Practically, though, if your application is so efficient that this sort of thing matters, though, you've already solved every performance problem so you should be focusing much more heavily on supportability and maintainability which would argue for an AL32UTF8 character set and character length semantics.


default /etc/mysql/my.cnf on wheezy I try to optimize MySQL with mysqltuner but it seems like my my.cnf was never upated. there are lots of depricated warnings in /var/log/syslog

How can I get back the default configuration file so I can start optimizing from scatch?

Mysqltuner is a tool aimed at providing advice, not replacinf a DBA or editing the config file.

Question: Will MySQLTuner fix my slow MySQL server?
No. MySQLTuner is a read only script. It won't write to any configuration files, change the status of any daemons, or call your mother to wish her a happy birthday. It will give you an overview of your server's performance and make some basic recommendations about improvements that you can make after it completes. Make sure you read the warning above prior to following any recommendations.

You can find a default configuration file by installing the mysql-common package (on a different machine).
You can download the package with
apt-get download mysql-common

Then you will have the package in your current directory. For me, it is
mysql-common_5.5.46-0+deb8u1_all.deb

Check the contents with
dpkg --contents mysql-common_5.5.46-0+deb8u1_all.deb

and extract it to (let's say it exists) /tmp/mysql_wd/
dpkg --extract mysql-common_5.5.46-0+deb8u1_all.deb /tmp/mysql_wd

It will be in /tmp/mysql_wd/etc/mysql/my.cnf
Then, there are simple basic advices given by Peter from Percona, which can help you get started at https://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/
Sorry if I only provide a link here, this is not the matter of the topic (there is no specific question about that).
The subject is too vast to process here and you will have to take a good look at it yourself but I think I have answered your question.
Best regards!


Does a lock mean a wait? I really don't understand promises about waits in case of concurrent data access.
All manuals operate by term lock. No one explains that lock probably causes SQL clients waiting for lock acquisition on the server side (and it can take seconds / minutes / infinity)?
Are there cases when locks don't cause waits? I can imagine:

probably READ UNCOMMITTED in some situations, like there are no DDL?
error is reported without waiting...
no one waits but first who does commit wins, other fail on their commit

I reviewed JDBC API: it mentions literally:

SQLTimeoutException - when the driver has determined that the timeout value that was specified by the setQueryTimeout method has been exceeded and has at least attempted to cancel the currently running Statement`

According to the docs timeout is detected on the client side. Widely used client API doesn't have influence on server side timeouts (including caused by locks). Probably you can set some proprietary connection properties to influence DB behavior.
I see some proprietary SQL extensions, like:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql SET LOCK_TIMEOUT timeout_period
https://www.postgresql.org/docs/13/runtime-config-client.html set lock_timeout ms & set statement_timeout ms

For me DB waits due to locks is such a grey area. Does a lock mean a wait?

In most cases, locks don't cause waits.
Imagine I have a simple banking system.  In order to transfer money from account A to account B, I start a transaction and acquire a row-level lock on both account records.  That lets me run my validations (i.e. A has enough money for the transfer, the fraud department doesn't have a flag on either account, etc.), update both accounts to reflect the new balance, generate whatever logging is needed, and commit without worrying that some other session has made a change to one or both accounts that should have prevented the transfer.
Unless some other session comes along in the fraction of a second that I'm processing the transfer which tries to lock either A or B, the locks I acquired don't generate any waits.  Sure, it's possible that another session will be processing a transaction for A at the same instant that my session is, hence the need for the lock.  But given the thousands of accounts and millions of transaction that need to be processed, it is very unlikely that there would be contention for the lock.  Particularly if I design the system sensibly so that, for example, all batched transactions for a particular account are processed by one thread.  If there is no contention, there are no waits.


Why the same execution plan causes LOB reads in some cases? I have a spatial index and the following query:

SELECT DS2.[ID]
      ,DS2.[InstanceID]
      ,DS2.[Location].STDistance(@Location)
FROM [dbo].[DataSource01] DS1
INNER JOIN [dbo].[DataSource02] DS2 WITH (INDEX = [IX_DataSource02_Location])
    ON DS1.[ID] = DS2.[ID]
WHERE DS2.[Location].STDistance(@Location) < 144840.96; -- 90 miles in meters

where the @Location is the current user location. For two different users, I get two different execution times - 0 seconds vs 5 seconds and I want to understand what is causing this.

I get two identical execution plans:

everything seems the same, except the Filter operator - in the faster case 5 k rows are returned against 1.8 k rows for the the other case.

I have compare the reads of the two queries and it seems the second one is performing a lot of LOB reads:

I am wondering what could cause such LOB reads when the plan is the same and the data size and rows of each operator is the same (except the Filter one)?

There is another difference in the Clustered Index Seek(Spatial) operator - the Number of Executions of the faster query is 1142 and for the slower is 1180.

The Warnings of the Clustered Index Seek (Spatial) operator is Columns With No Statistics. The Column Reference are:

[extended_index_1523900896_384000].Attr
[extended_index_1523900896_384000].pk0
[extended_index_1523900896_384000].pk1

execution plan - slow query: http://dox.bg/files/dw?a=c133c2a7fb

execution plan - fast query: http://dox.bg/files/dw?a=24818510fb

The queries are executed on the same hardware.

Comparing the two plans reveal this snippet:

                      <Warnings>
                        <SpillToTempDb SpillLevel="1" />
                      </Warnings>

The two plans enter execution with the same memory grant, but one has actual rows 3910 (and spills) while the other has 1502. As far as I can tell, the difference comes from the spatial operator itself. So ultimately the difference in runtime you see comes from the actual spatial data. I can also see that the actual Point you're computing the distance from differs:

          <ColumnReference Column="@Location" ParameterRuntimeValue="POINT (-75.549 39.7366)" />
          <ColumnReference Column="@Location" ParameterRuntimeValue="POINT (-83.6026 41.6721)" />

The plan that spills and has more rows to process is the one you labeled 'FAST'. Are you sure you did not mislabeled them? It makes a lot more sense for the spilling one to be slower.


SIMPLE or FULL recovery model for databases? When should I use the full recovery model and when should I use the simple recovery model for databases?

I always used the full recovery model because it is the default, but today i encountered this error:

  Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction
  log for database 'DATABASE NAME' is full. To find out why space in the
  log cannot be reused, see the log_reuse_wait_desc column in
  sys.databases

The specific database is actually one of the smallest and most inactive databases on my server, so I have no idea how the log can be full on this database, and not the others.

To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command

alter database myDbName SET recovery simple
go
dbcc shrinkfile('LOG FILE LOGICAL NAME', 100)
go

It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?

EDIT:

Every night at 1 o'clock, we are doing a scripted backup of every database on the server. This is being done by a 31 line script where the most important part is

set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak'
set @Description = 'Full backup of database ' + @Filename
BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT

Is the new recovery model and databaseshrink going to be a conflict with this script?

We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?

When should I use the full recovery model and when should I use the simple recovery model for databases?

You should use the full recovery model when you require point-in-time recovery of your database.  You should use simple recovery model when you don't need point-in-time recovery of your database, and when the last full or differential backup is sufficient as a recovery point. (Note: there is another recovery model, bulk logged.  For more information on the bulk-logged recovery model see this reference)

Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction log for database 'DATABASE NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The reason you got that error (most likely) is because you haven't been backing up your transaction log.  When it isn't backed up, then it will continue to physically grow the transaction log file (provided autogrowth is enabled and maxsize allows) because it can't reuse any of the "portions" of the transaction log (virtual log files).  It can only mark those VLFs for reuse and allow the "wrap-around" nature of the transaction log when you do a transaction log backup (and a few other requirements, such as no active transactions, some replication aspects, etc.).

To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command
......
It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?

This helped you because by setting your database to the simple recovery model you told SQL Server that you no longer care about point-in-time recovery, and the requirement to ensure that virtual log files no longer need to be preserved and marked as active, now a checkpoint process marks these VLFs as inactive.
Excerpt/quote taken from this MSDN reference:

Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. In contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation.

Then you did a physical database file shrink and because there was free space in your transaction log now it was able to physically shrink the NTFS file.
Reading worth spending some time on:

Recovery Models
Managing Transaction Logs (Gail Shaw)
Factors That Can Delay Log Truncation

EDIT after your Edit:

Is the new recovery model and databaseshrink going to be a conflict with this script?

That BACKUP DATABASE command will work with either recovery models.  As for the routine database shrink... DON'T DO IT!!!!  Seriously, size your database accordingly, and if you utilize the full recovery model then ensure that you are doing routine and frequent transaction log files, not just to keep the transaction log size at bay but to also meet recovery point objects.

We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?

If your database is utilizing the full recovery model, then yes you should be doing transaction log backups.  If your database is in simple recovery, then you physically can not do a transaction log backup.
As for what recovery model to use (simple vs. full), we can't make that decision for you.  Only you, your business team, and your SLAs can.


Intentionally recursive instead of insert trigger does not want to nest I have been struggling to get an instead of trigger to recursively call itself.

The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions evaluated at that point, the row data should be modified and then inserted. Also when these conditions are satisfied a second row should be inserted into to the same table also causing the trigger to fire and evaluate the newly inserted values as before and modify the data and insert a second row as before and so on and so forth until the TRIGGER_NESTLEVEL() reaches a specific value (always less than 3).

Try as I might, I cannot seem to get the trigger to execute itself again on the second insert.

Here is some sample code I wrote to attempt to illustrate my problem:

--delete Tester1
--go
--drop table Tester1
alter database Playground
set recursive_triggers on

go

create table Tester1(ID int identity, Val int, String varchar(255))

go

Create trigger [tr_Tester1]
on Tester1
instead of insert
as
Begin

    if(Select TRIGGER_NESTLEVEL()) >= 3
    Return

    --Insert the initial row (modify text based on criterion)
    insert into Tester1
    select
        inserted.Val,
        case
            when inserted.Val%2 = 0
                then 'modified ' + inserted.String --Insert modified text
            else
                inserted.String --Insert the original text
        end
    from inserted

    --Insert a second row if the criterion was satisfied
    insert into Tester1
    select
        inserted.Val * 2, --Insert a new value that should satisfy the condition
        'some more text ' + CAST(TRIGGER_NESTLEVEL() as varchar(5))
    FROM inserted
    where
        inserted.Val%2 = 0

End

insert into Tester1(Val, String)
values(2,'some text')

select * from Tester1

Results I'm seeing:

Results I was expecting to see:

I have checked SELECT DATABASEPROPERTYEX('Playground', 'IsRecursiveTriggersEnabled') and select * from sys.configurations where name like 'nested triggers' and both are enabled even though according to MSDN:

  "These actions can initiate other triggers, and so on. DML and DDL
  triggers can be nested up to 32 levels. You can control whether AFTER
  triggers can be nested through the nested triggers server
  configuration option. INSTEAD OF triggers (only DML triggers can be
  INSTEAD OF triggers) can be nested regardless of this setting."

Also, I am aware that in my example my logic would always result in recursion (Well, theoretically at least). This is however only to illustrate the problem. In the actual trigger this is not necessarily the case.

Please can somebody point what I am missing or if I am misguided in my approach?

An INSTEAD OF trigger will not directly call itself when performing operations.

Consider if you had wanted the trigger to insert the row only if it had an even value, it would look very close to the first statement and you wouldn't want it to recurse.

From the CREATE TRIGGER Page:

  If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions.

That doesn't mean that INSTEAD OF can't be part of recursion. If the trigger makes changes to another table, it will call the triggers for those. The triggers could bounce back and forth and create recursion that way.

I believe that you can get the behavior you intend with a combination of INSTEAD OF to control the original insert and an AFTER trigger to add the additional row.