Post reply

DTSLookups - Performance question?

  • January 16, 2006 at 4:50 am

    #68641

    I am looking at a DTS package that imports a text file and performs 3 separate DTSLookups as the records are imported.

    This means that instead of a blindingly fast import the process is very slow, as in "time it with a calendar" slow.

    The approach I have taken in the past is to upload my text file into a raw import staging table then run a data driven query task to process this into the final destination table.

    This is extremely fast and I am left wondering why anyone would bother to use a DTSLookup other than if disk space was extremely tight?

    LinkedIn Profile

  • January 16, 2006 at 3:01 pm

    #615013

    I think I used both DTSLookups and Data Driven Query about once each.

    My preferred approach is to bulk load data to a table and run stored procedures as required to process the data.

     

    --------------------
    Colt 45 - the original point and click interface

  • January 17, 2006 at 6:28 am

    #615116

    Thought i'd throw this out there for you to test out... in your lookup query, try adding a " WITH (NOLOCK)" on your FROM clause... this will keep it from locking the table, which I have found usually is the cause of a lot of wasted CPU cycles.

    -Mike Gercevich

  • January 17, 2006 at 9:42 am

    #615205

    I'm with Phill on this one. I always load data as raw as it comes and then transform it through stored procedures into tables where my users can look at it. I then squirt the raw data into archive tables so that if the transformations need changing its simple to move data back into the raw tables from the archive an re-run the transformation procs.

  • January 17, 2006 at 5:02 pm

    #615333

    I guess the power of lookups comes from the ability to run each on a different data source, so you can implement some very complex cross-server processes.

    The price of all this of course is crappy performance.

    I took a 32 hour DTS process that used 8 lookups and squeezed it down to under 2 hours by copying required tables into a staging area and joining them in a set-based operation that feeds the DDQ task.

    Next iteration of that system, we dispensed with the DDQ task altogether and do it all in 15 minutes in a T-SQL stored proc.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply