Run stored procedure away from client
-
Hi,
I have a stored procedure that populates a number of tables from source tables elsewhere. the procedure takes roughly 16 seconds to run.
The problem I am having is it relies on a paramemter from the user..so the user must enter this onto an MS Access form. on hitting enter the stored procedure is executed this then causes the time glass on the clients PC to come up for the gven 16 odd seconds.
I would like for the SP to execute then run in the background giving control back to the client after hitting enter.
Thanks in advance
Antony
Time to make a change -
You don't indicate how you are calling the code, but if you are using ADO you can run the process asynchronously. Synchronous is the default, which means the machine will be tied up until the process is complete.
-
I am running the code from a pass thru query with the parameter built in vba from the combo box value the user chooses.
How do I run it asynchronously?
Btw there is no data returned to the user
Code to run the SP :-
strSQL = "prc_Import_Order " & strBatch
strSQL_return = "prcDisplayBatchInfo " & strBatch
'Assign the syntax to the query replacing anything currently there
qdf.SQL = strSQL
DoCmd.OpenQuery "qry_run_SP"
Time to make a change -
Anyone?
/Bump
Time to make a change -
Ok so ive messed around with my code a little
Dim strBatch As String
Set cnn = New ADODB.Connection
cnn.Open "Driver={SQL Server};Server=GMSQL1;Database=GLOAD;Trusted_Connection=Yes;"
Set cmd = New ADODB.Command
strBatch = "'" & Me.batch_combo.Value & "'"
With cmd
Set .ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "prc_Import_Order"
.Parameters("@BatchNo") = Me.batch_combo.Value
End With
cmd.Execute , , adAsyncExecute
' For simple, forward-only snapshot recordset.
'Set rs = cmd.Execute
End sub
The area in Bold is where I am struggling, how do I set the connection to asynchronous? Set rs = cmd.execute works but again holds the client in a freeze until the stored proc is complete.
How do i apply commandoptions to the with cmd end with part?
Time to make a change -
You can create a work table, and have your procedure call populate the work table. Another job that runs on a schedule reads the work table and performs whatever work is required by the newly-inserted contents of the work table. The final step would be to remove or mark as completed the unit of work that it just finished.
-
The tolerance thresh hold, while waiting for a response from a computer, is about 3 seconds. I know I am guilty of having processes that run longer than 16 seconds, but I have a funny feeling you can tweek either indexes or the SP itself to run alot faster. There are tons of articles on this website to help make your queries run much faster.
-
I have gone through my stored procedure and made it as efficient as I possibly can, the time it takes to run is purely because of the amount of records it needs to sort through.
See most of the queries in the stored procedure have to pass through to a DB2/AS400 server and sort through millions of records. And beleive me I have spent 2 weeks on this reading through white papaers and documents on how to improve the speed...here is my stored procedure
CREATE PROCEDURE dbo.prc_Import_Order (@batchNo varchar(9))
AS
DECLARE @TSQL varchar(8000)
/*
=========================================================
Here we grab the header information and put it to the table tbl_OHeader
*Still missing customer information - DONE
*Missing Text for the customer
=========================================================
*/
SET @TSQL = 'INSERT INTO [gload].[dbo].[tbl_OHeader] (BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BATCH_DATE,BATCH_TIME,SBUSY,ISHRR,GORTP,EORNO,ECSTN9,EDTNO5,FSOCN,FSSDN,FSTXT,GPLID1,ECCODA,USXRTA,FSCPR1,ESOEB,[@SDOE],TSTOE,DSCRF1,DSCNT1,[@SRDD],GRUTE1,SSOVR,ESOVB,[@SOVD],TSOVT,SSCCH,[@SCHD],ESCHB,[@SCRD],ESCRB,[@PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSWDC1,RWDIS1,[@DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC1,ESRPP,ESRPS,ISFTR,EORNP,FBORD,TSRDD3,FCRSK1,FSHB1,FSHB2,FSHB3, ' +
'FSHB4,FSHB5,FSHM1,FSHM2,FSHM3,GSPID1,FASOO,SPOAS,SWOAS,ECSTNC,EDTNOC,DDNAM,ADAD1,ADAD2,ADAD3,ADAD4,ADPC1,ADPC2,DCNAM, ACAD1, ACAD2, ACAD3, ACAD4, ACPC1,ACPC2) '+
'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@BATCH_DATE],[@BATCH_TIME],SBUSY,ISHRR,GORTP,EORNO,ECSTN9,EDTNO5,FSOCN,FSSDN,FSTXT,GPLID1,ECCODA,USXRTA,FSCPR1,ESOEB,[@SDOE],TSTOE,DSCRF1,DSCNT1,[@SRDD],GRUTE1,SSOVR,ESOVB,[@SOVD],TSOVT,SSCCH,[@SCHD],ESCHB,[@SCRD],ESCRB,[@PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSWDC1,RWDIS1,[@DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC1,ESRPP,ESRPS,ISFTR,EORNP,FBORD,TSRDD3,FCRSK1,FSHB1,FSHB2,FSHB3,FSHB4,FSHB5,FSHM1,FSHM2, '+
'FSHM3,GSPID1,FASOO,SPOAS,SWOAS,ECSTNC,EDTNOC,DDNAM,ADAD1,ADAD2,ADAD3,ADAD4,ADPC1,ADPC2,DCNAM, ACAD1, ACAD2, ACAD3, ACAD4, ACPC1,ACPC2 FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN GML01A.GLOAD_CUST T2 ON T1.ECSTN9 = T2.ECSTNC AND T1.EDTNO5 = T2.EDTNOC '+
'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO'
PRINT @TSQL
EXEC (@TSQL)
/*
-------------------------------------------------------------------------------------------------------------------
End the header output SQL
-------------------------------------------------------------------------------------------------------------------
*/
/*
==========================================================================
Here we grab the line/line Extra/product detail information and put it to the table tbl_OLine
==========================================================================
*/
SET @TSQL = 'INSERT INTO [gload].[dbo].[tbl_OLine] (BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BATCH_DATE,BATCH_TIME, SDRCD,GORTP8,EORNO8,LORDS4,[@SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,QPODB1,QPODC1,FDEFC,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB1,FSSB2,FSSB3,FSSM1,FSSM2,SDRCD_old,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM26,EITM36,EITM46,EITM56,ESTOR2,ITMRR6,LISGS6,UOMSQ,[£UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[£TOVL],CTOVL,[£TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,EOROR1, '+
'EITM1B,EITM2B,EITM3B,EITM4B,EITM5B,[@SRDD1],[@SODD],EVATCB,GCNRC2,SASPO1,SASWO1,ESRPP1,ESRPS1,ISFTR1,FSRFD,TSRDD2,FSDB1,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,FWOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITEM,DITMD,UOMTU,MUDN3,QGWGT) '+
'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@BATCH_DATE],[@BATCH_TIME],SDRCD,GORTP8,EORNO8,LORDS4,[@SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,QPODB1,QPODC1,FDEFC,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB1,FSSB2,FSSB3,FSSM1,FSSM2, SDRCD,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM26,EITM36,EITM46,EITM56,ESTOR2,ITMRR6,LISGS6,UOMSQ,[£UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[£TOVL],CTOVL,[£TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,EOROR1,EITM1B,EITM2B,EITM3B,EITM4B, '+
'EITM5B,[@SRDD1],[@SODD],EVATCB,GCNRC2,SASPO1,SASWO1,ESRPP1,ESRPS1,ISFTR1,FSRFD,TSRDD2,FSDB1,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,FWOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITEM,DITMD,UOMTU,MUDN3,QGWGT FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO = T2.EORNO8 '+
'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 = T3.EORNO1 AND t2.LORDS4 = T3.LORDS '+
'INNER JOIN GML01A.GLOAD_ITEM T4 ON T2.EITN12 = T4.ITEM '+
'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO, LORDS4'
PRINT @TSQL
EXEC (@TSQL)
/*
-------------------------------------------------------------------------------------------------------------------
End the Line output SQL
-------------------------------------------------------------------------------------------------------------------
*/
/*
==========================================================================
Here we grab the order text table to output ready for later when we grab that data and put it
onto the paperwork
==========================================================================
-Old sql
SET @TSQL = 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO, EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR) '+
'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM Openquery(BS01BAA, '+
'''SELECT BATCH_NUMBER, EORNO3, LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOTRP T1 ON TM.BATCH_OI_NO = T1.EORNO3 '+
'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO3, LORDS1'
*/
SET @TSQL = 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO, EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR) '+
'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM Openquery(BS01BAA, '+
'''SELECT BATCH_NUMBER, EORNO3, LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN GML01A.GLOAD_TEXT T1 ON TM.BATCH_OI_NO = T1.EORNO3 '+
'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO3, LORDS1'
EXEC (@TSQL)
SET @TSQL = 'INSERT INTO [gload].[dbo].[tbl_CText] (IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3, BATCH_NO) '+
'SELECT IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3, BATCH_NUMBER FROM Openquery(BS01BAA, '+
'''SELECT BATCH_NUMBER, IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3, BATCH_NUMBER FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN BS01BAA.BCTEXTP T2 ON T1.ECSTN9 = T2.IACCK AND T1.EDTNO5 = EDTNP4 '+
'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY INTRR3'
EXEC (@TSQL)
/*
-------------------------------------------------------------------------------------------------------------------
End the Order text output SQL
-------------------------------------------------------------------------------------------------------------------
*/
/*
==========================================================================
Here we update the YSBTCHP_DONE table to show we have processed the batch
*If somone tries a batch for a second time, it wont proces it above but will
add another entry into this table, must find a way to stop that!
==========================================================================
*/
SET @TSQL = 'INSERT INTO Openquery(GMP01A, '+
'''SELECT DONE_BTCH FROM GMP01A.YSBTCHP_DONE WHERE 1=0 '+
''') VALUES ('+@BATCHNO+')'
EXEC (@TSQL)
/*
-------------------------------------------------------------------------------------------------------------------
End the header output SQL
-------------------------------------------------------------------------------------------------------------------
*/
SET @TSQL = 'SELECT * FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO = T2.EORNO8 '+
'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 = T3.EORNO1 AND t2.LORDS4 = T3.LORDS '+
'WHERE TM.BATCH_NUMBER = ' + @BATCHNO + ''') ORDER BY EORNO, LORDS4'
GO
Time to make a change
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply