[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

Slow Eval with SQL Server Back-End

Hello,

I have a parameter query, based on a table with 300k records
Front-End is MS Access 2003 (mdb)
Back-End is MS SQL Server 2008 Express Edition

If query criteria is [Forms]![MyForm]![ID], the query opens instantly.
If I change the criteria to Eval("[Forms]![MyForm]![ID]"), it takes almost 7 sec to open.
ID field is a primary key field with unique index.

Is there any way to speed up the query, when using Eval function?

Thank you
0
msacc97
Asked:
msacc97
  • 5
  • 4
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Is there any way to speed up the query, when using Eval function?>>

 No.  By using Eval(), your forcing JET to handle the query rather then being able to pass it off to SQL.

Why are you using Eval?  Is this in code?

Jim.
0
 
msacc97Author Commented:
Yes, this is in code
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
No reason to use eval() then, just build the SQL string fetching/setting the value yourself  or use a querydef object, open it, set the value of the query parameter directly, and then open the recorset on the querydef.

Jim.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
msacc97Author Commented:
This is how I'm doing this currently (using QueryDef), and, you are right, this works.

I was just curious, why saved query:
- when not using Eval, opens and fast;
- when using Eval, opens but slow?

Also, my intention was not to add extra lines of code for every recordset all over the database, where parameters required.

Also, in reality, some queries are based on several tables and subqueries, and they are really complicated, so building them in code may take hundreds of extra lines more.
So building SQL string in code is not an option in my case.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<- when not using Eval, opens and fast;
- when using Eval, opens but slow?>>

 Eval() is not a T-SQL expression, but a VBA one.   When you use it, you force JET to handle the query rather then simply passing it off to SQL Server, which it will do.

 If you turned on ODCB tracing, you would see the difference.

<<Also, my intention was not to add extra lines of code for every recordset all over the database, where parameters required.>>

 Post the code your using and we'll see what we can do.  For example, you can do this:

Dim qdef As QueryDef
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
qdef.Parameters(0) = Eval(qdef.Parameters(0).Name)
Set rs = qdef.OpenRecordset()

 and let eval() do the work for you, yet when you actually open the recordset, the SQL statement is set and will get passed off to SQL.

Jim.
0
 
msacc97Author Commented:
Looks like I did manage to figure it out.

In my example, in saved query criteria just replace Eval("[Forms]![MyForm]![ID]") by CLng(Eval("[Forms]![MyForm]![ID]"))

My guess is, that the issue is because Eval function returns Variant data type.
Even though MS says that "The Eval function returns a Variant that is either a string or a numeric type.", looks like setting resulting data type explicitly, makes things dramatically faster.

Now my query opens fast and no need to use QueryDef and assign parameter values in code.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<My guess is, that the issue is because Eval function returns Variant data type.>>

  Yes that's correct and so it the MS Statement; the subtype in the variant is either string or numeric, but it's still a variant.

<<Now my query opens fast and no need to use QueryDef and assign parameter values in code. >>

 But that's a surprise and I would not have expected data typing alone to make such a difference; nice work!

 Make sure you accept your own comment the solution

Jim.
0
 
msacc97Author Commented:
Honestly, this is the same surprise for myself..

Thank you, Jim, for taking time to look into my question.
0
 
msacc97Author Commented:
Managed to find the solution on my own
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now