Performance tuning a cascading delete with many foreign keys I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data (say, 400k rows) which seemed fine, but I don't understand one thing.

Stripped down view of data model:

table ParentObject
      int parentObjectId (PK)

table Child
      int childId (PK)
      int parentId (FK)
      <stuff>

table GrandChild
      int grandChildId (PK)
      int childId (FK)
      <more stuff>

Where a parent object might have 200,000 Children, and a Child has 2 or so GrandChildren. I am interested in tuning the performance of:

DELETE FROM ParentObject WHERE parentObjectId = %d;

On Grandchild, there is an extra nonclustered index on (childId, + two other columns) as well as the primary key index. On child there is an extra nonclustered unique index (parentId, + two other columns).

The thing that I saw in the query plan is that while deleting the Grandchild objects, there were two expensive sorting operations mixed in with the deletions, and I don't understand why they are there.

What should I be looking at to help this delete operation go faster? Does it need to sort? Would it help if I denormalized the ids and added a parent Id to the grandchild table? Did I build my index stupidly?

The full execution plan is here.

To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index.

This can be a good optimization, though the details depend on your hardware, how likely the affected pages are to be in memory, and whether the sorts can complete within the memory allocated to them. When the optimizer decides the cost of sorting will be paid back by the increased efficiencies associated with sequential index access, it sets a property DMLRequestSort on the update operator:

The optimizer may also decide to split the update into separate operators to maintain the clustered index (or heap) and then the nonclustered indexes. often, it will decide to sort more than once - first for the clustered index keys, and then again for the nonclustered index(es). Again, where sorting is considered optimal, each index update operator will have the DMLRequestSort property set to true.

All that said, the things I would fix first would be to eliminate the index scans where the join operator they feed is a nested loops join, and to remove the eager index spools, which are inserting rows into an empty index every time the query is executed. An eager index spool is often the clearest possible sign that you are missing a useful permanent index. The seek predicate in the index spool operator identifies the keys the optimizer would like an index on.

Examples of tables that are missing a nonclustered index (requiring an eager index spool) are:

child6gc8Selections
gc9s
child7s
gc6s

Examples of tables that are currently being scanned below a nested loops join are:

child1
parentObjectMessages
child8s
child7s
child6s
child5s
child4s
child3s
child2s

Taking the example shown above, the Clustered Index Scan has an output list of Id, parentObjectId, the Nested Loops Join predicate is child7s.parentObjectId = parentObject.Id, and the join output column list is child7s.Id.

From that information, it seems a good access method (index) on child7s for this part of the query would be keyed on parentObjectId with Id as an included column. You should be able to work out how best to work this into your existing indexing strategy.

The following are examples of tables where the optimizer is currently choosing a hash join. I would check tables like this to ensure that is a reasonable access method:

child6gc8Selections
gc2s
gc5s
gc6Properties

The table child2bigChild also participates in a merge join where an explicit sort is necessary. Again, I would check to see if this sort could be avoided.

Once the basic indexing issues are resolved, we can look at other optimizations if necessary.


What is wrong with table_cache hit rate? In my.cnf I have:

table_cache            = 524288
open_files_limit        = 65535

Both are at max allowed value for mysql config. Both are less than max open file limit:

# cat /proc/sys/fs/file-max
2097152

MySQL Variable Status:

mysql> SHOW GLOBAL STATUS LIKE 'open%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Open_files               | 193    |
| Open_streams             | 0      |
| Open_table_definitions   | 594    |
| Open_tables              | 802    |
| Opened_files             | 537248 |
| Opened_table_definitions | 4895   |
| Opened_tables            | 9174   |
+--------------------------+--------+
7 rows in set (0.00 sec)

Server has 32GB RAM. Mostly free!

Still, when I run mysqltuner script:

It says:

  [!!] Table cache hit rate: 13% (853 open / 6K opened)

Any reason table_cache hit rate is poor?

One thing, here, is that you should be using this form, instead:

mysql> show global status like '%open%';

Some of these counters are global and some of them are session, so not using the GLOBAL keyword gives you a split set of numbers (especially the Opened_table* values).

The problem with tuning scripts is they can't possibly take into account all of the factors that need to be taken into account when deciding whether values are in a sane range... for example, if you use FLUSH TABLES, your Opened_files and Opened_tables counters will immediately increment because all of the tables that got flushed are re-opened as soon as they're accessed again... which, of course, means nothing at all negative.

Using mysqldump for backups will usually issue a FLUSH TABLES or FLUSH TABLES WITH READ LOCK at the beginning of the backup process, which means if you had been running daily backups and had a server uptime of even a few days, you could easily see a very poor "table_cache hit rate" and, once again, it doesn't mean anything.

"Table_cache hit rate" is not actually a value from MySQL.  It's a calculation from two other values.  All they are doing in mysqltuner is dividing open_tables by opened_tables (how many are open now, compared with how many have ever been opened).

badprint "Table cache hit rate: $mycalc{'table_cache_hit_rate'}% (".hr_num($mystat{'Open_tables'})." open / ".hr_num($mystat{'Opened_tables'})." opened)\n";

So if you observe those two values over time and don't see Opened_tables rapidly incrementing except perhaps during the period after a backup when traffic picks up, then you don't have a problem.

This looks like a false alarm to me.


How to unpack a DACPAC? When I right-click on a DACPAC file in windows explorer, I don't have an option to "Unpack" the file, like this document indicates:

https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/unpack-a-dac-package?view=sql-server-ver15

I have both Visual Studio 2019 and SSMS v18.4 installed. Am I missing something?  Right now the file is associated with Notepad.

Please select "Open With..." and then choose from the list "Microsoft SQL Server DAC Package File Unpack Wizard". It should be also default association for this file type.

If you don't have this option, you can also rename this file to .zip and use any unzip tool.


patching SQL Server with Availability Group and Secondary Replica Scenario: SQL Server 2014 CU6. 1 AG with 2 databases (compatibility level 100). Failover Cluster with 3 nodes on Windows Server 2012 R2. Primary Availability Replica on node 1 and node 2. Secondary Availability Replica on node 3 (no votes). Async commit, manual failover, readable secondary. Node 3 is used to run reports on these 2 databases (out of 8 databases running outside AG on node 1 and 2).

Problem: Looking to patch these from SQL 2014 to SP1 and latest CU. Not my build and no experience with AG or FC so far. My "test" environment is also used for development, so no room for mistakes.

Questions:

What's the best order to patch the 3 nodes ?

Do I first have to remove the databases from AG on the node I'm patching ?

Do I need to take the Secondary out when patching it ?

No experience on this, and I can't find clear answers for my scenario (maybe not supported?) in the docs: https://msdn.microsoft.com/en-us/library/dn178483(v=sql.120).aspx

The scenario is called out and supported on the link you've provided.

Availability Group with One Remote Secondary Replica

If you have deployed an availability group only for disaster recovery, you may need to fail over the availability group to an asynchronous-commit secondary replica. Such configuration is illustrated by the following figure:

Availability Group Upgrade in DR Scenario

In this situation, you must fail over the availability group to the asynchronous-commit secondary replica during the rolling upgrade/update. To prevent data loss, change the commit mode to synchronous commit and wait for the secondary replica to be synchronized before you fail over the availability group. Therefore, the rolling upgrade/update process may look as follows:

1.Upgrade/update the remote server

2.Change the commit mode to synchronous commit

3.Wait until synchronization state is SYNCHRONIZED

4.Fail over the availability group to the remote site

5.Upgrade/update the local (primary site) server

6.Fail over the availability group to the primary site

7.Change the commit mode to asynchronous commit


Sql Server Stored procedure exicution taken long time I have worked in a web based project, in my code sometimes i want to execute a stored procedure more than 1000 times in a single button click. The button is used for some type of approval process and when user clicks the button we approve many things for him, so as the part of process we want to run a stored procedure continuously more than 1000 times. Each time take data from a view and store as xml document then parse that document then add each document as command text then execute the stored procedure. But the problem is it takes long time. 3-4 minutes to take to complete the process . i am looking for improving the performance. Anybody has any suggestion please tell me .

i had already go through this link but still the execution lags

In addition to Kapil's answer, I'd like to add the following...

SQL Server is optimized to perform set-based operations.  Your stored procedure reads very much like an iterative process.  (Do this step, increment counter, do another step, etc.)  I believe that if you restructure your stored procedure to perform one operation on 1,000 rows as opposed to 1,000 operations on a single row, that you will get better performance.  One thing that might help is to understand what a Tally table is.  Use of a Tally table should be able to help you with your incrementing ID values.  You should be able to find many blogs that tell you how to set up and use a Tally table.  (Here is one example.)

Each single-record insert or update is a command that needs to be analyzed, executed and whose results need to be handled.  By reducing the number of executions by a factor of roughly 1,000, you should see improved results.  However we do not have your server, nor your data.  So you'll need to do that footwork.  From reading the two comments that you posted so far, I get the feeling that you're looking for one of us to wave a magic wand and have everything fixed, nice and easy.  Performance tuning is rarely nice and easy.

Good luck.


Optimize very slow SQL joins on multiple tables or use different engine? We have something similar to Google Analytics, but decided to not use something that's already available (but didn't fit our needs exactly), and instead created our own "mini-analytics".

Now, this was all easy and fun, but as it grows, the architecture either wasn't designed properly, or the wrong tools were used to solve the problem.

The problem lies with queries that look like the following: "Get all user sessions that have the following events: login, chrome browser version 58, and profile_view".

Currently this hits the following tables:

CREATE TABLE `logins` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `login_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `browsers` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `browser_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

CREATE TABLE `profile_views` (
    `session_id` bigint(20) NOT NULL,
    `request_id` INT(11) NOT NULL,
    `timestamp` INT(11) NOT NULL,
    `profile_data` mediumblob,
     KEY `session_req_idx` (`session_id`, `request_id`),
     KEY `timestamp_idx` (`timestamp`)
)

Some notes:

All the mediumblob columns are JSON objects, but we haven't upgraded to MySQL 5.7.8 yet.
All tables have the same columns and indexes.
Each table contains in between several million and several billion rows.

One of the problems I seem to have is that I can't limit the inner queries (if using them), and joins also don't appear to work.

What I wonder is mostly: can this be efficiently solved using a SQL solution, or would this lean more towards one of the NoSQL (for example, a graph database) solutions?

--
EDIT:

Queries are built up using a loop that concatenates subqueries in the following manner:

For a single table (e.g. "sessions that have a profile view after timestamp x"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `profile_views`
    WHERE
        `timestamp` > x
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

Two tables (e.g. "Sessions that have a profile view and login"):

SELECT DISTINCT
    `grouped`.`session_id`
FROM (
    SELECT
        `session_id`
    FROM
        `logins`
    WHERE
        `timestamp` >= x
    AND
        `session_id` IN (
        SELECT
            `session_id`
        FROM
            `profile_views`
        WHERE
            `timestamp` >= x
    )
) `grouped`
ORDER BY
    `session_id` DESC
LIMIT
    100

I'm looking into joins, but at the moment they appear to return different results, for example something like the following:

SELECT DISTINCT
    `A`.`session_id`
FROM
    `logins` `A`
INNER JOIN
    `profile_views` `B`
ON
    `B`.`session_id` = `A`.`session_id`
WHERE
    `A`.`timestamp` > x
ORDER BY
    `session_id` DESC
LIMIT 100;

Your query using joins looks ok but it will help speed up the queries if you convert the timestamp_idx key to include session_id. This way the database engine won't have to to another/second lookup/sort and on tables with millions of rows, it will have significant improvement in response times.

ALTER TABLE <table name> ADD KEY ix_covering(`timestamp`, `session_id`,`request_id`);

Drop both indexes on all tables AFTER you have tested that the new index as per above does improve the performance.

Also, instead of

SELECT DISTINCT
    `A`.`session_id`

try

SELECT `A`.`session_id`
FROM `logins` `A`
INNER JOIN `profile_views` `B` ON `B`.`session_id` = `A`.`session_id`
WHERE `A`.`timestamp` > x
GROUP BY `A`.`session_id`
ORDER BY `session_id` DESC
LIMIT 100;


How can I generate an .mdf file containing a subset of my tables and rows? I need to provide some data to an organisation (lets call them DataCo) that we're working with. Since we're both SQL server shops, my counterpart at DataCo requested an .mdf file.

However, our database contains many tables that DataCo don't need. Moreover, they're only interested in rows from the last X years. For many reasons (inc. file size) I'd like an .mdf file containing only the data that DataCo need.

Is this possible?

With some clever jiggery-pokery with partitioning and filegroups you could get a particular sub-set of your data onto just one of the physical files used by a particular database.  Trying to attach that into an existing database at the recipient would be "challengining", I think.  In 20-some years of doing this stuff I've not heard of anyone trying that.*

My suggestions would be
a) copy your DB, delete the stuff you don't want to pass on, send a backup of that to the recipient
b) use BCP with the queryout option to dump just the required data to external files.  Send those to the recipient.

*I'm always willing to learn, however.


Unable to remote access MySQL - Unbuntu I have installed Ubuntu 15.04

I've bind the IP address, there are no firewalls installed, but yet connection is still being refused! I've tried everything I could find online, anyone have any way of doing this or testing to see what is actually wrong?

The "master" server is what I'm trying to connect to from a "slave" server, the master is running MySQL, and is setup with tcadmin and running fine on port 8880. What I'm struggling to do is allow remote connection from the "slave" to the "master".

Root has permission from any host.

I'm using tcadmin to connect on the master server, but when going through the setup, I get

Can't connect to MySQL server on '151.80.108.75' (10061): No connection could be made because the target machine actively refused it 151.80.108.75:3306

in ssh this works

mysql -u root -h 127.0.0.1` - which works

but when using the IP of the server connection is refused, When connecting via the IP it gives error:

  ERROR 2003 (HY000): Can't connect to MySQL server on '' (111)

This is all I have in the cnf file:

[mysql]
bind-address        = ***.***.***.***`

I can ping the address, it currently has tcadmin on it, which uses the port 8880 which works fine, but for some reason, I can't get the remote access to work with MySQL. I'm able to ping it from a remote location.

Doesn't allow connection via telnet, but Unsure how to open a port that isn't being blocked? There's no firewalls installed, nothing should be limiting it, port 8880 for tcadmin works fine?

Verify that the port is open. From a remote host with the default port:
telnet <ip> 3306

Can you show us the my.cnf file, [mysqld] section with the bind address parameter.

Can you ping that ip address at all? Do you use a hostname or an ip address to connect? If it works with ip and not hostname you may have a dns configuration issue.


Why the concept of missing index groups is provided in SQL Server? I know about missing indexes, and that each index belongs to a missing index group which has a unique handle identifier. I want to know why groups are useful when we can just deal with individual missing indexes and what category or attribute of an index puts multiple missing indexes in the same group(cause there is usually one index in a group). Why it is comfortable to keep several missing indexes in the same group?

Thanks for your time!

This was a planned feature that never was implemented (the group concept), but the meta-data (DMV structure) is still there.


Autogrowth Analysis in PROD DB based on the trace file data I have used the below query to check our Production Database Autogrowth events so that we can set to a value which is much more realistic than the current value of 200 mb , unrestricted for mdf file and 10 % Restricted to 2048 gb for ldf file.
The current version of our Database is SQL Server 2012 RTM Standard Version and we are managing it through AWS RDS, the total disk space is 2TB.
When I have used the trace file query ,it shows me data only from 08052018 as you can see in the screenshot attached
Can anyone explain why am i seeing data only from 08052018 and not earlier to that?

You are most likely looking into somethng known as default trace, a light weight trace that runs in the background when SQL Server starts. To see if default trace is enabled:

SELECT value_in_use
    FROM sys.configurations
    WHERE configuration_id = 1568 /* 1 indicates the trace is enabled */

To view the location of trace files:

SELECT  REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 256)) AS DefaultTraceLocation
FROM    sys.traces
WHERE   is_default = 1

You will find four trace files here, and each files are about 5 mb in size. When all four files are full, it rolls over (removes the oldest file from the disk) and a new trace file is created. Therefore, you might not be able to keep track of older data unless you siphon it off to a customized table somewhere.

You can view auto growth setting for your current trace file using:

/*Author - Feodor Georgiev*/
SELECT  TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime

You can also pull the data from previous trace files by slightly modifying the previous query.

SELECT  TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc'
                                                        FROM sys.traces
                                                            WHERE path LIKE '%\MSSQL\Log\log%.trc'
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Data File Auto Grow'
ORDER BY t.StartTime

An example on how to load this data into a table

CREATE TABLE [dbo].[DefaultTrace_History](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [instancename] [nvarchar](256) NOT NULL,
    [EventName] [nvarchar](128) NOT NULL,
    [DatabaseName] [nvarchar](256) NOT NULL,
    [DatabaseID] [int] NOT NULL,
    [NTDomainName] [nvarchar](256) NULL,
    [ApplicationName] [nvarchar](256) NOT NULL,
    [LoginName] [nvarchar](256) NOT NULL,
    [SPID] [int] NOT NULL,
    [Duration] [int] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [GrowthInMB] [decimal](5,2) NOT NULL,
    [FileName] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO

Procedure to load the data incrementally:

CREATE OR ALTER PROCEDURE [dbo].[SP_Load_DefaultTrace_History]
AS

    DECLARE @maxdt DATETIME
    DECLARE @sql NVARCHAR(max)

    SELECT @maxdt = max(starttime)
    FROM   [DefaultTrace_History]

    SET @sql=
    N'INSERT INTO [DefaultTrace_History] (InstanceName,EventName,DatabaseName,DatabaseID,NTDomainName,ApplicationName,LoginName,SPID,Duration,StartTime,EndTime,GrowthInMB,FileName)
    SELECT  @@servername,
        TE.name AS [EventName] ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.Duration ,
        t.StartTime ,
        t.EndTime,
        convert(decimal(19,2),t.IntegerData*8/1024) AS GrowthInMB,
        smf.physical_name AS [FileName]
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX(''\'',REVERSE(path)),LEN(path)))+''log.trc''
                                                        FROM sys.traces
                                                            WHERE path LIKE ''%\MSSQL\Log\log%.trc''
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.master_files smf ON smf.database_id = t.DatabaseID
WHERE   te.name = ''Data File Auto Grow'''
    IF (SELECT count(1)
        FROM  [DefaultTrace_History]
        WHERE  instancename = @@servername) = 0
      BEGIN
          EXEC sp_executesql @sql
      END
    ELSE
      BEGIN
          SET @sql= @sql + ' AND @@servername = @@servername AND t.StartTime >' + quotename(convert(VARCHAR(25), @maxdt, 121),'''') + ' ORDER BY t.StartTime DESC'
          EXEC sp_executesql @sql
      END

--Purge records > 365 days
DELETE FROM [DefaultTrace_History] WHERE [instancename] = @@servername AND [StartTime] < dateadd(dd,-365,getdate())
GO

You can then create a job to run on a schedule to load the data.


xlog flush request is not satisfied in log files Today I saw that Postgres produces error messages related to possible data corruption (?). Everything's working as expected so I didn't realize anything was wrong.

