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?
Here's the query (the employee database name is variablized so I can switch
easily back and forth between Access and SQL ... v6.5, by the way):
SELECT HomePhone.DirectoryName AS HomePhoneDirectoryName,
ApplicationName, NickName, Applications.Comments,
QualifierName, GroupName,
Minutes, AndOr,
DisplayName, LastName,
#EmployeeDB#.Phone AS BusinessPhone,
BusinessPhoneComment, Mobile, MobilePhoneComment, Pager, PagerComment,
EmployeeHomePhone.Phone AS HomePhone,
HomePhoneComment, Email, EmailComment,
ApplicationEscalations.ApplicationID,
ApplicationEscalations.AndOrOrder, ApplicationEscalations.Step
FROM (
(((( ((
(
#EmployeeDB#
LEFT JOIN EmployeeComments ON
(#EmployeeDB#.DirectoryName = EmployeeComments.DirectoryName))
LEFT JOIN EmployeeHomePhone ON
(#EmployeeDB#.DirectoryName = EmployeeHomePhone.DirectoryName))
LEFT JOIN Homephone ON (#EmployeeDB#.DirectoryName =
HomePhone.DirectoryName))
INNER JOIN ApplicationEscalations ON
((#EmployeeDB#.DirectoryName = ApplicationEscalations.ContactDirectoryName)
<cfif ApplicationIDSearch NEQ "">
AND
(ApplicationEscalations.ApplicationID = #ApplicationIDSearch#)
</cfif>
))
INNER JOIN Applications ON
(Applications.ApplicationID = ApplicationEscalations.ApplicationID))
INNER JOIN ApplicationNicknames ON
((Applications.NickNameID = ApplicationNicknames.NickNameID)
<cfif TitleSearch NEQ "">
AND ((ApplicationNicknames.Nickname
LIKE '%#TitleSearch#%')
OR
(Applications.ApplicationName LIKE '%#TitleSearch#%'))
</cfif>
))
INNER JOIN Qualifiers ON ((Applications.QualifierID
= Qualifiers.QualifierID)
<cfif QualifierSearch NEQ "">
AND (Qualifiers.QualifierName LIKE
'%#QualifierSearch#%')
</cfif>
))
INNER JOIN Infoman ON (Applications.GroupID =
Infoman.GroupID)
)
ORDER BY Applications.ApplicationName,
ApplicationEscalations.ApplicationID, ApplicationEscalations.Step,
ApplicationEscalations.AndOrOrder
Thanks ...
------
Paul Nagai
------------------------------------------------------------------------------
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.
