query very slow on SQL; maybe replace with search box?

[TraciAnn via AccessMonster.com]

I use a combo box with a filter query for users to select a record on a form.
As an Ac BE it only took 2 to 3 seconds to load. I upsized it to a SQL BE and
now it takes 3.5 minutes to load.

The Select Statement is:
SELECT dbo_vCTUser.UserName, [dbo_vCTUser].[LastName] & ", " & [FirstName] AS
ContactName
FROM dbo_vCTUser INNER JOIN (dbo_vCTRequest INNER JOIN dbo_vCTRequestUser ON
dbo_vCTRequest.RequestID = dbo_vCTRequestUser.RequestID) ON dbo_vCTUser.
UserName = dbo_vCTRequestUser.UserName
WHERE (((dbo_vCTRequest.ProjectID)=DLookUp("[Project]","[dbo_Filter]")))
GROUP BY dbo_vCTUser.UserName, dbo_vCTUser.LastName, dbo_vCTUser.FirstName
ORDER BY dbo_vCTUser.LastName, dbo_vCTUser.FirstName;

Is there something I can do to speed this up or should I replace the feature
with a Search function?

I've never created a search function so I could use some assistance if that
is the best direction. At the simplest, the search would just have to compare
the unique record ID "UserName", to better meet the needs of the users it
would need to be clever enough to search UserName or a combination of First &
Last names. It would also need to include the WHERE clause used in the select
statement.

Thanks everyone!

 

[Jeff Boyce]

TraciAnn

Perhaps its that "DLookup()" function...?

Is there a way you can select/identify the ProjectID using a query instead?
Or a form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TraciAnn via AccessMonster.com]

Jeff,

Is there a way you can select/identify the ProjectID using a query instead?

I'm not sure I understand. Would this require adding dbo_Filter to the query
views? If so, would it necessitate linking it to the other tables in the
query in some way?

The entire application is based on this DLookup which is performed in almost
every query. It is the result of a previous post of mine which questioned
whether I should set ProjectID as a Public Variable. As public variables are
not well viewed, this was the recommended solution.

Or a form?

I assume this means to initially load a form that holds the ProjectID value
and then references the object with that value? That means I would still be
using a DLookup doesn't it? Or is the difference that I'm referencing a local
object as opposed to an object on the server?

How would I implement this?

Thanks for your help!

 

[Jeff Boyce]

Since we're not there, you'll need to describe enough about what you're
working with for us to understand.

For example, I have no idea what kind of data you are keeping in your table
named "dbo_Filter" ... aside from the implication that it at least holds
ProjectID. How many ProjectID's? What else?

You've describe "how", but I"m not seeing the bigger picture. How will
having this working help you do ... ??!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TraciAnn via AccessMonster.com]

I have no idea what kind of data you are keeping in your table
named "dbo_Filter" ... aside from the implication that it at least holds
ProjectID. How many ProjectID's? What else?

That is it. Right now Filter is designed to enter one of three numbers in the
ProjectID column. It is a single record table that is edited by me. It
filters for the Project that all users are working on at any given time.

Filter table
FilterID (PK)
ProjectID (long integer)

Future plans for Filter would be something like adding a separate Project
table so Filters would be applied at the user level.

Filter table
UserName (PK)
ProjectID (FK to Project)

Project table
ProjectID
ProjectName
ProjectString

The ProjectName would be the friendly name by which users would select the
project they are working on. ProjectString provides an additional parameter
that could be used to filter beyond just the ProjectID - for example a
specific Task within the project.

You've describe "how", but I"m not seeing the bigger picture. How will
having this working help you do ... ??!

The SQL BE is used on a large scale by the organization. My app was being
used as a stand alone until this release in which they didn't want to keep
synchronizing tables between the Ac BE and SQL BE. So, they added the ac BE
tables to the SQL BE.

So now the BE has 12k Locations as opposed to the 500 that are used for a
project. It has 10k Users of which only 1k are used (at any given time) by my
FE.

Each Location can have dozens of Tickets (70k records in Ticket), with each
ticket associated to half a dozen Users. Whereas my users are only interested
in the ticket related to their project (totalling 500).

So, as you can see, Filter is supposed to weed out all the Users, Locations
and Tickets that aren't needed for the project. Supposedly speeding up the
app. But right now, it would almost be quicker to pull in all records and
ignore the 90% irrelevant records. <tongue in cheek>.

I hope this answers your questions Jeff. Thanks for your assistance!

 

[Jeff Boyce]

TraciAnn

I'm pretty sure I still don't 'get it', but I had an idea...

If, instead of a table that you have to open/edit, you used a form and a
textbox (or combobox) on that form to provide the "filter", you could
rewrite your query/SQL to look at that form/control for it's value. You'd
use something like:

Forms!YourFilterFormName!YourFilterControlName

to point to where the query could find the value for its "WHERE" clause.


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TraciAnn via AccessMonster.com]

Jeff,

I am resolving this issue (short term) in two ways.

1. dbo_Filter is a "fixed, one-record" table. In which only the project admin
will change the value of "ProjectID". A right join has been added to several
of the queries - matching ProjectID with the same field on another table. I
will resort to your "Form" solution for the next release, which will add the
ability for some users to change to another project.

The queries on which the above solution was used improved performance by
returning the results in a second or less (compared to 3.5 to 4.5 minutes).
The queries on which the above solution did not correct the problem (or
resulted in errors), I resorted to solution #2.

2. Our SQL developer created stored procedures of the queries that remained.
When testing them in the analyzer, the test demonstrated equal results as to
solution #1. BIG PROBLEM, I don't know how to "connect" to them.

The only help I can find on sprocs is to create them in Ac using the Pass-
Through function. I tried a suggestion I found on another newsgroup to "enter
the name of the stored proc in the SQL text portion of the Pass-Through
query". Which I did, but it produced an ODBC -- call failed error.

Can you help me?

All queries that have been converted to a sproc were either a control source
for a form or for a report. Part of my challenge may be that I need to change
my mindset to recognize the differences between a query as a control source
and a stored procedure as...well...a "procedure?". I'm assuming there is a
difference but I'm not sure.

I look forward to your assistance. Thanks Jeff!

 

[Jeff Boyce]

TraciAnn

I don't make use of stored procedures too often, so hopefully another
newsgroup reader can offer assistance.

I tend to create views in SQL Server that serve something like queries. You
can link to a view the same way as linking to a table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP