Async Query

[MajP]

Have you got a link to this documentation? It sounds pretty wrong to me and I wasn't able to find anything in line with your statement regarding the .Recordset

You are completely correct, but do not need documentation. That is simple enough to test. Requery or change recordsource on any control on a form and that will not impact the form. Probably do this all the time with cascading combos and the form does not requery.

My concern is that when you force a reset of the form's .Recordsource by directly assigning a new .Recordset or by defining a new SQL source, MSDN says you automatically requery the form, no choice in doing so

Also this is easy to prove wrong as it relates to a recordset. Make two forms one a copy of the other. On the second form remove the recordsource. Open the first form and manipulate it (sort, filter, add data). Open the second form and bind its recordset to the first forms recordset. They will be identical and synchronized and no requery takes place.

 

[The_Doc_Man]

Reference for my comments (from an MSDN site, which I usually feel I can trust):

https://msdn.microsoft.com/en-us/vba/access-vba/articles/form-recordsource-property-access

Note Changing the record source of an open form or report causes an automatic requery of the underlying data. If a form's Recordset property is set at runtime, the form's RecordSource property is updated.

I can't test this for an active SQL engine that could go ASync 'cause I don't have one now that I am retired. And I know from a long thread on this subject that "native" Access back-ends are not capable of going Asynchronous. So my primary concern is to avoid misleading our OP, and alarms went off in my head when I looked at this.

I do not doubt for a heartbeat than an external SQL engine could go Async. My concern is that the moment you change the .Recordsource directly OR IMPLICITLY, you force a .Requery, and if that behavior is true for .Rowsource as well as .Recordsource, then the whole point of the exercise is defeated because you don't want to repeat the query you just finished in Async mode.

Guys, I'm not trying to be contentious here - really not. It's just that what I thought I knew, and what MSDN is telling me, don't seem to line up with the advice being given. Hey, if the documentation from MSDN is wrong, fine. I'm sure it wouldn't be the first time.

If I am reading the MSDN quote incorrectly, then again it wouldn't be the first time that MS documentation is unclear on a fine point. But it says if you update the .Recordsource you do a .Requery and if you update the .Recordset you also update the .Recordsource, which could lead to another .Requery, and that is the source of my concern.

 

[MajP]

If I am reading the MSDN quote incorrectly

Yes.

Note Changing the record source of an open form or report causes an automatic requery of the underlying data

This is really easy to test. Open a form with a recordsource. Move to the last record. Have a button to execute
me.recordsource = me.recordsource
or
Me.recordsource = "some other query"
By changing the recordsource property the form requeries

If a form's Recordset property is set at runtime, the form's RecordSource property is updated.

All that is saying if you bind it to a recordset, it will update the recordsource property. Thats all. Easy to prove. Start with a form with no recordsourece

Private Sub Command12_Click()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("qryNIS")
  rs.MoveLast
  'Notice the selector on the last record. Therefore no requery
  Set Me.Recordset = rs
  MsgBox rs.Name & " " & Me.recordSource
End Sub

 

[The_Doc_Man]

Break my objection down into two parts, MajP. I have no doubt that if you directly change the .Recordset property, you WILL change the .Recordsource property or .Rowsource property. And I absolutely agree with that part.

It is the fine point of whether doing so triggers an automatic .Requery, and you say it does not even though it is possible to read the MSDN documentation otherwise. I will step away from this one because you seem to have it in hand.

 

[MajP]

It is the fine point of whether doing so triggers an automatic .Requery, and you say it does not even though it is possible to read the MSDN documentation otherwise

Don't take my word on it, prove it to yourself. I provided you several demonstrations that you can do on your own. Or build one yourself, it is pretty trivial. That is what I do, if I am unsure of the behavior I build a demo and try it out. That is how you learn to debug code. I try not to post any advice that I "think" may happen, I most always will test it first if unsure or say it is untested. In this case I have no machine to load Access and SQL on and stated so, but I am pretty versed in recordsets and believe this will work since there is plenty of examples of this on the web.

 

[The_Doc_Man]

I provided you several demonstrations that you can do on your own.

I'm an experimental scientist from way back (originally, a chemist by training), so I firmly believe in the experimental method. That isn't the problem. Remember, I'm not disputing that the .Rowsource will change if you jam in a new recordset into the appropriate control property. The question is what else it does - or doesn't - do.

The problem with your suggestion is that since I have no SQL back end to play with, I'm not sure how I would tell from a "native" Access operation (which is always synchronous) that a requery had or had not occurred. I.e. if I build a recordset that will be used for the combo box or list box and I use the suggestion to do a .MoveLast before jamming that recordset into the .Recordset property of the affected control, how will I know that it DIDN'T do a .Requery?

If I single-step through the code, the key moment is the instruction after jamming in the new .Recordset, and any test I can make here would be on local data for a table too small for it to make a difference whether it did or didn't requery. In either case, records would be available by the time I got around to looking at the combo box/list box. I don't know how to create a database log precise enough to differentiate between the two possibilities. The experimental setup for this isn't so simple as you suggest.

In essence, if I am uncertain of how to interpret the observation, it isn't worth doing the experiment - because if I can't prove what it did or didn't do, I have learned nothing. But I sense that you have this in-hand so I will stand aside and wait for the resolution of the problem to at least infer whether an automatic .Requery occurred. Given the size of our OP's database, it would be obvious if an automatic .Requery occurred after the recordset assignment.

 

[khurram7x]

Did you try doing like the example in #9? That example is using a sql server backend.

I tried and got error on function name as: 'User-defined type not defined'. I added Reference to 'Microsoft ActiveX Data Objects 6.1 Library' that fixed it.


Next, I setup the connection string as per my server configuration and tried to open the connection... failed.
Error I'm getting is: Data source name not found and no default driver specified.



I googled and found that Driver should be SQL Native Client. Tried to do so but still no use.

DRIVER={SQL Native Client};SERVER=(myServerName);DATABASE=(dbName);Uid=***;Pwd=***;
(tried with/without curly brackets)



I've never worked with ADO before, or I'm too dumb to run this thing properly. I've Windows 7 x64 setup. What possible should be my next action please?


P.S. Sorry I was not able to check the message over the weekend.



Regards,
K

 

[khurram7x]

...
Can you show us some sample data and the form to see if this is being done in a sensible manner?

Thanks. I'm uploading a sample code @ google here
https://drive.google.com/open?id=15MTzVgUeVPqQQQ9RXRUNWUyiX2qVXUYc


I get correct results when I use CurrentDB.Execute, but Row Source is not filled with PT Queries.

... I "think" may happen, I most always will  test it first if unsure or say it is untested.  In this case I have no  machine to load Access and SQL on and stated so, but I am pretty versed  in recordsets and believe this will work since there is plenty of  examples of this on the web.[/QUOTE]
[QUOTE="The_Doc_Man, post: 1579347, member: 3758"]...since I have no SQL back end to play with,  I'm not sure how I would tell from a "native" Access operation (which  is always synchronous) that a requery had or had not occurred..[/QUOTE]
 Thanks guys for such details, but RecordSet, RecordSource and RowSet does not need Requery as it does not need without SQL Server. This is what I've observed so far while working with both of them. I've a Developer Edition on SQL Server on my computer that I use for development, along with .Net which I'm trying to learn and with MS Access... all on same computer.
What I'm understanding from above posts that I could not run RowSource Async, but even if this is not the case I would rather learn about Async queries for any future reference. 

In addition, if there's a better way to query faster as oppose to my uploaded code file, please do let me know so that I could make use of it to enhance performance.

 

[The_Doc_Man]

khurram7x - please note that the discussion between MajP and me is not meant to discourage you. We are both seeking a way to improve the performance issue that you describe. But as is common with technically oriented people, we sometimes disagree on the approach that would work best, and our problem is that it is not clear what ELSE is done when you directly change the .Recordset property of that control.

You are in a prime situation to actually test MajP's suggested solution. Neither of us can perform that test because neither of us can create the required environment for it. If you have the time to try what she suggests, we might ALL find out what is happening behind the scenes on a fine point not covered in system documentation. And who knows? It might even be a solution to your problem.

Just to clarify: MajP suggests that if you create a recordset that has the fields required for your combo box, set it up to do an Async .MoveLast and then go about other work behind the scenes - if there IS anything else you can do. When that operation is finished, THEN you can copy the recordset onto the combo-box.Recordset property. She thinks that you would get the advantage of the Async query. She believes that it would not automatically do a .Requery in that case because the recordset is already loaded up with data.

My concern is whether changing the control's .Recordset, which transparently changes the .Rowsource, would do an automatic .Requery and thus "waste" the Async operation, which would leave you right where you were in terms of speed issues.

Neither of us is suggesting that you do a manual .Requery after reloading the .Recordset, and we both agree that you should not need to do so. It is at that crucial moment when you assert the new recordset to the control that we have some question about what ELSE happens based on automatic operations behind the scenes in Access.

RecordSet, RecordSource and RowSet does not need Requery as it does not need without SQL Server. This is what I've observed so far while working with both of them.

Are you saying with this statement that the .Requery occurs anyway?

 

[The_Doc_Man]

As a separate consideration,

For clarification, it is not cascading combos. In my case, you're allowed to select any of the 15 options right from the start. Once you select first option, other 14 options should re-query and show only options available based on first selection. There's no sequence as in cascading combo.

This is a quibble of sorts, but if it is not a "cascading combo box" sequence then the boxes should be truly independent of each other. If selections from box A can affect available choices for box B then it is a cascading combo case (based on the old theory that if it walks like a duck and quacks like a duck and flies like a duck, it ain't a hummingbird.)

Your comment about these combo box changes - that they take longer on a larger database - makes me wonder if your problem is that each box is querying against the main table to see what is left to be queried. So you potentially run a query of a query of a query a huge number of times. I had a case where I had six fields that were suitable for querying (or not) based on what the users needed to see. But rather than run a ton of queries, I just allowed them to specify commonly seen values for selection. I then used their combo box selections to rebuild a query's WHERE clause - but I didn't actually run that query against the main tables until they hit the "SHOW RESULT" button.

The way I populated the combo boxes was that I did not care whether they would get anything back if they overqualified the query. They just selected one value from a list of commonly found choices. And sometimes it happened that they would get no results. But I never did more than one query of the main table, which included literally a quarter of a million entries.

Minty also expressed some concern about the time you say this takes. If you could describe a bit more about WHAT you are selecting with each combo box, perhaps we might be able to offer thoughts on a different setup that doesn't require so many queries. And let's face it - your problem IS that you have so many queries to run that you want to minimize the time they take.

 

[MajP]

@The_Doc_Man

We are both seeking a way to improve the performance issue that you describe.

Just to be clear, I was not even looking at general ways to improve performance. I was focused only answering the question if it is possible to run a query asynchronous from Access to a SQL server back end. The ADODB model supports that. There may be all kinds of, ways to improve performance, but was not focused on that. Seemed from the OPs comments he is trying to learn this technique for this or possibly future developments and had some academic curiosity.

The problem with your suggestion is that since I have no SQL back end to play with, I'm not sure how I would tell from a "native" Access operation (which is always synchronous) that a requery had or had not occurred. I.e. if I build a recordset that will be used for the combo box or list box and I use the suggestion to do a .MoveLast before jamming that recordset into the .Recordset property of the affected control, how will I know that it DIDN'T do a .Requery?

The question you raised was if binding a recordset to a form or control would cause a requery. What I stated and provided you as proof is that when you bind a recordset to a form or control, it most certainly does not force a requery. You do not have to do this against a Sql backend to prove that. An ADODB recordset is an ADODB recordset so for what logical reason would you expect this work differently?

suggestion to do a .MoveLast before jamming that recordset into the .Recordset property of the affected control, how will I know that it DIDN'T do a .Requery?

As commented, the record selector is still on the last record after binding the recordset. It is pretty obvious if a requery would take place the record selector would be on the first record. Do you know of a case where that does not happen? I guess I could have sorted the recordset and then bound it to provide further proof that it does not go back to the original sort order.

Thought it was funny that you think I am a she. Not offended, just curious what I said that gave off that vibe.

@khurram7x
You probably realized that ADODB and ADO.Net are two completely different animals. ADO.Net is very powerful in what it can do. Take a look at some of the ADO.Net capabilities to do asynchronous processes
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/asynchronous-programming

Also for connection strings take a look here. There is some really useful info
https://www.connectionstrings.com/

 

[MajP]

@The_Doc_Man
As a scientist prove it to yourself
Buid a recordset from a query in a given sort order
Use the recordset sort property to sort the recordset to something different.
https://msdn.microsoft.com/en-us/library/office/ff198077.aspx
Which if you read only sorts the recordset, and does not change the query def or create a new recordset.
If you want move somewhere in the recordset.
Now bind it to the control or a form. If a requery would take place you would expect it to be in the original sort order and the record selector at the first record. Come back and tell me your results. Do it in DAO and ADO and see if there is any differences there.

 

[The_Doc_Man]

Sorry, MajP - made an incorrect gender assumption. Happens now and then. "He" it is, from now on.

I was focused only answering the question if it is possible to run a query asynchronous from Access to a SQL server back end.
...
An ADODB recordset is an ADODB recordset so for what logical reason would you expect this work differently?

I already know you CAN do Async queries to SQL Server and that you can use ADODB recordsets to do it. That never was an issue.

My question was whether something would happen behind the scenes to negate the desired Async effect because of the suspected forced requery related to this being a control's recordset, not a stand-alone recordset. I would expect no effect at all from cloning a stand-alone recordset to another stand-alone recordset. It is the control's actions that make this a wildcard in my mind.

Now bind it to the control or a form. If a requery would take place you would expect it to be in the original sort order

No, I wouldn't expect that. From your referenced article,

When you set this property for an object, sorting occurs when a subsequent Recordset object is created from that object.

When you do the assignment step, you essentially clone that recordset AND ITS PROPERTIES, which includes the .Sort property still in place. So sort order wouldn't tell me whether a requery had been done.

I'll have to think about whether the position of the recordset actually tells me what I would want to know.

 

[sonic8]

My concern is whether changing the control's .Recordset, which transparently changes the .Rowsource, would do an automatic .Requery and thus "waste" the Async operation, which would leave you right where you were in terms of speed issues.

Just for clarification: Setting the Recordset of a form or list-/combobox does not trigger a requery!


My general thoughts on this topic:
If the performance of some queries is bad, then fix the issue by making the queries run faster and/or by running the queries less often.

In most cases, introducing async operations, while making things much more complex, will only cure (some of) the symptoms but not the disease.

 

[khurram7x]

khurram7x - please note that the discussion between MajP and me is not meant to discourage you. We are both seeking a way to improve the performance issue that you describe. But as is common with technically oriented people, we sometimes disagree on the approach that would work best, and our problem is that it is not clear what ELSE is done when you directly change the .Recordset property of that control.
...

My concern is whether changing the control's .Recordset, which transparently changes the .Rowsource, would do an automatic .Requery and thus "waste" the Async operation, which would leave you right where you were in terms of speed issues.

Thanks to your guys for very fine details. I understand you're all trying to help me.
Just to be clear, this is an unbounded form and Record Source is not available. This forms opens in response to a selection of a combo on another form. I've not used .RecordSet anywhere in the code yet for this form, and I'll try once it is clear in our discussion about what is going to work best for me.

I mentioned that changing back-end to SQL Server has not changed anything the way code worked with JET Db, so we're quite safe to assume that it'll not re-query even if back-end if SQL Server.

...The way I populated the combo boxes was that I did not care whether they would get anything back if they overqualified the query. They just selected one value from a list of commonly found choices. And sometimes it happened that they would get no results. But I never did more than one query of the main table, which included literally a quarter of a million entries.

Minty also expressed some concern about the time you say this takes. If you could describe a bit more about WHAT you are selecting with each combo box, perhaps we might be able to offer thoughts on a different setup that doesn't require so many queries. And let's face it - your problem IS that you have so many queries to run that you want to minimize the time they take.

I actually did similar think earlier where query would not run until you hit 'Search' button, but I was told that we need to minimize the search at each selection to matching one's only so that someone will not selecte unmatched option and not return any result. If wrong selection is made, and no result is returned, Supervisor will not have any equipment to report progress against.
I've uploaded another screenshot at google drive, did share above and tried to describe on an image what is required, or actually happening, in my case.

I'm posting link again here. It also contains the text file which has the code for selection parameter form. From there you'll pick up what I'm trying to do in code, and you'll be able to suggest what is the best solution for me.
https://drive.google.com/open?id=15MTzVgUeVPqQQQ9RXRUNWUyiX2qVXUYc

@The_Doc_Man

...@khurram7x
You probably realized that ADODB and ADO.Net are two completely different animals. ADO.Net is very powerful in what it can do. Take a look at some of the ADO.Net capabilities to do asynchronous processes
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/asynchronous-programming...

Thanks. I've saved in my favorites and certainly will spend time on it.

...
My general thoughts on this topic:
If the performance of some queries is bad, then fix the issue by making the queries run faster and/or by running the queries less often.

In most cases, introducing async operations, while making things much more complex, will only cure (some of) the symptoms but not the disease.

I'm applying a condition which will not allow to run query against a combo which is disabled (I've explained disabled combox in screenshot I've discussed above) due to no matching data in disabled combo for selected item.
I've indexed the queries but all of them are text field, which still makes it slow as database grows.


My reason on being Async query was, we've parallel application in Android and it also started working slow at some point recently. We went back to Android developers and they fixed the issue by using Async queries and cutting some queries short, which is another topic. Developer told me that he has created 15 functions, one for each combo query, and a main function which calls to those 15 functions. Java compiler then run all those Async queries together. This does not keep the program on wait until all queries finish running, and they populate the combo RowSource quietly in the back ground. This has increased the speed of Android version of the program to very fast and responding, and this is what I'm also trying to implement in Access unless there's a better suggestion.

So is it possible to get this done on similar lines in Access, to let 15 Async queries run together in background while program does not wait finishing their execution?
I know all combos will not populate at once like this, but program will be responding at the least, and since queries are fast and indexed, they should be able to respond before someone could take the mouse there and click!

Plus, does the Async query also works in a similar fashion as OpenRecordSet... i mean in a record by record traversal of full table, of each and every record? like Cursors in SQL Server?

Where does the resultant query gets stored?
How should I give the RowSource to combo?
I've run the following query, which runs successfully but not understanding how to set RowSource for combo?


Set cmd = New ADODB.Command

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "SELECT DISTINCT tblBoQ.TagNo " & SqlWhere & ParaWhere & " AND tblBoQ.TagNo IS NOT NULL"
.CommandType = adCmdText
.Execute adAsyncExecute
End With

 

[MajP]

I would take a step back and not worry about doing asynch queries seems you need to get some basics first. However, the most important rules for making an efficient access with an external backend is:
1) Do as much heavy lifting on the server side. You do not want to pull and query on the client side when you can avoid it. Need to utilize pass through queris, odbc connection, stored procedures, sql views, etc.
2) Return from the server side the smallest amount of data and records needed.
3) Make sure your queries are efficient.

After that there are still a lot of strategies and things to do. This link is a little dated but very good. I would reference often

https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx

Some of the basics you have to learn if not already knowledgeable. You can google there is a ton of info:
1. Build and use Pass through queries
2. Connecting to Sql using ADODB
3. Building a recordset in AODB

Try to build a simple ADODB recordset and manipulate it. I would do this first with a local table, just to first learn working with recordsets. I should a simple example with DAO (another object model similar to ADODB, but more suited to working only in access)

Where does the resultant query gets stored?
How should I give the RowSource to combo?
I've run the following query, which runs successfully but not understanding how to set RowSource for combo?

However you have some confusion between a recordsource and a recordset. A recordsource is nothing but a sql string, query name, or table name. It is simply a string. It is a set of instructions to Access that says when you load this form or control execute this to retrieve a set of records. It is the "instructions" it is not data. Once the db follows those instructions it goes and retrieves those records and a Recordset is created. The recordset is a data object with lots and lots of properties, methods, and events. I only need the recordsource if I want Access to create the recordset for me. If I create my own recordset in code a recordsource (instruction to create the recordset) is not needed.

So if you create a recordset by code, you simply bind the recordset to the combo or form.
Set Me.combo2.recordset = SomeRecordsetICreated

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "SELECT DISTINCT tblBoQ.TagNo " & SqlWhere & ParaWhere & " AND tblBoQ.TagNo IS NOT NULL"
.CommandType = adCmdText
.Execute adAsyncExecute
End With

Understand the above code is connecting to a local access table since you are using a currentProject.connection. You are trying to make your local connection asynch, which I am not sure even has any meaning. You should practice returning an ADODB recordset from a local table/s. You can google how to do this. Then simply set the recordset to a control. Once you get that connect to your back end through ADODB and make a recordset.

 

[The_Doc_Man]

Regarding MajP's post #36 and the first three items enumerated in his first paragraph: Totally agree. ABSOLUTELY agree. Query optimization starts with data design optimization. Gyrations for making the same query run faster only make a difference on a dog-slow network with huge numbers of potential records to manage.

Sonic8 - thank you for the clarification regarding combo box recordset management.

If wrong selection is made, and no result is returned, Supervisor will not have any equipment to report progress against.

Regarding a design that would allow someone to choose a combination from a group of over a dozen possible criteria and yet NOT end up with a zero result set? My question is, if these people had any sense, don't you believe they would realize "Oh, darn! I've over-specified something. Let me try again."? Why were they made supervisors?

Do you have an estimate for the number of devices recorded in that table?

The INNER JOINS that you have shown us in that link are probably at least partly the source of your slowdown. Done that way, it appears that you have to search the whole table every time. I'm not entirely sure how I would approach this. I once had a related (but admittedly not identical) problem where I found a way to trim down my working list and speed up some parts of the query tremendously.

I built a local temporary table that had, in essence, two fields - an ID number (that was the list of prime keys from the main table) and a Yes/No flag that always initialized to YES. This table was easy to populate because it came from the main table that everything else would JOIN with.

I then built a query for the main table so that I could see all of the fields. When I ran the partial queries to eliminate things that didn't match up, it was through an INNER JOIN query that linked the temp table to the main table through the PK field. So in other words, I was looking at a layered query, never directly at the tables.

I worked through a form with code that did eliminations from the temp table rather than selections from it. Each elimination set the temp table flag to NO, after which I could drop those entries from the temp table. Of course you cannot drop things from the main table. After each elimination, I would know how many potential selections remained.

The first run through that table was miserable because it had to hit the whole table, but by only having one selection criterion, it wasn't so bad. Running through the second iteration, I didn't have to touch those members of the main table that had already been eliminated from the temp table. That reduced the size of the table to be searched which drastically improved the speed of the subsequent searches. The third search was even faster. That "telescoping" search method got to the point that the last couple of searches were available almost instantly.

I don't know if that would even be practical for you, but the point was that I found a way to avoid having to search everything every time. It would not be practical to set a flag in the main table since it has to be shared. But a local temp table would be unique for each user and you CAN do INNER JOIN queries between tables in two different files. Just takes a little longer than if they are all in the same place.

Treat this as an idea to be considered, not me saying this is the only way to proceed. The concept I want to put forward is that completely searching the totality of every table every time will eat your lunch - but you will still get the indigestion from it.

 

[isladogs]

Bit late now but I've just realised this has been cross posted (also with many detailed answers) at: https://social.msdn.microsoft.com/Forums/en-US/6df3b4b2-667f-474d-abaa-14fe69e56715/async-query?forum=accessdev

@Khurram
Please read the attached link regarding the etiquette of cross-posting https://www.excelguru.ca/content.php?184
There's no objection to this providing you follow the guidelines

When you consider the time & effort many people at both forums have spent answering your post, you do need to ensure they aren't wasting their time

 

[khurram7x]

...
1) Do as much heavy lifting on the server side...
2) Return from the server side the smallest amount of data and records needed.
3) Make sure your queries are efficient.

...After that there are still a lot of strategies and things to do. This link is a little dated but very good. I would reference often

https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx

...1. Build and use Pass through queries
2. Connecting to Sql using ADODB
3. Building a recordset in AODB...

...However you have some confusion between a recordsource and a recordset...

I'm using most of the time PT Queries and using Indexes on server side to return only the smallest set. I still intent to use it at least for a first table run. Thanks for the references. Will go through them.

...Regarding a design that would allow someone to choose a combination from a group of over a dozen possible criteria and yet NOT end up with a zero result set? My question is, if these people had any sense, don't you believe they would realize "Oh, darn! I've over-specified something. Let me try again."? Why were they made supervisors?

Do you have an estimate for the number of devices recorded in that table?

The INNER JOINS that you have shown us in that link are probably at least partly the source of your slowdown. Done that way, it appears that you have to search the whole table every time. I'm not entirely sure how I would approach this. I once had a related (but admittedly not identical) problem where I found a way to trim down my working list and speed up some parts of the query tremendously.

I built a local temporary table that had, in essence, two fields - an ID number (that was the list of prime keys from the main table) and a Yes/No flag that always initialized to YES. This table was easy to populate because it came from the main table that everything else would JOIN with.
...

There are simply huge number of records, Supervisors does not remember all options all the time and then re-query will be time wasting as per management because Supervisors has to record large number of records everyday.


The table has got over 16000 devices to be installed right now and it's very small, compare to 100 thousands of rows on bigger projects... and still i feel performance issue. That's why I though to cater with it now rather than waiting for a big project to bog it down in about a month with an embarrassment on me.


I agree on INNER JOIN and intended to reduce it, i just posted the original query. Will work on temp table idea, and will come back if i need more help on that.


I'll work on suggestions from both of you and will come back as soon as possible.


Thank you for the support.

 

[khurram7x]

Bit late now but I've just realised this has been cross posted (also with many detailed answers) at: https://social.msdn.microsoft.com/F...abaa-14fe69e56715/async-query?forum=accessdev...

Ridders, I did post on another forum on this occasion to get a couple of different ideas because this was quite complicated for me. I did get a message across from other forum to use PT Queries which is not working well when used with multiple combos. It is here I got more ideas and suggestions to mix up stuff.


Once my problem is solved, I'll mark both threads as answered and post a link to other forum and redirect them for the final answer on this forum.


Rest assure, I'm using forums for few years and follow the rules to portray myself as legit user to get answers from respected member.
Hope this clarifies.