You sure its not just something really simple, like you've got different indexes or
constraints set up on the two different systems? Maybe SQL may not be optimizing
properly due to
lack of hints that you are giving to Access...
I know that when I've moved from Access to SQL certain aspects of the table
definitions did not get automatically transferred without a hitch.
"Nagai, Paul" wrote:
> Howdy,
>
> I want to take three tables and merge them with the corporate employee
> database (my outer joins):
> Employee (employee database)
> EmployeeComments (add comments about emps' contact numbers)
> EmployeeHomePhone (add home phone field)
> HomePhone (indicates employee's permission to release home phone)
>
> I want to build a profile of an application (most of my inner joins):
> Application
> Qualifier
> Nickname
> Infoman
>
> The join joins the results of the two sets described above based on a table
> containing escalation steps (averaging 5 steps per escalation
> responsibility, let's say): An Escalation is an application and n
> escalation steps (stepnum, timing, employee).
>
> Records in my pilot system:
> Employee ~5000
> EmployeeHomePhone ~5000
> Employee comments ~10
> HomePhone ~25
>
> Applications ~25
> Qualifiers ~10
> Nicknames ~10
> Infoman ~300 (~10 are in-use ... linked to apps)
>
> Escalations (config'd apps) ~20
>
> When I run this against an Access database (where I developed the database
> design and the queries), it runs very snappily ... sub-second response time
> on the main query. I can even add 400 Applications and Escalations (each
> with 5 steps for 2000 additional records) and it still runs just fine. (~400
> apps/escs is the estimated production size I'm shooting for.)
>
> When I run it against SQL Server (upsized from Access), it is not snappy. It
> takes about 9.5 seconds to run. 20+ seconds to run with 50 test escalations.
> And pegs the server's CPU and fails at the client end (occasionally hanging
> the server) if I add 100 or more records.
>
> Access handles these increases fine and is still sub-second response time up
> to 100 test escalations. I don't have Access response times from 400 test
> escalations, but it's not too much longer than a second, I'd guess.
>
> Onsite SQL gurus (I'm most definitely a novice) are examining my query now,
> looking for ways to optimize, rewrite in more native sql style, store a
> procedure, build temp tables, etc, to help my response time. (Among other
> things, they think it's possible that having developed the query in
> Access-friendly SQL and not more natively MS-SQL articulated SQL, I've
> prevented MS-SQL from efficiently optimizing the query ...)
>
> Anyone care to comment?
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
