Convert a number to a letter postgresql i have a function that convert a number to a letter.
i want to optimise this function. see query: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=2586d3e0b98df1c899defc7b4078e1ef
i want to have decimal part also.
now this function just return integer part.
example:
If i have 203.145, I want to get in letter 'Two hundred three point one hundred forty-five',
Now this function just return 'Two hundred three'
Query uses:
CREATE OR REPLACE FUNCTION nombreenlettre1(
    nombre numeric)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE STRICT PARALLEL UNSAFE
AS $BODY$
/*
  Converti un nombre en lettre

  Exemple :  SELECT nombreenlettre(5423);
             cinq mille quatre cent vingt trois
 */
DECLARE
 unite text[]='{"un","deux","trois","quatre","cinq","six",
                "sept","huit","neuf","dix","onze","douze",
                "treize","quatorze","quinze","seize"}';
 dizaine text[] ='{"vingt","trente","quarante","cinquante",
                   "soixante","","quatre-vingt"}';
 coefs text[] ='{"cent","mille","million","milliard"}';
 temps text;
 c int2;
 d int2;
 u int2;
 coef int2 = 0;
 i int2;
 neg boolean;
 resultat text = '';
 n bigint;
BEGIN
  n:=nombre;

  IF n=0 THEN
   return 'zéro';
  END IF;

  neg:=n<0;
  IF neg=true THEN
    n:=-n;
  END IF;

  LOOP
   u:=n%10;
   n:=n/10;
   d:=n%10;
   n:=n/10;
   IF d in (1,7,9) THEN
     d:=d-1;
     u:=u+10;
   END IF;
   temps:='';
   IF d>1 THEN
    temps:=' '||dizaine[d-1];
    IF (d<8) and ((u=1) or (u=11)) THEN
     temps:=temps||' et';
    END IF;
   END IF;
   IF u>16 THEN
    temps:=temps||' '||unite[10];
    u:=u-10;
   END IF;
   IF u>0 THEN
    temps:=temps||' '||unite[u];
   END IF;
   IF (resultat='') and (d=8) and (u=0) THEN
    resultat:='s';
   END IF;
   resultat:=temps||resultat;
   c:=n%10;
   n:=n/10;
   IF c>0 then
    temps:='';
    IF c>1 THEN
     temps:=' '||unite[c]||temps;
    END IF;
    temps:=temps||' '||coefs[1];
    IF (resultat='') and (c>1) THEN
     resultat:='s';
    END IF;
    resultat:=temps||resultat;
   END IF;
   IF n>0 THEN
    coef:=coef+1;
    i:=n%1000;
    IF (i>1) and (coef>1) THEN
     resultat:='s'||resultat;
    END IF;
    IF i>0 THEN
     resultat:=' '||coefs[coef+1]||resultat;
    END IF;
    IF (i=1) and (coef=1) THEN
     n:=n-1;
    END IF;
   END IF;
   EXIT WHEN n=0;
  END LOOP;

  IF neg THEN
   resultat:='moins'||resultat;
  END IF;

  return resultat;
END;
$BODY$;

select nombreenlettre1(2053.234)

you can convert in case you have floationg number in two parts , integer and floating and double call your function.
in fact the result you need for 2053.234 is
select concat(nombreenlettre1(2053), 'point', select nombreenlettre1(234))

you need just to wrap your function with a substr of your first float to get two degits.


SQL Server Service can not start up I having the following issues, anyone has faced this ‘msdb110_upgrade.sql’ encountered error 2627, state 1, severity:

  Cannot recover the master database. SQL Server is unable to run.
  Restore master from a full backup, repair it, or rebuild it. For more
  information about how to rebuild the master database, see SQL Server
  Books Online.

  Script level upgrade for database ‘master’ failed because upgrade step
  ‘msdb110_upgrade.sql’ encountered error 2627, state 1, severity 14.
  This is a serious error condition which might interfere with regular
  operation and the database will be taken offline. If the error
  happened during upgrade of the ‘master’ database, it will prevent the
  entire SQL Server instance from starting. Examine the previous
  errorlog entries for errors, take the appropriate corrective actions
  and re-start the database so that the script upgrade steps run to
  completion.

After an upgrade /patch of your SQL Server, the databases usually the system databases undergo an upgrade as well via scripts. If the upgrade encountered any issues, it is possible that you could corrupt or script upgrade would not be successful.

Couple of things you can do:

Perform a repair of your SQL Server since you are using SQL Server 2012
SQL Server 2012 does come with template system databases when you first install.

  C:\Program Files\Microsoft SQL
  Server\MSSQL11.[instance_name]\MSSQL\Binn\Templates

This is the location where the system databases are stored. Remember these are templates from your first time installing so it will not have any custom configurations, logins, jobs, and more. You will have to copy it to the location of your corrupted system database and replace it.

Option 3 is the use of a trace flag 902 which will bypass the script upgrade during SQL Server startup. https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance.


Table clean up taking too long I am selecting around 10 million rows which are invalid from table grid into ##grid_temp and deleting their related info from grid_info table. I am running this in a python script. It is taking more than 6 hours. How can I optimize here?

query = """ select grid_id from grid into ##grid_temp
            where
            ....some logic.....

           set rowcount 10000
               while 1 = 1
               begin delete from grid_info where grid_id in (select grid_id from ##grid_temp)
               if @@rowcount = 0
                   break
           end
           set rowcount 0
         """
db_conn.execute_query(query);

