Loading Data To SQL Table

[Bill]

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.

[Tonny H]

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.
>

[DragonFly]

"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

[Paul Clapham]

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.
>

[Andrew Bushmakin]

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
>

[simon]

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.
>