SQL Server Performance Forum – Threads Archive

Dynamic SQL, Subform in MS Access, Performance

Hey! I have an terrible slow execution of a query – about 4 seconds the subform – with the following:
MS Access 2000 frontend wiht forms and subforms. SQL-Server 7.0 as DBMS on an Win 2000 Server as db-server system.
An example of an offer with price history. User can navigate in a little set of records the change and finish them.
Navigation is awful slow too: recordsource of the Subform in VB-code lines: SQL = "SELECT KR.KR_Rabatt, D.ET_V_ID, D.ET_GueltigAb, D.ET_Preis, D.ET_Preis * (100-KR.KR_Rabatt)/100 as KR_RabattPreis, "
SQL = SQL & "D.ET_Preis * (100-KR.KR_Rabatt)/100 * (100-V.A_Rabatt)/100 * (100-V.AD_Rabatt)/100 as RabattierterPreis, "
SQL = SQL & "V.AD_Menge * D.ET_Preis * (100-KR.KR_Rabatt)/100 * (100-V.A_Rabatt)/100 * (100-V.AD_Rabatt)/100 as PreisMalMengeRabattiert, V.* "
SQLins = SQL & " into T_F_AngebotDetails_UF_tmp "
SQL2 = "FROM ["
SQL2 = SQL2 & " select K.KR_Rabatt, K.KR_P_ID "
SQL2 = SQL2 & " from T_Kundenrabatte K inner join ( "
SQL2 = SQL2 & " select max(KR.KR_GueltigAb) AS M "
SQL2 = SQL2 & " from T_Kundenrabatte KR "
SQL2 = SQL2 & " WHERE KR.KR_GueltigAb <= cdate(‘" & tag & "/" & monat & "/" & jahr & "’) "
SQL2 = SQL2 & " and KR.KR_P_ID = " & Param_P_ID
SQL2 = SQL2 & " group by KR.KR_P_ID "
SQL2 = SQL2 & " ) "
SQL2 = SQL2 & " AS KMAX "
SQL2 = SQL2 & " on K.KR_GueltigAb = KMAX.M "
SQL2 = SQL2 & " ]. AS KR inner join ( "
SQL2 = SQL2 & " V_B_Angebote V INNER JOIN ( "
SQL2 = SQL2 & " SELECT A.ET_V_ID, A.ET_Preis, A.ET_GueltigAb "
SQL2 = SQL2 & " FROM T_Ersatzteilpreise AS A INNER JOIN ( "
SQL2 = SQL2 & " SELECT ET.ET_V_ID, max( ET.ET_GueltigAb) as MaxDatum "
SQL2 = SQL2 & " FROM T_Ersatzteilpreise AS ET WHERE ((ET.ET_GueltigAb) <= cdate(‘" & tag & "/" & monat & "/" & jahr & "’) ) "
SQL2 = SQL2 & " GROUP BY ET.ET_V_ID ) AS C "
SQL2 = SQL2 & " ON (A.ET_GueltigAb =C.MaxDatum) AND (A.ET_V_ID = C.ET_V_ID) ) AS D "
SQL2 = SQL2 & " ON V.V_ID = D.ET_V_ID ) "
SQL2 = SQL2 & "on KR.KR_P_ID = V.P_ID "
SQL2 = SQL2 & "where AD_A_ID = " & Param_AD_A_ID
SQL2 = SQL2 & " order by AD_EintragsDatum " SQL = SQL & SQL2
SQLins = SQLins & SQL2
Showplan der Jet-Engine (if I open only the subform a second time if the main-/subform together are opened behind): — temp query — – Inputs to Query –
ODBC table ‘T_Kundenrabatte’
Using index ‘PK_T_Kundenrabatte’
Having Indexes:
PK_T_Kundenrabatte 1661 entries, 2 pages, 1661 values
which has 2 columns, fixed, unique, primary-key, no-nulls
ODBC table ‘T_Kundenrabatte’
ODBC table ‘V_B_Angebote’
ODBC table ‘T_Ersatzteilpreise’
Using index ‘PK_ET_V_ID_ET_GueltigAb’
Having Indexes:
PK_ET_V_ID_ET_GueltigAb 12567 entries, 6 pages, 12567 values
which has 2 columns, fixed, unique, primary-key, no-nulls
ODBC table ‘T_Ersatzteilpreise’
– End inputs to Query – 453460120) Remote SQL
453460120) Remote SQL
453460120) Remote SQL
453460120) Remote SQL
453460120) Remote SQL
453460120) Remote SQL
453460120) Remote SQL
01) Sort result of ’00)’
02) Inner Join result of ’00)’ to result of ’01)’
using temporary index
join expression "K.KR_GueltigAb=KMAX.M"
453460120) Remote SQL
453460120) Remote SQL
453460120) Remote SQL
453460120) Remote SQL
03) Sort result of ’02)’
04) Inner Join result of ’02)’ to result of ’03)’
using temporary index
join expression "A.ET_GueltigAb=C.MaxDatum And A.ET_V_ID=C.ET_V_ID"
05) Sort result of ’04)’ —-End of showplan—- 1. how to read the plan?
2. how to create the execution completely on SQL-Server (parametrisized query e.a.)? any ideas? Spider2618

1. I have never read this kind of plan before.
2. I would say, create a stored procedure with parameters. There seems to some ‘temporary indexes’ in your plan. Please try to investigate what indexes you actually need to create to assist in the execution of this and any other queries. Can this query be written in a better way? Nathan H.O.
Moderator
SQL-Server-Performance.com

Hey! –]1. I have never read this kind of plan before. You can activate it by making some changes in the registry part of the jet engine (jetshowplan etc.). I will copy it the next time IÂ´m on a Windows system working. spider2618

OK. Unlike the SQL Server execution plan which is read from the left,bottom up to the right (kinda like chinese), the JET execution plan is read as it is – Top down. The operators are numbered 01),02),03 etc and are of course named differently from SQL Server’s. I suppose drawing a small flowchart for yourself might just be helpful. I must admit that it is not as good as that seen in the Query Analyser. Creating a stored procedure and calling it would still be better and you can actually see the (better) plan within the SQL QA if you were so inclined. Nathan H.O.
Moderator
SQL-Server-Performance.com

Hey! 1. Showplan in MS Access: HKEY_LOCAL_MACHINESOFTWAREMicrosoftJetXXEngines (XX is for the version number of your jet, i.e. 4.0 for Access 2000) Here create a key "Debug". Herein create a text value called JETSHOWPLAN with the value ON to activate (OFF to deactivate). Now access creates a file showplan.out at the work directory path set in MS Access/Option. 2. I tested the query in the Query Analyzer. There it is running as fast as possible. Just a click.
It must be something with the ODBC-overhead. So IÂ´m going to create a passthrough query by access code to obtain the best performance. spider 2618

]]>