How can we help you today? How can we help you today?

Large DB Schemas -- Performance

Hello Group,

We currently run PeopleSoft Finance on SQL 2000 SP4, Windows 2003 Ent. Server. For those of you who are not familiar with it, a typical finance database runs about 60,000 database objects, which is one of the largest schemas I have seen. I am current experiencing ~18 minute intellisense prompts when working in SQL Server Management Studio and Query Analyzer.

My configurations are as follows:
SQL Prompt 2.0 with Microsoft SQL Server Management Studio 9.00.1399.00, Win 2K SP4, 3 Ghz P4 H/T, 512 RAM

SQL Prompt 2.0 with SQL Query Analyzer 8.00.2039, XP SP2, 1.7 Centrino, 1 GB RAM

Please note this is connecting to the SQL 2000 machine posted above. I am also wondering if the caching can be configured at all in order to make this more responsive. If there are no suggestions then I will wait for 3.0.

I would like to add this is a great tool and works great with all other schemas. I am sold!

Thanks.
dadiutori
0

Comments

2 comments

Sort by
  • Tilman
    Hi David,

    thanks for posting. We are aware of performance issues with large databases and are trying to improve it in the next version. I'm afraid the current version is as good as it is going to get.

    Regards,

    Tilman
    Tilman
    0
  • Bart Read
    Hi David,


    Thanks for posting This is an issue we'd like to address in version 3, and we've designed it with performance in mind, however at this point we've no example databases of this size to test on so I was wondering if you'd be willing to do me a favour please?

    If you've not already got it installed could you download and install SQL Compare 5 from http://www.red-gate.com/products/SQL_Compare/index.htm.

    Then could you run SQL Compare 5, and just compare your Peoplesoft Finance database to itself (this will probably take some time). Once the comparison is complete, from the main menu can you please click "File" > "Save Snapshot of {databasename}" where {databasename} is the name of your Peoplesoft database. This will just save the database schema but *not* any of the data in a snapshot that we should be able to use to reconstruct the database here. Can you then zip up this snapshot and email it to bartDOTreadATredHYPHENgateDOTcom please? Let me just reiterate that this file won't contain any of your company's confidential information (but make sure you choose *not* to save passwords when you create your comparison project if you're using SQL Server authentication rather than Windows authentication), but only the database schema.

    Depending on whether you're using SQL Server 2000 or 2005, and whether or not this database has any encrypted objects in it we should be able to recreate it, at least in terms of its schema, here. If you're running on SQL Server 2005 and some of the objects are encrypted this won't work terribly well. If that were the case what we'd need is a FULL backup of an empty PeopleSoft Financials database that we could restore to a server here.

    If you've any problems emailing it to me please just drop me a message and I'll get an FTP account set up for you to upload it to.

    As I say, I'd really appreciate it if you could do this for us as we'll then have a much more realistic idea of how SQL Prompt 3 will perform on a database of this size and complexity.


    Many thanks,
    Bart Read
    0

Add comment

Please sign in to leave a comment.