Sage 100

Several months ago there was a discussion about Linked tables vs pass through queries and the performance of each.  I am trying to find that thread but can't seem to find it.  Does anyone remember this and happen to have a link to it?

Thanks!

------------------------------
John Leonard
President
JLA Consulting
Metairie LA
504-835-9639
------------------------------

John,  I've found this discussion from February, and I found another from last September.  Take a look at it while I look for the discussion that was moved over to the new platform.

------------------------------
Michelle Taylor
ERP Team Manager, CS3 Technology
918-388-9772
------------------------------

------Original Message------

John,  I've found this discussion from February, and I found another from last September.  Take a look at it while I look for the discussion that was moved over to the new platform.

------------------------------
Michelle Taylor
ERP Team Manager, CS3 Technology
918-388-9772
------------------------------

I suspect you might be looking for the thread I started comparing the performance of a linked table vs pass-through query in Access with a real world example amd test results against a large table. I can't find it on this 90M platform so I guess it didn't get migrated.
Regardless, what I found was that when you create a linked table, access copies the table's index info so any query you create that uses the linked table will benefit from the index.
This is incredibly important/beneficial if dealing with a table with a lot of records and you are using criteria that can use any one of the indexed fields, even the fields used in multi-field indexes and non-primary indexes.
A PTQ on the other hand will only benefit from the table's indexed fields if you embed the criteria against the fields in a WHERE clause in the PTQ's SQL statement. If you create your PTQ with a simple SQL statement like "SELECT * FROM AR_InvoiceHistoryHeader", any queries that use that PTQ as a source will query the entire record set before your WHERE clause is applied to it.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 05-30-2019 17:06
From: John Leonard
Subject: Linked tables vs pass through queries performance

Michelle,

 

The one from Feb is not the one I was thinking of, so it must be from September.

 

Thanks for your help on this.

 

 

 

John P. Leonard, CPA, CITP

JLA Consulting

(504) 835-9639

(504) 835-7850 (FAX)

www.jlaconsulting.net

 

Original Message------

John,  I've found this discussion from February, and I found another from last September.  Take a look at it while I look for the discussion that was moved over to the new platform.

------------------------------
Michelle Taylor
ERP Team Manager, CS3 Technology
918-388-9772
------------------------------

I agree with David, but I can add that if your report uses criteria other than keyed or index fields (or none at all), your report may benefit from how PTQ's are handled by the Microsoft Jet database engine (natively part of Windows since 2000). Note that the full version of MS Access is not needed to run PTQ's or links. Jet copies the table selected in the PTQ to a temporary file in a subfolder of the local workstation's designated TEMP folder (e.g. C:\Users\<<userid>>\Local\Temp\<<n>>). This is not unlike importing the table into a local Access database and running the report from there. This can have substantial benefits for a complex report that is not substantially filtered by keyed or indexed fields. As with any MS Access file, you will run into issues if the tables exceed 2 GB, but this can be mitigated if one avoids the simple "SELECT * FROM..." statement in the PTQ and specifies only the needed fields. This also can improve performance by decreasing data transfer time. A WHERE statement can be added also, but be sure to use ProvideX SQL syntax and not Jet Syntax ("Pass Through!" - get it?).  In Crystal Reports, check the Report menu for the Performance Timing command and then look for Performance Information to see a breakdown of how Crystal spends its time.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Original Message:
Sent: 05-31-2019 00:49
From: David Speck II
Subject: Linked tables vs pass through queries performance

I suspect you might be looking for the thread I started comparing the performance of a linked table vs pass-through query in Access with a real world example amd test results against a large table. I can't find it on this 90M platform so I guess it didn't get migrated.
Regardless, what I found was that when you create a linked table, access copies the table's index info so any query you create that uses the linked table will benefit from the index.
This is incredibly important/beneficial if dealing with a table with a lot of records and you are using criteria that can use any one of the indexed fields, even the fields used in multi-field indexes and non-primary indexes.
A PTQ on the other hand will only benefit from the table's indexed fields if you embed the criteria against the fields in a WHERE clause in the PTQ's SQL statement. If you create your PTQ with a simple SQL statement like "SELECT * FROM AR_InvoiceHistoryHeader", any queries that use that PTQ as a source will query the entire record set before your WHERE clause is applied to it.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 05-30-2019 17:06
From: John Leonard
Subject: Linked tables vs pass through queries performance

Michelle,

 

The one from Feb is not the one I was thinking of, so it must be from September.

 

Thanks for your help on this.

 

 

 

John P. Leonard, CPA, CITP

JLA Consulting

(504) 835-9639

(504) 835-7850 (FAX)

www.jlaconsulting.net

 

Original Message------

John,  I've found this discussion from February, and I found another from last September.  Take a look at it while I look for the discussion that was moved over to the new platform.

------------------------------
Michelle Taylor
ERP Team Manager, CS3 Technology
918-388-9772
------------------------------

------Original Message------

I agree with David, but I can add that if your report uses criteria other than keyed or index fields (or none at all), your report may benefit from how PTQ's are handled by the Microsoft Jet database engine (natively part of Windows since 2000). Note that the full version of MS Access is not needed to run PTQ's or links. Jet copies the table selected in the PTQ to a temporary file in a subfolder of the local workstation's designated TEMP folder (e.g. C:\Users\<<userid>>\Local\Temp\<<n>>). This is not unlike importing the table into a local Access database and running the report from there. This can have substantial benefits for a complex report that is not substantially filtered by keyed or indexed fields. As with any MS Access file, you will run into issues if the tables exceed 2 GB, but this can be mitigated if one avoids the simple "SELECT * FROM..." statement in the PTQ and specifies only the needed fields. This also can improve performance by decreasing data transfer time. A WHERE statement can be added also, but be sure to use ProvideX SQL syntax and not Jet Syntax ("Pass Through!" - get it?).  In Crystal Reports, check the Report menu for the Performance Timing command and then look for Performance Information to see a breakdown of how Crystal spends its time.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Original Message:
Sent: 05-31-2019 00:49
From: David Speck II
Subject: Linked tables vs pass through queries performance

I suspect you might be looking for the thread I started comparing the performance of a linked table vs pass-through query in Access with a real world example amd test results against a large table. I can't find it on this 90M platform so I guess it didn't get migrated.
Regardless, what I found was that when you create a linked table, access copies the table's index info so any query you create that uses the linked table will benefit from the index.
This is incredibly important/beneficial if dealing with a table with a lot of records and you are using criteria that can use any one of the indexed fields, even the fields used in multi-field indexes and non-primary indexes.
A PTQ on the other hand will only benefit from the table's indexed fields if you embed the criteria against the fields in a WHERE clause in the PTQ's SQL statement. If you create your PTQ with a simple SQL statement like "SELECT * FROM AR_InvoiceHistoryHeader", any queries that use that PTQ as a source will query the entire record set before your WHERE clause is applied to it.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 05-30-2019 17:06
From: John Leonard
Subject: Linked tables vs pass through queries performance

Michelle,

 

The one from Feb is not the one I was thinking of, so it must be from September.

 

Thanks for your help on this.

 

 

 

John P. Leonard, CPA, CITP

JLA Consulting

(504) 835-9639

(504) 835-7850 (FAX)

www.jlaconsulting.net

 

Original Message------

John,  I've found this discussion from February, and I found another from last September.  Take a look at it while I look for the discussion that was moved over to the new platform.

------------------------------
Michelle Taylor
ERP Team Manager, CS3 Technology
918-388-9772
------------------------------

------Original Message------

I suspect you might be looking for the thread I started comparing the performance of a linked table vs pass-through query in Access with a real world example amd test results against a large table. I can't find it on this 90M platform so I guess it didn't get migrated.
Regardless, what I found was that when you create a linked table, access copies the table's index info so any query you create that uses the linked table will benefit from the index.
This is incredibly important/beneficial if dealing with a table with a lot of records and you are using criteria that can use any one of the indexed fields, even the fields used in multi-field indexes and non-primary indexes.
A PTQ on the other hand will only benefit from the table's indexed fields if you embed the criteria against the fields in a WHERE clause in the PTQ's SQL statement. If you create your PTQ with a simple SQL statement like "SELECT * FROM AR_InvoiceHistoryHeader", any queries that use that PTQ as a source will query the entire record set before your WHERE clause is applied to it.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------