Arthur Fuller
fuller.artful at gmail.com
Sun Feb 24 16:48:44 CST 2013
I must be missing something here. You say that you built the Inquiry system and it's missing a column in one of its tables? Why not just add the column? Arthur On Sun, Feb 24, 2013 at 12:29 PM, Jim Dettman <jimdettman at verizon.net>wrote: > > Try adding a pseudo index on the Access side (add a index on the linked > table just on the part ID). > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks > Sent: Sunday, February 24, 2013 11:30 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Inquiry System Built With Access 2007 > PerformanceConcern Partial Key > > Jim, > > Thanks for your assistance. > > > >> While you cannot change anything existing, can you add a view to the SQL > >> database? > > We are not able to add views to the SQL-Server database. We cannot make > any > updates to anything, except to update data via the purchased package > front-end screens. > > > > >> Not sure I understand this part. When you link the table, do you see > the > >> index with both fields and is that set as the PK field or no? > > It appears that the table has a compound key (Part-ID and > Routing-Sequence-Number). > The Access-based Inquiry system that we have built has the Part-ID, but not > the Routing Sequence-Number available. When I tried to join this table in > one of the existing queries, the data was returned, but it took a long > time. > I have tried several variations of the query and ran quite a few tests. > Due > to the slowness, I have started to consider alternative approaches. I > don't > have a lot of experience with obtaining data from a table with only a > partial key. I would guess that a sweep of the entire table is being > performed, therefore the slowness. > > Brad > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com on behalf of Jim Dettman > Sent: Sun 2/24/2013 10:03 AM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Inquiry System Built With Access 2007 ?Performance > Concern - Partial Key > > Brad, > > <<We are not able to change any of the tables, keys, relationships, > indexes, > etc. in any of the tables in the purchased system. We can only read the > data. >> > > While you cannot change anything existing, can you add a view to the SQL > database? > > <<Currently the Inquiry System does not have the second part of the key > available. Only the Part_ID is available, not the > Routing_Sequence_Number. > (Not all Parts have routings, all we need to discern is whether a part has > a > routing or not) >> > > Not sure I understand this part. When you link the table, do you see the > index with both fields and is that set as the PK field or no? > > Jim. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks > Sent: Saturday, February 23, 2013 06:20 PM > To: Access Developers discussion and problem solving > Subject: [AccessD] Inquiry System Built With Access 2007 ? Performance > Concern - Partial Key > > All, > > We have an Inquiry System that obtains data from a purchased package's SQL > Server database via ODBC. > > The Inquiry System works nicely and response time is quite fast for all > screens. > > We are not able to change any of the tables, keys, relationships, indexes, > etc. in any of the tables in the purchased system. We can only read the > data. > > Recently, a request has been made where we will need to pull data that will > come from a table that has not been used by the Inquiry System before. > > Here is the catch. The new table that we now want to pull data from has a > two-part key (Part_ID, and Routing_Sequence_Number). > > Currently the Inquiry System does not have the second part of the key > available. Only the Part_ID is available, not the > Routing_Sequence_Number. > (Not all Parts have routings, all we need to discern is whether a part has > a > routing or not) > > When we try to obtain data from this table with only the Part_ID, the data > is returned, but response time is slow. This table has about 2,000 rows. > > I am considering two approaches and decided to see if anyone else has run > into something similar and has advice. > > Approach-1. Build an Access table from the SQL Server table, that has only > Part_ID for the key. > > Approach-2. Use the SQL Server data to build an array when the inquiry > system is first initiated then obtain the data from the array. > > I would guess that there may be other approaches that would work better. > > I appreciate your assistance. > > Thanks, > Brad > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Arthur Cell: 647.710.1314 Prediction is difficult, especially of the future. -- Niels Bohr