Loading Data To SQL Table


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Loading Data To SQL Table

  1. #1
    Bill Guest

    Loading Data To SQL Table


    I have a problem that I hope someone out there can help me. Here is the situation.

    I am using a VB6 program to update an SQL Server database from a tab delimited
    text file. The Sales History table (SOHIST) currently has 1,500,000+ records
    in it. The text file will average approximately 70,000 each month. The
    VB6 program needs to check for an existing record in the SOHIST table. If
    it finds a match it should change the fields in the table, if it doesn’t
    find a match it should add the text record to the table.

    Here’s the problem…. SPEED! Right now it takes 8 seconds per record to determine
    if there is a match using an SQL statement such as “Select * from SOHIST
    where OrderNum = ‘12345’. That computes to over 155 hours.

    Any suggestions (other than finding a new job) would be greatly appreciated.


  2. #2
    Tonny H Guest

    Re: Loading Data To SQL Table


    What I would do probably instead of parsing one and search database, I would
    have a table that you populate first with the OrderNum, and the text file,
    if you want to, as text field in SQL 7.
    Then schedule a stored procedure to run at certain time in a day. It can
    run many times depends on your need.
    Also index the OrderNum field! That would increase your speed
    After everything is done you can either truncate the table for the next process
    to run or set a variable like a DELFLAG as 1 (done) or 0 need to be pick
    up.

    good luck



    "Bill" <wdjacoby@ix.netcom.com> wrote:
    >
    >I have a problem that I hope someone out there can help me. Here is the

    situation.
    >
    >I am using a VB6 program to update an SQL Server database from a tab delimited
    >text file. The Sales History table (SOHIST) currently has 1,500,000+ records
    >in it. The text file will average approximately 70,000 each month. The
    >VB6 program needs to check for an existing record in the SOHIST table.

    If
    >it finds a match it should change the fields in the table, if it doesn’t
    >find a match it should add the text record to the table.
    >
    >Here’s the problem…. SPEED! Right now it takes 8 seconds per record to

    determine
    >if there is a match using an SQL statement such as “Select * from SOHIST
    >where OrderNum = ‘12345’. That computes to over 155 hours.
    >
    >Any suggestions (other than finding a new job) would be greatly appreciated.
    >



  3. #3
    DragonFly Guest

    Re: Loading Data To SQL Table


    "Bill" <wdjacoby@ix.netcom.com> wrote:

    >I am using a VB6 program to update an SQL Server database from a tab delimited
    >text file. The Sales History table (SOHIST) currently has 1,500,000+ records
    >in it. The text file will average approximately 70,000 each month. The
    >VB6 program needs to check for an existing record in the SOHIST table.

    If
    >it finds a match it should change the fields in the table, if it doesn’t
    >find a match it should add the text record to the table.
    >
    >Here’s the problem…. SPEED! Right now it takes 8 seconds per record to

    determine
    >if there is a match using an SQL statement such as “Select * from SOHIST
    >where OrderNum = ‘12345’.


    Try to do it that way:

    1. Import whole data set into blank table with the same structure as your
    SOHIST table (SOHIST_NEW)

    2. Do update SQL query like: UPDATE SOHIST SET .... FROM SOHIST_NEW WHERE
    SOHIST.OrderNum = SOHIST.OrderNum

    3. Do insert SQL query like: INSERT INTO SOHIST (SELECT * FROM SOHIST_NEW
    WHERE SOHIST.OrderNum<>SOHIST_NEW.OrderNUm)

    4. Delete everything from SOHIST_NEW

    You're all set )

    In my case everything is done on the server - there is no excess searching
    for data in row by row manner.

    >Any suggestions (other than finding a new job) would be greatly appreciated.


    Yeah, finding a new job is a good idea! ) But if you don't like it - try
    to read any of SQL books, it sure will help.

    Sincerely,
    DragonFly


  4. #4
    Paul Clapham Guest

    Re: Loading Data To SQL Table

    If it takes 8 seconds to find if there is a match to a simple query like
    that, then your problem is not to write this program, yet. Your problem is
    to fix the database response time. Maybe this is a tiresomely obvious
    question, but is there an index over the SOHIST table with OrderNum as its
    first key field? I'm not an SQL Server expert, but maybe someone out there
    who knows something about it can suggest whether 1.5M records is too large
    to expect to handle. (For comparison, I have an AS/400 file with 55M
    records and matching a record in it based on a key is essentially
    instantaneous.)

    Another alternative -- probably impractical at this point -- is to recast
    your sales history table as a data warehouse sort of table where you simply
    dump transactions at the end of the month, and have the reporting deal with
    combining records as necessary.

    PC2

    Bill <wdjacoby@ix.netcom.com> wrote in message
    news:39c394ff$1@news.devx.com...
    >
    > I have a problem that I hope someone out there can help me. Here is the

    situation.
    >
    > I am using a VB6 program to update an SQL Server database from a tab

    delimited
    > text file. The Sales History table (SOHIST) currently has 1,500,000+

    records
    > in it. The text file will average approximately 70,000 each month. The
    > VB6 program needs to check for an existing record in the SOHIST table. If
    > it finds a match it should change the fields in the table, if it doesn't
    > find a match it should add the text record to the table.
    >
    > Here's the problem.. SPEED! Right now it takes 8 seconds per record to

    determine
    > if there is a match using an SQL statement such as "Select * from SOHIST
    > where OrderNum = '12345'. That computes to over 155 hours.
    >
    > Any suggestions (other than finding a new job) would be greatly

    appreciated.
    >




  5. #5
    Andrew Bushmakin Guest

    Re: Loading Data To SQL Table


    DragonFly,

    Your INSERT statement will not work. Instead of just
    …(SELECT * FROM SOHIST_NEW WHERE SOHIST.OrderNum<>SOHIST_NEW.OrderNUm)

    must be

    (
    SELECT
    SOHIST_New.*
    FROM
    SOHIST_New
    WHERE
    SOHIST_New.OrderNum NOT IN
    (
    Select SOHIST.OrderNum From SOHIST
    );
    )

    Andrew Bushmakin
    *********************************


    "DragonFly" <ctpeko3a@email.com> wrote:
    >
    >"Bill" <wdjacoby@ix.netcom.com> wrote:
    >
    >>I am using a VB6 program to update an SQL Server database from a tab delimited
    >>text file. The Sales History table (SOHIST) currently has 1,500,000+ records
    >>in it. The text file will average approximately 70,000 each month. The
    >>VB6 program needs to check for an existing record in the SOHIST table.


    >If
    >>it finds a match it should change the fields in the table, if it doesn’t
    >>find a match it should add the text record to the table.
    >>
    >>Here’s the problem…. SPEED! Right now it takes 8 seconds per record to

    >determine
    >>if there is a match using an SQL statement such as “Select * from SOHIST
    >>where OrderNum = ‘12345’.

    >
    >Try to do it that way:
    >
    >1. Import whole data set into blank table with the same structure as your
    >SOHIST table (SOHIST_NEW)
    >
    >2. Do update SQL query like: UPDATE SOHIST SET .... FROM SOHIST_NEW WHERE
    >SOHIST.OrderNum = SOHIST.OrderNum
    >
    >3. Do insert SQL query like: INSERT INTO SOHIST (SELECT * FROM SOHIST_NEW
    >WHERE SOHIST.OrderNum<>SOHIST_NEW.OrderNUm)
    >
    >4. Delete everything from SOHIST_NEW
    >
    >You're all set )
    >
    >In my case everything is done on the server - there is no excess searching
    >for data in row by row manner.
    >
    >>Any suggestions (other than finding a new job) would be greatly appreciated.

    >
    >Yeah, finding a new job is a good idea! ) But if you don't like it - try
    >to read any of SQL books, it sure will help.
    >
    >Sincerely,
    >DragonFly
    >



  6. #6
    simon Guest

    Re: Loading Data To SQL Table


    Correct me if I am wrong. I think Data Transformation Service that comes
    with SQL Server can handle that easily and swiftly.

    Simon.


    "Bill" <wdjacoby@ix.netcom.com> wrote:
    >
    >I have a problem that I hope someone out there can help me. Here is the

    situation.
    >
    >I am using a VB6 program to update an SQL Server database from a tab delimited
    >text file. The Sales History table (SOHIST) currently has 1,500,000+ records
    >in it. The text file will average approximately 70,000 each month. The
    >VB6 program needs to check for an existing record in the SOHIST table.

    If
    >it finds a match it should change the fields in the table, if it doesn’t
    >find a match it should add the text record to the table.
    >
    >Here’s the problem…. SPEED! Right now it takes 8 seconds per record to

    determine
    >if there is a match using an SQL statement such as “Select * from SOHIST
    >where OrderNum = ‘12345’. That computes to over 155 hours.
    >
    >Any suggestions (other than finding a new job) would be greatly appreciated.
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center