Slow SQL Code on local server in MS Access

Question

Hi to all, I've got a particular SQL statement which takes about 30 seconds to perform, and I'm wondering if anyone can see a problem with it, or where I need additional indexing.

The code is on a subform in Access, which shows results dependent on the content of five fields in the master form. There are nearly 5000 records in the table that's being queried. The Access project is stored and run from a terminal server session on the actual SQL server, so I don't think it's a network issue, and there's another form which is very similar that uses the same type of querying...

Thanks

PG

SELECT TabDrawer.DrawerName, TabDrawer.DrawerSortCode, TabDrawer.DrawerAccountNo, TabDrawer.DrawerPostCode, QryAllTransactons.TPCChequeNumber, tabdrawer.drawerref
FROM TabDrawer LEFT JOIN QryAllTransactons ON  TabDrawer.DrawerRef=QryAllTransactons.tpcdrawer
WHERE (Forms!FrmSearchCompany!SearchName Is Null 
      Or [drawername] Like Forms!FrmSearchCompany!SearchName & "*") 
      And (Forms!FrmSearchCompany.SearchPostcode Is Null 
      Or [Drawerpostcode] Like Forms!FrmSearchCompany!Searchpostcode & "*") 
      And (Forms!FrmSearchCompany!SearchSortCode Is Null 
      Or [drawersortcode] Like Forms!FrmSearchCompany!Searchsortcode & "*") 
      And (Forms!FrmSearchCompany!Searchaccount Is Null 
      Or [draweraccountno] Like Forms!FrmSearchCompany!Searchaccount & "*") 
      And (Forms!FrmSearchCompany!Searchcheque Is Null 
      Or [tpcchequenumber] Like Forms!FrmSearchCompany!Searchcheque & "*");
    ");

---

EDIT

The Hold up seems to be in the union query that forms the QryAllTransactons query.

SELECT 
  "TPC" AS Type, 
  TabTPC.TPCRef, 
  TabTPC.TPCBranch, 
  TabTPC.TPCDate, 
  TabTPC.TPCChequeNumber, 
  TabTPC.TPCChequeValue, 
  TabTPC.TPCFee, 
  TabTPC.TPCAction,
  TabTPC.TPCMember, 
  tabtpc.tpcdrawer,
  TabTPC.TPCUser,
  TabTPC.TPCDiscount,
  tabcustomers.*
FROM 
  TabTPC 
  INNER JOIN TabCustomers ON TabTPC.TPCMember = TabCustomers.CustomerID

UNION ALL 

SELECT 
 "CTP" AS Type, 
  TabCTP.CTPRef, 
  TabCTP.CTPBranch, 
  TabCTP.CTPDate, 
  TabCTP.CTPChequeNumb, 
  TabCTP.CTPAmount, 
  TabCTP.CTPFee, 
  TabCTP.CTPAction,
  TabCTP.CTPMember,
  0 as CTPXXX,
  TabCTP.CTPUser,
  TabCTP.CTPDiscount, 
  TABCUSTOMERS.*
FROM 
  TabCTP  
  INNER JOIN TabCustomers ON Tabctp.ctpMember = TabCustomers.CustomerID;

I've done a fair bit of work with simple union queries, but never had this before...

Answer 1

At first, try compacting and repairing the .mdb file.

Then, simplify your WHERE clause:

WHERE
  [drawername] Like Nz(Forms!FrmSearchCompany!SearchName, "") & "*"
  And 
  [Drawerpostcode] Like Nz(Forms!FrmSearchCompany!Searchpostcode, "") & "*"
  And 
  [drawersortcode] Like Nz(Forms!FrmSearchCompany!Searchsortcode, "") & "*"
  And 
  [draweraccountno] Like Nz(Forms!FrmSearchCompany!Searchaccount, "") & "*"
  And 
  [tpcchequenumber] Like Nz(Forms!FrmSearchCompany!Searchcheque, "") & "*"

Does it still run slowly?

EDIT

As it turned out, the question was not clear in that it is an up-sized Access Database with an SQL Server back end-and an Access Project front-end.

