Slow Query when Prompts enabled

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

Guest

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]));
 
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.
 
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.
 
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.
 
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.


Jerry Whittle said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Ronald_L said:
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]));
Click to expand...
Click to expand...
 
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.


--
Duane Hookom
MS Access MVP



Ronald_L said:
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.


Jerry Whittle said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Ronald_L said:
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]));
Click to expand...
Click to expand...
Click to expand...
 
What does this mean.

Currentdb.QueryDefs("qsptYourPT").SQL

Duane Hookom said:
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.


--
Duane Hookom
MS Access MVP



Ronald_L said:
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.


Jerry Whittle said:
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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

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]));
Click to expand...
Click to expand...
Click to expand...
 
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.

--
Duane Hookom
MS Access MVP

Ronald_L said:
What does this mean.

Currentdb.QueryDefs("qsptYourPT").SQL

Duane Hookom said:
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.


--
Duane Hookom
MS Access MVP



Ronald_L said:
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?

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

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]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top