speed up my importing project

[lucia]

I am using vb6 and sql server 2000.
I have a program to import several tables from one server to another using vb6. I am using "INSERT" to add new records one by one. But when the table gets larger, the project is really slow to finish the process.

Can anyone give me any advise how to speed up the process?

Thanks a lot.

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

Is there a particular reason you are using VB, rather than making use of the features provided by the database system?

The chances are that leaving VB out of it will make it much faster, as you don't need to transfer all the data from the source database to your program (but this does depend on what code you are using).

If you show us your code (or at least a sample), we can give you more specific advice.

[techgnome]

Your best bet would be to set up a DTS package to do the datatransfer... if it is something that's done on a regular basis, you can also set up a SQL Job to run the package overnight, so you don't have to do it yourself.

-tg

[lucia]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

Is there a particular reason you are using VB, rather than making use of the features provided by the database system?

The chances are that leaving VB out of it will make it much faster, as you don't need to transfer all the data from the source database to your program (but this does depend on what code you are using).

If you show us your code (or at least a sample), we can give you more specific advice.

I use vb because I need change some data while importing.
Here is the sample code:

Do While Not rcsSS.EOF
       strRouteId = ""
       strComm = ""
       strCreationDT = ""
       
       iIndex = iIndex + 1
           
       If IsNull(rcsSS!Route Or IsNull(rcsSS!xCDC) Then
          strRouteId = "Null"
       Else
          If rcsSS!Route<> "" And rcsSS!xCDC<> "" Then
            strRouteId = GetRouteId(rcsSS!xCDC, rcsSS!Route)
          End If
       End If
       
       If rcsSS!Comm <> "" Then
           strComm = GetCommodity(rcsSS!Comm)
       End If
    
              
       'Check to see if record alread exist.
       strSQL = "SELECT * FROM SMent WHERE ItemID ='" & FixQuote(rcsSS!ItemID) & "' "
       Set rcsT = New ADODB.Recordset
       rcsT.Open strSQL, connT, adOpenKeyset, adLockOptimistic
          
       'If find, update the record.
       If Not rcsT.EOF Then
          rcsT!Shipper_Id = rcsSS!Shipper_Id
          rcsT!Pickup_Date = rcsSS!Pickup_Date
          ........ 
          ........          
          rcsT.Update
       Else 'Add New Record
          
          'Check TimeDate fields
          'If it is not Null, Store it in strings to get the millisecond transfered right.
          If IsNull(rcsSS!Creation_D) Then
             strCreationDT = "Null"
          Else
             strCreationDT = GetTime(rcsSS!Creation_D)
          End If
        
          strSQL = "INSERT Sment (ItemID, Shipper_Id, Pickup_Date, ....)
          strSQL = strSQL & "VALUES ('"
          strSQL = strSQL & FixQuote(rcsSS!ItemID) & "', '"
          strSQL = strSQL & FixQuote(rcsSS!Shipper_Id) & "', "
          
          If IsNull(rcsSS!Pickup_Date) Then
             strSQL = strSQL & "Null, '"
          Else
             strSQL = strSQL & "'" & Format$(rcsSS!Pickup_Date, "mm/dd/yyyy") & "', '"
          End If
          
          strSQL = strSQL & strComm & "', '"
                                                
          If strCreationDT = "Null" Then
             strSQL = strSQL & "Null, "
          Else
             strSQL = strSQL & "'" & strCreationDT & "', "
          End If
          
          ............
                                                 
          ConnT.Execute strSQL
                       

       rcsSS.MoveNext
       
       frmProgress.ProgressBar1.Value = iIndex
       
    Loop

Thank you.

[si_the_geek]

Your VB code would be quicker (and safer) if you used a Command object rather than building SQL statements in strings (note that if FixQuote changes ' to '', that only solves one special-character issue, whereas the Command object solves all the others too).

I presume the only changes you are doing are the ones done by GetRouteId and GetCommodity, but as you haven't shown us the code for those we can't tell you how you would do the equivalent in SQL - but I suspect it is possible (and that doing so would make the process significantly faster).

[lucia]

Your VB code would be quicker (and safer) if you used a Command object rather than building SQL statements in strings (note that if FixQuote changes ' to '', that only solves one special-character issue, whereas the Command object solves all the others too).

I presume the only changes you are doing are the ones done by GetRouteId and GetCommodity, but as you haven't shown us the code for those we can't tell you how you would do the equivalent in SQL - but I suspect it is possible (and that doing so would make the process significantly faster).

Thank you, Si.

Can you give me a sample of using command object?

the changes are GetRouteId, GetCommodity and GetTime( i need get millseconds of all the date time fields, otherwise the records may dupe with my destination records and will be treated as old data.)

GetRouteId and GetCommodity are functions to get RouteId and Comm from cross-reference table.
The code in GetRouteId is like this

Private Function sysGetRouteId(ByVal strTerm As String, strRouteNum As String) As String
   
   Dim rcsRouteID As ADODB.Recordset
 
   strSQL = "SELECT xCDC, SS_Route, CT_Route "
   strSQL = strSQL & "FROM Route_Xref "
   strSQL = strSQL & "WHERE xCDC='" & FixQuote(strTerm) & "' "
   strSQL = strSQL & "AND SS_Route ='" & FixQuote(strRouteNum) & "' "
   Set rcsRouteID = New ADODB.Recordset
   rcsRouteID.Open strSQL, ConnSSCan, adOpenForwardOnly, adLockOptimistic, adCmdText
   
   If Not rcsRouteID.EOF Then
      GetRouteId = rcsRouteID!CT_Route
   Else
      GetRouteId = strRouteNum
   End If
  
   rcsRouteID.Close
   Set rcsRouteID = Nothing
   
End Function

[si_the_geek]

Can you give me a sample of using command object?

I've been planning to write an FAQ about it, but for now there is an example (as Method 3) in the FAQ article How can I add a record to a database?.

It only shows how to use an Insert statement (but the conversion process is the same for all SQL statements), and doesn't contain use inside a loop, which can be done very efficiently.

GetRouteId and GetCommodity are functions to get RouteId and Comm from cross-reference table.
The code in GetRouteId is like this ..

Assuming that the SQL to fill rcsSS is something like this:

SELECT table1.fieldA, table1.fieldB, table1.fieldC, table1.xCDC, table1.Route
FROM table1
WHERE ....

you can incorporate sysGetRouteId into it like this:

SELECT table1.fieldA, table1.fieldB, table1.fieldC, table1.xCDC, table1.Route, 
       CASE 
          WHEN (table1.Route Is Null Or table1.xCDC Is Null) THEN Null
          WHEN (table1.Route = '' Or table1.xCDC = '') THEN ''
          WHEN (RX.CT_Route Is Not Null) Then RX.CT_Route
          ELSE table1.Route 
       END as RouteID
FROM table1 
LEFT JOIN Route_Xref RX ON (table1.xCDC = RX.xCDC AND table1.Route = RX.SS_Route)
WHERE ....

Note that if you didn't have the table name in front of the field names before, you will need to add that now.

To check that it gives the right results, you can either run it in a query window and check manually, or you can add a little extra code in your current section that sets strRouteId, eg:

       If IsNull(rcsSS!Route Or IsNull(rcsSS!xCDC) Then
          strRouteId = "Null"
If Not IsNull(rcsSS!RouteID) Then Stop
       Else
          If rcsSS!Route<> "" And rcsSS!xCDC<> "" Then
            strRouteId = GetRouteId(rcsSS!xCDC, rcsSS!Route)
          End If
If rcsSS!RouteID <> strRouteId Then Stop
       End If

(when you run the code, it will halt on those lines if the value differs)

If GetCommodity and GetTime work in a similar way to GetRouteId, you can do the same kind of thing for them.

[lucia]

Thanks a lot, Si.
The SQL to fill rcsSS is like this ( I select all in source table):

    strSQL = "SELECT * FROM Sment "  
    Set rcsSS = New ADODB.Recordset
    rcsSS.Open strSQL, ConnSS, adOpenKeyset, adLockReadOnly

   Do While Not rcsSS.EOF
       ......
   Loop

If I use the code you showed to me, do I have to select specific fields?

If GetCommodity and GetTime work in a similar way to GetRouteId, you can do the same kind of thing for them..

So, I left join commodity table just like you did on route table?

I will take a look the command method later. Thanks a lot.

[si_the_geek]

If I use the code you showed to me, do I have to select specific fields?

You might be OK not doing it, but it is definitely recommended (always) for a few reasons:

• It is faster - if you use * the database system has to find out what fields there are, and build the list for you.
• If you don't actually want all the fields, it is faster because less data needs to be transferred (in this case you don't want any fields from Route_Xref, except in the Case block).
• If you are getting data from multiple tables that have fields with the same name (like xCDC this time), at least one of them will be given a different name in the recordset.
• If any of the fields you need are ever removed from the table, your code will get an error at the point you try to open the recordset - rather than later when an error is likely to completely lock the table (depending on your error handling), and you may have already done things to other data that are no longer a good idea.

I think there are more reasons, but I can't remember them at the moment.

So, I left join commodity table just like you did on route table?

If GetCommodity does the same kind of thing as GetRouteId, yes.

[lucia]

You are right. I've changed select * to select fields. And I also tested code in query window, they worked perfectly. I am going to change my code in my project and see if the changes make any difference(speed up).

Thank you again, Si. I've learned a lot from you and this forum though I can't give you a point since I got "you must spread more ....." (I don't know what it is.)

[si_the_geek]

Just making those changes should speed up the process a bit (perhaps about 20%?), and from there it should be relatively simple to move across to purely SQL (which could be one Insert statement and one Update statement, with no looping) which should make it faster by a larger amount.

[lucia]

Just making those changes should speed up the process a bit (perhaps about 20%?), and from there it should be relatively simple to move across to purely SQL (which could be one Insert statement and one Update statement, with no looping) which should make it faster by a larger amount.

What do you mean

move across to purely SQL (which could be one Insert statement and one Update statement, with no looping) which should make it faster by a larger amount.

Can you spicify it a little more?

[si_the_geek]

Once you have a Select statement that gives the data as you want it (so includes the equivalents for GetCommodity etc), you can use that (with an extra Where clause) as the source for an Insert statement to directly add the records to the target, and similar for an Update statement.

For example, to add a single record to a table you could use this:

INSERT INTO Table1 (Field1, Field2)
VALUES (324, 'some text')

..but you can change the Values part to a Select statement, which can then add multiple records at once (or none, depending on how many records the Select statement returns), eg:

INSERT INTO Table1 (Field1, Field2)

SELECT FieldA, FieldB
FROM Table2 
WHERE ...

(in this case the Where would be the equivalent of
your current check for rcsT.EOF, but we'll get to the syntax of it later)

Doing something like that means that you don't need to transfer the data twice (rather than source->VB->target, just source->target), which is especially good if the source and target are on the same server.

It also means that you make use of the power of the database engine - which is much more efficient than VB code at this sort of thing, as it works with the records as a group (or Set) rather than as a series of single records like VB does.

It is hard to predict how much faster this would be (as it depends on too many factors, such as network speeds between the three locations, etc), but I have often seen that kind of change be about 10 times faster.

[szlamany]

Although it appears you are moving toward success in your problem - I wanted to explain methods we use here for loading data.

We almost always load data from TXT files into SQL with BULK INSERT.

BULK INSERT is the preferred method to load large amounts of data into a SQL database.

We like to load this data into staging tables - sometimes even TEMP TABLES.

Then we write a stored procedure to take data from the staging table and load into production tables.

That is where we would put the tricks that Si gave you for manipulating the data and transforming it prior to load into production.

The nice thing about doing it in a SPROC is that you can check for clean data and possibly abort the operation prior to trashing a production table.

Here is an example - we scrub a tax import from another vendor's system for refund checks that we are going to send out

CREATE PROCEDURE ScrubTax
As
BEGIN TRAN

Set NoCount On

Update TaxDist_T Set TaxAmount=Cast(Left(TaxPaid,10)+'.'+Right(TaxPaid,2) as money)
								,TaxStatus='A',TDate=GetDate() Where TaxStatus=''

If (Select Sum(TaxAmount) From TaxDist_T Where IsNull(TaxLevy,'')<>'' and TaxStatus='A')
	=(Select Max(TaxAmount) From TaxDist_T Where IsNull(TaxLevy,'')='' and TaxStatus='A')
	Update TaxDist_T Set TaxStatus=Case When IsNull(TaxLevy,'')<>'' Then 'B' Else 'P' End Where TaxStatus='A'
Else	Update TaxDist_T Set TaxStatus='X' Where TaxStatus='A'

COMMIT

But I also have to ask - as has been asked already - why aren't you going directly from SQL to SQL? Are these two database on the same server? If they are not can you setup a linked server between the two?

Here is one that actually grabs data from a remote PERVASIVE database

Declare @PostTbl Table (MasId int, RateSeq int, DistSeq int, PayPeriod varchar(7)
			,OffName varchar(100), TownId int, EntryDate datetime, DateWorked datetime
			,HoursPaid1 money, RatePaid1 money, HoursXRate1 money, AdminFee1 money, MiscFee1 money
			,Total money, InvoiceNum varchar(50), CustomerId varchar(50), VendorName varchar(100), PersonnelId varchar(10))

Insert into @PostTbl 
	select 0,0,0,''
		,RTrim(RTrim(Rank)+' '+RTrim(FirstName)+' '+RTrim(MiddleName)+' ' 
			+RTrim(LastName)+' '+RTrim(NameSuf))
		,cast(TownID as int)
		,CAST(EntryDate as datetime)
		,CAST(DateWorked as datetime)
		,CAST(HoursPaid1 as money)
		,Round(CAST(RatePaid1 as money),2)
		,CAST(HoursXRate1 as money)
		,CAST(AdminFee1 as money)
		,CAST(MiscFee1 as money)
		--If total is wrong, use SubTotal1
		,CAST(Total as money)
		,RTrim(IsNull(InvoiceNum,''))
		,CustomerID
		,RTrim(VendorName)
		,PersonnelId
	from AMSDDF1...PaidDetailTransactAT as PD left outer join AMSDDF1...PersonnelFileInfoAT AS PF on PF.ID=PD.PersonnelId left outer join AMSDDF1...VendorDetailCodesAT AS VC on VC.VendorId=PD.CustomerID
	--from openquery(amsddf1,'Select * from PaidDetailTransactAT as PD left outer join PersonnelFileInfoAT AS PF on PF.ID=PD.PersonnelId left outer join VendorDetailCodesAT AS VC on VC.VendorId=PD.CustomerID')
	Where EntryDate between @Start_Date and @End_Date and CustomerId not between '1000' and '1074' 
								and CustomerId not between '1076' and '2000'

Update @PostTbl Set MasId=TownId, PayPeriod=@Current_Pay_Period

Update @PostTbl Set RateSeq=IsNull(PR.RateSeq,0)
	From @PostTbl PT
	Left Join PayRate_T PR on PR.MasId=PT.MasId and PR.RateCd in ('PPD','PPD2','PLD') and PR.Amount=PT.RatePaid1
			and (IsNull(RatePPE,'')='' or RatePPE>=@Current_Pay_Period)
	Where PT.MasId<>0

Update @PostTbl Set DistSeq=Isnull(PD.DistSeq,0)
	From @PostTbl PT
	Left Join PayDistribution_T PD on PD.MasId=PT.MasId and PD.RateSeq=PT.RateSeq and PD.DistPerc<>0
			and (IsNull(DistPPE,'')='' or DistPPE>=@Current_Pay_Period)
	Where PT.MasId<>0 and PT.RateSeq<>0

If Exists(Select * From @PostTbl Where TownId=0 or RateSeq=0 or DistSeq=0)
Begin
	Select   OffName+' ('+cast(cast(TownID as int) as varchar(10))+')'		"~wide~Name"
		,TownId									"~form~frmPayroll/Payee"
		,CONVERT(varchar(10),EntryDate,101)					"~wide~Entry Date"
		,CONVERT(varchar(10),DateWorked,101)					"~wide~Date Work"
		,RatePaid1						">Rate Paid"
		,InvoiceNum								"~wide~Invoice"
		,CustomerID								"Vendor ID"
		,VendorName								"~wide~Vendor Name"
		,Case When TownId=0 Then '** Missing Town Id - Cannot Process **'
			When RateSeq=0 Then '** Cannot Find Rate with this Amount **'
			When DistSeq=0 Then '** Cannot Find Default Distribution **'
						Else '' End				"~wide~Error Message"
	From @PostTbl Where TownId=0 or RateSeq=0 or DistSeq=0
	Return
End

Begin Tran

Insert into PayPoliceTrans_T Select *, GetDate() From @PostTbl

Insert into PayTimeCard_T Select MasId,RateSeq,DistSeq,PayPeriod,DateWorked,Sum(HoursPaid1),'',GetDate() From @PostTbl
				Group by MasId,RateSeq,DistSeq,PayPeriod,DateWorked
.
.
.

That one grabs the data from the linked server - manipulates it.

Checks for errors - and if they are found returns a recordset for display to the user so they can see the errors.

Otherwise it starts a TRANSACTION that eventually loads the data into production tables.

[lucia]

Thank you Szlamany.
My importing program is importing data from one server to another(not linked). the table name and structure are almost the same. My destination table already has records and I need check if it needs to be added or updated. another thing is during the importing I need convert datetime field to millisseconds to avoid losing data. And also get new RouteId and comm which have already been solved by helping from Si.

[lucia]

Once you have a Select statement that gives the data as you want it (so includes the equivalents for GetCommodity etc), you can use that (with an extra Where clause) as the source for an Insert statement to directly add the records to the target, and similar for an Update statement.

For example, to add a single record to a table you could use this:

INSERT INTO Table1 (Field1, Field2)
VALUES (324, 'some text')

..but you can change the Values part to a Select statement, which can then add multiple records at once (or none, depending on how many records the Select statement returns), eg:

INSERT INTO Table1 (Field1, Field2)

SELECT FieldA, FieldB
FROM Table2 
WHERE ...

(in this case the Where would be the equivalent of
your current check for rcsT.EOF, but we'll get to the syntax of it later)

Doing something like that means that you don't need to transfer the data twice (rather than source->VB->target, just source->target), which is especially good if the source and target are on the same server.

It also means that you make use of the power of the database engine - which is much more efficient than VB code at this sort of thing, as it works with the records as a group (or Set) rather than as a series of single records like VB does.

It is hard to predict how much faster this would be (as it depends on too many factors, such as network speeds between the three locations, etc), but I have often seen that kind of change be about 10 times faster.

Thanks, Si.
With your help, I got RouteID and CommId with LEFT JOIN in SQL. But I have another same procedure to get milliseconds, Which slows down the process as well. Is there any method to get rid of that?
Thanks again.

[si_the_geek]

There probably is - but as I haven't seen how you do it now, I don't even know what it is actually doing, so can't tell how to convert it to the SQL equivalent.

My importing program is importing data from one server to another(not linked).

Is there anything stopping you from linking them? (as your program can connect to both servers it should be technically possible, so it will probably be a matter of whether you are allowed to or not).

If you can link them, it would allow you to move the entire process from VB to SQL Server - which would give several benefits (increased speed, ability to run it on a schedule, etc).

[lucia]

Is there anything stopping you from linking them? (as your program can connect to both servers it should be technically possible, so it will probably be a matter of whether you are allowed to or not).

If you can link them, it would allow you to move the entire process from VB to SQL Server - which would give several benefits (increased speed, ability to run it on a schedule, etc).

Tell you the truth I don't know where the actual source data located. I got testing database in my testing server(so, source and destination tables are in the same server) What can I do if I assume that I have linked server?

In my import procedure, i call the GetTime

          If IsNull(rcsSScanToCTrak!Creation_Date) Then
             strCreationDT = "Null"
          Else
             strCreationDT = sysGetTime(rcsSScanToCTrak!Creation_Date)
          End If

This is my sysGetTime, in this function I called GetMilliseconds.

Public Function sysGetTime(ByVal dDate As Date, Optional strFormat)

Dim lMilliseconds As Long
Dim bSecondRoundedToUpper As Boolean
    
    On Error GoTo ErrorHandler
    
    lMilliseconds = GetMilliseconds(dDate, bSecondRoundedToUpper)
    
    ' if the second gets rounded to upper substract one second
    If bSecondRoundedToUpper Then
       If lMilliseconds = "500" Then
          dDate = dRoundedDateTime
       Else
          dDate = DateAdd("s", -1, dDate)
       End If

    End If
    
    ' check of the strFormat is missing
    If Not IsMissing(strFormat) Then
       sysGetTime = Format(dDate, strFormat)
    Else
       sysGetTime = Format(dDate, "mm/dd/yyyy Hh:Nn:Ss")
    End If
    
    sysGetTime = sysGetTime & ":" & Format(lMilliseconds, "000")
    
    Exit Function

Public Function GetMilliseconds(dOriginalDate As Date, ByRef bSecondRoundedToUpper As Boolean) As Long


Dim strDateTime As String
   
   On Error GoTo ErrorHandler
   ' get the string (without milliseconds)
     strDateTime = CStr(dOriginalDate)

  ' return it to the datetime (this one doesn't have milliseconds)
  ' Dim dRoundedDateTime As Date
    dRoundedDateTime = CDate(strDateTime)
    
    ' check if we're below the zero
    If dOriginalDate > 0 Then
      'check if the rounded date is after the original
      If dRoundedDateTime > dOriginalDate Then
         'substract one second
         dRoundedDateTime = DateAdd("s", -1, dRoundedDateTime)
    
        'return the flag set to on
         bSecondRoundedToUpper = True
      Else
        'return the flag set to off
         bSecondRoundedToUpper = False
      End If
    Else
      'check if the rounded date is before the original
      If dRoundedDateTime < dOriginalDate Then
         'add one second (by deducting it! - bug in VB?)
         dRoundedDateTime = DateAdd("s", -1, dRoundedDateTime)
    
         'return the flag set to on
         bSecondRoundedToUpper = True
      Else
         'return the flag set to off
         bSecondRoundedToUpper = False
      End If
    
    End If
    
    'milliseconds are the number of ms in one day by the difference of the rounded and the orignal date
    GetMilliseconds = 86400000 * Abs(dOriginalDate - dRoundedDateTime)
    
    Exit Function

[szlamany]

My second code example showed how to get data from a remote/linked server.

Also - you should really consider loading this data from VB into a staging table and then having a SPROC work it up for placement into your production table.

[si_the_geek]

If you were using purely SQL (ie: an Insert with Select) the milliseconds wouldn't be an issue, as the actual values would be passed (rather than text which represents them).

Within VB it is a problem because you need to convert the value to text, and VB functions like Format don't support milliseconds (but they are stored by the Date data type).

This isn't really relevant, but sysGetTime and GetMilliseconds are more complex than needed, this does the equivalent:

Public Function sysGetTimeWithMS(ByVal dDate As Date, Optional strFormat As String = "mm/dd/yyyy Hh:Nn:Ss")

Const cSecsPerDay = 86400
Dim lMilliseconds As Long

  lMilliseconds = ((dDate * cSecsPerDay) - Int(dDate * cSecsPerDay)) * 1000
    '(if enough to make seconds round up, take 1s away)
  If lMilliseconds > 500 Then dDate = DateAdd("s", -1, dDate)
  
  sysGetTimeWithMS = Format(dDate, strFormat) & ":" & Format(lMilliseconds, "000")

End Function

The easier and quicker way to get the formatted text is to let SQL Server format it for you by using the Convert function (I used a Case as I'm not sure how it deals with Null input):

CASE
   WHEN (Creation_Date IS NULL) THEN Null
   ELSE Convert(Char(23), Creation_Date, 121)
END as Creation_Date_Text

(The parameter 121 means to use the format "ODBC canonical (with milliseconds, and century)", ie: yyyy-mm-dd hh:mi:ss.mmm)

As I said at the start of this post, you only need to do this sort of thing because you are using a VB program.

What can I do if I assume that I have linked server?

You can replace your entire VB program with a Stored Procedure, which would give the kind of benefits I mentioned in my last post.

The SP could be just two SQL statements (an Insert/Select and an Update, both based on the Select statement you had before this post), or something more complex/safe/efficient like szlamany posted.

[lucia]

My second code example showed how to get data from a remote/linked server..

in your second code example , PostTbl is the destination table, right?

Also - you should really consider loading this data from VB into a staging table and then having a SPROC work it up for placement into your production table.

I want to but I have no clue what SPROC is? I have to learn from the beginning.

Thank you again.

[szlamany]

STORED PROCEDURES are simply "groups" of queries that are put into a "named" space on the server. You execute them from a query window with EXEC MYSPROC, for example. From VB you use a slightly different syntax - you don't use a RECORDSET object you use a COMMAND object.

But all that is secondary to creating the queries that will process your file. The SPROC is just how you package those queries onto the server...

Ok - here's my second post - broken down a bit and explained. I'll explain each section under the code posting.

Declare @PostTbl Table (MasId int, RateSeq int, DistSeq int, PayPeriod varchar(7)
			,OffName varchar(100), TownId int, EntryDate datetime, DateWorked datetime
			,HoursPaid1 money, RatePaid1 money, HoursXRate1 money, AdminFee1 money, MiscFee1 money
			,Total money, InvoiceNum varchar(50), CustomerId varchar(50), VendorName varchar(100), PersonnelId varchar(10))

@PostTbl is a TABLE VARIABLE. TABLE VARIABLES can only really be created in a SPROC.

Your staging table can be a TABLE VARIABLE or a TEMP TABLE. If you are going to be "inserting" one row at a time from VB into the staging table then you need to make in a TEMP TABLE. If you can use linked servers - then you can use a TABLE VARIABLE. TABLE VARIABLES perform poorly with many rows - thousands - so if you have this situation then use a TABLE VARIABLE anyway.

And remember the whole purpose of the staging table is to get the "raw" data into the server - then you manipulate it. So the staging table really just closely matches the original data layout.

Insert into @PostTbl 
	select 0,0,0,''
		,RTrim(RTrim(Rank)+' '+RTrim(FirstName)+' '+RTrim(MiddleName)+' ' 
			+RTrim(LastName)+' '+RTrim(NameSuf))
		,cast(TownID as int)
		,CAST(EntryDate as datetime)
		,CAST(DateWorked as datetime)
		,CAST(HoursPaid1 as money)
		,Round(CAST(RatePaid1 as money),2)
		,CAST(HoursXRate1 as money)
		,CAST(AdminFee1 as money)
		,CAST(MiscFee1 as money)
		--If total is wrong, use SubTotal1
		,CAST(Total as money)
		,RTrim(IsNull(InvoiceNum,''))
		,CustomerID
		,RTrim(VendorName)
		,PersonnelId
	from AMSDDF1...PaidDetailTransactAT as PD left outer join AMSDDF1...PersonnelFileInfoAT AS PF on PF.ID=PD.PersonnelId left outer join AMSDDF1...VendorDetailCodesAT AS VC on VC.VendorId=PD.CustomerID
	--from openquery(amsddf1,'Select * from PaidDetailTransactAT as PD left outer join PersonnelFileInfoAT AS PF on PF.ID=PD.PersonnelId left outer join VendorDetailCodesAT AS VC on VC.VendorId=PD.CustomerID')
	Where EntryDate between @Start_Date and @End_Date and CustomerId not between '1000' and '1074' 
								and CustomerId not between '1076' and '2000'

The above INSERT/SELECT is actually populating my staging table. Since we are using a LINKED SERVER here it's getting the data from that SELECT. We are manipulating the data a small bit here - CAST'ing as money - fixing dates - trimming stuff.

If you can't use a LINKED SERVER like this them you can either do a BULK INSERT (from a .TXT file) or load row by row into the TEMP TABLE with INSERT's from VB (without processing the data - just raw loads).

Update @PostTbl Set MasId=TownId, PayPeriod=@Current_Pay_Period

Update @PostTbl Set RateSeq=IsNull(PR.RateSeq,0)
	From @PostTbl PT
	Left Join PayRate_T PR on PR.MasId=PT.MasId and PR.RateCd in ('PPD','PPD2','PLD') and PR.Amount=PT.RatePaid1
			and (IsNull(RatePPE,'')='' or RatePPE>=@Current_Pay_Period)
	Where PT.MasId<>0

Update @PostTbl Set DistSeq=Isnull(PD.DistSeq,0)
	From @PostTbl PT
	Left Join PayDistribution_T PD on PD.MasId=PT.MasId and PD.RateSeq=PT.RateSeq and PD.DistPerc<>0
			and (IsNull(DistPPE,'')='' or DistPPE>=@Current_Pay_Period)
	Where PT.MasId<>0 and PT.RateSeq<>0

The above is an example of how we cleanup the staging table a bit. We get some key values of other tables - move some fields around.

This would be where you did all your manipulation.

If Exists(Select * From @PostTbl Where TownId=0 or RateSeq=0 or DistSeq=0)
Begin
	Select   OffName+' ('+cast(cast(TownID as int) as varchar(10))+')'		"~wide~Name"
		,TownId									"~form~frmPayroll/Payee"
		,CONVERT(varchar(10),EntryDate,101)					"~wide~Entry Date"
		,CONVERT(varchar(10),DateWorked,101)					"~wide~Date Work"
		,RatePaid1						">Rate Paid"
		,InvoiceNum								"~wide~Invoice"
		,CustomerID								"Vendor ID"
		,VendorName								"~wide~Vendor Name"
		,Case When TownId=0 Then '** Missing Town Id - Cannot Process **'
			When RateSeq=0 Then '** Cannot Find Rate with this Amount **'
			When DistSeq=0 Then '** Cannot Find Default Distribution **'
						Else '' End				"~wide~Error Message"
	From @PostTbl Where TownId=0 or RateSeq=0 or DistSeq=0
	Return
End

The above is a little error check we put in - looks for rows with missing key fields - no id, etc. If it finds them the SPROC returns a recordset of the error rows.

Begin Tran

Insert into PayPoliceTrans_T Select *, GetDate() From @PostTbl

Insert into PayTimeCard_T Select MasId,RateSeq,DistSeq,PayPeriod,DateWorked,Sum(HoursPaid1),'',GetDate() From @PostTbl
				Group by MasId,RateSeq,DistSeq,PayPeriod,DateWorked
.
.
.

The above is the final load of the staging data into two production tables we have in our system.

Basically we grabbed police officer detail work info from the cop's pay detail system and loaded it into our payroll system.

[lucia]

If you were using purely SQL (ie: an Insert with Select) the milliseconds wouldn't be an issue, as the actual values would be passed (rather than text which represents them).

Within VB it is a problem because you need to convert the value to text, and VB functions like Format don't support milliseconds (but they are stored by the Date data type).

This isn't really relevant, but sysGetTime and GetMilliseconds are more complex than needed, this does the equivalent:

Public Function sysGetTimeWithMS(ByVal dDate As Date, Optional strFormat As String = "mm/dd/yyyy Hh:Nn:Ss")

Const cSecsPerDay = 86400
Dim lMilliseconds As Long

  lMilliseconds = ((dDate * cSecsPerDay) - Int(dDate * cSecsPerDay)) * 1000
    '(if enough to make seconds round up, take 1s away)
  If lMilliseconds > 500 Then dDate = DateAdd("s", -1, dDate)
  
  sysGetTimeWithMS = Format(dDate, strFormat) & ":" & Format(lMilliseconds, "000")

End Function

Thanks,I am going to use this before I get rid of the vb part.

The SP could be just two SQL statements (an Insert/Select and an Update, both based on the Select statement you had before this post), or something more complex/safe/efficient like szlamany posted.

In my select statement I just select all the data I want to import. But I need check destination table to see if they are already there. (the programm suppose to run everyday, so we get some new records with many old records)

So, how do I insert in a group and make sure the records are not duplicated(or cause error because of dups)?

Thanks again.

[szlamany]

Like I said you put all the data into the staging table.

Then you do something like this

Insert into FinalTable
   Select * From StagingTable ST
   Left Join FinalTable FT on FT.PriKeyCol=ST.PriKeyCol
   Where FT.PriKeyCol is null

Notice how this will insert into your production table with a SELECT from your staging table.

Most important though is that it "joins" the production table and only does rows where the "join" found nothing (FT.PriKeyCol is null).

You could also DELETE rows from the staging table that have primary keys that exist in the final production table.

You see - if you forget this "row-by-row" iterative processing that you are using now and instead bulk load and bulk clean up - then you are using SQL properly. SQL is meant to be used with "set-based" logic. Load whole table - process all rows in one statement. Update all rows in a table - insert all rows. Delete some rows...

It's not hard and there are lots of us here to help you out!

[lucia]

It's not hard and there are lots of us here to help you out!

You encouraged me! Thanks a M.
Now I understand more and I am going to try your way by first getting the stagging table.(I have more than 70 thousands records in a table, is it good to use stagging table?)

This is the code I get original data and build the recordset in Vb.

    strSQL = "SELECT s.Route_Num,  "
    strSQL = strSQL & "CASE WHEN (RX.CT_Route Is Not Null) Then RX.CT_Route "
    strSQL = strSQL & "ELSE s.route_Num END as RouteID, "
    strSQL = strSQL & "Convert(Char(23),s.StopDate,121) AS StopDT, s.Stop_Num, Convert(Char(23), s.Start_DateTime,121) AS StartDate, "
    strSQL = strSQL & "s.End_DateTime, s.GPS_DateTime, s.GPS_LAT, s.GPS_LONG, GPS_ALT, "
    strSQL = strSQL & "s.GPS_Speed,s.GPS_Direction, s.Last_StopTime, s.AreaID, "
    strSQL = strSQL & "s.Begin_Cust_DT, s.End_Cust_DT "
    strSQL = strSQL & "FROM Stops s "
    strSQL = strSQL & "LEFT JOIN Safescan_Route_Xref RX ON (s.xCDC_Term = RX.xCDC_Term AND s.Route_num = RX.SS_Route)"

So, the stagging table should be the same data as this recordset?

[si_the_geek]

Thanks,I am going to use this before I get rid of the vb part.

It would be a bit quicker to use the Convert instead, but if you use that you should change back to just the field when switching away from VB.

In my select statement I just select all the data I want to import. But I need check destination table to see if they are already there. (the programm suppose to run everyday, so we get some new records with many old records)

So, how do I insert in a group and make sure the records are not duplicated(or cause error because of dups)?

While szlamany's answer is better than what I'm about to write, I think this will help you to understand it, as it is a bit closer to what you have already.

You add a condition to the Where clause that does the equivalent of your "Check to see if record already exist."

To find records to Update, it would be similar to this (but more complex syntax for specifying the other table!):

WHERE ItemID IN ( SELECT ItemID FROM TableInOtherDatabase )

..and to find records to Insert, simply change IN to NOT IN.

In szlamany's example, this work is done by a combination of the Left Join and Where

[lucia]

It would be a bit quicker to use the Convert instead, but if you use that you should change back to just the field when switching away from VB.

Thank you Si.

What do you mean by "you should change back to just the field when switching away from VB."?

[lucia]

Now I create my varible table like this

     strSQL = "Declare @StopTbl Table "
     strSQL = strSQL & "(Route_Num varchar(3), StopDate datetime,  Stop_Num varchar(10), "
     strSQL = strSQL & "End_DateTime datetime, GPS_DateTime datetime, GPS_LAT numeric, "
     strSQL = strSQL & "GPS_LONG numeric, GPS_ALT numeric, GPS_Speed numeric, GPS_Direction numeric, "
     strSQL = strSQL & "Last_StopTime datetime, AreaID varchar(5), Begin_Cust_DT datetime, End_Cust_DT datetime "

Then I need to get data from source table and fill to this stagging table, right?
question, i use conn to execute recordset. what do I use to excute above code? I saw your post said "TABLE VARIABLES can only really be created in a SPROC. " I don't get this? can you explain a little? Thanks.

[si_the_geek]

That is for use within a Stored Procedure - which is inside SQL Server itself rather than in VB.

You create them using whatever programs you have for SQL Server (possibly Management Studio or Enterprise Manager or Query Analyser).

What do you mean by "you should change back to just the field when switching away from VB."?

If you are using just SQL (an Insert/Select or Update), the value is passed directly and in full, so you don't need to use Convert to get the text version of it - just using the fieldname by itself would work, and would be quicker.

[lucia]

That is for use within a Stored Procedure - which is inside SQL Server itself rather than in VB.

You create them using whatever programs you have for SQL Server (possibly Management Studio or Enterprise Manager or Query Analyser).

If you are using just SQL (an Insert/Select or Update), the value is passed directly and in full, so you don't need to use Convert to get the text version of it - just using the fieldname by itself would work, and would be quicker.

Ok I got it. Thanks

[szlamany]

If you were to change this

strSQL = "Declare @StopTbl Table "

to this

strSQL = "Create Table #StopTbl "

you would be creating a temporary table. They persist for "your connection" as long as that connection is held open.

Then you can load your data into that #StopTbl.

You could also just create a real table in your database - and just make sure it's empty (no rows) before your import operation.

But you first need to step back and consider if you are going to use a stored procedure to do all this or continue to execute sql insert/update/select statements from VB.

Making that decisions will dictate some of your other choices!

Please tell use what query tool you use - QUERY ANALYZER - MANAGEMENT STUDIO???

[lucia]

But you first need to step back and consider if you are going to use a stored procedure to do all this or continue to execute sql insert/update/select statements from VB.

Making that decisions will dictate some of your other choices!

Please tell use what query tool you use - QUERY ANALYZER - MANAGEMENT STUDIO???

Thank you.
I am using Query Analyzer.

I need to build a app for other users to import data not just import data by myself. If I use stored procedure, then I will excute these commands in Query Analyzer, right? I think It will be nice if I find a way to import data by using select/insert from Vb (get rid of the loop).

[szlamany]

Nope - the testing occurs in QA - but when you are ready to release - it becomes part of your VB application. We create the SPROC at the end of all this - and then the SPROC is executed from VB.

Do you want to try and get the data into a staging table?

If so I'm going to have a series of questions that we need to resolve before we get much further.

[lucia]

Do you want to try and get the data into a staging table?

If so I'm going to have a series of questions that we need to resolve before we get much further.

Yes. I actually run the code for staging table at QA but I don't where the table physically located.

[szlamany]

Do you mean that you don't know where the "source table" is located" The table you are getting the data from?

Show me what you have in QA now - so we can build on it.

[lucia]

Do you mean that you don't know where the "source table" is located" The table you are getting the data from?

Show me what you have in QA now - so we can build on it.

No, I mean the staging table.

 declare @tmpSSStops "
Route_Num varchar(5), StopDate datetime,  Stop_Num varchar(10), 
Start_DateTime datetime, End_DateTime datetime, GPS_DateTime datetime, GPS_LAT numeric, 
GPS_LONG numeric, GPS_ALT numeric, GPS_Speed numeric, GPS_Direction numeric,
Last_StopTime datetime, AreaID varchar(5), Begin_Cust_DT datetime, End_Cust_DT datetime

[szlamany]

Ok - how are we going to get the data into the staging table?

Where is it?

What is the query you use in VB to get the data - could you show that to me?

Will it be located someplace else in the future - I don't want to create a concept that only works during development.

[lucia]

What is the query you use in VB to get the data - could you show that to me?

I got data to temp table in vb like this. Thanks.

    strSQL = "INSERT tmpSSCanStops "
    strSQL = strSQL & "SELECT "
    strSQL = strSQL & "CASE WHEN (RX.CT_Route Is Not Null) Then RX.CT_Route "
    strSQL = strSQL & "ELSE s.route_Num END, "
    strSQL = strSQL & "s.StopDate, s.Stop_Num, Start_DateTime, s.End_DateTime, "
    strSQL = strSQL & " s.GPS_DateTime, s.GPS_LAT, s.GPS_LONG, GPS_ALT, "
    strSQL = strSQL & "s.GPS_Speed,s.GPS_Direction, s.Last_StopTime, s.AreaID, "
    strSQL = strSQL & "s.Begin_Cust_DT, s.End_Cust_DT "
    strSQL = strSQL & "FROM " & gsSScanToCTrakSQL & ".sscantoctrak.dbo.Stops s "
    strSQL = strSQL & "LEFT JOIN " & gsSScanToCTrakSQL & ".sscantoctrak.dbo.Safescan_Route_Xref RX ON (s.xCDC_Term = RX.xCDC_Term AND s.Route_num = RX.SS_Route)"
    ConnTracking.Execute strSQL

[szlamany]

Sorry - got busy today

So basically you are going to put all this into a query window (I'm not sure what's up with that " quote on your declare line - must have been a typo or copy/paste error - right?)

declare @tmpSSStops "
Table (Route_Num varchar(5), StopDate datetime,  Stop_Num varchar(10), 
Start_DateTime datetime, End_DateTime datetime, GPS_DateTime datetime, GPS_LAT numeric, 
GPS_LONG numeric, GPS_ALT numeric, GPS_Speed numeric, GPS_Direction numeric,
Last_StopTime datetime, AreaID varchar(5), Begin_Cust_DT datetime, End_Cust_DT datetime)

Insert into @tmpSSStops
SELECT CASE WHEN (RX.CT_Route Is Not Null) Then RX.CT_Route
    ELSE s.route_Num END,
    s.StopDate, s.Stop_Num, Start_DateTime, s.End_DateTime,
     s.GPS_DateTime, s.GPS_LAT, s.GPS_LONG, GPS_ALT,
    s.GPS_Speed,s.GPS_Direction, s.Last_StopTime, s.AreaID,
    s.Begin_Cust_DT, s.End_Cust_DT
    FROM ???.sscantoctrak.dbo.Stops s 
    LEFT JOIN ???.sscantoctrak.dbo.Safescan

You need to fix up those ???'s to be the server name - right?

See how quickly this runs on a real amount of data - should be pretty instant.

Then we start doing the manipulation required to get the data cleaned and prep'd for production.

[lucia]

Szlamany, I ran the code in QA and got error:SQL Server does not exist or access denied.(I did link CODENCTSQL1 server to server 21)

declare @tmpSSStops 
Table (Route_Num varchar(5), StopDate datetime,  Stop_Num varchar(10), 
Start_DateTime datetime, End_DateTime datetime NOT NULL, GPS_DateTime datetime NOT NULL, GPS_LAT numeric, 
GPS_LONG numeric, GPS_ALT numeric, GPS_Speed numeric, GPS_Direction numeric,
Last_StopTime datetime, AreaID varchar(5), Begin_Cust_DT datetime, End_Cust_DT datetime
Primary Key(Route_Num, StopDate, Stop_Num, Start_DateTime))

Insert into @tmpSSStops
SELECT CASE WHEN (RX.CT_Route Is Not Null) Then RX.CT_Route
ELSE s.route_Num END,
s.StopDate, s.Stop_Num, s.Start_DateTime, s.End_DateTime,
s.GPS_DateTime, s.GPS_LAT, s.GPS_LONG, s.GPS_ALT,
s.GPS_Speed,s.GPS_Direction, s.Last_StopTime, s.AreaID,
s.Begin_Cust_DT, s.End_Cust_DT
FROM uas_broadview21.sscantoctrak.dbo.Stops s 
LEFT JOIN CODENCTSQL1.sscantoctrak.dbo.Safescan_Route_Xref RX ON (s.xCDC_Term = RX.xCDC_Term AND s.Route_num = RX.SS_Route)

What could be the problem? Thanks.