Code running slow, SQL Server related? (1 Viewer)

Hello1

Registered User.
Local time
Today, 23:29
Joined
May 17, 2015
Messages
240
Hey guys,
Im not sure if this is the right place for this but here is whats making me troubles.

Im keeping queries, reports, some temporary tables and forms in my .accde and the big tables with data are on the local SQL Server databases so the tables are linked to the .accde.
The users use a RDP connection to connect to the server (on the same network) and use the .accde. There is one cmdButton on a form with subform which has a good amount of code on click action. The code takes too long to execute, about 10 minutes for looping through about 370 records whats too long (I tried it directly on the server, its similar over RDP).
The server PC is with an intel i5-6500 and 16GBs of RAM on Windows Server 2008R2 enterprise.
I copied the same .accde and SQL Server databases to another server with pretty similar specifications i5-6400, 16GBs of RAM and same OS. Then I tried to run the exactly the same code with same records and it took only about 2 minutes and 43 seconds, whats much faster compared to the other one.
I actually tried it on my laptop too, which is much weaker dual core T4500 with 6GBs of RAM and it took maybe 4 minutes.

What could be causing the problem?
Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:29
Joined
Oct 29, 2018
Messages
13,258
Hi. Unless I missed it, did you try it on the second server using RDP?
 

Hello1

Registered User.
Local time
Today, 23:29
Joined
May 17, 2015
Messages
240
No, unfortunately Im not in the position to do it so right now. I connect to the servers remotely with a 3rd party app. Right now I dont have access to the PCs which access the servers over RDP. On the other server which works faster users also connect with RDP but the database and accde is slightly different. However, Im running the .accde directly/locally on the servers, not over RDP, right now. And the difference in same conditions on 2 different servers is huge, as I mentioned, on the first server it takes about 10min and the second server just 2 min and 43 seconds.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:29
Joined
Oct 29, 2018
Messages
13,258
No, unfortunately Im not in the position to do it so right now. I connect to the servers remotely with a 3rd party app. Right now I dont have access to the PCs which access the servers over RDP. On the other server which works faster users also connect with RDP but the database and accde is slightly different. However, Im running the .accde directly/locally on the servers, not over RDP, right now. And the difference in same conditions on 2 different servers is huge, as I mentioned, on the first server it takes about 10min and the second server just 2 min and 43 seconds.

Hi. If the conditions are the same but the only difference is the machine, then maybe the problem is there. Just a thought...
 

Hello1

Registered User.
Local time
Today, 23:29
Joined
May 17, 2015
Messages
240
Very possible any thoughts how I could close it down, is it the SQL Server not running as it should, maybe something with the Windows OS or even hardware related?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:29
Joined
Oct 29, 2018
Messages
13,258
Very possible any thoughts how I could close it down, is it the SQL Server not running as it should, maybe something with the Windows OS or even hardware related?

How did you copy the database into the second server. If it’s not the same database, then maybe it’s the database settings itself.
 

Hello1

Registered User.
Local time
Today, 23:29
Joined
May 17, 2015
Messages
240
I wasn't clear enough, sorry. On the second server there is a similar database which is being used on a daily basis. That database isn't important, I just mentioned it because RDP. I copied the database from server 1 to server 2 by overwriting the server 2 database, so it's the database from server 1, exactly the same 1:1.
Anyway, in short only what's different on the server 2 is the PC, database and accde is from server 1
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:29
Joined
Oct 29, 2018
Messages
13,258
I wasn't clear enough, sorry. On the second server there is a similar database which is being used on a daily basis. That database isn't important, I just mentioned it because RDP. I copied the database from server 1 to server 2 by overwriting the server 2 database, so it's the database from server 1, exactly the same 1:1.
Anyway, in short only what's different on the server 2 is the PC, database and accde is from server 1

Hi. No need to apologize. We can’t see what you’re doing, so we might ask dumb questions. Again, since it’s the same database and accde, then it sounds like the only possible cause of the problem is the machine. Just my 2 cents...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:29
Joined
Feb 19, 2002
Messages
30,138
Are you sure the problem isn't with your code? You could almost do 370 records manually in 10 minutes. Do you have code inside the loop that belongs outside so it happens ONCE rather than at each iteration? Are you running domain functions inside the loop? It would be better to base the loop on a query with a join.
 

eshai

Registered User.
Local time
Today, 23:29
Joined
Jul 14, 2015
Messages
161
create a new empty db then import all from the old db compile and run
 

Hello1

Registered User.
Local time
Today, 23:29
Joined
May 17, 2015
Messages
240
I modified the code a little and now its from 10min down to 42 seconds, hopefully without any new errors :)
In the loop there was a Set VariableName = MyDb.OpenRecordset("SQL..."). In the query was another query joined to some other tables and that line of code needed a little under a second to execute x 370 number of the records in the loop and yeah thats a lot of time. So I made a temporary table and put everything needed in it and created the SQL from it, the speed is much better now. However, I still dont understand the big difference of time to execute between the 2 PCs with pretty similar specifications and same conditions.
Anyway, its running fine for now, I will update you if it goes back to slow or something :)
Thanks for the help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:29
Joined
Oct 29, 2018
Messages
13,258
I modified the code a little and now its from 10min down to 42 seconds, hopefully without any new errors :)
In the loop there was a Set VariableName = MyDb.OpenRecordset("SQL..."). In the query was another query joined to some other tables and that line of code needed a little under a second to execute x 370 number of the records in the loop and yeah thats a lot of time. So I made a temporary table and put everything needed in it and created the SQL from it, the speed is much better now. However, I still dont understand the big difference of time to execute between the 2 PCs with pretty similar specifications and same conditions.
Anyway, its running fine for now, I will update you if it goes back to slow or something :)
Thanks for the help!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

isladogs

CID VIP
Local time
Today, 21:29
Joined
Jan 14, 2017
Messages
14,030
It may well be that you can optimise this further as 42 seconds is still very slow for 370 records. Have a look at this article which may give you additional ideas such as indexing to try Optimising Queries.

Here is a summary showing improvements in my test results from around 27s to less than 0.5s i.e. about 50x faster

 

Attachments

  • OptQ-AvgResultsNEW.PNG
    OptQ-AvgResultsNEW.PNG
    25.6 KB · Views: 34
Last edited:

Hello1

Registered User.
Local time
Today, 23:29
Joined
May 17, 2015
Messages
240
Thanks, I will read that and if I find extra time post my code to explain it in more details. The loop i was talking about is actually reduced from about 9min and 40 seconds to about 8 seconds now and the whole code on the button click is taking 42 seconds. In short the code has 2 loops (While not MyRecordSet.EOF..movenext.. Wend.. That kind of loop but it has a lot of code in between) in it and some little code between them. The first loop is taking about 35 secons and the second abut 8, while previously the second loop was the problematic one. In those 2 loops are many other loops in them and add records code, especially in the first one.
This was pretty rude explanation from my phone. Once I get time I will post the code and tables involved and explain as much as i can (the language difference is making me problems)
 

isladogs

CID VIP
Local time
Today, 21:29
Joined
Jan 14, 2017
Messages
14,030
Loops within loops are always going to be slow. There may well be better ways of doing this

IIRC, the best improvement I ever achieved was from 35 minutes to 7 seconds
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:29
Joined
Feb 19, 2002
Messages
30,138
I went from 2.5 hours to 3 minutes simply by removing dLookup()'s. Then got it down to 1.5 minutes by getting rid of VBA entirely and doing the whole thing in an update query.
 

Hello1

Registered User.
Local time
Today, 23:29
Joined
May 17, 2015
Messages
240
Indeed, update queries are awesome but I'm still a beginner and struggling to create them in some conditions :D
Currently I'm busy with something else and this is on hold :/
 

isladogs

CID VIP
Local time
Today, 21:29
Joined
Jan 14, 2017
Messages
14,030
Just added a screenshot showing average results from my Optimising Queries app. See post #13
 

Users who are viewing this thread

Top Bottom