Solved

Query takes too long to go from SQL to DESIGN view and vice versa

Posted on 2014-11-24
26
225 Views
Last Modified: 2016-02-11
Hello,

I have a rather large Access 2K database (currently 700KB to 1.2KB depending upon when it was last compacted) that has started to exhibit extreme slowness when I go from SQL view to Design view and vise versa.  XP Pro platform split front end/back end

For example, some queries take ten minutes or more just to switch views.
(Not to run the query, just from the time I press the design view button)

What can I do?  This is a coding nightmare!
0
Comment
Question by:pcalabria
  • 9
  • 9
  • 5
  • +1
26 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Guessing it's out on a network<g>.

Couple things:

1. Make sure you have no unavailable printers or network drives mapped.

2. If you are accessing the drive via UNC (\\<server>\<share>) try switching to a mapped drive letter.  Or the opposite.

3. Make sure the DB s not being virus scanned at open.

4. Keep a table constantly open to the BE while your working (or a form bound to a table - anything that will keep the BE DB open).

Jim.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
- and 700KB to 1.2KB is in no way a large database; actually, it's a tiny one.
Any action with this should take a split-second.

Try to copy the backend to a local folder and work from there.

/gustav
0
 

Author Comment

by:pcalabria
Comment Utility
Thanks men...

I'm still working on this but have couple questions...

Yes, I do use UNC mapped drives because that way my code is not dependent upon workstations have the same mapped drives.  Is this not a good practice?  I can use mapped drives in my code, but suspect that may be problematic.

Most important, as far as keeping a form ties to the form open, I've done this for years with A2K but have not been doing so with new tables as I migrate to MS SQL Server.  Should I be doing the same thing?  Also, do you know any code I can use for this purpose?  I currently open invisible forms with a bound field in the autoexec start menu,..  which is fine for production but not development as don't always let this code execute.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
That's all fine, but did you try to run the backend off a local folder?

/gustav
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Yes, I do use UNC mapped drives because that way my code is not dependent upon workstations have the same mapped drives.  Is this not a good practice? >>

 Depends on the network.  In some cases, a mapped drive will work better.  Others have found UNC works well.   I suggest only trying it to see if it makes a difference.

<<Most important, as far as keeping a form ties to the form open, I've done this for years with A2K but have not been doing so with new tables as I migrate to MS SQL Server.>>

 Only applies to JET/ACE DB's.

 You are working with the FE locally, right?

Jim.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
You are, if I remember right, working old-school.
Perhaps very old-school (A2000)?

Things were absolutely night-and-day when I went from
\\someserver\someshare\somefolder\someotherfolder\someverylongLongerThanEightChars.mdb
to
m:\prod\app.mdb

8.3 still very much applies in Access 2003 and earlier.
Try it!
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Should I be doing the same thing?  Also, do you know any code I can use for this purpose?  I currently open invisible forms with a bound field in the autoexec start menu,..  which is fine for production but not development as don't always let this code execute.

Yuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuup!
Do it.
I have an invisible form bound to a single field in a dummy table on the SQL Server.  World of difference
MAKE SURE that the Startup form calls the code to get this done -- then it doesn't depend on autoexec.
Answered a Q for someone on SQL Azure who had his app timing out.
He put a 15 minute timer event requerying the field into that form as a keep-alive.
Worked like a charm.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I haven't tried to actually run net use from VBA to ensure that mapped drives are there.
I suspect that it could get done though.
FileSytemObject or Dir to see if the drive exists.

if not, it'd be a shell command
Shell "cmd /c \\DomainController\Netlogon\SomeLogonScript.bat"
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@Nick,

<<Yuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuup!
Do it.>>

  With SQL server, there is no reason to and in fact, you really don't want to.

 While establishing a connection still carries a lot of overhead, ODBC connection pooling is on by default and caches connections and that will get utilized most effectively if you don't keep connections open yourself.  That's not true with JET/ACE because normally, you don't go through ODBC to get there.

 Azure, while still SQL, is a different animal because it's over the internet.  Your timer solution was a work a round and really did not get to the bottom of the problem.   It may have turned out that there was no way around it other then to do that, but that was never really determined.  It may not even have been related to SQL, but rather a TCP/IP or networking problem.

Jim.
0
 

Author Comment

by:pcalabria
Comment Utility
I don't know if my system is still using the default for pooling...it sounds familiar though... I'm wondering whether we change a setting years ago?  Hmm?

Nick, yep Access 2K.

Jim, yes the FE is local.

Gustav, no.  Have not tried moving the backend and don't think I can.  It's MS SQL Server and on a network drive.

Here's an example of what I am seeing:

I have a query with 242K records... the query only takes about 2 seconds to run as a select query.
When I use the query builder to change it to an APPEND query,
and append the records to an MS SQL SERVER table,
it takes 14.5 minutes (used by iPhone stopwatch) to give me the ready to append 242K records message.

The table on the SQL Server is empty before I begin the append.

Here is the 14.5 minute query:
INSERT INTO InternetData ( OriginCode, LotCode, DateCodeAll, PartID, HouseNumber, ProjectHouseNumber, TrackingNumber, Description, PartClass, PartFamily, PartMarking, GenericNumber, Package, RecPrice, FullPacks, TrayQty, DocName, code, PackageQty, PackageType, Condition, ConditionCategory, PbookPrice, quantity, Manufacturer, SalesNote, Notes, LineID, [Value], Tolerance, FullReelQty, ReelSize, TapePitch, TapeWidth, CaseSize, TCR, Power, Voltage, OperatingRange, MSL, EURoHS, Pb, SearchNumber, OrderNumber )
SELECT Parts4Sales.OriginCode, Parts4Sales.Lotcode, Parts4Sales.DateCodeAll, Parts4Sales.PartID, Parts4Sales.HouseNumber, Parts4Sales.ProjectHouseNumber, Parts4Sales.TrackingNumber, ComponentMaster.Description, ComponentMaster.PartClass, ComponentMaster.PartFamily, ComponentMaster.PartMarking, ComponentMaster.GenericNumber, ComponentMaster.Package, Parts4Sales.RecPrice, Parts4Sales.FullPacks, Parts4Sales.TrayQty, ComponentMaster.DocName, ComponentMaster.code, Parts4Sales.PackageQTY, Parts4Sales.PackageType, Parts4Sales.Condition, Parts4Sales.ConditionCategory, ComponentMaster.PBookPrice, Parts4Sales.Quantity, ManufacturerMaster.ShortName AS Manufacturer, Parts4Sales.SalesNote, ComponentMaster.Notes, Parts4Sales.LineID, ComponentMaster.Value, ComponentMaster.Tolerance, ComponentMaster.FullReelQty, ComponentMaster.ReelSize, ComponentMaster.TapePitch, ComponentMaster.TapeWidth, ComponentMaster.CaseSize, ComponentMaster.TCR, ComponentMaster.Power, ComponentMaster.Voltage, ComponentMaster.OperatingRange, ComponentMaster.MSL, ComponentMaster.EURoHs, ComponentMaster.Pb, Parts4Sales.SearchNumber, ComponentMaster.OrderNumber
FROM (Parts4Sales LEFT JOIN ComponentMaster ON (Parts4Sales.LineID = ComponentMaster.LineID) AND (Parts4Sales.SearchNumber = ComponentMaster.SearchNumber)) INNER JOIN ManufacturerMaster ON Parts4Sales.LineID = ManufacturerMaster.LineID
WHERE (((Parts4Sales.SearchNumber) Is Not Null And (Parts4Sales.SearchNumber)<>''));
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Nick, yep Access 2K.
Ok map a drive letter to the folder that it is in and give it a name in line with an 8.3 naming convention.
Try that, and let us know how it goes.

Looking at the query, I'll ask a dumb question
FROM (Parts4Sales LEFT JOIN ComponentMaster ON (Parts4Sales.LineID = ComponentMaster.LineID) AND (Parts4Sales.SearchNumber = ComponentMaster.SearchNumber)) INNER JOIN ManufacturerMaster ON Parts4Sales.LineID = ManufacturerMaster.LineID
 WHERE (((Parts4Sales.SearchNumber) Is Not Null And (Parts4Sales.SearchNumber)<>''


Single quotes on the end are a bit of a puzzle (why not "" which would be Access) but...
Parts4Sales.SearchNumber = ComponentMaster.SearchNumber
Joining on text fields is not much fun
Joining on memo fields is a horror
And joining on either when both sides do not have indexes can make for atrocious performance.

My 2 cents on that, anyway
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@Jim,
With SQL server, there is no reason to and in fact, you really don't want to.
I'll defer to you on the SQL end -- but from MS
When an application makes its database connections by using ODBC database drivers, the connections are cached through the ODBC connection pool. When the first request for a connection to a database is made, the connection passes through the pooling components and then user information and any properties configured prior to making the connection are cached. The connection request is next passed on to the back-end server, and a live connection is handed to the user to satisfy the connection request. The rest of the application will be unaffected by pooling.

When the application releases the connection, the pool returns S_OK to the user, simulating a successful disconnection from the database. However, the actual connection is not released by ODBC but instead waits in the pool. When the next request for a connection to the database that has the same data source and user information comes in, it is satisfied from the connection in the pool rather than by making a full connection to the database. However, if the connection has already timed out or if there are no connections in the pool matching the request, a new connection is opened.

So it becomes a question of how long connections take to time out, and how long they would take to be re-fired VS how many connections the server is easily capable of maintaining.  In my Access\SQL Server environment, it's a no-brainer -- see to it that the app maintains an open connection.
YMMV
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
Comment Utility
<<it takes 14.5 minutes (used by iPhone stopwatch) to give me the ready to append 242K records message.

The table on the SQL Server is empty before I begin the append.>>

 There's nothing wrong with that....that's not opening a query in design view, but executing a query.

 My guess would be that your either joining to local tables, using VBA expressions, or JET specific SQL.

 If you do that, JET is forced to handle the query, in which case it's going to pull all 242K records to your PC, then push them all back out to SQL server.   On top of that, depending on how the query is structured, it may actually execute one insert for each and every row rather than doing a bulk insert.

 You need to move to either a pass-through or a stored procedure to get good performance.

Jim.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@Nick,

<<So it becomes a question of how long connections take to time out, and how long they would take to be re-fired VS how many connections the server is easily capable of maintaining. >>

 Yes exactly.  The default time out is 60 seconds.  In regards to the latter half of what you said, what's different for SQL vs JET in maintaining connections is that it's the server which is handling all of them, not each individual station.  Since there is a fair amount of overhead associated with each connection, with SQL you want connections minimized as much as possible.

 But there is overhead as well in establishing them, so the connection pooling is a middle of the road solution.   You hang onto the connection for a bit in case it gets re-used, but you also kill it if it's not going to get used for some time.

 But if your maintaining constant connections to SQL and you have a app that is inactive for lengths of time, then your forcing your server to over work.  

 Constant connections is not something I'd routinely use with SQL.  But as you said YMMV.

Jim.
0
 

Author Comment

by:pcalabria
Comment Utility
@Nick
The double quotes are there because the actual code is enclosed with double quotes in an strSQLtxt="yada yada day"

The query needs to ignore null and empty instances of the SearchNumber.  I think its called empty...
It is not a memo field but rather a text field such as SearchNumber="2N2222" .  if I only  test for null it includes records that are now empty.

<<Parts4Sales.SearchNumber = ComponentMaster.SearchNumber>>
Not sure what you were telling me, is there a better way to build my query?
I still need to try the mapped drive.

@JIM
That sounds very reasonable... I created a pass-through query once.... I think.  I think you may have once helped me create a stored procedure too....but I'm a fish out water here.

The earlier problem I reported was while troubleshooting the insert query.  I'm writing the code and trying to get the query to work...so its been... paste code into SQL view. Switch to design...wait 15 minutes. Run query to test.  Wait 15 minutes for results.  What a pain!  It would be a lot easier if could switch from sql to design view faster.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
"The double quotes are there because the actual code is enclosed with double quotes in an strSQLtxt="yada yada day""
Ah, so we're not looking at the dumpout of the SQL view of the Query Editor, we are looking at a String variable in VBA?
Or something else?

"The query needs to ignore null and empty instances of the SearchNumber."
Absolutely nothing wrong with that, and what it needs to be.

<<Parts4Sales.SearchNumber = ComponentMaster.SearchNumber>>
 Not sure what you were telling me, is there a better way to build my query?

In almost all instances you dearly want to avoid joins on text datatype fields
They are hellaciously difficult for the engine to compare straight up.
Things are a little better if you go into the design of the tables Parts4Sales and ComponentMaster, then and ensure that both Parts4Sales.SearchNumber and ComponentMaster.SearchNumber each have indexes on them.
Let us know if you don't know how to do that!

In general though, in a query you want to do your joins on indexed fields, and preferably on numeric type index fields.
So, in the two tables are there already unique numbers that correspond to SearchNumber?  Or can each SearchNumber occur many times in the tables so that there is no choice to join on them?

Your query is not performing, so we need to find a better way to build it -- if the 8.3 naming convention isn't the sole problem -- and I no longer think it is.

Step 1 : See if we can eliminate the need to join on text fields by joining on a unique numeric field that corresponds 1:1 with the text fields.  If not...
Step 2: Index the text fields and see if that improves performance.  Well-placed indexes are occasionally life-savers.  If not
Step 3: Break the query down into parts that perform better than the whole or use subqueries to improve matters.

Let us know how it goes.
0
 

Author Comment

by:pcalabria
Comment Utility
@nick
Yes on the VBA string.
As far as SEARCHNUMBER it is a manufacturers pn with dashes slashes and other characters stripped out. Allows for better search results..no not unique or numeric.

The combination of line I'd and SEARCHNUMBER is unique in the component master table..that's the manufacturer and PartNumber.

Parts4sales can include multiple parts of rows of the mfg and PartNumber..beach skew we have in stock.

I suspect Jim is right on...I just need to figure out this SQL stuff.
0
 

Author Comment

by:pcalabria
Comment Utility
@JIM
I'll say uncle! These queries are taking 45 minutes to run!

I'm ready to try a pass through or stored procedure...anything that will solve the problem.
InternetData is on the MS SQL Server
Parts4Sales and ComponentData are both MS Access

Which do you suggest?
I haven't written many, and you probably helped me with the two or three I have written.
My code is in my first post.  What do you, (or anyone else online now) suggest?

Also, how do I find out what type of DSN I'm using.   I don't remember how I connected the database.  Nothing shows up in the control panel as system or file.

Thanks
0
 

Author Comment

by:pcalabria
Comment Utility
The problem with it taking very long to go from query to design view is very wierd.  I worked on the system all weekend and did not experience that problem.  It was only taking two seconds.  Today, its 20 minutes again.  Perhaps its related to others in the DB?

The real problem, however, not the time that it takes to get into design view, it is the time that it takes to run the query to update our website.  We need to run six passes of the query, to append data from different sources. Its taking 20 to 30 minutes to run the first query.

In the Access query builder, it only takes 30 seconds to display all 250,000 records.  It seems odd to me that I can display all of the records in 30 seconds pulling them from a Windows 2000 server and an MDB file, but then takes 20-30 minutes to append the same data to a MS-SQL server.

Doesn't  that seem strange to you?

Jim suggested using a stored procedure or pass through, but its this possible when I"m sourcing the data from a couple of  Access MDB tables and appending it to a table on the SQL server?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Have a gander at my article here
http://www.experts-exchange.com/Database/MS_Access/A_6692-Overcoming-unbound-continuous-forms-limitations-by-staging-data.html.

Now in your case, you can pull the data quick in Access.  Great!  Append it into a staging table(s) to push it out to the SQL Server, and see if that improves matters.

"...to run the query to update our website."  That suggested whole passels of possible issues though.  Where is that server, what's upload speed to it like, how heavily burdened is it, etc.  Is the production SQL server the issue?

It's a stretch of my skill set to suggest pushing the data to a non-production SQL Server in a new MDF file, dismounting it, sneakernetting it to the production server and then mounting and moving the data, but that is doable in practice, too.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
>  It was only taking two seconds.  Today, its 20 minutes again.

This indicates tor me a networking or server issue rather than query troubles.

/gustav
0
 

Author Comment

by:pcalabria
Comment Utility
@Nick
Sorry, I was not very clear.  We have an MS SQL server on our network that we use for serving files to the office.  The Internal network includes the table named InternetData, and we are writing to this table, in our own building, on our LAN.  The performance problem in creating this table, occurs regardless whether anyone else is using the network.

Once the file is written to our server, we have different code that sends it to the production server, which is a shared server at web.com.  The code to send it through the net to our server works very well, and in a fraction of the time that it takes to create the table.

@gustav and everyone
It does sound like a network problem, but I don't think so.  I've been able to switch from Design to SQL view in seconds all weekend.  This morning it was 20 minutes again.  I was able to determine that there was one user logged into the system from home using RDP, although the user was not doing anything that would cause network traffic.

We know that Access performs much better in single user mode, so I'm wondering whether SQL has the same issue?
I can demonstrate in single user mode consistent 3 second searches, which becomes 7.5 seconds with even on other users logged in.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I am becoming confused.
We first had an issue switching views in the query editor
Then we had issues running the append query
Now we have issues appending to SQL Server.

Perhaps it's time to retrench and refresh.
Is the problem evolving?
Please summarize the problem(s), the attempted solution(s), and the present state
0
 

Author Comment

by:pcalabria
Comment Utility
Its all the same problem, or at least, mostly related.

The problem is that I have written an Append query (above query is 1/5) of the query, to append records from the Access tables to our SQL server located on our premises'.  Ultimately, the data is uploaded to our web server, which is hosted by web.com.

In the process of writing the SQL for the queries, I use the Access query builder to test my code.  I write the code in the code section of access, then copy and paste the code into the SQL view.

Next, I switch to design view, review the query, then run it.

In this case, I was needing to wait 20 minutes just to switch from SQL to design view with the query above, and then another 20-30 minutes for the query to run, only to find out that I needed to make some new query modification, like add >0.

So I raised the question, why the heck is this taking this long!  Over the weekend, I noticed the same process, switching form SQL to Code view was only taking 3 seconds.  This morning it was 20 minutes again, and the only explanation I have is that an employee used RDP to access his workstation.

 With this all said, the sql to design view problem is certainly odd, and an obstacle to my productivity, but getting the same query that we have been talking about all along to run faster, is the real problem.

Thanks for hanging in there...
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 400 total points
Comment Utility
I use the Access query builder to test my code.  I write the code in the code section of access, then copy and paste the code into the SQL view.
That strikes me as highly unorthodox.  Usually, you fire up the query editor, add tables, joins, criteria and output fields.  If you need the SQL to use in code, you switch to SQL view to copy the SQL to use in code.

So let's ask a dumb question :)
Your query from ID: 40463402
If you create the same query results by:
Opening the editor
Adding Parts4Sales, ComponentMaster and ManufacturerMaster
Creating the appropriate LEFT and INNER Joins
Selecting  Parts4Sales.OriginCode, Parts4Sales.Lotcode, Parts4Sales.DateCodeAll, Parts4Sales.PartID, Parts4Sales.HouseNumber, Parts4Sales.ProjectHouseNumber, Parts4Sales.TrackingNumber, ComponentMaster.Description, ComponentMaster.PartClass, ComponentMaster.PartFamily, ComponentMaster.PartMarking, ComponentMaster.GenericNumber, ComponentMaster.Package, Parts4Sales.RecPrice, Parts4Sales.FullPacks, Parts4Sales.TrayQty, ComponentMaster.DocName, ComponentMaster.code, Parts4Sales.PackageQTY, Parts4Sales.PackageType, Parts4Sales.Condition, Parts4Sales.ConditionCategory, ComponentMaster.PBookPrice, Parts4Sales.Quantity, ManufacturerMaster.ShortName AS Manufacturer, Parts4Sales.SalesNote, ComponentMaster.Notes, Parts4Sales.LineID, ComponentMaster.Value, ComponentMaster.Tolerance, ComponentMaster.FullReelQty, ComponentMaster.ReelSize, ComponentMaster.TapePitch, ComponentMaster.TapeWidth, ComponentMaster.CaseSize, ComponentMaster.TCR, ComponentMaster.Power, ComponentMaster.Voltage, ComponentMaster.OperatingRange, ComponentMaster.MSL, ComponentMaster.EURoHs, ComponentMaster.Pb, Parts4Sales.SearchNumber, ComponentMaster.OrderNumber
And
add the criteria to Parts4Sales.SearchNumber

How does that perform? Better, worse, the same?
And we haven't talked about "XP Pro platform split front end/back end"
Are you creating these queries in the front end or in the backend?
Does the performance change depending on where you create the queries?
And what hosts the backend?
One hopes it's a proper server and not some user's workstation!
0
 

Author Closing Comment

by:pcalabria
Comment Utility
Thanks!  So many of your comments were helpful
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now