Access slow to sum or join on null values?

[qwertyjjj]

Is Access slower at summing null records than SQL server?
I have a query which takes less than 1second in SQL server but takes about 5-10 in Access but can't think why there is such a lag in processing speeds.

 

[KenHigg]

Access is going to be slower at a lot of things than SQL Server. It's the nature of the beast. How may records are hitting and are doing the query in an Access .mdb that is in a file sever/network or is it on your local machine?

 

[qwertyjjj]

Yeah, it's on my local machine. The SQL Server has about 3x more RAM and it's SQL Server anyway so, as you say, it's probably faster.

I would like to use SQL SErver as the back end and Access as the front end but it seems to run into the same trouble because it is Access running queries through the linked tables, which is still slow in comparison.

 

[KenHigg]

Are you updating records or just displaying data? How complex is the sql statement? The closer it is to plain vannilla sql the better your chances are of it executing on the sql server side.

 

[qwertyjjj]

It's quite a complex SQL statement full of joins onto nested sql statements. It works on SQL server fine. The problem I am having is that Access' queries are slow. The only way to improve the speed would be to have a stored procedure on the SQL server side wouldn't it?

In which case, there's no point in having Access at all. A separate VBA front end would be better.

 

[KenHigg]

So your problem is the query not the OnCurrent event stuff.

S0 you have a complex query you use to populate a list box? How often does the data get updated/changed in the underlying tables?

 

[qwertyjjj]

Without seeing your query, it is hard to tell how you might optimize it. Search for the kb article on client/server optimization. One thing I have noticed is that Jet doesn't optimize subselects efficiently. You usually get better results with nested queries and outer joins.

Jet makes every effort to "pass-through" queries to the back end server. If your queries are significantly slower when run from Access, you may need to rework them to help Jet out.

I have attached an example of one. All the work is done in the first part of the joins SELECT ? FROM Structure. Then all the separate sub selects are joined onto that. Interestingly, it only became slow as soon as I added the 3 extra subselects onto the end NA_ZA, NA_BD, and NA_ZI.
Any ideas?

In SQL Server this runs very quickly...