Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Steven Wilber 103 posts 98 karma points
    Jul 30, 2009 @ 00:09
    Steven Wilber
    0

    Database incredibly slow - please help!

    We run lots of umbraco projects and just one is amazingly slow. It is on a server with about 20 other projects, all of which are fine.

    To load an Umbraco page in the backend takes around 20-30 seconds, whereas other sites load up in around 1 second, if not less.

    Running SQL Server Profiler it returns around 84,000 rows. Not all represent a SQL Statement, but a lot do, so it seems to be an incredible amount.

    We have upgraded to 4.0.2.1.

    We are just not sure where to go from here. Does anyone have any advice on:

    - what settings may have been set on this particular database to make it so slow?

    - if it could perhaps be something else (the public website is fine and fast, so that's why we believe it to be the database)

    - what to look for in the SQL Profile and what to do about it when we find it

    - Anything else we should look at.

    The document types are not complicated with around 20 items at most, all of which are fairly simple standard data types.

     

    Please help as we are completely stumped.

     

    Cheers

     

    Steve

  • Tom Maton 387 posts 660 karma points
    Jul 30, 2009 @ 00:16
    Tom Maton
    1

    Hi Steve,

    Have you tried re-indexing the database tables?

    What version did you upgrade from?

    Have you tried running "fiddler tool" on your browser to see what component takes its time loading?

    Also what datatypes and how many do you have on the pages taking the longest and are they all default or is there some custom ones?

    Tom

  • Roel Snetselaar 151 posts 305 karma points
    Jul 30, 2009 @ 00:33
    Roel Snetselaar
    1

    When I have database issues I usually run a SQL profiler for a couple of minutes (time needed to run the profiler depends on the traffic of the website of course) Make sure to log the profiler data into a table within SQL Server.

    Now you can run a query on that table ordered by execution time. So you can see if certain queries take a lot of time and also if a particular query is executed a lot of times.

    Now you can copy the query and run it within SQL Server Management studio. Make sure to check the option for "include actual execution plan" After the query has finished you can check the execution plan for "index scans" or "table scans" you don't want these. You want to have "seeks"

    If scans occur check out the fields which are used in the "where" part of the query. Probably you need to create extra indexes for these fields.

    You could also look at server performance of the database server of course and/or the webserver. Does the application recycle a lot? does it use a lot of memory on the webserver? what's the processor load on your database server? Do you run maintenance plans on your database server to keep indexes up to date? Did you import lots of new data into your database? Did you disable caching? I would check all this to isolate your problem.

     

     

  • Steven Wilber 103 posts 98 karma points
    Jul 30, 2009 @ 11:16
    Steven Wilber
    0

    Hi guys,

    Thanks for the responses.

    The actual problem it turns out is what appears to be a simple datatype that in some way is thrashing the database, I suspect through some kind of misguided recursion. Taking out datatypes one by one from the doctype identified the problem. Without the naughty datatypes pages load sub second.

    So the next stage now is to debug the datatypes.

     

    Thanks for your help

     

    Cheers

     

    Steve

  • Chris Houston 535 posts 980 karma points MVP admin c-trib
    Jul 30, 2009 @ 11:27
    Chris Houston
    0

    Hi Steve,

    Maybe when you have worked it out ( I think your right it sounds like some sort of recursion ) maybe you could write a SQL statement that can find such issues and post it to this thread for others who receive this iregularity.

    Cheers,

    Chris

  • Morten Bock 1867 posts 2140 karma points MVP 2x admin c-trib
    Jul 30, 2009 @ 11:38
    Morten Bock
    0

    Steven, it would be interresting to hear which datatype it was, if it is a standard umbraco datatype?

  • Steven Wilber 103 posts 98 karma points
    Aug 12, 2009 @ 23:19
    Steven Wilber
    0

    Hi all,

    It was a hand crafted data type which we have since rewritten three times to speed it up, but with no success. Using the Umbraco log it appears to take no time at all to init and render and yet including two of them within a document type makes everything soooo slow!

    I'll keep digging, but thanks for the comments.

     

    Cheers

    Steve

  • Steven Wilber 103 posts 98 karma points
    Jan 28, 2010 @ 22:10
    Steven Wilber
    0

    Sorry,

    I forgot to post an update. Data types are made up of three components, the DataType itself and then the Developers editor view (Prevalue Editor) to help set up the configuration for the datatype and the Editor view for web editors to see.

    When the editor loads it needs to know the configuration of the Prevalues, i.e. the configuration set for the datatype.

    Convention has it that in order to get the prevalues you create an instance of the prevalue editor and then get the configuration from that. Well in our prevalue editor, you had to choose a content node (from which child nodes would be presented in the editor). Thus when editing the content the Editor would load the PreValue editor to get the config. The Prevalue editor would needlessly create a list of every content item in Umbraco as it created and then threw away its interface just to get the config items.

    Obviously I've now split this up a little more so that I have a separate configuration class that is part of the DataType rather than being in the PreValue editor.

    I hope that this makes sense to someone, but it speeded up the datatype no end - silly mistake really.

    Cheers

    Steve

Please Sign in or register to post replies

Write your reply to:

Draft