SQL guys

[EdFred]

What would run quicker?

Using lookup or Dlookup into another table that isn't dynamically opened yet to grab one field Or doing a case select.

I need to run a few billion cycles of code, and any sort of time trimming could save me a lot of time.

 

[ggroves]

Select

If you could post an example, that would be helpful to further qualify my answer.

Greg

 

[Greebo]

Assuming you have table A and want to populate a field in A from a lookup table B, based on a 2nd field in A...

An inner join:

update a
set a.f1 = b.lookupid
from a join b on a.f2 = b.value

Is that what you're trying to do?

 

[EdFred]

I have a table called MyData that I will select the Top 2-10 records in that table based on the city.
For Atlanta I may want to see 2 records
For Nashville it might be 5 records
For St Paul it could be 3 records

Most cities I only want to see 2 records. (There are about 2500 records in the table called MyData)

For each record I open in that query, I will repeat what I just did again.

I can do a Select Case where I can call out in code (I'm working in Access)

Select case City
Case "Atlanta", "Raleigh", "Lansing"

strSQL = "SELECT TOP 2 FROM MyData......"

​

Case "Nashville"

strSQL = "SELECT TOP 5 FROM MyData......"


​

Case "St. Paul"

strSQL = "SELECT TOP 3 FROM MyData......"



​

End Select

Or, I can do something like:
strSql = "SELECT TOP " & DLookup("Number", "Cities", "City = 'myCity'") & " FROM MyData...


The Table Cities would have the info for my Top 2-10, but that info is not in Table MyData

Once it runs through all the iterations it writes to a 3rd table.

Which runs quicker?

 

[Greebo]

Probably the case statement will run faster, but is much less flexible and should be avoided because its very rigid and the data sounds somewhat dynamic.

I'm thinking there is an alternative, however, I need to know the database platform. Is this in SQL Server?

NEVER MIND, I see you're in Access.

Let me think on this for a few...

 

[EdFred]

Like I said Chuck, it runs mostly 2 record for most all cities, and only 3 for a handful, and 5 for 1 or 2 cities. So I'm not worried about the constraints or rigidity.

 

[Greebo]

Well then I'd go with the case statement, because while the case will have to be determined on every row (if I understand you) the dlookup will have to do a table lookup for each and every row processed. Table lookups are almost always slower than in memory decisions.

 

[EdFred]

That's what I thought, but I had to get some backup to convince someone otherwise.

 

[TangoWhiskey]

That's what I thought, but I had to get some backup to convince someone otherwise.

Run a test benchmark. Record system time at start and finish of both methods. Numbers are the best "backup to convince someone".

 

[EdFred]

Run a test benchmark. Record system time at start and finish of both methods. Numbers are the best "backup to convince someone".

That means I would actually have to make the changes to the original table and then write the necessary code. I already have the CASE done.

 

[TangoWhiskey]

That means I would actually have to make the changes to the original table and then write the necessary code. I already have the CASE done.

Understood. Is this something you're running once, ad-hoc, or will run lots of times? That might factor in on your choice to benchmark or not. If you're just going to run this once, and the speed difference is negligible, do whatever is easiest for you to write/debug/understand later. If you're going to run this as a scheduled job every xx hours/days, then you might consider taking the time and effort to tweak it.

Usually, on lookups like this, the biggest speed improvement you'll get is from having a clustered index on the lookup table on the column(s) that are used in your WHERE clause or JOIN of the lookup.