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 ...
18
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....
answered Feb 4 at 15:25
17
The maximum batch size for SQL Server 2005 is 65,536 * Network Packet Size (NPS), where NPS is usually 4KB. That works out to 256 MB. That would mean that your insert statements would average 5.8 KB each. That doesn't seem right, but maybe there are extraneous spaces or something unusual in there.
My first suggestion would be to put a "GO" statement after ...
17
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 ...
16
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
BULK INSERT or bcp seem more appropriate options than 45,000 insert statements.
If you need to stick with the insert statements, I would consider a few options:
A: Use transactions and wrap batches of 100 or 500 or 1000 statements in each one to minimize the impact on the log and the batch. e.g.
BEGIN TRANSACTION;
INSERT dbo.table(a, ...) SELECT 1, ...
...
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 ...
13
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 ...
11
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 ...
10
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.2k2 gold badges50 silver badges69 bronze badges
9
I am not sure why you are getting the out of memory error, but there is an easier approach.
If you can export the data from the spreadsheet into a delimited format (e.g. csv) you can use the data import wizard in SSMS to insert the data for you:
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 ...
8
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 ...
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 ...
answered Jan 2 '16 at 15:06
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?
...
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
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 (...
5
If you have 200 identical sources then you can parameterise a SSIS package with the data source and kick off nultiple threads. These can be controlled within the package by a foreach loop or from an external source that kicks off the extractors with a parameter.
You could consider a full load for relatively small dimensional sources and an incremental load ...
answered Apr 30 '12 at 10:16
ConcernedOfTunbridgeWells
16.2k2 gold badges50 silver badges69 bronze badges
5
We do this, the environment varaiable is at the user level, so the user for each environment that runs the SQL agent jobs is different. So on the the dev environment our agent user is something like SQLDev and on the QA environment it is something like SQLQA.
If you are not running from jobs (which I highly suggest doing except on dev while doing actual ...
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,...
Only top voted, non community-wiki answers of a minimum length are eligible
