Re: timing queries?

[Roger Baklund]

Jacek Becla wrote:

Hi,

Does anyone know how to execute SQL statements from a text file
such that the summaries "X rows in set (X.YZ sec)" are printed
for each query?

Neither of these do it:
  mysql < batch-file
  mysql -e 'source batch-file'


If you add verbosity using the parameter -vv (or -v -v or --verbose 
--verbose) you will get the "X rows in set" message, but you will also 
get the SQL statements in the output.


C:> mysql -vv -e 'select 2+2'
--
select 2+2
--

+-+
| 2+2 |
+-+
|   4 |
+-+
1 row in set (0.00 sec)

Bye

C:>

You can combine with --skip-column-names and --batch to change the output.

--
Roger


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: timing queries?

[Sujay Koduri]

If you use source absolute-pathname from mysql command prompt, then it will
execute each statement(or line) in the file individually and print the
results exactly in the format you are looking for.

sujay

-Original Message-
From: Jacek Becla [mailto:[EMAIL PROTECTED] 
Sent: Saturday, October 01, 2005 1:20 AM
To: mysql@lists.mysql.com
Subject: timing queries?

Hi,

Does anyone know how to execute SQL statements from a text file such that
the summaries "X rows in set (X.YZ sec)" are printed for each query?

Neither of these do it:
   mysql < batch-file
   mysql -e 'source batch-file'


'source batch-file' run inside mysql command-line does it, but then I need
to manually copy and paste the results.

Thanks,
Jacek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timing problem

[Nuno Pereira]

Tony Leake wrote:

On Thu, 2005-09-29 at 16:15 +0100, Nuno Pereira wrote:


[EMAIL PROTECTED] wrote:




This one is interesting in terms of concurrency...

Is the app2 the one responsable for setting the flag? I supose that it is.
If that happens it's important that app2 doesn't mess with the flag, i 
mean, it may lead to problems if app2 sees that app1 writes the sales 
data, app2 sees it, starts reading it, app1 writes more data and app2 
sets the flag without seeing that app1 have written more data, and sets 
the flag without reading the new one.


Make this work without problems can be tricky, and I don't see a good 
solution to this in five minutes. When app2 reads data what app does 
with it? How app2 sees what was the last data she read?


--
Nuno Pereira




I don't think there is any concurency problem:

App 2 sets the flag and then polls for it to be unset, it is unset by
app 1 when it has written all of the sales data. meanwhile app 2 is
polling for the flag to be unset again and does nothing until it is, if
app 1 dies before unsetting the flag app then app 2 will sit in a loop
forever.


That isn't true if you are using transactions.


when the flag is unset, app 2 reads it, process it and posts it to a
website via xmlrpc

app 2 site in a shell script while loop: (pseudocide)

while (1){
 start app 2
sleep (60)
}

so there can never be more than one instance of app 2 running. 


tony


You may be right, but app2 is running while app1 is...

You know better if there are problems...

--
Nuno Pereira

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timing problem

[SGreen]

Nuno Pereira <[EMAIL PROTECTED]> wrote on 09/29/2005 11:15:08 AM:

> [EMAIL PROTECTED] wrote:
> > Tony Leake <[EMAIL PROTECTED]> wrote on 09/29/2005 07:08:24 AM:
> > 
> > 
> >>Hi
> >>
> >>I have 2 applications communicating via a mysql database
> >>
> >>the db is 4.1.8 running on a debian linux system. 
> >>All tables are innodb
> >>
> >>app 1 1 runs on a windows machine, is written in c# and talks to the 
db
> >>with odbc
> >>
> >>app 2 runs on the same machine as the db and is writtin in php.
> >>
> >>
> >>Here's the problem
> >>
> >>app 1 writes sales data to a table, when it is finished it unsets a 
flag
> >>in another table to indicate that it is finished
> >>
> >>app 2 polls for the flag to be unset, then reads the sales data.
> >>
> >>Sometimes whem app 2 reads the data there is nothing to be read, i am
> >>logging the queries that app 2 is using to do the inserts, then by the
> >>time i can open up a terminal and query the table manually the data is
> >>there. 
> >>
> >>The only thing I can assume it that there is some kind of timing issue
> >>and the data is not fully written when I try to read it, this doesn't
> >>happen every time and may only be when the server is loaded. Does this
> >>happen?  If so what can I do about it, would putting the inserts into
> >>one big transaction help? At the moment All of the inserts are done by
> >>implicit commits.
> >>
> >>
> >>Sorry for the essay, i an just trying to fully document what I know.
> >>
> >>Thanks
> >>tony
> >>
> > 
> > 
> > 
> > When it comes to problems, more information is better. The key here is 

> > that you are making multiple changes from app1 that really should be 
> > within a transaction.  The entire process of writing sales data and 
> > unsetting a flag from app1 needs to be transacted.  That way the other 

> > server (app2) will either have consistent data or will never find out 
that 
> > anything was going on in the first place.
> > 
> > The good thing is that you are already using InnoDB for all of your 
> > tables. This makes wrapping your process in a transaction fairly 
simple. 
> > Before you begin the "sales data write", issue the command "START 
> > TRANSACTION;". Do your writes and unset your flag. Then if all seems 
to 
> > have completed correctly, issue the command "COMMIT;" and if something 

> > went wrong issue the command "ROLLBACK;".  The trick to making this 
work 
> > is that everything that happens between "START TRANSACTION" and 
"COMMIT" 
> > happens on the same connection. You cannot start a transaction from 
one 
> > connection and finish it from another. Depending on how your 
application 
> > (app1) is designed, you may have to do a little work to make sure that 
you 
> > use the same connection for the entire process.
> > 
> > Once you add those two commands, app2 should never see that flag unset 

> > unless the sales data is actually available.
> > 
> > For more detais, RTFM: http://dev.mysql.com/doc/mysql/en/commit.html
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> 
> This one is interesting in terms of concurrency...
> 
> Is the app2 the one responsable for setting the flag? I supose that it 
is.
> If that happens it's important that app2 doesn't mess with the flag, i 
> mean, it may lead to problems if app2 sees that app1 writes the sales 
> data, app2 sees it, starts reading it, app1 writes more data and app2 
> sets the flag without seeing that app1 have written more data, and sets 
> the flag without reading the new one.
> 
> Make this work without problems can be tricky, and I don't see a good 
> solution to this in five minutes. When app2 reads data what app does 
> with it? How app2 sees what was the last data she read?
> 
> -- 
> Nuno Pereira

Transactions provide for process atomicity and process isolation. Both of 
which help to avoid the concurrency issues you discuss. 

If app2 opens a transaction to read the data then reset the flag, app1 
won't be able to mess with that particular set of data until app2 finishes 
and COMMIT-s. If at the same time app1 tries to update the data and unset 
the flag that app2 has it's transaction open, app1's changes won't be 
visible to app2 until: 1) app2 finishes it's transaction (COMMIT or 
ROLLBACK) and 2) app1 COMMIT-s its transaction. If app1 does a ROLLBACK, 
nothing changes.

That kind of process coordination and data consistency is precisely what 
transactions and row-level locking are intended to enable. They can't 
solve every problem but they are a good 90%-level solution to database 
coordination and are exactly what's needed for the OP's situation (IMHO)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: timing problem

[Tony Leake]

On Thu, 2005-09-29 at 16:15 +0100, Nuno Pereira wrote:
> [EMAIL PROTECTED] wrote:

> 
> This one is interesting in terms of concurrency...
> 
> Is the app2 the one responsable for setting the flag? I supose that it is.
> If that happens it's important that app2 doesn't mess with the flag, i 
> mean, it may lead to problems if app2 sees that app1 writes the sales 
> data, app2 sees it, starts reading it, app1 writes more data and app2 
> sets the flag without seeing that app1 have written more data, and sets 
> the flag without reading the new one.
> 
> Make this work without problems can be tricky, and I don't see a good 
> solution to this in five minutes. When app2 reads data what app does 
> with it? How app2 sees what was the last data she read?
> 
> -- 
> Nuno Pereira
> 

I don't think there is any concurency problem:

App 2 sets the flag and then polls for it to be unset, it is unset by
app 1 when it has written all of the sales data. meanwhile app 2 is
polling for the flag to be unset again and does nothing until it is, if
app 1 dies before unsetting the flag app then app 2 will sit in a loop
forever.

