creating a new index and yes/no index

rocketman

Quick Question - If I tick the indexed box on an attribute in an already live system, will the index be built automatically when I publish (DB is MySQL) or will I have to build it using a MySQL tool.

Second Question, Why can't I index a yes/no attribute. (A lot of my reports check this flag)

nlarson

Can't speak to #1, but there is typically there is no value to indexing a Boolean field, it has no meaningful cardinality (max of 2 searchable values: yes/no). A single index might make sense to aggregate many seperate Boolean attributes, but that's not really the same thing. So, as a one off there is no performance gain, in fact it would negatively impact write performance (might not be noticeable, but it's there none the less).

rocketman

Yes thanks,

Might have to transfer all my dead, dying, lapsed and resigned members to a duplicate BO to improve performance in my app.

The problem is - from a table of around 600, the selection is always around 120 (active members) which in the mobile app - where we don't have the luxury of downloading dynamically as the user types on referenced attributes, downloading these (120 x 2 - intructor and trainee) 240 items of Surname and Firstname is taking around 10-15 sconds on a 2Mb broadband link.

This is proving unacceptable to the users since, if they click in any of the editable fields, the download is interrupted and you can't restart it again.

As it happens, I've redesigned this part of the system, but question 1 was for possible future use in other reports where the performance is a bit sluggish

nlarson

Sadly all mobile platforms seem to 'hate' dynamic selections. If I had to guess the delay is in delivering the data, not running the query so moving inactive members out may not help (unless they are included in the data return)

That said, 240 txt names should not be very much data, a few K I would think. so you might want to check the Query execution time on your database and if that looks good then get a copy of Fiddler (freeware) to test the return simulating a narrow pipe.

Fiddler can also tell you exactly how big the package is and what it contains. I find it very helpful troubleshooting performance issues. It seems unlikely but perhaps the query is returning more than is being displayed and the data being retrieved can be slimmed down.

rocketman

sounds like a good tip. I'll check fiddler out and let you know how I get on

Thanks

rocketman

WOW .... My 120 surnames and firstnames (avg 25 chars per row) turned into an obcene 74K with all the padding, css formatting etc. 7 seconds per drop down

Nice tool that Fiddler (but at the same time frightening)

The raw MySQL search returned the rows in 0.025 seconds

rocketman

Hi NLarsen,

Seems searching on the boolean is killing my app. If I'm reading Fiddler correctly, the delay is in the server responding. Once the download starts it's quite quick - and to be fair, the other fields which use an indexed search complete in about 1 second.

Here's my output - would be grateful if you could cast an expert eye over them and comment on my diagnosis

ACTUAL PERFORMANCE

ClientConnected: 08:55:32.932
ClientBeginRequest: 08:55:32.932
GotRequestHeaders: 08:55:32.932
ClientDoneRequest: 08:55:32.932
Determine Gateway: 0ms
DNS Lookup: 0ms
TCP/IP Connect: 41ms
HTTPS Handshake: 0ms
ServerConnected: 08:55:32.979
FiddlerBeginRequest: 08:55:32.979
ServerGotRequest: 08:55:32.979
ServerBeginResponse: 08:55:42.683
GotResponseHeaders: 08:55:42.683
ServerDoneResponse: 08:55:43.060
ClientBeginResponse: 08:55:43.060
ClientDoneResponse: 08:55:43.060

Overall Elapsed:	00:00:10.1279285

And here's one of the indexed searches

ACTUAL PERFORMANCE

ClientConnected: 08:55:32.948
ClientBeginRequest: 08:55:32.948
GotRequestHeaders: 08:55:32.948
ClientDoneRequest: 08:55:32.948
Determine Gateway: 0ms
DNS Lookup: 0ms
TCP/IP Connect: 40ms
HTTPS Handshake: 0ms
ServerConnected: 08:55:32.995
FiddlerBeginRequest: 08:55:32.995
ServerGotRequest: 08:55:32.995
ServerBeginResponse: 08:55:34.324
GotResponseHeaders: 08:55:34.324
ServerDoneResponse: 08:55:34.355
ClientBeginResponse: 08:55:34.355
ClientDoneResponse: 08:55:34.355

Overall Elapsed:	00:00:01.4072265

nlarson

based on the info in your previous post this is not an indexing issue. 1/40 of a second is the query process time, so no amount of indexing will fix that. If you are confident in that post you can rule out the Database tier entirely.

So the big question is.... what is happening during that ₉ seconds from when SQL returns the row to the render on the client. I can think of a few likely culprits. If Secha Touch works like EXTJS then my money is on rendering the payload for delivery, or possible but less liely the actual rendering on the client.

The easiest way for for me to rule some out would be to get more data. If you are comfortable sharing the .saz Fiddler session, then PM me and I will give you my email address.

nlarson

I almost forgot. What is the payload size on the second trace? is it the same?

rocketman

Payload was about 15k so calculating that up to 75k the time would be about the same.

Looking at the fiddler files would be a academic excercise since, thanks to using up my remaining supplies of midnight oil, the app is now faster than the standard version, sticking to a few basic rules.

For the benefit of others, here are my self imposed rules.

Don't use forms which use referenced attributes as drop downs unless you know the list will be quite small <20
Don't have more than 1 such ref Att per form
Use lots of forms with lesser amounts of info/data and use the menu structure to break down the tasks you need to do into smaller bite sized chunks.
If you really do need to "see" the whole picture, try using a form with a single HTML attribute and lay your stuff out in that
Use lists with menu items to the side/top/bottom so you can more easily break the tasks into smaller chunks

Results of my last midnight oil burning session here http://www.youtube.com/watch?v=aK0iovZ9r8c&feature=youtube_gdata_player