My Form RecordSource SQL is too slow - what are my options?

[darbid]

Hi guys,

I must admit this one is a long way over my head.

SETUP:
Access 2003 mdb
SQL Server 2000 backend.


Form Record Source SQL

 [FONT=Arial]SELECT *[/FONT]

  [FONT=Arial]FROM [tbl_LuT  Vorgang] [/FONT]

  [FONT=Arial]LEFT JOIN  tbl_Taetigkeit AS taet1 ON [tbl_LuT Vorgang].[LuT  Aktenzeichen]=taet1.Aktenzeichen[/FONT]

  [FONT=Arial]WHERE ([tbl_LuT  Vorgang].[Bearbeiter LuT]= '" & str_current_lc & "'[/FONT]

  [FONT=Arial]OR [tbl_LuT  Vorgang].[Treiber]= '" & str_current_lc & "')[/FONT]

  [FONT=Arial]AND [tbl_LuT  Vorgang].[Reporting] = true[/FONT]

  [FONT=Arial]AND   (taet1.Arbeitstag = (select TOP 1 max(Arbeitstag) from tbl_Taetigkeit  taet2[/FONT]

  [FONT=Arial]                                                 WHERE taet2.Aktenzeichen = [tbl_LuT Vorgang].[LuT Aktenzeichen])[/FONT]

  [FONT=Arial]          OR  taet1.Aktenzeichen is null) ORDER BY doneID DESC;[/FONT]

The problem is that this SQL is taking a long time to execute. With the server next to me it takes 10 seconds - but for users on the other side of the world (Japan/China) it takes more than 10min.

The interesting thing is that I used to have this

 [FONT=Arial]SELECT *[/FONT]

  [FONT=Arial]FROM [tbl_LuT  Vorgang] [/FONT]

  [FONT=Arial]LEFT JOIN  tbl_Taetigkeit [/FONT]

  [FONT=Arial]AS taet1 ON [tbl_LuT  Vorgang].[LuT Aktenzeichen]=taet1.Aktenzeichen[/FONT]

  [FONT=Arial]WHERE ([tbl_LuT  Vorgang].[Bearbeiter LuT]= '" & str_current_lc & "'[/FONT]

  [FONT=Arial]OR [tbl_LuT  Vorgang].[Treiber]= '" & str_current_lc & "')[/FONT]

  [FONT=Arial]AND [tbl_LuT  Vorgang].[Reporting] = true[/FONT]

  [FONT=Arial]AND   (taet1.Arbeitstag = (select max(Arbeitstag) from tbl_Taetigkeit  taet2[/FONT]

  [FONT=Arial]                                                 WHERE taet2.Aktenzeichen = [tbl_LuT Vorgang].[LuT Aktenzeichen][/FONT]

  [FONT=Arial]                                                 and taet2.[Fuer Monatsbericht] = true)[/FONT]

  [FONT=Arial]          OR (not  exists (select 1 from tbl_Taetigkeit taet3[/FONT]

  [FONT=Arial]                                                 WHERE taet3.Aktenzeichen = [tbl_LuT Vorgang].[LuT Aktenzeichen][/FONT]

  [FONT=Arial]                                                 and taet3.[Fuer Monatsbericht] = true)[/FONT]

  [FONT=Arial]                AND  taet1.Arbeitstag = (select max(Arbeitstag) from tbl_Taetigkeit taet4[/FONT]

  [FONT=Arial]                                                 WHERE taet4.Aktenzeichen = [tbl_LuT Vorgang].[LuT Aktenzeichen]))[/FONT]

  [FONT=Arial]          OR  taet1.Aktenzeichen is null);[/FONT]

I would have thought this older SQL would have been worse but it did not seem to be.

So my question: what can I do?

I know that I could restrict the SELECT to fields but I will use most fields anyway.
I know that sub selects are not good for Access but I do not see a way around this.

I appreciate any help.

PS: The above SQLs were written by a friend who I would call an expert on SQL but has never used Access or SQL Server so I cannot get any help from her.

 

[namliam]

lets first clean up the SQL

SELECT *
FROM      [tbl_LuT  Vorgang] 
LEFT JOIN [tbl_Taetigkeit]   AS taet1 ON [tbl_LuT Vorgang].[LuT  Aktenzeichen]=taet1.Aktenzeichen
WHERE    (    [tbl_LuT  Vorgang].[Bearbeiter LuT] = '" & str_current_lc & "'
           OR [tbl_LuT  Vorgang].[Treiber]        = '" & str_current_lc & "')
  AND    [tbl_LuT  Vorgang].[Reporting] = true
  AND   (    taet1.Arbeitstag = ( select TOP 1 max(Arbeitstag) 
                                  from   tbl_Taetigkeit  taet2
                                  WHERE  taet2.Aktenzeichen = [tbl_LuT Vorgang].[LuT Aktenzeichen])
         OR  taet1.Aktenzeichen is null) 
ORDER BY doneID DESC;

Problems there could be but probably arent is indexing of the searched fields. If the time difference is so big, it is likely that the query is being executed localy vs on the server, as a result your full table is fetched from the SQL server to china then localy determained what it needs.

My suggestion is to NOT use queries but rather use Stored Procedures (SP), this is assuming all the tables are SQL server tables...
The call the SP with the values, which should return much faster I think.

 

[darbid]

Thanks for the clean up I was not sure how to set it out for you guys to look at.

Problems there could be but probably arent is indexing of the searched fields.

Could you maybe please re-write this sentence as I am not sure I understand it.

If the time difference is so big, it is likely that the query is being executed localy vs on the server, as a result your full table is fetched from the SQL server to china then localy determained what it needs.

There is a log I can run on a local computer to check this isn't there. I forget where?

My suggestion is to NOT use queries but rather use Stored Procedures (SP), this is assuming all the tables are SQL server tables...
The call the SP with the values, which should return much faster I think.

I was afraid of something like this. I have never done these. irrrr I have to learn again.

 

[namliam]

Thanks for the clean up I was not sure how to set it out for you guys to look at.

Its not "us guys" it is general SQL "good practice" or coding in general.
Keep stuff readale ...

Could you maybe please re-write this sentence as I am not sure I understand it.

You are specificaly searching 2 fields and using some key fields in join's.
If those fields are not indexed or not indexed 'properly' or the indexes are not up to date, then this can cause big decrease in performance.

There is a log I can run on a local computer to check this isn't there. I forget where?

I think so, but sorry dont have a clue...

I was afraid of something like this. I have never done these. irrrr I have to learn again.

Quick offhand sample idea like thing:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ParamTesting] ( @Param2 varchar(10))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

             -- Insert statements for procedure here
	SELECT @param2
END

Good luck

 

[darbid]

I am a little bit hesitant to invest time in changing to Stored Procedures in this case. But I have started reading and thus I have read about JET as well. JET should be handling this fine and not pulling all the tables to the machine locally.

I am guessing the problem is that my SQL query contains code that SQL Server (or ODBC driver) does not understand and thus the need to pull all the tables accross.

Is this the case with my SQL query?
Can anyone see a better way of doing my query?

 

[darbid]

Also unless I have not read enough so far - A stored procedure for a form will make the form read-only.

 

[namliam]

Also unless I have not read enough so far - A stored procedure for a form will make the form read-only.

Will admitt to not being sure on that point, I know you can send data into a SP that will update tables.... but interactively, dont know.

Alternative perhaps a view in SQL Server then
Select * from View where ....

 

[darbid]

I am still not keen on doing views/stored procedures as they will cause other limitations. I have done a trace with the ODBC but there is one part I do not understand.

Why is this being executed so many times

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

I assume this is the problem as it should not be executing that many times.

SQLExecDirect: SELECT Config, nValue FROM MSysConf

SQLExecDirect: SELECT "Lutadmin"."tbl_LuT Vorgang"."VorgangID" ,"Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ,"Lutadmin"."tbl_LuT Vorgang"."Kandidat" ,"Lutadmin"."tbl_LuT Vorgang"."KandidatCountry" ,"Lutadmin"."tbl_LuT Vorgang"."Sachgebiet-Vorgang" ,"Lutadmin"."tbl_LuT Vorgang"."Bearbeiter LuT" ,"Lutadmin"."tbl_LuT Vorgang"."Bearbeiter IP" ,"Lutadmin"."tbl_LuT Vorgang"."Treiber" ,"Lutadmin"."tbl_LuT Vorgang"."Datum Status" ,"Lutadmin"."tbl_LuT Vorgang"."Status" ,"Lutadmin"."tbl_LuT Vorgang"."Bemerkung" ,"Lutadmin"."tbl_LuT Vorgang"."Start" ,"Lutadmin"."tbl_LuT Vorgang"."Start_old" ,"Lutadmin"."tbl_LuT Vorgang"."Sector" ,"Lutadmin"."tbl_LuT Vorgang"."Bereich" ,"Lutadmin"."tbl_LuT Vorgang"."GG" ,"Lutadmin"."tbl_LuT Vorgang"."GZ" ,"Lutadmin"."tbl_LuT Vorgang"."Aktiv" ,"Lutadmin"."tbl_LuT Vorgang"."Wiedervorlage" ,"Lutadmin"."tbl_LuT Vorgang"."Aktion bei WV" ,"Lutadmin"."tbl_LuT Vorgang"."Kategorie" ,"Lutadmin"."tbl_LuT Vorgang"."Aufwand" ,"Lutadmin"."tbl_LuT Vorgang"."Priorität" ,"Lutadmin"."tbl_LuT Vorgang"."Patentportfolio" ,"Lutadmin"."tbl_LuT Vorgang"."ClaimChart" ,"Lutadmin"."tbl_LuT Vorgang"."Reporting" ,"Lutadmin"."tbl_LuT Vorgang"."ppSlides" ,"Lutadmin"."tbl_LuT Vorgang"."Tot" ,"Lutadmin"."tbl_LuT Vorgang"."TechnologyLong" ,"Lutadmin"."tbl_LuT Vorgang"."PotentialLicenceVolume" ,"Lutadmin"."tbl_LuT Vorgang"."Issues" ,"Lutadmin"."tbl_LuT Vorgang"."Contact" ,"Lutadmin"."tbl_LuT Vorgang"."Project" ,"Lutadmin"."tbl_LuT Vorgang"."Date_of_Contact" ,"Lutadmin"."tbl_LuT Vorgang"."Date_of_Project" ,"Lutadmin"."tbl_LuT Vorgang"."Pics" ,"Lutadmin"."tbl_LuT Vorgang"."locked_task" ,"Lutadmin"."tbl_LuT Vorgang"."upsize_ts" ,"taet1"."doneID" ,"taet1"."Arbeitstag" ,"taet1"."Aktenzeichen" ,"taet1"."Taetigkeit" ,"taet1"."Zeit" ,"taet1"."Fuer Monatsbericht" ,"taet1"."Berichtskategorie" ,"taet1"."fakeDate" ,"taet1"."upsize_ts" FROM {oj "Lutadmin"."tbl_LuT Vorgang" LEFT OUTER JOIN "Lutadmin"."tbl_Taetigkeit" "taet1" ON ("Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" = "taet1"."Aktenzeichen" ) } WHERE (((("Lutadmin"."tbl_LuT Vorgang"."Bearbeiter LuT" = 'COR' ) OR ("Lutadmin"."tbl_LuT Vorgang"."Treiber" = 'COR' ) ) AND ("Lutadmin"."tbl_LuT Vorgang"."Reporting" = 1) ) AND (("taet1"."Aktenzeichen" IS NULL ) OR ((NOT(EXISTS (SELECT 1 FROM "Lutadmin"."tbl_Taetigkeit" "taet3" WHERE (("taet3"."Aktenzeichen" = "Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ) AND ("taet3"."Fuer Monatsbericht" = 1) ) )) AND ("taet1"."Arbeitstag" = (SELECT MAX("taet4"."Arbeitstag" ) FROM "Lutadmin"."tbl_Taetigkeit" "taet4" WHERE ("taet4"."Aktenzeichen" = "Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ) )) ) OR ("taet1"."Arbeitstag" = (SELECT MAX("taet2"."Arbeitstag" ) FROM "Lutadmin"."tbl_Taetigkeit" "taet2" WHERE (("taet2"."Aktenzeichen" = "Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ) AND ("taet2"."Fuer Monatsbericht" = 1) ) )) ) ) )

SQLExecDirect: SELECT "Lutadmin"."tbl_LuT Vorgang"."VorgangID","taet1"."doneID" FROM {oj "Lutadmin"."tbl_LuT Vorgang" LEFT OUTER JOIN "Lutadmin"."tbl_Taetigkeit" "taet1" ON ("Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" = "taet1"."Aktenzeichen" ) } WHERE (((("Lutadmin"."tbl_LuT Vorgang"."Bearbeiter LuT" = 'COR' ) OR ("Lutadmin"."tbl_LuT Vorgang"."Treiber" = 'COR' ) ) AND ("Lutadmin"."tbl_LuT Vorgang"."Reporting" = 1) ) AND (("taet1"."Aktenzeichen" IS NULL ) OR ((NOT(EXISTS (SELECT 1 FROM "Lutadmin"."tbl_Taetigkeit" "taet3" WHERE (("taet3"."Aktenzeichen" = "Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ) AND ("taet3"."Fuer Monatsbericht" = 1) ) )) AND ("taet1"."Arbeitstag" = (SELECT MAX("taet4"."Arbeitstag" ) FROM "Lutadmin"."tbl_Taetigkeit" "taet4" WHERE ("taet4"."Aktenzeichen" = "Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ) )) ) OR ("taet1"."Arbeitstag" = (SELECT MAX("taet2"."Arbeitstag" ) FROM "Lutadmin"."tbl_Taetigkeit" "taet2" WHERE (("taet2"."Aktenzeichen" = "Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ) AND ("taet2"."Fuer Monatsbericht" = 1) ) )) ) ) )

SQLExecDirect: SELECT "LPID" ,"LuT Aktenzeichen" ,"GJ" ,"Potential" ,"degree_of_probability" ,"Weighted_Potential" ,"upsiz_ts" FROM "Lutadmin"."tbl_Licence_Potential" WHERE ("LuT Aktenzeichen" = '2009L09626' )

SQLExecDirect: SELECT "Lutadmin"."tbl_Licence_Potential"."LPID" FROM "Lutadmin"."tbl_Licence_Potential" WHERE ("LuT Aktenzeichen" = '2009L09626' )

SQLPrepare: SELECT "LPID","LuT Aktenzeichen","GJ","Potential","degree_of_probability","Weighted_Potential","upsiz_ts" FROM "Lutadmin"."tbl_Licence_Potential" WHERE "LPID" = ? OR "LPID" = ? OR "LPID" = ? OR "LPID" = ? OR "LPID" = ? OR "LPID" = ? OR "LPID" = ? OR "LPID" = ? OR "LPID" = ? OR "LPID" = ?

SQLExecute: (MULTI-ROW FETCH)

SQLPrepare: SELECT "VorgangID","LuT Aktenzeichen","Kandidat","KandidatCountry","Sachgebiet-Vorgang","Bearbeiter LuT","Bearbeiter IP","Treiber","Datum Status","Status","Bemerkung","Start","Start_old","Sector","Bereich","GG","GZ","Aktiv","Wiedervorlage","Aktion bei WV","Kategorie","Aufwand","Priorität","Patentportfolio","ClaimChart","Reporting","ppSlides","Tot","TechnologyLong","PotentialLicenceVolume","Issues","Contact","Project","Date_of_Contact","Date_of_Project","Pics","locked_task","upsize_ts" FROM "Lutadmin"."tbl_LuT Vorgang" WHERE "VorgangID" = ?

SQLExecute: (GOTO BOOKMARK)

SQLPrepare: SELECT "doneID","Arbeitstag","Aktenzeichen","Taetigkeit","Zeit","Fuer Monatsbericht","Berichtskategorie","fakeDate","upsize_ts" FROM "Lutadmin"."tbl_Taetigkeit" WHERE "doneID" = ?

SQLExecute: (GOTO BOOKMARK)

SQLExecute: (GOTO BOOKMARK)

SQLExecute: (GOTO BOOKMARK)

SQLExecDirect: SELECT "Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" ,"Lutadmin"."tbl_LuT Vorgang"."VorgangID","taet1"."doneID" ,"taet1"."Arbeitstag" ,"taet1"."Aktenzeichen" ,"taet1"."doneID" FROM {oj "Lutadmin"."tbl_LuT Vorgang" LEFT OUTER JOIN "Lutadmin"."tbl_Taetigkeit" "taet1" ON ("Lutadmin"."tbl_LuT Vorgang"."LuT Aktenzeichen" = "taet1"."Aktenzeichen" ) } WHERE ((("Lutadmin"."tbl_LuT Vorgang"."Bearbeiter LuT" = 'KIS' ) OR ("Lutadmin"."tbl_LuT Vorgang"."Treiber" = 'KIS' ) ) AND ("Lutadmin"."tbl_LuT Vorgang"."Reporting" = 1) )

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLExecDirect: SELECT "Arbeitstag" ,"Aktenzeichen" FROM "Lutadmin"."tbl_Taetigkeit" "taet2"

SQLPrepare: SELECT "VorgangID","LuT Aktenzeichen","Kandidat","KandidatCountry","Sachgebiet-Vorgang","Bearbeiter LuT","Bearbeiter IP","Treiber","Datum Status","Status","Bemerkung","Start","Start_old","Sector","Bereich","GG","GZ","Aktiv","Wiedervorlage","Aktion bei WV","Kategorie","Aufwand","Priorität","Patentportfolio","ClaimChart","Reporting","ppSlides","Tot","TechnologyLong","PotentialLicenceVolume","Issues","Contact","Project","Date_of_Contact","Date_of_Project","Pics","locked_task","upsize_ts" FROM "Lutadmin"."tbl_LuT Vorgang" WHERE "VorgangID" = ?

SQLExecute: (GOTO BOOKMARK)

SQLPrepare: SELECT "doneID","Arbeitstag","Aktenzeichen","Taetigkeit","Zeit","Fuer Monatsbericht","Berichtskategorie","fakeDate","upsize_ts" FROM "Lutadmin"."tbl_Taetigkeit" WHERE "doneID" = ?

SQLExecute: (GOTO BOOKMARK)

SQLExecDirect: SELECT "Lutadmin"."tbl_Licence_Potential"."LPID" FROM "Lutadmin"."tbl_Licence_Potential" WHERE ("LuT Aktenzeichen" = '2005L20985' )

SQLExecDirect: SELECT "LPID","LuT Aktenzeichen","GJ","Potential","degree_of_probability","Weighted_Potential","upsiz_ts" FROM "Lutadmin"."tbl_Licence_Potential" WHERE ("LuT Aktenzeichen" = '2005L20985' )

SQLExecDirect: SELECT Config, nValue FROM MSysConf

SQLExecDirect: SELECT [tbl_LuT Vorgang].Status, Count([Datum Status]) AS Expr1, [tblComboLists_Server].listorder FROM [tbl_LuT Vorgang] INNER JOIN [tblComboLists_Server] ON [tblComboLists_Server].English=[tbl_LuT Vorgang].Status WHERE [Bearbeiter LuT]='KIS' And [Reporting]=1 GROUP BY [tbl_LuT Vorgang].Status, [tblComboLists_Server].listorder;

SQLExecDirect: SELECT [tbl_LuT Vorgang].[LuT Aktenzeichen], [tbl_LuT Vorgang].Kandidat, tbl_Licence_Potential.Potential, FLOOR(tbl_Licence_Potential.Potential*tbl_Licence_Potential.degree_of_probability/100) AS WeightedPotential FROM [tbl_LuT Vorgang] INNER JOIN tbl_Licence_Potential ON [tbl_LuT Vorgang].[LuT Aktenzeichen]=tbl_Licence_Potential.[LuT Aktenzeichen] WHERE [Bearbeiter LuT]='KIS' And tbl_Licence_Potential.GJ='09/10' And [Reporting]=1;

SQLExecDirect: select ISNULL(project.yemo,contact.yemo), contactcount, projectcount From (SELECT convert(varchar(7), Date_of_Contact, 120) as yemo, count(Date_of_Contact) as contactcount From [tbl_LuT Vorgang] WHERE [Bearbeiter LuT] = 'KIS' AND Date_of_Contact BETWEEN '10/01/2009' AND '05/27/2010' GROUP by convert(varchar(7), Date_of_Contact, 120) ) contact full Outer join (SELECT convert(varchar(7), Date_of_Project, 120) as yemo , count (Date_of_Project) as projectcount From [tbl_LuT Vorgang] WHERE [Bearbeiter LuT] = 'KIS' AND Date_of_Project BETWEEN '10/01/2009' AND '05/27/2010' GROUP by convert(varchar(7), Date_of_Project, 120) ) project ON contact.yemo = project.yemo;

SQLPrepare: SELECT "VorgangID","LuT Aktenzeichen","Kandidat","KandidatCountry","Sachgebiet-Vorgang","Bearbeiter LuT","Bearbeiter IP","Treiber","Datum Status","Status",'#S_C_H#' ,"Start","Start_old","Sector","Bereich","GG","GZ","Aktiv","Wiedervorlage",'#S_C_H#' ,"Kategorie","Aufwand","Priorität","Patentportfolio","ClaimChart","Reporting","ppSlides","Tot",'#S_C_H#' ,'#S_C_H#' ,'#S_C_H#',"Contact","Project","Date_of_Contact","Date_of_Project","Pics","locked_task","upsize_ts" FROM "Lutadmin"."tbl_LuT Vorgang" WHERE "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ? OR "VorgangID" = ?

SQLExecute: (MULTI-ROW FETCH)

SQLPrepare: SELECT "doneID","Arbeitstag","Aktenzeichen",'#S_C_H#' ,"Zeit","Fuer Monatsbericht","Berichtskategorie","fakeDate","upsize_ts" FROM "Lutadmin"."tbl_Taetigkeit" WHERE "doneID" = ? OR "doneID" = ? OR "doneID" = ? OR "doneID" = ? OR "doneID" = ? OR "doneID" = ? OR "doneID" = ? OR "doneID" = ? OR "doneID" = ? OR "doneID" = ?

 

[namliam]

( select TOP 1 max(Arbeitstag) 
   from   tbl_Taetigkeit  taet2
   WHERE  taet2.Aktenzeichen = [tbl_LuT Vorgang].[LuT Aktenzeichen])

Is probably the cause of that...

Try re-writing this into a "proper" join... That would be something like this:

SELECT *
FROM      [tbl_LuT  Vorgang] 
LEFT JOIN [tbl_Taetigkeit]   AS taet1 ON [tbl_LuT Vorgang].[LuT  Aktenzeichen]=taet1.Aktenzeichen
Left Join ( select Aktenzeichen, max(Arbeitstag) MaxTag
            from   tbl_Taetigkeit  
	    Group by taet2.Aktenzeichen ) teat2 on taet1.Arbeitstag = taet2.MaxTag
					       and taet1.Aktenzeichen
 = teat2.Aktenzeichen
WHERE    (    [tbl_LuT  Vorgang].[Bearbeiter LuT] = '" & str_current_lc & "'
           OR [tbl_LuT  Vorgang].[Treiber]        = '" & str_current_lc & "')
  AND    [tbl_LuT  Vorgang].[Reporting] = true
ORDER BY doneID DESC;

Mind its aircode, might need some adjusting

 

[LPurvis]

Hi

That your query takes some executing isn't too surprising. Your old one should have been at least as bad. Perhaps the number of rows has changed in the interim?


>>There is a log I can run on a local computer to check this isn't there. I forget where?
SQL Out. http://support.microsoft.com/kb/113918
You can also switch on ODBC tracing or set up a trace on the server (using Profiler) to watch what it is receiving querywise.

>> I am guessing the problem is that my SQL query contains code that SQL Server (or ODBC driver) does not understand and thus the need to pull all the tables accross.
That can happen yes - though as you have multiple criteria there's every chance that the scalar values you've requested (e.g. [tbl_LuT Vorgang].[Bearbeiter LuT]= '" & str_current_lc & "') will be parsed and filtered on the server. But your subquery will be a repetative request which isn't joined).

>> Also unless I have not read enough so far - A stored procedure for a form will make the form read-only.
Nope. What the SP selects and how you bind its results to the form may result in it being read only - but it's not an inevitable result of using an SP in a form by any means.

>> Alternative perhaps a view in SQL Server then Select * from View where
That would yield a better result if you're performing some aggregation or sub-select in the view.
Your subquery isn't requiring any form values AFAICT - so it's purely a pre-filtering exercise.
However, depending upon how it was eventually formed, it would run the risk of not being updatable (even on the server I mean).

>> Why is this being executed so many times
It'll be to satisfy the requirements for your subquery in the WHERE clause.
It's a corrolated subquery - and so will be executed for each row. Without the server taking the brunt of that (separate tables linked in your Access app) it's not optimised but made as separate requests from Jet.

>> Try re-writing this into a "proper" join...
I'd agree to alleviate the corrolated repeated execution somewhat - but the aggregated query in an ANSI join (not in the WHERE clause anymore) means your overall query result will not be updatable.

Ultimately if you're making convoluted data requests - then the effort to implement an SP is often just a consequence.
If you ask for more, you pay more.
(Here the requests is data convolution, the price is a bit of time at development stage).

That's assuming your request can't be simplified a bit...
Certainly:
select TOP 1 max(Arbeitstag) from tbl_Taetigkeit taet2
is redundant.
select max(Arbeitstag) from tbl_Taetigkeit taet2
would be adequate.

Cheers.

 

[darbid]

namlian: Thanks for the suggestion. I will give it a go or refine it to see if it speeds things up. I could take a while to reply cause this stuff is very much above me and I have to learn it.

Leigh: I really appreciate you going through the whole thread and putting a reply. Thank you.

I will try to get a better SQL query. But I really want my code to run fast/efficient and not have people waiting. It is slow enough in China and Japan because the server is in Europe so I do not want to make this situation even worse.

Thus I have realised that the "Top 1 Max(Arbeitstag)" part gives me 3 fields that I do not need to update. They are only shown.

So it might be very well possible that I drop it all together in the RecordSource and then use just this part in the OnCurrent event of the form.

Once again I really appreciate your help guys.

 

[LPurvis]

It just seems you're potentially harming the productivity of your application by not at least creating a view - whose definition is just:

SELECT *
FROM 
    [tbl_LuT  Vorgang] V
       LEFT JOIN  
    tbl_Taetigkeit AS T 
        ON V.[LuT  Aktenzeichen]=T.Aktenzeichen
WHERE  V.[Reporting] = 1
  AND  (T.Arbeitstag = (SELECT MAX(T2.Arbeitstag) 
                        FROM tbl_Taetigkeit  T2
                        WHERE T2.Aktenzeichen = V.[LuT Aktenzeichen])
          OR  T.Aktenzeichen Is Null)

Your local query then becomes just

strSQL = "SELECT * " & _
    "FROM vw_YourView " & _
    "WHERE ([Bearbeiter LuT]= '" & str_current_lc & "'" & _
    "  OR [Treiber]= '" & str_current_lc & "')"

That's not necessarily an ultimate solution - but it must perform better than the one you've seen so far.

Obviously, omitting the subquery altogether will have an impact.
Both positive in performance, but that criterion must be there for some reason. You only want the maximum dates returned.
The view is an easy half-way house short of going to a SP (which would then require some alternative execution mechanism than your linked tables as at present).

Cheers.

 

[darbid]

It just seems you're potentially harming the productivity of your application by not at least creating a view - whose definition is just:

I hate to ask a sub question when someone posts and answer but from my reading a view in SQL Server 2000 is not updateable when on an Access 2003 form. I do not expect you to tell me how, but knowing that I can, I then will not be wasting time googleing and reading how to.

This form has many fields where people will make changes to things.

I am a self taught coder, doing this for the love of it but my program is for work so I cannot do anything on it today. My plan for testing if I can get things faster is;

1. Simply take out the "Top 1" and see what difference that makes.
2. Follow the suggestion of Namlian with a "proper join" and see what difference that makes.
3. pull out hair and cry a little and see what difference that makes. (probably just make me uglier)
4. learn views and or SPs and how to get them to work with access forms
5. pull out what is left of my hair.

I appreciate the help.

 

[LPurvis]

No probs.

1. It was just redundant. Alone it shouldn't make that much difference (it might even have been optimised out already).
2. The Join syntax (as I mentioned) is a good idea for performance, but will render your results non-updatable.
3. I've probably felt my share of frustrations (especially years ago when starting out) but the highs are worth it and it gets so much easier.
4. There's not much to learn. You can create one on the server almost as easily as a table itself. Open a query window and create it as
CREATE VIEW vw_YourViewName
AS
{insert the SQL from above here}
The run that.
Once your view exists, you can link to it from within your Access application (just like a table).
Assuming the view is updatable on the server then it would be in Access if you select the appropriate primary key when linking.
Views are intended to only allow updating to a single table though. (Access/Jet in general hides how complicated multiple table updates are - it's really very good at it, even compared to some of the big server databases).
If you're wanting to update more than one table then that usually says to me that a parent/sub form may be a more appropriate UI. Each form bound to the appropriate table/view only.

In summary - no a view needn't be read only, just as an SP needn't be.

Cheers.

 

[namliam]

( select TOP 1 max(Arbeitstag) 
   from   tbl_Taetigkeit  taet2
   WHERE  taet2.Aktenzeichen = [tbl_LuT Vorgang].[LuT Aktenzeichen])

Is probably the cause of that...

Thinking about this again, perhaps analytics is the solution to this sub-query, basicaly you want the highest value which is totaly doable

select row_number() over (partition by Aktenzeichen order by Arbeitstag desc, cont_key desc) RowNumber
, t.* from tbl_Taetigkeit t

This query/table remains updatable as far as I know and when used in a subquery:
Select
From abovequery
Where Rownumber = 1

Will only return the one, but be a lot better than what you have I think.

 

[LPurvis]

Bearing in mind that this would have to be created on the server (view or SP/UDF).
It's one of several concepts worth doing though - if you're willing and able to create server objects.

Cheers.

 

[darbid]

hi guys, thanks.

Tomorrow I will get into learning, implementing, testing and most probably questioning.

 

[darbid]

Learning and testing day today.

Using access and the query builder I copied my original query into it and as expected it took a long time to do.

I then took out the "TOP 1" and the query is executed instantly. Not only is that TOP 1 redundant but it must give JET/SQL Server some real hell.

I have sent a test to Japan to see how they get on.

If that has "fixed" the problem then I will implement it as a short term fix. I will then read up on views and try the view out next.

But I would like to get my code "working" in the eyes of my friends/colleagues first.

 

[LPurvis]

Fair enough.
Only by examining a trace (or ShowPlan) would you know what was actually happening query-wise with that, but the TOP 1 was clearly not being optimised out - and was almost certainly being applied on the client - possibly even before aggregation (based upon execution time, but that would be a disasterous choice given the clearly defined aggregation applied).

Bear in mind that even the aggregation alone is very likely being performed on the client - making this process less than ideal (i.e. you might still observe a poor performance over a remote connection - such WAN solutions, even on a server database, are not ideal and often poor performing).

Cheers

 

[darbid]

Agreed.

Step two is the View.

Sometimes problems are good, it means you learn new things.

If the code was not mine I would swear that somebody else put that TOP 1 in there cause I have had a look at my backups and it is not there. Mr Access 2003 Fairy did it for sure . I hope that the 2010 Fairy is nicer.