Does JET process (slowly) the qdf.OpenRecordset statement?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Folks

When getting the result of a pass-through from SQL Server, does JET's poor performance impact these QueryDef statements which retrieve the result of an insert

Here is the sp

CREATE PROCEDURE dbo.spAddRecord A
SET NOCOUNT O
INSERT INTO tblXXX (Item1, Item2, ...) VALUES (23,25,...
SELECT @@IDENTITY AS "ID
G

'I ASSUME THIS RUNS FAST WITHOUT JET OVERHEA
Set db = CurrentDb(
Set qdf= db.CreateQueryDef(""
qdf.ReturnsRecords = Tru
qdf.SQL = "Exec sp_InsertRecord

'IS THIS SLOWED BY JET OVERHEAD
Set rs = qdf.OpenRecordset(
ID = rs.fields(0

Sparky
 
Query processing involves preparation (parsing the SQL, generating an
execution plan, creating & saving the querydef), and execution (actually
pulling the data out of the tables).

The preparation phase is very fast with Jet. It takes a negligible amount of
time to parse SQL, generate query plans & save new querydefs. Any speed
problems are generally in the execution phase, due to the normal/obvious
causes - insufficient indexing, badly written SQL &/or database structure
errors.

With a passthrough query, I would have thought that any slow performance
would be problems at the other end - not overheads imposed by Jet.

But I've never linked to SQL server, so I'm happy to be contradictied on any
of this, in that context.

HTH,
TC


Sparky said:
Folks:

When getting the result of a pass-through from SQL Server, does JET's poor
Click to expand...
performance impact these QueryDef statements which retrieve the result of an
insert?
 
Hi

I ran a test and querydefs were 10x slower than cmd.parameter("@XXX") method -- it screams

Thanks
 
Hi Sparky,

Have you run the SP in the Query Analyzer. If so, is there any performance issue?

Based on my experience, the issue seems to be an ODBC performance issue executing the pass-thru
query from MS Access. The issue can be caused by the Page Latches when perform the insertions. Please
read the following articles to exclude some known issues and apply the suggestions within the following KB
articles:

209091 ACC2000: How to Optimize Microsoft Access When Using ODBC Data Sources
http://support.microsoft.com/?id=209091

208858 ACC2000: Optimizing for Client/Server Performance
http://support.microsoft.com/?id=208858

If the verion of Jet engine 4.0 is earlier than 6.0, I recommend you apply the latest Jet 4.0 SP8 via:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;829558

to suppress the performance issue described in the KB 302496:

302496 ACC2000: Queries Are Slower After You Install Microsoft Jet 4.0 Service
http://support.microsoft.com/?id=302496


Please apply my suggestions above and let me know if this helps improve the performance. If there is any
headway from you, or should you have any questions and concerns on the , please feel free to post it in the
group.

Regards,

Billy Yao
Microsoft Online Support
 
Hi Sparky,

I am just checking on your progress regarding the information that was sent you.

Have you tried the steps I provided to you? I wonder how the testing is going. If you encounter any difficulty,
please do not hesitate to let me know. Please post here and let me know the status of your issue. Without your
further information, it's very hard for me to continue with the troubleshooting.

I look forward to hearing from you soon.

Regards,

Billy Yao
Microsoft Online Support
 
Back
Top