new features suggestion
DanAvni
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
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
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
Product Manager
Redgate Software
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?
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
Product Manager
Redgate Software
http://www.sqlcommunity.com/Default.asp ... =77&id=247 for more details