Does an Access query negate the benefits of utilizing SQL

[gojets1721]

Full disclosure, I'm completely new to SQL so I apologize if this is a silly question.

I am using SQL as a backend and access as a front end. In general, does using a query to filter data for a form negate some of the speed and performance of SQL?

 

[Isaac]

You are wanting a blanket answer but one cannot (honestly) be provided. It all depends.

Here are a few notes, in no particular order and not necessarily related, that I've observed over the years:

1. It is common for people to point out that simply using SQL Server as a back end is not a cure-all for performance, as extremely low-performance design is still very possible. While I agree with that statement, it's also worth pointing out that sometimes, simply using SQL Server will, in fact, cause things to speed up - but we avoid saying that (I think) because we don't want people to be lazy and use bad habits, just hoping the mere move to SQL will erase the consequences, which is fine, but it's still a fact nonetheless in my experience.
2. It is true that if you are going to use SQL Server, you can often realize a major gain in performance by using pass-through queries, written in T-SQL (not Access, but saved in Access), and executed on the server. And in that case, my point #1 becomes especially relevant - where you often WILL see major performance gains, even if the SQL/query/performance-related design is not great and even if it's exactly the same as it was in Access. Again, we encourage people to do good design - not just move to a faster processor - but there's nothing wrong, I think, with pointing it out. Use a table with 20 million records and see how fast Access can return (and render on screen) "select * from table" vs. SQL Server, and the point is made.

To my mind, beyond those two points, nothing further can be made in the form of a blanket statement.
I have done a number of projects where my "overhaul" project consisted in nothing more than moving the back end to SQL - changing nothing at all about the FE design or queries - before the project was decommissioned or the sponsors lost interest and moved me to other things. In most of those cases, performance sped up considerably - on the sole basis of the new back end. Despite it being a very bad idea to rely on that in place of performant design, it is what it is.
And, after all, most of the world's tech users behave the same way, if we're being honest. Most people, when their laptop starts giving them problems or slowness, they just rave about the next biggest and fastest laptop. Rather than understanding that an older, slower laptop works quite well if maintained and used correctly. But we have come to accept that. I suppose some of that mentality is present in tech design, too.

90% of the "our data warehouse load jobs are taking too long"-conversations at the DBA+Developer table consist in promises to "buy more cores" rather than improve performance of design...Even in the best of shops.

 

[GPGeorge]

Short answer, "no". It's an apples and oranges comparison in many ways.

Long answer, "sometimes". You can write awkward SQL in Access queries that make it hard to retrieve data from a remote database, and that may drag down performance. On the other hand, Access is pretty smart about how it executes queries, so many times there's no noticeable difference. And in still other situations, factors other than query design impact performance even more.

As a rule of thumb, I'd venture a guess that something like this would be pretty efficient as the recordsource for a form.

SELECT Field1,Field2, Field3, Field4, Field5, Field5,
FROM Table
WHERE Field1 = 1000

And moving it into a passthru to process on the server would not make a noticeable difference in most cases.

 

[gojets1721]

You are wanting a blanket answer but one cannot (honestly) be provided. It all depends.

Here are a few notes, in no particular order and not necessarily related, that I've observed over the years:

1. It is common for people to point out that simply using SQL Server as a back end is not a cure-all for performance, as extremely low-performance design is still very possible. While I agree with that statement, it's also worth pointing out that sometimes, simply using SQL Server will, in fact, cause things to speed up - but we avoid saying that (I think) because we don't want people to be lazy and use bad habits, just hoping the mere move to SQL will erase the consequences, which is fine, but it's still a fact nonetheless in my experience.
2. It is true that if you are going to use SQL Server, you can often realize a major gain in performance by using pass-through queries, written in T-SQL (not Access, but saved in Access), and executed on the server. And in that case, my point #1 becomes especially relevant - where you often WILL see major performance gains, even if the SQL/query/performance-related design is not great and even if it's exactly the same as it was in Access. Again, we encourage people to do good design - not just move to a faster processor - but there's nothing wrong, I think, with pointing it out. Use a table with 20 million records and see how fast Access can return (and render on screen) "select * from table" vs. SQL Server, and the point is made.

To my mind, beyond those two points, nothing further can be made in the form of a blanket statement.
I have done a number of projects where my "overhaul" project consisted in nothing more than moving the back end to SQL - changing nothing at all about the FE design or queries - before the project was decommissioned or the sponsors lost interest and moved me to other things. In most of those cases, performance sped up considerably - on the sole basis of the new back end. Despite it being a very bad idea to rely on that in place of performant design, it is what it is.
And, after all, most of the world's tech users behave the same way, if we're being honest. Most people, when their laptop starts giving them problems or slowness, they just rave about the next biggest and fastest laptop. Rather than understanding that an older, slower laptop works quite well if maintained and used correctly. But we have come to accept that. I suppose some of that mentality is present in tech design, too.

90% of the "our data warehouse load jobs are taking too long"-conversations at the DBA+Developer table consist in promises to "buy more cores" rather than improve performance of design...Even in the best of shops.

Gotcha. Thanks so much. This is very helpful info. Sorry to be so vague; what prompted me to ask this was also just a vague statement that I saw which essentially said "Using Access queries with SQL negates the benefits of SQL".

Your response is much more thorough

 

[theDBguy]

Gotcha. Thanks so much. This is very helpful info. Sorry to be so vague; what prompted me to ask this was also just a vague statement that I saw which essentially said "Using Access queries with SQL negates the benefits of SQL".

Your response is much more thorough

Hi. Providing a link to where you read that might help us explain any confusion. Just a thought...

 

[Isaac]

what prompted me to ask this was also just a vague statement that I saw which essentially said "Using Access queries with SQL negates the benefits of SQL

