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,433
Thanks: 40
Thanked 3,367 Times in 3,262 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: 6,844
Thanks: 92
Thanked 1,686 Times in 1,567 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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.

Web 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
Previously known as ridders : 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 11:15 PM.


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

Sponsored Links

How to advertise

Media Kit


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