Database with insert data every 2 minutes I have a site to develop into PHP and mysql where I take, with a cron, every two minutes 10 xml from a server parse each and insert data into my database.
Well this data are simple string (maximum 10) and about 20 field (string(10)).
Every two minutes I insert 10 records with 20 fields (the number of xml can be incremented).

Into this database I have other tables but the biggest table is where I insert every two minutes some records.

I want to know if is bettere to separate database by year like: database_2013, database_2014... create database automatically when the year change beacuse I dn't want that this table of data can generate every year Gb of database and ten years from now I have a very big database where to make query is very slow.

One database (isn't a problem a lot of Gb for a database) or more database divided by years?

This is my table that I implement:

CREATE TABLE IF NOT EXISTS `station` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `station_id` int(11) unsigned NOT NULL,
  `dewpoint` varchar(5) NOT NULL,
  `heat_index` varchar(10) NOT NULL,
  `pressure` varchar(10) NOT NULL,
  `relative_humidity` varchar(5) NOT NULL,
  `temperature` varchar(5) NOT NULL,
  `wind_degrees` varchar(10) NOT NULL,
  `wind_direction` varchar(10) NOT NULL,
  `wind_speed` varchar(5) NOT NULL,
  `windchill` varchar(10) NOT NULL,
  `rain_rate_in_per_hour` varchar(10) NOT NULL,
  `rain_rate_hour_high_in_per_hour` varchar(10) NOT NULL,
  `rain_storm_in` varchar(10) NOT NULL,
  `leaf_wetness` varchar(2) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Inserting 10 rows each 2 minutes will result in (24*60/2)*10 rows per day (7200) -> that is not a large value to worry about. Also, it's great that you think on the "future - 10 years", but don't lose time with premature optimization.

If your only concern it's about this table where you're inserting data every 2 mins, there's no point in creating additional databases (one per year), so let's stick with this table (Table_A).

Now, because this table will slowly increase in time, and your want your queries to run fast, you have plenty of options:

you can partition the table by certain criteria. Because your using the table for a "weather station", and your data is time series values, your best option would be to partition by [station_id] then by [created]
Create a Table_A_Archive, where you can move data that would be to old to keep in Table_A.
How long do you intend to keep data in Table_A ? would it make sense to delete old rows that become obsolete for you application

... And so on.

Best Bet: Partitioning the existing table by [station_id] and [created],  you will have "A" partitions for each station, "B" partitions for each month, and a total of AxB posible number of partitions. Once you partition Table_A, do the same thing for Table_A_Archive, and on the end of each year, move the data from Table_A to Table_A_Archive.

** IMPORTANT:** After you make the partitioning schema, keep in mind that all queries should have in the WHERE clause the conditions necesarly so that the query will hit as little partitions as posible.

Ex.

Select AVG(pressure)
FROM Table_A
WHERE station_id = 123
AND created between '2013-01-01 00:00:00' AND '2013-02-01 00:00:00'

.

The query above will only touch the partition "123" and the 2 sub partitions for months 1 and 2


How does Automatic Seeding work - Always On Availability Group I found only these two statements from BOL regarding this Initial Data Synchronization method:
Link 1

Automatic seeding uses the log stream transport to stream the backup
using VDI to the secondary replica for each database of the
availability group using the configured endpoints.

Link 2

During automatic seeding, SQL Server performs a backup over the
network for initialization.

Looking into SQL Server Error logs of both primary and secondary replicas, these two logs caught my attention:
On primary replica

On secondary replica

My question is related to the working nature of automatic seeding, namely where does taken backup is stored before restoring on the secondary replica?

As you noted, the docs refer to "log stream transport" when performing automatic seeding. Similar to the way that you can stream movies to your device without downloading a specific file, automatic seeding streams data to the secondary without storing an actual backup file.
While the technical implementation details aren't exactly this, it's essentially taking a backup, but rather than sending it to a storage device or file share, it sends it to the secondary server. Rather than writing a file, it immediately begins to restore to the secondary server. The pseudo-backup is buffered and restored in a single step, without a literal backup being written to file.


MySQL GTID replication, slave read executed binlogs, failed to get the latest binlog MySQL VERSION: Server version:        5.7.19-17-log Source distribution
Replication mode: mysql multi source replication: MySQL-C works as slave for MySQL-A(source1) and MySQL-B(source2);  real  topology as follows:

current state:

  channel 1( A-C),  both IO and SQL_THREAD works well, relay-log from A (parsed by mysqlbinlog), all gtids in relay-log are executed in Executed_Gtid_Set.
  channel 2( B-C), works fine.

One workaround for this condition is finding corresponding binlog-file and binlog-pos according to the Executed_Gtid_Set from show slave status.  Change GTID-Based replication to File-Postion replication.


mariadb takes too much load though having sufficient RAM I am facing more CPU usage for my app server. Server configuration is as below.

1> CPU: Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz 8 core
2> RAM: 32 GB
3> 600 GB SSD NVRAM
4> Mariadb version 10.3.17.
5> OS: Debian 9

I have run mysql tuning script to optimize server performance and add some parameter in my.cnf. But yet it's not creating big impact.

I can see that RAM used very less compare to CPU. How can I balance in both and improve server performance?

Here is my tuner script output tunner.
Here is current my.cnf output on my.cnf

With your limited information posted, suggestions to consider for your my.cnf [mysqld] section

max_connections=512  # from 2000 because max_used_connections only 213
join_buffer_size=256K  # from 20M to reduce RAM required per connection
read_buffer_size=256K  # from 2M to reduce RAM required per connection
read_rnd_buffer_size=128K  # from 8M to reduce RAM required per connection

These changes will significantly reduce your CPU busy and provide better performance.


No One Can Login SQL Server Instance Even Though Its Services Are Running I have an issue. Today, our developers had reported to me a problem that they couldn't reach SQL Server database server several hours ago. I have checked the services and i saw that they were in running state but it was not reachable remotely. And of course, i have checked the TCP/IP connection settings. They are enabled and settings are correct. What can cause such an issue? Have you ever experienced like this situation before? The error code that i get when i try to connect to server is :

ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or
was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: SQL
Network Interfaces, error: 26 - Error Locating Server/Instance
Specified) (Microsoft SQL Server, Error: -1)

This server is development server. There are 5 different SQL Server instances. And, no one can reach them. By the way, i can connect to the instances on the cmd screen. Can you help me?
The instance is running on a Windows Server Core machine, and I can connect to the instance locally from the server itself.

That seems to be a firewall issue.
Open the Windows Defender Firewall with Advanced Security applet from a client machine on the same network, connect to the server, and see if you can enable the SQL Server or or TCP port. By default the port is 1433, but it might be some other port. You can use SQL Server Configuration Manager to determine which port the server is running on.
You might need to enable Remote Management of the Server Core machine.  Connect to it via RDP, open a command-prompt, and run sconfig.  There is an option to enable remote management from there.


Select user activity over past months grouped by cohorts In MySQL 5.7, I have a table user that contains the columns registered and lastLogin. My goal is to group the users by calendar months of registration and then select for each group how many of these users were still active after 1 month, after 2 months and so on.

20,000 users have registered in June 2015, from these 10,000 have been active in July 2015 or later, 5,000 have been active in August 2015 or later, etc. Continue this output for all users registered in the past months until today.

I understand how I can select any of the activity months by hand, for example select all users that were active one calendar month after their registration:

SELECT year(registered), month(registered), count(id)
FROM user
WHERE lastLogin >= DATE_ADD(DATE_SUB(date(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY), INTERVAL 1 MONTH)
GROUP BY year(registered), month(registered)

This would give me the number of users who were active one month after their registration or later.

But in addition I would also like to select the number of users who were active 2 months after their registration, 3 months after their registration and so on up to the current month.

The results would be something like this (not necessarily look like this, but it should get the idea across):

Columns:      Cohort |    total | 1 month | 2months | 3months | ... | month n
             07/2015 |   20,000 | 10,100  | 5,500   | 2,600   | ... | 150
             08/2015 |   21,000 | 9,800   | 4,300   | 1,300   | ... | 180
             09/2015 |   19,700 | 11,400  | 6,200   | 3,500   | ... | 200
    ...
             08/2017 |   25,300 |  13,000
             09/2017 |   12,500

How do I change the query to select all months of activity instead of a specific month?

With the help of Rick to look for pivot table I was able to create the final query as described in this post: https://stackoverflow.com/questions/7674786/mysql-pivot-table

Query for a 6 months analysis:

SELECT
    YEAR(registered), MONTH(registered), COUNT(*) as total,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 1 MONTH) AS month1,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 2 MONTH) AS month2,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 3 MONTH) AS month3,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 4 MONTH) AS month4,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 5 MONTH) AS month5,
    SUM(lastLogin >= DATE(registered) - INTERVAL DAYOFMONTH(registered) - 1 DAY + INTERVAL 6 MONTH) AS month6
FROM
    user
WHERE
    registered >= "2015-01-01"
GROUP BY YEAR(registered) , MONTH(registered)

The part DATE_SUB(DATE(registered), INTERVAL DAYOFMONTH(registered) - 1 DAY) will return the first of a month to make sure the analysis is based on calendar months. I am not sure if it could be simplified. I was looking at STR_TO_DATE(LEFT(registered, 7), "%y-%m") but I was reading working on Strings is slower and should be avoided. If someone knows a better / cleaner way of doing that, please comment and I will include it in the answer.

Here is some exemplary output of the query:

  year, month, total , month1, month2, month3, month4, month5, month6
'2015',  '1', '14776', '4302', '3225', '2827', '2547', '2330', '2163'
'2015',  '2', '12162', '3859', '2866', '2465', '2259', '2046', '1890'
'2015',  '3', '10770', '3831', '2841', '2507', '2288', '2135', '1987'
'2015',  '4', '15685', '3731', '2641', '2273', '2045', '1872', '1730'
'2015',  '5', '18130', '3686', '2403', '1993', '1744', '1528', '1363'


MySQL cpu at 400% on c5.xlarge box Box is running only mysql and CPU is at 400% constantly. I can't seem to find anything that would be causing it. I've changed a few values in my.cnf based on some research, but had no luck reducing the CPU usage.

my.cnf as follows:

[mysqld]
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
#bind-address=127.0.0.1
skip-name-resolve=1
skip-external-locking
key_buffer_size = 64M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 64M
thread_cache_size = 40
join_buffer_size = 1M
query_cache_limit=0
innodb_lru_scan_depth=100
max_connections = 100
open_files_limit=30000
# Try number of CPU's x 2 for thread_concurrency
thread_concurrency = 16

Remove these for a start, they are almost certainly counterproductive:

table_open_cache = 256
join_buffer_size = 1M
thread_concurrency = 16

There is also a conspicuous absence of innodb_buffer_pool_size being set.

Configuration isn't going to be the root cause of your problem, though - you need to identify what queries are eating all of your CPU and address those specifically, either by rewriting them or by making sure that appropriate indexes for them exist.


Determine last time a mysql table was accessed (i.e. SELECT'ed) In my mysql DB schema there are old tables that are no longer used.
I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not UPDATE'ed: that would be easy).

There's a way of getting the general_log and parsing the gigantic file, but it's not elegant. I'm wondering if there's a tool or technique to learn and record time as each table gets accessed/read. Thanks.

My environment is 5.5 and 5.6

It is not possible to view accessed information unless and untill General Query Log is enabled in Mysql
you can enable general query log in mysql 5.5 as follows, which would help you in future
Here's how in Dhanesh Mane's words:

OS : Ubuntu 12.04.1 LTS
MySQL version : 5.5.28
Locate and edit my.cnf file. We need to change two variables in this file to enable general query log.

sudo gedit /etc/mysql/my.cnf
Locate this variable in the file : general_log_file
It must have some default value, this variable is nothing but the path of log file.
The default value is "/var/log/mysql/mysql.log", you can change it as per your wish.
Now locate variable "general_log", and set it to 1. The means turning on the log system.
Now restart the MySQL server by running : sudo service mysql restart
now if you open the mysql.log file, you can see all SQL queries in it

Hope this helps!


Is it possible today to replicate 30 TB from multiple databases to PostgreSQL close to real-time? If you had 20 databases spread across 20 servers, could you replicate them all into a single PostgreSQL instance? And can this be done on a server costing less than $100,000?

The databases total 30 TB on disk. There are ~30,000 tables. The maximum number of rows in a single table is 1.2 billion. The number of rows changed per second is modest (100s or less).

Some of the servers run PostgreSQL, some MySQL. The versions are different, but they're relatively current (PostgreSQL >= 9.1, MySQL >= 5.6).

How fast is the network connection?  Do some math -- 30TB through a single pipe will take how many days?  That's just to get the data started.  Then redo the calculation based on how fast the data grows.

There is some amount of overhead; I guestimate that the initial 30TB would take a few weeks.  You have not provided numbers for how much traffic per hour in steady state.  You can estimate that by watching the  size of the binlogs on a representative server, then multiply by 20.

Once you have the 30TB on a single server, have you figured out what you can do with it?  Again, some math would compute how many days or weeks it would take to do something with all that data.

Oh, I forgot about the overhead that you might incur to get the index(es) built.  Are you using SSDs?


How to organise and understand a terribly modelled database I have recently joined a company where there are hundreds of schemas and thousands of tables. All the schemas we have are from other vendors who have allowed us access to their database. The entire database is poorly documented (little to no documentation). Vendors also don't care for documentation, hence they don't provide us with any documentation.
The other employees themselves find majority of their time figuring out what the data represents and if the data is current. There are numerous front end tools (not built by our company) which allow us to pull data (which is correct and relevant). However, then the issue becomes that we don't know the schemas, tables or columns the front end is using to pull the data.
I cannot create new tables as the tables are owned by 3rd parties who have allowed us access to their database. How can I go about documentation and how do I figure out what schemas, tables and columns front end tools are using to pull data.
Tables don't even have foreign keys to figure out how they are linking to other tables. For example in tableA there may be a column called car_id, but in tableB there is a column called cid (which is the car_id). They both link together.

To add on to the first two comments on your post, yes unfortunately this is the case in a lot of Vendor systems. Reverse engineering, and normalizing their less than optimal schemas has been a fairly common task I've dealt with for multiple Vendor systems at multiple different companies I've worked for. There's a couple things I recommend to make it slightly easier on yourself either now or at least in the future:

Documenting your progression is important. As you find out the relations between entities, I'd recommend building out a database diagram for them, and then expanding on that diagram as you learn about other relevant entity relations. Of course in a large scale system, a single database diagram can become unwieldly, so you might want to break it up into multiple diagrams for each subject matter in the system. E.g. ERP systems typically have a multitude of modules that span different subject matters between Finance, Sales, Production, etc and in such a case I'd recommend at least a separate database diagram for each module.

Familiarize yourself with a schema search tool, such as the Find DB Object feature of the Oracle SQL Developer or the search features of PL SQL Developer as mentioned in this StackOverflow answer. (Microsoft SQL Server has the luxury of RedGate's SQL Search tool, but unfortunately I don't think RedGate offers an equivalent for Oracle in their tool belt.) This will be helpful in understanding all the places an entity is referenced and how it's used.

As much of a pain as it can be working with the Vendor and their documentation, if you have a technical contact who can at least answer the question if their application itself (or if they have a tool or database dictionary) that exposes the entity names of the fields behind the UI then that could make your job with reverse engineering their system a lot easier. Surprisingly this is fairly common practice that I've seen among many Vendor systems I've encountered. In some cases it was a dedicated shortcut key while focused on the field in the UI, sometimes it was a matter of right clicking the field and it was an option in the context menu, other times it was a separate tool bundled with their software (which was also used for customizations), and I've even seen it purely in the database in a dedicated schema / database dictionary. Also you may need to be a super user of sorts to be able to access that information. Either way, the Vendor should be able to answer that question for you.

Best of luck!


How to find the last error codes and messages of failed statements executed by a user? Hopefully, a program that connects to a Oracle DB does sufficient logging of errors on its own, e.g. when doing a bulk insert the error reporting of a program could produce something like this:

ORA-24381: error(s) in array DML
statement: insert into BARTABLE(...) ....
row 5: code=12096, message=ORA-12096: error in materialized view log on "FOO"."BARTABLE"
  ORA-12899: value too large for column "FOO"."MLOG$_BARTABLE"."XCOL"
    (actual: 50, maximum: 100)

But sometimes it is not possible to change all existing DB client programs.

For that usecase - where to look up the same information inside the database?

Perhaps there are some 'virtual' oracle tables/views that one can use to get the last x error messages/codes for a certain user ...

Ideally also including the corresponding SQL statement.

You can create an AFTER SERVERERROR trigger and log the relevant information to a table.

Here is an example: http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html


How to merge multiple rows in one column with hierarchy Postgresql 12? I have a table like this to save the catalog of 90 services:
-------------------------------------------------------------------------------------
|  id  |  service     | subservice      | description                      | cost
-------------------------------------------------------------------------------------
| 2044 |   Tests      |   Tests         | Calcium                          | 50.00
| 1385 |   Cardiology |   Cardioversion | Electric Cardioversion programmed| 200.00
| 7000 |   Cardiology |  Ecocardiography| Chest Ultrasound                 | 100.00
-------------------------------------------------------------------------------------

I need to change the table structure in order to have the three levels (service, subservice and description) in the same column with its own id and pointing to a new column with the level's number. Namely something like this (note that the id's are something that I made up):
-------------------------------------------------------------------------------------
|  id  | description  | parent_id   |  cost
-------------------------------------------------------------------------------------
| 1    |   Tests      |       NULL  | 0.00
| 2    |   Tests      |         1   | 0.00
| 2044 |   Calcium    |         2   | 50.00
-------------------------------------------------------------------------------------

I'm working on Postgresql 12. I have created the new column parent_id and I was trying to do this sequence:
CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

ALTER TABLE catalog
ALTER COLUMN parent_id SET DEFAULT nextval('seq_parent_id')

Can anyone please give a rough idea how to transform the structure of the table?

Its a bit lengthy,
So the steps are:

Old Table

create table test (id int, service varchar, subservice varchar, description varchar, cost decimal(10,2));

New Table

create table newtest(id int, description varchar, parent_id int, cost decimal(10,2));

Create a sequence as you have mentioned in your question

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

Now Run below query

with cte(id, description, parent_id,cost) as
(select nextval('seq_parent_id'), service, null::bigint,0::decimal
from test group by 2
),
cte1(id, description, parent_id,cost) as
(select nextval('seq_parent_id'), subservice, t2.id,0::decimal
from test t1
inner join cte t2 on t1.service=t2.description and t2.parent_id is null group by 2,3
)

insert into newtest

select * from cte
union all
select * from cte1
union all
select t1.id,t1.description,t3.id,t1.cost from test t1
inner join cte t2 on t1.service=t2.description and t2.parent_id is null
inner join cte1 t3 on t1.subservice=t3.description and t3.parent_id=t2.id

DEMO
Explanation of above query is demonstrated in this fiddle
NOTE: Before running the query please ensure that counter value will not cross the already existing ID. If newly inserted rows are more than minimum existing count then set the start of sequence max(id)+1 before running this query.


How to store LOB-image or PDF in Postgresql and what is the impact of having in Postgresql? Please guide me to store LOB-image or PDF in Postgresql and let me know what is the impact of storing these types of objects on Postgresql database.

Any help will be appreciated.

BYTEA is a good choice here.

The BYTEA data type allows storage of binary strings.

It stores a LOB within the table, respectively using TOAST.
It is thus limited to 1 GB
The storage is octal and allows non printable characters (in contrast to character strings which don't).
The input/output format is HEX (as of PostgreSQL 9.0).
Notes:

BYTEA comes close to the SQL standard binary string type 'BLOB'. The functions and operators provided by BYTEA are mostly the same, while HEX input format of BYTEA is different.
BYTEA is slower for lengths >20 MB than the LO facility (it has no random accees).

More details can be found here


Differences between SQL Server 2005 and SQL Server 2008 deadlock handling We recently upgraded a server to SQL Server 2008 R2. The server had been running SQL Server 2005. We are using

Transactional Replication
Management Data Warehouse and Data Collector on the Production server

We are starting to see deadlock errors in the application log. This did not happen in SQL 2005.

On the reporting database I can replicate a deadlock using an "ad-hoc" query with an inner join and table scan on an active table. However, I can run reporting procs that take minutes to complete with no problems.

Is sql 2008 more sensitive to deadlocks to sql 2005? I am going to start undoing some of the changes we have implemented and tested. Before I go that route I wanted to see if anyone else has noticed increased deadlocks when upgrading to 2008 R2.

"Sensitivity to deadlocks" is not version-specific.  It is related to concurrency and contention.  Please see this BOL reference on more information regarding deadlocking.

There are certain things that influence deadlocking, such as transaction isolation levels, and lock escalation.  These are configurable settings that impact if and when deadlocking can/will happen in certain situations.

It will take a bit of investigation on your part to find out why you're seeing more deadlocking happening.  But to answer your question, it isn't specific to the version of SQL Server.

As for the deadlock process, when SQL Server runs into a deadlock it will choose a victim so that the unresolvable concurrency situation can complete.


Best Approach to storing chat history? I'm building a chat application (Yes, another one :P) and am really curious about the best way to store a chat's history(String username, String Message, Time, and possibly string channel depending on how I end up setting it up.

Since I'm using PostgreSQL anyway, I was tempted for going with it for chat history. However, I don't see a sane approach for using it to do this without generating a new row for every message, something obviously not very scale-able.

I know some MongoDB and was thinking that using a json array for each channel and storing the history in that would work well.

Is using two entirely databases in the same application a terrible idea?

Are there any other alternatives I should take a look at? (I'm open to pretty much anything)

Is there a popular PostgreSQL way of doing this? I took a look at a JSON columns, but it seemed fairly new and limited at the moment.

You can use hstore for use EAV. hstore is available from pgsql 8.4. If you have installed json you can then use function hstore_to_json(hstore)

select hstore_to_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4')

this will output:

{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}


How to trim a string by finding multiple words I want to trim a string if it contains certain words or characters. This is just part of a big stored procedure.

I want if v_selection_name contains any of those characters .eg: ' (RES)' or ' (R)' remove these characters.

For example :

`Micheal Jackson (RES)`    => SHOULD BE =>  `Micheal Jackson`

The way I currently do it is this and it is working:

if v_sport_id IN (1, 121225) then
    -- remove word reserved from selection name to make match possible
   SELECT TRIM(TRAILING ' (NEEDS SUPPLEMENTING)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' (RES)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' (R)' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' N/R' FROM v_selection_name) INTO v_selection_name;
   SELECT TRIM(TRAILING ' %' FROM v_selection_name) INTO v_selection_name;
end if;

Is there any other better way to do this?

The string will only ever contain one substring to be trimmed.

Does only one substring to be trimmed is contained in a field/variable value? Or there are values like 'something (R) (RES)' which must be trimmed twice?

Replacing multiple substrings is fairly easy to do with REPLACE commands inside of a CASE block.
I successfully tested the following command on MySQL 5.6:
select
    case
        when my_string like '% (RES)%' then replace(my_string, ' (RES)', '')
        when my_string like '% (R)%' then replace(my_string, ' (R)', '')
        else my_string
    end as my_string_without_characters
from
    sample_table;

Shameless plug: I tested this command across multiple databases and posted about it on my blog here: How to replace multiple substrings with SQL.


Incrementally Updated Image File Backup Multiple Locations With an Incrementally Updated Image File Backup the same script handles both the initial image backups, and the incremental backups that can be applied to the images to bring them up to date.  Is there a way to change a parameter or modify this script so that the image files get created in one location and the incrementals get created in another?

A partial solution is to do the inital image file backups, move them to the alternate location and then re-catalog them there.  The next run will then create the incremental backup in the original location, but update the image files in the new location.  The problem comes when a new datafile is created.  The backup will recognize that it doesn't have an image for it and create one, but in the same location as the incrementals.  Aside from repeating the move and re-catalog is there a way to have things happen as desired from the start?

If you're wondering why this would be useful, consider that the image backup volume could be snapped and cloned to a new volume on another server for fast refreshes of lower environments.  Having the incrementals and images in separate locations reduces the space requirements for the snap/clone.

Backup Command:

BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'MyImageBackup' DATABASE;

No, it is not possible to have this single backup command do the level 0 to a different location than the level 1.  It is possible to change the script to determine which files don't have level zero backups and do a separate backup just for those datafiles before running the existing command to the incremental location.


How can I design this database? So I have a ton of transaction logs in pipe-separated format spread across a few dozen files. Concatenated into one file, it's around 2.4 million lines.

Each transaction has a store number and a company name and ~30 fields total. Right now, I have all 2.4m records in ONE table with no indexes. A simple query takes like 30 seconds to run and it's honestly unacceptable to me.

The thing is, there are no unique pieces of data that I can create primary keys with. Each store can have multiple transactions and each transaction number can have multiple lines (multiple products, returns, etc). There are 300+ stores and so a table for each store doesn't really seem realistic since I would have to join all of these just to get the data I need. What can I do?

Indexes don't have to be unique. Primary keys do.

The purpose of a Primary Key is to uniquely identify a single row of data. If you don't have something that is naturally unique then add a column such as an identity column and define it as the primary key. It's standard practice, and will help you later if you need to update a row.

If the table is being queried for analysis or reporting a single table is fine. Do some analysis on the types of queries you are performing and add indexes on relevant key columns. Good indexes will significantly improve query performance.

For example if you are looking for results for a single store, by adding an index on the store id, you could exclude the other 299 stores from the select. This reduces IO and speeds up the query. If you have years worth of data but are only looking for things that happened in the last week then adding an index to a date column may be a big help.

Look at your queries and see if there fields you are regularly filtering on. Start with those.

Ensure the db has updated statistics for the table and see if performance improves.

The results you get will vary depending upon what you are trying to do. If you are trying to aggregate records (i.e total sales for each store) then the query may still have to read the whole table.

Below I've added a script with a basic test example.

USE [YOURDBNAME]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

-- Create Test Table.

CREATE TABLE [dbo].[rewards](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fname] [varchar](50) NULL,
    [lname] [varchar](50) NULL,
    [tstamp] [datetime] NULL,
    [card_id] [int] NULL

) ON [PRIMARY]

-- Add Primary key. This can be done at table creation. Or added after as shown below.

ALTER TABLE rewards ADD CONSTRAINT pk_rewards PRIMARY KEY NONCLUSTERED  ([id])

-- Add an index on columns that your query will use.

CREATE CLUSTERED INDEX [IDX01] ON [dbo].[rewards]
(   [card_id] ASC,
    [fname] ASC,
    [lname] ASC
) ON [PRIMARY]

-- Create some test data.

INSERT INTO [dbo].[rewards] VALUES ('Allan2', 'Aadvark2', getdate(), 2345) ;
INSERT INTO [dbo].[rewards] VALUES ('Billy2', 'Babo0n', getdate(), 2356) ;
INSERT INTO [dbo].[rewards] VALUES ('Chester2', 'Cheetah', getdate(), 2367) ;
INSERT INTO [dbo].[rewards] VALUES ('Doodoo', 'Dog', getdate(), 2376) ;
INSERT INTO [dbo].[rewards] VALUES ('Esme', 'Elephant', getdate(), 1235) ;
INSERT INTO [dbo].[rewards] VALUES ('Freddy', 'Fox', getdate(), 1239) ;

GO

-- run the following query repeatedly to generate volume.
INSERT INTO [dbo].[rewards]
           ([fname]
           ,[lname]
           ,[tstamp]
           ,[card_id])
     select [fname]
           ,[lname]
           ,[tstamp]
           ,[card_id]
           from [dbo].[rewards] ;

GO

SET STATISTICS TIME ON
GO

-- Test query. This shows that the card 1234 is use by 2 different customers.

Select r.card_id, count(*)
from (
    select card_id, fname, lname
    from [dbo].[rewards]
    group by card_id, fname, lname ) r

group by r.card_id
having count(*) > 1 ;

GO

SET STATISTICS TIME OFF

GO

-- I just tested this with 3,538,944 rows. And I get SQL Server Execution Times:  CPU time = 1904 ms,  elapsed time = 490 ms.
-- On repeated execution I can reliably get this under .5 of a second.

Your results may vary depending upon machine setup and load, But I think you should resonably expect to get your query down to ~1 sec or less.


Fast DELETE with double subquery on big table for dupe remover I have a table with 9million rows.
At some point in time i changed unique key from one column to two columns(for partitioning).

After this i created duplicates of my first column which is unwanted, so i try to delete all records that are duplicates

I asked about it (Find a longest sequence that doesn't have duplicates inside) and modified the code i received to make it MySql compliable.
Here's the code:

DELETE FROM t USING `mytable` AS t WHERE EXISTS
(SELECT *
    FROM (SELECT * FROM mytable) AS t2
    WHERE t2.Text = t.Text
    AND t2.Id < t.Id);

The Table looks like this:

+---+--------+----+----+
| Id| Text   |Col1|Col2|
+---+--------+----+----+
| 1 | adf    |1   |0   |
| 2 | qwer   |5   |1   |
+---+--------+----+----+

Problem is that the query takes over 2 hours now without sign of progress.

How i can optimize this query?

I'm running on 8 core/16GB Windows 10 machine.

I wonder if that works at all, MySQL always had a stupid restriction: You cannot delete from a table and select from the same table in a subquery :-)

So create a temporary table first with the result of

select min(id)
from mytable
group by text;

And use this in your delete

select from mytable
where id not in
 ( select id from mytemptable);


MAX(UPPER(range)) can't be accelerated with a GiST index on the range? To get the most recent session I need to SELECT MAX(UPPER(range)) AS end FROM sessions WHERE session_id = ?. I have a GiST index on (session_id, range). This query is extremely slow, taking nearly 30 seconds. I added a normal btree index on (session_id, UPPER(range)) and that fixed it down to under a millisecond, but it seems like the index on range should allow for queries on its upper bound. Is there a way to do this with just one index? Am I doing something wrong, either in the query or in the index? Should I use a different type of index other than GiST?

Any index can only be used for WHERE conditions of the form

<indexed expression> <operator> <constant>

Here <operator> must be of an operator class supported by the index, and <constant> must be constant for the duration of the index scan.

Other than that, indexes can speed up the following (where the first two are only possible with B-tree indexes):

max(<indexed expression>) and min(<indexed expression>)
ORDER BY <indexed expression>
functions returning boolean in the WHERE condition, if they have a support function.


Total Memory used by SQL Server (64 bit) My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named sys.dm_os_process_memor that gives you physical working set.

But I am not sure ...

  is there a DMV or perf counter that could tell me total (buffer pool +
  non buffer pool i.e. MemToLeave) physical memory used by SQL Server
  process for 64 bit SQL Server 2008 R2 and SQL 2012 running on 64 bit
  Windows OS?

I'm not sure about a single view for memory, but you can get the information from two queries.  The first (taken from Glenn Berry's DMV queries) leverages sys.dm_os_buffer_descriptors and will show you buffer pool by database:

SELECT
    DB_NAME(database_id) AS [Database Name]
    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second query I put together by looking at sys.dm_os_memory_cache_entries to show me non-buffer pool information:

select
    name
    ,sum(pages_allocated_count)/128.0 [Cache Size (MB)]
from sys.dm_os_memory_cache_entries
where pages_allocated_count > 0
group by name
order by sum(pages_allocated_count) desc

There are a host of DMVs related to memory, notated by dm_os_memory_*.  You can use these to investigate how memory is used by SQL Server on a variety of levels.


Why does this cursor produce results in the incorrect order? I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes.

The line ORDER BY 1,2,3 DESC; in the SQL below is designed to output DROP INDEX... statements before ALTER TABLE... statements in order to DROP the NONCLUSTERED index first and then add a CLUSTERED index.  I had to add the DESC after column 3 to get the DROP first followed by the ALTER.  This is backwards, unless I'm losing it!

DECLARE @Server nvarchar(max);
DECLARE @Database nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE @IndexType int;

SET @IndexType = 2; /*  1 is CLUSTERED, 2 is NONCLUSTERED */
SET @Server = 'MyServer';
SET @Database = 'MyDatabase';

SET @cmd = '
    DECLARE @cmd nvarchar(max);
    SET @cmd = ''
    SET NOCOUNT ON;
    DECLARE @IndexInfo TABLE (TableName nvarchar(255), IndexName nvarchar(255), IndexColumnName nvarchar(255));
    INSERT INTO @IndexInfo (TableName, IndexName, IndexColumnName)
    SELECT t.name AS TableName, i.name AS IndexName, c.name AS IndexColumnName /*, t.create_date, ic.*, c.* */
    FROM sys.tables t
        LEFT JOIN sys.indexes i ON t.object_id = i.object_id
        LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
        LEFT JOIN sys.columns c ON i.object_id = c.object_id and ic.column_id = c.column_id
    WHERE i.is_primary_key = 1
        AND i.type = ' + CAST(@IndexType as nvarchar(max)) + '
    ORDER BY t.create_date desc;
    DECLARE @t1 nvarchar(max);
    DECLARE @t2 nvarchar(max);
    DECLARE @t3 nvarchar(max);
    DECLARE @cmd nvarchar(max);
    DECLARE cur CURSOR FOR
    SELECT TableName, IndexName, 1 AS ExecOrder, ''''DROP INDEX '''' + IndexName + '''' ON '''' + TableName + '''';'''' FROM @IndexInfo I
    UNION ALL
    SELECT TableName, IndexName, 2 AS ExecOrder, ''''ALTER TABLE '''' + TableName + '''' ADD CONSTRAINT PK_'''' + TableName + ''''_'''' + IndexColumnName + '''' PRIMARY KEY CLUSTERED ('''' + IndexColumnName + '''')'''' + '''';'''' FROM @IndexInfo I
    ORDER BY 1,2,3 DESC;
    OPEN cur;
    FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @cmd;
        FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
    END
    CLOSE cur;
    DEALLOCATE cur;
    '';
    EXEC ' + @Server + '.' + @Database + '.sys.sp_executesql @cmd;
';
PRINT @cmd;

The PRINT statement inside the WHILE loop does execute in the order you expect, but the output is buffered before sys.sp_executesql returns.  Implementation details mean the buffered output is reversed.

Using RAISERROR (@cmd, 0, 1) WITH NOWAIT; instead of PRINT forces the buffer to flush after each call, giving you the results in the order you expect.  IIRC the NOWAIT trick only works per-row for the first 500 rows.  In any case, all this is undocumented stuff that could change at any time, so please don't rely on it - I only mention it to explain what you see.

The reversal does not occur if you replace the sp_executesql call with EXEC (@cmd) AT ' + @Server + ' though that does require a USE database command prefixed to @cmd and also the linked server needs to be enabled for RPC.  This is not a recommendation either, just showing the output reversal is a quirk of sp_executesql.


Archive Logs filling up Flash Recovery Area (FRA) space I got hired to fill a position of DBA that I wasn't quite ready to fill, and while the training is getting me there, I am running close to a potential issue.

My oracle 11g database's Flash Recovery Area (FRA) is getting very close to being full(2.5% left). It is in ARCHIVELOG mode since we do live backups(at least that is the reason I was told). We have cleared up all unneeded backups through the enterprise manager, but the Archive logs are still taking up over 50% of the allocated FRA space. Is it possible to delete some of the older archive logs to free up space? The server holds logs from several months ago still, and we don't really need backups that far back.

If I can delete them, what would be the best way to do this?

Oracle will automatically delete obsolete files. In general, you should not have to manually delete anything.

  An important part of RMAN maintenance is deleting backups that are no longer needed. If you configure a fast recovery area, then the database automatically deletes unneeded files in this area automatically

From Overview of RMAN Backup and Repository Maintenance

First, determine if you are running out of space. For example using the following query (sample output included):

SELECT * FROM V$RECOVERY_FILE_DEST;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------  ----------- ---------- ----------------- ---------------
/mydisk/rcva     5368709120 109240320             256000              28

If you are running out of space you have to either decrease your retention policy or increase the size of your FRA.


Secondary Database Replica in Reverting/InRecovery (Long time) Reposted from: https://www.sqlservercentral.com/forums/topic/secondary-database-replica-in-reverting-inrecovery-long-time

Hi,

One of my AG secondary replica has been in reverting/in recovery for more than a day. The database size is about 700GB. I was trying to suspend/remove the database from the AG so I could apply the differential and log backups to get it up-to-date, but SQL Server wouldn't allow me to do so because it's running a HADR_DB_COMMAND that is blocking me. Is there a way to bypass this? Or, would it be best to allow SQL Server to do its thing?

Thank you.

I waited it out for about two days and the replication is back up.

I guess it's best to let SQL Server do it and to monitor the counters pertaining to the undo (SQLSERVER:Database Replica -> Log Remaining For Undo and Total Log Requiring Undo).


Does it make sense to store a couple of Boolean values as array? I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)

Instead of having Boolean columns, I could have a single array column which contains an array of an enumerated custom datatype, and thereby store only the columns which are non-null.

I feel weird using arrays, but my coworker has pointed out to me that there isn't really a strong reason against using them, and we might actually see savings from using them since we aren't storing a bunch of empty columns.

Are there any downsides to using an array? Specifically: would they take up more space, take up more time to query, or prevent Postgres features (e.g. gin indexes) from being used?

TL;DR: Do not use an array. Use individual boolean columns.

Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres.

Do nullable columns occupy additional space in PostgreSQL?

A couple of boolean columns are also very cheap and efficient - nullable or not. Actually, just 5 boolean columns hardly matter for the row size at all - even if defined NOT NULL. 1 byte each, 5 bytes and no alignment restrictions. Hardly relevant. There are typically much more substantial things you can do to your row. (You should have provided your actual table definition.)

Configuring PostgreSQL for read performance

Arrays have an overhead of 24 bytes. Plus actual data. And handling is much less convenient and much more expensive:

Calculating and saving space in PostgreSQL

This related answer has a detailed assessment for three relevant options:

separate boolean columns
an integer to encode up to 32 boolean values
a bitstring (bit(n) or bit varying(n))

Is there any difference between integer and bit(n) data types for a bitmask?


SQL Agent using domain user for Jobs with TSQL I have tried every option but i still cant find out why my sql agent is using a domain user when executing job steps with TSQL and keep getting the following error:

Executed as user: RESOURCE\UXCOLEBSREP$. Login failed for user 'RESOURCE\UXCOLEBSREP$'. [SQLSTATE 28000] (Error 18456).  The step failed.

I have made sure that the agent is running using this acocunt:
NT SERVICE\SQLAgent$UXB

I have made sure that no run as being used under any job stpes.

Please advice! I'm using sql 2017

Thx

uxcolebsrep$ is probably the SQL Server name itself.

In this case, as you are using a linked server, that makes sense. Your actual Agent service account is a local account to your sql server. It does not exist on the remote Linked Server so SQL uses the Machine account to try to connect to the remote SQL Server (the one in the linked server).

To get this fix, you have a couple of option:
1 - Change your SQL Agent service acccount for a domain account and make sure it has permission on the remote SQL server (the one in the linked server)  - (Recommanded option)
2 - Give access to the SQL Machine (uxcolebsrep$) on your remote SQL Server
3 - Create a proxy to use in your SQL job and make sure that this proxy will use an account with permission on the remote SQL Server.


Change DateTime format How can i return a date only format (DD/MM/YYYY) from a DateTime format column? example below

Modified Date
2007-09-01 02:00:00.000
2005-09-01 05:00:00.000

Output:

Date
09-01-2007
09-01-2005

If you are looking for a particular string formatting of a datetime data type value then you need to work with the convert() function with a particular style.  In this case, you are looking for the style with a value of 110 (resulting in the format of mm-dd-yyyy):

select convert(char(10), getdate(), 110);

(Note: I use getdate() just to generate a datetime value, in which case you would just reference your datetime column) My output is this:

06-08-2014


Access view based on table in another database without account in that other database I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account in database2. How can I solve this issue? I don't want to create an account in database2.

This is easy to accomplish in a very secure way using Module Signing. This will be similar to the following two answers of mine, also here on DBA.StackExchange, that give examples of doing just this:

Stored procedure security with execute as, cross database queries, and module signing

Permissions in triggers when using cross database certificates

The difference for this particular question is that it deals with a View, and Views cannot be signed. So, You will need to change the View into a multi-statement Table-Valued Function (TVF) as those can be signed and can be accessed just like a View (well, for SELECT access).

The following example code shows doing exactly what is being requested in the question in that the Login / User "RestrictedUser" only has access to "DatabaseA" and is yet able to get data from "DatabaseB". This works only by selecting from this one TVF, and only due to it being signed.

Accomplishing this type of cross-database access while still using a View, and not giving the User any additional permissions, would require enabling Cross-Database Ownership Chaining. That is far less secure because it is completely open-ended for all objects between both Databases (it cannot be restricted to certain objects and/or Users). Module Signing allows just this one TVF to have the cross-DB access (the User doesn't have the permission, the TVF does), and Users that cannot SELECT from the TVF have no access to "DatabaseB" at all.

USE [master];

CREATE LOGIN [RestrictedUser] WITH PASSWORD = 'No way? Yes way!';
GO

---

USE [DatabaseA];

CREATE USER [RestrictedUser] FOR LOGIN [RestrictedUser];

GO
CREATE FUNCTION dbo.DataFromOtherDB()
RETURNS @Results TABLE ([SomeValue] INT)
AS
BEGIN
    INSERT INTO @Results ([SomeValue])
        SELECT [SomeValue]
        FROM   DatabaseB.dbo.LotsOfValues;

    RETURN;
END;
GO

GRANT SELECT ON dbo.[DataFromOtherDB] TO [RestrictedUser];
GO
---

USE [DatabaseB];

CREATE TABLE dbo.[LotsOfValues]
(
    [LotsOfValuesID] INT IDENTITY(1, 1) NOT NULL
        CONSTRAINT [PK_LotsOfValues] PRIMARY KEY,
    [SomeValue] INT
);

INSERT INTO dbo.[LotsOfValues] VALUES
    (1), (10), (100), (1000);
GO

---

USE [DatabaseA];

SELECT * FROM dbo.[DataFromOtherDB]();

EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

REVERT;

All of the steps above recreate the current situation: the User has access to DatabaseA, has permission to interact with an object in DatabaseA, but gets an error due to that object in DatabaseA accessing something in DatabaseB where the User does not have any access.

The steps below set up the Module Singing. It does the following:

creates a Certificate in DatabaseA
Signs the TVF with the Certificate
Copies the Certificate (without the Private Key) to Database B
Creates a User in DatabaseB from the Certificate
Grants SELECT permission to the Table in DatabaseB to the Certificate-based User

Module Signing setup:

CREATE CERTIFICATE [AccessOtherDB]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for accessing other DB',
    EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
    TO dbo.[DataFromOtherDB]
    BY CERTIFICATE [AccessOtherDB]
    WITH PASSWORD = 'SomePassword';

---
DECLARE @CertificatePublicKey NVARCHAR(MAX) =
            CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'AccessOtherDB')), 1);

SELECT @CertificatePublicKey AS [Cert / PublicKey]; -- debug

EXEC (N'USE [DatabaseB];
CREATE CERTIFICATE [AccessOtherDB] FROM BINARY = ' + @CertificatePublicKey + N';');
---

EXEC (N'
USE [DatabaseB];
CREATE USER [AccessOtherDbUser] FROM CERTIFICATE [AccessOtherDB];

GRANT SELECT ON dbo.[LotsOfValues] TO [AccessOtherDbUser];
');

---

EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
-- Success!!

SELECT * FROM [DatabaseB].[dbo].[LotsOfValues];
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

REVERT;

IF ACCESS NEEDS TO BE THROUGH A VIEW, for whatever reason, then you can simply create a View that selects from the TVF shown above. And, in that situation, SELECT access does not need to be granted to the TVF, only to the View, as demonstrated below:

GO
CREATE VIEW dbo.[DataFromTVF]
AS
SELECT [SomeValue]
FROM   dbo.DataFromOtherDB();
GO

-- Remove direct access to the TVF as it is no longer needed:
REVOKE SELECT ON dbo.[DataFromOtherDB] FROM [RestrictedUser];

GRANT SELECT ON dbo.[DataFromTVF] TO [RestrictedUser];

And now to test it:

EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'DataFromOtherDB',
database 'DatabaseA', schema 'dbo'.
*/

SELECT * FROM [OwnershipChaining].[dbo].[LotsOfValues];
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

SELECT * FROM dbo.[DataFromTVF];
-- Success!!

REVERT;

For more info on Module Signing, please visit: https://ModuleSigning.Info/


What is an Intent Lock in SQL Server I have this query

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 5000;

Which have these stats

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |        2429 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |          97 |
+--------------+---------------+---------------+-------------+

I wonder about the IX, which is an Intent Lock. What does that mean, and why does there exist one on the table it self? As I understand, it is not a true lock but more something SQL Server use (or is set by the transaction?) to indicate that a lock might occur.

Is the above right?

You are correct, it is a signal to the engine that you wish to put a lock on an object. To over-simplify it, imagine you want to obtain a book from the library, if somebody has the book checked out you cannot obtain it, so you put your name on a list of people who intend to check that book out. When everybody else is done with it, and it is your turn, then you are allowed to check out the book.

If you haven't done so already I highly recommend reading Transaction Locking and Row Versioning Guide on MSDN which provides some great detail on how it all works.

The below is a snippet from that page:

  Intent Locks

  The SQL Server Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.
  Intent locks serve two purposes:

  To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
  To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.

  For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the SQL Server Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.


Where to find the installer for SQL Server Native Client 11.0.5058? I am trying to track down some behaviour issues in our software which seem to dependent on the version of SQL Server Native Client in use. I have version 2011.110.2100.60 (aka 11.00.2100) but our client has 2011.110.5058.00 (aka 11.00.5058). I've been around the house on Microsoft websites, including MSDN, but I can't find a version more recent than the one I have.

Is there a reliable repository of the different versions of the installer (sqlncli.msi) for testing purposes, or a guide to which versions are bundled with other products?

SQL Server version 11.00.5058 is SQL 2012 SP2, so you need to download Microsoft SQL Server 2012 SP2 Feature Pack

There are several places where you can look up the exact version of a build number. Personally I use sqlserverbuilds but there are others.

Once you find the name of the build you can use that to google and it may lead you to the correct version of the installer or feature pack you need.

If you click download on that page it takes you to a list of packages you can select that looks like this:


Proper Way to Remove Mysql Tables I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?.

Removing the .frm files from my datadir has resolved the mysqldump issue of trying to dump non-existent tables. Now my question is how do I drop a table and have all references to it removed? I would have thought issuing the drop table would remove all associated files in the directory. I am using MyISAM so perhaps the issue is related to that.

Just remove all three of the correlated table data files from the file system and that should ensure all traces are removed as per my understanding of 15.2 The MyISAM Storage Engine

Each MyISAM table is stored on disk in three files:

.frm
.MYD
.MYI

  Each MyISAM table is stored on disk in three files. The files have
  names that begin with the table name and have an extension to indicate
  the file type. An .frm file stores the table format. The data file has
  an .MYD (MYData) extension. The index file has an .MYI (MYIndex)
  extension.

  Source

Further Resources

1.6.3 How to Repair MyISAM Tables


How open oracle DB Linux (After installation) I have just installed Oracle 11gR2 on my Linux Mint Distro (xfce). All worked fine, and I was able to acces to

https://localhost:1158/em [Erreur 102 (net::ERR_CONNECTION_REFUSED) ]

I rebooted my PC and tried to reopen the page but this time I am unable to open it.

I am not so expert in Linux but could that be a problem of acces permission ?

How could I resolve this issue?

Thank you.

First edit your /etc/oratab changing N to Y like that:

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:N

Must be change to Y:

orcl:/u01/app/oracle/product/11.2.0/dbhome_1:Y

Then to start you must use the dbstart script like that:

export ORACLE_HOME=youroraclehome
su oracle -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"

Where "oracle" in the "su" command is the name of the user you used to install oracle.

This will up all oracle essential services including the https port 1158 (and you can put that in an auto-start script)

And to stop it:

export ORACLE_HOME=youroraclehome
su oracle -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"

And to backup it (dump) ;-)

export ORACLE_HOME=youroraclehome
$ORACLE_HOME/bin/exp file=mybackup.dmp full=y

Remember to test your backup before thinking its working.


one to many design struggle I have two tables :

CREATE TABLE fiber (
 fib_id uuid primary key default gen_random_uuid(),
 fib_number not null,
 fib_object uuid not null reference cable, --useless here but imagine a cable
 fib_optr uuid reference optical_road on delete set null,
 fib_color text not null
);

-- optical road are multiple fiber, spliced together.
-- Onces spliced, it is just one long fiber.

CREATE TABLE optical_road (
 optr_id uuid primary key default gen_random_uuid(),
 optr_name text not null,
 optr_client uuid reference client -- just an example
);

I want to enforce the fact that you cannot delete a fiber if it is linked to an optical road. I cannot find a design without trigger.

Imagine this record :

 fib_id   | fib_number | fib_object | fib_optr | fib_color
 <anuuid> |          1 | <anuuid2>  | <anuuid3>| #12345

Then here I want that DELETE FROM fiber where fib_id=<anuuid> raises an error.

optical_road {OPTR_ID, OPTR_NAME, OPTR_CLIENT}
          PK {OPTR_ID}

fiber {FIB_ID, FIB_NUMBER, FIB_OBJECT, FIB_COLOR}
   PK {FIB_ID}

fiber_road {FIB_ID, OPTR_ID}
        PK {FIB_ID}

       FK1 {FIB_ID}  REFERENCES fiber {FIB_ID}
                     ON DELETE RESTRICT

       FK2 {OPTR_ID} REFERENCES optical_road {OPTR_ID}
                     ON DELETE RESTRICT

Note:

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY


How to find equivalent of Oracle's privileges in SQL Server? I have a user in Oracle for which the permissions given are CONNECT and SELECT_CATALOG_ROLE.

How do you find the equivalents when setting up a similar user in SQL Server?

In SQL Server the right to "CONNECT" is given at the instance level.  You'll find this right assigned to the login.  The ability to view the database catalog is given at the database level by assigning the "VIEW DEFINITION" right to the database or the schema.


How can I get the Oracle TNSListener to start after changing my host name? When running

lsnrctl status

I can see that it is using the old host name, but I don't know where it is pulling that value from.

My Oracle installation is 11.2.0 on Windows7 x64, if that makes any difference.

When you run lsnrctl status, one of the things that it tells you is the location of the Listener Parameter File (the listener.ora file)

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 06-JUN-2012 13:12:22

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                06-JUN-2012 09:33:58
Uptime                    0 days 3 hr. 38 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\oracle\diag\tnslsnr\jcave8560w\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jcave11gR2XDB" has 1 instance(s).
  Instance "jcave11gr2", status READY, has 1 handler(s) for this service...
Service "jcave11gr2" has 1 instance(s).
  Instance "jcave11gr2", status READY, has 1 handler(s) for this service...
The command completed successfully

If you open that file, there will probably be an entry like this

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

where the host is specified.  Change the host there and restart the listener.


SQL - Inverse Selection I have two tables in my schema

students(id text, matric text)

attendance(id serial, a_date date, matric text references students matric)

To get records showing date and student present, I can run a query like

select a.a_date, s.matric
from students s, attendance a
where a.matric = s.matric
and a.a_date between '2015-01-01' and '2015-04-01'

I want a query for the records showing date and student absent.

For more information, please visit http://sqlfiddle.com/#!15/e43a9/6 to view the schema and sample data

You first need a list of all possible dates in that range. This can be done using the generate_series() function in Postgres:

select dt::date dt
from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt

Using this you can create a list of all possible attendances:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad;

The above uses a common table expression to make the select a bit more readable (at least in my eyes). It can be seen as a "temporary view" that only exists for the runtime of the query. If you want, you can ignore that and just assume that there is a table called all_dates that contains all dates in the range you want.

The result of this can the be used to find those rows where not attendance row exists for a student on that date:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad
where not exists (select 1
                  from attendance a
                  where a.matric = s.matric
                  and a.a_date = ad.dt);

The not exists subquery can also be re-written as a left join, which might be faster:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad
  left join attendance a on a.matric = s.matric and a.a_date = ad.dt
where a.a_date is null;

A third option is to "subtract" the list of attendances from the list of possible attendances. Sets in SQL can be subtracted using the except operator:

with all_dates as (
  select dt::date dt
  from generate_series(date '2015-01-01', date '2015-04-01', interval '1' day) dt
)
select s.*, ad.dt
from students s
  cross join all_dates ad

except

select s.*, a.a_date
from student s
  join attendance a on a.matric = s.matric;


Is there a way to use sysconv() built-in function? If you get the definition of some views using sys.sp_helptext:

exec sys.sp_helptext 'sys.columns'

CREATE VIEW sys.columns
AS
  SELECT c.id                                                             AS object_id,
         c.NAME,
         c.colid                                                          AS column_id,
         c.xtype                                                          AS system_type_id,
         c.utype                                                          AS user_type_id,
         c.length                                                         AS max_length,
         c.prec                                                           AS PRECISION,
         c.scale,
         CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
         sysconv(bit, 1 - ( c.status & 1 ))                               AS is_nullable,-- CPM_NOTNULL
         sysconv(bit, c.status & 2)                                       AS is_ansi_padded,-- CPM_NOTRIM
         sysconv(bit, c.status & 8)                                       AS is_rowguidcol,-- CPM_ROWGUIDCOL
         sysconv(bit, c.status & 4)                                       AS is_identity,-- CPM_IDENTCOL
         sysconv(bit, c.status & 16)                                      AS is_computed,-- CPM_COMPUTED
         sysconv(bit, c.status & 32)                                      AS is_filestream,-- CPM_FILESTREAM
         sysconv(bit, c.status & 0x020000)                                AS is_replicated,-- CPM_REPLICAT
         sysconv(bit, c.status & 0x040000)                                AS is_non_sql_subscribed,-- CPM_NONSQSSUB
         sysconv(bit, c.status & 0x080000)                                AS is_merge_published,-- CPM_MERGEREPL
         sysconv(bit, c.status & 0x100000)                                AS is_dts_replicated,-- CPM_REPLDTS
         sysconv(bit, c.status & 2048)                                    AS is_xml_document,-- CPM_XML_DOC
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1

There is a function called sysconv.

The function 'sysconv' is used by these views, and you can select values from this view without getting any error message.

select * from sys.columns;

But if I try something similar to:

select sysconv(bit, 375 & 8);

I receive the error:

  sysconv is not a recognized built-in function name

As Martin Smith pointed out, this function can be replaced by convert (bit, 375 & 8), but I wonder why I can select values from this view and I can't use it in my queries, even when SSMS recognizes it as as command, by changing the foreground color?

The view sys.columns is a system view. This view is provided to you by the sys user in the master database, which is a database user without login.

The sys user has its own schema sys which is then used to link all the sys.* objects.

Permissions to select from the sys.* objects are granted to you via membership in the public SQL Server role. The SQL Server role public has SELECT permissions on all sys.* schema objects.

This is how you are granted the permission to select * from sys.columns.

If you are not member of the public server role, then you don't have access to any of the sys.* objects.

sys user (script)

USE [master]
GO

/****** Object:  User [sys]    Script Date: 16.12.2016 15:57:08 ******/
CREATE USER [sys]
GO

sys schema (script)

USE [master]
GO

/****** Object:  Schema [sys]    Script Date: 16.12.2016 15:57:35 ******/
CREATE SCHEMA [sys]
GO

public server role (script)

USE [master]
GO

/****** Object:  ServerRole [public]    Script Date: 16.12.2016 16:05:05 ******/
CREATE SERVER ROLE [public]
GO

Public database role

There is also a public database role, which is somehow linked to the server role. If you query the sys.database_principals (view) in the master database, then you will see that there is a public principal with the same id as the server role public which is 0. I am assuming that this is the missing link, between the database_role public and the server_role public.

Edit: Added some information regarding the database role "public"

The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. Grant public only the permissions you want all users to have.

Quote from Server and Database Roles in SQL Server, Section: "The public Role"

If you retrieve the permissions for the database role public in the master database you will get the following listing:

Query:

SELECT
   OBJECT_NAME(dp.major_id) AS OBJECT ,
   USER_NAME(dp.grantee_principal_id) AS grantee,
   USER_NAME(dp.grantor_principal_id) AS grantor,
  dp.permission_name
FROM sys.database_permissions AS dp
WHERE dp.grantee_principal_id = 0

Results:

sp_MSalreadyhavegeneration  public dbo EXECUTE
sp_MSwritemergeperfcounter  public dbo EXECUTE
TABLE_PRIVILEGES            public dbo SELECT
sp_replsetsyncstatus        public dbo EXECUTE
sp_replshowcmds             public dbo EXECUTE
sp_publishdb                public dbo EXECUTE
dm_pdw_nodes_os_tasks       public dbo SELECT
...
[truncated]

To come back to your example with the sys.colums view: You have been granted the permissions to SELECT from the view, but you do not have permissions to actually execute the sysconv function directly, which is the definiton of a column in the result set. It has been hidden from your prying eyes.


How to export database structure from Firebird? I'm doing replication and I need to export db structure from one Firebird db (dialect 3) into another. I've seen a couple tools like IBPump and FBExport, however all I can find is how to export all the data and not the structure. Any advice?

There are several ways to do this:

A metadata-only backup using gbak:

gbak -backup -meta_data employee employee.meta.fbk

And then restore that backup.
A metadata-only restore using gbak:

gbak -create employee.fbk mytest.fdb -meta_data

This can be useful if you already have a backup with data.
Exporting the DDL of a database with ISQL (or another tool)

isql -ex -o ddldump.sql /path/to/your/database.fdb

And then use that script to populate a new database.

For options 1 & 2 see gbak: Backup & Restore Recipes: Meta Data Only
For option 3 see ISQL: Command Line Switches.


What is the Relationship between Instagram users and user followers I'm building a blog project, which each user can follow other users. I'm still figuring out the design and creating the diagrams first. I know that the relationship between users and followers is many to many. Is this the correct approach for the ER design of the relationship?

The relationship between Instagram user and follower is many to many because:

One user can have multiple followers.
One follower can have multiple "followed users".

Useful:  Always name the entities (classes) as singular e.g. User & Follower


mysqldump is ignoring option '--databases' due to invalid value I have this MySQL database named capsule_corporation_database.
Then, I have this my.cnf file in my home directory:
~/.my.cnf
[client]
host=localhost
user=trunks
password=mai
database=capsule_corporation_database

Attempt to mysqldump
mysqldump > copy_of_capsule_corporation_database.sql

The backup did not pursue and logged the below message:
Warning: mysqldump: ignoring option '--databases' due to invalid value 'capsule_corporation_database'

What does it mean and how to resolve it?

In order to fix this issue, just move the database parameter from client to mysql in my .cnf

i.e

$ cat ~/.my.cnf
[client]
host=localhost
user=trunks
password=mai

[mysql]
database=capsule_corporation_database


how to deal with mysql 500 million data per month? this is a interview question

a telecom billing system, there is a internet flow table (phone_number,start_time,stop_time,time_lasting,fare),500 million records per month(2G),every record size is 300K, insert and update when people use 4G to surf the internet,and update their account balance every time（166 write/s），and people will query their internet details from 5 * 6 = 30 hundred million (save half year data)

question1:how to use mysql to handle these data?
quesiton2:how to optimize insert and update?
question3:how to optimize query ?

my opion (mybe not correct)
1.insert and update is a transaction so use InnoDB engine
2.partition table every day
3.do I need redis or anything to help speed up insert and update ?
4.I don't know how to deal with this query optimize

thank you very much

Plan A:  ENGINE=InnoDB, with these specific suggestions:

Since you are purging after 6 months, I recommend PARTITION BY RANGE (TO_DAYS(...)) with weekly partitions.  Every week, drop the oldest and REORGANIZE "future" into "next week" and "future".  See Details.

Partitioning by day leads to so many partitions that it is inefficient.  And, dropping a week's worth of data is just as easy as a day's worth.

a_vlad is right that partitioning does not necessarily help with queries.  But it is excellent for that time-based purge.

200M * 300K is more like 2T.  Where is the math error?  Perhaps 300B?  Are you implying a fixed-length row?  Don't.

300KB for one record seems terribly big -- what are you keeping in it?  Seriously consider normalizing out any repetitive columns.

Provide a tentative SHOW CREATE TABLE and the main queries (insert/update/delete).  It is unclear that there need to be any updates or deletes for your application.

166 writes/sec is pushing the limits of a spinning drive.  Either use hardware RAID-5 with a Battery Backed Write Cache or use SSDs.

I suggest you adapt the suggestions in my High speed ingestion blog.  That discusses a technique for getting well past 166 writes/sec.  (Keep in mind that you will have peak loads that are much higher than 166.)

Plan B: NDB Cluster.  (Sorry, I don't have specific suggestions.  But NDB was designed for Telecom.)

Redis?  That adds another moving part -- more things to go wrong.  And, assuming that your traffic is reasonably consistent, there is little to be gained by any buffering scheme.

The MySQL box should be separate from the Client box(es).  You should design the client boxes (web servers?) to be scalable -- any number of them, add more as needed.


TempDB Query being Blocked I have been monitoring Blocking issues on our SQL Server database. Very often the below query is running on tempdb and is blocked with LCK_M_S

What is this query used for and why does it run regularly?

Thanks!

<?query --
select @reservedpages = sum(a.total_pages),
    @usedpages = sum(a.used_pages),
    @pages = sum(
            CASE
                -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                When a.type <> 1 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
            END
        )
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id

    /* unallocated space could not be negative */

--?>

This code is from a system stored procedure called sp_spaceused, which returns the amount of allocated space (pages) for database objects.

Look for home-brewn monitoring jobs or maintenance scripts that may be running a bit too frequently.


SQL Server 2014 Failover cluster - Nodes on different patch levels As part of trouble shooting a performance issue with a SQL Server cluster I found out that the nodes haven't been patched properly to the same level. It is a 2 node active / passive cluster. SQL Server performance was  much better on node with higher patch level.

The nodes have been patched to the same level now.

Just trying to understand how SQL Server and the would have  behaved in this sort of a scenario?
Would this patch level miss match have caused other issue with the cluster?

SQL Server versions on nodes were different and SQL Server needed to do upgrade or downgrade after failover. This increased failover time.


MongoDB Indexing - How many fields should I index? I have a Collection in my db, called Post.

Here's how it looks:

Post {
    user: <EmbeddedObject>,
    school: <EmbeddedObject>,
    hashtag: <EmbeddedObject>,
    numberOfReports: <Number>,
    viewRanking: <Number>,
    type: <String>,
    isPollOfTheDay: <Bool>,
    createdAt: <Date>,
    endsAt: <Date>
}

(Obviously it also contains other, unrelated fields)

So this Post collection, is being queried by 5 different screens in my app. School, Hashtag, All Polls, Discover & Profile. All queries are very similar to each other, but they differ.

Let's have a look at them individually:

  School

Here, I have 2 queries

I compare by school.name (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)
I compare by isPollOfTheDay (equal to true) and endsAt (greater than or equal to)

  Hashtag

I compare by hashtag.id (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)

  All Polls

I compare by type (equal to), isPollOfTheDay (equal to false), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order either by createdAt or viewRanking (depends on the user)

  Discover

I compare by school.name (not equal to), type (equal to), numberOfReports (less than), user.id (for blocks checking (not contained in)) and lastly, we sort in descending order by createdAt

  Profile

I compare by user.id (equal to) and we sort in descending order by createdAt

These are all of my queries! I can say that they are called almost with the same frequency. My question is, should I just index all 9 fields? Are they too many to index? Should I ignore the isPollOfTheDay field, since it's a Boolean? (I've read that we shouldn't index Booleans)

EDIT: Every document occupies about 200 bytes. We currently have 25K documents and growing in a pace of ~300/day.
The only fields that can change, are viewRanking and numberOfReports where the first one will change often, whereas the second far less often!

The selectivity of each query is high (I think), since the needed documents are found mainly by their first comparison. There are about 50 different school.names & another 50 hashtag.ids.

It seems that your collection is quite small (5MB?). I would create one index for each query and check execution plans.
The limit of creating too many indexes are :

If your indexes are too big and don't fit in RAM (I suppose you have
several GB of RAM ?)
It increases I/O during documents insert/update because each time you write a document, indexes are also updated.

Also, indexes are necessary when sort operation cannot fit in memory. There is a limit of 32MB, so here again you are below this limit.

Please refer to indexing strategies for more details.


What will be best (for performance) transaction isolation level? There, are two types of queries that are executed in my app:

DELETE FROM tbl1 WHERE user_id = 1;
INSERT INTO tbl1 VALUES (...), (...), ...;

and

UPDATE tbl2 WHERE id = 1 SET ...;
DELETE FROM tbl3 WHERE tbl2_id = 1;
INSERT INTO tbl3 VALUES (...), (...), ...;
DELETE FROM tbl4 WHERE tbl2_id = 1;
INSERT INTO tbl4 VALUES (...), (...), ...;

The ORM that I am using automatically sets transaction isolation level to REPEATABLE READ and I was wondering if it is a right choice for best performance. My goal is to only execute all or nothing (from 2 transaction above) and I don't care about any reads...

So which isolation level will be best for best performance in my case?

READ COMMITTED imposes fewer overheads as PostgreSQL does not have to do dependency tracking.

In practice the difference is likely to be minimal.

Isolation level should be selected based on correctness, not performance. It's not possible to determine whether READ COMMITTED is sufficient from the queries provided, but I don't see a strong reason to suspect it not to be.

There are almost certainly many other ways to improve performance before playing with things like isolation levels anyway.


Does it make sense to consolidate Oracle databases? I am more experienced with SQL Server and Sybase than Oracle, and understand those products well. I've been asked to look for ways to reduce the server estate running Oracle. I understand that an instance in Oracle maps to a database hosting many tablespaces. I have a fairly good grasp of the fundamentals, however if I wanted to consolidate SERVER1,..,SERVER4 running Oracle database into one server what would be the best way to do it physically? I am considering Virtual as well using a DBaaS (Database as a Service) model, but am curious if it can/should be done physically.

Is it possible to have four separate instances point to four separate databases on one machine? Or would I have to merge the four databases into one database on the consolidated server and manage the schemas to ensure there are no name conflicts? If I did that would I have one instance or four?

I have read the documentation but I'm still not 100% sure about this area.

It is fairly easy to consolidate multiple databases into one real database. In Oracle, a database is the collection of files. Users connect to the database by connecting to an instance. A database can be served by multiple instances, in which case you are running RAC.

So for simply consolidating database into one single database you don't need RAC but you could, if you want/need to do this.

If you are consolidating into one database there are a few things to take into account:

namespaces
service level agreements - don't make maintentance impossible by combining conflicting slas.
performance isolation - use Resource Manager to handle this
application isolation - make sure the apps all use their own tns_alias to connect
services - give every application an own service name in the database, if possible.

When you have naming conflicts, there is a problem, combination not possible.
You might want to take some downtime for maintenance/upgrades. If you can not get a downtime from all applications at the same time, you have a problem.
Using Resource Manager you can give a certain performance guarantee for the specific services.
Services are a smart thing to use, it is the easiest thing to see how resources are used, compared to one and other.

Easiest is to run multiple instances on a single server, each serving it's own database. This is the easiest but not the smartest thing to do. Smartest is to have a single instance on a single server. This is because every instance considers itself as the master of the server. You can not very easy isolate their resource usage, as you can do in a single instance. If you want to give some performance guarantee in a multiple instance server, the setup will grow in complexity because in many cases you need to start multiple projects and users to run your databases under.

A single database can easily support a few hundred applications, a lot cheaper than using a few hundred databases. This quickly saves BIG money on a yearly basis by making good use of Oracle features.


PostgreSQL: Last access time to table I am in charge of a large PostgreSQL database, with few dozen tables. I suspect that many of these tables are never accessed.

What's the best way to check when was the last time a certain table was accessed? I thought of adding a trigger on DELETE, INSERT and UPDATE, but I hope there is a more efficient way.

pg_catalog.pg_statio_all_tables is your friend. All you should need to do is periodically poll pg_statio_all_tables for the tables in question. Changing stats ~ active table, non-changing stats ~ potentially unused table. Just be careful that no one does a select pg_stat_reset () ; in the middle of your monitoring.

For example:

test_1=# create table test_stats (col1 integer);
CREATE TABLE

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |              0 |             0 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Inserts:

test_1=# insert into test_stats (col1) select generate_series( 1, 10000000);
INSERT 0 10000000

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          44260 |      10088481 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Selects:

test_1=# select count (*) from test_stats where col1 between 10000 and 50000;
 count
-------
 40001
(1 row)

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          85560 |      10091429 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Deletes:

test_1=# delete from test_stats where col1 between 10000 and 50000;
DELETE 40001

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema')
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |         155075 |      10136163 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

update-- 2011-09-01

Further testing indicates that vacuum appears to increase the values in pg_statio_all_tables somewhat, which is unfortunate for your desired use. While vacuum doesn't make use of pg_statio_all_tables useless, it does make interpreting the results a bit fuzzier.

Perhaps a better place to monitor is pg_catalog.pg_stat_all_tables (at least with newer versions of Pg). I'm looking at version 8.4 and that has counts for tuples inserted, read, updated, and deleted-- ISTR 8.2 doesn't have all that and I don't know about 8.3 so YMMV depending on the version of Pg that you are using.

A third option (for insert, update, and delete activity) is to watch the file timestamps in the $PGDATA/base/$datid directory. The filename should map to the oid of the table, so you could use this to identify tables that aren't getting inserts, updates, or deletes. Unfortunately, this doesn't address tables that are still getting selected from, and using tablespaces will cause additional complications (as those files will not be under $PGDATA/base/$datid). The timestamps won't update until any pending changes are flushed, but if the file hasn't changed in months then the odds of a currently pending change are probably small.


Create products with color variants I'm working on an e-commerce project to sell clothes, to store these products I'm using a table tb_product. These clothes will have the attributes color and size, where I also need to keep track of the stock for each produtct, size and color.

If I use a second table to control the attributes, as I already asked here, I think it's going to add unecessary complexity, because I need to keep track of the stock for the each shirt, each size and each color, all related.

One solution I've seen many stores doing is to add each color as a single product and only control the size attribute. The ony doubt I have is how to link all colors of the some product to show under an option "More colors", like this image example. If I click on any of those options on the tab "Mais cores" it will open a new product page.

This is where my doubt begins, because every other aspect of the item, such as name, price, description, etc.. Is all the same, only the color is changing.

How can I create a link between these products to show them as related colors? The simple solution I could think of is to add a new column to the table, called, for example, group, and all the products would contain within this group. For example:

id | active | group |      name     | description | price | ...
1  |    1   |   A   | T-Shirt white | This is a.. | 9.90  | ...
2  |    1   |   A   | T-Shirt Black | This is a.. | 9.90  | ...
3  |    1   |   B   | Hat yellow    | This is a.. | 9.90  | ...
4  |    1   |   B   | Hat red       | This is a.. | 9.90  | ...
5  |    1   |  null | Custom dress  | This is a.. | 9.90  | ...
6  |    1   |  null | Belt          | This is a.. | 9.90  | ...

CREATE TABLE IF NOT EXISTS `product` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `active` tinyint(1) NOT NULL DEFAULT '0',
    `group` varchar(10) DEFAULT NULL,
    `name` varchar(250) NOT NULL,
    `description` longtext NOT NULL,
    `price` decimal(10,2) NOT NULL,
    PRIMARY KEY (`id`),
);

Is there a better, or any other, approach to this solution?

You have Table called 'product'
To find out the unique product among the one product, u need to maintain these structure.
1.Product table
2.Characteristics table. EX(size, color etc)
3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose)
4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.

Then you can get one valuable structure for eCommerce products.
For stock you have to maintain from product price table.


How to change Zeros ( 0 ) to NULL in SSIS? I would like to change some Zeros into NULL using SSIS 2008. In that context I tried "conditional split" where I have set the columns test in question with the following expression:

REPLACE((DT_WSTR,8)test,"0", NULL(DT_STR,10,1252))

but that doesn't seem right. The expression is red.

I get the errors:

  data types "DT_WSTR and "DT_I8" are incompatible for conditional operator...

  Error at create lists [Derived Column 9 [141128]]: Attempt to find the input column named "NULL" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

  Error at create lists [Derived Column 9 [141128]]: Attempt to parse the expression "REPLACE(PLZ,"0",NULL)" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

  Error at create lists [Derived Column 9 [141168]]: Attempt to parse the expression "[PLZ]==0 ? NULL(DT_I8, 10) : [PLZ]" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

  Error at create lists [Derived Column 9 [141168]]: Cannot parse the expression "[PLZ]==0 ? NULL(DT_I8, 15) : [PLZ]". The expression was not valid, or there is an out-of-memory error

