MS Access 2003 front end with SQL Backend - queries vs stored procedures

[Ksirr]

Hi All,

I would appreciate any help you are able to give.

My question is this: I have wriiten a telesales database for a company with 14 users. The front end is MS Access 2003 mdb with a SQL Server 2005 back end. The whole system works very well and has done for a number of years, but as the business increases in size, I now have an efficiency problem in one area.

The company buys leads from a source, sometimes 20,000 at a time. The database tables, currently have over 250,000 records in them. When new leads are supplied, the telephone number for each lead is checked against the existing telephone numbers in the database. If the telephone number of the new lead does not already exist in the database, it is then added to the table, together with other related data. If the telephone number already exists in the database, the telephone number and related data are added to a claim table, so that the cost of that lead can be reclaimed as they have already supplied that lead before.

The problem that I have is that the time it takes to check new leads with existing ones in the tables is becoming increasingly long as would be expected.

The checking is all done at present by running a query for each lead against the backend tables. Would this be better performed by a stored procedure on the sql server, and if so is it possible to pass the telephone number for each new lead to this stored procedure as a parameter, without sql server having to compile the query each time.

Many thanks

Eddie

 

[mdlueck]

You give no indication if the current design uses queries in Pass-Through mode, or if you are even worse off in having Access + SQL query optimizers thrash your queries.

Perhaps a better design would be to upload the list to a SQL Server "incoming" table, then have Stored Procedures process the incoming table comparison to historical tables. Finally return to Access only the result set of records you are interested in.

Always start by wiping the incoming table clean... SQL TRUNCATE.

 

[IsDon]

I'm by no means a real expert, but would having an Index on the telephone numbers allow you to go through the 2 lists in incrementing order on each, only comparing the current pair?

So,
no1=lowest to highest new number to check
no2 = lowest to highest existing numbers
while (no1 > no2) no2.next (quick check running up the index)
if no1==no2, its in already (skip?)
next no2
next no1

Hope that makes sense, it turns it into a straight check of 1 read per record, rather than however Access may be doing it already.

Don
have a great day