Need Vba Loop to replace in clause

[PapaG39]

I have a static zip code table that I want use with a vba loop instead of using a large in clause. I believe this will speed up the seek time for each zip code pair. Static Table has from: and to: zip bouncing off of another query searching for a match. I just need to make the static table populate the query one record at a time using a loop?

Thanks,

 

[kjv1611]

I'm not sure that a loop is really what you want, but we need more information to be totally sure as to what you're wanting to get in your final data result(s).

For isntance, WHY is it that you want to return the zip codes in pairs?

I guess, basically, what I want to know is - in the end, what/how are you wanting to return? In other words, are you spitting the data out to a user form, or to a report of some sort, or a query that gets exported out to an Excel worksheet, or what?

Generally speaking, if you can do the same thing in a SQL statement as you can in a Recordset loop via VBA, or any VBA loop, then it is just simply going to run faster. I know I've heard and/or read the whys on that, but I can't tell you right off hand at this particular moment.

 

[PapaG39]

kjv1611, thanks for the post. The task is easy to explain but the results are slow right now because of the in clause. I think! Think of a static table with every possible zip code pair in the United States. Now pretend that you have a customer file with start zip and stop zip. I want to bounce those start and stop zips off of the master table to get a millage result back. When I use an In clause it takes forever. So I thought I would try a loop using a function? Passing one record set at a time.

 

[SkipVought]

Do you mean by "start zip and stop zip"

'...
sSQL = sSQL & "Where ZIP >='" & [start zip] & "'" 
sSQL = sSQL & "  AND ZIP <='" & [stop zip] & "'"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Hmm, taking another stab at this from the SQL side... let's say you've got a range of the Zip codes... so you've got at least a range, if not a list.... you could create a temp table, feed that list into the temp table, and JOIN that table to your FROM table in your main SQL clause.

Of course, the quick and easy way for sure will be to set up the >= and <= conditions as Skip pointed out for sure..

Well, I've got another idea, but I am beginning to think that skips >= and <= critiera will work the best if it's a range.. otherwise, you'll spend resources building the other tables, queries, or whatever depending upon method chosesn... so regardless of the time the one query takes, the total time will likely be best with Skip's method.

 

[PapaG39]

SkipVought,

I have it set up right now were Table 1 has 2 zip code pairs in it. I then have a query that links Table 1 to the master zipcode table to pull in my results. It takes forever. I am open for any suggestions. Just as an fyi..I want to query thousands of zips so I thought some kind of loop would be needed or maybe you have another idea. I thought I could just link my table 1 to the master zip table but it is very slow when I do that.

Thanks,

 

[kjv1611]

Is there some way you can filter down your main table before hitting your "zip filter" against it?

Another possibility - are there any other joins in this query you're pulling, or are you only joining the 2 tables mentioned so far?

Anther question - are there any columns, whatsoever, that you can eliminate from the query results? If not, you could try pulling one query that compares against the joined zip filter table, (Table 1), and then another query that simply takes the IDs of those results, and grabs the other required fields from your main table.

And yet another thought - is your main/zip table normalized and indexed?

 

[SkipVought]

I understand what you previously stated.

This is a suggestion to SOLVE your problem.

Did you TRY it?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PapaG39]

Thanks guys I changed the way my query was indexed and made sure I got rid of extra columns with respect to many to many relationship. I had a one to many and it seemed to be a big part of my issue. Then I just added distinct to my results and it pulls fast and perfect. Thanks again. With all your ideas it made me think that this was not a loop that I needed.

 

[JoeAtWork]

With all your ideas it made me think that this was not a loop that I needed.

Looping and/or calling functions are almost always guaranteed to be slower than straight SQL, often by several magnitudes.