visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Availability checker
By: desperado101 U.S.A.  Date: 12/05/2003 00:00:00  English  Points: 400 Status: Answered
Quality : Excellent
Hi,

I am trying to build an online reservation sytem but i'm struggling with a query.

My database contains a table called Bookings which amongst others, has fields RoomID, ArrivalDate and DepartureDate.

The query I want to create will, given a month and a roomid, return a list of all days that the room is booked, ideally as an array.

As a novice to SQL I have no idea how or if this can be acheived.

Many thanks,

Paul.
By: VGR Date: 12/05/2003 06:55:00 English  Type : Comment
pretty difficult in pure SQL with only that information available 8-)

but easily done with a frontend script/program. Don't you have access to something like PHP ?

Given you offer 400 points for this, I think you understood it was difficult, if not impossible.

People will try to help you, but there is no easy solution. I could give you queries that :
-check that on the given month and for that roomid, there are days the room is booked
-builds temporary data per month listing days it's booked, and then given your month, displays results,

but none uses only one SQL query, so I think humbly that there is no solution in one query using standard SQL.

Now let's wait until the cryptic guys come in to hear a different bell sound. Psadac, are you listening ?
By: TheImagineer Date: 12/05/2003 06:59:00 English  Type : Comment
select * from Bookings
where MONTH(ArrivalDate) = month
and MONTH(DepartureDate) = month
and roomID = id

this will select all rooms that have the month you choose in the arrival or departure dates.
Note that Arrival and Departure must be type Date or DateTime
By: VGR Date: 12/05/2003 07:13:00 English  Type : Comment
as I had said, this doesn't "return a list of all days that the room is booked"

good try, though
By: VGR Date: 12/05/2003 07:14:00 English  Type : Comment
please note you implemented my first dash-ed evokation 8-)

I knew it was useless to tell people "don't even try, there's no solution in one query", but anyway :D
By: desperado101 Date: 12/05/2003 07:17:00 English  Type : Comment
If i use SQL to get the date arrive/depart pairs how could i use php to get this into a list of days?
By: desperado101 Date: 12/05/2003 07:17:00 English  Type : Comment
thanks for your help so far guys - appreciated.
By: VGR Date: 12/05/2003 07:29:00 English  Type : Comment
it's a lot simplier in PHP because we may analyse the data returned and compute exactly what you want. More easily than with pure SQL and tricks 'n' traps

let's do it together :
Input : My database contains a table called Bookings which amongst others, has fields RoomID, ArrivalDate and DepartureDate.

Output : given a month and a roomid, return a list of all days that the room is booked, ideally as an array.

Suggested Algorithm :
-take the $theroomID and $themonth (the arguments)
-query the DB for all rows where MONTH(ArrivalDate)<=$themonth and MONTH(DepartureDate)>=$themonth;
-thus we now have either :
-nothing (0 rows) : the room $theroomID is free in month $themonth
-more than 0 rows : compute $a=Max(ArrivalDate,"$themonth-01") [the first day of month $themonth] and $b=Min(DepartureDate,month ($themonth+1)'s first day - INTERVAL 1 DAY)
-simply for $i from $a to $b display "room roomID is booked on day $i"

I think that's it and that's the most efficient way
By: desperado101 Date: 12/05/2003 07:38:00 English  Type : Comment
Ok - still a little confused...

Why is the comparison less than or equal to MONTH(ArrivalDate)<=$themonth

I figure what I need to do is get all bookings back when either the arrival/departure date is in $themonth.

if both arrival and departure date are in the month, calculate all days inbetween and add to array.

If end date only is in month add all days to beginning of the month into the array.

If arrive date only is in month add all days to end of the month into the array.

what do you think?
By: VGR Date: 12/05/2003 07:48:00 English  Type : Comment
it's exactly what I'll arrive to also 8-)))))

look at the Min() and Max()

I thought a bit and came up with this test. I think it works. I'll demonstrate it. Give me 5 minutes
By: VGR Date: 12/05/2003 07:55:00 English  Type : Comment
I'm right. The test is really :
where RoomID=$theroomid AND MONTH(ArrivalDate)<=$themonth and MONTH(DepartureDate)>=$themonth;

un dessin vaut mieux qu'un long discours :
By: VGR Date: 12/05/2003 07:57:00 English  Type : Comment
now consider $a=Max(ArrivalDate,beginningofmonth) and $b=Min(DepartureDate,endofmonth)

it works ;-)
By: VGR Date: 12/05/2003 08:00:00 English  Type : Comment
going to sleep. I think you've all the elements to build the solution. Bye-bye
By: volking Date: 12/05/2003 14:32:00 English  Type : Comment
This is a brute force method. Although this works, it is not elegant and will be slow as an old hound dog!

Givens:

Table named: Bookings
Contents:
RoomID ArrivalDate DepartureDate
104 2003-01-01 2003-01-04
104 2003-01-04 2003-01-10
104 2003-01-13 2003-01-14
104 2003-01-16 2003-01-18

