32
VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs.
These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there ...
25
I see nothing in your question that autovacuum would not take care of. It largely depends on the pattern of your writing activities. You mention 3 million new rows per week, but INSERT (or COPY) typically don't create table and index bloat. (autovacuum only has to take care of column statistics, the visibility map and some minor jobs). UPDATE and DELETE are ...
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 ...
14
lots of Discussions about ETL vs ELT out there.
The main difference between ETL vs ELT is where the Processing happens
ETL processing of data happens in the ETL tool (usually record-at-a-time and in memory)
ELT processing of data happens in the database engine
Data is same and end results of data can be achieved in both methods.
it very much depends on ...
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 ...
11
It's almost a matter of semantics. A lot of hot air gets released in discussions about this but I'm not really convinced that there is any real philosophical depth to a distinction between the two.
At some level you can view ETL as transforming data in a client-side tool before finally loading it, with ELT implying that the data is transferred to some sort ...
answered Jun 14 '12 at 18:44
ConcernedOfTunbridgeWells
16.4k2 gold badges52 silver badges69 bronze badges
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
This is a resource issue.
The DB server cannot satisfy your queries due to the client host being configured incorrectly as-per the pre-requisites for an Oracle database client installation.
Ask your system administrators to verify that they have set the required number of open files, semaphores, shmax etc etc. Link to the Oracle documentation - I assume ...
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
25.2k2 gold badges25 silver badges59 bronze badges
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
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
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
