slow process insert data to sql database

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

hello my beloved senior...

i'm using VB. i have about 6702 record of employee.
the problem is my application have to process the data and stored it into another table but it takes too much time.

i'm using:
1. progress bar to see the progress.
2. "do while", i'm calling the procedure to insert the data at the end of "do..while"

So i execute the proc equals to the amount of the employee.

here some of my code:

VB

Me.bs.MoveFirst()
            ' loop-------------------
            ProgressBar1.Maximum = Me.bindingsource.Count
            Do While x <= Me.dataset.tbdatapegawai.Count
                My.Application.DoEvents()
                
                Progress_bar_percent = x / CInt(Me.bindingsource.Count) * 100
                Me.persenLabel.Text = Progress_bar_percent.ToString & "%"

                'this is only example. the data that has been calculate and ready to be stored
                var_1_pajak = 12.000
                var_2_bruto = 5.000
                var_3_potongan = 2.000
                var_4_gasih = 1.500
                var_5_potjabatan = 1.725
                var_6_iuranpensiun = 900

                'saving one data using stored proc (just insert data)
                Save_data()

                ProgressBar1.Value = x
                x = x + 1
                Me.bindingsource.MoveNext()
            Loop

it takes about 18 minutes to finish all the data.
is there a way to make it faster?
thanks for your time.
sorry about my english.

regard
Samuel T

Posted 8-Aug-11 4:12am

Samuel_Tandibua

Updated 8-Aug-11 4:14am

v2

Add a Solution

Comments

Herman<T>.Instance 8-Aug-11 10:18am

what data from table 1 is copied to table 2? Should this be done rowwise or can this be done tablewise with 1 query or stored procedure with conditions?

Samuel_Tandibua 8-Aug-11 10:39am

the data from table 1 using some calculation for each field into some result and then it's stored into table 2.

thanks for reading my problems

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Herman<T>.Instance · Accepted Answer · 2011-08-08T04:26:00

Solution 1

you can do an insert in a table based on a select from another table. The select can contain calucaltions. In thsi case your process can be done in 1 query in a couple of seconds (7k records is nothing for a database!)

For an example see: http://www.sqlteam.com/article/using-select-to-insert-records[^]

Posted 8-Aug-11 4:26am

Herman<T>.Instance

Comments

Samuel_Tandibua 8-Aug-11 10:36am

i think it's not gonna work.
var_1_pajak = 12.000
var_2_bruto = 5.000
var_3_potongan = 2.000
var_4_gasih = 1.500
var_5_potjabatan = 1.725
var_6_iuranpensiun = 900
those variable are from calculation after that it's being stored into another table. can sql do some complex calculation? if there any article i would appreciate it.
thanks for reading my problem.

Herman<T>.Instance 8-Aug-11 10:52am

Computed columns http://msdn.microsoft.com/en-us/library/ms191250.aspx

Samuel_Tandibua 10-Aug-11 23:00pm

Thank you very much.
it pretty much solved my problem.

sorry for the trouble.

regard
samuel t