What is the purpose of a user's default database? As I understand it the default database is specified so that in case you don't specify one in the connection string, it will know where to start. Is this correct?

I've never seen a connection string without a database specified, is it even possible? Why would anyone want to do this? It just makes it less transparent to which database you are connecting.

In other words is there any reason to choose anything else than master as the default database?

Well Management Studio, for example, by default, only asks for (and only requires) server name and credentials. You can override these if you bother going into Options, but most people don't.

Do you really want all of your users connecting to master? In a lot of shops this can cause quite a bit of headache, especially with loose security. If users have the permission to create things in master, if they connect to master by default, they inevitably will create things there. And this can only mean cleanup will be needed later.


How can I normalize this schema about students and courses further? 1NF:

I am working on 2NF:

student (student_id, name, start, program)
course (coure_code, course_name, faculty, credits, time)
student_course(student_id, course_code, grade)

I am trying to achieve 3NF and BCNF.

I have identified what I believe are transitive dependencies: { credit } → { time }, and maybe { program } → { start }.

Does this make any sense?

I would not think that credits and time would really have anything to do with each other. One is essentially a count of how many credits they have accumulated, and the other relates to their status as a student. As far as the degree program and start date, those are also just data points to keep in the student's record.
For more info on this topic, this article helped me.
https://www.lifewire.com/transitive-dependency-1019760

In your course table, I would use professor_id to indicate who is teaching it, and have that table hold the faculty department information. I am a bit confused on what you are calling 'time.' Is it related to a specific class, or a student, or a course?

If you would like, I can help walk through your Entity Relationship Diagram. I have found the rubber duck method really helps clear things up.


Is it possible to run a trigger on Table Creation in MySQL Simple Question. Is it possible to create a Trigger that runs everytime a Table is created.

The goal is to create a metadata table for each table, but I can worry about dynamically passing the table name later.

The MySQL documentation implies that you can only run triggers on Inserts, Updates, and Deletes.See: 13.1.15 CREATE TRIGGER Syntax.

Unfortunately, no.

MySQL documentation clearly states applicable events:

    trigger_event: { INSERT | UPDATE | DELETE }


In-Memory OLTP SQL Server 2016 - insufficient system memory I am trying to place an In-Memory OLTP table,
Here is the memory estimate you will need:

But when the migration starts, the following error message is displayed:

The size of my table is approximately 22GB

I have a server with 64GB of memory and I allocated it for SQL 56GB, and to put the table in memory I only need 23GB

Can anybody help me?
Thanks.

So my guess is you may not actually have enough Memory for that table to be used as an In-Memory Table.
As mentioned in my comments, per this Brent Ozar article Hekaton (In-Memory OLTP) Tables In Use, it sounds like one of the requirements are you have at least twice as much Memory available as is the size of your In-Memory Tables:

But you need to understand the limitations, and they’ve changed a lot over the years...

Your memory needs to be 2x the size of your data

While you dedicated 56 GB of Memory to your SQL Server instance, a 23 GB table to be moved to an In-Memory Table means you should have at least 46 GB available for just that table, following Brent Ozar's advice. And because SQL Server uses the Memory you allocate to it for caching other things like data pages loaded in Memory, the 10 GB you have left over from 56 GB - 46 GB is not a lot to rely on. I.e. I wouldn't doubt your instance is already consuming more than 10 GB and therefor you don't have 46 GB available to provide to your In-Memory Table.
This article Estimate Memory Requirements for Memory-Optimized Tables in Microsoft's Books Online also is a good guide on how to estimate how much Memory you'll actually need.
I would recommend testing a much smaller table that's only a few GB or so as a proof of concept. Then work your way up. If you get that working, then you can try increasing your Memory a bit further before retrying your original table.


Query for fetching records from SQLite database on Blackberry I want to fetch data from SQLite database for Blackberry.

There is one table with four columns: ID, FROM, TO, TIME

I want to fetch record on the basis of FROM, and TO values.

Where condition 1:

FROM = [from value] AND TO = [to value]

2nd condition is:

FROM = TO AND TO = FROM

It should return the last 20 records.

This might work as an example:

CREATE TABLE Messages
(
    MessageID INT
    , fromUserID INT
    , toUserID INT
    , TimeSent DATETIME
);

INSERT INTO Messages VALUES (1, 10, 11, GetDate());
INSERT INTO Messages VALUES (2, 11, 10, GetDate());
INSERT INTO Messages VALUES (3, 11, 12, GetDate());
INSERT INTO Messages VALUES (4, 11, 10, GetDate());
INSERT INTO Messages VALUES (5, 11, 12, GetDate());
INSERT INTO Messages VALUES (6, 11, 10, GetDate());
INSERT INTO Messages VALUES (7, 11, 12, GetDate());
INSERT INTO Messages VALUES (8, 11, 12, GetDate());
INSERT INTO Messages VALUES (9, 11, 10, GetDate());
INSERT INTO Messages VALUES (10, 11, 12, GetDate());
INSERT INTO Messages VALUES (11, 11, 10, GetDate());
INSERT INTO Messages VALUES (12, 11, 12, GetDate());

DECLARE @t TABLE (MessageID INT);
DECLARE @From INT;
DECLARE @To INT;
DECLARE cur CURSOR FOR
SELECT fromUserID, toUserID
FROM Messages
GROUP BY fromUserID, toUserID;
OPEN cur;
FETCH NEXT FROM cur INTO @From, @To;
WHILE @@FETCH_STATUS=0
BEGIN
    INSERT INTO @t
    SELECT TOP(2) MessageID
    FROM Messages
    WHERE fromUserID = @From AND toUserID = @To
    ORDER BY TimeSent DESC;
    FETCH NEXT FROM cur INTO @From, @To;
END
CLOSE cur;
DEALLOCATE cur;
SELECT *
FROM Messages
    INNER JOIN @t tt ON Messages.MessageID = tt.MessageID

This returns the 2 most recent messages for each conversation between fromUserID and toUserID.  Change the TOP(2) to TOP(20) to get the 20 most recent conversations.

I'm sure this is not the most optimal way to do this; I believe it can be done in a set-based manner as opposed to the cursor-based, procedural manner shown here.


BACKUP WITH CHECKSUM works but DBCC CHECKDB detects problems A server I administer recently experienced a power outage that caused issues with the SSRS ReportServerTempDB. After the outage, the nightly job that runs Ola's DatabaseIntegriyCheck started reporting problems:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d Master
  -Q "EXECUTE [dbo].[DatabaseIntegrityCheck]
      @Databases = 'USER_DATABASES,SYSTEM_DATABASES',
      @LogToTable = 'Y'" -b

  Command: DBCC CHECKDB ([ReportServerTempDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
  Msg 8979, Level 16, State 1, Server XXXXXXX, Line 1
  Table error: Object ID 133575514, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041466880 (type In-row data). Page (1:130168) is missing references from parent (unknown) and previous (page (1:123239)) nodes. Possible bad root entry in system catalog.
  Msg 8979, Level 16, State 1, Server  XXXXXXX, Line 1
  Table error: Object ID 133575514, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041466880 (type In-row data). Page (1:130169) is missing references from parent (unknown) and previous (page (1:123239)) nodes. Possible bad root entry in system catalog.
  Msg 8979, Level 16, State 1, Server  XXXXXXX, Line 1
  Table error: Object ID 133575514, index ID 1, partition ID 72057594040156160, alloc unit ID 72057594041466880 (type In-row data). Page (1:130170) is missing references from parent (unkn...
  Process Exit Code 1.  The step failed.

However, our nightly backup job continued to report success every time it ran. SSRS didn't seem to have any problems either. The backup job does have multiple backup commands rolled up into one step:

BACKUP DATABASE [ReportServer] TO  DISK = N'C:\Backup-SQLServer\ReportServer.bak' WITH NOFORMAT, COMPRESSION, CHECKSUM, INIT,  NAME = N'ReportServer-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [ReportServerTempDB] TO  DISK = N'C:\Backup-SQLServer\ReportServerTempDB.bak' WITH NOFORMAT, COMPRESSION, CHECKSUM, INIT,  NAME = N'ReportServerTempDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [TEST-PH] TO  DISK = N'C:\Backup-SQLServer\TEST-PH.bak' WITH NOFORMAT, COMPRESSION, CHECKSUM, INIT,  NAME = N'TEST-PH-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Step two of our backup job checks the files. It ran every night and reported success each time:

RESTORE VERIFYONLY FROM DISK= N'C:\Backup-SQLServer\ReportServer.bak'
GO
RESTORE VERIFYONLY FROM DISK= N'C:\Backup-SQLServer\ReportServerTempDB.bak'
GO
RESTORE VERIFYONLY FROM DISK= N'C:\Backup-SQLServer\TEST-PH.bak'
GO

This went on for a few days until a convenient downtime. I resolved the issue by restoring/overwriting ReportServerTempDB with a nightly backup from prior to the outage.

Our databases' PAGE_VERIFY_OPTION is set to CHECKSUM.

What scenarios would explain this behavior?

sp_BlitzErik wrote in a comment (since deleted):

  ...is asking [about the PAGE_VERIFY_OPTION] because of this: Blitz Results: Page Verification Not Optimal. The CHECKSUM option for backups will only throw errors if you're using the correct page verification option. It can sometimes be suboptimal if your database came from an older version of SQL Server.

And it will only throw errors, even with the CHECKSUM option in use for the page verify option, if the page became corrupted after writing to disk. And I don't suppose they're necessarily asking the question just because of that post - my guess is they have the option set to CHECKSUM already.

The scenario described in the question can and does happen when a page corruption happens in memory (either because of bad memory, a memory scribbler, or a SQL Server corruption bug), and then the corrupt page is written to disk with a valid checksum.

Subsequent reads of the page, including testing of the page checksum, will succeed. However, when DBCC CHECKDB (or a hapless query) runs, the corruption will become evident. In this case, it doesn't matter whether the BACKUP and RESTORE statements use CHECKSUM; they won't detect that corruption.

This is why you cannot just rely on using CHECKSUM everywhere and not run consistency checks.

Hope this helps explain what you saw.

From the errors you posted, it looks like the root page of that clustered index got whacked somehow.


Is it safe to use set-returning sql functions instead of security-barrier views? Since 9.2, it's been possible to use the security_barrier attribute to guard against maliciously-chosen functions and operators accessing data 'hidden' behind filters in views (full info in the postgres docs).

You can see this happening in the test below, but the same effect isn't observed with the set-returning function instead of a view at the end of the test.

Is this just a quirk of this individual test or are set-returning functions always a safe1 way to guard against this sort of leak?

testbed:

create schema stack;
set search_path=stack;
create table t(secret) as values (1), (2);

test1:

create view v as select * from t where secret^4>1;
select * from v;
/*
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/
create function f(integer) returns integer cost 1 language plpgsql as $$
begin
  raise notice 'secret is: %',$1;
  return $1;
end;
$$;
select * from v where f(secret)>0;
/*
NOTICE:  secret is: 1                <-------- SECURITY LEAK
NOTICE:  secret is: 2
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/

test2:

create function fv() returns setof t language sql as $$
  select * from t where secret^4>1
$$;
select * from fv() where f(secret)>0;
/*
NOTICE:  secret is: 2                <-------- no leak
┌────────┐
│ secret │
├────────┤
│      2 │
└────────┘
*/

clean up:

drop schema stack cascade;

1 There are performance reasons why you might not want to go this route even if it is safe

Yes - if you use functions in a language other than SQL, or if you define them as STRICT.

Essentially, you must prevent inlining of the function. If the function isn't inlined, then predicates can't be pushed down through it and it can't be flattened.

Only SQL functions are eligible for inlining, and only if they are not defined as STRICT.


Trigger on replicated DB If I have database db1 with schema sch1. I want to create another schema sch2 which will be used by different application and the tables in sch2 will be populated from tables in sch1. So my approach is to create triggers on insert, update, delete perform this task

So my questions are

1-  Is this  a good way to do this or there is a better way?

2-  I don’t want to affect the performance on db1 so I am want to replicate it to different server and create the triggers on the subscriber DB, so replicated server will have Sch1 and Sch2 on it,  Can I do that?

This is on SQL Server 2012 Standard Edition

Note: tables in sch2 have different names and different columns name. also more than one table in sch2 may get populated from data from sch1

It looks like replication is what you need here. There are different flavors of replication (merge, transactional and snapshot) that are suitable in different situations. In this case, it looks like transactional replication is what you need, unless changes on the secondary copy have to be propagated to the original copy of the data.

I fail to understand the role of the second schema. If you need both schemas at the primary database, then trigger could be a way to keep the two schemas in sync. If you need the second copy of the tables only at the second server, you can replicate the tables from sch1 to sch2 without issues.


Update specific records of table based on subquery's output (sql server) have this query which produce below output :

select sec.site,sec.Name,sec.Freq from
( select Name from sec group by name having count(Freq) > 3)as dt
inner join sec on dt.Name = sec.Name
where sec.Freq like '%MUF2900%' ;

site    Name            Freq
S003   sct003a       MUF2900_26
S003   sct003a       MUF2900_26
S003   sct003a       MUF2900_26
S034   sct034a       MUF2900_26
S034   sct034a       MUF2900_26
S034   sct034a       MUF2900_26

Now i have to update my another table name Test based on the output of above query.
Table Test currently contains following records (before Update) :

 site      cell       ID
 S003      09M00011   1019581
 S003      09M00012   1019582
 S003      09M00013   1019583
 S003      29M00011   1019581
 S003      29M00012   1019582
 S003      29M00013   1019583
 S034      09M00011   1016581
 S034      09M00011   1016582
 S034      09M00011   1016583

 now for site S003 , ID for for cell starting from 09 and 29 are same ,so i need to update the ID for 29  i.e the unit place of ID should be incremented by 10 , like following..

REQUIRED :

  Site      cell       ID
 S003      09M00011   1019581
 S003      09M00012   1019582
 S003      09M00013   1019583
 S003      29M00011   10195811
 S003      29M00012   10195812
 S003      29M00013   10195813
 S034      09M00011   1016581
 S034      09M00011   1016582
 S034      09M00011   1016583

I have many records like this.

Note : S034 has no cell starting from  29 , so its ID dont need to update.

what i tried and where im getting confuse is  :

 update Test set ID = (how to do this ??)
  where Test.site = (select sec.site,sec.Name,sec.Freq from
( select Name from sec group by Name having count(Freq) > 3)as dt
inner join sec on dt.Name = Sec.Name
where sec.Freq like '%MUF2900%') where cell like '29M%';

How should i do this. Do i need to make cursor for this ??

From your examples, the statement below should do what you want to do.  It's not fast as it involves casting ID to a string and manipulating it, but I think this is the only thing that will work for you.

UPDATE TEST
SET ID = CAST(LEFT(CAST(ID AS VARCHAR(20)), LEN(CAST(ID AS VARCHAR(20)) - 1)) + '1' + RIGHT(CAST(ID AS VARCHAR(20)), 1) AS INT)

If ID is a string to begin with then you can remove the CAST(ID AS VARCHAR(20)) statement and replace it with just ID.

UPDATE TEST
SET ID = CAST(LEFT(ID, LEN(ID) - 1)) + '1' + RIGHT(ID, 1) AS INT)


Use cases for hstore vs json datatypes in postgresql In Postgresql, the hstore and json datatypes seem to have very similar use cases. When would you choose to use one vs. the other? Initial thoughts:

You can nest with json; you can't with hstore
Functions for parsing json won't be available until 9.3

Taken from http://www.depesz.com/2013/03/30/waiting-for-9-3-add-new-json-processing-functions-and-parser-api/ per dezso's comment:

  Given current limitations for both datatypes, I would say that they
  should be used in different scenarios:

  if you want indexable searches, and you’re fine with “no-nesting”
  limitation: use hstore. if you need nested, complicated structures,
  and don’t care about indexing – use json. and if you need the best
  from both world – you’ll have to wait to see what, and when, the
  future will bring.


MySQL Replication: How to restore master FROM slave? TL;DR: given a master/slave setup between two MySQL databases, where the connection from the master to the slave is read only, how can you restore the master from the slave in the event of a failure of master?

I'm investigating alternative backup solutions for our main MySQL database.

Up to now, we've been using mysqldump to dump our databases, with the resulting file being zipped & the old backups being rotated.

This has worked well for several years, but we're now getting to the point where the database is so large that the overnight backup operation is extending into business hours and impacting operations (since some or all of the tables are still locked when employees start arriving in the morning).

I have set up a slave database on another server & have it syncing with our main database. It's working nicely, and I have the slave backing up (via mysqldump again). But I have two questions/problems:

How can I be sure that I'll be able to restore master from the mysqldump output from slave?

I'm reasonably confident that the dump files are OK, but there are stored procedures & views in our database and I've had issues with mysqldump before - it can be a little finicky about dumping these things in the correct order/with correct permissions/etc.

I've thought about doing a test restore to a blank system (in a Docker container), but a) it's going to take a LONG time, b) I'll need to write a script to actually compare the test system with the master, and c) it will be hard to get a comparison at a single point in time.

As an alternative, I've thought about simply shutting down the slave database, backing up the data files, then restarting the slave. This will be faster, simpler, and probably more reliable, but how do I then restore in the event of a failure?

I have read the MySQL documentation on the subject, but it only talks about how to do the backup, not how to restore it.

I imagine that it would be possible to shut down all the systems, revise the settings on the slave to remove readonly = 1, then somehow force the slave to push everything to the master.

So, given this setup, my main question is this:

What are the steps involved in restoring all the data from the read-only slave to the master in the event of a failure of the master?

1) Backups:

You can verify if your backup is successful by tailing the mysqldump for "dump completed successfully" to verify the success of a mysqldump.
Invalid backup is as-good-as (or as-bad-as) no backup. It's a good idea employ a backup-validation process by restoring it to a provisioned location as possible. Do it.
If you want to stick to logical backup, try using with myduper/myloader, which will be quicker than mysqldump.
Again physical backups are faster  than logical and this is specifically true for large sizes. If you can giveup on MySQLDump which is slow for restores and especially if you have all-innodb, you should consider using xtrabackup from Percona (for hotbackup).
Check installing and configuring xtrabackup with holland framework.

2) Restoring master from slave.

As such there are few questions and articles available but i will quickly notedown steps I can makeout for restoring master from  slave.

Considering your master is M and slave is S. (M --> S)
M is down.
S is promoted to Master ( readonly=false, binlogging is enabled)
M is later fixed and ready to join but need a restore.
Restore M from latest backup, mark it as readonly=true.
Upon restore, make it slave of S. (S --> M)
Once it catches up, you may want to failback to original master.
Stop production traffic, Mark M as readonly=false, S as readonly=true.
Mark S as slave of M (should be simple change master to from present binlog of M)
Thus making them active-passive master-master pair.
Move traffic to M.
You might want to break the master-master but it's fine to keep active-passive.
Making sure nothing writes to Passive master.

Hope this helps.


Count rows with max per group and additional condition I have the following table:

 horse_main_id | race_id |    horse_name     | rating100 | race_result
---------------+---------+-------------------+-----------+-------------
            23 |     159 | Angelology        |       126 |           1
            24 |     159 | Arwoc             |       118 |
            25 |     159 | Marlo Man         |       118 |
            26 |     159 | Hucknall          |       113 |
            27 |     159 | It's Electrifying |       110 |           2
            28 |     159 | Monte Carlo       |       107 |
            29 |     159 | Cross Constance   |       103 |
            30 |     160 | Auld Burns        |       119 |
            31 |     160 | Diamond Jim       |       117 |
            33 |     160 | Livery            |       115 |
            34 |     160 | Paraggi           |       115 |
            35 |     160 | I'll'ava'alf      |       110 |           2
            36 |     160 | Jacks 'n' Kings   |       108 |
            32 |     160 | Fastnet Isle      |       115 |           1

I would like to operate on groups of race_id, find all groups that have the max(rating100) for their group and race_result = 1, and count all the times this happens.

So this example should return only 1, as for race_id 159 group the max rating100 of 126 also has race_result = 1, but for race_id 160 group, the max rating100 is 119, but does not have race_result = 1.

This is the closest I've got (was told I should use window functions):

SELECT * FROM (
    SELECT horse_name, race_id, race_result
         , max(rating100) OVER (partition by race_id) AS max_rating
    FROM horse_main) t
WHERE race_result = 1;

Since you need a subquery in either case, I would use a plain aggregate in the subquery (may be cheaper):

SELECT count(*)
FROM  (SELECT race_id, max(rating100) AS rating100
       FROM   horse_main
       GROUP  BY 1) x
JOIN   horse_main h USING (race_id, rating100)
WHERE  h.race_result = 1;

If there are many rows per race_id, it will be faster to get group-wise maxima with one of these techniques:

Optimize GROUP BY query to retrieve latest record per user

Or use the window function rank() - that's what you count effectively: all greatest rating100 per race_id with race_result = 1:

SELECT count(*)
FROM  (SELECT race_result, rank() OVER (PARTITION BY race_id ORDER BY rating100 DESC) AS rnk
       FROM   horse_main) x
WHERE  rnk = 1
AND    race_result = 1;

An index on (race_id, rating100 DESC, race_result DESC) should help performance with big tables.

Assuming rating100 is defined NOT NULL, else you need to add NULLS LAST in query and index.


Looking for ideas on maintaining data warehouse in close to real-time - ETL tools, etc I have done a fair number of traditional data warehouse implementations where the loads were done in a batch-oriented manner, i.e. the data is refreshed nightly or, at most, every few hours.  I now face a challenge of creating a system, where the data in the data warehouse needs to be maintained close to real-time (a few minutes delay is OK, but no more than that).  I have done a lot of reading and it seems that getting close to real-time has been a trend in DW over the last few years.  However, I am having trouble finding specific examples and concrete information about the available tools, which support this kind of "trickle-feed" ETL.  It seems that the right tool would be able to read the database transaction log and send those changes over to the data warehouse, while allowing to do some data transformations in-flight.  Does anybody has experience with real-time data warehousing and can recommend a good tool or point out a good reading on this subject.

Here are a couple of relevant links:
http://proc.conisar.org/2012/pdf/2214.pdf
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=37&cad=rja&uact=8&ved=0CGQQFjAGOB4&url=http%3A%2F%2Fwww.researchgate.net%2Fpublication%2F226219087_Near_Real_Time_ETL%2Ffile%2F79e4150b23b3aca5aa.pdf&ei=G_dOU8HXI4iy2wW954GoDw&usg=AFQjCNHZVJuxfmuqwjrdi10oxM8v51WcNA&sig2=6OI-xoIz9b0mH_hzITBVrQ

Thank you!

P.S. I work in a Microsoft shop, so the source databases are on Microsoft SQL Server.  I do have a good handle on SSIS, but it doesn't seem to fit here.

If you have the Enterprise version of SQL Server, then I would recommend doing this using Change Data Capture (CDC). This is much easier and cleaner than setting up triggers to watch for changed data. Plus it reads the transaction log for changes and then logs those changes in system tables.

You can use SSIS for your ETL. If you have 2012 installed, there are some very handy CDC components available. However you could also use regular components like the OLE DB source and query the built in CDC functions that SQL Server installs when CDC is enabled.

I have used CDC and SSIS to build an integration that is keeping 2 systems in sync while we are transitioning from one to another and it runs every 10 minutes without issue. The key is to keep your SSIS packages as light weight as possible. This means moving lookups away from full cache and other things like that. Ideally, the more often the job runs, the less data you are transmitting each run.

One other thing to consider if you are worried about putting stain on your source system would be look into seeing up an Availability Group read only replica for your source in asynchronous mode. This would allow you to offload your reads from your OLTP database. This does require Enterprise Edition and 2012 or higher though.


Adding a clustered index to a heap table I've noticed that we have a few heap tables on our database, they all have a non clustered index on the primary key (which is an Int). The biggest table has over 2 million rows, a forwarded record count of 663,479 and page count of 609,028. Also noticed that the forwarded records per second are 972 on the server.

From my limited knowledge and a bit of googling this all looks pretty bad so i'm looking into adding a clustered index to this table.

Would you agree this is the right thing to do?
If so what are the best practices for adding a clustered index to this table, i
assume i will have to remove the existing non clustered index on the primary key (and any foreign keys referencing it) and then add the clustered index and recreate the foreign keys? Are there any scripts to assist with dropping and recreating the foreign keys as there are quite a few?
Would there be any benefit to rebuilding the heap as a short term fix while waiting for a chance to sort out the indexes?

Sorry if i haven't provided enough information, let me know if you need to know anything else to be able to help, thanks!

Would you agree this is the right thing to do?

Yes, especially if (1) the table is looked up using the Primary Key, and (2) you are noticing performance issues.

Note that if the table is primarily looked up using a set of data other than the PK (Example: Order Lines may be looked up by the OrderID and sorted by the OrderLineId), then you may consider a clustered index based on the most common lookup(s), especially if they select sequences of rows. For example, on the Order Lines example above, having a Clustered index like:

CREATE CLUSTERED INDEX Idx_OrderID_OrderLineId
ON OrderLines (OrderId, OrderLineId)

would be optimal for retrieving all of the order lines for a given order.

Also, there are cases where having a non-clustered covering index may be optimal. You will need to keep an eye on performance whatever you do with these tables.

  If so what are the best practices for adding a clustered index to this
  table, i assume i will have to remove the existing non clustered index
  on the primary key (and any foreign keys referencing it) and then add
  the clustered index and recreate the foreign keys?  Are there any scripts to assist with dropping and recreating the foreign keys as there are quite a few?

Best practices depends on the answers to the above questions. If you actually want a clustered PK (because that is how the table is commonly referenced, or because there is no clear clusterable index that could be created), then you will need to perform all of the steps you specify above. If you decide to create a clustered index that is identical to your PK (and yes, people do this... ) you won't need to do all these steps. And if you decide your optimum clustered index is some combination of fields not identical to the PK, then you will not need to do all of these steps either.

If you do replace the PK with a clustered PK, then there are scripts that can help with identifying the foreign keys. I tend to use the View Dependencies functionality inside to SSMS to identify possible locations for FKs to my table, then manually script them out and drop them myself (because I had some bad experiences with scripts that should handle FKs), so I can't recommend any specific choices right now.

  Would there be any benefit to rebuilding the heap as a short term fix while waiting for a chance to sort out the indexes?

Not really, no.


How to dedicate server to MySQL for max performance? I am using MariaDB 10.5, I see this in MySQLTuner report

Dedicate this server to your database for highest performance.

How do I do this?

My server is on Centos 8.

Sorry this startetd as comment, but it git to big

This only means that the database server should run on a hardware where it doesn't share Memory, CPU and Hard drive with other services like webserver for example, or run in a virtual hardware with other virtual environments, which share memory and CPUs.

The biggest Problems you will face are huge amount of data or a lot of parallel connections, which will need be addressed by better hardware or one of the cloud services.

Till then check the error logs of mysql and server, if something is a miss like to small temporary tables, not enough connection and lots more, you will find log entry about it and find a solution by searching for that error or warning.

On a final point the settings depend a lot on what you are doing have  you for example huge inner joins and 6 or 7 of them your settings will be nit the same as other scenarios.

Check your query with EXPLAIN and see that teh indexes are correct set, that will help much more than tweaking one or other parameter.


update one column based on query containing another (joined) table Consider the following select query:

SELECT s.id, s.event, s.column, e.oldcolumn
FROM section as s
INNER JOIN event as e
ON s.event = e.id
WHERE s.id = 1

Now I wish to update the section table so that the values are equal to the oldcolumn from the event table.

I tried:

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."section"
INNER JOIN public."event"
on public."section".event = public."event".id
WHERE s.id = 1

However this, on postgresql, returned the following error:

ERROR:  table name "section" specified more than once
SQL state: 42712

How would I do such a thing, using joins inside an update query?

Disclaimer, I don't use PostgreSQL but I'm pretty familiar with SQL Server.

From what I understand in the PostgreSQL documentation here, I think the error you are getting is caused by the inclusion of public."section" INNER JOIN in your FROM statement. When using UPDATE you specify the table you are updating so adding public."section" in the FROM is not necessary. Per the documentation "Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item)."

If you change your code to what I have below, I think it would resolve your issue. Though I'm not sure about the quotation marks you have, I just copied your format. The main difference being, only have the tables you're joining to in the FROM and move the ON to the WHERE.

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."event"
WHERE public."section".event = public."event".id AND public."section".id = 1


Which version of SQL Server 2016 supports R Microsoft buying REvolution results (amongst other things) in the integration of R in SQL Server 2016.

Looking through the features supported by the SQL Server 2016 I cannot find R-Support. Does anyone know to what feature R-Integration belongs to or what version(s) integrate this feature?

SQL Server 2016 Community Technology Preview 2.2  is the latest release of SQL Server 2016. This version does not have R support.

Later release of 2016 (probably 3.0) will have support for R language.

   Found this slideshare presentation (slide - 130) that says R support will be in CTP 3.0.

EDIT: Announced and demoed in PASS Summit 2015 - As of SQL Server 2016 CTP 3.0, SQL server supports Advanced Analytics (RRE integration):

  With this release, we are very excited to announce the public availability SQL Server R Services in SQL Server 2016, an Advanced Analytics capability which supports enterprise-scale data science, significantly reducing the friction for adopting machine learning in your business. SQL Server R Services is all about helping customers embrace the highly popular open source R language in their business. R is the most popular programming language for Advanced Analytics. You can use it to analyze data, uncover patterns and trends and build predictive models. It offers an incredibly rich set of packages and a vibrant and fast-growing developer community. At the same time, embracing R in an enterprise setting presents certain challenges, especially as the volume of data rises and with the switch from modeling to production environments. Microsoft SQL Server R Services with in-database analytics helps customers embrace this technology by supporting several scenarios.

Also, Henk blogs about Introducing Microsoft SQL Server 2016 R Services


Creating a view (or table) from JSON data with two different shapes I'm pulling in JSON data from two different sources that have roughly the same information, but in different shapes.  Let's say I'm pulling in data about music albums, and one of the API responses looks like this:

{
  "id": "6289d24d-1a49-499b-90a7-8f9e43b4d6af",
  "artist-credit": [{
    "name": "The Hot Sardines"
  }],
  "title": "The Hot Sardines",
  "date": "2014",
  "media": [{
    "tracks": [{
      "position": 1,
      "id": "5928bc3b-a5b9-4149-8a1e-b224707dab5a",
      "title": "Bei Mir Bist Du Schoen",
      "artist-credit": [{
        "name": "The Hot Sardines"
      }],
      "length": 246000,
    },  {
      "length": 246000,
      "number": "11",
      "artist-credit": [{
        "name": "The Hot Sardines"
      }],
      "title": "(I Don't Stand) A Ghost of a Chance With You",
      "id": "be97c530-d2d4-4a68-a777-637a2c23852e",
      "position": 11
    }],
    "track-count": 11
  }],
  "packaging-id": "ec27701a-4a22-37f4-bfac-6616e0f9750a"
}

While the other looks like:

{
  "album_type" : "album",
  "artists" : [ {
    "id" : "2BTZIqw0ntH9MvilQ3ewNY",
    "name" : "Cyndi Lauper"
  } ],
  "id" : "0sNOF9WDwhWunNAHPD3Baj",
  "name" : "She's So Unusual",
  "release_date" : "1983",
  "tracks" : {
    "items" : [ {
      "artists" : [ "some artists"],
      "disc_number" : 1,
      "duration_ms" : 305560,
      "id" : "3f9zqUnrnIq0LANhmnaF0V",
      "name" : "Money Changes Everything",
      "track_number" : 1,
      "type" : "track",
    }, {
      ...
    } ],
    "total" : 13
  },
  "type" : "album",
  "uri" : "spotify:album:0sNOF9WDwhWunNAHPD3Baj"
}

I'd like to be able to query this data via SQL in PostgreSQL, with the table structure looking like:

album               (id, title,    release_date, spotify_id, musicbrainz_id)
track               (id, title,    album_id,     duration,   spotify_id, musicbrainz_id, position)
artist              (id, name,     spotify_id,   musicbrainz_id)
artist_track_credit (id, track_id, artist_id)
artist_album_credit (id, album_id, artist_id)

From what I've gathered there are a couple of ways to do this.

One would be to take each API response, convert them into SQL INSERT queries in software and then send those queries to the database like so:

INSERT INTO album (title, release_date, etc) VALUES (?, ?, ? ...);

-- Get the album ID back and use it in the following queries

INSERT INTO track (title, album_id, etc) VALUES (?, ?, ? ...), (?, ?, ? ...), ...;

INSERT INTO artist (name, etc) VALUES (?, ...), ... ON CONFLICT DO NOTHING

-- Get the new artist ids back (if created) and use them in the following queries

INSERT INTO artist_track_credit VALUES ...
INSERT INTO artist_album_credit VALUES ...

The problem here is that it is pretty messy, and requires several round trips to the DB in order to get the album ID to insert with the tracks, then the artist IDs to insert with the artist and album credits.

One alternative is to insert the API response directly into the database, and then use database functions to manipulate the data and insert it into the relational structure. The downside here is that I'm not as familiar with writing functions for postgresql, but if this is the best solution I'd go for it.

Another alternative is to take advantage of postgresql's JSON handling abilities and insert the API responses directly into the database, and then develop indices and views on that JSON data to simulate ordinary relational data. This seems promising, but I'm not sure how to handle the data having different shapes. Is it possible to write a set of views or indices for each schema, and then some kind of union that will take, say SELECT * FROM album and convert that into SELECT * FROM musicbrainz_album_view UNION SELECT * FROM spotify_album_view without having to write it out every time?

What is the best way to handle this scenario?

I recommend not to store the data as JSON in the database, particularly if you want to query them with SQL. Storing the data will be easy that way, but your queries will be much more complicated and slower.

If you want to use several SQL statements in client code or a function in the database should mostly depend on the question if you want to keep your business logic in the application or in the database.

There shouldn't be too many round trips necessary if you use RETURNING as in

INSERT INTO artist (name)
VALUES ('Jethro Tull')
RETURNING id;

You can even insert several rows with a construct like

WITH a AS (
   INSERT INTO artist (name)
   VALUES ('Jethro Tull')
   RETURNING id
)
INSERT INTO album (name, artist_id)
SELECT ('Catfish Rising', a.id)
FROM a;


Why does PostgreSQL ignore auto_explain.log_analyze = 0 in is postgresql.conf file and how can I turn on auto_explain for all sessions? I have a strange problem with PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5), 64-bit (that was the complete output from select version();).
My problem is I want to turn on the auto_explain to chase down some problem with triggers that aren't firing. I need this auto explain to give me all the details for a short time to debug this. But not just in my session, I need it to be in all sessions that all application servers access.
I am showing you the problem right here:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_nested_statements = true
auto_explain.log_triggers = true
auto_explain.log_timing = false

it doesn't do anything unless I set in my session
SET auto_explain.log_min_duration = 0

then it will log the auto explain for that session. But all the application server sessions continue mute.
This is some sort of bug I found. Here is how I know it.
If I put
manu_confuse = foo

into the postgresql.conf file, then no error is thrown.
If I put
auto_explain.foo = bar

into the postgresql.conf file, and not the shared_preload_libraries, then only when I say
LOAD 'auto_explain';

will it tell me that there is no auto_explain.foo property to set.
However, it simply ignores the auto_explain.log_min_duration parameter, no matter what value I give it.
For example, if I say in the .conf file;
auto_explain.log_analyze = 'foobar'

it will complain that the value to log_analyze must be a Boolean.
But when I say in the .conf file
auto_explain.log_min_duration = 'foobar'

it will not complain in any way. It just ignores it. And no matter how often I repeat some bogus assignment of auto_explain.log_analyze, just ignored.
I find that very strange. How has been able to turn auto_explain on for all sessions by default?
PS: A Related question is this, but it is very different. I'm only referencing it because it brought me to this site to ask.

Hmm, in my desperation I further remembered I could try this:
ALTER SYSTEM auto_explain.log_min_duration = '0s';

and it would accept it. It would also recognize if I am using bogus values. Hm, apparently now also acted on it. Is there some sort of stored settings from ALTER SYSTEM that always overrides what postgresql.conf contains? That could be a reasonable answer.
I noticed more strange behavior that after a restart now it does work initially. The ALTER SYSTEM does not need to be invoked again. But it takes some time to begin the auto_explain logging, the very first command was not logged, but then it was.


Connecting to the default instance of SQL Server connects to a named instance I have 2 instances of SQL Server installed on one server: SQL002.

One instance is a default instance, SQL2008 R2, and the SQL service is generally not running except for specific tests.

The other instance is a named instance, SQL 2014 (so SQL002/SQL2014). This is generally running all the time.

Today I just had a developer tell me that when they connect to SQL002 in SQL Server Management Studio it will connect successfully. I replicated this from my machine and also see a successful connection, however when I look at the properties of the connection it is reporting that it connected to the named instance.

What is going on here? Is there a redirect I'm not aware of?

There can be three reasons:

SQL Aliases: either you or that  dev has an alias
Named instance running on port 1433. Connecting to the default instance means just connecting with port 1433 instead of asking the sql browser first (only applies to tcp)
Explicit port inside the connection string


Converting MySQL AES_DECRYPT() to Postgres decrypt() I have this MySQL query using AES_DECRYPT() that is the following:

SELECT *
FROM data_tbl
WHERE AES_DECRYPT(data_point,'aBcDeF')='data_1';

I've figured out as much that the equivalent in Postgres 11 is decrypt() (F.25.4. Raw Encryption Functions) and the query would look something like this:

SELECT *
FROM data_tbl
AND decrypt(data_point,'aBcDeF','aes')='data_1';

At least, the documentation for both leads me to believe that these are equivalent.

The data was dumped from MySQL and imported into PostgreSQL.

The data-point field in MySQL is type varbinary(255) and the values all show BLOB in MySQL Workbench.
The data_point field in Postgres is type bytea and the values all show [binary data] in pgAdmin.

Whether those data types are equivalent, I admit, I'm not sure.

The MySQL query works and finds a record based on he criteria. The PostgreSQL does not, although the record is in there. So it seems like one of three things:

The data types (varbinary(255) vs bytea) are not equivalent
The data does not match (BLOB vs [binary data]) are not equivalent
The functions (AES_DECRYPT vs decrypt()) are not equivalent

I did try to decrypt the value in PostgreSQL just to see what it is in a readable format by first trying:

SELECT decrypt(data_point, 'aBcDeF', 'aes')
FROM data_tbl;

But that just returned [binary data] for all the rows.

Then I came across this answer to use convert_from as in:

SELECT convert_from(decrypt(data_point, 'aBcDeF', 'aes'), 'SQL_ASCII')
FROM data_tbl;

However, I just get this error:

ERROR:  invalid byte sequence for encoding "UTF8": 0xcf 0xf5
SQL state: 22021

So at this point I'm stumped as to how to resolve the primary issue. The primary issue being doing the equivalent of AES_DECRYPT but in PostgreSQL 11. The last error message isn't so much an issue, but it is likely related to decrypt() not returning the same results.

Any suggestions?

Actually, I suspect all three of your assumptions are correct and all play a part in why you cannot decrypt the data. I will attempt to explain a little.

One of the most important factors in encryption & decryption is binary compatibility - to be more precise, how does each database physically store the data? Is the data stored big-endian or little-endian, how are the internal data structures implemented (what is the binary difference between VARBINARY and BYTEA), what is the character set & encoding in use on each system? Encryption is bit-sensitive and so any variations in the bit/byte order will affect the outcome of any encryption or decryption.

Additionally, even though both systems can use AES their default settings vary slightly. On MySQL the default for AES is a 128bit key using ECB mode ; whereas on PostgreSQL the default is a 128bit key using CBC mode to encrypt the data. You will need to ensure that all of the options used to encrypt/decrypt are the same (even if you were trying to decrypt it within the same system).

Logically, if everything was identical between the two systems you should be able to decrypt the data. In practice, it's going to be a lot harder than that and if you have access to the source system, decrypt in on there, export the data, then import & encrypt it in PostgreSQL.


Mongodb migration and appending data to existing database I have two databases in two different machines containing data in same schema. Both have relational mappings in form of ObjectIDs as it is done in NoSQL database. The task at hand is to pick one database from machine A and append that data to another database residing in the machine B. If I simply insert the data there will be ObjectID collisions and it simply won't work.
The only option which I see is to insert the primary key collections first in the database, then insert the collections containing the foreign key mappings and replace the ObjectIDs with the newly created ObjectIDs from the primary key documents. However, it isn't as easy as it sounds because there are multiple collections which are interconnected with ObjectIDs.
Please suggest me the most efficient way in which this can be done. TIA

In mongoshell you can connect to several databases and use them at the same time. A plain simple copy would be this one:
var db_A = Mongo("mongodb://user:password@host-A").getDB("data")
var db_B = Mongo("mongodb://user:password@host-B").getDB("data")

var docs = db_A.getCollection("collection_name").find().toArray();
if (docs.length > 0) {
    db_B.getCollection("collection_name").insertMany(docs, {ordered: false});
}

Your migration script will look more complex of course, but that's the way you can do it.


MySQL Workbench: How to detach reverse-engineered EER diagram from existing database? MySQL Workbench provides the option to 'Create EER Model From Existing Database'. Trying to remove a table from the resulting diagram leads to a dialog asking:

  Should corresponding database objects be deleted with the figures?

As the database must be left untouched in any case, my question is now: How to safely detach the EER model / diagram from the underlying database?

It looks like the table is deleted in the "model" only. The original database is not harmed. One also can filter tables via the reengineering wizard in advance.


How to get MAX numeric value from a column with char values in MySql I would like to know how can I get the max value only within the numeric values in a column with char values in a MySql database:

Supposing the column is as follows:

 col
---------
ALL
USD
USD
RET
RET
ACC
ACC
ALL
ALL
ERR
ERR
PRO
PRO
1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
AY
AY
C
C
CF

I should get 9 as a result of my query.

Thanks in advance.

If the numbers are all Integer, that you want

SELECT MAX(CAST(col AS SIGNED)) from nextg WHERE col REGEXP '^[0-9]+$';

Like @Akina said in the comments MySQL knows another way

SELECT MAX(col + 0) from nextg WHERE col REGEXP '^[0-9]+$';

See example

If you have also Decimal Numbers.

SELECT MAX(CAST(col AS DECIMAL(8,2))) from nextg WHERE col
REGEXP '^[0-9]+\\.?[0-9]*$'


Most efficient queries on flags in TSQL: bit field or table join? Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values.  For example, whether or not a user has opted in to marketing communications.

The use case here is finding user data for all users who have opted in.  The Users table contains millions of rows of contact information.

Idea 1: Users table contains an indexed bit field

CREATE TABLE Users
  UserID INT PK
  ...
  OptInFlag BIT

Updating a user's OptIn preference would then just involve setting the OptInFlag to 1 or 0.
Selecting data for all users who have opted in might benefit from the index, depending on opt-in distribution:

SELECT UserID, ... FROM Users WHERE OptInFlag=1

Idea 2: Separate OptIn table that holds FK references to the UserIDs that have opted in

CREATE TABLE Users
  UserID INT PK
  ...

CREATE TABLE OptIn
  OptInID INT PK
  UserID INT FK

Updating a user's OptIn preference would involve INSERTing or DELETEing from the OptIn table.
Selecting data for all users who have opted in might benefit from the pre-filtering of the JOIN:

SELECT u.UserID, ... FROM OptIn o JOIN Users u ON o.UserID = u.UserID

Idea 2 looks like it would be the most efficient at first blush, since it effectively pre-filters the Users table before the join, but is that actually the case and are there any potential gotchas with this implementation?  For instance, I expect that selecting opted out users would be slower than in Idea 1, but that is OK in this scenario.

Is there an alternative better than either idea 1 or 2?

Edit Ran some tests: Statistics shows the Total Time for idea 1 (using a filtered index) and idea 2 to be about the same.  However, the filtered index uses 1/4 the CPU time of the other, showing the winner clearly.

It is very dependent on the makeup of the data, how the data changes over time and what queries are run as to what the most efficient method is.

I would suggest that a filtered index is the solution that is going to be the most efficient without talking about a specific scenario.

Edit:

You should add a filtered index filtered on where optIn = 1 with the fields that are required by the query of consequence as it will use this index to generate the result and not have to refer to the main table at all.  To check that it reads from just the index you can analyse the execution plan.  I would expect that the query of consequence would not require all of the fields for the user record and thus the fields in the index would be a subset of the fields in the actual user record.

In short the goal is to do a little extra work on inserting the record (updating the indexes) and storing extra data so that the query has to read a significantly smaller amount of data that is in continuous blocks on the disk (assuming we are talking about traditional disk technology) than it would if it had to scan through the whole table picking about the records and fields in those records required by the query.


How can I track if a trace is executing? I'm sure there's a trace here running and creating logs.

I have no idea who did this in the past ( Its here since my first day in this company ).

There's no trace of it on sp_whoisactive.

Activity monitor shows nothing.

Theres no job running.

There's no job about something like this ( I deleted all old jobs and created my own ones ).

But it's still creating this files. I can't delete if because SQL Server is using it. I can't stop this server because it's the production one. Maybe if I stop the server I can delete the files but they will be created again.

I'm sure there's no sign of a trace running.

This error log file is the same error log we can see on error logs > current

I think the log file you are referencing in your question is created by the SQL Server default trace.  You should be able to verify that by running:

SELECT path AS [Default Trace File]
    ,max_size AS [Max File Size of Trace File]
    ,max_files AS [Max No of Trace Files]
    ,start_time AS [Start Time]
    ,last_event_time AS [Last Event Time]
FROM sys.traces
WHERE is_default = 1
GO

There is additional useful information about Different Ways to Find Default Trace Location in SQL Server.


How do you tell which host is close to being blocked in MySQL? I have a two part question:

In cases were this error comes up, is there a way to see if a host is close to being blocked? And fire off an alert?

PDOException: SQLSTATE[HY000] [1129] Host `hostname` is blocked because of many connection errors; unblock with `mysqladmin flush-hosts`

What guidelines should one follow when increasing max_connect_errors-- ours is at 10? Is it recommended to run mysqladmin flush-hosts daily, via cron?

Running MySQL 5.5.13 (Enterprise) on Red Hat 6.

Actually, you should raise the max_connect_errors way beyond the default of 10.

The range is 1-18446744073709547520. I would set it to 1000 for now.

However, you have a bigger problem. mysqladmin requires you connect to mysqld before it does anything. If max_connect_errors is reached too quickly, you cannot make mysqladmin do anything at that point. Even a cron job will fail in this respect.

Since you are using MySQL 5.5, your best option is create a scheduled event within the MySQL Instance to go off every 5 minutes that will only do one thing: run FLUSH HOSTS.

I just did this in MySQL 5.5.12 on my PC

mysql> use test
Database changed
mysql> CREATE EVENT ev ON SCHEDULE
    ->       EVERY 5 MINUTE
    -> STARTS (NOW() + INTERVAL 1 MINUTE)
    -> DO FLUSH HOSTS;
Query OK, 0 rows affected (0.02 sec)

mysql>

Great, no complaints from mysqld. Of course, I arbitrarily chose 5 minutes. You can choose whatever schedule you prefer.

Give it a Try !!!

UPDATE 2011-08-25 11:22 EDT

For everybody using release of MySQL prior to 5.1, I have a worthwhile suggestion:

Write a perl script that keeps an open connection, making it heartbeat itself by retrieving something (i.e., uptime, aborted_connects, etc) and every 10 minutes kicking out FLUSH HOSTS. Whatever user you connect as, make sure it has RELOAD privilege.

For example: run this command:

GRANT RELOAD on *.* to 'whateveruser'@'localhost' identified by 'whateverpassword';

Here is a perl script I call heartbeat.pl to do this using that user and password:

#!/usr/bin/perl

use DBI;
use DBD::mysql;

my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;

$check_replication = 1;

$username = "whateveruser";
$password = "whateverpassword";

$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE  = "/tmp/MySQLFlatLine.txt";

$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";

if ( $SLEEP_TIME_INTERVAL == 0 )
{
        open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
        printf fh_output "DONE !!!\n";
        close fh_output;
        exit 0;
}

$db = DBI->connect("DBI:mysql:information_schema;host=127.0.0.1",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";

$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts  = $db->prepare("FLUSH HOSTS")        or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";

$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
{
        $st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
        {
                $StatusCount  = 0;
                while ( my $row = $st_globalstatus->fetchrow_hashref() )
                {
                        my $uptime = $row->{Value};
                }
                $st_globalstatus->finish();
                open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
                printf fh_output "%s : %s\n",$uptime;
                close fh_output;
        }
        for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
        {
                if ( -f $MYSQL_FLATLINE_FILE )
                {
                        unlink $MYSQL_HEARTBEAT_FILE;
                        unlink $MYSQL_FLATLINE_FILE;
                        $my_heart_is_beating = 0;
                }
                sleep 1;
        }
        $heartbeat_count++;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
        {
                $heartbeat_count = 0;
                $st_flush_hosts->execute();
        }
}

$db->disconnect();

In the OS, call the perl script to heartbeat every 6 seconds. When it heartbeats 100 times, that 600 seconds (10 minutes), make it run FLUSH HOSTS. Call it like this:

nohup perl heartbeat.pl 6 100 &

Here are other schedules:

nohup perl heartbeat.pl  6  50 & (heartbeat every  6 sec,FLUSH HOSTS every 5 minutes)
nohup perl heartbeat.pl 10 360 & (heartbeat every 10 sec,FLUSH HOSTS every 1 hour)
nohup perl heartbeat.pl 60 180 & (heartbeat every 60 sec,FLUSH HOSTS every 3 hours)

To stop the program, run this:

perl heartbeat.pl


Calculate MSB on MySQL Suppose I have Table A

|-10|
|-1 |
|0  |
|1  |
|10 |
|100|

and take the most significant bit position of each value and import it into Table B, like so:

|-4|
|-1|
|0 |
|1 |
|4 |
|7 |

How do we do it efficiently? Is there a user-defined function that mimics the behavior of __builtin_clz as with GCC?

UPDATE: Added example for zero and negative values.

Like this?

 INSERT INTO b SELECT FLOOR(LOG2(Col1)) + 1 FROM a;


Connection pooling with 3 databases but 1 database as master for repmgr replication? I'm using pgbouncer for connection pooling against 3 database on different servers, now I'm planning to use repmgr for replication, my question is, can I keep doing connection pooling on these 3 databases and have one of them as master replicating to the others, or I should have pgbouncer doing connection pooling on one database and have this as master replicating to the others?

My intention is have connections being distributed over databases and make use all database are up to date and returning updated data to users.

After research more about this topic the answer is, no, I can't, the repmgr way to work allows me to have pgbouncer connecting to master instance of postgres to handle writes and allowing to use slaves to perform reads.

I order to archive the goal exactly like I asked I must use pgbouncer for connection pooling and use pgpool2 and its Statement-Based Middleware for replication.


Managed Instance Restart Does anyone know how to restart a SQL Server Managed Instance?

The stop / start / reset are all grayed out, so that’s really. It an option.

Any thoughts?

Thanks...

You don't restart Managed Instance. The beauty of PaaS is that the cloud service provider ensures that you get a certain uptime SLA-- 99.99% in this case.

The service is abstracted away. Microsoft will ensure that the service is "always" up and running. They handle patching and maintenance on the OS and the instance (and necessary restarts that go along with those things).

If you need to stop, scale, or make other changes to the instance, you'd do that from the Azure portal (either the web GUI or PowerShell).


How can I benchmark the performance of UDFs to figure out which one is faster? Basically I have two scalar UDFs that output the same data, one that uses a cursor and one that uses a recursive CTE. I want to determine which one should be used and discard the other; I'd prefer to make this decision based on actual performance numbers. I'm also curious how much of a difference there is between the two.

How can I benchmark the performance of these UDFs to figure out which one is faster?

The SQL Profiler, execution plan, IO/Cpu statistics are your best friend at checking the performance of your code. You will see if there are any differences in performance between the 2 versions of functions.

In this question - Tuning exercises - you can find some basic and advanced references about tuning.


MariaDB compare JSON How to compare two JSON fields in MariaDB?

MariaDB 10.2 and 10.3 - FAILS

SELECT CAST('{"q":2}' AS JSON);

Returns : SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON)' at line 1

MySQL 5.7.19 - works OK

SELECT CAST('{"num": 1.1, "date": "2015-11-01"}' AS JSON) = CAST('{"date": "2015-11-01", "num": 1.1}' AS JSON);

Returns: 1

  Source: http://rpbouman.blogspot.com/2015/11/mysql-few-observations-on-json-type.html

Other similar questions

I found this but my JSON is more complex.

There is not, as of 10.3.7, a native JSON type on mariadb server. JSON is only an alias for LONGBLOB for compatibility reasons with MySQL: https://mariadb.com/kb/en/library/json-data-type/

While there is a library of function to work with JSON https://mariadb.com/kb/en/library/json-functions/ (select and transform it); there isn't really a native type, so all input and output of those functions is really of type string. While you could technically implement a recursive json parser with a stored procedure, I would recommend to just read it and compare it with client libraries or other client utilities (for example, https://stackoverflow.com/questions/25851183/how-to-compare-two-json-objects-with-the-same-elements-in-a-different-order-equa ).

Despite the claims by MariaDB that it is a drop-in replacement of MySQL, because MariaDB is no longer a 100% compatible fork, functionalities that are available on one software are no longer available on the other and viceversa.


How do you ORDER BY a parameter? Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!).

Basically I have a single SP that I call to return a list of records (Jobs) that may have one or more statuses and a sort order (I'm using RowNum for paging). At the moment I'm using WITH RECOMPILE because the variations on the statuses can change all the time (depending on user etc). There's also some filtering going on.

I'm using an IF statement to essentially run the same bit of code with the only change being the sort order.

I guess my questions are: Is there a better way of doing this (maybe different SP's for different statuses)? Am I overcomplicating things due to lack of knowledge (quite likely) Is the SP actually ok, but requires minor tweaks to reduce the number of lines?

I've pasted a portion of the SP below - the only difference to the full code is the additional IF statements for the different sort orders...

I'd appreciate any feedback.

Thanks in advance!

PROCEDURE [dbo].[sp_Jobs]

@PageNumber int,
@PageSize int,
@FilterExpression varchar(500),
@OrderBy varchar(50),
@CustomerID int,
@ShowNotSet bit,
@ShowPlaced bit,
@ShowProofed bit,
@ShowReProofed bit,
@ShowApproved bit,
@ShowOnTime bit,
@ShowLate bit,
@ShowProblem bit,
@ShowCompleted bit,
@ShowDispatched bit,
@ShowUnapproved bit,
@ShowClosed bit,
@ShowReturned bit,
@UserID int

WITH RECOMPILE

AS

--JobNumber DESC
if @OrderBy='JobNumberDESC'
BEGIN

WITH Keys AS (SELECT TOP (@PageNumber * @PageSize) ROW_NUMBER() OVER (ORDER BY JobNumber DESC) as rn,P1.jobNumber,P1.CustID,P1.DateIn,P1.DateDue,P1.DateOut,p1.client,p1.MasterJobStatusID,p1.MasterJobStatusTimestamp,p1.OwnerID

FROM
vw_Jobs_List P1 WITH (NOLOCK)

WHERE
(@CustomerID = 0 OR CustID = @CustomerID)
AND (@UserID = 0 OR OwnerID = @UserID)
AND ((@ShowNotSet = 1 AND MasterJobStatusID=1) OR (@ShowPlaced = 1 AND MasterJobStatusID=2) OR (@ShowProofed = 1 AND MasterJobStatusID=3) OR (@ShowReProofed = 1 AND MasterJobStatusID=4) OR (@ShowApproved = 1 AND MasterJobStatusID=5) OR (@ShowOnTime = 1 AND MasterJobStatusID=6) OR (@ShowLate = 1 AND MasterJobStatusID=7) OR (@ShowProblem = 1 AND MasterJobStatusID=8) OR (@ShowCompleted = 1 AND MasterJobStatusID=9) OR (@ShowDispatched = 1 AND MasterJobStatusID=10) OR (@ShowUnapproved = 1 AND MasterJobStatusID=11) OR (@ShowClosed = 1 AND MasterJobStatusID=12) OR (@ShowReturned = 1 AND MasterJobStatusID=13)) AND (Search LIKE '%'+@FilterExpression+'%')

ORDER BY
P1.JobNumber DESC ),SelectedKeys AS (
SELECT TOP (@PageSize)SK.rn,SK.JobNumber,SK.CustID,SK.DateIn,SK.DateDue,SK.DateOut

FROM
Keys SK

WHERE
SK.rn > ((@PageNumber-1) * @PageSize)

ORDER BY
SK.JobNumber DESC)

SELECT SK.rn,J.JobNumber,J.OwnerID,J.Description,J.Client,SK.CustID,OrderNumber, CAST(DateAdd(d, -2, CAST(isnull(SK.DateIn,0) AS DateTime)) AS nvarchar) AS DateIn, CAST(DateAdd(d, -2, CAST(isnull(SK.DateDue,0) AS DateTime)) AS nvarchar) AS DateDue,CAST(DateAdd(d, -2, CAST(isnull(SK.DateOut,0) AS DateTime)) AS nvarchar) AS DateOut, Del_Method,Ticket#, InvoiceEmailed, InvoicePrinted, InvoiceExported, InvoiceComplete, JobStatus,j.MasterJobStatusID,j.MasterJobStatusTimestamp,js.MasterJobStatus

FROM SelectedKeys SK JOIN vw_Jobs_List J WITH (NOLOCK) ON j.JobNumber=SK.JobNumber JOIN tbl_SYSTEM_MasterJobStatus js WITH (NOLOCK) ON j.MasterJobStatusID=js.MasterJobStatusID

ORDER BY
SK.JobNumber DESC
END

--ELSE IF for other column sorting

The sorting can be taken care of with a CASE expression, something along the lines of:

ORDER BY
    CASE WHEN @SortDirection = 'A' THEN
        CASE
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId
        END
    END ASC
    , CASE WHEN @SortDirection = 'D' THEN
        CASE
           WHEN @SortBy = 'JobNumber' THEN JobNumber
           WHEN @SortBy = 'JobId' THEN JobId
        END
    END DESC

You may want to reconsider the OR'd where conditions as they are likely to generate poor plans. One of the best articles I've read covering this (and the alternative approaches) is Dynamic Search Conditions in T-SQL

Edit: Looking again at your parameter list, the primary filters appear to be @CustomerId and @UserId. I'd suggest creating two procs, spJobs_SelectByCustomerId and spJobs_SelectByUserId, which filter by their respective parameters so you eliminate the '@Param = 0 or Column = @Param' conditions. I guess the next important param is @ShowCompleted (presuming that once a job is 'done', it isn't shown unless @ShowCompleted=1), which i'd consider including in indexes on the CustomerId and UserId.

Edit2: Funny how these questions sometimes tick over in the back of your mind! :) On indexing @ShowCompleted, this is one of the occasions where Using a Low-Selectivity BIT Column First Can Be the Best Strategy. Filtered indexes should also be considered.


Create index for WHERE COALESCE() condition I'm using PostgreSQL V9.6.11

Table DDL:

CREATE TABLE test_c (
          insrt_prcs_id bigint NOT NULL,
          updt_prcs_id bigint, src_sys_id integer NOT NULL,
          load_dttm timestamp(6) with time zone NOT NULL,
          updt_dttm timestamp(6) without time zone);

I was trying to create an index for the query below:

SELECT *
FROM test_c
WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07'

Create index as:

create index idx_test_c  on test_c(COALESCE((updt_dttm, load_dttm)))

But the query planner is not scanning the index:

EXPLAIN ANALYZE
SELECT *
FROM test_c
WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07'

Seq Scan on test_c as test_c (cost=0..1857.08 rows=207 width=496) (actual=5.203..5.203 rows=0 loops=1)
Filter: ((COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) > '2020-04-10 15:29:44.596311-07'::timestamp with time zone) AND (COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) <= '2020-04-11 15:29:44.596311-07'::timestamp with time zone))
Rows Removed by Filter: 41304

Why is the index scan not happening?

Strip the incorrect pair of parentheses:

CREATE INDEX idx_test_c ON test_c(COALESCE(updt_dttm, load_dttm));

db<>fiddle here

The way you had it was effectively indexing the composite value (updt_dttm, load_dttm), COALESCE not doing anything.

Your second problem was revealed by the added table definition:

CREATE TABLE test_c (
  insrt_prcs_id bigint NOT NULL
, updt_prcs_id  bigint
, src_sys_id    integer NOT NULL
, load_dttm     timestamp(6) with time zone NOT NULL
, updt_dttm     timestamp(6) without time zone   -- !!!
);

Why would you use different data types for load_dttm and updt_dttm? Fix that and the second problem goes away. I suggest:

CREATE TABLE test_c (
  -- ...
, load_dttm     timestamp with time zone NOT NULL
, updt_dttm     timestamp with time zone  -- !!!
);

Why?

You got this error:

  ERROR: functions in index expression must be marked IMMUTABLE

