[AccessD] Inquiry System Built With Access 2007 PerformanceConcern Partial Key

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


More information about the AccessD mailing list