Help: Optimize performance.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Help: Optimize performance.

  1. #1
    ethereal@iname.com Guest

    Help: Optimize performance.


    recently a customer site has hit a problem.
    Performance relating to 1 table seems to have crawled.
    that table contains 1 blob field.

    currently inserts ,updates , deletes are slow like crap.

    recently (1wk ago) I did a database device migration from (messy all over
    the place database devices) into a single directory 500mb x 10 datadevices.

    How can I resolve this?
    dbcc checkdb returns nothing.
    there are indexes on relevant fields, (but can't index blob)
    update statistics are done weekly.

    please reply to my email. all suggestions are appreciated.


  2. #2
    Colin McGuigan Guest

    Re: Help: Optimize performance.

    ethereal@iname.com wrote in message <38fd38da$1@news.devx.com>...
    >
    >recently a customer site has hit a problem.
    >Performance relating to 1 table seems to have crawled.
    >that table contains 1 blob field.
    >
    >currently inserts ,updates , deletes are slow like crap.
    >
    >recently (1wk ago) I did a database device migration from (messy all over
    >the place database devices) into a single directory 500mb x 10 datadevices.
    >
    >How can I resolve this?
    >dbcc checkdb returns nothing.
    >there are indexes on relevant fields, (but can't index blob)
    >update statistics are done weekly.
    >
    >please reply to my email. all suggestions are appreciated.
    >


    How about some more pertinent information, like what type of database you're
    using (SQL Server, Oracle, etc)? My suggestion would be to make a new table
    (called 'BLOBData', or similar), that has only two fields; an identity
    primary key, and the blob field. Remove the blob field from the customer
    table, and replace it with a foreign key to the new BLOBData table. Getting
    rid of the BLOBs should reduce page size and speed up any changes to the
    table.

    --
    Colin McGuigan




  3. #3
    C. E. Buttles Guest

    Re: Optimize performance.

    What's in the blob field? You could move the content out to disk and just
    store a pointer in SQL Server.

    Usually upgrading to more and faster processors and/or more memory can do
    the trick. Also, check available disk space and your virtual memory
    (pagefile.sys).


    <ethereal@iname.com> wrote in message news:38fd38da$1@news.devx.com...
    >
    > recently a customer site has hit a problem.
    > Performance relating to 1 table seems to have crawled.
    > that table contains 1 blob field.
    >
    > currently inserts ,updates , deletes are slow like crap.
    >
    > recently (1wk ago) I did a database device migration from (messy all over
    > the place database devices) into a single directory 500mb x 10

    datadevices.
    >
    > How can I resolve this?
    > dbcc checkdb returns nothing.
    > there are indexes on relevant fields, (but can't index blob)
    > update statistics are done weekly.
    >
    > please reply to my email. all suggestions are appreciated.
    >



  4. #4
    D. Patrick Hoerter Guest

    Re: Help: Optimize performance.

    ethereal,

    Sounds like you may have hosed yourself on disk thrashing. You would be
    better off segregating this file group over several distinct disk
    subsystems.

    Regards,
    D. Patrick Hoerter
    ethereal@iname.com wrote in message <38fd38da$1@news.devx.com>...
    >
    >recently a customer site has hit a problem.
    >Performance relating to 1 table seems to have crawled.
    >that table contains 1 blob field.
    >
    >currently inserts ,updates , deletes are slow like crap.
    >
    >recently (1wk ago) I did a database device migration from (messy all over
    >the place database devices) into a single directory 500mb x 10 datadevices.
    >
    >How can I resolve this?
    >dbcc checkdb returns nothing.
    >there are indexes on relevant fields, (but can't index blob)
    >update statistics are done weekly.
    >
    >please reply to my email. all suggestions are appreciated.
    >



  5. #5
    John Baker Guest

    Re: Help: Optimize performance.


    It really depends on what is in that blob field. Is the blob something like
    a picture? If so then store the relative path to the directory where the
    picture is located. If that is not possible then I would try to move the
    blob data out of the table into a seperate table and create a one to one
    relationship between the new blob table and the previous table. At least
    then the updates to the heavily hit table would go quicker.

    John D. Baker
    Computer Solutions
    jdbaker01@yahoo.com

    "ethereal@iname.com" <ethereal@iname.com> wrote:
    >
    >recently a customer site has hit a problem.
    >Performance relating to 1 table seems to have crawled.
    >that table contains 1 blob field.
    >
    >currently inserts ,updates , deletes are slow like crap.
    >
    >recently (1wk ago) I did a database device migration from (messy all over
    >the place database devices) into a single directory 500mb x 10 datadevices.
    >
    >How can I resolve this?
    >dbcc checkdb returns nothing.
    >there are indexes on relevant fields, (but can't index blob)
    >update statistics are done weekly.
    >
    >please reply to my email. all suggestions are appreciated.
    >



  6. #6
    C. E. Buttles Guest

    Re: Optimize performance.

    One other idea. Is one of your indexes a clustered index. If so, you are
    creating a lot of work for your server as clustered indexes physically
    rearrange the data.

    It sounds like you might have a small server (500MB directory, etc.) so you
    might want to look at your hardware as a source of difficulty (# of
    processors, memory, swap file allocation, spare disk space, network hardware
    and connection types, etc.)


    "C. E. Buttles" <cebuttle@sprintsvc.net> wrote in message
    news:38fde535@news.devx.com...
    > What's in the blob field? You could move the content out to disk and just
    > store a pointer in SQL Server.
    >
    > Usually upgrading to more and faster processors and/or more memory can do
    > the trick. Also, check available disk space and your virtual memory
    > (pagefile.sys).
    >
    >
    > <ethereal@iname.com> wrote in message news:38fd38da$1@news.devx.com...
    > >
    > > recently a customer site has hit a problem.
    > > Performance relating to 1 table seems to have crawled.
    > > that table contains 1 blob field.
    > >
    > > currently inserts ,updates , deletes are slow like crap.
    > >
    > > recently (1wk ago) I did a database device migration from (messy all

    over
    > > the place database devices) into a single directory 500mb x 10

    > datadevices.
    > >
    > > How can I resolve this?
    > > dbcc checkdb returns nothing.
    > > there are indexes on relevant fields, (but can't index blob)
    > > update statistics are done weekly.
    > >
    > > please reply to my email. all suggestions are appreciated.
    > >

    >



  7. #7
    bj thomson Guest

    Re: Optimize performance.


    recently a customer site has hit a problem.
    > Performance relating to 1 table seems to have crawled.
    > that table contains 1 blob field.
    >
    > currently inserts ,updates , deletes are slow like crap.
    > recently (1wk ago) I did a database device migration from (messy all
    >over
    > the place database devices) into a single directory 500mb x 10 datadevices.

    It may be that the original DBA deliberately put the devices on specific
    disks for performance... its conventional to put log and data on different
    discs, sometimes putting certain tables on diferent discs can help too -
    is this SQL Server 6.5 or 7?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center