Tweet
Just because an index exists, that does not mean its being used. Often times, the SQL engine ends up doing a table scan anyway, which may be the case here since it takes the same amount of time to run no matter what date you enter. Try using the visual explain tool inside iSeries navigator to evaluate the statement and see how it is being performed. I believe that STRSQL maybe returning results quicker, because it might be displaying the first screen of data before the entire result set is built. (that is just a guess though)
-
-
SQL Performance
It's just a small note, but I always use select count(*) when I'm comparing retrieval speeds since it doesn't give you the numbers till it's completely finished. -
SQL Performance
I have the following SQL code in a program. The file it goes against is rather large with almost 10 million records of 206 bytes each. There is also in index using the G1trn@ date and the G1Grade1 fields. The curious thing is the length of time it takes to process as embedded vs what it takes interactive under STRSQL. Regardless of the amount of days I select (60 or back to 9/27/2002) the response in STRSQL is almost instantaneous while it takes up to 28 seconds in the RPG program. I can rerun the program over and over without changing the selection criteria and it never gets any better. What's wrong??? Bill
CodeComment
-
SQL Performance
I used navigator and built the index based on what it said. As to returning a whole set. In one instance I am only retrieving 60 days worth of data (max records would be 300). On STRSQL as soon as the first screen of data shows I go to the bottom and that is instantaneous also. We can have a maximum of 5 records per store per day. That would be if all our stores sold 5 grades of fuel. The majority sell 3 with many only 2. I seems to have something to do with writing to a subfile but I wouldn't swear to it. I may wind up calling IBM and see what they have to say. Thanks for the responses. BillComment
Comment