faster query - smaller tables?

My database is much faster now because I found that I only need around
150,000 records in a table for any single query. The date selection will
always be in a certain range.
So if I break my tables up into smaller tables based on the maximum date
range, its much faster.
I don't understand why its 2x's slower if there are more than x-number of
records in the table.. I have tried non-clustered and then clustered indexes
on the date field, and the date + customer id as the key fields... but its
still slower as more records are added to the table. Is this normal?
SELECT DISTINCT A.CustID, B.Discount
FROM Sales A, Discounts B WHERE
A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0.2
If I have 9.5 million records in Sales, the query takes 2 seconds to 4
seconds.
If I have about 60 tables of 150000 records each, and I select from the
appropriate table, the query is about a half second or less.
Is this a common practice, to break tables up based on date range or am I
still doing something wrong?
CREATE TABLE [Sales] (
[SaleDate] [datetime] NOT NULL ,
[CustID] [varchar] (10) NOT NULL ,
[SaleAmt] [DECIMAL (8,3)] NOT NULL
) ON [PRIMARY]
CREATE TABLE [Discounts] (
[DiscountDate] [datetime] NOT NULL ,
[Discount] [DECIMAL (8,3)] NOT NULL
[Code] [numeric] NOT NULL
) ON [PRIMARY]Rich
You may want to read 'Creating a Partitioned View' article in the BOL.
Does the optimizer available to use indexes defined on the table?
"Rich" <no@.spam.invalid> wrote in message
news:UekHe.54059$4o.18050@.fed1read06...
> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered
> indexes
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount >
> 0.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>|||Hi
Where are your indexes?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" wrote:

> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>
>|||Dear Rich,
When processing a join, The SQL optimizer evaluates all reasonable join
permutations and estimates the total I/O cost, in terms of I/O time. The pla
n
resulting in the lowest estimate of I/O time is the plan chosen.
Please Note that As the number of tables increases, then the number of
permutations that the optimizer must evaluate increases as a factorial of th
e
number of tables in the query:
I.E: Nbr Of Tables is 2 then Nbr Of Permutions On SQL is 2!
Nbr Of Tables is 3 then Nbr Of Permutions On SQL is 3!
Nbr Of Tables is 4 then Nbr Of Permutions On SQL is 4!
and so on...
In Your case you have 2 tables and as i can see they are not related(No Join
In Between), one way to solve your problem and optimize your query processin
g
time is either you filter your tables Discounts and Sales and then build the
join,
Or you build a relation between both tables on primary indexes and then make
your criteria fields as clustered indexes(i.e: SaleDate , DiscountDate,
Discount)
i prefer you combine both ways
Good Luck
Mario Aoun
"Rich" wrote:

> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]
>
>|||I was just curious about what is the approximate criterion
which allows an optimizer to tell that
T3 joined T1 joined T2
is faster than
T1 joined T2 joined T3 ?
Is it based on the number of columns? Can't be based on
rows because their number is unknown before the join
and would take to much time to compute it...
Any information or pointer on this problem?
Thank you
- Pamela
.NET developer|||Rich,
Why aren't the two tables joined in the query? Do all Customers get all
Discounts? That doesn't seem to make sense (from a data model point of
view).
Assuming you do have some common key (and you join on it in the query),
then this type of query would benefit from a clustered index on the date
range, or from a covering index (see BOL for more details).
Hope this helps,
Gert-Jan
Rich wrote:
> My database is much faster now because I found that I only need around
> 150,000 records in a table for any single query. The date selection will
> always be in a certain range.
> So if I break my tables up into smaller tables based on the maximum date
> range, its much faster.
> I don't understand why its 2x's slower if there are more than x-number of
> records in the table.. I have tried non-clustered and then clustered index
es
> on the date field, and the date + customer id as the key fields... but its
> still slower as more records are added to the table. Is this normal?
> SELECT DISTINCT A.CustID, B.Discount
> FROM Sales A, Discounts B WHERE
> A.SaleDate = '20000117' AND B.DiscountDate = '20000118' AND B.Discount > 0
.2
> If I have 9.5 million records in Sales, the query takes 2 seconds to 4
> seconds.
> If I have about 60 tables of 150000 records each, and I select from the
> appropriate table, the query is about a half second or less.
> Is this a common practice, to break tables up based on date range or am I
> still doing something wrong?
> CREATE TABLE [Sales] (
> [SaleDate] [datetime] NOT NULL ,
> [CustID] [varchar] (10) NOT NULL ,
> [SaleAmt] [DECIMAL (8,3)] NOT NULL
> ) ON [PRIMARY]
> CREATE TABLE [Discounts] (
> [DiscountDate] [datetime] NOT NULL ,
> [Discount] [DECIMAL (8,3)] NOT NULL
> [Code] [numeric] NOT NULL
> ) ON [PRIMARY]|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42EE6C4F.DD1000E0@.toomuchspamalready.nl...
> Rich,
> Why aren't the two tables joined in the query? Do all Customers get all
> Discounts? That doesn't seem to make sense (from a data model point of
> view).
> Assuming you do have some common key (and you join on it in the query),
> then this type of query would benefit from a clustered index on the date
> range, or from a covering index (see BOL for more details).
> Hope this helps,
> Gert-Jan
That helps, thanks.
Richard
> Rich wrote:
of
indexes
its
0.2
I

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Timnot entirely sure why my browser posted my original message again!
sorry.

thanks guys for your help so far.
yes I have indexes on the columns

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

Tim|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

erland - I don't quite get what you are doing with creating and
dropping a table and a view?

The DROP at the end is just cleanup. The script was meant to show how
you create an indexed view and how to use it.

--
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|||I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.

Thanks

Tim|||"C10B" <tswalton@.gmail.comwrote in message
news:1172494748.828877.126110@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?
I'll check it out though and let you know if it's any quicker.
>
Thanks
>
Tim
>

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||

Quote:

Originally Posted by

If you want more help, I suggest you give us a complete DDL of your
database, some real example data and perhaps we can do better.

The requirement is simply this...

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"

with unlimited segmenting of the data really.

So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.

Any other tips?

Thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I'm still mildly puzzled as to how an indexed view would be quicker to
function than going straight to the table. Surely there's exactly the
same amount of work to be done?

No. An indexed view is materialised, and this particular view has one
row per webpage with the count of visitors. An alternative would be have
a separate table with the counts, and update that table through a trigger.
But why roll our own when SQL Server can do the job for us?

--
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|||"C10B" <tswalton@.gmail.comwrote in message
news:1172502298.291068.60420@.s48g2000cws.googlegro ups.com...

Quote:

Originally Posted by

>

Quote:

Originally Posted by

>If you want more help, I suggest you give us a complete DDL of your
>database, some real example data and perhaps we can do better.

>
>
The requirement is simply this...
>
I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>

Yes, we understand the requirements. That doesn't eliminate the value of a
DDL and sample data.

Quote:

Originally Posted by

"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.
>

Define slow?

(my former employer did queries similar to this in subsecond times.)

Quote:

Originally Posted by

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.
>
Any other tips?

Yes, post a full DDL.

Quote:

Originally Posted by

>
Thanks
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a lot of pages and I want to record when each is visited.
I then need to be able to answer questions like this...
>
"Which was the most popular page in January 07?"
"Which are the top 10 most viewed pages between 2 and 3 in the
afternoon"
>
with unlimited segmenting of the data really.
>
So I thought I would record an entry in a table everytime a page is
viewed, along with the date and time.
Using some simple queries using "count(*)", a where clause and a group
by clause I was answering all the questions.
Trouble is the speed. Several million records (and growing rapidly)
and count(*) gets slow.

You did not say anything about wanting to get the counts for a certain
day, so I interpreted your question that you wanted total counts of
the entire table. Whence my suggestion of an indexed view.

If you are not able to relate clearly what you want to do, then the
answers you get will be equally imprecise.

Quote:

Originally Posted by

Just while I was writing this I had an idea - I could move a finished
month's data to another table, so each month has a table of data. It
might still be a million records though, but at least it wont get
worse and worse over time. This would make some results harder to get
like "most viewed page of all time" but my monthy, hourly, weekly
figures would be quicker.

There is no need for that. If you always want to run these count
queries for a certain period, make sure that there is a clustered
index on the datetime column in your table.

--
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

faster count(*) or alternative

hi,

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00

I now need to find the most popular pages so I do this...

SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC

...which gives me the top 10 most viewed pages

big problem - this is getting slower and slower as the table grows and
grows.

what should I do?

is there an alternative?

I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"

tia

Tim"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.

First question, do you have an index on the table.

That should help.

Quote:

Originally Posted by

>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?

A non-clustered index on place would help, as SQL Server then only
would have to scan that index, and not the entire difference. But it
seems that the table has two columns. In such case, the index would not
reduce execution time that much.

A better alternative may be to define an indexed view that maintains
the count:

CREATE TABLE pagehits (pageid varchar(20) NOT NULL,
viewtime datetime NOT NULL,
PRIMARY KEY (pageid, viewtime)
)
go
CREATE VIEW pagecount WITH SCHEMABINDING AS
SELECT pageid, cnt = COUNT_BIG(*)
FROM dbo.pagehits
GROUP BY pageid
go
CREATE UNIQUE CLUSTERED INDEX pagecount_ix ON pagecount(pageid)
go
SELECT TOP 10 pageid, cnt
FROM pagecount WITH (NOEXPAND)
ORDER BY cnt
go
DROP VIEW pagecount
DROP TABLE pagehits

I added the NOEXPAND hint to the query, since it's only on Enterprise
Edition, the optimizer considers indexed views.

Note that this could have effect on performance when writing to the table.

--
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|||Fastest returning query would be on a separate table that maintains the
count to the granularity you would require in your output (week, day, or
hour). A query that requires more than hourly granularity can still be done
on the source table.

For each new week|day|hour add a row for each page to be tracked. This can
be done dynamically during monitoring but it is better to do it ahead of
time.

Then put an insert trigger on your million row table that will increment the
count on the proper row of the table.

An alternative would be a temp table that contains a subset copy of the
source data for the time range most likely to queried. For example a table
that contains the last 31 days of data, the 32nd day of data
deleted/archived every night.

"C10B" <tswalton@.gmail.comwrote in message
news:1172439270.641426.83160@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

hi,
>
I have a table with several million rows.
Each row is simply the date and time a certain page was viewed.
>
eg
page1 1-1-00
page2 2-1-00
page1 16-1-00
page1 17-1-00
page2 19-1-00
>
I now need to find the most popular pages so I do this...
>
SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViews
GROUP BY place ORDER BY COUNT(place) DESC
>
...which gives me the top 10 most viewed pages
>
big problem - this is getting slower and slower as the table grows and
grows.
>
what should I do?
>
is there an alternative?
>
I think I need all the rows (rather than a simple incrementing
counter) because I might want to say "what was the most popular page
on a certain day or within a certain period"
>
tia
>
Tim
>

|||erland - you are quite right, I could have worded my first post a
little better

greg - I don't know what a DDL is

russ - thanks|||C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

greg - I don't know what a DDL is

DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.

If I had had a penny for every time someone says DDL in answer, without the
person asking having no idea what it means, I would be a rich man now.

Quote:

Originally Posted by

russ - thanks

What Russ proposed is the same idea that I proposed, but rather than relying
SQL Server updating an indexed view, he suggested a separate table that you
update through a trigger.

--
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|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98E480B95D736Yazorman@.127.0.0.1...

Quote:

Originally Posted by

C10B (tswalton@.gmail.com) writes:

Quote:

Originally Posted by

>greg - I don't know what a DDL is

>
DDL = Data Definition Language. That is, CREATE TABLE and CREATE INDEX and
the like.
>
If I had had a penny for every time someone says DDL in answer, without
the
person asking having no idea what it means, I would be a rich man now.
>

Quote:

Originally Posted by

>russ - thanks

>
What Russ proposed is the same idea that I proposed, but rather than
relying
SQL Server updating an indexed view, he suggested a separate table that
you
update through a trigger.
>

Indexed view is probably best, I just like having more control.

Quote:

Originally Posted by

>
--
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

Fast Date Ranges Without Loops In SQL Server 2000

The trick is to use a pivot table

Check out the code here

http://sqlservercode.blogspot.com/2...ops-in-sql.htmltechnically that does have a loop right?|||To populate the table yes, but that's a one time thing

I was talking specifically about the select statement

http://sqlservercode.blogspot.com/|||I have added the code to split a character string without tedious
looping but using a pivot table instead
go to my blog to look at the code

http://sqlservercode.blogspot.com/

Farmat number into Date

I am reporting off an oracle database that has a field with a number that is
the date. ie 20041201 (yyyymmdd)
What would be the best way to convert this into a date with the format
dd/mm/yy.
I regularly use this field with a parameter to allow my usres to generate
their own reports.
Thanks in advanceLook at the DateTime structure and its constructors:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
The following expression would convert the field into a valid DateTime
object:
=new DateTime(Left(CStr(Fields!Date.Value), 4), Mid(CStr(Fields!Date.Value),
5, 2), Right(CStr(Fields!Date.Value), 2))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:9EA0FDAC-20C4-459A-AD30-AE5F439A38B2@.microsoft.com...
>I am reporting off an oracle database that has a field with a number that
>is
> the date. ie 20041201 (yyyymmdd)
> What would be the best way to convert this into a date with the format
> dd/mm/yy.
> I regularly use this field with a parameter to allow my usres to generate
> their own reports.
> Thanks in advance|||Thank you Robert,
At what point would you recommend i use this expression.
Is it possible to use at the dataset stage and if so how.
Regards
Todd
"Robert Bruckner [MSFT]" wrote:
> Look at the DateTime structure and its constructors:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
> The following expression would convert the field into a valid DateTime
> object:
> =new DateTime(Left(CStr(Fields!Date.Value), 4), Mid(CStr(Fields!Date.Value),
> 5, 2), Right(CStr(Fields!Date.Value), 2))
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:9EA0FDAC-20C4-459A-AD30-AE5F439A38B2@.microsoft.com...
> >I am reporting off an oracle database that has a field with a number that
> >is
> > the date. ie 20041201 (yyyymmdd)
> >
> > What would be the best way to convert this into a date with the format
> > dd/mm/yy.
> > I regularly use this field with a parameter to allow my usres to generate
> > their own reports.
> >
> > Thanks in advance
>
>|||If you want to perform the conversion in the query instead of inside the
report (or by adding a calculated field on the dataset), you have to find
function calls in the query language that allow you to convert the field
into a DateTime object. The data provider / database server of the data
source will determine the language that has to be used (T-SQL, PL/SQL, etc.)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:63B337CF-3563-4DA4-AC77-18429117803F@.microsoft.com...
> Thank you Robert,
> At what point would you recommend i use this expression.
> Is it possible to use at the dataset stage and if so how.
> Regards
> Todd
> "Robert Bruckner [MSFT]" wrote:
>> Look at the DateTime structure and its constructors:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
>> The following expression would convert the field into a valid DateTime
>> object:
>> =new DateTime(Left(CStr(Fields!Date.Value), 4),
>> Mid(CStr(Fields!Date.Value),
>> 5, 2), Right(CStr(Fields!Date.Value), 2))
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Tango" <Tango@.discussions.microsoft.com> wrote in message
>> news:9EA0FDAC-20C4-459A-AD30-AE5F439A38B2@.microsoft.com...
>> >I am reporting off an oracle database that has a field with a number
>> >that
>> >is
>> > the date. ie 20041201 (yyyymmdd)
>> >
>> > What would be the best way to convert this into a date with the format
>> > dd/mm/yy.
>> > I regularly use this field with a parameter to allow my usres to
>> > generate
>> > their own reports.
>> >
>> > Thanks in advance
>>

Fails to launch SQL Profiler 2005

Hi,
There seems to be a problem with SQL Profiler 2005 recently. It was working
till date and recently it is not starting up all of a sudden. I get the below
message.
PROFILER90.EXE - Application Error : The application failed to initialize
properly (0xc0000005).
Let me know if you need any information regarding the softwares installed on
my machine.
More information never hurts, but on other forms it's talking about .NET 3.0
being the issue.
Ref Link:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1628587&SiteID=1
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Sriram Mallajyosula" wrote:

> Hi,
> There seems to be a problem with SQL Profiler 2005 recently. It was working
> till date and recently it is not starting up all of a sudden. I get the below
> message.
> PROFILER90.EXE - Application Error : The application failed to initialize
> properly (0xc0000005).
> Let me know if you need any information regarding the softwares installed on
> my machine.