Results 1 to 13 of 13
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Avoid Disk Access

    I have small table (about 50 lines) and another table (about 300 lines). These two tables work together with some logic in our application that can take up to an hour to process!

    Currently this is very heavy on the disk. So can we write these tables to some temp location in RAM to avoid disk I/O ?

    The entire DB is actually 50 GB so putting the entire DB in RAM would not be an option (if even possible).

    Well, it doesn't seem like this would be too hard to do right? Any help would be appreciated

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    what is the query? it's hard for me to imagine doing anything with two tables that small that could take an hour.

    unless you are modeling an entangled QM system with 50 or 300 particles. that would take more than an hour.

  3. #3
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by jezemine View Post
    what is the query? it's hard for me to imagine doing anything with two tables that small that could take an hour.

    unless you are modeling an entangled QM system with 50 or 300 particles. that would take more than an hour.
    One table is a pdf file file normalized into a db with x and y coordinates. and another is parser configuration settings the application is Microsoft X++ Axapta, known as Microsoft AX.

  4. #4
    Join Date
    May 2008
    Posts
    97
    So it is safe for me to assume that we don't have to ability to create a table into RAM?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SQL Server is pretty good at caching data that it needs. I am assuming here that you are seeing a lot of disk access when you run this query? What does the join look like? Are there user defined functions in the query that call some larger tables?

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by Eric the Red View Post
    So it is safe for me to assume that we don't have to ability to create a table into RAM?
    No, that is NOT a safe assumption.

    I get the feeling that there is something you are not telling us.

    Any query relating a 50 record table to a 300 record table taking more than a tenth of a second to run has some other issues.

    There is something else happening here.

    Please post everything you have.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Eric the Red View Post
    So it is safe for me to assume that we don't have to ability to create a table into RAM?
    It is actually pretty easy to force tables into ram if you have an SSD, and that does help disk I/O hotspots tremendously. The problem is that about 98% of the hotspots are caused by either schema or coding problems, which are usually a lot cheaper/easier to fix.

    You are asking us to help you fix a technical problem. My guess is that you are assuming that you know what the problem is, but you don't have any measurements that confirm that the problem exists.

    I'm very much with PracticalProgram in wanting to see more of your problem. I doubt that pushing the tables into RAM will make much difference until you find and fix the underlying problem, and once you fix that underlying problem or problems I'm not at all sure that the RAM issue will mean anytihng.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    May 2008
    Posts
    97
    Okay I will give you specifics about the data my application is parsing. Maybe then you can tell me if you still think its an application code/design flaw.

    I did a benchmark just now.

    PDF to parse = 52 pages, 1450 lines of data, 5 columns.
    Time to Process = 12.23 minutes

    DB IOPS = 80

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At 80 IOPS spread over 12.23 minutes, you are doing less than one I/O operation every nine seconds. Doing 80 IOPS to a CD wouldn't have a significant impact on performance unless the drive had to spin up for every IOP!

    Parsing PDF files to a database can be a fast operation. I have used one customized parser that pulled eight columns of data from a PDF file into a table at 11 pages (440 rows) per second, and I'm pretty sure that it could be done much faster than that.

    I really think that SQL Server is not the weak point in this process. To test my assumption, see how fast Adobe or some other product can render the document. If it can render much faster than your converstion routine, then the routine is the place where I would start working to improve performance.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    May 2008
    Posts
    97
    Thank you for the insight, it should be very useful!

  11. #11
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by Pat Phelan View Post
    At 80 IOPS spread over 12.23 minutes, you are doing less than one I/O operation every nine seconds. Doing 80 IOPS to a CD wouldn't have a significant impact on performance unless the drive had to spin up for every IOP!

    Parsing PDF files to a database can be a fast operation. I have used one customized parser that pulled eight columns of data from a PDF file into a table at 11 pages (440 rows) per second, and I'm pretty sure that it could be done much faster than that.

    I really think that SQL Server is not the weak point in this process. To test my assumption, see how fast Adobe or some other product can render the document. If it can render much faster than your converstion routine, then the routine is the place where I would start working to improve performance.

    -PatP
    Ohh Question for you. Which PDF Parser 3rd Party Component are you using? I want to try something for myself

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The client was using a C# application to parse the PDF file using a GPL library. I will be there late in October, but I'll shoot them an email to see if one of the geeks can get me an answer for your question.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    May 2008
    Posts
    97
    Thanks that would be great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •