To search for partial string we are using WHERE column_name LIKE '%str%'.
But its taking long time to return the results. Is there any way to improve
the performance.
What is the datatype of the column you are searching against. An index would
help majorly but it has to been an allowable type.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
> To search for partial string we are using WHERE column_name LIKE
> '%str%'.
> But its taking long time to return the results. Is there any way to
> improve
> the performance.
|||datatype of the column is varchar(max). This column contains the description
of the product. Users want to search for a string rather than a word. If
search by a word is the requirement, I would have gone with FTE.
"Warren Brunk" wrote:
> What is the datatype of the column you are searching against. An index would
> help majorly but it has to been an allowable type.
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
>
>
|||using a wildcard at the start of a LIKE expression does not use an INDEX SEEK
but rather an INDEX SCAN so the query will be slow even if you created
indexes on this column
http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Misbah Arefin
"Ramu" wrote:
[vbcol=seagreen]
> datatype of the column is varchar(max). This column contains the description
> of the product. Users want to search for a string rather than a word. If
> search by a word is the requirement, I would have gone with FTE.
>
> "Warren Brunk" wrote:
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Parent? Child? Distant Relative? Casual Aquaintance?
Hi all, I have a question that relates to a relationship in my stock performance database. It is a conceptual one, but is the only aspect of my database design that I am "losing sleep over" *LOL* (as if that could happen with me... ;) )
Anyway, Here are my tables/Keys, as setup
1) CurrentList - Primary Key is PortfolioID, StockID, BuyDate, Selldate.
- essentially, this table defines stock portfolios. A PortfolioID has one-to-many StockIDs (stocks in the portfolio), and each PortfolioID/StockID pair can further be grouped by BuyDate and Selldate (because a single stock may come in and out of a single portfolio over time).
2) StockProperty - Primary Key is StockID, CreateDate. This table is built on a daily basis from rows in the Currentlist. It represents the affected stocks currently in ANY portfolio. For example, the IBM stock may exist in more than one PORTFOLIO (PortfolioID) but there will still only be ONE row for IBM on a particular date (CreateDate) in the StockProperty table (since the stock is the same stock, regardless of how many portfolios it is in).
I am trying to figure out which is the parent table, and which the child...or if there IS an "enforceable" relationship at all!?!? None of the non-key data is the same in either table, so the only columns with a relationship is StockID, and StockProperty.Createdate - which must be between CurrentList.BuyDate and CurrentList.SellDate.
I guess that's my quandry (or one of myriad quandries in my life ;) ) - I can't really think of how to enforce (via foreign keys, etc) the relationship in the date range...AND...how to explain the relationship between the two tables (I lean toward CurrentList being Parent to StockProperty, but can't come to grips with what the probably obvious "standard" relationship description would be.
Any (preintable) thoughts?
Thanks!This is known as a many-to-many relationship, and is pretty common. An intermediary table (CurrentList, in your case) contains the primary keys of two unrelated parent tables (PortfolioID and StockID). In your example, your current list will have additonal composite key fields (BuyDate and SellDate) that describe a historical record of the many-to-many relationship.
That said, I suspect that you are going to run into more problems with your design. This is a very complicated business process to model, and you will likely run into many more many-to-many relationships and even more obscure structures before your get something robust and workable. I strongly recommend that you scale back the scope of your project if possible, and then add features as you verify your design through use.|||Okee-dokee, thanks for burning a few brain cells on this...
It's actually working in production, and as you probably guessed, this is just about 1/5th of the project's database, and the overall production database is working with no apparent design issues so far. I THINK I'm ok in that respect. As with all things, time and unforseen keystroke sequences will tell.
There are really only TWO tables involved at this part of the design though...the CurrentList and the StockProperty tables. A Currentlist row, however, can only be associated with ONE stockproperty row (in my example above, if the same stock is in the same currentlist multiple times, the BuyDate and SellDate in the CurrentList row will be different, resulting in a new and separate CurrentList row). (or were you saying that I SHOULD have the intermediary table?) Conversely, a StockProperty row can be associated with one-to-many CurrentList rows.
Anyway, I know it works, I'm just trying to step back and look at this segment of the design and try to figure out if it's designed the way it SHOULD be. Any time I see something like this that gives me trouble mapping out relationships onto paper - just is a design "uh-oh" red flag to me. (hey, Daddy, what's "paper"?)
I'm thinking that it really would be enough at one level to add a foreign key on StockID using CurrentList as the child, and StockProperty as the parent. That seems too simple though, and disregards the stockproperty.date to currentlist.daterange relationship.
Hmmm...I appreciate any insight anyone can provide...but no big rush or urgency. It's more to me like a "OK, it's out there, NOW what could I have done differently?" thing. Yeah, I know...NOT how to develop ideally, but The Machine coerced me...and now I just wanna make SURE, in retrospect, that my kid won't see this someday and think "Good God, Dad...WTF were you THINKING?" (she does that enough NOW ;) ).
Anyway, Here are my tables/Keys, as setup
1) CurrentList - Primary Key is PortfolioID, StockID, BuyDate, Selldate.
- essentially, this table defines stock portfolios. A PortfolioID has one-to-many StockIDs (stocks in the portfolio), and each PortfolioID/StockID pair can further be grouped by BuyDate and Selldate (because a single stock may come in and out of a single portfolio over time).
2) StockProperty - Primary Key is StockID, CreateDate. This table is built on a daily basis from rows in the Currentlist. It represents the affected stocks currently in ANY portfolio. For example, the IBM stock may exist in more than one PORTFOLIO (PortfolioID) but there will still only be ONE row for IBM on a particular date (CreateDate) in the StockProperty table (since the stock is the same stock, regardless of how many portfolios it is in).
I am trying to figure out which is the parent table, and which the child...or if there IS an "enforceable" relationship at all!?!? None of the non-key data is the same in either table, so the only columns with a relationship is StockID, and StockProperty.Createdate - which must be between CurrentList.BuyDate and CurrentList.SellDate.
I guess that's my quandry (or one of myriad quandries in my life ;) ) - I can't really think of how to enforce (via foreign keys, etc) the relationship in the date range...AND...how to explain the relationship between the two tables (I lean toward CurrentList being Parent to StockProperty, but can't come to grips with what the probably obvious "standard" relationship description would be.
Any (preintable) thoughts?
Thanks!This is known as a many-to-many relationship, and is pretty common. An intermediary table (CurrentList, in your case) contains the primary keys of two unrelated parent tables (PortfolioID and StockID). In your example, your current list will have additonal composite key fields (BuyDate and SellDate) that describe a historical record of the many-to-many relationship.
That said, I suspect that you are going to run into more problems with your design. This is a very complicated business process to model, and you will likely run into many more many-to-many relationships and even more obscure structures before your get something robust and workable. I strongly recommend that you scale back the scope of your project if possible, and then add features as you verify your design through use.|||Okee-dokee, thanks for burning a few brain cells on this...
It's actually working in production, and as you probably guessed, this is just about 1/5th of the project's database, and the overall production database is working with no apparent design issues so far. I THINK I'm ok in that respect. As with all things, time and unforseen keystroke sequences will tell.
There are really only TWO tables involved at this part of the design though...the CurrentList and the StockProperty tables. A Currentlist row, however, can only be associated with ONE stockproperty row (in my example above, if the same stock is in the same currentlist multiple times, the BuyDate and SellDate in the CurrentList row will be different, resulting in a new and separate CurrentList row). (or were you saying that I SHOULD have the intermediary table?) Conversely, a StockProperty row can be associated with one-to-many CurrentList rows.
Anyway, I know it works, I'm just trying to step back and look at this segment of the design and try to figure out if it's designed the way it SHOULD be. Any time I see something like this that gives me trouble mapping out relationships onto paper - just is a design "uh-oh" red flag to me. (hey, Daddy, what's "paper"?)
I'm thinking that it really would be enough at one level to add a foreign key on StockID using CurrentList as the child, and StockProperty as the parent. That seems too simple though, and disregards the stockproperty.date to currentlist.daterange relationship.
Hmmm...I appreciate any insight anyone can provide...but no big rush or urgency. It's more to me like a "OK, it's out there, NOW what could I have done differently?" thing. Yeah, I know...NOT how to develop ideally, but The Machine coerced me...and now I just wanna make SURE, in retrospect, that my kid won't see this someday and think "Good God, Dad...WTF were you THINKING?" (she does that enough NOW ;) ).
Friday, March 23, 2012
Partitioning performance on one disk?
Hi,
I know partitioning improves the performance on large tables.
We have a table of app. 100.000.000 records.
We want to partition the table, but we were wondering if there is still an increase in performance if all the partitions are on the same disk. We want to partition the data per month, but we don't have any disks left were we can spread the partitions.
So my main question is:
Is there still a performance increase when you use partitioning on one disk in stead of multiple disks.
Thx!
Regards Tony
sure. though the gain is maximized when you partition across multiple disks and the system is running on multi cpu.
PARTITIONING DISKS
Hi all,
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
Fabio
First off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>
|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>
|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
Fabio
First off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>
|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>
|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com
PARTITIONING DISKS
Hi all,
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
FabioFirst off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
FabioFirst off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com
PARTITIONING DISKS
Hi all,
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
FabioFirst off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
--
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com
What is the best way to partitioning disks for performance in sql server
2000?
whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
free space.
What's the best way to partitioning this disks on windows 2003?
Thanks in Advance,
FabioFirst off if you only have 4 disks and you are using Raid 10 you will only
get 140GB of usable space. A Raid10 is a stripe of mirrored disks so you
would loose half the capacity storage wise. Partitioning a single drive
array does nothing for performance. It gives you the illusion that you have
multiple drives when in fact they are only logical in nature and all the
activity is still happening on a single drive array. It has an added down
side that now if you create several partitions and you need more space you
in one of them you may be in a bind. If you only have a single drive array
I recommend you place a single partition on it for maximum usability. Just
be sure to create seperate folders to logically divide your data etc from
your other files.
--
Andrew J. Kelly SQL MVP
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Does the server come with only 4 disk capacity? Where are you planning to
install OS and SQL program files on?
In this case, you are limited. You can only build two RAID 1 mirror sets
with 70 GB maximum space for each. You definitely want to separate data and
log files on different physical arrays. Your best bet is to put logs on the
OS array and data files on separate array. Neither configuration is a good
design but if your database isn't highly resource demanding, you may get
away with it.
Richard
"Fabio" <fabio@.glb.com.br> wrote in message
news:OT4z$HX%23EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> What is the best way to partitioning disks for performance in sql server
> 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have 280GB of
> free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
>|||Fabio wrote:
> Hi all,
> What is the best way to partitioning disks for performance in sql
> server 2000?
> whe have 4 disks of 70 GB, and we want to use RAID-10, so we have
> 280GB of free space.
> What's the best way to partitioning this disks on windows 2003?
> Thanks in Advance,
> Fabio
We don't know your requirements or database sizes, but as Andrew
mentioned you'll lose half your drive space using any mirrored RAID
configuration. I might suggest the following. If this is a small/medium
database, you can do the following:
- Add a fifth drive
- Create a mirrored partition using two of the drives and install the
OS, SQL Server files, tempdb, and the transaction logs there (this is
your C drive). Use hardware mirroring, not software.
- Create a RAID 5 array using the three remaining disks (140GB usable).
RAID 5 has good read performance, but low write performance. For
anything but large systems, the throughput should be more than adequate.
If you let us know more about your requirements, we may be able to offer
alternative solutions.
David Gugick
Imceda Software
www.imceda.com
Wednesday, March 21, 2012
Partitioned View Performance Question
Hi,
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
NickNick Dawson (no.thanks@.nowhere.com) writes:
> I've got a view that union's 3 tables together on the same server. The
> base tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
First of all, use the date format YYYYMMDD. The above constraints will
fail if the connection uses a language where Jan and Dec are not month
names.
Second, write the constraints as
Date >= '20060101' AND Date < '20070101'
since there is no date type in SQL Server, your constraints loses a
day as far as SQL Server is concerned.
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06',
> 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
Do you really get poor performance, or do you just think that the query
plan looks bad?
The query plan is expected. Since SQL Server does not know the value of the
variable before-hand, it must have a plan that involves all tabeles.
But if everything is working OK, you will see that the plan operators
has a STARTUP EXPR. Furthermore, if you run a query with SET STATISTICS ON,
you will see that for two the tables there are 0 logical reads. What
happens is that the SQL Server prunes two of the tables at run-time.
There is one thing that is very important with partioned views, and that
is that the CHECK constraints are *trusted*. It is not sufficient that
they are enabled. If you use a tool like Enterprise Mangager to change
the constraint, it is likely that it use something like:
ALTER TABLE tbl ADD constraint ckc WITH NOCHECK CHECK (Date...)
WITH NOCHECK means that SQL Server should not check whether existing
values conforms to the constraint. This sames time, but leads to the
constraint being not trusted, because there could be non-conformant
data.
To find if you have any constraints that are not trusted do:
SELECT name, xtype, object_id(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What happens when @.d is fed to the proc as a datetime value and you
eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
Hi,
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
Nick|||Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||That's odd. have you tried declaring a datetime variable, assigning it the
date and then selecting from the view, using the parameter?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||I think it's your CHECK constraints. They overlap. See below:
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
'20070101')
GO
These don't overlap, since I changed the <= to <.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||Hhmm, I didn't spot that - nice call.
I changed them as you suggested.
But, these two queries:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
select * from alltmp where DateCol = CONVERT(DATETIME,'2006.02.02')
Still produce different queryplans - the second one is what I'd call
"correct" Even in stats i/o output, the second one clearly only scans one
table whereas the top one scans both of them.
Digging around on google groups a liitle, it appears that other people have
had issues with Datetime columns on partitioning views indicating that it
"doesn't work" Maybe I've encountered a similar problem.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u1NrGqKMGHA.2624@.TK2MSFTNGP12.phx.gbl...
>I think it's your CHECK constraints. They overlap. See below:
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
> '20070101')
> GO
> These don't overlap, since I changed the <= to <.
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
> I just re-created all the constraints with "Check" in place. Thanks for
> pointing that out.
> Unfortunately it doesn't seem to make a difference. Do I need to create
> the
> view in a particular way? - I simply did:
> create view alltmp as
> select * from tmp1
> union all
> select * from tmp2
> I also tried explicitly providing the column names.
> Thanks for your help Tom.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
>
>
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
NickNick Dawson (no.thanks@.nowhere.com) writes:
> I've got a view that union's 3 tables together on the same server. The
> base tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
First of all, use the date format YYYYMMDD. The above constraints will
fail if the connection uses a language where Jan and Dec are not month
names.
Second, write the constraints as
Date >= '20060101' AND Date < '20070101'
since there is no date type in SQL Server, your constraints loses a
day as far as SQL Server is concerned.
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06',
> 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
Do you really get poor performance, or do you just think that the query
plan looks bad?
The query plan is expected. Since SQL Server does not know the value of the
variable before-hand, it must have a plan that involves all tabeles.
But if everything is working OK, you will see that the plan operators
has a STARTUP EXPR. Furthermore, if you run a query with SET STATISTICS ON,
you will see that for two the tables there are 0 logical reads. What
happens is that the SQL Server prunes two of the tables at run-time.
There is one thing that is very important with partioned views, and that
is that the CHECK constraints are *trusted*. It is not sufficient that
they are enabled. If you use a tool like Enterprise Mangager to change
the constraint, it is likely that it use something like:
ALTER TABLE tbl ADD constraint ckc WITH NOCHECK CHECK (Date...)
WITH NOCHECK means that SQL Server should not check whether existing
values conforms to the constraint. This sames time, but leads to the
constraint being not trusted, because there could be non-conformant
data.
To find if you have any constraints that are not trusted do:
SELECT name, xtype, object_id(parent_obj)
FROM sysobjects
WHERE objectproperty(id, 'CnstIsNotTrusted') = 1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What happens when @.d is fed to the proc as a datetime value and you
eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
Hi,
I've got a view that union's 3 tables together on the same server. The base
tables all have a constraint on a date field like:
Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
The view simply "union-all"'s the tables together.
When I do a query like this:
select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
then the graphical query plan clearly shows it going directly to the
clustered index on Table06 as expected
However, when I query like this:
select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
The graphical plan shows it scanning all three base tables which is not what
I hoped would happen.
Could anyone explain why this is? It really hurts performance when I query
the view in a stored-proc.
Thanks
Nick|||Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||That's odd. have you tried declaring a datetime variable, assigning it the
date and then selecting from the view, using the parameter?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
Aplogies Tom, a typo there. I always query the view and not the table
@.d is fed as a datetime value, with a default, to the sp.
The show stats i/o does correctly indicate that it doesn't scan the
underlying tables it doesn't need. Like this:
Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
read-ahead reads 7178.
Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads 0,
read-ahead reads 0.
That's great.
However, if I query the view (outside the sp) like this:
select * from MyView
where [Date] = '09/Feb/06'
the graphical plan it indicates it's spending over 50% of it's time checking
the 2004 and 2005 tables that it doesn't need. The plan also indicates that
it's having to "convert" the date/time parameter.
If I call the select like this:
SELECT *
FROM MyView
WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
It doesn't convert the date, and it goes directly to one underlying table
and the graphical plan doesn't even touch the archive tables.
We've notice some end of day processing taking longer since this view has
been created, and we think it's because it's not optimially selecting from
the "current" table but scanning the archive tables too much.
Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
> What happens when @.d is fed to the proc as a datetime value and you
> eliminate the CONVERT? Also, why do you query "pnl" and not MyView?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:uPxJQ6IMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've got a view that union's 3 tables together on the same server. The
> base
> tables all have a constraint on a date field like:
> Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006')
> Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005')
> The view simply "union-all"'s the tables together.
> When I do a query like this:
> select * from MyView where [Date] = CONVERT(DATETIME, '01/Feb/06', 102)
> then the graphical query plan clearly shows it going directly to the
> clustered index on Table06 as expected
> However, when I query like this:
> select * from pnl where [Date] = CONVERT(DATETIME, @.d, 102)
> The graphical plan shows it scanning all three base tables which is not
> what
> I hoped would happen.
> Could anyone explain why this is? It really hurts performance when I query
> the view in a stored-proc.
> Thanks
> Nick
>
>|||Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
Tom,
Give this a bash:
CREATE TABLE [dbo].[tmp1] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tmp2] (
[IdCol] [int] NOT NULL ,
[DateCol] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
(
[IdCol],
[DateCol]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
'20070101')
GO
No need to insert any data.
Now run this:
select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
00:00:00', 102)
And look at the graphical plan. Looks good to me, as it's only looking at
tmp2 table. This is what I was hoping was happening.
Whereas this:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
Looks at both the tables.
As does this:
declare @.Date datetime
select @.Date = '2006.02.02'
select * from alltmp where DateCol = @.Date
select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
I'm a bit lost! Any ideas are really appreciated.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
> That's odd. have you tried declaring a datetime variable, assigning it
> the
> date and then selecting from the view, using the parameter?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:Ov9AEYJMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Aplogies Tom, a typo there. I always query the view and not the table
> @.d is fed as a datetime value, with a default, to the sp.
> The show stats i/o does correctly indicate that it doesn't scan the
> underlying tables it doesn't need. Like this:
> Table 'tblPNL'. Scan count 4, logical reads 26339, physical reads 4,
> read-ahead reads 7178.
> Table 'tblPNLArchive_2004'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> Table 'tblPNLArchive_2005'. Scan count 0, logical reads 0, physical reads
> 0,
> read-ahead reads 0.
> That's great.
> However, if I query the view (outside the sp) like this:
> select * from MyView
> where [Date] = '09/Feb/06'
> the graphical plan it indicates it's spending over 50% of it's time
> checking
> the 2004 and 2005 tables that it doesn't need. The plan also indicates
> that
> it's having to "convert" the date/time parameter.
> If I call the select like this:
> SELECT *
> FROM MyView
> WHERE ([date] = CONVERT(DATETIME, '2006-02-02 00:00:00', 102))
> It doesn't convert the date, and it goes directly to one underlying table
> and the graphical plan doesn't even touch the archive tables.
> We've notice some end of day processing taking longer since this view has
> been created, and we think it's because it's not optimially selecting from
> the "current" table but scanning the archive tables too much.
> Thanks
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ewXE4OJMGHA.552@.TK2MSFTNGP10.phx.gbl...
>
>|||I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||I think it's your CHECK constraints. They overlap. See below:
ALTER TABLE [dbo].[tmp1] ADD
CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
'20060101')
GO
ALTER TABLE [dbo].[tmp2] ADD
CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
'20070101')
GO
These don't overlap, since I changed the <= to <.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Nick Dawson" <no.thanks@.nowhere.com> wrote in message
news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
I just re-created all the constraints with "Check" in place. Thanks for
pointing that out.
Unfortunately it doesn't seem to make a difference. Do I need to create the
view in a particular way? - I simply did:
create view alltmp as
select * from tmp1
union all
select * from tmp2
I also tried explicitly providing the column names.
Thanks for your help Tom.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
> The NOCHECK's can be problematic. Have you tried doing this WITH CHECK?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23fdY$MKMGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Tom,
> Give this a bash:
> CREATE TABLE [dbo].[tmp1] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tmp2] (
> [IdCol] [int] NOT NULL ,
> [DateCol] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp1] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp2] WITH NOCHECK ADD
> CONSTRAINT [PK_tmp2] PRIMARY KEY CLUSTERED
> (
> [IdCol],
> [DateCol]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <=
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <=
> '20070101')
> GO
> No need to insert any data.
> Now run this:
> select * from alltmp where DateCol = CONVERT(DATETIME, '2006-02-02
> 00:00:00', 102)
> And look at the graphical plan. Looks good to me, as it's only looking at
> tmp2 table. This is what I was hoping was happening.
> Whereas this:
> select * from alltmp where DateCol = '2006-02-02 00:00:00'
> Looks at both the tables.
> As does this:
> declare @.Date datetime
> select @.Date = '2006.02.02'
> select * from alltmp where DateCol = @.Date
> select * from alltmp where CONVERT(DATETIME,@.Date) = DateCol
> I'm a bit lost! Any ideas are really appreciated.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23cFnPIKMGHA.3460@.TK2MSFTNGP15.phx.gbl...
>
>|||Hhmm, I didn't spot that - nice call.
I changed them as you suggested.
But, these two queries:
select * from alltmp where DateCol = '2006-02-02 00:00:00'
select * from alltmp where DateCol = CONVERT(DATETIME,'2006.02.02')
Still produce different queryplans - the second one is what I'd call
"correct" Even in stats i/o output, the second one clearly only scans one
table whereas the top one scans both of them.
Digging around on google groups a liitle, it appears that other people have
had issues with Datetime columns on partitioning views indicating that it
"doesn't work" Maybe I've encountered a similar problem.
Nick
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:u1NrGqKMGHA.2624@.TK2MSFTNGP12.phx.gbl...
>I think it's your CHECK constraints. They overlap. See below:
> ALTER TABLE [dbo].[tmp1] ADD
> CONSTRAINT [CK_tbltmp1] CHECK ([DateCol] >= '20050101' and [DateCol] <
> '20060101')
> GO
> ALTER TABLE [dbo].[tmp2] ADD
> CONSTRAINT [CK_tbltmp2] CHECK ([DateCol] >= '20060101' and [DateCol] <
> '20070101')
> GO
> These don't overlap, since I changed the <= to <.
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Nick Dawson" <no.thanks@.nowhere.com> wrote in message
> news:%23SqmciKMGHA.2744@.TK2MSFTNGP10.phx.gbl...
> I just re-created all the constraints with "Check" in place. Thanks for
> pointing that out.
> Unfortunately it doesn't seem to make a difference. Do I need to create
> the
> view in a particular way? - I simply did:
> create view alltmp as
> select * from tmp1
> union all
> select * from tmp2
> I also tried explicitly providing the column names.
> Thanks for your help Tom.
> Nick
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%236bUodKMGHA.2668@.tk2msftngp13.phx.gbl...
>
>
Partitioned View performance
What is wrong with my partitioned view?
I have split a table into partitioned view by month (see below).
Check the following query information
--this is the original table
select count(*) from activitydetailbackup
where [datetime] between '1/2/2006' and '1/26/2006'
--takes 35 seconds
--this is run against the view
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
--takes over 6 minutes
SET STATISTICS IO ON
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
SET STATISTICS IO OFF
--shows the following
Table 'ActivityDetailBackup200603'. Scan count 4, logical reads 76860,
physical reads 0, read-ahead reads 76865.
Table 'ActivityDetailBackup200602'. Scan count 4, logical reads 2366,
physical reads 0, read-ahead reads 2365.
Table 'ActivityDetailBackup200601'. Scan count 4, logical reads 73249,
physical reads 0, read-ahead reads 73250.
Table 'ActivityDetailBackup200512'. Scan count 4, logical reads 42978,
physical reads 67, read-ahead reads 42930.
Table 'ActivityDetailBackup200511'. Scan count 4, logical reads 44662,
physical reads 67, read-ahead reads 44631.
Table 'ActivityDetailBackup200510'. Scan count 4, logical reads 41996,
physical reads 0, read-ahead reads 41996.
Table 'ActivityDetailBackup200509'. Scan count 4, logical reads 36542,
physical reads 0, read-ahead reads 36546.
Table 'ActivityDetailBackup200508'. Scan count 4, logical reads 41171,
physical reads 0, read-ahead reads 41175.
Table 'ActivityDetailBackup200507'. Scan count 4, logical reads 38037,
physical reads 66, read-ahead reads 38269.
Table 'ActivityDetailBackup200506'. Scan count 4, logical reads 38804,
physical reads 65, read-ahead reads 39051.
Table 'ActivityDetailBackup200505'. Scan count 4, logical reads 40052,
physical reads 70, read-ahead reads 40332.
Table 'ActivityDetailBackup200504'. Scan count 4, logical reads 37436,
physical reads 70, read-ahead reads 37693.
Table 'ActivityDetailBackup200503'. Scan count 4, logical reads 38750,
physical reads 66, read-ahead reads 38890.
Table 'ActivityDetailBackup200502'. Scan count 4, logical reads 32304,
physical reads 73, read-ahead reads 32451.
Table 'ActivityDetailBackup200412'. Scan count 4, logical reads 33051,
physical reads 72, read-ahead reads 33131.
Table 'ActivityDetailBackup200411'. Scan count 4, logical reads 36257,
physical reads 69, read-ahead reads 36429.
Table 'ActivityDetailBackup200410'. Scan count 4, logical reads 24012,
physical reads 69, read-ahead reads 24149.
Table 'ActivityDetailBackup200409'. Scan count 4, logical reads 32, physical
reads 1, read-ahead reads 31.
Each table is created as follows:
CREATE TABLE [dbo].[ActivityDetailBackupYYYYMM](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Querystring] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SessionVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomValue] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [ActivityIDDateYYYYMM] PRIMARY KEY CLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[ActivityDetailBackup200410] WITH NOCHECK ADD CHECK
(([DateTime] >= convert(datetime,'10/1/2004') and [DateTime] <
convert(datetime,'11/1/2004')))
View created as follows:
CREATE VIEW [dbo].[ActivityDetailBackup_view]
AS
SELECT * FROM ActivityDetailBackup200409
UNION ALL
SELECT * FROM ActivityDetailBackup200410
UNION ALL
SELECT * FROM ActivityDetailBackup200411
UNION ALL
SELECT * FROM ActivityDetailBackup200412
UNION ALL
SELECT * FROM ActivityDetailBackup200502
UNION ALL
SELECT * FROM ActivityDetailBackup200503
UNION ALL
SELECT * FROM ActivityDetailBackup200504
UNION ALL
SELECT * FROM ActivityDetailBackup200505
UNION ALL
SELECT * FROM ActivityDetailBackup200506
UNION ALL
SELECT * FROM ActivityDetailBackup200507
UNION ALL
SELECT * FROM ActivityDetailBackup200508
UNION ALL
SELECT * FROM ActivityDetailBackup200509
UNION ALL
SELECT * FROM ActivityDetailBackup200510
UNION ALL
SELECT * FROM ActivityDetailBackup200511
UNION ALL
SELECT * FROM ActivityDetailBackup200512
UNION ALL
SELECT * FROM ActivityDetailBackup200601
UNION ALL
SELECT * FROM ActivityDetailBackup200602
UNION ALL
SELECT * FROM ActivityDetailBackup200603
UNION ALL
SELECT * FROM ActivityDetailBackup200604
Haroldsthe reason might be because of adding the constraint with no check..
so it has to go and check the existing data in all the tables to find out if
rows exists.|||My understanding of the NOCHECK in the ALTER TABLE statement just means to
set the constraint without validating the data currently on the table.
--
Harolds
"Omnibuzz" wrote:
> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.|||your understanding is right. So when you select from the view, it cannot rel
y
on the contraint you have created and has to go and check all the tables.
This is what I believe. If you can try this. create the constraints (without
the no check option and run it
"Harolds" wrote:
> My understanding of the NOCHECK in the ALTER TABLE statement just means to
> set the constraint without validating the data currently on the table.
> --
> Harolds
>
> "Omnibuzz" wrote:
>|||This did the trick.
Thanks for the help,
--
Harolds
"Omnibuzz" wrote:
> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.
I have split a table into partitioned view by month (see below).
Check the following query information
--this is the original table
select count(*) from activitydetailbackup
where [datetime] between '1/2/2006' and '1/26/2006'
--takes 35 seconds
--this is run against the view
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
--takes over 6 minutes
SET STATISTICS IO ON
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
SET STATISTICS IO OFF
--shows the following
Table 'ActivityDetailBackup200603'. Scan count 4, logical reads 76860,
physical reads 0, read-ahead reads 76865.
Table 'ActivityDetailBackup200602'. Scan count 4, logical reads 2366,
physical reads 0, read-ahead reads 2365.
Table 'ActivityDetailBackup200601'. Scan count 4, logical reads 73249,
physical reads 0, read-ahead reads 73250.
Table 'ActivityDetailBackup200512'. Scan count 4, logical reads 42978,
physical reads 67, read-ahead reads 42930.
Table 'ActivityDetailBackup200511'. Scan count 4, logical reads 44662,
physical reads 67, read-ahead reads 44631.
Table 'ActivityDetailBackup200510'. Scan count 4, logical reads 41996,
physical reads 0, read-ahead reads 41996.
Table 'ActivityDetailBackup200509'. Scan count 4, logical reads 36542,
physical reads 0, read-ahead reads 36546.
Table 'ActivityDetailBackup200508'. Scan count 4, logical reads 41171,
physical reads 0, read-ahead reads 41175.
Table 'ActivityDetailBackup200507'. Scan count 4, logical reads 38037,
physical reads 66, read-ahead reads 38269.
Table 'ActivityDetailBackup200506'. Scan count 4, logical reads 38804,
physical reads 65, read-ahead reads 39051.
Table 'ActivityDetailBackup200505'. Scan count 4, logical reads 40052,
physical reads 70, read-ahead reads 40332.
Table 'ActivityDetailBackup200504'. Scan count 4, logical reads 37436,
physical reads 70, read-ahead reads 37693.
Table 'ActivityDetailBackup200503'. Scan count 4, logical reads 38750,
physical reads 66, read-ahead reads 38890.
Table 'ActivityDetailBackup200502'. Scan count 4, logical reads 32304,
physical reads 73, read-ahead reads 32451.
Table 'ActivityDetailBackup200412'. Scan count 4, logical reads 33051,
physical reads 72, read-ahead reads 33131.
Table 'ActivityDetailBackup200411'. Scan count 4, logical reads 36257,
physical reads 69, read-ahead reads 36429.
Table 'ActivityDetailBackup200410'. Scan count 4, logical reads 24012,
physical reads 69, read-ahead reads 24149.
Table 'ActivityDetailBackup200409'. Scan count 4, logical reads 32, physical
reads 1, read-ahead reads 31.
Each table is created as follows:
CREATE TABLE [dbo].[ActivityDetailBackupYYYYMM](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Querystring] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SessionVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomValue] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [ActivityIDDateYYYYMM] PRIMARY KEY CLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[ActivityDetailBackup200410] WITH NOCHECK ADD CHECK
(([DateTime] >= convert(datetime,'10/1/2004') and [DateTime] <
convert(datetime,'11/1/2004')))
View created as follows:
CREATE VIEW [dbo].[ActivityDetailBackup_view]
AS
SELECT * FROM ActivityDetailBackup200409
UNION ALL
SELECT * FROM ActivityDetailBackup200410
UNION ALL
SELECT * FROM ActivityDetailBackup200411
UNION ALL
SELECT * FROM ActivityDetailBackup200412
UNION ALL
SELECT * FROM ActivityDetailBackup200502
UNION ALL
SELECT * FROM ActivityDetailBackup200503
UNION ALL
SELECT * FROM ActivityDetailBackup200504
UNION ALL
SELECT * FROM ActivityDetailBackup200505
UNION ALL
SELECT * FROM ActivityDetailBackup200506
UNION ALL
SELECT * FROM ActivityDetailBackup200507
UNION ALL
SELECT * FROM ActivityDetailBackup200508
UNION ALL
SELECT * FROM ActivityDetailBackup200509
UNION ALL
SELECT * FROM ActivityDetailBackup200510
UNION ALL
SELECT * FROM ActivityDetailBackup200511
UNION ALL
SELECT * FROM ActivityDetailBackup200512
UNION ALL
SELECT * FROM ActivityDetailBackup200601
UNION ALL
SELECT * FROM ActivityDetailBackup200602
UNION ALL
SELECT * FROM ActivityDetailBackup200603
UNION ALL
SELECT * FROM ActivityDetailBackup200604
Haroldsthe reason might be because of adding the constraint with no check..
so it has to go and check the existing data in all the tables to find out if
rows exists.|||My understanding of the NOCHECK in the ALTER TABLE statement just means to
set the constraint without validating the data currently on the table.
--
Harolds
"Omnibuzz" wrote:
> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.|||your understanding is right. So when you select from the view, it cannot rel
y
on the contraint you have created and has to go and check all the tables.
This is what I believe. If you can try this. create the constraints (without
the no check option and run it
"Harolds" wrote:
> My understanding of the NOCHECK in the ALTER TABLE statement just means to
> set the constraint without validating the data currently on the table.
> --
> Harolds
>
> "Omnibuzz" wrote:
>|||This did the trick.
Thanks for the help,
--
Harolds
"Omnibuzz" wrote:
> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.
Monday, February 20, 2012
Parameterized MDX Query Performance Issue in SSRS
Hi,
I'm having issues with a reasonably complex MDX query used in a Reporting Services report. I have executed the query and traced activity on the SSAS box and have discovered that there is a major difference in performance between the parameterized query and the exact same one but with 'hard coded' values. The difference we are talking about is from a couple of seconds (hard coded) to minutes when parameterized!!
The key difference is that the parameterized query is causing litterally hundreds of thousands of Query Dimension events (EventSubClass : 1 - Cache Data) to occur against one of the dimensions (the Date dimension to be exact). This dimension is not even large with only a few hundred leaf level members and about five levels. There is another very large dimension in the cube however, c.1.5million members, upon which subsets of member are being ranked but this doesn't appear in the trace any more often than in the non-parameterized version of the query.
Have I missed something here or is there something I can do to improve the performance ofparameterized queries? Any suggestions or adive whatsoever woould be greatly appreciated.
Thanks,
Rob.
Hi Rob,
Can you post up the query you're using?
Chris
|||Hi Chris... below is the query. Note that I've had to change a few of the member names etc so apologies if I've introduced any syntax errors etc...
With
Member [Measures].[Current Period Count] As
(
STRTOMEMBER(@.DateMember),
[Measures].[Count]
)
Set [Current Period Documents Ordered by Count] As
TOPPERCENT(
NonEmpty(
Descendants(
STRTOMEMBER(@.DocumentMember),
[Documents].[Documents].[Document Title],
SELF
),
[Measures].[Current Period Count]
),
100,
[Measures].[Current Period Count]
)
Member [Measures].[Current Period Document Ranking] As
Rank(
([Documents].[Documents].CurrentMember),
[Current Period Documents Ordered by Count],
-[Measures].[Current Period Count]
), FORMAT_STRING="#;#;-;-"
Member [Measures].[Prev Period Count] As
(
STRTOMEMBER(@.DateMember).PrevMember,
[Measures].[Click Through Count]
), FORMAT_STRING="#;#;-;-"
Set [Prev Period Documents Ordered by Count] As
TOPPERCENT(
NonEmpty(
Descendants(
STRTOMEMBER(@.DocumentMember),
[Documents].[Documents].[Document Title],
SELF
),
[Measures].[Prev Period Count]
),
100,
[Measures].[Prev Period Count]
)
Member [Measures].[Prev Period Document Ranking] As
IIF(
[Measures].[Prev Period Count] > 0,
Rank(
[Documents].[Documents].CurrentMember,
[Prev Period Documents Ordered by Count],
-[Measures].[Prev Period Count]
),
Null
), FORMAT_STRING="#;#;-;-"
Member [Measures].[Param1] As
[Documents].[Documents].CurrentMember.Properties("Param1")
Member [Measures].[Param2] As
[Documents].[Documents].CurrentMember.Properties("Param2")
Select
{
[Measures].[Param1],
[Measures].[Param2],
[Measures].[Current Period Count],
[Measures].[Current Period Ranking],
[Measures].[Prev Period Count],
[Measures].[Prev Period Ranking]
} On 0,
TOPCOUNT(
Filter(
[Current Period Documents Ordered by Count],
[Measures].[Count] > 0
),
30
)
On 1
From
[OLAPDatabase]
Where
(
STRTOMEMBER(@.TimePeriod),
STRTOMEMBER(@.UserMember),
STRTOMEMBER(@.ProductMember)
)
|||Hi Rob,
Two ideas to start off with....
I'm not sure exactly why parameterising the query has such a performance hit, but I think it's because using parameters involves using the StrToX family of functions, and any calculated member which uses these functions is going to incur a performance hit and possibly stop caching being so effective. You've got no choice here though, unless your data source in Reporting Services from using the 'Analysis Services' provider to using the 'OLE DB' provider and not using AS parameters, but dynamically generating the entire query string with Reporting Services expressions - which is pretty unpleasant. Just out of interest though, if you remove the parameters from the calculated member definitions and leave them in place elsewhere in the query (eg in the WHERE clause), what's performance like?|||
Chris, many thanks for your reply.
Yes I've been considering dynamically building the MDX statements as a get around but as you say, it's not pleasant and certainly not elegant... however it looks as if your assumptions were correct as removing the parameters from the calculated members causes a significant improvement to performance. Even leaving the parameters in the Where clause, it's returns the result set only fractionally slower then the non-parameterized query. And by implimenting your second suggestion of replacing the calculated members with Tuples throughout the query the performance is greatly improved from what I had originally. It's still far slower than the non-parameterized version but acceptable.
Many thanks for you swift and effective advice!
Rob.
|||Glad I could help. I don't know enough about your cube or parameters to say whether this would be possible, but potentially the query could be further optimised by removing the parameterisation from the calculated measure definitions and putting it somewhere else; it depends on whether the contents of @.DateMember could be put in the WHERE clause or on a visible axis.
Chris
Subscribe to:
Posts (Atom)