Re: [U2] MySQL query qestion....
Hi George, An unconfirmed use per your query question: SELECT * FROM ( SELECT UPDATE_PROCEDURE() ) AS `updateProcedure`, ( SELECT * FROM theTablename ) AS theTable WHERE theTable.`LAST_UPDATE` > updateProcedure.`lastUpdateDate` ; -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: Friday, 20 July 2012 1:56 AM To: U2 Users Subject: [U2] MySQL query qestion This is specific to MySQL, but could apply to any SQL DB. Is there a way to put the results of one query to be used by another, without having that second query run on each Iteration of the sub query? Example: SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE()); The function UPDATE_PROCEDURE() returns a timestamp, and then updates itself with a new timestamp. My workaround is: SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT GET_PROCEDURE()); SELECT UPDATE_PROCEDURE(); Here the GET_PROCEDURE() only retrieves a value, and doesn't update it. BUT...if there were half a million records in the TABLE, I'd prefer not to run the GET procedure half a million times To retrieve the same result which could add significant processing time (this would be run for each of the tables I'm mirroring on UV from MySQL . George ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- Message protected by DealerGuard: e-mail anti-virus, anti-spam and content filtering. http://www.pentanasolutions.com Click here to report this message as spam: https://login.mailguard.com.au/report/1FaOdyY7h0/4FA8oy9gy44s1FA732Klkr/0 This email and any attachments to it are confidential. You must not use, disclose or act on the email if you are not the intended recipient. Liability limited by a scheme approved under Professional Standards Legislation. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] MySQL query qestion....
Here's what I would up going with
DELIMITER $$
CREATE PROCEDURE `GetTableUpdates`(p_key VARCHAR(45))
BEGIN
DECLARE i_result timestamp;
SELECT UpdateUTime(p_key) INTO i_result;
SET @t1 =CONCAT('SELECT * FROM ',p_key," WHERE LastUpdate > '",i_result,"'"
);
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END
And is called using "CALL GetTableUpdates('tablename')"
George
-Original Message-
From: u2-users-boun...@listserver.u2ug.org
[mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists
Sent: Thursday, July 19, 2012 12:47 PM
To: u2-users@listserver.u2ug.org
Subject: Re: [U2] MySQL query qestion
My SQL-fu isn't up to much, but my reaction was along the lines of
SELECT *, LAST_UPDATED=GET_PROCEDURE() FROM TABLE WHERE LAST_UPDATE >
LAST_UPDATED;
An optimiser may be able to optimise this for you, depending how
complicated GET_PROCEDURE() is. If it's just a select from yet another
table, the optimiser should spot it's constant and it'll fly. You might
be able to declare it constant, I don't know.
(And don't trust my example syntax! :-)
Cheers,
Wol
On 19/07/12 17:29, Brian Leach wrote:
> George
>
> It depends on the db type. In SQL Server, for example, you would create a
> stored procedure that would capture the subquery result to a local variable
> and use that.
>
> e.g.
>
> DECLARE @lastUpdate DATETIME;
> SELECT @lastUpdate = some_expression ;
> select * from table where last_update > @lastUpdate
>
> But I don't know with mySQL - it's a bit short on intellect - but I see it
> has a SELECT .. INTO format for populating a variable. Might be worth a
> shot.
>
>
> Brian
>
> -Original Message-
> From: u2-users-boun...@listserver.u2ug.org
> [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen
> Sent: 19 July 2012 16:56
> To: U2 Users
> Subject: [U2] MySQL query qestion
>
> This is specific to MySQL, but could apply to any SQL DB.
>
> Is there a way to put the results of one query to be used by another,
> without having that second query run on each Iteration of the sub query?
>
> Example:
>
> SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE());
>
> The function UPDATE_PROCEDURE() returns a timestamp, and then updates itself
> with a new timestamp.
>
> My workaround is:
>
> SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT GET_PROCEDURE()); SELECT
> UPDATE_PROCEDURE();
>
> Here the GET_PROCEDURE() only retrieves a value, and doesn't update it.
>
> BUT...if there were half a million records in the TABLE, I'd prefer not to
> run the GET procedure half a million times
>To retrieve the same result which could add significant processing time
> (this would be run for each of the tables
>I'm mirroring on UV from MySQL .
>
> George
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
___
U2-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] MySQL query qestion....
I agree with Brian, a stored procedure or function seems to be the way to go, where you get the value, store it in a variable, and then use the variable in the query. On Thu, Jul 19, 2012 at 10:29 AM, Brian Leach wrote: > George > > It depends on the db type. In SQL Server, for example, you would create a > stored procedure that would capture the subquery result to a local variable > and use that. > > e.g. > > DECLARE @lastUpdate DATETIME; > SELECT @lastUpdate = some_expression ; > select * from table where last_update > @lastUpdate > > But I don't know with mySQL - it's a bit short on intellect - but I see it > has a SELECT .. INTO format for populating a variable. Might be worth a > shot. > > > Brian > > -Original Message- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen > Sent: 19 July 2012 16:56 > To: U2 Users > Subject: [U2] MySQL query qestion > > This is specific to MySQL, but could apply to any SQL DB. > > Is there a way to put the results of one query to be used by another, > without having that second query run on each Iteration of the sub query? > > Example: > > SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE()); > > The function UPDATE_PROCEDURE() returns a timestamp, and then updates > itself > with a new timestamp. > > My workaround is: > > SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT GET_PROCEDURE()); SELECT > UPDATE_PROCEDURE(); > > Here the GET_PROCEDURE() only retrieves a value, and doesn't update it. > > BUT...if there were half a million records in the TABLE, I'd prefer not to > run the GET procedure half a million times >To retrieve the same result which could add significant processing time > (this would be run for each of the tables >I'm mirroring on UV from MySQL . > > George > > > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > > > ___ > U2-Users mailing list > U2-Users@listserver.u2ug.org > http://listserver.u2ug.org/mailman/listinfo/u2-users > ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] MySQL query qestion....
My SQL-fu isn't up to much, but my reaction was along the lines of SELECT *, LAST_UPDATED=GET_PROCEDURE() FROM TABLE WHERE LAST_UPDATE > LAST_UPDATED; An optimiser may be able to optimise this for you, depending how complicated GET_PROCEDURE() is. If it's just a select from yet another table, the optimiser should spot it's constant and it'll fly. You might be able to declare it constant, I don't know. (And don't trust my example syntax! :-) Cheers, Wol On 19/07/12 17:29, Brian Leach wrote: > George > > It depends on the db type. In SQL Server, for example, you would create a > stored procedure that would capture the subquery result to a local variable > and use that. > > e.g. > > DECLARE @lastUpdate DATETIME; > SELECT @lastUpdate = some_expression ; > select * from table where last_update > @lastUpdate > > But I don't know with mySQL - it's a bit short on intellect - but I see it > has a SELECT .. INTO format for populating a variable. Might be worth a > shot. > > > Brian > > -Original Message- > From: u2-users-boun...@listserver.u2ug.org > [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen > Sent: 19 July 2012 16:56 > To: U2 Users > Subject: [U2] MySQL query qestion > > This is specific to MySQL, but could apply to any SQL DB. > > Is there a way to put the results of one query to be used by another, > without having that second query run on each Iteration of the sub query? > > Example: > > SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE()); > > The function UPDATE_PROCEDURE() returns a timestamp, and then updates itself > with a new timestamp. > > My workaround is: > > SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT GET_PROCEDURE()); SELECT > UPDATE_PROCEDURE(); > > Here the GET_PROCEDURE() only retrieves a value, and doesn't update it. > > BUT...if there were half a million records in the TABLE, I'd prefer not to > run the GET procedure half a million times >To retrieve the same result which could add significant processing time > (this would be run for each of the tables >I'm mirroring on UV from MySQL . > > George ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] MySQL query qestion....
George It depends on the db type. In SQL Server, for example, you would create a stored procedure that would capture the subquery result to a local variable and use that. e.g. DECLARE @lastUpdate DATETIME; SELECT @lastUpdate = some_expression ; select * from table where last_update > @lastUpdate But I don't know with mySQL - it's a bit short on intellect - but I see it has a SELECT .. INTO format for populating a variable. Might be worth a shot. Brian -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of George Gallen Sent: 19 July 2012 16:56 To: U2 Users Subject: [U2] MySQL query qestion This is specific to MySQL, but could apply to any SQL DB. Is there a way to put the results of one query to be used by another, without having that second query run on each Iteration of the sub query? Example: SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT UPDATE_PROCEDURE()); The function UPDATE_PROCEDURE() returns a timestamp, and then updates itself with a new timestamp. My workaround is: SELECT * FROM TABLE WHERE LAST_UPDATE > (SELECT GET_PROCEDURE()); SELECT UPDATE_PROCEDURE(); Here the GET_PROCEDURE() only retrieves a value, and doesn't update it. BUT...if there were half a million records in the TABLE, I'd prefer not to run the GET procedure half a million times To retrieve the same result which could add significant processing time (this would be run for each of the tables I'm mirroring on UV from MySQL . George ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
