Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

SQL performance using union

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Guest's Avatar
    Guest replied
    SQL performance using union

    Try the attached code. When you make a query, always eliminate records from outside of the query going inside.
    Code

    Leave a comment:


  • Guest's Avatar
    Guest replied
    SQL performance using union

    I have written a subfile program to display 2 records per page with a subfile size of 10 records. I am using embedded SQL to query a large data file (540K records with 200 columns) which is querying 3 logical files(not join logicals) all using the proper index based on the where condition. The cursor is using a prepared statement and each of the columns are needed for the display. I did not use a left join since there is a strong possibility of null values being returned on 1 or all of the selects. This is running approximately 10 seconds in morning and around 45 secs to 1 minute when system is slow. Any suggestions on this query to improve performance?
    Code

    Leave a comment:


  • M.Savino
    replied
    SQL performance using union

    STRDBG ( you may have to use UPDPROD(*YES) )and then go into Interactive SQL. Paste your Embedded SQL Statement into your session and run it. Then check your Joblog for Messages from the SQL Engine. Sometimes they can be helpful. And have you tried using a Work File to do this? It seems counter-intuitive but if nothing else has worked and you've got the time to try..... Who Knows? (But don't get your hopes up) Also, I think in the examnple below, the work file would contain dupes, whereas your original statement with UNION would not. Mike
    Code

    Leave a comment:


  • Guest's Avatar
    Guest replied
    SQL performance using union

    Joe Pluta wrote: > Otherwise, it seems to me that the system has to build the whole > table internally and then sort it by PlDco and PlPol before giving > you any results. I'd try adding the indices I suggested, but if that > doesn't work, use native code. I also think it has to read in all of the data. How can it optimize for 10 rows when it has to select data from all 3 tables, then sort it to be able to figure out the 10 rows? How many rows are typically returned? If it's a manageable amount, I'd not do the ordering in SQL but in RPG using the sort API. How many times is the program run during the day? If it's more than once an hour, you might consider creating a permanent SQL View and building an Index over it. Bill

    Leave a comment:


  • J.Pluta
    replied
    SQL performance using union

    If I were doing this natively, I would have the following logical views: LF1: (PlSsn, PlDco, PlPol) LF2: (PlHic, PlDco, PlPol) LF3: (PlAdr2, PlCity, PlSt, PlZip5, PlDco, PlPol) Then I would read LF1 with the SSN, LF2 with the HIC and LF3 with the address information. Then I would perform matching record logic on PlDco and PlPol to determine which records to process. Otherwise, it seems to me that the system has to build the whole table internally and then sort it by PlDco and PlPol before giving you any results. I'd try adding the indices I suggested, but if that doesn't work, use native code. Joe

    Leave a comment:


  • Guest's Avatar
    Guest replied
    SQL performance using union

    If it's not possible for the records to have identical values, you could use UNION ALL instead of UNION to avoid the overhead of the "remove duplicates" logic of UNION.

    Leave a comment:


  • dacust
    started a topic SQL performance using union

    SQL performance using union

    You could create 6 EVIs, 1 each over P1Adr2, P1Cty, P1St, P1Zip5, P1Ssn and P1Hic. This might help, and then it might not. And when you say the 3 logicals have the "proper" index based on the where condition, what are those logicals? To help the where clause, you might need a logical built over the same fields listed above, again a single logical for each field. If this is a single physical file, you might be better to do a complex if statement instead of the union. Specifying a logical on the select from doesn't normally get you anything. All it does is guarantee that the optimizer will consider that logical for the query. Unless your wuery is really complicated, or there are hundreds of logicals on the file, the optimizer will find that logical anyway and use it if it's the best way. It doesn' harm anything to specify the logical, but it probably doesn't help either. An if statement that uses parenthesis and and/or logic in a single select, with an EVI for each of the fields in the where clause may well run faster. That'd be what I'd try. -dan
Working...
X