Slow project transfer speed eapx to SQL Server

[Sunshine]

Installing Prolaborate and when we connected it to SQL Server DB a message came up saying we should upgrade the schema on our repository.
https://sparxsystems.com/resources/repositories/index.html
Fair enough we backed up the DB and did project transfer to eapx repo (took approx 1hr 15 min) so not too bad for 1.7GB file.

We ran the SQL script  EASchema_1558_SQLServer.sql to update the schema. Then attempted a project transfer back to the SQL Server. The problem is that the project transfer from eapx to SQL server is really slow. Left it running for 3 days then it bombed out. Subsequent transfers equally slow and bombing out. On the task manager we were seeing network upload speeds averaging 120Kbps on a 100Mbps wired LAN. So we tried a windows file transfers we saw 20 Mbps upload speed which isn't great but a lot better than 120Kbps. The network engineer has been investigating for over a week and can't find any reason why it should be slow from the network perspective.

We are using OLE DB SQL Server connection and were just wondering if anyone had similar slow speeds when transferring from eapx to SQL or have ideas what could be causing the slow speed.
Tried using latest builds of V15.1 and 15.2

[Paolo F Cantoni]

[SNIP]

We are using OLE DB SQL Server connection and were just wondering if anyone had similar slow speeds when transferring from eapx to SQL or have ideas what could be causing the slow speed.
Tried using the latest builds of V15.1 and 15.2

Hi Sunshine,

We have ALWAYS found .eap(x) to SQL Server transfers MUCH slower.  Consequently, we don't do it.  IIRC the last time we did it (say 5 years ago) it was 10 mins export, 6 hours import, for a 0.5Gb file.  Also, IIRC, the files were on the same machine!  We routinely snapshot out SQL Server repository to .eapx and it takes about 10-15mins for a 0.8Gb file between two machines (although in the same datacentre).  For the record, if we have to recover stuff, we use direct connections to both repositories and use SQL to recover the affected portions.

HTH,
Paolo

[Geert Bellekens]

You can update the schema on a live database containing data.

No need to pass through a .eap file.

Geert

[Sunshine]

Thanks for the replies. I see the script now that alters the schema rather than blowing it away and creating all the tables from scratch again. Makes it less of a chore. by not having to do the transfer. Still bit of mystery why a project transfer takes just over an hour from sql to eapx file yet several days the other way. Was blaming the network but that may have been erroneous.

[qwerty]

No, that's the queries taking time. EAP(x) is completely in-memory on your machine. Access to the SQL server will be over some network. And there can be lots of hurdles (the server itself, the network at all the many hops it has to take).

q.

[Geert Bellekens]

The weird thing is that SQL-Server to SQL Server is not faster.

I think it's even faster to do SQL-Server -> eap file -> SQL-Server then it is to do a direct SQL-Server->SQL-Server project transfer.

Geert

[qwerty]

Well, for a SQL-SQL-Server you can simply use a SQL-Server backup.

q.

[Geert Bellekens]

Well, for a SQL-SQL-Server you can simply use a SQL-Server backup.

q.

Unless you want to reload it in a fresh (updated schema) database, or you don't have access to the server, or...

But indeed, if you have the chance you should use SQL-Server backup restore. This usually only takes a few seconds.

Geert

[Sunshine]

Indeed the SQL backup only takes a few minutes however a restore from backup wasn't an option as we were updating the schema. I've advised Prolaborate support team about the slow transfer rate from eapx to SQL Server and that running the SQL script that alters the schema is much quicker.

No, that's the queries taking time. EAP(x) is completely in-memory on your machine. Access to the SQL server will be over some network. And there can be lots of hurdles (the server itself, the network at all the many hops it has to take).

We checked the performance of the machine with Sparx EA at the time and it was only using about 10% CPU with 50% Ram usage so it looked like it wasn't doing much. Similarly we looked at the DB server performance and it was pretty much idol. Called in a network engineer to check if anything was calling a bottleneck but he didn't find anything.
Still not sure what the cause of performance issue was. As we managed to complete the task of upgrading the schema today without having to do a project transfer we've abandoned any further investigation in looking into the cause.
Be interesting to see if its still a problem with V16 using SQL lite to SQL server.