MYSQL Query Performance

[drgarytraub]

I am working on optimizing some queries.

The following MYSQL query takes approximately 4.78 seconds in Navicat BUT 8.82 seconds in Alpha's SQL Query Genie. This speed difference is noticeable in all queries I have checked, particularly noticeable in longer running queries. What could account for this?

Have others noticed this?

SELECT appointments.chargeno, appointments.date, appointments.pracid, appointments.idnumber, appointments.prov, appointments.billprov, appointments.svcode, appointments.fee, appointments.units, appointments.i1exp, appointments.ptexp, appointments.dxcode, appointments.i1insconame, appointments.i2insconame, appointments.i3insconame, appointments.respname, appointments.charge, appointments.progno, appointments.i1bdate, appointments.i2bdate, appointments.i3bdate, appointments.ptbdate, patients.FULLNAME, patients.IDCOMBO, patients.REFERRER, patients.PTTYPE, left(appointments.i1insconame, 6) AS i1name, left(appointments.respname, 6) AS rname, left(patients.fullname, 12) AS fname, left(patients.referrer, 6) AS refname
FROM appointments appointments
INNER JOIN patients patients
ON appointments.idnumber = patients.IDNUMBER
WHERE appointments.charge = 'Y' AND appointments.date BETWEEN '2018-01-01' AND '2018-12-31' AND appointments.pracid = 'somepractice' AND appointments.prov BETWEEN 'aa' AND 'zz'
ORDER BY appointments.date, patients.FULLNAME, appointments.prov

[mikeallenbrown]

Re: MYSQL Query Performance

Maybe it's the difference between portable SQL and native SQL within Alpha. There is a checkbox usually around somewhere to check off that your query is native or not. Under portable SQL there is additional overhead as Alpha needs to evaluate your query before executing it. When it's native there is no overhead. .....at least that is my general understanding.

https://documentation.alphasoftware....able%20SQL.xml

[drgarytraub]

Re: MYSQL Query Performance

I know I have seen that checkbox somewhere but I cannot find it.

Hard to imagine though that this would take an additional 4 seconds - but, if I can find that checkbox I will try it.

Thanks Mike!

[Davidk]

Re: MYSQL Query Performance

If you run the Query in MySQL Workbench, does it run faster than Navicat? I'd guess the speed of the query decreases the closer you get to MySQL.

Workbench will probably be fastest.
Navicat probably has some work to do in order to get to MySQL but I'd bet Navicat is fairly well optimized since talking to databases is their business.
Alpha is probably not that well optimized and probably does a lot more work before and after getting to the database. There's probably a lot more overhead in Alpha.

If you run the query two or three times, are your results the same within Navicat and Alpha?

[drgarytraub]

Re: MYSQL Query Performance

Hi David,

I ran the query several times in each scenario and here are the results:

Workbench: 3 - 3.1"

Navicat: 3.2-3.4"

Alpha SQL Query Genie: 5.5-6"

Same query but within an alpha grid: 7.5-8"

By the way, the result set has 17,411 records and the appointments table has indexes on charge,date,pracid, and prov

[JerryBrightbill]

Re: MYSQL Query Performance

Your test is actually testing much more than query speed. In all instances, the end product is a display of the result set, so the result set must be converted into some format to show the data. This may be a simple grid type display in Navicat or Workbench, or a far more complex display such as a web grid.

Also, when you are testing in Navicat or Workbench, you already have a connection open, so the time to make the connection factors into the total time

As test, I built a query with a complex INNER JOIN that returns just a count of records (1 value). This was tested on a remote data server not on my local network. The query did not have a WHERE clause and was run multiple times to get a repeatable baseline.

SELECT count(*) as kount from .......

Running in Navicat, the query took .062 seconds to return a count of 196,074

Running in xbasic and just timing the query and the code to get the kount using a high accuracy timer took .059 seconds to return 196,074

dim cn as sql::Connection
dim SQL as c
sql = <<%txt%
SELECT count(*) as kount FROM ...
%txt%
dim kounter as n
if cn.open("::name::PEP")
	dim t1 as util::timer
	cn.execute(sql)
	kounter = cn.ResultSet.data("kount")
	t1.stop()
	cn.close()
end if 
ui_msg_box(""+kounter,str(t1.ElapsedMicroseconds*.000001,12,6))

The xbasic method was tested with portable sql on and off (cn.PortableSQLEnabled = .F.) with no measurable difference in speed. Adding a WHERE clause slowed each query almost exactly the same amount of time.