ERROR:  xlog flush request 6E/82FFED10 is not satisfied --- flushed only to 3D/CA02E920
CONTEXT:  writing block 1008 of relation base/118517/118823
CONTEXT:  writing block 1008 of relation base/118517/118823
WARNING:  could not write block 1008 of base/118517/118823
LOG:  request to flush past end of generated WAL; request 6E/82FFED10, currpos 3D/CA02E920
DETAIL:  Multiple failures --- write error might be permanent.

PostgreSQL 11.
Configuration:

max_connections = 100
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 41943kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           6775130872383000604
Database cluster state:               in production
pg_control last modified:             Thu Mar 12 04:46:20 2020
Latest checkpoint location:           85/802C9268
Latest checkpoint's REDO location:    85/802C9230
Latest checkpoint's REDO WAL file:    000000010000008500000080
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:69393025
Latest checkpoint's NextOID:          511846
Latest checkpoint's NextMultiXactId:  11365
Latest checkpoint's NextMultiOffset:  23069
Latest checkpoint's oldestXID:        562
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  69393025
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Thu Mar 12 04:45:50 2020
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            edec50c3ef6ee1a42351c2e593de539feb25343ad239e12b813b4c212ae2a1d6

I did a backup and then I restored it to another database and removed the old one. It didn't work. I still getting those errors.

P.S. PostgreSQL did not crash lately. There is also a plenty of space on SSD.

Yes that looks like corruption, perhaps caused by storage hardware problems.

Perform a pg_dumpall of the cluster and restore it to a newly created cluster, that should get rid of the problem.

Check your hardware.


Should I have a parent table with only one field? My database has multiple tables that all rely on dates. I was wondering if it would be smarter for me to take the date attribute and throw it in a parent table, rather than give each table a date field. Here are two case examples (one with parent table, and one without):

Tables with parent table:

Schedule
--
PK ScheduleId : int
--
Date : date (unique)

Delivery
--
PK DeliveryId : int
--
Name : nvarchar(100)
Time : time(7)
--
FK ScheduleId : int

Exception
--
PK ExceptionId : int
--
Name : nvarchar(100)
--
FK ScheduleId : int

GenericEvent
--
PK GenericEventId : int
--
Name : nvarchar(100)
--
FK ScheduleId : int

Tables without parent table:

Delivery
--
PK DeliveryId : int
--
Name : nvarchar(100)
Date : date
Time : time(7)

Exception
--
PK ExceptionId : int
--
Name : nvarchar(100)
Date : date

GenericEvent
--
PK GenericEventId : int
--
Name : nvarchar(100)
Date : date

In the first case illustrated, everything references a unique Date entry. I'm split because I'm not sure if it would be better to search by the indexed foreign keys to get all the events (deliveries, exceptions, and generic events), or if it would be faster/ better practice to botch the Date parent table, and just search all the individual tables by a date field. The data model for the second case would look as follows:

The other bonus I would achieve from doing the first practice is that since I'm using entity framework, the object relationships would already be in place, and I could search for a date, and automatically receive all related deliveries, exceptions, and genericevents.

Thank you for your input, it's appreciated more than you know!

Do not store dates in a separate table unless there is something about a particular date that has additional information that you need to store.  In general the date is a simple attribute of some other entity, it isn't an entity in itself unless you have a very peculiar application.

One exception to this might be a star schema data warehouse where there is a time dimension table that might use date as the primary key and have attributes that describe the way that date might roll up - assuming that some roll ups might not be trivial to calculate (like fiscal reporting period or some such).

You can index a date column just as easily as an integer column, so there is nothing to be gained by abstracting out a date so that you can give it an integer alias value.

You can also put a date column in a WHERE clause so searching for records by date is not at all onerous to do.


Mysql giving only 25 inserts per second I'm using ubuntu 12.04 LTS
Processor : Pentium(R) Dual-Core CPU E5200 @ 2.50GHz × 2
OS Type : 64-bit
Memory : 3.9 GiB
Disk : 101.1 GB

I installed mysql and wanted to test insert performance. Here is my table structure

 > create table mytable (text varchar(200)) engine=InnoDB;

   +-------+---------------+------+-----+---------+-------+
   | Field | Type          | Null | Key | Default | Extra |
   +-------+---------------+------+-----+---------+-------+
   | text  | varchar(2000) | YES  |     | NULL    |       |
   +-------+---------------+------+-----+---------+-------+

I wrote a simple python script for testing Insert performance.
i.e. How many inserts per second?

  import _mysql
  import time

  con = _mysql.connect('localhost', 'root', 'root', 'mydb')

  stop = time.time()+1

  while time.time() < stop:    # runs loop for 1 sec
      con.query("INSERT into mytable VALUES('Test Data')")

I expected atleast 8-10k inserts. But I'm getting only 25 inserts.

I tried many optimization techniques given in various blogs

1. Tried to run the loop in 1 transaction.
2. Tried LOAD DATA INFILE 'new.txt' INTO TABLE mytable

I also set innodb_flush_log_at_trx_commit=2 in /etc/mysql/my.cnf

But none of them worked, getting only 25-30 inserts per second. What to do?

First, I would argue that you are testing performance backwards. Instead of measuring how many inserts you can perform in one second, measure how long it takes to perform n inserts, and then divide by the number of seconds it took to get inserts per seconds. n should be at least 10,000.

Second, you really shouldn't use _mysql directly. It makes your code non-portable to other Python database adapters.

Third, _mysql is defaulting to whatever auto-commit setting is the default for your database, and MySQL is generally auto-commit on by default. MySQLdb turns off auto-commit by default.

Fourth, if you really want to insert a lot of rows fast, you should use cursor.executemany(sql, list_of_tuples_of_parameters). This is internally converted into a multi-row insert, and is an atomic operation for MySQL.

Please read PEP-249.


Restricting mail sending from SQL Server 2008 On a remote database server, hosting databases from a number of different clients, how do I ensure that someone does not send emails using my profile?

You just need to ensure the profile isn't public when you assign permissions and access to it.  The stored procedure in the msdb database is msdb.dbo.sysmail_add_principalprofile_sp, and this stored proc is what grants permissions for an msdb database role or user.

Excerpt taken from BOL on the subject:

  To make a profile public, specify a @principal_id of 0 or a @principal_name of public. A public profile is available to all users in the msdb database, though users must also be a member of DatabaseMailUserRole to execute sp_send_dbmail.

BOL reference on Creating a Database Mail Private Profile.


Select Count(*) FROM with-clause-named table order by a, b not working I have a series of tables defined in a stored procedure to eventually return to the application like this, then culminating to a simple table which contains distinct values from a previously defined table and the count of how many times that distinct value repeats.  For some reason it does not seem to be working...

...stored procedure...

OPEN p_retcursor FOR
WITH r AS
(SELECT ...
FROM MYDATATABLE),

s  AS
(SELECT ...
FROM r
WHERE ...)
,

t AS
(SELECT ...
WHERE ...
JOIN ...
ON ...
FROM s)

SELECT t.*, COUNT(*) as distinctABcount
FROM t
GROUP BY t.columnA, t.columnB

How do I get this to work?  I guess technically columnA and columnB are not columns of a table, but are columns of t so I think the order of evaluation is a bit messed up due to the fact that t is not an actual table...
Query fails around the count(*) column part and may be failing on the GROUP BY.

Thanks.

Update 1:
I have a theory that if I string together all the named tables into subqueries inside the from clause it will work... but that does not seem like a solution which will produce readable code considering this stored procedure is hundreds of lines long...

Update 2:
The theory was wrong, the derived table was not the problem, but as pointed out by the answer, you cannot ask for more columns than what you have set in the group by clause.  I fixed it by correcting the final section of the query like suggested:

select t.columnA, t.columnB, COUNT(*) as distinctABcount
FROM t
GROUP BY t.columnA, t.columnB

which runs correctly.

Unless there is just something specific to Oracle syntax, my suspicion is that you have more than columnA and columnB in your derived table t. If so, you'll need to group by those columns, apply an aggregate to them or do not ask for them (replace t.* with t.columnA, t.columnB)


Perform UNION ALL regardless of column order in the two joined tables SELECT * INTO TABLE1
FROM Table2
UNION ALL
SELECT * FROM Table3;
GO

I am using this query to stack two tables together into one table. These tables should have the columns in the exact same order for this query to execute successfully.

I am wondering if there is a trick we can do so that it works regardless of the column order. Is there a way to instruct SQL Server to automatically line up the columns from both tables by name and perform the UNION?

In SAS, the trick is to write a OUTER UNION CORR query, for example.

I think you have two options. Either type out the columns in your query or do something with dynamic SQL.

declare @sql nvarchar(max)
declare @col nvarchar(max)

select @col = stuff((select ','+quotename(C.COLUMN_NAME)
                     from INFORMATION_SCHEMA.COLUMNS as C
                     where C.TABLE_NAME = 'Table2'
                     order by C.ORDINAL_POSITION
                     for xml path(''), type).value('.',  'nvarchar(max)'), 1, 1, '')

set @sql = 'select '+@col+' into Table1 from Table2 union all '+
           'select '+@col+' from Table3'

exec (@sql)


Data mismatch on Subscriber Database in SQL server transactional replication We have Transactional replication set up between an OLTP Database server 'A' and reporting DB server 'B', where the Data from OLTP DB(Size 400 GB) "AR" is getting replicated on the server B as database "ARB".

There have been issues reported saying for the Data mismatch on the subscriber database ARB. As per them the latest data is for last two weeks and it should have been the latest instead.

We tried to figure if replication is broken or any error message at publisher or subscriber, but no luck.

In Replication monitor the status shows 'OK'

And status "Running"; Performance "Excellent" and latency varies between 2-3 seconds.

Please help me on how can i find for what could have been possibly going wrong where data does not match between both the DB.

I am new on Tran Replication, any inputs/helps would be appreciated, thanks!

Please help me on how can i find for what could have been possibly going wrong where data does not match between both the DB.

You can use tablediff utility to find out the difference in data between your Publisher and Subscriber server.

Check How to run TableDiff utility for ALL replicated tables ? article by Chris Skorlinski on REPLTalk. This blog has all the required scripts that will help you out.

If you have 3rd party tools like redgate schema and data compare, then your life would become much easier.

As to find out what went wrong, you should check if

replication was changed to have filters applied or some column properties has changed to NOT FOR REPLICATION ?
distribution agent is working fine along with the log reader agent ?
Check where the data is missing -

Is data present in distribution database ?
Is data in distribution database and not in subscriber database ?


PostgreSQL. Unstable execution time hugely exceeding planning time PG version: 10.9

Schema:

create table core_bundle
(
    id serial not null constraint core_bundle_pkey primary key,
    dt_add timestamp with time zone not null,
    dt_upd timestamp with time zone not null,
    aid integer not null constraint core_bundle_aid_check check (aid >= 0),
    prc_after timestamp with time zone,
    prc_grp uuid,
    status integer not null constraint core_bundle_status_check check (status >= 0)
);

create index core_bundle_aid_2d875f86 on core_bundle (aid);
create index core_bundle_prc_grp_a0b838ca on core_bundle (prc_grp);
create index core_bundle_status_5cfd1324 on core_bundle (status);

From time to time execution time for the following query dramatically varies.

explain analyze
    SELECT "core_bundle"."id", "core_bundle"."aid"
    FROM "core_bundle"
    WHERE (
            "core_bundle"."prc_after" <= '2019-07-11T14:29:11.159787'::timestamp
            AND "core_bundle"."status" = 13
        )
    ORDER BY "core_bundle"."id" ASC
    LIMIT 1;

Nice case:

Limit  (cost=266.19..266.19 rows=1 width=8) (actual time=0.288..0.289 rows=1 loops=1)
  ->  Sort  (cost=266.19..266.74 rows=221 width=8) (actual time=0.287..0.287 rows=1 loops=1)
        Sort Key: id
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Index Scan using core_bundle_status_5cfd1324 on core_bundle  (cost=0.43..265.08 rows=221 width=8) (actual time=0.029..0.257 rows=167 loops=1)
              Index Cond: (status = 13)
              Filter: (prc_after <= '2019-07-11 14:29:11.159787'::timestamp without time zone)
Planning time: 0.138 ms
Execution time: 0.319 ms

Bad case:

Limit  (cost=0.43..127.12 rows=1 width=530) (actual time=2208.373..2208.375 rows=1 loops=1)
  ->  Index Scan using core_bundle_pkey on core_bundle  (cost=0.43..391587.09 rows=3091 width=530) (actual time=2208.372..2208.372 rows=1 loops=1)
        Filter: ((prc_after <= '2019-07-11 14:29:11.159787'::timestamp without time zone) AND (status = 13))
        Rows Removed by Filter: 4179221
Planning time: 0.218 ms
Execution time: 2208.419 ms

Shared hit is low for nice case (around 67) and about 1030528 in a bad one.

In a bad case there's neither sort nor status index. I wonder what might be the reasons behind that, and what can be done to get rid of bad cases?

Unfortunately adding an index pinning status to a certain value is not really an option, since value varies in where clause.

May default_statistics_target tuning be of help here, what value should I prefer then? Or would an index on prc_after will be efficient and enough to improve things?

Maybe related:

SELECT very slow when no results and a LIMIT is specified
Index that is not used, yet influences query

The problem must come from varying statistics; perhaps the tables change a lot.

The bad plan comes when PostgreSQL believes that there are so many rows that satisfy the condition that it will be cheaper to scan the rows in ORDER BY order using the primary key index until it hits the first match. Either that assumption is wrong, or the correlation between these conditions is unfortunate.

Anyway, if you know that the first plan is always the correct one, you can tell PostgreSQL not to use the primary key index by modifying the ORDER BY clause like this:

ORDER BY core_bundle.id + 0


Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates? Based on http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx and other articles, SQL Server assumes that a multi-line table valued function returns one row. This causes the selection of a poor execution plan for the calling statement if it actually returns many rows.

Does adding WITH SCHEMABINDING to the RETURNS clause of the CREATE FUNCTION result in a more correct cardinality estimate for the return value of the function?

If we assume that we are passing a UserId to this function and getting back a table of RecordId values that the user is allowed to access, and that some users are only allowed to see a few records and that some are allowed to see many or even all records, would either the function or the calling statements (or the procedures that include them) benefit from using FORCE RECOMPILE? Does the use of WITH SCHEMABINDING in the function change this answer?

I realize that I could figure this out by experimentation, but I am hoping that someone has already figured out the answer. A pointer to someplace where this is well documented would be helpful.

In my tests, no, adding WITH SCHEMABINDING does not improve cardinality estimates. I created a simple table:

CREATE TABLE dbo.myobjects(id INT PRIMARY KEY);

INSERT dbo.myobjects SELECT [object_id] FROM sys.all_objects;

Then two functions:

CREATE FUNCTION dbo.noschemabinding(@UserID INT)
RETURNS @x TABLE (id INT)
AS
BEGIN
  INSERT @x SELECT id FROM dbo.myobjects;

  RETURN;
END
GO

CREATE FUNCTION dbo.withschemabinding(@UserID INT)
RETURNS @x TABLE (id INT)
WITH SCHEMABINDING
AS
BEGIN
  INSERT @x SELECT id FROM dbo.myobjects;

  RETURN;
END
GO

Comparing the actual plans, both show estimated rows = 1, actual rows = 2112 (this latter number may differ on your system depending on version/SP etc).

Comparing the speed:

SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
SELECT id INTO #x FROM dbo.noschemabinding(1);
DROP TABLE #x;
GO 1000
GO
SELECT SYSDATETIME();
GO
SELECT id INTO #x FROM dbo.withschemabinding(1);
DROP TABLE #x;
GO 1000
SELECT SYSDATETIME();

Results:

                    run 1               run 2
----------------    ------------------  ------------------
No schemabinding    14632 milliseconds  14079 milliseconds
Schemabinding       14251 milliseconds  13979 milliseconds

So, does it matter much? Nope.

SCHEMABINDING in this case is used for a more important goal: underlying schema stability. You will probably have much better optimization opportunities if you pursue converting your function to an inline TVF than to chase down obscure plan-affecting differences in a multi-statement TVF.


I am using Amazon REDSHIIFT and want to make my table data case insensitive? How can I change my Tables data stored in REDSHIFT case insensitive?

For example: Bell, BELL, bell in a column should return all the rows when queried.

AFAIK, there's no way to do that. Redshift doesn't allow you to set specific collation settings. To get the CI behavior, you'd have to add either UPPER() or LOWER() consistently to all your queries.

Technically, you could make those case conversions during loading itself and let the application tier handle display data in the appropriate case. However, I'd recommend you do not attempt that unless you know for certain your DW will never have data sets that has to be case sensitive (i.e. source already Y has a different meaning/value from y) or an application that treats different cases differently. In those situations, you've effectively corrupted your data without knowing it.


Creating my first Elastic Database Job on Azure On https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview it says that there is a new replacement for SQL Agent. However, I cannot get it to work.

I had problems with accepting the "Beta software" terms, but my manager accepted the terms on his account, and I could carry on.

On the portal I get this error message:

Deployment failed
Deployment to resource group 'MyRG' failed.
Additional details from the underlying API that might be helpful: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-debug for usage details.

I have also tried the Powershell interface, and here I get stuck on

New-AzSqlElasticJobAgent : No tenant found in the context.  Please ensure that the credentials you provided are authorized to access an Azure subscription, then run Connect-AzAccount to login.
At line:4 char:28
+ $JobAgent = $JobDatabase | New-AzSqlElasticJobAgent -Name $AgentName
+                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [New-AzSqlElasticJobAgent], ArgumentException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.ElasticJobs.Cmdlet.NewAzureSqlElasticJobAgent

It is probably something simple, but I cannot figure out what...

The error on the portal is not clear, but you should be able to find details in deployment summary.

On portal go to your resource group and navigate to Deployment tab. There you can find history of the deployments with details about progress and errors.

The problem with Powershell looks like you don't set subscription context.

You should set the context to subscription which contains Reasource Group to which you want to deploy content.

To make this work use this example:
Example 1: Set the current subscription

After apply current subscription you should be able to see what is the real error during deployment.


How can I store function names to be executed on a subset of rows? I have a table, accounts, which contains the vast majority of the data I am concerned with. accountTypes contains different types of accounts (Business, Personal, etc.). I want to perform calculations on each row of accounts based on the accountType. The best method I can think of for doing this would be to create UDFs, then store the names of the UDFs in a table typeFunctions, consisting of rows of accountTypeID and functionName, as in: P, "f_ageOfAccount" and B, "f_daysSinceLastcontact".

Unfortunately, joining this table to the accounts table and looping through it to exec the function name seems very kludgy.

Is there a better way to approach this problem?

Context:  Currently, the legacy version of this application stores ad hoc SQL in typeFunctions and execs the SQL, looping through the accounts with a cursor. Obviously, this has some performance implications and is not optimal when dealing with complex calculations.

Any time you use a scaler function you have to run the function once for each row.  Complex CASE statements will usually be faster for SQL to process, even though there are painful to read.


Configuring Oracle 11gR2 RAC on Virtual Machine I am working in a small company and interested to understand the RAC configuration.
I have read many documents.. I found that we need three IPs for each node on a RAC. Public IP , Private IP , and Virtual IP..

Now As, I know that sytem Admin Team will provide public IP from  for each node, but what about private and virtiual ip? who will provide private and virtual IP?
OR  dba  can create private and virtual ip from public IP.

