complex (?) query for a huge table

[jetmcquack]

Hello everybody,


i'm new to the forum, so sorry if i'm missing some rules, or access notion. This is my first attempt to create a big application.

i have this database with a complex table (STORICO_PDV) of over 90 fields and more than 140.000 records growing.
The index i have built are:

PERIODORIF (Date/time) in english is like "Date"
AREA (Text) a value between 12 different possibilities
PrimaryKey (Counter) a sort of transaction id

and others, not used in this query

i need to run a query to Sum 2 fields of this table grouping by a an other field called MCC

the records to sum are selected via a form which asks for:

AREA and PERIODORIF and other minor values.

the SQL code access generate is the following:

SELECT STORICO_PDV.MCC, mcc_desc.DESC_MCC, Sum(STORICO_PDV.TOT_VOLUMI_TRANSATI) AS SommaDiTOT_VOLUMI_TRANSATI, Sum(STORICO_PDV.TOT_REDDITIVITA) AS TOT_REDDITIVITA
 
 
FROM mcc_desc INNER JOIN STORICO_PDV ON mcc_desc.MCC = STORICO_PDV.MCC
 
 
WHERE 
 
((([AREA]=[Forms]![Vista X Aree]![AREA] Or [Forms]![Vista X Aree]![AREA] Is Null)=True) 
 
AND
 
(([PERIODORIF] Between [Forms]![Vista X Aree]![MeseInizio] And [Forms]![Vista X Aree]![MeseFine] Or [Forms]![Vista X Aree]![MeseInizio] Is Null Or [Forms]![Vista X Aree]![MeseFine] Is Null)=True) 
 
AND
 
((IIf([Forms]![Vista X Aree]![Segno] Is Null Or [Forms]![Vista X Aree]![Rating] Is Null,True,IIf([RATING] Is Null,Null,Eval([RATING] & [Forms]![Vista X Aree]![Segno] & [Forms]![Vista X Aree]![Rating]))))=True) 
 
AND 
 
(([FIS_VIR]=[Forms]![Vista X Aree]![FISVIR] Or [Forms]![Vista X Aree]![FISVIR] Is Null)=True))
 
 
 
GROUP BY STORICO_PDV.MCC, mcc_desc.DESC_MCC;

Now my problem is that the query run EXTREMLY slow the first time i run it and then a bit faster the next times, until the database is exited and cleaned. Is there a way to optimize it?

thanks for your precious help!

Cisco

[Dal Jeanis]

Cisco -

First, as a matter of form, you don't need the "=TRUE" in each of your subconditions - not sure whether Access is having to evaluate that again or not. You might speed up the database just by deleting the =TRUE in four places.

Next, a little bit deeper. As I read this, your SQL is being called after you fill out the data on a form called [Vista X Aree].

This form has these fields:
[AREA], [MeseInizio], [MeseFine], [Segno], [Rating], and [FISVIR]

On the table, you have [AREA], [PERIODORIF], and [FIS_VIR]

Now, if the field in the form is NULL, then it is not really needed for the query. Otherwise, the query should limit the response using the data in the form.
If I have this right, then I would suggest using VBA to build the query up from the values on the form. That way the database doesn't have to check each record for any unnecessary tests.

Put a command button on the form (you probably already have one) and in that button's onclick, you will build the SQL using some VBA code similar to this:

sub cmdbutton21_Onclick

Dim strSQL as String
Dim boolWhere as Boolean
' set flag for WHERE clause not yet loaded 
boolWhere = FALSE
strSQL = "SELECT STORICO_PDV.MCC, mcc_desc.DESC_MCC, " & _ 
  " Sum(STORICO_PDV.TOT_VOLUMI_TRANSATI) AS SommaDiTOT_VOLUMI_TRANSATI, " & _
  " Sum(STORICO_PDV.TOT_REDDITIVITA) AS TOT_REDDITIVITA " & _ 
  " FROM mcc_desc INNER JOIN STORICO_PDV ON mcc_desc.MCC = STORICO_PDV.MCC " 

if Me.AREA Is Not Null then 
   strSQL = strSQl & " WHERE ( ([AREA]= " & Me.Area & ")" 
   boolWhere = TRUE
End If 

If Me.MeseInizio is not null and Me.MeseFine is not null then   
   if boolWhere Then 
      strSQL = strSQl & " AND ([PERIODORIF] Between " & Me.MeseInizio & " AND " & Me.MeseFine & ") "
   else
      strSQL = strSQl & " WHERE ( ([PERIODORIF] Between " & Me.MeseInizio & " AND " & Me.MeseFine & ") "
      boolWhere = TRUE
   end if 
End if 

' and continue building the rest of the sql 
' be sure to close all the parens and include your order by clause
' I'll let you figure this out

' for testing, pop up the string and read it for missing spaces 
Msgbox strSQL

That way, you build clean SQL for the specific options that have been selected, and the jet engine won't need to test anything that doesn't need tested. This will be much faster.

My syntax may not be precise in the code above, but you should be able to get the idea from that code and use the Msgbox and the responses from Access to figure out where all your parenthesis and spaces need to be.

USEFUL REMINDERS: When building your SQL, Use & to concatenate, underscore _ as a continuation character at the end of a line, and Me.[controlname] to get the value from the form control to concatenate into your SQL string. You may need to use delimiters in your SQL like # around the value of date fields or quotes around text values. It's not tough, but it's picky sometimes.

In bocca al lupo!