Query time

[rkrause]

I have the following query. When i run it, it takes along time to pull the data up. its only 2000 records. Does anyone have any idea on how to simplify it ot make it run smoother. maybe even how to break it down into a couple of steps.

thanks

SELECT dbo_MFS_ACCOUNT.AccountID, dbo_MFS_ACCOUNT.AccountName, dbo_MFS_ACCOUNT.CreateDate, dbo_MFS_ORDER.InvoiceDate, Sum(dbo_MFS_ORDER.OrderSubtotal) AS SumOfOrderSubtotal, dbo_MFS_REGIONALMGR.Territory, dbo_MFS_REPGROUPS.GroupName
FROM ((dbo_MFS_ACCOUNT INNER JOIN dbo_MFS_ORDER ON dbo_MFS_ACCOUNT.AccountID = dbo_MFS_ORDER.AccountID) INNER JOIN ((dbo_MFS_ACCOUNT_REP_COMMISSION INNER JOIN dbo_MFS_SALESPERSON ON dbo_MFS_ACCOUNT_REP_COMMISSION.SalesPersonId = dbo_MFS_SALESPERSON.SalesPersonID) INNER JOIN dbo_MFS_REGIONALMGR ON dbo_MFS_SALESPERSON.RegMgrID = dbo_MFS_REGIONALMGR.RegMgrID) ON (dbo_MFS_ORDER.AccountID = dbo_MFS_ACCOUNT_REP_COMMISSION.AccountId) AND (dbo_MFS_ORDER.ShipToStoreID = dbo_MFS_ACCOUNT_REP_COMMISSION.StoreId)) INNER JOIN dbo_MFS_REPGROUPS ON dbo_MFS_SALESPERSON.GroupID = dbo_MFS_REPGROUPS.ID
GROUP BY dbo_MFS_ACCOUNT.AccountID, dbo_MFS_ACCOUNT.AccountName, dbo_MFS_ACCOUNT.CreateDate, dbo_MFS_ORDER.InvoiceDate, dbo_MFS_REGIONALMGR.Territory, dbo_MFS_REPGROUPS.GroupName, dbo_MFS_ACCOUNT.Active, dbo_MFS_ACCOUNT_REP_COMMISSION.Active, dbo_MFS_SALESPERSON.Active
HAVING (((dbo_MFS_ACCOUNT.CreateDate) Between #1/1/2008# And GetAllDate(#12/31/2008#)) AND ((dbo_MFS_ORDER.InvoiceDate) Between #1/1/2008# And GetAllDate(#12/31/2008#)) AND ((dbo_MFS_ACCOUNT.Active)="Yes") AND ((dbo_MFS_ACCOUNT_REP_COMMISSION.Active)="Yes") AND ((dbo_MFS_SALESPERSON.Active)="Yes"));

 

[boblarson]

Get rid of the GetAllDate function. That will help you immensely. Why not just use

Between #1/1/2008# And #1/1/2009# instead of the other?

 

[rkrause]

do you have anything else that might help me if i leave that function?

 

[boblarson]

do you have anything else that might help me if i leave that function?

I thought I just posted my suggestion. If I remembered correctly it is because you have a time component in that date field and you want all up to 23:59:59 of that day. So, changing the date to the next day

BETWEEN #1/1/2008# AND #1/1/2009#

Should take care of it.

 

[raskew]

Hi -

As an alternative, add two hidden fields to your query:

year(dbo_MFS_ORDER.InvoiceDate)
year(dbo_MFS_ACCOUNT.CreateDate)

In the criteria cell for each: 2008

Bob

 

[rkrause]

Get rid of the GetAllDate function. That will help you immensely. Why not just use

Between #1/1/2008# And #1/1/2009# instead of the other?

I got rid of the function and theres no change in how long it takes to pull up the data. still taking along time? is there any good way to maybe break this down into a couple different steps?

 

[boblarson]

I got rid of the function and theres no change in how long it takes to pull up the data. still taking along time? is there any good way to maybe break this down into a couple different steps?

Did you try raskew's suggestion?

 

[rkrause]

Yep and theres no change with his suggestion either

 

[namliam]

Please dont splash SQL on the forum, format it and use Code tags (# simbol on the post screen)

Some tips, from less involved to big change:

These fields are not in Query select thus have no bearing on the result but are in the group by slowing your query down
dbo_MFS_ACCOUNT.Active
dbo_MFS_ACCOUNT_REP_COMMISSION.Active
dbo_MFS_SALESPERSON.Active

Move your having clause into the Where, this will considerably reduce the grouping that needs to be done.
Make sure your "Active" fields are actually strings.

Restructure your Joins to make more sense, they are all over the place... This should not make much difference but OMG! and it actually CAN make a difference depending on database setup etc...

Then this is a Pass through (PT) query?? Or are you executing this on linked tables? It looks like linked tables.
If indeed linked tables try making it a PT query as it is likely that access is pulling full table scans from the database PT will be executed by the server 100% certainty and is likely to make MUCHO increase in performance

Look into the "cast" function or "convert" for sql to make dates out of strings, ## is access native and will not be possible in a pass through.

Check to make sure you are using indexed fields and make sure the indexes are up to date (probably need a dba to check). If this is a 'normal' application though it is highly likely that they are up to date.

Good luck !

 

[rkrause]

Could someone attemp to rework my query to clean it up some? I am not having much luck with this at all. Any help is greatly appeciated.

 

[namliam]

Normaly, I would have told you where to take this...

Please dont splash SQL on the forum, format it and use Code tags (# simbol on the post screen)

Some tips, from less involved to big change:

These fields are not in Query select thus have no bearing on the result but are in the group by slowing your query down
dbo_MFS_ACCOUNT.Active
dbo_MFS_ACCOUNT_REP_COMMISSION.Active
dbo_MFS_SALESPERSON.Active

Move your having clause into the Where, this will considerably reduce the grouping that needs to be done.
Make sure your "Active" fields are actually strings.

Restructure your Joins to make more sense, they are all over the place... This should not make much difference but OMG! and it actually CAN make a difference depending on database setup etc...

But your lucky I felt like a no thought job as a distraction from the headache project I am working on

SELECT dbo_MFS_ACCOUNT.AccountID
,      dbo_MFS_ACCOUNT.AccountName
,      dbo_MFS_ACCOUNT.CreateDate
,      dbo_MFS_ORDER.InvoiceDate
,      dbo_MFS_REGIONALMGR.Territory
,      dbo_MFS_REPGROUPS.GroupName
,      Sum(dbo_MFS_ORDER.OrderSubtotal) AS SumOfOrderSubtotal
FROM 	        dbo_MFS_ACCOUNT 
INNER JOIN 	dbo_MFS_ORDER                    ON dbo_MFS_ACCOUNT.AccountID = dbo_MFS_ORDER.AccountID
INNER JOIN      dbo_MFS_ACCOUNT_REP_COMMISSION   ON dbo_MFS_ORDER.AccountID = dbo_MFS_ACCOUNT_REP_COMMISSION.AccountId 
                                                AND dbo_MFS_ORDER.ShipToStoreID = dbo_MFS_ACCOUNT_REP_COMMISSION.StoreId
INNER JOIN      dbo_MFS_SALESPERSON              ON dbo_MFS_ACCOUNT_REP_COMMISSION.SalesPersonId = dbo_MFS_SALESPERSON.SalesPersonID
INNER JOIN      dbo_MFS_REGIONALMGR              ON dbo_MFS_SALESPERSON.RegMgrID = dbo_MFS_REGIONALMGR.RegMgrID
INNER JOIN      dbo_MFS_REPGROUPS                ON dbo_MFS_SALESPERSON.GroupID = dbo_MFS_REPGROUPS.ID
WHERE 		dbo_MFS_ACCOUNT.CreateDate Between #1/1/2008# And #12/31/2008#
  AND 		dbo_MFS_ORDER.InvoiceDate  Between #1/1/2008# And #12/31/2008# 
  AND 		dbo_MFS_ACCOUNT.Active="Yes"
  AND           dbo_MFS_ACCOUNT_REP_COMMISSION.Active="Yes"
  AND 		dbo_MFS_SALESPERSON.Active="Yes"
GROUP BY   dbo_MFS_ACCOUNT.AccountID
,          dbo_MFS_ACCOUNT.AccountName
,          dbo_MFS_ACCOUNT.CreateDate
,          dbo_MFS_ORDER.InvoiceDate
,          dbo_MFS_REGIONALMGR.Territory
,          dbo_MFS_REPGROUPS.GroupName

Then this is a Pass through (PT) query?? Or are you executing this on linked tables? It looks like linked tables.
If indeed linked tables try making it a PT query as it is likely that access is pulling full table scans from the database PT will be executed by the server 100% certainty and is likely to make MUCHO increase in performance

Look into the "cast" function or "convert" for sql to make dates out of strings, ## is access native and will not be possible in a pass through.

Check to make sure you are using indexed fields and make sure the indexes are up to date (probably need a dba to check). If this is a 'normal' application though it is highly likely that they are up to date.

Good luck !

This you still have to do yourself, I cannot do that for you.

My re-write though should atleast take care of the first part.

 

[rkrause]

SELECT tbl_TmpMFS_Account.AccountID, tbl_TmpMFS_Account.AccountName, tbl_TmpMFS_Account.CreateDate, tbl_TmpMFS_Order.InvoiceDate, tbl_TmpMFS_Order.OrderSubTotal, tbl_TmpMFS_RegionalMGR.Territory, tbl_TmpRepGroups.GroupName, tbl_TmpMFS_Account.Active, tbl_TmpMFS_Account_Rep_Commission.ActiveRepCom, tbl_TmpMFS_Salesperson.ActiveSalesPerson
FROM (((tbl_TmpMFS_Account INNER JOIN tbl_TmpMFS_Order ON tbl_TmpMFS_Account.AccountID = tbl_TmpMFS_Order.AccountID) INNER JOIN tbl_TmpMFS_Account_Rep_Commission ON (tbl_TmpMFS_Order.AccountID = tbl_TmpMFS_Account_Rep_Commission.AccountId) AND (tbl_TmpMFS_Order.ShipToStoreID = tbl_TmpMFS_Account_Rep_Commission.StoreId)) INNER JOIN (tbl_TmpMFS_Salesperson INNER JOIN tbl_TmpMFS_RegionalMGR ON tbl_TmpMFS_Salesperson.RegMgrID = tbl_TmpMFS_RegionalMGR.RegMgrID) ON tbl_TmpMFS_Account_Rep_Commission.SalesPersonId = tbl_TmpMFS_Salesperson.SalesPersonID) INNER JOIN tbl_TmpRepGroups ON tbl_TmpMFS_Salesperson.GroupID = tbl_TmpRepGroups.GroupName
WHERE (((tbl_TmpMFS_Account.CreateDate) Between #1/1/2008# And GetAllDate(#12/31/2008#)) AND ((tbl_TmpMFS_Order.InvoiceDate) Between #1/1/2008# And GetAllDate(#12/31/2008#)) AND ((tbl_TmpMFS_Account.Active)="Yes") AND ((tbl_TmpMFS_Account_Rep_Commission.ActiveRepCom)="Yes") AND ((tbl_TmpMFS_Salesperson.ActiveSalesPerson)="Yes"));

im trying a different route. I pulled a summary of my data and put it in Temp tables then tried linking them together and gettin a faster result.

The above is my neq query, but when i run it i get nothing returned. but everything is linked like my original query.

any help
thanks

 

[namliam]

Sorry I am not even looking at this splashed SQL, please make it readable or not post it...

If its not returning anything than your temp tables are not complete (enough) to sustain the query... assuming there is supposed to be result...