Solved It takes too long to download data from server

[TipsyWolf]

Hey everyone!

This is my first time doing this, so setting it all up feels pretty advanced for me.

I had my split database ready to go using SMB protocol (local network), but my IT guy suggested moving the tables to a server instead. I knew that was a more scalable solution, so he set up the server, and I exported all my tables via ODBC. BTW, everything runs smoothly on my PC - no issues there.

I installed the driver, and it looks like this:

ISSUE:
One of my forms uses a table with just 90 records as its record source, but it takes a ridiculous 20 seconds (!) to load and display everything. Here’s the SQL it’s using:

SELECT TrainingAttendance.TrainingAttendanceID, Customers.CustomerName, CustomerSite.SiteTitle, Trainers.tFullName, TrainingTypes.TrainingName, TrainingAttendance.Date1, TrainingAttendance.Date2, [OverallAvgRate]/100 AS Rscore, [Score]/100 AS Qscore, TrainingAttendance.tblDateTime
FROM CustomerSite INNER JOIN (TrainingTypes INNER JOIN (Trainers INNER JOIN (Customers INNER JOIN ((qryEmpRscores_by_taID INNER JOIN TrainingAttendance ON qryEmpRscores_by_taID.TrainingAttendanceID_FK = TrainingAttendance.TrainingAttendanceID) INNER JOIN qryEmpQscores_by_taID ON TrainingAttendance.TrainingAttendanceID = qryEmpQscores_by_taID.TrainingAttendanceID_FK) ON Customers.CustomerID = TrainingAttendance.CustomerID_FK) ON Trainers.TrainerID = TrainingAttendance.TrainerID_FK) ON TrainingTypes.TrainingTypeID = TrainingAttendance.TrainingTypeID_FK) ON (Customers.CustomerID = CustomerSite.CustomerID_FK) AND (CustomerSite.CustomerSiteID = TrainingAttendance.CustomerSiteID_FK);

Now, I’ve got another form that needs to display data from a table with nearly 6,000 records, and it takes forever to load.

To me, it seems like something’s making simple tasks way more complex - like it might be repeating the same steps over and over in some kind of intense loop?

My IT guy says the server sends the data in less than a second, so that’s not the bottleneck.

Also, in my dashboard, I had two custom text boxes with Control Sources like this:

=DLookUp("ravg";"q_count_globals")/100

=DLookUp("qavg";"q_count_globals")/100

Just these two simple DLookups made the form load super slow, so I had to remove them.

Could the issue be with the queries? But I have other queries in the dashboard that work fine.
Or could it be the VBA code in these forms? Here’s the strSQL VBA I’m using for a form that takes 20 seconds to display just 90 records:

Option Compare Database
Option Explicit

Private Sub Form_Load()
    ' No initialization needed
    TranslateForm Me
End Sub
' ------ COMBO BOX EVENT HANDLERS ------
Private Sub cboCustomerName_AfterUpdate()
    ' Update SiteTitle combo based on selected customer
    Me.cboSiteTitle.rowSource = _
        "SELECT DISTINCT CustomerSite.SiteTitle " & _
        "FROM CustomerSite " & _
        "INNER JOIN Customers ON CustomerSite.CustomerID_FK = Customers.CustomerID " & _
        "WHERE Customers.CustomerName = '" & Replace(Me.cboCustomerName, "'", "''") & "' " & _
        "ORDER BY CustomerSite.SiteTitle;"
 
    Me.cboSiteTitle = Null ' Clear previous selection
    Me.cboSiteTitle.Requery
    ApplyFilters
End Sub

Private Sub cboSiteTitle_AfterUpdate()
    ApplyFilters
End Sub

Private Sub cboFullName_AfterUpdate()
    ApplyFilters
End Sub

Private Sub cboTrainingName_AfterUpdate()
    ApplyFilters
End Sub
' ------ FILTER FUNCTION ------
Private Sub ApplyFilters()
    Dim strFilter As String
    Dim strSQL As String
 
    ' Customer filter
    If Not IsNull(Me.cboCustomerName) Then
        strFilter = strFilter & " AND Customers.CustomerName = '" & Replace(Me.cboCustomerName, "'", "''") & "'"
    End If
 
    ' Site filter
    If Not IsNull(Me.cboSiteTitle) Then
        strFilter = strFilter & " AND CustomerSite.SiteTitle = '" & Replace(Me.cboSiteTitle, "'", "''") & "'"
    End If
 
    ' Trainer filter
    If Not IsNull(Me.cboFullName) Then
        strFilter = strFilter & " AND Trainers.tFullName = '" & Replace(Me.cboFullName, "'", "''") & "'"
    End If
 
    ' Training filter
    If Not IsNull(Me.cboTrainingName) Then
        strFilter = strFilter & " AND TrainingTypes.TrainingName = '" & Replace(Me.cboTrainingName, "'", "''") & "'"
    End If
 
    ' Date Range filter (for date1)
    If Not IsNull(Me.dateStart) And Not IsNull(Me.dateEnd) Then
        ' Filter between two dates
        strFilter = strFilter & " AND TrainingAttendance.date1 BETWEEN #" & Format(Me.dateStart, "yyyy\/mm\/dd") & "# AND #" & Format(Me.dateEnd, "yyyy\/mm\/dd") & "#"
    ElseIf Not IsNull(Me.dateStart) Then
        ' Only start date provided (filter from start date onward)
        strFilter = strFilter & " AND TrainingAttendance.date1 >= #" & Format(Me.dateStart, "yyyy\/mm\/dd") & "#"
    ElseIf Not IsNull(Me.dateEnd) Then
        ' Only end date provided (filter up to end date)
        strFilter = strFilter & " AND TrainingAttendance.date1 <= #" & Format(Me.dateEnd, "yyyy\/mm\/dd") & "#"
    End If
 
    ' Build final SQL - including tblDateTime in the SELECT statement
    strSQL = "SELECT TrainingAttendance.TrainingAttendanceID, Customers.CustomerName, " & _
             "CustomerSite.SiteTitle, Trainers.tFullName, TrainingTypes.TrainingName, " & _
             "TrainingAttendance.Date1, TrainingAttendance.Date2, TrainingAttendance.tblDateTime, " & _
             "[OverallAvgRate]/100 AS Rscore, [Score]/100 AS Qscore " & _
             "FROM TrainingTypes INNER JOIN (Trainers INNER JOIN ((Customers INNER JOIN " & _
             "CustomerSite ON Customers.CustomerID = CustomerSite.CustomerID_FK) INNER JOIN " & _
             "((qryEmpRscores_by_taID INNER JOIN TrainingAttendance ON qryEmpRscores_by_taID.TrainingAttendanceID_FK = TrainingAttendance.TrainingAttendanceID) " & _
             "INNER JOIN qryEmpQscores_by_taID ON TrainingAttendance.TrainingAttendanceID = qryEmpQscores_by_taID.TrainingAttendanceID_FK) " & _
             "ON (Customers.CustomerID = TrainingAttendance.CustomerID_FK) AND " & _
             "(CustomerSite.CustomerSiteID = TrainingAttendance.CustomerSiteID_FK)) " & _
             "ON Trainers.TrainerID = TrainingAttendance.TrainerID_FK) " & _
             "ON TrainingTypes.TrainingTypeID = TrainingAttendance.TrainingTypeID_FK" & _
             IIf(strFilter <> "", " WHERE " & Mid(strFilter, 6), "") & ";"
 
    ' Apply to form
    Me.RecordSource = strSQL
    Me.Requery
 
    ' Ensure tblDateTime textbox is properly bound
    If Not Me.Recordset.EOF Then
        Me.tblDateTime.ControlSource = "tblDateTime"
    End If
End Sub

' ------ CLEAR ALL FILTERS ------
Private Sub cmdClearFilters_Click()
    ' Clear combo boxes
    Me.cboCustomerName = Null
    Me.cboSiteTitle = Null
    Me.cboFullName = Null
    Me.cboTrainingName = Null
 
    ' Clear date range
    Me.dateStart = Null
    Me.dateEnd = Null
 
    ' Reset SiteTitle combo
    Me.cboSiteTitle.rowSource = "SELECT DISTINCT SiteTitle FROM CustomerSite ORDER BY SiteTitle;"
    Me.cboSiteTitle.Requery
 
    ' Reset record source - including tblDateTime in the SELECT statement
    Me.RecordSource = "SELECT TrainingAttendance.TrainingAttendanceID, Customers.CustomerName, " & _
                      "CustomerSite.SiteTitle, Trainers.tFullName, TrainingTypes.TrainingName, " & _
                      "TrainingAttendance.Date1, TrainingAttendance.Date2, TrainingAttendance.tblDateTime, " & _
                      "[OverallAvgRate]/100 AS Rscore, [Score]/100 AS Qscore " & _
                      "FROM TrainingTypes INNER JOIN (Trainers INNER JOIN ((Customers INNER JOIN " & _
                      "CustomerSite ON Customers.CustomerID = CustomerSite.CustomerID_FK) INNER JOIN " & _
                      "((qryEmpRscores_by_taID INNER JOIN TrainingAttendance ON qryEmpRscores_by_taID.TrainingAttendanceID_FK = TrainingAttendance.TrainingAttendanceID) " & _
                      "INNER JOIN qryEmpQscores_by_taID ON TrainingAttendance.TrainingAttendanceID = qryEmpQscores_by_taID.TrainingAttendanceID_FK) " & _
                      "ON (Customers.CustomerID = TrainingAttendance.CustomerID_FK) AND " & _
                      "(CustomerSite.CustomerSiteID = TrainingAttendance.CustomerSiteID_FK)) " & _
                      "ON Trainers.TrainerID = TrainingAttendance.TrainerID_FK) " & _
                      "ON TrainingTypes.TrainingTypeID = TrainingAttendance.TrainingTypeID_FK;"
    Me.Requery
 
    ' Ensure tblDateTime textbox is properly bound
    If Not Me.Recordset.EOF Then
        Me.tblDateTime.ControlSource = "tblDateTime"
    End If
End Sub

Where should I dig in to fix this? Any ideas?

UPD:
- I removed all vba just to test it - VBA doesn't affect it anyhow.
- I cleared out my form's Record Source to just a table (without FK tables) and it load within 1 seconds like it should. but now i have no data in my form

 

[Minty]

It looks like your first query is joining server tables to a couple of access local queries.
Move the entire thing to a server based query (a View in SQL Server) including the local queries.

If the local queries use form references as parameters, it gets more complicated, but can still be done with some planning.

I suspect your dlookups() source query is also an issue

Complicated or multiple joins should always be done on the server where possible, then filter the results locally if required.

 

[Gasman]

Now, I’ve got another form that needs to display data from a table with nearly 6,000 records, and it takes forever to load.

When you are using client server technology, you should be limiting the amount of data coming over the network.
So you would ask the user for a client identifier, and then bring data for that client only.

I have never used client server technology, as all my tables were local on a server PC in the LAN, but believe that is the method you should use.
Also any linked fields should be indexed.

 

[GPGeorge]

It looks like your first query is joining server tables to a couple of access local queries.

Spoiler:

Move the entire thing to a server based query (a View in SQL Server) including the local queries.

If the local queries use form references as parameters, it gets more complicated, but can still be done with some planning.

I suspect your dlookups() source query is also an issue

Complicated or multiple joins should always be done on the server where possible, then filter the results locally if required.

If this is the case, i.e. your queries join local Access tables or queries to remote SQL Server tables, the result is the slowest possible performance. The only time you should do that is when there is absolutely no other alternative.

Figure out a way to avoid having both local Access tables and remote SQL Server tables. Either move everything to the SQL Server database, or create temp tables in the Access accdb for the data you'll need to join with local tables later in the process.

 

[cheekybuddha]

your queries join local Access tables or queries to remote SQL Server tables

Note, OP is using Postgres, but your point still stands, George!

 

[cheekybuddha]

What's the SQL for queries q_count_globals and qryEmpRscores_by_taID ?

As @Minty points out, you will probably want to recreate the queries as views on the server.

 

[TipsyWolf]

qryEmpRscores_by_taID

this is a query for Rate where employee gives after session \ training to trainer. there are 8 questions so later we could rate trainers \ facilitators.
i already made it as "view" in postgre and it looks like this

-- public.qryemprscores_by_taid source

CREATE OR REPLACE VIEW public.qryemprscores_by_taid
AS SELECT e."TrainingAttendanceID_FK",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 1 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate1",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 2 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate2",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 3 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate3",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 4 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate4",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 5 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate5",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 6 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate6",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 7 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate7",
    round(avg(
        CASE
            WHEN rr."RateID_FK" = 8 THEN rr."RateResponse" / r."Rate" * 100
            ELSE NULL::integer
        END)) AS "AvgRate8",
    round(avg(rr."RateResponse" / r."Rate" * 100)) AS "OverallAvgRate",
    ta."Date1",
    t."tFullName"
   FROM "Trainers" t
     JOIN "TrainingAttendance" ta ON t."TrainerID" = ta."TrainerID_FK"
     JOIN ("Employees" e
     JOIN ("RateResponse" rr
     JOIN "Rates" r ON rr."RateID_FK" = r."RateID") ON e."EmployeeID" = rr."EmployeeID_FK") ON ta."TrainingAttendanceID" = e."TrainingAttendanceID_FK"
  GROUP BY e."TrainingAttendanceID_FK", ta."Date1", t."tFullName"
  ORDER BY e."TrainingAttendanceID_FK";

q_count_globals

this is a query for my dashboard. basically its general statistic and its (its local yet, im moving everything to server)

SELECT SUM(Type1) AS TotalType1, SUM(Type2) AS TotalType2, SUM(Type1) + SUM(Type2) AS GlobalTotal, (SELECT AVG(Score) FROM qryEmpQscores_by_taID) AS Qavg, (SELECT AVG(OverallAvgRate) FROM qryEmpRscores_by_taID) AS Ravg
FROM q_count_ttype;

 

[TipsyWolf]

What's the SQL for queries q_count_globals and qryEmpRscores_by_taID ?

As @Minty points out, you will probably want to recreate the queries as views on the server.

YES doing it as i speak
thanks you all guys !

 

[GPGeorge]

Note, OP is using Postgres, but your point still stands, George!

You're right and I knew it, too.

 

[BlueSpruce]

YES doing it as i speak
thanks you all guys !

How is the performance now that you have created the server side views?
FYI, the server side views appear as linked tables in your Access frontend, so use AccessSQL on those linked tables.
Do NOT use Pass Through queries on the server side views because you cannot filter the results and it's going to send all the records in the table and performance will suffer.
Another limitation with PT queries is that they're read-only. This causes the ACE engine to be totally left out of the picture, such that the SQL is passed on to the db server unmodified.

 

[GPGeorge]

Um, while it is true that Pass Thru queries are read-only in Access, and that the term "Pass-Thru" means that the ACE engine is by-passed, I'm not sure the conclusion reached in your first statement is accurate.

As you note, Access sees the linked view as just another table, and an Access query on a linked view will perform the same as it would on a linked table.

That Access-side behavior has nothing to do with what happens on the server, though. A Pass-Thru query operates directly on the view in the SQL Server database. If the Pass-Thru query is parameterized, it's going to apply its parameters to the view in the server.

The result can be a single record from that view returned via the Pass-Thru.

One of the advantages of Pass-Thru queries, by the way, is that it can greatly improve performance because complex SQL logic executed on the server can be more efficient.

Perhaps you meant something else though? For example, it is not possible to pass parameters to the SQL used to generate the view itself. Parameters are applied only to the resulting recordset returned by the view. This is done via Stored Procedures in SQL Server. The same is true, though, whether we are looking at it from the Access side or the SQL Server side.

In Access we call them all "queries", whether they are parameterized or not, and whether they are SELECT or Action queries.

In SQL Server, Views are views. Parameterized SQL and executable SQL is executed in Stored Procedures.

 

[BlueSpruce]

Um, while it is true that Pass Thru queries are read-only in Access, and that the term "Pass-Thru" means that the ACE engine is by-passed, I'm not sure the conclusion reached in your first statement is accurate.

If you read Albert D. Kallal's post, and the other posts that follow it, especially this post, then you will realise why I made the statement in my previous post in this thread.

 

[dalski]

I would think the OP will require the recordset of the form to be editable, but enjoy the benefit of fast calculated fields in the view/ stored procedure/ function used inside a field of the query.

As you note, Access sees the linked view as just another table, and an Access query on a linked view will perform the same as it would on a linked table.

1 - Testing on a view in MSSQL; which is editable. The view then needs to be reloaded & the calculated field in the view is then updated. However using a PT Query in Access the recordset is not updatable. Creating an Access query on the PT Query; the recordset is still not updatable. All PK fields are in the query & timestamp field also. What am I missing here?

2 - Complicating things further; say the user does not want to expose business logic through security-weak Access & wants to hide important logic to the user/ hackers & wants to action this logic through MSSQL hiding as much as he can. Triggers through MSSQL on the view; but the recordset in Access is not editable.

Is it a case of:
(1) Create PT query
(2) Build a TempTable with an indexed view off (1)
?

 

[GPGeorge]

The data in views may or may not be editable either in SQL Server, via SSMS, or when linked in Access. It depends on how the view was created. The same is true of SELECT queries in Access, btw. The data returned by a SELECT query may be editable or not.

I wasn't as thorough in discussing Pass-Thru queries as I should have been.

Some Pass-Thrus return recordsets. Some only execute Stored Procedures in the database on the Server to modify data, but do not return recordsets.

When a Pass-Thru query returns any recordset, that recordset is never editable, regardless of how it was created in the SQL Server database. Access can't change the records returned by a Pass-Thru.

If you want the recordset returned by a view to be editable in Access, you can't retrieve it via a Pass-Thru. You can link to the view and Access will handle it as it would a table. If it's editable, Access can edit its data. If not, Access can't edit its data.

Now, this is the part I skipped over earlier.

Pass-Thru queries can execute Stored Procedures to add, update or delete records in SQL Server. Pass-Thru queries can also execute dynamically written SQL that creates, updates or deletes records.

Here's an example of the SQL in a non- parameterized Pass-Thru:

EXEC access.SP_FoodItems_cbo

It executes a stored procedure in a Schema called access. The name of that stored procedure is SP_FoodItems_cbo. That stored procedure runs a series of steps to repopulate a table in SQL Server that can be used as the rowsource for a combo box. It is not parameterized because it always does the same thing

Here's an example of the SQL in a dynamically rewritten Pass-Thru with two hard-coded values that could be parameterized if executed in Access.:

UPDATE tblCompany
SET CompanyType = 2
WHERE CompanyID = 4;

In order to execute this SQL via a Pass-Thru query, you use VBA to replace the two placeholders for the parameters with the actual values for the parameters. This is something we do in Access, as well. Note, as SQL, you can't pass parameters via a Pass-Thru, but you can accomplish the same result by dynamically rewriting the SQL in the Pass-Thru before executing it.

If the goal is to isolate business logic from users, Stored Procedures executed by Pass-Thru queries would be very useful.

 

[DaveMD]

I would add one more approach to this that isn't always thought of and that can perform very well when using a backend RDBMS, assuming the OP's dashboard does not need to be editable (I'm assuming it may not need to be editable based on the OP's screenshot and noting it is a dashboard that is showing statistical info).

Rather than using a linked table or a pass-through query, you can instead use an ADO recordset that directly queries your PostGres database with a custom SQL query and set the Form's Recordset to the ADO recordset. In the OP's situation, I would suggest:

• Just as many others in this thread suggest, create a View server-side (in PostGres) that encapsulates your current Form's recordsource query. This will allow PostGres to perform all the heavy lifting for query processing and provide advantages with security and business logic isolation like @dalski and @GPGeorge note. For example purposes, we can call this PostGres View "CustomerDashboardView"
• Update your existing ApplyFilters VBA function to build the SQL query string that will directly execute on PostGres.
• Open the SQL query string in the ADO recordset.
• Set the Form's Recordset to the ADO recordset which will populate the Form with the query results from PostGres.

To put it all together, it might look something like the below:

Private Sub ApplyFilters()
    Dim strFilter As String
    Dim strSQL As String
    Dim strDBConn As String
    Dim rst As New ADODB.Recordset

    ' Build the filter similar to the approach you do currently but in the context of PostGres SQL
    strFilter = ............

    ' Put together full PostGres query
    strSQL = "SELECT TrainingAttendanceID, CustomerName, SiteTitle, tFullName, TrainingName, " & _
                   "               Date1, Date2, Rscore, Qscore, tblDateTime " & _
                   " FROM CustomerDashboardView " & _
                   " WHERE " & strFilter

     ' Run query server-side and capture results in Recordset object
     strDBConn = "....Specify Connection String to your PostGres DB...."
     Call rst.Open(strSQL, strDBConn, adOpenKeyset, adLockReadOnly)

     ' Finally set the recordset results as the Form's recordset.
     ' This will populate your form with the PostGres generated query results.
     Set Me.Recordset = rst

     ' Clean-up local rst object
     rst.Close
     Set rst = Nothing
End Sub

EDIT TO NOTE: With this method, you should clear your Form's current "RecordSource" property (set = "") as it will not be used at all.

 

[Albert D. Kallal]

If you read Albert D. Kallal'...... you will realize why I made the statement in my previous post in this thread.

No worries.

Keep in mind that while you cannot pass parameters to a view? (unless you create a Stored procedure, and it thus can use parameters "against" the view which is perfectly fine and legal)?

Well, you don't need parameters anyway (at least in most cases - some you do!!!).

So, it becomes somewhat of a issue of "semantics" here, since you can't use parmeters in a view, well, you don't need to!

Just use a standard "where" clause in the Access client side. That includes say VBA and a SQL select against the linked view (which looks like a table to Access.

Hence, this is just fine:

strSQL = "SELECT * from SomeViewName where City = 'Edmonton'"

Or, if this is a form, or report? Then of course we use the "where clause" of the OpenForm/OpenReport such as:

EG:
docmd.OpenFrom "frmHotels",,,"City = 'Edmonton'"

Now, the frmHotels can be bound to that linked view, and even with 1 million rows?

SQL server and Access will ONLY pull the one row anyway - even if this is a view.

So, we actually don't really care that we can't use parmaters in a view, since in near call cases, you don't need to!

There is of course one exception that I note "may" require paramters.

This is if your SQL is really nasty, has MANY sub queries, or some sub queries (or even possible joins), that you want the restrictions to not occur overall in the query, but that of bits and parts of the SQL require restrictions for those bits and parts.

As noted, this use case does not happen too often, but it can and does in some cases.....

R
Albert

 

[dalski]

If you want the recordset returned by a view to be editable in Access, you can't retrieve it via a Pass-Thru. You can link to the view and Access will handle it as it would a table. If it's editable, Access can edit its data. If not, Access can't edit its data...

@GPGeorge, thank you so much for taking time out of your day for this articulate explanation (the best I've come across after a week or so full-time research where full detailed articles & all MSN research... don't compare to this fine explanation).

After much research I could not ascertain what the process was but after your single post it has now clarified it for me. Extremely grateful!

 

[Minty]

Something well worth noting from a performance perspective is to use named parameters in your pass through routines when running repetitive operations. SQL server will reuse the query plan instead of creating a new one every time.

This is a really simplistic example to demonstrate, but instead of simply running this:

UPDATE tblCompany
SET CompanyType = 2
WHERE CompanyID = 4;

If you are running this update regularly stick it a SP and/or use the parameters:

DECLARE 
           @iComp    AS INT = 4,
           @iCompType AS INT = 2


UPDATE tblCompany
SET CompanyType = @iCompType
WHERE CompanyID = @iComp

Obviously a simple update like this won't make much odds, but complex queries and processes will benefit.

 

[GPGeorge]

Something well worth noting from a performance perspective is to use named parameters in your pass through routines when running repetitive operations. SQL server will reuse the query plan instead of creating a new one every time.

This is a really simplistic example to demonstrate, but instead of simply running this:

UPDATE tblCompany
SET CompanyType = 2
WHERE CompanyID = 4;

If you are running this update regularly stick it a SP and/or use the parameters:

DECLARE
           @iComp    AS INT = 4,
           @iCompType AS INT = 2


UPDATE tblCompany
SET CompanyType = @iCompType
WHERE CompanyID = @iComp

Obviously a simple update like this won't make much odds, but complex queries and processes will benefit.

Good point. Essentially what you are passing here is very close to the structure of a Stored Procedure.

CREATE OR ALTER PROCEDURE sp_UpdateCompanyType
(
    @iComp    AS INT = 4,
    @iCompType AS INT = 2
)
AS
BEGIN
    SET NOCOUNT ON
    UPDATE tblCompany
SET CompanyType = @iCompType
WHERE CompanyID = @iComp
END
GO

In other words, if this were created as a stored procedure called sp_UpdateCompanyType in the database, the SQL in the Pass-Thru query would be:

Exec sp_UpdateCompanyType 4, 2