January 13th, 2005, 11:43 AM
|
|
Contributing User
|
|
Join Date: Oct 2004
Location: Guadalajara, Jalisco, México
Posts: 62
Time spent in forums: 17 h 51 m 29 sec
Reputation Power: 4
|
|
|
How to force a Plan to a view?
Hi...
Is there some way to force a Plan to a view definition, so when invoked through ODBC it applies?
If I run the next query without defining a Plan, it takes about 2 hours for Access 2003 to export an Excel File (I tried the query within IBExpert and it takes aout 15 minutes to give me the first 13 rows) :
Code:
select "Temp3".BUC, "NombreCliente", "Segmento", "NombreSegmento", "Cve_Prod", "Promedio", "Saldo", "Oficial", "Cve_Suc", "Cve_Zon", "Cve_Reg"
from ((((("Temp3" inner join "Clientes" on "Clientes".BUC = "Temp3".BUC) inner join "Segmentos" on "Clientes"."Segmento"="Segmentos"."Segmento")
inner join "AsignacionClientes" on "Temp3".BUC = "AsignacionClientes".BUC)
inner join "ArbolOficiales" on "AsignacionClientes"."Oficial" = "ArbolOficiales"."Oficial")
inner join "Sucursales" on "Sucursales"."Cve_Suc" = "ArbolOficiales"."Cve_Suc")
inner join "Zonas" on "Zonas"."Cve_Zon" = "Sucursales"."Cve_Zon"
where "Version" = 'A2' and "Cve_Reg"='9000'
IBExpert says that Firebird is using the next plan :
Code:
Adapted Plan PLAN JOIN (AsignacionClientes INDEX (PK_AsignacionClientes),ArbolOficiales INDEX (PK_ArbolOficiales),Sucursales INDEX (PK_Sucursales),
Zonas INDEX (PK_Zonas),Clientes INDEX (PK_Clientes),Segmentos INDEX (PK_Segmentos),Temp3 INDEX (PK_Temp3))
With the same query, assigning the next Plan manually it takes less than 20 seconds to spit all the 1035 rows:
Code:
plan join ( "Temp3" natural, "Clientes" INDEX ("PK_Clientes"), "Segmentos" INDEX ("PK_Segmentos"), "AsignacionClientes" INDEX ("PK_AsignacionClientes"), "ArbolOficiales" INDEX ("PK_ArbolOficiales"),
"Sucursales" INDEX ("PK_Sucursales"), "Zonas" INDEX ("PK_Zonas") )
Any suggestion?
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
