Results 1 to 18 of 18

Thread: Difference where query executes

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    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?

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: Difference where query executes

    The DAO operation is throwing a 'system resource exceeded' error. Running the query inside access does not?

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    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.

  4. #4
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    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).

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Difference where query executes

    Quote Originally Posted by Serge View Post
    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.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    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.

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    Re: Difference where query executes

    APHFChkNo and APHFSeqNo are not among the indexed fields.

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Difference where query executes

    Quote Originally Posted by Serge View Post
    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...

    Quote Originally Posted by TysonLPrice View Post
    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....

    Quote Originally Posted by mojo69 View Post
    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.

    Quote Originally Posted by mojo69 View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    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!

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    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.

  12. #12
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Wisconsin
    Posts
    788

    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.

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  16. #16
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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:
    1. create table dbo.testTable (
    2. FName nvarchar(10),
    3. LName nvarchar(20),
    4. ID int
    5. )
    6. go
    7. create index idx_TestTable_ID on dbo.testTable (ID)
    8. go
    9. insert into testTable values ('A','B',1)
    10. insert into testTable values ('C','D',3)
    11. insert into testTable values ('E','F',5)
    12. insert into testTable values ('G','H',9)
    13. insert into testTable values ('I','J',2)
    14. insert into testTable values ('K','L',6)
    15. go
    16.  
    17. select 'unordered index', * from dbo.testTable -- unordered index
    18. go
    19. drop index idx_TestTable_ID on dbo.testTable
    20. go
    21. create index idx_TestTable_ID on dbo.testTable (ID desc)
    22. go
    23. select 'ordered index desc',* from dbo.testTable -- ordered index desc
    24. go
    25. drop index idx_TestTable_ID on dbo.testTable
    26. go
    27. create index idx_TestTable_ID on dbo.testTable (ID asc)
    28. go
    29. select 'ordered index asc', * from dbo.testTable -- ordered index asc
    30. go
    31. drop index idx_TestTable_ID on dbo.testTable
    32. go
    33. create clustered index idx_TestTable_ID on dbo.testTable (ID)
    34. go
    35. select 'clustered index', * from dbo.testTable -- clustered index
    36. go
    37. drop index idx_TestTable_ID on dbo.testTable
    38. go
    39. create clustered index idx_TestTable_ID on dbo.testTable (ID desc)
    40. go
    41. select 'clustered index desc', * from dbo.testTable -- clustered index desc
    42. go
    43. drop index idx_TestTable_ID on dbo.testTable
    44. go
    45. drop table dbo.testTable
    46. 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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  17. #17
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  18. #18
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    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
  •  



Click Here to Expand Forum to Full Width