Hot answers tagged

15

Having worked on a large-scale, multi-tenant system (federated approach with customers spread across 18+ servers, each server having identical schema, just different customers, and thousands of transactions per second per each server), I can say: There are some folks (a few, at least) who will agree on your choice of GUID as the IDs for both "TenantID" and ...


8

As far as I understand it you are not actually talking about an UPSERT here just combining two different CRUD operations in one stored procedure. CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT, @Foo INT, @Bar VARCHAR(10) AS IF @Id IS NULL BEGIN INSERT INTO ...


6

From the article you were reading (by Craig Freedman): Hash join shares many characteristics with merge join. Like merge join, it requires at least one equijoin predicate, supports residual predicates, and supports all outer and semi-joins. (Emphasis added) The hash keys are built from the (required) equijoin predicates. All other predicates are ...


5

For example, take a table T with an index on attributes a and b. Is the index useful for the query: select * from T where a='foo' There are two questions there: Can an index on (a,b) be used at all for this query? The answer to that is generally yes. Maybe not all databases have that capacity, but most mainstream ones do AFAIK. Replace the select ...


5

Rows in postgresql have no fixed order. Not only are records placed where ever there is free space records can also move around. This is because when a row is updated a new version of the row is created in a new location while the old version continues to live in it's old location until it's removed by vacuum. A CLUSTER operations sorts all rows but does ...


4

It depends. Will you be accessing (SELECTing) only the 'static' data frequently? With complex WHERE clauses? If so, "vertical partitioning" is beneficial. It would keep the SELECTs from interfering with the UPDATEs. Will the JSON columns be large, and the 'static' columns be relatively small? If so, that adds to the argument for vertical partitioning. ...


3

If you do want to use one procedure, you could use the MERGE statement. Sample merge code: create table testo(Id int identity(1,1) NOT NULL, somechar char(1), someint int, AddedTime datetime2(0), LastModifiedTime datetime2(0)); alter table testo add constraint [PK_testo] primary key(Id); --Clustered index on target table. --No index necessary on the ...


3

Yes, but it depends on which index key you search by. Think of it like one of those old "white pages" phone books. In a phone book, people are ordered on the pages in the order LastName, FirstName. That means there are two components to the phone book's index. If you're looking for all of the people with the last name of "Smith", you just find the ...


3

Technically speaking, there is no single answer for "MySQL" itself, because MySQL has a pluggable storage engine architecture and the server itself is essentially unconcerned with and unaware of how the physical storage of data is accomplished. Those details are handled by the storage engines. The most relevant storage engine in MySQL is InnoDB. Can ...


3

It sounds like you're looking at something similar to, if not identical to, a multi-tenant architecture. In a multi-tenant architecture, all the tenants (clients, customers) share something. They might share only the server and the dbms, with each tenant getting a private database. Or they might share the database, with each tenant getting a private ...


2

PostgreSQL experience. We have table with 3 columns table test with columns id, first, second then we have two indexes first_second_idx (first, second) first_idx (first) then if we use statement (1) select * from test where first = 'whatever'; query planner should use index first_idx ( 'whatever' value is for example 5% of all records). It's ...


2

UPDATED version based on feedback: CREATE TABLE group ( group_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY(team_id) ); CREATE TABLE player ( player_id INT NOT NULL AUTO_INCREMENT, last_name VARCHAR(50), first_name VARCHAR(50), group_id INT, player_rank INT, PRIMARY KEY(player_id) ); CREATE TABLE game ( ...


2

The way MySQL stores data depends on storage engine. A concept of a page exists in InnoDB while in MyISAM there are blocks. In InnoDB a page is fixed 16k while a block in MyISAM is of fifferent size depending on type of the block and the size of the record in it. InnoDB stores tables in B+ tree data structures that they call indexes. The table itself ...


2

Supplementing to @MaxVernon's answer, there are some optimizations that you can do : Use different filegroup for staging tables and make sure the filegroup is on a different physical drive (or LUN). Use Instant File Initialization as well as have a proper autogrowth setting. I have a handy script to analyze autogrowth events. Since you will be emptying the ...


2

Putting those staging tables on their own filegroup allows you to do piece-meal restores. This helps get the database up-and-running more quickly in the event of a disaster recovery effort. Having those tables in their own filegroup will allow you to place them on their own set of disks which may increase performance.


1

If you will always have SchoolId then you could benefit from making the clustered index a composite key of SchoolId, Id as you won't have to have an additional index on SchoolId to avoid a table scan. Not having the additional index will let Inserts/Updates/Deletes complete faster as the transaction only has to update one index. You may find as you create ...


1

Have a single User table. Then if anything changes, such as a phone number, it changes for the lender and the borrower simultaneously without awkward reconciliation between the two tables. Your Loan table will look something like this Loan ---- LenderID -- A foreign key pointing to User BorrowerID -- Also a foreign key pointing to User ItemID ...


1

Galera (for MySQL, Percona XtraDB Cluster or MariaDB Cluster, they are essentially the same but from different vendors and base mysql version) can work perfectly with 2 machines, and it is a very common setup for substituting standard MySQL replication. Requiring 3 nodes is not a requirement of Galera, but of any cluster that cares about data consistency ...


1

Currently I am running my business on 2x SQL servers which have 38 GB of ram at all As well as the replication strategy to improve availability the though the replica taking over if the primary fails, you must factor in a god backup strategy - in what follows I'm going to assume that you already have this (if not, you need to factor it into your ...



Only top voted, non community-wiki answers of a minimum length are eligible