|
|
Rank: Fanatic
Joined: 7/20/2006
Posts: 408
Location: Amsterdam
|
On one of our systems we saw huge delays caused by slow queries, after adding some indices we saw huge performance improvements of about ten times faster. These are the indices we added: Code:
CREATE NONCLUSTERED INDEX [IX_nodeId_published_versionId] ON [dbo].[cmsDocument]
(
[nodeId] ASC,
[published] ASC,
[versionId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE CLUSTERED INDEX [IX_versionId] ON [dbo].[cmsContentVersion]
(
[VersionId] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_contentId_id] ON [dbo].[cmsContentVersion]
(
[ContentId] ASC,
[id] ASC
)
INCLUDE ( [VersionId]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
Oh we are using the latest version of Umbraco version 2
|
|
Rank: Addict
Joined: 3/17/2008
Posts: 953
Location: Nyborg, Denmark
|
That's cool Sjors. Would you care to help us improving the current schema - a lot of indexes and references got lost back in the days of porting the db schema for v2.0 and was never improved :-/
It would be wonderful to get the schema back on track - I'm sure there's loads of performance gains as an added value.
Jeeeez, did I really start this :-)
|
|
Rank: Fanatic
Joined: 7/20/2006
Posts: 408
Location: Amsterdam
|
Hi Niels, i can see what i can do, we did create those indices by analysing the heaviest queries, i will see or i can find some other optimisable tables.
|
|
Rank: Devotee
Joined: 3/31/2007
Posts: 66
Location: Switzerland
|
I have been frustrated for some time with the Umbraco db but instead of being frustrated I would like to do something about it (at last)!
I can see quite some room for improvement with the Umbraco DB concerning:
referential integrity,
storage and performance optimisation
I am happy to help optimise the db please send me a data dictionary if you have one if not say so and I will make one and ask question when necessary.
I am putting this post in the forum and in Codeplex so hopefully it gets picked up.
I am no C# guy but I do know my db's (6 years SQL Server + 2 years Ora)
As for O/R mapper I agree with Niels. My experience has been that although they are very good for developers they are not so good for optimisation. This is something that needs some thought depending on the application usage.
Niels if we can get this going then it is a good opportunity to implement the data access layer as mentioned, do you agree?!?
Alec
|
|
Rank: Addict
Joined: 3/17/2008
Posts: 953
Location: Nyborg, Denmark
|
This is fantastic! Alec - shoot me an email and let's get you on the core team aiming at db optimization for v4! /n
Jeeeez, did I really start this :-)
|
|
Rank: Devotee
Joined: 3/31/2007
Posts: 66
Location: Switzerland
|
Done
I have started to write a data dictionary for v3 though. This task alone has shown me just how big umbraco has become. The further I get into it the more I think the db should be rebuilt. Not an impossible task but not a light one either. Also when I look at the forum/discussion requests for thinks like macro, template version control, trash bins performance optimisation etc etc the more I feel this task has to start sooner rather than later before the db becomes even more dirty.
Hoverer building the db is the easy bit. Someone then has to implement the changes in the code... Perhaps using a nice new DAC?
This could also be a good opportunity to enable mySql as a second db option which I know would please some.
Is there anyone working on this already?
If anyone is interested in helping out please get in touch alecgriffiths at gmail.
|
|
Rank: Addict
Joined: 3/17/2008
Posts: 953
Location: Nyborg, Denmark
|
Hi Alec! I wrote you an e-mail suggesting that you start a wiki page and using the discussions on codeplex for this topic. Even though people want a lot of things, an important thing to actually ship something, is knowing that we can't satisfy everyone. As you mention, the db refactor is the easy part. Having a trashcan on things like document types, etc is HUGE as deleting a doctype also have influence on related documents, their properties and eventually published data. Versioning on dev related data should optimally be enabled by making it possible to use a source control system in my opinion. So please, let's be careful without being unambitious. There's absolutely no plans on modifying the db schema for the v3.x lifespan and as such it doesn't really matter how fast we can have something ready. v4 is scheduled for end of 2008, so we have time to take the right and realistic decisions. So start by outlining your ideas and let's get a exciting discussion started. Cheers, Niels...
Jeeeez, did I really start this :-)
|
|
Rank: Devotee
Joined: 3/31/2007
Posts: 66
Location: Switzerland
|
Niels Hartvig wrote:
Having a trashcan on things like document types, etc is HUGE as deleting a doctype also have influence on related documents, their properties and eventually published data.
I wasn't suggesting such things, all I meant is that I didn't want to start the task only to find out you are already doing a partial schema redesign for new features in v4. I will have a go at putting my ideas down on paper over Christmas. -a
|
|
Rank: Addict
Joined: 3/17/2008
Posts: 953
Location: Nyborg, Denmark
|
As you're a much better and more experienced db dev than me, I'll promise not to even look at the db schema without consulting you first.
I'm really happy that you're interested in joining and helping the project. It's situations and people like you I've been dreaming of and the very reason I open sourced umbraco.
Imagine what umbraco could be like if we could turn just a fraction of the collective intelligence in the community into the project. wow :-)
Best,
Niels...
Jeeeez, did I really start this :-)
|
|
|
Guest |