Funny, now that you say it like that, it inspires me to do what might appear to be an about face (but isn't really) and mention that I do, in fact, see some (usually older) Access developers who use SQL as a back end but never seem to have made the jump into actual SQL Server development and wow, what a waste that seems to me. (in fact I see the exact same phenomenon with VBA).
It's like getting to the edge of paradise, taking a selfie and then going back home. At least, that's how I felt after I made the jump and realized I'd been missing out on what I now consider the "99%" of the fun and opportunity.

maybe that's all they meant.

 

[KitaYama]

Access developers who use SQL as a back end but never seem to have made the jump into actual SQL Server development and wow, what a waste that seems to me. ...........
It's like getting to the edge of paradise, taking a selfie and then going back home.

We have several tables in sql server, each one with more than a million records. The tables are linked (DSNLess) in an Access FE and we use what @GPGeorge suggested. Queries that return 0 rows and then change the WHERE clause to what we need.
I have also done several tests to see performance changes if we use pass-through queries instead and honestly I don't see any difference.

With your experience, do you think it's better to us pass-through queries (specially for search forms)?

Thanks

 

[piano_playr]

I am a SQL Server aficionado. I love it. One of the neglected features of using Access as a front end and connecting to a SQL Server database on the back end is the programming available in SQL Server. One can execute SQL Server stored procedures on the server from Access. Most Access development includes "action queries". A tremendous performance boost will occur when action queries are converted to stored procedures. I want to provide an example. Let's say you have an action query to update customer information. It might look like this:

' Access QueryDef:
UPDATE sales_staffs SET sales_staffs.first_name = "New First Name"
WHERE (((sales_staffs.staff_id)=1));

If this QueryDef was converted to a stored procedure, the database update performance would be immensely improved. Below is Access VBA that is needed to call a DML stored procedure. The call to run the stored procedure occurs within a module-level procedure called ExecuteSQL. The code uses ADO. The procedure can be called from anywhere in an Access project. No need to repeat the code. DRY principle! This functionality is many times more efficient than running an action query out of Access. Please review the stored procedure further down the page.

' Access VBA to call stored procedure update
Private Sub btn_UpdateFirstName_Click()

On Error GoTo Err_btn_UpdateFirstName_Click()
   With Me
       ExeccuteSQL "EXEC uspUpdateCustomer " & .CustomerID & ",'" & .FirstName & "';""
   End With

Exit_btn_UpdateFirstName_Click():
    Exit Sub

Err_btn_UpdateFirstName_Click():
       MsgBox "Error number " & Err.Number & ": " & Err.DESCRIPTION
        GoTo Exit_btn_UpdateFirstName_Click()
End Sub

' Module code to run stored procedures 
Public g_adoConnection As New ADODB.Connection
Public g_adoCommand As New ADODB.Command
Public g_adoRS As New ADODB.Recordset


Public Function SetConnxn()

    On Error GoTo HandleError

    SetConnxn = "Provider=sqloledb;Data Source=<server name.>" & _
             "Initial Catalog=<database name>;User ID=<user name.;Password=<password>;trusted_connection=no"
          
HandleExit:
    Exit Function
HandleError:
    MsgBox Err.Description
    Resume HandleExit
End Function

Public Sub ExecuteSQL(strSQL, Optional lngTimeout As Long)

  On Error GoTo Err_ExecuteSQL

  If g_adoRS.state = adStateOpen Then g_adoRS.Close
  If g_adoConnection.state = adStateOpen Then g_adoConnection.Close

  g_adoConnection.ConnectionString = SetConnxn
  g_adoConnection.Open
  
  g_adoCommand.CommandTimeout = 360
  g_adoCommand.ActiveConnection = g_adoConnection
  g_adoCommand.CommandText = strSQL

  Set g_adoRS = g_adoCommand.Execute

  If g_adoRS.state = adStateOpen Then g_adoRS.Close
  If g_adoConnection.state = adStateOpen Then g_adoConnection.Close

Exit_ExecuteSQL:
    Exit Sub

Err_ExecuteSQL:
    MsgBox "SQL statement could not be executed :" & vbCrLf & strSQL _
           & vbCrLf & Err.Description, vbOKOnly, "Avium " & Err.Source
    Resume Exit_ExecuteSQL

End Sub

Here is the stored procedure on SQL Server. It will execute the update much faster than the QueryDef would. Please notice the update_date field gets automatically updated when the stored procedure executes.

'SQL Server Stored Procedure'
USE [<database name>]
GO

drop procedure [dbo].[uspUpdateCustomer]
go

set ansi_nulls on
go

set quoted_identifier on
go

-- =============================================
-- Author:            <developer>
-- Create date:     <date>
-- Description:      Update single customer first name
-- =============================================
create procedure [dbo].[uspUpdateCustomer]
    @ID integer = 0
  , @first_name nvarchar(255) = ''
as
begin
    update sales.customers
    set first_name = @first_name
      , update_date = getdate()
    where customer_id = @ID;
end;
go

 

[Isaac]

We have several tables in sql server, each one with more than a million records. The tables are linked (DSNLess) in an Access FE and we use what @GPGeorge suggested. Queries that return 0 rows and then change the WHERE clause to what we need.
I have also done several tests to see performance changes if we use pass-through queries instead and honestly I don't see any difference.

With your experience, do you think it's better to us pass-through queries (specially for search forms)?

Thanks

I don't think a blanket answer makes sense here. If you are experiencing unacceptable slowness, try a pass-through. But first, check whether you are doing something that is bad for performance, the solution to which is adjusting your SQL approach and completely transcends Access query vs. passthrough query.

For some bulk updates (especially deletes), I create pass through queries because they will run outside of Access' transaction umbrella. You know how when you run an Action query, Access always tells you something like, "you are about to delete 5893 records. Do you want to continue?" That is your clue the the delete is happening inside a transaction. This adds overhead. If you use a pass through query, it just runs and you don't get a second chance to change your mind.

A much easier way to avoid that is to use currentdb.execute. Creating a pass through to avoid that prompt rather than just using .execute doesn't make sense to me.

I am not sure what joins and complexity has to do with the decision to make a stored procedure. The biggest benefits of the stored procedure is that there is one, over-arching, centralized place to maintain code and enforce business logic and it makes a lot more sense to have SQL inside those objects than it does pasting raw SQL into a pass-through. It's also, of course, awful to have to use the Access IDE rather than SSMS.

I like many SQL developers tend to avoid Views unless there is really no other way. The problem is something that isn't evident when first starting out. At first a View seems like the "perfect toy/tool" for seemingly just about every situation. The problem comes because they are just too easy to select from as Tables - and as such, the natural effect that happens as time goes by is Views inside Views inside Views. Everyone wants to leverage an existing View rather than re-do the coding, which sounds good at first blush but actually leads to a lot of unchallenged assumptions (about Views that are being used that contain logic the next-level-up Select-er doesn't fully digest or even want), a LOT of bad performance, (Views that have to fully generate themselves every time they are referenced), and business logic buried too deeply that discourages anyone from taking the enormous time needed to investigate all the way down the 'chain' to verify that that is what they really want. Not to mention ripple effects from someone altering just one of them at any time.

TVF's have the redeeming quality of accepting parameters, at least, and I like them.

I never paste raw SQL into a pass through query. I make a proper stored proc, then only exec it in the pass thru.
All t-sql is maintained on the database where it belongs.

 

[piano_playr]

Not sure how you can make this statement about an update to a single column of a single row. Methinks you're overselling

It's an example. If the example is expanded to many more records with many more columns, the benefits become clearer. There are so many more development features in SQL Server such as common table expressions, temporary tables, functions, triggers, table variables ... the list goes on. These features give developers the ability to create powerful stored procedures that far exceed what one could do with an Access action query. Instead of action queries, Access can call those powerful stored procedures and benefit from their sophistication.

I realize I'm getting away from the OP's question because he is inquiring about the performance of select statements with where clauses. I think it's important to consider all the other benefits of moving some Access development to the database server (SQL Server). I've been guilty of writing a bunch of VBA to slice and dice data before committing it to a database. Not good. One gets a big performance boost by moving all those action queries and DML-like VBA modules to SQL Server.

 

[KitaYama]

Maybe create a sample database with side-by-side examples of using an Access update query vs a stored procedure to do the same thing along with timings.

I really appreciate this comment. I've seen so many comments about the benefits of using sql server tools (stored procedures, pass-through queries,...), but have never seen a decent example. As I explained above, we have a very complex database with more than 90 linked tables to sql server, some of them having more than a million records, but everything we do here is as fast as a blink of eye. I think those who designed it knew their job well. The only stored procedure in use is for backing up the server.
I am really interested to see how things can be faster and more convenient than this if someone has an example.

Not that I want to say using sql server tools is not preferred, I just want to see it in action.

thanks.

 

[Galaxiom]

I never paste raw SQL into a pass through query. I make a proper stored proc, then only exec it in the pass thru.
All t-sql is maintained on the database where it belongs.

I always have doubts about the case being presented when someone uses the word "proper" to justify their position.

However I do understand what you are saying.

The only time I can recall having used pass-through queries is when passing arguments to TVFs. I've never considered using pass through for performance. Like Pat says, Access will pass through what it can anyway. If it can't, then the process probably does belong on the server.

I don't often write to the server from Access. If it is just a value or two the linked table works fine. For repetitive stuff I am more likely to send commands to a Stored Procedure if that is possible in the situation. Editing the sql in a passthrough seems a bit clunky.

If it involves vast amounts of data being uploaded then the performance is going to be terrible anyway so I would probably try reading the Access database from the server instead using OPENROWSET. That is what I do for data I need to read from Excel.

 

[Galaxiom]

I find that the people who push the use of non-native Access methods don't actually understand Access or hate Access and are convinced they know better so they don't understand or ever learn how to use linked tables and bound forms.

That comes across as very arrogant. Is that what you "find" or what you "assume"?

 

[Isaac]

I always have doubts about the case being presented when someone uses the word "proper" to justify their position

Point taken, but I am making no attempt to justify my position by the word proper - I'll be very direct, I used "proper" because I think that's the proper thing to do in many cases.

 

[Isaac]

I really appreciate this comment. I've seen so many comments about the benefits of using sql server tools (stored procedures, pass-through queries,...), but have never seen a decent example. As I explained above, we have a very complex database with more than 90 linked tables to sql server, some of them having more than a million records, but everything we do here is as fast as a blink of eye. I think those who designed it knew their job well. The only stored procedure in use is for backing up the server.
I am really interested to see how things can be faster and more convenient than this if someone has an example.

Not that I want to say using sql server tools is not preferred, I just want to see it in action.

thanks.

You can even set aside performance for the moment: Just the organization and simplification and centralization is a reason to create SQL Server objects....in some cases. I can't tell you how many times I've seen Access databases where someone created and saved a new Query object every time they breathed, just about, it seemed like. That's because a relatively trivial process in Access can require a dozen Action queries. I don't know anyone who is substantially experienced in SQL Server development who would rather manage all that crap in the overly simplistic Access object list - it's just messy and hard to keep up with. You end up with the craziest naming conventions in the world, as there is no way to document anything. Managing a lot of that stuff in the form of SQL Server artifacts is a thousand times easier. IMO people who are resistant to it are the same crowd who are resistant to VBA, it's just new and unfamiliar and they'd rather not. I completely understand that - because at ANY time, any one of us has SOMEthing that they are currently resisting adopting or learning - it's human nature, and the nature of the development career.
Speaking of RAD, there is a big difference between rapidly creating stuff and then rapidly and efficiently maintaining it. People make too much out of the "how quick can I create something" question, and too little about the "how well can I keep it organized over the next 5 years" aspect.

Just for that reason alone SQL Server shines pretty bright and that's just the beginning.
Like piano player said, it's a question of taking advantage of the sophistication. They may have oversold and generalized it a bit much, but I think they're more in the right direction.

@KitaYama all I can say is, if you work on incorporating SQL Server as much as seems reasonable (for your own learning sake), in a year or two you will shake your head at the idea that you ever thought of doing 'just Access'. It will open doors that will swing much wider than the Access world - and the best thing is you can keep doing both if you like.

 

[KitaYama]

@KitaYama all I can say is, if you work on incorporating SQL Server as much as seems reasonable (for your own learning sake), in a year or two you will shake your head at the idea that you ever thought of doing 'just Access'. It will open doors that will swing much wider than the Access world - and the best thing is you can keep doing both if you like.

I've been trying hard with no significant progress. The first book I bought was too sophisticated that I had to stop trying following the writer in the middle of the first chapter. The second one is not helping more than the first one.

Can you guide me to a good start point? Any on-line reading material, helpful site, blog, book whatever is OK.
Yes, I know I can google it. But I prefer to hear it from someone experienced rather than myself picking something random.

Thanks.

 

[Galaxiom]

The first book I bought was too sophisticated that I had to stop trying following the writer in the middle of the first chapter. The second one is not helping more than the first one.

Reading books won't get you anywhere unless you do stuff that needs to be done with what you are reading about.

 

[KitaYama]

Reading books won't get you anywhere unless you do stuff that needs to be done with what you are reading about.

I should understand what the author is talking about to be able to experiment what he says.

 

[Minty]

@KitaYama I'm in agreement with @Galaxiom , unless you have a specific task to accomplish learning new stuff can be difficult.

We do almost all the processing of data on SQL Server because we are on Azure platforms, and the connectivity makes the performance acceptable. So we do create a lot of Views, to avoid doing multi-table joins in Access, it's simply more efficient in our environment.

In some applications, we have complicated queries with dozens of calculated fields, these are much quicker in operation when done on the server. We also make extensive use of row_number ranking and lag functions to produce financial reports, these are just not possible without creating inefficient sub-queries or even temporary tables in Access.

If your applications aren't needing these types of things at the moment then the advantages of using SQL probably aren't obvious at the moment.

 

[KitaYama]

@Minty thanks for clarification