Plz clarify!

You need 2 network interfaces, 1 for public network, 1 for private network. (1 is also enough with seperate VLAN support, but it is not common and goes against the design.)
The public IP is to be set on the public interface, and the private IP is to be set on the private interface. These 2 addresses are managed at operating system level and must be set before installing Oracle Grid Infrastructure.

Then you have a virtual IP, at least 1 for each node. This address will use the public network interface and it must be in the same subnet as the public IP address. This is managed by Oracle Grid Infrastructure, and this address must not be set at the operating system level or reserved by other systems. While this address logically belongs to a specified node, this address can be used by other nodes as well when a node goes down (vip failover).

Finally we have SCAN from version 11.2, which is a single name that is resolved to at least 1 IP address (but typically 3 addresses in the documentation and other examples) from the subnet of public network. It does not belong to any node, it belongs to the cluster. This behaves similarly to the above virtual IP.

The above is the minimum, you can have multiple public or private networks and multiple VIP and SCAN addresses over them in newer releases.


Why is mySQL output not procedurally consistent with source code input? I've assumed the order of INPUT statements would be output in the order they were declared and processed in the source code. I need help understanding what I observe in this context and "if possible" or "if feasable" be told how to ensure output is mapped to the order of the source code input when running the following example script...

DROP PROCEDURE IF EXISTS csgRunLoop;
DROP TABLE IF EXISTS testUUID;
CREATE TABLE testUUID (id binary(16) PRIMARY KEY);

BEGIN
DECLARE x, y INT DEFAULT 1;

--  WHILE x  <= 3 DO
--  INSERT INTO testUUID VALUES(UUID_TO_BIN(UUID()));
--  SET  x = x + 1;
--  END WHILE;

-- Move the two INSERT statments below WHILE loop or run here above WHILE loop
-- and the output is not consistent with the order the statements were declared
   INSERT INTO testUUID VALUES(UUID_TO_BIN('d6c03d84-2eed-4f15-b681-90fe627fcacf'));
   INSERT INTO testUUID VALUES(UUID_TO_BIN('d6c03d84-2eed-4f15-b681-90fe627fcacf', true));

   WHILE y  <= 3 DO
   INSERT INTO testUUID VALUES(UUID_TO_BIN(UUID(), true));
   SET  y = y + 1;
   END WHILE;

END$$
DELIMITER ;

CALL csgRunLoop();

-- time shifted
SELECT BIN_TO_UUID(id) FROM testUUID;

-- untime shifted
--SELECT BIN_TO_UUID(id, true) FROM testUUID WHERE id LIKE('%');

Databases have no concept of "order".

This is so that they can handle millions, billions, and trillions of rows of data without worrying about "order".

If you want output in a specific order, you MUST specify an ORDER BY clause.

This means you need a column you can ORDER BY.

On such column could be an Identity column.  Check your RDBMS documentation for any potential out-of-order possibilities. (Eg oracle RAC Sequence)

Another method is to use a CREATED_ONcolumn.  This a date+time column with sub-second resolution.


Listener Status Blocked I'm deploying a 12C dataguard on oracle.
When performing all procedures, start the instance in the standby, as nomount. I noticed that the standby listener is as BLOCKED status, making it impossible for connections in the database to make Duplicate through rman, it does not allow because of the blocked status of the listener.

Can someone help me?

A NOMOUNT instance stays in BLOCKED status with dynamic registration. You need to use static registration to connect a NOMOUNT instance remotely.

Add the below to listener.ora and restart the listener:

SID_LIST_LISTENER=
(SID_LIST=
 (SID_DESC=
  (GLOBAL_DBNAME=ORCL)
  (SID_NAME=ORCL)
  (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
 )
)

Of course, change the values of variables accordingly to your environment.


phpMyAdmin trigger error The following oddly does work, but shows an error in PhpMyAdmin...  (see image below)

(in the definition block as seen in the image)

BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2))
SECOND);
END

END has a red X.  The syntax is "Unrecognized statement type. (near END)

WHAT I HAVE TRIED TO FIX THIS

SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2))
SECOND);

The errors go away in phpMyAdmin but when I click GO it gives a huge error so that's obviously not correct.

I have read people are using define when more than 1 statement is needed so I tried...

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND);
END$$

DELIMITER ;

I get unexpected characters ;
then I tried...

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND)$$
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND)$$
END$$

DELIMITER ;

Which doesn't work and gives me the original error before I tried fixing anything.  So, I don't know what to say.  I am obviously a novice to all this.  I just spent over an hour reading about triggers, mysql docs, but I don't know what I am doing wrong.  I spent another 30 min reading other questions about similar things to see if I can spot the syntax problem I am causing by looking at other peoples examples in how they create triggers in this.   I don't know what I am doing wrong.  :)

Maybe it's valid but it gives an error in PhpMyAdmin incorrectly!!?!??!

Any ideas?  Thank you!

It works... but, maybe it's not supposed to.  I don't know the proper syntax to do this.  Any tips would be appreciated.

I wrote all this because I want to do things properly so I figured I would ask someone.

Thank you for your time.

Instead of editing the existing trigger, try to open a new SQL Console and try this :

DROP TRIGGER IF EXISTS `AuditSetExpiryTokens`;
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `AuditSetExpiryTokens` BEFORE
INSERT ON `token` FOR EACH ROW
BEGIN
 SET NEW.expiry=DATE_ADD(NOW(),INTERVAL NEW.duration SECOND);
 SET NEW.delete_date=DATE_ADD(NOW(),INTERVAL(NEW.duration+(3600*2)) SECOND);
END$$
DELIMITER ;


Storing draft version without any constraint checks If a system allows users to save draft (not versioning but kind of like partial save) of their work in database, I can think of 3 ways.

I see similar questions being asked here already but I could not find any with numbers showing performance of each approach. So just wanted to check if someone has evaluated approaches of this sort and already knows performance of each.

Create duplicate tables: We can have 2 copies of each affected table. One with all constraints and one without. Drafts go into one without checks and final version (with all mandatory data) goes in to another. Application code will then look into both tables and do whatever they do with it. This violates DRI and also creates 2 entities on UI for same thing. Plus they are querying 2 tables where they should ideally be querying one.
Same approach as #1 but create a view which shows all records together. Here, application code will only deal with view. This will again violate DRI and also create an overhead of recreation of views at every insert/update.
Have check constraints on all mandatory table along with a flag to tell if this is partial or complete save. This makes life a bit easy but might cause performance impact because check constraint will be evaluated all the time.

This is for a rather small database with 50 odd tables where biggest one would have about 15 columns. There are few tables which also save blob (~500 KB max).

Any suggestions (apart from trying it out myself, of course)?

It will depend in part on how long a draft should live:

Should it disappear if the user logs out or if user session timeout occurs? Then it would make sense to keep it in memory, e.g. in a Session variable (esp. for Web Apps that often already have support for this).
Should it be kept 'forever' until processing is resumed, e.g. an e-mail draft? Then by all means make it persist in the database.

If you choose to persist in the database then personally I would put it in the regular tables, with a Draft/Final flag on the main record. And think about proper cleanup code (perhaps Final records can't be removed, but Drafts can).

Also consider not enforcing all possible rules on it inside the database, instead I would do so using business logic in the Application layer, which can often be done in more flexible ways than what you can (or want to) express in database constraints.


Stored procedure for selecting table rows from a date range used to populate two other tables I have this two tables shipments_docs and dict_shops:

CREATE TABLE shipment_docs (
  doc_id INT PRIMARY KEY,
  doc_num VARCHAR(20) NOT NULL,

  doc_date DATETIME NOT NULL,

  shop_id INT NOT NULL

);
GO
CREATE TABLE dict_shops (

  shop_id INT PRIMARY KEY,

  shop_name VARCHAR(30) NOT NULL,

  town_id INT NULL,

  shop_address VARCHAR(100) NOT NULL

);
GO

And I need to populate them with values from the third table (dbo.shipment_data) that fall into the date range.

CREATE TABLE dbo.shipment_data (

  doc_num VARCHAR(20) NOT NULL,    -- номер документа

  doc_date DATETIME NOT NULL,      -- дата документа

  shop_name VARCHAR(30) NOT NULL,  -- наименование торговой точки

  shop_address VARCHAR(100) NOT NULL  -- почтовый адрес торговой точки

);

I need to implement stored procedure to populate the shipment_docs and dict_shops tables. The procedure should take two parameters - the start date and the end date - which should limit the loaded data set by the doc_date field.

CREATE PROCEDURE FillTables
@DateFrom DATETIME,
@DateTo DATETIME,
AS
BEGIN
DECLARE @flag INT;
SET @flag = 1;
WITH theDates AS
        (SELECT @DateFrom as theDate
         UNION ALL
         SELECT DATEADD(day, 1, theDate)
           FROM theDates
          WHERE theDate < @DateTo
        )
..........

After reading Google, I realized that in this case it is necessary to use the wif construction. But I can’t understand what the whole query should look like.

I'm not sure why you need a recursive CTE. If you just need to split the data into two tables based on a date range you can do the following:

CREATE PROCEDURE FillTables
(
    @dateFrom DATETIME,
    @dateTo DATETIME
)
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRANSACTION;
BEGIN TRY

    -- GET ONLY THE DATA WE NEED AND CACHE IT
    SELECT doc_num, doc_date, shop_name, shop_address
      INTO #results
      FROM dbo.shipment_data
     WHERE doc_date BETWEEN @dateFrom AND @dateTo;

    -- ADD THE SHOPS TO GENERATE THE ID's
    -- (COULD USE OUTPUT HERE AS AN ALTERNATIVE)
    INSERT dict_shops (shop_name, shop_address)
    SELECT DISTINCT shop_name, shop_address
      FROM #results
    EXCEPT
    SELECT DISTINCT shop_name, shop_address
      FROM dict_shops;

    -- JOIN ON THE NEWLY CREATED SHOPS TO POPULATE SHIPMENT DOCS
    INSERT shipment_docs (doc_num, doc_date, shop_id)
    SELECT DISTINCT r.doc_num, r.doc_date, s.shop_id
      FROM #results AS r
      JOIN dict_shops AS s
        ON s.shop_name = r.shop_name
       AND s.shop_address = r.shop_address;

COMMIT;
END TRY
BEGIN CATCH

     IF @@TRANCOUNT > 0
        ROLLBACK;
     THROW;

END CATCH

END


SQL Server prevents me from changing compatibility level I have a SQL Server DB that is in a SQL Server 2008 Express instance that will not let me change the compatibility level beyond SQL Server 2005 (It was originally created in 2005) either through the GUI or T-SQL. If you view the options in the GUI (Right Click on DB, Select Properties, Select "Options" and then look at the "Compatibility level:", it shows options for only past versions 7(70), 2000 (80), and 2005 (90).

This is in production and I cannot simply drop and recreate it as it is constantly in use. When I backed up and restored it to another SQL Server instance (2014) all compatibility levels were available, but that's not an option without taking down all VM's that are tied to it. If all else fails, yes that is an option, but one that brings no joy.

When I run :

     ALTER DATABASE [VIM_VCDB] SET  COMPATIBILITY_LEVEL =  100

I get the following:

     Msg 102, Level 15, State 1, Line 1
     Incorrect syntax near '100'.

This is true for all system DB's on this box also... How to I raise the compatibility level for this? Is it a bad install of the SQL Server 2008 Express?

You have SQL Server 2005 installed and not 2008. You'll need to install 2008 or newer and migrate your databases over to the new instance.


Verify if the client is connecting to the Oracle database using native network encryption My environment is as below -

Server: Oracle 12C 12.1.0.2.v17 ( AWS RDS Service)

Client: Windows 2012 machine with Oracle 19C 64 bit full client

Tools : SQL Developer and Toad on the client machine

I have configured native network encryption for the RDS service by following the instructions in https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/use-network-encryption-and-integrity.html

I set following options on the RDS -

SQLNET.ENCRYPTION_SERVER= Accepted

SQLNET.ENCRYPTION_TYPES_SERVER= AES256

The client Oracle 19c 64 bit home is
C:\oracle\product\19.0.0\client_1\network\admin\sqlnet.ora

Both client tools using the TNS name in the above path; I verified this by using  tnsping

The sqlnet.ora in client has following options -

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

After the above configurations, I restarted my windows machine(just to make sure) and am able to establish connection to the RDS with Toad and SQL Developer tool.

MY question: How do I verify if my connection is encrypted ?

I tried executing below query -

select NETWORK_SERVICE_BANNER
from v$session_connect_info
where SID = sys_context('USERENV','SID');

The output I am seeing is

TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production

Encryption service for Linux: Version 12.1.0.2.0 - Production

Crypto-checksumming service for Linux: Version 12.1.0.2.0 - Production

SHA1 Crypto-checksumming service adapter for Linux: Version 12.1.0.2.0 - Production

I do not see "AES256 Encryption" in the NETWORK_SERVICE_BANNER output.

Related question: I need all the client connections from this machine to my server use encryption. Is there a way I can enforce this by using logon trigger ?

You're confused with my comments,with accepted on server side and required client side sqlplus connection is encrypted and Sql Developer with thin client  is unencrypted but the same Sql Developer with thick client connection is encrypted.(Toad is out of scope I never used at all).In other words accepted on server side is working as expected.
Let me demonstrate for you
 sqlnet.ora on server

    $ cat $TNS_ADMIN/sqlnet.ora
    # sqlnet.ora Network Configuration File: /u01/app/oracle/product/19/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.

    NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

    SQLNET.ENCRYPTION_SERVER = accepted

    SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)

    SQLNET.CRYPTO_CHECKSUM_SERVER = required

    [CDB2] oracle@hol:~

   Client side Window 10 Pro

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.3.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

#SQLNET.ENCRYPTION_CLIENT=REQUIRED   -- commented out
#SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256) -- commented out

    SQL> @network_encryption

NETWORK_SERVICE_BANNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production

Now I'll uncomment last two lines in sqlnet.ora client side
# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.3.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

ADR_BASE = C:\app\oracle\product\19.3.0\db_1\log

SQL> @network_encryption

NETWORK_SERVICE_BANNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production
SHA1 Crypto-checksumming service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production.

Now let's test with Sql Developer thin client

It's evident from above image it uses jdbc thin driver.Let's check the sql out put in Sql Developer and as expected there is no AES256  encryption

Now I will switch to thick client,check both images

Hope this clears up


How to remotely manage (with PhpMyAdmin) a MySQL RDS on a load-balanced AWS Elastic Beanstalk setup? Although there are similar questions and topics, I can't find a solution that currently works.

I specifically wrote remotely manage because I'd like to use my home computer's PhpMyAdmin to directly connect to the RDS instance and keep its 'public accessibility' to private.

What I've done so far:

installed PhpMyAdmin on my home computer (mac osx) and got it working
added an inbound rule for the security group used by the RDS (rule: my.home.network's.ipv4/32, port 3306)
configured my mac's phpmyadmin conf.inc.php according to this suggestion. I've never done SSH tunneling, but the idea seems safer than opening 3306 to the public. So I've set it up accordingly, but these are the PhpMyAdmin errors I get:

Cannot log in to the MySQL server
mysqli_real_connect(): MySQL server has gone away
mysqli_real_connect(): Error while reading greeting packet. PID=xxxx
mysqli_real_connect(): (HY000/2006): MySQL server has gone away

Any ideas what I'm missing/doing wrong? Also, do I need to have MySQL at all installed on the mac? I assumed I didn't need it since I'd be using the remote one, but I've installed it anyway, and tried both with and without it. Same errors.

Thank you!

Ok, so I finally figured it out. I was close. It turns out I was doing the SSH tunneling wrong, and that's because I had no idea what I was doing. So after a bit more research I realized that I needed to change the remote host to be the RDS, not the EC-2.

So I ended up running the ssh command like this:

ssh -L 3306:my-rds-endpoint:3306 ec2-user@my-ec2-endpoint -i /path/to/aws-eb/key

Or add -fN if you want to run it in the background:

ssh -fNL 3306:my-rds-endpoint:3306 ec2-user@my-ec2-endpoint -i /path/to/aws-eb/key

The config.inc.php ended up being configured like so:

$cfg['Servers'][$i]['host']          = '127.0.0.1';
$cfg['Servers'][$i]['port']          = '3306';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysqli';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'cookie';

So yeah, my issue was understanding the ssh command, and realizing that I needed to change it so it would tunnel to the RDS endpoint through the EC2 instance. Also, I had some weird issue with my mac's local MySQL still running in the background, so it wouldn't allow a new connection to the 3306 port. Once I ran mysqladmin -u root -p shutdown PhpMyAdmin could finally connect to the RDS!


Data truncation: Incorrect datetime value: '7/21/15' for column 'announced_on' at row 1 I am importing a flat file into a table in DBeaver v7.0.2. When I attempt to import, it's throwing an error for date field formatted as mm/dd/yy.

The date column in the table is defined with datatype DATE.

I receive the following error:

Data truncation: Incorrect datetime value: '7/21/15' for column 'announced_on' at row 1

Any ideas on why it's complaining about the date format? It appears that I do not need to explicitly specify date format in the CREATE TABLE statement.

the normal date format is 2020-04-19.

If you try to insert 7/21/15 into a date field this will fail as you noticed.

Most imports allow to reformat input.

In mysql you would use STR_TO_DATE('7/21/15', '%m/%e/%y') to import data.

If you don't want to rewrite or need the dates that way , you could define the field as varchar(10) that is big enough to hold it.


How can I in Oracle discover the average number of rows affected per transaction? Our Oracle server is a big cluster of three very large individual instances. I want to try to understand the average number of rows per transaction. How can I go about doing this?

Not exactly an answer to your question but amount of changes generated by the session can be found using such query.

select st.inst_id, trunc(st.value/(1024*1024)) as MB, ss.sid, ss.username,
  ss.osuser, ss.machine, ss.program, ss.logon_time
from gv$sesstat st, v$statname sn, gv$session ss
where st.statistic# = sn.STATISTIC# and sn.name = 'redo size'
  and st.inst_id = ss.inst_id and st.sid = ss.sid and ss.type != 'BACKGROUND'
order by 2 desc;

Number of rows changed correlates with amount of redo generated.


Alter column fails due to "hidden" statistic I'm trying to change a column data type from datetime2 to datetimeoffset. I have db owner permissions.

Whenever I run the query:

alter table dbo.<my-table>
alter column [TimeStamp] datetimeoffset

I get the following error message:

  The statistics 'Stat_a41cbd7f968a4cba86199c960f6f7c3a' is dependent on column 'TimeStamp'.

  ALTER TABLE ALTER COLUMN TimeStamp failed because one or more objects access this column.

However, it looks like this statistic 'Stat_a41cbd7f968a4cba86199c960f6f7c3a' is not present in my sys.stats table, which is very odd.

Is there any other location it might be defined?

Edit: There isn't a single entry in sys.stats named 'Stat_a41cbd7f968a4cba86199c960f6f7c3a'. In addition, I have never defined any custom statistics other than the automatic ones.

After reaching out to Azure support, I have managed to solve the issue.
Part of the support engineer's answer included the following:

I've reviewed internal telemetry and can confirm that the statistic listed in the error does not exist. This is likely a compute-node-level statistic that wasn't cleaned up properly.

I was able to confirm and fix this issue by doing the following steps:

Run a CTAS to create a copy of the table
Rename the previous table to something different (eg: ) and the new one to the original table name
DROP the old table

After that, the ALTER TABLE statement worked as expected.


How to close all query windows in DBeaver? DBeaver retains all results & sql editor windows when the program is closed.

The indicator showing tab overflow is easy to overlook (see pic). A user can accumulate an absurd number of tabs if they aren't paying attention & actively closing tabs after each session. I don't see an obvious way to do it and it's impractical to close them one by one.

How can I close all query windows in DBeaver?

I mean 'close' as in save nothing and close the query window completely.

You can right-click on any tab then choose "Close All":

In order to automatically close database object editors when exiting the application, you can uncheck this option: "Reopen database editors after restart":

Note that this will only close database editors (tables, schemas...) and it won't affect text editors. There's currently (DBeaver 5.1.2) no option to automatically close text editors when exiting the application. The feature request is tracked on this issue.


Sqlplus not using NLS_LANG env variable I am trying to login to an Oracle 10g database vía sqlplus.

In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish.

In the database server my NLS_LANG is SPANISH_SPAIN.WE8ISO8859P1.

In the client my NLS_LANG is AMERICAN_AMERICA.UTF8.

I tried setting this with an after logon trigger, but it does not work.
If I change this setting vía ALTER SESSION it works ok, but it is not a solution.

The database server is a Red Hat Enterprise 5.6
The client is a CentOS 7.5.
I am using Oracle Instant client 10.2.0.5.

I do not know how to continue...

I am presuming you are setting it to AMERICAN_AMERICA.* with your ALTER SESSION.

In my experience the language setting has to match between the client, server and nls_database_parameters.  It might be a big mission for your DBA to convert your character set to AMERICAN_AMERICA.UTF8 using the Database Migration tool, so would it be possible to make your change to NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1 on the server?

Another problem is that WE8ISO8859P1 is a subset of UTF8 so when your RDBMS does its single-pass (or more) conversions when transporting data between the layers, you can get lossy results and see strange characters returning to your client.  So you would be better off to set your client and server locale to WE8ISO8859P1 to match your nls_database_parameters on the database.


How to implement a Set based algorithm/UDF I have an algorithm that I need to run against every row in a table with 800K rows and 38 columns.
The algorithm is implemented in VBA and does a bunch of math using values from some columns to manipulate other columns.

I'm currently using Excel (ADO) to query SQL and use VBA with client side cursors to apply the algorithm by loop through every row. It works but takes 7 hours to run.

The VBA code is complex enough that it would be a lot of work to recode it into T-SQL.

I've read about CLR integration and UDFs as possible routes. I also thought about putting the VBA code in an SSIS script task to get closer to the database but am sure an expert methodology to this type of performance problem exists.

Ideally I'd be able to run the algorithm against as many rows (all?) as possible in a parallel set based way.

Any help greatly predicated on how to get best performance with this type of problem.

--Edit

Thanks for the comments, I'm using MS SQL 2014 Enterprise, here's some more details:

The algorithm finds characteristic patterns in time series data. The functions within the algorithm perform polynomial smoothing, windowing, and finds regions of interest based on input criteria, returning a dozen values and some Boolean results.

My question is more about methodology than the actual algorithm: If I want to achieve parallel computation on many rows at once, what are my options.

I see re-code into T-SQL is recommended which is a lot of work but possible, however the algorithm developer works in VBA and it changes frequently so I'd need keep in sync with the T-SQL version and re-validate every change.

Is T-SQL the only way to implement set based functions?

With regards to methodology, I believe you are barking up the wrong b-tree ;-).

What we know:

First, let's consolidate and review what we know about the situation:

Somewhat complex calculations need to be performed:

This needs to happen on every row of this table.
The algorithm changes frequently.
The algorithm ... [uses] values from some columns to manipulate other columns
Current processing time is: 7 hours

The table:

contains 800,000 rows.
has 38 columns.

The application back-end:

is Microsoft Excel.
uses VBA (Visual Basic for Applications)** as the language.

Database is SQL Server 2014, Enterprise Edition.
There is a Stored Procedure that is called for every row:

This takes 50 ms (on avg, I assume) to run.
It returns approximately 4000 rows.
The definition (at least in part) is:

SELECT AVG([AD_Sensor_Data])
           OVER (ORDER BY [RowID] ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING)
           as 'AD_Sensor_Data'
FROM   [AD_Points]
WHERE  [FileID] = @FileID
ORDER BY [RowID] ASC

What we can surmise:

Next, we can look at all of these data points together to see if we can synthesize additional details that will help us find one or more bottle necks, and either point towards a solution, or at least rule some possible solutions out.

The current direction of thought in the comments is that the major issue is data transfer between SQL Server and Excel. Is that really the case? If the Stored Procedure is called for each of the 800,000 rows and takes 50 ms per each call (i.e. per each row), that adds up to 40,000 seconds (not ms). And that is equivalent to 666 minutes (hhmm ;-), or just over 11 hours. Yet the whole process was said to take only 7 hours to run. We are already 4 hours over the total time, and we have even added in time to do the calculations or save the results back to SQL Server. So something is not right here.

Looking at the definition of the Stored Procedure, there is only an input parameter for @FileID; there isn't any filter on @RowID. So I suspect that one of the following two scenarios is happening:

This Stored Procedure does not actually get called per each row, but instead per each @FileID, which appears to span approximately 4000 rows. If the stated 4000 rows returned is a fairly consistent amount, then there are only 200 of those grouping in the 800,000 rows. And 200 executions taking 50 ms each amounts to only 10 seconds out of that 7 hours.
If this stored procedure actually does get called for every row, then wouldn't the first time a new @FileID is passed in take slightly longer to pull new rows into the Buffer Pool, but then the next 3999 executions would typically return faster due to already being cached, right?

I think that focusing on this "filter" Stored Procedure, or any data transfer from SQL Server to Excel, is a red herring.

For the moment, I think the most relevant indicators of lackluster performance are:

There are 800,000 rows
The operation works on one row at a time
The data is being saved back to SQL Server, hence "[uses] values from some columns to manipulate other columns" [ my emphasis ;-) ]

I suspect that:

while there is some room for improvement on the data retrieval and calculations, making those better wouldn't amount to a significant reduction in processing time.
the major bottleneck is issuing 800,000 separate UPDATE statements, which is 800,000 separate transactions.

My recommendation (based on currently available information):

Your biggest area of improvement would be to update multiple rows at one time (i.e. in one transaction). You should update your process to work in terms of each FileID instead of each RowID. So:

read in all 4000 rows of a particular FileID into an array
the array should contain elements representing the fields being manipulated
cycle through the array, processing each row as you currently do
once all rows in the array (i.e. for this particular FileID) have been calculated:

start a transaction
call each update per each RowID
if no errors, commit the transaction
if an error occurred, rollback and handle appropriately

If your clustered index isn't already defined as (FileID, RowID) then you should consider that (as @MikaelEriksson suggested in a comment on the Question). It won't help these singleton UPDATEs, but it would at least slightly improve the aggregate operations, such as what you are doing in that "filter" stored procedure since they are all based on FileID.
You should consider moving the logic to a compiled language. I would suggest creating a .NET WinForms app or even Console App. I prefer Console App as it is easy to schedule via SQL Agent or Windows Scheduled Tasks. It shouldn't matter whether it is done in VB.NET or C#. VB.NET might be a more natural fit for your developer, but there will still be some learning curve.

I don't see any reason at this point to move to SQLCLR. If the algorithm changes frequently, that would get annoying have to re-deploy the Assembly all of the time. Rebuilding a Console App and having the .exe get placed into the proper shared folder on the network such that you just run the same program and it just happens to always be up-to-date, should be fairly easy to do.

I don't think moving the processing fully into T-SQL would help if the problem is what I suspect and you are just doing one UPDATE at a time.
If the processing is moved into .NET, you can then make use of Table-Valued Parameters (TVPs) such that you would pass the array into a Stored Procedure that would call an UPDATE that JOINs to the TVP table variable and is hence a single transaction. The TVP should be faster than doing 4000 INSERTs grouped into a single transaction. But the gain coming from using TVPs over 4000 INSERTs in 1 transaction likely won't be as significant as the improvement seen when moving from 800,000 separate transactions to only 200 transactions of 4000 rows each.

The TVP option is not natively available for the VBA side, but someone came up with a work-around that might be worth testing:

How do I improve the database performance when going from VBA to SQL Server 2008 R2?
IF the filter proc is only using FileID in the WHERE clause, and IF that proc is really being called per every row, then you can save some processing time by caching the results of the first run and using them for the rest of the rows per that FileID, right?
Once you get the processing done per FileID, then we can start talking about parallel processing. But that might not be necessary at that point :). Given that you are dealing with 3 fairly major non-ideal parts: Excel, VBA, and 800k transactions, any talk of SSIS, or parallelograms, or who-knows-what, is premature optimization / cart-before-the-horse type stuff. If we can get this 7 hour process down to 10 minutes or less, would you still be thinking of additional ways to make it faster? Is there a target completion time that you have in mind? Keep in mind that once processing is done on a per FileID basis, if you had a VB.NET Console App (i.e. command-line .EXE), there would be nothing stopping you from running a few of those FileIDs at a time :), whether via SQL Agent CmdExec step or Windows Scheduled Tasks, etc.

AND, you can always take a "phased" approach and make a few improvements at a time. Such as starting with doing the updates per FileID and hence using one transaction for that group. Then, see if you can get the TVP working. Then see about taking that code and moving it to VB.NET (and TVPs work in .NET so it will port nicely).

What we do not know that could still help:

Does the "filter" Stored Procedure run per RowID or per FileID? Do we even have the full definition of that Stored Procedure?
Full schema of the table. How wide is this table? How many variable length fields are there? How many fields are NULLable? If any are NULLable, how many contain NULLs?
Indexes for this table. Is it partitioned? Is either ROW or PAGE Compression being used?
How large is this table in terms of MB / GB?
How is index maintenance handled for this table? How fragmented are the indexes? How update to date are the statistics?
Do any other processes write to this table while this 7 hour process is taking place? Possible source of contention.
Do any other processes read from this table while this 7 hour process is taking place? Possible source of contention.

UPDATE 1:

** There seems to be some confusion about what VBA (Visual Basic for Applications) and what can be done with it, so this is just to make sure we are all on the same web-page:

VBA is not Visual Basic (stand-alone language), nor is it VBScript (works in IIS and Windows Scripting Host). VBA is a reduced form of VB that is specific to Microsoft Office products, to extend their capabilities. Hence, there is no real way to simply "remove" the VBA code from Excel in order to get Excel out of the picture.
Can VBA Code Be Run Outside of MS Applications?
Run VBA outside of Office applications
Run Excel Macro from Outside Excel Using VBScript From Command Line

UPDATE 2:

One more point to consider: How are connections being handled? Is the VBA code opening and closing the Connection per each operation, or does it open the connection at the start of the process and close it at the end of the process (i.e. 7 hours later)? Even with connection pooling (which, by default, should be enabled for ADO), there should still be quite an impact between opening and closing once as opposed to opening and closing either 800,200 or 1,600,000 times. Those values are based on at least 800,000 UPDATEs plus either 200 or 800k EXECs (depending on how often the filter stored procedure is actually being executed).

This issue of too many connections is automatically mitigated by the recommendation I outlined above. By creating a transaction and doing all of the UPDATEs within that transaction, you are going to be keeping that connection open and reusing it for each UPDATE. Whether or not the connection is kept open from the initial call to get the 4000 rows per the specified FileID, or closed after that "get" operation and opened again for the UPDATEs, is far less impacting since we are now talking about a difference of either 200 or 400 total connections across the entire process.

UPDATE 3:

I did some quick testing. Please keep in mind that this is a rather small scale test, and not the exact same operation (pure INSERT vs EXEC + UPDATE). However, the differences in timing related to how connections and transactions are handled are still relevant, hence the information can be extrapolated to having a relatively similar impact here.

Test Parameters:

SQL Server 2012 Developer Edition (64-bit), SP2
Table:

 CREATE TABLE dbo.ManyInserts
 (
    RowID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    InsertTime DATETIME NOT NULL DEFAULT (GETDATE()),
    SomeValue BIGINT NULL
 );

Operation:

INSERT INTO dbo.ManyInserts (SomeValue) VALUES ({LoopIndex * 12});

Total Inserts per each test: 10,000
Resets per each test: TRUNCATE TABLE dbo.ManyInserts; (given the nature of this test, doing the FREEPROCCACHE, FREESYSTEMCACHE, and DROPCLEANBUFFERS didn't seem to add much value.)
Recovery Model: SIMPLE (and maybe 1 GB free in the Log file)
Tests that use Transactions only use a single Connection regardless of how many Transactions.

Results:

Test                                   Milliseconds
-------                                ------------
10k INSERTs across 10k Connections     3968 - 4163
10k INSERTs across 1 Connection        3466 - 3654
10k INSERTs across 1 Transaction       1074 - 1086
10k INSERTs across 10 Transactions     1095 - 1169

As you can see, even if the ADO connection to the DB is already being shared across all operations, grouping them into batches using an explicit transaction (the ADO object should be able to handle this) is guaranteed to significantly (i.e. over 2x improvement) reduce the overall process time.


Clean up space used by moveChunk in file system I have a directory in my local file system where databases are stored called moveChunk which I understand is used by MongoDB for shard movement of chunks.  This is taking up around 206GB and I can't find any information on MongoDB site for cleaning this.  Some of the data is from earlier in the year.  Is there a resource to determine if this is safe to clean?  Or is there a process within MongoDB that cleans this?

As per the MongoDB official documentation:

Is it safe to remove old files in the moveChunk directory?

  Yes. mongod creates these files as backups during normal shard
  balancing operations. If some error occurs during a migration, these
  files may be helpful in recovering documents affected during the
  migration.

  Once the migration has completed successfully and there is no need to
  recover documents from these files, you may safely delete these files.
  Or, if you have an existing backup of the database that you can use
  for recovery, you may also delete these files after migration.

  To determine if all migrations are complete, run
  sh.isBalancerRunning() while connected to a mongos instance.


Most efficient way to compare strings in SQL Server 2014? Hope everything is going ok.
So I'm in an awful situation. I have these two tables, and I need to check that there are not duplicated people. The duplicates are shown to the user in the front end:

Table 1: Id, SSN, First Name, Last Name, Birth Date, Age Gender, Address (yeah, this includes everything: street, cp, city), Email, Cellphone.
Table 2: SSN, First Name, Last Name, Gender, State (its a tinyint) and other data that isn't relevant (a bunch of datetimes)

So, the problem is that the data from table 1 was sent to us in a csv. But this csv wasn't generated by a computer, someone just sat and typed around 4,000 records. So it's likely that there are errors in the NSS. While false positives do not matter that much (there will be an extra page where the user can double check if those two people are exactly the same), it is really important that the system doesn't throw false negatives.
Another problem is that the data from second table will be captured by external users (a bunch of people that are not part of my organization), and I have to consider that they can have typos as well (in the SSN, capitalization, perhaps event accents that the ones who captured the data on the first table didn't include). Both datasets aren't reliable. Yet, the less reliable is the first one, at least we are building the page where users will enter the data that'll fill the second table and we can place some validation to make sure they don't include extra whitespaces or things like that.
Currently, im comparing like this:
SELECT NSS, CONCAT_WS(' ', FirstName, LastName) AS Name, Email, Cellphone
 FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON (T1.SSN=T2.SSN
ON (TRIM(T1.FirstName) COLLATE Latin1_General_AI_CI LIKE '%'+TRIM(T2.FirstName)+'%'  COLLATE Latin1_General_AI_CI
AND TRIM(T1.LastName) COLLATE Latin1_General_AI_CI LIKE '%'+TRIM(T2.LastName)+'%'  COLLATE Latin1_General_AI_CI))

While it gets the job done, the performance is terrible (leading wildcards). It takes up to 4 minutes to give the user the result (the users of that page are people of my organization, so in the worst case scenario I can simply tell them that the results are going to take a while whenever they want to check it, it'll be slower if they compared it manually anyway). So I'm trying to find a faster way to do it which doesn't sacrifice it's reliability. Sadly, asking for a second and cleaner csv is out of the question (lots of papers and time that we don't have).
The first table haves like 4,000 entries. For the second one, we are expecting it to have between 2,500 and 3,000 records at the end of the registration.
Thanks for your time! Take care!

So a couple things:

You are collating both sides of the predicate comparison which can affect your query's cardinality estimate which can severely affect performance. It looks like you're doing this to ensure a case insensitive comparison. You're better off storing the data in tables or a database with that collation specified instead, so you can eliminate the need to do it in your JOIN clause.

Your should insert the data into the table already trimmed to eliminate a function call from your predicates as well, that can also affect the cardinality estimates.

Ensure you have an index on (LastName, FirstName). While it's unlikely to be seeked on in your query (because you're doing a wildcard contains search) even an index scan on those fields might be more performant than nothing.

*4. If you're ok with loosening your comparison logic a little bit (at the risk of the JOIN not matching as many cases) you can do a starts with comparison, and a starts with comparison on the reversed strings of the FirstName and LastName fields instead of the contains search. This will be more efficient because starts with comparisons are SARGable whereas ends width and contains searches are not SARGable, meaning the index will be seekable. If you implement your logic this way that means your predicates would match on "John" and "Johnson", or "Smith" and "Blacksmith" but would not match on "lack" and "Blacksmith".


SQL Server 2016 -number of SQL agent replication jobs(transaction log repl) So I have a SQL server 2016 on a Windows Server 2012 instance. We have 140 databases(small to med). So the SQL instance was setup for self replication and in the process we have 140 SQL agent replication jobs. When the server reboots it can handle ~120 replication jobs, then when one is stopped it can not be re-started - it fails - but with no message at all - then says "between retries". In replication monitor - the agent has the red x and or is showing not running. In the event viewer I see logread.exe application crash. I was trying to research to see if there is a cap on the number of agent jobs running. Our memory is good, the disks and CPU usage is low. I have not been part of something of this size before and I am currently out of ideas. My current work around is to have a PowerShell script read the current failures and manually kick off the -Publisher [xxx] -PublisherDB [DbName] -Distributor [xxx] -DistributorSecurityMode 1 -Continuous  . That is what makes me think there is a cap on the number of agents because I can run it with out the SQL-Agent. I was thinking maybe I need to change that to more of a scheduler but we have reports run that need timely info. What am I missing? Or am I right in looking to breaking up the job runs?

Weird. Never had that many agent jobs running replication before. Do you have a dedicated distributor, or is it all running form the publisher?


How to get latest 2 records of each group So, I have table similar to:

sn color value
1  red   4
2  red   8
3  green 5
4  red   2
5  green 4
6  green 3

Now I need the latest 2 rows for each color, eg:

2  red   8
4  red   2
5  green 4
6  green 3

How to do it, other than using separate query for each color?

Thanks

With MySQL 8

SELECT sn, color, value
FROM (
  SELECT
    sn,
    color,
    value,
    DENSE_RANK() OVER (PARTITION BY color ORDER BY sn) AS r
  FROM table
) AS t
WHERE t.r <= 2;

Using MySQL < 8

You need to use their special variables.. something like this