How many reads versus writes?

Do a profile log for some period of time on your OLTP system, and look
at the sum of reads versus the sum of writes.
I just did this, and as expected, there were far more reads than
writes, and even given that I'm counting logical reads versus physical
writes, the difference between the two counts was astounding. I'd be
curious what others see on their systems. Are your read over writes:
90% reads?
99% reads?
99.9% reads?
99.99% reads?
Or anything else?
Curiously,
JoshThis is a very normal observation on an oltp system. It's a mis-conception
that oltp systems write more than they read. Most transactions perform more
reads than writes because of basic things, such as looking up foreign keys,
indexes etc to perform queries. Most GUI screens that users use to interact
with oltp systems are heavily dependant on reads to populate screens, web
pages etc. Generally speaking, users tend to open various screens before
even keying in "stuff" & screens generally need to read the db to populate
fields etc.. In my observations, very high ratios or reads vs writes in oltp
is very normal indeed.
Regards,
Greg Linwood
SQL Server MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:f7aap05bvred65fanfnnb2anq2da58hb78@.
4ax.com...
> Do a profile log for some period of time on your OLTP system, and look
> at the sum of reads versus the sum of writes.
> I just did this, and as expected, there were far more reads than
> writes, and even given that I'm counting logical reads versus physical
> writes, the difference between the two counts was astounding. I'd be
> curious what others see on their systems. Are your read over writes:
> 90% reads?
> 99% reads?
> 99.9% reads?
> 99.99% reads?
> Or anything else?
> Curiously,
> Josh
>

How many reads versus writes?

Do a profile log for some period of time on your OLTP system, and look
at the sum of reads versus the sum of writes.
I just did this, and as expected, there were far more reads than
writes, and even given that I'm counting logical reads versus physical
writes, the difference between the two counts was astounding. I'd be
curious what others see on their systems. Are your read over writes:
90% reads?
99% reads?
99.9% reads?
99.99% reads?
Or anything else?
Curiously,
Josh
This is a very normal observation on an oltp system. It's a mis-conception
that oltp systems write more than they read. Most transactions perform more
reads than writes because of basic things, such as looking up foreign keys,
indexes etc to perform queries. Most GUI screens that users use to interact
with oltp systems are heavily dependant on reads to populate screens, web
pages etc. Generally speaking, users tend to open various screens before
even keying in "stuff" & screens generally need to read the db to populate
fields etc.. In my observations, very high ratios or reads vs writes in oltp
is very normal indeed.
Regards,
Greg Linwood
SQL Server MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:f7aap05bvred65fanfnnb2anq2da58hb78@.4ax.com...
> Do a profile log for some period of time on your OLTP system, and look
> at the sum of reads versus the sum of writes.
> I just did this, and as expected, there were far more reads than
> writes, and even given that I'm counting logical reads versus physical
> writes, the difference between the two counts was astounding. I'd be
> curious what others see on their systems. Are your read over writes:
> 90% reads?
> 99% reads?
> 99.9% reads?
> 99.99% reads?
> Or anything else?
> Curiously,
> Josh
>

How many reads versus writes?

Do a profile log for some period of time on your OLTP system, and look
at the sum of reads versus the sum of writes.
I just did this, and as expected, there were far more reads than
writes, and even given that I'm counting logical reads versus physical
writes, the difference between the two counts was astounding. I'd be
curious what others see on their systems. Are your read over writes:
90% reads?
99% reads?
99.9% reads?
99.99% reads?
Or anything else?
Curiously,
JoshThis is a very normal observation on an oltp system. It's a mis-conception
that oltp systems write more than they read. Most transactions perform more
reads than writes because of basic things, such as looking up foreign keys,
indexes etc to perform queries. Most GUI screens that users use to interact
with oltp systems are heavily dependant on reads to populate screens, web
pages etc. Generally speaking, users tend to open various screens before
even keying in "stuff" & screens generally need to read the db to populate
fields etc.. In my observations, very high ratios or reads vs writes in oltp
is very normal indeed.
Regards,
Greg Linwood
SQL Server MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:f7aap05bvred65fanfnnb2anq2da58hb78@.4ax.com...
> Do a profile log for some period of time on your OLTP system, and look
> at the sum of reads versus the sum of writes.
> I just did this, and as expected, there were far more reads than
> writes, and even given that I'm counting logical reads versus physical
> writes, the difference between the two counts was astounding. I'd be
> curious what others see on their systems. Are your read over writes:
> 90% reads?
> 99% reads?
> 99.9% reads?
> 99.99% reads?
> Or anything else?
> Curiously,
> Josh
>

how i convert varchar sal field to numeric in query

how i convert varchar sal field to numeric in query

select sum(sal) from emp1

error:the sum or average aggregate operation cannot take a varchar data type as an argument.

Use Cast as in:

select sum(cast(sal as float)) from emp1

How format COMPUTE results?

Is it possible to format ARBalance below with commas?
SELECT ARBalance
FROM Invoice
compute SUM(ARBalance)
I have tried inserting variations from below with no luck:
CONVERT(varchar, CAST(SUM(Invoice.ARBalance) AS money), 1)
Thanks!Why don't you apply string formatting at the client/presentation tier? VB
has Format(), VBScript/ASP have FormatNumber() and FormatCurrency(), etc.
A
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:eelASapnFHA.3036@.TK2MSFTNGP14.phx.gbl...
> Is it possible to format ARBalance below with commas?
> SELECT ARBalance
> FROM Invoice
> compute SUM(ARBalance)
> I have tried inserting variations from below with no luck:
> CONVERT(varchar, CAST(SUM(Invoice.ARBalance) AS money), 1)
> Thanks!
>|||Hi
Formatting is really a function of the client, for instance if you are using
Query Analyser the "Use regional settings when displaying currency, number,
dates and times" check box on the connection tab does this.
John
"Mike Harbinger" wrote:

> Is it possible to format ARBalance below with commas?
> SELECT ARBalance
> FROM Invoice
> compute SUM(ARBalance)
> I have tried inserting variations from below with no luck:
> CONVERT(varchar, CAST(SUM(Invoice.ARBalance) AS money), 1)
> Thanks!
>
>|||Noye that COMPUTE is a deprecate feature. Use CUBE, ROLLUP or make the SUM
part of your query.
David Portas
SQL Server MVP
--|||Now how did I miss that! Thanks to all
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:03BC582C-EEA5-4AD2-BC33-B1CE1ED1A5AD@.microsoft.com...
> Hi
> Formatting is really a function of the client, for instance if you are
> using
> Query Analyser the "Use regional settings when displaying currency,
> number,
> dates and times" check box on the connection tab does this.
> John
> "Mike Harbinger" wrote:
>