You have a performance problem, so investigate it like a performance problem. Use a methodology like Waits and Queues. This will reveal what is the performance bottleneck, including any blocking that may occur. Likely culprits are blocking (your query is blocked by... you and did not do anything in the past 6 hours), log growth, just poor performance (lack of indexes, including on the ##temp_table).

As a side note the use of SET ROWCOUNT to restrict deletes is deprecated and should be replaced with usage of TOP keyword.


Mysql query for get repeated customers list I have a two tables oc_order and oc_customer. I want to get customers who order more than once.

This is sample:

oc_order:

order_id | cus_id | cus_name
-------------------------------
1        | 1      | Kumar
2        | 2      | Vimal
3        | 1      | Kumar
4        | 3      | Ramesh

oc_customer

  cus_id   | cus_name |  cus_address
    -----------------------------------
    1        | Kumar    |  Chennai
    2        | Vimal    |  Coimbatore
    3        | Ramesh   |  Selam
    4        | Deepan   |  Chennai
    5        | Ashok    |  Coimbatore

  For the above example i want to select only repeated order customer details only.

I mean now select only cus_id- 1 details, because customer one order more then one time.

This is the query am used

SELECT *
FROM oc_order o
LEFT JOIN oc_customer oc ON (o.customer_id = oc.customer_id)
WHERE  (SELECT *
        FROM oc_order
        WHERE customer_id=o.customer_id)

SELECT c.*
FROM oc_customer c
JOIN oc_order o ON c.cus_id = o.cus_id
GROUP BY c.cus_id
HAVING COUNT(o.order_id) > 1


Database full backup,differential backup and log backup plan I am working with DB backup, first I would like to get a roughly plan on weekly/daily backup. I have countered some problems when I am trying to get such a plan, What I tried is:

For weeklyFULL DATABASE BACKUP, using: BACKUP DATABASE name To Disk... WITH INIT,SKIP;

Can I set it to SKIP and overwrite the old full backup file every week? If I did not add EXPIREDATE or RETAINDAYS, does that mean the current backup will not overwrite the existing one because the old one never expires?

For daily Differential DATABASE BACKUP, using BACKUP DATABASE name To Disk... WITH DIFFERENTIAL,INIT,SKIP;
How does the SQL Server know which full backup is daily differential based on? By using WITH FORMAT, MediaName='...'? If so, does that mean for the first time I do the full database backup I need to format a new media set such as DatabaseA set for that database, and for the later back up, I could use WITH NOFORMAT, medianame = DatabaseA' to let sql server knows that differential backup should be attached to that full backup database?

For transaction log backup, using BACKUP LOG A TO DISK...
I know it is not good to add WITH INIT to log backup, but how to handle the increasing size of log backup? Shrinkfile(log)each time I did the full database backup? Does that mean I could ignore the increasing size of transaction log before the next full backup comes?

I'm going to try to save you a lot of time messing with outdated and mostly unnecessary backup options. Just use Ola Hallengren's free scripts.

Next up, deciding backup frequency is a business decision as it relates to RPO and RTO. No one here can tell you how much data you're allowed to lose. One thing though: if you're thinking about taking log backups once a day, you shouldn't be in full recovery model.

That also relates to: DON'T SHRINK YOUR DATABASE. Your log file grows because you don't back it up either at all, or often enough. If you shrink it, it will grow again, and that's just not a smart way to administer a SQL Server. Either back it up more often, or switch to SIMPLE recovery.

That decision is between you and management, though.


"Truncate" or "Delete/Vacuum Full" for deleting some of the table rows I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided to remove all records but the first record for every minute. So now there are 8 records for each minutes and after this process it will store 1 record per minute which are older than 3 months.

Should I do it by delete and vacuum full afterwards or by truncate? There are also some views related to some of those tables. If I do it by truncate, would those views work afterwards?

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would:

Select the (relatively few) surviving rows into a temporary table.
Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer:
Optimizing bulk update performance in PostgreSQL
Truncate the table.
Re-insert the survivors.

This way, views stay intact. They would prohibit you from dropping involved tables.
You also do not need any vacuuming, no dead rows at all this way. While being at it, you may want to ORDER BY your rows for the INSERT for optimized SELECT performance.

Here and here are closely related cases with more details on SO.


Query execution plan is horrible until Statistics are Updated I hope you guys can help me here.
Our application polls a message table every 3 seconds looking for notifications to send out. This works well on all our Customers (single-tenant DB) except one. They will have no activity for 23hours a day and then load thousands of messages all at once (3000+). In other cases, this volume is nothing and we can easily deal with it, except in this case, the SQL query below takes approximately 30 seconds to run and gets worse as the queue backs up as it does an update, require an exclusive lock and therefore blocking all the other queries and so the issues causes all kinds of havoc.
This is all due to a bad query plan.

We have daily reindexing that runs every morning at 5am (reorganise < 30%, rebuild > 30%, ignore <5%) as well as updates statistics. These are both from the Ola Hallengren maintenance solution.
We are also on SQL Server 2016 and are fully up to date (13.0.5492.2)

I don't have the 2 plans handy, but basically the bad plan goes and does a full table scan of the MessagesSent table (3.5m rows).

My theory is that because the query returns nothing all day, certain parts are not executed and therefore the bad query is the most efficient query for SQL.

This will continue after flushing the plan for the query as it just generates the same plan, however when I UPDATE STATISTICS on the MessagesSent table, the good plan is created and all is healthy, with the query executing in about 10-30ms.

Does anyone know how I can fine-tune this to always use the better plan even if no data exsists for the query to return?
As a hotfix, we have added option recompile to the application but I don't feel that's the ideal solution for a query that is executing ever 3 seconds.

Here is the query :

WITH TopMessage
    AS
    (
        SELECT TOP 1 ID, BatchID FROM MessagesSent
        JOIN Units ON Unit = idUnit
        WHERE   MessageDate <= GETDATE()
          AND         Active = 'True'
          AND         Status = 'Queued'
          AND NOT(DialString = 'null')
          AND           Unit = ('29')
          AND System in ('SystemName', 'Q1', '')
        ORDER BY
            CASE
                WHEN QPriority IS NULL
                    THEN
                        CASE
                            WHEN DefaultPriority IS NULL
                                THEN 999999
                                ELSE DefaultPriority
                        END
                 ELSE QPriority
            END ASC,
            Retries ASC,
            MessageDate ASC,
            ID ASC
    ),
    BatchCalls
    AS
    (
        SELECT * FROM MessagesSent
        WHERE (
                 (LEN(BatchID) > 0
                  AND BatchID = (SELECT TOP 1 BatchID FROM TopMessage)
                 )
        OR ID = (SELECT TOP 1 ID FROM TopMessage)
        )
        AND Status = 'Queued' AND Active = 'True'
    )

    UPDATE BatchCalls
    SET LastUpdated = @dtNow
    OUTPUT INSERTED.*
    WHERE Status = 'Queued'

Thank you very much for your time and for looking.

You could use a manual plan guide to enforce the desired plan.

Alternatively, you could use Query Store to enforce plan guides via the GUI.

Also, you could run an update statistics job after the big load of messages.  I wrote a post on my blog showing an easy way to do that.


Why are all sessions used by SYSMAN? I hope I can find some help with an issue in Oracle 11g on Windows 2008.  We've done a system copy by copying the datafiles from backup, and recreating the control files, and the database is up. However, within an hour or so, all sessions become taken by SYSMAN. We've found that stopping the dbconsole (emctl stop dbconsole) will terminate them. Is there something I should do to refresh dbconsole, or delete and recreate the configuration?

You mentioned system copy. Whenever you change the ipaddress or hostname, DBConsole goes haywire, and even the official solution is to recreate it as described in:

How to configure Dbconsole After the Ipaddress or hostname of the Machine has Been Changed or if the hostname ipaddress is going to be changed? (Doc ID 1333938.1)

The usual steps to do this:

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create


How to confirm that object owners can grant and revoke privileges on objects he owns? I'm trying to bring my systems in compliance with a certain set of standards. One of them in confirming that the Oracle default behavior of allowing users to grant and revoke privileges to objects they own is still active. I have no idea how this would even be disabled in the first place, but I'm trying to figure out what evidence I can provide that it is still active other than "this is default behavior of Oracle." Is there a way to confirm this in views like dba_tab_privs or something?

Any assistance would be appreciated. Let me know if you need me to clarify.

It isn't just "the default behavior", it's the only behavior. Owners always have full control on their own objects (including grants and revokes on those objects). That's why separation of container schemas from accounts that login to the database is so key to database security.

  Disclaimer
  The original answer was posted as a comment by @KrisJohnston

Addition

There is a slight twist to the answer in that the permissions can be changed so that the owner can longer modify his own objects, BUT the owner is then no longer the owner. This is a loss of ownership for the original owner.

This can be achieved using the following technique:

How to change an Oracle table owner

  create table bill.emp as select * from scott.emp;


SQL Server - How to determine ideal memory for instance? We have some virtual machines that have X memory allocated to them.  This amount of memory is somewhat random.  It was allocated to the machine because that was the amount of memory the physical machine had, because of a vendor recommendation, or because we threw out a best guess as to how much memory we thought the instance would require.

I understand the more memory the better, but I would also like to avoid over allocating memory to the VM when it isn't necessary.  The memory could be better utilized by another machine.

What would be the best way to determine an ideal amount of memory per instance that is actually active and being used?  Are there other counters we should be looking at in addition to page life expectancy?  We have instance that have PLE's of 10k+ and others that have 100k+.

Any insight is much appreciated.
Thanks,
Sam

A good starting point is best outlined by Glenn in his article : Suggested Max Memory Settings for SQL Server 2005/2008

Best is to baseline your database server usage during your full business cycle as that will give you the best number based on your workload using below PERFMON counters :

SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Memory\Available Mbytes
SQL Server: Memory Manager - Total Server Memory
SQL Server: Memory Manager - Target Server Memory

Note: If you are going to use any formula or online calculator to calculate SQL Server memory configuration then best is to read Beaware of Wow… An online calculator to misconfigure your SQL Server memory ! - by Jonathan Kehayias  first.

For Page Life Expectancy refer to Page Life Expectancy isn’t what you think… by Paul Randall.

An excellent reference :

How much memory does my SQL Server actually need? - by Jonathan Kehayias


Move values from one table to another one via stored procedure in sql server 2008 I have two tables

Login (userid, username, password)
audit_login (userid, Transaction_Date, Status (success or failed)

Now I want create a stored procedure in which, if anyone wants to login then their userid, the Transaction_Date and Status (success or failed) details should automatically come into another table audit_login.

I will then want to know how many times a login was successful.

You can easily implement your requirement using a trigger on Login table that will log info in the audit_login table.

the key is using table trigger to log info in the audit table for auditing.

I will see if I can provide a working code. But you got the idea now.

Update: below is a prototype code that will help you.

Note: you dont need any Stored procedure, A trigger will do the job.

You can put try/catch to handle errors, which I have not done as this is only to help you understand the concept.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Logins]') AND type in (N'U'))
DROP TABLE [dbo].[Logins]
GO
create table Logins (userid varchar(15) not null
                        ,username varchar(max)
                        ,[password] varchar(50)
                        ,[status] varchar(1)) -- S = Success, F= Fail
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit_login]') AND type in (N'U'))
DROP TABLE [dbo].[audit_login]
GO
SELECT [userid],[username],[status]
    ,AuditDataState=CONVERT(VARCHAR(10),'')
    ,AuditDMLAction=CONVERT(VARCHAR(10),'')
    ,AuditUser =CONVERT(SYSNAME,'')
    ,AuditDateTime=CONVERT(DATETIME,'01-JAN-1900')
    into dbo.audit_login
FROM dbo.Logins
WHERE 1=2

go
CREATE TRIGGER Logins_Insert
ON dbo.Logins
FOR INSERT
AS

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'New','Insert',SUSER_SNAME(),getdate()  FROM INSERTED

GO
CREATE TRIGGER Logins_Delete
ON dbo.Logins
FOR DELETE
AS

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'Old','Delete',SUSER_SNAME(),getdate()  FROM DELETED
GO

CREATE TRIGGER Logins_Update
ON dbo.Logins
FOR UPDATE
AS

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'New','Update',SUSER_SNAME(),getdate()  FROM INSERTED

 INSERT INTO dbo.audit_login
 SELECT [userid],[username],[status],'Old','Update',SUSER_SNAME(),getdate()  FROM DELETED

GO

Now insert some data :

-- Insert New Data
insert into Logins
values ('kin001', 'kin','$tr0ng01','s')

select * from audit_login

-- Update the current Data
update Logins
set userid = 'kin-up-01'
where userid = 'kin001'

select * from audit_login

-- delete the data
delete from Logins
where userid = 'kin-up-01'

Below will be the end result:


Where can I find the connection string for a DB2 database? I need to connect an application to my DB2 database. Both application and db are running on the same machine.

I've tried this:

Server=localhost:50000;Database=myDataBase;UID=myUsername;PWD=myPassword;

I'm getting the following error:

  CLI0124E  Invalid argument value. SQLSTATE=HY009

Am I using the wrong connection string?

Edit
I've also tried the following format:

Provider=IBMDADB2;Database=urDataBase;Hostname=urServerAddress;Protocol=TCPIP;Port=50000;
Uid=urUsername;Pwd=urPassword;

Same result.

Not sure where you got those connection strings. According to the documentation, the provider you use needs three keywords: DSN, UID, and PWD. DSN refers to the Windows data source, which you define as usual after installing the DB2 Data Server Client software.


Why set up static data in views vs. using tables in mysql? I get an LDAP feed nightly.  I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields.

I have the following too:

LDAPIMPORTANT - view that stores all 75K but only 15 fields

LDAPSHORT - view that stores all 75k but 5 fields

LDAPAB - view that only stores 9k employees based on two groups (field lookup)

Each of these are used a lot and for different apps and also there are a lot of views written against these views.  But there is no updates to them.  We do not update employee data. It is just LDAPALL update once a night.

In this circumstance should I create tables from the LDAPALL table instead of views?  I could set up jobs to create these tables once a night.  What is best practice behind this?  Speak in layman's terms because I am a PHP developer made to do all DB admin stuff.

I would simply use the views if they perform.  No need to make copies of data unnecessarily, and many database platforms allow indexed or materialized views and filtered views etc.

HOWEVER, MySQL does not support indexed views.  So unless the underlying indexes on your table support the various ways you are accessing the data, it might be worthwhile materializing a version of the view yourself.


