new features suggestion

DanAvniDanAvni Posts: 72 Bronze 2
after comparing tables i need to insert about 400000 records to a table on a db i access on a remote server on a VPN. since the traffic out of our office is slow i have been looking on ways to improve the performance of the sql that data compare generated to insert these rows. one thing that i noticed is that since my table has a lot of fields the sql looks like this:
insert into table (field1, field2, ... field N) values (1,2,3...)
insert into table (field1, field2, ... field N) values (1,2,3...)
and so on.
my first improvement idea is this: since the transmission time of each statement is long (because of our slow Internet connection), what if data compare created a SPROC on the beginning of the sync script that does the insert and then only executed it
from very basic testing i did, this would shorten the sync script by about 50%(!)
the sproc could then be dropped at the end of the script thus creating a much more efficient script.

another idea is this:
have data compare generate an exe file or a data file for an exe that comes with data compare and is free to distribute. the exe would be run on the target server and will show a small GUI that allows me to select the DB and just click a "run" button to start the insert process. if the data for this exe would be saved in a compressed and efficient way it could be even faster to execute it that to run the 400000 insert statements
Dan Avni

Comments

  • Thanks for your ideas. One option to consider may be to install SQL Data Compare on a machine on the network and remote desktop to it, rather than to run over a slow vpn connection.

    With regards to your second suggestion, we have a product, SQL Packager, that does just that. It lets you specify schema and/or data changes and build this into an executable.

    http://www.red-gate.com/products/SQL_Packager/index.htm

    Kind regards,

    David Atkinson
    Product Manager
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • DanAvniDanAvni Posts: 72 Bronze 2
    Hi David,

    thanks for the quick reply. running on a remote machine is not much faster in my case since i have machines on different continents (one on Israel and one on south America). i am simply looking for ways to make to sql run faster using the current configuration

    about sql packager, after running data compare, let's assume that data compare found 2 new records and 4 modified records and i want just to run the 2 inserts for the new records. on data compare i do it simply by selecting the checkbox of the 2 records while clearing all other checkboxes. however on sql packager, i can only move entire tables and not only parts. maybe you can make data compare generate an sql package based on my selections?
    Dan Avni
  • That's a good point. SQL Packager doesn't benefit from the row-selection granularity of SQL Data Compare.

    I've taken note of your suggestions and will try to push some improvements into future versions of our products.

    Thanks for your ideas. :)

    David Atkinson
    Product Manager
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • DanAvniDanAvni Posts: 72 Bronze 2
    following the discussion, please add support for row costructors in sql 2008 which would greatly improve performance. see
    http://www.sqlcommunity.com/Default.asp ... =77&id=247 for more details
    Dan Avni
Sign In or Register to comment.