RE: [UV] Very slow SQL query
Hello Björn I build the index and the result is equal. Tanks. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Björn Eklund Enviado el: jueves, 19 de febrero de 2004 17:41 Para: 'U2 Users Discussion List' Asunto: SV: [UV] Very slow SQL query Cesar, You need to build the index also, try BUILD.INDEX FILNAME ALL In Unidata that is. Björn Eklund -Ursprungligt meddelande- Från: Cesar Riba [mailto:[EMAIL PROTECTED] Skickat: den 19 februari 2004 17:24 Till: 'U2 Users Discussion List' Ämne: RE: [UV] Very slow SQL query -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Brian Leach Enviado el: jueves, 19 de febrero de 2004 16:22 Para: 'U2 Users Discussion List' Asunto: RE: [UV] Very slow SQL query César, I think we will need more information to help you. 1. are you running the SQL query at TCL or through ODBC or OleDB? César: I running the SQL query at TCL 2. Can you give the command you are running please. Cesar: SELECT PEDIDO,REFART,DESART,CANTIDAD,CANTMODALB FROM GCMLIAL WHERE EMPALBA = '1*05236' ORDER BY EMP, ALBA, NUMLIN; Cesar: SELECT PEDIDO, SUREF FROM GCMLIAL WHERE EMPALBA = '1*05236' GROUP BY PEDIDO, SUREF; 3. Are there any indexes on the files? (LIST.INDEX filename ALL) Cesar: There aren't indexes on the files, I create index but the result is the same. Regards, Brian Leach -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cesar Riba Sent: 19 February 2004 15:04 To: [EMAIL PROTECTED] Subject: [UV] Very slow SQL query Hello, I have two files one has 300.000 records and another has 200.000 records, I wante to make SQL Query whit the two files and this takes bettewn 30 seconds and 1 minute to return the result. However, I have a program with BASIC to obtime the same result and it takes less than one second, I have heard that other DB Systems like SQL Server, Oracle, etc... are very fast with the SQL Query. Is this posible or do have I made a mistake? Thanks César -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users This email was checked by MessageLabs SkyScan before entering Microgen. This email was checked on leaving Microgen for viruses, similar malicious code and inappropriate content by MessageLabs SkyScan. DISCLAIMER This email and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and do not disclose the contents to any other person, use it for any purpose, or store or copy the information. In the event of any technical difficulty with this email, please contact the sender or [EMAIL PROTECTED] Microgen Information Management Solutions http://www.microgen.co.uk -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
SQL with UniObject
Hello, Can somebody can explain me how to build a soubroutine for executing SQL commands from Visual Basic and return of result in a UniDynArray. I need too insert sentences as like SELECT TO SLIST 0 FROM CUSTOMERS; Thanks, César Riba -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: SQL with UniObject
Hello I test SQL query from TCL and it work fine, but from Visual Basic don't work. In TCL I execute this sentence and the result is OK: SELECT TO SLIST 0 FROM GCDHIST WHERE CODUSER = '02'; 98 record(s) selected to SELECT list #0. SELECT ESTADO, FECHA, MARCA FROM SLIST 0 GCDHIST; In VB I execute this sentence and the first sentence not activate the list. Is this a incompatibility? Thanks. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Hona, David S Enviado el: martes, 24 de febrero de 2004 5:32 Para: 'U2 Users Discussion List' Asunto: RE: SQL with UniObject Your SQL SELECT query is invalid. FORM is a typo and should be FROM, also you haven't specified any column names. Fix those two problems and you should be in business. SELECT valid-column-name(s) TO SLIST select-list-number FROM table-name ; I suggest you always test your SQL queries from TCL first. If it won't work from TCL, it isn't going to work from UniObjects. If you're unfamilar with UV/SQL, you should refer to the UV SQL Reference Guide. It details the correct syntax. Manuals are online, here: http://www-306.ibm.com/software/data/u2/pubs/library/ Regards David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Cesar Riba Sent: Tuesday, February 24, 2004 4:52 AM To: 'U2 Users Discussion List' Subject: RE: SQL with UniObject Hello, Your code is ok, but the SQL Command SELECT TO SLIST 0 FORM CUSTOMERS;, isn't generate a selectlist active. What is that ocurr?. This command don't functio from VB. Thanks. -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Consult about SQL
Hello, I'm a problem when use a SQL query that have one or various INNER JOIN this is very very slow. How I can accelerate this tipus of query. Thanks. César -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
RE: [UV] Consult about SQL
Hello, Another question Because a SQL query executed in Universe it take 20 seconds and the same query in MSAccess take 3 seconds. This is the result of EXPLAIN of this query SELECT LINFAC.GRUPART, SUM(LINFAC.BASPP * LINFAC.CAN) As SUMCANT, GCAREPRE.NOMBRE AS NOMREPRE, SQL+GCAREPRE.CODIGO AS CODREPRE SQL+FROM SQL+GCAREPRE INNER JOIN GCMCL ON GCAREPRE.CODIGO = GCMCL.CODREP1 SQL+INNER JOIN LINFAC ON [EMAIL PROTECTED] = LINFAC.CODCLI SQL+WHERE LINFAC.EMP = 1 AND SQL+(LINFAC.TIPFAC='01' OR LINFAC.TIPFAC='03' OR LINFAC.TIPFAC='05' OR LINFAC.TIPFAC='06' OR LINFAC.TIPFAC='10') SQL+GROUP BY LINFAC.GRUPART, GCAREPRE.CODIGO, GCAREPRE.NOMBRE HAVING SUM(LINFAC.BASPP*LINFAC.CAN) IS NOT NULL SQL+EXPLAIN; Optimizing query block 0 Table restriction: LINFAC.EMP = 1 AND (LINFAC.TIPFAC = '10' OR (((LINFAC.TIPFAC= '01' OR LINFAC.TIPFAC = '03') OR LINFAC.TIPFAC = '05') OR LINFAC.TIPFAC = '06')) Driver source: LINFAC Access method: file scan 1st join primary: LINFAC est. cost: 30445 secondary: GCMCL est. cost: 23469 type: hashed join (primary key) 2nd join primary: GCMCL est. cost: 23469 secondary: GCAREPRE est. cost: 131 type: hashed join (primary key) Grouped by: GRUPARTCODIGONOMBRE Thanks, -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Ray Wurlod Enviado el: jueves, 01 de abril de 2004 22:44 Para: U2 Users Discussion List Asunto: Re: [UV] Consult about SQL The best way to speed inner joins is to index the column that is the foreign key. For example, if your join is of the form: SELECT T1.ORDERNO, T2.CUSTNO, T2.CUSTNAME FROM ORDERS T1, CUSTOMERS T2 WHERE T1.CUSTNO = T2.CUSTNO ; then you need an index on ORDERS.CUSTNO In other cases, depending on exactly what the query is, you can also sometimes obtain benefit by rewriting the SQL to use sub-queries, unions, and other techniques. - Original Message - From: Cesar Riba [EMAIL PROTECTED] Date: Thu, 1 Apr 2004 11:10:18 +0200 To: [EMAIL PROTECTED] Subject: Consult about SQL Hello, I'm a problem when use a SQL query that have one or various INNER JOIN this is very very slow. How I can accelerate this tipus of query. Thanks. César -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
