Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 8th, 2003, 02:12 PM
Registered User
Join Date: Oct 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Running a Report that feeds from a nested query

I have a Query- Summary, this selects from another query -UnAllocated.

Does it take more time to run a query that selects from another query, than if i just include the SQL logic in Summary?



Old October 8th, 2003, 08:47 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi M,

Best way to know for sure is to create both queries on your system and time their execution. For example, the following times an insert operation on my system from a table with 225,000 records to a target table. Total elapsed time equals about 15 seconds. I commented out a loop which performs the operation 10 times:

Public Sub TestAddByAppend()
   Dim dbs As DAO.Database
   Dim varTimeStart As Variant
   Dim varTimeEnd As Variant
   Dim intCount As Integer

   Set dbs = CurrentDb
   varTimeStart = Time

   Debug.Print "Start Add By Append: " & varTimeStart
   'For intCount = 1 To 10
       dbs.Execute "Delete * from tblTarget;"
       dbs.Execute "INSERT INTO tblTarget ( Field1, Field2 ) " _
                   & "SELECT DISTINCTROW S.Field1, S.Field2 " _
                   & "FROM tblSource As S;"
   'Next intCount

   varTimeEnd = Time
   Debug.Print "End Add By Append: " & varTimeEnd
   Debug.Print "Elapsed Time: "; DateDiff("s", varTimeStart, varTimeEnd)

End Sub

The results displayed in the debug window are:

Start Add By Append: 9:36:47 PM
End Add By Append: 9:37:02 PM
Elapsed Time: 15

Can't beat that for certainty.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested query deb1980 ASP.NET 2.0 Basics 2 April 10th, 2007 04:38 AM
Nested Query teclo MySQL 1 March 22nd, 2005 07:20 AM
Nested Query BrianWren Oracle 5 February 1st, 2005 01:45 PM
nested insert query/...how? mch SQL Server 2000 2 April 28th, 2004 08:46 AM
Nested Query Trouble ashley_y VB How-To 1 December 23rd, 2003 05:10 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.