Chris M:

Your code is...

Grant Fritchey (9/17/2012)

---

ISNULL and COALESCE on columns in the where clause are going to hurt performance. I still think your stats are off. I'm seeing estimated 4.5 million, actual 15,000....

I see a couple of websites that explain some of this:

• http://www.mssqltips.com/sqlservertip/1856/sql-server-query-execution-plans-in-sql-server-management-studio/
• http://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/

I also see that Grant has a book on execution plans:

• http://www.sqlservercentral.com/articles/books/65831/

---

Anyways, I've now got query down to 15-20 seconds; it...

Silly question, how do I read the execution plan to know what columns to index?

I created two indexes (just eyeballed query and guessed). The first one seems to have...

I did an update of the statistics on all of the related tables. That didn't change the execution time.

The view ([font="Courier New"]EmailAudit[/font]) duplicates many of the tables in the...

I'm updating statistics on those tables right now; dbo.EmailAudit is a view. I'll look into that.

Thanks,

Rob

See if this helps: http://msdn.microsoft.com/en-us/library/ms137625(v=sql.105).aspx

Take a look at this:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/67871/

That should give you a pretty good idea of what you want.

HTH,

Rob

At a high level, you're going to create a SQL Agent job that runs a SSIS package that extracts/pulls the data you want from your CRM machine and writes it...

Really? for development you can get the immediate BIDS feedbak by running package through a SQL Agent job on the server?

Rob

I'm not in disagreement with you Elliot about having SSIS installed and running on the server and not through BIDS on a local machine.

if you are pushing data from one...

I realize that your data source is a flat file, but could you import the flat file into a SQL Server table or temp table. Then you could do...

What leads you to think that CPU is a bottleneck?

On most of my SSIS packages (even when run through BIDS) doing data loads, network or I/O are the typical slow...

Try running the T-SQL from your OLE DB Command in SSMS. Does it squawk about a conversion there? This might shed some light.

HTH,

Rob

If you're using SQL 2008 or newer, it sounds like a good place for the MERGE statement.

Checkout: http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/