If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Multiple indexes on same fields in different order
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...1-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. . |
#3
|
|||
|
|||
Multiple indexes on same fields in different order
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" píše v diskusním příspěvku ... 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...1-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. . |
#4
|
|||
|
|||
Multiple indexes on same fields in different order
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" pĂ*še v diskusnĂ*m pøĂ*spĂŹvku ... 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...1-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. . . |
#5
|
|||
|
|||
Multiple indexes on same fields in different order
Okay, I'll try that.
"Jerry Whittle" píše v diskusním příspěvku ... 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" pí1e v diskusním poíspivku ... 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...1-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. . . |
Thread Tools | |
Display Modes | |
|
|