21
Since you're just looking for changes, you don't need a cryptographic hash function.
You could choose from one of the faster non-cryptographic hashes in the open-source Data.HashFunction library by Brandon Dahler, licensed under the permissive and OSI approved MIT license. SpookyHash is a popular choice.
Example implementation
Source Code
using Microsoft....
17
I'm not sure if parallelism will be any / significantly better with SQLCLR. However, it is really easy to test since there is a hash function in the Free version of the SQL# SQLCLR library (which I wrote) called Util_HashBinary. Supported algorithms are: MD5, SHA1, SHA256, SHA384, and SHA512.
It takes a VARBINARY(MAX) value as input, so you can either ...
12
This isn't a traditional answer, but I thought it would be helpful to post benchmarks of some of the techniques mentioned so far. I'm testing on a 96 core server with SQL Server 2017 CU9.
Many scalability problems are caused by concurrent threads contending over some global state. For example, consider classic PFS page contention. This can happen if too ...
9
You are definately on the right track with Kimball rather than inmon for Redshift.
There are a number of patterns for this, I have used them all in different use cases
"ELT" pattern - Load the source tables to redshift fully, do not do any significant
transformations until the data has been loaded. For this you can
either load to s3, then use redshift copy ...
9
If you really must do it from T-SQL, and your SQL Server instance version is 2012 or more recent, You could use a FileTable. It relies on the FileStream feature, so you'll have to enable this first.
To keep it simple, a FileTable works mostly like a regular table, but the file content is stored in a FILESTREAM file group, and the files can be accessed ...
7
There are many reasons that an insert and delete can be faster in practice than an single update that achieves the same end result. I am not even going to attempt to list all the considerations, but for example:
Updates that affect an index key might appear to do an in-place update from the execution plan, but this is not the case at the lowest level. An ...
7
I'll weigh in from the user perspective.
I have worked extensively (15 or so projects) with one of the automation tools on a SQL Server backend, and results were mixed.
Did the DWA tool greatly reduce the time to get a data warehouse up and running, or did the time it took to ramp up on learning the tool eat the time that otherwise would have been gained?
...
7
You can probably improve the performance, and perhaps the scalability of all the .NET approaches by pooling and caching any objects created in the function call. EG for Paul White's code above:
static readonly ConcurrentDictionary<int,ISpookyHashV2> hashers = new ConcurrentDictonary<ISpookyHashV2>()
public static byte[] SpookyHash([SqlFacet (...
answered Feb 8 '19 at 22:41
David Browne - Microsoft
36.7k3 gold badges38 silver badges79 bronze badges
6
There are a couple of options if you want to use T-SQL and SSIS.
You could compare the key columns on the staging table vs ops table to determine if the row already exists and this way you would know if you need to do an INSERT or UPDATE.
If using SSIS you can use the lookup component. Your source componenet will have something like
SELECT keycol FROM ...
6
Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):
function_body
Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.
Could you double check that function is created in DW?
6
Azure SQL Data Warehouse has limited support for UDFs. It does not yet support the syntax SELECT @var =. Instead you must use DECLARE @var int = or SET @var =. SQL DW UDFs also do not yet support queries on user tables. Please use our feedback page to vote for new features.
6
This doesn't answer your question with regards to why the query is slow on the 11th day, but hopefully it helps clarify why it fails after 10 minutes.
You mention:
The remote timeout on the linked server is set to 0.
Intuitively, this might seem like there is no limit.
What it actually does is use the sp_configure default for remote query timeout, ...
5
SSIS gets its power by being an in-memory transformation engine. The base unit of work within a data flow task is the buffer. If you ever wonder why SSIS is so persnickety about data types, it's because it calculates the cost for a row and then allocates memory for N rows. All* the downstream components use the same memory address to do their part of the ETL,...
5
The main strengths of SSIS as I see it are 1) the ability to do things across servers which aren't otherwise linked (eg via linked servers) and 2) the ability to do things in parallel (eg multiple Execute SQL tasks operating concurrently).
If you are using traditional SQL then you probably don't need 1), but you might need 2). Your ETL must be running in ...
5
Assuming that your assumption is correct regarding the VARCHAR(50) field using a collation of SQL_Latin1_General_CP1_CI_AS, then you should consider altering those alphanumeric "code" fields in each of the tables where it exists, to have a collation of Latin1_General_BIN2. Since the value is derived from an algorithm, the casing of any alpha characters ...
5
Eckerson's list is poorly researched. There is a more comprehensive catalogue of data warehouse automation tools on our web site, at http://ajilius.com/competitors.
I'll answer your questions from a vendor perspective.
Our customers report significant savings in project time. Most DWA products are written by data warehouse people who first want to save ...
5
Given the following info:
Our support engineers need to grab database backups from clients.
and:
The most "valuable" data is "the configuration" of our system. It is contained in about 50 tables...
and:
I'm concerned about temporarily changing the recovery model for this purpose [of allowing the "Data" filegroup to become ReadOnly so that it can ...
5
Your question is a bit confusing so I hope I'm deciphering it correctly.
Technically you can connect to a SAP HANA database using ODBC -- this is helpfully documented in the manual.
What you do after you connect is another question. The SAP ERP application schema is very complex and poorly (if at all) documented, and it is highly unlikely that you will be ...
5
there are various words that are "written" to the database incorrectly.
No, the characters are being read incorrectly. They are being written correctly. Or, another way to look at this is: the characters are being written incorrectly to the data file. Either way, SQL Server is doing exactly what it is being asked to do.
This is a simple encoding issue. The ...
5
Having an identity column doesn’t force you to use it as a clustered index key.
You’re right that heaps work well here. I would consider Thomas Kejser to be the authority on the subject, and it’s good you’ve listed him as one of your resources.
As for fragmentation in heaps - doesn’t happen on insert-only.
Edit: Go through this article about parallel ...
4
Ensure you're using mongoimport version 3.4 or higher, and use the --columnsHaveTypes parameter to tell the import what type each column is.
See the MongoDB Reference Documentation for details.
The following types can be specified:
auto()
binary(<arg>)
boolean()
date(<arg>)
date_go(<arg>)
date_ms(<arg>)
date_oracle(<arg>)
...
4
The process you describe is very extremely techinically ETL, though if I were reviewing qualifications with a candidate during an interview for a position that required what I usually think of as ETL experience and they described the process you mention as the "ETL" process they were familiar with, it's very likely that the interview would end politely and ...
4
Starting at the beginning:
SQL Server uses statistics objects to generate cardinality estimates for the result set steps during the optimization phase. Cardinality estimates are used to generate a query plan, which is the logical plan by which SQL Server will physically access and retrieve the data in order to satisfy the request. Generally speaking, more ...
4
Based on your sample data it looks like there are 6 columns in total.
COL1, COL2, COL4, COL5, COL6 = All fixed width .
COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding).
You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed ...
4
You don't need any auxiliary tools. This works on any platform.
COPY to a simple temporary ancillary table:
CREATE TEMP TABLE tmp(txt text);
COPY tmp FROM '/path/to/your_file.csv' (FORMAT csv, ENCODING 'utf8', DELIMITER E'\b');
I chose backspace as delimiter (E'\b') which never occurs. This way you get one text column.
Assuming UTF8 encoding. Adapt if ...
4
Visual studio 2015 SSDT work with SQL Server 2016 and has a backward compatibility to SQL server 2014 and 2012 only.
Target version of SQL Server | Development environment for SSIS packages
2016 | SQL Server Data Tools for Visual Studio 2015
2014 | SQL Server Data Tools for Visual Studio 2015 or SQL Server Data Tools - Business Intelligence ...
4
That sounds perfectly expected. If you are updating most of your data and rebuilding all of your indexes: all the affected extents need to be in the differential backup, there is simply no way around that because it must contain every extent that has changed since the last full or differential backup.
If you are not already doing so add the COMPRESSION ...
4
REQUIRE SSL grant option on users is what you need. It works for me as intended:
MariaDB [(none)]> CREATE USER testssl@localhost;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> GRANT USAGE ON *.* TO testssl@localhost REQUIRE SSL;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> Bye
$ sudo mysql -u testssl --ssl
Welcome to the ...
3
There are few ways to work around this problem:
As you mention, partitioning will help. If you partition on the leading column in the index and make sure all partitions are equally sized, you can gain super scale. However, the total CPU required to build the index wont be much lower if you partition. But it will save you memory (if you have a slow I/O ...
3
Pentaho have an open-source tool called Kettle that we use for ETL development with Postgres. It is quite good and very comprehensive.
http://community.pentaho.com/projects/data-integration/
Only top voted, non community-wiki answers of a minimum length are eligible