I am building a report and would like only certain data to show in a cell.
For instance I have 8 pay codes in my select statement, but would like to
show just 4 of them in the detail section. I don't want to limit my select
statement to just the 4 because I need the other 4 in another cell of this
report. Any ideas?
Its almost like I need a where clause within my particular cell.
Thanks,
RyanAlso, I thought about using multiple data sources and create a new table, but
is there a way to link two different data sources in a report? For instance
my report is on employee id and I would need to make sure that the data from
both data sources in the row detail are for the same employee id.
"Ryan Mcbee" wrote:
> I am building a report and would like only certain data to show in a cell.
> For instance I have 8 pay codes in my select statement, but would like to
> show just 4 of them in the detail section. I don't want to limit my select
> statement to just the 4 because I need the other 4 in another cell of this
> report. Any ideas?
> Its almost like I need a where clause within my particular cell.
> Thanks,
> Ryan|||Maybe you can use Filters.
In your data properties, select tab "Filters" and configure there the values
you want to show.
Does it help?
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> escribió en el mensaje
news:0F3503DD-A100-47C5-B4F3-B36EE6AF8BBE@.microsoft.com...
> Also, I thought about using multiple data sources and create a new table,
> but
> is there a way to link two different data sources in a report? For
> instance
> my report is on employee id and I would need to make sure that the data
> from
> both data sources in the row detail are for the same employee id.
> "Ryan Mcbee" wrote:
>> I am building a report and would like only certain data to show in a
>> cell.
>> For instance I have 8 pay codes in my select statement, but would like to
>> show just 4 of them in the detail section. I don't want to limit my
>> select
>> statement to just the 4 because I need the other 4 in another cell of
>> this
>> report. Any ideas?
>> Its almost like I need a where clause within my particular cell.
>> Thanks,
>> Ryan|||I will give that a shot. I built some case statements in my syntax to
seperate the data better.
"Mónica" wrote:
> Maybe you can use Filters.
> In your data properties, select tab "Filters" and configure there the values
> you want to show.
> Does it help?
>
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> escribió en el mensaje
> news:0F3503DD-A100-47C5-B4F3-B36EE6AF8BBE@.microsoft.com...
> > Also, I thought about using multiple data sources and create a new table,
> > but
> > is there a way to link two different data sources in a report? For
> > instance
> > my report is on employee id and I would need to make sure that the data
> > from
> > both data sources in the row detail are for the same employee id.
> >
> > "Ryan Mcbee" wrote:
> >
> >> I am building a report and would like only certain data to show in a
> >> cell.
> >> For instance I have 8 pay codes in my select statement, but would like to
> >> show just 4 of them in the detail section. I don't want to limit my
> >> select
> >> statement to just the 4 because I need the other 4 in another cell of
> >> this
> >> report. Any ideas?
> >>
> >> Its almost like I need a where clause within my particular cell.
> >>
> >> Thanks,
> >> Ryan
>
>
Showing posts with label statement. Show all posts
Showing posts with label statement. Show all posts
Monday, March 26, 2012
Making DATEDIFF "flexible"
I have the following SQL statement that works out how many days are between todays date and the first of April 2007.
SELECT DATEDIFF(dd,'2007-04-01 00:00:00.000',GetDate()) AS 'Days Left'
This works fine, however: After the 1st April 2007 I want to start counting down the days till 1st April 2008 - and so on and so forth.
How can I do this? Hopefully you understand my question - if not, ask me any questions needed!
Cheers - GeorgeVHope I'm not putting my foot in my mouth here, I'm just learning SQL:
SELECT DATEDIFF (dd,'2007-04-' & Year(GetDate)+1 ,GetDate())AS 'Days_Left';
I don't know if you can perform that calculation in the middle there like that. Is there a way you can perform the calculation before the select statement and load it into a variable? I haven't yet seen variable in SQL so I don't know if that can be done.
EDIT: I realized concatenate was needed|||I have tried things like:
SELECT DATEDIFF(dd, YEAR(GetDate())&'-04-01 00:00:00.000',GetDate()) AS 'Days Left'
But you get the error message:
Server: Msg 245, Level 16, State 1, Line 9
Syntax error converting the varchar value '-04-01 00:00:00.000' to a column of data type int.
--
EDIT: Variables appear to be working!
DECLARE @.Year AS VarChar(30) SET @.Year = YEAR(GetDate())
SELECT DATEDIFF(dd, @.Year + '-04-01 00:00:00.000',GetDate()) AS 'Days Left'|||suck, I hoped that would do it.
maybe a case statement? it's not pretty but if you did cases through 2025, you should be ok, I would think.|||Here's how to do it without the variable
SELECT DATEDIFF(dd, cast(Year(getdate()) as char(4)) + '-04-01 00:00:00.000',GetDate()) AS 'Days Left'|||Aye RNG, if you wrap ABs round it (or swap the cast statement with the GetDate() :)
Ok, know to make it harder...
Say this was the 2nd of April I'd want to get a result of:
0 years, 11 months, 29 days.
(if you get me :p)|||suck, I hoped that would do it.
maybe a case statement? it's not pretty but if you did cases through 2025, you should be ok, I would think.
It did, it did, it did!
I musta been editing it as you posted ;)
Cheers stark|||Ok, I'm in a particularly evil mood today. I'll give you a solution, then let you sus out which line actually solves your problem and maybe even work out how the demo statement does what it does!SELECT Convert(CHAR(10), d, 121)
, DateDiff(day, d, Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
FROM (SELECT DateAdd(day, z31.d + z32.d + z33.d + z43.d + z53.d + z63.d, GetDate()) AS d
FROM (SELECT 0 AS d UNION SELECT 1 UNION SELECT 2) AS z31
CROSS JOIN (SELECT 0 AS d UNION SELECT 3 UNION SELECT 6) AS z32
CROSS JOIN (SELECT 0 AS d UNION SELECT 9 UNION SELECT 18) AS z33
CROSS JOIN (SELECT 0 AS d UNION SELECT 27 UNION SELECT 54) AS z43
CROSS JOIN (SELECT 0 AS d UNION SELECT 81 UNION SELECT 162) AS z53
CROSS JOIN (SELECT 0 AS d UNION SELECT 243 UNION SELECT 486) AS z63) AS z
ORDER BY d-PatP|||Ok Pat, I accept your challenge.
However it will have to wait till tomorrow morning at workies!
Never heard of a CROSS join before *ponders*|||Oh, I think you'll have fun working this one out... The puzzle is one that is tough for most folks to get their head around at first, but then a wonderful thing once they "grok" it. The neat thing about a simple statment like this is that you can demonstrate that it works (just run the silly thing), then you can sit down and tear it apart to see the inner workings.
I sometimes throw these out for the kids with assignments... They can see that they've got the solution, but by the time they understand it well enough to turn it in, they've already learned a LOT more about SQL than it would have taken to just do their homework and be done with it. The neat thing about these is that for the business user who only needs a solution, they are sufficient. For the serious SQL user, they are a chance to learn new things. For the student looking for someone to do their homework, they are just plain useless.
Our beloved R937 coined a name for these kind of solutions, an NZDF or "Non-Zero Deviousity Factor" solution. Every so often I enjoy creating one, although I almost always use them for cases where I'm not sure about the business need, and in your case I'll take that as a given... I just thought you'd enjoy the puzzle.
-PatP|||I bet I will ;)
I already figured a small part of it out before I left the office at 5:30.
I always prefer the challenge - hate answers on a plate (unless I've been hitting my head against a brick wall for days).
This problem is not something I need the answer to, just something I want - and I'm sure your query will help me learn the trickery I need :D
I get like that sometimes - always wanting to go above and beyond a probelm just to learn (I think that's what helped me land this job ;))
Cheers again!|||I get like that sometimes - always wanting to go above and beyond a probelm just to learn (I think that's what helped me land this job ;))
Oh, to be young and not yet jaded...|||youch, I took a shot at this.
I don't think I got it either, all I got was
todays date as milliseconds, the number of days between today and April 1, 2007. I'm anxious to see George's answer and then the actual answer.
Thanks for the exercise.|||poor man's tally table ;)|||SELECT Convert(CHAR(10), d, 121)
, DateDiff(day, d, Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
FROM (SELECT DateAdd(day, z31.d + z32.d + z33.d + z43.d + z53.d + z63.d, GetDate()) AS d
FROM (SELECT 0 AS d UNION SELECT 1 UNION SELECT 2) AS z31
CROSS JOIN (SELECT 0 AS d UNION SELECT 3 UNION SELECT 6) AS z32
CROSS JOIN (SELECT 0 AS d UNION SELECT 9 UNION SELECT 18) AS z33
CROSS JOIN (SELECT 0 AS d UNION SELECT 27 UNION SELECT 54) AS z43
CROSS JOIN (SELECT 0 AS d UNION SELECT 81 UNION SELECT 162) AS z53
CROSS JOIN (SELECT 0 AS d UNION SELECT 243 UNION SELECT 486) AS z63) AS z
ORDER BY d
121 = yyyy-mm-dd hh:mi:ss.mmm(24h)
0 = mon dd yyyy hh:miAM (or PM)
3 = dd/mm/yy
6 = dd mon yy
9 = mon dd yyyy hh:mi:ss:mmmAM (or PM)
...
Am I on the right track?
EDIT: Re-read, ignore the above (bar 121) because it's carp ;)
EDIT: I'd like to point out that the above was assumed without running the code :p|||Am I on the right track?
don't think so. I gave you a little hint above btw.
try running the cross join stuff in exclusion of the rest, what does it do?|||EDIT: Re-read, ignore the above (bar 121) because it's carp ;)
EDIT: I'd like to point out that the above was assumed without running the code :p
Hehe - I have no access to SS at home ;)|||I've got what the cross join select m'job does.
0 + 0 + 0 + 0 + 0 + 0 = 0
1 + 0 + 0 + 0 + 0 + 0 = 1
2 + 0 + 0 + 0 + 0 + 0 = 2
0 + 3 + 0 + 0 + 0 + 0 = 3
1 + 2 + 0 + 0 + 0 + 0 = 4
.. and so on
Correct?|||(Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
And this bit gives me the correct year for the dateadd!
So adding days to the result of the above will give me the count...
Apart from when it is the 1st of April it says 366 instead of 0.
So now I want the query to return:
[Todays Date] [Days remaining]
Just a single line.
Possible?
EDIT: God this is hard to explain :p|||SELECT Convert(CHAR(10), GetDate(), 103) AS 'Todays Date',
DateDiff(day, GetDate(), Cast(Year(GetDate()) + CASE WHEN 3 < Month(GetDate()) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01') AS 'Days Remaining'
:D Fairly sure this is right now - just unsure what will happen when we get to April 1st or even - next year - I think it will be right.
EDIT:
Answer - if today was april 1st - displays 366 :(
I think it works right for next year though! :p|||There actually ARE 366 days between 2007-04-01 and 2008-04-01. I'll celebrate my first anniversary on DBForums in there!
-PatP|||There are?
surely there are only 365 BETWEEN in a leap year..?
On the day - the difference between days should read 0 - shouldn't it?|||By that reasoning, there should only be 364 if there isn't a leap year. Try executing:SELECT
DateDiff(day, '2006-04-01', '2007-04-01')
, DateDiff(day, '2007-04-01', '2008-04-01')-PatP|||I'll celebrate my first anniversary on DBForums in there!tee hee hee ;)
nice to see you back in form, old buddy
SELECT DATEDIFF(dd,'2007-04-01 00:00:00.000',GetDate()) AS 'Days Left'
This works fine, however: After the 1st April 2007 I want to start counting down the days till 1st April 2008 - and so on and so forth.
How can I do this? Hopefully you understand my question - if not, ask me any questions needed!
Cheers - GeorgeVHope I'm not putting my foot in my mouth here, I'm just learning SQL:
SELECT DATEDIFF (dd,'2007-04-' & Year(GetDate)+1 ,GetDate())AS 'Days_Left';
I don't know if you can perform that calculation in the middle there like that. Is there a way you can perform the calculation before the select statement and load it into a variable? I haven't yet seen variable in SQL so I don't know if that can be done.
EDIT: I realized concatenate was needed|||I have tried things like:
SELECT DATEDIFF(dd, YEAR(GetDate())&'-04-01 00:00:00.000',GetDate()) AS 'Days Left'
But you get the error message:
Server: Msg 245, Level 16, State 1, Line 9
Syntax error converting the varchar value '-04-01 00:00:00.000' to a column of data type int.
--
EDIT: Variables appear to be working!
DECLARE @.Year AS VarChar(30) SET @.Year = YEAR(GetDate())
SELECT DATEDIFF(dd, @.Year + '-04-01 00:00:00.000',GetDate()) AS 'Days Left'|||suck, I hoped that would do it.
maybe a case statement? it's not pretty but if you did cases through 2025, you should be ok, I would think.|||Here's how to do it without the variable
SELECT DATEDIFF(dd, cast(Year(getdate()) as char(4)) + '-04-01 00:00:00.000',GetDate()) AS 'Days Left'|||Aye RNG, if you wrap ABs round it (or swap the cast statement with the GetDate() :)
Ok, know to make it harder...
Say this was the 2nd of April I'd want to get a result of:
0 years, 11 months, 29 days.
(if you get me :p)|||suck, I hoped that would do it.
maybe a case statement? it's not pretty but if you did cases through 2025, you should be ok, I would think.
It did, it did, it did!
I musta been editing it as you posted ;)
Cheers stark|||Ok, I'm in a particularly evil mood today. I'll give you a solution, then let you sus out which line actually solves your problem and maybe even work out how the demo statement does what it does!SELECT Convert(CHAR(10), d, 121)
, DateDiff(day, d, Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
FROM (SELECT DateAdd(day, z31.d + z32.d + z33.d + z43.d + z53.d + z63.d, GetDate()) AS d
FROM (SELECT 0 AS d UNION SELECT 1 UNION SELECT 2) AS z31
CROSS JOIN (SELECT 0 AS d UNION SELECT 3 UNION SELECT 6) AS z32
CROSS JOIN (SELECT 0 AS d UNION SELECT 9 UNION SELECT 18) AS z33
CROSS JOIN (SELECT 0 AS d UNION SELECT 27 UNION SELECT 54) AS z43
CROSS JOIN (SELECT 0 AS d UNION SELECT 81 UNION SELECT 162) AS z53
CROSS JOIN (SELECT 0 AS d UNION SELECT 243 UNION SELECT 486) AS z63) AS z
ORDER BY d-PatP|||Ok Pat, I accept your challenge.
However it will have to wait till tomorrow morning at workies!
Never heard of a CROSS join before *ponders*|||Oh, I think you'll have fun working this one out... The puzzle is one that is tough for most folks to get their head around at first, but then a wonderful thing once they "grok" it. The neat thing about a simple statment like this is that you can demonstrate that it works (just run the silly thing), then you can sit down and tear it apart to see the inner workings.
I sometimes throw these out for the kids with assignments... They can see that they've got the solution, but by the time they understand it well enough to turn it in, they've already learned a LOT more about SQL than it would have taken to just do their homework and be done with it. The neat thing about these is that for the business user who only needs a solution, they are sufficient. For the serious SQL user, they are a chance to learn new things. For the student looking for someone to do their homework, they are just plain useless.
Our beloved R937 coined a name for these kind of solutions, an NZDF or "Non-Zero Deviousity Factor" solution. Every so often I enjoy creating one, although I almost always use them for cases where I'm not sure about the business need, and in your case I'll take that as a given... I just thought you'd enjoy the puzzle.
-PatP|||I bet I will ;)
I already figured a small part of it out before I left the office at 5:30.
I always prefer the challenge - hate answers on a plate (unless I've been hitting my head against a brick wall for days).
This problem is not something I need the answer to, just something I want - and I'm sure your query will help me learn the trickery I need :D
I get like that sometimes - always wanting to go above and beyond a probelm just to learn (I think that's what helped me land this job ;))
Cheers again!|||I get like that sometimes - always wanting to go above and beyond a probelm just to learn (I think that's what helped me land this job ;))
Oh, to be young and not yet jaded...|||youch, I took a shot at this.
I don't think I got it either, all I got was
todays date as milliseconds, the number of days between today and April 1, 2007. I'm anxious to see George's answer and then the actual answer.
Thanks for the exercise.|||poor man's tally table ;)|||SELECT Convert(CHAR(10), d, 121)
, DateDiff(day, d, Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
FROM (SELECT DateAdd(day, z31.d + z32.d + z33.d + z43.d + z53.d + z63.d, GetDate()) AS d
FROM (SELECT 0 AS d UNION SELECT 1 UNION SELECT 2) AS z31
CROSS JOIN (SELECT 0 AS d UNION SELECT 3 UNION SELECT 6) AS z32
CROSS JOIN (SELECT 0 AS d UNION SELECT 9 UNION SELECT 18) AS z33
CROSS JOIN (SELECT 0 AS d UNION SELECT 27 UNION SELECT 54) AS z43
CROSS JOIN (SELECT 0 AS d UNION SELECT 81 UNION SELECT 162) AS z53
CROSS JOIN (SELECT 0 AS d UNION SELECT 243 UNION SELECT 486) AS z63) AS z
ORDER BY d
121 = yyyy-mm-dd hh:mi:ss.mmm(24h)
0 = mon dd yyyy hh:miAM (or PM)
3 = dd/mm/yy
6 = dd mon yy
9 = mon dd yyyy hh:mi:ss:mmmAM (or PM)
...
Am I on the right track?
EDIT: Re-read, ignore the above (bar 121) because it's carp ;)
EDIT: I'd like to point out that the above was assumed without running the code :p|||Am I on the right track?
don't think so. I gave you a little hint above btw.
try running the cross join stuff in exclusion of the rest, what does it do?|||EDIT: Re-read, ignore the above (bar 121) because it's carp ;)
EDIT: I'd like to point out that the above was assumed without running the code :p
Hehe - I have no access to SS at home ;)|||I've got what the cross join select m'job does.
0 + 0 + 0 + 0 + 0 + 0 = 0
1 + 0 + 0 + 0 + 0 + 0 = 1
2 + 0 + 0 + 0 + 0 + 0 = 2
0 + 3 + 0 + 0 + 0 + 0 = 3
1 + 2 + 0 + 0 + 0 + 0 = 4
.. and so on
Correct?|||(Cast(Year(d) + CASE WHEN 3 < Month(d) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01')
And this bit gives me the correct year for the dateadd!
So adding days to the result of the above will give me the count...
Apart from when it is the 1st of April it says 366 instead of 0.
So now I want the query to return:
[Todays Date] [Days remaining]
Just a single line.
Possible?
EDIT: God this is hard to explain :p|||SELECT Convert(CHAR(10), GetDate(), 103) AS 'Todays Date',
DateDiff(day, GetDate(), Cast(Year(GetDate()) + CASE WHEN 3 < Month(GetDate()) THEN 1 ELSE 0 END AS CHAR(4)) + '-04-01') AS 'Days Remaining'
:D Fairly sure this is right now - just unsure what will happen when we get to April 1st or even - next year - I think it will be right.
EDIT:
Answer - if today was april 1st - displays 366 :(
I think it works right for next year though! :p|||There actually ARE 366 days between 2007-04-01 and 2008-04-01. I'll celebrate my first anniversary on DBForums in there!
-PatP|||There are?
surely there are only 365 BETWEEN in a leap year..?
On the day - the difference between days should read 0 - shouldn't it?|||By that reasoning, there should only be 364 if there isn't a leap year. Try executing:SELECT
DateDiff(day, '2006-04-01', '2007-04-01')
, DateDiff(day, '2007-04-01', '2008-04-01')-PatP|||I'll celebrate my first anniversary on DBForums in there!tee hee hee ;)
nice to see you back in form, old buddy
Friday, March 23, 2012
Making a create statement from existing database
We have a Peoplesoft application built over MS server 2000. The peoplesoft
application has created a table in ms server via its application designer
where the field types, sizes, indexes are automatically generated from the
data dictionary and search keys etc.
What I would like to know is how can I inquire via query analyser the
structure of these tables ( sort of reverse enginering the SQL DDL statement
s
) so that I can then end up with an SQL create statement etc.
thanks.listTableColumns should get you pretty close:
http://www.aspfaq.com/2177
"JD" <JD@.discussions.microsoft.com> wrote in message
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.|||"JD" <JD@.discussions.microsoft.com> wrote in message
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.
Take a look at INFORMATION_SCHEMA in the Books Online. In particular, you
will want to pay attention to INFORMATION_SCHEMA.TABLES and .COLUMNS.
As for the indexes and so forth, that will be a bit more tricky.
Rick Sawtell
MCT, MCSD, MCDBA|||If you can use Enterprise Manager, there is a wizard that generates scripts.
You can pick the specific table you're interested in and save it creation
script. There's options to include indexes, primary keys, etc.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> listTableColumns should get you pretty close:
> http://www.aspfaq.com/2177
>
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
>
>|||Try Creating a SQL Script from Enterprise Manager. Save it and then open it
up with Query Analyzer
"JD" <JD@.discussions.microsoft.com> escribi en el mensaje
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.
application has created a table in ms server via its application designer
where the field types, sizes, indexes are automatically generated from the
data dictionary and search keys etc.
What I would like to know is how can I inquire via query analyser the
structure of these tables ( sort of reverse enginering the SQL DDL statement
s
) so that I can then end up with an SQL create statement etc.
thanks.listTableColumns should get you pretty close:
http://www.aspfaq.com/2177
"JD" <JD@.discussions.microsoft.com> wrote in message
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.|||"JD" <JD@.discussions.microsoft.com> wrote in message
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.
Take a look at INFORMATION_SCHEMA in the Books Online. In particular, you
will want to pay attention to INFORMATION_SCHEMA.TABLES and .COLUMNS.
As for the indexes and so forth, that will be a bit more tricky.
Rick Sawtell
MCT, MCSD, MCDBA|||If you can use Enterprise Manager, there is a wizard that generates scripts.
You can pick the specific table you're interested in and save it creation
script. There's options to include indexes, primary keys, etc.
Joe
"Aaron Bertrand [SQL Server MVP]" wrote:
> listTableColumns should get you pretty close:
> http://www.aspfaq.com/2177
>
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
>
>|||Try Creating a SQL Script from Enterprise Manager. Save it and then open it
up with Query Analyzer
"JD" <JD@.discussions.microsoft.com> escribi en el mensaje
news:56C8824E-D60E-4965-A62F-55B893D8F7F4@.microsoft.com...
> We have a Peoplesoft application built over MS server 2000. The peoplesoft
> application has created a table in ms server via its application designer
> where the field types, sizes, indexes are automatically generated from the
> data dictionary and search keys etc.
> What I would like to know is how can I inquire via query analyser the
> structure of these tables ( sort of reverse enginering the SQL DDL
> statements
> ) so that I can then end up with an SQL create statement etc.
> thanks.
Monday, March 19, 2012
Make Stored Proc Faster
Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:
ALTER PROCEDURE sproc_ReturnAvailability
@.ExtractDate DateTime,
@.DateFrom DateTime,
@.DateTo DateTime,
@.96hrPlusFlag int,
@.AppointmentsCount int OUTPUT
AS
IF @.96hrPlusFlag = 0
BEGIN
SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @.DateFrom AND @.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE
BEGIN
IF @.96hrPlusFlag = 1
SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime >@.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
Cheers...You need to LEFT OUTER JOIN tbl_Appointment rather than use NOT IN on it. And in the WHERE clause filter in records where SurgerySlotKey IS NOT NULL.
ALTER PROCEDURE sproc_ReturnAvailability
@.ExtractDate DateTime,
@.DateFrom DateTime,
@.DateTo DateTime,
@.96hrPlusFlag int,
@.AppointmentsCount int OUTPUT
AS
IF @.96hrPlusFlag = 0
BEGIN
SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @.DateFrom AND @.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE
BEGIN
IF @.96hrPlusFlag = 1
SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime >@.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
Cheers...You need to LEFT OUTER JOIN tbl_Appointment rather than use NOT IN on it. And in the WHERE clause filter in records where SurgerySlotKey IS NOT NULL.
Monday, March 12, 2012
make 1 record with Union statement
Hi there,
I've made a join query:
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM QTYORDER',
A.QTYONHND AS 'QTYONHND'
FROM IV00102 A LEFT JOIN
IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
UNION
SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
QTYORDER',
0 AS 'QTYONHND'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
GROUP BY ITEMDESC, ITEMNMBR
the result looks like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
0 -477
Proactiv (ITEM) S.NL.543
6000 0
what I want is to produce 1 record which will look like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
6000 -477
any suggestions on how to do this?
Thanks in advance,
SusannaUse the UNION-ed query as a derived table & use aggregate functions on the
outer query. i.e :
SELECT MAX( itemdesc ) AS "item_desc",
MAX( itemnbr ) AS "item_nbr",
...
FROM ( < your query with UNION > ) D
Anith|||What are you doing here? It could either be the max, or adding. I am
assuming that it doesn't matter because the 0 values actually mean not
applicable in your main query.
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
Like anith says, you can just do a group, or possibly something like this,
assuming that you are actually putting out one row per ITEMMBR (if not the
union isn't going to work well either)
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, A.QTYONHND AS
'QTYONHND',
(SELECT SUM(QTYORDER) AS 'SUM QTYORDER'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR =
A.ITEMNMBR) as 'QTYORDER'
FROM IV00102 A
LEFT JOIN IV00101 B
ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2)
AND A.LOCNCODE IN ('SALES')
AND A.ITEMNMBR = 'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
Do you have some issues with poor data quality? I notice that ITEMDESC
comes from an outer join (which makes me wonder what that table is for) and
what the uniqueness is on. In your second query it seems to be itemnmber.
If it is, this should work (or something close.)
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:5D04D744-C679-487D-85F9-8AFBC2D3A844@.microsoft.com...
> Hi there,
> I've made a join query:
> SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM
> QTYORDER',
> A.QTYONHND AS 'QTYONHND'
> FROM IV00102 A LEFT JOIN
> IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
> WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
> 'S.NL.543'
> GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
> A.ATYALLOC, A.QTYSOLD
> UNION
> SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
> QTYORDER',
> 0 AS 'QTYONHND'
> FROM POP10110
> WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
> GROUP BY ITEMDESC, ITEMNMBR
> the result looks like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
> what I want is to produce 1 record which will look like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 6000 -477
> any suggestions on how to do this?
> --
> Thanks in advance,
> Susanna
I've made a join query:
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM QTYORDER',
A.QTYONHND AS 'QTYONHND'
FROM IV00102 A LEFT JOIN
IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
UNION
SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
QTYORDER',
0 AS 'QTYONHND'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
GROUP BY ITEMDESC, ITEMNMBR
the result looks like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
0 -477
Proactiv (ITEM) S.NL.543
6000 0
what I want is to produce 1 record which will look like this:
ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
Proactiv (ITEM) S.NL.543
6000 -477
any suggestions on how to do this?
Thanks in advance,
SusannaUse the UNION-ed query as a derived table & use aggregate functions on the
outer query. i.e :
SELECT MAX( itemdesc ) AS "item_desc",
MAX( itemnbr ) AS "item_nbr",
...
FROM ( < your query with UNION > ) D
Anith|||What are you doing here? It could either be the max, or adding. I am
assuming that it doesn't matter because the 0 values actually mean not
applicable in your main query.
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
Like anith says, you can just do a group, or possibly something like this,
assuming that you are actually putting out one row per ITEMMBR (if not the
union isn't going to work well either)
SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, A.QTYONHND AS
'QTYONHND',
(SELECT SUM(QTYORDER) AS 'SUM QTYORDER'
FROM POP10110
WHERE POLNESTA IN (2) AND ITEMNMBR =
A.ITEMNMBR) as 'QTYORDER'
FROM IV00102 A
LEFT JOIN IV00101 B
ON A.ITEMNMBR = B.ITEMNMBR
WHERE A.RCRDTYPE IN (2)
AND A.LOCNCODE IN ('SALES')
AND A.ITEMNMBR = 'S.NL.543'
GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
A.ATYALLOC, A.QTYSOLD
Do you have some issues with poor data quality? I notice that ITEMDESC
comes from an outer join (which makes me wonder what that table is for) and
what the uniqueness is on. In your second query it seems to be itemnmber.
If it is, this should work (or something close.)
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:5D04D744-C679-487D-85F9-8AFBC2D3A844@.microsoft.com...
> Hi there,
> I've made a join query:
> SELECT B.ITEMDESC AS ITEMDESC, A.ITEMNMBR AS ITEMNMBR, 0 AS 'SUM
> QTYORDER',
> A.QTYONHND AS 'QTYONHND'
> FROM IV00102 A LEFT JOIN
> IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
> WHERE A.RCRDTYPE IN (2) AND A.LOCNCODE IN ('SALES') AND A.ITEMNMBR =
> 'S.NL.543'
> GROUP BY B.ITEMDESC, A.ITEMNMBR, A.LOCNCODE, A.QTYONHND, A.QTYBKORD,
> A.ATYALLOC, A.QTYSOLD
> UNION
> SELECT ITEMDESC AS ITEMDESC, ITEMNMBR AS ITEMNMBR, SUM(QTYORDER) AS 'SUM
> QTYORDER',
> 0 AS 'QTYONHND'
> FROM POP10110
> WHERE POLNESTA IN (2) AND ITEMNMBR = 'S.NL.543'
> GROUP BY ITEMDESC, ITEMNMBR
> the result looks like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 0 -477
> Proactiv (ITEM) S.NL.543
> 6000 0
> what I want is to produce 1 record which will look like this:
> ITEMDESC ITEMNMBR SUM QTYORDER QTYONHND
> Proactiv (ITEM) S.NL.543
> 6000 -477
> any suggestions on how to do this?
> --
> Thanks in advance,
> Susanna
Friday, March 9, 2012
Major performance hit in select statement once database reaches 50,000 records
I'm running SQL Server 2005 (64bit) Evaluation Edition. I'm doing an evaluation on performance for an internal project here at work.
The machine has 2GB of RAM and is an AMD 64 running Windows XP 64 with SQL Server 2005 64 Eval
The database has about 200,000 records in the person table.
Select * from person is the statements "very simple"
The results up to 49,000 are acceptable then at 50,000 it goes into a crawl and starts returniong 100 records every 2 seconds.
Does anyone know what is going on here?
I assume you have a very good reason for sending tens of thousands of records back to a client... You may easily solve your problem by using something like a where clause.
The number of records in a table likely has little to do with the issue, more likely is that is has to do with the overall number of pages. For something like a SELECT * the very best plan you can get is a clustered index scan, providing you have a clustered index. Barring the presence of a clustered index you'll see a table scan.
Either one of these plans are going to generate a substantial amount of disk IO. On a machine with 2GB of ram the maximum size for data cache will be around 1.6GB, my guess is that your table exceeds that size and this simple query is creating a great deal of activity on your disk drives.
Another issue is going to be concurrency. For a SELECT * you might well be attempting to acquire a table lock up front.
Start by getting the query plan for your statement. You can do that with either SET STATISTICS PROFILE ON or SET STATISTICS XML ON.
You should also have a look at the amount of disk IO generated by the query, and the performance of your disk drives.
SET STATISTICS IO ON and SET STATISTICS TIME ON will help out there, and you'll need to start perfmon and have a look at the physical disk counters for avg disk sec/read, avg disk sec/write, avg disk sec/transfer for the drives where your data and log files are located. Anything over 10ms is cause for concern.
Be sure to try from different clients as well to rule out any client issues.
|||
Thank you for the input. I ran it again with your recommendations and yes my Disk Write is max'd out completley. The table has 174,000 records of sample data and the query took 1Hour and 29Minutes.
Here is what I got back from prefixing my SQL Select Statement:
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
select * from consultants;
174511 1 select * from consultants; 1 1 0 NULL NULL NULL NULL 174511 NULL NULL NULL 20.51673 NULL NULL SELECT 0 NULL
174511 1 |--Clustered Index Scan(OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK])) 1 2 1 Clustered Index Scan Clustered Index Scan OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK]) [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. 174511 20.32461 0.1921191 7222 20.51673 [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. NULL PLAN_ROW 0 1
Any guidance would be appreciated.
|||Well - that certainly is a wide table. Again I assume you have a good reason to send back tens of thousands of rows to a client. If this is for a performance test, I hope this is not indicative of how the application is written.
Since you already have a clustered index scan in the plan the only thing that might help you out here is to defrag the index, assuming you have not already done so.
I find it odd that your disk writes are impacted. There should be no write activity at all generated by SQL Server during execution of this statement, if you have writes then you need to find out what is using your drive and stop it.
I'm really curious about the client though, it seems that results should start flowing immediately. Can you replicate this same behavior from SQLCMD and managment studio?
|||Perhaps it is spooling to tempdb?|||How do u know the disk size is max out
Major performance hit in select statement once database reaches 50,000 records
I'm running SQL Server 2005 (64bit) Evaluation Edition. I'm doing an evaluation on performance for an internal project here at work.
The machine has 2GB of RAM and is an AMD 64 running Windows XP 64 with SQL Server 2005 64 Eval
The database has about 200,000 records in the person table.
Select * from person is the statements "very simple"
The results up to 49,000 are acceptable then at 50,000 it goes into a crawl and starts returniong 100 records every 2 seconds.
Does anyone know what is going on here?
I assume you have a very good reason for sending tens of thousands of records back to a client... You may easily solve your problem by using something like a where clause.
The number of records in a table likely has little to do with the issue, more likely is that is has to do with the overall number of pages. For something like a SELECT * the very best plan you can get is a clustered index scan, providing you have a clustered index. Barring the presence of a clustered index you'll see a table scan.
Either one of these plans are going to generate a substantial amount of disk IO. On a machine with 2GB of ram the maximum size for data cache will be around 1.6GB, my guess is that your table exceeds that size and this simple query is creating a great deal of activity on your disk drives.
Another issue is going to be concurrency. For a SELECT * you might well be attempting to acquire a table lock up front.
Start by getting the query plan for your statement. You can do that with either SET STATISTICS PROFILE ON or SET STATISTICS XML ON.
You should also have a look at the amount of disk IO generated by the query, and the performance of your disk drives.
SET STATISTICS IO ON and SET STATISTICS TIME ON will help out there, and you'll need to start perfmon and have a look at the physical disk counters for avg disk sec/read, avg disk sec/write, avg disk sec/transfer for the drives where your data and log files are located. Anything over 10ms is cause for concern.
Be sure to try from different clients as well to rule out any client issues.
|||
Thank you for the input. I ran it again with your recommendations and yes my Disk Write is max'd out completley. The table has 174,000 records of sample data and the query took 1Hour and 29Minutes.
Here is what I got back from prefixing my SQL Select Statement:
SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
select * from consultants;
174511 1 select * from consultants; 1 1 0 NULL NULL NULL NULL 174511 NULL NULL NULL 20.51673 NULL NULL SELECT 0 NULL
174511 1 |--Clustered Index Scan(OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK])) 1 2 1 Clustered Index Scan Clustered Index Scan OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK]) [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. 174511 20.32461 0.1921191 7222 20.51673 [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. NULL PLAN_ROW 0 1
Any guidance would be appreciated.
|||Well - that certainly is a wide table. Again I assume you have a good reason to send back tens of thousands of rows to a client. If this is for a performance test, I hope this is not indicative of how the application is written.
Since you already have a clustered index scan in the plan the only thing that might help you out here is to defrag the index, assuming you have not already done so.
I find it odd that your disk writes are impacted. There should be no write activity at all generated by SQL Server during execution of this statement, if you have writes then you need to find out what is using your drive and stop it.
I'm really curious about the client though, it seems that results should start flowing immediately. Can you replicate this same behavior from SQLCMD and managment studio?
|||Perhaps it is spooling to tempdb?|||How do u know the disk size is max out
Subscribe to:
Posts (Atom)