Slow Query when Prompts enabled

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ronald_L - 14 Sep 2006 17:05 GMT

I have a query that is very slow, 5minutes+ to run when I have any of the
fields prompted for information. If I type in the criteria in the Criteria
Field the report runs in about 3 seconds or less. I am using [FN] as the
prompt. Why would this be happening.

Here is the SQL view that is so slow

SELECT DISTINCT dbo_Folder.sTtlFldr, dbo_vDocPrincipal.PrincipalFirstName,
dbo_vPrincipal.lastname, dbo_vPrincipal.cparty, dbo_vProperty.tAddress,
dbo_vProperty.City, dbo_vProperty.State, dbo_vProperty.County,
dbo_vProperty.Legal1, dbo_vProperty.Legal2, dbo_vProperty.Legal3,
dbo_vProperty.Legal4, dbo_vProperty.Legal5, dbo_vProperty.Legal6
FROM ((dbo_Folder LEFT JOIN dbo_vPrincipal ON dbo_Folder.iFolder =
dbo_vPrincipal.ifolder) LEFT JOIN dbo_vProperty ON dbo_Folder.iFolder =
dbo_vProperty.iFolder) LEFT JOIN dbo_vDocPrincipal ON dbo_Folder.iFolder =
dbo_vDocPrincipal.iFolder
WHERE (((dbo_vDocPrincipal.PrincipalFirstName)=[FN]));

Reply to this Message

Duane Hookom - 14 Sep 2006 17:36 GMT

Looks like this query is hitting a database server such as SQL Server. You
should consider using a Pass-Through query to return the results. You can
write code like:

Dim strFN as String
Dim strSQL as String
strFN = InputBox("Enter Firstname")
strSQL = "SELECT DISTINCT ....." & _
" WHERE vDocPrincipal.PrincipalFirstName ='" & strFN & "'"
Currentdb.QueryDefs("qsptYourPT").SQL = strSQL

Your strSQL would need to mirror you SQL view but would probably need to
remove the "dbo_" from table/view names.

Signature

Duane Hookom
MS Access MVP

>I have a query that is very slow, 5minutes+ to run when I have any of the
> fields prompted for information. If I type in the criteria in the
[quoted text clipped - 14 lines]
> dbo_vDocPrincipal.iFolder
> WHERE (((dbo_vDocPrincipal.PrincipalFirstName)=[FN]));

Reply to this Message

Dale Fye - 14 Sep 2006 17:48 GMT

Ronald,

Have you defined [FN] as a parameter? If not, open your query in design
view. put your cursor in the grey area where your tables are displayed and
right click. Select Parameters, then in the top left box enter [FN]. Then
tab over and define it as a text data type. Let me know if that helps.

> I have a query that is very slow, 5minutes+ to run when I have any of the
> fields prompted for information. If I type in the criteria in the Criteria
[quoted text clipped - 13 lines]
> dbo_vDocPrincipal.iFolder
> WHERE (((dbo_vDocPrincipal.PrincipalFirstName)=[FN]));

Reply to this Message

Jerry Whittle - 14 Sep 2006 18:37 GMT

1. Are the various ifolder fields indexed in the tables?

2. Is dbo_vDocPrincipal.PrincipalFirstName indexed?

3. You haven't defined the parameter datatype. Your first line of the SQL
should look something like:

PARAMETERS [FN] Text ( 255 );

Noticed the trailing semicolon.

4. Any time that you do a Distinct expect a performance hit. That you need a
distinct leads me to believe that you have a normalization problem. Also the
Legal1, Legal2, etc.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Reply to this Message

Ronald_L - 14 Sep 2006 18:46 GMT

All of the tables are indexed via iFolder. This slowness is only an issue
when I pass the query its criteria via Prompts. If I type in the query
criteria manually, the query runs in about 3 seconds parsing approx 300,000
records.

I set just the query to prompt me for 1 thing [FN], and then it takes
forever for the query to return the results. Why is this happening.

> 1. Are the various ifolder fields indexed in the tables?
>
[quoted text clipped - 28 lines]
> > dbo_vDocPrincipal.iFolder
> > WHERE (((dbo_vDocPrincipal.PrincipalFirstName)=[FN]));

Reply to this Message

Duane Hookom - 14 Sep 2006 20:56 GMT

This is happening because you are setting a criteria on a field from a view.
Views aren't normally indexed so you are probably performing a tablescan on
your server through your Access frontend. The same would happen if you
change your parameter prompt to a reference to a control on a form.

That is why I suggest you create a pass-through query to your database
server and change the SQL property as needed. You may be wise to create a
stored procedure on your server that accepts the [FN] as a parameter. Your
P-T query SQL would then be

EXEC spYourStoredProcedure 'Jerry'

You could easily use DAO to change the SQL property to add a parameter to
match user input into a text (or combo) box.

Signature

Duane Hookom
MS Access MVP

> All of the tables are indexed via iFolder. This slowness is only an issue
> when I pass the query its criteria via Prompts. If I type in the query
[quoted text clipped - 44 lines]
>> > dbo_vDocPrincipal.iFolder
>> > WHERE (((dbo_vDocPrincipal.PrincipalFirstName)=[FN]));

Reply to this Message

Ronald_L - 14 Sep 2006 21:03 GMT

What does this mean.

Currentdb.QueryDefs("qsptYourPT").SQL

> This is happening because you are setting a criteria on a field from a view.
> Views aren't normally indexed so you are probably performing a tablescan on
[quoted text clipped - 59 lines]
> >> > dbo_vDocPrincipal.iFolder
> >> > WHERE (((dbo_vDocPrincipal.PrincipalFirstName)=[FN]));

Reply to this Message

Duane Hookom - 14 Sep 2006 21:25 GMT

This would be a line of code that changes the SQL property of a saved,
pass-through query.
I create a function in most of my mdbs to change the sql property of saved
queries:

Function ChangeSQL(pstrQueryName As String, _
pstrSQL As String) As String
'this function requires a reference set to a DAO Object Library
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQueryName)
'return the current SQL from this function
ChangeSQL = qd.SQL
'update the SQL to the new SQL (pstrSQL)
qd.SQL = pstrSQL
'clean up
Set qd = Nothing
Set db = Nothing
End Function

I can then call this function from other code to change the SQL property of
any saved query.

Signature

Duane Hookom
MS Access MVP

> What does this mean.
>
[quoted text clipped - 73 lines]
>> >> > dbo_vDocPrincipal.iFolder
>> >> > WHERE (((dbo_vDocPrincipal.PrincipalFirstName)=[FN]));

Reply to this Message