slow DTSDataPumpTask - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

slow DTSDataPumpTask

Author

Topic

SomethingStupid
Starting Member

3 Posts

Posted - 2008-05-06 : 11:57:44

hi

I use DTS to copy data from Sqlserver to MYSQL. For MySQL db connection , mysql odbc driver is used and also i'm logging queries that run over odbc. When i try to execute DtsDatapumptask from my visual basic activex DLL , it runs too slowly . I've looked query logs passing over mysql odbc and i've seen "select * from [destination_table]" log record before insert logs.
that's the problem because destination table has more than two hundred thousand rows.
Why is DTS selecting all record from destination just before import?
How can i solve this problem ?

Thanks and sorr for my poor english

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-06 : 21:32:34

How did you build package? Has such code you mentioned in it?

SomethingStupid
Starting Member

3 Posts

Posted - 2008-05-07 : 04:16:58

here is the vb code
---------------------------------------------
Set oStep = goPackage.Steps.New

oStep.Name = "Copy Data from Results to `webaraba`.`temp_cached_pages` Step"
oStep.Description = "Copy Data from Results to `webaraba`.`temp_cached_pages` Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from Results to `webaraba`.`temp_cached_pages` Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = True

goPackage.Steps.Add oStep
Set oStep = Nothing

Call Task_Sub1(goPackage, visitId)

Public Sub Task_Sub1(ByVal goPackage As Object, Optional ByVal visitId As Long = -1, Optional ByVal visitTime As Date)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Copy Data from Results to `webaraba`.`temp_cached_pages` Task"
oCustomTask1.Description = "Copy Data from Results to `webaraba`.`temp_cached_pages` Task"
oCustomTask1.SourceConnectionID = 1
If visitId <> -1 Then
oCustomTask1.SourceSQLStatement = "SELECT * FROM CACHED_PAGES WHERE VISIT_ID > " & visitId
Else
oCustomTask1.SourceSQLStatement = "SELECT * FROM CACHED_PAGES WHERE VISIT_TIME >= '" & visitTime & "'"
End If
oCustomTask1.DestinationConnectionID = 2
If visitId <> -1 Then
oCustomTask1.DestinationObjectName = "`webaraba`.`cached_pages`"
Else
oCustomTask1.DestinationObjectName = "`webaraba`.`temp_cached_pages`"
End If
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1(oCustomTask1)

goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("VISIT_ID", 1)
oColumn.Name = "VISIT_ID"
oColumn.Ordinal = 1
oColumn.Flags = 32784
oColumn.Size = 0
oColumn.DataType = 131
oColumn.Precision = 18
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("VISIT_TIME", 2)
oColumn.Name = "VISIT_TIME"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("PAGE_URL", 3)
oColumn.Name = "PAGE_URL"
oColumn.Ordinal = 3
oColumn.Flags = 8
oColumn.Size = 350
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("PAGE_CONTENT", 4)
oColumn.Name = "PAGE_CONTENT"
oColumn.Ordinal = 4
oColumn.Flags = 232
oColumn.Size = 0
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("PAGE_SIZE", 5)
oColumn.Name = "PAGE_SIZE"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 131
oColumn.Precision = 18
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("NEXT_VISIT_TIME", 6)
oColumn.Name = "NEXT_VISIT_TIME"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("VISIT_ID", 1)
oColumn.Name = "VISIT_ID"
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 131
oColumn.Precision = 18
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("VISIT_TIME", 2)
oColumn.Name = "VISIT_TIME"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("PAGE_URL", 3)
oColumn.Name = "PAGE_URL"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 0
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("PAGE_CONTENT", 4)
oColumn.Name = "PAGE_CONTENT"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("PAGE_SIZE", 5)
oColumn.Name = "PAGE_SIZE"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 131
oColumn.Precision = 18
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("NEXT_VISIT_TIME", 6)
oColumn.Name = "NEXT_VISIT_TIME"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties

Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-07 : 14:46:54

Looks like you run two 'select * ...' in the package. Did you try with import/export db objects wizard?

SomethingStupid
Starting Member

3 Posts

Posted - 2008-05-07 : 15:46:12

no, code does not run two selects . It prefers one of two from paramaters.

Also I've tried the wizard.It's slow too.
Again the same way : DTS tries to "select * from" destination table.
Why why why?.....

sdidev
Starting Member

1 Post

Posted - 2008-05-28 : 12:58:21

I've experienced the slowness issue you mentioned (DTS & MySQL) - both in external application and using the DTS Wizard. I suspect you might have a similar issue, here is what I did:

Change the default database engine for MySQL from InnoDB (or other) to MyISAM - I think you will see a huge improvement in speed. I just stumbled on this problem today myself, and my application with MySQL allows me to make this change without issue.

Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources