Brad Marks
BradM at blackforestltd.com
Sun Feb 24 18:49:01 CST 2013
Arthur, The inquiry system that we built is obtaining data from a purchased package's SQL Server Database tables. We can make no changes to the tables, views, indexes, etc. The only updates that can be made are updates to the data via the purchased package's screens. The report writer than came with the purchased package is not good at all. Therefore, we built an inquiry and reporting system with Access, which has worked very nicely so far. I am sure that the performance issue will be resolved, I am just looking for ideas from others who may have run into something similar in the past. Thanks, Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com on behalf of Arthur Fuller Sent: Sun 2/24/2013 4:48 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Inquiry System Built With Access 2007 PerformanceConcern Partial Key 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 -- 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.