How to make this query more efficient? (1 Viewer)

[darbid]

I have the following query (I am sorry if it is not formatted correctly)

SELECT *
FROM tbl_questionnaire AS mt1
WHERE mt1.q_id=(SELECT top 1 mt2.q_id FROM tbl_questionnaire as mt2 
                                WHERE mt1.q_akz=mt2.q_akz
                                ORDER BY mt2.q_decision_date desc, mt2.q_decision_number desc, mt2.q_bkz_id);

As far as I know - for each q_akz it will
order them by q_decision_date then q_decision_number then q_bkz_id
But it will only give me the highest number in the q_id.

q_id ------ q_akz - - - - q_decision_date -- q_decision_number -- q_bkz_id
1 - - - - - 2000K058 - - - - 10/01/2005 - - - - - - - - - - 1 - - - - - - - - - 2356
2 - - - - - 2000K058 - - - - 20/01/2006 - - - - - - - - - - 2 - - - - - - - - - 2356
3 - - - - - 2000K058 - - - - 20/01/2007 - - - - - - - - - - 3 - - - - - - - - - 2356
4 - - - - - 2000K058 - - - - 20/01/2004 - - - - - - - - - - 1 - - - - - - - - - 5898
5 - - - - - 2000K058 - - - - 20/01/2006 - - - - - - - - - - 2 - - - - - - - - - 5898
6 - - - - - 2000K058 - - - - 20/01/2007 - - - - - - - - - - 3 - - - - - - - - - 5898

So on the above only one row would be returned and that is row 6

I am pretty sure this works but it is inefficient / slow.

I intend to add more conditions to it like this.

SELECT *
FROM tbl_questionnaire AS mt1
WHERE mt1.q_id=(SELECT top 1 mt2.q_id FROM tbl_questionnaire as mt2 
                                WHERE mt1.q_akz=mt2.q_akz 
                                AND q_used_by_third_party<3 
                                AND q_can_use=1 
                                AND q_hierarchie_level_2 Like '*H*'
                                ORDER BY mt2.q_decision_date desc, mt2.q_decision_number desc, mt2.q_bkz_id);

I am currently testing this in the query builder at the moment and this above query sometime works and sometime crashes Access. The thing is that the condition is going to get even more complicated. Further this Query is going to be the source query for a form.

tbl_questionnaire is a linked SQL Server table with 79,000 records with q_id as a primary key

So my question what can I do to make this more efficient? Or how should I be doing this.

 

[spikepl]

Did you put indexes on the fields examined in WHERE?

 

[darbid]

Did you put indexes on the fields examined in WHERE?

Hi spikepl,

q_id, q_akz, q_decision_date, q_decision_number, q_bkz_id are indexed - but I cannot index them all as potentially the where could be anything.

 

[spikepl]

but I cannot index them all

Why not? You'd get a performance hit on INSERT but a huge gain on SELECT - so you must decide on the required balance

 

[spikepl]

Also, if your
ORDER BY mt2.q_decision_date desc, mt2.q_decision_number desc, mt2.q_bkz_id)

decision number is permanent, perhaps a composite index on these 3

 

[darbid]

Also, if your
ORDER BY mt2.q_decision_date desc, mt2.q_decision_number desc, mt2.q_bkz_id)

decision number is permanent, perhaps a composite index on these 3

I will have to do some research on what "compsote indexes" are.

Right now I have moved my base sql as a view in SQL Server and then linked the View to access. No I am just executeing a where on the view and it is working much much faster. This might be my solution.

 

[darbid]

One thing and I know it is a little off topic but maybe it is not too difficult.

The view in SQL Server looks like this

SELECT     mt1.*
FROM         Lutadmin.tbl_questionnaire mt1
WHERE     (q_id =
                          (SELECT     TOP 1 mt2.q_id
                            FROM          Lutadmin.tbl_questionnaire AS mt2
                            WHERE      mt1.q_akz = mt2.q_akz
                            ORDER BY mt2.q_decision_date DESC, mt2.q_decision_number DESC, mt2.q_bkz_id))

When I link it - Access asks for a primary key. Can I add something to the above SQL in the view so that Access does not need to ask this question?

 

[stopher]

First, if you want row 6 (q_id 6), then surely q_bkz_id also needs to be DESC. The query you have supplied is going to return row 3.

Second, what's the point of the subquery? It seems only to included more columns. But you can do this in one query:

SELECT TOP 1 q_id, q_akz, q_decision_date, q_decision_number, q_bkz_id
FROM tbl_questionnaire
ORDER BY q_decision_date DESC , q_decision_number DESC , q_bkz_id

I'm sure the subquery is going to hurt the performance.

Maybe I'm missing something?

Chris

 

[spikepl]

This is getting beyond my experience and imagination. But I would also guess that pass-through queries, i.e. queries that are run by the SQL-engine on the server might be a better choice here.

Someone with more experience in this area will hopefully turn up soon.

 

[darbid]

Maybe I'm missing something?

I hope not, because I now think my SQL does have a problem (other than it takes a long time).

I will take a closer look at your suggestion and test.

The idea is that I would like the newest q_decision_date or the highest q_decision_number from the q_akz.

As you can see it is possible for q_decision_date and q_decision_number to be the same. The q_bkz_id cannot be used because that is an ID. Thus the idea (which is an assumption) to get only one in this case was to take the TOP 1 from the primary key or q_ID.

I would appreciate all suggestions.

 

[stopher]

This is getting beyond my experience and imagination. But I would also guess that pass-through queries, i.e. queries that are run by the SQL-engine on the server might be a better choice here.

Someone with more experience in this area will hopefully turn up soon.

Same here. I'd missed the SQL Server ref in the first post. But it makes sense to get SQL Server to do the work and return 1 record rather than pulling 79,000 records over the network.

Pass-through queries

Chris

 

[darbid]

Second, what's the point of the subquery? It seems only to included more columns. But you can do this in one query:

SELECT TOP 1 q_id, q_akz, q_decision_date, q_decision_number, q_bkz_id
FROM tbl_questionnaire
ORDER BY q_decision_date DESC , q_decision_number DESC , q_bkz_id

I'm sure the subquery is going to hurt the performance.

That took less time than I thought to test.

This returns the ultimate TOP 1 (if I can call it that).

My little table above is just one example of a q_akz from the table. It must do this for each q_akz. My query needs to return the TOP 1 for each different q_akz .

 

[darbid]

Same here. I'd missed the SQL Server ref in the first post. But it makes sense to get SQL Server to do the work and return 1 record rather than pulling 79,000 records over the network.

I agree and maybe I would say the same, but I kinda/maybe need this to work with JET as well as it is going to be a Forms Record source and all the Pass-Through stuff is going to cause what's left of my hair to fall out.

In building my SQL's because I really have problems with SQL - I test them anywhere and everywhere to get them to work. Once I know I have something that works I implement it.

 

[mdmatiullah]

1. Indexing is required
2. Query is too slow because of linked table 'tbl_questionnair' in Access database, You should make a connection with Sql server instead of making a linked table.
I have faced this type of problem in linking DB2 table with access database.

Mati

 

[darbid]

My question here was about efficiency. I really cannot answer it. To solve my problem I have split my query up into 2 queries. 1 is the base query which is saved as a query and the second which is created in code based on the users choice. This seems to be working fast enough.