SELECT distinct b.t_orno, b.t_pono
FROM tblMonthlyBooki ng AS b, tblFilterDate, tblFilterDate AS
tblFilterDate_1
WHERE (((b.t_yearMont h) Between [tblfilterdate].[fromDate] And
[tblFilterDate_1].[toDate]));
tblMonthlyBooki ng is a sql server table, 200K rows, yearMonth is an
indexed long integer
the primary key is t_orno, t_pono
tblFilterDate is an access table used to store reporting criteria, and
it will always only have one row, yearMonth is an indexed long integer
this query takes approx 3 minutes to run, so does this one
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooki ng AS b
WHERE (((b.t_yearMont h)>=(select fromYearMonth from tblFilterDate where
dateId = 1) And (b.t_yearMonth) <=(select toYearMonth from tblFilterDate
where dateId = 1)));
this one is runs in a second
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooki ng AS b
WHERE (((b.t_yearMont h)>=98 And (b.t_yearMonth) <=99));
if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes,
and change the query to refer to the form fields, again the query runs
in a second
showplan.out for the long running query refers to a temporary table
01) Restrict rows of table tblMonthlyBooki ng
by scanning
testing expression "b.t_yearMonth> = And b.t_yearMonth<= "
store result in temporary table
is there another way to build this query, to make use of the two
tables, and have results in a second ?
note, the tblFilterDate table cannot be moved to sqlServer, that would
be a massive reengineering exercise 6 3281
<le*********@na tpro.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. . SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMont h) Between [tblfilterdate].[fromDate] And [tblFilterDate_1].[toDate]));
tblMonthlyBooki ng is a sql server table, 200K rows, yearMonth is an indexed long integer the primary key is t_orno, t_pono
tblFilterDate is an access table used to store reporting criteria, and it will always only have one row, yearMonth is an indexed long integer
this query takes approx 3 minutes to run, so does this one SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE (((b.t_yearMont h)>=(select fromYearMonth from tblFilterDate where dateId = 1) And (b.t_yearMonth) <=(select toYearMonth from tblFilterDate where dateId = 1)));
this one is runs in a second SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE (((b.t_yearMont h)>=98 And (b.t_yearMonth) <=99));
if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes, and change the query to refer to the form fields, again the query runs in a second
showplan.out for the long running query refers to a temporary table 01) Restrict rows of table tblMonthlyBooki ng by scanning testing expression "b.t_yearMonth> = And b.t_yearMonth<= " store result in temporary table
is there another way to build this query, to make use of the two tables, and have results in a second ?
note, the tblFilterDate table cannot be moved to sqlServer, that would be a massive reengineering exercise
I would prefer the following which should be faster and is certainly easier
to read:
SELECT DISTINCT b.t_orno, b.t_pono
FROM tblMonthlyBooki ng AS b
WHERE b.t_yearMonth BETWEEN 98 AND 99
But what about using both tables?
Come on, you're not serious, right?
The local Jet table is restricted to one row and its purpose is store a
MinValue and a MaxValue for your reporting purposes. This is fine, and may
be useful in letting you close the database and remember these settings -
but they are only really settings and trying to join these tables for the
single purpose of passing two parameters to your SQL Server database isn't
really sensible.
The best solution will depend on a number of things:
Are you using linked tables
Are you able to create server objects such as a stored procedure, a view,
etc
Do you have a preference for DAO or ADO code
Is the recordset you return to be read-only or not.
One option would be to create a stored procedure on the server, with two
parameters for the min and max value. You could then generate a recordset
by using ADO to execute the recordset, passing in the values which it looks
up from your local table.
Another option might be to look up the values, then dynamically re-write a
pass-through query to get the records. le*********@nat pro.com wrote in
news:11******** **************@ u72g2000cwu.goo glegroups.com: SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMont h) Between [tblfilterdate].[fromDate] And [tblFilterDate_1].[toDate]));
tblMonthlyBooki ng is a sql server table, 200K rows, yearMonth is an indexed long integer the primary key is t_orno, t_pono
tblFilterDate is an access table used to store reporting criteria, and it will always only have one row, yearMonth is an indexed long integer
Why are you using two instances of tblfilterdate?? ?? Because of
the cartesian joins, this will produce double the number of
rows, which must be reduced again by the select distinct.
Reccomendation:
SELECT distinct
b.t_orno,
b.t_pono
FROM tblMonthlyBooki ng AS b,
tblFilterDate,
WHERE b.t_yearMonth Between [tblfilterdate].[fromDate] And
[tblFilterDate].[toDate];
you may not even need the distinct statement any more. this query takes approx 3 minutes to run, so does this one SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE (((b.t_yearMont h)>=(select fromYearMonth from tblFilterDate where dateId = 1) And (b.t_yearMonth) <=(select toYearMonth from tblFilterDate where dateId = 1)));
this one is runs in a second SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE (((b.t_yearMont h)>=98 And (b.t_yearMonth) <=99));
if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes, and change the query to refer to the form fields, again the query runs in a second
showplan.out for the long running query refers to a temporary table 01) Restrict rows of table tblMonthlyBooki ng by scanning testing expression "b.t_yearMonth> = And b.t_yearMonth<= " store result in temporary table
is there another way to build this query, to make use of the two tables, and have results in a second ?
note, the tblFilterDate table cannot be moved to sqlServer, that would be a massive reengineering exercise
--
Bob Quintal
PA is y I've altered my email address.
On Tue, 21 Mar 2006 22:49:24 GMT, Bob Quintal <rq******@sympa tico.ca>
wrote:
Not double, but the square.
-Tom.
<clip> Why are you using two instances of tblfilterdate?? ?? Because of the cartesian joins, this will produce double the number of rows, which must be reduced again by the select distinct.
<clip>
> Are you using linked tables
yes Are you able to create server objects such as a stored procedure, a view, etc
yes
Do you have a preference for DAO or ADO code
access97, so DAO
Is the recordset you return to be read-only or not.
yes
If I store the parameters as text boxes on a hidden form and refer to
the text boxes as parameters in my query, query timing is substantially
improved
I thought access would do the same optimization when using a small
table, but obvious not
I will look at using a stored procedure ... thanks
Anthony England wrote: <le*********@na tpro.com> wrote in message news:11******** **************@ u72g2000cwu.goo glegroups.com.. . SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMont h) Between [tblfilterdate].[fromDate] And [tblFilterDate_1].[toDate]));
tblMonthlyBooki ng is a sql server table, 200K rows, yearMonth is an indexed long integer the primary key is t_orno, t_pono
tblFilterDate is an access table used to store reporting criteria, and it will always only have one row, yearMonth is an indexed long integer
this query takes approx 3 minutes to run, so does this one SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE (((b.t_yearMont h)>=(select fromYearMonth from tblFilterDate where dateId = 1) And (b.t_yearMonth) <=(select toYearMonth from tblFilterDate where dateId = 1)));
this one is runs in a second SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE (((b.t_yearMont h)>=98 And (b.t_yearMonth) <=99));
if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes, and change the query to refer to the form fields, again the query runs in a second
showplan.out for the long running query refers to a temporary table 01) Restrict rows of table tblMonthlyBooki ng by scanning testing expression "b.t_yearMonth> = And b.t_yearMonth<= " store result in temporary table
is there another way to build this query, to make use of the two tables, and have results in a second ?
note, the tblFilterDate table cannot be moved to sqlServer, that would be a massive reengineering exercise
I would prefer the following which should be faster and is certainly easier to read:
SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE b.t_yearMonth BETWEEN 98 AND 99
But what about using both tables? Come on, you're not serious, right? The local Jet table is restricted to one row and its purpose is store a MinValue and a MaxValue for your reporting purposes. This is fine, and may be useful in letting you close the database and remember these settings - but they are only really settings and trying to join these tables for the single purpose of passing two parameters to your SQL Server database isn't really sensible.
The best solution will depend on a number of things: Are you using linked tables Are you able to create server objects such as a stored procedure, a view, etc Do you have a preference for DAO or ADO code Is the recordset you return to be read-only or not.
One option would be to create a stored procedure on the server, with two parameters for the min and max value. You could then generate a recordset by using ADO to execute the recordset, passing in the values which it looks up from your local table. Another option might be to look up the values, then dynamically re-write a pass-through query to get the records.
Thanks for the feedback.
Another alternative would be to create a SQL Server view.
You can create an Access table linked to this view as if it were a table.
<le*********@na tpro.com> wrote in message
news:11******** **************@ g10g2000cwb.goo glegroups.com.. . Are you using linked tables yes
Are you able to create server objects such as a stored procedure, a view, etc yes
Do you have a preference for DAO or ADO code access97, so DAO
Is the recordset you return to be read-only or not. yes
If I store the parameters as text boxes on a hidden form and refer to the text boxes as parameters in my query, query timing is substantially improved
I thought access would do the same optimization when using a small table, but obvious not
I will look at using a stored procedure ... thanks
Anthony England wrote: <le*********@na tpro.com> wrote in message news:11******** **************@ u72g2000cwu.goo glegroups.com.. . > SELECT distinct b.t_orno, b.t_pono > FROM tblMonthlyBooki ng AS b, tblFilterDate, tblFilterDate AS > tblFilterDate_1 > WHERE (((b.t_yearMont h) Between [tblfilterdate].[fromDate] And > [tblFilterDate_1].[toDate])); > > tblMonthlyBooki ng is a sql server table, 200K rows, yearMonth is an > indexed long integer > the primary key is t_orno, t_pono > > tblFilterDate is an access table used to store reporting criteria, and > it will always only have one row, yearMonth is an indexed long integer > > > this query takes approx 3 minutes to run, so does this one > SELECT DISTINCT b.t_orno, b.t_pono > FROM tblMonthlyBooki ng AS b > WHERE (((b.t_yearMont h)>=(select fromYearMonth from tblFilterDate where > dateId = 1) And (b.t_yearMonth) <=(select toYearMonth from tblFilterDate > where dateId = 1))); > > this one is runs in a second > SELECT DISTINCT b.t_orno, b.t_pono > FROM tblMonthlyBooki ng AS b > WHERE (((b.t_yearMont h)>=98 And (b.t_yearMonth) <=99)); > > if I load 'fromYearMonth' and 'toYearMonth' into a form's textboxes, > and change the query to refer to the form fields, again the query runs > in a second > > showplan.out for the long running query refers to a temporary table > 01) Restrict rows of table tblMonthlyBooki ng > by scanning > testing expression "b.t_yearMonth> = And b.t_yearMonth<= " > store result in temporary table > > is there another way to build this query, to make use of the two > tables, and have results in a second ? > > note, the tblFilterDate table cannot be moved to sqlServer, that would > be a massive reengineering exercise
I would prefer the following which should be faster and is certainly easier to read:
SELECT DISTINCT b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b WHERE b.t_yearMonth BETWEEN 98 AND 99
But what about using both tables? Come on, you're not serious, right? The local Jet table is restricted to one row and its purpose is store a MinValue and a MaxValue for your reporting purposes. This is fine, and may be useful in letting you close the database and remember these settings - but they are only really settings and trying to join these tables for the single purpose of passing two parameters to your SQL Server database isn't really sensible.
The best solution will depend on a number of things: Are you using linked tables Are you able to create server objects such as a stored procedure, a view, etc Do you have a preference for DAO or ADO code Is the recordset you return to be read-only or not.
One option would be to create a stored procedure on the server, with two parameters for the min and max value. You could then generate a recordset by using ADO to execute the recordset, passing in the values which it looks up from your local table. Another option might be to look up the values, then dynamically re-write a pass-through query to get the records. le*********@nat pro.com wrote in news:1142937414 .254928.223190
@u72g2000cwu.go oglegroups.com: SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooki ng AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMont h) Between [tblfilterdate].[fromDate] And [tblFilterDate_1].[toDate]));
Joins are often faster and more efficient than Wheres.
Without seeing your tables I can't be completely accurate but I think
something like
SELECT tmb.*
FROM tblMonthBooking AS tmb
INNER JOIN tblFilterDate AS tfd
ON (tmb.t_YearMont h BETWEEN tfd.fromDate AND tfd.toDate)
modified for your exact requirements should be quite fast.
It's inlikley the query-grid-wizard nonsense window will be able to
represent the JOIN given so one would have to use the SQL view to create
the query or VBA to create or run it.
If yearMonth is Integer and fromDate and toDate are Dates I'm not sure how
the Between works, but that's another issue.
When one is doing Dates, Between often fails to do what one wants and more
accurate results may be achieved with something like
tmb.t_YearMonth >= tfd.fromDate (includes fromDate)
AND
tmb.t_YearMonth < tfd.fromDate (excludes toDate).
--
Lyle Fairfield This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: JENS CONSER |
last post by:
Hello NG,
We have a performance problem in using a client
server solution based on MS SQL-Server 2000 through
a VPN tunnel (via broadband internet connection).
The SQL Server is running on a Windows 2003 Server
which is configured as VPN server as well.
We figured out that not the performance
|
by: ChaimG |
last post by:
Hi everyone,
I Run DB Application using Access97 (with DAO) on XP Client and
Windows2000 Server (Version5, SP4) with active directory.
A Simple "Select *" Query takes no time ( less then second), but query
with "Group By " Expresion ( Select CustNum From Customers Group By
CustNum) takes more then 30 seconds to perform.
This Problem accures only with XP Client on Windows2000 server.
The same "Group By " Querys takes less then second On...
|
by: Chris Dugan |
last post by:
Hi,
has anybody come across a solution to running an Access97 database on
Windows XP. The situation I have is an Access 97 database that runs
perfectly from Win95/98 clients running Access97, the database sits on
a Windows2000 server to which everyone has full NTFS access rights.
The problem is the WinXP and Win2k clients which appear to run fine
(also running Access97) in reading any existing data but as soon as
you attempt to edit a...
|
by: Rebecca |
last post by:
We are converting my Access97 back end to SQL Server and almost have it
all working. The current problem has to do with one situation in which
I have to programmatically (still in the Access97 front end) add a
record to an existing table. Here is the code:
If arg = cintBatchReg Then
Set newrec = db.OpenRecordset("SELECT * FROM ", _
dbOpenDynaset, dbSeeChanges)
Set newrec = db.OpenRecordset("SELECT * FROM _
", dbOpenDynaset,...
|
by: lesperancer |
last post by:
I've got a sql server view that returns data, as a linked table to
access97 (qryPlannedPurPT)
this link table is used in an access query (qryPlannedPUR) that uses
all of the linked table fields and adds a couple of calculated fields
when I create a second query using qryPlannedPUR that adds a specific
WHERE clause
I may or may not get the correct resultset
I always get the correct number of rows, but one of the row contains
the wrong...
| |
by: lesperancer |
last post by:
currently using access97 to link to sql7 tables
sql7 is running on NT4 and terminal server 2000
in the process of upgrading hardware and software to latest stable
versions
citrix - windows 2003
sql server 2005
office 2003 (maybe 2007)
first step is to implement sql server 2005 on windows 2003 and have it
|
by: Roger |
last post by:
can anyone explain this ?
originally using access97 with a linked table to an mdb backend, to
create a worksheet using
SELECT * FROM qryIPS WHERE location = 999
and it works fine with either excel97 or excel2003
moved backend mdb tables to sql server2005 and relinked
location field in both cases is a long integer
but the above statement no longer works with either version of excel
|
by: Ramchandar |
last post by:
Hi,
I am creating reports using VBA code.
I have the same query in a querydef residing both in Access97 and Access2003. The result of this querydef is then moved to a table in Access97 and Access2003 respectively. The table in Access97 returns 874 rowcount, table in Access 2003 returns 1050 rowcount. In both the case the querydef is retrieving from the same database which resides in SQL Server 2003.
I executed the application Access97 and...
|
by: Roger |
last post by:
on sql 2005, I've got a view with select permission granted, the view
just "select * from table"
using odbc in access97, I linked this view and I create a query to
retrieve certain fields
the query properties are 'no locks' and dynaset record type
but just executing the query as a datasheet, locks some records in the
original sql server table
is this how it should work ? seems to me that it should not lock
anything
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |