SQL Server 2012 Log File Best practice We have a 2012 SQL Server and I am looking into how it is being backed up currently. We do have a shadow copy that runs hourly. We also have a nightly full backup that is taken. DB runs in FULL mode.

My issue is my log files seem to be growing very large quickly. I have read that log backups as often as every 5 minutes. I know that shrinking the log files often is not a good solution. I want to make sure that we have full recovery options.

If I take a nightly backup, then take a log backup right after will that leave a break in consistency if there happens to be transactions going on at that time?

Taking a log backup should free up space in the log to prevent file growth, leaving a file large enough for a days activities is ok. Or should I be taking log backups more often?

New to the SQL Server world and want to learn all the best practices I can. I am also just trying to ensure we are doing everything we can to safeguard our backups. Thanks.

Think of the interval between log backups as the maximum amount of data you can afford to lose.

Realize you need to restore a "FULL" backup, plus all the log backups taken since the full backup in order to get back to the most recent point in time.

Also, practice your restore strategy.  This is perhaps the single most important thing you can do vis-a-vis backups.  You can have all the backups in the world, but if you don't practice how to restore them, they won't do you any good in the event of disaster.

Regarding your questions:

  My issue is my log files seem to be growing very large quickly. I have read that log backups as often as every 5 minutes. I know that shrinking the log files often is not a good solution. I want to make sure that we have full recovery options.

Taking a full backup and a log backup will leave your log in a state where it is pretty close to empty (unless you have something like log shipping, mirroring, always on, etc, that relies on log records).  The log will subsequently continue to grow until the next log backup is taken, at which point SQL Server will begin re-using space in the log file.  For some interesting reading about log growth, please see this question.

  If I take a nightly backup, then take a log backup right after, will that leave a break in consistency if there happens to be transactions going on at that time?

Any transactions that have not been committed at the precise moment a log backup begins will be automatically rolled back during the recovery process.  However, if you have a later log backup where those transactions were committed, they will be committed during recovery once that later log backup is restored.

Typically, a sound production level backup strategy consists of the following minimal steps:

Take a full backup once per day, at some low-activity point.  For instance say 2:00am.
Take log backups every 15 minutes, retaining every log backup taken since the last nightly full backup.
Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

If once-per-day full backups are unworkable, you can instead do:

Once per week, take a full backup.
Once per day, take a differential backup.
Once every 15 minutes take a log backup.  Retain every log backup taken since either the last full backup, or the last differential backup, whichever is most recent.
Restore the full backup, and every intervening log-backup once per day onto a non-production box to ensure the backup files and recovery procedures actually work.

You may be interested in looking at Ola Hallengren's backup solution which is very highly regarded in the SQL Server community.


How does subpartitioning actually work and what physical files are created? I can't understand how the subpartitioning works in mysql.
I am reading the following example:

create table invoices (
customer_id int not null,
product_id int not null,
amount decimal (8,2) not null,
entry_date date not null default '2006-01-01',
store_id int not null
)
partition by range (year(entry_date))
subpartition by hash(customer_id)
subpartitions 4 (
partition p0 values less than (2008),
partition p1 values less than (2009),
partition p2 values less than (2010),
partition p3 values less than (2011),
partition plast values less than MAXVALUE
);

I can not understand or visualize how this is formed in the HD.
I think that 4 * 5 = 20 physical files are created. Is this correct?
And what data are stored together? Is there a "clustering" of 2008 dates for all customers with the same hash? I am confused on that. Also I am confused how the data are fetched.
Is the customer_id hash used first to determine the range partition or the opposite. What are the exact steps to fetch the data?

UPDATE
Another example. I did the following:

mysql> create table nums_composite (
    -> id int, happened datetime not null,
    -> primary key (id, happened)
    -> ) engine=InnoDB
    -> partition by range (hour(happened))
    -> subpartition by hash(id) subpartitions 2
    -> (
    -> partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than MAXVALUE
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> select partition_name, subpartition_name as sub, partition_method as method,   partition_description ,table_rows from information_schema.partitions where table_name='nums_composite';
+----------------+-------+--------+-----------------------+------------+
| partition_name | sub   | method | partition_description | table_rows |
+----------------+-------+--------+-----------------------+------------+
| p0             | p0sp0 | RANGE  | 10                    |         17 |
| p0             | p0sp1 | RANGE  | 10                    |         24 |
| p1             | p1sp0 | RANGE  | 20                    |         20 |
| p1             | p1sp1 | RANGE  | 20                    |         17 |
| p2             | p2sp0 | RANGE  | MAXVALUE              |         13 |
| p2             | p2sp1 | RANGE  | MAXVALUE              |          9 |
+----------------+-------+--------+-----------------------+------------+
6 rows in set (0.00 sec)

It seems that I am right it creates hash_no* subpartition files but where are they?

root@jim-Linux:/# find . -iname "*_composite*"
./var/lib/mysql/partioning_chapter_test/nums_composite.frm
./var/lib/mysql/partioning_chapter_test/nums_composite.par

Why can't I see the actual files?

Sample in Windows

I ran the following on my Laptop in MySQL 5.6.15

mysql> use test
Database changed
mysql> drop table if exists nums_composite;
Query OK, 0 rows affected (0.70 sec)

mysql> create table nums_composite (
    -> id int, happened datetime not null,
    -> primary key (id, happened)
    -> ) engine=InnoDB
    -> partition by range (hour(happened))
    -> subpartition by hash(id) subpartitions 2
    -> (partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than MAXVALUE);
Query OK, 0 rows affected (1.91 sec)

mysql> show create table nums_composite\G
*************************** 1. row ***************************
       Table: nums_composite
Create Table: CREATE TABLE `nums_composite` (
  `id` int(11) NOT NULL DEFAULT '0',
  `happened` datetime NOT NULL,
  PRIMARY KEY (`id`,`happened`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (hour(happened))
SUBPARTITION BY HASH (id)
SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.01 sec)

mysql>

I went to the OS and ran the following:

C:\>cd \MySQL_5.6.15\data\test

C:\MySQL_5.6.15\data\test>dir nums_comp*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.15\data\test

08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp1.ibd
08/31/2014  04:09 PM             8,594 nums_composite.frm
08/31/2014  04:09 PM                96 nums_composite.par
               8 File(s)        598,514 bytes
               0 Dir(s)  686,691,409,920 bytes free

C:\MySQL_5.6.15\data\test>

Reason you cannot see the files ?

I can see my data files because I have innodb_file_per_table enabled by default in MySQL 5.6.

mysql> show global variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql>

In your case, you must have innodb_file_per_table disabled. The actual table was inside the system tablespace file ibdata1 (usually located in /var/lib/mysql)

My Guess

You are using a version before MySQL 5.6
innodb_file_per_table is set to 0 because of one of the following:

You are running on defaults (no my.cnf)
You have my.cnf with innodb_file_per_table=0

How to fix this:

STEP #1 : Enable innodb_file_per_table

Add this to the my.cnf

innodb_file_per_table=1

STEP 2 : Restart mysql

service mysql restart

STEP 3 : Regenerate the table

CREATE TABLE nums_composite_new LIKE nums_composite;
INSERT INTO nums_composite_new SELECT * FROM  nums_composite;
ALTER TABLE nums_composite RENAME nums_composite_old;
ALTER TABLE nums_composite_new RENAME nums_composite;
DROP TABLE nums_composite_old;

The .ibd files should now be visible

GIVE IT A TRY !!!

UPDATE 2014-08-31 16:37 EDT

You asked me

  I am in ubuntu.Does it change anything?

The only thing that changes is where my.cnf should be. Run this

ls -l /etc/mysql/my.cnf

If the file exists, open it in an editor and add innodb_file_per_table=1 under the [mysql] header

If the file does not exist, run this

echo "[mysqld]" > /etc/mysql/my.cnf
echo "innodb_file_per_table=1" >> /etc/mysql/my.cnf

Then, you can do STEP 2 and STEP 3


SQL Server 2008 R2 Most Efficient Full Text Search Field Type In SQL Server 2008 R2, what is the most efficient column type to store data enabling full text search?

I can currently see two options, nvarchar and varbinary.

Which one performs better and why?

EDIT

If, for example, at the moment all I need is text, but in the future it might change, will there be a performance hit if i use varbinary?

varbinary is for binary data.

nvarchar is for character data.

Regardless of what the full text index is capable of indexing, use a data type suited to the domain.

Also, you will be able to see the contents of a nvarchar column in directly SSMS.

Best Practices for Choosing a Language When Creating a Full-Text Index
Best Practices for Integrated Full Text Search (iFTS) in SQL 2008

Performance issues are more likely to do with the frequency of maintenence of indexes, and hardware such as where your data is stored (as in a separate Filegroup), and memory usage, and indexing type (full population, incremental, manual, and auto change tracking population).

  Note that SQL Server 2008 full-text search is more aggressive in
  memory usage and may use more I/O; 64‑bit architectures are better
  than 32‑bit in doing crawl tasks in general.

  Sometimes crawl can be too aggressive in committing memory and this
  could cause sqlserver/system to have fewer resources than needed. It
  is highly recommended that you do the following:

  Limit the memory used by the crawl (full population) by setting max server memory to an appropriate value to prevent the crawl task from
  taking all available memory.
  Increase the page file size if applicable.

  Indexing performance is generally superior to SQL Server 2005.
  However, in certain situations there may be some performance
  degradation as follows:

  Crawl on large documents (~>8KB). In non 64‑bit platforms, this might be slower in some scenarios.
  varchar type data. This is because SQL Server 2005 does a better job with the varchar type than with a text type, whereas in the new
  full-text architecture there is no difference in theory between varchar data and text data.
  Noninteger keys, especially GUID columns used as a full-text key
  Too many unique keywords. It has been shown in random document crawl modes that performance can be slower than SQL Server 2005 (when
  uniquekeycount > 80 million).

  Ref: SQL Server 2008 Full-Text Search: Internals and Enhancements


Parameter sniffing = On with Parameterization = forced. Which takes precedence? These two settings seem to contradict each other. One forces plan parameterization so that only one plan gets created. The other allows for multiple plans

If you have Parameterization = forced, should parameter sniffing be false, or does one take precedence?

Additional details

Regarding the comments below, parameter sniffing is a database scoped option as of SS 2016. (was a surprise to me too).  See also: SQLShack: SQL Server 2016 Parameter Sniffing

These do not conflict. Parameter sniffing is the process that uses the parameter values when building a plan. Forced parameterization turns literals into parameters in queries.

So having both on will turn literals into parameters, but not use those values when estimating cardinality.

More on parameter sniffing

More on forced parameterization


How do I calculate a field based on multiple non-numeric records I have a several tables that I have joined together to get raw data.

Some information about my query in the most basic form:

Table1 has a column [FacilityID] that is a unique identifier in that table (as well as several other columns, that are unimportant to the question).
Table2 has a column [FacilityID] that matches Table1.[FacilityID], but not all of the records from Table1.[FacilityID] are found in Table2.[FacilityID]
Because of this, Table1.[FacilityID] is Leftjoined to Table2.[FacilityID].
Table2 has additional columns [ActivityCode], [ActivityDate], [ActivityEval?], and [ActivityOutcome]
Table2.[ActivityOutcome] is only populated if Table2.[ActivityEval?] = "Yes"
Table2.[ActivityCode] contains several string values, all nonnumeric.
For simplicity and purposes of this example, Table2.[ActivityCode] contains values "EVALA", "EVALB", "EVALC", "EVALD", "EVALE", "EVALF", "EVALG", "EVALH", "EVALI", "EVALJ", "NOEVALA", "NOEVALB, "NOEVALC", and "NOEVALD" (not in that order, nor with the same prefixes or suffixes)

Right now my query is pulling: Table1.[FacilityID], Table2.[ActivityCode], Table2.[ActivityOutcome], Where (Table2.[ActivityDate] is between a begin date and end date) And Table2.[ActivityEval?] = "Yes"

My problem lies within the codes listed in Table2.[ActivityCode].

Codes: EVALA, EVALB, EVALF, EVALG, EVALH, EVALI, and EVALJ can all stand on their own and are counted as a single event
Codes: EVALC, EVALD, and EVALE cannot be counted as a single event, as they would only happen with at least 1 code from above.
If EVALA, EVALB, and EVALC are all entered within the timeframe set by the query, then an EVAL3 event has taken place.
If EVALA, EVALB, EVALC, EVALD, and EVALE are all entered within the timeframe set by the query, then an EVAL5 event has taken place.

I need a calculated field in my query to list out either an EVAL3 or EVAL5 event, or list out the individual codes from Table2.[ActivityCode].  This is to help our data entry folks quickly check if all their codes are entered into the system for credit.  (We've been having a hard time getting consistent data entry with thousands of data points entered every year, by hundreds of people.)

I cannot, for the life of me, find how to complete this task out on the google.

Can anyone help me?  Will I just end up with a Crosstab query that I have to filter in Excel?  I really want this to happen all in one fell swoop, so I don't spend a "billion" hours each time the data is pulled refiltering in Excel.

I'm not the best with SQL or VBA, but I definitely try my damnedest to understand. I've tried using the Expression Builder and Concatrelated, but I am not getting the results that I want. That's why I'm finally turning here.

It's an access db, so yes, it runs on SQL. I don't necessarily need a single row, unless the 3 or 5 criteria are true and there are no other codes. Say for 1 FacilityID EVAL3 is TRUE, but they also have an EVALD, but not an EVALE, I want one row for EVAL3 and another for EVALD. Once an EVALE is added for the facility, I only want one row for the facility to show EVAL5. Any other EVAL codes should be listed out on separate rows.

Also, the ActivityCode may not be unique for the FacilityID in the query. There may be times that an EVALA occurs as part of an EVAL3 or EVAL5 event, but later in the timespan pulled for the query (usually a year) another EVALA occurs because of new information about or a change at the Facility. In this case the new EVALA is acceptable and should be counted in the activities for that facility, but not counted as part of the EVAL3 or EVAL5 event.

the solution that I thought involves creating a calculated column on table2 called the_level which will give you the exact level depending on the ActivityCode.

the second step is on your query, where you join table1 and table2,
there you need to identify whether or not the item is
entered within the timeframe set by the query, and change the the_level accordingly.

the_level has been created as PERSISTED, you did not mention indexes and performance but you said billion hours, so you will need some indexes there.

this is an example of calculated column with case and not null.

I have, in the past, create calculated columns that reference a different table,
but in my environment the performance was not the best. Somehow it slowed writes, but I had an index on the calculated column. I am not stating this is a rule, but I would monitor it before doing it on a busy updated table.

    create table table2(

    [ActivityCode]  varchar(10) not null,
    [ActivityDate]  datetime,
    [ActivityOutcome] int,
    [ActivityEval?] char(3) not null default('Yes')
    )
    go

    -- add the facilityID, as a primary key at least for the exercise
    alter table table2
    add [FacilityID] int not null identity(1,1) primary key clustered
    go

    --insert the activity codes
    insert into table2 ([ActivityCode])
     select  'EVALA'
union all select  'EVALB'
union all select  'EVALC'
union all select  'EVALD'
union all select  'EVALE'
union all select  'EVALF'
union all select  'EVALG'
union all select  'EVALH'
union all select  'EVALI'
union all select  'EVALJ'
union all select  'NOEVALA'
union all select  'NOEVALB'
union all select  'NOEVALC'
union all select  'NOEVALD'
     go

     -- have a look at the table
     select * from table2

     -- test the calculation of the the_level column
     select [ActivityCode],
            the_level =
            CASE WHEN ActivityCode    LIKE '%EVALA%' OR
                      ActivityCode    LIKE '%EVALB%' OR
                      ActivityCode    LIKE '%EVALF%' OR
                      ActivityCode    LIKE '%EVALG%' OR
                      ActivityCode    LIKE '%EVALH%' OR
                      ActivityCode    LIKE '%EVALI%' OR
                      ActivityCode    LIKE '%EVALJ%'THEN 1
              ELSE
              CASE WHEN ActivityCode    LIKE '%EVALC%' OR
                        ActivityCode    LIKE '%EVALD%' OR
                        ActivityCode    LIKE '%EVALE%' THEN 2
                 ELSE 0
              END
            END
    FROM TABLE2

    ----------------------------------------------------
    -- ADDED the_level as A persisted column to table2
    ----------------------------------------------------
     alter table table2
    add  the_level AS ISNULL(
    CASE WHEN ActivityCode    LIKE '%EVALA%' OR
                      ActivityCode    LIKE '%EVALB%' OR
                      ActivityCode    LIKE '%EVALF%' OR
                      ActivityCode    LIKE '%EVALG%' OR
                      ActivityCode    LIKE '%EVALH%' OR
                      ActivityCode    LIKE '%EVALI%' OR
                      ActivityCode    LIKE '%EVALJ%'THEN 1
              ELSE
              CASE WHEN ActivityCode    LIKE '%EVALC%' OR
                        ActivityCode    LIKE '%EVALD%' OR
                        ActivityCode    LIKE '%EVALE%' THEN 2
                 ELSE 0
              END
    END,0) PERSISTED

    -- after this do in your query something like this
    -- maybe the case in the line
    --  WHEN date_entered BETWEEN '2016-04-01' AND '2016-04-07'
    -- does not need to exist

    -- you will need to add your business logic on the query below
    -- but at least you have the level calculated

    select T1.[FacilityID]
    t2.[ActivityCode],
    T2.[ActivityOutcome],

    the_FINAL_level = CASE
        WHEN date_entered BETWEEN '2016-04-01' AND '2016-04-07' THEN
             CASE WHEN T2.THE_LEVEL = 1 THEN T2.THE_LEVEL+1
                  ELSE
                   CASE WHEN T2.THE_LEVEL = 2 THEN THE_LEVEL+1
                   ELSE -- ADD BUSINESS LOGIC HERE
                   END
              END
        ELSE 0
    END

    FROM TABLE1 T1
    INNER JOIN TABLE2 T2
            ON T1.[FacilityID] = T2.[FacilityID]
    WHERE T2.[ActivityDate] BETWEEN '2016-04-01' AND '2016-04-07'
      AND T2.[ActivityEval?] ='Yes'


Pitfalls of renaming tables in sql server I've thought of an approach for a process that builds a fairly large table (2million rows), now I'm canvassing opinion for it.  What's going to bite me with this renaming approach to a making a table 'live'?

Background:
The table (data.CatExt) is an overlayed version of our product catalogue. It builds daily in a complex procedure (getting data from lots of different areas and deciding which source trumps when there are conflicts - the complexity is in the business logic, not the technological side).  All the work and any tables required to get the final output happen in a 'build' schema, and the final table lives in the 'data' schema.  The schemas have different permissions.

The table is pretty wide, about 100 fields. We've taken the approach of building it from scratch each day, rather than using a 'change/additions/deletions' approach, because of the nature of the data sources (horrible to work out what is going to require a change to the final table.)  So we build a new version of the table each day, and then (after it passes some automated checks) allow that to become the new live version.

New approach:
So here's where the new approach comes in.  What's the best way to replace the live version? The simplest option is to truncate and replace with the new data, but that seems like a lot of writing of data unnecessarily. We could do an update over each field, but with 100 fields that's a lot of nasty coding/comparison to find any differences.  So I thought, why not rename the table?  It would go like this:

Live version data.CatExt exists and is useable through steps 2 and 3.
Build new version of the table build.CatExt.
Checks, indexing etc over build.CatExt all complete - ie, it's perfect.
Drop (well, I'm too chicken - probably rename) data.CatExt, rename build.CatExt to data.CatExt.

Voila, new version, no unnecessary moving of data and associated long time while the live version is being locked.

Please point out the flaws. (Be gentle, I'm new! :) )

Another option is to use partition switching once you have both tables set up create a switch out table.

Then do a simple switch command like this:

ALTER TABLE [Table Name] SWITCH
   TO [Table Name]_SwitchOut

ALTER TABLE [Table Name]_SwitchIn SWITCH
   TO [Table Name];

Then simply drop the in and out tables.


Restored Database is different from the original I have recently performed a restore of our data-warehouse staging database onto a separate instance in our dev environment.   After the restore I did a comparison and it looks like the database sizes are different.  Staging_Original is 8246 MB while Staging_New is 7571.63 MB.  It appears that 2 tables in the new db are missing and for some reason the new database has a table that doesn't exist in the old database.  How is this possible if I've performed a full backup and restore?  How can I get an exact copy of one database and restore it into a second instance?  Both of the model databases appear to be the same as well.

Backup scrtip:

BACKUP DATABASE [BI_Staging] TO  DISK = N'G:\BI_Staging.bak'
  WITH NOFORMAT, NOINIT,  NAME = N'BI_Staging-Full Database Backup',
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset
  where database_name=N'BI_Staging'
  and backup_set_id=(select max(backup_set_id)
    from msdb..backupset where database_name=N'BI_Staging' )
if @backupSetId is null
begin
  raiserror(N'Verify failed. Backup information for database ''BI_Staging'' not found.', 16, 1)
end
RESTORE VERIFYONLY FROM  DISK = N'G:\BI_Staging.bak'
  WITH FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Restore Script:

USE [master]
GO
ALTER DATABASE [BI_Staging] SET  RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [BI_Staging] FROM
DISK = N'G:\BI_Staging.bak'
WITH  FILE = 1
,  MOVE N'BI_Staging' TO N'E:\MSSQL10_50.BI_DSS2\MSSQL\DATA\BI_Staging.mdf'
,  MOVE N'BI_Staging_log' TO N'F:\MSSQL10_50.BI_DSS2\MSSQL\Tlogs\BI_Staging.ldf'
,  NOUNLOAD,  REPLACE, RECOVERY, STATS = 10
GO

You've used NOINIT in your backup, and then FILE = 1 in your restore. So it seems like you have multiple backups in the .bak file, and you aren't pulling the right one for the restore.  In fact you can see that in your RESTORE VERIFYONLY script you specify the FILE = @backupSetId but in the actual restore you just hard-code FILE = 1.

This is one of many reasons I don't like reusing a single file for multiple backups. What does it gain you? Not a whole lot, unless you are creating a gazillion backups every day and exceeding the capacity of file count by NTFS. I would much rather have a single .bak file with a timestamp in the name or something, then it is very easy to identify oldest or most current backups both visually and programmatically, restores are simpler, and it is also easier to purge expired backups.

For now, try a much simpler command with a unique filename and WITH INIT (I don't think you really need all of those other options)...

BACKUP DATABASE [BI_Staging]
  TO DISK = N'G:\BI_Staging_This_Time_For_Sure.bak' WITH INIT;

...and then restore that file.


How to find when change tracking was enabled? How can I determine when change tracking was enabled on a table in a SQL database?

Assume that this table has a regular cleanup routine, which means that looking at the oldest record in the change tracking table won't provide the right answer.

Run the following query in the context of the database in question in order to get the Change Tracking table names used on the database. (Query via Kendra Little on BrentOzar.com)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
    select sct1.name as CT_schema,
    sot1.name as CT_table,
    ps1.row_count as CT_rows,
    ps1.reserved_page_count*8./1024. as CT_reserved_MB,
    sct2.name as tracked_schema,
    sot2.name as tracked_name,
    ps2.row_count as tracked_rows,
    ps2.reserved_page_count*8./1024. as tracked_base_table_MB,
    change_tracking_min_valid_version(sot2.object_id) as min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 on it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 on
    sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 on
    it.object_id = ps1. object_id
    and ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 on
    sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 on
    sot2.object_id = ps2. object_id
    and ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210);
GO

Find the create_date for the change_tracking table(s) found in the first result set by querying sys.internal_tables in the context of the change tracking database.

SELECT * FROM sys.internal_tables;


percentage of count in Oracle I'm trying to get percentage of payments according to status every day. I found a MySQL query which throws an error at Oracle when I use '=' inside SUM parentheses:

"ORA-00907: missing right parenthesis"

How could I convert this into Oracle accepted script?

SELECT pdate,
       COUNT(status) total,
       SUM(status='successful') successful,
       SUM(status='failed') failed,
       SUM(status='refunded') refunded,
       SUM(status='successful')/COUNT(status)*100 successful_percent,
       SUM(status='failed')/COUNT(status)*100 failed_percent,
       SUM(status='refunded')/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate;

That is invalid SQL for Oracle - you can't apply sum on a boolean expression (it works on MySQL because it treats boolean expressions as numbers). You need to use a CASE for that:

SELECT pdate,
       COUNT(status) total,
       count(case when status='successful' then 1 end) successful,
       count(case when status='failed' then 1 end) failed,
       count(case when status='refunded' then 1 end) refunded,
       count(case when status='successful' then 1 end)/COUNT(status)*100 successful_percent,
       count(case when status='failed' then 1 end)/COUNT(status)*100 failed_percent,
       count(case when status='refunded' then 1 end)/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate;

This works because count() (like every other aggregate) ignores NULL values. The expression case when status='successful' then 1 end yields null for any status other than 'successful' and a not-null value or those that are successful, effectively counting only those that have the indicated status.


Historical dbcc checkdb results Is there a better way of accessing historical dbcc checkdb result history (if output logging was not used at runtime) than xp_readErrorLog?

EXEC xp_readErrorLog {0-20}, 1, N'CHECKDB';

SQL Server 2008 or later.

Method 1: Easy way

The Best way that i am using as of now is ola hallengren's  SQL server Maintenance solution for  SQL Server Integrity Check.

Within this solution it gives you an option of storing the output or logging it to a table as per you're convenience:

LogToTable

Log commands to the table dbo.CommandLog.

Please read the complete web link as highlighted in above answer to know how you can make use of it:

Method 2:

To insert the DBCC results into a readable and manageable format, you can use the 'WITH TABLERESULTS' option for DBCC CHECKDB to get an output that can be piped to a table for analysis:

1) Collect the Data:- via creating a table to store the output from created SP as explained in this link.

2) Schedule the job

3) Analyse the data from the table.

Also, would suggest you to read CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?


Aggregate data from two tables with different field names I have two tables that each have date and amount fields.  This is for a donation system so one is pledges and the other is donations.  Here are the structure of relevant tables and fields:

pledges

pledge_date
amount_pledged

donations

processed_date
amount_donated

I would like to end up with results that sum pledges and donations by month.  Resulting in:

Year    Month    Pledged    Donated
2015    01       120        80
2015    02       90         100
2015    05       0          10
2015    06       120        0

Obviously it's easy enough to get the data summed and grouped from the tables individually, but is there a way to get it all in one result set?  Here is the sample SQL to get the data just from the pledges table for example:

SELECT year(pledge_date) as year, month(pledge_date) as month, sum(amount_pledged) as pledged
FROM pledges
GROUP by year(pledge_date), month(pledge_date)
ORDER by pledge_date ASC

I am trying to accomplish this in MySQL.

Thanks to anyone for your help or clues on how to structure this!

SELECT year, month, sum(pledged) AS Pledged, sum(donated) AS DONATED FROM
(
SELECT year(pledge_date) as year, month(pledge_date) as month, amount_pledged  as pledged, 0 as donated FROM pledges
UNION
SELECT year(processed_date) as year, month(processed_date) as month, 0 as pledged, amount_donated as donated FROM **donations**
)x
GROUP BY year, month
ORDER BY year, month


Help with getters & setters in PL/SQL I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question:

I have 2 tables one file level & another batch level. In a day, only 1 file_id is possible; but for each file_id there are multiple batches with different batch_id and then again for each batch_id there are multiple documents.

Now, I need something like the simple getters & setters. I want some data from the file level table.. on the basis of that get some data from the batch level table and then get some more data from the files table again.

This was very easy if I had an object & I could simply pass it around & setting stuff & filling up the object with things I want.

How can I do something similar with PL/SQL like passing the same object again & again ??

Getters and Setters implies retrieving the data for one row and changing the data for one row.  It sounds like you are planning to call a get procedure for each row you want in the file level table and then for each of those rows loop through getting the corresponding data from the batch level table etc.

My suggestion is that you write one SQL statement that selects all the data you need from the file level table joining in the data you need from the batch level table.

I don't know anything about BI Publisher, so based on your comment, none of this may apply. Perhaps someone familiar with BIP can say whether these parameters could be bound into the query to allow the query to determine what data should be returned.  A PL/SQL method may be required for this application, but it needn't be more than a wrapper for your SQL.


How to create rolling backups for SQL Server 2008 R2 I want to run daily backups for a set of databases to disk such that I always have 3 days’ worth of backups kept for each database, with the oldest backup being deleted or overwritten each day. I set up a maintenance plan and set the expiry for the backup set to 3 days. The system uses a timestamp for each file (which I like) but older files are not removed and just build up in the directory, so I'm not sure what expiry means. Is there a way to achieve what I am looking for without having to write re a separate script to clean up the old backups?

Ola Hallengren's excellent scripts provide solutions for database maintenance including backups - the scripts have won some awards in the last year, and are increasingly referenced by SQL Server luminaries. I've been using them for about a year - I love them because they are highly configurable - for backups, you define the retention period in the job in hours. The retention period only kicks in if you actually have refresh backups.

http://ola.hallengren.com/


Query duration in MySQL General log MySQL has feature of logging all sql queries - General log.

But this log does not contain queries durations.

How to log all sql queries to analyze those durations?

I got an answer after writing a question, but i want to share it to community.

Just set up Slow log and set SET GLOBAL long_query_time = 0.

You will see all sql queries with durations in slow log file.


How to change page size in an existing sybase database I have an existing database with 1 kb page size i want to increase that to 16kb and increase cache to take advantage of my memory for best performance. sybase documentations said you can't change page size of database but you can create new empty database with preferred page size, so how can i move my data to from old db to new database with big page size ?

Take a look at the dbunload utility, particularly the -an and -ap switches. You didn't say what version you are using, but here are the docs on v12.0.1.


MySQL error 1241: Operand should contain 1 column, INTERSECT two subqueries I'm running this query in MYSQL and I'm getting the error 1241: Operand should contain 1 column(s).

I want to know the names and student id's of the students enrolled in Database Management Systems and Knowledge Discovery in Databases (both course titles in the CoursesOffer table)

Students(stuID: String, stuName: String, gender: String, birthdate: Date, enterYear: Year, gpa: Float)
Departments(deptName: String, numPhDs: Integer)
ProfessorWorks(profID: String, profName: String, deptName: String)
CoursesOffer(cNo: String, cTitle: String, deptName: String)
Majors(deptName:  String, stuID: String, degreeProgram: String, attendYear: Year, attendSemester: String)
Sections(cNo: String, semester: String, sectNo: String, profID: String)
Enrolls(stuID: String, cNo: String, sectNo: String, semester: String, grade: String)

My query looks like

SELECT S.StuName, S.StuID
FROM Students S NATURAL JOIN CoursesOffer C NATURAL JOIN Enrolls E
WHERE cTitle='Database Management Systems'
IN (
SELECT S2.StuName, S2.StuID
FROM Students S2 NATURAL JOIN CoursesOffer C2 NATURAL JOIN Enrolls E2
WHERE cTitle='Knowledge Discovery in Databases');

You probably want to replace:

WHERE cTitle='Database Management Systems'
IN (
SELECT S2.StuName, S2.StuID
...
  ) ;

with:

WHERE cTitle='Database Management Systems'
  AND (S.StuName, S.StuID) IN (                -- this changed
SELECT S2.StuName, S2.StuID
...
  ) ;


Anybody know story behind pgFoundry (PostgreSQL project site) downtime? pgFoundry site's been unreachable 2 days and counting....

I have googled up some ominous messages from the Spring about it eventually closing, but I had never seen any notice on the site to say that was imminent.

Any inside scoop?

Update: It's back. No explanation I've seen.

Pgfoundry is maintained outside the PostgreSQL infrastructure team's jurisdiction. It's a 3rd party site, and it hasn't seen a great deal of attention lately. Most projects on pgfoundry have moved to GitHub or git.postgresql.org.

If there's a particular project still on PgFoundry that you use, please contact its maintainers and ask them to relocate to git.postgresql.org or github.

If you're unable to reach them after a few weeks consider adopting the project and re-hosting it; just be prepared to hand hosting back over if the original maintainers reappear.

It was originally run by Marc Fournier. I'm not sure if he still runs it. You could ask on the pgsql-www mailing list for more information.

(Note that while I contribute to PostgreSQL, I am not a member of the infrastructure team, core team, or a committer. I do not speak for the project, or for my employer.)


boolean purpose column char(0) or tinyint(1) For Boolean purpose column which one is better: nullable char(0) or tinyint(1).

I know bool is an alias for tinyint(1) , but in "High Performance MySQL" book that published by O'reilly said:

  "If you want to store a true/false value in a single bit of storage
  space, another option is to create a nullable CHAR(0) column. This
  column is capable of storing either the absence of a value (NULL) or a
  zero-length value (the empty string). "

which one is better for size, performance, indexing or ...

UPDATE:
I found this link useful for this question:
Efficient Boolean value storage for Innodb Tables

You should keep in mind it is impossible to index a CHAR(0) column, even as part of a compound index.

In terms of size nothing beats NULLable CHAR(0); but how many of these do you have? As compared to other data types in your table? Typically when compared to overall data types & size this is neglect-able.

The clearest is BOOL/TINYINT UNSIGNED. It is most obvious what values are expected.

Otherwise please consider many alternatives in my post, and please see good ideas on comments: Choosing MySQL boolean data types


Function returning "No data - zero rows fetched, selected, or processed" in MySQL 5.5 I have upgraded MySQL version to 5.5.16

After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed.

In those functions, cursors are there, but the control is not even going to the cursor.

I have also used continue Handler as follows,

Declare continue handler for NOT FOUND
Begin
  set Done = 1;
End ;

I have tried using continue handler in following way,

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'

Even after changing it is returning same warning.

You are using 'NOT FOUND' and SQLSTATE '02000'. There were bugs reports that addressed this:

http://bugs.mysql.com/bug.php?id=42834 (March 5, 2009 : STATUS Closed)
http://bugs.mysql.com/bug.php?id=55843 (August 9, 2010 : STATUS Closed)
http://bugs.mysql.com/bug.php?id=59169 (December 25, 2010 : STATUS Duplicate)

Here is something enlightening about the whay MySQL does error handling.

The book MySQL Stored Procedure Programming has a whole chapter on this subject. On your particular problem, you need what's on pages 132,133 under the subheading Handler Conditions.

It states on page 132 that there are three ways to define an error:

MySQL error code
ANSI-standard SQLSTATE code
Named Condition

You must change the code to

Declare continue handler for 1329
Begin
  set Done = 1;
End ;

I found that error code here : http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

It says as follows:

  Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

  Message: No data - zero rows fetched, selected, or processed

The book I mentioned recommends getting away from ANSI error codes and using the direct MySQL error codes. The reason? Page 133 has a box "SQLSTATE or MySQL Error Code?", which says the following:

  In theory, using the SQLSTATE codes will make your code more portable
  to other database platforms and might therefore seem to be the best
  choice. Hoewver, there are a number of reasons to use MySQL error
  codes rather than the SQLSTATE codes when writing MySQL stored
  programs:

  In reality, it is unlikely that you will move your stored programs to
  another RDBMS. The Oracle and SQL Server stored program languages are
  totally incompatible with MySQL. The DB2 stored program language is
  somewhat compatible (both are based on the SQL:2003 standard). It is
  very likely, however, that you will use MySQL-specific syntax as you
  write your application, which will prevent your stored code from being
  portable.

  Not all MySQL error codes have SQLSTATE equivalents. Although every
  MySQL error code is associated with some SQLSTATE error code, often it
  will be a general-purpose SQLSTATE that is not specfic (such as
  HY000). Therefore, you will almost certainly have to code some
  handlers that refer directly to MySQL error codes. You'll probably
  find that the advantages of using a consistent handler format will
  outweigh the theorectical portability advantage of SQLSTATE error
  codes.


Wrapping query in IF EXISTS makes it very slow I have the below query :

select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)

The above query completes in three seconds.

If the above query returns any value, we want the stored procedure to EXIT,so I rewrote it like below:

If Exists(
select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
)
Begin
Raiserror('Source missing',16,1)
Return
End

However this is taking 10 minutes.

I can rewrite the above query like below, which also completes in less than 3 seconds:

  select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source  <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source
if @@rowcount >0
Begin
Raiserror('Source missing',16,1)
Return
End

The issue with above rewrite is that the above query is part of bigger stored procedure and it returns multiple result sets. In C#,we iterate through each result set and do some processing.

The above returns an empty result set, so if I go with this approach, I have to change my C# and do deployment again.

So my question is,

  why does using just IF EXISTS changes the plan to take so much time?

Below are the details which may help you and let me know if you need any details:

Create table and statistics script to get same plan as mine
Slow Execution plan
Fast Execution plan

Slow plan using Brentozar Paste the plan
Fast Plan using Brentozar Paste the plan

Note: Both queries are the same (using parameters), the only difference is EXISTS (I might have made some mistakes while anonymizing though).

The table creation scripts are below:

http://pastebin.com/CgSHeqXc  -- small table stats
http://pastebin.com/GUu9KfpS  -- big table stats

As has been explained by Paul White in his blog post: Inside the Optimizer: Row Goals In Depth the EXISTS introduces a row goal, which prefers NESTED LOOPS or MERGE JOIN over HASH MATCH

  As a final example, consider that a logical semi-join (such as a
  sub-query introduced with EXISTS) shares the overall theme: it should
  be optimised to find the first matching row quickly.

In your query this apparently happens to introduce nested loops and remove parallelism, resulting in a slower plan.

So you would probably need to find a way to rewrite your query without using the NOT EXISTS from your query.

You might get away with rewriting your query using a LEFT OUTER JOIN and checking there wasn't a row in smalltable by testing for NULL

If EXISTS(
    SELECT databasename
    FROM somedb.dbo.bigtable l
    LEFT JOIN dbo.smalltable c ON c.source = l.source
    WHERE databasename = 'someval'
    AND source <> 'kt'
    AND c.source IS NULL
)

You could probably use a EXCEPT query too, depending on how many fields you need to compare on like this:

If EXISTS(
   SELECT source
   FROM somedb.dbo.bigtable l
   WHERE databasename = 'someval'
   AND source <> 'kt'

   EXCEPT

   SELECT source
   FROM dbo.smalltable
)

Mind you, Aaron Bertrand has a blog post providing reasons why he prefers NOT EXISTS which you should read through to see if other approaches work better, and to be aware of the potential correctness issues in case of NULL values.

Related Q & A: IF EXISTS taking longer than embedded select statement


How to take backup of a database in mysql? For Back up I have used the following commands.

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

mysqldump -u [username] -p[root_password] --single-transaction
 --routines --triggers --lock-tables [database_name] > backup.sql;

Getting same error:

  mysqldump: Couldn't execute 'show table status like 'uc_order_products_pair_vw'': SELECT command denied to user 'username'@'localhost' for column 'nid' in table 'uc_order_products' (1143)

After running select * from mysql.tables_priv;

Can you try grant permission on whole database like,

GRANT ALL PRIVILEGES ON db.* TO 'username'@'%' IDENTIFIED BY 'yourpass' WITH GRANT OPTION;


SQL Server Agent unable to view network drives I am unable to get agent jobs to output to a network path. I have pushed the IT guy to set up a domain authenticated user that logs in when the agent starts. That login does have access to the domain and is able to see the network drives. If I set the location of the output file to be the local c: then this works without issue. However if I set the drive to be a network location I get the following message;
[SQLSTATE 01000] (Message 0)Unable to open Step output file. The step succeeded.

Any help would be very much appreciated

Thank you for all the help. UNC file paths was the solution.

Another thing to check is that you refer to the output location with a UNC path instead of a mapped letter drive. Letter drive mappings aren't reliable from something running as a service.  – Doug Deden 2 days ago

Here is some more information regarding mapped drives and services: https://stackoverflow.com/questions/182750/map-a-network-drive-to-be-used-by-a-service – G.Smith 2 days ago


Orders and Quotes Tables - Database Design Below is the ERD of my current db schema for the orders part of an e-commerce site. The site is based on oscommerce, but has been modified, also I have removed quite a few of the fields from the tables to simplify the diagram.

I am planning to add the functionality to create quotes in the administration of the site.
A quote will be very similar to an order, and most quotes will be converted to an order. There will be some different data needed for each, although most fields will appear in both, such as customer details.
Also the original quote should be maintained so that it can be used again in the future, that is modifying an order that has been created from a quote does not modify the original quote.

Considering the similarities; it felt unnecessary to duplicate the whole of the orders structure for quotes, especially as the child tables for orders are in the format needed to store the data for quotes.

The approach I thought I might go for is to create a quotes table, that would be similar to orders and then reuse most of the orders child tables to work also with a quote.

(Probably I will still duplicate orders_status and orders_status_history to quotes_status and quotes_status_history as there will be different statuses for quotes, and a quote and order should only have a relevant status.)

Tables I was hoping to re-use for a quotes:

orders_total
admin_watch_list
orders_products
orders_products_attributes
orders_products_downloads

In order to re-use these tables, it would seem to me that I would have to do one of the following:

add a quotes_id field to each one of the tables listed above. Although then each of theses tables will have a 'quotes_id' and 'orders_id' one of which should always have a value and the other should always be NULL.
add an orders_id field to the quotes table, start the auto increment of the orders_id in my quotes table to say 1000000000 so that it doesn't interfere with the auto-increment orders_id that started from 0 in my orders table. The orders_id in quotes could then reference all the other tables using it's orders_id, although that orders_id would never appear in the orders table

Both of these seemed solutions did not seem ideal and I was wondering if there was a better way to accomplish what I wanted to do.

Ideally I would like to re-use as much as possible as it could mean faster development and less to maintain in the future.

I already have a page in my administration for editing orders, and I feel the closer I keep to the structure of orders, the easier I think it would be to reuse my code from edit orders page for a create/edit quote page which I need to add.

All help or advice is appreciated.

How about combine them? Keep the idea of an order table as the base, and add a status field with values:

Entered - data being typed in, just started
Quoted - quote provided to customer
Ordered - customer has accepted the quote and the quote converted to an order

You mentioned keeping the original which is doable if you have an ORDERS_HISTORY or ORDERS_VERSION table. Basically, whenever the status of an order changes, update the main table but keep a snapshot of what it used to look like in the history table. This would let you pull up old versions of the quote/order to quote against.


SELECT ROWS WITH SIMILAR ID AND MERGE THE FILED VALUES IN ONE My situation is as follows. I have the query below and the query is doing its job pretty well. However I have excluded one able from it which is field_data_field_luogo and the rows inside this can have column entity_id and with different values in column field_luogo_tid , so what i want to do is , if entity_id is similar for 5 rows then merge the column of field_luogo_tid 's values in one column instead of having multiple rows.

SELECT a.nid,a.type,a.title,a.status,b.body_value,c.field_bici_value,
d.field_classifiche_url,d.field_classifiche_title,e.field_circuiti_tid,
f.field_corto_value,g.field_dcorto_value,h.field_medio_value,
i.field_dmedio_value,j.field_lungo_value,k.field_dlungo_value,
l.field_cronometraggio_tid,m.field_dalzeroit_value,n.field_data_value,
o.field_edizione_value,p.field_email_email,q.field_recapiti_value,
r.field_note_value,s.field_partenza_value,t.field_organizzatore_value,
u.field_star_value,v.field_superstar_value,w.field_tipomanifestazione_tid,
x.field_verifica_percorsi_value,tex.name as c_name ,
tex_t.name as cr_name,tex_n.name as m_name
FROM node a
LEFT JOIN field_data_body b ON b.entity_id = a.nid
LEFT JOIN field_data_field_bici c ON c.entity_id = a.nid
LEFT JOIN field_data_field_classifiche d ON d.entity_id = a.nid
LEFT JOIN field_data_field_circuiti e ON e.entity_id = a.nid
LEFT JOIN field_data_field_corto f ON f.entity_id = a.nid
LEFT JOIN field_data_field_dcorto g ON g.entity_id = a.nid
LEFT JOIN field_data_field_medio h ON h.entity_id = a.nid
LEFT JOIN field_data_field_dmedio i ON i.entity_id = a.nid
LEFT JOIN field_data_field_lungo j ON d.entity_id = a.nid
LEFT JOIN field_data_field_dlungo k ON k.entity_id = a.nid
LEFT JOIN field_data_field_cronometraggio l ON l.entity_id = a.nid
LEFT JOIN field_data_field_dalzeroit m ON m.entity_id = a.nid
LEFT JOIN field_data_field_data n ON n.entity_id = a.nid
LEFT JOIN field_data_field_edizione o ON o.entity_id = a.nid
LEFT JOIN field_data_field_email p ON p.entity_id = a.nid
LEFT JOIN field_data_field_recapiti q ON q.entity_id = a.nid
LEFT JOIN field_data_field_note r ON r.entity_id = a.nid
LEFT JOIN field_data_field_partenza s ON s.entity_id = a.nid
LEFT JOIN field_data_field_organizzatore t ON t.entity_id = a.nid
LEFT JOIN field_data_field_star u ON u.entity_id = a.nid
LEFT JOIN field_data_field_superstar v ON v.entity_id = a.nid
LEFT JOIN field_data_field_tipomanifestazione w ON w.entity_id = a.nid
LEFT JOIN field_data_field_verifica_percorsi x ON x.entity_id = a.nid
LEFT JOIN taxonomy_term_data tex ON tex.tid = e.field_circuiti_tid
LEFT JOIN taxonomy_term_data tex_t ON tex_t.tid = l.field_cronometraggio_tid LEFT JOIN taxonomy_term_data tex_n ON tex_n.tid = w.field_tipomanifestazione_tid
WHERE a.type = 'manifestazione'
AND a.status = 1
GROUP BY a.nid

like if I have multiple rows with entity_id = 5 with field_luogo_id different in every row like :

2345
9877
9863
2374

by merging it will become :

2345,9877,9863,2374


Improve time for Select first 10 Rows of a Table with 10 Million Rows Postresql I am working with a table of 10 million rows and I am selecting the first 10 or the last 10 rows with this query:

SELECT *
FROM category_member
ORDER BY (p::float8/q) DESC
LIMIT 10;

I am ordering by (p::float8/q) cause I am using a user specified order with fractions as index (I have the same table as in this link explained just with 10 million rows)

Limit  (cost=585557.40..585557.43 rows=10 width=24) (actual time=15865.330..15865.332 rows=10 loops=1)
  ->  Sort  (cost=585557.40..610557.40 rows=10000000 width=24) (actual time=15865.327..15865.327 rows=10 loops=1)
        Sort Key: (((p)::double precision / (q)::double precision))
        Sort Method: top-N heapsort  Memory: 25kB"
        ->  Seq Scan on category_member  (cost=0.00..369461.00 rows=10000000 width=24) (actual time=0.070..13528.741 rows=10000000 loops=1)
Planning time: 10.556 ms
Execution time: 15865.540 ms

This query takes to long in my eyes 16,135 s to get the first 10 rows of a table with 10 Million rows.

Anyone had the same issue and knows how to improve performance on my query?

CREATE UNIQUE INDEX ON category_member (category_id, (p::float8/q));

To improve the speed of that query, you need the following index:

create index on category_member ((p::float8/q));


Why integer search string is slower on char field? I'm using MySQL version 5.1 at my workplace.

I have a column my_cloumn char(10) in my_table.

I recently found that a process was taking longer, and the SQL that was responsible for this was below:

SELECT * FROM my_table WHERE my_column = 12345, which can take some 3 minutes running.

Now, editing and using quotes on the search string this way

SELECT * FROM my_table WHERE my_column = '12345'

retrieves the record in a matter of micro seconds.

I do not have access (direct or indirect) to have the code changed to pass the queries with the quotes (by either direct sql or specifying the correct data type in PDO), and currently may not be able to change the column whilst users are using the database. What I need most is how to explain the difference above.

Why is this faster with quotes and not without?

The quotes define the expression as a string, whereas without the single quote it is evaluated as a number. This means that MySQL is forced to perform a Type Conversion to convert the number to a CHAR to do a proper comparison.

As the doc above says,

  For comparisons of a string column with a number, MySQL cannot use an
  index on the column to look up the value quickly. If str_col is an
  indexed string column, the index cannot be used when performing the
  lookup...

However, the inverse of that is not true and while the index can be used, using a string as a value causes a poor execution plan (as illustrated by jkavalik's sqlfiddle) where using where is used instead of the faster using index condition. The main difference between the two is that the former requires a row lookup and the latter can get the data directly from the index.

You should definitely modify the column data type (assuming it truly is only meant to contain numbers) to the appropriate data type ASAP, but make sure that no queries are actually using single quotes, otherwise you'll be back where you started.


Sql Server Stored Procs vs Linq with C# and unit testing I'm going to run into a situation where I am, where the stored proc vs Linq is going to come up. I know I'm asking this in the DBA stackexchange so most of you might say use stored procs, but I'm curious on the unit test suites that exist in SQL Server 2012.

Visual Studio 2015 has a test explorer part of the IDE and MSTest framework which makes writing unit tests really easy, and honestly a lot of "business" logic can be done in SQL/Linq so that's the part that needs to be tested most often.

I'm open to using stored procs but coming from C# my expectation is:

1) Be able to mock some data up just for said tests (in memory not disk for both speed and to make sure it always stays around with the test vs someone deleting it or moving it sometime in the future)

2) Call the proc against mocked data, and then compare the results and get some nice visual feedback that the test failed/succeeded.

3) Seeing all unit tests listed out nicely in some kind of Test Explorer and able to run them all with a click of a button or have them grouped in various ways.

My question is, does this or something similar to this exist on Sql Server to help aid in unit testing stored procedures?

Take a look at tSQLt (http://tsqlt.org/) which is a great free tool to create unit tests in SQL Server.

You can fake tables (by executing tSQLt.FakeTable) within your tests and then create mock data as you describe. Tests are themselves stored procedures and any changes made as part of your testing suite are rolled back post test.

You run your tests by executing the tSQLt.RunAll stored procedure and it gives you visual feedback summary of your test results.

To get started, navigate to http://tsqlt.org/downloads/ and download. Running Example.sql will create an example database called [tSQLt_Example], create the testing framework and some example tests to see what is possible.

Also available is the Redgate paid product SQL Test (http://www.red-gate.com/products/sql-development/sql-test/). This is a GUI wrapper around tSQLt but is not required to use tSQLt.


How to install Barman with pg_receivewal and pg_basebackup without install PostgreSQL server? Follow the official document, it's only necessary to install barman by yum or apt straightforward:

http://docs.pgbarman.org/release/2.10/#installation

Unnecessary to install PostgreSQL server on the barman server.

But in order to use barman, these 2 libraries are necessary: pg_receivewal and pg_basebackup. They are in the path /usr/pgsql-10/bin/ if install PostgreSQL server.

How to make them out without install PostgreSQL server? Is there anyway?

Actually the given URL also says in the System requirements section:

Linux/Unix
Python >= 3.4
Python modules:
    argcomplete
    argh >= 0.21.2
    psycopg2 >= 2.4.2
    python-dateutil
    setuptools
PostgreSQL >= 8.3
rsync >= 3.0.4 (optional for PostgreSQL >= 9.2)

It looks like you need to install PostgreSQL server.


How to execute system command in mysql stored procedure? Here is the code,

delimiter //
create procedure trig ()
begin
    declare cmd varchar(50);
    set cmd = "sh /home/yogaraj/Documents/Maastry/firefox.sh";
    system cmd;
end //

delimiter ;

But I can't execute system command. Can somebody help me?

This cannot be accomplished. Why ?

According to Can MySql stored proc call a system cmd? (from July 19, 2009 11:47AM)

  This question is asked several times a week. The answers are (i) not out of the box, for very good reasons, (ii) you could write and link into your server a UDF which calls the operating system, but that would create a glaring security issue for your app.

For example, I have written about using system before : Why would I use the MySQL "system" command?. In my post, I showed how easy it would be to step into the operating system with

mysql> \! bash
[root@**** ~]# exit
exit
mysql>

Imagine what one could do in the OS because of such a breach.

Usually, stored procedures are used to prevent SQL injection. If system was allowed within a stored procedure, that eliminates such security.

You will have to run the script as follows

mysql> system /home/yogaraj/Documents/Maastry/firefox.sh

or

mysql> \! /home/yogaraj/Documents/Maastry/firefox.sh

This is all you can really do


Just 4 Table Join query takes too much time and some time fail to execute I am working on getting candidate detail from mysql database and for that i have created one query.

Is my query structure is wrong?

Here is the query:

SELECT  `candidate`.`id`, `candidate`.`email`, `candidate`.`fname`,
        `candidate`.`lname`, `candidate`.`id` as `can_id`, `candidate`.`update_date` AS `updated_at`,
        DATE_FORMAT(candidate.dob, '%d-%m-%Y') AS dob, `candidate`.`gender`,
        GROUP_CONCAT(DISTINCT candidate_experience.functional_area_name SEPARATOR ',') AS can_functional_area,
        GROUP_CONCAT(DISTINCT candidate_experience.industry_type_name SEPARATOR ',') AS can_industry_type_name,
        GROUP_CONCAT(DISTINCT qualification_name SEPARATOR ',') AS qualification_name,
        GROUP_CONCAT(DISTINCT candidate_key_skills.name SEPARATOR ',') AS skill,
        `candidate_desired_job`.`current_location`,
        `candidate_desired_job`.`total_experiance`, `candidate_desired_job`.`prefered_location`,
`candidate_desired_job`.`notice_period`,
IFNULL(candidate_desired_job.expected_salary, 'N') AS expected_salary
    FROM  `candidate`
    LEFT JOIN  `candidate_key_skills`  ON `candidate_key_skills`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_qualification`  ON `candidate_qualification`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_desired_job`  ON `candidate_desired_job`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_experience`  ON `candidate_experience`.`candidate_id` = `candidate`.`id`
    WHERE  ( `name` LIKE '%PHP%' ESCAPE '!' )
      AND  ( `prefered_location` LIKE '%Ahmedabad%' ESCAPE '!' )
      AND  `total_experiance` >= '-1'
      AND  `total_experiance` <= '40'
      AND  `expected_salary` >= '0'
      AND  `expected_salary` <= '500000'
      AND  ( `candidate_experience`.`industry_type_name` LIKE '%Accommodation and  Food Services%' ESCAPE '!'
           )
      OR  `carrier_summary` LIKE '%PHP%' ESCAPE '!'
      OR  `carrier_summary` LIKE '%Ahmedabad%' ESCAPE '!'
      OR  `carrier_summary` LIKE '%Accommodation
      and  Food Services%' ESCAPE '!'
      AND  `candidate`.`id` >0
    GROUP BY  `candidate_key_skills`.`candidate_id`, `candidate_desired_job`.`id`
    ORDER BY  `candidate_desired_job`.`id` DESC

Red flag!  x AND y OR z is the same as (x AND y) OR z.  I suspect you need parentheses around the ORs.

And something seems to be missing (even before my edit) on the last OR.

(I suggest you use short "aliases" to help de-clutter the query.)

Please qualify all columns.  Meanwhile, I will assume all the columns mentioned in the WHERE come from candidate, else some of the following will not be correct....

Leading wildcards with LIKE prevents usage of indexes, so I will ignore name, etc.  Can you use FULLTEXT instead of LIKE?

ids are usually positive values, so why do you have AND  candidate.id >0??

So, we are down to two choices for a useful INDEX: (total_experiance) or (expected_salary).  But I suspect neither of those will be very useful.

As for the 4 LEFT JOINs -- Are they "many:1"

Ouch.  2 different tables in the GROUP BY.  That forces the generation of a temp table leading into the GROUP BY.  That temp table will have lots of rows, and be slow.

Since the ORDER BY is different than the GROUP BY, there will be another temp table and sort.  This can be avoided by saying

ORDER BY `candidate_key_skills`.`candidate_id` DESC,
         `candidate_desired_job`.`id` DESC

to match the GROUP BY.  This will speed up the query a little.

Ordinarily I would suggest a subquery instead of a LEFT JOIN, thereby avoiding some of the "inflation" caused by the join.  But I see that each case involves two select items.

Sorry, there is not much that will speed up the query.


selecting different categories of values from one column using limit i have table question_bank in my database where i store questions and there is one column called complexity. which defines complexity of question (High,Med,Low). for example i entered 30 question in my table ,10 have high complexity ,10 have medium and 10 have low. is there any way so i can show user 15 questions (5 from each category)
what i want to achieve

select * from question_bank where complexity=high and complexity=medium and complexity=low limit 5

this query is just for your idea

thanks in advance

You could do union on three queries, each selecting only 5 records of choosen complexity :

(select * from question_bank where complexity='high' limit 5)
union
(select * from question_bank where complexity='medium'limit 5)

....

This way, you "sum" the records of all your queries (union works only if every request fetch the same columns if I'm not wrong).

You have to be careful to put select statement between parenthesis when using limit and union together (thanks to this answer : https://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query ), if you fail to do so, your limit won't apply to all select statements.


Oracle Autodop disabled at the table level I have a question related to Oracle auto DOP and parallel DML concepts. I have a scenario, if  I set the Auto DOP set to 1 on the table and if I run parallel DML statements on that table through parallel hint, will the query processing happens in parallel execution or since the Auto dop is set to 1 will it be a serial execution?

Can you please provide some insight on this topic.

Thanks for the help!

You can't have "Auto DOP set to 1". You can either have:
- have auto DOP, meaning Oracle will attempt to automatically determine optimal DOP
- set DOP to 1, for example with ALTER TABLE PARALLEL 1

The second option disables parallel query, so yes, query will execute serially. Unless you override it with a hint or ALTER SESSION FORCE PARALLEL QUERY.


Postgres 9.3 copy data from another table with relation Hi I have two tables in my database which is related, which is forms and qa_forms.

forms
->id
->name
-address
-phone
-calldatetime

and then `

qa_forms
->id
->forms_id
->name
->address
->phone

So the qa_forms is just the replicate of forms I already have data on both however I forgot to add calldatetime in my qa_forms. So what I did is i add new column on qa_forms which is calldatetime. As expected all the values will be null since I already have data before and this one is newly added, how can I get their respective calldatetime on forms on the fastest way I can do it? They have relation which is forms.id = qa_forms.forms_id.

Any query on that?

I think something like this would work -- let me know if it doesn't

WITH forms_data AS (
  SELECT id, calldatetime FROM forms
) UPDATE qa_forms qf
  SET calldatetime = fd.calldatetime
  FROM forms_data fd
  WHERE qf.id = fd.id;

Edit (better version, suggested by dezso):

UPDATE qa_forms qf
SET calldatetime = f.calldatetime
FROM forms f
WHERE qf.id = f.id;


Which packages are required to install Oracle 11.2.0.3 on Solaris 11? Where can I find information on which packages are required to install Oracle 11.2.0.3 on Solaris 11? The documentation only mentions Solaris 10. I tracked down the packages required for Solaris 10, and most of them are either included in the GA release of Solaris 11 or obsoleted.

There are however a couple of packages that are missing from the base install of Solaris 11:

SUNWarc (renamed to):

consolidation/osnet/osnet-incorporation (installed)
developer/library/lint (missing)

SUNWhea (renamed to):

consolidation/osnet/osnet-incorporation (installed)
system/header (misising)

So it looks like developer/library/lint and system/header are packages from required for Solaris 10 that are missing from the Solaris 11 base install.

The installer however doesn't seem to be looking for any packages whatsoever. Although it does check the kernel version (5.11) Does that mean all the packages needed are bundled in Solaris 11 and  it's safe to install the database?

The Solaris 11 (11.2) install uses groups of packages called:
solaris-desktop
solaris-large-server
solaris-small-server
solaris-minimal-server
The group is defined in a install manifest during AI install
Oracle also provides a x86 (64bit) desktop install iso
A SPARC or x86 Text server install
See the install documentation for  the list of files if you really want it.
Solaris no longer uses SVR4 packages to install OS packages, it now uses IPS packages.

http://docs.oracle.com/cd/E36784_01/index.html


Is there a reason/benefit I am not aware of for this kind setup? I now manage a MySQL database that was designed and created by another.

I am confused about a certain setup that the original developer created..

He did not use auto increments for table IDs. Instead, he created a table called
system_sequences which has two columns:

system_sequences.Table_Name
system_sequences.Next_Value

Here is an example of a table and how it currently inserts into the table (try my best):

there is a table called customer with an unique ID column.

If I want to insert into customer, currently I have to do the following:

SELECT system_sequences.Next_Value
FROM system_sequences
WHERE system_sequences.Table_Name = 'customer'

save the next_value into a variable and use it as the next ID for customer

then I have to:

UPDATE system_sequences
SET Next_Value = Next_Value + 1
WHERE system_sequences.Table_Name = 'customer'

I did not design this, I do not like it in any way shape or form  ( I actually hate it ) and I want to convert everything carefully to auto_increment

But is there a reason for this or a benefit that I am not aware of? I have never seen anything like it before. And there is no documentation for this database that explains anything.

I don't know about mysql but in SQL server, you would use this type of thing when you can't afford to have gaps in the sequence from rollbacks. Often this is a requirement for invoice numbering for accounting auditing.


SQL Server trusted connection additional security Using Windows authentication, is there a way to prohibit users from connecting via ODBC to the database?

The feature designed to help in this scenario is Application Roles.  With an Application Role you can allow the user to connect to the target database, but not be able to run any queries until the application supplies a secret to activate the Application Role.


Append-only and snapshots with PostgreSQL? I'm working on a small content management system database and I wanted to use an append-only architecture, having snapshots of content (revisions) that can be used at any time, kinda like Git. But unlike git, i don't think it's feasible to store deltas, but just an entire snapshot.

Does anyone know of a way to do this in PostgreSQL in a way that supports relationships? How can i store a snapshot of relations between tables?

The SQL:2011 standard has a feature known as temporal tables or system-versioned tables which might be helpful for this type of use-case.

Basically, whenever you update or delete a record in the database, the database automatically holds on to the previous version of the record, including the time period when it was valid.

PostgreSQL doesn't support this feature natively, but there's an extension called temporal_tables. A tutorial can be found here. A few other DBMSes support temporal tables natively, including SQL Server 2016 and MariaDB 10.3.


Formatting a float value I am fetching data from MySQL using PHP. I know that most values stored in the database are float. Sometimes, the database outputs values like 3.9999E-5 and sometimes like 0.00104961904762 (14 digits).

How can I truncate the value to 8 digits only. I am currently using:

<? echo sprintf("%.8f",$get_results['Price']);?>


SQL 2008 R2 Datafile Too Large To Empty or Remove Our primary database has three datafiles: 1TB, 400G and 500G.  The 1TB file is just too dang big and isn't really even needed any more, so I want to empty it and get rid of it so we can use that drive space to partition the data.  However, any attempts to empty the file will get to 16% or so and then drop to an absolute crawl: the last attempt, after 24 hours, showed the estimated time to complete (from sys.dm_db_exec_requests) was 4.5 days, and I will guarantee that would continue to increase out of proportion to the percent of work completed.

The file is currently 96% free so a lot of the data was moved out, but now we're stuck.  We can't run a shrink 'cause it's apparently too big, and it won't let us remove it 'cause it was the initial datafile for the database and holds the sys data.

Is there a faster way (faster than, say, a week or two of downtime) to shrink this thing and/or get rid of it entirely and migrate everything to another datafile in the PRIMARY filegroup?

Edit:  Found a solution!  I can't shrink the whole thing at once; I just have to do it in dribs and drabs.  Shrinking by 50G at a time is working like a champ!

Shot in the dark without any information on how the database is structured i.e. what tables are on what filegroups, is there an existing partition scheme?.

How about rebuild the tables on the big filegroup to one of the other existing filegroups, or a new smaller one?

CREATE UNIQUE CLUSTERED INDEX PK_MyTable ON dbo.MyTable
(
<column list>
)
WITH (DROP_EXISTING=ON, ONLINE=ON)
ON <new filegroup>


MS Access displays alle columns in SQL Server tables as "deleted" I have created an ODBC connection to a SQL Server 2005.

When I link to the tables from MS Access, some tables have only "deleted" data.

This means the rows and columns are displayed, but all data seems "deleted" ("Geloscht" in the image)

I find it remarkable that some of the tables of the same database work fine.

It looks like the linked SQL Server table does not have a unique index on in. MS Access uses the MS Jet DB engine that is designed around a keyset model. Actions like inserts, updates, etc, uses this keyset. This may be missing from your linked SQL Server table. Click here for more detailed info.


Is it *always* bad to have long-running transactions, or is this case OK? I do this:
BEGIN;
TRUNCATE TABLE test RESTART IDENTITY;
-- Tons of INSERTS to the test table here, taking minutes or many seconds each time.
COMMIT;

This is nothing fundamentally wrong with this, is it? Even though the transaction takes a long time before it finally commits, in this case it's simply necessary for the whole operation be "atomic" and not make other scripts see an empty or half-populated test table, right?
The other queries will still continue to work while it's taking its sweet time to finish, right? This is where my understanding of transactions falls apart. It seems to me that it's guaranteed that I will be able to SELECT from the table while this transaction block is running, but is it also possible to INSERT and DELETE and UPDATE?
My brain cannot comprehend how exactly the database would handle this. Is this what a deadlock is? Or will they continue INSERTing and UPDATEing and DELETING from a "ghost table" and all changes are lost when the long-running transaction finally COMMITs? That doesn't sound right. I assume that it would refuse any "changing" operation to the table while that transaction is running, correct?
Yes, I actually did the manual again today on transactions and deadlocks. Some people are able to read something once and understand it perfectly, while others (like me) can spend many years and repeatedly convince themselves that they have understood something, only to see "nonsensical" errors logged and Bad Things happening to their live production applications due to misunderstandings of how complicated things like this really work.

This is nothing fundamentally wrong with this, is it? Even though the transaction takes a long time before it finally commits,

The risk is locks.  Only one transaction can update a given row at a time.  There can be various reasons for this.  A few easy to understand cases are:

Update statements which depend on the current state, such as update blah set count = count + 1.  What would happen if two processes each read a row with count = 4, they each add 1, which produces 5, and they both write 5?  One of them had their results corrupted.
Indexes - select first_name, last_name from users where last_name = 'Jones'; - imagine there is an index on last_name.  Great!  We can optimize the query by scanning the index, then for each row, go out to the table and retrieve the two columns.  Imagine a transaction updated the table, but had not yet updated the index, then we come along and suddenly select first_name, last_name from users where last_name = 'Jones';  returns Jane Doe.  Indexes are expected to be consistent with the table.
Cascading deletes - if table B defines a foreign key constraint referring to table A with on delete cascade, what happens if you query table B before the cascade delete has happened?

The solution to all of these problems, and more, is locking.  Postgres will lock the row for any updates to it so that any other updates to that row have to wait for the lock to be released.  When running in a transaction, locks continue to be held until the transaction is committed or rolled back.

It seems to me that it's guaranteed that I will be able to SELECT from the table while this transaction block is running, but is it also possible to INSERT and DELETE and UPDATE?

In PostgreSQL, I believe that is correct.

My brain cannot comprehend how exactly the database would handle this. Is this what a deadlock is?

A deadlock is when multiple transactions, running concurrently, each require the same locks, but request them in a different order.
Process A wants to lock rows X and Y.  Process B wants to lock rows Y and X.  They each go ahead.  Process A locks row X and process B locks row Y.  Now what do they do?  They enter a state where they wait for each other.  Because they each are waiting on each other, neither makes progress, so they wait forever.  That is what creates the "dead" in deadlock.  The processes will appear to be dead because they are sleeping forever.

Or will they continue INSERTing and UPDATEing and DELETING from a "ghost table" and all changes are lost when the long-running transaction finally COMMITs? That doesn't sound right. I assume that it would refuse any "changing" operation to the table while that transaction is running, correct?

Selects should work fine because what happens with insert, update, and delete, is Postgres creates new versions of the rows and keeps track of which transactions see which versions.  If you're not in a transaction and simply select a row, you should see the last committed version of the row at the time you query it.  Inserts, updates, and deletes, however, will block, waiting in line for their turn.
Nothing should error.  To a reasonable degree, blocking is normal.  Blocking is the database protecting your data.  However, blocking is a scalability problem because it means only 1 transaction at a time can change something.  Like all performance concerns, it's a question of your workload and what tradeoffs you can tolerate.  Blocking is a common problem encountered when troubleshooting database performance.
So what's the problem with long-running transactions?
Any rows updated in a transaction may cause other transactions to wait.  Those transactions may also be holding locks, and now they are stalled, which may cause them to block others.  This pattern leads to blocking chains and in an OLTP workload, can degrade performance substantially.
The premise is simple: Hold as few locks as you need for as short of a time as possible.
What to do in your case?
It depends.  Is the truncate statement being run in production or only in test environments?  Are there foreign keys involved?
It's odd that the inserts would take so long for you.  Can you batch them into a single statement or keep the rows in a second table and after truncate, do a insert into test select ... from second_table?
It really depends on what problem it's causing you, if any.  If the table isn't used by most of your queries, then maybe your transaction is just fine.  If you're getting blocking issues (issue defined as something impacting users) in production, then you should try to optimize the inserts.


Why is MySQL ignoring my indexes? I have a query

select distinct id from patient where company_id in (1) and
(name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%')

running on a table that looks like this

CREATE TABLE `patient` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `status` enum('tmp','active') DEFAULT 'tmp',
  `remote_id` varchar(64) DEFAULT NULL,
  `name_last` varchar(64) DEFAULT NULL,
  `name_first` varchar(64) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `mtime` datetime NOT NULL,
  `ctime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_ID_SCHEME_MRN` (`company_id`,`remote_id`),
  KEY `ix_ID_SCHEME_LAST` (`name_last`),
  KEY `ix_ID_SCHEME_FIRST` (`name_first`),
  KEY `ix_ID_COMPANY_ID` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I run an explain on it, I see that MySQL isn't using any of my indexes.

           id: 1
  select_type: SIMPLE
        table: patient
         type: ALL
possible_keys: ix_ID_SCHEME_MRN,ix_ID_SCHEME_LAST,ix_ID_SCHEME_FIRST,ix_ID_COMPANY_ID
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 494
        Extra: Using where

I am surprised that it isn't using ix_ID_COMPANY_ID at least. But, honestly, I would think that it would make use of most of the indexes.

Any advice?

Thanks.

If a significant portion of the rows match company_id in (1), then MySQL will choose to do a table-scan instead of using the index. In my experience, "significant portion" is about 20%.

Think of it this way: in the index at the back of a book, why don't they index words like "the"? Because the index entry would just show a list of every single page number. Looking up each page the word occurs on would be more trouble (flipping back and forth in the book) than it would be to simply read the book cover-to-cover.

That's an extreme example, but there's a break-point where it's more efficient to do a table-scan. For better or worse, MySQL tends to choose this option if the value you're searching for occurs somewhere between 16-20%  of the rows.

The other indexes cannot be used because you're using OR.


How to test MongoDb sharded cluster I set up a MongoDb sharded cluster (2 shards) as described in this tutorial.

Now, I want to test that the shard 1 (RS1) and shard 2 (RS2) works well. However, all the databases, collections and documents that I create goes in RS1 but seems to never go in RS2.

Am I missing something? How to make sure that RS2 works?

I use MongoDB shell version: 2.6.9.

The tutorial refers on how to setup a sharded cluster but not on how to shard collections.

The next step is to enable sharding for a database and shard one or more collections in order to distribute the data.

From the mongos first use sh.enableSharding("dbname") https://docs.mongodb.org/v2.6/reference/method/sh.enableSharding/

and then sh.shardCollection("dbname.collectionname",{shardkey:1}) https://docs.mongodb.org/v2.6/reference/method/sh.shardCollection/

You need to choose what the shard key will be. A tutorial on how to choose a shard key can be found here  https://docs.mongodb.org/v2.6/tutorial/choose-a-shard-key/


Problem with procedure performance I'm having problem in Oracle, where I have two procedures that are executed one after the other.

Where the first procedure, I have a cursor that does insert into a table.
This insert has about 30 million records, which are inserted at a time fast and acceptable.

But when the following procedure is executed, which makes the update is rather delay. In a last execution took about 38 hours. And it has a single slider update, less than the first and run the cursor separately, it runs in less than one minute.

One detail is that when the procedures are being performed, and see that the second procedure is "locked" if I stop running, and execute analyze table, and only perform the second procedure again, it is executed typically taking about 1 or 2 hours to complete.

can someone help me with this problem?

It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently):

1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately updating them. Perhaps you need to look into insert using select statements to dynamically derive the full value, and then insert the rows in their final state.

2) By inserting 30 million rows, I suspect that you're altering your tables by a value > 10%. Oracle's automated jobs will reanalyze stats on any table with a change of 10% or greater; I make it a practice to do the same thing when I'm inserting/updating/deleting data. This is why you're seeing this behavior when you stop the 2nd procedure and analyze the table.

3) For the update statement, have you properly indexed the columns?

