Requery just ONE row from sql server (1 Viewer)

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 21, 2019

• #21

So the solution (so far) seems to be linking a View via DSNless connection, creating the index, and I expect good speed.

 

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 21, 2019

• #22

Okay - no luck. The recordsource of the Form is a View from SQL Server, with an index, and yet Search/Find is still taking a very long time.

 

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 21, 2019

• #23

So... to summarize... here are some options I've tried...

1) Link to SQL server table... Form uses standard query on this... does a lot of what I want BUT search/Find is slow.

2) Link to View on Server... similar to 1) I think

3) Pass-through query - everything is faster, but it doesn't refresh data when the data is updated behind the scenes, even with the Refresh command

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 01:41

Joined

Feb 19, 2002

Messages

43,233

• Sep 21, 2019

• #24

Can you post the query you are running - both versions please.

 

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 21, 2019

• #25

This is the standard query...

RecordSource = "SELECT usysServer_tblPersons.* FROM usysServer_tblPersons WHERE (((usysServer_tblPersons.Group)=fngsGroup())) ORDER BY usysServer_tblPersons.ID;"

usysServer_tblPersons is a Server table linked to Access with Group/ID as the index.

fngsGroup() is a constant selected earlier in the process.

The other standard query just referred to a View instead.

The passthrough was very similar, but of course had a connection string to the server.

 

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 21, 2019

• #26

Maybe another way to ask the question is this... because really all I'm trying to do is find the Row where ID = 12345 (for example) and go to that row. If I use recordset / find, it's slow. I can of course query where ID = 12345 but that won't give me the row. How do I get the row number, quickly (without looping through all words), that meets a criteria?

 

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 21, 2019

• #27

I came up with a "cheat"... I created a pass-through query that mimics the Form recordsource, but also returns Row_Number. Then I run another query off the P-T query which grabs the RowNum that matches the criteria, then I go to that RowNum on the form. It runs relatively quickly. I don't need a View, or indexing, etc.

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 01:41

Joined

Feb 19, 2002

Messages

43,233

• Sep 22, 2019

• #28

The query isn't using a join, it is using a function to look for a matching record. Of course this takes forever. How many rows is the function reading?

 

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 23, 2019

• #29

Ah. It might be 10,000 records. I see what you mean. I guess I assumed that once the query found the records, doing a search should be fast, but I guess that "criteria" slows things down quite a bit. Since I found a work-around, I'm going to just use that.

 

C

CedarTree

Registered User.

Local time

Today, 01:41

Joined

Mar 2, 2018

Messages

404

• Sep 23, 2019

• #30

P.S. Just out of curiosity, I fixed the query to be hard-coded SELECT usysServer_tblPersons.* FROM usysServer_tblPersons WHERE Group = 'XYZ' ORDER BY usysServer_tblPersons.ID;
And the search/find still took forever.

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 01:41

Joined

Feb 19, 2002

Messages

43,233

• Sep 23, 2019

• #31

Sounds like a large table and no index on Group.

 

S

sonic8

AWF VIP

Local time

Today, 07:41

Joined

Oct 27, 2015

Messages

998

• Sep 24, 2019

• #32

CedarTree said:

I created a pass-through query that mimics the Form recordsource, but also returns Row_Number. Then I run another query off the P-T query which grabs the RowNum that matches the criteria, then I go to that RowNum on the form. It runs relatively quickly.

Click to expand...

Interesting idea. That simulates what Access/ODBC does automatically when using a linked table (or view) with a standard query in Access.

CedarTree said:

I don't need a View, or indexing, etc.

Click to expand...

I agree on you not needing a view. - When I suggested that, I assumed a complex query across multiple tables. - That is a very common reason for using a Pass-Through-Query.


However, I think indexing is the one thing you most urgently need! Put an index on tblPersons.Group in SQL Server and then check how your queries behave. - I guess this might solve your problem completely.