.. because COALESCE must return one data type. timestamp with time zone (timestamtz) is the "preferred type", so updt_dttm timestamp is coerced to timestamptz, which uses a function that depends on the timezone setting of the current session and hence is not IMMUTABLE. And index expressions cannot involve non-IMMUTABLE functions.

Related:

Is timestamptz preferred when timezone logic is performed by the client?
Ignoring time zones altogether in Rails and PostgreSQL

You could make the index work with your original (broken) table design by hard-coding the time zone - 'Europe/Vienna' in my example:

CREATE INDEX ON test_c(COALESCE(updt_dttm AT TIME ZONE 'Europe/Vienna', load_dttm));

Just a proof of concept. Queries hoping to use that index would have to use the same expression. Don't go there if you don't have to. Fix your table definition instead.


SQL Server MAXDOP on VMWare Virtual Servers When setting MAXDOP on VMWare virtual servers VMWare recommend following the Microsoft guidlines

https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

https://support.microsoft.com/en-gb/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

Keep MAXDOP at or below # of logical processors
Keep MAXDOP at 8
Keep MAXDOP at or below # of logical processors per NUMA node
Keep MAXDOP at 8

A colleague argues that virtual CPUs are different, SQL Server can only access 1 CPU no matter how many you have and MAXDOP should be set to 1. Based on the above information and lots of other websites I think that is wrong, and bad for performance. I have set MAXDOP as above for years. But is there any truth in what they are saying.

Many Thanks

Your colleague is wrong. SQL Server will use parallelism whether the cores are virtual or not.

In fact the VMWare SQL Server best practices guide you link to explicitly states:

  There is a fair amount of misconception and incorrect advice on the
  Internet regarding  the values of these configurations in a virtual
  environment. When low performance is  observed on their database, and
  CXPACKET is high, many DBAs decide to disable  parallelism altogether
  by setting MAXDOP value to one (1). This is not recommended  because
  there might be large jobs that will benefit from processing on
  multiple CPUs.  The recommendation instead is to increase the CTFP
  value from five seconds to  approximately 50 seconds to make sure only
  large queries run in parallel. Set the  MAXDOP according to
  Microsoft’s recommendation for the number of cores in the  VM’s NUMA
  node (no more than eight).

I will however note that the writer of the VMWare best practices guide is confused. The value is not in seconds but in "cost" (also known as query bucks)


Not able to produce table based on conditional select I would like to achieve something that probably ends up in some kind of conditional select (MS SQL) but I do not know how to.

I have tried to simplified my problem to the description below:

I have 3 tables: 1 containing Articles (Art_number, Art_description and Productgroup), the second with Customers (Cust_number and Cust_name) and a third table containing discount information (Cust_number, Productgroup and Discount_percentage)

Table: Articles

001,fork,cutlery
002,knife,cutlery
003,plate,tableware
004,cup,tableware

Table: Customer

1234,smith
5678,jones

Table: Discounts

1234,cutlery,0.9
1234,tableware,0.8
5678,cutlery,0.75
5678,tableware,0.7

My questions is about the relation with a 4th table that contains some (not all!) alternative (customer related) article numbers named Cross_Ref that links the first two tables by containing a article number and a customernumber (Art_number, Alt_Art_Nr and Customer_Nr)

Table: Cross_Ref

002,1002,1234
002,92002,5678
004,1004,1234
004,92004,5678

I would like to produce a table that shows the article numbers in the first column,
Article description in the second column.
The alternative article numbers in the third, column (if available, otherwise empty) and the Customer name in 4th column

It should look like this with the information above:

001,fork,,smith
001,fork,,jones
002,knife,1002,smith
002,knife,92002,jones
003,plate,,smith
003,plate,,jones
004,cup,1004,smith
004,cup,92004,jones

My query currently looks like this:

SELECT Art_number, Art_description, Alt_Art_Nr, Cust_name
FROM Articles,
Customer,
Cross_Ref
WHERE Articles.Art_number = Cross_Ref.Art_number
and Customer.Cust_number = Cross_Ref.Customer_Nr
and Customer.Cust_number = Discounts.Cust_number
and Articles.Productgroup = Discounts.Productgroup

This only shows me those lines that indeed have an alternative article number, but do not show the articles that do not have a reference mentioned in the Cross_Ref table.

In principle it is a combination of 4 tables that are connected fully by 3 tables, but the 4th table only contains a number of the articles (that have a alternative number) and thereby resulting in only those articlenumbers in the query result, while I would like to see all the article numbers and empty (NULL) fields, if there is no result.

Can anybody show me an example how to archive this?

You're probably looking at needing an OUTER JOIN instead of the implied INNER JOINs that you're using. e.g. suppose

   Table 1 Employee (Empl_number, Empl_Name)
   001, Paul
   002, John
   003, Fred

   Table 2 EmployeeRoles (Role_ID, Role_Desc)
   100, Manager
   200, Clerk
   300, KeyHolder

   Table 3 RoleLink (ID, Empl_number, Role_ID
   1, 001, 100
   2, 001, 300
   3, 002, 200

Then to get a list of all employees and their roles you would use

   SELECT e.empl_number, e.empl_name, ISNULL(r.role_desc, 'No role')
   FROM EMPLOYEE e
   LEFT OUTER JOIN ROLELINK l on e.empl_number = l.empl_number
   LEFT OUTER JOIN EMPLOYEEROLES r on l.role_id = r.role_id

Would give

   001, Paul, Manager
   001, Paul, Keyholder
   002, John, No role
   003, Fred, Clerk


How to Store Skip Lists on Disk I am new to thinking in terms of databases. I have read about how B-trees are stored on disk but I haven't seen an actual implementation or read about how it works for B-trees.

Wondering how one might store Skip Lists on disk. There are many descriptions for storing it with pointers in memory, but I haven't seen anything on disk storage. Wondering what it might look like at a high level (if it's just one big file or many small files, if there are different types of files, etc.).

A DBMS is an implementation of a datamodel - a structure for how data will be represented. The relational model represents data as tables and columns, graph model as nodes and edges, document model as nested key-value pairs.
If your objective is to persist a skip list using an existing DBMS you must map its parts to the model's corresponding affordances.

If the objective is to implement this feature within a DBMS you're no longer bound by that DBMS's external model. A simple implementation would be to replace the memory pointers with the corresponding disk pointers (file_id, offset). By careful arrangement random / sequential reads can be optimised.

I remember some of the MySQL clones have implemented this. Their source is available. These would also include BTree implementations.


What can cause statistics to get out of line? I've just worked through a problem at a clients site which it turned out was caused by the statistics being wrong which caused the Optimizer to time out. Running exec sp_updatestats fixed the problem and all is now good.

What I am a bit confused about now is how did the statistics get out of line in the first place?

The database has both auto_create_stats and auto_update_stats switched on. So SQL Server should have kept the statistics up to date without any intervention.

So why did it fail in this instance?

This client had recently upgraded their database server. They handled it themselves, so I'm not exactly sure what procedure they went through, but I can't imagine it was anything more complicated than backing the database up on the old server and restoring it on the new one. Could this have caused the glitch somehow?

I'll guide you to an answer to an older question. Please read the comments, too, they are very valuable.

In short, MSDN says:

"Insert Operations Occur on Ascending or Descending Key Columns Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the query optimizer performs. Insert operations append new values to ascending or descending columns. The number of rows added might be too small to trigger a statistics update. If statistics are not up-to-date and queries select from the most recently added rows, the current statistics will not have cardinality estimates for these new values. This can result in inaccurate cardinality estimates and slow query performance.

For example, a query that selects from the most recent sales order dates will have inaccurate cardinality estimates if the statistics are not updated to include cardinality estimates for the most recent sales order dates.

After Maintenance Operations Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates."

So you would benefit from searching for statistics that were not updated recently.


is "%LIKE%" faster than executing multiple queries? for example, each user on my site has 2 phone numbers (i.e. let say 1234 and 6789) .

but about performance , which is faster:

to save the numbers in one column(separated by comma,like [1234,6789] ), and thus, searching the columns with :

select  userID from table where number LIKE %1234%

or saving values into 2 columns

select  userID from table where number1=1234 or number2=1234

which will be faster? lets say i have thousands or millions of rows.

This is bad - it is a table scan

LIKE %1234

But thousands of rows is not much

Best would be to have second table with phone

phone:
userID PK FK to user
number PK

select *
from user
join phone
       on phone.userID = user.userid
      and phone.numer = '1234'

if you don't want to fix the data design then two column with = will be faster


After server move a query doesn't work anymore I've moved an ASP+SQLServer application from an old server to a new one.
The old one was a Windows 2000 server with MSDE, and the new one runs Windows 2008 with SQL 2008 Express.

Everything is ok, even a little faster, except just one function whose ASP page gives a time out.
I've tryed the query within that page in a management query windows and it never ends. In the old server it took about 1 minute to be completed.

Here is the query:

SELECT DISTINCT TBL1.TBL1_ID, REPLACE(TBL1_TITOLO, CHAR(13)+CHAR(10), ’ ’), COALESCE(TBL1_DURATA, 0), TBL1_NUMERO, FLAG_AUDIO
FROM SPOT AS TBL1
INNER JOIN CROSS_SPOT AS CRS ON CRS.TBL1_ID=TBL1.TBL1_ID
INNER JOIN DESTINATARI_SPOT AS DSP ON DSP.TBL1_ID = TBL1.TBL1_ID
WHERE DSP.PTD_ID_PUNTO = 1044
AND DSP.DSP_FLAG_OK = 1 AND TBL1.FLAG_AUDIO_TESTO=1 AND TBL1.FLAG_AUDIO_GRAFICO=’A’ AND CRS.CRS_STATO>2
OR TBL1.TBL1_ID IN (SELECT ID FROM V_VIEW1 WHERE ID IS NOT NULL AND V_VIEW1.ID_MODULO = 403721)
OR TBL1.TBL1_ID IN (SELECT TBL1_ID FROM V_VIEW2 WHERE V_VIEW2.ID_PUNTO = 1044)
ORDER BY TBL1_NUMERO

I've tried to transform the two views in the last lines into tables and the query works, even if it's a little slower than before. I've migrated the database with its backup/restore function. Could it be an index problem? Any other suggestions?

How big are the tables? Sounds like an index problem. Do you have the same indexes on both servers? Did you rebuild the indexes after restoring?

As TiCL suggested, the best way is to compare the execution plans of both servers.


How should I set up SQL Server Login permissions for access to website on a different server using Windows Authentication I know this question doesn't really belong here, but I posted on the MS IIS Site and am still hoping for a reply. I have an intranet site created with .NET Framework 4.0 in Visual Studio 2012 and I used the VS2012 publish command with "Window Authentication". The SQL Server is on a different server than the Web Server. I have set up and given the correct permissions to the Application pool for both the NTFS and SQL Server (remember my SQL Server is on a different (virtual) Server. I used this document to set up the particulars:

IIS Setup Win2008R2 Server With IIS 7.0

I also added the machine name (Domain\MachineName$) as a user to the SQL Server, as I couldn't find a way to add the app pool as a user to a SQL Server that wasn't on the same box. Bonus Question -- Is there a way to get the app pool from one server recognized on a different server... it actually came up in intellisense in SQL Server Management Studio, but I wasn't able to add it?

Everything works for "My" login (which is a member of the "administrator" group), but I was unable to allow users to get access to the intranet website unless I added their username to the root folder of the website and gave them permissions.

My Questions:  (Bearing in mind that this is an intranet app that will only be accessed by authenticated users)

1)  Is it OK from a security standpoint to grant users access to the root of the website? If not ... what am I missing... If this is a no-go from a security standpoint, what are the better ways to do this? If it is OK, what permissions do they need?

Bonus Question:

2) Is there a way to get the app pool from one server recognized on a different server?

In order to get the application pool account to be recognized by the SQL Server you'll have to configure the application pool to run under a domain account.  Then give this account the rights that it needs within the SQL Server instance.

If you are using Windows auth then your users will need read access to the folder which is hosting the website, otherwise they won't be able to read the files for the website.  This is normal. Just give the users of the website read only rights to the folder which hosts the website.


trigger on 2 tables one to many Here are two tables :
table1

id1(pk) objectid z
0        1       100
1        2       98

table2

id2(pk) n_sd prof alt_inf
0        1   5
1        1   15
2        2   8

objectid -> n_sd (1->n).

The aim of the trigger is on insert on table2 to do the maths alt_inf = z-prof for objectid = n_sd.

IF (TG_OP = 'INSERT') THEN
NEW.alti_inf = (select z-NEW.prof from table1 join table2
on table2.n_sd = table1.objectid
where new.gid = table2.gid )
RETURN NEW;
END IF ;

DROP TRIGGER IF EXISTS trg_calcul_altiinf ON table2 ;
CREATE TRIGGER trg_calcul_altiinf AFTER INSERT ON table2
FOR EACH ROW EXECUTE PROCEDURE calcul_altiInf();

I have no result nor error message.

In a trigger fired AFTER the insertion it makes no sense to modify the NEW record, as it has been already inserted. Use a BEFORE trigger.

CREATE TRIGGER trg_calcul_altiinf
BEFORE INSERT OR UPDATE ON table2    -- + UPDATE
FOR EACH ROW EXECUTE PROCEDURE calcul_altiInf();

In the function body you should query only the table1, as the current value of n_sd is in the NEW record:

IF TG_OP IN ('INSERT', 'UPDATE') THEN    -- + UPDATE
    NEW.alti_inf = (
        SELECT z - NEW.prof
        FROM table1
        WHERE NEW.n_sd = table1.objectid);
    RETURN NEW;
END IF;


TSQL query for viewing start/end of all SQL Agent jobs yesterday See this related query: What's a good tool for visualizing SQL Agent jobs?

I'm not after a visual tool however, since I am looking to build my own visualisation.  This is my starting point, but you'll agree it's sorely lacking.

select TOP(1000)
       h.run_date, min(h.run_time), j.name, j.description, max(h.run_duration) run_duration
from msdb..sysjobhistory h
join msdb..sysjobs j on j.job_id = h.job_id
group by h.run_date, j.name, j.description, h.instance_id, j.job_id
having max(h.run_duration) > 1000  -- << -- 10 minutes encoded in HHMMSS (digits)
order by h.run_date desc, 2 desc

The crux is that there does not seem to be a key to the sysjobhistory table to identify it as a particular "instance" of a sysjob.  For example, a job that started at 12:30 with 4 steps gets inserted into sysjobhistory 4 times with different instance_ids (unique).  They all tie to the sysjob.job_id value, but that's the schedule, not the invocation of the job.  Is there another table in which I should look?

This is probably fairly simple, since SSMS is able to do it easily.

You just need to filter where the step_id = 0 which is the job outcome and has the total run duration.

SELECT TOP ( 1000 )
        h.run_date ,
        MIN(h.run_time) ,
        j.name ,
        j.description ,
        MAX(h.run_duration) run_duration
FROM    msdb..sysjobhistory h
        JOIN msdb..sysjobs j ON j.job_id = h.job_id
WHERE   step_id = 0
GROUP BY h.run_date ,
        j.name ,
        j.description ,
        h.instance_id ,
        j.job_id
HAVING  MAX(h.run_duration) > 1000  -- << -- 10 minutes encoded in HHMMSS (digits)
ORDER BY h.run_date DESC ,
        2 DESC


MariaDB Column Store Suitability for Data Warehouse We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column Store an attractive option. However, we are all non-professionals in the field of data warehousing. That’s why I am would like to hear some opinions on the applicability and hardware required.
The data from the source systems (Oracle, IBM DB2, MariaDBs) sum up to 1 TB (including PK/FK and indexes) containing 36 months of data history. The data consist of tables with mostly integer keys, a mix of integers/doubles (65%), mostly shorter VARCAHRs (30%) and 5% of GEO-data. The data comes from 15-20 different topics with different weights. Meaning some make 50GB some 5GB in the data warehouse. Not all topics (tables) can and will be joined, because there are simply no common keys. For the reporting case, the different topics will mostly be queried isolated (max. five topics will be joined). To query all time points (months) will be used seldomly. I think it is reasonable to assume up to 17 months of data will be quered for product performance comparison. New data will be written overnight in batch jobs.
The MariaDB database server is planned to run on a 8 phy. core machine with 1,25 TB of SSD storage and 64GB of RAM. In total, we expect about 50 people querying the data, 45 of which use aggregated reporting tables and 5 are heavy analytics users – joining tables, grouping, exporting stuff to other ML tools. Complex string searches are not in our scope for the data warehouse. We do not expect the 45 people to query the data at the same time, but up to 20 concurrent users can be possible. We do not want basic reporting queries to take 10 seconds or longer. More complex queries for the 5 analytical users can take longer.
Some considerations we had and I would be happy to get your opinions on:

Do commercial vendors offer dramatically better solutions than MariaDB column store, which we should consider for our case? Or is it possible to do it with an open source RDBMS?
Does it make sense to have one stronger machine, or 2-3 weaker ones balancing the load a little better? Would they then need a full copy of all data? (so 1,25 TB SSDs x 3 Workers?).
Does it make sense to not have one big SSD, but five (256 GB each) smaller ones to increase IO? (Saving topics of similar tables on the same SSD, because they will be joined more frequently.)
Does it make sense to have 64GB or RAM, or is more needed? What are the determinants of RAM needed?

As you can see, our lack of experience causes some fundamental questions of the hardware required. Is it even possible with the information provided to draw conclusions on the possible performance? If not, what kind of information is needed?

This is what I say about MySQL/MariaDB without columnstore:
Not good:  "sum up to 1 TB" versus "1,25 TB of SSD storage".  As a Rule of Thumb, you should have half your disk free for maintenance and growth.  As a minimum, there should be enough room for an extra copy of the largest table -- data+indexes.  This allows any ALTER to run without running out of disk space.
Let's discuss the largest table.  Please show us SHOW CREATE TABLE and the main queries -- for inserting, for updating and deleting (if needed), and for selecting.  With that we can discuss shrinking the disk footprint, indexing, and partitioning (or not).
Here's my discussion of DW: http://mysql.rjweb.org/doc.php/datawarehouse See also its link to a Summary table discussion.
Please elaborate on "simply no common keys".
With "overnight batch loading", I highly recommend:

load into a temp table;
normalize (tips on that in another link);
augment the summary table(s) from the temp table;
copy the data into the main Fact table;
drop the temp table.

If you are "purging" "old" data, then PARTITION the table so as to replace the big, slow, DELETE with a fast DROP PARTITION.  More details: http://mysql.rjweb.org/doc.php/partitionmaint
Your other numeric specs are not scary but could be problematic.  Need to get into the actual analytic queries to see.
Note: if your team grows and the system fails to provide adequate concurrent queries, you can build a Replication system, wherein the Primary copies to read-only Replicas.  Each Replica can independently handle several big analysis queries.
The amount of RAM will be determined by the table sizes, query complexity, etc.  More ram is better -- but there is a point of "diminishing returns" that cannot be predicted.  Start with your 64GB, try to optimize the "worst queries"; then decide whether to add Replicas.
The layout of the SSDs won't matter much.  (Aside from needing more room.)  With multiple small drives, you could use RAID striping and/or parity.  I would want a raid controller with battery-backed-write-cache (costly, good, but probably overkill since you write only nightly, and possibly don't care if it takes 2 hours instead of 1?)  That would allow for `RAID-5 deployment of 4x500g or 7x250G drives,  Either would give you effectively 1.5TB plus parity.
(Years ago, I did a similar project (0.5TB data; HHD; RAID-5; hourly load; 7 summary tables loaded in about 7 minutes.  Primary-Replica was implemented, but was overkill in my case.  The Summary tables turned hour-long queries into minute-long queries; YMMV.)
columnstore
Columnstore shrinks typical data by 10x.  (I suspect that a well designed schema on InnoDB can't be shrunk by that much.)  Some of the performance characteristics come from this compression; some from parallel operations.
What does your 1TB come from?  Does it mean that you would need only 0.1TB of disk for Columnstore?  Or that InnoDB would need 10TB?  (My discussions above may need adjusting accordingly.)
Columnstore is excellent at querying on an arbitrary column because every column is indexed.  But when you filter on more than one column, it will efficiently filter on one, then (perhaps) have to brute force on the others.
Without more insight into your queries, I can't judge between

Columnstore
MySQL/MariaDB with regular indexing
MySQL/MariaDB with Partitioning / FULLTEXT / Spatial

It is likely that CS will outperform or underperform an equivalent database without CS -- depending on the query.