odd performance

odd performance

Post by Frank D. Grec » Fri, 04 Jul 2003 13:15:51



<<x-no-archive: yes>>

I have an application that talks to Sybase via garden variety SQL.
The database is medium to large sized.

When I run a particular query, it usually takes about 60 seconds
to retrieve about 5000 rows.  For the most part, its consistently
around 60 seconds.  However, the *same* query occasionally can take
up to 20 min during various parts of the day.  When I run sp_who,
I only see about 100 users connected but with only 4 or 5 users
doing selects or inserts.

Anyone have any ideas what's going on?  Its weird.

Frank
+=========================================+
| Crossroads Technologies Inc.            |
| Enterprise Java Engineering             |
|   Web: www.CrossroadsTech dot com       |

+=========================================+

 
 
 

odd performance

Post by Anthony Mandi » Fri, 04 Jul 2003 14:12:27



> <<x-no-archive: yes>>

        Sorry, but that's not going to work.

Quote:> I have an application that talks to Sybase via garden variety SQL.
> The database is medium to large sized.

> When I run a particular query, it usually takes about 60 seconds
> to retrieve about 5000 rows.  For the most part, its consistently
> around 60 seconds.  However, the *same* query occasionally can take
> up to 20 min during various parts of the day.  When I run sp_who,
> I only see about 100 users connected but with only 4 or 5 users
> doing selects or inserts.

> Anyone have any ideas what's going on?  Its weird.

        Assuming that by "Sybase" you mean a version of ASE, you
        could look at the query's showplan to see what's going on.
        It looks like it might be table scanning or there's some
        resource contention going on (does sp_who show it as being
        blocked when its slow?).

-am     ? 2003

 
 
 

1. Odd Performance Issue

I have a strange problem.  I have an application being developed that
queries the SQL Server database often.  Sometimes, reponse time for these
queries goes to between 4 and 10 seconds.  But the queries are identical,
and 90% of the time it takes 30ms.  The SQL Server being used is currently
the desktop version of SQL 7 running on an NT Workstation (that's not being
used for anything else at the time) with 256MB of RAM.

I've got the Perfomance Monitor running and the Profiler tracing.  I see no
lock deadlocks reported.  I don't think I've seen any lock waits reported in
the performance monitor.  The client is a VB application using ADO.  I've
installed MDAC 2.6 on the client.  The pause is in the server, it seems,
because the profiler does report the duration time as between 4000 and
10000ms.  It always seems to be the same query that suddenly takes a long
time.

Here is the query.  It actually references a view, I'll provide that as
well...

SELECT Unit, Status, CallID,
    Online = CASE When ISNULL(ActiveUnitsView.Online, 0) > 0 THEN 'YES' ELSE
'' END,
    UnitType, StatusCode, BackColor, ForeColor, Bold, Italic
    FROM ActiveUnitsView
    WHERE (NOT ISNULL(UnitType, ' ') = 'D')
    ORDER BY Unit

Here is the ActiveUnitsView.  It is a join of a number of tables:
CREATE VIEW dbo.ActiveUnitsView
AS
SELECT UnitShiftEntry.UnitKey AS Unit,
    UnitsActive.UnitShiftEntryKey,
    UnitsActive.LastActivity AS StatusCode,
    UnitsActive.CADCallKey AS CallID,
    CADCallActivityTypeRef.CADCallActivityName AS Status,
    Unit.UnitType, UnitsOnlineView.Cnt AS Online,
   ISNULL(Officer.OfficerRank + ' ', '') + ISNULL(Officer.LastName, '') +
ISNULL(', ' + Officer.FirstName, '')  AS StaticOfficer,
    CADCallActivityTypeRef.BackColor,
    CADCallActivityTypeRef.ForeColor,
    CADCallActivityTypeRef.Bold,
    CADCallActivityTypeRef.Italic,
    CADCallActivityTypeRef.MobileCode,
    UnitTypeRef.MultiCall

FROM UnitsActive LEFT OUTER JOIN
    UnitShiftEntry ON UnitsActive.UnitShiftEntryKey =
UnitShiftEntry.UnitShiftEntryKey LEFT OUTER JOIN
    Unit ON UnitShiftEntry.UnitKey = Unit.UnitKey LEFT OUTER JOIN
    CADCallActivityTypeRef ON CADCallActivityTypeRef.CADCallActivityTypeKey
= UnitsActive.LastActivity LEFT OUTER JOIN
    UnitsOnlineView ON Unit.UnitKey = UnitsOnlineView.UnitKey LEFT OUTER
JOIN
    Officer ON unit.StaticOfficerKey = Officer.OfficerKey LEFT OUTER JOIN
    UnitTypeRef ON Unit.UnitType = UnitTypeRef.UnitType

Which references the UnitsOnlineView, which is defined as:

2. Network Suggestions???

3. Odd performance problem

4. Generate an e-mail from Pick

5. Odd performance dropoff inside a cursor

6. Help ! Tempdb !

7. Odd Performance Problem

8. InterDev and Progress

9. Odd Performance Spikes in ASE 11.9.2

10. Very odd performance problem

11. Odd view performance

12. Online 7.11 Select Performance - Odd Results

13. SQL Server Performance Problem - Good query performance, bad update performance