Is it possible to run faster when data compare?

mago

Dears:

Many thanks for great product to make us convenient to sync database.
But I have a question about sync performance.
I'm not sure if it's limit about data sync.
We have two database sync, the data size is about 1GB.
It costs 3 mins every sync. Is it possible to run faster when data compare?
I set "ignoreidentical" in xml. As for other parameters, it's default options.
Please let me know if I could do anything to improve the performance. Thanks a lot.


Regards,
Mago

mago

Dears:

I found you ever mentioned that indexed view sync.
I guess it may resolve our problem. Because we just used a limited data of whole table. But I try to build a indexed view and sync. The sync is done, but related tables are not synced. Could you please kindly tell us should I do any setting about indexed view sync ? Thanks!

You could make an indexed view on the table and compare the indexed views instead. Normally you can also synchronize through the indexed views and that will update the underlying tables.

Brian Donahue

Does this goive you an error? In certain circumstances you'd need to implement an INSTEAD OF trigger on the view to get an update through it to work. You can look up INSTEAD OF triggers in SQL Server Books Online but the gist is that you get the updated record to update the underlying table in a different way.

mago

Dear Brian:

Thanks for your reply. :-)
I got the problem. I found I didn't create index on the view, So the indexed view couldn't sync. But when I try to create unique indexed on it, the error is "Index cannot be created on view 'Data' because the view contains text, ntext or image columns". I check the sql server document, it said :

Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index. In addition, a view cannot include any text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement

Since we couldn't use indexed view. Do you have another suggestion for rapid sync ? :<

Brian Donahue

Aside from inserting the data you want into another table and comparing that one instead, I think we've exhausted the possibilities there.

mago

I couldn't know your meaning.

My steps are :
1. Create a schema binding view for 3 column.
The data type is (int, nvarchar, ntext)
2. Set unique, clustered index on this view
=> Have error in sql server
3. I ingore to build unique, clustered index on the view. Sync two views in sql data compare, then it would sync all tables, not including the view.

If we have ntext column, that means we couldn't build a indexed view for this table to sync indexed view ?

Brian Donahue

Hello,

There are certain limitations on indexed views and that may be one of them. I don't think an indexed view is going to work for you.