On Mar 30, 8:31 am, Ed Murphy <emurph...@socal.rr.com> wrote:
>
> 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?

Here is the query I ran, it returns 70.8K rows in 2 seconds via QA:

Select M.MatterID From Matters M
Inner Join MatterConflicts MC On MC.Matters = M.Matters
Inner Join Matters M2 On M2.Matters = MC.HitMatters
Inner Join MatterConflictHits MCH On MCH.MatterConflicts =
MC.MatterConflicts

Now the application is doing all kinds of things, probably more
complicated than my query above. Honestly I don't know enough about
Profiler to isolate one operation. I did check it out and there are
many, many sp_cursorexecute, prepare and close statements, along with
many select fields from tables queries.

I dunno, I doubt I will be able to make any sort of significant impact
on performance without access to the code.

FYI: This program is a legal case management software called Prolaw by
Thomson-Elite...it *sucks* royally! LOL.

On Mar 30, 8:51 pm, "Zamdrist" <zamdr...@gmail.com> wrote:
> On Mar 30, 8:31 am, Ed Murphy <emurph...@socal.rr.com> wrote:
>
>
>
> > 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?
>
> Here is the query I ran, it returns 70.8K rows in 2 seconds via QA:
>
> Select M.MatterID From Matters M
> Inner Join MatterConflicts MC On MC.Matters = M.Matters
> Inner Join Matters M2 On M2.Matters = MC.HitMatters
> Inner Join MatterConflictHits MCH On MCH.MatterConflicts =
> MC.MatterConflicts
>
> Now the application is doing all kinds of things, probably more
> complicated than my query above. Honestly I don't know enough about
> Profiler to isolate one operation. I did check it out and there are
> many, many sp_cursorexecute, prepare and close statements, along with
> many select fields from tables queries.
>
> I dunno, I doubt I will be able to make any sort of significant impact
> on performance without access to the code.
>
> FYI: This program is a legal case management software called Prolaw by
> Thomson-Elite...it *sucks* royally! LOL.

Cursors are slow. If it is opening cursors on tables with million rows
then I am afraid there is not much you can do. Besides, opening a
cursor on a table with million records even with indexes can be slow
and I don't think the application developers had performance issue on
their mind when they wrote the code initially. Besides, handling most
of the processing on client side without stored procedures will also
slow things down considerably especially for large tables.

"Zamdrist" <zamdrist@gmail.com> wrote in message
news:1174669705.000774.151130@n59g2000hsh.googlegr oups.com...
>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.
>
> 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.
>
> Thoughts? Suggestions? Thanks...

What are you using for hardware? Something from the year 1999? Do you have
at least 512MB of RAM in it? With 512MB of RAM, unless there's a lot more
tolerably large tables involved, your entire database should fit into main
memory, which is a good place to have it. If you don't already have enough
memory on the box to comfortably fit SQL Server, the OS and any other
ancillary processes into main memory, I'd recommend you buy some. Buy a
gigabyte. In fact, if it's a decent quality server, buy a gigabyte in 4 X
256MB cards, which will usually allow maximum memory interleaving on good
hardware. Memory, nowadays, costs very little - compared to your time.

In terms of contemporary WIntel servers, your database really isn't all that
large. Guessing at like 75% use of the varchar() fields in volved, you've
got something less than 200MB in those two tables. Can you sneak a new
server in under the database? Would the app run against a SQL Server 2000
back end?

I've got a two or three year old, 4 X 700mhz CPU server here, with 16GB of
memory, and if I run a select count(*) from <table> where <check something
that's not indexed> on a 9 million row, 5GB table, it takes 27 seconds the
first time I execute that command and the second time I run that it takes 4
seconds (the second time, the table is in memory). You don't need that much
server to get similar performance.



--
Posted via a free Usenet account from http://www.teranews.com