Multiple indexes on same fields in different order

I sometimes need to find the largest or smallest value in a field. If I put 
two indexes on the same field, one in ascending order and one in descending 
order, is Jet smart enough to use the proper index if a give it a grouping 
query with the condition Max or Min, or am I just wasting resources?

Pete


-- 
This e-mail address is fake, to keep spammers and their address harvesters 
out of my hair. If you want to get in touch personally, I am 'pdanes' and I 
use yahoo mail. But please use the newsgroup when possible, so that all may 
benefit from the exchange of ideas.


0
Petr
5/13/2010 4:48:03 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1010 Views

Similar Articles

[PageSpeed] 4

How many records are you talking about? If it's less than ten thousand, it 
probably doesn't matter.

You could get out a stopwatch and test which works faster.

Then there's always Showplan which will tell you what index, if any, that is 
being used by a query.

http://articles.techrepublic.com.com/5100-10878_11-5064388.html
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Petr Danes" wrote:

> I sometimes need to find the largest or smallest value in a field. If I put 
> two indexes on the same field, one in ascending order and one in descending 
> order, is Jet smart enough to use the proper index if a give it a grouping 
> query with the condition Max or Min, or am I just wasting resources?
> 
> Pete
> 
> 
> -- 
> This e-mail address is fake, to keep spammers and their address harvesters 
> out of my hair. If you want to get in touch personally, I am 'pdanes' and I 
> use yahoo mail. But please use the newsgroup when possible, so that all may 
> benefit from the exchange of ideas.
> 
> 
> .
> 
0
Utf
5/13/2010 5:58:03 PM
Around 80,000. I tried the ShowPlan bit a few days ago, but wasn't able to 
find the file that it supposedly creates, so I probably did something wrong.

Also, I'm running this split over a network, with Access for both frontend 
and backend, so if I put a proper index on the backend, it might make a 
sizable difference in how fast it gets something from an extreme end, no?

Pete



"Jerry Whittle" <JerryWhittle@discussions.microsoft.com> p�e v diskusn�m 
p��sp�vku news:81290248-E199-44F0-8DAA-E1763969621C@microsoft.com...
> How many records are you talking about? If it's less than ten thousand, it
> probably doesn't matter.
>
> You could get out a stopwatch and test which works faster.
>
> Then there's always Showplan which will tell you what index, if any, that 
> is
> being used by a query.
>
> http://articles.techrepublic.com.com/5100-10878_11-5064388.html
> -- 
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Petr Danes" wrote:
>
>> I sometimes need to find the largest or smallest value in a field. If I 
>> put
>> two indexes on the same field, one in ascending order and one in 
>> descending
>> order, is Jet smart enough to use the proper index if a give it a 
>> grouping
>> query with the condition Max or Min, or am I just wasting resources?
>>
>> Pete
>>
>>
>> -- 
>> This e-mail address is fake, to keep spammers and their address 
>> harvesters
>> out of my hair. If you want to get in touch personally, I am 'pdanes' and 
>> I
>> use yahoo mail. But please use the newsgroup when possible, so that all 
>> may
>> benefit from the exchange of ideas.
>>
>>
>> .
>> 


0
Petr
5/13/2010 6:55:50 PM
Your best bet is to create the indexes and get out a stopwatch. See if it 
makes a difference. Tip: Time the second or third execution of the query as 
Access often takes extra time to optimize the query in memory on the first 
run.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Petr Danes" wrote:

> Around 80,000. I tried the ShowPlan bit a few days ago, but wasn't able to 
> find the file that it supposedly creates, so I probably did something wrong.
> 
> Also, I'm running this split over a network, with Access for both frontend 
> and backend, so if I put a proper index on the backend, it might make a 
> sizable difference in how fast it gets something from an extreme end, no?
> 
> Pete
> 
> 
> 
> "Jerry Whittle" <JerryWhittle@discussions.microsoft.com> pí¹e v diskusním 
> pøíspìvku news:81290248-E199-44F0-8DAA-E1763969621C@microsoft.com...
> > How many records are you talking about? If it's less than ten thousand, it
> > probably doesn't matter.
> >
> > You could get out a stopwatch and test which works faster.
> >
> > Then there's always Showplan which will tell you what index, if any, that 
> > is
> > being used by a query.
> >
> > http://articles.techrepublic.com.com/5100-10878_11-5064388.html
> > -- 
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> > "Petr Danes" wrote:
> >
> >> I sometimes need to find the largest or smallest value in a field. If I 
> >> put
> >> two indexes on the same field, one in ascending order and one in 
> >> descending
> >> order, is Jet smart enough to use the proper index if a give it a 
> >> grouping
> >> query with the condition Max or Min, or am I just wasting resources?
> >>
> >> Pete
> >>
> >>
> >> -- 
> >> This e-mail address is fake, to keep spammers and their address 
> >> harvesters
> >> out of my hair. If you want to get in touch personally, I am 'pdanes' and 
> >> I
> >> use yahoo mail. But please use the newsgroup when possible, so that all 
> >> may
> >> benefit from the exchange of ideas.
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
5/14/2010 2:16:08 PM
Okay, I'll try that.


"Jerry Whittle" <JerryWhittle@discussions.microsoft.com> p�e v diskusn�m 
p��sp�vku news:19D3C5AD-AA18-4D3C-8B0E-CA2F6FAFE58C@microsoft.com...
> Your best bet is to create the indexes and get out a stopwatch. See if it
> makes a difference. Tip: Time the second or third execution of the query 
> as
> Access often takes extra time to optimize the query in memory on the first
> run.
> -- 
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Petr Danes" wrote:
>
>> Around 80,000. I tried the ShowPlan bit a few days ago, but wasn't able 
>> to
>> find the file that it supposedly creates, so I probably did something 
>> wrong.
>>
>> Also, I'm running this split over a network, with Access for both 
>> frontend
>> and backend, so if I put a proper index on the backend, it might make a
>> sizable difference in how fast it gets something from an extreme end, no?
>>
>> Pete
>>
>>
>>
>> "Jerry Whittle" <JerryWhittle@discussions.microsoft.com> p�1e v diskusn�m
>> po�spivku news:81290248-E199-44F0-8DAA-E1763969621C@microsoft.com...
>> > How many records are you talking about? If it's less than ten thousand, 
>> > it
>> > probably doesn't matter.
>> >
>> > You could get out a stopwatch and test which works faster.
>> >
>> > Then there's always Showplan which will tell you what index, if any, 
>> > that
>> > is
>> > being used by a query.
>> >
>> > http://articles.techrepublic.com.com/5100-10878_11-5064388.html
>> > -- 
>> > Jerry Whittle, Microsoft Access MVP
>> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>> >
>> >
>> > "Petr Danes" wrote:
>> >
>> >> I sometimes need to find the largest or smallest value in a field. If 
>> >> I
>> >> put
>> >> two indexes on the same field, one in ascending order and one in
>> >> descending
>> >> order, is Jet smart enough to use the proper index if a give it a
>> >> grouping
>> >> query with the condition Max or Min, or am I just wasting resources?
>> >>
>> >> Pete
>> >>
>> >>
>> >> -- 
>> >> This e-mail address is fake, to keep spammers and their address
>> >> harvesters
>> >> out of my hair. If you want to get in touch personally, I am 'pdanes' 
>> >> and
>> >> I
>> >> use yahoo mail. But please use the newsgroup when possible, so that 
>> >> all
>> >> may
>> >> benefit from the exchange of ideas.
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
Petr
5/15/2010 7:44:23 AM
Reply:

Similar Artilces:

Filter on Combobox Field in Data Project
I designed a Data Project that uses a SQL Server back-end and an Access 2007 front end. Many of the forms include Comboboxes for Lookup values. These forms are in Datasheet view. The Users need to filter the records by the displayed value, but the autofilter drop-down forms do not detect the values. This feature worked in ACCDB files. How can I get this to work with ADP/ADE files? My users are all running Windows XP Professional on a corporate network. Thanks, Matthew Pfluger ...

multiple NTDS Settings
to whom it may concern: is it possible to have more than 1 NTDS Settings in 1 DC? any effect on this? thank you. On Fri, 28 Apr 2006 19:14:31 +0800, "sherwin de claro" <sherwin.de.claro@gmail.com> wrote: >to whom it may concern: > >is it possible to have more than 1 NTDS Settings in 1 DC? > >any effect on this? > > >thank you. > What do you mean here? If you're talking about AD Sites and Services then no, only one. You can have many replication conenctions under that, but only one "NTDS Settings" under the DC. yes its on AD Si...

Invoicing select lines (or "releases") from a Sales Order
In Great Plains 7.5... I'm trying to choose the best way to handle a very common business transaction we encounter with sales orders having multiple releases. For example, a customer orders 300 Widgets as 3 lines of 100 widgets each. Each line has a different delivery due date, such as 100 pcs Feb 1, 100 pcs Mar 1, and 100 pcs Apr 1. The problem happens when we go to "transfer to invoice" for the individual releases. We want a clear for the invoice to be only for a particular release, and for it to be clear in the original order as to what lines still remain to be s...

Access 2000
Hello all, I have a field named Field213 that holds (CASETYPE) from the database. When I open the form, I want it to look at that field and make field163 visible depending on what it stored in Field213. Right now it only becomes visible if I manually change what is in field213, then field163 becomes visible. I should mention that I have field163 as visible no and display always, the same as field PBISUB, Field382 and Field163. Probably something simple, but I can't seem to see it right now. I have the following: Private Sub Field213_BeforeUpdate(iC As Integer) If Me!CASETYPE = &qu...

too many different formatting cells
I can't open an excel document because whem I try to open it says that too many different formatting cells. How to resolve this and open this document? Maybe... XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have said that OpenOffice.Org has been able to open the file. Then they clean it up and save it there. Then excel can open that cleaned up version. http://www.openoffice.org, a 60-104 meg download or a CD jo wrote: > > I can't open an excel document because whem I try to open it says that too >...

Prevent multiple entries in a month
I have a stored procedure that inserts note records and if a value > 0 is set in the @Units parameter AND the @ActivityCode sent = 64 then it looks to see if it should add a new ActivityHistory record and a ClientCaseNotes record. It is suppose to not create more than 1 record if there is already a record for a linked person (PeopleLinkID) for that same month. Below is the portion of the stored procedure where this is occurring. Note the IF @ActivityCode = 64 and NOT EXISTS .... section is as that is where it is failing. It creates 2 records for a month for 1 person and Act...

Manager Assist ...... Field Service Module
Hi to all, I wnat to ask about Manager Assist, that is used to manage the calls on the service call module...... is it a GP module or it is a separate software thank you in advance, ManageAssist is a seperate VB application that connects to the GP Field Service tables. It allows for both viewing of schedule calls as well as updating of certain records (i.e. Unavailable time, sick time, etc.) -- Gary Neumann Field Service Series Designer/Architect "Eng. Tareq Sabri" wrote: > Hi to all, > > I wnat to ask about Manager Assist, that is used to manage the calls on th...

Big difference in performance
I wrote a class on which I perform unit-testing before integration in the main development line, the unit-testing and main dev being two separate projects. I wrote a routine which constructs a data structure in the class, which uses CArrays, vectors, and the nth_element routine from STL, with the routine being recursive. Now, in the unit-test project, the routine takes ~3-4 seconds to execute. In the main development code, it takes ~70 seconds to execute. This is measured for the routine itself (no setup or cleanup), same data as input, same computer, and both projects compiled for debug. ...

Edit the Auto Fill in the To: field
On the Send new message window, is there a way to edit the auto fill list for the To: Field. when i start to type in a name in the To Field the auto fill will provide a list of names to choose from. the one i most use is on the bottom of the list. How do i edit the list to get the one i use most to the top? Do you use the others as well? Editing the list will remove them from the list. If you again use the names, they will be added back to the same position. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-...

Multi-field search form
Hi there, I am rather new to this place, so I would like to thank you all up front for any and all assistance you may provide. :) It has been a while since I worked with MS Access last. What I am trying to do is create a multi-field search form, where any of the fields can be filled which will display a result set. Preferably if no selection in the fields is made, the entire resultset should be shown - this is not a requirement, though. I seem to be a bit at a loss on how to procede with this task or to find any resources to help me get along. Again, thanks for any and ...

display the value of an unbound textbox in a field within a table
I created 3 textboxs to calculate the number of business days between 2 dates. The 3rd textbox contains the value. I now want to have this value displayed in a table field. I'm sure this is easy but I'm a complete novice and have spent far too much time on this already. Can anyone help! Fibi, The general concept of tables is that they are for storage of data in the background. They are not for display of data. That is what forms and reports are for. Therefore, the appearance of the data in the tables is not normally relevant, and the display of calculated values is im...

MATCH INDEX MIN and 2nd MIN
I have 8 suppliers with their prices listed in columns beneath their names. With the help of many in here, I can achieve finding out who is the minimum supplier and who is the 2nd minimum supplier. Suppose all the suppliers are listed in columns D to K and some suppliers haven't quoted for all parts listed and in certain sections I have 2 suppliers that did not submit prices for any parts and have zero values right down their list of prices. To obtain the minimum supplier, I have used the following formula =INDEX($D$1:$K$1,1,MATCH(V2,$D$2:$K$2,0)) where V2 contains the minimum val...

Difference between XML templates and Active Reports
Why does RMS use XML templates (for receipts and P.O.s) and for most other reports Active Reports? I realize that the XML receipts can not be changed or resorted on the preview screen, but why use not all active reports? Active reports are based on formating the results of a SQL Query - if you can work out the SQL to get the result set you want, you can create almost any report you want. The XML Templates are based on objects internal to RMS, like the current transaction for reciept printing, the active PO or Transfer request for PO prining, or a given customer for Statements. Y...

Multiple problems with WLM
Multiple problems with WLM I realize that this problems list is long enough that it's unlikely that just one person will be able to solve them all. Therefore, it's OK to reply only for the one you can handle. Since some of these problems may be related, I decided to list them all in one post. 1. The All news view in the Quick views is either mislabelled, or unable to handle very large collections of unread newsgroups posts (I first saw this problem when the count was over 1,000,000, WLM was restarted, and the count dropped much lower (under 50,000). Since then I&#...

data entry on multiple worksheets
I have a book with one master worksheet and then several other worksheets with data from the master. Is it possible to have the data I enter or update on the master to automatically populate the correct information into the other worksheets, even though the other worksheets store the data on different rows than the master? Hi you could use cell links such as ='master sheet'!A1 in your target cells -- Regards Frank Kabel Frankfurt, Germany "diosdias" <diosdias@discussions.microsoft.com> schrieb im Newsbeitrag news:A674ACB3-C5F7-477D-954E-561B85FF705E@microsoft.co...

how field name output same as input
Thanks for any help. I have a query from a couple tables, and one of the field names in the table is 'IGP' and I have a field in the query that was IGP and now I changed to IGP: iif(blah IGP blah) which gives me a circular error. The table is used by a bunch of other things, so I don't want to change the field name there, and the field name for the output is used by a bunch of things, so nor do I want to change the name there. I am using Access 2007. I thought about changing the output field to IGP_, and I could change the embedded queries in Excel that use it, but...

passing non-field value into vba
I need to pass the line item sequence number (lnitmseq) from the sales item detail entry window into vba. This isnt a visible field in the window, so i cannot "add field to vba" using the TOOLS --> customize toolbar window. Anyone have any idea how this can be done? Using the Modifier you can make the field visible, add it to VBA and then remove the modified window or make the field invisible again. While in the Window Layout mode, make sure that the Invisible fields are shown (Layout >> Show Invisible Fields) and then change the Visual Property, Visible = true and mov...

multiple fies updating one file
Hi, How would I setup an excel sheet that needs to collect numbers from other excel sheets. The rows and fields numbers will keep growing. I know how to reference a cell in a file but not sure how you can quickly create a reference to a range of cells and how about when the range keeps growing. Thank you Hi Vic; You can select and copy the range you want to link, then go to then go to the receiving sheet and click the top right cell. Click Edit / Paste Special and select Paste Link. This will link the entire range. If you happen to be running Office 2007 you can right click into the ...

Derived Field
I'd like to add a derived field to this situation: Parent Table : Shipments (pk)Shipment_No Shipment_Date Shipment_Size ....other fields... Child Table : Shipment_Inspection Shipment_No (connected field) Number_Out_Tolerance (bad ones in the shipment) ....other fields... I'd like to make a derived field "der_percent_good" that takes the shipment size that the inspection belongs to and does (1 - Number_Out_Tolerance/Shipment_Size)*100 to find the percent of the shipment that is Good. Thanks! ...

Date/Time field calculation
Hello, i am trying to make a sum calculation on a field that keeps duration of time. How can i do this? I get errors or zero as result. Thanks On Jun 21, 10:02 am, Stathis <s_paraske...@hotmail.com> wrote: > Hello, > i am trying to make a sum calculation on a field that keeps duration of > time. How can i do this? I get errors or zero as result. > Thanks You need to do the calculation at a level at least 1 level below what you want the answer to be in. Example msgbox DateDiff("h", #6/20/2007 1:00:00 AM#, #6/20/2007 1:30:00 PM#) will give an ans...

Rule not matching "from" field
I want to set up a rule that will match words in the "From" field. Sometimes I get mail like this: From: bill@somedomain.com [mailer@someotherdomain.com] I want to match "bill@somedomain.com". I can't figure out how to match it. I can't pick "from people or distribution list" because it won't let me match arbitrary email addresses. I can't pick "with specific words in the sender's address" it just doesn't work. So far as I can tell, it will only match the part in the brackets. Anon E. Mouse <fake@fake.com> wrote: &...

PA Cost Categories should allow multiple transaction usage types
When creating new cost categories, in a real life project accounting or job cost environment, most clients don't want all their cost categories to be different. You should be able to say when setting up a cost category that you want that cost category to be able to be used as a Timesheet AND Purchases/Materials, you should be able to pick one or many of the usage types. I currently have a client that had to implement 2 million cost categories to get around this product limitation and there is not one good business reason why the software wouldn't allow this! Please place this...

Timeout issues on Sales Orders with many Products
Hi all, I'm experiencing timeout issues with Sales Orders that have 25-30 products on the Order Detail level. I haven't found any documentation on this particular issue. Has any ever seen this before? I'm a little loathe to start reindexing the tables, especially considering the SalesOrderDetail table has less than 300 rows. -Luke ...

Multiple e-mail addresses #3
When a Contact has multiple e-mail addresses, how do I set one of them as a default? Now, emails are going to ALL of their addresses. Thanks-Rose ...

multiple accounts at a single bank
Can anyone help me set up downloads for multiple accounts at a single institution? When I change account numbers, it deletes the previous account. Thanks In microsoft.public.money, MichaelG wrote: >Can anyone help me set up downloads for multiple accounts >at a single institution? When I change account numbers, >it deletes the previous account. Thanks In Help Ask Money box enter "two accounts". ...