Start Building Professional
Web Apps Today
| Categories | Question details Back To List | ||||||||||||||
Big data Grid - Used Connector 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 Answer posted by Stanislav (support) on Dec 04, 2009 00:46 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. Answer posted by João de Lima on Dec 08, 2009 05:05 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"); Answer posted by Stanislav (support) on Dec 08, 2009 10:22 >>,#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 ) http://dhtmlx.com/dhxdocs/doku.php?id=dhtmlxconnectorjava:select-box_columns_in_grid >>c.dynamic_loading(true); >>c.dynamic_loading(20); You need only second line c.dynamic_loading(20); Answer posted by João de Lima on Dec 08, 2009 13:51 Thanks friend for your help, as the performance that I've decided. <link rel="STYLESHEET" type="text/css" href="<%=basePath%>/resources/dhtmlxGrid/codebase/dhtmlxgrid.css"> <script src="<%=basePath%>/resources/libCompiler/dhtmlxcommon.js"></script> <script> <link rel="STYLESHEET" type="text/css" href="<%=basePath%>/resources/dhtmlxCombo/codebase/dhtmlxcombo.css"> <script type="text/javascript" src="<%=basePath%>/resources/dhtmlxCombo/codebase/dhtmlxcombo.js"></script> Hugs and thanks again :-) Answer posted by Stanislav (support) on Dec 09, 2009 02:13 >>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. Answer posted by João de Lima on Dec 09, 2009 05:02 Quero dizer atualizar o livro, eu não uso o update direto na grelha. Attachments (2)
|
