Brad Marks
BradM at blackforestltd.com
Thu Feb 28 08:02:53 CST 2013
Arthur,
Here is the situation.
We have a purchased package that uses SQL Server.
We cannot make any changes to the SQL Server database other than the
normal updates that are done through the Purchased Package screens.
We have used Access 2007 to build an Inquiry and Reporting system that
has worked very nicely for over a year.
Now there is a need to obtain additional data.
The data that we need to obtain is contained in a table that we have not
worked with before.
This table has a two part key "Part ID" and "Routing Sequence Number".
I was trying to enhance an existing query to now obtain data from this
new table.
The catch is that the existing query only has Part ID available and not
the routing sequence number.
In initial tests, the query seemed very slow. My theory was that a
"table sweep" was being done on the new table because both parts of the
key were not available.
Let's say that there are 1,000 rows in the new table and the query is
bringing back data from 10,000 rows for the older tables. It appeared
that a sweep of 1,000 rows was being done for each of the 10,000 rows in
the old tables. This seems really slow.
Other higher priority issues have surfaced in a completely different
realm, so this issue has been put on hold for the time being.
Thanks,
Brad
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Wednesday, February 27, 2013 12:18 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Inquiry System Built With Access 2007 Performance
Concern Partial Key
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 databasea
dvisors.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://databasead
visors.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
--
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.