|
|
Hi all,
I have an MDB being used successfully with ACC2003, connecting to SQL 2000
via ODBC. However when I open the exact same MDB (ie Access 2003 version
MDB) with ACC2007 I have severe performance issues.
I have a query returning approx 60000 records.
In ACC2003 the form takes less than a second to load.
In ACC2007 the form takes between 10 - 30 secs to load.
After the form finishes opening, I can click between all the fields fine
except for any Date field, where it takes 10 - 30 secs before control comes
back. It seems to cache this result as when I move between records and click
on the same date field again, there is no delay.
I have unsuccessfully tried all combinations of SQL 2000, SQL 2005, the 2
different ODBC drivers, and even upgrading the MDB to ACC2007 format ACCDB.
I have narrowed down the problem to when a recordset contains Date fields.
When I remove the date fields from the recordset this problem does not occur.
I am in Australia so am using UK date format as default.
However when I changed all the settings I could find to US date the problem
still occurred.
I have performed a SQL Trace (first time ever, so let me know if there is
anything else that would help me)
When using ACC2007, the first time each date field is selected it proceeds
to individually query every record in the recordset, so over 60000 calls!
This does not occur using ACC2003 or earlier.
Initial Event:
declare [ at ]P1 int
set [ at ]P1=54
exec sp_prepexec [ at ]P1 output, N'[ at ]P1 int', N'SELECT
"REF_NUMBER","CLIENT","ClientID","INST_BY","BRANCH","REFERENCE","APPLICANT","ADDRESS","SUBURB","PostCode","VALUER","INSPECTED","VALUATION","FEE","PMT_RCD","COMMENT","PROPERTY_TYPE","MELWAY","CONSTRUCTION","BLDG_SIZE","LAND_SIZE","LAND_VALUE","BILLING_ADDRESS_ID","BILLING_
ADDRESS","PrintBillingAddress","Mailing_ADDRESS_ID","Mailing_ADDRESS","PrintMailingAddress","TITLE_FEE","TRAVEL_FEE","PaymentSelected","ChequeNo","ChequeName","Web_Comment","Credit_Number","FeeLock","chgDateIn","chgInspectTime","chgDateSent","chgWebComment","tmpAmtAllocated","tmpGSTAllocated","PaymentCompleted","Sale","SalePrice","Delayed","OnHold","WorkingDaysElapsed","LastChangedBy","CreatedBy","WithAdmin","InstructionStatusID","InstructionStatus","upsize_ts"
FROM "dbo"."RegisterCurrent" WHERE "REF_NUMBER" = [ at ]P1', 1
select [ at ]P1
Then for each record the following event:
exec sp_execute 54, 1
exec sp_execute 54, 2
exec sp_execute 54, 3
exec sp_execute 54, 6
etc for 60000 events!
Hope someone can help.
Cheers,
Davin Biggs
|
|
|