This sheds a different light on the whole problem.

Can you explain in more detail how this whole query is intended to be used?

If you use it to populate the RecordSource of some Form or Report, I think you will be able to refactor the whole thing like this:

• make a view on the SQL server that returns the right data
• query that view with a SQL server syntax, not with Access syntax
• let the server sort it out

Answer 2

Thanks for that - it hasn't made a difference to the speed of the query, nor has doing the compact and repair. I've been doing some more investigation, and have taken the wildcards out to see if that speeds it up. It's now returning a very quick result if 0 records are found, but it's taking just as long to display the query results, even when I use an exact company name that only matches one record!

This is only happening since the database was upsized over the weekend, so is there something I need to toggle on the SQL server for this table to speed things up?

Answer 3

How many rows are in QryAllTransactons?

If your result returns 0 rows then Access may be able to see that immediately and stop, but if it returns even a single row then it needs to pull in the entire resultset of QryAllTransactons so that it can do the join internally. That would be my first guess as to what is happening.

Your best bet it usually to do joins on SQL Server. Try creating a view that does the LEFT OUTER JOIN and query against that.

Your goal, even when Access is running on the SQL Server itself and minimizes network traffic, is to only send to Access what it absolutely needs. Otherwise a large table will still take up memory, etc.

Answer 4

Have you tried running each of the subqueries in the union? Usually optimizers don't spend much time trying to inspect efficiencies between union elements - each one runs on its own merits.

Given that fact, you could also put the "IF" logic into the procedural code and run each of the tests in some likely order of discovery, without significant additional overhead from more calls.

Answer 5

Get rid of those like operators.

In your case you don't need them. Just check if the field starts with a given value which you can achive whith something like this:

Left([field], Len(value)) = value

This method applied to your query would look like this (did some reformatting for better readability):

SELECT
  TabDrawer.DrawerName, 
  TabDrawer.DrawerSortCode, 
  TabDrawer.DrawerAccountNo, 
  TabDrawer.DrawerPostCode, 
  QryAllTransactons.TPCChequeNumber, 
  TabDrawer.DrawerRef
FROM
  TabDrawer 
  LEFT JOIN QryAllTransactons 
    ON TabDrawer.DrawerRef = QryAllTransactons.TpcDrawer
WHERE 
  (Forms!FrmSearchCompany!SearchName Is Null 
  Or Left([drawername], Len(Forms!FrmSearchCompany!SearchName)) = Forms!FrmSearchCompany!SearchName)
And
  (Forms!FrmSearchCompany.SearchPostcode Is Null 
  Or Left([Drawerpostcode], Len(Forms!FrmSearchCompany!Searchpostcode)) = Forms!FrmSearchCompany!Searchpostcode) 
And 
  (Forms!FrmSearchCompany!SearchSortCode Is Null 
  Or Left([drawersortcode], Len(Forms!FrmSearchCompany!Searchsortcode)) = Forms!FrmSearchCompany!Searchsortcode) 
And 
  (Forms!FrmSearchCompany!Searchaccount Is Null 
  Or Left([draweraccountno], Len(Forms!FrmSearchCompany!Searchaccount)) = Forms!FrmSearchCompany!Searchaccount) 
And 
  (Forms!FrmSearchCompany!Searchcheque Is Null 
  Or Left([tpcchequenumber], Len(Forms!FrmSearchCompany!Searchcheque)) = Forms!FrmSearchCompany!Searchcheque)

Note that you're comparing case sensitive. I'm not totally sure if the like operator in MS-Access is case insensitive. Convert both strings to upper- or lowercase, if needed.

Answer 6

Two things. Since this is an Access database with a SQL Server backend, you may find a considerable speed improvement by converting this to a stored proc.

Second, do you really need to return all those fields, especially in the tabCustomers table? Never return more fields than you actually intend to use and you will improve performance.

Answer 7

When you upsized did you make sure the tables were properly indexed? Indexes will speed queries tremendously if used properly (note they may also slow down inserts/updates/deletes, so choose carefully what to index)