Should I use a three-way associative table? I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows.

There are are three tables involved in this: groups, user_types, and resources.

The requirement is that a user can add a resource for a specific combination of user types and groups. For instance, a given user might upload a ‘technical manual’ (resources) that is supposed to be only accessible to ‘painters’ and ‘foremen’ (user_types) in ‘group a’ (groups). They might then assign the ‘manual’ to only ‘painters’ in ‘group b’, and so on.

My tentative solution to this is to have a many-to-many (M:N) relationship between groups and user_types using an associative table called groups_user_types. Then, that associative table has a many-to-many relationship to resources, using another associative table unfortunately called groups_user_types_resources. I've also considered having an associative table that connects the three tables, but I'm not a DBA and I don't even know if this is a valid way to do it.

Next problem is seeing if my chosen ORM supports this type of relationship, but that's a question for another Stack Exchange site...

Does this relationship make sense? Is there a solution that doesn't connect an associative table to another table?

If I have not provided enough information, please let me know and I'll explain further.

If I have followed correctly, the two options is whether to have one or two intermediate table for the relationship between groups, user_types and resources. The answer, from a purely design point of view depends on whether that relationship is a strong or weak one. In other words, if groups_user_types (lets call it authorizations) is something that should exist if one of the other entities do not, and if an authorization is shared between resources or not. Let's see an example:

"manual1" can be accessed by:

painters in group a
foremen in group a
painters in group b

manual2 can be accessed by:

painters in group a
foremen in group a

resources
---------
id|name
 1|manual1
 2|manual2

user_types
----------
id|name
 1|painters
 2|foremen

groups
------
id|name
 1|a
 2|b

groups_user_types
-----------------
id|group_id|user_type_id
 1|       1|           1
 2|       1|           2
 3|       2|           1

groups_user_types_resources
---------------------------
resource_id|groups_user_type_id
          1|                  1
          1|                  2
          1|                  3
          2|                  1
          2|                  2

You know your application better than I do, but I do not see groups_user_types as a strong entity (no attributes, no reasons to exist if  groups_user_types_resources disappears). Maybe I am wrong, and permissions is a big part of our application, and you will give each resource a named authorisation that can be shared between resources. But if not, you can change the last two tables into:

    groups_user_types_resources
    ---------------------------
    resource_id|group_id|user_type_id
              1|       1|           1
              1|       1|           2
              1|       2|           1
              2|       1|           1
              2|       1|           2

Again, this depends on how strong groups_user_types is and if those permissions are shared between resources or not. Are the permissions for resource 1 the same entity that the same permissions for resource 1. Will you have lots of resources with the exact same permissions? Are there permissions that won't be allowed? Only you can answer that.


How to update table1 column from table3, with matching id from table2 I have 3 tables - customer, garages, the_cars. With columns like

customer           garages                  the_cars
id | car       owner_id | the_car_id       car_id | car

and need to update the_cars.car with customer.car if customer.car is null, and based by ids. Where customer.id matches with garages.owner_id, find by its garages.the_car_id the the_cars.car_id, and by it finally get the_cars.car and put into customer.car

the code I tried is, don't judge strictly, I'm new to this kind of sql queries

UPDATE `customer`
SET `customer`.`car` = `the_cars`.`car`
WHERE (`customer`.`car` IS NULL)
AND (`customer`.`id` = `garages`.`owner_id`)
AND (`garages`.`the_car_id` = `the_cars`.`car_id`)

I think that will help others too.

Your backticks indicate MySQL, where this syntax works:

UPDATE customer AS c
JOIN   garages  AS g ON g.owner_id = c.id
JOIN   the_cars AS t ON t.car_id   = g.the_car_id
SET    c.car  = t.car
WHERE  c.car IS NULL;

In Postgres you would use a different UPDATE syntax:

UPDATE customer AS c
SET    car  = t.car
FROM   garages  AS g
JOIN   the_cars AS t ON t.car_id = g.the_car_id
WHERE  c.car IS NULL
AND    g.owner_id = c.id;

The FROM clause is not standard SQL.

You have to make sure that the query finds at most one car per customer, or the result is not deterministic.


What is the best practice for free space for a SQL server database drive? I'm trying to determine the best practice for sizing a single database on a 120Gb data drive that will contain the LDF and MDF files.

I'm assuming that I don't want to set the size to be the maximum size of the drive, allowing room for moving and reorganising the data. I don't want to let it expand automatically either.

I'd like to set the size to something sensible and clear out the data regularly.

Can anyone provide any guidance?

EDIT:

some additional data...

The database is set to "Simple mode" and the transaction logs will be on the same drive as the data files.

I'm trying to determine the best practice for sizing a single database on a 120Gb data drive that will contain the LDF and MDF files.

Best practice for sizing is always to do capacity planning - how fast does your data grow now?  How fast will it grow in the next 1-5 years?

Add on some contingency, too.

Set your data file large enough to accomodate that now, and put a calendar entry on your calendar to revisit your capacity planning annually (and check your RAID controller for a bad battery or a bad drive, too).

Set your T-log file likewise, but first read Kimberly Tripp's Transaction Log VLFs – too many or too few? article.

Then read Database File Initialization, particularly the Instant File Initialization section and the Group Policy Perform Volume Maintenance requirement.

Note that index maintenance can use up a lot of space in the log.

Then you can set autogrow on both to fixed increments of reasonable size based on how long you can afford to wait for the growth, as well as keeping VLF sizes in check.

Now set a calendar entry or automated process to check on free space regularly - weekly or monthly, perhaps.


How to speed up an insertion from a huge table with postgres? I have 5 tables in my database with respectively a size of 70Gb, 500Mb, 400 Mb, 110Mb and 20 Mb.

I want to create a new table that contains all columns of all tables, so I tried 2 queries, the first one is :

select into new_table as select .. from t1 , t2 , t3 , t4 ,t5 where t1.id2 = t2.id and t1.id3 = t3.id and t1.id4 = t4.id and t1.id5 = t5.id

and the second one is :

insert into new_table select .. from t1 , t2 , t3 , t4 ,t5 where t1.id2 = t2.id and t1.id3 = t3.id and t1.id4 = t4.id and t1.id5 = t5.id

Before executing these two queries on my big data tables, I tried both on a total 1G database, the first on took only 7s and the second one approximately 10 mn.

Now, executing the first one on my huge database, made my disk full even though I had 250Gb free space before running the query, and without finishing the query so I got the follow error :

ERROR:  could not write to temporary file: No space left on device

The second one, is taking a lot of time and consuming my free disk space slowly and, as the first one, not returning the result.

What are the difference between these two queries ? Is there a way to make the insert into non transactional so as I can follow my insert steps. And I guess Postgres uses logs (journalization) so is there a way to deactivate that in order to speed up the insertion ? or I should follow another method in order to get a desired result without filling up all disk.

Ps : No triggers, only a primary key on each table.

.. a new table that contains all columns of all tables ..

Really?  That's unlikely to be useful.

  select .. from t1 join t2 join t3 join t4 ...

Without "on" clauses to tell postgres how to put the tables together, those 'join's will link every row in t1 to every row in t2 and then every row of that to every row in t3 and then every row of that to every row in t4 ...

That's an awful lot of "rows".

As always, build a select statement that returns the data that you want, then just add the "insert into" bit on the front of it.

If your tables are all the same structure, I'd suspect you'll be better served by 'union [all]'s instead of 'join's but YMMV; we can't tell because you haven't given us the structure of your tables or your intended result.


Is it possible to upgrade MongoDB from 2.0 to 2.4 directly We have a replica set with 2 members and one arbiter in production. 10gen documentation states that 2.0 to 2.2 is a binary-compatible “drop-in" upgrade and so it's 2.2 to 2.4. Is it safe to upgrade from the 2.0 version directly to the 2.4 version?

This question was posted on stackoverflow and closed as offtopic. It was suggested to me that I try to ask it here.

In general, yes, you can do this, the 2.4 binaries can be used as drop in replacements.  However, there are exceptions.  In sharded clusters for example, you have to make sure to do the meta data upgrade

Essentially, the caveats are all really deployment and feature (authentication), not version based.  Basically make sure to read the notes here:

http://docs.mongodb.org/manual/release-notes/2.4-upgrade/


Weird error on simple query in MsAccess Any idea what's wrong with the query

SELECT DISTINCT Table1.Description
FROM Table1
ORDER BY Table1.Description DESC;

I'm running this on MsAccess on a table where all fields are Memo. There are no indexes, primary or anything like that just a bunch of Memos. I'm getting the error Reserved error (-1060); there is no message for this error.

If you think my query is OK please let me know because I just had a problem with MDAC being corrupted and had to reinstall so it could be related to that.

Edit: OK I narrowed it down so you guys can try to repro:

Create a blank database (table Table1 and ID field will be there by default) in MS Access.
Add a field called Field1 of type Memo
Add some random data and
Try to run this query: SELECT DISTINCT Field1 FROM Table1 ORDER BY Field1

If you SELECT * it will work but won't be uniquer because it will consider the ID field.

If you don't get an error please let me know because that would mean my database engine is messed up.

Which MS Access version are you using?

Depending on MS Access version it's not possible to order by memo fields. You will need a workaround like:

ORDER BY Left(Description, 127) DESC


Combine two child tables based on parent Suppose I have 3 tables parent, child1 and child2.
The parent table may look as follows:
+--------+-----------+-----------+
| Column | Child1_Id | Child2_Id |
+--------+-----------+-----------+
| Name1  | 1         | 1         |
+--------+-----------+-----------+
| Name1  | 2         | 2         |
+--------+-----------+-----------+
| Name2  | NULL      | 3         |
+--------+-----------+-----------+
| Name1  | 4         | 4         |
+--------+-----------+-----------+
| Name2  | 5         | 5         |
+--------+-----------+-----------+

Currently I have tried this:
SELECT        Parent.*, Child_1.*, Child_2.*
FROM            Lead INNER JOIN
                     Child_1 ON Parent.Child1_Id = Child1.Id INNER JOIN
                     Child_2 ON Parent.Child2_Id = Child_2.Id

The problem is, that I only get returned 4 records, but optimally it should return 5 records.
I believe it is because, Child1_Id is Null. However, I still want my records from Child2_Id.. Is there any way to achieve this?

You may change your code to replace join with left join since you need all the rows from left table, code could be written as:
SELECT        Parent.*, Child_1.*, Child_2.*
FROM          Lead AS Parent
          LEFT JOIN
              Child_1 ON Parent.Child1_Id = Child1.Id
          LEFT JOIN
              Child_2 ON Parent.Child2_Id = Child_2.Id
;

Left Join - The LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.


Thanks All for your effort.

I found the solution.
CodeIgniter where_in() added single quotation automatically, which is a bug in codeigniter. I use where() in place of where_in(). Issue fixed.


Creating dummy from JSON values and using it in the WHERE clause The variable I'm interested in is an answer to a questionary, stored in a JSON column in a table called Questions. What I need is the number of users who've answered 'no' to a question coded as hasDefaulted. The way I see it, I need to first parse the JSON column, create a dummy and then use it in the WHERE clause, but the exact syntax is just beyond me. Since the data is proprietary, I'll hide the FROM clause - please trust me it's correctly written.

SELECT
COUNT(*) as count,
CREATE INDEX dummy ON dummy(cast("answer"->>'no' AS int))

FROM CLAUSE HERE

WHERE
_____CONDITION1, omitted____
AND "Questions"."code" = 'hasDefaulted'
AND dummy = 1

I've conceived an unelegant but working approach using a subquery:

SELECT COUNT(*) as has_defaulted

FROM (
SELECT answer::json->>'value' AS resp
_FROM CLAUSE HERE_
WHERE CONDITION 1
  AND "code" = 'hasDefaulted' ) as subquery
  GROUP BY subquery.resp
  ORDER BY subquery.resp ASC
  LIMIT 1)


What would be an appropriate schema for this real life scenario? A Company C provides an enterprise product to organisations (O1, O2, O3... On) and their sub-organisations (SO1 ... SOn). The product offers the organisations a database of content which is created by C. This content is accessible by all organisations O1 - On and SO1 - SOn.

At the same time an organisation O1 can create their own content that is not accessible by O2 - On.

These organisations, each have multiple sub organisations (SO1 - SOn). Content created by O1 is accessible to all suborganisations SO1 - SOn but content created by SO1 is only accessible to O1 and not accessible to SO2 - SOn

I have attached a sample schema that I think might work.

Content created (either by organisations or company) is always represented by the same entity
Organisations can get their content specific content by org_id
Organisations can get the product content by root_org_id
The Company C is represented as root_org, which is an organisation itself but has some special system privileges.

What would be an appropriate schema design to cater for the scenario mentioned? My main issue is how can Company C be represented in the schema as it is an organisation for all intentions and purposes but it also has special system / product privileges which no other organisations have. For e.g. creation of organisations (O1 - On)

Example scenario - Company C has it's own administrative dashboard to

create content and make it available to all organisations (O1 - On)
administer organisations (O1 - On) i.e. CRUD organisations
provide support - sometimes login as the organisations (O1 - On) to troubleshoot and support

I think this is a case where a super type and subtype should be considered.

The fields that are specific to an organisation go in a separate table to the main organisation, and if there are any feels that are specific to say a sub-org they are stored in a separate table and joined for crud operations.

This approach means any validation for say addresses applies to all organisations, and the validations (access permissions) for sub types apply to all entities in the relevant table.

This approach also eliminated excessive quantities of null fields.

Another possibility is the parent-child model using the same table. This is where the table records a parent which refers back to an I'd of the same table. Only reason I suggest this as a possible option is as far as I'm aware the easiest way to enable unlimited sub-organisation levels. Querying this model is relatively complicated.


Change query to improve operator estimates I have a query which runs in an acceptable amount of time but I want to squeeze the most performance possible from it.

The operation I'm trying to improve is the "Index Seek" at the right of the plan, from Node 17.

I've added appropriate indexes but the estimates that I get for that operation are half of what they are supposed to be.

I've looked for changing my indexes and adding a temporary table and re-writing the query, but I couldn't simplify it more than this in order to get the right estimates.

Does anyone have any suggestions on what else I can try?

The full plan and its details can be found here.

The non-anonymized plan can be found here.

Update:

I have a feeling the initial version of the question raised a lot of confusion, so I'm going to add the original code with some explanations.

create procedure [dbo].[someProcedure] @asType int, @customAttrValIds idlist readonly
as
begin
    set nocount on;

    declare @dist_ca_id int;

    select *
    into #temp
    from @customAttrValIds
        where id is not null;

    select @dist_ca_id = count(distinct CustomAttrID)
    from CustomAttributeValues c
        inner join #temp a on c.Id = a.id;

    select a.Id
        , a.AssortmentId
    from Assortments a
        inner join AssortmentCustomAttributeValues acav
            on a.Id = acav.Assortment_Id
        inner join CustomAttributeValues cav
            on cav.Id = acav.CustomAttributeValue_Id
    where a.AssortmentType = @asType
        and acav.CustomAttributeValue_Id in (select id from #temp)
    group by a.AssortmentId
        , a.Id
    having count(distinct cav.CustomAttrID) = @dist_ca_id
    option(recompile);

end

Answers:

Why the odd initial naming in the pasteThePlan link?

Answer: Because I used anonymize plan from SQL Sentry Plan Explorer.
Why OPTION RECOMPILE?

Answer: Because I can afford recompiles in order to avoid parameter sniffing (the data is / could be skewed). I have tested and I am happy with the plan that the Optimizer generates while using OPTION RECOMPILE.
WITH SCHEMABINDING?

Answer: I'd really want to avoid that and would use it only when I have a indexed view. Anyway, this is a system function (COUNT() ) so no use for SCHEMABINDING here.

Answers to more possible questions:

Why do I use INSERT INTO #temp FROM @customAttrributeValues?

Answer: Because I noticed and now know that when using variables plugged into a query, any estimates that come out of working with a variable is always 1. And I tested putting the data into a temp table and the Estimated is then equal with Actual Rows.
Why did I use and acav.CustomAttributeValue_Id in (select id from #temp)?

Answer: I could have replaced it with a JOIN on #temp, but developers were very confused and proffered the IN option. I don't really think there would be a difference even by replacing and either way, there is no problem with this.

The plan was compiled on a SQL Server 2008 R2 RTM instance (build 10.50.1600). You should install Service Pack 3 (build 10.50.6000), followed by the latest patches to bring it up to the (current) lastest build 10.50.6542. This is important for a number of reasons, including security, bug fixes, and new features.

The Parameter Embedding Optimization

Relevant to the present question, SQL Server 2008 R2 RTM did not support the Parameter Embedding Optimization (PEO) for OPTION (RECOMPILE). Right now, you are paying the cost of recompiles without realizing one of the main benefits.

When PEO is available, SQL Server can use the literal values stored in local variables and parameters directly in the query plan. This can lead to dramatic simplifications and performance increases. There is more information about that in my article, Parameter Sniffing, Embedding, and the RECOMPILE Options.

Hash, Sort and Exchange Spills

These are only displayed in execution plans when the query was compiled on SQL Server 2012 or later. In earlier versions, we had to monitor for spills while the query was executing using Profiler or Extended Events. Spills always result in physical I/O to (and from) the persistent storage backing tempdb, which can have important performance consequences, especially if the spill is large, or the I/O path is under pressure.

In your execution plan, there are two Hash Match (Aggregate) operators. The memory reserved for the hash table is based on the estimate for output rows (in other words, it is proportional to the number of groups found at runtime). The memory granted is fixed just before execution begins, and cannot grow during execution, regardless of how much free memory the instance has. In the supplied plan, both Hash Match (Aggregate) operators produce more rows than the optimizer expected, and so may be experiencing a spill to tempdb at runtime.

There is also a Hash Match (Inner Join) operator in the plan. The memory reserved for the hash table is based on the estimate for probe side input rows. The probe input estimates 847,399 rows, but 1,223,636 are encountered at run time. This excess may also be causing a hash spill.

Redundant Aggregate

The Hash Match (Aggregate) at node 8 performs a grouping operation on (Assortment_Id, CustomAttrID), but the input rows are equal to the output rows:

This suggests the column combination is a key (so the grouping is semantically unnecessary). The cost of performing the redundant aggregate is increased by the need to pass the 1.4 million rows twice across hash partitioning exchanges (the Parallelism operators on either side).

Given that the columns involved come from different tables, it is more difficult than usual to communicate this uniqueness information to the optimizer, so it can avoid the redundant grouping operation and unnecessary exchanges.

Inefficient thread distribution

As noted in Joe Obbish's answer, the exchange at node 14 uses hash partitioning to distribute rows among threads. Unfortunately, the small number of rows and available schedulers means all three rows end up on a single thread. The apparently-parallel plan runs serially (with parallel overhead) as far as the exchange at node 9.

You can address this (to obtain round-robin or broadcast partitioning) by eliminating the Distinct Sort at node 13. The easiest way to do that is to create a clustered primary key on the #temp table, and perform the distinct operation when loading the table:

CREATE TABLE #Temp
(
    id integer NOT NULL PRIMARY KEY CLUSTERED
);

INSERT #Temp
(
    id
)
SELECT DISTINCT
    CAV.id
FROM @customAttrValIds AS CAV
WHERE
    CAV.id IS NOT NULL;

Temporary table statistics caching

Despite the use of OPTION (RECOMPILE), SQL Server can still cache the temporary table object and its associated statistics between procedure calls. This is generally a welcome performance optimization, but if the temporary table is populated with a similar amount of data on adjacent procedure calls, the recompiled plan may be based on incorrect statistics (cached from a previous execution). This is detailed in my articles, Temporary Tables in Stored Procedures and Temporary Table Caching Explained.

To avoid this, use OPTION (RECOMPILE) together with an explicit UPDATE STATISTICS #TempTable after the temporary table is populated, and before it is referenced in a query.

Query rewrite

This part assumes the changes to the creation of the #Temp table have been made already.

Given the costs of possible hash spills and the redundant aggregate (and surrounding exchanges), it may pay to materialize the set at node 10:

CREATE TABLE #Temp2
(
    CustomAttrID integer NOT NULL,
    Assortment_Id integer NOT NULL,
);

INSERT #Temp2
(
    Assortment_Id,
    CustomAttrID
)
SELECT
    ACAV.Assortment_Id,
    CAV.CustomAttrID
FROM #temp AS T
JOIN dbo.CustomAttributeValues AS CAV
    ON CAV.Id = T.id
JOIN dbo.AssortmentCustomAttributeValues AS ACAV
    ON T.id = ACAV.CustomAttributeValue_Id;

ALTER TABLE #Temp2
ADD CONSTRAINT PK_#Temp2_Assortment_Id_CustomAttrID
PRIMARY KEY CLUSTERED (Assortment_Id, CustomAttrID);

The PRIMARY KEY is added in a separate step to ensure the index build has accurate cardinality information, and to avoid the temporary table statistics caching issue.

This materialization is quite likely to occur in memory (avoiding tempdb I/O) if the instance has enough memory available. This is even more likely once you upgrade to SQL Server 2012 (SP1 CU10/SP2 CU1 or later), which has improved Eager Write behaviour.

This action gives the optimizer accurate cardinality information on the intermediate set, allows it to create statistics, and allows us to declare (Assortment_Id, CustomAttrID) as a key.

The plan for the population of #Temp2 should look like this (note the clustered index scan of #Temp, no Distinct Sort, and the exchange now uses round-robin row partitioning):

With that set available, the final query becomes:

SELECT
    A.Id,
    A.AssortmentId
FROM
(
    SELECT
        T.Assortment_Id
    FROM #Temp2 AS T
    GROUP BY
        T.Assortment_Id
    HAVING
        COUNT_BIG(DISTINCT T.CustomAttrID) = @dist_ca_id
) AS DT
JOIN dbo.Assortments AS A
    ON A.Id = DT.Assortment_Id
WHERE
    A.AssortmentType = @asType
OPTION (RECOMPILE);

We could manually rewrite the COUNT_BIG(DISTINCT... as a simple COUNT_BIG(*), but with the new key information, the optimizer does that for us:

The final plan may use a loop/hash/merge join depending on statistical information about the data that I do not have access to. One other small note: I assumed that an index like CREATE [UNIQUE?] NONCLUSTERED INDEX IX_ ON dbo.Assortments (AssortmentType, Id, AssortmentId); exists.

Anyway, the important thing about the final plans is that the estimates should be much better, and the complex sequence of grouping operations has been reduced to a single Stream Aggregate (which does not require memory and therefore cannot spill to disk).

It is hard to say that performance will actually be better in this case with the extra temporary table, but the estimates and plan choices will be much more resilient to changes in data volume and distribution over time. That may be more valuable over the long term than a small performance increase today. In any case, you now have much more information on which to base your final decision.


Can two primary AlwaysOn servers be each other's secondary--for completely seperate sets of databases? Can two primary AlwaysOn servers be each other's secondary?
We have a situation where our primary disaster recovery site it also a valid hospital satellite (SQLB) to our main campus hospital (SQLA) in town. The same application (XAPP) is using a local database at each respective site as shown in Figure A.
In theory a disaster "could" occur at each site. So the main campus could be the DR for the satellite site and vice versa. Can AlwaysOn be used to allow each SQL Server to store the other's secondary database/replica? And, even if this is possible, is it a good idea?
Figure A: Can SQLA be the primary for SQLB's databases and vice versa?

Yes, it is possible to have two availability groups configured in this manner.  Each availability group has its own settings in regard to failover policies, so you can set up one availability group to be primary on SQLA, and configure SQLB for manual failover.  So when SQLA reboots, the AG goes offline, but doesn't fail over to SQLB unless you manually fail it over.  When SQLA comes back online, it takes ownership of the AG and brings the database online and continues on just as before.

The second availability group would be configured to be primary on SQLB, and SQLA would be set for manual failover.

This is a completely reasonable setup and makes sense in a scenario where both sites have production databases in use.


Simple query on sys.dm_db_partition_stats is running for 30 minutes Simple query on sys.dm_db_partition_stats on user database is running for close to 30 mins where as it used to complete in less than 5 seconds earlier.
Query is just to fetch the right partition for loading the data and is not reading any data from user databases. Same query ran and completed in less than 5 seconds on any other large user database. Looking at the execution plan for this db and query output messages , we observed for one Table 'sysrowsets' . Scan count 1, logical reads is hundreds of thousands where as on other databases, it is less than a thousand.
Here is the output for reference.

Table 'sysrowsets'. Scan count 888, logical reads 3227880, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

We are unable to query this sysrowsets directly as it is a underlying table. Able to query when connected via DAC but couldn't find any thing abnormal from this .
Still wondering what is the reason for this many reads and why is it taking such long time.
SQL Server Version : 2019, Compatibility Level : 150
SELECT Max(pstats.partition_number) AS PartitionNumber
FROM sys.dm_db_partition_stats AS pstats(NOLOCK)
INNER JOIN sys.destination_data_spaces AS dds(NOLOCK) ON pstats.partition_number = dds.destination_id
INNER JOIN sys.partition_schemes AS ps(NOLOCK) ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf(NOLOCK) ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS prv(NOLOCK) ON pf.function_id = prv.function_id
AND pstats.partition_number = (
CASE pf.boundary_value_on_right
WHEN 0
THEN prv.boundary_id
ELSE (prv.boundary_id + 1)
END
)
WHERE OBJECT_Name(pstats.object_id) = 'OBJECTNAME'
AND OBJECT_SCHEMA_NAME(pstats.object_id) = 'DBNAME'

I see where you are coming from. But I don't think you'll get anywhere without changing the query. This is the world we live in, with optimizers and the uncertainty regarding what query plan we get. Make the predicate seekable and you are likely to get a different plan, quite probably to the better. I tried a non-seekable predicate vs a seekable predicate on object_id when selecting from sys.dm_db_partition_stats, and did indeed get different execution plans:
SELECT * FROM sys.dm_db_partition_stats
WHERE object_id  = 123132

SELECT * FROM sys.dm_db_partition_stats
WHERE CAST(object_id AS varchar(20))  = '123132'

So, repeating Denis' suggestion in the first comment:
"Let's begin from changing your WHERE clause pstats.object_id = OBJECT_ID(N'SchemaName.TableName')"
(To avoid a long running comment thread (and Paul's wrath ;-), I put this as an answer which can be elaborated on and expanded on. )


Postgres slow select query on another table after other table alter and update I have postgresql 9.1.14 (upgraded a few weeks ago from 9.1.3, but no problems up till now).

I've recently altered several tables by adding a new UUID column and have been updating these UUIDs to match another unrelated system. This was a large (around 100'000) update for the system, which only usually has small changes.

One of the tables I updated had a trigger on it to update another table (which is used for querying) this table now runs SELECT queries upwards of 10+ times slower than it did before the update. The original tables I changed do not show any slowdown for select statements. The queries are not complicated, for instance:

SELECT iwork_id,
FROM cofk_union_queryable_work
WHERE date_of_work_std_year > 1800;

I've tried reindex, vacuum and analyse this table but no change. The table has only ever had a couple of indexes related to IDs, not to the columns which are queried on.

Running EXPLAIN (ANALYSE, BUFFERS, VERBOSE) on this database:

 Seq Scan on public.cofk_union_queryable_work  (cost=0.00..357332.10 rows=7198 width=4) (actual time=9451.804..32923.487 rows=7434 loops=1)
   Output: iwork_id
   Filter: (cofk_union_queryable_work.date_of_work_std_year > 1800)
   Buffers: shared hit=857 read=354888
 Total runtime: 32924.968 ms

This table contains about 100'000 entries.

Any thoughts on other lines of investigation?

There is no reasonable way to determine why a seqscan is slower shy of bloat and dead rows. Removing the dead rows is simple. Try VACUUM FULL cofk_union_queryable_work.
Consider indexing the columns to avoid the seq scan entirely. When you say something like this "this was a large (around 100'000) update for the system, which only usually has small changes" That's a clue there should be indexes. The only reason not to index, is to save space and write/speed. If you're not writing, index away! Feel free to \d table and update your question if you need help.
Your version of PostgreSQL is olldddd. 9.1 was released on 2011-09-11.  That's Shark Night 3D old

Most of this stuff was said by @a_horse_with_no_name, like usual, and in the comments. Also, like usual.


MySQL Join with empty table I have these tables

select * from mass_categories;
+----+--------------------------+
| id | categorie                |
+----+--------------------------+
|  1 | ESI-QTOF                 |
|  2 | MALDI-TOF                |
|  3 | FT-MS                    |
|  4 | GC-MS                    |
|  5 | IT-MS                    |
|  6 | ICP                      |
|  7 | Standard MS Analysis     |
|  8 | Analysis Scientist Cost  |
|  9 | Analysis Technician Cost |
+----+--------------------------+

select * from mass_groups;
+----+-----------+------------------+
| id | groupname | groupdisplayname |
+----+-----------+------------------+
|  2 | LOCAL     | LOCAL (U1)       |
|  4 | ACADEMIC  | ACADEMIC (U2)    |
|  5 | EXTERNAL  | EXTERNAL (U3)    |
+----+-----------+------------------+

and "mass_prices" which is empty but with this structure

CREATE TABLE `mass_prices` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `id_categorie` smallint(5) unsigned NOT NULL,
  `hour_price` float(6,2) DEFAULT NULL,
  `id_group` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_categorie` (`id_categorie`,`id_group`),
  KEY `id_group` (`id_group`)

I could launch this select which returns me what I want when the mass_prices is empty

select categorie, mg.id as groupid, hour_price
    from mass_categories mc, mass_groups mg
    LEFT JOIN mass_prices mp on mp.id_group=mg.id;
+--------------------------+---------+------------+
| categorie                | groupid | hour_price |
+--------------------------+---------+------------+
| ESI-QTOF                 |       2 |       NULL |
| ESI-QTOF                 |       4 |       NULL |
| ESI-QTOF                 |       5 |       NULL |
| MALDI-TOF                |       2 |       NULL |
| MALDI-TOF                |       4 |       NULL |
| MALDI-TOF                |       5 |       NULL |
| FT-MS                    |       2 |       NULL |
| FT-MS                    |       4 |       NULL |
| FT-MS                    |       5 |       NULL |
| GC-MS                    |       2 |       NULL |
| GC-MS                    |       4 |       NULL |
| GC-MS                    |       5 |       NULL |
| IT-MS                    |       2 |       NULL |
| IT-MS                    |       4 |       NULL |
| IT-MS                    |       5 |       NULL |
| ICP                      |       2 |       NULL |
| ICP                      |       4 |       NULL |
| ICP                      |       5 |       NULL |
| Standard MS Analysis     |       2 |       NULL |
| Standard MS Analysis     |       4 |       NULL |
| Standard MS Analysis     |       5 |       NULL |
| Analysis Scientist Cost  |       2 |       NULL |
| Analysis Scientist Cost  |       4 |       NULL |
| Analysis Scientist Cost  |       5 |       NULL |
| Analysis Technician Cost |       2 |       NULL |
| Analysis Technician Cost |       4 |       NULL |
| Analysis Technician Cost |       5 |       NULL |
+--------------------------+---------+------------+

but as soon as I add lines in this mass_prices, this select query is returning too many lines.

So to resume, I need a select which takes each "mass_groups" then join each "mass_categories" and mass_prices even if prices are not defined.

I'll use that on a webpage to allow users to change those prices.

Thanks !

You first have an (implied) CROSS JOIN between mass_categories and mass_groups, then LEFT JOIN to mass_prices. But the left join is using the id (group_id) only from one of the two tables for the join condition:

FROM
    mass_categories mc, mass_groups mg   -- the CROSS join
    LEFT JOIN mass_prices mp
      ON  mp.id_group = mg.id             -- LEFT join condition

Use both the group and the category id (and an explicit CROSS JOIN to not get a different error):

FROM
    mass_categories AS mc
    CROSS JOIN                         -- doesn't byte
    mass_groups AS mg
    LEFT JOIN mass_prices AS mp
      ON  mp.id_group = mg.id          -- LEFT join condition
      AND mp.id_categorie = mc.id
;

note:

You could still use the (implicit CROSS join) comma operator if you want but that would just make your life difficult. The two operators have different precedence, so you'd need to add parentheses as well:

FROM
    ( mass_categories AS mc , mass_groups AS mg )
    LEFT JOIN mass_prices AS mp
      ON  mp.id_group = mg.id          -- LEFT join condition
      AND mp.id_categorie = mc.id
;


Deciding what values should be used in a data warehouse dimension? I'm beginning to look at data warehousing and I'm coming across examples where, for instance, we have check payment transactions. I understand that this should be the "fact" and we would have dimensions around it that would describe the fact (account, product, etc.). However, it also contains things like "memo line" and string "transaction id" values which, as strings, don't really belong in the fact table.

My question is, should there be a separate payment "meta" dimension that contains these descriptors, or is it ok to say that something like the memo can't be reported on since it's different every time, so we should leave it out of the warehouse. I know it's somewhat of an abstract question, but I'm not finding much on this in my reading. Any suggestions would be greatly appreciated.

If you have an attribute with the same granularity of the Fact table (i.e.: "TransactionID") and it is usually used to filter one single Fact table, you don't have to create a separate table (aka Shared Dimension) for it. Instead, this attribute can live in the fact table itself.
This is know as Degenerate Dimension

You can find more about it here: Degenerate Dimensions


How to find missing data from one table that is a join of two other tables I have two tables, stock and location, with a third table that retains the level of stock at each location, stock_loc_info.

Each stock item should have a row for each location.

What SQL query would show that the row B1 is missing from stock_loc_info?

stock.stockcode
---------------
A
B

location.locno
--------------
1
2

stock_loc_info.fkstockcode  stock_loc_info.fklocno
--------------------------  ----------------------
A                           1
A                           2
B                           2

You need all combinations from the two tables, so a CROSS JOIN and then remove the ones in the 3rd table, using either NOT EXISTS or EXCEPT:

SELECT
    s.stockcode, l.locno
FROM
    dbo.stock AS s
  CROSS JOIN
    dbo.location AS l

EXCEPT

SELECT
    si.fkstockcode, si.fklocno
FROM
    dbo.stock_loc_info AS si ;


Creating High Availability Cluster with PostgreSQL I am new to database design.  I have a task to create a high-availability cluster.  I Googled a lot, however I haven't found any good way to do this on PostgreSQL.

Where should I start and what are the steps involved in creating a high-availability cluster?

There are many ways to set up an HA cluster for PostgreSQL. Some ways are big and tough.

The easiest method is the following:

sync your db to a standby server
Install heartbeat and do a asymmetric clustering

This method is the easiest method to build a HA cluster with graceful degradation  .


Question about composite indexes and WHERE + GROUP BY Alrighty so I understand how composite indexes work with WHERE but what if a column in the middle of the index is in the GROUP BY, as follows:
CREATE TABLE `test` (
      `a` VARCHAR(255) NOT NULL,
      `b` VARCHAR(255) NOT NULL,
      `c` VARCHAR(255) NOT NULL,
      INDEX(`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

SELECT * FROM test WHERE a = ? AND c = ? GROUP BY b

Would MySQL stop using the index before c, or would it be able to use all three indexes in the above query since b is in the GROUP BY.  I haven't been able to find any resources on how exactly GROUP BY utilizes a composite index in a case like the above.  In this case I'm using MySQL for database flavor.

The index isn't covering since B isn't part of the WHERE clause as a predicate, so the index is not applicable to this query. In other words, the index stores the data sorted by A then by B then by C, but since you're only filtering on A and C, there's no direct way to seek out C without B. You might end up seeing an index scan or table scan get used instead, in this case.


correct security and user setup for SQL Server database I am currently on setting a sql server database on a database server. A front end web application running off a web server communicates to the database.

What is the best approach for setting up users to access the database etc.?

My plan was:

On web server, create a local user which the application pool runs under and assign it the necessary rights
On database server, create a SQL user that can log in to the database, have necessary permissions on tables/DBs etc. This user is what is included in the connection string from the MVC web application to connect to the database.

Is this the best approach - I have read in to mixed mode SQL Server with SSPI etc. and I am confused a little as to the best secure setup. Any tips or further information would be great.

I have read that SSPI is a means of providing an integrated secure trust relationship between the web servers and the back end database servers, without having to transmit sensitive user and password information. Buy I am wondering if what I have outlined sufficent. Note sensitive information such as SSN, credit card numbers etc may be stored in the SQL Server 2012 database.

I would use Application Roles. See this link http://msdn.microsoft.com/en-us/library/ms190998.aspx
Also, Integrated security is more secure than Mixed Mode, so I would use a domain account, not a SQL account, to avoid having to have 2nd tier authentication enabled.


How to write a multi row AND query I have a table that keeps tags and their reference. For example:

| tag  | tableName | rowId |
| ---- | --------- | ----- |
| tag1 | table1    | 1     |
| tag2 | table1    | 1     |
| tag1 | table2    | 1     |

I would like to form a query that says something like "get distinct table name and rowId WHERE it has all of these tags: 'tag1', 'tag2'"

The objective of this table is to make it easy to look up rows in tables by tags. Maybe I'm going the complete wrong direction with how to solve this.

How can I do this and keep performance high?

Thanks for the help.

OK, I believe that I have a satisfactory answer.

SELECT tableName, rowId
  FROM tags
  WHERE tag IN ('tag1', 'tag2')
  GROUP BY tableName, rowId
  HAVING COUNT(tag) = 2;

I'm pretty new to this, but this query allows it to find all matches and group them by tableName and rowId. Before returning the results it limits the rows to those that matched exactly the number of tags being searched on.

If anyone knows a better way to do this I'm up for suggestions. Thanks.

Edit 07/06/2018

@akina and @RickJames both pointed out that this query only works if the rows are unique and offered two solutions to address this. See the comments below.


It is possible to extract Extented Properties using SQL Developer Data Modeler? I'm using SQL Developer Data Modeler in a oracle server. No problem, I can connect to SQL Server, list tables and generate a really good report. But what I need is to extract the extended properties  from tables and columns. I'm looking all over the internet but what I found is:

MS SQL Server
Support for following objects – database, login, user and roles with their privileges and permissions, default, rule, user-defined type, computed and identity columns, partitioned tables using partition function and partition scheme, primary and secondary XML indexes, triggers on tables and views, stored procedures, indexes on views, synonyms
    Domains can be created as user-defined types
    **Extended properties for each object can be defined and generated in DDL**
    Database and schema controlled generation of DDL

http://www.oracle.com/technetwork/developer-tools/datamodeler/featurelist-167684.html

From the official manual. But I don't want to generate manually the properties with the software. I  have already all properties of extended properties from almost all tables.

Or can I make a Data Dictionary with my good SQL Server Management Studio? I can only see reports from disk usage, performance metrics and etc.

This is what I have for now:

I'm not sure I understand what your use case is, but you can retrieve them by querying the sys.extended_properties system view.

SSMS doesn't provide a helpful way to view extended properties outside of the individual dialogue window for a single object anyways, so this is probably your best route.

Neither tool will be able to build you a data dictionary necessarily, but you can generate a simple one by querying from any tool.


Primary key vs. unique index: performance difference with foreign keys? Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following:

A primary key
A unique index

It has nothing to do with performance as such, but it is a conceptual
 issue.

Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an
 index which happens to be unique, for instance because you add the primary
 key to it.

As per the Scenario:

Primary Key

When you want to keep a identifier for each row. So each row can be obtianed by the Key value.(since pk unique and not null)

Unique Key

When you want to keep secondary identifier where already the primary exists in the same table. So each row can be obtained by your PK or UK. Unique will allow null but only once(what is the use to allow single null).

Note:

Both can be either clustered or non-clustered. When you create a constraint you are allowed to choose which type of index you want to use for these keys.

Sources:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/290619be-b892-4f52-99e0-d8e2ff80aec4

http://bytes.com/topic/sql-server/answers/83999-unique-constraint-vs-unique-index-ms-sql-2000-a


Linux Error: 111: Connection refused LSNRCTL I have a problem starting my listener, I am using Centos 6.5 and Oracle 12cR1.

Database has been installed successfully.

Now my issue is when I start my listener, the following error is shown:

LSNRCTL> service
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gaurav)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
   Linux Error: 111: Connection refused
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "tuts" has 1 instance(s).
Instance "tuts", status UNKNOWN, has 1 handler(s) for this service...
 Handler(s):
  "DEDICATED" established:0 refused:0
     LOCAL SERVER
 The command completed successfully

My hostname is gaurav

So i create an entry in hosts file as

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
 192.168.1.1        gaurav      oracle

Also, I have created a listener.ora file, with contents as follows:

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

sid_list_listener=
   (sid_list=
   (sid_desc=
   (oracle-home=/home/oracle/app/oracle/product/12.1.0/dbhome_1)
   (sid_name=tuts)))

I am able to ping my host gaurav.

Can anyone please help me in fixing the issue?

EDIT

[oracle@gaurav ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 05-JUN-2014 22:38:57

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

Starting /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr:  please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /home/oracle/app/oracle/product/12.1.0/dbhome_1 /network/admin/listener.ora
Log messages written to /home/oracle/app/oracle/diag/tnslsnr/gaurav/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gaurav) (PORT=1521)))
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gaurav)(PORT=1521)))
  TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
  Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                05-JUN-2014 22:38:59
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/oracle/product/12.1.0/dbhome_1 /network/admin/listener.ora
Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/gaurav /listener/alert/log.xml
 Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gaurav)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tuts" has 1 instance(s).
Instance "tuts", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

To me it looks like you did not start the listener but it is started. Your problem seems to be the tcp connection and not the ipc. So the listener is running, you can not reach it using tcp. Test this using telnet gaurav 1521

If the telnet does not give a connection, a firewall is blocking you. Stop/edit the firewall.


Can't convert this type of ER diagram into SQL or relational tables I have stumbled upon a problem during database design. Let me explain what it is all about:

Contract has some simple attributes that describe him, and may have annexes and dynamics of payment.

Therefore I have decided to make main table Contracts, and placed simple attributes as columns.

Dynamics of payment is a complex attribute of Contracts table. It can have none, one or multiple values. Searching through the Internet I have learned that this is called multivalued attribute, and have found this example that seems to illustrate my case very well ( Dynamics of payment is equivalent to the Hobbies table in the linked example ).

As for Annexes, it is a complex attribute of Contract. Contract can have many of them, one or none.

Annex has exactly the same simple attributes as contract, and can also have Dynamics of payment. Annex' relationship with Dynamics of payment is the same as the relationship between Contract and Dynamics of payment.

To sum-it-up, Annex and contract have everything the same, the only difference is that Annex is a complex attribute of Contract.

Using this as a reference, I have made the sketch of my ER diagram:

This is the first time I see this type of relationships so I ask the community to help me convert this ER diagram into SQL or relational tables.

I apologize for not providing more info, if you have further requests please leave a comment and I will update my post immediately.

Thank you for your understanding and help.

Best regards.

I will agree that @MladenUzelac's approach is probably the more common pattern for this situation, but there is a potential theoretical flaw that can expose itself over time and make life difficult. That flaw is: a Contract, at the end of the day, is not an Annex. Of course, I emphasized "potential" because currently I am not privy to whether or not they are currently considered the same thing (i.e. Contract === Annex). However, to a degree that would not change my recommendation to find out that they are the same. Anything is subject to change at any point in time, even if highly unlikely, so having the same nature today does not imply that they will always have the same nature, and often enough things do grow in different directions. So yes, they share the same attributes today, and they share the same relationship with Dynamic of Payment today; but tomorrow is another day and the nature of things changes, even for irrational reasons ;-).

Don't get me wrong: there definitely are times when a self-referencing entity is the appropriate solution, but I think that requires that the entities be of the same nature. The two most common examples I can think of are:

Employee / Manager: It makes sense to have a [ManagerID] (or I prefer [ManagerUserID]) field in the [Employee] table that is a self-referencing FK back to [UserID]. This works because a manager is an employee.
Departments: In corporate / retail hierarchies, it makes sense to have a [ParentDepartmentID] field in the [Department] table that is a self-referencing FK back to [DepartmentID]. This works because the "parent" Department is a Department.

In both of these cases, the relationship between parent and child is not a primary determining property of those entities. Instead, the relationship is a property just like the name of the entity. In both cases, the relationship is not critical to the existence of the child entities, and the entities defined as "children" exist even without the relationship. On the other hand, if an Annex requires a Contract in order to exist, well, that is something quite different.

What it comes down to is: just because two things look the same does not mean that they are the same.

Storing two "similar yet different" entities together, while reducing some amount of tables / joins / code, leads to some pragmatic concerns, even if only in terms of physical storage (i.e. the DB layer).

I have worked for years on a system, built before I got there, in which this pattern was used several times and for situations that were nearly identical to what is being described here. I am not sure if the entities had the exact same attributes on day 1, but over time at least, they began to diverge and take on different properties from the other. In some cases fields were added that were appropriate to one of the entities but not the other, so we just had to know when a NULL field was appropriate for that type of row, or if it was a bug. Other times a "multivalued attribute" approach was taken with the structure being effectively: [EntityID], [AttributeID], [AttributeValue].

As expected, in order to get a list of "parent" entities we would query with WHERE ParentEntityID IS NULL. And to pull a list of "child" entities we would query with WHERE ParentEntityID IS NOT NULL. It got fun when we needed both entities because they were really a property of another primary entity, so we needed to show both as separate properties of the primary entity. What was fun was how that self-referencing join hurt performance (though it is possible that a slight change in indexing strategy could have fixed that). But it got super-duper fun when we placed that EntityID in other entities as FKs, and we only wanted to reference one of those two entity types, but it was not always guaranteed that we wanted the "child" entity (though that was the most common entity to refer to). In the end, as I started creating new tables and refactoring old ones, I included the entity name in the name of the FK field in the related entity, just to have it "self documented" for anyone looking through the tables needing to create new queries. And in some cases I added two FK fields, one for each entity type, just to avoid that costly self-join (and it was safe to denormalize since that parent-child relationship could never change once created).

So, I would start out heading in the same direction as @Peter in terms of separate tables for Contract and Annex (even if they are identical, or nearly identical, structures), but I would approach the handling of their relationship to Dynamic of Payment differently. I seem to recall having tried the two different FK fields thing in the past and not really liking how it played out. At the very least it seems less manageable over time as other entities are added that can relate to Dynamic of Payment a you need to keep adding FKs back to new parents. And you also need to have a CHECK CONSTRAINT on the table ensuring that one, and only one, of those FK fields is NOT NULL.

Instead, I would create a separate bridge / relationship table for each of Annex and Contract to relate to Dynamic of Payment. Yes, when new entities are added that can relate to Dynamic of Payment you will need to add new bridge / relationship tables, but somehow I feel better about that because it is not altering the structure of Dynamic of Payment: the Dynamic of Payment entity is the same whether 1 or 100 entities relates to it.

(the SQL below is use Microsoft SQL Server T-SQL semantics)

Main Entities

CREATE TABLE SchemaName.[Contract]
(
  ContractID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  ContractName VARCHAR(50) NOT NULL,
  ContractDetails NVARCHAR(MAX) NULL
);

CREATE TABLE SchemaName.Annex
(
  AnnexID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  ContractID INT NOT NULL REFERENCES SchemaName.[Contract] (ContractID),
  AnnexName VARCHAR(50) NOT NULL,
  AnnexDetails NVARCHAR(MAX) NULL
);

Dynamics of Payment

The [DynamicsOfPaymentType] table is a lookup table that assumes that the "dynamics" are named properties, possibly mimicking an enum in the app layer. I chose TINYINT (0 - 255) as typically most things have less than 255 distinct properties / attributes. If needing more than 255 then go up to the next higher size. But don't go higher than you need as that field is copied into the relationship tables which can get large, especially with lots of Contracts and Annexes that each have several DynamicOfPayment entries.

Please note that the PK on DynamicsOfPayment is a composite key made up of [DynamicsOfPaymentID] and [DynamicsOfPaymentTypeID]. While technically only the [DynamicsOfPaymentID] field is needed in the PK as it is guaranteed unique, having the [DynamicsOfPaymentTypeID] field allows us to push that property up to the relationship table for use in constraining each entity to having only one instance of any particular DynamicsOfPayment property. Please see notes in next section for more details.

CREATE TABLE SchemaName.DynamicsOfPaymentType
(
  DynamicsOfPaymentTypeID TINYINT NOT NULL PRIMARY KEY, -- SMALLINT if > 255 are possible
  DynamicsOfPaymentType VARCHAR(50) NOT NULL
);

CREATE TABLE SchemaName.DynamicsOfPayment
(
  DynamicsOfPaymentID INT IDENTITY(1, 1) NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL REFERENCES
                          SchemaName.[DynamicsOfPaymentType] (DynamicsOfPaymentTypeID),
  Value NVARCHAR(MAX) NULL,
  PRIMARY KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

Main entity to Dynamics of Payment relationships

Please note that the PK of both relationship tables is a composite key made up of the main entity ID and the [DynamicsOfPaymentTypeID] field--not the [DynamicsOfPaymentID] field. The reason, as mentioned in the prior sections notes, is to enforce a single instance of any particular DynamicsOfPayment property for each main entity. If an entity can have multiple instances of a DynamicsOfPayment property, then it is a simple matter of adjusting the PK on that particular relationship table to be a composite key made up of the main entity ID and the [DynamicsOfPaymentID] field (or if you prefer, both the [DynamicsOfPaymentTypeID] and the [DynamicsOfPaymentID] fields). As you can see, this model can allow for not only the relationships to enforce singular instances of related properties OR for the relationships to accept multiple related properties, but it also allows for mixing the two as the relationship tables don't have to use the same combination of fields for their PK: some relationships can be constrained while others not. So maybe Annex is limited to a single instance of any particular DynamicsOfPayment property, but Contract is allowed to have multiple instances of a particular property.

CREATE TABLE SchemaName.[ContractXDynamicsOfPayment]
(
  ContractID INT NOT NULL,
  DynamicsOfPaymentID INT NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL,
  PRIMARY KEY (ContractID, DynamicsOfPaymentTypeID),
  FOREIGN KEY (ContractID) REFERENCES SchemaName.[Contract] (ContractID),
  FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
              SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

CREATE TABLE SchemaName.[AnnexXDynamicsOfPayment]
(
  AnnexID INT NOT NULL,
  DynamicsOfPaymentID INT NOT NULL,
  DynamicsOfPaymentTypeID TINYINT NOT NULL,
  PRIMARY KEY (AnnexID, DynamicsOfPaymentTypeID),
  FOREIGN KEY (AnnexID) REFERENCES SchemaName.[Annex] (AnnexID),
  FOREIGN KEY (DynamicsOfPaymentID, DynamicsOfPaymentTypeID) REFERENCES
              SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
);

Pro Tips

Yes, there will be slightly more tables, joins, and app code using this model. But, just keep in mind that refactoring app code is much easier than refactoring tables, especially once the app is live and data is in there. Take the small hit of slightly more time now to code for this and save yourself many, many hours over the years of trying to make changes to simpler models, or as projects come up in a few years, just deciding to put in hacks / band-aids because the business won't go for the 10 - 20 (or more) hours it will take to make changes to the simplified model that saved you 1 - 2 hours now. Trust me, database programmers / architects are not cheap (at least not these days) and my employers have spent an egregious amount of time and money--in the form of my salary (and the salaries of my fellow DB coworkers)--for me to refactor poor design choices that saved maybe even 1 day of initial dev time, but over the next 3 years cost them a week (a week that I was not adding new features)
You can coordinate the cleanup of the relationship tables by marking the FK as ON DELETE CASCADE. The syntax might differ slightly between RDBMS's, but I would expect that most, if not all, of them support cascaded deletes. This way, if you delete a Dynamic of Payment entry, you don't need to worry about its entry in either of the relationship tables.
Depending on what RDBMS you use, you might even be able to coordinate the creation of the relationship records. Microsoft SQL Server has a very cool OUTPUT clause that returns the changes made by a DML operation. Hence, you can insert a record into [DynamicsOfPayment], and within the context of that operation (and hence, transaction), you can do the insert into the relationship table. Assume you have an insert proc that accepts an extra parameter for @ContractID that is not initially needed for the [DynamicOfPayment] table. It can be used as follows:

INSERT INTO SchemaName.[DynamicsOfPayment] (DynamicsOfPaymentTypeID, Value)
OUTPUT @ContractID,
       INSERTED.DynamicsOfPaymentID, -- server-generated auto-increment field
       INSERTED.DynamicsOfPaymentTypeID
INTO   SchemaName.[ContractXDynamicsOfPayment]
       (ContractID, DynamicsOfPaymentID, DynamicsOfPaymentTypeID)
VALUES (@DynamicsOfPaymentTypeID, @Value);

Now you just need two stored procedures (since each one need to reference a different relationship table):

DynamicsOfPayment_CreateForContract (@DynamicsOfPaymentTypeID, @Value, @ContractID)
DynamicsOfPayment_CreateForAnnex (@DynamicsOfPaymentTypeID, @Value, @AnnexID)


Is there any way to force at least one column NOT NULL I have a schedule_days table that is having id, day, date columns.

  id - auto increment, primary key
  day - weekday - enum(weekdays) default not null
  date - a specific date - default not null

The schedule is for the train time table. That will have the same routine for all weeks except for any specific date, such as any festival. Thus I have set both null as I want to set either day or date.

If the date is specified, then I can get the day from it, so it doesn't make sense to ask or force the user to set day, which may cause human error.

I may use TRIGGER but would like to know if there is any better way to do in MySQL - 8.0.* version

Table Query

CREATE TABLE IF NOT EXISTS schedule_days
(
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    day  ENUM ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY') DEFAULT NULL,
    date DATE DEFAULT NULL,
    INDEX staff_index (day),
    CONSTRAINT day_day_check CHECK (`day` IS NULL + `date` IS NULL = 1) # check constraint
) ENGINE = INNODB;

If you use version 8.0.16 or above, use CHECK constraint.

Something like

CHECK COALESCE(`day`, `date`) IS NOT NULL

for "at least one is not null", or

CHECK `day` IS NULL + `date` IS NULL = 1

for "strongly one is not null".


How to retrieve master log position and master log file in shell script I'm writing a shell script for point in time recovery for MySQL database.For the below statement I need to specify the start position and end position of a log file.

mysqlbinlog --start-position=368315 --stop-position=368316 /usr/local/mysql/data/mysql-bin.123456

How can I retrieve the log position and log file.

There is a cleaner approach

SMS=/tmp/show_master_status.txt
mysql -ANe "SHOW MASTER STATUS" > ${SMS}
CURRENT_LOG=`cat ${SMS} | awk '{print $1}'`
CURRENT_POS=`cat ${SMS} | awk '{print $2}'`
echo ${CURRENT_LOG} ${CURRENT_POS}

This logs into mysql only once.


Monitoring the rows in a table I'm trying to achieve the following tasks in SQL Server. I have a table that I need to monitor its growth. The table shouldn't be containing more than X amount of rows, if there are that many amount of rows I need to know.
I was planning on creating a stored procedure that would return to me the amount of rows found in the table that I want to monitor.
The stored procedure would also evaluate the number of rows in the table and if its greater I want to store the time that this happen in a table that I have designated for this purpose(this will give me a clue if this is happening at certain times).
Then I would put this SP in a sql agent job and run it every hour, and have it email me when the event occur.

However I'm not sure if this is the best way to monitor how many rows there are in the table. I was thinking I could also make a trigger on the table and on every insert have it check the amount of rows that there are, and then write it to the monitoring table, and then I would check this table every so often with a sql agent job and have it email me as well.

Is there a way I could make a job email me as soon as the table goes over the threshold limit that i have set? I'm not sure what would be the best way to implement this.

If you have all the logic that you want to check within your stored procedure, then storing the time "that it happened" would only be storing the time you actually checked the table. If you are executing this stored procedure every hour via an Agent job then you only know it happend withing that previous hour.

Using a trigger on the inserts would provide a more detailed timestamp as to when the table went over your threshold.

Now for immediate notification I would likely look at SQL Agent Alerts. I don't work with triggers all that much, but would think you could do something similar to the following logic in the trigger:

Check row count of table
If over threshold use RAISERROR to generate event text (custom sys.message) to include your table name
Write entry to your monitoring table, with a timestamp

Now you would need to create a custom message using sp_addmessage (BOL), so you can use it with the RAISERROR. Then your SQL Agent Alert would just check for that message text. When the alert is fired (meaning it caught the message) you configure it to notify you by email.


What is the primary key on a temporal table? If I use the following table design to capture history...:

CREATE TABLE MyTable (
    insertion_timestamp TIMESTAMP,
    deleted_flag BOOLEAN,
    natural_key INT,
    attribute VARCHAR
);

... then what should the primary key be?

(The history mechanism is INSERT-only: updated rows are inserted with a different insertion_timestamp, and deleted rows are inserted with a different timestamp and the deleted_flag set to true.)

I'm thinking PRIMARY KEY (insertion_timestamp, deleted_flag, natural_key), but the only reason for including deleted_flag is to account for the possibility of a row being inserted and then (soft) deleted immediately, sooner than the next tick of the granularity of the TIMESTAMP data type. This feels overly paranoid...

I'm thinking PRIMARY KEY (insertion_timestamp, deleted_flag, natural_key), but the only reason for including deleted_flag is to account for the possibility of a row being inserted and then (soft) deleted immediately, sooner than the next tick of the granularity of the TIMESTAMP data type. This feels overly paranoid...

I don't know if it's too paranoid, but it's reasonable to just stipulate that no two rows may share the same insertion_timestamp and natural_key, and make it the responsibility of the clients doing the inserts to handle the edge case.  It simplifies using the table considerably if you can assume that those combinations are unique—plus in the tripartite key you suggested, it's tricky to order the rows in the triply-paranoid case where there are two immediate "soft" deletes in a row.

So my recommendation would be PRIMARY KEY (natural_key, insertion_timestamp) (in that order—but I'm guessing natural_key is going to be queried for more often, which could be wrong).

Also, you should seriously consider the following additions:

Add an inactive_timestamp column to the tables.  This allows you to query for rows that were active at a point in time.
Think carefully about what the timestamps mean.  Is the table supposed to represent the history of the real-world values ("Joe's phone number was 555-5555 until January 22, then it was 666-6666 from January 22"), or history of modifications to an "eternal" value?  Your schema looks like it's going after the latter, but make sure you understand which is going to be needed.


Get The Latest values For Same ID/Duplicate ID ----------------I Want To Show Only Red marked product_id,on_hand,created_at, In this image Product_id have same Duplicate id but i want only last entry or latest product_id information and Show if last entry value is zero .

Try this:

SELECT primary_table.product_id, primary_table.on_hand, primary_table.created_at
     FROM
`table_name` primary_table,
(SELECT MAX(purchase_id) as pId FROM `table_name` Group by product_id) second_table
     where primary_table.purchase_id = second_table.pId


Ola Hallengren - DatabaseBackup SQL Version - Backup to Azure requirements I see that the DatabaseBackup requires SQL Server 2012 SP1 CU2 (11.03339). I have a Server using SP1 (11.0.3000), and I have verified Backing up to azure blob storage does work.

Is there a reason the Hallengren scripts require CU2?

From Microsoft's documentation:

  Manage your backups to Microsoft Azure: Using the same methods used to
  backup to DISK and TAPE, you can now back up to Microsoft Azure
  storage by specifying URL as the backup destination. You can use this
  feature to manually backup or configure your own backup strategy like
  you would for a local storage or other off-site options. This feature
  is also referred to as SQL Server Backup to URL. For more information,
  see SQL Server Backup to URL. This feature is available in SQL Server
  2012 SP1 CU2 or later. This feature has been enhanced in SQL Server
  2016 (13.x) to provide increased performance and functionality through
  the use of block blobs, Shared Access Signatures, and striping.


What happens when I reach the last id with MySQL InnoDB? So perhaps I don't know the technical name of the 'id' column which has autoincrement so if a similar question exists, I cannot find it. I was wondering about the limit of the number of rows in an InnoDB table and the id reference that goes with it, and what happens when the limit is reached (assuming it can be reached).

It's a risk I have to consider. I have a table which contains a series of tens to hundreds of records which can be added/removed regularly. Assuming my product reaches enormous success (?), assuming records are changed regularly, the id field will quickly tick by. Add a year or more, even five years...

I asked a similar question to this on stackoverflow some time ago and from what I recall I was just asked not to worry about it but nobody could direct me to a hard answer.

Anyone care to share?

Thanks...

Do not use GUIDs -- they have a serious performance problem at scale.  (This is because of their randomness.)

Within 5 years (probably within 1 year), you will decide on some significant change and this 'last id' question will be moot.

You are "removing" regularly?  Why?  If you are doing a REPLACE or DELETE, you probably have some other unique key on which you are finding what to remove.  Consider making that the PRIMARY KEY and eliminate the AUTO_INCREMENT.

I have a PRIMARY KEY on virtually every table I make, yet only about 1/3 are AUTO_INCREMENT.

Beware of INSERT IGNORE and REPLACE (and others) -- they pre-allocate AUTO_INCREMENT ids, but may not use them.


Clustered Index - Trade-off Queries VS Inserts? I'm working on improving database performance of my application and now it comes to the part of clustered index. I don't have experience with it so I would like to ask.

My application caveats

In my application I have the concept of Show.

This means that a user can create/open a Show and then it will access only the objects related to that Show (Actors, clothes, items, etc). So it is really commom to have queries using "...WHERE ShowId = X"

Also we do have quite a lot of inserts because of integrations (we have a job running daily that would get +/- 30k new rows to the database, all in the same Show) and deletions are not that commom... The user may delete a Show (which is really taking quite a long time).

The usage of the application is usually like:

User creates a Show based on a "Master Show" (which copies a lot of data changing only the ShowId)
User navigates around some screens to check this data
User calls an algorithm that proccess the input of this show and generate a lot of outputs (30k+ inserts again, on the same show)
User analyses these outputs

The question

Based on that, I thought that the best clustered key would be on [ ShowId, id ] for every table. But then, reading this documentation, I have doubts if this wouldn't make it worse given the number of inserts or if, because the inserts are made on the same ShowId, it would be ok.

I don't have access to the PRD database nor to a copy of it for testing. I tested with the dev database but it is so small it didn't seem to have any impact at all.

Can someone help?

30k to 40k rows of Insert/update/Delete is not considered a big problem.

CI should be the most searchable column of each table.

Yes CI do have impact on DML due to index fragmentation.As you are saying there can 30k insert in same showid.You can minimise this be tuning correct Fill Factor.

Choosing correct fill factor will avoid frequent page split.

Correct application design (Not look and feel),DB design and optimize query is very important.

As per you explanation one user insert 30k rows and next minute it will analyse those 30K rows.That means Insert/Select both are equally important in your application and Select have more weight-age than insert.

So CI will be there in each table.only concern is correct choice of KEY and fill factor.

  I mean, it would be slower if it was with different ShowId? My
  understanding is, since this is CI, then each insert would have to
  physically shift positions to insert. Is that true? If so, why having
  the same ShowId makes any difference?

Sorry If i am wrong.See if there are 30k insert,and showid is CI and also there are different Showid then insert will be relatively slower.Why ?
Because CI are ordered and optimizer will first search for  for correct sequence of each page then insert showid one by one.Example Showid 2 will be inserted between 1 & 3 and showid 34 will be inserted between 33 & 35 and so on.So optimizer task will increase.

In case of similar showid,optimizer have to search just once.

I think this link will clear your doubts,

https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

Of course first read about fill factor also.

And Rebuild Index every 1-2 days.


Query to return zero if nothing exist I have a table With currency, paymenttype and invoiceamount. I have to write a query to get currency, paymenttype and total invoiceamount made.

This is pretty simple with group by. But actually I have three payment types - 0, 1, 2. The data in the table is

    Currency   PaymentType Invoice Amount
    Aaa.        0.           100
    Aaa.        1.           200
    Aaa.        1.            50
    Bbb.        0.           150
    Bbb.        1.           100
    Bbb.        2.           100

My query is

Select currency, paymenttype, sum(invoiceamount) as total
from table
group by currency, paymenttype

Result

      Currency  paymenttype total
      Aaa.       0.          100
      Aaa.       1.          250
      Bbb.       0.          150
      Bbb.       1.          100
      Bbb.       2.          100

As Aaa. does not have paymenttype 2 it should also show a row with 0 value like below.

      Aaa.      2.     0

How to do this?

If the list of payment types and currencies are known, you can do this with CTEs.

with paymenttypes as (
  select 0 as ptype,
  Union select 1 as ptype,
  Union select 2 as ptype ),
currencies as
( select 'Aaa' as currency
  union
  select 'Bbb' as currency ),
sourcedata as (
  select currency, ptype as paymenttype, 0 as amount
  from currencies, paymenttypes
  Union all
  select *
  from data)
select currency, paymenttype, sum(amount)
from sourcedata
group by currency, paymenttype;

It basically creates a Cartesian product of all currency, paymenttype pairs with a 0 amount, then joins it with the source data.

If your lists of payment types and currencies are all in lookup tables, then substitute them in.

Also note there are other ways, using COALESCE etc.

SQL Fiddle link,


SQL Server 2008 R2 Express password overwrite I'm using a program that requires SA log-in to SQL Server. We had issues where some 3rd party support numpty changed the SA password and never informed us, so the program couldn't log-in.

I've accessed our information via the SQL Server Management Studio. Fortunately, the 'remember password' option had been checked on the machine.

Via the object explorer, I've accessed the SA log-in credentials and changed the password accordingly. However, every time I re-load the SQL Server Management Studio, the password re-sets to the original one.

When I test the connection of the program that uses the SA log-in by manually entering the new password it appears to work OK. But when I let the program start-up automatically, Windows services is detailing that the log-in is failing due to incorrect log-in details - which I'm guessing is because SQL Server keeps reverting back to the old password?

Can anyone advise how I stop it from reverting back to the old password and overwrite with the new password definitively? (SQL Server 2008 R2 Express) I'm hoping that should help fix my more specific problem with the other program?

As stated in the comments to your question SQL Server will not revert a password back to the previous one used. I believe the problem you are having is that your application has the sa password stored somewhere that it needs to be saved to. That is likely why you are receiving the error regarding login credentials. I would suggest checking the documentation for your application (if it is COTS) to see where the password for the SQL Login is set at.

If the application is a Windows Service you will likely find the password set in some configuration file or registry key. I would hope not in plain text, but no product is perfect. If you find the password is not in plain text (garbled or unreadable) this would indicate that the application has some configuration API (GUI or command-line/CLI) that needs to be used to set that password so the application stores it where it needs to see it.


Storing date increments in tables I'm working on a ASP/MS SQL project that performs a lot of calculations based on dates and time.  In one case, the system will lookup a value based on the nearest half hour increment throughout the day.

When designing the database should I create a lookup table with just the increments, for example: 00:00:00, 00:30:00, 01:00:00, and then create another table with the value and a FK to the hour increments?  Or should I include the time increments in another column with the values?  Or should I not include the increments and build the business logic within a function or stored procedure?

You can create a time table which stores time in minutes. Then have a column as the foreign key to the record which has got half an hour difference to it. If you need to get half an hour before a time slot and half an hour after it, you will have 2 columns. One referring to the record which has got the half an hour prior value and another has got the half an hour after value. In means the foreign key referenced to the table itself. The table definition would be something like below:

Create Table TimeTbl

(

Id int not null primary key,

TimeValue time not null,

HalfHrBeforeId int null,

HalfHrAfterId int null

)

HalfHrAfter Foreign Key referenced TimeTbl(Id)

HalfHrBeforeId Foreign Key referenced TimeTbl(Id)

In this table you will have:

1, 00:00:00, null, 30

2, 00:01:00, null, 31

...

30, 00:30:00, 1, 60

31, 00:31:00, 2, 61

...

60,01:00:00, 30, 90

...

So if you query this table each includes all time records of a day in minute, you will have foreign key to half an hour before and after and can get and compare their values, to choose the minimum one(the closest half an hour increment).


Auto Switch DB to primary back (persistent mode?) when available in a High Availability Always On settings We have Always On Availability Group settings, and we have a preferred DB that needs to be in primary all the time even after it reboots.

Once a month or so, these 2 servers DB1 and DB2 reboot for upgrade/updates, however whichever come back online first becomes the primary with HA on, but we prefer to always set DB1 as primary when it comes back online no matter what.

Are there any configs for this, or should we create a trigger?

Thank you

You don't really want to do this automatically because:

A long-running transaction could be running on DB2, and you wouldn't want to interrupt it (especially given the problems with single-threaded rollbacks)
DB1 might come back online and SQL Server might be available, but the databases might have problems (or just be starting up)
DB1 may not be in sync (if DB1 went down for a while, it might take quite some time for it to get back in sync with DB2)


Is it possible to have a SQL Server cluster with Hadoop? I know how Hadoop works. Is there any possible way to have SQL Server run under Hadoop to build a cluster based on SQL Server and not Drill or Hive.

Just a wild question.

Any good tutorial/documentation?

You cannot run SQL Server under Hadoop but you can query Hadoop data via T-SQL in SQL Server 2016.
This next edition of SQL Server includes a technology called PolyBase which allows access of Hadoop data and Azure blob storage from T-SQL. Tutorials are available from Microsoft at the bottom of the linked page.

SSIS for SQL Server 2016 has Hadoop connection managers available also: Hadoop File System Task.


to pivot or not to pivot - schema design question I am implementing something similar to this schema
http://www.databaseanswers.org/data_models/event_reservations/index.htm

It occurs to me that the pivot table for Customer_Bookings would just keep on growing for the primary database interaction (insert Customer_Booking) and become a huge list. All the bookings, past, present and future, for each customer and each event will be contained in this table. The size of Customer_Bookings will be many times the size of any other table in the database and will only continue to get bigger with each new Event and Customer. None of the Customer_Bookings get deleted as new Events are created. Is this normal? Cause for concern? Can the table become too big over time?

I am new to database design but couldn't find the information about this type of know-how anywhere...a link to a good reference would be appreciated too.

MySQL should not have trouble handling a large table like that.  You have it indexed (because of the foreign keys) so most queries against the table should use one of the indexes and be fairly fast even if there are a huge number of rows in the table.


MySQL failover - Master to Master Replication My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered through different geographical locations, each containing a MySQL 5.5 server with several DBs, that for the time being are merely slave-replicating from the production server - the objective up until now was just checking the latency and general resilience of MySQL replication.

The plan however is to add a Master-Master replication environment between two servers in two separate locations, and these two instances would handle all the DB writes. The idea wouldn't necessarily imply concurrency; rather the intention is having a single one of the instances handling the writes, and upon a downtime situation using a DNS Failover service to direct the requests to the secondary server. After the primary comes back online, the b-log generated in the meantime in the secondary would be replicated back, and the DNS Failover restored the requests back to the first one.

I am not an experienced administrator, so I'm asking for your own thoughts and experiences. How wrong is this train of thought? What can obviously go wrong? Are there any much better alternatives? Bash away!

Thanks!

I'm thinking of doing exactly as you describe using Amazon's Route 53 DNS Failover.  So the idea is to setup 2 databases as master-master

db1.domain.com
db2.domain.com

Then do a Route53 "failover" set on

db.domain.com
-> primary is db1.domain.com => TCP-based health check on port 3306
-> secondary is db2.domain.com  => TCP-based health check on port 3306

I have not gotten to this point yet but that is my plan


Is sys.stats_columns incorrect? Let's say I have a table Foo with columns ID1, ID2 and a composite primary key defined over ID2, ID1. (I'm currently working with a System Center product that has several tables defined this way with the primary key columns listed in the opposite order they appear in the table definition.)

CREATE TABLE dbo.Foo(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED (ID2, ID1)
);
GO

-- Add a row and update stats so that histogram isn't empty
INSERT INTO Foo (ID1, ID2) VALUES (1,2);
UPDATE STATISTICS dbo.Foo;

The key_ordinal column in sys.index_columns shows the index columns in the same order they were declared in the composite primary key:

SELECT t.name, i.name, c.column_id, c.name, ic.index_column_id, ic.key_ordinal
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
JOIN sys.columns AS c
ON ic.column_id = c.column_id
AND ic.[object_id] = c.[object_id]
WHERE t.name = 'Foo';

The histogram also shows the statistics in the same order:

DBCC SHOW_STATISTICS ('Foo',PK_Foo);

However, sys.stats_columns shows the columns listed in the inverse order (ID1, ID2).

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.[object_id] = sc.[object_id]
JOIN sys.columns AS c
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o
ON o.[object_id] = c.[object_id]
WHERE o.name = 'Foo'
AND s.name = 'PK_Foo';

Books Online says stats_column_id is a "1-based ordinal within set of stats columns," so I was expecting the value 1 to point to the first column in the statistics object.

Is this a bug in sys.stats_columns or a misunderstanding on my part?

I've verified this behaviour occurs on current builds of SQL Server 2005, 2008, 2008 R2, 2012, and 2014.

sys.stats_columns seems to reflect the order within the statistics object in other situations, for example:

CREATE TABLE dbo.Foo2(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
  ID3 int NULL,
  String VARCHAR(10) NULL,
CONSTRAINT [PK_Foo2] PRIMARY KEY CLUSTERED (ID2, ID1)
);

GO

INSERT INTO Foo2 (ID1, ID2, ID3, String) VALUES (1,2,3,'String');

CREATE STATISTICS ST_Test ON Foo2 (ID3, String);
CREATE STATISTICS ST_Test2 ON Foo2 (String, ID3);

DBCC SHOW_STATISTICS ('Foo2',ST_Test);
DBCC SHOW_STATISTICS ('Foo2',ST_Test2);

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.[object_id] = sc.[object_id]
JOIN sys.columns AS c
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o
ON o.[object_id] = c.[object_id]
WHERE o.name = 'Foo2'
AND s.name LIKE 'ST_Test%';

Here is another example where sys.stats_columns appears to return the correct data, this time for statistics on an index:

--drop table dbo.Foo3
CREATE TABLE dbo.Foo3(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
  ID3 int NULL,
  String VARCHAR(10) NULL,
CONSTRAINT [PK_Foo3] PRIMARY KEY CLUSTERED (ID2, ID1)
);

GO

INSERT INTO Foo3 (ID1, ID2, ID3, String) VALUES (1,2,3,'String');
UPDATE STATISTICS Foo3;

CREATE INDEX IX_Test ON Foo3 (ID3, String);
CREATE INDEX IX_Test2 ON Foo3 (String, ID3);

DBCC SHOW_STATISTICS ('Foo3',IX_Test);
DBCC SHOW_STATISTICS ('Foo3',IX_Test2);

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.[object_id] = sc.[object_id]
JOIN sys.columns AS c
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o
ON o.[object_id] = c.[object_id]
WHERE o.name = 'Foo3'
AND s.name LIKE 'IX_Test%';

This seems to be a long standing error:

swasheck - March 5, 2015 posted:

https://connect.microsoft.com/SQLServer/feedback/details/1163126

  MSDN notes that sys.stats_columns.stats_column_id is "1-based ordinal
  within set of stats columns." However, it seems to actually reflect
  table definition order. Altering index order is not reflected in
  sys.stats_columns.

Max Vernon and James Lupolt seem to agree based on their comments/encouragement.


Should I normalize a relational database design if I know the data will never be updated? I'm designing a PostgreSQL database that will be storing an archive of data that I am absolutely certain will never be updated, only read (these are archives will be pointing to real data at a point in the past, and since the past can't be changed, these rows won't either).

There will be a lot of data (up to terabytes), so I am trying to optimize the design as much as I can.

Should I worry about normalizing the data if I know I will never have to update it? Consider I could simplify the design substantially by keeping data that would be stored in two tables in a normalized version in one single table.

Thanks!

It is not clear what the source of your data is, but your comment "these are archives pointing to real data at a point in the past, and since the past can't be changed, these rows won't either!" implies that your "new" database is a copy of subset of the "current" data.

Assuming that your are coping/moving a subset of old production data to a designated archive location, I would suggest not making any changes in the database design.

I would expect users have existing queries that they use to extract required production data.  As an end user I would expect my existing queries to work on the archive data.  If you make any design changes you put my existing solutions at risk.

TL:DR; Don't make changes to archive copies of data, existing solutions for retrieving specific data, are dependent on existing structure.

Edit

Per your most recent comment This data exists but not in database format. Like say in giant CSV files in a hard disk, that can't be queried You have a large flat file.  In this case you designing a database structure makes sense.  As Jonathan says in his comment You may get space savings by normalizing the data

You also need to consider the cost of your time; Is the potential space savings worth the cost of your time to achieve it? Are you moving the data so it can be queried, will the amount of time you spend result in notable resource saving for the end users? There are several business case questions that you would need to decide internally.

Now if this is a spare time project with no deadlines, it could be a great learning experience, go for it.

If you do decide get excentric with the design, I strongly suggest that you don't make design changes after the import AND you keep your import code.  Business user have a habit of finding just one more data set (disk) that needs go with the other that they promised was the only one that would ever exist.


Is `log_bin` necessary on slave in master-slave MySQL replication? I'm running out of disk space on a slave due to MySQL binary logs (mysql-bin.000xxx) being stored on the slave.

Here's the relevant portion of the slave's my.cnf:

binlog_format  = mixed
log_bin        = /var/log/mysql/mysql-bin.log
sync_binlog    = 1

I'm currently catching up to Master, so I expect to see a bunch of mysql-relay-bin.00xxxx's on Slave, but I'm not sure why it also needs mysql-bin.000xxx's.

The Master also has a lot of mysql-bin.000xxx's, but I expect it there.

If the slave is not a master to other slaves, then you do not need binary logging on the slave

To reclaim the space of those logs on the Slave immediately, run this

mysql> STOP SLAVE;
mysql> RESET MASTER;
mysql> START SLAVE;

This will hose all binary logs on the Slave.

You could then remove or comment out log_bin from the my.cnf of the Slave and restart mysqld


What are possible causes for consistently volatile INSERT performance in MySQL tables? I have a Java program running on a VM that is sequentially feeding a queue of single row INSERTs through a single connection to a MySQL server on the same VM. The queue execution is not artificially capped - once a query is done executing, the next one gets executed immidiately. The queue is also never empty.

The data gets inserted into four different MyISAM tables. Two of these have a UNIQUE column and the other two each have a UNIQUE CONSTRAINT across two columns. Most of the data generated by the program already exists in the db, but instead of checking for duplicates, the program just throws the data at the db and ignores duplicate errors.

Now I am logging the execution time of every query with the java program. Obviously that execution time is slightly skewed because it is timed by Java and not MySQL, but I am able to verify the accuracy of the average execution time by dividing a longer time frame by the total number of queries executed in that time frame. So I will assume that the timing from my Java application is accurate.

Onto the actual problem: I have observed that the average time per INSERT is going up and back down by up to factor two fairly consistently (but not gradually) every few hours or minutes. That increase or decrease is consistent across the different kinds of INSERTS, so it should be independent of the table used.

MySQL is steadily using 22% RAM of the VM, Java is using about 7%.
The VM has four CPU cores. Two are never used, Java is using 10-30% of one and MySQL is using 30-60% (never more) of another one.

There's nothing else running on the VM.

If I fire up a second application, queueing INSERTs for another set of tables, CPU usage goes up by 50-100% on each cores and the average execution time goes up a bit (I am not sure about this because the execution time is so inconsistent anyway).

Once the average execution time was at the minimum for about 20 hours, usually it's at the minimum or at the maximum for a few hours a day and then goes up and down a bit for most of the day.

I am pretty clueless about the possible causes for this seemingly random volatility in performance, so any ideas are greatly appreciated.

Edit: I should add that I am experiencing the same phenomenon when doing 100-row to 1000-row bulk INSERTs into InnoDB tables.

The simple answer is: it is not possible to guess what is the source of contention.

The more detailed: MySQL 5.6+ can instrument a break-down of query execution time, so that you can see if performance is stalled waiting on IO, locks, etc.

The diagnostic feature is called performance_schema.  The easiest way to start using it, is to download MySQL Workbench 6.1 and chose "Performance Reports" (under Performance).