Hello friends, I would first like to wish a Merry Christmas to all and their families.
Well come on, as the subject of the e-mail says I have a cruel doubt that the best way to create a select from sequinte tables:
CEP_UF => STATE
CEP_CIDADE => CITY
CEP_BAIRRO => NEIGHBORHOOD
CEP_ENDERECO = ADDRESS
The forenkeys are exactly the sequence of tables that is:
CEP_ENDERECO => COD_BAIRRO
CEP_BAIRRO => COD_CIDADE
CEP_CIDADE => COD_UF
Now I want to create a select that I may result in the following fields:
cep_endereco.cod_logradouro (PK), cep_endereco.num_cep, cep_endereco.dsc_logradouro, cep_endereco.dsc_complemento,
cep_bairro.dsc_bairro
cep_cidade.dsc_cidade
cep_uf.cod_uf
I have 700,000 records in the table CEP_ENDERECO
Now comes the big question?
What is the best way to receive the result of SELECT as quickly as possible in the database PostgreSQL?
Apart from the obvious that it’s the JOIN of tables which would be the rates (I believe that is the way to solve the problem) I create and in which tables?
Hugs and thanks for any help you can give me.
Jomello
NOTE: This select is being used in a web application and I’m using LIMIT 20 OFFSET 0 (for example), but even so this taking too long to return the records
By any chance, are you using ORDER BY in final SQL?
While using LIMIT instruction in SQL query the only way to slowdown it is use of ORDER or GROUP instruction, because to perform them DB need to build full list of possible records.
I managed to work with a little more speed, but now
when I pgdn it correctly sends the variables start and count.
Also generates the correct SQL, but when the GRID
recharge often back to the first record.
What am I doing wrong?
My code:
JS
mygridEndereco = new dhtmlXGridObject(‘gridEndereco’);
mygridEndereco.setImagePath(cBase+"/resources/dhtmlxGrid/codebase/imgs/");
mygridEndereco.setHeader(“C.E.P.,Logradouro,Complemento,Bairro,Cidade,Estado”);
mygridEndereco.attachHeader("#connector_text_filter,#connector_text_filter, ,#connector_text_filter,#connector_text_filter,#connector_select_filter")
mygridEndereco.setInitWidths(“100,,,,,50”);
mygridEndereco.enableAutoWidth(true);
mygridEndereco.setColAlign(“right,left,left,left,left,center”);
mygridEndereco.setColTypes(“ro,ro,ro,ro,ro,ro”);
mygridEndereco.setColSorting(“connector,connector,connector,connector,connector,connector”);
mygridEndereco.attachEvent(“onRowSelect”, selecionaRowEndereco);
mygridEndereco.attachEvent(“onXLE”, fechaJanela);
mygridEndereco.attachEvent(“onXLS”, function() {
document.getElementById(‘cover’).style.display = ‘block’;
});
mygridEndereco.enableSmartRendering(true);
mygridEndereco.init();
mygridEndereco.setSkin(“dhx_skyblue”);
mygridEndereco.loadXML(cBase+“ServerConnectorServlet”);
SERVLET
Connection conn = (new DataBaseConnection()).getConnection();
String cSql = "SELECT cep_endereco.COD_LOGRADOURO,cep_endereco.NUM_CEP,cep_endereco.DSC_LOGRADOURO, "
+ "coalesce(cep_endereco.dsc_complemento, cep_endereco.dsc_complemento, ’ ') as dsc_complemento, " +
"cep_bairro.dsc_bairro, cep_cidade.dsc_cidade, cep_cidade.cod_uf "
+ "from cep_endereco, cep_bairro, cep_cidade "
+ "where cep_endereco.cod_bairro = cep_bairro.cod_bairro and cep_bairro.cod_cidade = cep_cidade.cod_cidade ";
GridConnector c = new GridConnector(conn, DBType.PostgreSQL);
c.dynamic_loading(true);
c.dynamic_loading(20);
c.render_sql(cSql, “cep_endereco.cod_logradouro”, “num_cep,dsc_logradouro,dsc_complemento,dsc_bairro,dsc_cidade,cod_uf”);
,#connector_select_filter"
Server side code makes DISTINCT SELECT against all dataset to fill list of options, which is most probably slowdown initial rendering. It may have sense to define separate query or predefined list of values for this filter. ( not related to pgdn problem )
dhtmlx.com/dhxdocs/doku.php?id=d … ns_in_grid
>>c.dynamic_loading(true);
>>c.dynamic_loading(20);
You need only second line
c.dynamic_loading(20);
Thanks friend for your help, as the performance that I’ve decided.
The problem now is that when the Grid renders it sends the parameters correctly and does the SQL also correct, but when the GRID will update the data in return he always puts the first record, as if I had opened the GRID at the moment.
I do not know if it can generate an error, but in my page beyond the GRID also am using the ComboBox.
All files that I am putting for matching are:
<script src="<%=basePath%>/resources/dhtmlxCombo/codebase/ext/dhtmlxcombo_extra.js">
Hugs and thanks again 
but when the GRID will update the data in return he always puts the first record, as if I had opened the GRID at the moment
By update do you mean the scrolling or edit operation in the grid ?
Incorrect response on edit operation can be caused by missed connector.js ( or including it before dataprocessor.js )
If problem occurs during scrolling - please provide a server side log for problematic operation and sample of response xml.
Quero dizer atualizar o livro, eu não uso o update direto na grelha.
Uma coisa que notei é que incorpora os registros retornados, mas sempre volta para a primeira linha e no exemplo que ele mostra os novos registros a partir da linha atual.
LOG.TXT (68.3 KB)
XML_GRID.txt (4.61 KB)