VB6 - Optimize multiple SQL executes.

Posted on 2008-11-18
Medium Priority
Last Modified: 2012-05-05

I have the code below which i know isnt that pretty, it uses simple strings to send commands to my SQLConnection using the execute method.

However the code below can be quite slow, is there anyone with an idea to optimize this code ?
For Each cVare In getArticleContainer
        If (cVare.isSelected Or Not onlySelected) And (Not cVare.adlibitum Or afregnAdlibitum) Then
            If cVare.isBetaling Then
                sqlStr = "insert into salg_betal (salg_text,salg_value,salg_id) "
                sqlStr = sqlStr & " values('" & FQuo(cVare.vare_navn) & "'," & getSQLDecimalVal(cVare.vare_price) & "," & sellThis & ")"
                frmFront.registerCnxn.execute (sqlStr)
                frmFront.fValues = "nuværende vare: " & cVare.getCopyStr
                sqlStr = "insert into salg_linie (salg_vare,salg_antal,salg_id,salg_navn) values(" & cVare.vare_id & "," & getSQLDecimalVal(Round(cVare.vare_antal, 2)) & "," & sellThis & ",'" & FQuo(cVare.vare_navn) & "')"
                found = frmFront.registerCnxn.execute("SET NOCOUNT ON " & sqlStr & " select SCOPE_IDENTITY() as mID SET NOCOUNT OFF").Fields("mID").value
                '' Tjek om der er en besked på denne vare når den sælges.
                Call checkForMessage(cVare.vare_id)
                '' RABAT
                If cVare.rabat_procent > 0 Then
                    frmFront.registerCnxn.execute "insert into rabat_salg (salg_linieID,rabat_id,rabat_procent,person_id) values (" & found & "," & cVare.rabat_id & "," & getSQLDecimalVal(cVare.rabat_procent) & "," & pers_id & ")"
                End If
                Call checkAddArticle(frmFront.monthID, cVare.vare_id)
                If cVare.isSammensat Then
                    For Each nVare In cVare.getArticleCollection
                        Call checkAddArticle(frmFront.monthID, nVare.vare_id)
                        sqlStr = "insert into salg_sammensat (salg_linieID,salg_vare,salg_pris,salg_momsID,salg_momsnavn,salg_moms,vare_type) "
                        sqlStr = sqlStr & " select " & found & "," & nVare.vare_id & "," & getSQLDecimalVal(nVare.vare_price) & ",moms_id,moms_navn,moms_sats from moms_db inner join t_vare on moms_id=vare_moms where vare_id=" & nVare.vare_id
                        frmFront.fValues = frmFront.fValues & vbNewLine & sqlStr
                        frmFront.registerCnxn.execute (sqlStr)
                    sqlStr = "insert into salg_sammensat (salg_linieID,salg_vare,salg_pris,salg_momsID,salg_momsnavn,salg_moms,vare_type) "
                    sqlStr = sqlStr & " select " & found & "," & cVare.vare_id & "," & getSQLDecimalVal(cVare.vare_price) & ",moms_id,moms_navn,moms_sats from moms_db inner join t_vare on vare_moms=moms_id where vare_id=" & cVare.vare_id
                    frmFront.fValues = frmFront.fValues & vbNewLine & sqlStr
                    frmFront.registerCnxn.execute (sqlStr)
                End If
            End If
        End If

Open in new window

Question by:mSchmidt

Accepted Solution

thiyaguk earned 2000 total points
ID: 22983422
1.You can completely Port this code to SQL Stored procedure.
2.try to Form multiple insert statements like
"insert into tablename values (blah,blah,blah);insert into tablename"

Construct this type of sql statement and try to run only once. Definitely this will solve the problem

Author Comment

ID: 22983506
How would you go about doing this a a Stored procedure.
I have n number of items in my ArticleContainer and each article may consist of multiple sub articles.
Sending this data as parameters seems impossible.
However it would ofcause be the best situation because it would limit data being sent to the server to the absolute minimum.

Additionally doing this as multiple insert statements joined would be possible for the inner loop however i use a value returned from a former execute and append this within

sqlStr = "insert into salg_linie (salg_vare,salg_antal,salg_id,salg_navn) values(" & cVare.vare_id & "," & getSQLDecimalVal(Round(cVare.vare_antal, 2)) & "," & sellThis & ",'" & FQuo(cVare.vare_navn) & "')"
found = frmFront.registerCnxn.execute("SET NOCOUNT ON " & sqlStr & " select SCOPE_IDENTITY() as mID SET NOCOUNT OFF").Fields("mID").value

I dont see how this would be possible ?

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question