when the flag is unset, app 2 reads it, process it and posts it to a
website via xmlrpc

app 2 site in a shell script while loop: (pseudocide)

while (1){
 start app 2
sleep (60)
}

so there can never be more than one instance of app 2 running. 

tony





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timing problem

[Nuno Pereira]

[EMAIL PROTECTED] wrote:

Tony Leake <[EMAIL PROTECTED]> wrote on 09/29/2005 07:08:24 AM:



Hi

I have 2 applications communicating via a mysql database

the db is 4.1.8 running on a debian linux system. 
All tables are innodb


app 1 1 runs on a windows machine, is written in c# and talks to the db
with odbc

app 2 runs on the same machine as the db and is writtin in php.


Here's the problem

app 1 writes sales data to a table, when it is finished it unsets a flag
in another table to indicate that it is finished

app 2 polls for the flag to be unset, then reads the sales data.

Sometimes whem app 2 reads the data there is nothing to be read, i am
logging the queries that app 2 is using to do the inserts, then by the
time i can open up a terminal and query the table manually the data is
there. 


The only thing I can assume it that there is some kind of timing issue
and the data is not fully written when I try to read it, this doesn't
happen every time and may only be when the server is loaded. Does this
happen?  If so what can I do about it, would putting the inserts into
one big transaction help? At the moment All of the inserts are done by
implicit commits.


Sorry for the essay, i an just trying to fully document what I know.

Thanks
tony





When it comes to problems, more information is better. The key here is 
that you are making multiple changes from app1 that really should be 
within a transaction.  The entire process of writing sales data and 
unsetting a flag from app1 needs to be transacted.  That way the other 
server (app2) will either have consistent data or will never find out that 
anything was going on in the first place.


The good thing is that you are already using InnoDB for all of your 
tables. This makes wrapping your process in a transaction fairly simple. 
Before you begin the "sales data write", issue the command "START 
TRANSACTION;". Do your writes and unset your flag. Then if all seems to 
have completed correctly, issue the command "COMMIT;" and if something 
went wrong issue the command "ROLLBACK;".  The trick to making this work 
is that everything that happens between "START TRANSACTION" and "COMMIT" 
happens on the same connection. You cannot start a transaction from one 
connection and finish it from another. Depending on how your application 
(app1) is designed, you may have to do a little work to make sure that you 
use the same connection for the entire process.


Once you add those two commands, app2 should never see that flag unset 
unless the sales data is actually available.


For more detais, RTFM: http://dev.mysql.com/doc/mysql/en/commit.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


This one is interesting in terms of concurrency...

Is the app2 the one responsable for setting the flag? I supose that it is.
If that happens it's important that app2 doesn't mess with the flag, i 
mean, it may lead to problems if app2 sees that app1 writes the sales 
data, app2 sees it, starts reading it, app1 writes more data and app2 
sets the flag without seeing that app1 have written more data, and sets 
the flag without reading the new one.


Make this work without problems can be tricky, and I don't see a good 
solution to this in five minutes. When app2 reads data what app does 
with it? How app2 sees what was the last data she read?


--
Nuno Pereira

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timing problem

[Tony Leake]

On Thu, 2005-09-29 at 09:30 -0400, [EMAIL PROTECTED] wrote:


> The key here is 
> that you are making multiple changes from app1 that really should be 
> within a transaction.  The entire process of writing sales data and 
> unsetting a flag from app1 needs to be transacted.  That way the other 
> server (app2) will either have consistent data or will never find out that 
> anything was going on in the first place.
> 

Many thanks Shawn, I few changes needed to the app but i'll give it a
go.

Regards
tony




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: timing problem

[SGreen]

Tony Leake <[EMAIL PROTECTED]> wrote on 09/29/2005 07:08:24 AM:

> Hi
> 
> I have 2 applications communicating via a mysql database
> 
> the db is 4.1.8 running on a debian linux system. 
> All tables are innodb
> 
> app 1 1 runs on a windows machine, is written in c# and talks to the db
> with odbc
> 
> app 2 runs on the same machine as the db and is writtin in php.
> 
> 
> Here's the problem
> 
> app 1 writes sales data to a table, when it is finished it unsets a flag
> in another table to indicate that it is finished
> 
> app 2 polls for the flag to be unset, then reads the sales data.
> 
> Sometimes whem app 2 reads the data there is nothing to be read, i am
> logging the queries that app 2 is using to do the inserts, then by the
> time i can open up a terminal and query the table manually the data is
> there. 
> 
> The only thing I can assume it that there is some kind of timing issue
> and the data is not fully written when I try to read it, this doesn't
> happen every time and may only be when the server is loaded. Does this
> happen?  If so what can I do about it, would putting the inserts into
> one big transaction help? At the moment All of the inserts are done by
> implicit commits.
> 
> 
> Sorry for the essay, i an just trying to fully document what I know.
> 
> Thanks
> tony
> 


When it comes to problems, more information is better. The key here is 
that you are making multiple changes from app1 that really should be 
within a transaction.  The entire process of writing sales data and 
unsetting a flag from app1 needs to be transacted.  That way the other 
server (app2) will either have consistent data or will never find out that 
anything was going on in the first place.

The good thing is that you are already using InnoDB for all of your 
tables. This makes wrapping your process in a transaction fairly simple. 
Before you begin the "sales data write", issue the command "START 
TRANSACTION;". Do your writes and unset your flag. Then if all seems to 
have completed correctly, issue the command "COMMIT;" and if something 
went wrong issue the command "ROLLBACK;".  The trick to making this work 
is that everything that happens between "START TRANSACTION" and "COMMIT" 
happens on the same connection. You cannot start a transaction from one 
connection and finish it from another. Depending on how your application 
(app1) is designed, you may have to do a little work to make sure that you 
use the same connection for the entire process.

Once you add those two commands, app2 should never see that flag unset 
unless the sales data is actually available.

For more detais, RTFM: http://dev.mysql.com/doc/mysql/en/commit.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: timing

[Mary Stickney]

It doesn't run over the network.
The reports are requested from a website , and a record is inserted into the
ReportQueue.
And then the reports are run local on the Production machine and then
emailed to the recipient.

At this time only 2 people order reports.



Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: David Brodbeck [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 31, 2003 3:09 PM
To: 'Mary Stickney'; [EMAIL PROTECTED]
Subject: RE: timing


Have you checked for network problems?  You might try FTPing a file to and
from the production server, or something similar, just to rule this out.

I've had slowness problems with various apps that were driving me *nuts*
until I found a network issue causing them.  In a few cases I had a duplex
mismatch -- an ethernet card set to full duplex connected to a half-duplex
hub/switch, or vice versa.  This results in a connection that appears fine
when you ping and works great under light traffic, but bogs down with
massive numbers of collisions as soon as you do anything that involves
moving lots of data.  The collisions may not show up in the statistics since
the end that's set to full duplex won't notice them.  Letting the card
auto-detect the duplex setting does *not* always work right...

This is a subtle, easy trap to fall into.  Easy enough that I've done it
multiple times. ;)

> -Original Message-
> From: Mary Stickney [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 30, 2003 9:08 AM
> To: [EMAIL PROTECTED]
> Subject: timing
>
>
>
> I have 2 Crystal Reports 8.5 (for the record I didn't make
> these reports)
> that draw straight from the MYSQL
> database. They use only 1 table. I am calling them to print
> from VB 6.0
> Theses reports have lots of complex calculations in them,
> counts , sums and
> groups.
>
> My test server has the same code , same database , same indexes , same
> amount of memory as the Production server,
> My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
> installed on my test server.
>
> The Production server has MYSQL 3.23 and an older version of
> MYODBC then I
> have.
> No Crystal Reports installed. And the server is a faster
> machine then mine.
>
>
> They run in 8 minutes on my test server , about 4 minutes
> each. Printing to
> a PDF driver.
>
> On the Production Server they take HOURS to run and print.
>
> Any ideas...
>
>
>
>
> Mary Stickney
> TAG-TMI
> Data Warehouse Programmer
> 402-474-7612 x 3099
> [EMAIL PROTECTED]
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[David Brodbeck]

Have you checked for network problems?  You might try FTPing a file to and
from the production server, or something similar, just to rule this out.

I've had slowness problems with various apps that were driving me *nuts*
until I found a network issue causing them.  In a few cases I had a duplex
mismatch -- an ethernet card set to full duplex connected to a half-duplex
hub/switch, or vice versa.  This results in a connection that appears fine
when you ping and works great under light traffic, but bogs down with
massive numbers of collisions as soon as you do anything that involves
moving lots of data.  The collisions may not show up in the statistics since
the end that's set to full duplex won't notice them.  Letting the card
auto-detect the duplex setting does *not* always work right...

This is a subtle, easy trap to fall into.  Easy enough that I've done it
multiple times. ;)

> -Original Message-
> From: Mary Stickney [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 30, 2003 9:08 AM
> To: [EMAIL PROTECTED]
> Subject: timing
> 
> 
> 
> I have 2 Crystal Reports 8.5 (for the record I didn't make 
> these reports)
> that draw straight from the MYSQL
> database. They use only 1 table. I am calling them to print 
> from VB 6.0
> Theses reports have lots of complex calculations in them, 
> counts , sums and
> groups.
> 
> My test server has the same code , same database , same indexes , same
> amount of memory as the Production server,
> My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
> installed on my test server.
> 
> The Production server has MYSQL 3.23 and an older version of 
> MYODBC then I
> have.
> No Crystal Reports installed. And the server is a faster 
> machine then mine.
> 
> 
> They run in 8 minutes on my test server , about 4 minutes 
> each. Printing to
> a PDF driver.
> 
> On the Production Server they take HOURS to run and print.
> 
> Any ideas...
> 
> 
> 
> 
> Mary Stickney
> TAG-TMI
> Data Warehouse Programmer
> 402-474-7612 x 3099
> [EMAIL PROTECTED]
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[Mary Stickney]

thanks.  we are doing the data load today and will upgrade the MYODBC to
3.51 before we do it.  This will be a good test as the load takes 6 hours
on the current version.



Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 6:07 PM
To: Mary Stickney; [EMAIL PROTECTED]
Subject: Re: timing


Mary,

> The Production server has MYSQL 3.23 and an older version of MYODBC
then I
> have.

> They run in 8 minutes on my test server , about 4 minutes each.
Printing to
> a PDF driver.
> On the Production Server they take HOURS to run and print.

Maybe this won't help too much, but I've experienced that in some cases
MyODBC 2.50 is sooo much slower than MyODBC 3.51. An export I did from
MS Access to MySQL (some 1.5 million records) took hours with 2.50, and
only 3 minutes with 3.51.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Mary Stickney" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 30, 2003 3:08 PM
Subject: timing


>
> I have 2 Crystal Reports 8.5 (for the record I didn't make these
reports)
> that draw straight from the MYSQL
> database. They use only 1 table. I am calling them to print from VB
6.0
> Theses reports have lots of complex calculations in them, counts ,
sums and
> groups.
>
> My test server has the same code , same database , same indexes , same
> amount of memory as the Production server,
> My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
> installed on my test server.
>
> The Production server has MYSQL 3.23 and an older version of MYODBC
then I
> have.
> No Crystal Reports installed. And the server is a faster machine then
mine.
>
>
> They run in 8 minutes on my test server , about 4 minutes each.
Printing to
> a PDF driver.
>
> On the Production Server they take HOURS to run and print.
>
> Any ideas...
>
>
>
>
> Mary Stickney
> TAG-TMI
> Data Warehouse Programmer
> 402-474-7612 x 3099
> [EMAIL PROTECTED]
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: timing

[Stefan Hinz, iConnect \(Berlin\)]

Mary,

> The Production server has MYSQL 3.23 and an older version of MYODBC
then I
> have.

> They run in 8 minutes on my test server , about 4 minutes each.
Printing to
> a PDF driver.
> On the Production Server they take HOURS to run and print.

Maybe this won't help too much, but I've experienced that in some cases
MyODBC 2.50 is sooo much slower than MyODBC 3.51. An export I did from
MS Access to MySQL (some 1.5 million records) took hours with 2.50, and
only 3 minutes with 3.51.

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  Geschäftsführer / CEO iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: "Mary Stickney" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 30, 2003 3:08 PM
Subject: timing


>
> I have 2 Crystal Reports 8.5 (for the record I didn't make these
reports)
> that draw straight from the MYSQL
> database. They use only 1 table. I am calling them to print from VB
6.0
> Theses reports have lots of complex calculations in them, counts ,
sums and
> groups.
>
> My test server has the same code , same database , same indexes , same
> amount of memory as the Production server,
> My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
> installed on my test server.
>
> The Production server has MYSQL 3.23 and an older version of MYODBC
then I
> have.
> No Crystal Reports installed. And the server is a faster machine then
mine.
>
>
> They run in 8 minutes on my test server , about 4 minutes each.
Printing to
> a PDF driver.
>
> On the Production Server they take HOURS to run and print.
>
> Any ideas...
>
>
>
>
> Mary Stickney
> TAG-TMI
> Data Warehouse Programmer
> 402-474-7612 x 3099
> [EMAIL PROTECTED]
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[Mary Stickney]

oh and my test server is my PC , so I had all kinds o crap running
on it at the same time.



Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: Jennifer Goodie [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 1:57 PM
To: Mary Stickney; [EMAIL PROTECTED]
Subject: RE: timing


>They run in 8 minutes on my test server , about 4 minutes each. Printing to
>a PDF driver.

>On the Production Server they take HOURS to run and print.

The production server is probably trying to do other stuff at the same time,
making your reports slower.  A machine with one active connection will run
queries a lot quicker than an identical machine with 50 connections.  Think
about what happens when you try to do 50 things at once :)

Is the test server using a localhost connect and the production not?
Localhost is a lot faster than TCP/IP when running a large amount of
queries.

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 6:08 AM
To: [EMAIL PROTECTED]
Subject: timing



I have 2 Crystal Reports 8.5 (for the record I didn't make these reports)
that draw straight from the MYSQL
database. They use only 1 table. I am calling them to print from VB 6.0
Theses reports have lots of complex calculations in them, counts , sums and
groups.

My test server has the same code , same database , same indexes , same
amount of memory as the Production server,
My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
installed on my test server.

The Production server has MYSQL 3.23 and an older version of MYODBC then I
have.
No Crystal Reports installed. And the server is a faster machine then mine.


They run in 8 minutes on my test server , about 4 minutes each. Printing to
a PDF driver.

On the Production Server they take HOURS to run and print.

Any ideas...




Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[Mary Stickney]

The warehouse is updated once a month at night , with data from the
mainframe.  no records are updated or added to these databases in any other
way.
It is strictly a report database. They reports are ordered from a website.
A record is put into a database called ReportQueue the VB app checks the
queue
and runs the reports one at a time and emails them out.

Outlook Express is running in order to email the PDF files to the recipient.

I can understand it being SOME slower , but not hours slower.



Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: Jennifer Goodie [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 2:12 PM
To: Mary Stickney; mysql list
Subject: RE: timing


You are positive there is nothing else running while you are trying to do
your reports?  Have you tried keeping an eye on the processlist to see where
it is getting stuck and to make sure there is nothing else going on?  Where
does the production server get its data from?  Are their updates going on
while you are running your reports?

Is the production server tuned for the type of complex queries you are
running?

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 12:02 PM
To: Jennifer Goodie; [EMAIL PROTECTED]
Subject: RE: timing



no it is dedicated to the DataWarehouse Report Processor.
runs one report at a time from a ReportQueue.

both have a local host.

Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: Jennifer Goodie [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 1:57 PM
To: Mary Stickney; [EMAIL PROTECTED]
Subject: RE: timing


>They run in 8 minutes on my test server , about 4 minutes each. Printing to
>a PDF driver.

>On the Production Server they take HOURS to run and print.

The production server is probably trying to do other stuff at the same time,
making your reports slower.  A machine with one active connection will run
queries a lot quicker than an identical machine with 50 connections.  Think
about what happens when you try to do 50 things at once :)

Is the test server using a localhost connect and the production not?
Localhost is a lot faster than TCP/IP when running a large amount of
queries.

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 6:08 AM
To: [EMAIL PROTECTED]
Subject: timing



I have 2 Crystal Reports 8.5 (for the record I didn't make these reports)
that draw straight from the MYSQL
database. They use only 1 table. I am calling them to print from VB 6.0
Theses reports have lots of complex calculations in them, counts , sums and
groups.

My test server has the same code , same database , same indexes , same
amount of memory as the Production server,
My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
installed on my test server.

The Production server has MYSQL 3.23 and an older version of MYODBC then I
have.
No Crystal Reports installed. And the server is a faster machine then mine.


They run in 8 minutes on my test server , about 4 minutes each. Printing to
a PDF driver.

On the Production Server they take HOURS to run and print.

Any ideas...




Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[Mary Stickney]

one other thing , the girl that made them did it with an Access database
copy of the warehouse.
I changed the connection to the real Datawarehouse.

if that makes any difference


Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: Jennifer Goodie [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 1:57 PM
To: Mary Stickney; [EMAIL PROTECTED]
Subject: RE: timing


>They run in 8 minutes on my test server , about 4 minutes each. Printing to
>a PDF driver.

>On the Production Server they take HOURS to run and print.

The production server is probably trying to do other stuff at the same time,
making your reports slower.  A machine with one active connection will run
queries a lot quicker than an identical machine with 50 connections.  Think
about what happens when you try to do 50 things at once :)

Is the test server using a localhost connect and the production not?
Localhost is a lot faster than TCP/IP when running a large amount of
queries.

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 6:08 AM
To: [EMAIL PROTECTED]
Subject: timing



I have 2 Crystal Reports 8.5 (for the record I didn't make these reports)
that draw straight from the MYSQL
database. They use only 1 table. I am calling them to print from VB 6.0
Theses reports have lots of complex calculations in them, counts , sums and
groups.

My test server has the same code , same database , same indexes , same
amount of memory as the Production server,
My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
installed on my test server.

The Production server has MYSQL 3.23 and an older version of MYODBC then I
have.
No Crystal Reports installed. And the server is a faster machine then mine.


They run in 8 minutes on my test server , about 4 minutes each. Printing to
a PDF driver.

On the Production Server they take HOURS to run and print.

Any ideas...




Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[Jennifer Goodie]

You are positive there is nothing else running while you are trying to do
your reports?  Have you tried keeping an eye on the processlist to see where
it is getting stuck and to make sure there is nothing else going on?  Where
does the production server get its data from?  Are their updates going on
while you are running your reports?

Is the production server tuned for the type of complex queries you are
running?

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 12:02 PM
To: Jennifer Goodie; [EMAIL PROTECTED]
Subject: RE: timing



no it is dedicated to the DataWarehouse Report Processor.
runs one report at a time from a ReportQueue.

both have a local host.

Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: Jennifer Goodie [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 1:57 PM
To: Mary Stickney; [EMAIL PROTECTED]
Subject: RE: timing


>They run in 8 minutes on my test server , about 4 minutes each. Printing to
>a PDF driver.

>On the Production Server they take HOURS to run and print.

The production server is probably trying to do other stuff at the same time,
making your reports slower.  A machine with one active connection will run
queries a lot quicker than an identical machine with 50 connections.  Think
about what happens when you try to do 50 things at once :)

Is the test server using a localhost connect and the production not?
Localhost is a lot faster than TCP/IP when running a large amount of
queries.

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 6:08 AM
To: [EMAIL PROTECTED]
Subject: timing



I have 2 Crystal Reports 8.5 (for the record I didn't make these reports)
that draw straight from the MYSQL
database. They use only 1 table. I am calling them to print from VB 6.0
Theses reports have lots of complex calculations in them, counts , sums and
groups.

My test server has the same code , same database , same indexes , same
amount of memory as the Production server,
My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
installed on my test server.

The Production server has MYSQL 3.23 and an older version of MYODBC then I
have.
No Crystal Reports installed. And the server is a faster machine then mine.


They run in 8 minutes on my test server , about 4 minutes each. Printing to
a PDF driver.

On the Production Server they take HOURS to run and print.

Any ideas...




Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[Mary Stickney]

no it is dedicated to the DataWarehouse Report Processor.
runs one report at a time from a ReportQueue.

both have a local host.

Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]

-Original Message-
From: Jennifer Goodie [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 1:57 PM
To: Mary Stickney; [EMAIL PROTECTED]
Subject: RE: timing


>They run in 8 minutes on my test server , about 4 minutes each. Printing to
>a PDF driver.

>On the Production Server they take HOURS to run and print.

The production server is probably trying to do other stuff at the same time,
making your reports slower.  A machine with one active connection will run
queries a lot quicker than an identical machine with 50 connections.  Think
about what happens when you try to do 50 things at once :)

Is the test server using a localhost connect and the production not?
Localhost is a lot faster than TCP/IP when running a large amount of
queries.

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 6:08 AM
To: [EMAIL PROTECTED]
Subject: timing



I have 2 Crystal Reports 8.5 (for the record I didn't make these reports)
that draw straight from the MYSQL
database. They use only 1 table. I am calling them to print from VB 6.0
Theses reports have lots of complex calculations in them, counts , sums and
groups.

My test server has the same code , same database , same indexes , same
amount of memory as the Production server,
My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
installed on my test server.

The Production server has MYSQL 3.23 and an older version of MYODBC then I
have.
No Crystal Reports installed. And the server is a faster machine then mine.


They run in 8 minutes on my test server , about 4 minutes each. Printing to
a PDF driver.

On the Production Server they take HOURS to run and print.

Any ideas...




Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: timing

[Jennifer Goodie]

>They run in 8 minutes on my test server , about 4 minutes each. Printing to
>a PDF driver.

>On the Production Server they take HOURS to run and print.

The production server is probably trying to do other stuff at the same time,
making your reports slower.  A machine with one active connection will run
queries a lot quicker than an identical machine with 50 connections.  Think
about what happens when you try to do 50 things at once :)

Is the test server using a localhost connect and the production not?
Localhost is a lot faster than TCP/IP when running a large amount of
queries.

-Original Message-
From: Mary Stickney [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 6:08 AM
To: [EMAIL PROTECTED]
Subject: timing



I have 2 Crystal Reports 8.5 (for the record I didn't make these reports)
that draw straight from the MYSQL
database. They use only 1 table. I am calling them to print from VB 6.0
Theses reports have lots of complex calculations in them, counts , sums and
groups.

My test server has the same code , same database , same indexes , same
amount of memory as the Production server,
My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal
installed on my test server.

The Production server has MYSQL 3.23 and an older version of MYODBC then I
have.
No Crystal Reports installed. And the server is a faster machine then mine.


They run in 8 minutes on my test server , about 4 minutes each. Printing to
a PDF driver.

On the Production Server they take HOURS to run and print.

Any ideas...




Mary Stickney
TAG-TMI
Data Warehouse Programmer
402-474-7612 x 3099
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php