Slow Eval with SQL Server Back-End

<<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.

Yes, this is in code

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.

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.

<<- 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.

<<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.

Honestly, this is the same surprise for myself..

Thank you, Jim, for taking time to look into my question.

Managed to find the solution on my own