Different default innodb_buffer_pool_size on Windows with MySQL 8.0.18 I have installed MySQL 8.0.18 Community with the same configuration (mysql-installer-community-8.0.18.0.msi installer, custom install with server + workbench, standalone server, legacy authentication, rest are the defaults )  on multiple Windows environments with surprisingly varying results.

3 out of 5 of these machines ran MySQL insanely slow when I was inserting a few million lines into the DB

Task Manger said that the disk activity time is 100% but barely moved any data (50-500 KB/s for half an hour)
I went into MySQL Workbench, noted that the data graphs on the Server Status window are not working,
the InnoDB Disk Writes section on the Dashboard says that the writing speed is about 40 KB/s.

Then I went to check the my.ini file and found this nonsense:

  innodb_buffer_pool_size=8M

which was the default setting for some odd reason.

The other two machines had this line commented out in the ini file so they were working properly, MySQL was behaving as expected.

The bad environments:

Desktop machine, Windows 10, 16 GB RAM 7th gen i5 CPU with 4 cores. MySQL was never installed on it before.
Physical machine, Windows Server 2016, 44 GB RAM, some beefy Xeon CPU (don't remember the exact model but it was not bad), MySQL was never installed before
VM (on the laptop described in good #2) with 16 GB ram and 6 vCPUs. MySQL was never installed before.

The good environments:

Desktop machine, Windows 10, 16 GB RAM, 4th gen I5 CPU with 4 cores. MySQL 8.0.17 was installed previously, it was working properly. I did a clean install to 8.0.18 and the buffer pool size value was commented out
Laptop, Windows 10, 32 GB RAM, 8th gen I7 CPU with 4 cores. Same story with MySQL.

I could not find anything in the MySQL documentation or on the web describing this behavior. I mean innodb_buffer_pool_size=8M is laughable, I see NO reason why would this be the default value

Does anyone have any idea why this would happen?

8M is a very old default for MySQL.  Yes, it is laughable.  It is much too small for any instance today.

Either that my.ini was left over from some previous installation, or it is worth a bug report.

If you have at least 4GB of RAM, set innodb_buffer_pool_size to about 70% of RAM.


Select unique value pairs that occur (count) more than once inside a table How can I retrieve an row with unique value pairs that occur more than once...
Heres an example of what I'm what I am trying to..

SELECT
 column_fk_id1, column_fk_id2,
 COUNT(distinct column_fk_id1, column_fk_id2) AS unique_pair_repeats
FROM table
WHERE column_fk_id1 IS NOT NULL
GROUP BY column_fk_id1, column_fk_id2
HAVING unique_pair_repeats > 2

What you have almost works, just remove the distinct and change the > 2 to > 1.  The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three entries rather than just two.

drop table tab1;
create table tab1 as (select 1 column_fk_id1,2 column_fk_id2 from dual);
insert into tab1 values (1,2);
insert into tab1 values (2,1);
insert into tab1 values (3,4);
insert into tab1 values (4,3);
insert into tab1 values (5,5);
insert into tab1 values (6,7);
insert into tab1 values (6,7);
insert into tab1 values (6,8);
commit;

SELECT column_fk_id1, column_fk_id2, COUNT(*) AS unique_pair_repeats
FROM tab1
WHERE column_fk_id1 IS NOT NULL
GROUP BY column_fk_id1, column_fk_id2
HAVING count(*) > 1;

COLUMN_FK_ID1 COLUMN_FK_ID2 UNIQUE_PAIR_REPEATS
------------- ------------- -------------------
            1             2                   2
            6             7                   2

2 rows selected.


Any advantages if we use multiple database instead of only one in PostgreSQL? I would like to get the suggestion for my usecase.

We have a database in our production machine.We are doing continuous writes in that database since we are doing crawling,parsing and it is being inserted into database as a streamer.We are running some crons on the database.That database gets some reads for the web application also.

I got the requirement just to store the html document into one another table for the cross verification.So,the plan was to store the pages into one new table which was created in the same database.Here we need to run one more cron on the new table to find the page number and update into that table.

We got one more idea like to create the table and running cron in new database instead of the old database by considering performance improvement.

Will it be any difference to create a new database and update into it rather than using old database to update the data and running a cron?

we are using amazon ec2 medium instance with Ubuntu operating system also PostgreSQL 8.4.

your thoughts please?

If it needs to be cross-verified, put it in the same db.  That way you can do your cross-verification in the db, taking advantage of indexes, query optimization, etc. which you can't do as easily in separate db's without a fair bit of extra work.

In theory you can spread out your disk/IO loads by putting it in another machine but typically, if this is virtually all write, I don't think it will be worth it.

If it is a different db on the same machine, you will not get any better write performance and you will likely get worse read performance.


How to track DML and DDL daily events I have the following query to track what tables are modified in the current date:

SELECT TABLE_OWNER,
       TABLE_NAME,
       INSERTS,
       UPDATES,
       DELETES,
       TIMESTAMP AS `LAST_CHANGE`
FROM  ALL_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY')
ORDER BY LAST_CHANGE DESC;`

Result

As you can see the query works fine , but I want to add more complexity like  adding a field  "LAST_CHANGE_TYPE" showing what was the last transanction type (addded, deleted or modified) in the lastest process.

It would be good too another query that can be usefull to monitoring my DW.

Like monitor all posible actions , to track what user made for example:

drop constraints if its posible specify what kind of constraint
Modify Packages Body

When the actions are listed, its necessary to see the user and date of each action.

| Object     |Type   | Source   | Operation Type  | User  | Date      |

| Constraint | PK    | Table A  |  Drop           | Scott | 11/11/2011|

| Table      | Table | Table B  |  Alter          | HR    | 05/05/2002|

Currently open to all suggestions

As Justin said,  all_tab_modifications is not a real-time accurate count of the number of modifications to a table since the last time statistics were gathered

I recommend you to track this kind of events, see the following  Link


Setting parameters like PCTUSED when creating new tables in ORACLE 12 C When creating new tables in ORACLE 12 C, do we need to explicitly set parameters like PCTUSED, PCTFREE, INITRANS, MAXTRANS, STORAGE etc. (as shown in example below)?
Or will ORACLE automatically set these values efficiently? Developers generally tend to leave out these parameters as they focus only on table/schema/data model creation - "the bare minimum" needed for any application to run!

CREATE TABLE TABLE1
(
COL1 VARCHAR2(28 BYTE) NOT NULL
.....
)
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

"Or will ORACLE automatically set these values efficiently"

Well, Oracle will set them to something by default ... probably not very "efficiently" though ;) For that, you're going to want to do a bit of research and groundwork on what YOU need.

As mentioned by Wernfried in comments, LOCALLY managed tablespaces are the way to go, and once you have those defined, specifying those options per table is wasted - Oracle will ignore them.

It's generally seen as better to setup certain types of Tablespaces for different types of tables used.

Small, mostly read-only tables: 1 tablespace: set to small Extent sizes, and small/0 pctincrease.

Larger, highly volatile tables: another tablespace: larger extent sizes, larger pctincrease ..

etc.
You need to work out what your needs are, and what kinds of tables you have (and then what tablespaces/storage options you'll want).


SET ANSI_NULLS at the database level versus setting it at the table level I have generated schema-only script for my sql server 2008 R2, here is part of it:-

USE [master]
GO
/****** Object:  Database [****]    Script Date: 03/25/2015 12:17:09 ******/
CREATE DATABASE [****] ON  PRIMARY
( NAME = N'TMS', FILENAME = N'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\****.mdf' , SIZE = 11264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TMS_log', FILENAME = N'L:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\****.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [****] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [****].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [****] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [****] SET ANSI_NULLS OFF
GO
ALTER DATABASE [****] SET ANSI_PADDING OFF
GO
ALTER DATABASE [****] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [****] SET ARITHABORT OFF
GO
ALTER DATABASE [****] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [****] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [****] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [****] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [****] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [****] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [****] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [****] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [****] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [****] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [****] SET  DISABLE_BROKER
GO
ALTER DATABASE [****] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [****] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [****] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [****] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [****] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [****] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [****] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [****] SET  READ_WRITE
GO
ALTER DATABASE [****] SET RECOVERY FULL
GO
ALTER DATABASE [****] SET  MULTI_USER
GO
ALTER DATABASE [****] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [****] SET DB_CHAINING OFF
GO
USE [****]
GO
/****** Object:  User [AD-ITSERVICES\TMSDB.user]    Script Date: 03/25/2015 12:17:09 ******/
CREATE USER [AD-ITSERVICES\test.user] FOR LOGIN [****\test.user] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  Table [dbo].[TechnologyTypes]    Script Date: 03/25/2015 12:17:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TechnologyTypes](
    [AssetTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [IncludedInSearch] [bit] NULL,
 CONSTRAINT [PK_AssetType] PRIMARY KEY CLUSTERED
(
    [AssetTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_AssetType] UNIQUE NONCLUSTERED
(
    [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TechnologyStatus]    Script Date: 03/25/2015 12:17:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

now i am confused on why i go this sentence :-

ALTER DATABASE [****] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [****] SET ANSI_NULLS OFF

but when creating any sql table i will get :-

SET ANSI_NULLS ON

so what is the final result; is ANSI_NULLS will be equal to off or on ? from my understanding the "SET ANSI_NULLS" will be OFF at the database level, unless i set it ON at the table level ? is this correct ? any why i got the ANSI_NULLs set to off at the database level ?

ANSI_NULL_DEFAULT { ON | OFF } :

When you dont specify ANSI_NULL_DEFAULT in CREATE TABLE or ALTER TABLE statements, this database option will determine the default value, NULL or NOT NULL for a column or CLR user-defined type.

When SET to ON, the DEFAULT value is NULL. When SET to OFF, the DEFAULT value is NOT NULL.

  Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when connecting to an instance of SQL Server.

ANSI_NULLS { ON | OFF }:

ANSI_NULL should be ON. This will be a default behavior in future versions of SQL Server.

When ANSI_NULLS is set to ON then NULLs follow the ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators.

  When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When ANSI_NULLS is set to OFF then a comparison of NULL=NULL or NULL<>NULL returns TRUE or FALSE respectively, instead of an unknown answer NULL. It treats NULL into a value of its own.

  When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

Always remember that session level setting will override database level setting for the above SET options.

Reference : SET ANSI_NULL_DFLT_ON and SET ANSI_NULLS


mysql - can a replica have a different primary key than the source? I have a table with PRIMARY KEY (`id`) and I want to change it to PRIMARY KEY (`username`, `id`).  These columns are defined as:
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',

This table is within a primary/secondary MySQL topology with binary row replication.  Can I get away with taking the replica offline, changing the primary key, and reconnect it to the source without changing the source?  For clarity, only the primary key index would be different between the source/replica.  All other columns/order of columns would be the same.

Depends on the Storage Engine
InnoDB
The auto_increment column will behave but the Replica will become very bloated. Why ???
For any given non-unique index in an InnoDB table, each entry in s secondary index will have a copy of the PRIMARY KEY as part of the entry.
If your InnoDB table has PRIMARY KEY (id) (which is 8 bytes), then each secondary index entry will have the id attached.
Now if you change the PRIMARY KEY to (username,id), then each secondary index entry will have the (username,id) attached. That can be up to 28 (8 + 20) bytes to each entry.
This will make INSERTs slower on the Replica.
This also allow duplicates of username.
You are better off creating a UNIQUE KEY on username.
MyISAM
For those still using MyISAM, PLEASE DO NOT ATTEMPT THIS UNDER ANY CIRCUMSTANCES !!! Why ???
Any MyISAM table that has a compound key that involves an auto_increment column with increment based on the presences of the other columns
EXAMPLE from the MySQL Documentation
CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;

which prints out
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Do you see how id increments on a common value when grp already exists  in the table ?
The Source and the Replica will be so horribly out-of-sync. There will be no way to know which row in the Source belongs to a row in the Replica, and vice versa.
This makes changing the PRIMARY KEY of a MyISAM table totally dangerous for a Replica.
I have discussed this subject over 6 years ago

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Jun 10, 2012 : MySQL get next unique value without auto increment
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
Aug 28, 2013 : How to go about modeling the main object of relationship?

UPDATE 2019-04-20 08:52 EDT
Of course, it is possible but BEWARE !!! : My post was just about the dangers of doing so. In the event of problems, MyISAM would be an absolute nightmare to unravel. InnoDB would be somewhat OK but you are messing with the PRIMARY KEY.
Back on Aug 14, 2012, I have recommended changing the storage engine and adding an index on a replica (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?). I still recommend do something like this for older versions of MySQL (When FULLTEXT indexes for InnoDB did not exist yet). The PRIMARY KEY can be a different story.
I can easily see the EXPLAIN plan of a SELECT being radically different on a replica from the EXPLAIN plan of its source given a completely different order of PRIMARY KEY columns.
You could never use a tool like pt-table-checksum and pt-table-sync to reconcile differences should the table become out of sync or replication breaking for unrelated reasons. Why ??? Both tools rely on the PRIMARY KEY columns. Even with plain MySQL, just running CHECKSUM TABLE table_name; would always be different between Source and Replica.
Your hoping for better performance runs the risk of MySQL interpreting the table as not being the same (because it simply won't be the same).
If you looking for the username to be unique, FORGET IT !!! Having (username,id) as a PRIMARY KEY allows for it. If you already have a UNIQUE KEY on username on the Source, then the Replica already has the key it needs.
SUGGESTION
Make sure the Source has a UNIQUE KEY on the username.
Don't do anything to the PRIMARY KEY on the Replica. If you want to the Source to have it, then do it on the Source so it transfers to the Replica.
EPILOGUE
Giving you a simple it's possible or it's not possible answer would be absolutely unfair to you given the bullet-riddled problems it would produce down the road for anyone who tries.
I would never recommend changing the PRIMARY KEY of a Replica regardless of storage engine or DBMS. If you are still willing to accept the responsibility of changing the PRIMARY KEY on a Replica after reading this, Godspeed, Spiderman !!!


SQL query - How to select to display group, group total, grand total? I've seen some advice but want to know the best way to select a group, group total, grand total from a data table WITHOUT using a subquery or unnecessary join.

My initial thought was something like this:

select   product_family,
         sum(widgets),
         sum(widgets) over ()
from     table.widget
group by product_family

or the following:

select   product_family,
         sum(widgets),
         sum(widgets) over (partition by all_field)
from     table.widget
group by product_family

Obviously neither of these works. I know a partition can be of a higher order than the actual rows/ groups, but not how to partition by "all" other than leaving it blank as the first example. However, it breaks with the group by statement.

Best I can find is something like this:

select product_family,
       family_sum,
       sum(family_sum) over () as grand_sum
from (
      select   product_family, sum(widgets)
      from     table.widget
      group by product_family
     ) as A

That still involves a subquery though, which is okay. I just feel like I'm missing an easy function here.

GROUP BY ROLLUP does what you want.

As an example:

SELECT o.schema_id
    , type_desc
    , [Count Of Objects] = COUNT(o.object_id)
FROM sys.objects o
GROUP BY ROLLUP (o.schema_id, o.type_desc)

This produces the following output:

╔═══════════╦════════════════════════╦══════════════════╗
║ schema_id ║       type_desc        ║ Count Of Objects ║
╠═══════════╬════════════════════════╬══════════════════╣
║ 1         ║ PRIMARY_KEY_CONSTRAINT ║                7 ║
║ 1         ║ SERVICE_QUEUE          ║                3 ║
║ 1         ║ USER_TABLE             ║                8 ║
║ 1         ║ NULL                   ║               18 ║
║ 4         ║ INTERNAL_TABLE         ║               16 ║
║ 4         ║ SYSTEM_TABLE           ║               72 ║
║ 4         ║ NULL                   ║               88 ║
║ NULL      ║ NULL                   ║              106 ║
╚═══════════╩════════════════════════╩══════════════════╝

The NULL shown in the first two columns represents the roll-up amounts.

To make it "pretty", you can use some substitutions for the NULL rolled-up values:

SELECT [Schema Name] = CASE
        WHEN s.name IS NULL THEN
            '[Grand Total]'
        ELSE s.name
        END
    , [Object Type] = CASE
        WHEN o.type_desc IS NULL THEN
            '[Total - ' + COALESCE(s.name, 'Overall') + ']' COLLATE SQL_Latin1_General_CP1_CI_AS
        ELSE o.type_desc
        END
    , [Count of Objects] = COUNT(o.object_id)
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
GROUP BY ROLLUP (s.name, o.type_desc);

╔═══════════════╦════════════════════════╦══════════════════╗
║  Schema Name  ║      Object Type       ║ Count of Objects ║
╠═══════════════╬════════════════════════╬══════════════════╣
║ dbo           ║ PRIMARY_KEY_CONSTRAINT ║                7 ║
║ dbo           ║ SERVICE_QUEUE          ║                3 ║
║ dbo           ║ USER_TABLE             ║                8 ║
║ dbo           ║ [Total - dbo]          ║               18 ║
║ sys           ║ INTERNAL_TABLE         ║               16 ║
║ sys           ║ SYSTEM_TABLE           ║               72 ║
║ sys           ║ [Total - sys]          ║               88 ║
║ [Grand Total] ║ [Total - Overall]      ║              106 ║
╚═══════════════╩════════════════════════╩══════════════════╝


Defining OLAP or OLTP on table level in Oracle/postgres/SQLserver Usually companies have separate databases for OLTP and OLAP (Row stores and column stores) where data is loaded through ETL jobs to another database for analytical processing. I am particularly interested to know if there is any way we can define  which tables or objects to store for OLAP and which tables or objects to store for OLTP queries in any RDBMS like oracle or postgres on any level of granularity in the same database.

Oracle includes an in-memory columnar store that lets you combine OLAP-style queries on top of a plain tabular data mode without requiring any transformation
See https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/intro-to-in-memory-column-store.html#GUID-BFA53515-7643-41E5-A296-654AB4A9F9E7


MySQL Stored Procedure - no of times executed My question is simple, is there any way to calculate, number of times a stored procedure is called on applications or executed on MySQL server.

For example:

Procedure name: sptest

Executing procedure: call sptest(1);

Is there any way to identify number of time the statement CALL sptest(1) executed on server.

Thanks in advance.

You can create a mapping table with two columns, one key (a string containing the called procedure name plus arguments if you need that) and one value (an integer containing the number of times the corresponding procedure has been called).

The first thing you do in your procedure you want to monitor is to update the mapping table to record the procedure call (by incrementing the counter). This logic you can encapsulate in another stored procedure.

This approach will be inconvenient if you need to monitor a large number of stored procedures, but will work for a small number.


Indicators for right balance of the number of DB2 instances per AIX LPAR I have a question that has been bugging me, but I just don't know how to validate it. We run DB2 LUW on AIX in our shop. Our AIX is currently at 7.1. Our DB2 is at 9.7 FP 4 for most installations and 10.5 FP 2 for our newest databases. We even have mixed installations (ie, 9.7 and 10.5) on the same "boxes".

Our boxes are LPARs of course, given to us by our AIX admins. The AIX admins are also our storage admins and so all disk is SAN. There is not much physical about our database servers.

What has been bugging me is what is the correct balance of DB2 instances per LPAR? I have started to see some strange behavior regarding DB2 auto-configuring itself. On one DEV LPAR we have the following configurations:

Do note that the following is true for all instances: HEALTH_MON is set to OFF, and AUTO_MAINT is set to OFF for every database. We do allow Self-Tuning Memory Manager (STMM) to be on by default with all necessary parameters set for that (ie, zeros or AUTOMATIC in the correct parameters, the default that DB2 provids...). If we build an OLTP database then we set instance parm MAX_QUERYDEGREE to 1 and leave INTRA_PARALLEL to NO and db parm DFT_DEGREE to 1. If we build an OLAP database (separate instance) we set instance parm MAX_QUERYDEGREE to ANY and INTRA_PARALLEL to YES and the db parm DFT_DEGREE to ANY. We also do not try to mix OLAP and OLTP databases in the same instance, though part of the question is whether to mix the instances on the same LPAR.....

Here is what we have on one DEV LPAR:

First instance is 9.7. Contains some OLAP databases, though they are very small since they are used for testing out DDL changes.
Second instance is 9.7. Contains an OLAP database "playground" for developers. (This one is supposed to be retired the first chance we get.)
Third instance is 10.5. Contains the official DEV version of that playground database from instance 2.
Fourth instance is 10.5. OLTP database with a home grown metadata dictionary.
Fifth instance is 10.5. OLTP database that is used for lookup data.
Sixth instance is 10.5. ODS database (unsure if this is OLTP or OLAP....) for developers to tie data from two transactional systems.

Now disk isn't the biggest issue as that is handled by the SAN. And even memory and CPU are virtuallized by nature of this being an LPAR. But I have seen some strange stuff with the memory.

The one OLAP database (both the playground and the official DEV) are rather large. The developers are testing ETL'ing in large volumes of data and estimating size for their official request for QAT and PROD buildouts. Ever since one of these has started to grow, I've noticed that new instance buildouts and new database buildouts now have STMM turned off by default. I can manually turn it back on, but the point being I find this disturbing. Whether or not STMM is enabled by default is decided by DB2 at creation time based on system resources. So, to me, this is saying DB2 does not feel it has adequate resources for auto management, so it is trying to cap memory in hopes of it gains at least that much. (My guess on that.)

When I discuss this with the AIX admins, they don't seem to think memory and CPU is an issue. They say we aren't really fulling utilizing what we could be, so in their mind (and I'm saying this very loosely) we could put all our dev instances on the same LPAR. Or similar for other environments. Again, that is loose, because they understand the point of separation for HA and DR scenarios.

Anyway that leaves me wondering, are their any pointers or guidelines between knowing when you can add instances to an LPAR or when you should split to other LPARs? I'm sure the answer is "it depends", but I'm looking for any approximations or guidelines? Especially if those differ along the lines of OLTP and OLAP.

You won't get any specific answer to this question, because as you observed, it depends.

The answer depends on your company's tolerance for having one DB2 instance adversely impact the performance of another's.  You may be able to control or limit this impact if your AIX admins are willing to learn and set up WLM policies at the operating system level (not DB2 WLM).

This is really a question of capacity management.  Understanding the workloads in your environment is key here, and being able to identify (or predict) when you'll run into limits will influence your decisions about how many instance(s) to put on a single server.


MySQL ranking one user based on cell value I got to figure out how to optimally check for a users rank in all the stages he has passed in a game. The rank is based on the amount of time (float) it took for the player to pass the stage.

What I've got is this schema: http://www.sqlfiddle.com/#!9/3764e/1

id| fTime | stage |
-------------------
1 | 11.123| first |
1 | 12.561| second|
1 | 13.333| third |
2 | 11.642| first |
2 | 12.231| second|
2 | 13.999| third |
3 | 11.000| first |
3 | 12.842| second|
3 | 13.849| third |
4 | 11.535| first |
4 | 12.653| second|
4 | 13.145| third |
5 | 11.653| first |
5 | 12.134| second|
5 | 13.678| third |
6 | 11.546| first |
6 | 12.325| second|
6 | 13.532| third |

The result that I need is the players rank in each stage he has passed, based on his time in it (fTime).

|     id|     stage| rank|
|      1|   'first'|    1|
|      1|  'second'|    2|
|      1|   'third'|    1|

The way I've been doing this thus far, is by querying the stages the player has finished, then the time he has achieved in each stage, and then getting his rank on that stage. This is obviously not optimal, as I got to do a lot of trips to the mysql server and back, when it could be done in way less queries.

I've been looking around the net for issues like this, but haven't found anything that quite matches, and this goes beyond my SQL skills..

There are two basic ways to do it:

A join (usually preferred in MySQL)

SELECT a.id, a.stage, count(b.id)+1 AS rank
FROM playertimes a
LEFT JOIN playertimes b on (a.stage=b.stage and a.fTime > b.fTime)
group by a.id, a.stage;

And a dependent subquery which may be a lot slower in some cases but might work OK for you

SELECT id, stage,
  (
    select count(1)+1
    from playertimes b
    where a.stage=b.stage and a.fTime > b.fTime
  ) AS rank
FROM playertimes a
order by id, stage;

You can check it at http://www.sqlfiddle.com/#!9/2d9580/3 - I added an index on (stage, fTime) which can make it faster in both cases.

Both solutions will assign the same rank to players with the same time (if it can happen at all) and then skip the "unused" ranks - like 1, 2, 2, 2, 5, 6, 6, 8.


Import poorly formatted CSV/JSON to SQL Server I am trying to parse Android notification logs into a database. The columns are uneven because different notifications omit certain attributes. Essentially, it is a CSV file of key-value pairs. To put it another way, it is a really poorly formatted (& syntactically incorrect) JSON file. Maybe it would be easier to show you...

{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}
{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}

I have truncated them for the sake of brevity, but they do not line up toward the end of the rows. Some text values are quoted, some are not. All of the keys are quoted. I can use string functions to trim the extraneous characters once I get the columns sorted, but therein lies the rub: How do I sort the values into columns? I can do this in about 4 different programming languages, but I am just learning T-SQL.

How can I parse this to SQL Server (2016) in a single query? Is it even possible?

I could import it to a temporary table & then transfer it to the permanent one, but I would like to have one query to digest the info, since this will be repeated on a daily (maybe more) basis. The magic would be if I could declare a second-level delimiter, since all values are preceded by a colon. Declaring the colon as the delimiter breaks it the other way...arg(s)!

The data is all around ragged. Even the notifications from the same app may be drastically different. The way I would do it in PHP, C, JS, etc  would be to loop through the keys until I hit the ones I needed, grab the appropriate values following them (or the whole pair), then trim the extraneous chars. I just don't know how to do it in T-SQL & the online docs leave something to be desired.

The expected result would be something like this:

Package Name | Post Time | System Time | Offset | NotificationText
-----------------------------------------------------------------
automatic    | 1489795   | 786083      | -1400  | this is the note
gameservice  | 1489756   | 786090      | -1400  | this is the note
automatic    | 1489799   | 786045      | -1400  | this is the note

The big problem is that the info I need is scattered throughout the rows. I don't need much of it, but it is buried.

You can use the technique shown in Loading line-delimited JSON files in SQL Server 2016 on the SQL Server Database Engine Blog, written by Jovan Popovic .

For example, given a file import.txt:

{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}
{"packageName":"com.automatic.mustang","postTime":1489795785973,"systemTime":1489795786083,"offset":-14400000,"isOngoing":false}
{"packageName":"com.enhance.gameservice","postTime":1489040766066,"systemTime":1489040766136,"offset":-18000000,"isOngoing":false}

and a format file import.fmt:

13.0
1
1 SQLCHAR 0 0 "\r\n" 1 json ""

The file can be bulk imported and parsed with:

SELECT
    Parsed.packageName,
    Parsed.postTime,
    Parsed.systemTime,
    Parsed.offset,
    Parsed.isOngoing
FROM OPENROWSET
(
    BULK 'C:\Temp\import.txt',
    FORMATFILE= 'C:\Temp\import.fmt'
) AS BulkLog
CROSS APPLY OPENJSON(BulkLog.[json])
WITH
(
    packageName varchar(50),
    postTime    bigint,
    systemTime  bigint,
    offset      bigint,
    isOngoing   bit
) AS Parsed;

Giving:


SQL Server Cache Flush and Disk I/O We're busy load testing an OLTP system we've developed in .NET 4.0 and runs SQL Server 2008 R2 in the back. The system uses SQL Server Service Broker queues, which are very performant, but we are experiencing a peculiar trend whilst processing.

SQL Server process requests at a blistering rate for 1 minute, followed by ~20 seconds of increased disk write activity. The following graph illustrates the problem.

Yellow = Transactions per second
Blue   = Total CPU usage
Red    = Sqlsrv Disk Write Bytes/s
Green  = Sqlsrv Disk Read Bytes/s

During troubleshooting, we tried the following without any significant change in the pattern:

Stopped SQL Server Agent.
Killed almost every other running process (No A/V, SSMS, VS, Windows Explorer, etc.)
Removed all other databases.
Disabled all conversation timers (we don't use any triggers).
Moved away from a message queue driven approach to a simple/crude table monitoring design.
Used different loads from light to heavy.
Fixed all deadlocks.

It seems as if SQL Server might be building up its cache and writing it to disk at specific time-based intervals, but I can't find anything online to support this theory.

Next, I plan to move the solution over to our dedicated test environment to see if I can replicate the problem. Any help in the interim would be greatly appreciated.

Update 1
As requested, herewith a graph that includes the Checkpoint Pages/Sec, Page Life Expectancy, and some disk latency counters.

It appears as if the Checkpoint (light blue line) is the cause of the reduced performance (yellow line) we're observing.^

The disk latency remains relatively consistent during processing and the page life expectancy does not seem to have any noticeable effect. We also adjusted the amount of ram available for SQL Server, which also did not have a big effect. Changing the recovery model from SIMPLE to FULL also made little difference.

Update 2
By changing the "Recovery Interval" as follows, we've managed to reduce the interval at which checkpoints occur:

EXEC sp_configure 'show advanced options',1
GO

RECONFIGURE
GO

EXEC sp_configure 'recovery interval', '30'
GO

RECONFIGURE
GO

EXEC sp_configure 'show advanced options',0
GO
RECONFIGURE

I am unsure whether this is bad practice though?

Others have already pointed out the culprit: SQL Server accumulates updates in memory (in the buffer pool) and only flushes them out periodically (at checkpoints). The two options suggested (-k and checkpoint interval) are complementary:

-k will make the cause the checkpoint to produce less aggressive IO requests and last longer
lowering recovery interval will cause the checkpoint to start more often

But I did not respond only to regurgitate the fine comments you received do far :)

What you're seeing is, unfortunately, a very typical behavior of queued processing. Whether you use Service Broker queues or opt for using tables as queues approach, the system is very prone to this kind of behavior. This is because queuing based processing is write heavy, even more write heavy than OLTP processing. Both enqueue and dequeue primitives are write operations and there are almost no read operations. Simply put, queue processing will generated the most writes (= most dirty pages, and most log) compared to any other workload, even OLTP (ie. TPC-C like workload).

Very importantly, the writes of a queue workload follow an the insert/delete pattern: every row inserted is very quickly deleted. This is important to distinguish from an append-only pattern of a insert heavy (ETL) workload. You are basically feeding the ghost cleanup task a full meal, and you can easily outrun it. Think about what that means:

enqueue is an insert, it will create a dirty page
dequeue is a delete, it will dirty the same page again (it may be lucky and catch the page before checkpoint, so it will avoid double-flush, but only if is lucky)
ghost cleanup will cleanup the page, making it dirty again

Yes, it really means that you may end up writing a page three times to disk, in three different IO requests, for each message you process (worst case). And it also means that the random IO of checkpoints will be really random as the write point of the page will be visited by those moving heads again between two checkpoints (compare with many OLTP workloads tend to group the writes on some 'hot spots', not queues...).

So you have these three write points, racing to mark the same page dirty again and again. And that is before we consider any page splits, which queue processing may be prone too because of the insert key order. By comparison 'typical' OLTP workloads have a much more balanced read/write ratio and the OLTP writes distribute across inserts/updates/deletes, often with updates ('status' changes) and inserts taking the lion's share. Queue processing writes are exclusively insert/delete with, by definition, 50/50 split.

Some consequences follow:

Checkpoint becomes a very hot issue (no longer a surprise for you)
You'll see heavy fragmentation (the fragmentation per-se won't matter much as you are not going to do range scans, but your IO efficiency suffers and ghost cleanup has more to work, slowing it down even more)
Your MDF storage random IO throughput is going to be your bottleneck

My recommendation comes in 3 letters: S, S and D. Move your MDF to a storage that can handle fast random IO. SSD. Fusion-IO if you have the moneys. Unfortunately this is one of those symptoms that cannot be resolved with more cheap RAM...

Edit:

As Mark points out you have two logical disks backed by one physical disk. Perhaps you tried to follow best practices and split log on D: and data on C: but alas is to no avail, C and D are the same disk. Between checkpoints you achieve sequential throughput but as soon as checkpoint starts the disk heads start to move and your log throughput collapses, taking down the entire app throughput. Make sure you separate the DB log so that is not affected by data IO (separate disk).


No 'Copying to tmp table' state in profiling a mysql query I have a table with 100k rows

CREATE TABLE `small_table` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `varc` varchar(255) DEFAULT NULL,
  `txt` text,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=103925 DEFAULT CHARSET=utf8

and I had executed the following query

select varc,count(*) from small_table group by varc;

and I had profiled the same

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000060 |
| checking permissions | 0.000010 |
| Opening tables       | 0.004685 |
| init                 | 0.000025 |
| System lock          | 0.000006 |
| optimizing           | 0.000002 |
| statistics           | 0.000010 |
| preparing            | 0.000006 |
| Creating tmp table   | 0.000020 |
| Sorting result       | 0.000003 |
| executing            | 0.000005 |
| Sending data         | 0.001720 |
| Creating sort index  | 0.000033 |
| end                  | 0.000002 |
| query end            | 0.000004 |
| removing tmp table   | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000004 |
| freeing items        | 0.000015 |
| cleaning up          | 0.000007 |
+----------------------+----------+

The profile has Creating tmp table state (by mysql docs it means a tmp table is created either in memory or in disk). My doubt is that the profile does not have Copying to tmp table state (during the state, The server will be copying to a temporary table in memory.) My assumption is that it has created a tmp table and not using it. Am I right?

Where is my Copying to tmp table state?

It just has a slightly different name. It's "Creating tmp table", which also makes more sense. MySQL knows, that a temporary table will be needed, so it generates it on the fly. The state name "copying to tmp table" is just poorly chosen.


Autocomplete in Intellisense SSMS 2017. Where'd it go? I recently got a new dev box, got my Visual Studio installed.  Got my SSMS installed.  Started writing some procs and tables... no intellisense help.
Whaaa??
All the boxes are ticked.  I'm in the right DB. I get the red squigglies if the table doesn't exist, but I have no flyout for object name completion.  Surely I messed something up because my googling tells me 2017 has much improved intellisense capabilities.
Nov 06 2018 Update
I removed my own answer saying that caching had been the culprit, because it still seems super flaky on what will and won't allow auto complete.  I can hop back to an earlier version of SMSS and get the kind of of snappy results I was used to until this version.
I've started using Azure Data Studio for day to day stuff.  It's super snappy. Lots of QoL improvements.  Not even close to enough features for server management, but lots better for general proc writing and data analysis.

Having just spent two days with the same problem, I finally found the answer (in my case).  Intellisense does not work in SQLCMD mode, and mine was on by default (Tools, Options, Query Execution, SQL Server, General, second last!)


When to apply normalization in database design Hello database administrators.
Recently, I have been reading about normalization in order to improve my database design skills. However, I am a bit confused about when to apply this technique. Prior to learning about normalization I would usually read the scenario/description in the requirements and draw up an ER diagram (using the Chen's notation) using nouns as entities and verbs as clues to their relationships then go straight to carrying out the SQL statements to create the tables.

At the moment it seems to me that normalization is carried out after designing an ER diagram then applying it to any entity that contains a multi valued attribute.

Am I correct in thinking this? Or am I supposed to carryout normalization prior to creating an ER digram, perhaps after listing the possible attributes of a system?

It sounds like you learnd it somewhere in an CS course or similar (which is a good thing IMO). I learnd it in some classes as well and I liked the approach that was taken by the teacher:

colleagues from other universitites, courses,... told me they were given big papers that explained normalization in a very theoretical (therefore rather boring) manner and they had no clue how to move this theretical knowledge into an ER-diagram.

We, on the other hand spend many hours learning how properly design ER-diagrams, the ideas that make up a good and maintainable database, how to solve arising problems in the diagram as well as in the database (with SQL) itself and much more. After these concepts were thought, we were told "what you did is called 'normalization'". You have done this and that when designing the ER, this is the 1st form of normalization.....

A sentence that got stuck in my head for such questions from my teacher is "with a well-though and properly designed ER-model, you ALREADY have a normalized database, so you do not need to think about all the forms of normalization".

Since your question is not about HOW/WHY use normalization but WHEN in the designing process, I think my long text above gives you a possible answer: when you understood the principles of good DB design, than your final ER-model IS normailzed (e.g. you created proper join tables, used foreign keys were applicable,...). I think it is a process that needs to go hand-in-hand with the setup of the tables, their relations and so on.

Of course, normalization is not always needed or wanted but in most cases, "drawing" the ER and thinking it through saves you from applying normalization by looking at the model and thinking: "OK, 1st form, are my values as atomic as possible"


pg_basebackup not running even after trying all the methods I configured pg_basebackup on another server, where it is running perfectly, but in one of the servers pg_basebackup is not running at all. Below are the error details. I tried multiple ways to troubleshoot, but didn't solve the issue; please help. The PostgreSQL version is 11.7.

-bash-4.2$ pg_basebackup -p 5432 -U postgres -Ft -D /var/lib/pgsql/backup/cluster1day1
pg_basebackup: could not get write-ahead log end position from server: ERROR:  could not open file "./.pg_ident.conf.swp": Permission denied
pg_basebackup: removing contents of data directory "/var/lib/pgsql/backup/cluster1day1"

You did something that you shouldn't do: you used the root user to edit a file in the PostgreSQL data directory using vi.

Then the session got interrupted, and now you are stuck with a swp file in the data directory that is owned by root. The operating system user postgres, who reads the files in the data directory to send them to pg_basebackup, cannot read that file and throws the appropriate error message.

Solution: remove the offending file from the data directory, and everything will work again.


Very large Transaction Log File - When is it normal? I have a server that is running extremely low on disk space and I dug into to determine if something is not functioning properly or if it simply is a healthy database that just happens to be growing.

I discovered that an ldf for a particular DB has reached 116gb (more than half the available space on the drive). However everything seems to be operating as it should in order to properly maintain the ldf size:

-Nightly Full backups
-Morning log backups

I also tried shrinking the ldf to free any unused space and got almost a single mb back. All things considered would this be a case where we just need to either A)increase disk space or B)move this DB's ldf to a different location?

Any feedback is greatly appreciated!

Log truncation frees space in the log file for reuse by the transaction log. You must regularly truncate your transaction log to keep it from filling the allotted space. Several factors can delay log truncation. One of the most probable reason - you didn't make log backups. You need to backup your transaction log and after that you will be able to shrink it. If you don't need log backups you can switch database recovery model to simple and shrink the log.
Other possible reasons are a bit more complicated (for example, you may have some issue with database mirroring or long running transactions). You can find more details here:
https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#FactorsThatDelayTruncation


Can't debug this simple SQL Statement I'm importing data into a MySQL table using LOAD DATA LOCAL INFILE.
I.ve done this on another table without difficulty, and I copied the stmt and just changed the table name and the fields. Here's the statement:

LOAD DATA LOCAL INFILE '/John/league_seasons.csv'
INTO TABLE lwl_league_players
IGNORE 1 LINES
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(id, location_id, game_id, league_season_id, player_id);

Here's the error:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(id, location_id, game_id,' at line 4

The FIELDS TERMINATED BY and LINES TERMINATED BY lines are exactly the same as my previously working statement. I've checked the structure and it's fine.

I'd appreciate someone pointing out where I'm screwing up.  Thanks

The order of the options matter.

Once you placed IGNORE 1 LINES, the next thing should be the list of columns.

LOAD DATA LOCAL INFILE '/John/league_seasons.csv'
INTO TABLE lwl_league_players
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, location_id, game_id, league_season_id, player_id);

See the MySQL Documentation on the syntax and you will see the correct order for the options.


DB Strategy on MSSQL Server I am asked to prepare a Database Strategy for my Company? My company is using MSSQL Server 2016 with 1 instance and 10 databases. What should I include in the Database Strategy? Any format or template?

In strategy what do you try to ask?

RTO: Recovery time objective, how long do you have to restore data,
RPO: Recovery point objective, how much data are you allowed to lose when reverting to a restore,
SLA: How fast does it need to be and how are maintenance windows
managed between the data owners,
Features: What version of SQL server do you need,
Data sizing: How much data will the system have in N years from now, do you have a storage strategy, RAM needs?,
Data retention: how long do you need to retain the data for legal compliance,
Users: Data sensitivity and authentication schemes that need to be supported,
Lineage: where does the data come from and where does it go.
Governance: who in the business (not IT) owns what data and has authority to grant and restrict access
PII: what is held and what uses have been agreed to


how to check whether Oracle database is accessed or not? I am an Oracle DBA. Application owner is asking me when was the last time Oracle database was accessed? How do I find information about the DB usage timeline? I mean, I want to check whether it is being used by the application or not?

Unfortunately the application owner has no idea whether her application is used by the users or not.

From DBA's side which data dictionary should I look into? Can ASH help? if yes, then which specific section should I focus on?

Take a look at it this thread on AskTom:

  Auditing of course would be the easiest way

  You could possibly get something from the DBA_HIST views (part of AWR/ASH - workload repository and active session history) but bear in mind

  they are sampled snapshots - not 100% of everything is in there
  many of them are "top" reports only - dba_hist_seg_stat for example - shows IO's against segments - it seems perfect, but it is only "This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT" - it will not have 100% of everything.

  Auditing is the right answer, funny thing with auditing - we only really want it when it isn't on don't we :)

Oracle has a lot of history tables as you can see here:
http://www.dba-oracle.com/oracle10g_tuning/t_dba_hist_data_dictionary_views.htm


How do oracle date time work? I have following where condition in query. What the time is between T1 and T2 means it includes or excludes the boundary. Also between T1 and T1 means what?I am confused as both returns result.

DATETIME BETWEEN TO_DATE(D1-T1,'MM-dd-yyyy HH24:MI:SS' )  AND TO_DATE(D1-T2,'MM-dd-yyyy HH24:MI:SS' )

DATETIME BETWEEN TO_DATE(D1-T1,'MM-dd-yyyy HH24:MI:SS' )  AND TO_DATE(D1-T1,'MM-dd-yyyy HH24:MI:SS' )

BETWEEN is inclusive. From the documentation:

  And the value of

expr1 BETWEEN expr2 AND expr3

  is the value of the boolean expression:

expr2 <= expr1 AND expr1 <= expr3

So your first clause will match exactly T1, exactly T2, and anything in between. Your second clause will match exactly T1, i.e. any rows where DATETIME is exactly T1.


Mirroring multiple source databases to a single destination in SQL Server 2008 How can we maintain multiple source databases data in a single destination server database   in SQL Server 2008? They all are at different networks, if that makes any difference.

Mirroring doesn't work like that. You can't bring multiple databases into a single database. You need to look to Replication, specifically Merge Replication as a solution for this. It can be set up across domains and it's specifically designed to take multiple different databases and bring them back to a single location. Mirroring is for creating a copy, a mirror, of a database. If you have more than one database, you'll have to maintain more than one mirror.


Select FK constraints with ON UPDATE NO ACTION We use Postgres 9.5.

Is it possible to modify this script to show the ON UPDATE action for each foreign key reference at this table? Or get this values somehow?

 SELECT
     tc.table_schema,
     tc.constraint_name,
     tc.table_name,
     kcu.column_name,
     ccu.table_schema AS foreign_table_schema,
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name
 FROM
     information_schema.table_constraints AS tc
     JOIN information_schema.key_column_usage AS kcu
       ON tc.constraint_name = kcu.constraint_name
       AND tc.table_schema = kcu.table_schema
     JOIN information_schema.constraint_column_usage AS ccu
       ON ccu.constraint_name = tc.constraint_name
       AND ccu.table_schema = tc.table_schema
WHERE constraint_type = 'FOREIGN KEY';

For the "update action" as in:

FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE CASCADE ON DELETE CASCADE
FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE DELETE ON DELETE CASCADE
FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON UPDATE NO ACTION ON DELETE CASCADE

etc.

.. look in the system catalog table pg_constraint. It contains the column confupdtype which holds, according to the manual:

  Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default

Queries

Find all FK constraints with ON UPDATE NO ACTION pointing to a given table (your question in the title):

SELECT conrelid::regclass        AS table_from
     , conname                   AS constraint_name
     , pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  contype = 'f'
AND    confupdtype = 'n'
AND    confrelid = 'public.target_table'::regclass  -- target table here
ORDER  BY 1;

Inspect all FK constraints in the DB:

SELECT conrelid::regclass        AS table_from
     , conname                   AS constraint_name
     , pg_get_constraintdef(oid) AS constraint_def
     , CASE confupdtype
        WHEN 'a' THEN 'NO ACTION'
        WHEN 'r' THEN 'RESTRICT'
        WHEN 'c' THEN 'CASCADE'
        WHEN 'n' THEN 'SET NULL'
        WHEN 'd' THEN 'SET DEFAULT'
        ELSE 'WARNING: unexpected value!'
       END                       AS fk_update_action
FROM   pg_constraint
WHERE  contype = 'f'
ORDER  BY 1;

Related:

Retrieving all PK and FK


Query takes longer as domain user than local admin I have an application that runs ad-hoc queries to a remote SQL server. I'm running into a bizarre problem with the application taking significantly longer to return results when the application is running as a user versus a local administration (to the workstation).

I launched my application normally, and then launched a separate instance by right  clicking > run as administrator.

I started profiling the database. Using the exact same steps with the exact same query, the 'user' application takes 15 seconds to return data.
When I run the application 'as administrator' it takes only 3 seconds.

I can't find a reason why the same application using the same steps running the same queries is taking significantly longer to return results. I've run the test several times with similar discrepancies.

I can reproduce the issue using multiple workstation and multiple domain user accounts. Anytime the user runs the application 'as administrator' (invoking local admin), it runs much quicker.

Any insight would be helpful here, I'm completely stumped.

EDIT: The application authenticates to the database using SQL authentication in both cases (using the same credentials).

After a few hours and some pow-wow time with my developers, we discovered the issue:

I typically uncheck login\logoff auditing in SQL profiler, but it happened to be the window I needed to peek on the cause. When running 'as admin' the application connects using TCP\IP.

When running as just a user, the app connects over Named Pipes. I'll still need to work out why, but in the mean time I can force the app to run in TCP\IP using a different connection string.


MSG 666 when running an insert query on 80M-row indexed table Strangely, my stored procedure started to receive Msg 666 for some input data.

Stored procedure fails on the last step when it tries to insert a row into a table with the following structure:

Columns:
A_Id: PK, int
B_Id: PK, FK, int
C_Id: PK, FK, int
D_Id: PK, smallint

This is essentially a table that connects all referenced entities together.

Indexes:
IX_TableName_D_id - Clustered index on D_id column
PK_TableName - Unique non-clustered index on all columns (A_Id, B_Id, C_Id, D_Id)

Fragmentation for both indexes is low (<25%). However PK_TableName fragmentation quickly grows, since the amount of operation on the table is quite intense.

Table size:

Row count: ~80,000,000 rows

So, when I try to run a veeery simple query, for some of D_Id's I get the following message:

  Msg 666. The maximum system-generated unique value for a duplicate group was
  exceeded for index with partition ID 422223771074560. Dropping and
  re-creating the index may resolve this; otherwise, use another
  clustering key.

Query example:

INSERT INTO TableName
(A_Id,B_Id,C_Id,D_id)
VALUES (1,1,1,14)

For example, when I set D_Id to some values - it fails, '14' for example. If I set D_ID to other values (1,2,3,...13, 15,16,...), the query runs fine.

I suspect there's something really bad going on with indexes... But I cannot get to the bottom of this... :( Why it fails?

The low selectivity issue mentioned by Remus is not sufficient on its own to cause the problem on that size table.

The uniqueifier starts at 1 and can go up to 2,147,483,646 before actually overflowing the range.

It also requires the right pattern of repeated deletes and inserts to see the issue.

CREATE TABLE T
(
X SMALLINT,
Y INT IDENTITY PRIMARY KEY NONCLUSTERED
)

CREATE CLUSTERED INDEX IX ON T(X)

INSERT INTO T VALUES (1),(1),(1),(2),(2)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 2 |           1 |
| 1 | 3 |           2 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Then running

DELETE FROM T
WHERE Y IN (2,3)

INSERT INTO T VALUES (1),(1)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 6 |           3 |
| 1 | 7 |           4 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Showing in that case the uniqueifier did not reuse the values from the deleted rows.

However then running

DELETE FROM T
WHERE Y IN (6,7)
WAITFOR DELAY '00:00:10'
INSERT INTO T VALUES (1),(1)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 8 |           1 |
| 1 | 9 |           2 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Showing that the high water mark can be reset after deleting the duplicate with the highest uniqueifier value. The delay was to allow the ghost record cleanup process to run.

Because life is too short to insert 2 billion duplicates I then used DBCC WRITEPAGE to manually adjust the highest uniqueifier to 2,147,483,644

I then ran

INSERT INTO T VALUES (1)

multiple times. It succeeded twice and failed on the third attempt with error 666.

This was actually one lower than I would have assumed. Meaning that the highest uniqueifier inserted was 2,147,483,646 rather than the maximum int size of 2,147,483,647


optimizing or estimating postgresql table size on disk This question is in the context of the helpcovid GPLv3+ project (work in progress in March 2020). With PostgreSQL 11 on Debian/Buster/x86-64. See the file hcv_database.cc there.

I have a table of web users:

---- TABLE tb_user
CREATE TABLE IF NOT EXISTS tb_user (
  user_id SERIAL PRIMARY KEY NOT NULL,  -- unique user_id
  user_firstname VARCHAR(31) NOT NULL,  -- first name, in capitals, UTF8
  user_familyname VARCHAR(62) NOT NULL, -- family name, in capitals, UTF8
  user_email VARCHAR(71) NOT NULL,      -- email, in lowercase, UTF8
  user_gender CHAR(1) NOT NULL,         -- 'F' | 'M' | '?'
  user_crtime DATE NOT NULL             -- user entry creation time
); --- end TABLE tb_user

Imagine I have a million rows in that table, and 99% of users have a user_firstname of less than 16 bytes, and 99% of users have a user_family of less than 32 bytes.

Assume that this table is only filled by INSERT SQL statements.

What would be the estimated disk space consumption of the PostgreSQL files (under /var/lib/postgreql) containing that database. As measured by du -h

For simplicity, assume that this table is the only one in the database.

To ask the question otherwise, the VARCHAR(31) size is used for every row on disk, or not?

In other words, how practically important is the width of columns in tables, w.r.t to disk space?

Related question: How does Postgres store many varchars on disk?

The best way is to run a little benchmark:

INSERT INTO tb_user
   (user_firstname, user_familyname, user_email, user_gender, user_crtime)
SELECT substr(md5(random()::text), 1, 15),
       substr(md5(random()::text), 1, 15),
       md5(random()::text),
       'F',
       current_date
FROM generate_series(1, 1000000);

SELECT pg_size_pretty(pg_total_relation_size('tb_user'));

 pg_size_pretty
----------------
 126 MB
(1 row)

A text or varchar that is short enough (which the ones in the table certainly are) will consume one byte more than the number of bytes in the string.

There is some overhead caused by block and tuples headers and other metadata, as well as by fragmentation (some empty space will remain in each 8kB block). And then the index that supports the primary key is also sizeable:

\di+ tb_user_pkey
                            List of relations
 Schema  |     Name     | Type  |  Owner  |  Table  | Size  | Description
---------+--------------+-------+---------+---------+-------+-------------
 laurenz | tb_user_pkey | index | laurenz | tb_user | 21 MB |
(1 row)

Some additional hints:

You can save a little space by having user_crtime in the second position. Otherwise, you might lose up to three bytes to padding.
Use bigint for the primary key so that you can never run out of values. Trying to save on these 4 bytes is usually not a good idea.


mysql.user table damaged suddenly I didn't do an upgrade or anything but suddenly MySQL (MariaDB) stopped working after a restart. This is the log:

2018-02-11 14:10:02 140193631740480 [Note] Using unique option prefix 'innodb_buffer_pool_instance' is error-prone and can break in the future. Please use the full name 'innodb-buffer-pool-instances' instead.
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: The InnoDB memory heap is disabled
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Using Linux native AIO
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Using SSE crc32 instructions
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Completed initialization of buffer pool
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Highest supported file format is Barracuda.
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: The log sequence number 1445306045 in ibdata file do not match the log sequence number 1445306065 in the ib_logfiles!
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: 128 rollback segment(s) are active.
2018-02-11 14:10:03 140193631740480 [Note] InnoDB: Waiting for purge to start
2018-02-11 14:10:03 140193631740480 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.36-82.1 started; log sequence number 1445306065
2018-02-11 14:10:03 140190647551744 [Note] InnoDB: Dumping buffer pool(s) not yet started
2018-02-11 14:10:03 140193631740480 [Note] Plugin 'FEEDBACK' is disabled.
2018-02-11 14:10:03 140193631740480 [Note] Recovering after a crash using tc.log
2018-02-11 14:10:03 140193631740480 [Note] Starting crash recovery...
2018-02-11 14:10:03 140193631740480 [Note] Crash recovery finished.
2018-02-11 14:10:03 140193631740480 [Note] Server socket created on IP: '127.0.0.1'.
2018-02-11 14:10:03 140193631740480 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

I tried looking around but can't quite see how to fix it.

Try mysqlcheck --all-databases.  That will probably say that mysql.user needs repair.  Then do

mysqlcheck --all-databases --repair

Ref: https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html


Is it acceptable to have a default value on a foreign key Say we have a tabled called Account that has a foreign key FK_CurencyID, is it acceptable to hard set the default value of the FK_CurrencyID?

For example, if we wanted the default currency of all accounts to be GBP. In the currency table, GBP has a primary key of 50. The primary key data type is an int identity(1,1) field.

The problem I see see with setting the default of Account.FK_CurrencyID to 50 is that:

Since the PK of currency is an identity, the PK of GBP may be different across instances (e.g. It may be different on Live, UAT and on any of the developers local machines). There are ways to force the PK to be 50 for GBP initially so we can work around this as long as nothing changes.
Since the currency table may change, we could end up in a situation where we have to delete GBP as an option, which would cause the system to fall down until we update the default value.

I think the problem ultimately comes down to mixing / coupling code & system design with data (is there a word for this?)

Is it acceptable to set the default foreign key value to a hard coded ID or should we remove the default and have it handled in the logic layer of the application?

It is acceptable, and you can prevent some of your concerns by making sure the database creation script includes inserting the default values in your data, with a specific ID... that is what

   set identity_insert on

is for.

Of course, as you mention, there is a trade off, once you decide the default, you are constraining your system against some possible future requirements. But all decisions come to trade offs, and any change to such defaults should be properly evaluated on any case.

The good part about setting it as a default constraint is that it is self-documenting, and the intention is obvious enough. The scenarios you mention would show up right away as referential integrity errors.


mysql's query cache behaviour on LIMIT Is it accurate to assume that MySQL's query cache gets checked on subsequent execution of queries in the form SELECT ... WHERE ... LIMIT x, y where the the only varying paramaters in the query are x and y?

Actually... no.

The query cache in MySQL, as I discussed at length in another answer, is an optimization that does only one thing: it returns the exact same result set in response to the exact same query, if the previous result was cached and has not been removed from the cache due to invalidation (by changes to the data in the underlying tables) or pruning to make room for more recent queries.

The query cache is checked before the query reaches the parser, whenever a query comes in starting with the characters "SEL" (case-insensitive).

If the query, byte-for-byte, is not identical to one already cached, the query will not be served from the cache.  If anything changes, even a single space in the query, then that's a different query according to the query cache.

If a query with LIMIT X,Y or the equivalentLIMIT ... OFFSET ... is cached, then only the actual rows returned are stored in the query cache, and only the identical query with the same limit and offset will fetch those same rows from the cache again.


Change logging level MongoDB We are experiencing huge logs creation in our MongoDB shard setup. Log settings used are default values -

            firstset:PRIMARY> db.getLogComponents()
  {
"verbosity" : 0,
"accessControl" : {
    "verbosity" : -1
},
"command" : {
    "verbosity" : -1
},
"control" : {
    "verbosity" : -1
},
"executor" : {
    "verbosity" : -1
},
"geo" : {
    "verbosity" : -1
},
"index" : {
    "verbosity" : -1
},
"network" : {
    "verbosity" : -1,
    "asio" : {
        "verbosity" : -1
    },
    "bridge" : {
        "verbosity" : -1
    }
},
"query" : {
    "verbosity" : -1
},
"replication" : {
    "verbosity" : -1
},
"sharding" : {
    "verbosity" : -1
},
"storage" : {
    "verbosity" : -1,
    "journal" : {
        "verbosity" : -1
    }
},
"write" : {
    "verbosity" : -1
},
"ftdc" : {
    "verbosity" : -1
}

}

We need only error and warning logs. Reference document (https://docs.mongodb.com/manual/reference/log-messages/) mentions these levels. I need to know how i can change the level.

You have the verbosity set to 0 overall and every individual component is set to -1, which means they will all inherit the parent verbosity setting (of 0). That is as low as the verbosity setting goes, to further reduce the logging you can set systemLog.quiet but that is not recommended for production systems because it can make tracking down issues too difficult and it may not address the root cause in any case.

You mention that the reason you are looking into this is the volume of logs you are seeing, so I would suggest the best thing to do would be to figure out what is causing the excessive logging (slow queries? something else?) at the lowest verbosity level and address that.

Note that if it is slow queries (and you are OK with them being that slow) you may just want to adjust the default 100ms operationProfiling.slowOpThresholdMs threshold upward to reduce that logging specifically.


Start SQL Server from SSMS Is there a way to start SQL Server directly from SSMS?

I ask because I often go directly into SSMS, only to realize that I haven't started the SQL Server service yet. Which means either running a different app or messing around with the command prompt.

You can start the SQL Server service from SSMS Registered Servers list (View-->Registered Servers). You might need to first register the desired server (local or remote).

Right-click on the registered name and select Service control-->Start.


Removing Database from sharded cluster in Mongo DB I Have a mongo shard db system with two shards and and three mongos servers.My application creates DB on daily basis as per our business requirements. We have some sharded collection in each day DB.

I removed some old DB using db.dropDatabse(). Now when i connect to mongos server and run show dbs these databases are not there but when i check config database it still has entries for these database in in different collections. Like database,changelog,chunk

Could you please tell me how i can permanently delete these database

Unfortunately there is some stuff that will not be cleaned up through dropDatabase. On the other hand if you delete it by hand may not will be harmful. Some data like statistics will reside on your nodes anyway till the restart of mongods. (Try it out first! Not on the production), as the configuration database is relatively small and does not have huge requirements in terms of performance, that maybe even not a problem, just to be on the safe side and leave them as they are.

Also something to check out how to make the manual actions:
https://stackoverflow.com/questions/9407838/mongodb-dropdatabase-not-working

and as it is mentioned on this question the dropDatabase in such case a bit bugy:https://jira.mongodb.org/browse/SERVER-4804