• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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.

Regards

J Gonzaga ScriptSQL.txt ScriptSQL.txt
0
JGonzaga
Asked:
JGonzaga
2 Solutions
 
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.
0
 
daveamourCommented:
Where is the data coming from - give me the big picture of your application
0
 
cquinnCommented:
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

0
 
JGonzagaAuthor Commented:
Daveamour

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.

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

Regards.

0
 
CodeCruiserCommented:
Try using the ConnectionTimeOut and CommandTimeOut properties.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now