1. Create a view for the data you need to work on.

2. Query DISTINCT customer email from the view

3. Loop on the customers

4. Query the view again filtering for current...

The account that executes the job step must have sufficient privileges to load the provider and connect to the target database.

That account will (hopefully) be different from your user account.

Depending...

You need the OLEDB provider for DB2 AS/400 that you can download here:

http://www.microsoft.com/en-us/download/details.aspx?id=29100

Both INSERT INTO and SELECT ... INTO can achieve minimal logging.

INSERT INTO can be minimally logged if the following conditions are met:

* The recovery model of the database is set...

Look s like the trace file is corrupted.

DId you try to open it with profiler?

Oracle GoldenGate is an option. Not cheap though.

Hmmm, yeah.

I had the chance to optimize some...

SQL Server has a very bad implementation of cursors. I have no idea what exactly makes them so slow compared to Oracle cursors.

That said, regardless of the fast or slow...

Pipe the results to a temporary table:

IF OBJECT_ID('tempdb..#discard') IS NOT NULL

DROP TABLE #discard

GO

SELECT (

SELECT TOP(1) COUNTRY_CODE_ALPHA2

FROM...

Discard your results: transferring and formatting results is part of the execution time and, since you're returning a big...

ChrisM@Work (5/15/2014)

I wonder if that is functionally equivalent to this:

SELECT

SRC_COUNTRY_CODE_ALPHA2 = x.COUNTRY_CODE_ALPHA2,

e.*

FROM ONS_AIO_1.SECURITY_EVENTS_STG_TEST e

CROSS APPLY (

SELECT

c.COUNTRY_CODE_ALPHA2,

rn = ROW_NUMBER() OVER(ORDER BY c.[START] DESC)

FROM IG_ODS.dbo.IP2COUNTRY_LOOKUP c

WHERE e.SOURCE_ADDRESS_INT BETWEEN c.[START] AND...

You cannot avoid the scan: you're asking for the whole SECURITY_EVENTS_STG_TEST table (no filter predicates).

As far as the index spool is concerned, I have no idea what it's trying to...

+1000

Please post:

1) Table creation scripts

2) Index creation scripts

3) Actual execution plan attached as .sqlplan file (not jpg image)

4) Some sample data (possibly same amount of data you have in production,...

Not out of the box.

You should create an audit for that.