SQL Server connection

[Mark]

I’m using an ODBC connection to SQL Server. I’m working with several reports
that have complicated record sources. Some I have rewritten into views and
saved on SQL Server. Others because they do not translate easily into T SQL I
have left as SQL statements in the reports record source. Both methods work.
My question is this: when a SQL statement is used as the record source with
an ODBC connection to SQL Server, does SQL Server send back only the records
requested or all the total records in the associated tables? The thin/thick
client question revisited. Which is better?
Here’s an example of a conditional Access SQL statement that works as a
record source but not as a view.
SELECT IIf(Left([CarrName],1)='A',"A " &
[CarrName],IIf(Left([CarrName],1)='B',"B " &
[CarrName],IIf(Left([CarrName],1)='C',"C " & [CarrName],"Not A,B, or C"))) AS
BeginLtr
FROM [dbo_Add Carrier Divisions];

Access 2002
SQL Server 2000?

Thanks for your help,
Mark

 

[Jellifish]

I'm using an ODBC connection to SQL Server. I'm working with several
reports
that have complicated record sources. Some I have rewritten into views and
saved on SQL Server. Others because they do not translate easily into T
SQL I
have left as SQL statements in the reports record source. Both methods
work.
My question is this: when a SQL statement is used as the record source
with
an ODBC connection to SQL Server, does SQL Server send back only the
records
requested or all the total records in the associated tables? The
thin/thick
client question revisited. Which is better?

Run all of your sql on the server as Views or Stored Procedures, SQL Server
is set up to do all this much more efficiently than linked tables.

Here's an example of a conditional Access SQL statement that works as a
record source but not as a view.
SELECT IIf(Left([CarrName],1)='A',"A " &
[CarrName],IIf(Left([CarrName],1)='B',"B " &
[CarrName],IIf(Left([CarrName],1)='C',"C " & [CarrName],"Not A,B, or C")))
AS
BeginLtr
FROM [dbo_Add Carrier Divisions];

--SQL Server
SELECT CASE WHEN LEFT([CarrName],1) IN ('A','B','C') THEN LEFT([CarrName],1)
+ ' ' + [CarrName] ELSE 'Not A, B or C' As BeginLtr
FROM [dbo_Add Carrier Divisions];

 

[Mark]

Run all of your sql on the server as Views or Stored Procedures, SQL Server
is set up to do all this much more efficiently than linked tables. ???????

Again, does this mean that all the data is transferred as opposed to just
the records requested?

When a SQL statement is used as the record source with
an ODBC connection to SQL Server, does SQL Server send back only the records
requested or all the total records in the associated tables?

Thanks,
M

 

[golfinray]

I am no sql expert, but I know on my Access front end sql back end, it sends
the entire table.

 

[Jellifish]

Run all of your sql on the server as Views or Stored Procedures, SQL
Server
is set up to do all this much more efficiently than linked tables. ???????

Again, does this mean that all the data is transferred as opposed to just
the records requested?

If you are using linked tables and the Jet db engine to query them then I
think so (I'm not too sure as I never do it that way).

If you use pass-through queries then as the name suggests, the query is
passed to the SQL Server but this is still not as efficient or secure as
using Views or SPs.

When a SQL statement is used as the record source with
an ODBC connection to SQL Server, does SQL Server send back only the
records
requested or all the total records in the associated tables?

Depends on the factors above.

 

[Mark]

My thought is that you’re correct. I have tables with too many fields which
are not normalized completely. There are many ‘immediate if’ statements in
multiple columns and formatting and the like which are time consuming to
translate (at least for me). Logic would dictate that a simple select query
would utilize the jet and therefore bring back the whole table. I’m told that
even with this simple change, the front end works much, much better with SQL
than with an mdb. I’m at a loss to understand why if the whole table is being
sent across the line. It’s still thick client, client side processing.
Where’s the boost coming from?
M

 

[Kurz rómskeho jazyka Vaker Romanes]

hi

 

[John W. Vinson]

My question is this: when a SQL statement is used as the record source with
an ODBC connection to SQL Server, does SQL Server send back only the records
requested or all the total records in the associated tables?

Jellifish and Golfinray are *mistaken* in this case.

If you have proper indexes on the table, ODBC connections, and properly
written queries, Access parses the JET query into SQL syntax and retrieves
only the requested records.

If you want to demonstrate this, you can use a trace on the connection in
SQL/Server.

 

[Tony Toews [MVP]]

I am no sql expert, but I know on my Access front end sql back end, it sends
the entire table.

I'm not an expert either on Access and SQL Server however If this is
the case then you have not setup indexes on your criteria or
sorting/sequencing fields. Or something else is rather wonky.

Tony

 

[Jellifish]

Jellifish and Golfinray are *mistaken* in this case.

If you have proper indexes on the table, ODBC connections, and properly
written queries, Access parses the JET query into SQL syntax and retrieves
only the requested records.

If you want to demonstrate this, you can use a trace on the connection in
SQL/Server.

John, thanks for pointing that out, have you tested queries with functions
supported by Access but not SQL Server (Iif) to see if they get translated
as well or was it a simple query you used as a test?

 

[John W. Vinson]

John, thanks for pointing that out, have you tested queries with functions
supported by Access but not SQL Server (Iif) to see if they get translated
as well or was it a simple query you used as a test?

Simple query. Obviously SQL will not handle Access-specific functions,
especially if you're applying criteria to the result of the function. You can
consider using a SQL View or Stored Procedure to move the calculation (and the
retrieval) to the server, or use a simple query and do calculations on the
form or report.

As a rule, it's a bad idea to apply criteria to ANY calculated field, since
this will make indexed searching difficult or impossible.

 

[amber ptuk]

hi

 

[Armen Stein]

If you have proper indexes on the table, ODBC connections, and properly
written queries, Access parses the JET query into SQL syntax and retrieves
only the requested records.

Yes, this is true most of the time, but even some "properly written"
queries won't have the processing done by SQL Server. For example,
I've run tests with subqueries against Northwind that take 20 seconds
to run with linked tables, but are practically instantaneous as a
passthrough. Same exact syntax, so it appears that ODBC can't hand
*everything* over to SQL Server, even when there are no
Access-specific functions.

For example, this query illustrates that dramatic difference:

SELECT *
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT TOP 10 PERCENT
Customers.CustomerID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Customers.CustomerID
ORDER BY Sum([Order Details].[UnitPrice]*[Quantity]*[Discount])
DESC)));

If you want to demonstrate this, you can use a trace on the connection in
SQL/Server.

Yes, this is essential if you want to see what SQL Server is really
doing and what it is sending back to Access.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jellifish]

Yes, this is true most of the time, but even some "properly written"
queries won't have the processing done by SQL Server. For example,
I've run tests with subqueries against Northwind that take 20 seconds
to run with linked tables, but are practically instantaneous as a
passthrough. Same exact syntax, so it appears that ODBC can't hand
*everything* over to SQL Server, even when there are no
Access-specific functions.

For example, this query illustrates that dramatic difference:

SELECT *
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT TOP 10 PERCENT
Customers.CustomerID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Customers.CustomerID
ORDER BY Sum([Order Details].[UnitPrice]*[Quantity]*[Discount])
DESC)));

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Armen, thanks for looking into this, do you know if it the subqueries, the
TOP 10 in the SELECT or the ORDER BY clause that is causing this?

 

[John W. Vinson]

Yes, this is true most of the time, but even some "properly written"
queries won't have the processing done by SQL Server. For example,
I've run tests with subqueries against Northwind that take 20 seconds
to run with linked tables, but are practically instantaneous as a
passthrough. Same exact syntax, so it appears that ODBC can't hand
*everything* over to SQL Server, even when there are no
Access-specific functions.

For example, this query illustrates that dramatic difference:

SELECT *
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT TOP 10 PERCENT
Customers.CustomerID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Customers.CustomerID
ORDER BY Sum([Order Details].[UnitPrice]*[Quantity]*[Discount])
DESC)));

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Armen, thanks for looking into this, do you know if it the subqueries, the
TOP 10 in the SELECT or the ORDER BY clause that is causing this?

I've certainly read that the JET/ACE query optimizer does a poor job
implementing the IN(SELECT...) clause. Try an alternative syntax: create and
save the TOP 10 query, and join it to your Customers table.

 

[Armen Stein]

Yes, this is true most of the time, but even some "properly written"
queries won't have the processing done by SQL Server. For example,
I've run tests with subqueries against Northwind that take 20 seconds
to run with linked tables, but are practically instantaneous as a
passthrough. Same exact syntax, so it appears that ODBC can't hand
*everything* over to SQL Server, even when there are no
Access-specific functions.

For example, this query illustrates that dramatic difference:

SELECT *
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT TOP 10 PERCENT
Customers.CustomerID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Customers.CustomerID
ORDER BY Sum([Order Details].[UnitPrice]*[Quantity]*[Discount])
DESC)));

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Armen, thanks for looking into this, do you know if it the subqueries, the
TOP 10 in the SELECT or the ORDER BY clause that is causing this?

I don't know exactly, I would need to play around with it to figure it
out. If I have time soon I will. Or you can! Just hook your
database up to Northwind and try some different combinations.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jellifish]

I don't know exactly, I would need to play around with it to figure it
out. If I have time soon I will. Or you can! Just hook your
database up to Northwind and try some different combinations.

I don't have permissions to run a trace at work, and I'm stuck with the
Express version at home.

 

[Paul Shapiro]

The last time I looked the SQL Server Developer Edition was only $50. That
includes all the features of the Enterprise edition, but licensed for a
single connection and no production usage. If you're doing development for
work, maybe they would spring for the $50.