query assistance: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

Wednesday, March 28, 2012

Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Try:
select
u.Name
, c.CommitteeName -- assumes this column exists
, uc.TermEndDate
from
Users u
join
User_mm_Committee uc on u uc.UserID = u.UserID
join
Committees c on c.CommitteeId = uc.CommitteeID
where
uc.TermEndDate < dateadd (dd, 60, getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!|||Thanks for the help, Tom. I think I can get this to work now.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:upaed5aiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> u.Name
> , c.CommitteeName -- assumes this column exists
> , uc.TermEndDate
> from
> Users u
> join
> User_mm_Committee uc on u uc.UserID = u.UserID
> join
> Committees c on c.CommitteeId = uc.CommitteeID
> where
> uc.TermEndDate < dateadd (dd, 60, getdate())
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a
> Committee for a specified Term, at which point they are either re-elected
> or
> replaced. I want to produce a Query that shows me this - a record for
> each
> vacancy that will be created in the next 60 days (a term expires). An
> example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>sql

query help

I want to find days when people either haven't entered at least 8 hours of
time or days where they haven't entered time at all.
If I select out of the table that holds the time I can get days where they
haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
emp_hours < 8). But how do I also get days where time hasn't been entered
since those days won't be in the table?
Thanks,
--
Dan D.Create a calendar table and do an outer join against that table. Some info on calendar tables:
http://www.aspfaq.com/show.asp?id=2519
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BED0D9F5-2515-4A14-B902-B2334EF59A57@.microsoft.com...
>I want to find days when people either haven't entered at least 8 hours of
> time or days where they haven't entered time at all.
> If I select out of the table that holds the time I can get days where they
> haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
> emp_hours < 8). But how do I also get days where time hasn't been entered
> since those days won't be in the table?
> Thanks,
> --
> Dan D.|||Is there any other way? I'd rather not have to maintain another table.
Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Create a calendar table and do an outer join against that table. Some info on calendar tables:
> http://www.aspfaq.com/show.asp?id=2519
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BED0D9F5-2515-4A14-B902-B2334EF59A57@.microsoft.com...
> >I want to find days when people either haven't entered at least 8 hours of
> > time or days where they haven't entered time at all.
> >
> > If I select out of the table that holds the time I can get days where they
> > haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
> > emp_hours < 8). But how do I also get days where time hasn't been entered
> > since those days won't be in the table?
> >
> > Thanks,
> > --
> > Dan D.
>|||You can create that table on the fly, as a temp table. But I fail to see that problem of having such
table. You create it once and for all. If you hold 10 years, it is only about 3650 rows in it!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BA3C552B-FA37-4657-B77A-1074C512E9AD@.microsoft.com...
> Is there any other way? I'd rather not have to maintain another table.
> Thanks,
> --
> Dan D.
>
> "Tibor Karaszi" wrote:
>> Create a calendar table and do an outer join against that table. Some info on calendar tables:
>> http://www.aspfaq.com/show.asp?id=2519
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:BED0D9F5-2515-4A14-B902-B2334EF59A57@.microsoft.com...
>> >I want to find days when people either haven't entered at least 8 hours of
>> > time or days where they haven't entered time at all.
>> >
>> > If I select out of the table that holds the time I can get days where they
>> > haven't entered at least 8 hours (i.e. select emp_hours from emp_time where
>> > emp_hours < 8). But how do I also get days where time hasn't been entered
>> > since those days won't be in the table?
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>

Query Help

Query Hangs/Time out

I have an intermittent problem that I have not been able to fix.
I have a database that will all of a sudden start timing out on a particular
query that uses both stored procedures and Views on an aspx page. If I
perform the same query in Query Analyzer it will just hang...I've let it go
10 minutes and no result.
I looked for blocked processes when this occurs - there are none. I've
checked the error log, nothing.
To correct this problem, I've found that if I simply copy the database to a
devel box the query on the prod server will again work fine - then we can go
several hours to several days before it starts hanging again.
Now, if I try to put the prod database in single user, then detach, then
re-attach, the query still hangs.
I'm assuming a reboot of the server itself would do the trick too...but
avoiding that for obvious reasons.
We have not run into this on any of our other databases on either the
production server or our development desktops. We have not been able to
recreate this condition on a test system.
The devel/test boxes are xp pro..latest service paks.
The production server is win2003..latest service paks too.
We are on SQL2000 SP4 on all systems.
Has anyone heard of this? Any help would be appreciated.Reid,
I would suggest tunning the query(s). Generate a query plan and concentrate
on the steps that take up most of the % of the execution plan. Add indexes
where identified typically columns used in the where clause are a good place
to start and see if that helps. If there are indexes already on the table(s)
and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
they will preform better. I had the exact same issue with the aspx page that
went into production and ran fine for 3 months and they hung. I added an
index or two and it started running in less than a 1 second so indexes are a
wonderful thing.
Hope this helps,
Rick Butler MCP
"Reid" wrote:
> I have an intermittent problem that I have not been able to fix.
> I have a database that will all of a sudden start timing out on a particular
> query that uses both stored procedures and Views on an aspx page. If I
> perform the same query in Query Analyzer it will just hang...I've let it go
> 10 minutes and no result.
> I looked for blocked processes when this occurs - there are none. I've
> checked the error log, nothing.
> To correct this problem, I've found that if I simply copy the database to a
> devel box the query on the prod server will again work fine - then we can go
> several hours to several days before it starts hanging again.
> Now, if I try to put the prod database in single user, then detach, then
> re-attach, the query still hangs.
> I'm assuming a reboot of the server itself would do the trick too...but
> avoiding that for obvious reasons.
> We have not run into this on any of our other databases on either the
> production server or our development desktops. We have not been able to
> recreate this condition on a test system.
> The devel/test boxes are xp pro..latest service paks.
> The production server is win2003..latest service paks too.
> We are on SQL2000 SP4 on all systems.
> Has anyone heard of this? Any help would be appreciated.|||RcsterMan,
Thanks for the suggestions. I've actually done all of that...they where
the first things I tried. This situation is intermittent...if I was having
tuning issues would I not be having more consistant hangs? After I do the DB
copy, or reboot the server, the site runs fine and runs the querys in several
seconds - which is quite acceptable due to the complexity of the query. But
then it simply hangs - the performance doesn't degrade, it simply hangs -
While I can do other searches/query's with no issues.
I knew this would be a very nebulas problem to bug out..any other suggestions?
Thanks in advance for your time,
Reid
"RicsterMan" wrote:
> Reid,
> I would suggest tunning the query(s). Generate a query plan and concentrate
> on the steps that take up most of the % of the execution plan. Add indexes
> where identified typically columns used in the where clause are a good place
> to start and see if that helps. If there are indexes already on the table(s)
> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
> they will preform better. I had the exact same issue with the aspx page that
> went into production and ran fine for 3 months and they hung. I added an
> index or two and it started running in less than a 1 second so indexes are a
> wonderful thing.
> Hope this helps,
> Rick Butler MCP
> "Reid" wrote:
> > I have an intermittent problem that I have not been able to fix.
> >
> > I have a database that will all of a sudden start timing out on a particular
> > query that uses both stored procedures and Views on an aspx page. If I
> > perform the same query in Query Analyzer it will just hang...I've let it go
> > 10 minutes and no result.
> >
> > I looked for blocked processes when this occurs - there are none. I've
> > checked the error log, nothing.
> >
> > To correct this problem, I've found that if I simply copy the database to a
> > devel box the query on the prod server will again work fine - then we can go
> > several hours to several days before it starts hanging again.
> >
> > Now, if I try to put the prod database in single user, then detach, then
> > re-attach, the query still hangs.
> >
> > I'm assuming a reboot of the server itself would do the trick too...but
> > avoiding that for obvious reasons.
> >
> > We have not run into this on any of our other databases on either the
> > production server or our development desktops. We have not been able to
> > recreate this condition on a test system.
> >
> > The devel/test boxes are xp pro..latest service paks.
> >
> > The production server is win2003..latest service paks too.
> >
> > We are on SQL2000 SP4 on all systems.
> >
> > Has anyone heard of this? Any help would be appreciated.|||What is the wait type when the query hangs? Is it doing
anything or just actually just sitting there - did you check
CPU, Memory, IO for the SPID while the query appears to
hang?
What's going on with the locks when this happens? Have you
run profiler to see what else is going on? Did you also
monitor for recompiles, statistics being updated? Did you
check for issues with file size changes during this?
Anything in particular going on with system itself at the
time the process seems to hang?
-Sue
On Mon, 16 Jan 2006 20:56:03 -0800, "Reid"
<Reid@.discussions.microsoft.com> wrote:
>RcsterMan,
>Thanks for the suggestions. I've actually done all of that...they where
>the first things I tried. This situation is intermittent...if I was having
>tuning issues would I not be having more consistant hangs? After I do the DB
>copy, or reboot the server, the site runs fine and runs the querys in several
>seconds - which is quite acceptable due to the complexity of the query. But
>then it simply hangs - the performance doesn't degrade, it simply hangs -
>While I can do other searches/query's with no issues.
>I knew this would be a very nebulas problem to bug out..any other suggestions?
>Thanks in advance for your time,
>Reid
>"RicsterMan" wrote:
>> Reid,
>> I would suggest tunning the query(s). Generate a query plan and concentrate
>> on the steps that take up most of the % of the execution plan. Add indexes
>> where identified typically columns used in the where clause are a good place
>> to start and see if that helps. If there are indexes already on the table(s)
>> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
>> they will preform better. I had the exact same issue with the aspx page that
>> went into production and ran fine for 3 months and they hung. I added an
>> index or two and it started running in less than a 1 second so indexes are a
>> wonderful thing.
>> Hope this helps,
>> Rick Butler MCP
>> "Reid" wrote:
>> > I have an intermittent problem that I have not been able to fix.
>> >
>> > I have a database that will all of a sudden start timing out on a particular
>> > query that uses both stored procedures and Views on an aspx page. If I
>> > perform the same query in Query Analyzer it will just hang...I've let it go
>> > 10 minutes and no result.
>> >
>> > I looked for blocked processes when this occurs - there are none. I've
>> > checked the error log, nothing.
>> >
>> > To correct this problem, I've found that if I simply copy the database to a
>> > devel box the query on the prod server will again work fine - then we can go
>> > several hours to several days before it starts hanging again.
>> >
>> > Now, if I try to put the prod database in single user, then detach, then
>> > re-attach, the query still hangs.
>> >
>> > I'm assuming a reboot of the server itself would do the trick too...but
>> > avoiding that for obvious reasons.
>> >
>> > We have not run into this on any of our other databases on either the
>> > production server or our development desktops. We have not been able to
>> > recreate this condition on a test system.
>> >
>> > The devel/test boxes are xp pro..latest service paks.
>> >
>> > The production server is win2003..latest service paks too.
>> >
>> > We are on SQL2000 SP4 on all systems.
>> >
>> > Has anyone heard of this? Any help would be appreciated.|||Sue,
I'll need to wait until it starts doing it again. Right now it is working
as it is supposed to. I'll pay attention to the info on the SPID as
suggested.
I have run profiler. In fact, I believe at one time when the query was
hanging I ran the Index tuning wizard and it came back with some suggestions
that I accepted. Tried to re-run the query and it continued to hang. So I
did the copy db again and tried the query and it worked fine.
The rest of the server appears to work fine when this is occuring. I can do
other queries/operations on the site and no problems. It seems to be
isolated to this one db.
I'll post when I have more info..thanks for you time.
"Sue Hoegemeier" wrote:
> What is the wait type when the query hangs? Is it doing
> anything or just actually just sitting there - did you check
> CPU, Memory, IO for the SPID while the query appears to
> hang?
> What's going on with the locks when this happens? Have you
> run profiler to see what else is going on? Did you also
> monitor for recompiles, statistics being updated? Did you
> check for issues with file size changes during this?
> Anything in particular going on with system itself at the
> time the process seems to hang?
> -Sue
> On Mon, 16 Jan 2006 20:56:03 -0800, "Reid"
> <Reid@.discussions.microsoft.com> wrote:
> >RcsterMan,
> >
> >Thanks for the suggestions. I've actually done all of that...they where
> >the first things I tried. This situation is intermittent...if I was having
> >tuning issues would I not be having more consistant hangs? After I do the DB
> >copy, or reboot the server, the site runs fine and runs the querys in several
> >seconds - which is quite acceptable due to the complexity of the query. But
> >then it simply hangs - the performance doesn't degrade, it simply hangs -
> >While I can do other searches/query's with no issues.
> >
> >I knew this would be a very nebulas problem to bug out..any other suggestions?
> >
> >Thanks in advance for your time,
> >Reid
> >
> >"RicsterMan" wrote:
> >
> >> Reid,
> >> I would suggest tunning the query(s). Generate a query plan and concentrate
> >> on the steps that take up most of the % of the execution plan. Add indexes
> >> where identified typically columns used in the where clause are a good place
> >> to start and see if that helps. If there are indexes already on the table(s)
> >> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
> >> they will preform better. I had the exact same issue with the aspx page that
> >> went into production and ran fine for 3 months and they hung. I added an
> >> index or two and it started running in less than a 1 second so indexes are a
> >> wonderful thing.
> >>
> >> Hope this helps,
> >> Rick Butler MCP
> >>
> >> "Reid" wrote:
> >>
> >> > I have an intermittent problem that I have not been able to fix.
> >> >
> >> > I have a database that will all of a sudden start timing out on a particular
> >> > query that uses both stored procedures and Views on an aspx page. If I
> >> > perform the same query in Query Analyzer it will just hang...I've let it go
> >> > 10 minutes and no result.
> >> >
> >> > I looked for blocked processes when this occurs - there are none. I've
> >> > checked the error log, nothing.
> >> >
> >> > To correct this problem, I've found that if I simply copy the database to a
> >> > devel box the query on the prod server will again work fine - then we can go
> >> > several hours to several days before it starts hanging again.
> >> >
> >> > Now, if I try to put the prod database in single user, then detach, then
> >> > re-attach, the query still hangs.
> >> >
> >> > I'm assuming a reboot of the server itself would do the trick too...but
> >> > avoiding that for obvious reasons.
> >> >
> >> > We have not run into this on any of our other databases on either the
> >> > production server or our development desktops. We have not been able to
> >> > recreate this condition on a test system.
> >> >
> >> > The devel/test boxes are xp pro..latest service paks.
> >> >
> >> > The production server is win2003..latest service paks too.
> >> >
> >> > We are on SQL2000 SP4 on all systems.
> >> >
> >> > Has anyone heard of this? Any help would be appreciated.
>

Query Hangs/Time out

Wednesday, March 21, 2012

query filling tempdb

Hello,
I've got a user who has been trying to run a query for the last few
days. Every time he tries to run it, the tempdb grows to be about 5G and
ends up filling up the hard drive. Anyone have any insight into why
this particular query is filling up the tempdb like this?
SELECT top 50
SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
dbo.CIMSDetail.AccountCode As Acct_Code,
dbo.CIMSDetail.StartDate AS StartDay,
dbo.CIMSDetail.EndDate AS EndDay,
dbo.CIMSDetail.RateCode as RateCode,
dbo.CIMSDetail.ResourceUnits AS Volume,
dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
FROM dbo.CIMSDetail INNER JOIN
dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID = dbo.CIMSDetailIdent.DetailUID AND
dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber = dbo.CIMSIdent.IdentNumber
WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) = 1
AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
GROUP BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
ORDER BY
DATEPART(yyyy, dbo.CIMSDetail.EndDate),
DATEPART(mm, dbo.CIMSDetail.EndDate),
DATEPART(dd, dbo.CIMSDetail.EndDate),
SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
dbo.CIMSDetail.RateCode,
dbo.CIMSDetail.StartDate,
dbo.CIMSDetail.EndDate,
dbo.CIMSIdent.IdentDescription,
dbo.CIMSDetailIdent.IdentValue,
dbo.CIMSDetail.AccountCode,
SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You might consider normalization. :-) Why is all that discrete data in one
column? The database has to do a lot of work separating it out with
SUBSTRING like that.
You might also consider putting tempdb on a drive with more than 5GB free.
:-)
"Rachael Faber" <rfaber@.alldata.net> wrote in message
news:eJh59sOjDHA.3192@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I've got a user who has been trying to run a query for the last few
> days. Every time he tries to run it, the tempdb grows to be about 5G and
> ends up filling up the hard drive. Anyone have any insight into why
> this particular query is filling up the tempdb like this?
> SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26, 3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
> FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID => dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine = dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber => dbo.CIMSIdent.IdentNumber
> WHERE DATEPART(DD,GETDATE()) - DATEPART(DD, dbo.CIMSDetail.EndDate) => 1
> AND dbo.CIMSDetail.RateCode IN ('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription = 'WORK_ID'
> GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
> ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Typically coputed values in your select statement (like
all of the substring statements) require use of tempdb
space, and the group by and order by portions of your
query will also require a large amount of tempdb space.
In addition to Aaron's comments, you may want to consider
using a summary table that stores some of these
calculated/summary values so that you don't have to
calculate them within the query itself. While this will
use space within your actual database, this will be
easier to monitor and control then the space allocated on
the fly within tempdb.
Just an idea. I hope that this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I've got a user who has been trying to run a query for
the last few
>days. Every time he tries to run it, the tempdb grows to
be about 5G and
>ends up filling up the hard drive. Anyone have any
insight into why
>this particular query is filling up the tempdb like this?
>SELECT top 50
> SUBSTRING(dbo.CIMSDetail.AccountCode, 1, 1) AS
Corp,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 2, 2) AS
BillGroup,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 4, 7) As
BillEntity,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2) AS
LOB,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 13, 3) AS
Portfolio,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 16, 1) AS
WorkType.
> SUBSTRING(dbo.CIMSDetail.AccountCode, 17, 3) AS
Client,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 20, 2) AS
Service,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 22, 2) AS
Service Type,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 24, 2) AS
Product,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 26,
3) AS Department,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3) AS
LOC,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4) AS
LPAR,
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 18) AS
ORIGSUM,
> dbo.CIMSDetail.AccountCode As Acct_Code,
> dbo.CIMSDetail.StartDate AS StartDay,
> dbo.CIMSDetail.EndDate AS EndDay,
> dbo.CIMSDetail.RateCode as RateCode,
> dbo.CIMSDetail.ResourceUnits AS Volume,
> dbo.CIMSDetailIdent.IdentValue AS Ident_Value,
> dbo.CIMSIdent.IdentDescription AS IDENT_Desc,
> DATEPART(yyyy, dbo.CIMSDetail.EndDate) AS Year,
> DATEPART(mm, dbo.CIMSDetail.EndDate) AS Month,
> DATEPART(dd, dbo.CIMSDetail.EndDate) AS Day
>
>FROM dbo.CIMSDetail INNER JOIN
> dbo.CIMSDetailIdent ON dbo.CIMSDetail.DetailUID =>dbo.CIMSDetailIdent.DetailUID AND
> dbo.CIMSDetail.DetailLine =dbo.CIMSDetailIdent.DetailLine INNER JOIN
> dbo.CIMSIdent ON dbo.CIMSDetailIdent.IdentNumber =>dbo.CIMSIdent.IdentNumber
>WHERE DATEPART(DD,GETDATE()) - DATEPART(DD,
dbo.CIMSDetail.EndDate) =>1
> AND dbo.CIMSDetail.RateCode IN
('Z003', 'Z020', 'ZZ05')
> AND dbo.CIMSIdent.IdentDescription = 'JOBNAME'
> OR dbo.CIMSIdent.IdentDescription
= 'WORK_ID'
>GROUP BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32, 4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>ORDER BY
> DATEPART(yyyy, dbo.CIMSDetail.EndDate),
> DATEPART(mm, dbo.CIMSDetail.EndDate),
> DATEPART(dd, dbo.CIMSDetail.EndDate),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 32,
4),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 11, 2),
> SUBSTRING(dbo.CIMSDetail.AccountCode, 29, 3),
> dbo.CIMSDetail.RateCode,
> dbo.CIMSDetail.StartDate,
> dbo.CIMSDetail.EndDate,
> dbo.CIMSIdent.IdentDescription,
> dbo.CIMSDetailIdent.IdentValue,
> dbo.CIMSDetail.AccountCode,
> SUBSTRING(dbo.CIMSDETAIL.AccountCode,11,18)
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>

Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss the
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesnâ't change at all
and I profile on database level. So Iâ'm confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

Query execution time using an ORMapper

Hello there!
I have a strange problem regarding the query execution time of a
parameterised query executed over sp_executesql. We use an ORMapper
(NHibernate) which generates the query, so it is not necessary to discuss th
e
use of sp_executesql. If I run the query in SQL Server Management Studio it
is much faster than executing it over the application. Looking at Profiler
the same query needs in MMS 6000 reads and 500 ms and over the application
nearly 200.000 reads and 30.000 ms.
I have no idea why this happens because the database doesn’t change at all
and I profile on database level. So I’m confused that it depends on the
execution source. The count of database sites in the data cache is constant
and the query plan cache is hit also in both executions.
Any ideas? Thanks
Rob"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:EB7FF480-3C92-48AA-8867-FF2C30E55767@.microsoft.com...
> Hello there!
> I have a strange problem regarding the query execution time of a
> parameterised query executed over sp_executesql. We use an ORMapper
> (NHibernate) which generates the query, so it is not necessary to discuss
> the
> use of sp_executesql. If I run the query in SQL Server Management Studio
> it
> is much faster than executing it over the application. Looking at Profiler
> the same query needs in MMS 6000 reads and 500 ms and over the application
> nearly 200.000 reads and 30.000 ms.
> I have no idea why this happens because the database doesn't change at all
> and I profile on database level. So I'm confused that it depends on the
> execution source. The count of database sites in the data cache is
> constant
> and the query plan cache is hit also in both executions.
>
sp_executesql is fine, the issue is that a parameterized query is just
different than a non-parameterized one. You should expect a different plan
for expensive queries.
Test, analyze and optimize the query in Management Studio using
sp_executesql.
David

Query execution time problem

We are executing following query from query analyzer,
SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
[Table Strcuture => id int, data varchar(500)]
This table contains 45000 records.
When this query is executed from Query Analyzer locally on the database
server, output is returned in 30 seconds.
However, when the query is executed from Query Analyzer installed on
application server it takes 110 seconds.
Execution plan is same both times. CPU, reads are identical for both
executions. However there is big difference between the execution times.
Also, both database server and application server in same VLAN and direct
network connectivity is available without any interim hops.
Any kind of help or suggestion giving insight into this aspect is welcomed.
Thanks in advance for all the inputs.pvv30 (pvv30@.discussions.microsoft.com) writes:
> We are executing following query from query analyzer,
> SELECT PD_ID, PD_DATA FROM TBL_PROJECT_DETAILS
> [Table Strcuture => id int, data varchar(500)]
> This table contains 45000 records.
> When this query is executed from Query Analyzer locally on the database
> server, output is returned in 30 seconds.
> However, when the query is executed from Query Analyzer installed on
> application server it takes 110 seconds.
> Execution plan is same both times. CPU, reads are identical for both
> executions. However there is big difference between the execution times.
> Also, both database server and application server in same VLAN and direct
> network connectivity is available without any interim hops.
The most obvious guess is that network latency matters. It may be a good
VLAN, but on the database server the connection is over shared memory
so it's not unreasonable that it takes more time to get it over the
wire.
Another thing to consider is the CPU power on the two machines.
Particularly if you run the output in text mode in QA, this could
matter.
Then again, I don't really see the point in measuring how long time
takes to receive 45000 rows in Query Analyzer. The tool is not really
intended for that.
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|||Query Analyzer was used just to indicate a client application.
We are also monitoring the SQL calls using Profiler which is also giving us
the same output.
Also, the two boxes are of same configuration in terms of RAM & CPU.
You mentioned about connection being created in shared memory. Can anyone
please explain it in more detail as to how will it impact the query
performance? It might help us in understanding the problem better and work
towards a solution.
This time we enabled client statistics option in Query Analyzer. The
difference between two queries is for following attributes,
1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
(app server)] v/s 81 [for 30 sec execution (locally from database server)]
2. "Number of bytes received" recieved is slightly more for query executed
from app server compared to database server
What does "Cumulative wait time on server replies" stats represents and how
to interpret it?
Thanks in advance for the responses.
"Erland Sommarskog" wrote:

> pvv30 (pvv30@.discussions.microsoft.com) writes:
> The most obvious guess is that network latency matters. It may be a good
> VLAN, but on the database server the connection is over shared memory
> so it's not unreasonable that it takes more time to get it over the
> wire.
> Another thing to consider is the CPU power on the two machines.
> Particularly if you run the output in text mode in QA, this could
> matter.
> Then again, I don't really see the point in measuring how long time
> takes to receive 45000 rows in Query Analyzer. The tool is not really
> intended for that.
>
> --
> 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
>|||pvv30 (pvv30@.discussions.microsoft.com) writes:
> Query Analyzer was used just to indicate a client application.
I don't think it's a good benchmark for that purpose, since QA does a lot
of things a client app wouldn't do. You are better of writing a client
application that just receives the dataset. Particularly, you should use
the client API you plan to use in your client application.

> You mentioned about connection being created in shared memory. Can anyone
> please explain it in more detail as to how will it impact the query
> performance? It might help us in understanding the problem better and work
> towards a solution.
Normally clients communicate with SQL Server over network connection, most
commonly these days is TCP/IP, but there is also support for named pipes
and a few more protocols.
But when the client and server are on the same physical machine, using
the network is bit of overkill and therefore there is the possibiliy to
use shared memory, and this is also the default when connecting locally.
You can use the Client Network Utility to turn off shared memory, but
even with TCP/IP locally, you would see much better execution times
locally than over the network.

> This time we enabled client statistics option in Query Analyzer. The
> difference between two queries is for following attributes,
> 1. "Cumulative wait time on server replies" 38045 [for 110 sec execution
> (app server)] v/s 81 [for 30 sec execution (locally from database server)]
> 2. "Number of bytes received" recieved is slightly more for query executed
> from app server compared to database server
> What does "Cumulative wait time on server replies" stats represents and
> how to interpret it?
Books Online says "Cumulative amount of time the driver spent waiting for
replies from the server."
I would account the difference in numbers to the network connection. I
did a quick test on our site. I ran the same SELECT * against a 35000
row table on two servers. One which is hear at our main office in
Stockholm, and one which is at our branch office in Gvle. For the
server here in Stockholm the number was 60, and the server in Gvle
it was 12075. The difference in execution time was 2 seconds in
Stockholm and 28 seconds for the server in Gvle.
I ran both tests from my own machine, which hosts none of the servers in
question.
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

query execution time

i want to know is there is a command to know how many time takes a query to run

You can achive this using SQL Profiler. You need not to do any programming/coding for this.

See at Books Online..

You can do it programatically also...

Here the sample code..

Code Snippet

DECLARE @.StartDateTime DATETIME

DECLARE @.EndDateTime DATETIME

DECLARE @.Msg VARCHAR(200)

DECLARE @.RC as Int

SELECT @.StartDateTime = GETDATE()

EXEC YOURSP / QUERY

SELECT @.RC = @.@.ROWCOUNT, @.EndDateTime = GETDATE()

SELECT @.Msg = 'Your SP Name' + CONVERT(VARCHAR(10),@.RC) + ' ' + CONVERT(VARCHAR(25), DATEDIFF(MS, @.StartDateTime, @.EndDateTime)) + 'ms'

PRINT @.Msg

|||

instead of above code use sp_who.

|||there have to be something like just one command
|||

Luis:

Maybe SET STATISTICS TIME ON and SET STATISTICS TIME OFF?

sql

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regards
Have you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards
|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx
.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Query execution time

Hi,
Is there any command that i can use, that tells me with
precision the execution time of a query?
I'm using:
select getdate()
execute Query
select getdate()
Thanks,
Best regardsHave you tried: SET STATISTICS TIME ON ?
This will return a message with CPU time and elapsed time.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards|||Look at the SET STATISTICS TIME statement. You might want to look ate SET
STATISTICS IO and SET STATISTICS PROFILE as additional performance tuning
tools.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:28b0701c46519$98104f50$a601280a@.phx.gbl...
> Hi,
> Is there any command that i can use, that tells me with
> precision the execution time of a query?
> I'm using:
> select getdate()
> execute Query
> select getdate()
> Thanks,
> Best regards

Tuesday, March 20, 2012

Query execution failed for data set ''RS_MeterReading

Hi,

We are using SQL 2005 server for generating reports.When we ran the reports it taking so much time after some time it shows this error:

ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. > Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set > System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.

Can you help me out.

Thanks,

--Amit

Hi AMITMEENA

Can you check the Report Query whether it contains all valid columns and tables.

This type of Error may Occur when we are trying to Query the Columns that are not related the tables present in the Query

|||

This looks like Sql query/procedure error. Check the dataset query/procedure in Sql Query analyzer.

Senthil

|||

Yes ,I did this thing also.But When we ran the " StoreProcedure in Queranalyzer it is working fine ..I am getting the data within 10 sec. for all system.But When we ran the same report from reportviewer..we are getting this eror "ERROR: An exception has occurred in data source . Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set . > System.Data.SqlClient.SqlException: A severe error occurred on the current command"

Can give some inputs.

Your inputs would be appreciated.

|||

Could this be a permissions problem? IOW, does the identity executing the query through RS have the permissions it needs to execute the proc?

>L<

|||

can you give me some detail expaination.I didn't understand what does it?

Thanks,

Amit

|||

What didn't you understand? Checking the permissions for a sproc? Or something else in this thread?

>L<

|||

I have also increase the SesionTimeOut upto 6000sec. but still am getting that error.So i am sending the log files of system.here we want to run the Performancereport,it works fine when we ran this report in QueryAnalyzer but when i ran that report in WebReorting am getting that error log files.:--

|||

Does the report run under the credentials of the user or (as probably has to be for a snapshot) are you storing the credentials with/in report server?

It looks like you're creating a snapshot here.

So, under what credentials is that running? And does that identity have permissions to execute this stored procedure in the appropriate database?

(going to bed now, won't see your reply immediately)

>L<

|||I need some more explaination|||

Hi moderator,

Can you delete my all threads from your system.

Thanks

|||

My team is also facing similar problem. The RS trace logs report:

w3wp!processing!13!9/26/2007-15:31:23:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'msdb'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'msdb'. > System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.
Operation cancelled by user.

In our system, Report Server and Database are on different machines. Report Server access database using a service account who has stored proc execute permissions on database.

Problem comes only if the query execution time exceeds 5 mins. Otherwise the report gets generated successfully.

I suspected this to be some timeout issue. But I have checked that all timeout settings in rs config files are as default.

Any pointers?

Thanks

puns

pun_iit@.hotmail.com

Query execution failed for data set ''RS_MeterReading

Hi,

We are using SQL 2005 server for generating reports.When we ran the reports it taking so much time after some time it shows this error:

Can you help me out.

Thanks,

--Amit

Hi AMITMEENA

Can you check the Report Query whether it contains all valid columns and tables.

This type of Error may Occur when we are trying to Query the Columns that are not related the tables present in the Query

|||

This looks like Sql query/procedure error. Check the dataset query/procedure in Sql Query analyzer.

Senthil

|||

Can give some inputs.

Your inputs would be appreciated.

|||

Could this be a permissions problem? IOW, does the identity executing the query through RS have the permissions it needs to execute the proc?

>L<

|||

can you give me some detail expaination.I didn't understand what does it?

Thanks,

Amit

|||

What didn't you understand? Checking the permissions for a sproc? Or something else in this thread?

>L<

|||

Does the report run under the credentials of the user or (as probably has to be for a snapshot) are you storing the credentials with/in report server?

It looks like you're creating a snapshot here.

So, under what credentials is that running? And does that identity have permissions to execute this stored procedure in the appropriate database?

(going to bed now, won't see your reply immediately)

>L<

|||I need some more explaination|||

Hi moderator,

Can you delete my all threads from your system.

Thanks

|||

My team is also facing similar problem. The RS trace logs report:

In our system, Report Server and Database are on different machines. Report Server access database using a service account who has stored proc execute permissions on database.

Problem comes only if the query execution time exceeds 5 mins. Otherwise the report gets generated successfully.

I suspected this to be some timeout issue. But I have checked that all timeout settings in rs config files are as default.

Any pointers?

Thanks

puns

pun_iit@.hotmail.com

Query execution failed for data set ''RS_MeterReading

Hi,

We are using SQL 2005 server for generating reports.When we ran the reports it taking so much time after some time it shows this error:

Can you help me out.

Thanks,

--Amit

Hi AMITMEENA

Can you check the Report Query whether it contains all valid columns and tables.

This type of Error may Occur when we are trying to Query the Columns that are not related the tables present in the Query

|||

This looks like Sql query/procedure error. Check the dataset query/procedure in Sql Query analyzer.

Senthil

|||

Can give some inputs.

Your inputs would be appreciated.

|||

Could this be a permissions problem? IOW, does the identity executing the query through RS have the permissions it needs to execute the proc?

>L<

|||

can you give me some detail expaination.I didn't understand what does it?

Thanks,

Amit

|||

What didn't you understand? Checking the permissions for a sproc? Or something else in this thread?

>L<

|||

Does the report run under the credentials of the user or (as probably has to be for a snapshot) are you storing the credentials with/in report server?

It looks like you're creating a snapshot here.

So, under what credentials is that running? And does that identity have permissions to execute this stored procedure in the appropriate database?

(going to bed now, won't see your reply immediately)

>L<

|||I need some more explaination|||

Hi moderator,

Can you delete my all threads from your system.

Thanks

|||

My team is also facing similar problem. The RS trace logs report:

In our system, Report Server and Database are on different machines. Report Server access database using a service account who has stored proc execute permissions on database.

Problem comes only if the query execution time exceeds 5 mins. Otherwise the report gets generated successfully.

I suspected this to be some timeout issue. But I have checked that all timeout settings in rs config files are as default.

Any pointers?

Thanks

puns

pun_iit@.hotmail.com

Query execution failed for data set ''RS_MeterReading

Hi,

We are using SQL 2005 server for generating reports.When we ran the reports it taking so much time after some time it shows this error:

Can you help me out.

Thanks,

--Amit

Hi AMITMEENA

Can you check the Report Query whether it contains all valid columns and tables.

This type of Error may Occur when we are trying to Query the Columns that are not related the tables present in the Query

|||

This looks like Sql query/procedure error. Check the dataset query/procedure in Sql Query analyzer.

Senthil

|||

Can give some inputs.

Your inputs would be appreciated.

|||

Could this be a permissions problem? IOW, does the identity executing the query through RS have the permissions it needs to execute the proc?

>L<

|||

can you give me some detail expaination.I didn't understand what does it?

Thanks,

Amit

|||

What didn't you understand? Checking the permissions for a sproc? Or something else in this thread?

>L<

|||

Does the report run under the credentials of the user or (as probably has to be for a snapshot) are you storing the credentials with/in report server?

It looks like you're creating a snapshot here.

So, under what credentials is that running? And does that identity have permissions to execute this stored procedure in the appropriate database?

(going to bed now, won't see your reply immediately)

>L<

|||I need some more explaination|||

Hi moderator,

Can you delete my all threads from your system.

Thanks

|||

My team is also facing similar problem. The RS trace logs report:

In our system, Report Server and Database are on different machines. Report Server access database using a service account who has stored proc execute permissions on database.

Problem comes only if the query execution time exceeds 5 mins. Otherwise the report gets generated successfully.

I suspected this to be some timeout issue. But I have checked that all timeout settings in rs config files are as default.

Any pointers?

Thanks

puns

pun_iit@.hotmail.com

Friday, March 30, 2012

query help

Wednesday, March 28, 2012

query help

Query Help

query help

Query Help

Query Hangs/Time out

Query Hangs/Time out

Query Hangs/Time out

Wednesday, March 21, 2012

query filling tempdb

Query execution time using an ORMapper

Query execution time using an ORMapper

Query execution time problem

query execution time

Query execution time

Query execution time

Query execution time

Tuesday, March 20, 2012

Query execution failed for data set ''RS_MeterReading

Query execution failed for data set ''RS_MeterReading

Query execution failed for data set ''RS_MeterReading

Query execution failed for data set ''RS_MeterReading

query assistance

Blog Archive

About Me