Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-07-2019, 11:38 AM   #1
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 191
Thanks: 68
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Code running slow, SQL Server related?

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!

Hello1 is offline   Reply With Quote
Old 09-07-2019, 12:23 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,962
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Code running slow, SQL Server related?

Hi. Unless I missed it, did you try it on the second server using RDP?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-07-2019, 12:33 PM   #3
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 191
Thanks: 68
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Code running slow, SQL Server related?

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.

Hello1 is offline   Reply With Quote
Old 09-07-2019, 01:20 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,962
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Code running slow, SQL Server related?

Quote:
Originally Posted by Hello1 View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-07-2019, 01:26 PM   #5
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 191
Thanks: 68
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Code running slow, SQL Server related?

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?
Hello1 is offline   Reply With Quote
Old 09-07-2019, 01:50 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,962
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Code running slow, SQL Server related?

Quote:
Originally Posted by Hello1 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-07-2019, 01:55 PM   #7
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 191
Thanks: 68
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Code running slow, SQL Server related?

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

Hello1 is offline   Reply With Quote
Old 09-07-2019, 02:40 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,962
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Code running slow, SQL Server related?

Quote:
Originally Posted by Hello1 View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-07-2019, 08:44 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,272
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Code running slow, SQL Server related?

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Hello1 (09-08-2019)
Old 09-08-2019, 01:11 PM   #10
eshai
Newly Registered User
 
Join Date: Jul 2015
Posts: 86
Thanks: 12
Thanked 5 Times in 5 Posts
eshai is on a distinguished road
Re: Code running slow, SQL Server related?

create a new empty db then import all from the old db compile and run
eshai is offline   Reply With Quote
The Following User Says Thank You to eshai For This Useful Post:
Hello1 (09-08-2019)
Old 09-08-2019, 03:02 PM   #11
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 191
Thanks: 68
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Code running slow, SQL Server related?

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!
Hello1 is offline   Reply With Quote
Old 09-08-2019, 03:10 PM   #12
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,962
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Code running slow, SQL Server related?

Quote:
Originally Posted by Hello1 View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Hello1 (09-08-2019)
Old 09-08-2019, 09:20 PM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,064
Thanks: 115
Thanked 3,021 Times in 2,748 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Code running slow, SQL Server related?

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

Attached Images
File Type: png OptQ-AvgResultsNEW.PNG (25.6 KB, 23 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing

Last edited by isladogs; 09-09-2019 at 03:42 PM. Reason: Added screenshot
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Hello1 (09-08-2019)
Old 09-08-2019, 10:11 PM   #14
Hello1
Newly Registered User
 
Join Date: May 2015
Posts: 191
Thanks: 68
Thanked 3 Times in 3 Posts
Hello1 is on a distinguished road
Re: Code running slow, SQL Server related?

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)
Hello1 is offline   Reply With Quote
Old 09-08-2019, 10:15 PM   #15
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,064
Thanks: 115
Thanked 3,021 Times in 2,748 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Code running slow, SQL Server related?

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow running code Acropolis Modules & VBA 4 06-08-2015 12:00 PM
SQL Server Linked Server to Oracle slow --> OpenQuery Solution Rx_ SQL Server 2 05-19-2015 08:25 AM
Qry Running Very Slow Acropolis Queries 4 09-04-2014 02:15 AM
SQL Server Express '05 on top of SQL Server '08 running as mirror? madEG SQL Server 2 11-18-2010 02:59 PM
Slow Running samer General 1 06-24-2005 03:27 AM




All times are GMT -8. The time now is 12:14 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World