Table Update Speed Problem

Hi All ,
I need an answer.I have tried everything in the Book to speed up updates to My MSAccess SQL table.Indexes , and transactions . You can see the Big SQL statement (lots of Join Statements) in the "Where " clause.

i loop through the recordset returned by this query and for each record update some other table.(see the While Loop enveloped in transactions and i use the DBSQLPASSTHROUGH option for each update.This option does not load the Jet database Engine and i think what is does is to directly invoke functions implemented in the ODBC Driver--- in other words sidesteps one layer).

Then Why in the world does this update take time.It is very slow.Even the application,seems to hang.Conceptually , I understand that for each value to be updated in the
table a radom access is made to that record(records may be in non-Sequential locations).But then what is the solution??????????????????????????????????????????????????/

'******************************SEE the CODE BELOW**********************************


str1 = "SELECT D.CustGrp, D.CustCombo ,D.MktArea,D.OrderSize, D.ProdClass as Prod, P.RET_PRICE AS retail,((DB.MDemand + DB.SDemand )* P.RET_PRICE * (PT.EP_DISCNT/100) * (PT.EP_MAX_DAY/100)) as EPD " & _
"FROM DemandData AS D, ProdInfo AS P ,detailedBreakdowns as DB ,PAYTERMS as PT " & _
"Where d.ProdClass = p.PROD_CLASS and d.CustCombo = DB.CustCombo and d.OrderSize = DB.OrderSize and d.Mktarea = DB.MarketArea and PT.Cust_Group = D.CustGrp;"



Set rsEPD = cnxn.OpenRecordset(str1, , dbSQLPassThrough)
sum = 0

While (rsEPD.EOF <> True)

str2 = "Update detailedBreakDowns as D " & _
"Set EPmtDiscount = " & rsEPD!EPD & " " & _
"Where D.CustCombo = " & rsEPD!CustCombo & " and D.OrderSize = " & rsEPD!OrderSize & " and D.MarketArea = " & rsEPD!MktArea & " "


cnxn.Execute str2, dbSQLPassThrough

sum = sum + rsEPD!EPD

rsEPD.MoveNext

Wend



rsEPD.Close

try this composite query instead of a recordset

Update detailedBreakDowns TAB2
Set EPmtDiscount = (select TAB1.EPD
from
(SELECT D.CustGrp, D.CustCombo ,D.MktArea,D.OrderSize, D.ProdClass as Prod, P.RET_PRICE AS retail,((DB.MDemand + DB.SDemand )* P.RET_PRICE * (PT.EP_DISCNT/100) * (PT.EP_MAX_DAY/100)) as EPD
FROM DemandData AS D, ProdInfo AS P ,detailedBreakdowns as DB ,PAYTERMS as PT
Where d.ProdClass = p.PROD_CLASS and d.CustCombo = DB.CustCombo and d.OrderSize = DB.OrderSize and d.Mktarea = DB.MarketArea and PT.Cust_Group = D.CustGrp) TAB1,detailedBreakDowns TAB2
where TAB2.CustCombo =TAB1.CustCombo and TAB2.OrderSize=TAB1.OrderSize and TAB2.MarketArea=TAB1.MARKETAREA)

hi kronique ,
I need some explanation.I understand that this is a Select query as a nested query to the Update Query.Is this availabale in standard SQL.
further , what if more than 1 field is updated.
Will get back to you again,after i try this
thanks a lot

well i think if you are using oracle as db, this would work. if it is say SQL server, u can recreate this same using the query designer, where they allow a join while creating an update statement

Well , i am using an MSAccess database???????????

hello, i just noticed something in this query

str1 = "SELECT D.CustGrp, D.CustCombo ,D.MktArea,D.OrderSize, D.ProdClass as Prod, P.RET_PRICE AS retail,((DB.MDemand + DB.SDemand )* P.RET_PRICE * (PT.EP_DISCNT/100) * (PT.EP_MAX_DAY/100)) as EPD " & _
"FROM DemandData AS D, ProdInfo AS P ,detailedBreakdowns as DB ,PAYTERMS as PT " & _
"Where d.ProdClass = p.PROD_CLASS and d.CustCombo = DB.CustCombo and d.OrderSize = DB.OrderSize and d.Mktarea = DB.MarketArea and PT.Cust_Group = D.CustGrp;"

you have specified inner joins with oracle syntax, use access syntax like 'INNER JOIN DemandData on ProdInfo.prodclass=DemandData.prodclass

hello, i just noticed something in this query

str1 = "SELECT D.CustGrp, D.CustCombo ,D.MktArea,D.OrderSize, D.ProdClass as Prod, P.RET_PRICE AS retail,((DB.MDemand + DB.SDemand )* P.RET_PRICE * (PT.EP_DISCNT/100) * (PT.EP_MAX_DAY/100)) as EPD " & _
"FROM DemandData AS D, ProdInfo AS P ,detailedBreakdowns as DB ,PAYTERMS as PT " & _
"Where d.ProdClass = p.PROD_CLASS and d.CustCombo = DB.CustCombo and d.OrderSize = DB.OrderSize and d.Mktarea = DB.MarketArea and PT.Cust_Group = D.CustGrp;"

you have specified inner joins with oracle syntax, use access syntax like 'INNER JOIN DemandData on ProdInfo.prodclass=DemandData.prodclass

I will work on this tomorow.I am tooo tired now,
bye and thanks ,, will let u know tomorow.
matu

I run this query as it is (with a correction on the name of the field name which was TAB1.MarketArea and should have been TAB1.Mktarea. And it gives me an error saying

"Operation must be an Updateable query"

so whats the deal with permissions here??/
Other than this query i can update a table , but dont know whats wrong here??

Also does an INNER JOIN work faster than an Oracle Style Join or What ???///

thanks much
matu


Update detailedBreakDowns TAB2
Set EPmtDiscount = (select TAB1.EPD
from
(SELECT D.CustGrp, D.CustCombo ,D.MktArea,D.OrderSize, D.ProdClass as Prod, P.RET_PRICE AS retail,((DB.MDemand + DB.SDemand )* P.RET_PRICE * (PT.EP_DISCNT/100) * (PT.EP_MAX_DAY/100)) as EPD
FROM DemandData AS D, ProdInfo AS P ,detailedBreakdowns as DB ,PAYTERMS as PT
Where d.ProdClass = p.PROD_CLASS and d.CustCombo = DB.CustCombo and d.OrderSize = DB.OrderSize and d.Mktarea = DB.MarketArea and PT.Cust_Group = D.CustGrp) TAB1,detailedBreakDowns TAB2
where TAB2.CustCombo =TAB1.CustCombo and TAB2.OrderSize=TAB1.OrderSize and TAB2.MarketArea=TAB1.MktArea);

More than likely your updateable query error is a permissions problem. The windows user that's running your program needs read/write access to the .mdb file. This is common with asp apps when the IIS user account isn't able to write to the db file.

Hi ,
I have been able to create temporary tables and delete them before , but only when i update a table using a "Update- Select Query " , am i not able to update the table.thanks so much ,
Regards ,
Utpal