the ado store is slow to load a saved diagram

Sep 20, 2012 at 9:32 PM
Edited Sep 21, 2012 at 1:14 AM

i created a diagram of 1 square only.  I saved this into the adoSql Store.

it takes about 30 seconds to load.  this is VERY SLOW.

I  did a sql profiler trace and it is loading about 300 "commands"

see below.  Why is it loading 389 commands for 1 square???

(i am using your Designer.cs project to crate and save the daigrams)

---------------------------------------------------------------------------------------- see below...this is the trace.

exec sp_executesql N'SELECT Name, Type FROM SysParameter WHERE Command = @Command ORDER BY No',N'@Command int',@Command=1

 

exec sp_executesql N'SELECT Name, Type FROM SysParameter WHERE Command = @Command ORDER BY No',N'@Command int',@Command=2

......

......

.....

exec sp_executesql N'SELECT Name, Type FROM SysParameter WHERE Command = @Command ORDER BY No',N'@Command int',@Command=379

exec sp_executesql N'SELECT Name, Type FROM SysParameter WHERE Command = @Command ORDER BY No',N'@Command int',@Command=380

exec sp_executesql N'SELECT Name, Type FROM SysParameter WHERE Command = @Command ORDER BY No',N'@Command int',@Command=381

Sep 21, 2012 at 1:03 AM
Edited Sep 21, 2012 at 1:09 AM

i did more tracing...and still cant figure out why it try to load about 389 sql commands to reload a 1 shape diagram.

please help.

Coordinator
Sep 21, 2012 at 8:01 AM
alexdoan102 wrote:
it takes about 30 seconds to load.  this is VERY SLOW.

 30 seconds is indeed too long. On my machine, it takes less than 2 seconds to open a (small) repository on the database server. Did you try a local "SQLServer Express" database?

alexdoan102 wrote:
Why is it loading 389 commands for 1 square??? 

Please keep in mind that NShape targets on industrial applications. When integrating a product like NShape in an existing host application, you will have an existing database storage for the host application in most cases.
In order to provide a fully customizable persistency interface, the SQLRepository has no hard coded SQL commands but provides instead customizable SQL commands for every action it performs on the database. This results in quite a number of SQL commands that need to be stored in the database, too. These are the 389 (in your case) commands that have to loaded before opening any NShape diagrams. The number of commands varies depending on the libraries used while creating the database because for every shape type in each library, 5 to 7 SQL commands will be created. Using only the GeneralShapes library will result in 248 commands.
Due to the reasons mentioned above, the shipped SQLRepository is more or less a sample implemenation that shows how to implement a database storage.

Sep 21, 2012 at 8:20 AM

i am usind a local sqlexpress db.

is there a way we can optimize the qb query?

instead of doing 5-7 queries per shape, can u just do 1 query?

for example, "select * from sysparameter where command in (1,2,3,4,5,6,7)"

where would i need to look in the code to do this?

can u please show me sample code to do this?

 

Coordinator
Sep 21, 2012 at 9:08 AM
Edited Sep 21, 2012 at 9:15 AM
alexdoan102 wrote:
i am usind a local sqlexpress db.
  • How does it take to execute the queries in "SQL Management Studio"?
  • How long does it take to execute the queries in a simple .NET sample program?
  • Is there a difference in query execution speed if you disable your anti virus program temporarily?
alexdoan102 wrote:
is there a way we can optimize the qb query?
instead of doing 5-7 queries per shape, can u just do 1 query?
for example, "select * from sysparameter where command in (1,2,3,4,5,6,7)"

Sure. The shipped SQLRepository implementation is not optimized at all and there should be numerous optimizations that could be applied.

But I'm not convinced that optimizing query execution will solve your problem... 30 seconds for reading 400 strings from a local database file?!?
This is *way* too much. Just for comparison: On my machine, loading repository an SQL repository (with NShapeDesigner) containing a diagram with 7500 shapes takes <15 seconds.

alexdoan102 wrote:
where would i need to look in the code to do this? 

The base class for the database storage is AdoNetStore. The default database repository implamentation is in class SqlStore. It consists more or less only of methods that create SQL commands.

Sep 21, 2012 at 11:41 AM

Found the problem.

I was loading "SoftwareArchitectureShapes" and  "ElectricalShapes" libraries also.  

The more libraries you use, the longer it takes to load.

I took those 2 libraries out, and just loaded "GeneralShapes".

The load now takes about 4 seconds.

However, 4 seconds to load 1 square is still not great.

The XMLrepository, loads in about 2 seconds.  (  i think i like the xmlrepository better)

But i am using a client/server architecture and need the SQL repository.

Maybe some kind of hybrid (of using XML but storing it in SQL) ????

what are your thoughts on this?

BTW, can you please give me a quick dbschema of the SQLRepository?  I need to understand this better.

I think we need to optimise it a little more to load faster.  Thx!

Coordinator
Sep 21, 2012 at 2:34 PM
alexdoan102 wrote:
The load now takes about 4 seconds. However, 4 seconds to load 1 square is still not great.
The XMLrepository, loads in about 2 seconds.  (  i think i like the xmlrepository better)

Ok, that sounds better. You are right, 4 seconds for a square is not great, but the SQL repository has much more overhead than the XMLRepository.
Moreover, as I said, the XMLRepository was optimized for speed, the SQLRepository wasn't optimized at all.

 

alexdoan102 wrote:
Maybe some kind of hybrid (of using XML but storing it in SQL) ????
what are your thoughts on this?

Some ideas on how to improve performance:

  • At the moment, the "LoadSysCommands" method is called 3 times. Each time, all commands are disposed and re-created.
  • Perhaps you can get a little extra performance if you store the commands in an other format.
  • Storage of shapes is not optimum in the schema used by the default implementation:
    There is a table there all shape ID's are stored (in order to avoid duplicate shape Id's). The shapes themselves are stored in a table for each shape type and for the child shapes, an additional table exists (contains only ParentId and ChildId).
    At the moment, loading the diagram shapes performs a "load child shapes" query for each loaded shape in the diagram. Avoiding this would give the SQLRepository a noticable performance boost.

Caution: Database repositories support partial loading.

This means that diagrams will not be loaded when opening the repository but when accessing them. This also means, you cannot tell if all objects stored in the SqlStore were loaded into the CachedRepository's buffers. So you have to check against the database before deleting objects used in other objects.

 

alexdoan102 wrote:
BTW, can you please give me a quick dbschema of the SQLRepository?  I need to understand this better.

Download Microsoft's "SQL Server Management Studio" and install it. This tool can create database schema diagrams and much, much more...