MS Access Table/Query Design/Best Practices

We have a database of approximately 500,000 customers.

We have 20 sales reps that cover the entire country. Their territories are defined by a range of zip codes with the following structure:

A particular sales reps records might look like this:

SalesTerritoryId      SalesId      BeginningZip        EndingZip
136                              84              90000                92199
137                              84              92200                92299
138                              84              92300                92599
139                              84              92600                92899
140                              84              93000                93199
141                              84              93200                93499

The sales reps can only see their customers in an Access database form using the following many to many query as the source for the form:

SELECT dbo_tblCustomer.*
FROM dbo_tblSalesTerritories, dbo_tblCustomer
WHERE (((dbo_tblCustomer.CustomerTypeId)=2 Or (dbo_tblCustomer.CustomerTypeId)=3 Or (dbo_tblCustomer.CustomerTypeId)=4 Or (dbo_tblCustomer.CustomerTypeId)=6) AND ((Left([Zip],5)) Between [dbo_tblSalesTerritories].[BeginningZip] And [dbo_tblSalesTerritories].[EndingZip]) AND ((dbo_tblSalesTerritories.SalesId)=[Forms]![frmMainMenu]![SalesId]));

This query is very slow to execute, particularly if the sales rep has a lot of zip code ranges in their territory. And the actual query includes some sub queries to lookup customer history which slows it down even further.

The front end database is Access 2003. The backend is MS SQL Server 2008.

Finally to my question. Is this the best way of structuring the data? If yes, is there a better method for querying the customer records for each sales rep? I thought of a pass-through query, but don't know how to pass the SalesId to the query.


Any help would be gratefully appreciated?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
A quick and dirty solution is to create a fake pass-through query that will act as the source...and make the SalesID a unique value (e.g : -99999) and then load the SQL of the source and substitute the unique value to your real SalesID...

something like this

Dim strSQL As String
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs(SourcePass)
strSQL = vbNullString
strSQL = qdf.SQL
strSQL = Replace(strSQL, fakeSalesID, SalesID)
set qdf = Nothing
Set qdf = CurrentDb.QueryDefs(DestinationPass)
qdf.SQL = strSQL
Set qdf = Nothing

Open in new window

If you need something better then some sample data are needed in order to check the table/query design ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The problem may be caused by the function used on the zip code.  Access does its best to "pass-through" all queries to the server for processing but since SQL Server doesn't recognize VBA or UDF functions, Access may be asking for all the data to be returned and then applying the where clause locally.  You should probably do some analysis on what Access is actually sending to the Server to see if that is the issue.  SSMS has tools that will trace this for you.  They are not normally turned on so your DBA might have to give you authority to use them or he might have to run the trace himself if he won't give you permission.

Another problem may be that even if there is an equivalent SQL function to get Left(...), the Zip index may not be able to be used since the lookup is on only part of it.  This again might be resulting in a full table scan.  Talk to your DBA and ask him what he thinks.  He will know if SQL Server can use an index if all you give it are the first 5 characters.  If the second problem is what is causing the slowdown, the solution would be to break the zip into zip and zip+4 so you don't need to use a function but you can use a non-equi-join instead.  The join would be faster.

Make sure you have indexes on the criteria fields.  In some cases where there is only a small set of discrete values as I suspect exists with CustomerTypeId, you might need to make a compound index with CustomerTypeId as the first column and the second column as the PK.  Having the index be unique will encourage the query engine to use it if possible.

You could ask the DBA to make a view that joins these two tables (assuming the join can be on a partial field) so you can query the view and just pass in the Customer types.

And finally, your join is non-specific.  If you ran this query against Jet/ACE tables, the result would be a Cartesian Product and a non-updateable recordset.  I'm pretty sure SQL Server doesn't care but you are making it figure out that it can do a join rather than telling it that is what you know is right and that might contribute to the time it takes to run the query.
I'm pretty sure your performance problem is caused by your form control reference.
I would change the reference to a parameter and simplify the CustomerTypeId match.
SELECT dbo_tblCustomer.*
FROM dbo_tblSalesTerritories, dbo_tblCustomer
  (dbo_tblCustomer.CustomerTypeId In (2,3,4,6))
  ((Left([Zip],5)) Between [dbo_tblSalesTerritories].[BeginningZip] And [dbo_tblSalesTerritories].[EndingZip]) 

Open in new window

If you can't pass a parameter or create a parameterized stored proc, you might have to use dynamic SQL and plug in the value of the SalesId in place of the parameter.

The other thing I would look at are the data types for the zip code fields.  If possible, you should try to do numeric comparisons whenever possible.  If you are working strictly inside the US, you can long integer (4 byte) fields in your SalesTerritories table.  Ideally, you would have a calculated field in the Customer table/view that would use the left five characters in the zip and cast that as a long integer (4 bytes).  In fact, I would suggest you evaluate that as a possible table join criterion between your two tables.
Gustav BrockCIOCommented:
Your main issue is ((Left([Zip],5)) which will force a full table scan of the customer table and make an index useless.
As this is a string, the Left is not needed, thus:

AND ([Zip] Between [dbo_tblSalesTerritories].[BeginningZip] And [dbo_tblSalesTerritories].[EndingZip])

I guess you already have an index on field Zip.

dwcummingsAuthor Commented:
Sorry that it took me so long to respond. As usual, something immediate took me away from this problem and I am now just getting back to it.

Thanks to all who commented. John's suggestion solved my problem and Pat's shortened the execution time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.