4) What is your expectation for the second procedure's run time? Does 1-2 hours to update 30 million rows sound too long? Only you can know that. For example, in the environment I work in, we can run our databases at 17,000 IOPS, but we force logging on everything because we run a data guard to a DR site, which adds some overhead on operations. So for me, 1-2 hours for a 30 million row update is right about where I normally am. Of course, this also depends on factors like row size (are you updating a single character, or a blob?), the where clause of the update (are you updating based on primary key, a foreign key, or based on an unindexed field?), and the overall load on your system at run time.

HTH.


Make a backup file or save a DB in PostgreSQL I have deployed for my data base in production a new groups of users;

An admin user with full access to the DB ;
A read only user ;
A super admin user to manage all the DBs ;

The matter is for the backup file or the data base save file, can i just use the read only user to save the DB or i shall use the super admin user to insure all data will be saved.

First think about what you want to achieve with a backup. To my humble opinion, a dump is not a backup good enough for DR. It can help restoring to a point in time but with a dump you can not recover the transactions that were created since the 'backup' was taken.
So if you just want an SQL dump that allows you to restore to a time in the past and ignores all transactions made between the dump time and the restore time, you can do that with a user that has read only access to those tables.
If you want a real backup, one that also enables you to recover the transactions made since the backup, use pg_backup to make a physical backup of the database and regularly make wall backups (there is a parameter for that "archive_command") and of course, your cluster needs archive_mode = ON, and wal_level to archive or replica (depends on version, replica for >= 9.6) This backup needs to be done by the database owner, often the postgres OS user.


Exporting SSRS report to Excel We have a number of SSRS reports and want to show the report in Excel format to the users. SSRS has provided an option to save the report in Excel format (.xls), however when the report is saved as Excel, all the cell borders are gone and the spread sheet background color is white. For example:

Is there any way to prevent it from happening? So the report is shown in Excel with all grids shown as per normal?

There is some Technet information on the intended behaviour of the export: https://technet.microsoft.com/en-us/library/dd255234(v=sql.105).aspx

  Images, charts, sparklines, data bars, maps, gauges, indicators, and
  lines are positioned within one Excel cell but they sit on top of the
  cell grid. Lines are rendered as cell borders.


Is there an easy way to update connection strings to a database moved to a different instance I have created a new instance of SQL Server 2008 r2 and exported my database to the new instance.  Then I realized that the connection string for all of my datasets still point to the original database.  Is there an easy way to update the connection string for each of the datasets?

This also brings a question on deployment.  Each customer that purchases the software would have a different connection string.  Can you point me to some documentation that would explain how to handle this scenario?

Is there an easy way to update the connection string for each of the datasets?

Connection strings are what client software use to figure out how to connect to the data store. So I would say outside of a programmatic update (search and replace) no there is no straightforward or native way to do this.

  Each customer that purchases the software would have a different connection string. Can you point me to some documentation that would explain how to handle this scenario?

This is really more of a question of software install/deployment/configuration, but typically setting the connection string would be done through the installation process.


HStore vs. multiple tables with inheritance - do I have a good use case for HStore? My broader question here has to do with when it is appropriate to use HStore vs. multiple tables vs. one table for storing document-like objects.

Now, for my localized example: I’m designing a database structure for holding experimental results. My first idea was to have separate tables for each experiment. This would look like:

However, this would require a lot of JOINs across tables since it would be fairly common for end-users to want to look for data across multiple experiments. (Side note: maybe a good use case for inheritance?). I thought I could avoid this by having a monolithic experiments table like this:

That would certainly eliminate the need to have JOINs but I feel like it would be logical to have individual experiments be separate in some way. Also, it will be very common that one experiment is requested in its entirety. This is why I’m considering using HStore kind of like this:

This appeals to me because each individual result set is very document-like, but I’m afraid that I’ll be running into the same issues that the multiple tables approach would bring.

Other considerations and thoughts that may be relevant:

I’ve thought about having additional tables for storing meta-data/annotations relating to individual data points in individual experiments. Some of this would fit very nicely into the HStore experiments table.
Different users will be inputting experimental data, I would use schema for this
There will be different experiment types – I think the best way to handle this would be storing these in entirely different databases

I should also mention that I have very limited experience with PostgreSQL, but I’m already delighted at what it has to offer over MySQL!

