Hi,
I've searched all over for this and not found that much useful
information for what I think to be a fairly obvious issue.
I am having to do some benchmark tests on a SELECT statement and
whether it will run faster as a massive JOIN or as a series of smaller
SELECTs
i.e.
SELECT *
FROM tbl_1
LEFT OUTER JOIN tbl_2 ON tbl_1.ID=tbl_2.ID
LEFT OUTER JOIN tbl_3 ON tbl_1.ID=tbl_3.ID
LEFT OUTER JOIN tbl_4 ON tbl_1.ID=tbl_4.ID
LEFT OUTER JOIN tbl_5 ON tbl_1.ID=tbl_5.ID
WHERE tbl_1.ID IN (1,2,3,4,5...)
or
SELECT * FROM tbl_1 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_2 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_3 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_4 WHERE ID IN (1,2,3,4,5...)
SELECT * FROM tbl_5 WHERE ID IN (1,2,3,4,5...)
I've written a script that performs one or the other of the statements,
logs the time and outputs the results.
The problem is that the more you run the tests the faster they get
I've tried using FLUSH TABLES and setting the query cache to 0 but
nothing seems to work, the query times keep dropping.
Does any one have any advice?
TIA
Sam 3 1498
cedrik wrote: Hi,
I've searched all over for this and not found that much useful information for what I think to be a fairly obvious issue.
I am having to do some benchmark tests on a SELECT statement and whether it will run faster as a massive JOIN or as a series of smaller SELECTs
i.e. SELECT * FROM tbl_1 LEFT OUTER JOIN tbl_2 ON tbl_1.ID=tbl_2.ID LEFT OUTER JOIN tbl_3 ON tbl_1.ID=tbl_3.ID LEFT OUTER JOIN tbl_4 ON tbl_1.ID=tbl_4.ID LEFT OUTER JOIN tbl_5 ON tbl_1.ID=tbl_5.ID WHERE tbl_1.ID IN (1,2,3,4,5...)
or
SELECT * FROM tbl_1 WHERE ID IN (1,2,3,4,5...) SELECT * FROM tbl_2 WHERE ID IN (1,2,3,4,5...) SELECT * FROM tbl_3 WHERE ID IN (1,2,3,4,5...) SELECT * FROM tbl_4 WHERE ID IN (1,2,3,4,5...) SELECT * FROM tbl_5 WHERE ID IN (1,2,3,4,5...)
I've written a script that performs one or the other of the statements, logs the time and outputs the results.
The problem is that the more you run the tests the faster they get
I've tried using FLUSH TABLES and setting the query cache to 0 but nothing seems to work, the query times keep dropping.
Does any one have any advice?
TIA
Sam
Sam
Try repeatedly running one of the tests until there is no
further improvement in the time. Then repeatedly run the other
test until there is no improvement in time.
You will then know the optimum performance that you can expect
for the hardware, os, and database configuration you are using.
If any of these change you will have to repeat the procedure
since your results may change from release to release.
The catch is that in a production environment you will probably
see less than optimum results. To compensate you may find it
more realistic to take the initial test time of each of your
alternatives.
HTH
Jerry
Hi,
there might be some other buffers enables, e.g. the key_buffer, which stores
the indexes in memory, or the join buffer or sort buffer a.s.o. This makes
it plausible that the second run will be faster than the first run, but
afterwards it shouldn't get any faster anymore.
However, the 2 queries (if we count the set of queries in the second example
as one) are not absolutely equivalent, so it would of course depend on, what
you really want to do. If the first query is what you want to get, and the
indexes are set correctly (probably primary keys on the ID column), the
first query will probably be the better choice (less roundtrips to the
server, and assuming that indexing is right, it shouldn't (or should hardly)
require more reads than the second example). If you also consider, that the
second query (the set of queries) will probably require some additional work
on the client side, I believe that the first query will give you better
performence.
Markus
Thank you for both your suggestions.
You are right, the JOIN should be the faster but so far doesn't
actually seem to be. In terms of client side work both are equivalent.
The search itself is for a bookstore so all the joins are on the
author, subject, language, etc. tables, each one is liekly to bring
back a couple of rows per book from the primary table. Once the
information is retrieved storing it in arrays and displaying are
virtually identical.
I am getting results from the live server in the region of 5 - 10
seconds for the JOIN but am unable to replicate this under test
conditions (running on the same server I hasten to add).
It seems (after running several thousand iterations in a number of
orders) that the only reliable way I can get any results that are
slightly above average is to wait a couple of hours between tests (this
isn't realistic).
Oh well, the search continues.
Sam This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: lkrubner |
last post by:
Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR,
INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql?
PostGre info would also be useful.
I'm trying to...
|
by: Bart Nessux |
last post by:
Just fooling around this weekend. Wrote and timed programs in C, Perl and
Python. Each Program counts to 1,000,000 and prints each number to the
console as it counts. I was a bit surprised. I'm not...
|
by: Shailesh Humbad |
last post by:
I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors...
|
by: Shailesh Humbad |
last post by:
I posted this article I wrote a couple weeks ago, but
I've completely updated it, with new sections on
the performance of 'bursting' GetRows and GetString.
If you're interested:
...
|
by: trint |
last post by:
Ok,
This script is something I wrote for bringing up a report in reporting
services and it is really slow...Is their any problems with it or is
their better syntax to speed it up and still provide...
|
by: Mike Nau |
last post by:
We currently have all of our schema and testdata laid out in a large
set of sql scripts.
It currently takes about 15 minutes to run the scripts on a Dual
1.7ghz box with 1gb of ram.
Does...
|
by: ColinWard |
last post by:
Hi there.
I have the following code in the open event of a form. Most of it sets
or checks the value of certain variables so that the form opens
properly. It also sets the recordsource for the...
|
by: jphelan |
last post by:
Ever since I successfully applied some techniques for increasing the
speed of my 17 meg. Application; it has only made me hunger for more.
First, let me list what I have done so far:
1. Split...
|
by: SQL Learner |
last post by:
Hi all,
I have an Access db with two large tables - 3,100,000 (tblA) and 7,000
(tblB) records. I created a select query using Inner Join by partial
matching two fields (X from tblA and Y from...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |