Re: Slow Query when Prompts enabled

Tech-Archive recommends: Speed Up your PC by fixing your registry

What does this mean.

Currentdb.QueryDefs("qsptYourPT").SQL

"Duane Hookom" wrote:

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" <RonaldL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2074AC7D-59FE-422D-A97F-927ECFC5996A@xxxxxxxxxxxxxxxx
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" wrote:

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" wrote:

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



.

Relevant Pages

  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... Full text does not perform with a nested loop because the search is done for each iteration, if your server doesn't have much memory you will have a huge IO issue. ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ... dB (this time from the actual ASP page that passes the query to the ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... if your server doesn't have much memory ... and the "executing query" ball just kept ... When I get properties for the FT catalog, ... The company I am currently working for is a heavy consumer of SQL FTS ...
    (microsoft.public.sqlserver.fulltext)
  • Re: freetexttable query never completes nor times out?
    ... SQL Server MVP ... This is a dev server so I hadn't even been ... When I get properties for the FT catalog, ... I just ran a similar query on ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Single-Threading / Performance issues
    ... SQL Server MVP ... > Early this year my database was migrated from a single CPU, ... (when a long select query is running, ... server guy also sez it's not his problem. ...
    (microsoft.public.sqlserver.server)