That would certainly eliminate the need to have JOINs but I feel like it would be logical to have individual experiments be separate in some way. Also, it will be very common that one experiment is requested in its entirety

Both of those are good arguments for using table partitioning. PostgreSQL implements this with inheritance and constraint exclusion.

  I’ve thought about having additional tables for storing meta-data/annotations relating to individual data points in individual experiments. Some of this would fit very nicely into the HStore experiments table

Nothing stops you having an hstore field for annotations, or a separate join table of hstore fields for them.

  Different users will be inputting experimental data, I would use schema for this

Why? Surely the same argument applies, that you'll want to do cross-user aggregation too?

I'd just partition by experiment and have the user ID as part of the table's key.

  There will be different experiment types – I think the best way to handle this would be storing these in entirely different databases

Why?

Do you ever think you might need to query across them or aggregate them? If so, do not store them in different DBs.

If they store similar kinds of data, just have them in the main table, and have some optional columns.

If they store mostly different kinds of data (mostly or entirely different columns) then use different tables in the same DB.


MySQL Load Data Infile extremely slow I have searched in Google, in StackOverflow, I have tested many methods, but I really don't understand why the query is extremely slow.

I have a CSV file with this structure with 94K rows:

name;email;employer;city;state;born;active;group;

with two load data queries. One for group (with dummy columns and with name in the last) and other query to import the columns ignoring the last.

--- FIRST (It is running for 12 hours and did not finish yet.)
LOAD DATA INFILE 'C:/path/file.csv'
    REPLACE INTO TABLE group
    FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' IGNORE 1 LINES
    (@dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, name)
    SET id = NULL, active = 1;

--- SECOND (not begin because the first not ended)
LOAD DATA INFILE 'C:/path/file.csv'
    REPLACE INTO TABLE subscriber
    FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' IGNORE 1 LINES
    (name, email, employer, city, state, born, active, @dummy)
    SET id = NULL;

Ok, I don't have the best computer, but the file not is too large.
I have a Windows 64bits with 4GB RAM, using XAMPP;

In my.ini I have this settings:

innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M

Please, can someone help me? Sorry my english and thank you!

Edit

CREATE TABLE group (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL,
    created TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    active TINYINT(1) UNSIGNED DEFAULT "1",
    PRIMARY KEY (id),
    UNIQUE KEY name_UNIQUE (name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

REPLACE is DELETE any or all rows that match, then INSERT the one row.

For REPLACE to be useful, you need an index, preferrably UNIQUE or PRIMARY so that it knows what to DELETE.

Do you have an index on group; that is what the first REPLACE needs.  Or am I confused about the columns?

Also, ... Consider whether it would be better to use INSERT IGNORE instead of REPLACE.  The semantics is similar, but different.

If those suggestions do not work, then I recommend, LOADing the CSV files into temp tables, then perform queries to copy the data into the 'real' tables.


Table Structure: 2 Related Entities Share the Same Entity I was hoping I could get some advice on my table structure. Im writing a simple workout tracker for me and my buddies. These are the requirements:

Multiple users
A user can have multiple workouts
A workout has multiple sets
A set can have multiple users

I have a way to accomplish what I need to but I feel like theres a much simpler way that Im missing.

tbl_user

| UserId |   Name |
-------------------
|      1 |  John  |
|      2 |  Greg  |

tbl_workout

| WorkoutId |
-------------
|     1     |
|     2     |

tbl_user_workout

| WorkoutId | UserId |
----------------------
|     1     |    1   |
|     2     |    1   |

tbl_set

| SetId | WorkoutId |
---------------------
|   1   |      1    |
|   2   |      1    |

tbl_user_set

| UserSetId | SetId | UserId | Reps | Weight |
----------------------------------------------
|     1     |    1  |    1   |  20  |   50   |
|     2     |    1  |    2   |  15  |   60   |

I figure this way I could get:

A users sets with user->userset
A users workouts with user->userworkout
A workouts sets with workout->set

Is this structure logical?

Try it like this:
general table:
general_id/user_id/set_id/workout_id/Reps/Weight
In your select query you can then do something like this:
SELECT concat(u.name, ' , u.last_name) as user,
       s.set,
       w.workout,
       g.reps,
       g.weight
FROM general_tbl g JOIN user u ON u.id_user = g.id_user
                   JOIN set s ON s.id_set = g.id_set
                   JOIN workout w ON w.id_workout = g.id_workout