How do I insert huge quantities of rows in a MySql table, using VB2008, without time out expired

I am developing a system in VB 2008 with MySql data base. I have some stored procedures witch execute queries that insert a huge quantity of registers in tables and sometimes, during debbuging time out expired occurs.

I ask for help in the Experts Exchange site and they told me to run routines with trouble in background and changing the time of time out in the connection string.

I´ve tried all the solutions they gave, but the time out expired remains and the expert who gave me the solutions told me that my problem could be in the queries.

In the attached file I put the SQL code of one of the queries where I have problems and the create statements for de tables that are in the SQL code.

The bigger table is tabitensentradasaida witch has 500000 rows.


J Gonzaga ScriptSQL.txt ScriptSQL.txt
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JGonzagaAuthor Commented:
I´m still waiting for a solution.

Maybe my question is not clear, so, if necessary I can add new comments, but i think that will be better if do this by answering questions.
Where is the data coming from - give me the big picture of your application
Just one point on the SQL script - you can make your select statement more efficient by taking out the Round(xxxxx,3) > 0 from the select criteria - the column is either greater than zero or it isn't - the ROUND function is superfluous.  Removing it means that the function is not called 500000 times!

WHERE (((tabitensentradasaida.IND_OPER)='0') AND (((tabitensentradasaida.QTD*tabitensentradasaida.FAT_CONV))>0) AND ((tabresumocfop.SelEstoque)=1) AND ((tabresumoitens.Sel_Estoque)=1));

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JGonzagaAuthor Commented:

The dates come from of the same database where the destination table is.

At first I only had the view w, but the acess was so slowly that I decided save tha dates in a table. My idea is that in the begining I will spent time in the insert, but, after that, the access to the dates in only one table will be first.

I will make as you sad. In this and in the other analogs situations.


Try using the ConnectionTimeOut and CommandTimeOut properties.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.