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....
19
Here's what I've done before:
(SELECT 'TableA', * FROM TableA
EXCEPT
SELECT 'TableA', * FROM TableB)
UNION ALL
(SELECT 'TableB', * FROM TableB
EXCEPT
SELECT 'TableB', * FROM TableA)
It's worked well enough on tables that are about 1,000,000 rows, but I'm not sure how well that would work on extremely large tables.
Added:
I've run the query against my ...
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 ...
14
While it doesn't automatically prevent duplicates, you can disable the identity temporarily using the following, and then you would likely just want to set the identity seed to the highest value in the table:
SET IDENTITY_INSERT dbo.tablename ON;
INSERT ...
SET IDENTITY_INSERT dbo.tablename OFF;
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = N'DBCC ...
12
I wouldn't want to have 200 data flows in a single package. The time it'd take just to open up and validate would make you old before your time.
EzAPI is fun but if you're new to .NET and SSIS, oh hell no, you don't want that. I think you'll spend far more time learning about the SSIS object model and possibly dealing with COM than actually getting work ...
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 ...
8
Here are several ideas that might help:
Try different data diff tool - have you tried Idera's SQL Comparison toolset or ApexSQL Data Diff. I realize that you already paid for RG but you can still use these in trial mode to get the job done ;).
Divide and conquer - how about splitting tables into 10 smaller tables that can be handles by some commercial data ...
7
I believe you should investigate BINARY_CHECKSUM, although I would opt for the Red Gate tool:
http://msdn.microsoft.com/en-us/library/ms173784.aspx
Something like this:
SELECT BINARY_CHECKSUM(*) from myTable;
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
26.4k2 gold badges27 silver badges61 bronze badges
6
The way I would do this is with a Derived Column Transformation. Take a look at this example (hitting the sample database, AdventureWorks2012):
As you can see on this screenshot, what I'm doing is taking the Name column from AdventureWorks2012.HumanResources.Department (this would work with your version of SQL Server/SSIS as well, I believe. Although you ...
6
While I agree that using the auto features is best instead of running it database wide, in most cases per table tuning is necessary.
I don't quite agree with the design choice of postgres to tie together vacuum and analyze, I have seen several instances where databases that do a lot of insert/update but little delete never get analyze done and start to ...
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
The quickest fix would be to restart the SQL Server and Tempdb will be recreated with default size and empty files.
But if it's a production server you can't really restart it when you want. A real fix would be to add a new file on a different drive and run your queries.
An example would be (new file of starting size 1 MB, increase 100 MB, limit 500 MB):
...
5
We finally resolved the issue. It turns out that SSIS calculates the length based on the first handful of rows in the excel file. When we moved the rows with the longer data to the top the columns changed to unicode text (allowing for the extra length).
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
SSIS does a good work of exporting data from Oracle to SQL Server. I suggest you create staging tables in SQL Server and use SSIS to extract the data for the 10 Oracle tables. Then either use stored procedures in SQL Server and execute them using SSIS or use Merge syntax and try to do both insert as well as update in Control Flow. Updates tend to be slow in ...
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