CREATE PROC CalcMonthBookings(@FirstDayOfMonth DATETIME, @RoomID INT) AS
DECLARE @TargetMonth INT, @TargetDate DATETIME
SET @TargetMonth=MONTH(@FirstDayOfMonth)
CREATE TABLE #t(RsvDate DATETIME PRIMARY KEY, IsRsvd BIT DEFAULT 0)
WHILE MONTH(@FirstDayOfMonth)=@TargetMonth
BEGIN
INSERT INTO #t VALUES (@FirstDayOfMonth, 0)
SET @FirstDayOfMonth=DATEADD(day, 1, @FirstDayOfMonth)
END
DECLARE MyCsr CURSOR FOR SELECT ArrivalDate, DepartureDate FROM Bookings WHERE RoomID=@RoomID
DECLARE @ArrivalDate DATETIME, @DepartureDate DATETIME
OPEN MyCsr
FETCH NEXT FROM MyCsr INTO @ArrivalDate, @DepartureDate
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @TargetDate=@ArrivalDate
WHILE @TargetDate<=@DepartureDate AND MONTH(@TargetDate)=@TargetMonth
BEGIN
UPDATE #t SET IsRsvd=1 WHERE RsvDate=@TargetDate
SET @TargetDate=DATEADD(day, 1, @TargetDate)
END
END
FETCH NEXT FROM MyCsr INTO @ArrivalDate,@DepartureDate
END
SELECT * FROM #t
DROP TABLE #t
RETURN

Calling Syntax:
DECLARE @MonthDayOne DATETIME
SET @MonthDayOne = CAST('01/01/2003' as DATETIME)
Exec CalcMonthBookings @MonthDayOne, 104

Results:
2003-01-01 1
2003-01-02 1
2003-01-03 1
2003-01-04 1
2003-01-05 1
2003-01-06 1
2003-01-07 1
2003-01-08 1
2003-01-09 1
2003-01-10 1
2003-01-11 0
2003-01-12 0
2003-01-13 1
2003-01-14 1
2003-01-15 0
2003-01-16 1
2003-01-17 1
2003-01-18 1
2003-01-19 0
2003-01-20 0
2003-01-21 0
2003-01-22 0
2003-01-23 0
2003-01-24 0
2003-01-25 0
2003-01-26 0
2003-01-27 0
2003-01-28 0
2003-01-29 0
2003-01-30 0
2003-01-31 0

Enjoy
Frederick Volking
By: volking Date: 12/05/2003 14:59:00 English  Type : Comment
hmmmmm ... the solution I posted above was tested in SQL2000 Enterprise. I just realized you're using MySQL. Does MySQL allow temporary tables? Stored Procs? Hope it works for you.

Frederick Volking
By: VGR Date: 12/05/2003 16:19:00 English  Type : Comment
MySql uses extensively TEMPORARY tables and yes, allows the user to create some explicitly (until connection is closed)

MySql doesn't have stored procedures and I strongly hope it'll never have. It's a lot faster (at least faster to debug & develop) the "normal way". Stored procs are good for DB2 or ORACLME, slow cumbersome rodent databases, not for MySql-the-fast-and-Furious
By: desperado101 Date: 12/05/2003 16:22:00 English  Type : Comment
Hi VGR thanks for your help on this - I will go through this tonight when I get home from work, i'm new to PHP aswell so I need to do a little reading - once I'm done assuming everything works as expected, points will be coming your way.

Thanks again,

Paul.
By: TroyK Date: 13/05/2003 03:09:00 English  Type : Comment
desperado101;

In another thread, volking asked if I could propose a set-based solution as an alternative to his loop-and-cursor solution. Here it is:

---- Solution procedure ----
CREATE PROC GetMonthlyBookingsByRoomID(@FirstDayOfMonth datetime, @RoomID int)
AS
SELECT DISTINCT c.CalendarDate 'Room Is Booked'
FROM auxCalendar c
INNER JOIN Bookings b
ON c.CalendarDate BETWEEN b.ArrivalDate AND b.DepartureDate
WHERE b.RoomID = @RoomID
AND DATEPART(month, c.CalendarDate) = DATEPART(month, @FirstDayOfMonth)
AND DATEPART(year, c.CalendarDate) = DATEPART(year, @FirstDayOfMonth)
GO
----- End of Solution procedure ------

This solution requires the use of an auxiliary "Calendar" table (a common tool for performing date-range queries).

Here is how you build this auxiliary table (using another auxiliary table "Sequence", which contains the numbers 0 to 9999 in this example):

------ Begin SQL to build auxiliary table(s) --------
--SET UP Auxiliary table (auxSequence) of numbers 0-9999
CREATE VIEW Digits
AS
SELECT 0 'Digit' UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
GO

CREATE TABLE auxSequence(Nbr int NOT NULL PRIMARY KEY)
GO

INSERT INTO auxSequence
SELECT dThous.Digit * 1000 + dHund.Digit * 100 + dTens.Digit * 10 + dOnes.Digit
FROM Digits dThous
CROSS JOIN Digits dHund
CROSS JOIN Digits dTens
CROSS JOIN Digits dOnes
GO

CREATE TABLE auxCalendar(
CalendarDate datetime NOT NULL PRIMARY KEY
-- Could include other attributes such as Weekend Day, Holiday, Fiscal Year End, etc.
)
GO

-- Populate auxiliary calendar table with years 2000 through 2005
INSERT INTO auxCalendar
SELECT DATEADD(day, Nbr, '20000101')
FROM auxSequence
WHERE DATEADD(day, Nbr, '20000101') < CAST('20060101' AS datetime)
GO
------ End SQL to build auxiliary table(s) --------

Here's the proposed solution in the context of a SQL script. I'm using T-SQL on SQL Server 2000, but I think you should be able to port this to MySQL pretty easily.

------ BEGIN SQL ------
CREATE TABLE Bookings(
RoomID int NOT NULL,
ArrivalDate datetime NOT NULL,
DepartureDate datetime NOT NULL,
CONSTRAINT ck_Booking_Dates
CHECK(DepartureDate > ArrivalDate)
)
GO

INSERT INTO Bookings
SELECT 104, '2003-01-01', '2003-01-04' UNION ALL
SELECT 104, '2003-01-04', '2003-01-10' UNION ALL
SELECT 104, '2003-01-13', '2003-01-14' UNION ALL
SELECT 104, '2003-01-16', '2003-01-18' UNION ALL
SELECT 104, '2003-02-01', '2003-02-04'
GO

CREATE PROC GetMonthlyBookingsByRoomID(@FirstDayOfMonth datetime, @RoomID int)
AS
SELECT DISTINCT c.CalendarDate 'Room Is Booked'
FROM auxCalendar c
INNER JOIN Bookings b
ON c.CalendarDate BETWEEN b.ArrivalDate AND b.DepartureDate
WHERE b.RoomID = @RoomID
AND DATEPART(month, c.CalendarDate) = DATEPART(month, @FirstDayOfMonth)
AND DATEPART(year, c.CalendarDate) = DATEPART(year, @FirstDayOfMonth)
GO

--Calling Syntax:
DECLARE @MonthDayOne DATETIME
SET @MonthDayOne = CAST('01/01/2003' as DATETIME)
EXEC GetMonthlyBookingsByRoomID @MonthDayOne, 104
GO

/* Cleanup code for text box
DROP PROC GetMonthlyBookingsByRoomID
DROP TABLE Bookings
*/
GO
------ END SQL ------

HTH,
TroyK, MCSD
By: volking Date: 13/05/2003 03:21:00 English  Type : Comment
TroyK - Impressive ... really impressive. And the most impressive thing is I UNDERSTAND YOUR SCRIPT! Your final workhorse query will run far far faster than my old while/cursor.

THIS is the reason I hang around EE. To learn from people like you. THANKS!

Frederick Volking
By: VGR Date: 13/05/2003 03:24:00 English  Type : Comment
it would be interesting to compare the performance of the two solutions :D :D

(and to compare simplicity too)
By: volking Date: 13/05/2003 03:51:00 English  Type : Comment
I have to say, your code has taught me how much I didn't know. For example, CROSS JOIN? When I saw CROSS JOIN I had to go to SQL books online. (smile) Tricky! Tricky! Tricky! Didn't know you could do that.

Frederick Volking
By: desperado101 Date: 13/05/2003 04:02:00 English  Type : Comment
thanks chaps for all your help - as a novice I'm gonna have to spend a few hours going through this! Anyway i can split the points and award you both a couple a hundred?!
By: volking Date: 13/05/2003 04:03:00 English  Type : Comment
TroyK?

CREATE VIEW Digits
AS
SELECT 0 'Digit' UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
GO

Returns error:
Line 6: Incorrect syntax near 'ALL'.
By: volking Date: 13/05/2003 04:11:00 English  Type : Comment
Got it ... one extra UNION ALL at the end. Should read

CREATE VIEW Digits
AS
SELECT 0 'Digit' UNION ALL
--SELECT 0 UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
GO

Frederick Volking
By: VGR Date: 13/05/2003 04:12:00 English  Type : Comment
completely unefficient if you want my advice :D
By: volking Date: 13/05/2003 04:14:00 English  Type : Comment
desperado101 - Please award all points to TroyK. And please pardon my inadvertent hijacking of your question (smile). I saw an opportunity to learn and grabbed it!

Thanks

Frederick Volking
By: volking Date: 13/05/2003 04:20:00 English  Type : Comment
VGR - Not efficient? hmmmmm ... possibly, but the technique TroyK has shown us could easily be adapted to generate output which has eluded me. For example, using TroyK's example, add another join and alter the select and suddenly you'll have a beautiful cross tab of all reserved rooms for each day in an entire hotel.

Try doing that with my old cursor and while loop technique. Now we're talking sloooooooooooooow!

Frederick Volking
By: VGR Date: 13/05/2003 04:21:00 English  Type : Answer
BTW, Troyk's "solution" won't work in MySql : there are no stored procedures (and no need of ;-)
By: volking Date: 13/05/2003 04:29:00 English  Type : Comment
The difference I see? TroyK is using true RDBMS SET methodology and I was using old mainframe sequential ISAM methodology. For about 95% of most business requirements us old-time programmer can "make-do" by using sequential access but believe me, that last 5% can be very troublesome. Using set methodology pulls that 5% back into the "can-do-it" realm.
By: VGR Date: 13/05/2003 04:30:00 English  Type : Comment
1) cursor solutions are always the worst :D
2) "blah blah blah, oh and let's add a JOIN" : ROFL
have you ever tried AVOIDING to add an "extra JOIN" ?

You're lucky MySql query optimizer takes care of all those JOINs ;-)

3) at least give a try to my simple, elegant, shorter and faster algorithm :D
By: VGR Date: 13/05/2003 04:30:00 English  Type : Comment
oh yes, I forgot :

3bis) my PORTABLE solution ;-)
By: volking Date: 13/05/2003 04:35:00 English  Type : Comment
VGR - I've noticed you dwell on the lack of stored procs in MySQL. You do realize, of course, that the code inside a stored proc can simply be executed as dynamic script, right? So unless there's a nested proc call, everything that can be done in stored procs can ALSO be done with simple dynamic calls. And even most nested calls can be re-architected for use dynamically.
By: VGR Date: 13/05/2003 04:55:00 English  Type : Comment
not exactly.

The code CAN be run as a "dynamic script" (if ever I understand what you write about here), BUT a stored proc is ***usually*** considered useful because it's "compiled" and tightly linked to the DB

This I find bad and wrong, and I'm not the only one.

It's usually for PERFORMANCE reasons that people put code in stored procs. MySql doesn't need this : it's the fastest "real" DB I've ever encountered.


To keep mastery on your code, everything should be in the same place : in the frontend scripts in this case.

Working on a DB that (you not knowing) relies on stored procs is something that costed me some days of hair-pulling in the past, I won't do the same mistake twice.
By: TroyK Date: 13/05/2003 17:52:00 English  Type : Comment
VGR;

>> "...it's the fstest "real" DB I've ever encountered..."

I'm interested in learning more about this. Can you provide a link to published benchmarks that support this claim?

Thanks!
TroyK, MCSD
By: VGR Date: 13/05/2003 18:31:00 English  Type : Comment
yes, but it all depends what you call "published", and by whom ;-)

I still haven't found an independantly-done benchmark (I'm trying to do one, but T-Sql of Sql-Server is sooooo SQL-uncompliant that I've a lot of problems ;-)

so I can point you to those links :


<A HREF="http://www.mysql.com/doc/en/MySQL_Benchmarks.html">http://www.mysql.com/doc/en/MySQL_Benchmarks.html</a>


<A HREF="http://www.mysql.com/information/benchmark-results/result-mysql,oracle.html">http://www.mysql.com/information/benchmark-results/result-mysql,oracle.html</a>

<A HREF="http://www.mysql.com/information/benchmark-results/result-db2,informix,ms-sql,mysql,solid,sybase.html">http://www.mysql.com/information/benchmark-results/result-db2,informix,ms-sql,mysql,solid,sybase.html</a>

<A HREF="http://www.mysql.com/doc/en/MySQL-PostgreSQL_benchmarks.html">http://www.mysql.com/doc/en/MySQL-PostgreSQL_benchmarks.html</a>

an interesting article from MySql documentation (such a degree in honesty has never been found in Microsoft's or Oracle's documentation ! ) :

<b>5.5.3 How Compiling and Linking Affects the Speed of MySQL</b><br />
<br />
Most of the following tests are done on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads. <br />
<br />
You get the fastest executable when you link with -static. <br />
<br />
On Linux, you will get the fastest code when compiling with pgcc and -O3. To compile `sql_yacc.cc' with these options, you need about 200M memory because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library (it is not needed). Note that with some versions of pgcc, the resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting code to be working on all x586 type processors (like AMD). <br />
<br />
By just using a better compiler and/or better compiler options you can get a 10-30% speed increase in your application. This is particularly important if you compile the SQL server yourself! <br />

<br />
<b>We have tested both the Cygnus CodeFusion and Fujitsu compilers, but when we tested them, neither was sufficiently bug free to allow MySQL to be compiled with optimisations on.</b> <br />
<br />
When you compile MySQL you should only include support for the character sets that you are going to use. (Option --with-charset=xxx.) The standard MySQL binary distributions are compiled with support for all character sets. <br />
<br />
Here is a list of some measurements that we have done: <br />
<br />
If you use pgcc and compile everything with -O6, the mysqld server is 1% faster than with gcc 2.95.2. <br />
<b>If you link dynamically (without -static), the result is 13% slower on Linux. Note that you still can use a dynamic linked MySQL library. It is only the server that is critical for performance. </b><br />
If you strip your mysqld binary with strip libexec/mysqld, the resulting binary can be up to 4% faster. <br />

<b>If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower on the same computer. (If you are connection to localhost, MySQL will, by default, use sockets.) </b><br />
<b>If you connect using TCP/IP from another computer over a 100M Ethernet, things will be 8-11% slower. </b><br />
When running our benchmark tests using secure connections (all data encrypted with internal SSL support) things were 55% slower. <br />
If you compile with --with-debug=full, then you will lose 20% for most queries, but some queries may take substantially longer (The MySQL benchmarks ran 35% slower) If you use --with-debug, then you will only lose 15%. By starting a mysqld version compiled with --with-debug=full with --skip-safemalloc the end result should be close to when configuring with --with-debug. <br />
On a Sun UltraSPARC-IIe, Forte 5.0 is 4% faster than gcc 3.2 <br />
<b>On a Sun UltraSPARC-IIe, Forte 5.0 is 4% faster in 32 bit mode than in 64 bit mode. </b><br />
Compiling with gcc 2.95.2 for UltraSPARC with the option -mcpu=v8 -Wa,-xarch=v8plusa gives 4% more performance. <br />
<b>On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads on a single processor. With more load/CPUs the difference should get bigger. </b><br />
Running with --log-bin makes mysqld 1% slower. <br />

Compiling on Linux-x86 using gcc without frame pointers -fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp makes mysqld 1-4% faster. <br />
<b>The MySQL-Linux distribution provided by MySQL AB used to be compiled with pgcc, but we had to go back to regular gcc because of a bug in pgcc that would generate the code that does not run on AMD.</b> We will continue using gcc until that bug is resolved. In the meantime, if you have a non-AMD machine, you can get a faster binary by compiling with pgcc. The standard MySQL Linux binary is linked statically to get it faster and more portable. <br />
</font>

<input TYPE="submit" NAME="buttPOST" VALUE="Post a Response">
<input TYPE="submit" NAME="return" VALUE="Return to Articles">
<input type=hidden name="locsuperth" value="1"><input TYPE="submit" NAME="buttRETSUJ" VALUE="Return to Sujets">&nbsp;&nbsp;<input TYPE="submit" NAME="buttVUESUJ" VALUE="View Sujet">&nbsp;&nbsp;&nbsp;&nbsp;<font color=blue size=+2><</font><input TYPE="submit" NAME="buttEDIT" VALUE="Edit Article"><font color=blue size=+2>></font>

<ul></ul><input TYPE="hidden" NAME="lecture" VALUE="173705">
<input TYPE="hidden" NAME="thread" VALUE="173671">
<input TYPE="hidden" NAME="subject" VALUE="au sujet &quot;du code pas optimisi&quot;">
<input TYPE="hidden" NAME="author" VALUE="VGR Maison">

<input TYPE="hidden" NAME="parent" VALUE="173693">
<input TYPE="hidden" NAME="plock" VALUE="0">
<input TYPE="hidden" NAME="pconfpseudo" VALUE="tous">
<input TYPE="hidden" NAME="pconf" VALUE="0">
<input TYPE="hidden" NAME="content" VALUE="un chapitre intiressant de la doc MySql. Vous pouvez toujours chercher une page aussi honnjte chez Kro$oft pour sa &quot;base de donnies&quot; Sql-Server, grande soeur d'Access :rire:

JAMAIS ils ne vous parleront de &quot;ga&quot;, et pourtant je prends les paris que ga s'applique aussi bien ` leurs produits &quot;multi-plateforme&quot; :arf:

&lt;B&gt;5.5.3 How Compiling and Linking Affects the Speed of MySQL&lt;/B&gt;

Most of the following tests are done on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads.

You get the fastest executable when you link with -static.

On Linux, you will get the fastest code when compiling with pgcc and -O3. To compile `sql_yacc.cc' with these options, you need about 200M memory because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library (it is not needed). Note that with some versions of pgcc, the resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting code to be working on all x586 type processors (like AMD).

By just using a better compiler and/or better compiler options you can get a 10-30% speed increase in your application. This is particularly important if you compile the SQL server yourself!

&lt;B&gt;We have tested both the Cygnus CodeFusion and Fujitsu compilers, but when we tested them, neither was sufficiently bug free to allow MySQL to be compiled with optimisations on.&lt;/B&gt;

When you compile MySQL you should only include support for the character sets that you are going to use. (Option --with-charset=xxx.) The standard MySQL binary distributions are compiled with support for all character sets.

Here is a list of some measurements that we have done:

If you use pgcc and compile everything with -O6, the mysqld server is 1% faster than with gcc 2.95.2.
&lt;B&gt;If you link dynamically (without -static), the result is 13% slower on Linux. Note that you still can use a dynamic linked MySQL library. It is only the server that is critical for performance. &lt;/B&gt;
If you strip your mysqld binary with strip libexec/mysqld, the resulting binary can be up to 4% faster.
&lt;B&gt;If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower on the same computer. (If you are connection to localhost, MySQL will, by default, use sockets.) &lt;/B&gt;
&lt;B&gt;If you connect using TCP/IP from another computer over a 100M Ethernet, things will be 8-11% slower. &lt;/B&gt;
When running our benchmark tests using secure connections (all data encrypted with internal SSL support) things were 55% slower.
If you compile with --with-debug=full, then you will lose 20% for most queries, but some queries may take substantially longer (The MySQL benchmarks ran 35% slower) If you use --with-debug, then you will only lose 15%. By starting a mysqld version compiled with --with-debug=full with --skip-safemalloc the end result should be close to when configuring with --with-debug.
On a Sun UltraSPARC-IIe, Forte 5.0 is 4% faster than gcc 3.2
&lt;B&gt;On a Sun UltraSPARC-IIe, Forte 5.0 is 4% faster in 32 bit mode than in 64 bit mode. &lt;/B&gt;
Compiling with gcc 2.95.2 for UltraSPARC with the option -mcpu=v8 -Wa,-xarch=v8plusa gives 4% more performance.
&lt;B&gt;On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads on a single processor. With more load/CPUs the difference should get bigger. &lt;/B&gt;
Running with --log-bin makes mysqld 1% slower.
Compiling on Linux-x86 using gcc without frame pointers -fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp makes mysqld 1-4% faster.
&lt;B&gt;The MySQL-Linux distribution provided by MySQL AB used to be compiled with pgcc, but we had to go back to regular gcc because of a bug in pgcc that would generate the code that does not run on AMD.&lt;/B&gt; We will continue using gcc until that bug is resolved. In the meantime, if you have a non-AMD machine, you can get a faster binary by compiling with pgcc. The standard MySQL Linux binary is linked statically to get it faster and more portable.
">
By: volking Date: 13/05/2003 18:36:00 English  Type : Comment
VGR says, "This I find bad and wrong, and I'm not the only one." (smile)
By: VGR Date: 13/05/2003 18:42:00 English  Type : Comment
heh, I've the right to express my opinion, no ?
By: desperado101 Date: 15/05/2003 20:26:00 English  Type : Comment
Chaps, if anyone is interested, this is the PHP script I have comeup with to do this. Thanks for your help.

//----------------------------------------------------------------------------------------------------------
// This function, given a month, year and room (Numeric Values only) will return an array containing a list of all booked days.
//----------------------------------------------------------------------------------------------------------

function get_booked_days($month, $year, $room)
{

// Define required variables.

$bookedDays = array();
$numDaysInMonth = num_days_in_month($month, $year); // Accesses function on include file.

// First we design a query that will return any bookings from within the month...

$sql = "SELECT * FROM Bookings WHERE RoomID='" . $room . "' AND (MONTH(ArrivalDate)='" . $month . "' OR MONTH(DepartureDate)='" . $month . "') AND (YEAR(ArrivalDate)='" . $year . "' OR YEAR(DepartureDate)='" . $year . "');";

// Query the DB

$result = query_database($sql); // Accesses function on include file.

// Count number of returned rows (bookings).

$num = mysql_num_rows($result);

if ($num>0) // I.E. Some bookings have been returned.
{
$counter = 0;

while ($row = mysql_fetch_array($result)) // This while loop works all the time there are new rows in $result.
{
$Arrival = explode("-", $row['ArrivalDate']); // $Arrival will contain date as an array. $Arrival[0]=year, $Arrival[1]=month and $Arrival[2]=day.
$Departure = explode("-", $row['DepartureDate']); // $Departure will contain date as an array. $Departure[0]=year, $Departure[1]=month and $Departure[2]=day.

if (($Arrival[0]==$Departure[0]) and ($Arrival[1]==$Departure[1])) // Arrival/Departure date are both in the same year and month.
{
for($x=$Arrival[2]; $x<($Departure[2]+"1"); $x++) // $x takes value from arrival day to departure day.
{
$bookedDays[$counter] = $x; // Each booked day is added to the bookedDays array.
$counter++;
}
}
else if(($Arrival[1]==$month and $Departure[1]>$Arrival[1]) and ($Arrival[0]==$Departure[0])) // You Arrive in the month but leave in a later month in the same year. We need to add days to end of month to bookedDays array.
{
for($x=$Arrival[2]; $x<($numDaysInMonth+1); $x++) // $x takes values from Arrival day through end of month
{
$bookedDays[$counter] = $x; // Each booked day is added to the bookedDays array.
$counter++;
}
}
else if(($Departure[1]==$month and $Arrival[1]<$Departure[1]) and ($Arrival[0]==$Departure[0])) // You Arrive in an ealier month in the year, and leave in the month.
{
for($x=$Departure[2]; $x>0; $x--) // $x takes values from departure day through beginning of month. (>0 i.e 1, the first of month).
{
$bookedDays[$counter] = $x; // Each booked day is added to the bookedDays array.
$counter++;
}
}
else if(($Arrival[1]==$month and $Departure[1]<$Arrival[1]) and ($Arrival[0]<$Departure[0])) // You Arrive in the month but leave in a later month in the next year. We need to add days to end of month to bookedDays array.
{
for($x=$Arrival[2]; $x<($numDaysInMonth+1); $x++) // $x takes values from Arrival day through end of month
{
$bookedDays[$counter] = $x; // Each booked day is added to the bookedDays array.
$counter++;
}
}
else if(($Departure[1]==$month and $Departure[1]<$Arrival[1]) and ($Arrival[0]<$Departure[0])) // You depart in the month but arrived last year.
{
for($x=$Departure[2]; $x>0; $x--) // $x takes values from Arrival day through end of month
{
$bookedDays[$counter] = $x; // Each booked day is added to the bookedDays array.
$counter++;
}
}
}
}

sort($bookedDays);
return $bookedDays;
}

Thanks again for all your help.
By: VGR Date: 15/05/2003 20:35:00 English  Type : Comment
yor code is wrong. You didn't listen to my demonstration (I even had done a schema for you!)

if arrival date is < currentMonth and departureDate is > currentMonth, it means the full month is booked. And your query will not return any rows so you'll think it's free. It's just the absolute counter-truth :D
By: desperado101 Date: 15/05/2003 20:45:00 English  Type : Comment
Your right, I won't pick up longer bookings!

But if i do:

'where RoomID=$theroomid AND MONTH(ArrivalDate)<=$themonth and MONTH(DepartureDate)>=$themonth'

What happens if i arrive in december(12) and leave in jan(1)? Any ideas how to get round that?
By: VGR Date: 15/05/2003 20:54:00 English  Type : Comment
there's no problem. I demonstrated it. if the month you query for is January, 1st will be booked. If the month is december, days booked will be 12th to 31th

look at my algorithm.
By: desperado101 Date: 15/05/2003 21:00:00 English  Type : Comment
Sorry I meant arrive December(12) leave Feb(2) then looking at Jan (1)...

WHERE RoomID="1" AND MONTH(ArrivalDate)<="1" AND MONTH(DepartureDate)>="1";

would'nt return the booking. I think we need a few or's in there depending on how the years are compared to each other...

Your statement only holds true if the Arrival/Departure year is the same...

If the departure year>arrival year then the reverse is true.. I think?!
By: VGR Date: 15/05/2003 21:10:00 English  Type : Comment
it will; Look at my ***algorithm***. All implementation ***detail*** are missing from an algorithm ;-)

You're right about the year's problem, but it's not really a problem. You've to ensure that month(e)s do follow a logical sequence from year to year, so there's a detail we haven't discussed nor evoked : I would store the months as YYYYMM or use comparisons for "month" on values formatted that way


this way everything works, 200212 is inferior to 200302

I still think my algorithm is the most efficient, and is even elegant (no false modesty here :D ). I'm ready to prove it
By: desperado101 Date: 15/05/2003 21:11:00 English  Type : Comment
Right I think it needs to be:

WHERE RoomID='$room' AND ((MONTH(ArrivalDate)<='$month' AND MONTH(DepartureDate)>='$month') AND YEAR(ArrivalDate)='$year' AND YEAR(DepartureDate)='$year') OR ((MONTH(ArrivalDate)>='$month' AND MONTH(DepartureDate)>='$month' AND YEAR(ArrivalDate)<'$year' AND YEAR(DepartureDate)>='$year') OR ((MONTH(ArrivalDate)<='$month' AND MONTH(DepartureDate)<='$month' AND YEAR(ArrivalDate)<='$year' AND YEAR(DepartureDate)>'$year');

What do you reckon?

I'm gonna try and test it now..
By: VGR Date: 15/05/2003 21:18:00 English  Type : Comment
no, it's ugly

WHERE RoomID='$room' AND (YEARMONTH(ArrivalDate)<='$yearmonth' AND YEARMONTH(DepartureDate)>='$yearmonth');


$yearmonth could be written also $year$month if you don't want/can have a $yearmonth variable='200302';

if YEARMONTH() doesnt exist in MySql (I think it's missing), you can use CONCAT(YEAR(),MONTH()) or use some function that I have already (using it for my stats yearly/monthly/daily)
By: desperado101 Date: 15/05/2003 21:25:00 English  Type : Comment
I agree - this is the best! Thanks,

No modesty required by the way you've really helped me out.. My biggest problem is I don't know much SQL so I am missing half the bits of the puzzle!
By: VGR Date: 15/05/2003 21:33:00 English  Type : Comment
agree. That's why MySql is fine :
-it's simple and easy
-it's SQL compliant
- the ONLINE DOCUMENTATION is a MUST

you'll learn the missing puzzle parts from lookibng in here, for instance...

<A HREF="http://www.mysql.com/doc/en/String_functions.html">http://www.mysql.com/doc/en/String_functions.html</a>

look for concat(), left(), mid(),


and in here

<A HREF="http://www.mysql.com/doc/en/Date_and_time_functions.html">http://www.mysql.com/doc/en/Date_and_time_functions.html</a>


look for month(), year(), to_days(), date_format(), now(), unix_timestamp()...
By: desperado101 Date: 15/05/2003 21:36:00 English  Type : Comment
Cheers VGR, now I just have to add some bits to my PHP for these other circumstances...
By: VGR Date: 15/05/2003 21:40:00 English  Type : Comment
I'm willing to demonstrate the superiority of my algorithm, if you could give some sample data .... ;-)
By: desperado101 Date: 15/05/2003 22:20:00 English  Type : Comment
Here you go...

#
# Table structure for table `Bookings`
#
# Creation: May 14, 2003 at 04:00 PM
# Last update: May 15, 2003 at 12:36 PM
#

CREATE TABLE `Bookings` (
`ID` int(8) NOT NULL auto_increment,
`RoomID` int(8) NOT NULL default '0',
`ClientID` int(100) NOT NULL default '0',
`ArrivalDate` date NOT NULL default '0000-00-00',
`DepartureDate` date NOT NULL default '0000-00-00',
`Adults` int(8) NOT NULL default '0',
`Children` int(8) NOT NULL default '0',
`SpecialRq` text NOT NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

#
# Dumping data for table `Bookings`
#

INSERT INTO `Bookings` VALUES (1, 4, 1, '2003-05-04', '2003-05-11', 1, 0, '');
INSERT INTO `Bookings` VALUES (2, 4, 1, '2003-05-28', '2003-06-08', 1, 0, '');
INSERT INTO `Bookings` VALUES (3, 4, 1, '2003-04-28', '2003-05-02', 1, 0, '');
INSERT INTO `Bookings` VALUES (4, 1, 1, '2002-12-21', '2003-01-15', 1, 0, '');
INSERT INTO `Bookings` VALUES (5, 1, 1, '2002-12-04', '2003-02-03', 1, 0, '');
INSERT INTO `Bookings` VALUES (6, 1, 1, '2003-03-07', '2004-01-23', 1, 0, '');
INSERT INTO `Bookings` VALUES (7, 1, 1, '2003-11-03', '2004-01-04', 1, 0, '');
By: VGR Date: 15/05/2003 23:42:00 English  Type : Comment
cool, I'm in it.

what is num_days_in_month() ?
By: desperado101 Date: 15/05/2003 23:46:00 English  Type : Comment
A function from my include file:

function num_days_in_month($month, $year)
{
$numDaysInMonth = date("t", mktime(0, 0, 0, $month, 1, $year));
return $numDaysInMonth;
}

I have also added the following 2 sections for longer than a month bookings....

else if(($Departure[1]>$month and $Arrival[1]>$month) and ($Arrival[0]<$Departure[0]))
{
for($x=1; $x<($numDaysInMonth+1); $x++)
{
$bookedDays[$counter] = $x;
$counter++;
}
}
else if(($Departure[1]<$month and $Arrival[1]<$month) and ($Arrival[0]<$Departure[0]))
{
for($x=1; $x<($numDaysInMonth+1); $x++)
{
$bookedDays[$counter] = $x;
$counter++;
}
}
By: VGR Date: 15/05/2003 23:59:00 English  Type : Comment
I had "emulated" it with this

function num_days_in_month($month, $year) {
$sql = "SELECT TO_DAYS('$year-$month-01')-TO_DAYS('$year-$month-01' + INTERVAL 1 MONTH));";
// Query the DB
$result = query_database($sql); // Accesses function on include file.
$res=mysql_result($result,0,0);
return $res;
} // num_days_in_month($month, $year) integer function

I also redeveloped the database_query() function
By: VGR Date: 16/05/2003 00:04:00 English  Type : Comment
sorry boy, but your program is buggy.

It returns this for the query $thearray=get_booked_days('01', '2004', 1);

I also noticed some days are 0 padded, but the majority isn't.

I will finish my own script in some time

Array
(
[0] => 1
[1] => 1
[2] => 2
[3] => 2
[4] => 3
[5] => 3
[6] => 04
[7] => 4
[8] => 5
[9] => 6
[10] => 7
[11] => 8
[12] => 9
[13] => 10
[14] => 11
[15] => 12
[16] => 13
[17] => 14
[18] => 15
[19] => 16
[20] => 17
[21] => 18
[22] => 19
[23] => 20
[24] => 21
[25] => 22
[26] => 23
)
By: VGR Date: 16/05/2003 00:06:00 English  Type : Comment
I admit that this was because of those overlapping periods for the same room :D

| 6 | 1 | 1 | 2003-03-07 | 2004-01-23 | 1 | 0 | |
| 7 | 1 | 1 | 2003-11-03 | 2004-01-04 | 1 | 0 | |
By: desperado101 Date: 16/05/2003 00:07:00 English  Type : Comment
I hadn't noticed because all i do is when I draw the calender table, if the day number appears in the array the background is coloured red. so it could appear 100's of times and it wouldn't matter..

I will have to go through it and see whats up.
By: desperado101 Date: 16/05/2003 00:08:00 English  Type : Comment
Is there not a php function that can remove duplicates? not that i'm lazy or anything!!
By: VGR Date: 16/05/2003 00:11:00 English  Type : Comment
in this case,no. That was because you had two reservations for the same room in the same period (2004-01-01 to 04)

this isn't supposed to occur :D

of cource there are ways to remove duplicates... wait for my code :D
By: desperado101 Date: 16/05/2003 00:13:00 English  Type : Comment
of course.. I added these by hand to test things.. The webpage checks for availability before making a booking...

Well, it will do when I write it!!!
By: VGR Date: 16/05/2003 00:45:00 English  Type : Comment
ok. Done.

As I told you, this kind of query is exactly what you needed (Arriva<>=yearmonth AND Departure>=yearmonth)

SELECT *,('2003-06-01' + INTERVAL 1 MONTH)- INTERVAL 1 DAY as a FROM Bookings WHERE RoomID=4 AND (CONCAT(LEFT(ArrivalDate,4),MID(ArrivalDate,6,2))<='200306' AND CONCAT(LEFT(DepartureDate,4),MID(DepartureDate,6,2))>='200306');

My source code is somewhat shorter than yours :D

------------------- here are the results compared
-- your way
page generated in 89.79403 milliseconds
page generated in 2.09403 milliseconds
Array
(
[0] => 1
[1] => 2
[2] => 3
[3] => 4
[4] => 5
[5] => 6
[6] => 7
[7] => 08
)

-- my way
page generated in 1.94097 milliseconds
Array
(
[0] => 1
[1] => 2
[2] => 3
[3] => 4
[4] => 5
[5] => 6
[6] => 7
[7] => 8
)


------------------- here is the source
function get_booked_days($month, $year, $room) {
/*
Suggested Algorithm :
-take the $theroomID and $themonth (the arguments)
-query the DB for all rows where MONTH(ArrivalDate)<=$themonth and MONTH(DepartureDate)>=$themonth;
-thus we now have either :
-nothing (0 rows) : the room $theroomID is free in month $themonth
-more than 0 rows : compute $a=Max(ArrivalDate,"$themonth-01") [the first day of month $themonth] and $b=Min(DepartureDate,month ($themonth+1)'s first day - INTERVAL 1 DAY)
-simply for $i from $a to $b display "room roomID is booked on day $i"
*/
//-take the $theroomID and $themonth (the arguments)
$yearmonth=$year.$month; // I've a PHP function ready to be used to take care of 0 padding, etc
// Define required variables.
$bookedDays = array();
// First we design a query that will return any bookings from within the month...
//-query the DB for all rows where MONTH(ArrivalDate)<=$themonth and MONTH(DepartureDate)>=$themonth;
$sql = "SELECT *,('$year-$month-01' + INTERVAL 1 MONTH)- INTERVAL 1 DAY as a FROM Bookings WHERE RoomID=$room AND (CONCAT(LEFT(ArrivalDate,4),MID(ArrivalDate,6,2))<='$yearmonth' AND CONCAT(LEFT(DepartureDate,4),MID(DepartureDate,6,2))>='$yearmonth');";
// Query the DB
$result = query_database($sql); // Accesses function on include file.
// Count number of returned rows (bookings).
$num = mysql_num_rows($result);
//-thus we now have either :
//-nothing (0 rows) : the room $theroomID is free in month $themonth
if ($num>0) { // I.E. Some bookings have been returned.
if ($num>1) echo "warning more than one booking found for month '$yearmonth'
";
//-more than 0 rows : compute $a=Max(ArrivalDate,"$themonth-01") [the first day of month $themonth] and $b=Min(DepartureDate,month ($themonth+1)'s first day - INTERVAL 1 DAY)
// get returned row
$res=mysql_fetch_array($result);
// get data
$theArrivalDate=$res['ArrivalDate'];
$theDepartureDate=$res['DepartureDate'];
$lastday=$res['a'];
// compute
$a=(integer) substr(Max($theArrivalDate,"$year-$month-01"),8,2);
$b=(integer) substr(Min($theDepartureDate,$lastday),8,2);
// done
//-simply for $i from $a to $b display "room roomID is booked on day $i"
for ($i=$a;$i<=$b;$i++) $bookedDays[]=$i;
} // else RàF, empty array returned, no bookings
return $bookedDays;
} // get_booked_days Array Function
By: desperado101 Date: 16/05/2003 00:56:00 English  Type : Comment
blimey - thanks I will work through it...
By: VGR Date: 16/05/2003 01:08:00 English  Type : Comment
that's 100% the short&clear algorithm I suggested in the first place (05/12/2003 02:29PM PST ), augmented of year/month handling.

Please note I sticked to your way of programming (no $connectionID or $linkID taken from mysql_connect(), calling query_database() while it's faster to call mysql_query() directly, etc etc

Not only is my script 3 times shorter than yours, but it's a lot more readable. It's also faster :D
By: desperado101 Date: 16/05/2003 01:11:00 English  Type : Comment
Its great thanks for your help - I've only been doing SQL/PHP for 3 days so your help has been much appreciated!
By: VGR Date: 16/05/2003 01:21:00 English  Type : Comment
yes and no. When it comes to algorithmics, I think all people are equal in chances to get a proper one.

May-be I'm wrong and my script doesn't work properly, though, but given it's simplicity I doubt it.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 474.328990 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page