mika Posted October 6, 2017 Share Posted October 6, 2017 I have very interesting problem with SQL server and it has probably nothing to do with uniGUI. Actually, I am sure it has nothing to do with uniGUI whatsoever. I just hope someone here can shed some light into this. Our software is fairly large and both uniGUI standalone application and SQL server consume quite a lot of memory. When SQL server uses 4096Mb memory, which is most it can use, our software crashes. Only thing that is certain at this point is that when SQL Server uses maximum amount of memory, soon after our application will crash. As SQL server memory usage inevitably increases in time, application crash at some point is certain. I'm pretty sure SQL server isn't behaving badly in anyway. It has something to do with my code, FireDAC and uniGUI in combination. We currently still use SQL Server 2014 Express edition which is not ideal but that's what we got now. Also, upgrading database software would not be a real solution anyway even if it could alleviate this problem in short term because SQL server is most likely not the source of this problem. Software we are using: Delphi XE5 Architect uniGUI Pro Plus 1.0.0.1410 SQL server 2014 Express edition If anyone here has even a clue where to start looking, I'd be very greatful. // Mika Link to comment Share on other sites More sharing options...
davidizadar Posted October 6, 2017 Share Posted October 6, 2017 Mika, SQL Server shouldn't affect the stability of your application, desktop or web. But if you open a query which returns a huge amount of records, you will be breaking your application and forcing SQL Server (or any other database) to use a lot of memory for creating the resultset. If that is the case, you need to make sure that your requests are small enough. Take into account that you cannot show more than a handful of records client-side. In FireDAC there are several ways of checking how many records a query will return. Programs that used to work with BDE now fail with FireDAC. It is just about handling huge amounts of data in queries. Sorry if I cannot be more specific. David Link to comment Share on other sites More sharing options...
adragan Posted October 7, 2017 Share Posted October 7, 2017 Possible scenarios : 1. I know there is a setting in SQL Sever that limits the ammount of memory used by the server. I don't know if it is also in the last Express but it's worth investigating. I used to run with 1 Gb without any problem. The rest might be used by the UniGui App. 2. Go to 64 Bit 3. Separate the SQL Server by the UniGui App. Put them on 2 different machines. 4. In your software avoid queries like : "Give me all invoices from 2000 till today" 5. Close all queries and commit all transactions when you close a form or a frame. 6. If you have no restricted requirements change the SQL Server to Firebird or Postgres. They are really good options and for 90% of applications there are no notisable speed differences. Link to comment Share on other sites More sharing options...
Administrators Farshad Mohajeri Posted October 7, 2017 Administrators Share Posted October 7, 2017 Do your see a "Memory Overflow" exception in your log file? Link to comment Share on other sites More sharing options...
sbokhari Posted October 8, 2017 Share Posted October 8, 2017 Hello Mika, As adragan said, separate your DB server from your UniGUI App. Use FireBird or MariaDB or Postgres if you don't have a specific need to use SQL Server. Whenever we used SQL server and application on the same machine we encountered some hidden problems like slowness and excessive memory consumption by OS, etc. As you are using SQL Express 2014, so you are running on a 64 bits machine. SQL Server has a quite overhead load which is not required in most cases. Check if you UniGUI app is also 64bits. If UniGUI app is 32bits on 64bits platform and you run out of RAM, your app will crash. We are in the process of evaluating UniGUI for a very large in-house application that might contain more than 700 forms and data modules. 300+ connections. So far we have decided to go with MariaDB second option is Postgres (MariaDB has performed better than Postgres in our case) with UniGUI. We are waiting for native support of UniGUI for Linux and application load balancing. Waiting for Mr. Farshad to provide us some good news in the regards. Another factor is FireDAC. We use UniDAC or MyDAC from DevArt. FireDAC takes more memory than UniDAC for its datasets. On large datasets, it may be an issue. Regards Link to comment Share on other sites More sharing options...
mika Posted October 8, 2017 Author Share Posted October 8, 2017 Thank you all for your advices. I will go through our software and try to find places to improve based on your suggestions. @Farshad, I didn't find any "Memory Overflow" entries in logs. @DavidIzadaR, I know that SQL server should not affect our software like this but the fact is that our software crashes only when SQL server is using maximum amount of memory (4096Mb). Computer where all software is installed on, has 16Gb of memory. Memory usage of our software doesn't seem to correlate with crashes at all and faily normal memory usage is around 500-1000Mb. Link to comment Share on other sites More sharing options...
Recommended Posts
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now