Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-03-2018, 04:00 PM   #16
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,773
Thanks: 40
Thanked 3,494 Times in 3,381 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Mythbusters - Speed Comparison Tests - Having vs Where

Hi Colin,

Sorry, wasn't very clear - what I meant was


Code:
SELECT DISTINCT Postcodes.PostcodeArea, Postcodes.PostcodeDistrict, Postcodes.PostcodeSector
FROM Postcodes

versus

Code:
SELECT Postcodes.PostcodeArea, Postcodes.PostcodeDistrict, Postcodes.PostcodeSector
FROM Postcodes
GROUP BY Postcodes.PostcodeArea, Postcodes.PostcodeDistrict, Postcodes.PostcodeSector
Quote:
What I need to study is why both versions repeatedly scan a table (tblKickout) that is nothing to do with the query
But that's a job for another day .. unless either of you can enlighten me!
just a guess but is there a relationship between tblKickout and postcodes?

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 11-04-2018, 07:27 AM   #17
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,292
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Mythbusters - Speed Comparison Tests - Having vs Where

Hi CJ

There is no relationship between Postcodes & tblKickout
The Postcodes tables was a linked table from my UK Postal Addresses app which includes a forced closedown procedure. Every 30s, that app checks the status of a boolean field in tblKickout and shuts down if its true.
I just didn't expect that to be included in the ShowPlan as I didn't link that table
I assume its included as its being checked in the background all the time

To avoid complications, I'll use a local table this time (tblPatients from the Having vs Where test)

Query1
Code:
SELECT DISTINCT tblPatients.Surname, tblPatients.Forename, tblPatients.Gender, tblPatients.DOB
FROM tblPatients;
ShowPlan1
Quote:
--- Query1 ---

- Inputs to Query -
Table 'tblPatients'
- End inputs to Query -

store result in temporary table
Query2
Code:
SELECT tblPatients.Surname, tblPatients.Forename, tblPatients.Gender, tblPatients.DOB
FROM tblPatients
GROUP BY tblPatients.Surname, tblPatients.Forename, tblPatients.Gender, tblPatients.DOB;
ShowPlan2
Quote:
--- Query2 ---

- Inputs to Query -
Table 'tblPatients'
- End inputs to Query -

01) Group table 'tblPatients'
Not sure that tells you anything unexpected...
As you would expect, both give the same output of 3338 records

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.

Last edited by isladogs; 11-04-2018 at 07:46 AM.
isladogs is offline   Reply With Quote
Reply

Tags
having/where , speed tests

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Determine OS from VBA - a few tests needed spikepl Modules & VBA 11 06-09-2018 02:14 PM
Table Comparison / Partial text comparison??? dkinnz Forms 1 03-07-2007 12:20 PM
Table Comparison / Partial Text Comparison??? dkinnz Modules & VBA 1 03-02-2007 09:05 PM
Tests Uncle Gizmo General 8 05-23-2006 11:30 AM
Continuous Forms & Logical Tests Luddite Lad Forms 3 10-05-2005 06:36 PM




All times are GMT -8. The time now is 09:26 AM.


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