Sample data:

test     test ideal

 0            NULL
120           120
304           304
100           100
222           222
520           520
0             NULL
0             NULL
10            10
11            11
0             NULL

Use Derived Column Transformation with expression [test] == 0 ? NULL(DT_I8) : [test]


Storing no of players the last seven days in an online game I want to plot the number of players, in the last seven days, of an MMORPG which haves 50~ servers. The system fetches the number of players in each server every 15 minutes and I'm not really sure how to store and update this information in an efficient way.

The easiest (but probably not so efficient) way I can think of is just having three columns, "time_fetched", "server", "no_players". Every 15 minutes it inserts 50 new rows and deletes the 50 oldest rows.

Maybe I could store weekday, hours and minute instead? So instead of deleting and inserting rows it just updates the correct rows? This would mean that I always have a fixed number of rows in the table (50 * 4 * 24 * 7 = 33600).

Also; would it be more efficient to store the server name as an enum instead of just strings (or some other way, maybe one table for each server)?

I would store the timestamp, server identifier, and number of players in just the way you thought of, deleting the oldest (if you need to) and adding new ones every 15 minutes.

Moving the server names into another "server" table and referencing the primary key in your player count table would be a more conventional normalised design.

With reference to the trigger, don't forget that you can perform both the insert and the delete in the same SQL statement with a common table expression: http://sqlfiddle.com/#!1/c8362/5