Alternative for DTC - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Alternative for DTC

Author

Topic

gop_62
Starting Member

8 Posts

Posted - 2007-07-30 : 05:45:36

Hi,
i am using the DTC in my code to connect to two different servers on the network through a SQL query which is unfortunately very slow; can u please guide me with an alternative for the same

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 05:53:16

You can try LINKED SERVER.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 05:56:54

doesn't linked server also start DTC?

the way to go here is to return as little data from the linked server as possible.

but for running DTC the whole process will be as slow as the slowest machine in the DTC process

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:03:06

Then there is OPENROWSET. But that also maybe starts DTC?

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 06:08:00

AFAIK, DTC is started once that you start to query data outside of the instance in a transaction no matter what the method is.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

gop_62
Starting Member

8 Posts

Posted - 2007-07-30 : 06:16:21

Well, i tried linked server too, but that does start the DTC. Can i use File I/O in my code and if so how? I am using visual studio 2005 and the code is in C#

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:21:59

Maybe it is wrong with the query?
How is the query written? Post the query here.

Or the network is slow?

E 12°55'05.25"
N 56°04'39.16"

gop_62
Starting Member

8 Posts

Posted - 2007-07-30 : 06:29:25

I have used two different servers in the query, and have atleast five consecutive queries like the one below to insert a part of a table from one server to another.

The syntax for C# requires the commas. etc.

SqlCmd.CommandText = "SET XACT_ABORT ON \r INSERT INTO " +
"[" + sCompLibServer + "].[" + sCompLibDB + "].[dbo].tblImportCompanyComponentTree(ID, Name, " +
"Model, AdditionalInfo,Rating, ImportID, LevelInTree, PositionInBranch, " +
"IsDeleted, Surveyable, Critical, IsFolder, Environmental, Approved, IsLocked) " +
"SELECT cct.ID, cct.Name, cct.Model, cct.AdditionalInfo, " +
"cct.Rating,'" + sImportID + "',0," + lit1 +
", 0,0,0,0,0,0,0 FROM [" + i.sISMSDB + "]..tblwrkCompanyComponentTree cct " +
"WHERE cct.ID = '" + compName + "' " +
"AND cct.IsDeleted = 0 AND cct.IsRedundant = 0";

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:39:42

The query does not seem that complex.

1) How many records are transferred?
2) How many records are there in source database?
3) How many records are there in target database?

Problem description
1. If many records are transferred, the network may crawl to a stop.
2. Maybe it take too long time to find the records you want? Which indexes do you have?
3. Maybe it take too long time to insert to records? What indexes do you have?

E 12°55'05.25"
N 56°04'39.16"

gop_62
Starting Member

8 Posts

Posted - 2007-07-30 : 07:11:20

The query does transfer a hierarchial tree structure, at the most it can have around 100 to 200 elements, however the query following it does the work of transfering the related data (in this case - Equipments like spares, activities to the size of 2000-3000 and 100 resepctively)
I have wrapped these queries in a transaction, wonder if this is the problem
Also for the time being, i have not included the images which can be as many as 50 to 60 for every single transaction and each image size is 25-50KB

The indexes are common for both the tables - i.e for the GUID(the CompanycomponenttreeID)

gop_62
Starting Member

8 Posts

Posted - 2007-07-30 : 07:12:45

Can i use File I/O to make the select and insert queries instead of using DTC; if so how?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 07:19:36

you could use BCP to export and then import data from flat file...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

gop_62
Starting Member

8 Posts

Posted - 2007-07-30 : 09:34:26

But can i use bulk insert for a selective query?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 09:37:55

you bulk insert the data you exported... you filter it in the export.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 09:43:29

You mean that

1) Exporting the data to a file with BCP
2) Copying the file over the network
3) Importing the file

is faster than

1) Running the query over a linked server

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 10:44:06

i have no idea what is best for his problem...
i'm just giving him options...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

gop_62
Starting Member

8 Posts

Posted - 2007-07-31 : 01:18:10

Thanks guys for showing interest
I need to find another way out
can i by any chance use threads to improve the performance, in that case two queries will be run at the same time and dont know whether this is possible.
Can you please advise

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 02:10:40

SQL Server takes care of that.
You know that SQL Server can run 100's of users simultaneously?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources