Last attempt

--=======AVGMAIL-41E523F90D02=======
Content-Type: multipart/alternative; boundary="----=_NextPart_000_0003_01C4F8BA.2A917DC0"

------=_NextPart_000_0003_01C4F8BA.2A917DC0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I'm at a loss here, we need to decide on a db platform for our software, =
MySQL looks like the winner, but this slow query is unnacceptable...

Okay, I just done a basic query of my mail table in MySQL Control Center

SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority, =
FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead, =
FlagFollowUpBy
FROM officium_mailinbox WHERE Retained =3D 0 AND isTempMimeMessage =3D 0 =
and Location =3D 'Inbox' LIMIT 10000

It took 17.22 seconds to return the results (3222 rows)

I have even created an index specifically for this query, i.e a =
multi-column index with the above 3 fields in it.

Now my questions....

Is this performance normal for mySQL ? Are others out there getting this =
much data in a flash?

What could I possibly do to bring this time to under 7 seconds? Or am I =
asking too much of MySQL?
Am I better off using another database product such as SQL Server?

Could somebody think of anything else at all? I read through the whole =
optimisation section again, made a few tweaks to my.ini file but still =
this is ridiculous performance...

Any ideas would be greatly appreciated, please help!

Here is my updated .ini file...
[mysqld]
port =3D 20101
basedir=3DC:/database
datadir=3DC:/database/data
default-character-set=3Dlatin1
default-storage-engine=3DMYISAM
max_connections=3D200
max_allowed_packet=3D64M
query_cache_size=3D64M
table_cache=3D1520
tmp_table_size=3D16M
thread_cache=3D64
myisam_max_sort_file_size=3D100G
myisam_max_extra_sort_file_size=3D100G
myisam_sort_buffer_size=3D80M
key_buffer_size=3D148M
read_buffer_size=3D256K
read_rnd_buffer_size=3D256K
sort_buffer_size=3D256K
skip-innodb




------=_NextPart_000_0003_01C4F8BA.2A917DC0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

RE: Last attempt

------=_NextPart_000_000C_01C4F882.BEA444D0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_000D_01C4F882.BEA6B5D0"


------=_NextPart_001_000D_01C4F882.BEA6B5D0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

1) What kind of machine are you running this on? Is it a single or a dual
CPU system? Hard drives (i.e. do you have a system drive and a data drive)?
Asseblief, ek weet dat jy dis vraag gebeantwoord, maar ek kan nie onthou
nie. Jy kan Afrikaans praat.

2) Why are you not running this on Linux? Although I have run MySQL on both
Linux and Windows, Linux has a lot less peripheral "crap" running in the
background than Windows does. May affect performance.

I was running statistical analysis on 130 million records in <90 seconds, so
I can't see why your limit statement is taking so long unless you are using
BLOG and LONGTEXT fields in your column types.

May also be that your pagefile on your Windows box is on the same physical
drive as your MySQL data and indexes, which REALLY slows down performance.

Just some thoughts...

J.R.

_____

From: Greg Quinn [mailto:greg@i-online.co.za]
Sent: Wednesday, January 12, 2005 8:20 AM
To: win32@lists.mysql.com
Subject: Last attempt


I'm at a loss here, we need to decide on a db platform for our software,
MySQL looks like the winner, but this slow query is unnacceptable...

Okay, I just done a basic query of my mail table in MySQL Control Center

SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority,
FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,
FlagFollowUpBy
FROM officium_mailinbox WHERE Retained = 0 AND isTempMimeMessage = 0 and
Location = 'Inbox' LIMIT 10000

It took 17.22 seconds to return the results (3222 rows)

I have even created an index specifically for this query, i.e a multi-column
index with the above 3 fields in it.

Now my questions....

Is this performance normal for mySQL ? Are others out there getting this
much data in a flash?

What could I possibly do to bring this time to under 7 seconds? Or am I
asking too much of MySQL?
Am I better off using another database product such as SQL Server?

Could somebody think of anything else at all? I read through the whole
optimisation section again, made a few tweaks to my.ini file but still this
is ridiculous performance...

Any ideas would be greatly appreciated, please help!

Here is my updated .ini file...
[mysqld]
port = 20101
basedir=C:/database
datadir=C:/database/data
default-character-set=latin1
default-storage-engine=MYISAM
max_connections=200
max_allowed_packet=64M
query_cache_size=64M
table_cache=1520
tmp_table_size=16M
thread_cache=64
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=80M
key_buffer_size=148M
read_buffer_size=256K
read_rnd_buffer_size=256K
sort_buffer_size=256K
skip-innodb





------=_NextPart_001_000D_01C4F882.BEA6B5D0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable



charset=3Dus-ascii">

Re: Last attempt

--=======AVGMAIL-41E52E9F7E90=======
Content-Type: multipart/alternative; boundary="----=_NextPart_000_0041_01C4F8C0.83534BE0"

------=_NextPart_000_0041_01C4F8C0.83534BE0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi JR, here are the machine specs again...

1) What kind of machine are you running this on? Is it a single or a =
dual CPU system? Hard drives (i.e. do you have a system drive and a data =
drive)?=20

Single P4 2.8 Ghz with HT. 1 Gig Ram. 180 Gig 7200rpm IDE drive.

Our software is basically an asp.net web-based business app, and mail =
server all in one.
Our server is not that busy, serves a few web pages, and about 10 =
subscribers email accounts.
The mail message headers are pushed through to an ActiveX control, but =
the bottleneck is definately MySQL doing the data read...

2) Why are you not running this on Linux? Although I have run MySQL on =
both Linux and Windows, Linux has a lot less peripheral "crap" running =
in the background than Windows does. May affect performance.

We will be porting to Linux, but all code is currently in .NET, will =
move over to Mono or similar in the future.

I was running statistical analysis on 130 million records in <90 =
seconds, so I can't see why your limit statement is taking so long =
unless you are using BLOG and LONGTEXT fields in your column types.

I have LONGTEXT in my table, but they are not included in the query. I =
removed the LONGTEXT field but it didn't make a performance difference, =
even though the table file size went from 2 Gigs to about 10 Meg.

May also be that your pagefile on your Windows box is on the same =
physical drive as your MySQL data and indexes, which REALLY slows down =
performance.

Yip it is, the thing is we need to cater for businesses having desktop =
PC's as servers. So we are pretty screwed when it comes to making =
hardware choices.



----- Original Message -----=20
From: J.R. Bullington=20
To: win32@lists.mysql.com=20
Cc: 'Greg Quinn'=20
Sent: Wednesday, January 12, 2005 3:43 PM
Subject: RE: Last attempt


1) What kind of machine are you running this on? Is it a single or a =
dual CPU system? Hard drives (i.e. do you have a system drive and a data =
drive)?=20
Asseblief, ek weet dat jy dis vraag gebeantwoord, maar ek kan nie =
onthou nie. Jy kan Afrikaans praat.

2) Why are you not running this on Linux? Although I have run MySQL on =
both Linux and Windows, Linux has a lot less peripheral "crap" running =
in the background than Windows does. May affect performance.

I was running statistical analysis on 130 million records in <90 =
seconds, so I can't see why your limit statement is taking so long =
unless you are using BLOG and LONGTEXT fields in your column types.

May also be that your pagefile on your Windows box is on the same =
physical drive as your MySQL data and indexes, which REALLY slows down =
performance.

Just some thoughts...

J.R.



------------------------------------------------------------ -------------=
-----
From: Greg Quinn [mailto:greg@i-online.co.za]=20
Sent: Wednesday, January 12, 2005 8:20 AM
To: win32@lists.mysql.com
Subject: Last attempt


I'm at a loss here, we need to decide on a db platform for our =
software, MySQL looks like the winner, but this slow query is =
unnacceptable...

Okay, I just done a basic query of my mail table in MySQL Control =
Center

SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority, =
FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead, =
FlagFollowUpBy
FROM officium_mailinbox WHERE Retained =3D 0 AND isTempMimeMessage =3D =
0 and Location =3D 'Inbox' LIMIT 10000

It took 17.22 seconds to return the results (3222 rows)

I have even created an index specifically for this query, i.e a =
multi-column index with the above 3 fields in it.

Now my questions....

Is this performance normal for mySQL ? Are others out there getting =
this much data in a flash?

What could I possibly do to bring this time to under 7 seconds? Or am =
I asking too much of MySQL?
Am I better off using another database product such as SQL Server?

Could somebody think of anything else at all? I read through the whole =
optimisation section again, made a few tweaks to my.ini file but still =
this is ridiculous performance...

Any ideas would be greatly appreciated, please help!

Here is my updated .ini file...
[mysqld]
port =3D 20101
basedir=3DC:/database
datadir=3DC:/database/data
default-character-set=3Dlatin1
default-storage-engine=3DMYISAM
max_connections=3D200
max_allowed_packet=3D64M
query_cache_size=3D64M
table_cache=3D1520
tmp_table_size=3D16M
thread_cache=3D64
myisam_max_sort_file_size=3D100G
myisam_max_extra_sort_file_size=3D100G
myisam_sort_buffer_size=3D80M
key_buffer_size=3D148M
read_buffer_size=3D256K
read_rnd_buffer_size=3D256K
sort_buffer_size=3D256K
skip-innodb




------=_NextPart_000_0041_01C4F8C0.83534BE0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

----- Original Message -----

style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black">From:=20
href=3D"mailto:bullijr@innovatim.com">J.R.=20
Bullington

To: title=3Dwin32@lists.mysql.com=20
href=3D"mailto:win32@lists.mysql.com">win32@lists.mysql.com

Cc: title=3Dgreg@i-online.co.za=20
href=3D"mailto:greg@i-online.co.za">'Greg Quinn'

Sent: Wednesday, January 12, =
2005 3:43=20
PM

Subject: RE: Last attempt

face=3DVerdana=20
color=3D#0000ff size=3D1>1) What kind of machine are you running this =
on? Is it a=20
single or a dual CPU system? Hard drives (i.e. do you have a system =
drive and=20
a data drive)?

face=3DVerdana=20
color=3D#0000ff size=3D1>Asseblief, ek weet dat jy dis vraag =
gebeantwoord, maar ek=20
kan nie onthou nie. Jy kan Afrikaans praat.

face=3DVerdana=20
color=3D#0000ff size=3D1> 

face=3DVerdana=20
color=3D#0000ff size=3D1>2) Why are you not running this on Linux? =
Although I have=20
run MySQL on both Linux and Windows, Linux has a lot less =
peripheral=20
"crap" running in the background than Windows does. May affect=20
performance.

face=3DVerdana=20
color=3D#0000ff size=3D1> 

face=3DVerdana=20
color=3D#0000ff size=3D1>I was running statistical analysis on 130 =
million records=20
in <90 seconds, so I can't see why your limit statement is taking =
so long=20
unless you are using BLOG and LONGTEXT fields in your column=20
types.

face=3DVerdana=20
color=3D#0000ff size=3D1> 

face=3DVerdana=20
color=3D#0000ff size=3D1>May also be that your pagefile on your =
Windows box=20
is on the same physical drive as your MySQL data and indexes, which =
REALLY=20
slows down performance.

face=3DVerdana=20
color=3D#0000ff size=3D1> 

face=3DVerdana=20
color=3D#0000ff size=3D1>Just some thoughts...

face=3DVerdana=20
color=3D#0000ff size=3D1> 

face=3DVerdana=20
color=3D#0000ff size=3D1>J.R.

---

From: Greg Quinn =
[mailto:greg@i-online.co.za]=20

Sent: Wednesday, January 12, 2005 8:20 AM
To:=20
win32@lists.mysql.com
Subject: Last =
attempt

I'm at a loss here, we need to decide =
on a db=20
platform for our software, MySQL looks like the winner, but this slow =
query is=20
unnacceptable...

 

Okay, I just done a basic query =
of my mail=20
table in MySQL Control Center

 

SELECT InBoxMailID, repliedMessages,=20
forwardedMessages, thePriority, FlagStatus, Attachments, theFrom, =
theSubject,=20
receivedDate, isRead, FlagFollowUpBy
FROM officium_mailinbox WHERE =
Retained=20
=3D 0 AND isTempMimeMessage =3D 0 and Location =3D 'Inbox' LIMIT =
10000

 

It took 17.22 seconds to return =
the results=20
(3222 rows)

 

I have even created an index =
specifically for=20
this query, i.e a multi-column index with the above 3 fields in=20
it.

 

Now my questions....

 

Is this performance normal for mySQL =
? Are others=20
out there getting this much data in a flash?

 

What could I possibly do to bring =
this time to=20
under 7 seconds? Or am I asking too much of MySQL?

Am I better off using another =
database product=20
such as SQL Server?

 

Could somebody think of anything else =
at all? I=20
read through the whole optimisation section again, made a few tweaks =
to my.ini=20
file but still this is ridiculous performance...

 

Any ideas would be greatly =
appreciated, please=20
help!

 

Here is my updated .ini =
file...

[mysqld]
port =
=
20101
basedir=3DC:/database
datadir=3DC:/database/data
default-c=
haracter-set=3Dlatin1
default-storage-engine=3DMYISAM
max_connectio=
ns=3D200
max_allowed_packet=3D64M
query_cache_size=3D64M
table_c=
ache=3D1520
tmp_table_size=3D16M
thread_cache=3D64
myisam_max_so=
rt_file_size=3D100G
myisam_max_extra_sort_file_size=3D100G
myisam_s=
ort_buffer_size=3D80M
key_buffer_size=3D148M
read_buffer_size=3D256=
K
read_rnd_buffer_size=3D256K
sort_buffer_size=3D256K
skip-innod=
b

 

 

 

size=3D2> 

RE: Last attempt

------=_NextPart_000_00BC_01C4F881.72260360
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

What is the EXPLAIN for the query? What is the table definition? Do you
really want to return 3222 rows to a user at once?
=20
=20


--
Mike Hillyer, Technical Writer
MySQL AB, www.mysql.com
Office: +1 403-380-6535
Mobile: +1 403-330-0870

"The Open Source movement has become a major force across the software
industry, and MySQL is the world's most popular Open Source database."
--Fortune Magazine


-----Original Message-----
From: Greg Quinn [mailto:greg@i-online.co.za]=20
Sent: Wednesday, January 12, 2005 6:20 AM
To: win32@lists.mysql.com
Subject: Last attempt


I'm at a loss here, we need to decide on a db platform for our software,
MySQL looks like the winner, but this slow query is unnacceptable...
=20
Okay, I just done a basic query of my mail table in MySQL Control Center
=20
SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority,
FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,
FlagFollowUpBy
FROM officium_mailinbox WHERE Retained =3D 0 AND isTempMimeMessage =3D 0 =
and
Location =3D 'Inbox' LIMIT 10000
=20
It took 17.22 seconds to return the results (3222 rows)
=20
I have even created an index specifically for this query, i.e a =
multi-column
index with the above 3 fields in it.
=20
Now my questions....
=20
Is this performance normal for mySQL ? Are others out there getting this
much data in a flash?
=20
What could I possibly do to bring this time to under 7 seconds? Or am I
asking too much of MySQL?
Am I better off using another database product such as SQL Server?
=20
Could somebody think of anything else at all? I read through the whole
optimisation section again, made a few tweaks to my.ini file but still =
this
is ridiculous performance...
=20
Any ideas would be greatly appreciated, please help!
=20
Here is my updated .ini file...
[mysqld]
port =3D 20101
basedir=3DC:/database
datadir=3DC:/database/data
default-character-set=3Dlatin1
default-storage-engine=3DMYISAM
max_connections=3D200
max_allowed_packet=3D64M
query_cache_size=3D64M
table_cache=3D1520
tmp_table_size=3D16M
thread_cache=3D64
myisam_max_sort_file_size=3D100G
myisam_max_extra_sort_file_size=3D100G
myisam_sort_buffer_size=3D80M
key_buffer_size=3D148M
read_buffer_size=3D256K
read_rnd_buffer_size=3D256K
sort_buffer_size=3D256K
skip-innodb
=20
=20
=20
=20


------=_NextPart_000_00BC_01C4F881.72260360--

Re: Last attempt

I would bet that your SQL query is returning as fast as it can, but
keep in mind that even if you're not dealing with the data (showing
just small parts of it on your app frontend), all of it is comming
from the server to your app, so, based on the information you gave, I
would say that MySQL is not responsable for the slow response,
instead, what could be limiting your response is the ammount of data
and the time to receive, process and show it on your app.

Try selecting only small parts of the table and only demand the rest
of the data once the user has asked for it... (assuming its a kind of
webmail app) You would show and index of mesgs, and clicking on it
you'll select the rest of the data and show it.

Its just my 2 cents, I can't SEE your app, so, its hard to tell.

Best regards,

On Wed, 12 Jan 2005 08:33:48 -0700, Mike Hillyer wrote:
> What is the EXPLAIN for the query? What is the table definition? Do you
> really want to return 3222 rows to a user at once?
>
> --
> Mike Hillyer, Technical Writer
> MySQL AB, www.mysql.com
> Office: +1 403-380-6535
> Mobile: +1 403-330-0870
>
> "The Open Source movement has become a major force across the software
> industry, and MySQL is the world's most popular Open Source database."
> --Fortune Magazine
>
>
> -----Original Message-----
> From: Greg Quinn [mailto:greg@i-online.co.za]
> Sent: Wednesday, January 12, 2005 6:20 AM
> To: win32@lists.mysql.com
> Subject: Last attempt
>
> I'm at a loss here, we need to decide on a db platform for our software,
> MySQL looks like the winner, but this slow query is unnacceptable...
>
> Okay, I just done a basic query of my mail table in MySQL Control Center
>
> SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority,
> FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,
> FlagFollowUpBy
> FROM officium_mailinbox WHERE Retained = 0 AND isTempMimeMessage = 0 and
> Location = 'Inbox' LIMIT 10000
>
> It took 17.22 seconds to return the results (3222 rows)
>
> I have even created an index specifically for this query, i.e a multi-column
> index with the above 3 fields in it.
>
> Now my questions....
>
> Is this performance normal for mySQL ? Are others out there getting this
> much data in a flash?
>
> What could I possibly do to bring this time to under 7 seconds? Or am I
> asking too much of MySQL?
> Am I better off using another database product such as SQL Server?
>
> Could somebody think of anything else at all? I read through the whole
> optimisation section again, made a few tweaks to my.ini file but still this
> is ridiculous performance...
>
> Any ideas would be greatly appreciated, please help!
>
> Here is my updated .ini file...
> [mysqld]
> port = 20101
> basedir=C:/database
> datadir=C:/database/data
> default-character-set=latin1
> default-storage-engine=MYISAM
> max_connections=200
> max_allowed_packet=64M
> query_cache_size=64M
> table_cache=1520
> tmp_table_size=16M
> thread_cache=64
> myisam_max_sort_file_size=100G
> myisam_max_extra_sort_file_size=100G
> myisam_sort_buffer_size=80M
> key_buffer_size=148M
> read_buffer_size=256K
> read_rnd_buffer_size=256K
> sort_buffer_size=256K
> skip-innodb
>
>


--
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re : Last attempt

--=======AVGMAIL-41E5509446AE=======
Content-Type: multipart/alternative; boundary="----=_NextPart_000_0003_01C4F8D4.C0FD7DD0"

------=_NextPart_000_0003_01C4F8D4.C0FD7DD0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

No, the bottleneck is the query data read. I have rebooted my PC, and =
accessed a query that is in the query cache and the results appear in =
less than 0.5 seconds in my app.

The app is web-based, but all the user mail message headers are bound to =
an ActiveX control.=20

-----Original Message-----
From: Daniel da Veiga
To: MySQL Win32 List,
Sent: 1/12/2005 6:25:54 PM
Subject: Re: Last attempt

I would bet that your SQL query is returning as fast as it can, but
keep in mind that even if you're not dealing with the data (showing
just small parts of it on your app frontend), all of it is comming
from the server to your app, so, based on the information you gave, I
would say that MySQL is not responsable for the slow response,
instead, what could be limiting your response is the ammount of data
and the time to receive, process and show it on your app.

Try selecting only small parts of the table and only demand the rest
of the data once the user has asked for it... (assuming its a kind of
webmail app) You would show and index of mesgs, and clicking on it
you'll select the rest of the data and show it.

Its just my 2 cents, I can't SEE your app, so, its hard to tell.
------=_NextPart_000_0003_01C4F8D4.C0FD7DD0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re : Last attempt

--=_alternative 005E3F9C85256F87_=
Content-Type: text/plain; charset="US-ASCII"

"Greg Quinn" wrote on 01/12/2005 11:30:12 AM:

> No, the bottleneck is the query data read. I have rebooted my PC,
> and accessed a query that is in the query cache and the results
> appear in less than 0.5 seconds in my app.
>
> The app is web-based, but all the user mail message headers are
> bound to an ActiveX control.


Let me see if I understand your architecture:
MySQL data store
ODBC
ADO
ASP (VBScript/JScript)

Where does your ActiveX control fit in? What does it do? Can you change
its behavior? Does it need to read ALL of the user's headers EVERY TIME it
does what it does or could it do something less DB intensive (like a
"SELECT count(1) from .... WHERE ...."). If you can make each page
database friendly, your entire app will become more responsive. I know of
very few effective web-apps that deal with thousands of lines of data on a
single page. It usually creates information overload and your users will
almost always hate it. However, if the user has ASKED to see ALL of his
headers, and the user KNOWS that he has thousands of messages, then he is
more than likely willing to wait a few extra seconds for all of that data.

Also, if you are not preserving some "state" between certain pages, your
control (depending on what it does) may need to "work from scratch" every
time that page is requested. There are several ways to cache information
(not necessarily through IIS) so that the control doesn't need to perform
a full table read every time it needs just one record. Again, considering
this kind of optimization depends heavily on what your control is doing
and especially if you can change its behavior in any way.

What I am trying to suggest is that you may be abusing the disks for no
useful purpose. If you are only showing the user the headers from messages
100-199 then use a LIMIT clause to only pull those into your app. If you
only need 3 fields from the header information, only query for those
fields. In this case the phrase "less is more" implies that the less data
you manage in any single read, the more pages you can present per second.
That means that your application will seem more responsive and will
generally provide a better user experience.

If you manually execute the same query using the MySQL command-line
client, how long does the database take to respond? (count only until you
see data _start_ to scroll, not until you reach the end of the scroll).
Generally, if you can tune your database to perform well when working
through the command-line client, then the same queries when presented from
your application should also perform well.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


> -----Original Message-----
> From: Daniel da Veiga
> To: MySQL Win32 List,
> Sent: 1/12/2005 6:25:54 PM
> Subject: Re: Last attempt
>
> I would bet that your SQL query is returning as fast as it can, but
> keep in mind that even if you're not dealing with the data (showing
> just small parts of it on your app frontend), all of it is comming
> from the server to your app, so, based on the information you gave, I
> would say that MySQL is not responsable for the slow response,
> instead, what could be limiting your response is the ammount of data
> and the time to receive, process and show it on your app.
>
> Try selecting only small parts of the table and only demand the rest
> of the data once the user has asked for it... (assuming its a kind of
> webmail app) You would show and index of mesgs, and clicking on it
> you'll select the rest of the data and show it.
>
> Its just my 2 cents, I can't SEE your app, so, its hard to tell.No
> virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 1/10/2005
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
--=_alternative 005E3F9C85256F87_=--

Re: Re : Last attempt

Greetings again,

On Wed, 12 Jan 2005 12:06:54 -0500, SGreen@unimin.com wrote:
> "Greg Quinn" wrote on 01/12/2005 11:30:12 AM:
>
> > No, the bottleneck is the query data read. I have rebooted my PC,
> > and accessed a query that is in the query cache and the results
> > appear in less than 0.5 seconds in my app.
> >

Yes, I understand, but you must see that between your server cache
(feeded by ODBC) and the MySQL Server, there's a long path, ODBC speed
is limited by a lot of stuff, and its a large ammount of data you're
requesting... Try runnning the same query at command prompt like this:

C:\MySQL\bin> mysql -D -e "" >

and see how long will it take... This way you disk will work (not your
screen) wich is a lot faster... But Shawn's state about the console
query returns (at the end of the WHOLE screen of data) the time spent
by the server processing the query like this:

19827 row in set (1.36 sec)

> > The app is web-based, but all the user mail message headers are
> > bound to an ActiveX control.
>
>
> Let me see if I understand your architecture:
> MySQL data store
> ODBC
> ADO
> ASP (VBScript/JScript)
>
> Where does your ActiveX control fit in? What does it do? Can you change
> its behavior? Does it need to read ALL of the user's headers EVERY TIME it
> does what it does or could it do something less DB intensive (like a
> "SELECT count(1) from .... WHERE ...."). If you can make each page
> database friendly, your entire app will become more responsive. I know of
> very few effective web-apps that deal with thousands of lines of data on a
> single page. It usually creates information overload and your users will
> almost always hate it. However, if the user has ASKED to see ALL of his
> headers, and the user KNOWS that he has thousands of messages, then he is
> more than likely willing to wait a few extra seconds for all of that data.
>

I completely agree with Shawn, your app must use some "optimization"
too. If the query I was talking about before (the one at the console)
takes at least HALF the time your web app does, it means your app is
the bottleneck.

> Also, if you are not preserving some "state" between certain pages, your
> control (depending on what it does) may need to "work from scratch" every
> time that page is requested. There are several ways to cache information
> (not necessarily through IIS) so that the control doesn't need to perform
> a full table read every time it needs just one record. Again, considering
> this kind of optimization depends heavily on what your control is doing
> and especially if you can change its behavior in any way.
>

It's really hard for a web based app to completely get data and at the
first refresh be forced to do the whole process again, even if you're
testing the app in a LAN based connection, when your app goes "out" to
the real world, you may find it just too slow over a connection that
has any bandwidth limitation.

> What I am trying to suggest is that you may be abusing the disks for no
> useful purpose. If you are only showing the user the headers from messages
> 100-199 then use a LIMIT clause to only pull those into your app. If you
> only need 3 fields from the header information, only query for those
> fields. In this case the phrase "less is more" implies that the less data
> you manage in any single read, the more pages you can present per second.
> That means that your application will seem more responsive and will
> generally provide a better user experience.
>
> If you manually execute the same query using the MySQL command-line
> client, how long does the database take to respond? (count only until you
> see data _start_ to scroll, not until you reach the end of the scroll).
> Generally, if you can tune your database to perform well when working
> through the command-line client, then the same queries when presented from
> your application should also perform well.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
> > -----Original Message-----
> > From: Daniel da Veiga
> > To: MySQL Win32 List,
> > Sent: 1/12/2005 6:25:54 PM
> > Subject: Re: Last attempt
> >
> > I would bet that your SQL query is returning as fast as it can, but
> > keep in mind that even if you're not dealing with the data (showing
> > just small parts of it on your app frontend), all of it is comming
> > from the server to your app, so, based on the information you gave, I
> > would say that MySQL is not responsable for the slow response,
> > instead, what could be limiting your response is the ammount of data
> > and the time to receive, process and show it on your app.
> >
> > Try selecting only small parts of the table and only demand the rest
> > of the data once the user has asked for it... (assuming its a kind of
> > webmail app) You would show and index of mesgs, and clicking on it
> > you'll select the rest of the data and show it.
> >
> > Its just my 2 cents, I can't SEE your app, so, its hard to tell.No
> > virus found in this outgoing message.
> > Checked by AVG Anti-Virus.
> > Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 1/10/2005
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>


--
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Re : Last attempt

> > The app is web-based, but all the user mail message headers are
> > bound to an ActiveX control.
>

Keep in mind that the ActiveX control runs on the *client*. All the data
is getting sent to the client. This may actually end up being a lot
more data that you think.

I recommend using pure ASP and steer away from ActiveX if you are
concerned about performance. A nice benefit would then be that the app
would theoretically work with any browser.

-Kevin

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Questions regarding stored procedures

I am looking at moving towards MySql 5.0 and possibly moving much of my
queries into stored procedures. Has anyone experimented with this yet and
if so do you feel there is a performance increase or performance decrease in
using stored procedures in MySql?

Thanks in advance,
Dan


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Questions regarding stored procedures

--=_alternative 007577B385256F87_=
Content-Type: text/plain; charset="US-ASCII"

As long as you are comfortable with the fact that v5 is still a work in
progress, I can imagine that stored procedures will, at the very least,
save time and network bandwidth because they will help you to reduce the
number of round trips you need make between your app and the server in
order to perform certain tasks. Keep us informed of your results, ok?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Danny Willis" wrote on 01/12/2005
04:17:58 PM:

> I am looking at moving towards MySql 5.0 and possibly moving much of my
> queries into stored procedures. Has anyone experimented with this yet
and
> if so do you feel there is a performance increase or performance
decrease in
> using stored procedures in MySql?
>
> Thanks in advance,
> Dan
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 007577B385256F87_=--

Re: Re : Last attempt

At Wed, 12 Jan 2005 12:40:31 -0800,
PF: MySQL wrote:
>
>
> > > The app is web-based, but all the user mail message headers are
> > > bound to an ActiveX control.
> >
>
> Keep in mind that the ActiveX control runs on the *client*. All the data
> is getting sent to the client. This may actually end up being a lot
> more data that you think.

and if all teh data is getting sucked into the client then that is kind of
undesirable from a network citizen perspective. you would be eating up a big
wad of bandwidth for data that the reader may not be interested in.

and if your activex control is *successful* (ie installed in a lot of places)
that would be really consumptive.

> I recommend using pure ASP and steer away from ActiveX if you are
> concerned about performance. A nice benefit would then be that the app
> would theoretically work with any browser.
>
> -Kevin
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=john@utzweb.net
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Questions regarding stored procedures

------=_NextPart_000_00E1_01C4F8C3.C0CEDC70
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit

Absolutely. I'm going to be testing on a non live box at home so it'll give
me a chance to revamp my site and see how decent the new features are in
5.0. I am still about a week away from getting the box up and functioning
but as I plan for the build this was a major stepping stone. I will keep
the list informed of my findings.



Dan



_____

From: SGreen@unimin.com [mailto:SGreen@unimin.com]
Sent: Wednesday, January 12, 2005 4:21 PM
To: Danny Willis
Cc: win32@lists.mysql.com
Subject: Re: Questions regarding stored procedures




As long as you are comfortable with the fact that v5 is still a work in
progress, I can imagine that stored procedures will, at the very least, save
time and network bandwidth because they will help you to reduce the number
of round trips you need make between your app and the server in order to
perform certain tasks. Keep us informed of your results, ok?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Danny Willis" wrote on 01/12/2005
04:17:58 PM:

> I am looking at moving towards MySql 5.0 and possibly moving much of my
> queries into stored procedures. Has anyone experimented with this yet and
> if so do you feel there is a performance increase or performance decrease
in
> using stored procedures in MySql?
>
> Thanks in advance,
> Dan
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>


------=_NextPart_000_00E1_01C4F8C3.C0CEDC70--

RE: Questions regarding stored procedures

I should also note that upon successful home environment testing I plan to
move it into my beta testing group which currently includes about 500 people
so that'll give it a solid test as well.

Are there any known major issues with 5.0 that I should be concerned with
that are not in the documentation (I haven't finished reviewing all of it
yet but I thought I'd ask).



-----Original Message-----
From: Danny Willis [mailto:danny.willis@project-wildfire.com]
Sent: Wednesday, January 12, 2005 4:29 PM
To: SGreen@unimin.com
Cc: win32@lists.mysql.com
Subject: RE: Questions regarding stored procedures

Absolutely. I'm going to be testing on a non live box at home so it'll give
me a chance to revamp my site and see how decent the new features are in
5.0. I am still about a week away from getting the box up and functioning
but as I plan for the build this was a major stepping stone. I will keep
the list informed of my findings.



Dan



_____

From: SGreen@unimin.com [mailto:SGreen@unimin.com]
Sent: Wednesday, January 12, 2005 4:21 PM
To: Danny Willis
Cc: win32@lists.mysql.com
Subject: Re: Questions regarding stored procedures




As long as you are comfortable with the fact that v5 is still a work in
progress, I can imagine that stored procedures will, at the very least, save
time and network bandwidth because they will help you to reduce the number
of round trips you need make between your app and the server in order to
perform certain tasks. Keep us informed of your results, ok?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Danny Willis" wrote on 01/12/2005
04:17:58 PM:

> I am looking at moving towards MySql 5.0 and possibly moving much of my
> queries into stored procedures. Has anyone experimented with this yet and
> if so do you feel there is a performance increase or performance decrease
in
> using stored procedures in MySql?
>
> Thanks in advance,
> Dan
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Questions regarding stored procedures

Yes it can inprove performance if you have to produce multiple round trips
to the server. 5.02 is broke use 5.01 until 5.03 is released.

Here is an example SP...

CREATE PROCEDURE `traffic_tbl`.`sp_GetETA`(userETA INT, barcodeETA CHAR(30),
tagDate CHAR(8), adj INT,OUT eta CHAR(30))
BEGIN

DECLARE BarcodeFirst CHAR;
DECLARE BarcodeCurrent CHAR;
DECLARE SequenceCurrent INT;
DECLARE SeqValueETA INT;
DECLARE CountStops INT;
DECLARE d DATETIME;
DECLARE FirstDeliveryTime DATETIME;
DECLARE CurrentDeliveryTime DATETIME;
DECLARE CurrentETA DATETIME;
DECLARE TotMinutes DOUBLE;
DECLARE MinPerStop DOUBLE;

SET d = Now();
SET BarCodeFirst = '';
SET BarCodeCurrent = '';
SET SequenceCurrent = 0;
SET SeqValueETA = 0;
SET CountStops = 0;
SET FirstDeliveryTime = Now();
SET CurrentDeliveryTime = Now();

drop table if exists traffic_tbl.delivery_tracking_eta;

create table traffic_tbl.delivery_tracking_eta(
PACKAGEID int(11) not null,
RECID int(11),
RECTYPE int(11),
USERID int(11),
TIMETAG1 datetime,
TIMETAG2 datetime,
P_DATA1 varchar(20),
P_DATA2 varchar(20),
SEQVALUE int(11),
COMPANY_CD varchar(6),
PRODUCT_CD char(2),
IO_NUM varchar(4),
BARCODE varchar(25),
SEQCODE varchar(6),
SEQUENCED_TRIP datetime,
PRIMARY KEY (PACKAGEID),
KEY CoCode (COMPANY_CD, PRODUCT_CD)) TYPE = HEAP ;

insert into traffic_tbl.delivery_tracking_eta(
PACKAGEID,
RECID,
RECTYPE,
USERID,
TIMETAG1,
TIMETAG2,
P_DATA1,
P_DATA2,
SEQVALUE,
COMPANY_CD,
PRODUCT_CD,
IO_NUM,
BARCODE,
SEQCODE,
SEQUENCED_TRIP)
SELECT
PACKAGEID,
RECID,
RECTYPE,
USERID,
TIMETAG1,
TIMETAG2,
P_DATA1,
P_DATA2,
SEQVALUE,
COMPANY_CD,
PRODUCT_CD,
IO_NUM,
BARCODE,
SEQCODE,
SEQUENCED_TRIP
FROM traffic_tbl.delivery_tracking
WHERE TRIP_DATE = tagDate
AND USERID = userETA
order by TIMETAG2 desc, SEQVALUE;

SELECT count(distinct TIMETAG2) as CNT INTO CountStops
FROM traffic_tbl.delivery_tracking_eta
WHERE (P_DATA1 <> '14'
AND P_DATA1 like '__');
SELECT
TIMETAG2,
BARCODE INTO CurrentDeliveryTime, BarCodeCurrent
FROM traffic_tbl.delivery_tracking_eta
WHERE SEQUENCED_TRIP is null
order by TIMETAG2 desc limit 1;

SELECT
SEQVALUE INTO SequenceCurrent
FROM traffic_tbl.delivery_tracking_eta
WHERE BARCODE = BarCodeCurrent
AND P_DATA1 = '14' limit 1;

SELECT
TIMETAG2,
BARCODE INTO FirstDeliveryTime, BarCodeFirst
FROM traffic_tbl.delivery_tracking_eta
WHERE USERID = userETA
AND (P_DATA1 <> '14'
AND P_DATA1 like '__')
order by TIMETAG2 asc limit 1;

SELECT
SEQVALUE INTO SeqValueETA
FROM traffic_tbl.delivery_tracking_eta
WHERE BARCODE = barcodeETA
AND P_DATA1 = '14' limit 1;

SET TotMinutes =
TIME_TO_SEC(TIMEDIFF(CurrentDeliveryTime,FirstDeliveryTime)) /60;
SET MinPerStop = TotMinutes/CountStops;


SET CurrentETA = DATE_ADD(CurrentDeliveryTime, INTERVAL MinPerStop
*(SeqValueETA-SequenceCurrent)+15 MINUTE);
if CountStops >= 6 then
SET eta = DATE_FORMAT(CurrentETA,'%a, %b %d %Y %h:%i %p');
else
SET eta = 'Call traffic';
end if;

END


----- Original Message -----
From: "Danny Willis"
To:
Sent: Wednesday, January 12, 2005 1:17 PM
Subject: Questions regarding stored procedures


> I am looking at moving towards MySql 5.0 and possibly moving much of my
> queries into stored procedures. Has anyone experimented with this yet and
> if so do you feel there is a performance increase or performance decrease
in
> using stored procedures in MySql?
>
> Thanks in advance,
> Dan
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=palmtreeFRB@earthlink.net
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Questions regarding stored procedures

--=_alternative 00781FF285256F87_=
Content-Type: text/plain; charset="US-ASCII"

I would also review the bugs list (http://bugs.mysql.com) to see what's
active. That could help you to avoid any gotchas that aren't fixed yet.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Danny Willis" wrote on 01/12/2005
04:34:01 PM:

> I should also note that upon successful home environment testing I plan
to
> move it into my beta testing group which currently includes about 500
people
> so that'll give it a solid test as well.
>
> Are there any known major issues with 5.0 that I should be concerned
with
> that are not in the documentation (I haven't finished reviewing all of
it
> yet but I thought I'd ask).
>
>
>
> -----Original Message-----
> From: Danny Willis [mailto:danny.willis@project-wildfire.com]
> Sent: Wednesday, January 12, 2005 4:29 PM
> To: SGreen@unimin.com
> Cc: win32@lists.mysql.com
> Subject: RE: Questions regarding stored procedures
>
> Absolutely. I'm going to be testing on a non live box at home so it'll
give
> me a chance to revamp my site and see how decent the new features are in
> 5.0. I am still about a week away from getting the box up and
functioning
> but as I plan for the build this was a major stepping stone. I will
keep
> the list informed of my findings.
>
>
>
> Dan
>
>
>
> _____
>
> From: SGreen@unimin.com [mailto:SGreen@unimin.com]
> Sent: Wednesday, January 12, 2005 4:21 PM
> To: Danny Willis
> Cc: win32@lists.mysql.com
> Subject: Re: Questions regarding stored procedures
>
>
>
>
> As long as you are comfortable with the fact that v5 is still a work in
> progress, I can imagine that stored procedures will, at the very least,
save
> time and network bandwidth because they will help you to reduce the
number
> of round trips you need make between your app and the server in order to
> perform certain tasks. Keep us informed of your results, ok?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> "Danny Willis" wrote on 01/12/2005
> 04:17:58 PM:
>
> > I am looking at moving towards MySql 5.0 and possibly moving much of
my
> > queries into stored procedures. Has anyone experimented with this yet
and
> > if so do you feel there is a performance increase or performance
decrease
> in
> > using stored procedures in MySql?
> >
> > Thanks in advance,
> > Dan
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
> >
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 00781FF285256F87_=--

RE: Questions regarding stored procedures

Thanks!

-----Original Message-----
From: Fredrick Bartlett [mailto:palmtreefrb@earthlink.net]
Sent: Wednesday, January 12, 2005 4:48 PM
To: Danny Willis; win32@lists.mysql.com
Subject: Re: Questions regarding stored procedures

Yes it can inprove performance if you have to produce multiple round trips
to the server. 5.02 is broke use 5.01 until 5.03 is released.

Here is an example SP...

CREATE PROCEDURE `traffic_tbl`.`sp_GetETA`(userETA INT, barcodeETA CHAR(30),
tagDate CHAR(8), adj INT,OUT eta CHAR(30))
BEGIN

DECLARE BarcodeFirst CHAR;
DECLARE BarcodeCurrent CHAR;
DECLARE SequenceCurrent INT;
DECLARE SeqValueETA INT;
DECLARE CountStops INT;
DECLARE d DATETIME;
DECLARE FirstDeliveryTime DATETIME;
DECLARE CurrentDeliveryTime DATETIME;
DECLARE CurrentETA DATETIME;
DECLARE TotMinutes DOUBLE;
DECLARE MinPerStop DOUBLE;

SET d = Now();
SET BarCodeFirst = '';
SET BarCodeCurrent = '';
SET SequenceCurrent = 0;
SET SeqValueETA = 0;
SET CountStops = 0;
SET FirstDeliveryTime = Now();
SET CurrentDeliveryTime = Now();

drop table if exists traffic_tbl.delivery_tracking_eta;

create table traffic_tbl.delivery_tracking_eta(
PACKAGEID int(11) not null,
RECID int(11),
RECTYPE int(11),
USERID int(11),
TIMETAG1 datetime,
TIMETAG2 datetime,
P_DATA1 varchar(20),
P_DATA2 varchar(20),
SEQVALUE int(11),
COMPANY_CD varchar(6),
PRODUCT_CD char(2),
IO_NUM varchar(4),
BARCODE varchar(25),
SEQCODE varchar(6),
SEQUENCED_TRIP datetime,
PRIMARY KEY (PACKAGEID),
KEY CoCode (COMPANY_CD, PRODUCT_CD)) TYPE = HEAP ;

insert into traffic_tbl.delivery_tracking_eta(
PACKAGEID,
RECID,
RECTYPE,
USERID,
TIMETAG1,
TIMETAG2,
P_DATA1,
P_DATA2,
SEQVALUE,
COMPANY_CD,
PRODUCT_CD,
IO_NUM,
BARCODE,
SEQCODE,
SEQUENCED_TRIP)
SELECT
PACKAGEID,
RECID,
RECTYPE,
USERID,
TIMETAG1,
TIMETAG2,
P_DATA1,
P_DATA2,
SEQVALUE,
COMPANY_CD,
PRODUCT_CD,
IO_NUM,
BARCODE,
SEQCODE,
SEQUENCED_TRIP
FROM traffic_tbl.delivery_tracking
WHERE TRIP_DATE = tagDate
AND USERID = userETA
order by TIMETAG2 desc, SEQVALUE;

SELECT count(distinct TIMETAG2) as CNT INTO CountStops
FROM traffic_tbl.delivery_tracking_eta
WHERE (P_DATA1 <> '14'
AND P_DATA1 like '__');
SELECT
TIMETAG2,
BARCODE INTO CurrentDeliveryTime, BarCodeCurrent
FROM traffic_tbl.delivery_tracking_eta
WHERE SEQUENCED_TRIP is null
order by TIMETAG2 desc limit 1;

SELECT
SEQVALUE INTO SequenceCurrent
FROM traffic_tbl.delivery_tracking_eta
WHERE BARCODE = BarCodeCurrent
AND P_DATA1 = '14' limit 1;

SELECT
TIMETAG2,
BARCODE INTO FirstDeliveryTime, BarCodeFirst
FROM traffic_tbl.delivery_tracking_eta
WHERE USERID = userETA
AND (P_DATA1 <> '14'
AND P_DATA1 like '__')
order by TIMETAG2 asc limit 1;

SELECT
SEQVALUE INTO SeqValueETA
FROM traffic_tbl.delivery_tracking_eta
WHERE BARCODE = barcodeETA
AND P_DATA1 = '14' limit 1;

SET TotMinutes =
TIME_TO_SEC(TIMEDIFF(CurrentDeliveryTime,FirstDeliveryTime)) /60;
SET MinPerStop = TotMinutes/CountStops;


SET CurrentETA = DATE_ADD(CurrentDeliveryTime, INTERVAL MinPerStop
*(SeqValueETA-SequenceCurrent)+15 MINUTE);
if CountStops >= 6 then
SET eta = DATE_FORMAT(CurrentETA,'%a, %b %d %Y %h:%i %p');
else
SET eta = 'Call traffic';
end if;

END


----- Original Message -----
From: "Danny Willis"
To:
Sent: Wednesday, January 12, 2005 1:17 PM
Subject: Questions regarding stored procedures


> I am looking at moving towards MySql 5.0 and possibly moving much of my
> queries into stored procedures. Has anyone experimented with this yet and
> if so do you feel there is a performance increase or performance decrease
in
> using stored procedures in MySql?
>
> Thanks in advance,
> Dan
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
http://lists.mysql.com/win32?unsub=palmtreeFRB@earthlink.net
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=danny.willis@project-wild fire.com



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Questions regarding stored procedures

Roger That.

-----Original Message-----
From: SGreen@unimin.com [mailto:SGreen@unimin.com]
Sent: Wednesday, January 12, 2005 4:50 PM
To: Danny Willis
Cc: win32@lists.mysql.com
Subject: RE: Questions regarding stored procedures

I would also review the bugs list (http://bugs.mysql.com) to see what's
active. That could help you to avoid any gotchas that aren't fixed yet.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Danny Willis" wrote on 01/12/2005
04:34:01 PM:

> I should also note that upon successful home environment testing I plan
to
> move it into my beta testing group which currently includes about 500
people
> so that'll give it a solid test as well.
>
> Are there any known major issues with 5.0 that I should be concerned
with
> that are not in the documentation (I haven't finished reviewing all of
it
> yet but I thought I'd ask).
>
>
>
> -----Original Message-----
> From: Danny Willis [mailto:danny.willis@project-wildfire.com]
> Sent: Wednesday, January 12, 2005 4:29 PM
> To: SGreen@unimin.com
> Cc: win32@lists.mysql.com
> Subject: RE: Questions regarding stored procedures
>
> Absolutely. I'm going to be testing on a non live box at home so it'll
give
> me a chance to revamp my site and see how decent the new features are in
> 5.0. I am still about a week away from getting the box up and
functioning
> but as I plan for the build this was a major stepping stone. I will
keep
> the list informed of my findings.
>
>
>
> Dan
>
>
>
> _____
>
> From: SGreen@unimin.com [mailto:SGreen@unimin.com]
> Sent: Wednesday, January 12, 2005 4:21 PM
> To: Danny Willis
> Cc: win32@lists.mysql.com
> Subject: Re: Questions regarding stored procedures
>
>
>
>
> As long as you are comfortable with the fact that v5 is still a work in
> progress, I can imagine that stored procedures will, at the very least,
save
> time and network bandwidth because they will help you to reduce the
number
> of round trips you need make between your app and the server in order to
> perform certain tasks. Keep us informed of your results, ok?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> "Danny Willis" wrote on 01/12/2005
> 04:17:58 PM:
>
> > I am looking at moving towards MySql 5.0 and possibly moving much of
my
> > queries into stored procedures. Has anyone experimented with this yet
and
> > if so do you feel there is a performance increase or performance
decrease
> in
> > using stored procedures in MySql?
> >
> > Thanks in advance,
> > Dan
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
> >
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org