However, if the timer included the time to open and close the connection, the same query in Alpha Anywhere took .291 seconds, indicating significant time was expended just making a connection to the remote server. Again, portable on / off made no difference.

Another test just did SELECT * and the xbasic just "walked" the resultSet to get a count

	while cn.ResultSet.nextRow()
		kounter = kounter + 1
	end while

Navicat ran SELECT * and displayed the data in 16 - 30 seconds
The xbasic ran in 18- 40 seconds to calculate the kount

The test was run many times, and the variation between queries in a single environment was greater than the difference between the fastest runs from each environment.

Another test changed the SELECT * by replacing the * with just 1 column.

Navicat now showed 196,074 rows in 12.8 - 13.5 seconds, and the xbasic walked the result set and returned the same data stream (list of values in the columns) in 14.2 to 14.3 seconds

When you run in a grid web component, you add another element as the grid runs 2 queries. The first gets a count of records, and the second gets the result set for the first number of rows in the grid. It also has to build the HTML to show the rows. In many grids, getting the count has a high overhead, especially with WHERE clauses with multiple conditions.

The conclusions:

• The actual query time is effectively the same in all environments
• Portable SQL on / off has no measurable speed impact
• Connection speed can be a significant factor
• Most of the overall time to display is getting the result set and formatting it into some useful display layout
• The time to format the result set (and run the query) varied based on the number of columns. Returning more columns such as with SELECT(*) was significantly slower.
• Web grid components do 2 queries, which creates a longer total time

All of this will vary considerably from one database to another and one query to another.

[frankbicknell]

Re: MYSQL Query Performance

"By the way, the result set has 17,411 records and the appointments table has indexes on charge,date,pracid, and prov "
You should have indexes on all the fields in the where clause.

[drgarytraub]

Re: MYSQL Query Performance

Jerry,

Thank you very much for the detailed explanation. Makes a lot of sense. BTW, though, would it then make sense to keep the sql connection open, if that is even possible?

Frank,

I do that usually, but in this case, for whatever reason, adding the index on prov actually slowed the navicat time by approx 1 second. Doesnt make sense I know but that is what happened. I will try it again.

[Davidk]

Re: MYSQL Query Performance

Hi David,

I ran the query several times in each scenario and here are the results:

Workbench: 3 - 3.1"

Navicat: 3.2-3.4"

Alpha SQL Query Genie: 5.5-6"

Same query but within an alpha grid: 7.5-8"

By the way, the result set has 17,411 records and the appointments table has indexes on charge,date,pracid, and prov

Those times fit exactly what I was expecting. The Grid is just old tech... so ignoring it's inefficiencies, the other times seem appropriate given that there is overhead within Alpha that you don't have with Workbench and Navicat. Do one more test with XBasic because there you get open the connection and then run the query... so you get to time just the query.

[drgarytraub]

Re: MYSQL Query Performance

Hi David,

I ran the script in an an a5w page. Opening the connection is very quick, much less than a second. The whole process including opening the connection and running the query takes approximately 3 seconds, just like workbench and navicat.

Given these observations, it seems that there is something about the query builder that slows things down (and the grid but I would expect that), and not alpha itself. Performance is actually quite similar. What are your thoughts on this?

[Davidk]

Re: MYSQL Query Performance

Are your times in the query builder the same if you run the same query twice? Is overhead diminished in the query builder with subsequent runs? Without getting Alpha to tell you exactly the query builder is doing, it'd be tough to find out what it's doing under the hood.

Overall, is the query builder something you need to be concerned about?

[drgarytraub]

Re: MYSQL Query Performance

No, not really.

[bob9145]

Re: MYSQL Query Performance

So what would be a expected window of time for:
1)Rendering a list based on a sql query
2)Performing a crud call back, and refreshing the list
My times are as high as 30 seconds. Tried many different connections doesn't significantly change this. My queries are joins, but I am only testing with a few hundred records.
Anything over 10 seconds is not practical in the mobile world. Anyone conquer this?

[drgarytraub]

Re: MYSQL Query Performance

That would depend on the actual query, number of fields, indexes, and then what else is going on in the grid or ux.

I have found that I can get performance time down to approx 3 or 4 seconds max (from complex queries with lots of data), and usually less

[bob9145]

Re: MYSQL Query Performance

A way to monitor the break down of how much time each task/event consumes would be handy, to just say it depends on this or that leaves only trial and (mostly) error. Maybe ultimately the answer is a further injection of Node into the mix?