Alpha Video Training
Results 1 to 10 of 10

Thread: <tbl>.append() vs. MS SQL Server's Import

  1. #1
    Member
    Real Name
    Darryl Harvey
    Join Date
    Jan 2011
    Posts
    36

    Default <tbl>.append() vs. MS SQL Server's Import

    Does anyone have any suggestions why the <tbl>.append() would take well over 5 days plus to load 1.2 million rows into a MS SQL Server table?
    MS SQL Server's Import feature can load the same exact data from a text file in less than a minute.
    The data being loaded contains 11 columns with a combinations of types character, numeric, and bit.

  2. #2
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,022

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    Does anyone have any suggestions why the <tbl>.append() would take well over 5 days plus to load 1.2 million rows into a MS SQL Server table?
    MS SQL Server's Import feature can load the same exact data from a text file in less than a minute.
    The data being loaded contains 11 columns with a combinations of types character, numeric, and bit.
    it would seem ms sql would be faster(more direct), but 5 days vs 1 minute? Guessing something in the alpha link to sql has to be a bit off. A lot off, it would not happen at all.

    Question, are you importing text and then sending it to ms sql?

    Others may be better to answer this, but I think more information is needed for a definitive answer.


    .
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  3. #3
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,606

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    I agree with Dave. Please explain to us the method you're using. Proper procedures should exceed 20,000 records per second.
    -Steve


  4. #4
    Member
    Real Name
    Darryl Harvey
    Join Date
    Jan 2011
    Posts
    36

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    I am loading data to an Active-Link (MS SQL Server) table in Alpha five from a Foxpro 2.6 DOS table using the <tbl>.append() process.

  5. #5
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,022

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    Darryl,

    I am loading data to an Active-Link (MS SQL Server) table in Alpha five from a Foxpro 2.6 DOS table using the <tbl>.append() process.
    Ok! You explained what you are doing(kinda guessed that part), but not how you are doing it. I am NO expert on sql yet, but I would add my connection string and exact procedure and code used on this thread so really knowledgeable people can help. Also, is this a web process or a desktop? I would post this in the appropriate section for what you are working with(v10 web - desktop or v9 or...) so MORE people will see it.

    Somewhere in the process, there is a problem( you figured that ouit). You did get it to work though.


    .
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  6. #6
    Member
    Real Name
    Darryl Harvey
    Join Date
    Jan 2011
    Posts
    36

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    Basically, I am new to Alpha Five, I have been designing and developing business applications for 25 plus years, using Foxpro, .Net, Oracle, and SQL Server. Since I am new to Alpha Five and I believed this to be a new developer type question, hence the reason why I posted here. With this program I am just trying to upload 1.2 million rows from a Foxpr0 2.6 table into an Alpha Active-Link table.




    ' Connection string to MS SQL Server
    {A5API='SQLServer',A5DBVersion=2008,Server='(local)',A5ANSINullPadWarn=Default,UserName='user_1',Password='*****',Database='Alpha'}


    dim tbl_old as p
    dim tbl_new as p

    ' Open Active-Link table (MS SQL Server)
    tbl_new = table.open( "MSTHIN", FILE_RW_SHARED )

    ' Open Foxpro 2.6 table
    append.t_db = "K:\CPUMSTDAT\MSTHIN.DBF"

    ' Init append statement
    append.m_key = ""
    append.t_key = ""
    append.m_filter = ""
    append.t_filter = ""
    append.type = "All"
    append.m_count = 11
    append.m_field1 = "TICKET"
    append.m_exp1 = "@MSTHIN->TICKET"
    append.m_field2 = "SALKEY"
    append.m_exp2 = "@MSTHIN->SALKEY"
    append.m_field3 = "IND_CASH"
    append.m_exp3 = "@MSTHIN->IND_CASH"
    append.m_field4 = "IND_CHG"
    append.m_exp4 = "@MSTHIN->IND_CHG"
    append.m_field5 = "IND_QTY"
    append.m_exp5 = "@MSTHIN->IND_QTY"
    append.m_field6 = "IND_TAX_L"
    append.m_exp6 = "@MSTHIN->IND_TAX_L"
    append.m_field7 = "IND_TAX"
    append.m_exp7 = "@MSTHIN->IND_TAX"
    append.m_field8 = "IND_TOT"
    append.m_exp8 = "@MSTHIN->IND_TOT"
    append.m_field9 = "IND_DIFF"
    append.m_exp9 = "@MSTHIN->IND_DIFF"
    append.m_field10 = "IND_SEQ"
    append.m_exp10 = "@MSTHIN->IND_SEQ"
    append.m_field11 = "IND_NOTES"
    append.m_exp11 = "@MSTHIN->IND_NOTES"
    append.t_count = 0

    ' Start appending records from Foxpro 2.6 to Alpha Active-Link table
    tbl_new.append()

    ' Close table
    tbl_new.close()

    ' Process complete

  7. #7
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,022

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    I only hope someone takes the time to look at your questions.

    To understand what I mean, look here:
    http://msgboard.alphasoftware.com/al...ad.php?t=92078

    I glance quickly through this section occassionally as others do.

    Your question is more appropriate/will get more people studying it if it is posed in the appropriate v10(or) and desktop(or) section.

    Just copy it and paste it there.

    You will get a lot better help than I can give you.

    .
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  8. #8
    Member
    Real Name
    Darryl Harvey
    Join Date
    Jan 2011
    Posts
    36

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    I really appreciate your reply, however, (and to to be too negative) I do not get too many replies in the other sections as well.

  9. #9
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,618

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    The number of records that you have is way above what most users experience.

    So you'll get a much lower response rate.

    For 1/10 th the number of records, I wouldn't use anything other than a native load.

    You could also use an insert with Foxpro to append the records and you'll find that it is considerably slower than the native load. A discreet load of record by record is always slower than the native load (SQL import in your words)

    Thank goodness there is a process to load natively.

    Does the SQL import require exclusive access to the table(s)?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #10
    Member
    Real Name
    Darryl Harvey
    Join Date
    Jan 2011
    Posts
    36

    Default Re: <tbl>.append() vs. MS SQL Server's Import

    I am not sure if the SQL import requires an exclusive access. That would not be an issue for me.
    However, your suggestion to use Foxpro to append the records may just be the way to go, as far as ease and speed. The SQL import is very fast, however, it does require many manual steps and changing of the data before the actual import.
    Thanks for your reply.

Similar Threads

  1. No columns showed in SQL Genie (SQL Server)
    By cmkivio in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 01-07-2011, 01:34 PM
  2. Win Server 2008 + SQL Server problem
    By jeffcogs in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 07-30-2010, 10:51 AM
  3. tbl.enter_begin(t)....tbl.enter_end(t)
    By enstorms in forum Alpha Five Version 9 - Desktop Applications
    Replies: 15
    Last Post: 09-21-2008, 03:47 PM
  4. <tbl>.Append() and Sorting records
    By turbojack in forum Alpha Five Version 8
    Replies: 2
    Last Post: 02-15-2008, 09:11 AM
  5. Cpy Flds in currnt tbl & post to other tbl
    By Danny Marx in forum Alpha Five Version 4
    Replies: 8
    Last Post: 08-24-2000, 10:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •