Why MDE file performance is much slower than MDB file?

I have an MDB file that converted into an MDE file.  With data the MDE file size is about 150MB.  About 90% of the application is driven by VBA and SQL statements.  How come it takes me about 10 minutes to process the data from the MDB file and it take about 10X as slow in the MDE file?  Is there a way to speed it up?

Thanks,
BT
LVL 1
babytamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
Often times when you compile into an MDE Access also decompiles your queries so the fierst time the queries are run the optimizers needs to do an anaysis. Do you get better performance the second time you process with an MDE?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
babytamAuthor Commented:
No..it's always the same.  I use VBA to write  SQL statement and use Docmd.RunSQL to execute it.  Could this be the reason?

Thanks,
Tam
0
stevbeCommented:
I am not sure that is causing your problem but if you can use saved queries and parameters you will get better performance.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

babytamAuthor Commented:
I think that must be it.  I switched from Docmd.RunSQL sqlStatement to Currentdb.Execute sqlStatement and it ran way faster, as fast as the MDB now.  What's the difference between Docmd.RunSql and Currentdb.Execute statments?

Thanks,
Tam
0
stevbeCommented:
I have never used Docmd.RunSQL but I *assume* there is lots of extra processing overhead.

if you want to run even faster you use DBEngine.Execute instead of CurrentDB. CurrentDB will force a refresh of all it's collections before executing whereas DBEngine just pops the SQL along. The obnly time you would need to be concerned is if you are creating tables and then you would either .Refresh the tableDefs collection or just use CurrentDB.
0
babytamAuthor Commented:
Do I need any special Reference file to use DBEngine.Execute?  I tried to switch to DBEngine.Execute and it gave me a compile error, Method or data member not found.

Thanks,
Tam
0
stevbeCommented:
You need the Microsoft DAO Object 3.6 Library

Steve
0
babytamAuthor Commented:
I have that, but I also have ActiveX Data Objects 2.1 Library.  If I put DAO Object 3.6 library before ActiveX Data Objects 2.1 Library then I will get error for ADO syntax, and if I put ActiveX Data Objects 2.1 Library before DAO Object 3.6 then can't use DBEngine.Execute statement.  Do you have any recommendations?

Thanks,
Tam
0
babytamAuthor Commented:
Actually, DBEngine.Execute is not a recognized method whether I put DAO Object 3.6 Library before or after ActiveX Data Objects 2.1 Library.
0
Leigh PurvisDatabase DeveloperCommented:
(NOT for points - just popping in :-)

CurrentDb.Execute strSQL
Or
DBEngine(0)(0).Execute strSQL

:-)
0
babytamAuthor Commented:
DbEngine(0)(0).Execute works.  

Thanks,
Tam
0
stevbeCommented:
Nice pickup Leigh ... I was typing too fast.

To address the order in which object libraries are referenced causing stuff to break ... If you fully qualify your object references it does not matter what order they are listed in.

DAO.DBEngine(0)(0)
ADODB.Recordset

etc.

Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.