Store millions of rows of denomalized data or some SQL magic?

You can do much with window functions. Presenting two solutions: one with and one without materialized view.

Test case

Building on this table:

CREATE TABLE hotel_data (
   hotel_id int
 , day      date  -- using "day", not "date"
 , spaces   int
 , price    int
 , PRIMARY KEY (hotel_id, day)  -- provides essential index automatically
);

Days per hotel_id must be unique (enforced by PK here), or the rest is invalid.

Multicolumn index for base table:

CREATE INDEX mv_hotel_mult_idx ON mv_hotel (day, hotel_id);

Note the reversed order as compared to the PK. You will probably need both indexes, for the following query, the 2nd index is essential. Detailed explanation:

Is a composite index also good for queries on the first field?
Working of indexes in PostgreSQL

Direct query without MATERIALIZED VIEW

SELECT hotel_id, day, sum_price
FROM  (
   SELECT hotel_id, day, price, spaces
        , sum(price)      OVER w * 2   AS sum_price
        , min(spaces)     OVER w       AS min_spaces
        , last_value(day) OVER w - day AS day_diff
        , count(*)        OVER w       AS day_ct
   FROM   hotel_data
   WHERE  day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
   AND    spaces >= 2
   WINDOW w AS (PARTITION BY hotel_id ORDER BY day
                ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to nights - 1
   ) sub
WHERE  day_ct = 4
AND    day_diff = 3  -- make sure there is not gap
AND    min_spaces >= 2
ORDER  BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;

Also see @ypercube's variant with lag(), which can replace day_ct and day_diff with a single check.

How?

In the subquery, only consider days within your time frame ("in January" means, the last day is included in the time frame).
The frame for the window functions spans the current row plus the next num_nights - 1 (4 - 1 = 3) rows (days). Calculate the difference in days , the count of rows and the minimum of spaces to make sure the range is long enough, gapless and always has enough spaces.

Unfortunately, the frame clause of window functions does not accept dynamic values, so ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING` cannot be parameterized for a prepared statement.

I carefully drafted all window functions in the subquery to reuse the same window, using a single sort step.
The resulting price sum_price is already multiplied by the number of spaces requested.

With MATERIALIZED VIEW

To avoid inspecting many rows without chance of success, save only the columns you need plus three redundant, calculated values from the base table. Be sure the MV is up to date. If you are not familiar with the concept, read the manual first.

CREATE MATERIALIZED VIEW mv_hotel AS
SELECT hotel_id, day
     , first_value(day) OVER (w ORDER BY day) AS range_start
     , price, spaces
     ,(count(*)    OVER w)::int2 AS range_len
     ,(max(spaces) OVER w)::int2 AS max_spaces

FROM  (
   SELECT *
        , day - row_number() OVER (PARTITION BY hotel_id ORDER BY day)::int AS grp
   FROM   hotel_data
   ) sub1
WINDOW w AS (PARTITION BY hotel_id, grp);

range_start stores the first day of each continuous range for two purposes:

to mark a set of rows as members of a common range
to show the start of the range for possible other purposes.

range_len is the number of days in the gapless range.
max_spaces is the maximum of open spaces in the range.

Both columns are used to exclude impossible rows from the query immediately.

I cast both to smallint ( max. 32768 should be plenty for both) to optimize storage: only 52 bytes per row (incl. heap tuple header and item identifier). Details:

Configuring PostgreSQL for read performance

Multicolumn index for MV:

CREATE INDEX mv_hotel_mult_idx ON mv_hotel (range_len, max_spaces, day);

Query based on MV

SELECT hotel_id, day, sum_price
FROM  (
   SELECT hotel_id, day, price, spaces
        , sum(price)      OVER w * 2   AS sum_price
        , min(spaces)     OVER w       AS min_spaces
        , count(*)        OVER w       AS day_ct
   FROM   mv_hotel
   WHERE  day BETWEEN '2014-01-01'::date AND '2014-01-31'::date
   AND    range_len >= 4   -- exclude impossible rows
   AND    max_spaces >= 2  -- exclude impossible rows
   WINDOW w AS (PARTITION BY hotel_id, range_start ORDER BY day
                ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) -- adapt to $nights - 1
   ) sub
WHERE  day_ct = 4
AND    min_spaces >= 2
ORDER  BY sum_price, hotel_id, day;
-- LIMIT 1 to get only 1 winner;

This is faster than the query on the table because more rows can be eliminated immediately. Again, the index is essential. Since partitions are gapless here, checking day_ct is enough.

SQL Fiddle demonstrating both.

Repeated use

If you use it a lot, I would create an SQL function and only pass parameters. Or a PL/pgSQL function with dynamic SQL and EXECUTE to allow adapting the frame clause.

Alternative

Range types with date_range to store continuous ranges in a single row might be an alternative - complicated in your case with potential variations on prices or spaces per day.

Related:

Find available apartments given a date range, a duration, and a list of unavailable dates
Native way of performing this hours of operation query in PostgreSQL


List chronological pageviews without adjacent duplicates in PostgreSQL

SELECT user_id, max(time) AS time, path, count(*)
FROM (
  SELECT *,
    count(is_reset) OVER (PARTITION BY user_id ORDER BY time) AS grp
  FROM (
    SELECT *,
      CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
    FROM pageviews
  ) AS t1
) AS t2
GROUP BY user_id, grp, path
ORDER BY user_id, grp;

First what you need to do is create something you can group by, for this we compute is_resets. For more information about why it's in a CASE, see this question In what case is a count(x or null) needed in Gaps and Islands?

SELECT *,
  CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
FROM pageviews
 user_id | time |    path    | is_reset
---------+------+------------+----------
       1 |    1 | /home      |
       1 |    3 | /profile   |        1
       1 |    4 | /profile   |
       1 |    6 | /home      |        1
       1 |    8 | /profile   |        1
       2 |    2 | /home      |
       2 |    5 | /dashboard |        1
       2 |    7 | /profile   |        1
(8 rows)

Then we count them to create groups.

SELECT *,
  count(is_reset) OVER (PARTITION BY user_id ORDER BY time) AS grp
FROM (
  SELECT *,
    CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
  FROM pageviews
) AS t1
 user_id | time |    path    | is_reset | grp
---------+------+------------+----------+-----
       1 |    1 | /home      |          |   0
       1 |    3 | /profile   |        1 |   1
       1 |    4 | /profile   |          |   1
       1 |    6 | /home      |        1 |   2
       1 |    8 | /profile   |        1 |   3
       2 |    2 | /home      |          |   0
       2 |    5 | /dashboard |        1 |   1
       2 |    7 | /profile   |        1 |   2
(8 rows)

Now we group by the user_id, and grp. We select the max(time) because you didn't specify which time to choose in your question.

SELECT user_id, max(time) AS time, path, count(*)
FROM (
  SELECT *,
    count(is_reset) OVER (PARTITION BY user_id ORDER BY time) AS grp
  FROM (
    SELECT *,
      CASE WHEN path <> lag(path) OVER (PARTITION BY user_id ORDER BY time) THEN 1 END AS is_reset
    FROM pageviews
  ) AS t1
) AS t2
GROUP BY user_id, grp, path
ORDER BY user_id, grp;

 user_id | time |    path    | count
---------+------+------------+-------
       1 |    1 | /home      |     1
       1 |    4 | /profile   |     2
       1 |    6 | /home      |     1
       1 |    8 | /profile   |     1
       2 |    2 | /home      |     1
       2 |    5 | /dashboard |     1
       2 |    7 | /profile   |     1
(7 rows)

  Can that query be simplified (for PG 9.4)? One comment mentions DENSE_RANK and WITHIN GROUP to eliminate subquery but I couldn't figure out how to do that.

This problem can also be solved with dense_rank, as noted. You may want to see my answer to this question for a good write up on how to do that.

Solving “Gaps and Islands” with row_number() and dense_rank()?


How do I escape backslashes and forward slashes in Neo4J Cypher?

Don't use double quotes " for a string, use single quotes ' and use double slash \\ to insert single backward slash \. The \ will escape the character after it (like in C).

MATCH (p:person {id:'1'}), (a:Address {street:'11\\34, Wall street'})RETURN p, a;


FLUSH TABLES stays in the processlist even after execution

I am very iffy when it comes to TRUNCATE TABLE.

FLUSH TABLES actually closes the file handles on all open tables and reopens them with new file handles.

It could be the two commands colliding into one another, especially if there are a lot of open file handles in mysqld.

SUGGESTION #1

Please replace

FLUSH TABLES;
TRUNCATE TABLE tbl_cdrload;
TRUNCATE TABLE tbl_cdrdetails_temp;

with

CREATE TABLE tbl_cdrload_new LIKE tbl_cdrload;
ALTER TABLE tbl_cdrload RENAME tbl_cdrload_old;
ALTER TABLE tbl_cdrload_new RENAME tbl_cdrload;
CREATE TABLE tbl_cdrdetails_temp_new LIKE tbl_cdrdetails_temp;
ALTER TABLE tbl_cdrdetails_temp RENAME tbl_cdrdetails_temp_old;
ALTER TABLE tbl_cdrdetails_temp_new RENAME tbl_cdrdetails_temp;
DROP TABLE tbl_cdrload_old;
DROP TABLE tbl_cdrdetails_temp_old;

SUGGESTION #2

Please add bulk_insert_buffer_size to my.ini

[mysqld]
bulk_insert_buffer_size=256M

and restart mysql

C:\> net stop mysql
C:\> net start mysql

If you cannot restart mysql at this time, please run this command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

UPDATE 2012-07-31 18:29 EDT

As for the Windows service stopping, you will have to look into the MySQL error log. It will reveal what kind of shutdown (or crash scenario) was executed. In the Linux version of mysql, the mysqld process is controlled by a startup script called mysqld_safe. It has an infinite loop that does this:

LOOP

Execute mysqld
When mysqld terminates, get return code
If ReturnCode is 0, Exit Loop
If ReturnCode > 0 and Severity OK, Loop or Terminate
If ReturnCode > 0 and Severity Bad, Terminate

END LOOP

This mechanism does not exist in MySQL for Windows. If the Windows service for MySQL crashes, that's it. You must check the error log.


Implementing something similar to "pointer arrays" with MySQL

You are conceptualizing this from something other than a relational database perspective, but it sounds like what you're looking for is a junction table (which also has several other names, all describing pretty much the same construct).

This structure allows multiple "this" to reference multiple "that" with unlimited flexibility with regard to which or how many of "this" references which or how many of "that"... you create a third table that is conceptually "between" the existing "this" and "that" tables.  The juction/map/link table often has just two columns, "this_id" and "that_id" which combine to make up its primary key... and you create the "pointers" by inserting rows in that third table containing each this_id/that_id pair that reflects the way the records should be related.

You typically then would retrieve the records with a join.


VoltDB has very limited support for triggers in DDL, the way some traditional SQL databases do. You can tell it to run a SQL statement when a table reaches a certain row count, which is often used to truncate older data.

Within stored procedures, there is tremendous support for very flexible logic. You can perform arbitrary database operations when certain conditions are met.

VoltDB does not have an official way to call a C function or executable with data. Perhaps you could expand on what you're trying to accomplish? If this is part of a change-data-capture scenario, you might want to take a look at VoltDB's Export technology, which is a push-based system for moving data to a downstream system in a reliable way.

https://voltdb.com/products/fast-data-ingest-and-export
https://docs.voltdb.com/UsingVoltDB/ChapExport.php


Recommended replication strategy for fast global access to MySQL DB

If you only want read access, then Master-Slave will work nicely.  Anything written to the Master will incur a replication delay getting to Asia, but after that, clients in Asia can get the data with very little delay from the Asia slave.

For fast write access everywhere in the world, Master-Master might be viable.  The long delay should not be a problem since the traffic is low.  There are many potential problems with M-M, mostly having to do with primary/unique keys, but perhaps you can resolve that.

NDB Cluster is another possibility.  However, many things are different with it, and the setup is more complex.  It relies on "eventual consistency" wherein you provide rules for what to do when conflicting data is written to different Masters.

In your current setup...

If the operations you perform involve multiple queries (say, a few selects, then an update), there is a simple speed-up.  Combine the queries into a Stored Procedure and perform one CALL.  This cuts the trans-oceanic hops down from "multiple" to "one".  I suspect the ping time from Asia to Europe is about 200ms?  That is how much additional latency for each SQL statement.


Specify the path for every database file (including ldf file) and SQL Server will not look for the original path. If the GUI does something strange, then use the CREATE DATABASE ... FOR ATTACH directly (examples in the product documentation, for instance).


Space Calculation for SQL Server Table: What's wrong with my query?

The documentation for sys.allocation_units  is incorrect, it seems. It states that data_pages is number of used pages and it should include LOB pages.
LOB pages are pages for data types such as the (MAX) types, XML, geospatial and columnstore data.
But, looking on my machine (SQL server 2019), I see 0 data pages for LOB data. If you remove the GROUP BY and look at the individual rows, you'll see what I mean. So, you would have to CASE whether it is LOB data and use some other column in that case.
I've used the FactResellerSalesXL_CCI in the AdventureworksDW2016 database for my test. Adjust to your liking. I parameterized the schema name and table. See the discrepancy between the resulting column names data (your original calculation) and data_corrected (my adjusted calculation):
DECLARE @s sysname, @t sysname, @f nvarchar(256)
SET @s = 'dbo'
SET @t = 'FactResellerSalesXL_CCI'
SET @f = QUOTENAME(@s) + '.' + QUOTENAME(@t)

SELECT
    t.name AS TableName,
    a.type_desc,
    i.index_id,
    p.rows,
    a.total_pages * 8 as reserved,
    a.data_pages * 8 as data,
    (CASE WHEN a.type_desc = 'LOB_DATA' THEN a.used_pages ELSE a.data_pages END) * 8 as data_corrected,
    N'Not Needed' as index_size,
    a.total_pages * 8 -  a.used_pages * 8 as unused
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = @t
ORDER BY
   1 Desc;

I leave it to you to bring back the aggregation. :-)


MySQL 5.7 - Where is the mysql_secret file on Windows 7?

My blind guess would be to look in %APPDATA%\MySQL on your system as follows:

cd %APPDATA%
cd MySQL
dir .my_secret

For those who use the MySQL no-install Zip File (such as myself), you will not see one.

If .my_secret is not on your Windows servers, I have an alternative. You could start up mysqld manually with skip-grant-tables, set the password

net stop mysql
cd "C:\Program Files (x86)\MySQL\MySQL 5.7\bin"
start mysqld --skip-grant-tables
mysql -ANe"update mysql.user set authentication_string=PASSWORD('mynewpassword') where user='root'"
mysqladmin shutdown
net start mysql
mysql -uroot -p
Enter password: (enter the password 'mynewpassword' and hit <Enter>)

Give it a Try !!!


Add column to database, used with logical replication generates large WAL files

When you update a row, a new copy of the complete row is written to the table (and to WAL). This is how PostgreSQL works.
So after the update, the table will be more than twice as big, and you have generated a lot of WAL.
One thing you can do is run the update in batches of 10 million rows and run VACUUM on the table between these batches. That won't reduce WAL (that is not possible), but it will prevent table bloat. But you cannot do that in a single transaction.
In short, everything is working as it should.


In MySQL Multiple instance, default instance is running, second instance is not

I looked and saw this

/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)

That's a file permission issue.

Please go to Linux and run

cd /var/lib/mysql-databases/
chown -R mysql:mysql mysqld2

Now, you should be able to restart mysql for port 3307


How to trigger an event when row column crosses now()?

You'd have to schedule a program to run regularly and process the table.
Usually it is better to implement such logic in the SELECT statements.


Oracle cluster top activity class "cluster"

Investigate v$session view about what particular event is causing those cluster waits. E.g.:

select s.event, s.* from v$session s order by s.event;

event column will get you offending event event name. Then you can google the event or add information to you question.
Also "Blocking Sessions" screen in Performance menu in Enterprise Manager in such cases helps quite often. Some session may simply block other sessions.


If the information fits inside the record, go for it.

But remember that there is a limit to it, if you limit the block to something like 1000 users, should be fine.

When you are not in the position to define limits, then you should create another collection to handle this relation.


Problem can be in field level as it was for me.

In a source database, a field in a table had a different collation in a varchar datatype.

If you choose < database default>  for collation property of the fields, the problem may be solved.


One of the options is to have an additional slave MySQL server specifically for the backup purpose which can be stopped/locked without affecting the production DB.


Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8

Any version declared "GA" is "good".

5.7 is only one major version behind what Oracle is providing.  There have been a lot of improvements in 8.0, but, as with any upgrade, you may or may not be affected.

MariaDB 10.3 is a good parallel to 5.7, but there could be differences -- in features and in Optimizations.

It is good that you are eager to upgrade, but since you had troubles with 8.0, I think it is reasonable to stay with 5.7 for a while.  (Some people are struggling with upgrading from 5.5 or even older versions!)


Is there any option to enable Window NT logs in SQL Server Logs

I think this has the answer you are looking for. See this link to different same question Looks like a user needs membership in the Event Log Readers group or the local Administrators group.


Why doesn't `vacuum full` release space immediately?

I traced this to a long-running autovacuum backend process. Even though the autovacuum was running on another table, it was hanging on to the file descriptors for the old files for the table on which I had just done a full vacuum. It was easy to confirm this with lsof.

Cancelling the autovacuum released the space immediately.


Do I need a separate Id column for this "mapping" table?

No, there is no value in adding an additional "primary key" to this table. Your joins are only ever going to refer to ProducerID and ProductID, so it is just dead weight. IMHO.

Though I agree with @Shark that the join table doesn't even seem to be needed here, unless you are going out of your way to not change the schema of the existing tables in any way.

As an aside, I also think it is worthwhile to name your primary identifier in full (e.g. Products.ProductID instead of Products.ID) so that the identifier is consistently named throughout the schema.


Please see the Microsoft BOL for Temporary Tables:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Once the TempTable is dropped, the space it was consuming (and any resources reliant on it) is released (internally). If the TempTable caused your TempDB data file to grow on disk, even though the space is released internally in SQL Server, it won't be released back to the disk in the OS (just like any other database's data file). It will be re-used internally by SQL Server for other TempDB operations though.
Restarting SQL Server or running a SHRINK command are ways to release the internal unused space back to the disk for the OS, if that is of concern.


Test MySQL credentials from Linux command line?

mysql -h host -u user -p<whatever> -e"quit"

This allows you to use the same connection string that you use for programmatically sending queries to the server. You can add || exit 1 to the end to auto exit on invalid arguments. You may also want to redirect stderr to /dev/null if you do not like the auto generated MySQL error message.


Surely, choosing {a,b} as key, R(X) is in 3NF

Using an alternative definition of 3NF:

3NF Relation

A Relation Schema R(X) is in 3NF if for every non-trivial FD Y->Z

Y contains a candidate key (Y is superkey)

Or

Z is a prime attribute (Z is part of a key)


MySQL - Store date as a value with an ID?

It sounds like you have reinvented "Entity-Attribute-Value" schema.  This will be painful in the long run.  Meanwhile...

Do not "normalize" dates or other 'continuous' values (such as FLOAT).

The DATE datatype is only 3 bytes, and may as well be put in whatever table it best fits in, not normalized into an attribute table.

Relationship tables usually do not need an AUTO_INCREMENT id PRIMARY KEY since a combination of columns makes a better PK.  More tips here.


Is storing attributes in separate tables instead of columns a standard practice?

Is structuring a database like this a standard practice?

No.

  I'd like a second opinion before approaching the company about it.

Are you explaining poor performance? Complaining? Suggesting a re-design or replacement? Did anyone ask for your professional or personal opinion of the system? Is it going to affect project estimations and cause issues?

Why you are going to approach the company should form the basis of how you bring this up and what justification you need to bring to the table. If it's unsolicited advice for a small company, you might approach it more diplomatically by determining if this design issue is known, if it's causing issues, and if someone there is attached to, and proud of, their baby.

There's plenty of poorly designed systems and weird implementations out there... and while it's easy to shake our heads and second guess it's not always helpful to the client or ourselves. ;)


change SSIS package protection level before deployment

Since you're in project deployment mode, which means you are deploying to the SSISDB, there's no need for what you're doing ;)

When you deploy, the isdeploymentwizard.exe is handling all that for your

As part of the deployment, the "Changing protection level" step switches the packages to ServerStorage level


MySQL query - Passing variable into subquery's derived table

Use GROUP BY
And transform the "week" into a number that can be used in the GROUP BY:  FLOOR((TO_DAYS(createdTime) - 1)/7).  As a simple test:
SELECT FLOOR((TO_DAYS(CURDATE()) - 1)/7);
+-----------------------------------+
| FLOOR((TO_DAYS(CURDATE()) - 1)/7) |
+-----------------------------------+
|                            105446 |
+-----------------------------------+

That FLOOR expression gives you a "week number" that for use with GROUP BY.  (Tweek the "-1" if I don't have the week starting on the right day of week.)  You can derive "start" and "end" from that number via some simple expressions like
SELECT FROM_DAYS(105446 * 7) AS 'start',
       FROM_DAYS(105446 * 7 + 6) AS 'end';
+------------+------------+
| start      | end        |
+------------+------------+
| 2020-11-28 | 2020-12-04 |
+------------+------------+


How to export SSIS (SQL Server Integration System) packages to third party data source

I found an article about that, the version is to HANA 4.1.

  To move data from SQL Server to HANA, you can use SAP BODS in version
  4.1 SP1 brings improved migrate with an assistant.

  Other methods such as:   Sybase replication server.


Instead of writing your own solution, I would highly recommend to use Ola Hallengren's SQL Server Backup Solution.

Refer to : Bad habits to kick : avoiding the schema prefix by Aaron Bertrand.

Below should work for you (I have not tested it):

CREATE SCHEMA job
go
CREATE PROC job.BACKUPS @dir varchar(max)
AS BEGIN
    DECLARE @comando nvarchar(max) = N'BACKUP DATABASE STAZIONE TO DISK = '''+@dir+'-'+convert(char(8),getdate(),104)+'.bak'' WITH  COMPRESSION,  ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert), STATS=1'
    EXEC msdb.dbo.sp_add_job @job_name = @job ;--Add a job
    EXEC msdb.dbo.sp_add_jobstep @job_name = @job, @step_name = N'Backing',  @subsystem = N'TSQL', @command = @comando   --Schedule the job at a specified date and time
    EXEC msdb.dbo.sp_add_jobschedule @job_name = @job, @name = N'DB_BACKUP', @freq_type=8, @freq_interval =1, @active_start_date = @startdate,   @active_start_time = @starttime -- Add the job to the SQL Server Server will run weekly
    EXEC msdb.dbo.sp_add_jobserver @job_name =  @job, @server_name = @servername
END
GO


Tracking changes made in database structure MYSQL

There is no way to do that from the database side, but you can version control your schema changes by putting all your schema modification .sql scripts in a Git repository the same way you do with source code.

There are also tools such as FlyWay and Liquibase that can help you with this sort of thing.


Mysql throwing table full error when adding new column

tmp_table_size and max_heap_table_size are useful for certain complex SELECTs, not for ALTER.  It is not wise to raise either of those above 1% of RAM.
ALTER works in one of two ways, depending on version and the specifics of the query.

Copy the table over, making the change as it goes.  This seems to be what is happening.
Certain optimizations that avoid needing to copy the table.  It seems that this option was either not available due to using an old version or the complexity of the ALTER.

(Please provide the version of MySQL and the text of the ALTER.)
The "copy" method needs disk space to make a full copy of the table, then it does something like RENAME TABLE to swap it into place.  In your case, there needed to be about 30GB free on disk to perform the Alter.  Without the space, you get that error.
It may be that that space needs to be in the "tmpdir".  Could it be that that is a separate disk partition, and that partition is too small?
At the time of running the Alter, what was the value of innodb_file_per_table?  If it was OFF (or 0), then you have a nasty problem -- ibdata1 would be expanded by the 30GB, and not shrunk back.  You may have that table so larger that the disk is full.  Ouch.
Since you mentioned 844MB and 3TB, I would guess that that setting was ON (or 1).  That is, your 30GB table was probably a .ibd file on disk.  And another 30GB file was being built, but disk space ran out.
Please provide more info (as requested above); we may be able to help you find a not-too-painful way out of your predicament.
(As for SO vs dba.SE -- There are a zillion and one questions on SO; no human can even glance at all the questions, especially those that have only one tag.  No traction => no responses.)


JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

The optimal DB design always depends on the complete picture.

Generally, there is hardly anything faster than a plain btree index for your simple query. Introducing json or jsonb or even a plain array type in combination with a GIN index will most likely make it slower.

With your original table this multicolumn index with the right sort order should be a game changer for your common query:

CREATE INDEX game_changer ON actions (receiver, time DESC);

This way, Postgres can just pick the top 100 rows from the index directly. Super fast.

Related:

Optimizing queries on a range of timestamps (two columns)

Your current indexes receiver_idx and actions_time_idx may lose their purpose.

Next to the perfect index, storage size is an important factor for big tables, so avoiding duplication may be the right idea. But that can be achieved in various ways. Have you considered good old normalization, yet?

CREATE TABLE receiver (
   receiver_id serial PRIMARY KEY
 , receiver    text NOT NULL -- UNIQUE?
);

CREATE TABLE action (  -- I shortened the name to "action"
   action_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   -- global_sequence bigint NOT NULL DEFAULT nextval('actions_global_sequence_seq'::regclass),  -- ??
   time       timestamptz NOT NULL DEFAULT now(),
   block_num  int NOT NULL,
   tx_id      text NOT NULL,
   contract   text NOT NULL,
   action     text NOT NULL,
   data       jsonb NOT NULL
)

CREATE TABLE receiver_action (
   receiver_id int    REFERENCES receiver
 , action_id   bigint REFERENCES action
 , PRIMARY KEY (receiver_id, action_id)
);

Also note the changed order of columns in table action, saves a couple of bytes per row, which makes a couple of GB for billions of rows.

See:

Calculating and saving space in PostgreSQL
How to implement a many-to-many relationship in PostgreSQL?
Configuring PostgreSQL for read performance

Your common query changes slightly to:

SELECT a.*
FROM   receiver_action ra
JOIN   action a USING (action_id)
WHERE  ra. receiver_id = (SELECT receiver_id FROM receiver WHERE receiver = 'Alpha')
ORDER  BY a.time DESC
LIMIT  100;

Drawback: it's much harder to make your common query fast now. Related:

Can spatial index help a "range - order by - limit" query

The quick (and slightly dirty) fix: include the time column in table receiver_action redundantly (or move it there).

CREATE TABLE receiver_action (
   receiver_id int    REFERENCES receiver
 , action_id   bigint REFERENCES action
 , time        timestamptz NOT NULL DEFAULT now()  -- !
 , PRIMARY KEY (receiver_id, action_id)
);

Create an index:

CREATE INDEX game_changer ON receiver_action (receiver_id, time DESC) INCLUDE (action_id);

INCLUDE requires Postgres 11 or later. See:

Does a query with a primary key and foreign keys run faster than a query with just primary keys?

And use this query:

SELECT a.*
FROM  (
   SELECT action_id
   FROM   receiver_action
   WHERE  receiver_id = (SELECT receiver_id FROM receiver WHERE receiver = 'Alpha')
   ORDER  BY time DESC
   LIMIT  100
   )
JOIN   action a USING (action_id);

Depending on the exact story behind one set of data may create 3 separate rows more may be possible - even 3 separate columns in table action instead of the n:m implementation and a expression GIN index ...
But that's going in too deep. I tap out here.


In Oracle 12c, is there an alternative to CREATE TYPE when I only have read-only privileges?

There is built-in type called SYS.ODCINUMBERLIST which is a VARRAY(32767) of NUMBER, and EXECUTE privilege is granted on it to everyone (PUBLIC). You can use that for up to 32767 numbers, example:

with function f1 (p_numbers SYS.ODCINUMBERLIST) return number
as
begin
 return p_numbers.count;
end;
select f1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7)) from dual;
/

F1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7))
-------------------------------------
                                    7

In 19c, there is another built-in type called SYS.FI_NUMERICALS which is a TABLE of NUMBER, also usable by anyone in a similar way.

with function f1 (p_numbers SYS.FI_NUMERICALS) return number
as
begin
 return p_numbers.count;
end;
select f1(SYS.FI_NUMERICALS(1,2,3,4,5,6,7)) from dual;
/

F1(SYS.FI_NUMERICALS(1,2,3,4,5,6,7))
------------------------------------
                                   7

I have not found any documentation or reference for the latter.


Different where clause based on SSRS parameter

Yes, something like the below.

SELECT *
FROM DataTable
WHERE (@SearchField = 'CreateDate' AND CreateDate BETWEEN @StartDate AND @EndDate)
OR (@SearchField = 'ModifyDate' AND ModifyDate BETWEEN @StartDate AND @EndDate)

Or, to use the example WHERE clauses you specified, how about the below?

SELECT *
FROM DataTable
WHERE (
        @ReportType = 1
        AND FundDatte >= @FromDate
        AND FunDate <= @ToDate
        )
    OR (
        @ReportType = 2
        AND DisbursementDate >= @FromDate
        AND DisbursementDate <= @ToDate
        )
    OR (
        @ReportType = 3
        AND (
            (
                FundDatte >= @FromDate
                AND FunDate <= @ToDate
                )
            AND (
                DisbursementDate >= @FromDate
                AND DisbursementDate <= @ToDate
                )
            )
        )


Unique index by VARCHAR(1023) vs by BINARY(32)

First, I must point out why this is bad:

UNIQUE KEY `i_2` (`uri`(255))

It constrains only the first 255 characters of uri to be unique.  That is, you cannot have two rows that agree in the first 255 characters, but differ after that.

Where is short_url_code?

Back to the question... Answer: "none of the above".  But here are some tips on improving performance:

If you need a uniqueness constraint on a too-long column, here is one approach:

Use UNHEX(MD5(uri)) in a BINARY(16).  Then make that the PRIMARY KEY and get rid of id.

For those who complain that MD5 might have dups, I point out that "If you have 9 trillion rows, there is only one chance in 9 trillion that the next row you insert will be a false dup."

So, I would have only these two indexes:

PRIMARY KEY(md5_uri),
INDEX(uri(44))

The secondary index assumes you need to look up uri prefixes when you don't know the full string.  The 44 was somewhat arbitrarily picked -- smaller to save space; larger to have fewer dups.  More important than the average of 51 you mentioned is "what is the shortest length that leads to a 'reasonably small' number of dups for each prefix."

The problem with any type of hash is the inability to cache the data (and/or index) after the data (or index) becomes bigger than can fit in cache (innodb_buffer_pool_size).

My suggested changes will

shrink amount of cache needed by getting rid of an index and a column, and
have only 1 uniqueness constraint (instead of 3) to check during INSERT.

But you still need more ram than the dataset size, so that innodb_buffer_pool_size has space for caching all the data.  Else, you will stuck with being as slow as your disk drive (which should like a slow spinning drive at 60/sec.)  Are there more columns in the table than what you listed?  If so, we might consider vertical partitioning.

That leaves another problem that you alluded to -- case folding.  No hash is impervious to case without help.  You might do UNHEX(MD5(LOWER(uri))).  But that will also strip accents in utf8.  Not knowing your full set of requirements here, I cannot discuss further.

Another thing to do is to avoid the at-least-one I/O that occurs with every transaction.  Where practical, batch inserts, group things between BEGIN..COMMIT, and/or set innodb_flush_log_at_trx_commit=2 (instead of the default of 1).  SSDs would also help.


Database model with Users, Resources, Permissions

-- User USR exists.
--
user {USR}
  PK {USR}

-- Resource type RTY exists.
--
res_typ {RTY}
     PK {RTY}

-- Resource RSC, of resource type RTY,
-- owned by user USR, is named RSC_NAME.
--
resource {RSC, RTY, USR, RSC_NAME}
      PK {RSC}

      SK {RSC, RTY}

      FK1 {RTY} REFERENCES res_typ
      FK2 {USR} REFERENCES user

-- User USR was granted permission PER to
-- resource RSC by owner of that resource.
--
res_perm {USR, RSC, PER}
      PK {USR, RSC, PER}

FK1 {USR} REFERENCES user
FK2 {RSC} REFERENCES resource

CHECK (PER in ('read', 'write'))

Now the resource subtypes. The proper way to implement constraints for subtypes would be to use assertions (CREATE ASSERTION), but it is still not available in major DBs. I will use FKs instead, and as all other substitute methods it is not perfect. People argue a lot, here and on the SO, what is better. I would encourage you to check other methods too.

-- Car RSC, resource type 'C', ... car specific.
--
car {RSC, RTY, ... }
 PK {RSC}

 FK {RSC, RTY} REFERENCES resource

 CHECK (RTY = 'C')

-- Location RSC, resource type 'L', ... location specific.
--
location {RSC, RTY, ... }
      PK {RSC}

      FK {RSC, RTY} REFERENCES resource

      CHECK (RTY = 'L')

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key


If the keys repeat frequently, create a second table

CREATE TABLE Key_tbl (key_id INT, key VARCHAR(<>))

and reference the first table with this, while inserting just look up the key and insert the key_id in SearchKeyword


Improving the performance of a long query

The performance bottleneck very likely arises from the use of the IN keyword. You should rewrite it with an EXISTS. See the accepted answer of my question on Stackoverflow for how to do it. I'm pasting the relevant part here (credit goes to Quassnoi and you have to adapt it to your SQL dialect if needed):

One of the most common things to do is replacing this query:

SELECT  *
FROM    mytable
WHERE   col IN
        (
        SELECT  othercol
        FROM    othertable
        )

with this one:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   o.othercol = mo.col
        )


Is there any command to optimize all MySQL tables one by one automatically?

I usually use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators to check, optimize and repair tables.

mysqlcheck -uroot -p --optimize --databases myDatabase

Here is the MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html

Max.


"Will this query always be indexed AND fast as the rows in the table grow?"

Definitely not necessarily. If there are many records and/or the value of deleted is skewed in some way (i.e. it's (nearly) always one particular value, or split 50-50), then the optimiser may decide to ignore a given index. Googling "why does mysql not use an index" gives 3.5M results

[EDIT]

You said:

"Thank you for your answer. Is there a different type of query I can use to count the number of rows with a WHERE clause?"

No -  that's what COUNT() is for. See here. Basically, you can't get round problems like the one you outline - indexes are frequently helpful, but there's no "magic bullet" that will cope well with all data volumes and profiles.

To see how difficult this problem is, check here - that's > 600 pages on the fundamentals of how an RDBMS decides on the best plan for a query. It is far from a trivial problem!


Returning Row Count in a Stored Procedure in Oracle

You are almost there, just put the result of the select into the out variable:

SELECT COUNT(*)
  into v_EX_ROWS
FROM EXAMPLE
WHERE EX_ID = v_EX_ID

You might be better of using a function though:

create or replace function SP_INFO_EX_S(v_EX_ID    IN NUMBER)
   return number
AS
  row_count number;
BEGIN
    SELECT COUNT(*)
      into row_count;
    FROM EXAMPLE
    WHERE EX_ID = v_EX_ID;

    return row_count;
END SP_INFO_EX;
/

Then just do:

select sp_info_ex_s(42) from dual;

Depending on your query tool, this might be easier to deal with than an out parameter.


How to verify that a certain commit has been transmitted to standby

Without opening the standby for read-only, you won't be able to identify specific changes to specific physical objects, instead you'll have to verify that a specific set of changes have been applied to the remote standby. Views including v$standby_apply_snapshot, v$archived_log, v$archive_gap, v$dataguard_stats, v$managed_standby are all ones that I use on a regular basis to verify standby apply health.

Additionally, you can use the dgmgrl command line interface to show the status of your log apply on the standby.


to_char(MyDate, 'DD/MM/YYYY') is the correct answer
(or to_char(MyDate, 'DD;MM;YYYY') if you want ; instead of /)

Beware : MyDate must be a timestamp zone.

If MyDate is a Date Zone, you should code

to_char(timestamp_iso(MyDate), 'DD/MM/YYYY')

(or to_char(timestamp_iso(MyDate), 'DD,MM,YYYY') if you want , instead of /)


Is there some table in MS SQL 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?

The simplest way to do this is the query the management views associated with the currently-executing request, the session, or the connection:

sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_connections

The first two expose a row_count attribute that contains the number of rows returned by the query so far. If you know the total number of rows that will be returned (even roughly) this can provide a simple progress indication.

The third view provides num_reads and num_writes indicating how many network packets have been sent and received by the connection. The size of the network packet in use for that connection is given in the net_packet_size attribute. The reads and writes include protocol overhead, and are not reset to zero at the start of each query or statement, so you would need to record the values before the query of interest starts executing.

All three views allow you to specify the session_id you are interested in, for example:

SELECT row_count
FROM sys.dm_exec_requests AS DER
WHERE session_id = 52;


Give every user 3 default 'bookmark' entries

Would you be OK with having a single table that has these three immutable bookmarks, and then using a view that joins this table with the user's real bookmarks?

immutable_bookmarks
-------------------
  id
  name
  URL (or whatever this is a bookmark to)

user_bookmarks
--------------
  id
  name
  user_id
  URL

And then define the view as:

create view user_bookmarks_v as
  select id, name, URL, '-1' as user_id
  from immutable_bookmarks
  union
  select id, name, URL, user_id
  from user_bookmarks

(syntax may not be 100% correct MySQL)

When your application needs to get a user's bookmarks, you can do something like this:

select *
from user_bookmarks_v
where user_id = -1 OR user_id = $USER_ID

This gives you the advantage of having the immutable bookmarks in the database, so that if you ever need to change them, it's very easy, as opposed to having them in the application and needing to roll out an application patch to all installations. You can also change the set of immutable bookmarks by adding or removing records and instantly having them accessible to all users immediately. There's no special triggers or procedures, you do need to add a new table and a new view.

This also has the advantage of not needing to insert 3 records for every user.

The disadvantage is the somewhat ugly (in my opinion) where user_id = -1 to capture the immutable bookmarks. It can probably be refined, I just don't have the time at the moment.

Whoops, I just realised this might not work if users can modify the 3 immutable bookmarks... Hmmm... @Sherlock: let me know if this makes sense, your "edit" requirements were a little unclear and this suggestion might not work for you.


How to calculate avg of some column in join?

All you really need to do is to SELECT from the gradetbl, apply the AVG aggregate function over the grade and then GROUP BY the teacherid like so:

SELECT teacherid, AVG(grade)
  FROM gradetbl
 GROUP BY teacherid;

For the results you have indicated there should be no need to use a JOIN at all. You will only need to use a JOIN when you want to fetch data associated with the teacherid:

SELECT t.name, AVG(g.grade)
  FROM gradetbl AS g
  JOIN teacher as t
    ON t.id = g.teacherid
 GROUP BY t.name;

You should view the documentation for SELECT, GROUP BY and AVG for thorough examples and explanations of common usage.


User defined function performance disadvantages

There comes a time when you have to decide what is more important to you, maintenance of the code or the speed at which it runs? The reason it is running slow is because UDF's get processed on a row-by-row basis - SQL Server performs best using set-based operations. There is no reason why you cant keep your UDF's as there may be time where it is more practical to use them than to expand out the query.

My advice is this: If your data set is small feel free to use the UDF's; but if you are working with a large data set then take the time to write, test and optimize the query to obtain the best results - it will benefit you in the long run when you don't have users complaining their system is slow.


Tail log backup or use log backup after putting DB into single user mode?

If the DB is set into single user mode which means that no one except me can access database, can I omit using NORECOVERY?

If you are connected to a different database, or when you eventually disconnect any other user can access the SINGLE_USER database.  A Tail Log backup leaves the database in the RESTORING state.  This guarantees that no users can accidentally read or write data to the old database after the cut-over.


Synchronize Queries Retrieving Sessions And Locks From DMVs

Not possible. These DMVs do not offer even self-consistency guarantees. For instance it is possible for a resource to appear in different mutually exclusive states, in the same run, in the same result set. If such DMVs would take the necessary steps to 'stabilize' the data during a scan the results would be disastrous for performance (imagine all locks being frozen in place while a scan over sys.dm_tran_locks is progressing).

Extended Events do offer a much better picture, but obviously is the picture of events, not of state. In theory is always possible to reconstruct state from the sequence of events knowing the initial state and achieve the same picture you get from the state dump (the DMVs) but that is only theory...


How to send mail from a restricted SQL Server

So, I used the below script.

Prerequisites are that the refresh script will write the log to a shared location accessible from a Prod Server.

$date = Get-Date -Format d
$dir = "\\xxxxx\g$\QARefreshLogs"
$recipient = "xxxxxx"
$smtpserver = "xxxxxxx"
$smtpport = "xx"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg = new-object Net.Mail.MailMessage
$msg.From = "xxxxxxxxxx"
$msg.To.Add($recipient)

---Retrieving the latest file

$latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Where-Object {$_.Name -ilike "<db_name>_refresh_log_*"} | Select-Object -First 1
$LatestRefreshLog = $latest.Name
$LatestRefreshLogDate = $latest.LastWriteTime.ToString('M/dd/yyyy')

--Validating if the log file generated today?

if (($LatestRefreshLogDate) -notmatch $date)
     {
      #Write-Host "WARNING: There is no today's restore log file!"
      $msg.Subject = "SQLServer QA Refresh Status"
      $msg.Body = "QA MSSQL DB refresh is having issues.Please validate."
      $smtp.Send($msg)
     }
     else
     {

--Validating if refresh is success or not?

      If(Get-Content -Path $dir\$LatestRefreshLog | %{$_ -match "RESTORE DATABASE successfully processed"})
      {
       #Write-Host "The latest restore log file is $LatestRefreshLog"
       $msg.Subject = "SQLServer QA Refresh Status"
       $msg.Body = "QA DB is refreshed successfully."
       $smtp.Send($msg)
      }
      else
      {
       #Write-Host "The latest restore log file is $LatestRefreshLog"
       $msg.Subject = "SQLServer QA Refresh Status"
       $msg.Body = "QA DB refresh is having issues.Restore failed."
       $smtp.Send($msg)
     } }

This may not be an optimal script. It served my purpose....thanks for all your inputs. This script could be helpful to others, have a PoSh time!!!


Query to combine each duplicate found entry

Since performance is obviously not going to be a high priority here (all this XML PATH concatenation is quite expensive), and since the only real problem with the output is that you have duplicates, try changing this:

SELECT [USER],
    STUFF(b.[licenseDup],1,1,'')    ...
  FROM
    (SELECT DISTINCT a.* FROM [FEBCardb].[dbo].[sheet1$] ...

To this:

SELECT DISTINCT [USER],
    STUFF(b.[licenseDup],1,1,'')    ...
  FROM
    (SELECT a.* FROM [FEBCardb].[dbo].[sheet1$] ...


NoSQL, CouchDB vs. CouchBase, What should i do?

IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to NoSQL solutions often with little understanding of what the tradeoffs are or how to use them appropriately and effectively.

I would be surprised if a well-tuned db was actually the problem here for a web app, if the size of the result set is the issue.  The simple fact is that a result set can only be retrieved from disk so fast (assuming you aren't using a main memory db where everything is forced in RAM), and then you actually have to spend time processing the result set to get your web page.  You need to start by fully profiling everything before assuming it's the db.

Your most basic tradeoff in NoSQL is data entry flexibility and easy scaling vs integrity guarantees and data processing on output.   The only way to do data processing in NoSQL over a result set of any size is to essentially do it on input and this has significant impacts over the life of a product if a NoSQL solution is used at the expense of a traditional RDBMS.  On the other hand, were appropriate these offer adjuncts to the RDBMS which can be helpful for both pre- and post-processing.  In short there are reasons to choose NoSQL but size really isn't one of them.

Now, you mention here that this is a "web page" that is loading a "very big result set."  Now, I do crazy things with web apps sometimes, and I suspect that if you are really loading a very large result set directly into a web page you have plenty of issues other than db performance.

In LedgerSMB for example, I know of cases where we pull over a thousand invoice rows to generate a single web page for some users (we use PostgreSQL).  For us PostgreSQL hums along quite well, even when aggregating across tends of thousands of records pulled out of multi-million-record tables.  Our time spent (profiled) per page load at that level is approx 15 sec of db time to up to 5 min of web app time to generate the web page.  (This is acceptable for the reason that it does globally optimize the workflow for this customer, keep in mind the web page may have up to 20k input elements, and the data has to be significantly transformed between where the db server sends it and where the web page is built).   This may not match your use case specifically but it may give you an idea of the fact that the database doesn't have to be the bottleneck there, and probably isn't if you are doing a lot with a web page.

Here are some aspects of troubleshooting and options you have if the db is in fact the problem.

Profile your whole application.  How much time is actually spent on db stuff?  How much is spent processing the page for display?
Profile your db queries.  What can be done to make them more efficient?

Do this before concluding that a different db will fix your problem.

Now if it turns out that you really have pushed this to the max, then you need to look at your choices.  These include:

PostgreSQL (yes, a relational db).  One thing this has going for it are much more generally optimized table/index structures (InnoDB specializes in pkey lookups, meaning that other searches are slower).
VoltDB (another Relational DB, but this one is main memory for high-speed oltp and very fast)
You can build a caching layer with a NoSQL db which works alongside your rdbms.  This is where you might be able to use MongoDB or CouchDB.


MS Access - Creating a report based on a pass-through query that requires a parameter

Here is how I approach this kind of task.  I use Microsoft Access with SQL Server back end all the time, and I either use parameters for stored procedures in pass-through queries, or dynamic sql in a pass-through queries all the time.

If you are calling a stored procedure in a pass-through query from Microsoft Access and you need to set the parameters, here is how I do it.

In your pass-through query, use special formatting to assist with dynamic search and replace, such as the following example.  This is a pass-through query that I created in Microsoft Access.  Note, that in a pass-through query, sql server (in my case) syntax applies, so I have put special comments in the query so that I can use code to search and replace the parameter values in between those comments.  In this example, my stored procedure takes two parameters, and the name of this pass-through query is qryInCode_SQL_uspProject_Search.

EXEC dbo.uspProject_Search

@Active =
-- R01_BEG
-1
-- R01_END
, @Search =
-- R02_BEG
''
-- R02_END
-- R03_BEG
-- R03_END
-- R04_BEG
-- R04_END
-- R05_BEG
-- R05_END

Notice the careful placement of the parameter names and separaters (commas) so that only the parameter value appears in between the begin/end markers.

Now, I have written a subroutine to automate replacement (setting) of parameters in any pass-through query.

Public Sub sitsUpdateSQLParams(stQName As String, ParamArray Params() As Variant)
On Error GoTo Err_PROC
    Dim ErrMsg As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim mssql As String
    Dim x As Long
    Dim y As Long

    Set db = CurrentDb()
    If stQName <> "" Then
        Set qdf = db.QueryDefs(stQName)
        For x = 0 To UBound(Params)
            y = InStr(qdf.SQL, "-- R" & Format(x + 1, "00") & "_BEG")
            mssql = Left(qdf.SQL, y + 9)
            mssql = mssql & vbCrLf & Params(x) & vbCrLf
            y = InStr(qdf.SQL, "-- R" & Format(x + 1, "00") & "_END")
            mssql = mssql & Mid(qdf.SQL, y)
            qdf.SQL = mssql
        Next x
    End If

Exit_PROC:
    Set qdf = Nothing
    db.Close
    Set db = Nothing
    Exit Sub

Err_PROC:
    Select Case Err.Number
        Case Else
            ErrMsg = "Error: " & Err.Number & vbCrLf
            ErrMsg = ErrMsg & "Line: " & Erl() & vbCrLf
            ErrMsg = ErrMsg & Err.Description
            DoCmd.Hourglass False
            MsgBox ErrMsg, vbOKOnly + vbCritical, "Sub sitsUpdateSQLParams"
            Resume Exit_PROC
            Resume
    End Select

End Sub

You will notice that this subroutine takes the name of the pass-through query as the first parameter, and then it take a Variant parameter array for all subsequent parameters.  This allows you to specify as many parameter values for replacement in the pass-through query as needed.

You would call this routine for my sample pass-through query as follows:

sitsUpdateSQLParams "qryInCode_SQL_uspProject_Search", 0, "'def'"

That will cause 0 to be passed for the @Active parameter and 'def' to be passed for the @Search parameter.

Also note, that your parameters in this function call must be listed on the above function call in the order they appear in your pass-through query.  My code is relying on the first replacement parameter value being inserted into the R01 block, and the second being inserted into the R02 block, and so forth.

So, in your case, for a report, I would do the following:

Create a pass-through query and format it like my example, so that the parameter replacement routine can correctly replace those parameters.

For this example, lets call your Microsoft Access pass-through query qryReport_SQL_PT_01

Now, create a regular Microsoft Access SELECT query that simply does:

SELECT * FROM qryReport_SQL_PT_01 and call it qryReport_01

Now, your report should have qryReport_01 as the RecordSource.

For your user interface, you now need to use a form, or some other method, to prompt the user for the parameter values so that you can have those values availble in VBA code.  For this to work, you can no longer rely on Microsoft Access to auto-prompt the user for parameters you reference in this query.  You must gather those from the user yourself.

Once you have those values, you can call the routine to update the pass-through query and then open the report.  The report will now reflect the parameters you collected from the user.


how to identify table name using a column name if we are not aware about the table name

You can use INFORMATION_SCHEMA.COLUMNS instead of sys.columns

SELECT
    TABLE_CATALOG,TABLE_SCHEMA,TABLE_SCHEMA,COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME LIKE '%MyColumn'


SQL Server Always On Node and File Share Majority

Our goal is that if the primary replica server 1 at site A is down, the Always On Availability Group (AG) fails over to the secondary replica server 2 at site A, and if both servers at site A are down, the AG fails over to site B.

Except for a very specific scenario, this will not work. In all other scenarios you'll lose quorum before you'd ever be able to automatically fail over (which requires... quorum).

The best answer would be to have the DR side be manual failover (it could still be synchronous) with the proper documentation on how to force quorum and bring the AG online.

You could also invest more into VMWare and use their technologies but that assumes the infrastructure, licensing, and ability to implement those products for a specific service such as this.


SQL Server service won't start due to certificate issues

TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

// MessageId: CRYPT_E_NOT_FOUND
//
// MessageText:
//
// Cannot find object or property.
//
#define CRYPT_E_NOT_FOUND                _HRESULT_TYPEDEF_(0x80092004L)

  What could be the issue with the certificate?

The certificate is corrupt or otherwise unusable in its current state, or lacks a private key component (either corrupt or account isn't able to access).

  How and what I have to check?

A quite easy and quick check is to open up the MMC Certificates Manager console (certmgr.msc) and right-click on the certificate, choose "all tasks", "Manage Private Key", which will most likely result in an error stating that the key doesn't exist or some other random error message.

If you don't receive an error, then make sure the SQL Server Service account has permissions to read the private key (also on the manage private keys screen).

If you get an error, then SQL Server can't read the private key which means it's going to fail to start up. Thus, either remove the SSL Certificate from use for SQL Server, restore it from a good backup, or generate and use a new one.


Quick way to validate two tables against each other

Here's what I've done before:

(SELECT 'TableA', * FROM TableA
EXCEPT
SELECT 'TableA', * FROM TableB)
UNION ALL
(SELECT 'TableB', * FROM TableB
EXCEPT
SELECT 'TableB', * FROM TableA)

It's worked well enough on tables that are about 1,000,000 rows, but I'm not sure how well that would work on extremely large tables.

Added:

I've run the query against my system which compares two tables with 21 fields of regular types in two different databases attached to the same server running SQL Server 2005.  The table has about 3 million rows, and there's about 25000 rows different.  The primary key on the table is weird, however, as it's a composite key of 10 fields (it's an audit table).

The execution plans for the queries has a total cost of 184.25879 for UNION and 184.22983 for UNION ALL.  The tree cost only differs on the last step before returning rows, the concatenation.

Actually executing either query takes about 42s plus about 3s to actually transmit the rows. The time between the two queries is identical.

Second Addition:

This is actually extremely fast, each one running against 3 million rows in about 2.5s:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB

If the results of those don't match, you know the tables are different.  However, if the results do match, you're not guaranteed that the tables are identical because of the [highly unlikely] chance of checksum collisions.

I'm not sure how datatype changes between tables would affect this calculation.  I would run the query against the system views or information_schema views.

I tried the query against another table with 5 million rows and that one ran in about 5s, so it appears to be largely O(n).


A function to check if a column allows NULL

You can query the INFORMATION_SCHEMA:

http://dev.mysql.com/doc/refman/5.0/en/columns-table.html

In the INFORMATION_SCHEMA.COLUMNS table there is a IS_NULLABLE column.

You could turn it into a function, I imagine, but I would probably put this logic in an outer part.


Alternative to slow [LEFT JOIN + OR] without UNION?

If you select from the results of a union all you will be able to filter the results you are looking for in a speedy fashion and only have to have a single where clause.

SELECT info.id
FROM
  (SELECT
    purchase.id
  FROM
    purchase
  LEFT OUTER JOIN user ON purchase.buyer = user.id
  LEFT OUTER JOIN user_documents ON user.id = user_documents.user
  UNION ALL
  SELECT
    purchase.id
  FROM
    purchase
  LEFT OUTER JOIN buyer_info ON purchase.id = buyer_info.purchase) info
WHERE info.document = '123'


How to fill/insert fake data into a MS Sql Server

Do a CROSS JOIN between sys.columns, use that same 2, 3 , or 4 times. Have a TOP, with as many rows as you want. Use that as a base for INSERT ... SELECT.

CREATE TABLE mytable(c1 int identity(1, 1), c2 char(10))

INSERT INTO mytable
SELECT TOP(1000) 'Hello'
FROM sys.columns AS a
 CROSS JOIN sys.columns AS b
 CROSS JOIN sys.columns AS c

The cross join is a classic trick. Do a cross join between two tables that has 1000 rows each and you have 1,000,000 rows in the result. With 3 tables, you have 1,000,000,000 rows. You can have some "numbers" table you create for these purposes, with for instance 1000 rows in it. Or, do a quick and dirty and use some view that you know exists with a decent amount or rows in it (like sys.columns).


Is this Normal SSIS Foreach Loop Container behavior

Based on clarification from the comments, the work flow was

C:\SourceFolder\File1.csv -> C:\SourceFolder\Archive\File1.csv

The ForEach File Enumerator has a Traverse Subfolder option which allows the enumerator to look beyond the top level of a folder hierarchy.

The specific mechanics of how the enumerator appears to be implemented is that it will identify all the elements within a folder. Process all files matching the FileSpec, *.csv is this case and then visit each directory applying the same pattern.

What you're observing was the original file was processed and correctly moved to the archive location. The archive location was then processed resulting in a double processed file.


Does a simple select query acquire locks?

I would like to understand whether the following, very simple select statement would take any locks

It is a common misconception that a SELECT query running at the default READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.

SQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).

Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the SELECT statement completes.

In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.

Overriding the current isolation level with the NOLOCK table hint is almost always a bad idea.

Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.

SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.


SQL Server Massive delete causing huge/full transaction log

If you cannot in any way modify the stored proc, then it sounds like the vendor is at fault for shipping a stored proc that doesn't work well for their customers.  They would need to fix something.
Your options would be things like giving yourself a bigger transaction log that can manage it.  If you don't have space you could temporary split the log into 2 files and put it on 2 drives.  That comes with risk and benefits such as more IO depending.
Also please look at VLF fragmentation.  Your log files are probably heavily fragmented now.  Defragging your log files after you find the proper sizing will help performance, recoverability, restores, and some disk space savings.


RESOURCE_SEMAPHORE_QUERY_COMPILE isn't aided by more memory in older versions of SQL Server. Since you're on 2014, you can use Trace Flag 6498 if you're on certain patch levels.

That TF increases the large query compile gateway dependent on the amount of memory in your server.


Asynchronous database inserts from C# always result in a deadlock

Seems like you have issues with your read statement. What is the isolation level on a database?

You can try to get rid of the second read by adding StatusId <2 to the first read statement

(SELECT Id
   FROM dbo.Documents AS d
  WHERE StatusID < 2
   AND EXISTS (SELECT Id FROM @documentData as I
                WHERE i.[File_Name] = d.[File_Name]
                  AND i.File_Size = d.File_Size)
)


If you are complying with all the RPO and RTO requierments by having database mirroring in place, with daily full backups then there are no data consistency  issues with making backups to NUL. So the answer to your question is No.

But (there is always a but) you are not having a secondary restore path if a full backup is corrupted and both the servers go down and your restore options are always the point in time when the daily backup is made.

By making normal log backups and storing them for 48 hours you can restore past a single corrupted full backup which is not possible to do with differential backups.


In Extended Events what is the difference between rpc_completed and sql_batch_completed?

The difference is how the command is executed by the client. The client can directly execute a stored procedure or the client can send a sql batch.

To call a stored procedure from .NET you would use

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;

and to execute a batch you would use

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.Text;

There are other things you need to set up like the connection, but the above shows the difference between the two execution modes.


Select from arrays in a way similar to IN

You can use overlap (&&) operator which means "have elements in common".

SELECT
    name
  FROM sal_emp
  WHERE
    pay_by_quarter && ARRAY[1,4,5]

Documentation link is here: Array Operators


SQL Data types and SmallInt/TinyInt vs Regular Integer

What are the SQL Server benefits of using proper data types?

If you use the right datatype your database will better match your model and is more likely to be efficient both in terms of space & speed. You are at risk of this question being closed as "too broad" because it can be quite a wide subject.

To touch your more specific points:

  SmallInt/TinyInt vs Regular Integer

Smaller types take less space so more rows fit in a page resulting in less space taken both on disk and in memory. Of course, if you choose too small a type your application will fail. Consider also BIGINT for when values larger than 2,147,483,648 (or smaller than -2,147,483,647) might be needed.

The difference is multiplied up when you consider indexes: the larger types end up in any index that covers that covers the columns too.

  Decimal(2) vs float

There is a huge difference here. DECIMAL (and its synonym NUMERIC) are actually scaled integers that are guaranteed to have the precision you specify. Floating point numbers have their uses but be very careful with them as they are only approximations due to rounding issues converting between binary and decimal forms - the most common example is 0.1+0.2 will usually not equal 0.3. See http://floating-point-gui.de/ amongst many other references for details and run this to see the effect first hand:

SELECT CAST(0.1 AS FLOAT        ) + CAST(0.2 AS FLOAT        ), CASE WHEN CAST(0.1 AS FLOAT        ) + CAST(0.2 AS FLOAT        ) = 0.3 THEN 'OK' ELSE 'oops' END
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)), CASE WHEN CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) = 0.3 THEN 'OK' ELSE 'oops' END

Essentially: don't use FLOAT unless you know it is what you need.

  Varchar(100) vs char(100)

Variable-length types are far more efficient space wise, but less efficient to process CPU-wise. If you use (MAX) they are likely to be stored off-page which is less efficient again and stops compression taking effect if you use that on your tables. If you use row or page compression your fixed length types become variable length anyway but you keep the built-in validation of length which may be useful for your model.

  Will it really matter these days, with modern computing?

Short answer: Yes.
Long answer: Yyyyyyyyeeeeeeeeeeeeesssssssssssss!

The considerations can be a little different with modern CPUs - they can be much faster relative to storage and network transfer than they used to be so it is more often a good optimisation to save space at the expense of extra CPU time, but correct type choice very much still matters.

Though type choice matter most for matters other than optimisation: correct types better model your data which should be your first priority.


You could return the sequential number of a row within a partition (in this case id) of a result set, starting at 1 (AND seqnum = 1) for the first row in each partition.

See below

SELECT t.created_by,
       t.created_date,
       tl.updated_by,
       tl.updated_date
FROM   test_name t
      INNER JOIN (SELECT tl.*,
            Row_number()
              OVER (
                partition BY id
                ORDER BY updated_date DESC ) AS seqnum
             FROM   test_log tl) tl
         ON t.id = tl.id
            AND seqnum = 1
WHERE  state = 'active';


Clearly the data you are trying to start does not belong to the configuration you have in the my.cnf. If you look at the beginning of the log, you will see that there is a mismatch between the files in the system and the InnoDB catalog, meaning that the engine was running before:

2016-07-19 18:11:00 3416 [ERROR] InnoDB: Table dbhipcom/cardplus_erro
in the InnoDB data dictionary has tablespace id 6, but tablespace with
that id or name does not exist. Have you deleted or moved .ibd files?
This may also be a table created with CREATE TEMPORARY TABLE whose .ibd
and .frm files MySQL automatically removed, but the table still exists in
the InnoDB internal data dictionary.

The innodb-buffer-pool-size is set to 1G, which does not match with the amount of memory is trying to allocate:

2016-07-19 18:11:17 3416 [ERROR] InnoDB:  InnoDB: Unable to allocate
memory of size 4294967016.

Check which configuration is the MySQL loading, inspecting the init file that you are currently using.


How to check whether old MDF and LDF files are referenced

Use SysInternals' Handle utility to view open handles to the file.  That will tell you succinctly and definitely if any process running on the machine in question has those files open.

As an example, on my dev machine, if I run this in a command-prompt:

C:\Users\xxxx\Downloads\Handle_> handle D:\SQLServer\MV2012\Data\CharSizeTest.mdf

I see this output:

Handle v3.51
Copyright (C) 1997-2013 Mark Russinovich
Sysinternals - www.sysinternals.com

sqlservr.exe       pid: 4028   type: File      B2C: D:\SQLServer\MV2012\Data\CharSizeTest.mdf

In the above, you can see sqlserver.exe has an open handle to the .mdf

Notice, this doesn't guarantee you can delete the file; it will simply show you if anything has the file in question open.

Prior to deleting anything, I take a backup of the file in question so that I can recover them if needed.  I scheduled that backup for deletion in, say, 6 months.

You can check for databases or individual files that are offline via the SQL Server instance, using this query:

SELECT  DatabaseName = d.name
    , DatabaseState = d.state_desc
    , FileName = mf.name
    , FileState = mf.state_desc
    , FilePath = mf.physical_name
FROM sys.master_files mf
    INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE mf.state_desc <> 'ONLINE'
    OR d.state_desc <> 'ONLINE'
ORDER BY d.name
    , mf.name;

For an offline database, the output looks like:

╔══════════════╦═══════════════╦══════════════════╦═══════════╦═══════════════════════════════════════════════╗
║ DatabaseName ║ DatabaseState ║     FileName     ║ FileState ║                   FilePath                    ║
╠══════════════╬═══════════════╬══════════════════╬═══════════╬═══════════════════════════════════════════════╣
║ CharSizeTest ║ OFFLINE       ║ CharSizeTest     ║ ONLINE    ║ D:\SQLServer\MV2012\Data\CharSizeTest.mdf     ║
║ CharSizeTest ║ OFFLINE       ║ CharSizeTest_log ║ ONLINE    ║ D:\SQLServer\MV2012\Logs\CharSizeTest_log.ldf ║
╚══════════════╩═══════════════╩══════════════════╩═══════════╩═══════════════════════════════════════════════╝


Renaming Sql Server database unusual result

What SQL Server is doing is, it is caching the query plan for

 exec [Database_A].[dbo].[TestSP]

This can be found using :

SELECT
    usecounts, cacheobjtype, objtype, text
FROM
    sys.dm_exec_cached_plans
CROSS APPLY
    sys.dm_exec_sql_text(plan_handle)
WHERE
    usecounts > 1 AND text LIKE '%TestSP%'
ORDER BY
    usecounts DESC;
GO

When you execute your stored procedure during step 1, 2 or 3, it gets cached in the procedure cache.

Hence during your 4th step, it throws an error.

If you do a dbcc freeproccache, and then rerun the SP exec [Database_A].[dbo].[TestSP], it will work.

As @Aaron pointed out, it will work using EXEC sys.sp_refreshsqlmodule 'Database_A.dbo.TestSP' or recompiling the SP sp_recompile TestSP  as well.

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

There is no need to restart SQL Server.

Note: Free ProcCache will flush out all the query plans, so its better to use sys.sp_refreshsqlmodule or sp_recompile.


The first is proper syntax, the second is not.
See here: https://riptutorial.com/oracle/example/4853/parallel-hint


What should I set my table_definition_cache to?

I hope this links can help you to decide your fine tunning. A little later my answer, sorry about.
I could resolve some problems in 5.5 MySQL version using this information.
Here: you can check how to calculate your table_open_cache

Find current value of open_tables and opened_tables

mysql> show global status  like 'open%';

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable.  If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.

Find out Table cache hit rate

Table cache hit rate = table_open_cache*100/Opened_tables

In general it should be more than 50%. So you need to increase value of table_open_cache, if you are under this value, though there are lots of reasons to have a high value of Opened_tables. Like FLUSH TABLES will close all open tables and reopen it which significantly increases Opened_tables value.

Calculate  the tune value of table_open_cache and set it

Table_open_cache = total_tables*Threads_connected

As all the threads (user) are not generally access all tables. I think you should set 50% of the value calculated.  Because too big value of this variable has some other side effects.  So the formula becomes

Table_open_cache = total_tables*Threads_connected*.50

Along with table_open_cache you should also tune open_files_limit system variable.

In general it is 2x of  table_open_cache.
open_files_limit= Table_open_cache*2

open_files_limit is not a dynamic variable. So you should set it in my.cnf file and restart MySQL.
Make sure that your operating system can cope with the number of open file descriptors required by the table_open_cache setting.

Ref. https://techinfobest.com/optimize-mysql-table_open_cache/
And here: MySQL Documentation give us the formula to set table_definition_cache based on table_open_cache size. (yes, I know, the link is for 5.7 version, but in 5.5 doc version there isn't any mention about the formula), so you need first define the size of table_open_cache, this is the reason why I give the link above.
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_table_definition_cache
Next, You must monitoring the status of COM_STMT_REPREPARE and Open_table_definitions to see what is happening in the server.
If you have applications that are using prepared statements and getting the message error code [1615]Prepared statement needs to be re-prepared, is a signal that MySQL is flushing that cached definition tables..
(https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_need_reprepare)
At last: Information about why MySQL is reprepared stmt
https://dev.mysql.com/doc/refman/5.5/en/statement-repreparation.html


You may find this question of value: https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete

Highlights:

Your tables should be set up with foreign keys with DELETE CASCADE.

Example:

CREATE TABLE categories (
    id int unsigned not null primary key,
    name varchar(255) default null
);
CREATE TABLE products (
    id int unsigned not null primary key,
    name varchar(255) default null
);

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE ON UPDATE CASCADE
);


What are the OMem and 1Mem columns in the plan output?

OMem - Estimated amount of memory needed to perform the operation in memory only. This is also called the optimal execution.

1Mem - Estimated amount of memory needed to perform the operation in one pass (write to and read from disk (temp) only once). This is called a one-pass execution.

A multi-pass execution is where the same data is written to and read from disk more than once.

Think about sorting, where the database has to sort large amount data in a small PGA/sort area.

Since you query plan statistics with ALLSTATS LAST, some extra columns:

Used-Mem - The amount of memory actually used for this operation.
Also there is a number in brackets in this column. If the number is 0, then it was an optimal execution, used only memory and no temporary space. If the number is 1, then it was a one-pass execution. If the number is greater than 1, it was a multi-pass execution, and that number is the number of passes.

Used-Tmp - The amount of temporary space used for this operation.

Here is a presentation about the execution passes starting at page 28: http://www.nocoug.org/download/2003-08/how_cbo_works.ppt


You have supertype (content) which may be one of different suptypes (article, video,review). One approach to model such relationship in database is :

CONTENT(content_id, content_type_id, PK(content_id), UNIQUE(content_type_id,content_id));

 ARTICLE(content_id, content_type_id,
  other attributes,
  PK(content_type_id,content_id),
  FK(content_type_id,content_id), CHECK (content_type_id = 'article_type_id'));

VIDEO(content_id, content_type_id,
  other attributes,
  PK(content_type_id,content_id),
  FK(content_type_id,content_id), CHECK (content_type_id = 'video_type_id'));

etc.

UNIQUE(content_type_id,content_id) seems redundant, but it's used to enforce storing of the sub-types in proper table, and only once. CHECK constraint restricts proper content type in each of detail tables. As far as I remember, Mysql still doesn't have unique CHECK constraints, but you can use enum to emulate them.


mySQL efficiency with inner join queries

Based (only) on your query i think the better query could be :

 SELECT table_a.record_id from table_a
 INNER JOIN table_b ON (table_a.record_id = table_b.cat_id)
 WHERE table_a.customer_id = "654" and table_b.customer_id= "654"
 GROUP BY table_a.record_id
 HAVING COUNT(*) > 1;

the correct index could be in   table a  only this:

KEY cust_and_rec (customer_id, record_id)

in table b only

KEY cust (custmer_id)

In the index you show in your question seem rc, test and test2 are the same and not  so selective like the inverse (customer_id is the most selective value in your query )

index rc on table b seem not useful for your purpose.


Postgres - Update multiple rows in the same query with limit

You can use a derived table or cte to find one row (the latest) per column_b:

with upd as
( select distinct on (t.column_b)
      t.pk, c.column_a              -- pk : the primary key column(s)
  from test as t
    join
      (values
         ('123', 1),
         ('345', 2)
      ) as c (column_b, column_a)
    on c.column_b = t.column_b
  order by t.column_b, t.date desc
)
update test as t
set column_a = upd.column_a
from upd
where upd.pk = t.pk ;


How to Configure Always Encrypted using PowerShell

You can configure Always Encrypted using PowerShell. This is documented in Microsoft's Docs page.


Is it possible to determine mysql pre-allocated memory footprint

Yes, and you absolutely should! Don't set MySQL to have a potential maximum use more than about 80% of available memory.

MySQL has two types of buffers, global and per-connection. Maximum possible memory use is: global buffers + (connection buffers * max connections).

The mysqltuner.pl script will calculate this for you (http://mysqltuner.com/). Here are the values this script uses for calculating:

Global buffers:

key_buffer_size
max_tmp_table_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size
query_cache_size

Per-connection buffers:

read_buffer_size
read_rnd_buffer_size
sort_buffer_size
thread_stack
join_buffer_size

You can find how much memory is currently used from your Operating System.


Add running count of specific state changes

Two levels of window functions.
To keep the code short, you can combine the aggregate FILTER clause with the outer window function:

SELECT ts, status
     , 1 + count(*) FILTER (WHERE previous_status = 's' AND status <> 's')
                    OVER (ORDER BY ts) AS flag
FROM (
   SELECT *, lag(status) OVER (ORDER BY ts) AS previous_status
   FROM   tbl
   ) sub;

db<>fiddle here

Assuming current Postgres and all columns NOT NULL.
Related:

Return counts for multiple ranges in a single SELECT statement
Grouping or Window


Best table schema to store daily hits for many items?

If performance of the site is a concern, then I would suggest going a third route.

Each time a page is viewed, insert a record into a table which is like:

  ItemId | TimeStamp

Then, each night (or whenever your site is least utilised), have a process that counts the occurences of ItemId and builds a value for a table such as you have in Option 1.

Doing it this way will be much more efficient, as an insert is ridiculously cheap compared to trying to update on a table that has 36,500,000 rows and avoids issues with locking.

I'd also strongly suggest you hold this data not on your main database, so you can query it as you like without a performace hit; and consider splitting the tables into weekly or monthly reports to cut down on the amount of data that needs to be scrolled through.


Oracle error "too many values", must be solved with nested SELECTs

Using a join?

SELECT
  minion.boss,
  emp.name,
  minion.minion
FROM acg_employees emp
JOIN
    (SELECT b_id as boss,
                 LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
    FROM (SELECT b_id,
                 e_id,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
          FROM acg_boss_of)
    GROUP BY b_id
    CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
    START WITH curr = 1) minion
ON (minion.boss=emp.e_id)


Is it possible to have one Sql-Server cluster node in multiple Sql-Server clusters?

Is it possible to create FAILOVER CLUSTER A with Node01 (active) and
  Node02 (passive) and FAILOVER CLUSTER B with the same Node02 (passive)
  and Node03 (active)?

No you cannot. This is enforced by Windows Server Failover Clustering.

  I realized that SQL Server instances on passive nodes are not charged,
  only the OS. So sharing a node for more than one passive Sql-Instance
  would make sense here. I know, once cluster A and cluster B have an
  issue I am nipped...

There is nothing stopping you from creating a 3-node cluster (all nodes in the same cluster) and installing two SQL Server FCIs. You'll get roughly the same results.


Well, you can check how often your plans are reused. After that, you may want to decide to switch to optimize for ad-hoc workloads. Those flag will only save a plan-stub instead of a full plan and will do a fast compile of the stub if there is a query which uses the same stub. Those option isn't enabled by default (due to the fact that it's a newer Serverfeature).

Take a look at this query for example:

DECLARE @AdHocSizeInMB DECIMAL(14, 2)
    ,@TotalSizeInMB DECIMAL(14, 2)
    ,@ObjType NVARCHAR(34)

SELECT @AdHocSizeInMB = SUM(CAST((
                CASE
                    WHEN usecounts = 1
                        AND LOWER(objtype) = 'adhoc'
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(14, 2))) / 1048576
    ,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
FROM sys.dm_exec_cached_plans

SELECT 'SQL Server Configuration' AS GROUP_TYPE
    ,' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS
    ,' ' + CASE
        WHEN @AdHocSizeInMB > 200
            OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
            THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
        ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
        END + ' ' AS RECOMMENDATIONS

It should give you an overview if you would benefit from it or not.

You can enable it using this:

sp_configure N'show advanced options',1
GO
reconfigure
GO
sp_configure N'optimize for ad hoc workloads',1
GO
sp_configure N'show advanced options',1
GO
reconfigure
GO

Hopefully this will help you.


To know what performance you will have you have to test on your data. I obviously can't do that so I made up my own xml data to test the two queries you have in this question.

Create a table with 5000 rows containing an XML document of 9475 characters in 415 nodes:

create table T
(
  ID int identity primary key,
  XMLCol xml not null
)

declare @X xml =
(
  select top 100 *
  from master..spt_values
  for xml path('row'), root('root'), type
)

insert into T(XMLCol)
select top(5000) @X
from master..spt_values as m1, master..spt_values as m2

Execute the queries to search for a value that is present in the first node (rpc) and another value that is present in the last node (SERVER ROLE).

select count(*)
from T
where charindex('rpc',cast(xmlcol as varchar(max))) > 0

select count(*)
from T
where XMLCol.exist('//*/text()[contains(., "rpc")]') = 1

select count(*)
from T
where charindex('SERVER ROLE',cast(xmlcol as varchar(max))) > 0

select count(*)
from T
where XMLCol.exist('//*/text()[contains(., "SERVER ROLE")]') = 1

The IO for the different queries is the same so here is the output from using set statistics time on

Search for rpc with charindex:

 SQL Server Execution Times:
   CPU time = 1435 ms,  elapsed time = 1434 ms.

Search for rpc with xml exist

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 68 ms.

Search for SERVER ROLE with charindex

 SQL Server Execution Times:
   CPU time = 7316 ms,  elapsed time = 7321 ms.

Search for SERVER ROLE with xml exist

 SQL Server Execution Times:
   CPU time = 3245 ms,  elapsed time = 3244 ms.

Clear winner in both cases is the XML query. It does a better job of scanning the entire XML and it does a much better job of early termination when the search string is found.

This is true for the test data above using SQL Server 2012. It could be different for you with your data and your search strings. You have to test to know what is best for you.

NOTE: As stated in the answer to your other question, the two queries above will not return the same result bucause the XML query only search node values where the charindex query searches the entire XML document including nodenames and markup.


Oracle 11gR2 expdp table converted to CSV from Linux

No its not possible with expdp, you need to use utl_file to solve this

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:88212348059


Can statistics affect the query logic instead of performance?

At least I think it is possible that the procedure gets a different plan and that way it doesn't touch the rows that have "invalid" data, for example numbers or dates stored as varchar that aren't correct and then conversion logic in the procedure.

For example if you're fetching with both date range and customer code, and some of the data for that customer causes the error, but is outside the date range -- so, if the plan uses date range, it works ok, but if it fetches all the rows for the customer, then does something related to the conversion and then last compares the date range, then it fails.

I haven't tested this, but I would assume that could happen, but of course the data / procedure should be fixed.


Log Shipping Fails with CONTINUE_AFTER_ERROR

Do a FULL backup of the primary DB (now or whenever), copy that backup file over to the secondary server, then restore that to the secondary DB in standby mode.

Afterwards, see if your LS jobs run successfully on secondary once you get enough fresh logs to apply to it.


Can I copy installed mysql from ubuntu to centos7 directly without installation?

The simple answer is NO. A more realistic answer would be not easily. If you wanted to do this "the wrong way", you would do something like this:

Install the exact same version of MySQL on Ubuntu as you are running on CentOS
Copy the entire contents of your /var/lib/mysql directory from CentOS to Ubuntu
Modify all of the configuration files to ensure they are using Ubuntu's directory paths rather than those used by CentOS
Cross your fingers
Start MySQL and hope everything magically works

This is a recipe for disaster as it almost always results in more work than doing it "a better way". Here is how I migrate MySQL data from one server to another:

Note: All of the following commands work on Ubuntu and MySQL.

Install MySQL on the target server:
sudo apt update
sudo apt install mysql-server

Configure MySQL:
sudo mysql_secure_installation

You will be walked through the preliminary setup for MySQL, including the option to set a password for the root account. Do note that even though you've set a password for the root MySQL user, the account is not configured to authenticate with a password when connecting to the MySQL shell. More on this later.

Create your MySQL administration account:
mysql> CREATE USER 'huangxy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'superSecretPassword!123';

If you will be connecting to MySQL remotely, you will need to replace localhost with %.
Important: Do not use root for all of your administration activities. It is better if you have an account specific to you, even if it has full access to the whole system. root should be used only when absolutely necessary, such as when configuring replication or fixing corrupted databases.

Give your account full access:
mysql> GRANT ALL ON *.* TO 'huangxy'@'localhost' WITH GRANT OPTION;

Ensure the privileges are written:
mysql> FLUSH PRIVILEGES;

Exit MySQL:
mysql> exit;

With this done, now you can create a backup of the MySQL database(s) on your CentOS server. You will need two connections to do this: one to MySQL, and the other to the shell where you can use mysqldump.

Lock MySQL, preventing any updates:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

This is done to ensure that nothing interferes with the database export.
Dump the databases to disk:
mysqldump -u root -p --routines --triggers --add-drop-database --all-databases --all-databases > full_backup.sql

If the version of MySQL on CentOS is 8.0.2 or above, you can also migrate your Users along with permissions:
mysqldump -u root -p mysql user --no-create-info > user_backup.sql

Note: If you are using any version of MySQL lower than 8.0.2, you cannot migrate your users with this command. Instead they will need to be created manually on the new server. This is because the mysql.user table has seen a number of schema changes over the years.
If people still need access to the previous system, you can now unlock the databases:
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

Be sure to advise people that any changes to data in the old system will not be reflected in the new system.
Copy the database dumps to the new server
Import the databases:
sudo mysql < full_backup.sql

Note: You do not need to specify -u or enter a password here because it is implied that a person with sudo access on an Ubuntu system has full permissions, therefore another password for MySQL is unnecessary.
If applicable, import the user accounts:
sudo mysql mysql < user_backup.sql

Note: Because the --no-create-info option was used when writing the user data to disk, there is no risk of the table being unnecessarily dropped. If there are any schema differences, an error will be reported and you will need to manually resolve the issues.
Connect to MySQL and check that your databases are intact.
Restart MySQL just to clear the buffers:
sudo service mysql restart

So long as there are no issues with the import process, a successful restart will let you know that you're pretty much good to go.

Next Steps ...
Nothing has been said about the various configuration files that MySQL uses for its settings, and that's because it is impossible for me to offer a properly comprehensive outline of what to do. I do not know anything about your server hardware, the databases, or how the systems are used. What I can recommend is this:

Tweak the basic InnoDB settings for your hardware
Monitor the system and make incremental changes as you see fit

I would not recommend copy/pasting the .cnf files from CentOS to Ubuntu, as this would be suboptimal.
While this answer is rather long and may look complicated, it's generally the most reliable way in my experience to move from one server and/or OS to another.


New SQL Server with slow Index Spool (Eager Spool) and old server used index

It's possible having double the amount of RAM in the new server is causing the query optimizer to choose a plan that performs worse than expected.

You could try using DBCC OPTIMIZER_WHATIF1 to see what happens if you have 20 cores and only 32 GB of RAM, like the old system.  I wrote a blog post showing how that works on sqlserverscience.com.

To see the effect on plan choice with 32GB of RAM, you could use this command:

dbcc optimizer_whatif ('MemoryMBs', 32768);

You'll need to evict the plan for the query from the plan cache, or force a new plan to be compiled using WITH RECOMPILE or OPTION (RECOMPILE) to see if that causes SQL Server to choose the "better" plan.

If you see a better plan after using dbcc optimizer_whatif, you might consider either re-factoring the query to allow the optimizer to make a better choice, or you could create a plan guide.

You may be interested in this question, and the answers about the differences in execution plans on two servers with different resources.

FYI for future visitors: the question states that most of the databases have been kept at compatibility level 100; this rules out an array of potential issues related to cardinality estimator changes in SQL Server 2014+.

1 - be aware, this is an unsupported DBCC command, and should only be used for testing purposes.