[AccessD] Inquiry System Built With Access 2007 Performance Concern Partial Key

Arthur Fuller fuller.artful at gmail.com
Wed Feb 27 12:18:27 CST 2013


Tina,

You and I seem both to be missing something here. I'm relieved to know that
I am not alone in this.

Unless Brad has mis-stated the problem, I don't get it. The home-built
Inquiry System ought to be able to do just about anything with the
linked-source-data; and if massages are required on said source-data,
that's what ETL is for. (Install a free copy of SQL Server Express, design
some ETL, and you're done.)

Brad, I fail to see the problem here. Please expound.

Thanks,



On Tue, Feb 26, 2013 at 4:34 PM, Tina Norris Fields <
tinanfields at torchlake.com> wrote:

> Okay, I'm probably missing something here.  Can you write new queries?  If
> you can, couldn't you make a select query qselA that pulls all the fields
> of that table, then use qselA as the source for qselB that uses the pair of
> fields as a compound PK?  Sorry if I'm being dense.
> T
>
> Tina Norris Fields
> tinanfields at torchlake.com
> 231-322-2787
>
>
> On 2/24/2013 11:30 AM, Brad Marks wrote:
>
>> 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@**databaseadvisors.com<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@**databaseadvisors.com<accessd-bounces at databaseadvisors.com>
>> [mailto:accessd-bounces@**databaseadvisors.com<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<http://databaseadvisors.com/mailman/listinfo/accessd>
> Website: http://www.databaseadvisors.**com<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