Tim

Making some mods to an app I did a bit of testing in EDBManager to see what speeds would be like. I found a major difference in the the time to prepare a query.

SELECT '>' + _Forename+ ' ' + _Surname + '<'+CAST(_ID AS VARCHAR) AS Pinner
FROM Contacts
WHERE
_ID IN (1021184,1002496,1021274,1021467)
ORDER BY _Surname

vs

SELECT '>' + _Name + '<'+CAST(_ID AS VARCHAR) AS Pinner
FROM Companies
WHERE
_ID IN
(
1001685,1001686,1001690,1001695,1001696,1001699,1001711,1001720,1001724,1001726,1001728,
1001735,1001746,1001749,1001762,1001772,1001776,1001780,1001783,1001784,1001785,1001786,
1001787,1001792,1001794,1001809,1001815,1001822,1001829,1001832,1001836,1001837,1001838,
1001839,1001845,1001848,1001851,1001860,1001865,1001866,1001874,1001875,1001877,1001881,
1001884,1001885,1001887,1001889,1001891,1001894,1001896,1001902,1001914,1001930,1001937,
1001938,1001939,1001942,1001943,1001945,1001950,1001962,1001963,1001965,1001969,1001979,
1001981,1001985,1001990,1001997,1002003,1002004,1002006,1002009,1002012,1002018,1002020,1002033)
ORDER BY _Name

Manually counting seconds the first one takes c5 secs to prepare and the other less than a second. This is over the LAN. Locally both are subsecond

Both are only referring to one table. I tried taking a column out of the first one but that makes no difference, tried taking the ORDER BY clause out - again no difference. I'd love to know what can affect this.

This may also have some bearing on the thread "Performance in client/server, particularly Prepare"

Roy Lambert

Roy,

Did you reboot between your timings? Otherwise the first test may have cached data locally so the 2nd test appeared to run faster.

Barry

Barry

>Did you reboot between your timings? Otherwise the first test may have cached data locally so the 2nd test appeared to run faster.

Nope but it shouldn't have made any difference since we're talking two different tables. What I did do was run test 1 then test 2 then test 2 then test 1 and got the same result each time.

Roy

Roy,

<< Manually counting seconds the first one takes c5 secs to prepare and the
other less than a second. This is over the LAN. Locally both are subsecond
>>

I think you answered your own question.  EDB can't be held accountable for
performance over a LAN - there's just too many variables at play, especially
in terms of the time it takes to break oplocks with SMB.

<< This may also have some bearing on the thread "Performance in
client/server, particularly Prepare" >>

Are you using the ElevateDB Server, or a direct local session ?  If the
latter, then one has nothing to do with the other.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Manually counting seconds the first one takes c5 secs to prepare and the
>other less than a second. This is over the LAN. Locally both are subsecond
> >>
>
>I think you answered your own question. EDB can't be held accountable for
>performance over a LAN - there's just too many variables at play, especially
>in terms of the time it takes to break oplocks with SMB.

That sounds fair enough but I'm not fully convinced I can't get my head around why it did with such a variance so consistently.

Just to confirm. My understanding is that preparing consists of:

Check syntax of query
Open catalog and verify existence of tables
Obtain handle to table(s)
Open catalog and verify existence of columns

Have I missed anything out?

I also have the memory that having a table already open elsewhere in the app will speed the preparation up. Is that right?

><< This may also have some bearing on the thread "Performance in
>client/server, particularly Prepare" >>
>
>Are you using the ElevateDB Server, or a direct local session ? If the
>latter, then one has nothing to do with the other.

Local

Roy

Tim


I'm less convinced now

Being an awkward cuss and liking to understand what's happening I decided to do an experiment.

First I created a micro app. It just prepared and unprepared the two queries I initially posted. I tried as follows:

Local on my development machine
Filesharing using a small notebook:
Wireless connection
    single user
    multi user
Wired connection
    single user
    multi user

I used a timer set to a random interval (up to 3 minutes) and randomly picked which to prepare. In ALL cases the query involving the Contacts table took 10x longer or more than the one involving the Companies table.

I then changed the queries to a simple SELECT * FROM and reran the rests - same results.

Next I altered the app to grab a table list from the catalog loading it into a stringlist and alter the sql for the query randomly using a table name from the stringlist. The sql generated is SELECT * FROM + table name. All I do is prepare the query.

On the single user test it looks as though there are two "rogue" tables - Contacts & Career - both take a LOT longer than the others to prepare (I'm talking >1500 ticks vs <100 ticks).

I'm running this latest test overnight so I can get some decent timings to investigate - now watch it randomly pick the same table every time


Roy Lambert

Tim


Running overnight so I get around 30 prepares for all tables in the database it seems that there are three rogues:

Contacts
Career
Sites

Each of those are averaging > 2000 ticks to prepare. The remainder are all averaging < 200 (with the odd spike now and then naturally)

This is consistent in both single and multi user mode.

I used a wireless connection to give a slower network speed and make the figures bigger. With a wired connection the numbers drop but the ratio is still the same.

Next stage is to find a window to extract the data, reverse engineer the database, reinstall the data and see if that sorts it out.

Roy Lambert

Try Table repair & optimise Roy?

I haven't had any experience of prepare-times varying in the way you say. It might be something to do with structures on disk?

Adam

>Try Table repair & optimise Roy?

Suggested far to late

>I haven't had any experience of prepare-times varying in the way you say. It might be something to do with structures on disk?

I would have said I hadn't until this. Almost all of my testing is done with local access and I can't spot the difference between 10 ticks and 100 ticks counting manually. It was pure "luck" I decided to alter a bit of the system and wanted to find out what the query run time would be over the LAN. Even then, if I hadn't had a bug in the generated SQL originally I wouldn't have tested it in EDBManager and would have shrugged the difference off as the LAN.

This catalog dates way back (v1.x ElevateDB) so it could be something in there which is why I want to try dumping it and trying a reverse engineered one but I need a suitable window of opportunity.

Roy

Roy,

<< That sounds fair enough but I'm not fully convinced I can't get my
head around why it did with such a variance so consistently. >>

Go check out Process Monitor from MS (formerly SysInternals):

http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

and monitor all I/O in your application.  That will convince you of where
the issue is - the network.

<< Just to confirm. My understanding is that preparing consists of: >>

Check syntax of query -> All object references are bound here (existence of
tables, columns, etc.), it's all one step
Open catalog and verify existence of tables -> No, catalog is opened when
the database is opened
Obtain handle to table(s)
Open catalog and verify existence of columns -> No, for same reason as
above

<< I also have the memory that having a table already open elsewhere in the
app will speed the preparation up. Is that right? >>

Yes.  EDB won't open the same physical table twice.

Tim Young
Elevate Software
www.elevatesoft.com