PC SOFT

FOROS PROFESIONALES
WINDEVWEBDEV y WINDEV Mobile

Inicio → WEBDEV 2024 → Native MSSQL driver but which code is better?
Native MSSQL driver but which code is better?
Iniciado por Steve Thompson, 17,dic. 2014 16:29 - 4 respuestas
Publicado el 17,diciembre 2014 - 16:29
Hi,

I'm finding the native MSSQL driver slow - my example is below. The HReset / Hadd code takes twice as long as the SQLExec code, when running this over 2000 items, the top code takes ~20s, the bottom code ~10s. Both are too slow in my mind but I can't get it any faster. Any ideas?

Thanks and Merry Xmas, Steve



HReset(ItemCartBackup)
ItemCartBackup.decAmount = tblItemCart234.decAmount
ItemCartBackup.dtCreateDateTime = tblItemCart234.dtCreateDateTime
ItemCartBackup.fPayFactor = tblItemCart234.fPayFactor
ItemCartBackup.iAdvPlay = tblItemCart234.iAdvPlay
ItemCartBackup.iCartEntryID = tblItemCart234.iCartEntryID
ItemCartBackup.iCustomerID = tblItemCart234.iCustomerID
ItemCartBackup.iGameID = tblItemCart234.iGameID
ItemCartBackup.iHouseID = tblItemCart234.iHouseID
ItemCartBackup.sChoice = tblItemCart234.sChoice
ItemCartBackup.sForm = tblItemCart234.sForm
HAdd(ItemCartBackup)


qrystring is string = "INSERT INTO ItemCartBackup (decAmount, dtCreateDateTime, fPayFactor, iAdvPlay, iCartEntryID, iCustomerID, iGameID, iHouseID, sChoice, sForm) values ("tblItemCart234.decAmount + ",getdate()," + tblItemCart234.fPayFactor + "," + tblItemCart234.iAdvPlay + "," + tblItemCart234.iCartEntryID + "," + tblItemCart234.iCustomerID + "," + tblItemCart234.iGameID + "," + tblItemCart234.iHouseID + ",'" + tblItemCart234.sChoice + "','" + tblItemCart234.sForm + "')"
SQLExec(qrystring, "Qry1")
Publicado el 17,diciembre 2014 - 16:46
Please put your code into a code tag so it can be easily read.
Use the farthest left symbol of the four symbols just above the message text box (The third symbol is a smiley face).
Publicado el 17,diciembre 2014 - 16:57
HReset(ItemCartBackup)
ItemCartBackup.decAmount = tblItemCart234.decAmount
ItemCartBackup.dtCreateDateTime = tblItemCart234.dtCreateDateTime
ItemCartBackup.fPayFactor = tblItemCart234.fPayFactor
ItemCartBackup.iAdvPlay = tblItemCart234.iAdvPlay
ItemCartBackup.iCartEntryID = tblItemCart234.iCartEntryID
ItemCartBackup.iCustomerID = tblItemCart234.iCustomerID
ItemCartBackup.iGameID = tblItemCart234.iGameID
ItemCartBackup.iHouseID = tblItemCart234.iHouseID
ItemCartBackup.sChoice = tblItemCart234.sChoice
ItemCartBackup.sForm = tblItemCart234.sForm
HAdd(ItemCartBackup)


qrystring is string = "INSERT INTO ItemCartBackup (decAmount, dtCreateDateTime, fPayFactor, iAdvPlay, iCartEntryID, iCustomerID, iGameID, iHouseID, sChoice, sForm) values (" + tblItemCart234.decAmount + ",getdate()," + tblItemCart234.fPayFactor + "," + tblItemCart234.iAdvPlay + "," + tblItemCart234.iCartEntryID + "," + tblItemCart234.iCustomerID + "," + tblItemCart234.iGameID + "," + tblItemCart234.iHouseID + ",'" + tblItemCart234.sChoice + "','" + tblItemCart234.sForm + "')"
SQLExec(qrystring, "Qry1")


Sorry! :)
Publicado el 17,diciembre 2014 - 19:54
It is personal preference really.
If you want to favour code readability/simplicity over speed of the application than your first example would be the best.
If you want to favour speed, then the second way would be best.

Here are a few suggestions that may speed up the second example since from your post, it seems you want to favour speed.
I ran this in sql server:
CREATE TABLE #TempItemCart (
decAmountDECIMAL(10,2),
dtCreateDateTimeDATETIME,
fPayFactorFLOAT,
iAdvPlayINT,
iCartEntryIDINT,
iCustomerIDINT,
iGameIDINT,
iHouseIDINT,
sChoiceVARCHAR(100),
sFormVARCHAR(100)
);

SET STATISTICS TIME ON

INSERT INTO #TempItemCart
(decAmount,dtCreateDateTime,fPayFactor
,iAdvPlay,iCartEntryID,iCustomerID
,iGameID,iHouseID,sChoice
,sForm)
VALUES
(1,GETDATE(),1,1,1,1,1,1,'1','1'),
. . .
(1000,GETDATE(),1000,1000,1000,1000,1000,1000,'1000','1000');

SET STATISTICS TIME OFF

DROP TABLE #TempItemCart


It took 3ms to insert 1000 rows on my system.
One way to speed up your inserts is to do less of them. Your approach has to create a connection, run the insert, and then get a result for every insert, doing this 2000 times. If you break the inserts up into blocks of 1000 ( the max number of rows in a sql server insert statement) then you would only create connection, insert, and get a result twice.
Here is some code that may help you test this:

// Create insert header
qrystring is string = [
Insert INTO ItemCartBackup
(decAmount,dtCreateDateTime,fPayFactor
,iAdvPlay,iCartEntryID,iCustomerID
,iGameID,iHouseID,sChoice
,sForm)
VALUES
]

// create 1000 rows
FOR EACH itemCart OF itemCarts
qrystring += "(%1,GETDATE(),%2,%3,%4,%5,%6,%7,'%8','%9'),"
qrystring = StringBuild(qrystring, ...
itemCart.decAmount,itemCart.fPayFactor,itemCart.iAdvPlay ...
,itemCart.iCartEntryID,itemCart.iCustomerID,itemCart.iGameID ...
,itemCart.iHouseID,itemCart.sChoice,itemCart.sForm)
END

// replace , with a ; at the end of the statement
qrystring = Truncate(qrystring,Length(qrystring) - 1) + ";"


Please let me know what the final time is if you implement this, I'm interested how it works with your situation.
This insert is only available in 2008 and above.
You can your tweaked code into a function, add your itemcarts to an array and when it reaches 1000, pass the array into the function to insert or break up the itemCarts in some way in 1000 groups.
Publicado el 18,diciembre 2014 - 17:21
Hi Zach,

Thanks for all your help so far!

That suggestion looks very promising - I'll have more of an idea shortly. As something along the same lines, as well as Inserting a record within the loop, I am also doing an update / insert depending on search criteria as such :

qrystring is string = "SELECT TOP(1) FROM tblItemsPerAccount WHERE dtDateFor = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND iShopID = " + tblItemCart234.iShopID + " AND sStraightAccount = '" + tblItemCart234.sChoice + "'"
SQLExec(qrystring, "Qry1")
IF SQLFetch("Qry1") = 0 THEN
qrystring = "UPDATE tblItemsPerAccount SET fAmount = fAmount + " + tblItemCart234.fPayFactor + ",iItemCount = iItemCount + 1 WHERE dtDateFor = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND iShopID = " + tblItemCart234.iShopID + " AND sStraightAccount = '" + tblItemCart234.sChoice + "'"
ELSE
qrystring = "INSERT INTO tblItemsPerAccount (dtDateFor,iShopID,fAmount,sStraightAccount,iItemCount,dtCreateDateTime) VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)," + tblItemCart234.iShopID + "," + tblItemCart234.fPayFactor + ",'" + tblItemCart234.sChoice + "',1,getdate())"
END
SQLExec(qrystring, "Qry1")


Is there an easier / multiple method for doing this also?

Regards, Steve