"Greg D. Moore (Strider)" <mooregr_deleteth1s@greenms.com> wrote in message
news:4fgNh.17267$Jl.14634@newsread3.news.pas.earth link.net...
> "David Cressey" <cressey73@verizon.net> wrote in message
> news:s2eNh.248$E46.187@trndny09...
> >
> >> > A million records isn't large? Ok.
> >>
> >> Nah, rather trivial these days. ;-)
> >
> > Does "trivial" mean easy or unimportant?
>
> No, in this case it means rather small which impacts how you approach
> maintainence issues. And to some extent how you solve problems.
>

I'd suggest that that's a misuse of the word "trivial", but that you might
have meant "small enough to be unimportant".




> For example, for some databases, it may be "simpler" to simply through
more
> memory at the problem. For a database 10x the size, more memory might not
> even make a dent.

This is because a lot of the work involved in sorting and searching expands
non linearly with regard to volume of data (row cardinality in this case).
In every database I've worked on, the difference between a table scan and
an index lookup has resulted in a "nontrivial" performance difference with a
million rows in the table.

But it depends on what you mean by "non trivial", I suppose.

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.googlegro ups.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.
>

On Mar 23, 11:08 pm, "Zamdrist" <zamdr...@gmail.com> wrote:
> I am tasked with maintaining a large database still on SQL Server 7.0.
> Performance is an issue not surprisingly and I've targeted two rather
> large tables to see what I can do with the indexes.
>
> The 2 tables are described as follows:
>
> 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
>
> Now MatterConflicts row count is approaching 500K and
> MatterConflictHits is approaching 1 Million rows. There are only one
> index on each table, each for the table's primary key. The Matters
> field in MatterConflicts table joins back with a table that users
> access directly.


Drop the index on primary key and create a index on Matters field
instead since this is the column users are using to access data. Also,
Since these two tables are related, it will be worthwhile to consider
a index between the joining columns MatterConflicts of both tables.


> Question is, would it be beneficial to add, or modify the existing
> indexes for these tables to include both the primary and foreign keys,
> as well as an additional field?
>
> Doesn't seem to be to be very beneficial to have an index that only
> includes the primary key. So for example I'm thinking of creating an
> index for MatterConflicts that includes the fields: MatterConflicts,
> Matters, and HitMatters.

no. Don't use composite indexs as they seldom are useful.

> Thoughts? Suggestions? Thanks...

By the way, these tables are fairly large and you are right that you
have started to address the performance issue.

On 26 Mar 2007 21:29:32 -0700, othellomy@yahoo.com wrote:

>> Doesn't seem to be to be very beneficial to have an index that only
>> includes the primary key. So for example I'm thinking of creating an
>> index for MatterConflicts that includes the fields: MatterConflicts,
>> Matters, and HitMatters.
>
>no. Don't use composite indexs as they seldom are useful.

Hi othellomy,

Do you have any source for that sweeping statement?

AFAIK, composite indexes can be VERY useful. For queries that filter or
join on two or more columns, a composite index is much better than
seperate indexes on the individual columns.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Thank you all for the advice and suggestions!

I did run Profiler and found that my suspicious are correct, all
queries are being ran via in-line text statements, no application
procedures exist much less being used.

Quite a bit usage of sp_cursorprepare, exceute, close, etc. also.
Don't know if this is normal or not.

So yeah, basically I'm up a river with no paddle. We have no access to
the application code or the programmer, nor do we even have a support
license agreement anymore with the company who wrote the application.

I did end up archiving off most of the data from the tables in
question and wrote a custom interface to that using ASP.Net. Works
like a charm, no indexes whatsoever. Archiving and subsequently
deleting the data from the production tables, and rebuilding existing
indexes had little to no effect on performance unfortunately.

Again...up a river with no paddle, lol.

Thanks

On Mar 30, 12:48 am, "Zamdrist" <zamdr...@gmail.com> wrote:
>
> I did end up archiving off most of the data from the tables in
> question and wrote a custom interface to that using ASP.Net. Works
> like a charm, no indexes whatsoever.

Does that mean who have no io problems if you use ASP.NET?

Archiving and subsequently
> deleting the data from the production tables, and rebuilding existing
> indexes had little to no effect on performance unfortunately.
> Again...up a river with no paddle, lol.

The indexes you have are useless. Therefore, rebuilding them will not
make any difference. You need to identify what columns (preferably
one, at most 2) you need your clustered indexes for. Hopefully the
system will use those indexes.

On Mar 30, 4:54 am, othell...@yahoo.com wrote:
> On Mar 30, 12:48 am, "Zamdrist" <zamdr...@gmail.com> wrote:
>
>
>
> > I did end up archiving off most of the data from the tables in
> > question and wrote a custom interface to that using ASP.Net. Works
> > like a charm, no indexes whatsoever.
>
> Does that mean who have no io problems if you use ASP.NET?
>
> Archiving and subsequently
>
> > deleting the data from the production tables, and rebuilding existing
> > indexes had little to no effect on performance unfortunately.
> > Again...up a river with no paddle, lol.
>
> The indexes you have are useless. Therefore, rebuilding them will not
> make any difference. You need to identify what columns (preferably
> one, at most 2) you need your clustered indexes for. Hopefully the
> system will use those indexes.

I copied (and subsequently deleted) from production a large amount of
the data to a different table and server, and wrote an interface to
that data in ASP.Net so the users, if needed could look back on older
infrequently used data. Accessing that data works great.

I could build more intelligent indexes for the data in production,
although I doubt the application would behave any differently (faster)
as I've verified it uses no views or stored procedures...it only uses
in-line text based queries...Select fields From table Where...and so
on.

I have no access to the application code to change this behavior
unfortunately.

On Mar 30, 5:57 pm, "Zamdrist" <zamdr...@gmail.com> wrote:
> On Mar 30, 4:54 am, othell...@yahoo.com wrote:
>
>
>
>
>
> > On Mar 30, 12:48 am, "Zamdrist" <zamdr...@gmail.com> wrote:
>
> > > I did end up archiving off most of the data from the tables in
> > > question and wrote a custom interface to that using ASP.Net. Works
> > > like a charm, no indexes whatsoever.
>
> > Does that mean who have no io problems if you use ASP.NET?
>
> > Archiving and subsequently
>
> > > deleting the data from the production tables, and rebuilding existing
> > > indexes had little to no effect on performance unfortunately.
> > > Again...up a river with no paddle, lol.
>
> > The indexes you have are useless. Therefore, rebuilding them will not
> > make any difference. You need to identify what columns (preferably
> > one, at most 2) you need your clustered indexes for. Hopefully the
> > system will use those indexes.
>
> I copied (and subsequently deleted) from production a large amount of
> the data to a different table and server, and wrote an interface to
> that data in ASP.Net so the users, if needed could look back on older
> infrequently used data. Accessing that data works great.
>
> I could build more intelligent indexes for the data in production,
> although I doubt the application would behave any differently (faster)
> as I've verified it uses no views or stored procedures...it only uses
> in-line text based queries...Select fields From table Where...and so
> on.
>
> I have no access to the application code to change this behavior
> unfortunately.- Hide quoted text -
>
> - Show quoted text -

You might consider creating a new clustered index on a column that is
frequently used to join with other tables. Copy paste your code to QA
and run a showplan with no exec, on and find out if it is doing a
table scan or index search.

On Mar 30, 6:11 am, othell...@yahoo.com wrote:
>
> You might consider creating a new clustered index on a column that is
> frequently used to join with other tables. Copy paste your code to QA
> and run a showplan with no exec, on and find out if it is doing a
> table scan or index search.

There is an index already on each of the two tables in question, one
each on the primary key only, and it is clustered.

When I run my own query with Show Execution Plan on, I see that there
are several Clustered Index Scans, Index Scans & Parallelism/
Repartition operations.

But this is my code, running in QA. I have no control over how the
application accesses the data.

Zamdrist wrote:

> On Mar 30, 6:11 am, othell...@yahoo.com wrote:
>> You might consider creating a new clustered index on a column that is
>> frequently used to join with other tables. Copy paste your code to QA
>> and run a showplan with no exec, on and find out if it is doing a
>> table scan or index search.
>
> There is an index already on each of the two tables in question, one
> each on the primary key only, and it is clustered.
>
> When I run my own query with Show Execution Plan on, I see that there
> are several Clustered Index Scans, Index Scans & Parallelism/
> Repartition operations.
>
> But this is my code, running in QA. I have no control over how the
> application accesses the data.

If your query is the same as what the application runs, except for
specific values being plugged in here and there, then the application
will generally get the same execution plan that you do.

Does QA run your query reasonably quickly? Based on the Profiler
trace, does the application seem slow because it runs slow queries,
or because it runs an inefficiently large number of queries which
are reasonably fast individually?