Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Speeding up a dataflex application over the Web

  1. #11
    Join Date
    Feb 2009
    Posts
    5,468

    Default Re: Speeding up a dataflex application over the Web

    Hi Sture

    No, I didn't know. What happens if you don't?
    It doesn't work. It just the way ODBC works. When you construct your SELECT statement you have to make sure all of your text felds are at the end eg

    Code:
    select col1,col2,col3,memo1,memo2 where x=y
    Otherwise you will get errors when you try to use SQLColumnValue/SQLGetData

    I find it hard to accept that the record fetching should be slow.
    All I can say is do your own indepedant test and compare it with SSMS etc ....

    I would also compare it with a 'traditional' VDF style find GT loop and play with the block size

    Have you found no way of controlling the cache size when fetching from the result set of a ESQL call?
    I dont think BLOCK_SIZE is appropriate for ESQL in the sense that you call SQLExecute, the SQL server does it's stuff and returns a result set ready for you to fetch. You are then in control via SQLFetch on how much or little of that data you 'collect'

    I think BLOCK_SIZE is used so that when executing traditional VDF find loops the CK contstructs an SQL Query to find the data you are looking for and it uses BLOCK_SIZE to limit how much of that data is collected in the first instance and then if you are looking for the next,next,next record it looks in that cache before getting the next block of 10 etc

    This makes the whole CK process more efficent as it is ultimately having to execute less individual queries to get at the data you want. In the case of ESQL you (the programmer) are in charge of this process

    As a side note if you have not already seen them you might find these threads interesting

    http://support.dataaccess.com/Forums...light=slow+daw

    http://support.dataaccess.com/Forums...ght=sqlgetdata
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

  2. #12
    Join Date
    Feb 2009
    Location
    Goteborg, Sweden
    Posts
    3,189

    Default Re: Speeding up a dataflex application over the Web

    In addition to what Focus said about testing - make sure you do not run any of your tests though the debugger. I wrote about some ESQL tests I did some time ago where I was totally fooled by the debugger - when speed testing. Altough I am not able to find that thread.
    Nils Svedmyr
    RDC Tools International
    www.rdctools.com

    "The problem with internet quotes is that you don't know if they are true."
    Abraham Lincoln

  3. #13
    Join Date
    Feb 2009
    Location
    Copenhagen, Denmark
    Posts
    2,006

    Default Re: Speeding up a dataflex application over the Web

    Hi Nils,

    That's right. We have seen that also.

    -Sture

  4. #14
    Join Date
    Feb 2009
    Location
    Copenhagen, Denmark
    Posts
    2,006

    Default Re: Speeding up a dataflex application over the Web

    Thank you, F. I have read the threads and gotten wiser.

    About the slow fetching of rows also mentioned in the thread you referenced (speed revisited). I guess it seems like some fetching buffer is too small. Have you done any of that monitoring to find out how that part works communication wise?

    I think you're right about BLOCK_SIZE. But in the case of ESQL the driver for once knows what the next instruction will be and can therefore fetch a large set of records at a time. And it was the "largeness" of these chuncks I expected to be able to set. But no, so far.

    -Sture

  5. #15
    Join Date
    Feb 2009
    Posts
    5,468

    Default Re: Speeding up a dataflex application over the Web

    Hi Sture

    I've not found anything either ... but then I've not really looked

    When MM reduced the amount of structure checking made as part of each call things improved from 10 times slower to 4 times slower which brought it within the region of "OK" for what we were doing at the time

    The Find GT thread hopefully will give you some ideas on how this could be made more efficent in your own version at maybe the expense of backward compatibility

    I fully appreciate the CK has to be a jack of all trades and provide predictable behaviour with the existing codebase but when you are looking for things that are on the whole not there (eg checking for occasional duplicates in an operation) then there is a dramtic hit cause by the find GT having to find 'something' for reasons of backward compatibility etc

    I look forward to your presentation ... and maybe a beer !
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

  6. #16
    Join Date
    Feb 2009
    Location
    Copenhagen, Denmark
    Posts
    2,006

    Default Re: Speeding up a dataflex application over the Web

    Hi Focus,

    In the table below you can see the time for fetching a number of rows (limited by use of the "TOP" clause) from a remote server. All is measured in ms. The row size is 191 bytes.

    As you can see the fetch-time per row seems constant.

    Code:
    Number of records, SQL Execute, SQL Fetch, Time per row
                    1          31        16           16
                    2          16         0            0
                    4          15         0            0
                    8           0        16            2
                   16          16         0            0
                   32          16        15      0,46875
                   64          15        16         0,25
                  128          32        31    0,2421875
                  256          31        62    0,2421875
                  512          62       110   0,21484375
                 1024         125       203   0,19824218
                 2048         219       422   0,20605468
                 4096         438       828   0,20214843
                 8192         843      1657    0,2022705
                16384        1672      3313   0,20220947
                32768        3328      6625   0,20217895
                65536        6641     13266   0,20242309
               131072       13281     26703   0,20372772
               262144       26719     53141   0,20271682
               524288       53141    106937   0,20396614
    I'll continue to look for slowness in other places.

    -Sture
    Last edited by Sture; 18-Apr-2012 at 09:47 AM.

  7. #17
    Join Date
    Feb 2009
    Posts
    5,468

    Default Re: Speeding up a dataflex application over the Web

    Hi Sture

    Very interesting but not 100% sure what you are showing here ?

    I think I said that ESQL fectch times were linear (as you have found) but were significantly slower than other tools/languages againsty the SAME database

    One test i've never done is if you take your say 64k rows test and write a traditional while/find GT loop how does that compare with your test above ? and does the block_size setting have much of an impact ?

    Also if you execute your 64k rows SQL statement directly in the SSMS I suspect it will execute and fill it's grid in a total of about 5 seconds as opposed to almost 20

    Cheers
    Success consists of going from failure to failure without loss of enthusiasm - Winston Churchill

Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •