-
Aug 13th, 2013, 03:39 PM
#1
Thread Starter
Fanatic Member
Difference where query executes
What is the difference between running this statement
Code:
SELECT * FROM APHF01 WHERE (APHFChkNo = '111265') ORDER BY APHFSeqNo DESC;
from a VB6 app using DAO and running the same query in Microsoft Access?
-
Aug 14th, 2013, 09:09 AM
#2
Thread Starter
Fanatic Member
Re: Difference where query executes
The DAO operation is throwing a 'system resource exceeded' error. Running the query inside access does not?
-
Aug 14th, 2013, 11:38 AM
#3
Re: Difference where query executes
I don't know access bit maybe it is similar to what occurs in MS SQL. In native mode the query might finish but in the program times out. Maybe it is something similar.
-
Aug 14th, 2013, 12:13 PM
#4
Re: Difference where query executes
How big is your table? Do you have indexes setup? According to your query, you should have a composite index on 2 fileds (APHFChkNo, APHFSeqNo desc).
-
Aug 14th, 2013, 12:57 PM
#5
Re: Difference where query executes
Originally Posted by Serge
How big is your table? Do you have indexes setup? According to your query, you should have a composite index on 2 fileds (APHFChkNo, APHFSeqNo desc).
Could you explain your index suggestion? What good is an index on something in an order by? I'm basing that on the MS SQL logical query processing phases but am guessing access whould be similar.
-
Aug 14th, 2013, 01:46 PM
#6
Thread Starter
Fanatic Member
Re: Difference where query executes
Table APHF01 is approximately 113,000 rows, 19 columns and it looks like there 4 of the columns that say indexed = Yes.
-
Aug 14th, 2013, 01:50 PM
#7
Thread Starter
Fanatic Member
Re: Difference where query executes
APHFChkNo and APHFSeqNo are not among the indexed fields.
-
Aug 14th, 2013, 02:29 PM
#8
Re: Difference where query executes
Originally Posted by Serge
How big is your table? Do you have indexes setup? According to your query, you should have a composite index on 2 fileds (APHFChkNo, APHFSeqNo desc).
Hmmmm.... APHFChkNo , yes... APHFSeqNo ... not so much... even still, they should be indexed individually first then, indexed together... at any rate...
Originally Posted by TysonLPrice
Could you explain your index suggestion? What good is an index on something in an order by? I'm basing that on the MS SQL logical query processing phases but am guessing access whould be similar.
indexing on APHFSeqNo wouldn't help with the ordering unless it's a clustered index on that field, which, given what we (don't) know may not be the best... plus, he's ordering DESC, a clustered index would be ASC, so never mind....
Originally Posted by mojo69
Table APHF01 is approximately 113,000 rows, 19 columns and it looks like there 4 of the columns that say indexed = Yes.
Do you really need all 19 collumn? Odds are, you don't. Realistically, you should only select back the columns you're going to actually use... this is especially true if any fields are large... I mean LARGE... like large text fields, blobs, OLE fields, images, things of that nature.
Originally Posted by mojo69
APHFChkNo and APHFSeqNo are not among the indexed fields.
My thought would be that APHFChkNo should be indexed... if one of those other 4 fields that are indexed are clustered, that could also be an issue as it maybe interfering with your order by... if you take the order by out... does that change anything?
-tg
-
Aug 14th, 2013, 02:54 PM
#9
Thread Starter
Fanatic Member
Re: Difference where query executes
Largest column size is 32 characters.
When the customer is off the system tonight I am going to change the indexing. It will index only on RecordID and APHFChkNo. Two of the current fields that it is indexing I have no clue why someone would index them? I will check tomorrow to see if they are still receiving the message.
So if I can ask, if the table has indexed fields and I reuqest and ORDER BY that is different will it blow the query size way up?
Thanks to all!
-
Aug 14th, 2013, 03:09 PM
#10
Re: Difference where query executes
It doesn't blow the query size up... it just may tax the database system... something else to think about... don't sort it until you get your recordset back from the database... normally I'd like to sort in the database, but I deal with SQL Server which typically has no issue with this kind of thing... I'm thinking Access, being file-based, has more limitations.
-tg
-
Aug 14th, 2013, 03:24 PM
#11
Thread Starter
Fanatic Member
Re: Difference where query executes
That is interesting because I watch the size of .mdb bloating right after I run any query that might contain more than a couple thousand records. The database is only ~500MB, so it is not close to the 2GB limit for Access. I will have to review each and every table and sql statement again.
Thanks.
-
Aug 14th, 2013, 03:41 PM
#12
Re: Difference where query executes
Well, yes... that's a normal process of the query processing... the system (Access in this case) has to store the data somewhere so it can sort it... so it momentarily goes into a "temp" table ... that's the bloating you see... it's going to happen anytime there is a sort, index or not... sorting counter to an index though is more of a performance issue... it takes time to re-arrange the data... that's why I was wondering if it still happens if you take the order by out... or if you reduce the number of fields coming back. These are just some of the typical things I look at when trying to squeeze performance out of the query.
-tg
-
Aug 14th, 2013, 04:07 PM
#13
Thread Starter
Fanatic Member
Re: Difference where query executes
What is supposed to happen after it finished sorting things out? Should it go back to the prior size? A compact and repair is performed each evening which greatly reduces the size, sometimes 100+ MB difference. I have a feeling that somewhere there is some data that is corrupt and causing issues. I am definitely going to try your suggestions though.
-
Aug 14th, 2013, 07:41 PM
#14
Re: Difference where query executes
Nothing happens to the size when it's done... it stays allocated to the file in case it needs it again... if you're data becomes corrupted, YOU WILL KNOW, since Access will stop working... That shrinkage you see is basically the log tables being flushed... so that's expected.
-tg
-
Aug 15th, 2013, 04:29 AM
#15
Re: Difference where query executes
indexing on APHFSeqNo wouldn't help with the ordering unless it's a clustered index on that field
Am I missing something here? My understanding has always been that an index (clustered or otherwise) can support ordering as it will naturally return the records in the right order. In this case I'd have suggested a two column index with APHFChkNo first to support the where clause and APHFSeqNo second to provide a natural ordering to return the records in. Is that wrong?
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 15th, 2013, 08:03 AM
#16
Re: Difference where query executes
I didn't think so... personal experience had me thinking that it wouldn't... but I couldn't be sure since 99% of the time I'm dealing with tables that have a clustered index of some kind... so I wasn't completely sure...
so to be fair, I ran a quick test.... here's the script:
SQL Code:
create table dbo.testTable ( FName nvarchar(10), LName nvarchar(20), ID int ) go create index idx_TestTable_ID on dbo.testTable (ID) go insert into testTable values ('A','B',1) insert into testTable values ('C','D',3) insert into testTable values ('E','F',5) insert into testTable values ('G','H',9) insert into testTable values ('I','J',2) insert into testTable values ('K','L',6) go select 'unordered index', * from dbo.testTable -- unordered index go drop index idx_TestTable_ID on dbo.testTable go create index idx_TestTable_ID on dbo.testTable (ID desc) go select 'ordered index desc',* from dbo.testTable -- ordered index desc go drop index idx_TestTable_ID on dbo.testTable go create index idx_TestTable_ID on dbo.testTable (ID asc) go select 'ordered index asc', * from dbo.testTable -- ordered index asc go drop index idx_TestTable_ID on dbo.testTable go create clustered index idx_TestTable_ID on dbo.testTable (ID) go select 'clustered index', * from dbo.testTable -- clustered index go drop index idx_TestTable_ID on dbo.testTable go create clustered index idx_TestTable_ID on dbo.testTable (ID desc) go select 'clustered index desc', * from dbo.testTable -- clustered index desc go drop index idx_TestTable_ID on dbo.testTable go drop table dbo.testTable go
And here's the results:
Code:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
FName LName ID
--------------- ---------- -------------------- -----------
unordered index A B 1
unordered index C D 3
unordered index E F 5
unordered index G H 9
unordered index I J 2
unordered index K L 6
(6 row(s) affected)
FName LName ID
------------------ ---------- -------------------- -----------
ordered index desc A B 1
ordered index desc C D 3
ordered index desc E F 5
ordered index desc G H 9
ordered index desc I J 2
ordered index desc K L 6
(6 row(s) affected)
FName LName ID
----------------- ---------- -------------------- -----------
ordered index asc A B 1
ordered index asc C D 3
ordered index asc E F 5
ordered index asc G H 9
ordered index asc I J 2
ordered index asc K L 6
(6 row(s) affected)
FName LName ID
--------------- ---------- -------------------- -----------
clustered index A B 1
clustered index I J 2
clustered index C D 3
clustered index E F 5
clustered index K L 6
clustered index G H 9
(6 row(s) affected)
FName LName ID
-------------------- ---------- -------------------- -----------
clustered index desc G H 9
clustered index desc K L 6
clustered index desc E F 5
clustered index desc C D 3
clustered index desc I J 2
clustered index desc A B 1
(6 row(s) affected)
As you can see, the ONLY indexes that affected the natural sorting were the two clustered indexes. So here's the rub... he COULD set a clustered index on the sorting field, but that's going to affect inserts and searches where that field ISN'T part of the lookup... It would be a very bad idea to force a clustered index jsut to get things to "sort" right... Something to keep in mind, clustered indexes actually PHYSICALLY REARRANGE the rows in a table. Indexing the sort column isn't going to hurt... it's just not going to help either.
In the mean time, given that the OP is also concerned about corruption and size... maybe an Access database isn't the way to go, and he should consider SQL Server or MySQL or Oracle even.
-tg
-
Aug 16th, 2013, 11:26 AM
#17
Re: Difference where query executes
TG, I think my use of "natural order" was a bad one because it implied I was talking about the order the record would be returned in if no Order by were included. I meant the order the records would be returned from a given index in which is a slightly different issue. I don't want to risk derailing this thread so I've created a new one here that should clarify what I mean.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 22nd, 2013, 02:13 PM
#18
Re: Difference where query executes
Indexes are also used in ORDER BY clause. Since he is using 1 column in WHERE and another in ORDER BY, he should have a composite index on both columns. When indexing column(s) you also specify direction (Asc, Desc). As far as clustered index, you can only have 1 per table and that index is usual used by Primary Key (at least it is by default in SQL Server when using designer).
http://technet.microsoft.com/en-us/l...=sql.105).aspx
From MS:
Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index.
Last edited by Serge; Aug 22nd, 2013 at 02:28 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|