Posted by DickChristoph on 03/26/07 02:47

Hi

Greg had very good advice that you may have missed if you have never used
SQL Profiler. SQL Profiler will show you the actual SQL code that is being
executed by the application (if it is inline SQL) or what Stored Procedures
are being called.

All this might be of limited usefulness if you don't have access to
programmers who can change poorly written code in the application. I am
thinking someone is perceiving "the appilcation is slow" and have decided to
kick the DB guy, when in fact the problem is with the application, Perhaps
the app is marching through ADO Recordsets to join data when it could be
done using basic SQL functionality.

For your original question if you have

MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts

MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits

MatterConflicts.MatterConflicts should be the Primary key of MatterConflicts
( think you said it was)

MatterConflictsHits.MatterConflictsHits should be the Primary key of
MatterConflicts ( think you said it was)

Add an index on MatterConflictHits.MatterConflicts this should speed up join
operations between the two tables.

Best of Luck.

-Dick Christoph


"Zamdrist" <zamdrist@gmail.com> wrote in message
news:1174682594.964521.45320@y66g2000hsf.googlegroups.com...
> On Mar 23, 1:59 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@greenms.com> wrote:
>>
>> If you have no access to these tables, you can't put indexes on it. So
>> perhaps I misunderstand.
>
> No I have access to all the objects via Enterprise Manager, I just
> don't have access to the application code to see what it is doing,
> there are however only a handful of queries/procedures, and over 70+
> tables, so I fear the worst.
>>
>> In any case, you probably CAN get this information via profiler and
>> looking
>> at the queries as they come through the machine.
>>
>> Also, there's some tools (only source I know of is via the SQL Server
>> Magazine website so they're copyrighted I believe) to follow wait
>> statistics, which can be VERY powerful to find out where your application
>> is
>> doing a lot of querying.
>>
>> You can also try (though generally I don't find it useful) the Index
>> Wizard
>> in EM.
>
> Thanks Greg.
>

[Back to original message]