Author Topic: Performance issue with 9.1.910 on SQL Server (Read 8849 times)

Geert Bellekens · « **on:** November 09, 2011, 11:18:54 pm »

Opening a diagram in version 9.1.910 takes 30 seconds (or more) where the diagram diagram is opened in 9.1.909 in less then 2 seconds.
So I've downgraded now

Bug report with the the query that was to blame sent to Sparx.

Geert

Paolo F Cantoni · « **Reply #1 on:** November 11, 2011, 03:08:05 pm »

Hi Geert,

Did the query change between releases?

Paolo

Geert Bellekens · « **Reply #2 on:** November 11, 2011, 03:36:11 pm »

Yes,

The added two selects in the union that include something like "t_object.PDATA2 LIKE t_xref.Client
This LIKE statement causes SQL Server to do a full table scan on t_xref (twice)
The previous version didn't have those selects in the list of unions.

Geert

qwerty · « **Reply #3 on:** November 11, 2011, 09:02:13 pm »

Is it just certain diagrams or will that effect any diagram?

q.

Geert Bellekens · « **Reply #4 on:** November 11, 2011, 09:24:25 pm »

All diagrams

The same query is used each time a diagram is opened, and because of the table scans it doesn't matter which diagram you are loading.

It might not be a problem in other databases.

Geert

qwerty · « **Reply #5 on:** November 12, 2011, 12:47:07 am »

When you say SQL Server you probably mean MS SQL Server - which is the one we recently moved to. Well, most of our diagrams are small so it might not be an issue at all. However, we're warned now.

Thanks, q.

Geert Bellekens · « **Reply #6 on:** November 12, 2011, 01:31:07 am »

Yep, I mean MS SQL Server (2008)
The size of the diagrams doesn't matter, only the size of the table t_xref.
We have about 163.000 records in it (mainly because of all the "column" steretoypes for the imported databases)

Geert

Paolo F Cantoni · « **Reply #7 on:** November 16, 2011, 12:40:45 pm »

We've also rolled back to 909 and performance has improved an order of magnitude - to confirm Geert's findings.

This needs to be fixed ASAP!

Paolo

Eve · « **Reply #8 on:** November 16, 2011, 02:19:18 pm »

This will be corrected in the next build.

Sorry for an inconvenience caused.

Geert Bellekens · « **Reply #9 on:** November 16, 2011, 06:00:54 pm »

Ah, good to know Simon.
Where exactly do I send my invoice to

Geert

MMA · « **Reply #10 on:** November 17, 2011, 01:25:13 pm »

It is fast in 9.2 now, cheers

Paolo F Cantoni · « **Reply #11 on:** November 17, 2011, 03:27:11 pm »

Quote

It is fast in 9.2 now, cheers

Confirmed... On a quick test about 20% slower than under 909.

Paolo

qwerty · « **Reply #12 on:** November 17, 2011, 08:38:31 pm »

You mean this is a re-definition of "fast"?

q.

Geert Bellekens · « **Reply #13 on:** November 17, 2011, 09:04:53 pm »

Well,

If look at it this way:
9.1.909: 1 second
9.1.910: 34 seconds
9.2.920: 1.2 seconds (+ 20%)

So yes, compared to 9.1.910 an impressive performance improvement

Geert

Paolo F Cantoni · « **Reply #14 on:** November 18, 2011, 12:46:50 pm »

Quote

Well,
[size=18]...[/size]
So yes, compared to 9.1.910 an impressive performance improvement
Geert

However, I'm also testing on a functionally equivalent EAP file (to the SQL DB) and I'm seeing 920 as 2-3 times slower than 909. Anyone else seeing this?

I'm preparing some formal test sets to compare longish (enough) runs to get a good comparison. I'll post the results here.

Sparxians, should we be seeing these types of numbers (especially on EAP files)?

[edit]I've documented my findings with EAP files in: Performance issue with 9.2.920 on EAP files[/edit]
Paolo

Sparx Systems Forum

News: