How much faster is a database access that is serviced from cache compared to
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?
Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>
|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?
|||Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
[vbcol=seagreen]
>As a rule of thumb: 100 to 1000 times faster.
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>HTH,
>Gert-Jan
>
>Robin East wrote:
|||Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
Showing posts with label relative. Show all posts
Showing posts with label relative. Show all posts
Friday, March 30, 2012
Relative speed of physical and logical I/Os
How much faster is a database access that is serviced from cache compared to
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?sql
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?sql
Relative speed of physical and logical I/Os
How much faster is a database access that is serviced from cache compared to
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared
to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order
of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?|||Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
[vbcol=seagreen]
>As a rule of thumb: 100 to 1000 times faster.
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>HTH,
>Gert-Jan
>
>Robin East wrote:|||Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared
to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order
of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?|||Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
[vbcol=seagreen]
>As a rule of thumb: 100 to 1000 times faster.
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>HTH,
>Gert-Jan
>
>Robin East wrote:|||Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
Relative speed of physical and logical I/Os
Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
>As a rule of thumb: 100 to 1000 times faster.
>
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>
>HTH,
>Gert-Jan
>
>
>Robin East wrote:
>>
>> How much faster is a database access that is serviced from cache compared
>> to
>> retrieved from disk. Obviously this is dependent on the specifics of disk
>> configurations, processor speed, memory speed, etc. but in terms of order
>> of
>> magnitude, is cache access 100, 1000, 10000 times quicker or more?Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
>> Thanks everyone who replied. I think Gerts answer was closest to what
>> I was looking for and expecting. I have greater experience with
>> Oracle where, as a general rule, the figure is closer to 100 than a
>> 1000.
>> As a matter of interest, Gert, how come the hot cache results gave CPU
>> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
>> time before but usually only about 10-15ms.
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
>As a rule of thumb: 100 to 1000 times faster.
>
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>
>HTH,
>Gert-Jan
>
>
>Robin East wrote:
>>
>> How much faster is a database access that is serviced from cache compared
>> to
>> retrieved from disk. Obviously this is dependent on the specifics of disk
>> configurations, processor speed, memory speed, etc. but in terms of order
>> of
>> magnitude, is cache access 100, 1000, 10000 times quicker or more?Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
>> Thanks everyone who replied. I think Gerts answer was closest to what
>> I was looking for and expecting. I have greater experience with
>> Oracle where, as a general rule, the figure is closer to 100 than a
>> 1000.
>> As a matter of interest, Gert, how come the hot cache results gave CPU
>> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
>> time before but usually only about 10-15ms.
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
Relative Performance: Native SQL vs User Functions
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.
Relative Performance: Native SQL vs User Functions
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
Relative paths
We have a growing issue where we have a relative dtsconfig file (which stores the absolute base path of the ETL packages). This way we can keep the ETL projects failry portable - only having to modify one value in the dtsconfig file. The master package that defines the dtsconfig location (which is config/Default.dtsconfig) usually interpretates this location to be relative the project. The problem is that every now and again when you open this package in .NETStudio, the path is interpreted differently and causes: config/Default.dtsconfig to state invalid path. But when we delete the variable (which defines the dtsconfig path), save/close and open/recreate it works again. This may or may not be supported MS method, but I was curious to know why this gets messed up. Is there somehwere in the .NET framework that defines what "/" is relatively under?
For example: Our absolute config path is "D:\Program Files\Microsoft SQL Server\90\DTS\Packages\ETLProject\ETLBase\config\Default.dtsconfig" but using: "config/Default.dtsconfig" for xml file value works. However, sometimes we will get an error stating that this file cannot be found, and when we just try to delete (without saving and closing) and immediatelly try to put "config/Default.dtsconfig" again and hit next, we get an error and the path is now:
'D:\Program Files\Microsoft SQL Server\90\DTS\Packages\DEVDataExchange\ETLBase\config\config\Default.dtsconfig'.
Ideas?
I got the same problem. I'm creating a set of packages that i would like to distribute to my customers. A lot of them don't like the idea of me creating environment variables on their server. Therefore I want to be able to use something like .\filename.dtsconfig as the path. This has worked fine for me, but from one moment to another it stopt working 
Hope someone can help me out here. I really would like to use relative paths for my configuration file.
Thanks,
Matthijs
|||Nope. You must use absolute paths for reliability and consistency. Create the environment variable and be done with it. Mandate it!|||Relative paths cannot be used in SSIS. Many people complain about that but there is a rationale for it. If the package is stored in SQL Server, what would teh path be relative to?
That's why absolute paths are used.
-Jamie
Relative paths
We have a growing issue where we have a relative dtsconfig file (which stores the absolute base path of the ETL packages). This way we can keep the ETL projects failry portable - only having to modify one value in the dtsconfig file. The master package that defines the dtsconfig location (which is config/Default.dtsconfig) usually interpretates this location to be relative the project. The problem is that every now and again when you open this package in .NETStudio, the path is interpreted differently and causes: config/Default.dtsconfig to state invalid path. But when we delete the variable (which defines the dtsconfig path), save/close and open/recreate it works again. This may or may not be supported MS method, but I was curious to know why this gets messed up. Is there somehwere in the .NET framework that defines what "/" is relatively under?
For example: Our absolute config path is "D:\Program Files\Microsoft SQL Server\90\DTS\Packages\ETLProject\ETLBase\config\Default.dtsconfig" but using: "config/Default.dtsconfig" for xml file value works. However, sometimes we will get an error stating that this file cannot be found, and when we just try to delete (without saving and closing) and immediatelly try to put "config/Default.dtsconfig" again and hit next, we get an error and the path is now:
'D:\Program Files\Microsoft SQL Server\90\DTS\Packages\DEVDataExchange\ETLBase\config\config\Default.dtsconfig'.
Ideas?
I got the same problem. I'm creating a set of packages that i would like to distribute to my customers. A lot of them don't like the idea of me creating environment variables on their server. Therefore I want to be able to use something like .\filename.dtsconfig as the path. This has worked fine for me, but from one moment to another it stopt working
Hope someone can help me out here. I really would like to use relative paths for my configuration file.
Thanks,
Matthijs
|||Nope. You must use absolute paths for reliability and consistency. Create the environment variable and be done with it. Mandate it!|||Relative paths cannot be used in SSIS. Many people complain about that but there is a rationale for it. If the package is stored in SQL Server, what would teh path be relative to?
That's why absolute paths are used.
-Jamie
Relative path for child packages
hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.
Is there any property in the SQL Server Agent or mayby a workaround to solve this?
Use absolute paths. Any reason you need relative paths?|||
Hi Santiago,
Unfortunately you cannot pass parameters to SQL Agent jobs. Workarounds usually involve:
1. Package configurations: set the path in a variable and use Expressions in the Package Connection Managers to dynamically apply the variable value.
2. Call the package dynamically: use a stored procedure to build a "dtexec" command-line and execute it via xp_cmdshell.
There are other ways to accomplish this as well. Personally, I recommend package configurations. I use xp_cmdshell less and less these days and when I do I enable it, do what I need to do, and disable it.
Hope this helps,
Andy
|||You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.|||
jwelch wrote:
You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.
Same here. This is (one of) the reason(s) that I always use the same folder structure on all of my projects.
Common folder structure
(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Common-folder-structure.aspx)
I also include the root path variable in my package template.
SSIS: Package Template
(http://blogs.conchango.com/jamiethomson/archive/2007/03/11/SSIS_3A00_-Package-Template.aspx)
-Jamie
relative location chart and image or text
I have a chart, image and text side by side. At design time they all look
good (relative positions). My chart has min and max Y axis, so the height is
always constant. At run time chart, image and text are at different heights.
How can I make them all aligned vertically to the top. Thanks.Why don't you give the Location of These objects
Go to properties >location>top of the Objects to 0
hope this will help
Regards
Raj Deep.A
bhanoji wrote:
> I have a chart, image and text side by side. At design time they all look
> good (relative positions). My chart has min and max Y axis, so the height is
> always constant. At run time chart, image and text are at different heights.
> How can I make them all aligned vertically to the top. Thanks.
good (relative positions). My chart has min and max Y axis, so the height is
always constant. At run time chart, image and text are at different heights.
How can I make them all aligned vertically to the top. Thanks.Why don't you give the Location of These objects
Go to properties >location>top of the Objects to 0
hope this will help
Regards
Raj Deep.A
bhanoji wrote:
> I have a chart, image and text side by side. At design time they all look
> good (relative positions). My chart has min and max Y axis, so the height is
> always constant. At run time chart, image and text are at different heights.
> How can I make them all aligned vertically to the top. Thanks.
Relative Dates
I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
Thanks
Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
sql
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
Thanks
Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
sql
Relative Dates
I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000
>> BOL:
>> USE pubs
>> GO
>> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
>> FROM titles
>> GO
>> GetDate() will allow you to compare to the time the query was run.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000
>> BOL:
>> USE pubs
>> GO
>> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
>> FROM titles
>> GO
>> GetDate() will allow you to compare to the time the query was run.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>
Relative Dates
When attempting to use the 'relative dates' functionality of the Report Builder component, I'm getting an error that states "The report may not be valid or the server could not process the data" then in the detail section it says that the field I'm using (an obvious date field) is being interpreted as an integer. If I use an absolute date, it works just fine, just errors out when I use a relative date.
My questions...has anyone else come accross this error? Is it a known issue? Is there a way around it that anyone knows of? I suspect it's a glitch in this new product but really need to know for sure.
Any references to MS topics on this would be hugely appreciated as well.
Thanks...Ooogy
I’m not aware of any known issues like the one you describe. Can yougive more precise repro steps?
Relative Dates
I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with
i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BO
L:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with
i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BO
L:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>
Relative date 'Rolling 3 months' --help please
Hi,
I need to add special relative date categories in SSAS
that similar to the functionality offered by Cognos/Powerplay. With Cognos, you
can create relative time categories very easily-- like ‘Rolling 3 months’,
‘Prior Rolling 12 Months’ etc.
I created Time Dimension with SSAS BI Studio, added a new
named calculation ‘Rolling 3 Months’ to the Time Dimension. Below is
calculation code:
CREATE MEMBER
CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling
3]
AS
null,
VISIBLE = 1;
Scope(
{
[Measures].[ORDERS]
}
);
// Rolling 3 Months
(
[Time].[Rolling 3
Months].[Rolling 3],
[Time].[Month].[Month].Members
) =
Tail([Time].[Month].members ,3 );
End Scope;
I uesd Tail() function in SS Management studio, it got
the result I want. But it doesn’t work in SSAS BI Studio.
Am I missing something here? Or any suggestions would be
greatly appreciated.
Thanks.
Your syntax for the expression is wrong - you assign set where the numeric value is expected. I suggest that you use built-in Time Intelligence wizard - it supports expressions for Rolling 3 months. It would be something like
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members) = Aggregate( [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember);
|||Thankyou very much for the help.
I realized something wrong here, but I just do
not know how to correct it. Thank you very much for point out. I tried Time
Dimension wizard, there are ‘month to date’ and ‘Three
Month Moving Average’ in Time Dimension wizard, but our project ask from Cognos Powerplay cube move to SSAS cube. in Powerplay,
‘Rolling 3 months’ will get each month order number for 3 months.
Should be like:
Month|
Orders
January, 2007|12345
December, 2006|14567
November, 2006|33562
Not 3 months Moving
Average. We expect get same
result like powerplay. Is there a way to get this result?
Thank you very much for the help, I really appreciated.
|||Please use the expression provided above.|||Hi Mosha,Yes. I used the expression you provided above, but got "#VALUE!".
Rolling 3 months | Orders
-
Current Periods | 235263
Rolling 3 months | #VALUE!
What else I can do?
Thanks a lot.
|||Please provide the query that you sent, the exact MDX Script you have and the text of error message in order to find where is the problem.|||The code in BI studio script view:
First try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS ([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
),
VISIBLE = 1 ;
Second try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember,
VISIBLE = 1 ;
Scope(
{
[Measures].[ORDERS]
}
);
// Rolling 3 Months
([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
End Scope;
Got same result in cube browser:
Rolling 3 Months | Orders
-
Current Periods | 123456
Rolling 3 | #VALUE!
no error message or say '#VALUE!' is an error message.
What's wrong?
|||
You still kept the portion with the error. You also need to decide what do you want to do at the levels above Month. I will put the code which keeps it NULL at such levels - up to you what to do there.
CREATE [Time].[Rolling 3 Months].[Rolling 3];
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
I put the code you provided in to script view.
CREATE [Time].[Rolling 3 Months].[Rolling 3];
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
result:
[Rolling 3] row is missing.
Rolling 3 Months | Orders
--
Current Periods | 12345
Currently, we just want to get each month's order for last three months with 'Rolling 3 Months' this calculated member.
I am not sure your question for 'at the levels above Month', you mean Year level?
If I need Year level, how to add in?
am I missing some point?
Thanks a lot.
|||Yes, I meant on the Year level. It is not clear how you want to aggregate rolling 3 months to the Year. Anyway, you in order to see rolling sum on 3 month, please slice by some specific month, or add months to the axis.|||Thanks Mosha for the help.
I add specific month, but result some like before -no 'Rolling 3' row.
CREATE [Time].[Rolling 3 Months].[Rolling 3] ;
([Time].[Rolling 3 Months].[Rolling 3],[Time].[Month].[November 2006], Measures.[ORDERS]) = Aggregate(
[Time].[Month].[November 2006].Lag(2): [Time].[Month].[November 2006]
);
Something still wrong?
Thanks.|||The expression in the MDX script should stay the way it was before. It is your MDX query that needs to change to include slice by month.|||
As my understanding(may be wrong), MDX
script run in BI Studio, MDX query run in SS management Studio. In SS
management Studio, I used following MDX query:
SELECT Tail(
[Time].[Month].members,3 ) ON rows,
[Measures].[ORDERS] on columns
from [My cube]
Got
‘Rolling 3 Months’ data I want.
Month|
Orders
October
2006|12345
November
2006|34556
December
2006|23545
In BI Studio, if using filter by Month, it still able
to get ‘Rolling 3 Months’ result.
The
problem is using filter by Month, there are too many steps, our business user
will not accept that. They hope only drag ‘Rolling 3 Months’ from Time dimension drop to the cube browser then
can get above data (like Powerplay does). Is there a way to do that?
How to add MDX query in BI Studio?
Thanks for the help..
Relative date ''Rolling 3 months'' --help please
Hi,
I need to add special relative date categories in SSAS
that similar to the functionality offered by Cognos/Powerplay. With Cognos, you
can create relative time categories very easily-- like ‘Rolling 3 months’,
‘Prior Rolling 12 Months’ etc.
I created Time Dimension with SSAS BI Studio, added a new
named calculation ‘Rolling 3 Months’ to the Time Dimension. Below is
calculation code:
CREATE MEMBER
CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling
3]
AS
null,
VISIBLE = 1;
Scope(
{
[Measures].[ORDERS]
}
);
// Rolling 3 Months
(
[Time].[Rolling 3
Months].[Rolling 3],
[Time].[Month].[Month].Members
) =
Tail([Time].[Month].members ,3 );
End Scope;
I uesd Tail() function in SS Management studio, it got
the result I want. But it doesn’t work in SSAS BI Studio.
Am I missing something here? Or any suggestions would be
greatly appreciated.
Thanks.
Your syntax for the expression is wrong - you assign set where the numeric value is expected. I suggest that you use built-in Time Intelligence wizard - it supports expressions for Rolling 3 months. It would be something like
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members) = Aggregate( [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember);
|||Thankyou very much for the help.
I realized something wrong here, but I just do
not know how to correct it. Thank you very much for point out. I tried Time
Dimension wizard, there are ‘month to date’ and ‘Three
Month Moving Average’ in Time Dimension wizard, but our project ask from Cognos Powerplay cube move to SSAS cube. in Powerplay,
‘Rolling 3 months’ will get each month order number for 3 months.
Should be like:
Month|
Orders
January, 2007|12345
December, 2006|14567
November, 2006|33562
Not 3 months Moving
Average. We expect get same
result like powerplay. Is there a way to get this result?
Thank you very much for the help, I really appreciated.
|||Please use the expression provided above.|||Hi Mosha,Yes. I used the expression you provided above, but got "#VALUE!".
Rolling 3 months | Orders
-
Current Periods | 235263
Rolling 3 months | #VALUE!
What else I can do?
Thanks a lot.
|||Please provide the query that you sent, the exact MDX Script you have and the text of error message in order to find where is the problem.|||The code in BI studio script view:
First try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS ([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
),
VISIBLE = 1 ;
Second try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember,
VISIBLE = 1 ;
Scope(
{
[Measures].[ORDERS]
}
);
// Rolling 3 Months
([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
End Scope;
Got same result in cube browser:
Rolling 3 Months | Orders
-
Current Periods | 123456
Rolling 3 | #VALUE!
no error message or say '#VALUE!' is an error message.
What's wrong?
|||
You still kept the portion with the error. You also need to decide what do you want to do at the levels above Month. I will put the code which keeps it NULL at such levels - up to you what to do there.
CREATE [Time].[Rolling 3 Months].[Rolling 3];
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
I put the code you provided in to script view.
CREATE [Time].[Rolling 3 Months].[Rolling 3];
([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);
result:
[Rolling 3] row is missing.
Rolling 3 Months | Orders
--
Current Periods | 12345
Currently, we just want to get each month's order for last three months with 'Rolling 3 Months' this calculated member.
I am not sure your question for 'at the levels above Month', you mean Year level?
If I need Year level, how to add in?
am I missing some point?
Thanks a lot.
|||Yes, I meant on the Year level. It is not clear how you want to aggregate rolling 3 months to the Year. Anyway, you in order to see rolling sum on 3 month, please slice by some specific month, or add months to the axis.|||Thanks Mosha for the help.
I add specific month, but result some like before -no 'Rolling 3' row.
CREATE [Time].[Rolling 3 Months].[Rolling 3] ;
([Time].[Rolling 3 Months].[Rolling 3],[Time].[Month].[November 2006], Measures.[ORDERS]) = Aggregate(
[Time].[Month].[November 2006].Lag(2): [Time].[Month].[November 2006]
);
Something still wrong?
Thanks.|||The expression in the MDX script should stay the way it was before. It is your MDX query that needs to change to include slice by month.|||
As my understanding(may be wrong), MDX
script run in BI Studio, MDX query run in SS management Studio. In SS
management Studio, I used following MDX query:
SELECT Tail(
[Time].[Month].members,3 ) ON rows,
[Measures].[ORDERS] on columns
from [My cube]
Got
‘Rolling 3 Months’ data I want.
Month|
Orders
October
2006|12345
November
2006|34556
December
2006|23545
In BI Studio, if using filter by Month, it still able
to get ‘Rolling 3 Months’ result.
The
problem is using filter by Month, there are too many steps, our business user
will not accept that. They hope only drag ‘Rolling 3 Months’ from Time dimension drop to the cube browser then
can get above data (like Powerplay does). Is there a way to do that?
How to add MDX query in BI Studio?
Thanks for the help..sql
Relative ConnectionString for SQL Server 2005 Express
I am creating a application in Visual C++ 2005 that has database connections in it. I have gone through the process of setting up the data source and it will work great on my computer. However, the second I move either the project or the created executable to another computer the application keeps crashing. I know that it is crashing due to the database connection string. The string is setup to work only on my computer, I am wondering how do I make, or edit, the string to work on any computer? Thanks for the help.
Have you gone into SQL Server Surface Area Configuration to enable remote connections to the database?
Have you gone into SQL Server Surface Area Configuration to enable remote connections to the database?
Monday, March 26, 2012
relation & query
Hello,
I'm relative new to sql and databases and the last few weeks I learned
myself a lot. I'm trying to make a hotel reservation application.
I have a database with a table Booking, a table Room, a table
RoomsPerBooking. So a booking contains date/time etc and a field
RoomsPerBookingID. The table RoomsPerBooking contains number of
persons, unitprice etc. and a field ID and a field RoomID. The table
Room contains data like name, notes etc.
now i have two questions:
First about relations:
The table Booking has relationship: PK table RoomsPerBooking - ID <-->
FK table Booking - RoomsPerBookingID.
The table RoomsPerBookingID has relationship: PK table Room - ID <-->
FK table RoomsPerBooking - RoomID
Is this relationset good for my purpose? I think it is, but I am not
sure.
The second question is:
How do I get available rooms per night
I came this far... what are the "some statements"?
CREATE PROCEDURE dbo.GetAvailableRooms
(
@.BeginDate DATETIME,
@.EndDate DATETIME
)
AS
SELECT Room.*
FROM Room
WHERE Room.ID NOT IN (
SELECT DISTINCT room.ID
FROM Room room JOIN RoomsPerBooking roomsPerBooking
ON room.ID = roomsPerBooking.RoomID
--Some statements--
WHERE booking.FromDate <= @.EndDate
AND booking.ToDate >= @.BeginDate)
GOPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Why do you have multiple names for the same data element? Why do you
use a singular name for a set of Rooms? Why did you use id and
room_id when the standard way of referencing a room is a "room number"?
Why do you use aliases that are the same as the base table names?
After you clean up the schema a bit, look at using a Calendar table.
Subscribe to:
Posts (Atom)