[opencms-dev] OpenCMS/SQL Server - Performance fix (patch attached)

[Geoff Winkless]

18 February 2004 07:45, Andras Balogh wrote:
>     You should check if the opencms DB has indexes and primary keys
> correctly set.

Indexes are all there except a few on the ONLINE_ and BACKUP_ tables
which aren't generated by the mssql init script but which don't seem to
make any difference anyway. It seems that the SQL Server machine (a
spare box we had lying around) simply isn't quick enough: it's running
at approaching 100% process time when I open a file to edit.

I had a bit of a brainwave and ran the SQL Server profiler, and found
most of the queries being run on opening the editor were the same: thus
the attached patch. This changes four of the entries in
mssql/query.properties to use SQL Server stored procedures, which cuts
down dramatically on the amount of time taken.

We're talking a 75% reduction in time taken to open a file in the
editor, at least on my (admittedly not particularly scientific) initial
testing.

Would probably improve general speed overall if we did this for all the
queries, does anyone foresee any problems with doing this? I don't want
to follow through and convert every single query in the query.properties
files into stored procedures if people will violently object for some
reason. 

Obviously we'd also need to modify the install scripts for SQL Server to
include creating the SPs, if we want this to be part of the release.

Disclaimer: attached files are GPL, obviously, since they're
modifications to GPL source. They seem to work for me but come with no
guarantees.

Cheers

Geoff

________________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com
________________________________________________________________________
-------------- next part --------------
A non-text attachment was scrubbed...
Name: mssql-query.properties.diff
Type: application/octet-stream
Size: 3218 bytes
Desc: not available
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20040219/ce2b7710/attachment.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: gen_sps.sql
Type: application/octet-stream
Size: 3978 bytes
Desc: not available
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20040219/ce2b7710/attachment-0001.obj>