Database

14-Jan-02 6:20

we have an app that does a whole bunch of calcs and end up writing upwards of 200,000 records into a sql7 database via direct odbc calls (sqlexecdirect) ... the app takes maybe 15 - 25 minutes to run which isn't so bad considering but i want to know if it can be sped up ... after profiling it i find that 89% of the time is spent inside sqlexecdirect()

we don't use stored procs and the records are inserted using a simple INSERT INTO blah (xxx,xxx,xxx,xxx) VALUES (xxx,xxx,xxx,xxx) statement ... the number of 'parameters' for the queries is maybe 20 or 25

my understanding of stored procs is that they compile once and execute quicker but will this make much difference given the simple nature of the statements and the number of parameters i would have to be working with?

thoughts would be appreciated

---
"every year we invent better idiot proof systems and every year they invent better idiots ... and the linux zealots still aren't being sterilized"

Konstantin Vasserman14-Jan-02 7:18

Konstantin Vasserman

14-Jan-02 7:18

One thing that can speed it up a lot (in my experience) is to batch multiple insert statements together. For example, I would append all your insert statements to a string and when it reaches certain length I would execute it, then start all over again. The limit of the query statement is pretty big, I think, it is around 16K, so you can safely build strings up to at least that limit.

Someone told me that wrapping your inserts in transactions speed things up, but from my personal experience I did not see any improvement.

I hope this helps.

Daniel Turini14-Jan-02 8:35

Daniel Turini

14-Jan-02 8:35

The fastest way of inserting records on a table is using BCP (Bulk Insert).
200.000 records takes about 3 or 4 seconds on my machine.

Other good option is using DTS if you need doing calculations on the data.

Crivo
Automated Credit Assessment

Carlos Antollini14-Jan-02 9:13

14-Jan-02 9:13

I agree with Daniel...
You must to use the BCP utility....

Regards Big Grin | :-D

Carlos Antollini.
Sonork ID 100.10529 cantollini

Konstantin Vasserman14-Jan-02 9:17

Konstantin Vasserman

14-Jan-02 9:17

Can you distribute BCP with your application?

Beside that, what I proposed to do is essentially the same thing that BCP does (batch inserting) only without a hassle of writing it to a file and then using external app to import it. What do you think?

Carlos Antollini14-Jan-02 9:45

14-Jan-02 9:45

Yes you can distribute the BCP utility. But I remember somthing better. The command BULK INSERT.
You can write a stored procedure using the command BULK INSERT in It. This command, You only need to inform the Path of the file that has the data, and the Field and record separator, and ready....
The good news is that, the BULK INSERT is executed in the Server, in that case all the resources are in the server.... Big Grin | :-D

Regards....

Carlos Antollini.
Sonork ID 100.10529 cantollini

Konstantin Vasserman14-Jan-02 9:51

Konstantin Vasserman

14-Jan-02 9:51

This indeed looks like a better choice providing they can upload or access their file from the server. Good thinking. Smile | :)

Carlos Antollini14-Jan-02 9:54

14-Jan-02 9:54

Thanks... Always rememmber, that the path that you must to inform to the BULK INSERT is the Path from the Server...

Regards...;)

Carlos Antollini.
Sonork ID 100.10529 cantollini

l a u r e n14-Jan-02 9:17

14-Jan-02 9:17

given that the prog calculates the data to be inserted would you suggest i write all data to a file and then run a bcp to bulk insert?

i think writing it all to a memory buffer might be a bit resource gobbling no?

---
"every year we invent better idiot proof systems and every year they invent better idiots ... and the linux zealots still aren't being sterilized"

Carlos Antollini14-Jan-02 9:49

14-Jan-02 9:49

Lauren: Read my previus answer, where I said that I think that is better to use the command BULK INSERT....

Regards...

Carlos Antollini.
Sonork ID 100.10529 cantollini

l a u r e n14-Jan-02 20:19

14-Jan-02 20:19

carlos i did read your post
my problem is that the data to be inserted are created one by one from calcs in the prog ... i would have to buffer them somewhere until ready to do a bulk insert no?

my issue was where does one buffer them?

thanks Smile | :)

---
"every year we invent better idiot proof systems and every year they invent better idiots ... and the linux zealots still aren't being sterilized"

Carlos Antollini15-Jan-02 2:41

15-Jan-02 2:41

lauren wrote:
my problem is that the data to be inserted are created one by one from calcs in the prog

In this case the only that you can make is insert record by record, using the INSERT SQL Statement, Is better to use a Stored Procedure...
Bt if you have thousand of records for insert, may be you can make a file, an send it to the server, for make a BULK INSERT, because the BULK INSERT is for insert data Files into the database....

Regards!!!

Carlos Antollini.
Sonork ID 100.10529 cantollini

AndyG14-Jan-02 15:38

AndyG

14-Jan-02 15:38

If you have any indexes on the table, drop them and recreate them after the insert. Make sure you aren't logging everything (in SQL Server) if you don't need to.

Andy Gaskell, MCSD

l a u r e n14-Jan-02 20:21

14-Jan-02 20:21

hi andy

no transaction loging is used on this piece and the only index is the primary key for the table

---
"every year we invent better idiot proof systems and every year they invent better idiots ... and the linux zealots still aren't being sterilized"

Nick Parker16-Jan-02 3:42

Nick Parker

16-Jan-02 3:42

One note:
Stored Procedures are not compiled, they are simply stored directly on the database, not as a SQL statement within your application. The great flexibilty that is gained through Stored Procedures is that if something in your SQL statement needs to be changed, you don't have to recompile your project as long as you don't change the name of the Stored Procedure. You also can expose yourself to other process methods that a stored procedure can do. Wink | ;)

Nick Parker