1

I extract 5 tables from sql server A to B. Each extraction is a query that joins many tables.

I usually run these extractions in sql server import wizard. I put them in SSIS (5 parallel extractions) now and I notice it's much slower (at least 50x slower).

enter image description here

I wonder am I doing the popular/best practice here? And idea why it's slow? Thanks

Thanks everyone for the discussion

I followed JodyT's idea and generated SSIS from import wizard, and found the difference:

in the Destination I should use Table or view - fast load - when I change from Table or view to this, I have speed similar to import wizard. enter image description here

YJZ
  • 3,934
  • 11
  • 43
  • 67
  • 1
    Are all tables located on the same database (Source?, destination?), Are tables stored within one file group? If there are many file groups are they located on the same Hard Disk? What is the source and destination server specifications? – Hadi Aug 17 '19 at 02:07
  • The Import and Export wizard creates and executes an SSIS package. You can save this package and inspect it to see how the data is imported. – JodyT Aug 19 '19 at 12:47
  • Are you measuring wall clock time or CPU (including I/O) time? – lit Aug 19 '19 at 18:40

1 Answers1

0

As with just about any question like this, whether or not what you are doing is the "best practice" depends. Your queries to gather data are not the only component to consider for performance. Those other factors to consider are:

  1. as others have hinted - your hardware (destination, source, network inbetween, etc...)
  2. your data (overall size, depth, width)
  3. your schedule (is this the only thing running? Must it be done within a given window? etc...)

For example, if the destination or source or network hardware are used heavily by others, you might not want to thread at all to minimize the amount of concurrent use. Conversely, if the hardware is dedicated to scheduled batch activity, then you likely do want to split the data movement into threads and use as much of the hardware as possible to reduce the overall time spent doing this task.

Short of being able to know all that info and make the best decision you COULD also just try incrementally increasing the number of threads. First, assess what "fast" and "slow" mean for your environment. When you run just one thread what is the transfer rate (if you don't already know this from knowing the hardware). Then add another thread, reassess transfer rate. Then another and so on until and if you see a decrease in overall time to move the data. Increase until you know you are using the optimal number of threads... for your hardware, on your network, within your schedule for your data.

You wont get a definitive answer here (or at least, you shouldn't :) ) on what is 'best' for you. Too much to share and consider.

jamie
  • 745
  • 4
  • 11